content: {-# LANGUAGE QuasiQuotes #-}
  
  module Simplex.Chat.Store.SQLite.Migrations.M20230621_chat_item_moderations where
  
  import Database.SQLite.Simple (Query)
  import Database.SQLite.Simple.QQ (sql)
  
  -- moderations that could not be applied - for messages that haven't been received at the time of moderation
  m20230621_chat_item_moderations :: Query
  m20230621_chat_item_moderations =
    [sql|
  CREATE TABLE chat_item_moderations (
    chat_item_moderation_id INTEGER PRIMARY KEY,
    group_id INTEGER NOT NULL REFERENCES groups ON DELETE CASCADE,
    moderator_member_id INTEGER NOT NULL REFERENCES group_members ON DELETE CASCADE,
    item_member_id BLOB NOT NULL,
    shared_msg_id BLOB NOT NULL,
    created_by_msg_id INTEGER REFERENCES messages(message_id) ON DELETE SET NULL,
    moderated_at TEXT NOT NULL, -- broker_ts of creating message
    created_at TEXT NOT NULL DEFAULT(datetime('now')),
    updated_at TEXT NOT NULL DEFAULT(datetime('now'))
  );
  
  CREATE INDEX idx_chat_item_moderations_group_id ON chat_item_moderations(group_id);
  CREATE INDEX idx_chat_item_moderations_moderator_member_id ON chat_item_moderations(moderator_member_id);
  CREATE INDEX idx_chat_item_moderations_created_by_msg_id ON chat_item_moderations(created_by_msg_id);
  
  CREATE INDEX idx_chat_item_moderations_group ON chat_item_moderations(group_id, item_member_id, shared_msg_id);
  |]
  
  down_m20230621_chat_item_moderations :: Query
  down_m20230621_chat_item_moderations =
    [sql|
  DROP INDEX idx_chat_item_moderations_group;
  
  DROP INDEX idx_chat_item_moderations_created_by_msg_id;
  DROP INDEX idx_chat_item_moderations_moderator_member_id;
  DROP INDEX idx_chat_item_moderations_group_id;
  
  DROP TABLE chat_item_moderations;
  |]
  