import type { Migration } from '@/shared/db';

export const sendQueueMigrations: readonly Migration[] = [
  {
    id: '2026-04-22_send_queue_initial',
    sql: `
      CREATE TABLE IF NOT EXISTS macsync.send_queue (
        id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        device_id         UUID NOT NULL REFERENCES macsync.devices(id) ON DELETE CASCADE,
        to_handle         TEXT NOT NULL,
        body              TEXT NOT NULL,
        status            TEXT NOT NULL DEFAULT 'queued',
        send_queue_id     TEXT,
        sent_at           TIMESTAMPTZ,
        failure_reason    TEXT,
        created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
        updated_at        TIMESTAMPTZ NOT NULL DEFAULT now()
      );
      CREATE INDEX IF NOT EXISTS idx_sendq_device_status ON macsync.send_queue(device_id, status);
      CREATE INDEX IF NOT EXISTS idx_sendq_created ON macsync.send_queue(created_at DESC);
      CREATE INDEX IF NOT EXISTS idx_sendq_status ON macsync.send_queue(status);
    `,
  },
  {
    // Global outbound send-rate cap. The pending endpoint releases at most
    // (max_sends − sent-in-window) queued messages per poll, so a burst queues
    // and drips out at the configured rate. Single-row table (id BOOLEAN PK).
    id: '2026-06-23_send_rate_config',
    sql: `
      CREATE TABLE IF NOT EXISTS macsync.send_rate_config (
        id             BOOLEAN PRIMARY KEY DEFAULT true CHECK (id),
        max_sends      INTEGER NOT NULL DEFAULT 10,
        window_seconds INTEGER NOT NULL DEFAULT 300,
        updated_at     TIMESTAMPTZ NOT NULL DEFAULT now()
      );
      INSERT INTO macsync.send_rate_config (id) VALUES (true) ON CONFLICT (id) DO NOTHING;
    `,
  },
  {
    // auto_queue: when true (default), over-cap sends are held in the queue and
    // released as the window rolls (burst-friendly drip). When false, the cap is
    // not enforced and queued sends release immediately.
    id: '2026-06-23_send_rate_autoqueue',
    sql: `
      ALTER TABLE macsync.send_rate_config
        ADD COLUMN IF NOT EXISTS auto_queue BOOLEAN NOT NULL DEFAULT true;
    `,
  },
  {
    // Provenance: who authored the message text vs who triggered the send.
    // Fixed vocabulary (validated at the API boundary); nullable for legacy rows.
    id: '2026-06-23_send_queue_provenance',
    sql: `
      ALTER TABLE macsync.send_queue
        ADD COLUMN IF NOT EXISTS authored_by   TEXT,
        ADD COLUMN IF NOT EXISTS dispatched_by TEXT;
    `,
  },
];
