content: {-# LANGUAGE QuasiQuotes #-}
  
  module Simplex.Chat.Store.SQLite.Migrations.M20230914_member_probes where
  
  import Database.SQLite.Simple (Query)
  import Database.SQLite.Simple.QQ (sql)
  
  m20230914_member_probes :: Query
  m20230914_member_probes =
    [sql|
  CREATE TABLE new__sent_probes(
    sent_probe_id INTEGER PRIMARY KEY,
    contact_id INTEGER REFERENCES contacts ON DELETE CASCADE,
    group_member_id INTEGER REFERENCES group_members ON DELETE CASCADE,
    probe BLOB NOT NULL,
    user_id INTEGER NOT NULL REFERENCES users ON DELETE CASCADE,
    created_at TEXT CHECK(created_at NOT NULL),
    updated_at TEXT CHECK(updated_at NOT NULL),
    UNIQUE(user_id, probe)
  );
  
  CREATE TABLE new__sent_probe_hashes(
    sent_probe_hash_id INTEGER PRIMARY KEY,
    sent_probe_id INTEGER NOT NULL REFERENCES new__sent_probes ON DELETE CASCADE,
    contact_id INTEGER REFERENCES contacts ON DELETE CASCADE,
    group_member_id INTEGER REFERENCES group_members ON DELETE CASCADE,
    user_id INTEGER NOT NULL REFERENCES users ON DELETE CASCADE,
    created_at TEXT CHECK(created_at NOT NULL),
    updated_at TEXT CHECK(updated_at NOT NULL)
  );
  
  CREATE TABLE new__received_probes(
    received_probe_id INTEGER PRIMARY KEY,
    contact_id INTEGER REFERENCES contacts ON DELETE CASCADE,
    group_member_id INTEGER REFERENCES group_members ON DELETE CASCADE,
    probe BLOB,
    probe_hash BLOB NOT NULL,
    user_id INTEGER NOT NULL REFERENCES users ON DELETE CASCADE,
    created_at TEXT CHECK(created_at NOT NULL),
    updated_at TEXT CHECK(updated_at NOT NULL)
  );
  
  INSERT INTO new__sent_probes
    (sent_probe_id, contact_id, probe, user_id, created_at, updated_at)
  SELECT
    sent_probe_id, contact_id, probe, user_id, created_at, updated_at
    FROM sent_probes;
  
  INSERT INTO new__sent_probe_hashes
    (sent_probe_hash_id, sent_probe_id, contact_id, user_id, created_at, updated_at)
  SELECT
    sent_probe_hash_id, sent_probe_id, contact_id, user_id, created_at, updated_at
    FROM sent_probe_hashes;
  
  INSERT INTO new__received_probes
    (received_probe_id, contact_id, probe, probe_hash, user_id, created_at, updated_at)
  SELECT
    received_probe_id, contact_id, probe, probe_hash, user_id, created_at, updated_at
    FROM received_probes;
  
  DROP INDEX idx_sent_probe_hashes_user_id;
  DROP INDEX idx_sent_probe_hashes_contact_id;
  DROP INDEX idx_received_probes_user_id;
  DROP INDEX idx_received_probes_contact_id;
  
  DROP TABLE sent_probes;
  DROP TABLE sent_probe_hashes;
  DROP TABLE received_probes;
  
  ALTER TABLE new__sent_probes RENAME TO sent_probes;
  ALTER TABLE new__sent_probe_hashes RENAME TO sent_probe_hashes;
  ALTER TABLE new__received_probes RENAME TO received_probes;
  
  CREATE INDEX idx_sent_probes_user_id ON sent_probes(user_id);
  CREATE INDEX idx_sent_probes_contact_id ON sent_probes(contact_id);
  CREATE INDEX idx_sent_probes_group_member_id ON sent_probes(group_member_id);
  
  CREATE INDEX idx_sent_probe_hashes_user_id ON sent_probe_hashes(user_id);
  CREATE INDEX idx_sent_probe_hashes_sent_probe_id ON sent_probe_hashes(sent_probe_id);
  CREATE INDEX idx_sent_probe_hashes_contact_id ON sent_probe_hashes(contact_id);
  CREATE INDEX idx_sent_probe_hashes_group_member_id ON sent_probe_hashes(group_member_id);
  
  CREATE INDEX idx_received_probes_user_id ON received_probes(user_id);
  CREATE INDEX idx_received_probes_contact_id ON received_probes(contact_id);
  CREATE INDEX idx_received_probes_probe ON received_probes(probe);
  CREATE INDEX idx_received_probes_probe_hash ON received_probes(probe_hash);
  |]
  
  down_m20230914_member_probes :: Query
  down_m20230914_member_probes =
    [sql|
  CREATE TABLE old__sent_probes(
    sent_probe_id INTEGER PRIMARY KEY,
    contact_id INTEGER NOT NULL REFERENCES contacts ON DELETE CASCADE,
    probe BLOB NOT NULL,
    user_id INTEGER NOT NULL REFERENCES users ON DELETE CASCADE,
    created_at TEXT CHECK(created_at NOT NULL),
    updated_at TEXT CHECK(updated_at NOT NULL),
    UNIQUE(user_id, probe)
  );
  
  CREATE TABLE old__sent_probe_hashes(
    sent_probe_hash_id INTEGER PRIMARY KEY,
    sent_probe_id INTEGER NOT NULL REFERENCES old__sent_probes ON DELETE CASCADE,
    contact_id INTEGER NOT NULL REFERENCES contacts ON DELETE CASCADE,
    user_id INTEGER NOT NULL REFERENCES users ON DELETE CASCADE,
    created_at TEXT CHECK(created_at NOT NULL),
    updated_at TEXT CHECK(updated_at NOT NULL)
  );
  
  CREATE TABLE old__received_probes(
    received_probe_id INTEGER PRIMARY KEY,
    contact_id INTEGER NOT NULL REFERENCES contacts ON DELETE CASCADE,
    probe BLOB,
    probe_hash BLOB NOT NULL,
    user_id INTEGER NOT NULL REFERENCES users ON DELETE CASCADE,
    created_at TEXT CHECK(created_at NOT NULL),
    updated_at TEXT CHECK(updated_at NOT NULL)
  );
  
  DELETE FROM sent_probes WHERE contact_id IS NULL;
  DELETE FROM sent_probe_hashes WHERE contact_id IS NULL;
  DELETE FROM received_probes WHERE contact_id IS NULL;
  
  INSERT INTO old__sent_probes
    (sent_probe_id, contact_id, probe, user_id, created_at, updated_at)
  SELECT
    sent_probe_id, contact_id, probe, user_id, created_at, updated_at
    FROM sent_probes;
  
  INSERT INTO old__sent_probe_hashes
    (sent_probe_hash_id, sent_probe_id, contact_id, user_id, created_at, updated_at)
  SELECT
    sent_probe_hash_id, sent_probe_id, contact_id, user_id, created_at, updated_at
    FROM sent_probe_hashes;
  
  INSERT INTO old__received_probes
    (received_probe_id, contact_id, probe, probe_hash, user_id, created_at, updated_at)
  SELECT
    received_probe_id, contact_id, probe, probe_hash, user_id, created_at, updated_at
    FROM received_probes;
  
  DROP INDEX idx_sent_probes_user_id;
  DROP INDEX idx_sent_probes_contact_id;
  DROP INDEX idx_sent_probes_group_member_id;
  
  DROP INDEX idx_sent_probe_hashes_user_id;
  DROP INDEX idx_sent_probe_hashes_sent_probe_id;
  DROP INDEX idx_sent_probe_hashes_contact_id;
  DROP INDEX idx_sent_probe_hashes_group_member_id;
  
  DROP INDEX idx_received_probes_user_id;
  DROP INDEX idx_received_probes_contact_id;
  DROP INDEX idx_received_probes_probe;
  DROP INDEX idx_received_probes_probe_hash;
  
  DROP TABLE sent_probes;
  DROP TABLE sent_probe_hashes;
  DROP TABLE received_probes;
  
  ALTER TABLE old__sent_probes RENAME TO sent_probes;
  ALTER TABLE old__sent_probe_hashes RENAME TO sent_probe_hashes;
  ALTER TABLE old__received_probes RENAME TO received_probes;
  
  CREATE INDEX idx_received_probes_user_id ON received_probes(user_id);
  CREATE INDEX idx_received_probes_contact_id ON received_probes(contact_id);
  CREATE INDEX idx_sent_probe_hashes_user_id ON sent_probe_hashes(user_id);
  CREATE INDEX idx_sent_probe_hashes_contact_id ON sent_probe_hashes(contact_id);
  |]
  