const express = require('express');
const { randomUUID } = require('crypto');
const sharp = require('sharp');

const db = require('../db/knex');

const {
  resolveFilePathForEntryId,
} = require('../utils/resolveFilePath');

const {
  createHttpError,
  getAuthenticatedUser,
  isFaceReviewAdmin,
  resolveFaceReviewUser,
} = require('../middleware/faceReviewUser');

const router = express.Router();

const DEFAULT_THUMBNAIL_SIZE = 192;
const MIN_THUMBNAIL_SIZE = 64;
const MAX_THUMBNAIL_SIZE = 512;
const FACE_MARGIN_FACTOR = 0.25;


function parseThumbnailSize(value) {
  if (value == null || value === '') {
    return DEFAULT_THUMBNAIL_SIZE;
  }

  const size = Number(value);

  if (
    !Number.isInteger(size)
    || size < MIN_THUMBNAIL_SIZE
    || size > MAX_THUMBNAIL_SIZE
  ) {
    throw createHttpError(
      400,
      `size deve essere compreso tra `
      + `${MIN_THUMBNAIL_SIZE} e `
      + `${MAX_THUMBNAIL_SIZE}`,
    );
  }

  return size;
}


function normalizeRotation(value) {
  let rotation = Number(value || 0);

  if (!Number.isInteger(rotation)) {
    throw createHttpError(
      500,
      `Rotazione foto non valida: ${value}`,
    );
  }

  rotation %= 360;

  if (rotation < 0) {
    rotation += 360;
  }

  if (
    rotation !== 0
    && rotation !== 90
    && rotation !== 180
    && rotation !== 270
  ) {
    throw createHttpError(
      500,
      `Rotazione foto non supportata: ${value}`,
    );
  }

  return rotation;
}


function calculateSquareCrop(
  face,
  imageWidth,
  imageHeight,
) {
  const faceX = Number(face.x);
  const faceY = Number(face.y);
  const faceWidth = Number(face.width);
  const faceHeight = Number(face.height);

  if (
    !Number.isFinite(faceX)
    || !Number.isFinite(faceY)
    || !Number.isFinite(faceWidth)
    || !Number.isFinite(faceHeight)
    || faceWidth <= 0
    || faceHeight <= 0
  ) {
    throw createHttpError(
      500,
      'Bounding box del volto non valida',
    );
  }

  const centreX =
    faceX + faceWidth / 2;

  const centreY =
    faceY + faceHeight / 2;

  const largestSide = Math.max(
    faceWidth,
    faceHeight,
  );

  let cropSize = Math.ceil(
    largestSide
    * (1 + FACE_MARGIN_FACTOR * 2),
  );

  cropSize = Math.max(
    1,
    Math.min(
      cropSize,
      imageWidth,
      imageHeight,
    ),
  );

  let left = Math.round(
    centreX - cropSize / 2,
  );

  let top = Math.round(
    centreY - cropSize / 2,
  );

  left = Math.max(
    0,
    Math.min(
      left,
      imageWidth - cropSize,
    ),
  );

  top = Math.max(
    0,
    Math.min(
      top,
      imageHeight - cropSize,
    ),
  );

  return {
    left,
    top,
    width: cropSize,
    height: cropSize,
  };
}


async function ensureTargetUserExists(user) {
  const run = await db('face_cluster_runs')
    .where({ user })
    .first('id');

  if (run) {
    return;
  }

  const photo = await db('photos')
    .where({ user })
    .first('id');

  if (!photo) {
    throw createHttpError(
      404,
      `Utente non trovato: ${user}`,
    );
  }
}


async function getLatestCompletedRun(user) {
  return db('face_cluster_runs')
    .where({
      user,
      status: 'done',
    })
    .orderBy('completed_at', 'desc')
    .orderBy('created_at', 'desc')
    .first();
}


function buildFaceResponse(
  face,
  targetUser,
) {
  const faceId = encodeURIComponent(
    face.face_id,
  );

  return {
    id: face.face_id,
    photo_id: face.photo_id,
    photo_name: face.photo_name,
    photo_path: face.photo_path,
    face_index: Number(face.face_index),
    detector_score:
      Number(face.detector_score),
    probability:
      face.probability == null
        ? null
        : Number(face.probability),
    review_status:
      face.review_status ?? null,
    person_id:
      face.person_id ?? null,
    rotation:
      Number(face.rotation || 0),
    thumbnail_url:
      `/face-review/faces/${faceId}`
      + '/thumbnail',
  };
}


async function loadClusterFaces(
  runId,
  clusterId,
  targetUser,
) {
  const rows = await db(
    'face_cluster_memberships as m',
  )
    .join(
      'faces as f',
      'f.id',
      'm.face_id',
    )
    .join(
      'photos as p',
      'p.id',
      'f.photo_id',
    )
    .join(
      'face_cluster_runs as r',
      'r.id',
      'm.run_id',
    )
    .where({
      'm.run_id': runId,
      'm.cluster_id': clusterId,
      'r.user': targetUser,
      'p.user': targetUser,
    })
    .select(
      'f.id as face_id',
      'f.photo_id',
      'f.face_index',
      'f.score as detector_score',
      'f.person_id',
      'p.name as photo_name',
      'p.path as photo_path',
      'p.rotation',
      'm.probability',
      'm.review_status',
    )
    .orderBy('m.probability', 'desc')
    .orderBy('p.name', 'asc')
    .orderBy('f.face_index', 'asc');

  return rows.map(
    (row) => buildFaceResponse(
      row,
      targetUser,
    ),
  );
}



