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

export const albumMigrations: readonly Migration[] = [
  {
    id: '2026-04-18_album_initial',
    sql: `
      CREATE TABLE IF NOT EXISTS macsync.albums (
        id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        device_id     UUID NOT NULL REFERENCES macsync.devices(id) ON DELETE CASCADE,
        external_id   TEXT NOT NULL,
        name          TEXT NOT NULL DEFAULT '',
        photo_count   INTEGER NOT NULL DEFAULT 0,
        synced_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
        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_albums_device ON macsync.albums(device_id);
    `,
  },
];

/**
 * Phase 1 of @imajin port: extends albums with type/date-range/cover/order.
 *
 * Exported separately because `cover_photo_id` is a FK to macsync.photos,
 * which must exist first. server.ts applies this array AFTER photoMigrations.
 */
export const albumImajinPortMigrations: readonly Migration[] = [
  {
    id: '2026-05-17_album_imajin_parity',
    sql: `
      ALTER TABLE macsync.albums
        ADD COLUMN IF NOT EXISTS album_type    TEXT NOT NULL DEFAULT 'user',
        ADD COLUMN IF NOT EXISTS start_date    TIMESTAMPTZ,
        ADD COLUMN IF NOT EXISTS end_date      TIMESTAMPTZ,
        ADD COLUMN IF NOT EXISTS cover_photo_id UUID REFERENCES macsync.photos(id) ON DELETE SET NULL,
        ADD COLUMN IF NOT EXISTS sort_order    INTEGER NOT NULL DEFAULT 0;

      CREATE INDEX IF NOT EXISTS idx_albums_device_sort
        ON macsync.albums(device_id, sort_order, name);
    `,
  },
];
