content: {-# LANGUAGE QuasiQuotes #-}
  
  module Simplex.Chat.Store.SQLite.Migrations.M20220122_v1_1 where
  
  import Database.SQLite.Simple (Query)
  import Database.SQLite.Simple.QQ (sql)
  
  m20220122_v1_1 :: Query
  m20220122_v1_1 =
    [sql|
  -- * pending group messages
  
  -- pending messages for announced (memberCurrent) but not yet connected (memberActive) group members
  CREATE TABLE pending_group_messages (
    pending_group_message_id INTEGER PRIMARY KEY,
    group_member_id INTEGER NOT NULL REFERENCES group_members ON DELETE CASCADE,
    message_id INTEGER NOT NULL REFERENCES messages ON DELETE CASCADE,
    group_member_intro_id INTEGER REFERENCES group_member_intros ON DELETE CASCADE,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now'))
  );
  
  -- * chat items
  
  -- mutable chat_items presented to user
  CREATE TABLE chat_items (
    chat_item_id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users ON DELETE CASCADE,
    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, -- NULL for sent even if group_id is not
    chat_msg_id INTEGER, -- sent as part of the message that created the item
    created_by_msg_id INTEGER UNIQUE REFERENCES messages (message_id) ON DELETE SET NULL,
    item_sent INTEGER NOT NULL, -- 0 for received, 1 for sent
    item_ts TEXT NOT NULL, -- broker_ts of creating message for received, created_at for sent
    item_deleted INTEGER NOT NULL DEFAULT 0, -- 1 for deleted
    item_content TEXT NOT NULL, -- JSON
    item_text TEXT NOT NULL, -- textual representation
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now'))
  );
  
  CREATE TABLE chat_item_messages (
    chat_item_id INTEGER NOT NULL REFERENCES chat_items ON DELETE CASCADE,
    message_id INTEGER NOT NULL UNIQUE REFERENCES messages ON DELETE CASCADE,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now')),
    UNIQUE (chat_item_id, message_id)
  );
  
  ALTER TABLE files ADD COLUMN chat_item_id INTEGER DEFAULT NULL REFERENCES chat_items ON DELETE CASCADE;
  
  -- * created_at & updated_at for all tables
  
  PRAGMA ignore_check_constraints=ON;
  
  -- ** contact_profiles
  
  ALTER TABLE contact_profiles ADD COLUMN created_at TEXT CHECK (created_at NOT NULL);
  UPDATE contact_profiles SET created_at = '1970-01-01 00:00:00';
  
  ALTER TABLE contact_profiles ADD COLUMN updated_at TEXT CHECK (updated_at NOT NULL);
  UPDATE contact_profiles SET updated_at = '1970-01-01 00:00:00';
  
  -- ** users
  
  ALTER TABLE users ADD COLUMN created_at TEXT CHECK (created_at NOT NULL);
  UPDATE users SET created_at = '1970-01-01 00:00:00';
  
  ALTER TABLE users ADD COLUMN updated_at TEXT CHECK (updated_at NOT NULL);
  UPDATE users SET updated_at = '1970-01-01 00:00:00';
  
  -- ** display_names
  
  ALTER TABLE display_names ADD COLUMN created_at TEXT CHECK (created_at NOT NULL);
  UPDATE display_names SET created_at = '1970-01-01 00:00:00';
  
  ALTER TABLE display_names ADD COLUMN updated_at TEXT CHECK (updated_at NOT NULL);
  UPDATE display_names SET updated_at = '1970-01-01 00:00:00';
  
  -- ** contacts
  
  ALTER TABLE contacts ADD COLUMN updated_at TEXT CHECK (updated_at NOT NULL);
  UPDATE contacts SET updated_at = '1970-01-01 00:00:00';
  
  -- ** sent_probes
  
  ALTER TABLE sent_probes ADD COLUMN created_at TEXT CHECK (created_at NOT NULL);
  UPDATE sent_probes SET created_at = '1970-01-01 00:00:00';
  
  ALTER TABLE sent_probes ADD COLUMN updated_at TEXT CHECK (updated_at NOT NULL);
  UPDATE sent_probes SET updated_at = '1970-01-01 00:00:00';
  
  -- ** sent_probe_hashes
  
  ALTER TABLE sent_probe_hashes ADD COLUMN created_at TEXT CHECK (created_at NOT NULL);
  UPDATE sent_probe_hashes SET created_at = '1970-01-01 00:00:00';
  
  ALTER TABLE sent_probe_hashes ADD COLUMN updated_at TEXT CHECK (updated_at NOT NULL);
  UPDATE sent_probe_hashes SET updated_at = '1970-01-01 00:00:00';
  
  -- ** received_probes
  
  ALTER TABLE received_probes ADD COLUMN created_at TEXT CHECK (created_at NOT NULL);
  UPDATE received_probes SET created_at = '1970-01-01 00:00:00';
  
  ALTER TABLE received_probes ADD COLUMN updated_at TEXT CHECK (updated_at NOT NULL);
  UPDATE received_probes SET updated_at = '1970-01-01 00:00:00';
  
  -- ** known_servers
  
  ALTER TABLE known_servers ADD COLUMN created_at TEXT CHECK (created_at NOT NULL);
  UPDATE known_servers SET created_at = '1970-01-01 00:00:00';
  
  ALTER TABLE known_servers ADD COLUMN updated_at TEXT CHECK (updated_at NOT NULL);
  UPDATE known_servers SET updated_at = '1970-01-01 00:00:00';
  
  -- ** group_profiles
  
  ALTER TABLE group_profiles ADD COLUMN created_at TEXT CHECK (created_at NOT NULL);
  UPDATE group_profiles SET created_at = '1970-01-01 00:00:00';
  
  ALTER TABLE group_profiles ADD COLUMN updated_at TEXT CHECK (updated_at NOT NULL);
  UPDATE group_profiles SET updated_at = '1970-01-01 00:00:00';
  
  -- ** groups
  
  ALTER TABLE groups ADD COLUMN created_at TEXT CHECK (created_at NOT NULL);
  UPDATE groups SET created_at = '1970-01-01 00:00:00';
  
  ALTER TABLE groups ADD COLUMN updated_at TEXT CHECK (updated_at NOT NULL);
  UPDATE groups SET updated_at = '1970-01-01 00:00:00';
  
  -- ** group_members
  
  ALTER TABLE group_members ADD COLUMN created_at TEXT CHECK (created_at NOT NULL);
  UPDATE group_members SET created_at = '1970-01-01 00:00:00';
  
  ALTER TABLE group_members ADD COLUMN updated_at TEXT CHECK (updated_at NOT NULL);
  UPDATE group_members SET updated_at = '1970-01-01 00:00:00';
  
  -- ** group_member_intros
  
  ALTER TABLE group_member_intros ADD COLUMN created_at TEXT CHECK (created_at NOT NULL);
  UPDATE group_member_intros SET created_at = '1970-01-01 00:00:00';
  
  ALTER TABLE group_member_intros ADD COLUMN updated_at TEXT CHECK (updated_at NOT NULL);
  UPDATE group_member_intros SET updated_at = '1970-01-01 00:00:00';
  
  -- ** files
  
  ALTER TABLE files ADD COLUMN updated_at TEXT CHECK (updated_at NOT NULL);
  UPDATE files SET updated_at = '1970-01-01 00:00:00';
  
  -- ** snd_files
  
  ALTER TABLE snd_files ADD COLUMN created_at TEXT CHECK (created_at NOT NULL);
  UPDATE snd_files SET created_at = '1970-01-01 00:00:00';
  
  ALTER TABLE snd_files ADD COLUMN updated_at TEXT CHECK (updated_at NOT NULL);
  UPDATE snd_files SET updated_at = '1970-01-01 00:00:00';
  
  -- ** rcv_files
  
  ALTER TABLE rcv_files ADD COLUMN created_at TEXT CHECK (created_at NOT NULL);
  UPDATE rcv_files SET created_at = '1970-01-01 00:00:00';
  
  ALTER TABLE rcv_files ADD COLUMN updated_at TEXT CHECK (updated_at NOT NULL);
  UPDATE rcv_files SET updated_at = '1970-01-01 00:00:00';
  
  -- ** snd_file_chunks
  
  ALTER TABLE snd_file_chunks ADD COLUMN created_at TEXT CHECK (created_at NOT NULL);
  UPDATE snd_file_chunks SET created_at = '1970-01-01 00:00:00';
  
  ALTER TABLE snd_file_chunks ADD COLUMN updated_at TEXT CHECK (updated_at NOT NULL);
  UPDATE snd_file_chunks SET updated_at = '1970-01-01 00:00:00';
  
  -- ** rcv_file_chunks
  
  ALTER TABLE rcv_file_chunks ADD COLUMN created_at TEXT CHECK (created_at NOT NULL);
  UPDATE rcv_file_chunks SET created_at = '1970-01-01 00:00:00';
  
  ALTER TABLE rcv_file_chunks ADD COLUMN updated_at TEXT CHECK (updated_at NOT NULL);
  UPDATE rcv_file_chunks SET updated_at = '1970-01-01 00:00:00';
  
  -- ** connections
  
  ALTER TABLE connections ADD COLUMN updated_at TEXT CHECK (updated_at NOT NULL);
  UPDATE connections SET updated_at = '1970-01-01 00:00:00';
  
  -- ** user_contact_links
  
  ALTER TABLE user_contact_links ADD COLUMN updated_at TEXT CHECK (updated_at NOT NULL);
  UPDATE user_contact_links SET updated_at = '1970-01-01 00:00:00';
  
  -- ** contact_requests
  
  ALTER TABLE contact_requests ADD COLUMN updated_at TEXT CHECK (updated_at NOT NULL);
  UPDATE contact_requests SET updated_at = '1970-01-01 00:00:00';
  
  -- ** messages
  
  ALTER TABLE messages ADD COLUMN updated_at TEXT CHECK (updated_at NOT NULL);
  UPDATE messages SET updated_at = '1970-01-01 00:00:00';
  
  -- ** msg_deliveries
  
  ALTER TABLE msg_deliveries ADD COLUMN created_at TEXT CHECK (created_at NOT NULL);
  UPDATE msg_deliveries SET created_at = '1970-01-01 00:00:00';
  
  ALTER TABLE msg_deliveries ADD COLUMN updated_at TEXT CHECK (updated_at NOT NULL);
  UPDATE msg_deliveries SET updated_at = '1970-01-01 00:00:00';
  
  -- ** msg_delivery_events
  
  ALTER TABLE msg_delivery_events ADD COLUMN updated_at TEXT CHECK (updated_at NOT NULL);
  UPDATE msg_delivery_events SET updated_at = '1970-01-01 00:00:00';
  
  PRAGMA ignore_check_constraints=OFF;
  |]
  