import { query } from './db.js';

export interface AuthorityProfile {
  cui: string;
  name: string;
  county: string | null;
  county_code: string | null;
  authority_type: string | null;
  main_activity: string | null;
  address: string | null;
  email: string | null;
  phone: string | null;
  url: string | null;
  total_contracts: number;
  total_awarded: number | null;
  total_estimated: number | null;
  avg_contract: number | null;
  first_contract_date: string | null;
  last_contract_date: string | null;
  distinct_suppliers: number;
}

export interface SupplierProfile {
  cui: string;
  name: string;
  county: string | null;
  address: string | null;
  email: string | null;
  phone: string | null;
  url: string | null;
  is_sme: boolean | null;
  contracts_won: number;
  total_awarded: number | null;
  avg_contract: number | null;
  first_contract_date: string | null;
  last_contract_date: string | null;
  distinct_buyers: number;
}

export interface FirmDetails {
  cui: string;
  name: string;
  forma_juridica: string | null;
  cod_inmatriculare: string | null;
  data_inmatriculare: string | null;
  registration_year: number | null;
  status_text: string | null;
  is_radiated_onrc: boolean;
  // Address
  adr_full: string | null;
  adr_judet: string | null;
  adr_localitate: string | null;
  // Activity
  caen_autorizate: string[] | null;
  caen_count: number;
  // Web/contact
  web: string | null;
  // Owners
  rep_legali: Array<{ persoana?: string; calitate?: string; localitate?: string; judet?: string; tara?: string }> | null;
  // Foreign
  tara_firma_mama: string | null;
}

export interface FirmFinancialYear {
  year: number;
  cifra_afaceri: number | null;
  profit_net: number | null;
  pierdere_neta: number | null;
  venituri_total: number | null;
  cheltuieli_total: number | null;
  active_imobilizate: number | null;
  active_circulante: number | null;
  datorii: number | null;
  capitaluri_total: number | null;
  capital_subscris: number | null;
  numar_salariati: number | null;
}

export interface ProfileContract {
  id: number;
  ref_number: string;
  type: string;
  title: string | null;
  cpv_code: string | null;
  cpv_name_ro: string | null;
  cpv_division: string | null;
  cpv_division_name: string | null;
  cpv_emoji: string | null;
  publication_date: string | null;
  awarded_value: number | null;
  estimated_value: number | null;
  currency: string | null;
  procedure_type: string | null;
  // Counterpart (other side of the deal)
  counterpart_name: string | null;
  counterpart_cui: string | null;
  authority_name?: string | null;
  authority_cui?: string | null;
  supplier_name?: string | null;
  supplier_cui?: string | null;
  seap_url: string | null;
  source: string;
  has_lots: boolean | null;
  lots_count: number | null;
}

export interface CpvBreakdown {
  cpv_division: string;
  cpv_division_name: string;
  emoji: string | null;
  contracts: number;
  total_value: number;
}

export interface CounterpartTop {
  cui: string;
  name: string;
  county: string | null;
  contracts: number;
  total_value: number;
}

export interface MonthlyTotal {
  month: string; // YYYY-MM
  contracts: number;
  total_value: number;
}

export interface DailyTotal {
  day: string; // YYYY-MM-DD
  contracts: number;
  total_value: number;
}

export interface CountyDistribution {
  county: string;
  county_normalized: string;
  contracts: number;
  total_value: number;
}

export interface SupplierKpi {
  cpv_diversity: number; // Shannon entropy on CPV divisions
  geo_footprint: number; // count distinct authority counties
  top_buyer_share: number; // 0..1 — max single-buyer dependency
  growth_yoy: number | null; // last 12 months vs previous 12, null if no prior
  modification_count: number;
}

export interface AuthorityKpi {
  direct_pct: number; // 0..1 — share of value via direct acquisition
  framework_pct: number; // 0..1
  top_supplier_share: number; // 0..1 — biggest single-supplier dependency
  hhi_suppliers: number; // 0..10000
  modification_count: number;
  cpv_diversity: number; // Shannon entropy
  q4_spike: number | null; // (Q4 value last full year) / (yearly avg quarter), null if insufficient data
}


// ── ONRC + ANAF firm details (works for any RO company, not just SEAP-active) ──
export async function getFirmDetails(cui: string): Promise<FirmDetails | null> {
  const normCui = cui.replace(/^RO\s*/i, '').trim();
  const result = await query<any>(`
    SELECT
      cui, name, forma_juridica, cod_inmatriculare,
      to_char(data_inmatriculare, 'YYYY-MM-DD') AS data_inmatriculare,
      registration_year, status_text, is_radiated_onrc,
      adr_full, adr_judet, adr_localitate,
      caen_autorizate,
      COALESCE(array_length(caen_autorizate, 1), 0) AS caen_count,
      web, rep_legali, tara_firma_mama
    FROM firms.entities
    WHERE cui = $1
  `, [normCui]);
  return result.rows[0] || null;
}

