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

export const faceMigrations: readonly Migration[] = [
  {
    // One row per detected face in a photo. identity_id is NULL until the
    // match worker either assigns to an existing identity or creates a new
    // one. content_hash is a sha256 of the face crop bytes, used to skip
    // duplicate work when the same face is detected twice (e.g. burst).
    id: '2026-05-17_face_initial',
    sql: `
      CREATE TABLE IF NOT EXISTS macsync.faces (
        id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        photo_id      UUID NOT NULL REFERENCES macsync.photos(id) ON DELETE CASCADE,
        identity_id   UUID REFERENCES macsync.identities(id) ON DELETE SET NULL,
        bbox          JSONB NOT NULL,
        embedding     DOUBLE PRECISION[] NOT NULL,
        confidence    REAL NOT NULL DEFAULT 0,
        content_hash  TEXT,
        match_status  TEXT NOT NULL DEFAULT 'pending',
        created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
      );
      CREATE INDEX IF NOT EXISTS idx_faces_photo ON macsync.faces(photo_id);
      CREATE INDEX IF NOT EXISTS idx_faces_identity
        ON macsync.faces(identity_id)
        WHERE identity_id IS NOT NULL;
      CREATE INDEX IF NOT EXISTS idx_faces_match_pending
        ON macsync.faces(match_status)
        WHERE match_status = 'pending';
      CREATE INDEX IF NOT EXISTS idx_faces_content_hash
        ON macsync.faces(content_hash)
        WHERE content_hash IS NOT NULL;
    `,
  },
];
