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);
    `,
  },
];