export async function getFirmFinancials(cui: string): Promise<FirmFinancialYear[]> {
  const normCui = cui.replace(/^RO\s*/i, '').trim();
  const result = await query<FirmFinancialYear>(`
    SELECT
      year,
      cifra_afaceri, profit_net, pierdere_neta,
      venituri_total, cheltuieli_total,
      active_imobilizate, active_circulante,
      datorii, capitaluri_total, capital_subscris,
      numar_salariati
    FROM firms.financials
    WHERE cui = $1
    ORDER BY year DESC
  `, [normCui]);
  return result.rows;
}


// ── Authority profile ──
export async function getAuthorityProfile(cui: string): Promise<AuthorityProfile | null> {
  const result = await query<AuthorityProfile>(`
    SELECT
      a.authority_cui AS cui,
      MIN(a.authority_name) AS name,
      MIN(cl.county) AS county,
      MIN(a.county_code) AS county_code,
      MIN(a.authority_type) AS authority_type,
      MIN(a.authority_main_activity) AS main_activity,
      MIN(a.authority_address) AS address,
      MIN(a.authority_email) AS email,
      MIN(a.authority_phone) AS phone,
      MIN(a.authority_url) AS url,
      count(*)::int AS total_contracts,
      sum(a.awarded_value)::numeric AS total_awarded,
      sum(a.estimated_value)::numeric AS total_estimated,
      avg(a.awarded_value)::numeric AS avg_contract,
      min(a.publication_date) AS first_contract_date,
      max(a.publication_date) AS last_contract_date,
      count(DISTINCT a.supplier_cui)::int AS distinct_suppliers
    FROM seap.announcements a
    LEFT JOIN seap.cui_location cl ON cl.cui = a.authority_cui
    WHERE a.authority_cui = $1
    GROUP BY a.authority_cui
  `, [cui]);
  return result.rows[0] || null;
}


export async function getSupplierProfile(cui: string): Promise<SupplierProfile | null> {
  // Strip "RO " prefix if user-supplied
  const normCui = cui.replace(/^RO\s*/i, '').trim();
  const result = await query<SupplierProfile>(`
    SELECT
      $1::text AS cui,
      MIN(a.supplier_name) AS name,
      MIN(cl.county) AS county,
      MIN(a.supplier_address) AS address,
      MIN(NULL::text) AS email,
      MIN(NULL::text) AS phone,
      MIN(NULL::text) AS url,
      bool_or(a.supplier_is_sme) AS is_sme,
      count(*)::int AS contracts_won,
      sum(a.awarded_value)::numeric AS total_awarded,
      avg(a.awarded_value)::numeric AS avg_contract,
      min(a.publication_date) AS first_contract_date,
      max(a.publication_date) AS last_contract_date,
      count(DISTINCT a.authority_cui)::int AS distinct_buyers
    FROM seap.announcements a
    LEFT JOIN seap.cui_location cl ON cl.cui = $2
    WHERE (a.supplier_cui = $1 OR a.supplier_cui = 'RO ' || $1 OR a.supplier_cui = 'RO' || $1)
      AND a.type IN ('ca_notice', 'rfq_notice', 'contract', 'atribuire_fara', 'ted_notice', 'da')
  `, [cui, normCui]);
  return result.rows[0]?.contracts_won ? result.rows[0] : null;
}


// Contracts list (paginated)
export async function getAuthorityContracts(cui: string, limit = 50, offset = 0): Promise<ProfileContract[]> {
  const result = await query<ProfileContract>(`
    SELECT
      a.id, a.ref_number, a.type, a.title,
      a.cpv_code, a.cpv_name_ro, a.cpv_division,
      c.name_ro AS cpv_division_name, c.emoji AS cpv_emoji,
      a.publication_date, a.awarded_value, a.estimated_value, a.currency,
      a.procedure_type,
      a.supplier_name AS counterpart_name, a.supplier_cui AS counterpart_cui,
      a.supplier_name, a.supplier_cui,
      a.seap_url, a.source,
      a.contract_has_lots AS has_lots, a.lots_count
    FROM seap.announcements a
    LEFT JOIN seap.cpv_codes c ON c.code = a.cpv_division
    WHERE a.authority_cui = $1
    ORDER BY a.publication_date DESC NULLS LAST
    LIMIT $2 OFFSET $3
  `, [cui, limit, offset]);
  return result.rows;
}


export async function getSupplierContracts(cui: string, limit = 50, offset = 0): Promise<ProfileContract[]> {
  const result = await query<ProfileContract>(`
    SELECT
      a.id, a.ref_number, a.type, a.title,
      a.cpv_code, a.cpv_name_ro, a.cpv_division,
      c.name_ro AS cpv_division_name, c.emoji AS cpv_emoji,
      a.publication_date, a.awarded_value, a.estimated_value, a.currency,
      a.procedure_type,
      a.authority_name AS counterpart_name, a.authority_cui AS counterpart_cui,
      a.authority_name, a.authority_cui,
      a.seap_url, a.source,
      a.contract_has_lots AS has_lots, a.lots_count
    FROM seap.announcements a
    LEFT JOIN seap.cpv_codes c ON c.code = a.cpv_division
    WHERE (a.supplier_cui = $1 OR a.supplier_cui = 'RO ' || $1 OR a.supplier_cui = 'RO' || $1)
    ORDER BY a.publication_date DESC NULLS LAST
    LIMIT $2 OFFSET $3
  `, [cui, limit, offset]);
  return result.rows;
}