function normalizePersonName(value) {
  const name = String(
    value || '',
  )
    .replace(/\s+/g, ' ')
    .trim();

  if (!name) {
    throw createHttpError(
      400,
      'Nome della persona mancante',
    );
  }

  if (name.length > 120) {
    throw createHttpError(
      400,
      'Nome della persona troppo lungo',
    );
  }

  return name;
}


function normalizeFaceIds(value) {
  if (!Array.isArray(value)) {
    throw createHttpError(
      400,
      'included_face_ids deve essere un array',
    );
  }

  const result = [];
  const seen = new Set();

  for (const rawValue of value) {
    const faceId = String(
      rawValue || '',
    ).trim();

    if (!faceId || seen.has(faceId)) {
      continue;
    }

    seen.add(faceId);
    result.push(faceId);
  }

  if (!result.length) {
    throw createHttpError(
      400,
      'Seleziona almeno un volto',
    );
  }

  return result;
}


async function findPersonByName(
  connection,
  user,
  name,
) {
  return connection('persons')
    .where({ user })
    .whereRaw(
      'LOWER(name) = LOWER(?)',
      [name],
    )
    .first();
}


async function loadOwnedCluster(
  connection,
  clusterId,
  user,
) {
  return connection(
    'face_clusters as c',
  )
    .join(
      'face_cluster_runs as r',
      'r.id',
      'c.run_id',
    )
    .where({
      'c.id': clusterId,
      'r.user': user,
    })
    .first(
      'c.id',
      'c.run_id',
      'c.label',
      'c.size',
      'c.person_id',
    );
}


async function loadClusterMemberships(
  connection,
  clusterId,
  user,
) {
  return connection(
    'face_cluster_memberships as m',
  )
    .join(
      'faces as f',
      'f.id',
      'm.face_id',
    )
    .join(
      'photos as p',
      'p.id',
      'f.photo_id',
    )
    .join(
      'face_cluster_runs as r',
      'r.id',
      'm.run_id',
    )
    .where({
      'm.cluster_id': clusterId,
      'm.noise_flag': 0,
      'p.user': user,
      'r.user': user,
    })
    .select(
      'm.id as membership_id',
      'm.face_id',
      'm.review_status',
      'f.person_id',
    );
}



async function queuePrototypeRefresh(
  connection,
  {
    user,
    personId,
    reason,
    requestedBy,
  },
) {
  const normalizedReason = String(
    reason || 'manual_rebuild',
  ).trim();

  const normalizedRequestedBy = String(
    requestedBy || user || '',
  ).trim();

  const now =
    new Date().toISOString();

  /*
   * Se esiste già un job pending per la
   * persona, aggiorna quello invece di
   * crearne uno nuovo.
   */
  const existing = await connection(
    'face_prototype_jobs',
  )
    .where({
      user,
      person_id: personId,
      status: 'pending',
    })
    .orderBy(
      'requested_at',
      'desc',
    )
    .first();

  if (existing) {
    await connection(
      'face_prototype_jobs',
    )
      .where({
        id: existing.id,
      })
      .update({
        reason: normalizedReason,
        requested_by:
          normalizedRequestedBy || null,
        requested_at: now,
        last_error: null,
        updated_at: now,
      });

    return {
      created: false,
      job: {
        ...existing,
        reason: normalizedReason,
        requested_by:
          normalizedRequestedBy || null,
        requested_at: now,
        last_error: null,
        updated_at: now,
      },
    };
  }

  const job = {
    id: randomUUID(),
    user,
    person_id: personId,
    status: 'pending',
    reason: normalizedReason,
    requested_by:
      normalizedRequestedBy || null,
    requested_at: now,
    started_at: null,
    completed_at: null,
    attempts: 0,
    last_error: null,
    result_json: null,
    input_signature: null,
    created_at: now,
    updated_at: now,
  };

  await connection(
    'face_prototype_jobs',
  ).insert(job);

  return {
    created: true,
    job,
  };
}


router.get('/context', async (req, res) => {
  try {
    const authenticatedUser =
      getAuthenticatedUser(req);

    const targetUser =
      resolveFaceReviewUser(req);

    await ensureTargetUserExists(
      targetUser,
    );

    const latestRun =
      await getLatestCompletedRun(
        targetUser,
      );

    res.json({
      authenticated_user:
        authenticatedUser,
      target_user:
        targetUser,
      is_admin:
        isFaceReviewAdmin(req),
      can_select_user:
        isFaceReviewAdmin(req),
      latest_run_id:
        latestRun?.id ?? null,
    });
  } catch (error) {
    res.status(error.status || 500).json({
      ok: false,
      error: error.message,
    });
  }
});


router.get('/runs', async (req, res) => {
  try {
    const targetUser =
      resolveFaceReviewUser(req);

    await ensureTargetUserExists(
      targetUser,
    );

    const rows = await db(
      'face_cluster_runs',
    )
      .where({ user: targetUser })
      .orderBy('created_at', 'desc')
      .select(
        'id',
        'user',
        'status',
        'scope',
        'embedding_count',
        'min_cluster_size',
        'min_samples',
        'metric',
        'selection_method',
        'engine',
        'engine_version',
        'cluster_count',
        'clustered_faces',
        'noise_faces',
        'noise_percent',
        'started_at',
        'completed_at',
        'created_at',
      );

    res.json({
      user: targetUser,
      runs: rows,
    });
  } catch (error) {
    res.status(error.status || 500).json({
      ok: false,
      error: error.message,
    });
  }
});


