/**
 * models/findingClassification.js
 *
 * One row per AI categorization of a finding. Re-classification
 * overwrites — we keep only the LATEST call's result per finding.
 * We never store the page content itself; only the category label
 * + a short quoted evidence phrase.
 */

'use strict';

const { db } = require('../config/db');

const insert = db.prepare(`
  INSERT INTO finding_classifications
    (finding_id, category, confidence, evidence, summary, model, ai_call_id, triggered_by, classified_at)
  VALUES
    (@finding_id, @category, @confidence, @evidence, @summary, @model, @ai_call_id, @triggered_by, @classified_at)
`);

// Latest classification for one finding.
const latestByFinding = db.prepare(`
  SELECT * FROM finding_classifications
   WHERE finding_id = ?
   ORDER BY classified_at DESC, id DESC
   LIMIT 1
`);

// Latest classification for many findings — keyed by finding_id.
const latestByFindingIds = (ids) => {
  if (!ids.length) return [];
  const placeholders = ids.map(() => '?').join(',');
  return db.prepare(`
    SELECT * FROM finding_classifications
     WHERE finding_id IN (${placeholders})
     ORDER BY classified_at DESC, id DESC
  `).all(...ids);
};

// Full-text-ish LIKE search across title + snippet for a case.
// This is a tiny dataset (operator-scoped), so SQLite FTS5 is overkill.
const searchInCase = db.prepare(`
  SELECT
    f.id AS finding_id, f.case_id, f.url, f.domain, f.page_title, f.page_snippet, f.tier, f.status,
    fc.category, fc.confidence, fc.evidence, fc.summary, fc.classified_at
  FROM findings f
  LEFT JOIN (
    SELECT finding_id, MAX(id) AS max_id FROM finding_classifications GROUP BY finding_id
  ) latest ON latest.finding_id = f.id
  LEFT JOIN finding_classifications fc ON fc.id = latest.max_id
  WHERE f.case_id = ?
    AND (
      LOWER(IFNULL(f.page_title,   '')) LIKE @q
      OR LOWER(IFNULL(f.page_snippet,'')) LIKE @q
      OR LOWER(IFNULL(f.url,        '')) LIKE @q
      OR LOWER(IFNULL(fc.evidence,   '')) LIKE @q
      OR LOWER(IFNULL(fc.summary,    '')) LIKE @q
    )
  ORDER BY f.created_at DESC
  LIMIT 200
`);

const listAll = db.prepare(`
  SELECT * FROM finding_classifications
   ORDER BY classified_at DESC, id DESC
   LIMIT 200
`);

function record({ finding_id, category, confidence, evidence, summary, model, ai_call_id, triggered_by = 'manual' }) {
  const info = insert.run({
    finding_id,
    category,
    confidence: Math.max(0, Math.min(1, Number(confidence) || 0)),
    evidence: (evidence || '').slice(0, 200),
    summary:  (summary  || '').slice(0, 200),
    model: model || null,
    ai_call_id: ai_call_id || null,
    triggered_by,
    classified_at: Date.now(),
  });
  return db.prepare('SELECT * FROM finding_classifications WHERE id = ?').get(info.lastInsertRowid);
}

function latestFor(findingId) { return latestByFinding.get(findingId); }

function latestForMany(findingIds) {
  const rows = latestByFindingIds(findingIds);
  const byId = {};
  for (const r of rows) {
    if (!byId[r.finding_id]) byId[r.finding_id] = r;   // first hit = newest because ORDER BY classified_at DESC
  }
  return byId;
}

function searchCase(caseId, term) {
  const q = '%' + String(term || '').toLowerCase().replace(/[%_]/g, c => '\\' + c) + '%';
  return searchInCase.all({ q });
}

function listRecent(limit = 50) { return listAll.all(limit); }

module.exports = { record, latestFor, latestForMany, searchCase, listRecent };