// CPV breakdown for an authority or supplier
export async function getCpvBreakdown(
  side: 'authority' | 'supplier',
  cui: string,
): Promise<CpvBreakdown[]> {
  const col = side === 'authority' ? 'authority_cui' : 'supplier_cui';
  const cuiCondition = side === 'authority'
    ? `a.${col} = $1`
    : `(a.${col} = $1 OR a.${col} = 'RO ' || $1 OR a.${col} = 'RO' || $1)`;
  const result = await query<CpvBreakdown>(`
    SELECT
      a.cpv_division,
      c.name_ro AS cpv_division_name,
      c.emoji,
      count(*)::int AS contracts,
      coalesce(sum(a.awarded_value), 0)::numeric AS total_value
    FROM seap.announcements a
    LEFT JOIN seap.cpv_codes c ON c.code = a.cpv_division
    WHERE ${cuiCondition} AND a.cpv_division IS NOT NULL
    GROUP BY a.cpv_division, c.name_ro, c.emoji
    ORDER BY 5 DESC NULLS LAST
    LIMIT 20
  `, [cui]);
  return result.rows;
}


// Top counterparts (top suppliers for an authority, top buyers for a supplier)
export async function getTopCounterparts(
  side: 'authority' | 'supplier',
  cui: string,
  limit = 10,
): Promise<CounterpartTop[]> {
  if (side === 'authority') {
    const result = await query<CounterpartTop>(`
      SELECT
        a.supplier_cui AS cui,
        MIN(a.supplier_name) AS name,
        MIN(cl.county) AS county,
        count(*)::int AS contracts,
        coalesce(sum(a.awarded_value), 0)::numeric AS total_value
      FROM seap.announcements a
      LEFT JOIN seap.cui_location cl ON cl.cui = regexp_replace(a.supplier_cui, '^RO\\s*', '', 'i')
      WHERE a.authority_cui = $1 AND a.supplier_cui IS NOT NULL
      GROUP BY a.supplier_cui
      ORDER BY 5 DESC NULLS LAST
      LIMIT $2
    `, [cui, limit]);
    return result.rows;
  } else {
    const result = await query<CounterpartTop>(`
      SELECT
        a.authority_cui AS cui,
        MIN(a.authority_name) AS name,
        MIN(cl.county) AS county,
        count(*)::int AS contracts,
        coalesce(sum(a.awarded_value), 0)::numeric AS total_value
      FROM seap.announcements a
      LEFT JOIN seap.cui_location cl ON cl.cui = a.authority_cui
      WHERE (a.supplier_cui = $1 OR a.supplier_cui = 'RO ' || $1 OR a.supplier_cui = 'RO' || $1)
        AND a.authority_cui IS NOT NULL
      GROUP BY a.authority_cui
      ORDER BY 5 DESC NULLS LAST
      LIMIT $2
    `, [cui, limit]);
    return result.rows;
  }
}


// Monthly timeline (last 24 months)
export async function getMonthlyTimeline(
  side: 'authority' | 'supplier',
  cui: string,
): Promise<MonthlyTotal[]> {
  const col = side === 'authority' ? 'authority_cui' : 'supplier_cui';
  const cuiCondition = side === 'authority'
    ? `a.${col} = $1`
    : `(a.${col} = $1 OR a.${col} = 'RO ' || $1 OR a.${col} = 'RO' || $1)`;
  const result = await query<MonthlyTotal>(`
    SELECT
      to_char(date_trunc('month', a.publication_date), 'YYYY-MM') AS month,
      count(*)::int AS contracts,
      coalesce(sum(a.awarded_value), 0)::numeric AS total_value
    FROM seap.announcements a
    WHERE ${cuiCondition}
      AND a.publication_date >= now() - interval '24 months'
    GROUP BY 1
    ORDER BY 1
  `, [cui]);
  return result.rows;
}


// Daily contracts heatmap (last 5 years) — GitHub-style calendar
export async function getDailyHeatmap(
  side: 'authority' | 'supplier',
  cui: string,
): Promise<DailyTotal[]> {
  const col = side === 'authority' ? 'authority_cui' : 'supplier_cui';
  const cuiCondition = side === 'authority'
    ? `a.${col} = $1`
    : `(a.${col} = $1 OR a.${col} = 'RO ' || $1 OR a.${col} = 'RO' || $1)`;
  const result = await query<DailyTotal>(`
    SELECT
      to_char(a.publication_date::date, 'YYYY-MM-DD') AS day,
      count(*)::int AS contracts,
      coalesce(sum(a.awarded_value), 0)::numeric AS total_value
    FROM seap.announcements a
    WHERE ${cuiCondition}
      AND a.publication_date >= now() - interval '5 years'
      AND a.publication_date IS NOT NULL
    GROUP BY 1
    ORDER BY 1
  `, [cui]);
  return result.rows;
}


