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

export const photoMigrations: readonly Migration[] = [
  {
    id: '2026-04-18_photo_initial',
    sql: `
      CREATE TABLE IF NOT EXISTS macsync.photos (
        id             UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        device_id      UUID NOT NULL REFERENCES macsync.devices(id) ON DELETE CASCADE,
        album_id       UUID REFERENCES macsync.albums(id) ON DELETE SET NULL,
        external_id    TEXT NOT NULL,
        filename       TEXT NOT NULL DEFAULT '',
        media_type     TEXT NOT NULL DEFAULT 'image/jpeg',
        width          INTEGER NOT NULL DEFAULT 0,
        height         INTEGER NOT NULL DEFAULT 0,
        duration       REAL,
        taken_at       TIMESTAMPTZ,
        storage_bucket TEXT,
        storage_key    TEXT,
        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_photos_device ON macsync.photos(device_id);
      CREATE INDEX IF NOT EXISTS idx_photos_album ON macsync.photos(album_id);
      CREATE INDEX IF NOT EXISTS idx_photos_taken ON macsync.photos(taken_at DESC);
    `,
  },
  {
    // Phase 1 of the @imajin → @mac-sync port: bring photos table up to feature
    // parity with imajin-media-gallery. media_type already stores the MIME
    // string ('image/jpeg'); media_kind is the new enum-like classifier
    // (image/video/live_photo/audio). content_hash is intentionally non-unique
    // here — the client may compute it lazily and upsert ON CONFLICT.
    id: '2026-05-17_photo_imajin_parity',
    sql: `
      ALTER TABLE macsync.photos
        ADD COLUMN IF NOT EXISTS media_kind             TEXT,
        ADD COLUMN IF NOT EXISTS mime_type              TEXT,
        ADD COLUMN IF NOT EXISTS modified_at            TIMESTAMPTZ,
        ADD COLUMN IF NOT EXISTS imported_at            TIMESTAMPTZ NOT NULL DEFAULT now(),
        ADD COLUMN IF NOT EXISTS thumbnail_key          TEXT,
        ADD COLUMN IF NOT EXISTS preview_key            TEXT,
        ADD COLUMN IF NOT EXISTS latitude               DOUBLE PRECISION,
        ADD COLUMN IF NOT EXISTS longitude              DOUBLE PRECISION,
        ADD COLUMN IF NOT EXISTS location_name          TEXT,
        ADD COLUMN IF NOT EXISTS is_favorite            BOOLEAN NOT NULL DEFAULT false,
        ADD COLUMN IF NOT EXISTS is_hidden              BOOLEAN NOT NULL DEFAULT false,
        ADD COLUMN IF NOT EXISTS is_screenshot          BOOLEAN NOT NULL DEFAULT false,
        ADD COLUMN IF NOT EXISTS is_selfie              BOOLEAN NOT NULL DEFAULT false,
        ADD COLUMN IF NOT EXISTS is_burst               BOOLEAN NOT NULL DEFAULT false,
        ADD COLUMN IF NOT EXISTS burst_identifier       TEXT,
        ADD COLUMN IF NOT EXISTS exif                   JSONB,
        ADD COLUMN IF NOT EXISTS content_hash           TEXT,
        ADD COLUMN IF NOT EXISTS processing_status      TEXT NOT NULL DEFAULT 'pending',
        ADD COLUMN IF NOT EXISTS processing_error       TEXT,
        ADD COLUMN IF NOT EXISTS category               TEXT,
        ADD COLUMN IF NOT EXISTS semantic_tags          JSONB,
        ADD COLUMN IF NOT EXISTS classification_status  TEXT NOT NULL DEFAULT 'pending',
        ADD COLUMN IF NOT EXISTS face_extraction_status TEXT NOT NULL DEFAULT 'pending',
        ADD COLUMN IF NOT EXISTS face_count             INTEGER NOT NULL DEFAULT 0;

      CREATE INDEX IF NOT EXISTS idx_photos_device_taken
        ON macsync.photos(device_id, taken_at DESC);
      CREATE INDEX IF NOT EXISTS idx_photos_processing_pending
        ON macsync.photos(processing_status)
        WHERE processing_status <> 'completed';
      CREATE INDEX IF NOT EXISTS idx_photos_face_pending
        ON macsync.photos(face_extraction_status)
        WHERE face_extraction_status = 'pending';
      CREATE INDEX IF NOT EXISTS idx_photos_content_hash
        ON macsync.photos(content_hash)
        WHERE content_hash IS NOT NULL;
      CREATE INDEX IF NOT EXISTS idx_photos_favorite
        ON macsync.photos(device_id)
        WHERE is_favorite;
    `,
  },
];
