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

export const identityMigrations: readonly Migration[] = [
  {
    // A person cluster derived from face embeddings. Multiple faces (see
    // entities/face) reference an identity once the match worker assigns
    // them; the centroid is the running mean of the assigned embeddings and
    // is what new faces are compared against.
    id: '2026-05-17_identity_initial',
    sql: `
      CREATE TABLE IF NOT EXISTS macsync.identities (
        id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        device_id             UUID NOT NULL REFERENCES macsync.devices(id) ON DELETE CASCADE,
        name                  TEXT,
        face_count            INTEGER NOT NULL DEFAULT 0,
        centroid_embedding    DOUBLE PRECISION[],
        centroid_updated_at   TIMESTAMPTZ,
        created_at            TIMESTAMPTZ NOT NULL DEFAULT now(),
        updated_at            TIMESTAMPTZ NOT NULL DEFAULT now()
      );
      CREATE INDEX IF NOT EXISTS idx_identities_device ON macsync.identities(device_id);
      CREATE INDEX IF NOT EXISTS idx_identities_named
        ON macsync.identities(device_id)
        WHERE name IS NOT NULL;
    `,
  },
];