// County distribution — for an authority: counties of its suppliers; for a supplier: counties of its buyers.
export async function getCountyDistribution(
  side: 'authority' | 'supplier',
  cui: string,
): Promise<CountyDistribution[]> {
  if (side === 'authority') {
    const result = await query<CountyDistribution>(`
      SELECT
        coalesce(scl.county, 'Necunoscut') AS county,
        upper(unaccent(coalesce(scl.county, 'Necunoscut'))) AS county_normalized,
        count(*)::int AS contracts,
        coalesce(sum(a.awarded_value), 0)::numeric AS total_value
      FROM seap.announcements a
      LEFT JOIN seap.cui_location scl ON scl.cui = regexp_replace(upper(a.supplier_cui), '(^RO)|\\s+', '', 'g')
      WHERE a.authority_cui = $1
        AND a.supplier_cui IS NOT NULL
      GROUP BY 1, 2
      ORDER BY total_value DESC NULLS LAST
    `, [cui]);
    return result.rows;
  } else {
    const result = await query<CountyDistribution>(`
      SELECT
        coalesce(acl.county, 'Necunoscut') AS county,
        upper(unaccent(coalesce(acl.county, 'Necunoscut'))) AS county_normalized,
        count(*)::int AS contracts,
        coalesce(sum(a.awarded_value), 0)::numeric AS total_value
      FROM seap.announcements a
      LEFT JOIN seap.cui_location acl ON acl.cui = a.authority_cui
      WHERE (a.supplier_cui = $1 OR a.supplier_cui = 'RO ' || $1 OR a.supplier_cui = 'RO' || $1)
        AND a.authority_cui IS NOT NULL
      GROUP BY 1, 2
      ORDER BY total_value DESC NULLS LAST
    `, [cui]);
    return result.rows;
  }
}


// Supplier KPI strip
export async function getSupplierKpi(cui: string): Promise<SupplierKpi> {
  const result = await query<{
    cpv_diversity: number;
    geo_footprint: number;
    top_buyer_share: number;
    yoy_recent: number;
    yoy_prior: number;
    modification_count: number;
  }>(`
    WITH base AS (
      SELECT a.*, regexp_replace(upper(a.supplier_cui), '(^RO)|\\s+', '', 'g') AS norm_cui
      FROM seap.announcements a
      WHERE (a.supplier_cui = $1 OR a.supplier_cui = 'RO ' || $1 OR a.supplier_cui = 'RO' || $1)
    ),
    cpv AS (
      SELECT cpv_division, count(*)::numeric AS n
      FROM base WHERE cpv_division IS NOT NULL
      GROUP BY cpv_division
    ),
    cpv_total AS (SELECT sum(n) AS tot FROM cpv),
    diversity AS (
      SELECT
        COALESCE(-sum((n / NULLIF((SELECT tot FROM cpv_total), 0)) * ln((n / NULLIF((SELECT tot FROM cpv_total), 0)))), 0) AS h
      FROM cpv
    ),
    geo AS (
      SELECT count(DISTINCT acl.county)::int AS n
      FROM base
      LEFT JOIN seap.cui_location acl ON acl.cui = base.authority_cui
      WHERE acl.county IS NOT NULL
    ),
    buyers AS (
      SELECT authority_cui, sum(awarded_value) AS total
      FROM base WHERE authority_cui IS NOT NULL AND awarded_value IS NOT NULL
      GROUP BY authority_cui
    ),
    top_buyer AS (
      SELECT
        CASE WHEN sum(total) = 0 OR sum(total) IS NULL THEN 0
             ELSE max(total) / sum(total) END AS share
      FROM buyers
    ),
    yoy AS (
      SELECT
        coalesce(sum(awarded_value) FILTER (WHERE publication_date >= now() - interval '12 months'), 0) AS recent,
        coalesce(sum(awarded_value) FILTER (WHERE publication_date >= now() - interval '24 months' AND publication_date < now() - interval '12 months'), 0) AS prior
      FROM base
    ),
    mods AS (
      SELECT count(*)::int AS n FROM base WHERE modification_desc IS NOT NULL
    )
    SELECT
      diversity.h::float8 AS cpv_diversity,
      geo.n::int AS geo_footprint,
      coalesce(top_buyer.share, 0)::float8 AS top_buyer_share,
      yoy.recent::numeric AS yoy_recent,
      yoy.prior::numeric AS yoy_prior,
      mods.n::int AS modification_count
    FROM diversity, geo, top_buyer, yoy, mods
  `, [cui]);
  const row = result.rows[0];
  if (!row) {
    return { cpv_diversity: 0, geo_footprint: 0, top_buyer_share: 0, growth_yoy: null, modification_count: 0 };
  }
  const recent = Number(row.yoy_recent);
  const prior = Number(row.yoy_prior);
  const growth_yoy = prior > 0 ? (recent - prior) / prior : null;
  return {
    cpv_diversity: Number(row.cpv_diversity) || 0,
    geo_footprint: Number(row.geo_footprint) || 0,
    top_buyer_share: Number(row.top_buyer_share) || 0,
    growth_yoy,
    modification_count: Number(row.modification_count) || 0,
  };
}