router.get('/clusters', async (req, res) => {
  try {
    const targetUser =
      resolveFaceReviewUser(req);

    await ensureTargetUserExists(
      targetUser,
    );

    const requestedRunId = String(
      req.query.run_id || '',
    ).trim();

    const run = requestedRunId
      ? await db('face_cluster_runs')
          .where({
            id: requestedRunId,
            user: targetUser,
          })
          .first()
      : await getLatestCompletedRun(
          targetUser,
        );

    if (!run) {
      return res.json({
        user: targetUser,
        run: null,
        clusters: [],
      });
    }

    const clusterRows = await db(
      'face_clusters as c',
    )
      .leftJoin(
        'persons as person',
        'person.id',
        'c.person_id',
      )
      .where({
        'c.run_id': run.id,
      })
      .select(
        'c.id',
        'c.run_id',
        'c.label',
        'c.size',
        'c.min_samples',
        'c.min_cluster_size',
        'c.person_id',
        'person.name as person_name',
        'c.created_at',
        'c.updated_at',
      )
      .orderBy('c.label', 'asc');

    const clusters = [];

    for (const cluster of clusterRows) {
      const faces = await loadClusterFaces(
        run.id,
        cluster.id,
        targetUser,
      );

      clusters.push({
        ...cluster,
        label: Number(cluster.label),
        size: Number(cluster.size),
        faces,
      });
    }

    res.json({
      user: targetUser,
      run,
      clusters,
    });
  } catch (error) {
    console.error(
      '[faceReview] GET /clusters:',
      error,
    );

    res.status(error.status || 500).json({
      ok: false,
      error: error.message,
    });
  }
});


router.get(
  '/clusters/:clusterId',
  async (req, res) => {
    try {
      const targetUser =
        resolveFaceReviewUser(req);

      const clusterId = String(
        req.params.clusterId || '',
      ).trim();

      const cluster = await db(
        'face_clusters as c',
      )
        .join(
          'face_cluster_runs as r',
          'r.id',
          'c.run_id',
        )
        .leftJoin(
          'persons as person',
          'person.id',
          'c.person_id',
        )
        .where({
          'c.id': clusterId,
          'r.user': targetUser,
        })
        .first(
          'c.id',
          'c.run_id',
          'c.label',
          'c.size',
          'c.min_samples',
          'c.min_cluster_size',
          'c.person_id',
          'person.name as person_name',
          'c.created_at',
          'c.updated_at',
        );

      if (!cluster) {
        throw createHttpError(
          404,
          'Cluster non trovato',
        );
      }

      const faces = await loadClusterFaces(
        cluster.run_id,
        cluster.id,
        targetUser,
      );

      res.json({
        user: targetUser,
        cluster: {
          ...cluster,
          label: Number(cluster.label),
          size: Number(cluster.size),
          faces,
        },
      });
    } catch (error) {
      res.status(error.status || 500).json({
        ok: false,
        error: error.message,
      });
    }
  },
);


router.get(
  '/unclustered',
  async (req, res) => {
    try {
      const targetUser =
        resolveFaceReviewUser(req);

      const run =
        await getLatestCompletedRun(
          targetUser,
        );

      if (!run) {
        return res.json({
          user: targetUser,
          run: null,
          faces: [],
        });
      }

      const rows = await db(
        'face_cluster_memberships as m',
      )
        .join(
          'faces as f',
          'f.id',
          'm.face_id',
        )
        .join(
          'photos as p',
          'p.id',
          'f.photo_id',
        )
        .join(
          'face_cluster_runs as r',
          'r.id',
          'm.run_id',
        )
        .where({
          'm.run_id': run.id,
          'm.noise_flag': 1,
          'r.user': targetUser,
          'p.user': targetUser,
        })
        .whereNull('f.person_id')
        .select(
          'f.id as face_id',
          'f.photo_id',
          'f.face_index',
          'f.score as detector_score',
          'f.person_id',
          'p.name as photo_name',
          'p.path as photo_path',
          'p.rotation',
          'm.probability',
          'm.review_status',
        )
        .orderBy('p.name', 'asc')
        .orderBy('f.face_index', 'asc');

      res.json({
        user: targetUser,
        run,
        faces: rows.map(
          (row) => buildFaceResponse(
            row,
            targetUser,
          ),
        ),
      });
    } catch (error) {
      res.status(error.status || 500).json({
        ok: false,
        error: error.message,
      });
    }
  },
);


router.get('/persons', async (req, res) => {
  try {
    const targetUser =
      resolveFaceReviewUser(req);

    const persons = await db('persons')
      .where({ user: targetUser })
      .orderBy('name', 'asc')
      .select(
        'id',
        'user',
        'name',
        'created_at',
        'updated_at',
      );

    res.json({
      user: targetUser,
      persons,
    });
  } catch (error) {
    res.status(error.status || 500).json({
      ok: false,
      error: error.message,
    });
  }
});


