content: {-# LANGUAGE QuasiQuotes #-}
  
  module Simplex.Messaging.Agent.Store.SQLite.Migrations.M20220915_connection_queues where
  
  import Database.SQLite.Simple (Query)
  import Database.SQLite.Simple.QQ (sql)
  
  m20220915_connection_queues :: Query
  m20220915_connection_queues =
    [sql|
  PRAGMA ignore_check_constraints=ON;
  
  -- rcv_queues
  ALTER TABLE rcv_queues ADD COLUMN rcv_queue_id INTEGER CHECK (rcv_queue_id NOT NULL);
  UPDATE rcv_queues SET rcv_queue_id = 1;
  CREATE UNIQUE INDEX idx_rcv_queue_id ON rcv_queues (conn_id, rcv_queue_id);
  
  ALTER TABLE rcv_queues ADD COLUMN rcv_primary INTEGER CHECK (rcv_primary NOT NULL);
  UPDATE rcv_queues SET rcv_primary = 1;
  
  ALTER TABLE rcv_queues ADD COLUMN replace_rcv_queue_id INTEGER NULL;
  
  -- snd_queues
  ALTER TABLE snd_queues ADD COLUMN snd_queue_id INTEGER CHECK (snd_queue_id NOT NULL);
  UPDATE snd_queues SET snd_queue_id = 1;
  CREATE UNIQUE INDEX idx_snd_queue_id ON snd_queues (conn_id, snd_queue_id);
  
  ALTER TABLE snd_queues ADD COLUMN snd_primary INTEGER CHECK (snd_primary NOT NULL);
  UPDATE snd_queues SET snd_primary = 1;
  
  ALTER TABLE snd_queues ADD COLUMN replace_snd_queue_id INTEGER NULL;
  
  -- connections
  ALTER TABLE connections ADD COLUMN deleted INTEGER DEFAULT 0 CHECK (deleted NOT NULL);
  UPDATE connections SET deleted = 0;
  
  -- messages
  CREATE TABLE snd_message_deliveries (
    snd_message_delivery_id INTEGER PRIMARY KEY AUTOINCREMENT,
    conn_id BLOB NOT NULL REFERENCES connections ON DELETE CASCADE,
    snd_queue_id INTEGER NOT NULL,
    internal_id INTEGER NOT NULL,
    FOREIGN KEY (conn_id, internal_id) REFERENCES messages ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
  );
  
  CREATE INDEX idx_snd_message_deliveries ON snd_message_deliveries (conn_id, snd_queue_id);
  
  ALTER TABLE rcv_messages ADD COLUMN rcv_queue_id INTEGER CHECK (rcv_queue_id NOT NULL);
  UPDATE rcv_messages SET rcv_queue_id = 1;
  
  PRAGMA ignore_check_constraints=OFF;
  |]
  