export interface NetworkNode {
  cui: string;
  name: string;
  contracts: number;
  total_value: number;
  side: 'authority' | 'supplier';
  others?: { cui: string; name: string; contracts: number; total_value: number }[];
}

export interface NetworkData {
  center: NetworkNode;
  neighbors: NetworkNode[];
}

/** Authority's network: top suppliers + each supplier's other top authorities */
export async function getAuthorityNetwork(cui: string, limit = 20): Promise<NetworkData> {
  // Center authority
  const centerResult = await query<any>(`
    SELECT
      $1::text AS cui,
      MIN(authority_name) AS name,
      COUNT(*)::int AS contracts,
      COALESCE(SUM(awarded_value), 0)::numeric AS total_value
    FROM seap.announcements
    WHERE authority_cui = $1
  `, [cui]);

  const top = await query<any>(`
    SELECT
      a.supplier_cui AS cui,
      regexp_replace(upper(a.supplier_cui), '(^RO)|\\s+', '', 'g') AS norm_cui,
      MIN(a.supplier_name) AS name,
      COUNT(*)::int AS contracts,
      COALESCE(SUM(a.awarded_value), 0)::numeric AS total_value
    FROM seap.announcements a
    WHERE a.authority_cui = $1
      AND a.supplier_cui IS NOT NULL
    GROUP BY a.supplier_cui
    ORDER BY total_value DESC NULLS LAST
    LIMIT $2
  `, [cui, limit]);

  // For each supplier, fetch top 4 OTHER authorities they win from
  const neighbors: NetworkNode[] = [];
  for (const sup of top.rows) {
    const others = await query<any>(`
      SELECT
        a.authority_cui AS cui,
        MIN(a.authority_name) AS name,
        COUNT(*)::int AS contracts,
        COALESCE(SUM(a.awarded_value), 0)::numeric AS total_value
      FROM seap.announcements a
      WHERE (a.supplier_cui = $1 OR a.supplier_cui = 'RO ' || $2 OR a.supplier_cui = 'RO' || $2)
        AND a.authority_cui IS NOT NULL
        AND a.authority_cui != $3
      GROUP BY a.authority_cui
      ORDER BY total_value DESC NULLS LAST
      LIMIT 4
    `, [sup.cui, sup.norm_cui, cui]);

    neighbors.push({
      cui: sup.norm_cui,
      name: sup.name,
      contracts: Number(sup.contracts),
      total_value: Number(sup.total_value),
      side: 'supplier',
      others: others.rows.map((o) => ({
        cui: o.cui,
        name: o.name,
        contracts: Number(o.contracts),
        total_value: Number(o.total_value),
      })),
    });
  }

  return {
    center: {
      cui,
      name: centerResult.rows[0]?.name || '',
      contracts: Number(centerResult.rows[0]?.contracts || 0),
      total_value: Number(centerResult.rows[0]?.total_value || 0),
      side: 'authority',
    },
    neighbors,
  };
}

/** Supplier's network: top buyers + each buyer's other top suppliers */
export async function getSupplierNetwork(cui: string, limit = 20): Promise<NetworkData> {
  const centerResult = await query<any>(`
    SELECT
      $1::text AS cui,
      MIN(supplier_name) AS name,
      COUNT(*)::int AS contracts,
      COALESCE(SUM(awarded_value), 0)::numeric AS total_value
    FROM seap.announcements
    WHERE supplier_cui = $1 OR supplier_cui = 'RO ' || $1 OR supplier_cui = 'RO' || $1
  `, [cui]);

  const top = await query<any>(`
    SELECT
      a.authority_cui AS cui,
      MIN(a.authority_name) AS name,
      COUNT(*)::int AS contracts,
      COALESCE(SUM(a.awarded_value), 0)::numeric AS total_value
    FROM seap.announcements a
    WHERE (a.supplier_cui = $1 OR a.supplier_cui = 'RO ' || $1 OR a.supplier_cui = 'RO' || $1)
      AND a.authority_cui IS NOT NULL
    GROUP BY a.authority_cui
    ORDER BY total_value DESC NULLS LAST
    LIMIT $2
  `, [cui, limit]);

  const neighbors: NetworkNode[] = [];
  for (const auth of top.rows) {
    const others = await query<any>(`
      SELECT
        regexp_replace(upper(a.supplier_cui), '(^RO)|\\s+', '', 'g') AS cui,
        MIN(a.supplier_name) AS name,
        COUNT(*)::int AS contracts,
        COALESCE(SUM(a.awarded_value), 0)::numeric AS total_value
      FROM seap.announcements a
      WHERE a.authority_cui = $1
        AND a.supplier_cui IS NOT NULL
        AND regexp_replace(upper(a.supplier_cui), '(^RO)|\\s+', '', 'g') != $2
      GROUP BY 1
      ORDER BY total_value DESC NULLS LAST
      LIMIT 4
    `, [auth.cui, cui]);

    neighbors.push({
      cui: auth.cui,
      name: auth.name,
      contracts: Number(auth.contracts),
      total_value: Number(auth.total_value),
      side: 'authority',
      others: others.rows.map((o) => ({
        cui: o.cui,
        name: o.name,
        contracts: Number(o.contracts),
        total_value: Number(o.total_value),
      })),
    });
  }

  return {
    center: {
      cui,
      name: centerResult.rows[0]?.name || '',
      contracts: Number(centerResult.rows[0]?.contracts || 0),
      total_value: Number(centerResult.rows[0]?.total_value || 0),
      side: 'supplier',
    },
    neighbors,
  };
}