router.get(
  '/faces/:faceId/thumbnail',
  async (req, res) => {
    try {
      const targetUser =
        resolveFaceReviewUser(req);

      const faceId = String(
        req.params.faceId || '',
      ).trim();

      const requestedSize =
        parseThumbnailSize(
          req.query.size,
        );

      const face = await db('faces as f')
        .join(
          'photos as p',
          'p.id',
          'f.photo_id',
        )
        .where({
          'f.id': faceId,
          'p.user': targetUser,
        })
        .whereNull('p.deleted_at')
        .first(
          'f.id',
          'f.photo_id',
          'f.x',
          'f.y',
          'f.width',
          'f.height',
          'p.rotation',
          'p.name',
        );

      if (!face) {
        throw createHttpError(
          404,
          'Volto non trovato',
        );
      }

      const filePath =
        await resolveFilePathForEntryId(
          face.photo_id,
        );

      if (!filePath) {
        throw createHttpError(
          404,
          'File originale non trovato',
        );
      }

      const rotation =
        normalizeRotation(
          face.rotation,
        );

      const sourceMetadata =
        await sharp(filePath)
          .metadata();

      const sourceWidth =
        Number(sourceMetadata.width);

      const sourceHeight =
        Number(sourceMetadata.height);

      if (
        !Number.isInteger(sourceWidth)
        || sourceWidth <= 0
        || !Number.isInteger(sourceHeight)
        || sourceHeight <= 0
      ) {
        throw createHttpError(
          500,
          'Dimensioni immagine non valide',
        );
      }

      const rotatedWidth =
        rotation === 90
        || rotation === 270
          ? sourceHeight
          : sourceWidth;

      const rotatedHeight =
        rotation === 90
        || rotation === 270
          ? sourceWidth
          : sourceHeight;

      const crop =
        calculateSquareCrop(
          face,
          rotatedWidth,
          rotatedHeight,
        );

      const buffer = await sharp(
        filePath,
        {
          failOn: 'none',
        },
      )
        .rotate(rotation)
        .extract(crop)
        .resize({
          width: requestedSize,
          height: requestedSize,
          fit: 'cover',
          position: 'centre',
          withoutEnlargement: false,
        })
        .webp({
          quality: 82,
          effort: 4,
        })
        .toBuffer();

      res.set({
        'Content-Type': 'image/webp',
        'Content-Length':
          String(buffer.length),
        'Cache-Control':
          'private, max-age=3600',
        'X-Content-Type-Options':
          'nosniff',
      });

      res.send(buffer);
    } catch (error) {
      console.error(
        '[faceReview] thumbnail error:',
        error,
      );

      res.status(error.status || 500).json({
        ok: false,
        error: error.message,
      });
    }
  },
);



router.post('/persons', async (req, res) => {
  try {
    const targetUser =
      resolveFaceReviewUser(req);

    const name =
      normalizePersonName(
        req.body?.name,
      );

    const result = await db.transaction(
      async (trx) => {
        const existing =
          await findPersonByName(
            trx,
            targetUser,
            name,
          );

        if (existing) {
          return {
            person: existing,
            created: false,
          };
        }

        const now =
          new Date().toISOString();

        const person = {
          id: randomUUID(),
          user: targetUser,
          name,
          created_at: now,
          updated_at: now,
        };

        await trx('persons')
          .insert(person);

        return {
          person,
          created: true,
        };
      },
    );

    res.status(
      result.created ? 201 : 200,
    ).json({
      ok: true,
      user: targetUser,
      created: result.created,
      person: result.person,
    });
  } catch (error) {
    console.error(
      '[faceReview] POST /persons:',
      error,
    );

    res.status(error.status || 500).json({
      ok: false,
      error: error.message,
    });
  }
});




router.post(
  '/clusters/:clusterId/assign-person',
  async (req, res) => {
    try {
      const authenticatedUser =
        getAuthenticatedUser(req);

      const targetUser =
        resolveFaceReviewUser(req);

      const clusterId = String(
        req.params.clusterId || '',
      ).trim();

      const personId = String(
        req.body?.person_id || '',
      ).trim();

      if (!clusterId) {
        throw createHttpError(
          400,
          'Cluster mancante',
        );
      }

      if (!personId) {
        throw createHttpError(
          400,
          'Persona mancante',
        );
      }

      const includedFaceIds =
        normalizeFaceIds(
          req.body?.included_face_ids,
        );

      const result = await db.transaction(
        async (trx) => {
          const person =
            await trx('persons')
              .where({
                id: personId,
                user: targetUser,
              })
              .first();

          if (!person) {
            throw createHttpError(
              404,
              'Persona non trovata',
            );
          }

          const cluster =
            await loadOwnedCluster(
              trx,
              clusterId,
              targetUser,
            );

          if (!cluster) {
            throw createHttpError(
              404,
              'Cluster non trovato',
            );
          }

          const memberships =
            await loadClusterMemberships(
              trx,
              clusterId,
              targetUser,
            );

          if (!memberships.length) {
            throw createHttpError(
              409,
              'Il cluster non contiene volti',
            );
          }

          const membershipFaceIds =
            new Set(
              memberships.map(
                (membership) =>
                  membership.face_id,
              ),
            );

          for (
            const faceId
            of includedFaceIds
          ) {
            if (
              !membershipFaceIds.has(faceId)
            ) {
              throw createHttpError(
                400,
                'Il volto non appartiene '
                + `al cluster: ${faceId}`,
              );
            }
          }

          const includedSet =
            new Set(includedFaceIds);

          const now =
            new Date().toISOString();

          const excludedFaceIds = [];

          for (
            const membership
            of memberships
          ) {
            const included =
              includedSet.has(
                membership.face_id,
              );

            await trx(
              'face_cluster_memberships',
            )
              .where({
                id:
                  membership.membership_id,
              })
              .update({
                review_status:
                  included
                    ? 'accepted'
                    : 'excluded',
                reviewed_by:
                  authenticatedUser,
                reviewed_at: now,
                updated_at: now,
              });

            if (included) {
              await trx('faces')
                .where({
                  id: membership.face_id,
                })
                .update({
                  person_id: personId,
                  updated_at: now,
                });

              await trx(
                'person_face_exclusions',
              )
                .where({
                  person_id: personId,
                  face_id:
                    membership.face_id,
                })
                .del();

              continue;
            }

            excludedFaceIds.push(
              membership.face_id,
            );

            await trx('faces')
              .where({
                id: membership.face_id,
              })
              .where((builder) => {
                builder
                  .whereNull('person_id')
                  .orWhere(
                    'person_id',
                    personId,
                  );
              })
              .update({
                person_id: null,
                updated_at: now,
              });

            await trx(
              'person_face_exclusions',
            )
              .insert({
                person_id: personId,
                face_id:
                  membership.face_id,
                user: targetUser,
                reason:
                  'Escluso manualmente '
                  + 'durante la revisione '
                  + 'del cluster',
                created_by:
                  authenticatedUser,
                created_at: now,
              })
              .onConflict([
                'person_id',
                'face_id',
              ])
              .merge({
                user: targetUser,
                reason:
                  'Escluso manualmente '
                  + 'durante la revisione '
                  + 'del cluster',
                created_by:
                  authenticatedUser,
                created_at: now,
              });
          }

          await trx('face_clusters')
            .where({
              id: clusterId,
            })
            .update({
              person_id: personId,
              updated_at: now,
            });

          await queuePrototypeRefresh(

            trx,

            {

              user: targetUser,

              personId,

              reason:

                'cluster_assigned',

              requestedBy:

                authenticatedUser,

            },

          );


          return {
            cluster_id: clusterId,
            cluster_label:
              Number(cluster.label),
            person: {
              id: person.id,
              user: person.user,
              name: person.name,
            },
            included_face_ids:
              includedFaceIds,
            excluded_face_ids:
              excludedFaceIds,
          };
        },
      );

      res.json({
        ok: true,
        user: targetUser,
        ...result,
      });
    } catch (error) {
      console.error(
        '[faceReview] assign person:',
        error,
      );

      res.status(error.status || 500).json({
        ok: false,
        error: error.message,
      });
    }
  },
);




