content: {-# LANGUAGE QuasiQuotes #-}
  
  module Simplex.Messaging.Agent.Store.SQLite.Migrations.M20230401_snd_files where
  
  import Database.SQLite.Simple (Query)
  import Database.SQLite.Simple.QQ (sql)
  
  m20230401_snd_files :: Query
  m20230401_snd_files =
    [sql|
  CREATE TABLE snd_files (
    snd_file_id INTEGER PRIMARY KEY,
    snd_file_entity_id BLOB NOT NULL,
    user_id INTEGER NOT NULL REFERENCES users ON DELETE CASCADE,
    num_recipients INTEGER NOT NULL,
    digest BLOB,
    key BLOB NOT NUll,
    nonce BLOB NOT NUll,
    path TEXT NOT NULL,
    prefix_path TEXT,
    status TEXT NOT NULL,
    deleted INTEGER NOT NULL DEFAULT 0,
    error TEXT,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now'))
  );
  
  CREATE INDEX idx_snd_files_user_id ON snd_files(user_id);
  
  CREATE TABLE snd_file_chunks (
    snd_file_chunk_id INTEGER PRIMARY KEY,
    snd_file_id INTEGER NOT NULL REFERENCES snd_files ON DELETE CASCADE,
    chunk_no INTEGER NOT NULL,
    chunk_offset INTEGER NOT NULL,
    chunk_size INTEGER NOT NULL,
    digest BLOB NOT NULL,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now'))
  );
  
  CREATE INDEX idx_snd_file_chunks_snd_file_id ON snd_file_chunks(snd_file_id);
  
  CREATE TABLE snd_file_chunk_replicas (
    snd_file_chunk_replica_id INTEGER PRIMARY KEY,
    snd_file_chunk_id INTEGER NOT NULL REFERENCES snd_file_chunks ON DELETE CASCADE,
    replica_number INTEGER NOT NULL,
    xftp_server_id INTEGER NOT NULL REFERENCES xftp_servers ON DELETE CASCADE,
    replica_id BLOB NOT NULL,
    replica_key BLOB NOT NULL,
    replica_status TEXT NOT NULL,
    delay INTEGER,
    retries INTEGER NOT NULL DEFAULT 0,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now'))
  );
  
  CREATE INDEX idx_snd_file_chunk_replicas_snd_file_chunk_id ON snd_file_chunk_replicas(snd_file_chunk_id);
  CREATE INDEX idx_snd_file_chunk_replicas_xftp_server_id ON snd_file_chunk_replicas(xftp_server_id);
  
  CREATE TABLE snd_file_chunk_replica_recipients (
    snd_file_chunk_replica_recipient_id INTEGER PRIMARY KEY,
    snd_file_chunk_replica_id INTEGER NOT NULL REFERENCES snd_file_chunk_replicas ON DELETE CASCADE,
    rcv_replica_id BLOB NOT NULL,
    rcv_replica_key BLOB NOT NULL,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now'))
  );
  
  CREATE INDEX idx_snd_file_chunk_replica_recipients_snd_file_chunk_replica_id ON snd_file_chunk_replica_recipients(snd_file_chunk_replica_id);
  
  CREATE TABLE deleted_snd_chunk_replicas (
    deleted_snd_chunk_replica_id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users ON DELETE CASCADE,
    xftp_server_id INTEGER NOT NULL REFERENCES xftp_servers ON DELETE CASCADE,
    replica_id BLOB NOT NULL,
    replica_key BLOB NOT NULL,
    chunk_digest BLOB NOT NULL,
    delay INTEGER,
    retries INTEGER NOT NULL DEFAULT 0,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now'))
  );
  
  CREATE INDEX idx_deleted_snd_chunk_replicas_user_id ON deleted_snd_chunk_replicas(user_id);
  CREATE INDEX idx_deleted_snd_chunk_replicas_xftp_server_id ON deleted_snd_chunk_replicas(xftp_server_id);
  |]
  
  down_m20230401_snd_files :: Query
  down_m20230401_snd_files =
    [sql|
  DROP INDEX idx_deleted_snd_chunk_replicas_xftp_server_id;
  DROP INDEX idx_deleted_snd_chunk_replicas_user_id;
  DROP TABLE deleted_snd_chunk_replicas;
  
  DROP INDEX idx_snd_file_chunk_replica_recipients_snd_file_chunk_replica_id;
  DROP TABLE snd_file_chunk_replica_recipients;
  
  DROP INDEX idx_snd_file_chunk_replicas_snd_file_chunk_id;
  DROP INDEX idx_snd_file_chunk_replicas_xftp_server_id;
  DROP TABLE snd_file_chunk_replicas;
  
  DROP INDEX idx_snd_file_chunks_snd_file_id;
  DROP TABLE snd_file_chunks;
  
  DROP INDEX idx_snd_files_user_id;
  DROP TABLE snd_files;
  |]
  