// Authority KPI strip
export async function getAuthorityKpi(cui: string): Promise<AuthorityKpi> {
  const result = await query<{
    total_value: number;
    direct_value: number;
    framework_value: number;
    modification_count: number;
    cpv_diversity: number;
    top_supplier_share: number;
    hhi_suppliers: number;
    q4_value_last_full_year: number;
    yearly_value_last_full_year: number;
  }>(`
    WITH base AS (
      SELECT * FROM seap.announcements WHERE authority_cui = $1
    ),
    aggregates AS (
      SELECT
        coalesce(sum(awarded_value), 0)::numeric AS total_value,
        coalesce(sum(awarded_value) FILTER (WHERE type = 'da'), 0)::numeric AS direct_value,
        coalesce(sum(awarded_value) FILTER (WHERE framework_agreement = true), 0)::numeric AS framework_value,
        count(*) FILTER (WHERE modification_desc IS NOT NULL)::int AS modification_count
      FROM base
    ),
    cpv AS (
      SELECT cpv_division, count(*)::numeric AS n
      FROM base WHERE cpv_division IS NOT NULL
      GROUP BY cpv_division
    ),
    cpv_total AS (SELECT sum(n) AS tot FROM cpv),
    diversity AS (
      SELECT
        COALESCE(-sum((n / NULLIF((SELECT tot FROM cpv_total), 0)) * ln((n / NULLIF((SELECT tot FROM cpv_total), 0)))), 0) AS h
      FROM cpv
    ),
    suppliers AS (
      SELECT supplier_cui, sum(awarded_value) AS total
      FROM base WHERE supplier_cui IS NOT NULL AND awarded_value IS NOT NULL
      GROUP BY supplier_cui
    ),
    supplier_shares AS (
      SELECT
        total,
        total / NULLIF(sum(total) OVER (), 0) AS ratio
      FROM suppliers
    ),
    top_sup AS (
      SELECT
        coalesce(max(ratio), 0) AS share,
        coalesce(sum(power(ratio, 2)) * 10000, 0) AS hhi
      FROM supplier_shares
    ),
    last_full_year AS (
      SELECT extract(year from now()) - 1 AS yr
    ),
    quarterly AS (
      SELECT
        coalesce(sum(awarded_value) FILTER (WHERE extract(quarter from publication_date) = 4), 0)::numeric AS q4,
        coalesce(sum(awarded_value), 0)::numeric AS yearly
      FROM base
      WHERE extract(year from publication_date) = (SELECT yr FROM last_full_year)
    )
    SELECT
      aggregates.total_value,
      aggregates.direct_value,
      aggregates.framework_value,
      aggregates.modification_count,
      diversity.h::float8 AS cpv_diversity,
      top_sup.share::float8 AS top_supplier_share,
      top_sup.hhi::float8 AS hhi_suppliers,
      quarterly.q4 AS q4_value_last_full_year,
      quarterly.yearly AS yearly_value_last_full_year
    FROM aggregates, diversity, top_sup, quarterly
  `, [cui]);

  const row = result.rows[0];
  if (!row) {
    return {
      direct_pct: 0, framework_pct: 0, top_supplier_share: 0, hhi_suppliers: 0,
      modification_count: 0, cpv_diversity: 0, q4_spike: null,
    };
  }
  const total = Number(row.total_value) || 0;
  const direct_pct = total > 0 ? Number(row.direct_value) / total : 0;
  const framework_pct = total > 0 ? Number(row.framework_value) / total : 0;
  const yearly = Number(row.yearly_value_last_full_year) || 0;
  const q4 = Number(row.q4_value_last_full_year) || 0;
  // q4_spike = q4 / (yearly_avg_per_quarter); 1.0 = even, >1.5 spike, >2 strong
  const q4_spike = yearly > 0 ? q4 / (yearly / 4) : null;

  return {
    direct_pct,
    framework_pct,
    top_supplier_share: Number(row.top_supplier_share) || 0,
    hhi_suppliers: Number(row.hhi_suppliers) || 0,
    modification_count: Number(row.modification_count) || 0,
    cpv_diversity: Number(row.cpv_diversity) || 0,
    q4_spike,
  };
}

