// Financial / audit profile helpers — ASF, AAAS, Curtea de Conturi.
// Mirrors style of getRegasStatus / getAepStatus / getAnafDebtStatus in
// profile-queries.ts. Each helper returns null when the CUI has no relevant
// rows (caller uses null to skip badge + section render).
import { query } from './db.js';

// ─────────────────────────────────────────────────────────────
// ASF — Authority for Financial Supervision (insurers / brokers /
// pension funds / AIFM / UCITS). One CUI may appear multiple times
// (different register types or active+radiat history); mv_entitati_per_cui
// rolls them up. Detail rows from asf.entitati keep the timeline intact.
// ─────────────────────────────────────────────────────────────

export interface AsfEntitatePreview {
  register_type: string;
  register_no: string;
  section_status: string;          // 'activ' | 'radiat'
  name: string;
  tip_companie: string | null;
  forma_juridica: string | null;
  data_autorizare: string | null;  // YYYY-MM-DD
  data_radiere: string | null;     // YYYY-MM-DD or null
  nr_autorizatie: string | null;
}

export interface AsfStatus {
  nr_total: number;
  nr_active: number;
  nr_radiate: number;
  nr_asigurator: number;
  nr_broker: number;
  nr_fond_pensii: number;
  nr_aifm: number;
  nr_ucits: number;
  register_types: string[];        // distinct types this CUI holds
  register_numbers: string[];      // canonical RA-NNN / RBK-NNN list
  prima_autorizare: string | null; // YYYY-MM-DD
  ultima_radiere: string | null;   // YYYY-MM-DD or null when none radiated
  has_active: boolean;
  has_radiat: boolean;
  recent: AsfEntitatePreview[];
}

export async function getAsfStatus(cui: string): Promise<AsfStatus | null> {
  const normCui = cui.replace(/^RO\s*/i, '').trim();

  const aggR = await query<any>(
    `SELECT nr_total::int,
            nr_active::int,
            nr_radiate::int,
            nr_asigurator::int,
            nr_broker::int,
            nr_fond_pensii::int,
            nr_aifm::int,
            nr_ucits::int,
            register_types,
            register_numbers,
            to_char(prima_autorizare, 'YYYY-MM-DD') AS prima_autorizare,
            to_char(ultima_radiere, 'YYYY-MM-DD')   AS ultima_radiere
       FROM asf.mv_entitati_per_cui
      WHERE cui = $1`,
    [normCui],
  );
  const a = aggR.rows[0];
  if (!a || Number(a.nr_total) === 0) return null;

  const recentR = await query<AsfEntitatePreview>(
    `SELECT register_type,
            register_no,
            section_status,
            name,
            tip_companie,
            forma_juridica,
            to_char(data_autorizare, 'YYYY-MM-DD') AS data_autorizare,
            to_char(data_radiere,    'YYYY-MM-DD') AS data_radiere,
            nr_autorizatie
       FROM asf.entitati
      WHERE cui = $1
      ORDER BY (section_status = 'activ') DESC,
               data_autorizare DESC NULLS LAST
      LIMIT 10`,
    [normCui],
  );

  return {
    nr_total: Number(a.nr_total),
    nr_active: Number(a.nr_active),
    nr_radiate: Number(a.nr_radiate),
    nr_asigurator: Number(a.nr_asigurator),
    nr_broker: Number(a.nr_broker),
    nr_fond_pensii: Number(a.nr_fond_pensii),
    nr_aifm: Number(a.nr_aifm),
    nr_ucits: Number(a.nr_ucits),
    register_types: a.register_types || [],
    register_numbers: a.register_numbers || [],
    prima_autorizare: a.prima_autorizare,
    ultima_radiere: a.ultima_radiere,
    has_active: Number(a.nr_active) > 0,
    has_radiat: Number(a.nr_radiate) > 0,
    recent: recentR.rows,
  };
}

// ─────────────────────────────────────────────────────────────
// AAAS — state still owns shares / firm owes state money.
// Stage 1 dataset = 12 named active-portfolio firms; very high signal.
// ─────────────────────────────────────────────────────────────

