import type { Pool } from 'pg';

import { pgAll, pgGet, pgRun } from '@/shared/db';
import { notFound } from '@/shared/http/errors';

import type { Identity } from './types';

interface Row {
  id: string;
  device_id: string;
  name: string | null;
  face_count: number;
  centroid_embedding: readonly number[] | null;
  centroid_updated_at: string | null;
  created_at: string;
  updated_at: string;
}

const hydrate = (r: Row): Identity => ({
  id: r.id,
  deviceId: r.device_id,
  name: r.name,
  faceCount: r.face_count,
  centroidEmbedding: r.centroid_embedding,
  centroidUpdatedAt: r.centroid_updated_at,
  createdAt: r.created_at,
  updatedAt: r.updated_at,
});

export async function createIdentity(
  pool: Pool,
  id: string,
  deviceId: string,
  name?: string | null,
): Promise<Identity> {
  try {
    await pgRun(
      pool,
      `INSERT INTO macsync.identities (id, device_id, name, face_count) VALUES ($1, $2, $3, 0)`,
      [id, deviceId, name ?? null],
    );
    return getIdentity(pool, id);
  } catch (err) {
    throw new Error(`createIdentity failed: ${err instanceof Error ? err.message : String(err)}`);
  }
}

export async function getIdentity(pool: Pool, id: string): Promise<Identity> {
  try {
    const row = await pgGet<Row>(pool, 'SELECT * FROM macsync.identities WHERE id = $1', [id]);
    if (!row) throw notFound('identity_not_found', `identity ${id} not found`);
    return hydrate(row);
  } catch (err) {
    if (err instanceof Error && err.message.includes('identity_not_found')) throw err;
    throw new Error(`getIdentity failed (id=${id}): ${err instanceof Error ? err.message : String(err)}`);
  }
}

export async function listIdentitiesByDevice(pool: Pool, deviceId: string): Promise<readonly Identity[]> {
  try {
    const rows = await pgAll<Row>(
      pool,
      `SELECT * FROM macsync.identities WHERE device_id = $1 ORDER BY face_count DESC, name ASC NULLS LAST`,
      [deviceId],
    );
    return rows.map(hydrate);
  } catch (err) {
    throw new Error(`listIdentitiesByDevice failed: ${err instanceof Error ? err.message : String(err)}`);
  }
}

export async function listIdentitiesWithCentroid(pool: Pool, deviceId: string): Promise<readonly Identity[]> {
  try {
    const rows = await pgAll<Row>(
      pool,
      `SELECT * FROM macsync.identities WHERE device_id = $1 AND centroid_embedding IS NOT NULL`,
      [deviceId],
    );
    return rows.map(hydrate);
  } catch (err) {
    throw new Error(`listIdentitiesWithCentroid failed: ${err instanceof Error ? err.message : String(err)}`);
  }
}

export async function renameIdentity(pool: Pool, id: string, name: string | null): Promise<void> {
  try {
    await pgRun(pool, `UPDATE macsync.identities SET name = $1, updated_at = now() WHERE id = $2`, [name, id]);
  } catch (err) {
    throw new Error(`renameIdentity failed (id=${id}): ${err instanceof Error ? err.message : String(err)}`);
  }
}

export async function mergeIdentities(pool: Pool, targetId: string, sourceId: string): Promise<void> {
  try {
    await pgRun(pool, 'BEGIN');
    await pgRun(pool, 'UPDATE macsync.faces SET identity_id = $1 WHERE identity_id = $2', [targetId, sourceId]);
    await pgRun(pool, 'DELETE FROM macsync.identities WHERE id = $1', [sourceId]);
    await pgRun(pool, 'COMMIT');
  } catch (err) {
    try {
      await pgRun(pool, 'ROLLBACK');
    } catch {
      // ignore rollback error; surface the original
    }
    throw new Error(`mergeIdentities failed (target=${targetId} source=${sourceId}): ${err instanceof Error ? err.message : String(err)}`);
  }
}

export async function setCentroid(
  pool: Pool,
  id: string,
  centroid: readonly number[],
  faceCount: number,
): Promise<void> {
  try {
    await pgRun(
      pool,
      `UPDATE macsync.identities
         SET centroid_embedding = $1,
             centroid_updated_at = now(),
             face_count = $2,
             updated_at = now()
         WHERE id = $3`,
      [centroid, faceCount, id],
    );
  } catch (err) {
    throw new Error(`setCentroid failed (id=${id}): ${err instanceof Error ? err.message : String(err)}`);
  }
}

export async function listPhotoIdsForIdentity(
  pool: Pool,
  identityId: string,
  limit = 200,
): Promise<readonly string[]> {
  try {
    const rows = await pgAll<{ photo_id: string }>(
      pool,
      `SELECT DISTINCT photo_id FROM macsync.faces WHERE identity_id = $1 ORDER BY photo_id LIMIT $2`,
      [identityId, limit],
    );
    return rows.map((r) => r.photo_id);
  } catch (err) {
    throw new Error(`listPhotoIdsForIdentity failed (id=${identityId}): ${err instanceof Error ? err.message : String(err)}`);
  }
}