// ─────────────────────────────────────────────────────────────
// EU funds beneficiary status — does this CUI also appear in
// fonduri.beneficiar_anunt (private side EU fundings)? Pulled to
// surface the cross-source signal on the firma profile.
// ─────────────────────────────────────────────────────────────

export interface EuFundsAnuntPreview {
  id: number;
  titlu: string | null;
  data_publicare: string | null;
  buget_lei: number | null;
  smis_proiect_code: string | null;
  smis_proiect_id: number | null;
  program_op_cod: string | null;
}

export interface EuFundsProgramRow {
  program_op_cod: string;
  program_op_text: string | null;
  proiecte: number;
}

export interface EuFundsBeneficiarStatus {
  anunturi: number;
  proiecte: number;
  buget_total: number;
  recent: EuFundsAnuntPreview[];
  programs: EuFundsProgramRow[];
}

export async function getEuFundsBeneficiarStatus(cui: string): Promise<EuFundsBeneficiarStatus | null> {
  const aggR = await query<{ anunturi: number; proiecte: number; buget: number }>(
    `SELECT
       count(*)::int AS anunturi,
       count(DISTINCT b.smis_proiect_id)::int AS proiecte,
       coalesce((SELECT sum(l.buget_lei)
                 FROM fonduri.beneficiar_anunt_lot l
                 JOIN fonduri.beneficiar_anunt b2 ON b2.id = l.anunt_id
                 WHERE b2.cui = $1), 0)::numeric AS buget
     FROM fonduri.beneficiar_anunt b
     WHERE b.cui = $1`,
    [cui],
  );
  const a = aggR.rows[0];
  if (!a || Number(a.anunturi) === 0) return null;

  const recentR = await query<EuFundsAnuntPreview>(
    `SELECT b.id,
            coalesce(b.titlu, b.smis_proiect_name) AS titlu,
            to_char(b.data_publicare, 'YYYY-MM-DD') AS data_publicare,
            (SELECT sum(buget_lei) FROM fonduri.beneficiar_anunt_lot WHERE anunt_id = b.id)::numeric AS buget_lei,
            b.smis_proiect_code,
            b.smis_proiect_id,
            p.program_op_cod
     FROM fonduri.beneficiar_anunt b
     LEFT JOIN fonduri.beneficiar_proiect p ON p.id = b.smis_proiect_id
     WHERE b.cui = $1
     ORDER BY b.data_publicare DESC NULLS LAST, b.id DESC
     LIMIT 10`,
    [cui],
  );

  const programsR = await query<EuFundsProgramRow>(
    `SELECT p.program_op_cod,
            min(p.program_op_text) AS program_op_text,
            count(DISTINCT p.id)::int AS proiecte
     FROM fonduri.beneficiar_anunt b
     JOIN fonduri.beneficiar_proiect p ON p.id = b.smis_proiect_id
     WHERE b.cui = $1
       AND p.program_op_cod IS NOT NULL
     GROUP BY 1
     ORDER BY proiecte DESC NULLS LAST
     LIMIT 5`,
    [cui],
  );

  return {
    anunturi: Number(a.anunturi),
    proiecte: Number(a.proiecte),
    buget_total: Number(a.buget) || 0,
    recent: recentR.rows.map(r => ({
      ...r,
      buget_lei: r.buget_lei == null ? null : Number(r.buget_lei),
    })),
    programs: programsR.rows.map(r => ({
      ...r,
      proiecte: Number(r.proiecte),
    })),
  };
}

// ─────────────────────────────────────────────────────────────────────────
// RegAS — Registrul Ajutoarelor de Stat (Consiliul Concurenței).
// Per-CUI rollup of state aid received: count, total RON, distinct
// finantatori (ministers/agencies), date range, recent records.

export interface RegasAjutorPreview {
  data_acordare: string | null;
  denumire_masura: string;
  referinta_masura: string | null;
  finantator: string | null;
  instrument_acordare: string | null;
  ajutor_acordat_subcategorie: number | null;
  intensitate: number | null;
  pdf_masura: string | null;
  id_masura: number;
}

export interface RegasStatus {
  nr_ajutoare: number;
  total_ron: number;
  nr_masuri: number;
  nr_finantatori: number;
  prima_acordare: string | null;
  ultima_acordare: string | null;
  finantatori: string[];
  instrumente: string[];
  recent: RegasAjutorPreview[];
}