export interface AaasFirmaPreview {
  name: string;
  aaas_status: string;             // 'active_holding' | 'post_priv_debt' | ...
  state_share_pct: number | null;  // 0–100
  debt_to_state_lei: number | null;
  reg_number: string | null;
  last_action: string | null;
  last_action_date: string | null; // YYYY-MM-DD
  source_url: string;
}

export interface AaasStatus {
  rows_count: number;
  statusuri: string[];                  // distinct aaas_status values
  max_state_share_pct: number | null;
  total_debt_to_state_lei: number | null;
  last_seen_at: string | null;          // YYYY-MM-DD
  recent: AaasFirmaPreview[];
}

export async function getAaasStatus(cui: string): Promise<AaasStatus | null> {
  const normCui = cui.replace(/^RO\s*/i, '').trim();

  const aggR = await query<any>(
    `SELECT rows_count::int,
            statusuri,
            max_state_share_pct::numeric,
            total_debt_to_state_lei::numeric,
            to_char(last_seen_at, 'YYYY-MM-DD') AS last_seen_at
       FROM aaas.mv_per_cui
      WHERE cui = $1`,
    [normCui],
  );
  const a = aggR.rows[0];
  if (!a || Number(a.rows_count) === 0) return null;

  const recentR = await query<any>(
    `SELECT name,
            aaas_status,
            state_share_pct::numeric,
            debt_to_state_lei::numeric,
            reg_number,
            last_action,
            to_char(last_action_date, 'YYYY-MM-DD') AS last_action_date,
            source_url
       FROM aaas.firme
      WHERE cui = $1
      ORDER BY last_action_date DESC NULLS LAST,
               state_share_pct DESC NULLS LAST
      LIMIT 10`,
    [normCui],
  );

  return {
    rows_count: Number(a.rows_count),
    statusuri: a.statusuri || [],
    max_state_share_pct: a.max_state_share_pct == null ? null : Number(a.max_state_share_pct),
    total_debt_to_state_lei: a.total_debt_to_state_lei == null ? null : Number(a.total_debt_to_state_lei),
    last_seen_at: a.last_seen_at,
    recent: recentR.rows.map((r) => ({
      name: r.name,
      aaas_status: r.aaas_status,
      state_share_pct: r.state_share_pct == null ? null : Number(r.state_share_pct),
      debt_to_state_lei: r.debt_to_state_lei == null ? null : Number(r.debt_to_state_lei),
      reg_number: r.reg_number,
      last_action: r.last_action,
      last_action_date: r.last_action_date,
      source_url: r.source_url,
    })),
  };
}

// ─────────────────────────────────────────────────────────────
// Curtea de Conturi — audit reports targeting this CUI.
// Stage 1 dataset (~1,133 reports) has audited_entity_name parsed from
// listing-page titles but audited_entity_cui is NULL until Stage 2 PDF
// resolve. We therefore match BOTH paths:
//   1. direct: rapoarte.audited_entity_cui = $1 (currently 0 rows;
//      future-proof for Stage 2 backfill).
//   2. fuzzy:  pg_trgm % match between firms.entities.name (for $1) and
//      rapoarte.audited_entity_name. Returned rows are flagged
//      match_method='name_fuzzy' so the UI can show a discrete
//      "identificare aproximativă" hint.
// Threshold: pg_trgm default 0.3 (already set on satra). We further
// restrict the firms-side lookup to a single CUI (no fan-out).
// ─────────────────────────────────────────────────────────────

export interface CurteaContPreview {
  slug_id: string;
  category: string;
  audit_type: string | null;    // 'financiar' | 'conformitate' | 'performanta' | 'control' | 'follow-up'
  audit_year: number | null;
  title: string;
  audited_entity_name: string | null;
  doc_number: string | null;
  doc_date: string | null;       // YYYY-MM-DD
  publication_date: string | null;
  detail_url: string;
  match_method: 'cui_direct' | 'name_fuzzy';
}

