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

export const photoAlbumMigrations: readonly Migration[] = [
  {
    // Many-to-many junction. iOS Photos allows a single asset to belong to
    // multiple albums (user, smart, system). photos.album_id remains as a
    // legacy "primary album" hint but the source of truth is this junction.
    id: '2026-05-17_photo_album_junction',
    sql: `
      CREATE TABLE IF NOT EXISTS macsync.photo_albums (
        photo_id UUID NOT NULL REFERENCES macsync.photos(id) ON DELETE CASCADE,
        album_id UUID NOT NULL REFERENCES macsync.albums(id) ON DELETE CASCADE,
        created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
        PRIMARY KEY (photo_id, album_id)
      );
      CREATE INDEX IF NOT EXISTS idx_photo_albums_album ON macsync.photo_albums(album_id);
    `,
  },
];