/*
 * Dettaglio persona con tutti i volti assegnati,
 * prototype attivi e casi difficili.
 */
router.get(
  '/persons/:personId',
  async (req, res) => {
    try {
      const targetUser =
        resolveFaceReviewUser(req);

      const personId = String(
        req.params.personId || '',
      ).trim();

      const person = await db('persons')
        .where({
          id: personId,
          user: targetUser,
        })
        .first();

      if (!person) {
        throw createHttpError(
          404,
          'Persona non trovata',
        );
      }

      const rows = await db('faces as f')
        .join(
          'photos as p',
          'p.id',
          'f.photo_id',
        )
        .leftJoin(
          'face_clusters as c',
          'c.id',
          'f.cluster_id',
        )
        .leftJoin(
          'person_face_prototypes as prototype',
          function joinPrototype() {
            this.on(
              'prototype.face_id',
              '=',
              'f.id',
            );

            this.andOn(
              'prototype.person_id',
              '=',
              'f.person_id',
            );
          },
        )
        .leftJoin(
          'person_face_reference_exclusions as exclusion',
          function joinReferenceExclusion() {
            this.on(
              'exclusion.face_id',
              '=',
              'f.id',
            );

            this.andOn(
              'exclusion.person_id',
              '=',
              'f.person_id',
            );
          },
        )
        .where({
          'f.person_id': personId,
          'p.user': targetUser,
        })
        .select(
          'f.id as face_id',
          'f.photo_id',
          'f.face_index',
          'f.score as detector_score',
          'f.status',
          'p.name as photo_name',
          'p.path as photo_path',
          'p.rotation',
          'c.id as cluster_id',
          'c.label as cluster_label',

          'prototype.active as prototype_active',
          'prototype.source_type as prototype_source_type',
          'prototype.selection_method as prototype_selection_method',
          'prototype.selection_order as prototype_selection_order',
          'prototype.quality_score as prototype_quality_score',
          'prototype.experiment_name as prototype_experiment_name',

          'exclusion.reason_code as reference_exclusion_reason',
          'exclusion.note as reference_exclusion_note',
          'exclusion.created_by as reference_exclusion_created_by',
          'exclusion.created_at as reference_exclusion_created_at',
        )
        .orderByRaw(
          `
          CASE
            WHEN prototype.active = 1
            THEN 0
            ELSE 1
          END
          `,
        )
        .orderBy(
          'prototype.selection_order',
          'asc',
        )
        .orderBy(
          'c.label',
          'asc',
        )
        .orderBy(
          'p.name',
          'asc',
        )
        .orderBy(
          'f.face_index',
          'asc',
        );

      const clusterCount = await db(
        'face_clusters',
      )
        .where({
          person_id: personId,
        })
        .countDistinct({
          count: 'id',
        })
        .first();

      const indexState = await db(
        'face_recognition_index_state',
      )
        .where({
          user: targetUser,
        })
        .first(
          'user',
          'status',
          'dirty',
          'version',
          'prototype_count',
          'index_path',
          'metadata_path',
          'built_at',
          'last_error',
          'updated_at',
        );

      const faces = rows.map((row) => {
        const faceId = encodeURIComponent(
          row.face_id,
        );

        const isPrototype =
          Number(row.prototype_active || 0)
          === 1;

        const hasReferenceExclusion =
          Boolean(
            row.reference_exclusion_reason,
          );

        return {
          id: row.face_id,
          photo_id: row.photo_id,
          photo_name: row.photo_name,
          photo_path: row.photo_path,

          face_index:
            Number(row.face_index),

          detector_score:
            Number(row.detector_score),

          status: row.status,

          rotation:
            Number(row.rotation || 0),

          cluster_id:
            row.cluster_id || null,

          cluster_label:
            row.cluster_label == null
              ? null
              : Number(
                  row.cluster_label,
                ),

          source_type:
            row.cluster_id == null
              ? 'noise'
              : 'cluster',

          is_prototype:
            isPrototype,

          prototype:
            isPrototype
              ? {
                  active: true,

                  source_type:
                    row.prototype_source_type,

                  selection_method:
                    row.prototype_selection_method,

                  selection_order:
                    row.prototype_selection_order
                      == null
                      ? null
                      : Number(
                          row.prototype_selection_order,
                        ),

                  quality_score:
                    row.prototype_quality_score
                      == null
                      ? null
                      : Number(
                          row.prototype_quality_score,
                        ),

                  experiment_name:
                    row.prototype_experiment_name
                    || null,
                }
              : null,

          is_reference_excluded:
            hasReferenceExclusion,

          reference_exclusion:
            hasReferenceExclusion
              ? {
                  reason_code:
                    row.reference_exclusion_reason,

                  note:
                    row.reference_exclusion_note
                    || null,

                  created_by:
                    row.reference_exclusion_created_by
                    || null,

                  created_at:
                    row.reference_exclusion_created_at
                    || null,
                }
              : null,

          thumbnail_url:
            `/face-review/faces/${faceId}`
            + '/thumbnail',
        };
      });

      const prototypeCount =
        faces.filter(
          (face) =>
            face.is_prototype,
        ).length;

      const difficultFaceCount =
        faces.filter(
          (face) =>
            face.is_reference_excluded,
        ).length;

      const clusteredFaceCount =
        faces.filter(
          (face) =>
            face.cluster_id != null,
        ).length;

      const confirmedNoiseCount =
        faces.length
        - clusteredFaceCount;

      res.json({
        user: targetUser,

        person,

        summary: {
          face_count:
            faces.length,

          cluster_count:
            Number(
              clusterCount?.count || 0,
            ),

          prototype_count:
            prototypeCount,

          difficult_face_count:
            difficultFaceCount,

          clustered_face_count:
            clusteredFaceCount,

          confirmed_noise_count:
            confirmedNoiseCount,
        },

        index_state:
          indexState
            ? {
                status:
                  indexState.status,

                dirty:
                  Number(
                    indexState.dirty || 0,
                  ) === 1,

                version:
                  Number(
                    indexState.version || 0,
                  ),

                prototype_count:
                  Number(
                    indexState.prototype_count
                    || 0,
                  ),

                index_path:
                  indexState.index_path
                  || null,

                metadata_path:
                  indexState.metadata_path
                  || null,

                built_at:
                  indexState.built_at
                  || null,

                last_error:
                  indexState.last_error
                  || null,

                updated_at:
                  indexState.updated_at
                  || null,
              }
            : {
                status: 'missing',
                dirty: true,
                version: 0,
                prototype_count:
                  prototypeCount,
                index_path: null,
                metadata_path: null,
                built_at: null,
                last_error: null,
                updated_at: null,
              },

        faces,
      });
    } catch (error) {
      console.error(
        '[faceReview] person detail:',
        error,
      );

      res.status(error.status || 500).json({
        ok: false,
        error: error.message,
      });
    }
  },
);