export async function getRegasStatus(cui: string): Promise<RegasStatus | null> {
  const aggR = await query<any>(
    `SELECT
       count(*)::int AS nr_ajutoare,
       coalesce(sum(ajutor_acordat_subcategorie), 0)::numeric AS total_ron,
       count(DISTINCT id_masura)::int AS nr_masuri,
       count(DISTINCT finantator)::int AS nr_finantatori,
       to_char(min(data_acordare), 'YYYY-MM-DD') AS prima_acordare,
       to_char(max(data_acordare), 'YYYY-MM-DD') AS ultima_acordare,
       (array_agg(DISTINCT finantator) FILTER (WHERE finantator IS NOT NULL)) AS finantatori,
       (array_agg(DISTINCT instrument_acordare) FILTER (WHERE instrument_acordare IS NOT NULL)) AS instrumente
     FROM regas.ajutoare
     WHERE cui = $1`,
    [cui],
  );
  const a = aggR.rows[0];
  if (!a || Number(a.nr_ajutoare) === 0) return null;

  const recentR = await query<RegasAjutorPreview>(
    `SELECT to_char(data_acordare, 'YYYY-MM-DD') AS data_acordare,
            denumire_masura,
            referinta_masura,
            finantator,
            instrument_acordare,
            ajutor_acordat_subcategorie::numeric,
            intensitate::numeric,
            pdf_masura,
            id_masura
     FROM regas.ajutoare
     WHERE cui = $1
     ORDER BY data_acordare DESC NULLS LAST, ajutor_acordat_subcategorie DESC NULLS LAST
     LIMIT 10`,
    [cui],
  );

  return {
    nr_ajutoare: Number(a.nr_ajutoare),
    total_ron: Number(a.total_ron) || 0,
    nr_masuri: Number(a.nr_masuri),
    nr_finantatori: Number(a.nr_finantatori),
    prima_acordare: a.prima_acordare,
    ultima_acordare: a.ultima_acordare,
    finantatori: a.finantatori || [],
    instrumente: a.instrumente || [],
    recent: recentR.rows.map(r => ({
      ...r,
      ajutor_acordat_subcategorie: r.ajutor_acordat_subcategorie == null ? null : Number(r.ajutor_acordat_subcategorie),
      intensitate: r.intensitate == null ? null : Number(r.intensitate),
    })),
  };
}

// ─────────────────────────────────────────────────────────────
// AEP electoral donations status — does this CUI appear in
// aep.donatii_pj as a donor to political parties?
// ─────────────────────────────────────────────────────────────

export interface AepDonationPreview {
  partid_id: string | null;
  an: number;
  suma_lei: number;
  data_donatie: string | null;
}

export interface AepStatus {
  nr_donatii: number;
  total_lei: number;
  partide: string[];
  prima_an: number | null;
  ultima_an: number | null;
  recent: AepDonationPreview[];
}

export async function getAepStatus(cui: string): Promise<AepStatus | null> {
  const normCui = cui.replace(/^RO\s*/i, '').trim();
  const aggR = await query<any>(
    `SELECT count(*)::int AS nr_donatii,
            sum(suma_lei)::numeric AS total_lei,
            array_agg(DISTINCT partid_id ORDER BY partid_id) FILTER (WHERE partid_id IS NOT NULL) AS partide,
            min(an)::int AS prima_an,
            max(an)::int AS ultima_an
     FROM aep.donatii_pj
     WHERE donator_cui = $1`,
    [normCui],
  );
  const a = aggR.rows[0];
  if (!a || Number(a.nr_donatii) === 0) return null;

  const recentR = await query<AepDonationPreview>(
    `SELECT partid_id, an, suma_lei::numeric AS suma_lei,
            to_char(data_donatie, 'YYYY-MM-DD') AS data_donatie
     FROM aep.donatii_pj
     WHERE donator_cui = $1
     ORDER BY an DESC, suma_lei DESC NULLS LAST
     LIMIT 10`,
    [normCui],
  );

  return {
    nr_donatii: Number(a.nr_donatii),
    total_lei: Number(a.total_lei) || 0,
    partide: a.partide || [],
    prima_an: a.prima_an,
    ultima_an: a.ultima_an,
    recent: recentR.rows.map(r => ({ ...r, suma_lei: Number(r.suma_lei) })),
  };
}

// ─────────────────────────────────────────────────────────────
// ANAF tax-debt status — does this CUI appear on the public
// quarterly list of firms with outstanding tax debts?
// ─────────────────────────────────────────────────────────────

export interface AnafDebtStatus {
  publication_date: string;
  period_label: string;
  debt_total: number;
  debt_principal: number | null;
  debt_penalty: number | null;
  debtor_category: string | null;
}

export async function getAnafDebtStatus(cui: string): Promise<AnafDebtStatus | null> {
  const normCui = cui.replace(/^RO\s*/i, '').trim();
  const r = await query<AnafDebtStatus>(
    `SELECT to_char(publication_date, 'YYYY-MM-DD') AS publication_date,
            period_label,
            debt_total::numeric, debt_principal::numeric, debt_penalty::numeric,
            debtor_category
     FROM anaf.datornici_latest
     WHERE cui = $1`,
    [normCui],
  );
  if (!r.rows[0]) return null;
  const row = r.rows[0];
  return {
    ...row,
    debt_total: Number(row.debt_total) || 0,
    debt_principal: row.debt_principal == null ? null : Number(row.debt_principal),
    debt_penalty: row.debt_penalty == null ? null : Number(row.debt_penalty),
  };
}
