content: {-# LANGUAGE QuasiQuotes #-}
  
  module Simplex.Chat.Store.SQLite.Migrations.M20231215_recreate_msg_deliveries where
  
  import Database.SQLite.Simple (Query)
  import Database.SQLite.Simple.QQ (sql)
  
  m20231215_recreate_msg_deliveries :: Query
  m20231215_recreate_msg_deliveries =
    [sql|
  DROP VIEW IF EXISTS direct_messages;
  DROP VIEW IF EXISTS direct_messages_plain;
  DROP VIEW IF EXISTS group_messages;
  DROP VIEW IF EXISTS group_messages_plain;
  DROP VIEW IF EXISTS all_messages;
  DROP VIEW IF EXISTS all_messages_plain;
  
  DROP INDEX msg_delivery_events_msg_delivery_id;
  DROP TABLE msg_delivery_events;
  
  DROP INDEX idx_msg_deliveries_message_id;
  DROP INDEX idx_msg_deliveries_agent_ack_cmd_id;
  
  CREATE TABLE new_msg_deliveries(
    msg_delivery_id INTEGER PRIMARY KEY,
    message_id INTEGER NOT NULL REFERENCES messages ON DELETE CASCADE, -- non UNIQUE for group messages and for batched messages
    connection_id INTEGER NOT NULL REFERENCES connections ON DELETE CASCADE,
    agent_msg_id INTEGER, -- internal agent message ID (NULL while pending), non UNIQUE for batched messages
    agent_msg_meta TEXT, -- JSON with timestamps etc. sent in MSG, NULL for sent
    chat_ts TEXT NOT NULL DEFAULT(datetime('now')),
    created_at TEXT CHECK(created_at NOT NULL),
    updated_at TEXT CHECK(updated_at NOT NULL),
    agent_ack_cmd_id INTEGER, -- broker_ts for received, created_at for sent
    delivery_status TEXT -- MsgDeliveryStatus
  );
  
  INSERT INTO new_msg_deliveries (
    msg_delivery_id, message_id, connection_id, agent_msg_id, agent_msg_meta,
    chat_ts, created_at, updated_at, agent_ack_cmd_id
  )
  SELECT
    msg_delivery_id, message_id, connection_id, agent_msg_id, agent_msg_meta,
    chat_ts, created_at, updated_at, agent_ack_cmd_id
  FROM msg_deliveries;
  
  DROP TABLE msg_deliveries;
  ALTER TABLE new_msg_deliveries RENAME TO msg_deliveries;
  
  CREATE INDEX idx_msg_deliveries_message_id ON "msg_deliveries"(message_id);
  CREATE INDEX idx_msg_deliveries_agent_ack_cmd_id ON "msg_deliveries"(connection_id, agent_ack_cmd_id);
  CREATE INDEX idx_msg_deliveries_agent_msg_id ON "msg_deliveries"(connection_id, agent_msg_id);
  |]
  
  down_m20231215_recreate_msg_deliveries :: Query
  down_m20231215_recreate_msg_deliveries =
    [sql|
  DROP INDEX idx_msg_deliveries_message_id;
  DROP INDEX idx_msg_deliveries_agent_ack_cmd_id;
  DROP INDEX idx_msg_deliveries_agent_msg_id;
  
  CREATE TABLE old_msg_deliveries(
    msg_delivery_id INTEGER PRIMARY KEY,
    message_id INTEGER NOT NULL REFERENCES messages ON DELETE CASCADE, -- non UNIQUE for group messages
    connection_id INTEGER NOT NULL REFERENCES connections ON DELETE CASCADE,
    agent_msg_id INTEGER, -- internal agent message ID(NULL while pending)
    agent_msg_meta TEXT, -- JSON with timestamps etc. sent in MSG, NULL for sent
    chat_ts TEXT NOT NULL DEFAULT(datetime('now')),
    created_at TEXT CHECK(created_at NOT NULL),
    updated_at TEXT CHECK(updated_at NOT NULL),
    agent_ack_cmd_id INTEGER, -- broker_ts for received, created_at for sent
    UNIQUE(connection_id, agent_msg_id)
  );
  
  INSERT INTO old_msg_deliveries (
    msg_delivery_id, message_id, connection_id, agent_msg_id, agent_msg_meta,
    chat_ts, created_at, updated_at, agent_ack_cmd_id
  )
  WITH unique_msg_deliveries AS (
    SELECT
      msg_delivery_id, message_id, connection_id, agent_msg_id, agent_msg_meta,
      chat_ts, created_at, updated_at, agent_ack_cmd_id,
      row_number() OVER connection_id_agent_msg_id_win AS row_number
    FROM msg_deliveries
    WINDOW connection_id_agent_msg_id_win AS (PARTITION BY connection_id, agent_msg_id ORDER BY created_at ASC, msg_delivery_id ASC)
  )
  SELECT
    msg_delivery_id, message_id, connection_id, agent_msg_id, agent_msg_meta,
    chat_ts, created_at, updated_at, agent_ack_cmd_id
  FROM unique_msg_deliveries
  WHERE row_number = 1;
  
  DROP TABLE msg_deliveries;
  ALTER TABLE old_msg_deliveries RENAME TO msg_deliveries;
  
  CREATE INDEX idx_msg_deliveries_message_id ON "msg_deliveries"(message_id);
  CREATE INDEX idx_msg_deliveries_agent_ack_cmd_id ON "msg_deliveries"(connection_id, agent_ack_cmd_id);
  
  CREATE TABLE msg_delivery_events (
    msg_delivery_event_id INTEGER PRIMARY KEY,
    msg_delivery_id INTEGER NOT NULL REFERENCES msg_deliveries ON DELETE CASCADE, -- non UNIQUE for multiple events per msg delivery
    delivery_status TEXT NOT NULL, -- see MsgDeliveryStatus for allowed values
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now')),
    UNIQUE (msg_delivery_id, delivery_status)
  );
  CREATE INDEX msg_delivery_events_msg_delivery_id ON msg_delivery_events(msg_delivery_id);
  |]
  