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

export const attachmentMigrations: readonly Migration[] = [
  {
    id: '2026-04-23_attachment_initial',
    sql: `
      CREATE TABLE IF NOT EXISTS macsync.attachments (
        id             UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        message_id     UUID NOT NULL REFERENCES macsync.messages(id) ON DELETE CASCADE,
        device_id      UUID NOT NULL REFERENCES macsync.devices(id) ON DELETE CASCADE,
        external_id    TEXT NOT NULL,
        filename       TEXT,
        transfer_name  TEXT,
        mime_type      TEXT,
        size           BIGINT,
        storage_kind   TEXT NOT NULL DEFAULT 'inline_base64'
                       CHECK (storage_kind IN ('inline_base64', 'minio', 'local', 'missing')),
        data           BYTEA,
        storage_bucket TEXT,
        storage_key    TEXT,
        created_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
        updated_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
        UNIQUE(device_id, external_id)
      );
      CREATE INDEX IF NOT EXISTS idx_attachments_message
        ON macsync.attachments(message_id);
      CREATE INDEX IF NOT EXISTS idx_attachments_mime
        ON macsync.attachments(mime_type)
        WHERE mime_type IS NOT NULL;
    `,
  },
];
