import type { Pool } from 'pg';

import { pgAll, pgRun } from '@/shared/db';

import type { Face, FaceBox, FaceDraft, FaceMatchStatus } from './types';

interface Row {
  id: string;
  photo_id: string;
  identity_id: string | null;
  bbox: FaceBox;
  embedding: readonly number[];
  confidence: number;
  content_hash: string | null;
  match_status: FaceMatchStatus;
  created_at: string;
}

const hydrate = (r: Row): Face => ({
  id: r.id,
  photoId: r.photo_id,
  identityId: r.identity_id,
  bbox: r.bbox,
  embedding: r.embedding,
  confidence: r.confidence,
  contentHash: r.content_hash,
  matchStatus: r.match_status,
  createdAt: r.created_at,
});

export async function insertFace(pool: Pool, id: string, draft: FaceDraft): Promise<void> {
  try {
    await pgRun(
      pool,
      `INSERT INTO macsync.faces (id, photo_id, bbox, embedding, confidence, content_hash, match_status)
       VALUES ($1, $2, $3::jsonb, $4, $5, $6, 'pending')`,
      [
        id,
        draft.photoId,
        JSON.stringify(draft.bbox),
        draft.embedding,
        draft.confidence ?? 0,
        draft.contentHash ?? null,
      ],
    );
  } catch (err) {
    throw new Error(`insertFace failed (photo=${draft.photoId}): ${err instanceof Error ? err.message : String(err)}`);
  }
}

export async function deleteFacesForPhoto(pool: Pool, photoId: string): Promise<void> {
  try {
    await pgRun(pool, 'DELETE FROM macsync.faces WHERE photo_id = $1', [photoId]);
  } catch (err) {
    throw new Error(`deleteFacesForPhoto failed (photo=${photoId}): ${err instanceof Error ? err.message : String(err)}`);
  }
}

export async function listFacesForPhoto(pool: Pool, photoId: string): Promise<readonly Face[]> {
  try {
    const rows = await pgAll<Row>(pool, 'SELECT * FROM macsync.faces WHERE photo_id = $1 ORDER BY created_at', [photoId]);
    return rows.map(hydrate);
  } catch (err) {
    throw new Error(`listFacesForPhoto failed (photo=${photoId}): ${err instanceof Error ? err.message : String(err)}`);
  }
}

export async function listPendingFaces(pool: Pool, deviceId: string, limit = 500): Promise<readonly Face[]> {
  try {
    const rows = await pgAll<Row>(
      pool,
      `SELECT f.* FROM macsync.faces f
         JOIN macsync.photos p ON p.id = f.photo_id
         WHERE p.device_id = $1 AND f.match_status = 'pending'
         ORDER BY f.created_at ASC
         LIMIT $2`,
      [deviceId, limit],
    );
    return rows.map(hydrate);
  } catch (err) {
    throw new Error(`listPendingFaces failed (device=${deviceId}): ${err instanceof Error ? err.message : String(err)}`);
  }
}

export async function assignFaceToIdentity(
  pool: Pool,
  faceId: string,
  identityId: string,
): Promise<void> {
  try {
    await pgRun(
      pool,
      `UPDATE macsync.faces SET identity_id = $1, match_status = 'matched' WHERE id = $2`,
      [identityId, faceId],
    );
  } catch (err) {
    throw new Error(`assignFaceToIdentity failed (face=${faceId}): ${err instanceof Error ? err.message : String(err)}`);
  }
}

export async function markFaceNoMatch(pool: Pool, faceId: string): Promise<void> {
  try {
    await pgRun(pool, `UPDATE macsync.faces SET match_status = 'no_match' WHERE id = $1`, [faceId]);
  } catch (err) {
    throw new Error(`markFaceNoMatch failed (face=${faceId}): ${err instanceof Error ? err.message : String(err)}`);
  }
}