/*
 * Rinomina una persona.
 *
 * Body:
 * {
 *   "name": "Nuovo nome"
 * }
 */
router.patch(
  '/persons/:personId',
  async (req, res) => {
    try {
      const targetUser =
        resolveFaceReviewUser(req);

      const personId = String(
        req.params.personId || '',
      ).trim();

      const name = normalizePersonName(
        req.body?.name,
      );

      const person = await db.transaction(
        async (trx) => {
          const existing = await trx(
            'persons',
          )
            .where({
              id: personId,
              user: targetUser,
            })
            .first();

          if (!existing) {
            throw createHttpError(
              404,
              'Persona non trovata',
            );
          }

          const duplicate =
            await findPersonByName(
              trx,
              targetUser,
              name,
            );

          if (
            duplicate
            && duplicate.id !== personId
          ) {
            throw createHttpError(
              409,
              'Esiste già una persona '
              + 'con questo nome',
            );
          }

          const now =
            new Date().toISOString();

          await trx('persons')
            .where({
              id: personId,
              user: targetUser,
            })
            .update({
              name,
              updated_at: now,
            });

          return {
            ...existing,
            name,
            updated_at: now,
          };
        },
      );

      res.json({
        ok: true,
        user: targetUser,
        person,
      });
    } catch (error) {
      console.error(
        '[faceReview] rename person:',
        error,
      );

      res.status(error.status || 500).json({
        ok: false,
        error: error.message,
      });
    }
  },
);


/*
 * Rimuove un volto da una persona senza
 * cancellare il volto o il cluster automatico.
 */
router.delete(
  '/persons/:personId/faces/:faceId',
  async (req, res) => {
    try {
      const authenticatedUser =
        getAuthenticatedUser(req);

      const targetUser =
        resolveFaceReviewUser(req);

      const personId = String(
        req.params.personId || '',
      ).trim();

      const faceId = String(
        req.params.faceId || '',
      ).trim();

      const result = await db.transaction(
        async (trx) => {
          const person = await trx(
            'persons',
          )
            .where({
              id: personId,
              user: targetUser,
            })
            .first();

          if (!person) {
            throw createHttpError(
              404,
              'Persona non trovata',
            );
          }

          const face = await trx(
            'faces as f',
          )
            .join(
              'photos as p',
              'p.id',
              'f.photo_id',
            )
            .where({
              'f.id': faceId,
              'f.person_id': personId,
              'p.user': targetUser,
            })
            .first(
              'f.id',
              'f.cluster_id',
            );

          if (!face) {
            throw createHttpError(
              404,
              'Volto non assegnato '
              + 'alla persona',
            );
          }

          const now =
            new Date().toISOString();

          await trx('faces')
            .where({
              id: faceId,
              person_id: personId,
            })
            .update({
              person_id: null,
              updated_at: now,
            });

          await trx(
            'face_cluster_memberships',
          )
            .where({
              face_id: faceId,
            })
            .whereNotNull('cluster_id')
            .update({
              review_status: 'excluded',
              reviewed_by:
                authenticatedUser,
              reviewed_at: now,
              updated_at: now,
            });

          await trx(
            'person_face_exclusions',
          )
            .insert({
              person_id: personId,
              face_id: faceId,
              user: targetUser,
              reason:
                'Rimosso manualmente '
                + 'dalla persona',
              created_by:
                authenticatedUser,
              created_at: now,
            })
            .onConflict([
              'person_id',
              'face_id',
            ])
            .merge({
              user: targetUser,
              reason:
                'Rimosso manualmente '
                + 'dalla persona',
              created_by:
                authenticatedUser,
              created_at: now,
            });

          await queuePrototypeRefresh(

            trx,

            {

              user: targetUser,

              personId,

              reason:

                'face_removed',

              requestedBy:

                authenticatedUser,

            },

          );


          return {
            person_id: personId,
            face_id: faceId,
            cluster_id:
              face.cluster_id || null,
          };
        },
      );

      res.json({
        ok: true,
        user: targetUser,
        ...result,
      });
    } catch (error) {
      console.error(
        '[faceReview] remove face:',
        error,
      );

      res.status(error.status || 500).json({
        ok: false,
        error: error.message,
      });
    }
  },
);




