content: {-# LANGUAGE QuasiQuotes #-}
  
  module Simplex.Chat.Store.SQLite.Migrations.M20230511_reactions where
  
  import Database.SQLite.Simple (Query)
  import Database.SQLite.Simple.QQ (sql)
  
  m20230511_reactions :: Query
  m20230511_reactions =
    [sql|
  CREATE TABLE chat_item_reactions (
    chat_item_reaction_id INTEGER PRIMARY KEY AUTOINCREMENT,
    item_member_id BLOB, -- member that created item, NULL for items in direct chats
    shared_msg_id BLOB NOT NULL,
    contact_id INTEGER REFERENCES contacts ON DELETE CASCADE,
    group_id INTEGER REFERENCES groups ON DELETE CASCADE,
    group_member_id INTEGER REFERENCES group_members ON DELETE SET NULL, -- member that sent reaction, NULL for items in direct chats
    created_by_msg_id INTEGER REFERENCES messages(message_id) ON DELETE SET NULL,
    reaction TEXT NOT NULL, -- JSON of MsgReaction
    reaction_sent INTEGER NOT NULL, -- 0 for received, 1 for sent
    reaction_ts TEXT NOT NULL, -- broker_ts of creating message for received, created_at for sent
    created_at TEXT NOT NULL DEFAULT(datetime('now')),
    updated_at TEXT NOT NULL DEFAULT(datetime('now'))
  );
  
  CREATE INDEX idx_chat_item_reactions_shared_msg_id ON chat_item_reactions(shared_msg_id);
  CREATE INDEX idx_chat_item_reactions_contact_id ON chat_item_reactions(contact_id);
  CREATE INDEX idx_chat_item_reactions_group_id ON chat_item_reactions(group_id);
  CREATE INDEX idx_chat_item_reactions_group_member_id ON chat_item_reactions(group_member_id);
  
  CREATE INDEX idx_chat_item_reactions_contact ON chat_item_reactions(contact_id, shared_msg_id);
  CREATE INDEX idx_chat_item_reactions_group ON chat_item_reactions(group_id, shared_msg_id);
  |]
  
  down_m20230511_reactions :: Query
  down_m20230511_reactions =
    [sql|
  DROP INDEX idx_chat_item_reactions_group;
  DROP INDEX idx_chat_item_reactions_contact;
  
  DROP INDEX idx_chat_item_reactions_group_member_id;
  DROP INDEX idx_chat_item_reactions_group_id;
  DROP INDEX idx_chat_item_reactions_contact_id;
  DROP INDEX idx_chat_item_reactions_shared_msg_id;
  
  DROP TABLE chat_item_reactions;
  |]
  