export interface CurteaContStatus {
  nr_total: number;
  nr_direct: number;             // matched by audited_entity_cui
  nr_fuzzy: number;              // matched by audited_entity_name fuzzy
  by_audit_type: Record<string, number>;
  ani_acoperiti: number[];       // distinct audit_year values, sorted desc
  prima_publicare: string | null;
  ultima_publicare: string | null;
  recent: CurteaContPreview[];
}

export async function getCurteaContStatus(cui: string): Promise<CurteaContStatus | null> {
  const normCui = cui.replace(/^RO\s*/i, '').trim();

  // Combined query: direct CUI match UNION ALL with fuzzy-name match (where
  // firms.entities.name_normalized % rapoarte.audited_entity_name's normalized
  // form). Direct rows take priority (DISTINCT ON would be safer but Stage 1
  // has zero direct rows so not yet relevant).
  const rowsR = await query<any>(
    `WITH direct AS (
       SELECT slug_id, category, audit_type, audit_year, title,
              audited_entity_name, doc_number,
              to_char(doc_date,         'YYYY-MM-DD') AS doc_date,
              to_char(publication_date, 'YYYY-MM-DD') AS publication_date,
              detail_url,
              'cui_direct'::text AS match_method
         FROM curteacont.rapoarte
        WHERE audited_entity_cui = $1
     ),
     firma AS (
       SELECT firms.normalize_company_name(name) AS norm
         FROM firms.entities
        WHERE cui = $1
        LIMIT 1
     ),
     fuzzy AS (
       SELECT r.slug_id, r.category, r.audit_type, r.audit_year, r.title,
              r.audited_entity_name, r.doc_number,
              to_char(r.doc_date,         'YYYY-MM-DD') AS doc_date,
              to_char(r.publication_date, 'YYYY-MM-DD') AS publication_date,
              r.detail_url,
              'name_fuzzy'::text AS match_method
         FROM curteacont.rapoarte r
         JOIN firma f ON TRUE
        WHERE r.audited_entity_cui IS NULL
          AND r.audited_entity_name IS NOT NULL
          AND firms.normalize_company_name(r.audited_entity_name) % f.norm
     )
     SELECT * FROM direct
     UNION ALL
     SELECT * FROM fuzzy
     ORDER BY publication_date DESC NULLS LAST,
              doc_date         DESC NULLS LAST
     LIMIT 100`,
    [normCui],
  );

  const rows = rowsR.rows;
  if (rows.length === 0) return null;

  const nrDirect = rows.filter((r: any) => r.match_method === 'cui_direct').length;
  const nrFuzzy  = rows.filter((r: any) => r.match_method === 'name_fuzzy').length;

  const byAuditType: Record<string, number> = {};
  for (const r of rows) {
    const k = r.audit_type || 'altele';
    byAuditType[k] = (byAuditType[k] || 0) + 1;
  }

  const aniSet = new Set<number>();
  for (const r of rows) {
    if (r.audit_year != null) aniSet.add(Number(r.audit_year));
  }
  const aniAcoperiti = Array.from(aniSet).sort((a, b) => b - a);

  const dates = rows.map((r: any) => r.publication_date).filter(Boolean).sort();
  const primaPublicare  = dates[0] || null;
  const ultimaPublicare = dates[dates.length - 1] || null;

  return {
    nr_total: rows.length,
    nr_direct: nrDirect,
    nr_fuzzy: nrFuzzy,
    by_audit_type: byAuditType,
    ani_acoperiti: aniAcoperiti,
    prima_publicare: primaPublicare,
    ultima_publicare: ultimaPublicare,
    recent: rows.slice(0, 10).map((r: any) => ({
      slug_id: r.slug_id,
      category: r.category,
      audit_type: r.audit_type,
      audit_year: r.audit_year == null ? null : Number(r.audit_year),
      title: r.title,
      audited_entity_name: r.audited_entity_name,
      doc_number: r.doc_number,
      doc_date: r.doc_date,
      publication_date: r.publication_date,
      detail_url: r.detail_url,
      match_method: r.match_method as 'cui_direct' | 'name_fuzzy',
    })),
  };
}