/*
 * Assegna manualmente un singolo volto
 * a una persona.
 *
 * Il volto può essere noise HDBSCAN:
 * cluster_id resta invariato, anche NULL.
 *
 * Body:
 * {
 *   "person_id": "..."
 * }
 */
router.post(
  '/faces/:faceId/assign-person',
  async (req, res) => {
    try {
      const authenticatedUser =
        getAuthenticatedUser(req);

      const targetUser =
        resolveFaceReviewUser(req);

      const faceId = String(
        req.params.faceId || '',
      ).trim();

      const personId = String(
        req.body?.person_id || '',
      ).trim();

      if (!faceId) {
        throw createHttpError(
          400,
          'Volto mancante',
        );
      }

      if (!personId) {
        throw createHttpError(
          400,
          'Persona mancante',
        );
      }

      const result = await db.transaction(
        async (trx) => {
          const person =
            await trx('persons')
              .where({
                id: personId,
                user: targetUser,
              })
              .first();

          if (!person) {
            throw createHttpError(
              404,
              'Persona non trovata',
            );
          }

          const face = await trx(
            'faces as f',
          )
            .join(
              'photos as p',
              'p.id',
              'f.photo_id',
            )
            .where({
              'f.id': faceId,
              'p.user': targetUser,
            })
            .whereNull('p.deleted_at')
            .first(
              'f.id',
              'f.photo_id',
              'f.cluster_id',
              'f.person_id',
            );

          if (!face) {
            throw createHttpError(
              404,
              'Volto non trovato',
            );
          }

          const now =
            new Date().toISOString();

          await trx('faces')
            .where({
              id: faceId,
            })
            .update({
              person_id: personId,
              updated_at: now,
            });

          /*
           * Accetta la membership della run
           * più recente che contiene il volto.
           * Funziona sia per cluster sia per noise.
           */
          const membership =
            await trx(
              'face_cluster_memberships as m',
            )
              .join(
                'face_cluster_runs as r',
                'r.id',
                'm.run_id',
              )
              .where({
                'm.face_id': faceId,
                'r.user': targetUser,
              })
              .orderBy(
                'r.created_at',
                'desc',
              )
              .first(
                'm.id',
                'm.run_id',
                'm.cluster_id',
                'm.label',
                'm.noise_flag',
              );

          if (membership) {
            await trx(
              'face_cluster_memberships',
            )
              .where({
                id: membership.id,
              })
              .update({
                review_status:
                  'accepted',
                reviewed_by:
                  authenticatedUser,
                reviewed_at: now,
                updated_at: now,
              });
          }

          /*
           * Se lo stesso volto era stato
           * precedentemente escluso dalla
           * persona, elimina l'esclusione.
           */
          await trx(
            'person_face_exclusions',
          )
            .where({
              person_id: personId,
              face_id: faceId,
            })
            .del();

          await queuePrototypeRefresh(

            trx,

            {

              user: targetUser,

              personId,

              reason:

                'noise_face_assigned',

              requestedBy:

                authenticatedUser,

            },

          );


          if (

            face.person_id

            && face.person_id !== personId

          ) {

            await queuePrototypeRefresh(

              trx,

              {

                user: targetUser,

                personId: face.person_id,

                reason:

                  'face_reassigned_away',

                requestedBy:

                  authenticatedUser,

              },

            );

          }


          return {
            person: {
              id: person.id,
              user: person.user,
              name: person.name,
            },
            face: {
              id: face.id,
              photo_id: face.photo_id,
              cluster_id:
                face.cluster_id || null,
              previous_person_id:
                face.person_id || null,
            },
            membership: membership
              ? {
                  run_id:
                    membership.run_id,
                  cluster_id:
                    membership.cluster_id
                    || null,
                  label:
                    Number(
                      membership.label,
                    ),
                  noise_flag:
                    Number(
                      membership.noise_flag,
                    ),
                }
              : null,
          };
        },
      );

      res.json({
        ok: true,
        user: targetUser,
        ...result,
      });
    } catch (error) {
      console.error(
        '[faceReview] assign face:',
        error,
      );

      res.status(error.status || 500)
        .json({
          ok: false,
          error: error.message,
        });
    }
  },
);




/*
 * Assegna più volti noise alla stessa persona.
 *
 * Body:
 * {
 *   "person_id": "...",
 *   "face_ids": ["face:...", "face:..."]
 * }
 *
 * In alternativa:
 * {
 *   "name": "Nuova persona",
 *   "face_ids": ["face:...", "face:..."]
 * }
 */
router.post(
  '/unclustered/assign-person',
  async (req, res) => {
    try {
      const authenticatedUser =
        getAuthenticatedUser(req);

      const targetUser =
        resolveFaceReviewUser(req);

      const faceIds =
        normalizeFaceIds(
          req.body?.face_ids,
        );

      let personId = String(
        req.body?.person_id || '',
      ).trim();

      const requestedName = String(
        req.body?.name || '',
      ).trim();

      if (!personId && !requestedName) {
        throw createHttpError(
          400,
          'Seleziona una persona oppure '
          + 'inserisci un nuovo nome',
        );
      }

      const result = await db.transaction(
        async (trx) => {
          let person = null;
          let personCreated = false;

          if (personId) {
            person = await trx('persons')
              .where({
                id: personId,
                user: targetUser,
              })
              .first();

            if (!person) {
              throw createHttpError(
                404,
                'Persona non trovata',
              );
            }
          } else {
            const name =
              normalizePersonName(
                requestedName,
              );

            person = await findPersonByName(
              trx,
              targetUser,
              name,
            );

            if (!person) {
              const now =
                new Date().toISOString();

              person = {
                id: randomUUID(),
                user: targetUser,
                name,
                created_at: now,
                updated_at: now,
              };

              await trx('persons')
                .insert(person);

              personCreated = true;
            }

            personId = person.id;
          }

          /*
           * Individua la run completata più recente
           * dell'utente. L'assegnazione bulk è
           * consentita solo sui noise di questa run.
           */
          const latestRun = await trx(
            'face_cluster_runs',
          )
            .where({
              user: targetUser,
              status: 'done',
            })
            .orderBy(
              'completed_at',
              'desc',
            )
            .orderBy(
              'created_at',
              'desc',
            )
            .first();

          if (!latestRun) {
            throw createHttpError(
              409,
              'Nessuna run di clustering disponibile',
            );
          }

          const rows = await trx(
            'face_cluster_memberships as m',
          )
            .join(
              'faces as f',
              'f.id',
              'm.face_id',
            )
            .join(
              'photos as p',
              'p.id',
              'f.photo_id',
            )
            .where({
              'm.run_id': latestRun.id,
              'm.noise_flag': 1,
              'p.user': targetUser,
            })
            .whereIn(
              'm.face_id',
              faceIds,
            )
            .whereNull(
              'f.person_id',
            )
            .select(
              'm.id as membership_id',
              'm.face_id',
              'm.label',
              'm.noise_flag',
              'f.cluster_id',
            );

          if (rows.length !== faceIds.length) {
            const foundIds = new Set(
              rows.map(
                (row) => row.face_id,
              ),
            );

            const invalidIds =
              faceIds.filter(
                (faceId) =>
                  !foundIds.has(faceId),
              );

            throw createHttpError(
              400,
              'Alcuni volti non sono noise '
              + 'assegnabili della run corrente: '
              + invalidIds.join(', '),
            );
          }

          const now =
            new Date().toISOString();

          for (const row of rows) {
            /*
             * Non modifica cluster_id:
             * un noise resta senza cluster.
             */
            await trx('faces')
              .where({
                id: row.face_id,
              })
              .update({
                person_id: personId,
                updated_at: now,
              });

            await trx(
              'face_cluster_memberships',
            )
              .where({
                id: row.membership_id,
              })
              .update({
                review_status: 'accepted',
                reviewed_by:
                  authenticatedUser,
                reviewed_at: now,
                updated_at: now,
              });

            await trx(
              'person_face_exclusions',
            )
              .where({
                person_id: personId,
                face_id: row.face_id,
              })
              .del();
          }

          await queuePrototypeRefresh(

            trx,

            {

              user: targetUser,

              personId,

              reason:

                'noise_faces_bulk_assigned',

              requestedBy:

                authenticatedUser,

            },

          );


          return {
            person: {
              id: person.id,
              user: person.user,
              name: person.name,
            },
            person_created:
              personCreated,
            run_id:
              latestRun.id,
            assigned_face_ids:
              faceIds,
            assigned_count:
              faceIds.length,
          };
        },
      );

      res.json({
        ok: true,
        user: targetUser,
        ...result,
      });
    } catch (error) {
      console.error(
        '[faceReview] bulk noise assignment:',
        error,
      );

      res.status(error.status || 500)
        .json({
          ok: false,
          error: error.message,
        });
    }
  },
);




/*
 * Accoda manualmente il ricalcolo dei
 * prototype di una persona.
 *
 * Richieste pending successive vengono
 * accorpate nello stesso job.
 */
router.post(
  '/persons/:personId/prototypes/recalculate',
  async (req, res) => {
    try {
      const authenticatedUser =
        getAuthenticatedUser(req);

      const targetUser =
        resolveFaceReviewUser(req);

      const personId = String(
        req.params.personId || '',
      ).trim();

      if (!personId) {
        throw createHttpError(
          400,
          'Persona mancante',
        );
      }

      const reason = String(
        req.body?.reason
        || 'manual_rebuild',
      ).trim();

      const result = await db.transaction(
        async (trx) => {
          const person = await trx(
            'persons',
          )
            .where({
              id: personId,
              user: targetUser,
            })
            .first();

          if (!person) {
            throw createHttpError(
              404,
              'Persona non trovata',
            );
          }

          const queued =
            await queuePrototypeRefresh(
              trx,
              {
                user: targetUser,
                personId,
                reason,
                requestedBy:
                  authenticatedUser,
              },
            );

          return {
            person: {
              id: person.id,
              user: person.user,
              name: person.name,
            },
            ...queued,
          };
        },
      );

      res.status(
        result.created ? 202 : 200,
      ).json({
        ok: true,
        user: targetUser,
        person: result.person,
        created: result.created,
        job: result.job,
      });
    } catch (error) {
      console.error(
        '[faceReview] queue prototype refresh:',
        error,
      );

      res.status(error.status || 500)
        .json({
          ok: false,
          error: error.message,
        });
    }
  },
);



module.exports = router;