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

// ─────────────────────────────────────────────────────────────
// Shared row types for one-click dashboards
// ─────────────────────────────────────────────────────────────

export interface DashboardContractRow {
  id: number;
  ref_number: string | null;
  type: string;
  title: string | null;
  authority_name: string | null;
  authority_cui: string | null;
  authority_county: string | null;
  supplier_name: string | null;
  supplier_cui: 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;
  procedure_type: string | null;
  has_lots: boolean | null;
  lots_count: number | null;
  framework_agreement: boolean | null;
  source: string;
  eu_funded: string | null;
  eu_program: string | null;
}

export interface SupplierRankRow {
  supplier_cui: string;
  supplier_name: string | null;
  contracts_won: number;
  total_awarded: number | null;
  distinct_clients: number;
  avg_awarded: number | null;
  most_recent: string | null;
}

export interface TopAuthorityRow {
  authority_cui: string;
  authority_name: string | null;
  county_code: string | null;
  total_awarded: number | null;
  awarded_count: number;
}

export interface TodayActivity {
  notices_count: number;
  awarded_count: number;
  total_awarded: number;
  total_estimated: number;
  distinct_authorities: number;
  top_authority: { cui: string | null; name: string | null; total: number } | null;
  top_cpv: { division: string | null; name: string | null; emoji: string | null; count: number } | null;
}

export interface DailyComparison {
  today_count: number;
  today_awarded: number;
  avg_count_30d: number;
  avg_awarded_30d: number;
  count_pct: number;       // signed % vs avg
  awarded_pct: number;     // signed % vs avg
  using_24h: boolean;      // true if no rows for "today" so we used last 24h
}

export interface EuProgramRow {
  program: string;
  contracts: number;
  total_awarded: number;
}

export interface EuBeneficiaryRow {
  cui: string;
  name: string | null;
  county: string | null;
  contracts: number;
  total_awarded: number;
}

export interface EuFundedSummary {
  total_awarded: number;
  contracts: number;
  distinct_authorities: number;
  distinct_suppliers: number;
  programs: EuProgramRow[];
  top_authorities: EuBeneficiaryRow[];
  top_suppliers: EuBeneficiaryRow[];
}

// fonduri.beneficiar_anunt summary — companies that *receive* EU money and
// must run their own procurements (private side, separate from SEAP).
export interface PrivateEuTopFirm {
  cui: string | null;
  name: string;
  county: string | null;
  anunturi: number;
  buget_total: number;
}

export interface PrivateEuTopProiect {
  id: number;
  smis_code: string | null;
  titlu: string | null;
  beneficiar_name: string | null;
  beneficiar_cui: string | null;
  program_op_cod: string | null;
  anunturi: number;
  buget_total: number;
  // We don't yet have a /proiect page; link via the largest anunt
  // of the project instead (same beneficiar/project context, just one of N).
  sample_anunt_id: number | null;
}

export interface PrivateEuCountyRow {
  judet: string;
  anunturi: number;
  buget_total: number;
}

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

export interface PrivateEuSummary {
  anunturi_total: number;
  proiecte_total: number;
  firme_matched: number;
  buget_total: number;
  proiecte_scraped: number;       // current beneficiar_proiect rows (scraping in progress)
  proiecte_target: number;        // distinct from beneficiar_anunt (the work list)
  top_firme: PrivateEuTopFirm[];
  top_proiecte: PrivateEuTopProiect[];
  top_judete: PrivateEuCountyRow[];
  programs: PrivateEuProgramRow[]; // populated only when beneficiar_proiect has rows
}


// ─────────────────────────────────────────────────────────────
// 1. Today: live activity + the 50 most recent notices
// ─────────────────────────────────────────────────────────────

const AWARDED_TYPES = `'ca_notice','rfq_notice','contract','atribuire_fara','da','ted_notice'`;

export async function getTodayActivity(): Promise<{ activity: TodayActivity; using_24h: boolean }> {
  // Try TODAY first
  const todayAgg = await query<{
    notices: number; awarded: number; total_awarded: number;
    total_estimated: number; distinct_auth: number;
  }>(`
    SELECT
      count(*)::int AS notices,
      count(*) FILTER (WHERE type IN (${AWARDED_TYPES}))::int AS awarded,
      coalesce(sum(awarded_value) FILTER (WHERE awarded_value IS NOT NULL), 0)::numeric AS total_awarded,
      coalesce(sum(estimated_value) FILTER (WHERE estimated_value IS NOT NULL), 0)::numeric AS total_estimated,
      count(DISTINCT authority_cui)::int AS distinct_auth
    FROM seap.announcements
    WHERE publication_date::date = current_date
  `);

  let using_24h = false;
  let where = `publication_date::date = current_date`;
  if ((todayAgg.rows[0]?.notices || 0) === 0) {
    using_24h = true;
    where = `publication_date >= now() - interval '24 hours'`;
  }

  const agg = using_24h
    ? (await query<any>(`
        SELECT
          count(*)::int AS notices,
          count(*) FILTER (WHERE type IN (${AWARDED_TYPES}))::int AS awarded,
          coalesce(sum(awarded_value) FILTER (WHERE awarded_value IS NOT NULL), 0)::numeric AS total_awarded,
          coalesce(sum(estimated_value) FILTER (WHERE estimated_value IS NOT NULL), 0)::numeric AS total_estimated,
          count(DISTINCT authority_cui)::int AS distinct_auth
        FROM seap.announcements
        WHERE ${where}
      `)).rows[0]
    : todayAgg.rows[0];

  // Top authority for today (or last 24h)
  const topAuth = await query<{ cui: string; name: string; total: number }>(`
    SELECT
      authority_cui AS cui,
      MIN(authority_name) AS name,
      coalesce(sum(awarded_value), 0)::numeric AS total
    FROM seap.announcements
    WHERE ${where} AND authority_cui IS NOT NULL
    GROUP BY authority_cui
    ORDER BY total DESC NULLS LAST, count(*) DESC
    LIMIT 1
  `);

  // Top CPV division for today
  const topCpv = await query<{ division: string; name: string; emoji: string; cnt: number }>(`
    SELECT
      a.cpv_division AS division,
      MIN(c.name_ro) AS name,
      MIN(c.emoji) AS emoji,
      count(*)::int AS cnt
    FROM seap.announcements a
    LEFT JOIN seap.cpv_codes c ON c.code = a.cpv_division
    WHERE ${where} AND a.cpv_division IS NOT NULL
    GROUP BY a.cpv_division
    ORDER BY cnt DESC
    LIMIT 1
  `);

  return {
    using_24h,
    activity: {
      notices_count: Number(agg?.notices || 0),
      awarded_count: Number(agg?.awarded || 0),
      total_awarded: Number(agg?.total_awarded || 0),
      total_estimated: Number(agg?.total_estimated || 0),
      distinct_authorities: Number(agg?.distinct_auth || 0),
      top_authority: topAuth.rows[0]
        ? { cui: topAuth.rows[0].cui, name: topAuth.rows[0].name, total: Number(topAuth.rows[0].total) }
        : null,
      top_cpv: topCpv.rows[0]
        ? { division: topCpv.rows[0].division, name: topCpv.rows[0].name, emoji: topCpv.rows[0].emoji, count: Number(topCpv.rows[0].cnt) }
        : null,
    },
  };
}

export async function getTodayFeed(limit = 50): Promise<DashboardContractRow[]> {
  // Same fallback logic as activity: today first, last 24h if empty.
  const todayCount = await query<{ n: number }>(`
    SELECT count(*)::int AS n
    FROM seap.announcements
    WHERE publication_date::date = current_date
  `);
  const where = (todayCount.rows[0]?.n || 0) > 0
    ? `a.publication_date::date = current_date`
    : `a.publication_date >= now() - interval '24 hours'`;

  const result = await query<DashboardContractRow>(`
    SELECT
      a.id, a.ref_number, a.type, a.title,
      a.authority_name, a.authority_cui,
      cl.county AS authority_county,
      a.supplier_name, a.supplier_cui,
      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.procedure_type,
      a.contract_has_lots AS has_lots, a.lots_count,
      a.framework_agreement,
      a.source, a.eu_funded, a.eu_program
    FROM seap.announcements a
    LEFT JOIN seap.cpv_codes c ON c.code = a.cpv_division
    LEFT JOIN seap.cui_location cl ON cl.cui = a.authority_cui
    WHERE ${where}
    ORDER BY a.publication_date DESC NULLS LAST, a.id DESC
    LIMIT $1
  `, [limit]);
  return result.rows;
}

export async function getDailyComparison(): Promise<DailyComparison> {
  // Today aggregate (or last 24h fallback)
  const todayRow = await query<{ cnt: number; awarded: number }>(`
    SELECT
      count(*)::int AS cnt,
      coalesce(sum(awarded_value) FILTER (WHERE awarded_value IS NOT NULL), 0)::numeric AS awarded
    FROM seap.announcements
    WHERE publication_date::date = current_date
  `);
  let using_24h = false;
  let today_count = Number(todayRow.rows[0]?.cnt || 0);
  let today_awarded = Number(todayRow.rows[0]?.awarded || 0);
  if (today_count === 0) {
    using_24h = true;
    const r = await query<{ cnt: number; awarded: number }>(`
      SELECT
        count(*)::int AS cnt,
        coalesce(sum(awarded_value) FILTER (WHERE awarded_value IS NOT NULL), 0)::numeric AS awarded
      FROM seap.announcements
      WHERE publication_date >= now() - interval '24 hours'
    `);
    today_count = Number(r.rows[0]?.cnt || 0);
    today_awarded = Number(r.rows[0]?.awarded || 0);
  }

  // 30-day average (excluding today). Per-day, then averaged.
  const avg = await query<{ avg_cnt: number; avg_awarded: number }>(`
    SELECT
      coalesce(avg(daily_cnt), 0)::numeric AS avg_cnt,
      coalesce(avg(daily_awarded), 0)::numeric AS avg_awarded
    FROM (
      SELECT
        publication_date::date AS day,
        count(*) AS daily_cnt,
        coalesce(sum(awarded_value) FILTER (WHERE awarded_value IS NOT NULL), 0) AS daily_awarded
      FROM seap.announcements
      WHERE publication_date::date >= current_date - interval '30 days'
        AND publication_date::date < current_date
      GROUP BY 1
    ) d
  `);
  const avg_cnt = Number(avg.rows[0]?.avg_cnt || 0);
  const avg_awarded = Number(avg.rows[0]?.avg_awarded || 0);

  const pct = (now: number, base: number) =>
    base <= 0 ? 0 : Math.round(((now - base) / base) * 100);

  return {
    today_count,
    today_awarded,
    avg_count_30d: Math.round(avg_cnt),
    avg_awarded_30d: Math.round(avg_awarded),
    count_pct: pct(today_count, avg_cnt),
    awarded_pct: pct(today_awarded, avg_awarded),
    using_24h,
  };
}


// ─────────────────────────────────────────────────────────────
// 2. Top contracts (last 12 months, awarded)
// ─────────────────────────────────────────────────────────────

export interface TopContractsFilter {
  eu_only?: boolean;
  has_lots?: boolean;
  framework?: boolean;
}

export async function getTopContractsRecent(
  limit = 100,
  filter: TopContractsFilter = {},
): Promise<DashboardContractRow[]> {
  const conds: string[] = [
    `a.publication_date >= now() - interval '12 months'`,
    `a.awarded_value IS NOT NULL`,
    `a.awarded_value > 0`,
    `a.type IN (${AWARDED_TYPES})`,
  ];
  if (filter.eu_only) conds.push(`a.eu_funded ILIKE 'da%'`);
  if (filter.has_lots) conds.push(`a.contract_has_lots = true`);
  if (filter.framework) conds.push(`a.framework_agreement = true`);

  const result = await query<DashboardContractRow>(`
    SELECT
      a.id, a.ref_number, a.type, a.title,
      a.authority_name, a.authority_cui,
      cl.county AS authority_county,
      a.supplier_name, a.supplier_cui,
      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.procedure_type,
      a.contract_has_lots AS has_lots, a.lots_count,
      a.framework_agreement,
      a.source, a.eu_funded, a.eu_program
    FROM seap.announcements a
    LEFT JOIN seap.cpv_codes c ON c.code = a.cpv_division
    LEFT JOIN seap.cui_location cl ON cl.cui = a.authority_cui
    WHERE ${conds.join(' AND ')}
    ORDER BY a.awarded_value DESC NULLS LAST
    LIMIT $1
  `, [limit]);
  return result.rows;
}

export interface TopContractsStats {
  total_awarded: number;
  contracts: number;
  avg_value: number;
  median_value: number;
}

export async function getTopContractsStats(
  filter: TopContractsFilter = {},
): Promise<TopContractsStats> {
  const conds: string[] = [
    `publication_date >= now() - interval '12 months'`,
    `awarded_value IS NOT NULL`,
    `awarded_value > 0`,
    `type IN (${AWARDED_TYPES})`,
  ];
  if (filter.eu_only) conds.push(`eu_funded ILIKE 'da%'`);
  if (filter.has_lots) conds.push(`contract_has_lots = true`);
  if (filter.framework) conds.push(`framework_agreement = true`);

  const r = await query<{ total: number; cnt: number; avg: number; median: number }>(`
    SELECT
      coalesce(sum(awarded_value), 0)::numeric AS total,
      count(*)::int AS cnt,
      coalesce(avg(awarded_value), 0)::numeric AS avg,
      coalesce(percentile_cont(0.5) WITHIN GROUP (ORDER BY awarded_value), 0)::numeric AS median
    FROM seap.announcements
    WHERE ${conds.join(' AND ')}
  `);
  return {
    total_awarded: Number(r.rows[0]?.total || 0),
    contracts: Number(r.rows[0]?.cnt || 0),
    avg_value: Number(r.rows[0]?.avg || 0),
    median_value: Number(r.rows[0]?.median || 0),
  };
}


// ─────────────────────────────────────────────────────────────
// 3. Top suppliers ranked
// ─────────────────────────────────────────────────────────────

export interface TopSuppliersStats {
  distinct_suppliers: number;
  top_supplier_total: number;
  median_total: number;
}

export async function getTopSuppliersRanked(
  limit = 100,
  sortBy: 'total_awarded' | 'contracts_won' = 'total_awarded',
): Promise<SupplierRankRow[]> {
  const orderCol = sortBy === 'contracts_won' ? 'contracts_won' : 'total_awarded';
  const r = await query<SupplierRankRow>(`
    SELECT
      supplier_cui,
      supplier_name,
      contracts_won::int AS contracts_won,
      total_awarded,
      distinct_clients::int AS distinct_clients,
      avg_awarded,
      most_recent
    FROM seap.mv_top_suppliers
    WHERE supplier_cui IS NOT NULL
    ORDER BY ${orderCol} DESC NULLS LAST
    LIMIT $1
  `, [limit]);
  return r.rows;
}

export async function getTopSuppliersStats(): Promise<TopSuppliersStats> {
  const r = await query<{ distinct: number; top_total: number; median_total: number }>(`
    SELECT
      count(*)::int AS distinct,
      coalesce(max(total_awarded), 0)::numeric AS top_total,
      coalesce(percentile_cont(0.5) WITHIN GROUP (ORDER BY total_awarded), 0)::numeric AS median_total
    FROM seap.mv_top_suppliers
    WHERE total_awarded IS NOT NULL AND total_awarded > 0
  `);
  return {
    distinct_suppliers: Number(r.rows[0]?.distinct || 0),
    top_supplier_total: Number(r.rows[0]?.top_total || 0),
    median_total: Number(r.rows[0]?.median_total || 0),
  };
}


// ─────────────────────────────────────────────────────────────
// 4. EU funded — summary, programs, top beneficiaries, top contracts
// ─────────────────────────────────────────────────────────────

export async function getEUFundedSummary(): Promise<EuFundedSummary> {
  // No-alias version (used in queries against seap.announcements directly)
  const baseWhere = `
    publication_date >= now() - interval '12 months'
    AND eu_funded ILIKE 'da%'
    AND awarded_value IS NOT NULL
    AND awarded_value > 0
    AND type IN (${AWARDED_TYPES})
  `;
  // Aliased version (for queries that JOIN, where columns must be qualified as a.*)
  const baseWhereA = `
    a.publication_date >= now() - interval '12 months'
    AND a.eu_funded ILIKE 'da%'
    AND a.awarded_value IS NOT NULL
    AND a.awarded_value > 0
    AND a.type IN (${AWARDED_TYPES})
  `;

  const agg = await query<{
    total: number; cnt: number; auths: number; supps: number;
  }>(`
    SELECT
      coalesce(sum(awarded_value), 0)::numeric AS total,
      count(*)::int AS cnt,
      count(DISTINCT authority_cui)::int AS auths,
      count(DISTINCT supplier_cui)::int AS supps
    FROM seap.announcements
    WHERE ${baseWhere}
  `);

  const programs = await query<{ program: string; contracts: number; total: number }>(`
    SELECT
      coalesce(NULLIF(trim(eu_program), ''), '(neprecizat)') AS program,
      count(*)::int AS contracts,
      coalesce(sum(awarded_value), 0)::numeric AS total
    FROM seap.announcements
    WHERE ${baseWhere}
    GROUP BY 1
    ORDER BY total DESC NULLS LAST
    LIMIT 12
  `);

  const topAuth = await query<EuBeneficiaryRow>(`
    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_awarded
    FROM seap.announcements a
    LEFT JOIN seap.cui_location cl ON cl.cui = a.authority_cui
    WHERE ${baseWhereA}
      AND a.authority_cui IS NOT NULL
    GROUP BY a.authority_cui
    ORDER BY total_awarded DESC NULLS LAST
    LIMIT 10
  `);

  const topSupp = await query<EuBeneficiaryRow>(`
    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_awarded
    FROM seap.announcements a
    LEFT JOIN seap.cui_location cl ON cl.cui = regexp_replace(a.supplier_cui, '^RO\\s*', '', 'i')
    WHERE ${baseWhereA}
      AND a.supplier_cui IS NOT NULL
    GROUP BY a.supplier_cui
    ORDER BY total_awarded DESC NULLS LAST
    LIMIT 10
  `);

  return {
    total_awarded: Number(agg.rows[0]?.total || 0),
    contracts: Number(agg.rows[0]?.cnt || 0),
    distinct_authorities: Number(agg.rows[0]?.auths || 0),
    distinct_suppliers: Number(agg.rows[0]?.supps || 0),
    programs: programs.rows.map(r => ({
      program: r.program,
      contracts: Number(r.contracts),
      total_awarded: Number(r.total),
    })),
    top_authorities: topAuth.rows.map(r => ({ ...r, contracts: Number(r.contracts), total_awarded: Number(r.total_awarded) })),
    top_suppliers: topSupp.rows.map(r => ({ ...r, contracts: Number(r.contracts), total_awarded: Number(r.total_awarded) })),
  };
}

export async function getPrivateEuSummary(): Promise<PrivateEuSummary> {
  // Aggregate stats in one round-trip for the strip.
  const aggR = await query<{
    anunturi: number; proiecte: number; firme: number; buget: number;
    scraped: number; target: number;
  }>(`
    SELECT
      (SELECT count(*)::int FROM fonduri.beneficiar_anunt) AS anunturi,
      (SELECT count(DISTINCT smis_proiect_id)::int FROM fonduri.beneficiar_anunt
        WHERE smis_proiect_id IS NOT NULL) AS proiecte,
      (SELECT count(DISTINCT cui)::int FROM fonduri.beneficiar_anunt
        WHERE cui IS NOT NULL) AS firme,
      coalesce((SELECT sum(buget_lei) FROM fonduri.beneficiar_anunt_lot), 0)::numeric AS buget,
      (SELECT count(*)::int FROM fonduri.beneficiar_proiect) AS scraped,
      (SELECT count(DISTINCT smis_proiect_id)::int FROM fonduri.beneficiar_anunt
        WHERE smis_proiect_id IS NOT NULL) AS target
  `);
  const a = aggR.rows[0];

  const topFirme = await query<PrivateEuTopFirm>(`
    SELECT
      coalesce(b.cui, '') AS cui,
      coalesce(e.name, b.beneficiar_name) AS name,
      coalesce(e.adr_judet, b.beneficiar_judet) AS county,
      count(*)::int AS anunturi,
      coalesce(sum(l.buget_lei), 0)::numeric AS buget_total
    FROM fonduri.beneficiar_anunt b
    LEFT JOIN fonduri.beneficiar_anunt_lot l ON l.anunt_id = b.id
    LEFT JOIN firms.entities e ON e.cui = b.cui
    WHERE b.cui IS NOT NULL
    GROUP BY b.cui, e.name, b.beneficiar_name, e.adr_judet, b.beneficiar_judet
    ORDER BY buget_total DESC NULLS LAST
    LIMIT 20
  `);

  const topProiecte = await query<PrivateEuTopProiect>(`
    WITH anunt_buget AS (
      SELECT b.id AS anunt_id,
             b.smis_proiect_id,
             coalesce(sum(l.buget_lei), 0)::numeric AS buget_anunt
      FROM fonduri.beneficiar_anunt b
      LEFT JOIN fonduri.beneficiar_anunt_lot l ON l.anunt_id = b.id
      WHERE b.smis_proiect_id IS NOT NULL
      GROUP BY b.id
    ),
    sample_per_proiect AS (
      SELECT DISTINCT ON (smis_proiect_id)
             smis_proiect_id, anunt_id
      FROM anunt_buget
      ORDER BY smis_proiect_id, buget_anunt DESC NULLS LAST, anunt_id
    ),
    agg AS (
      SELECT
        b.smis_proiect_id AS id,
        min(b.smis_proiect_code)            AS smis_code,
        min(b.smis_proiect_name)            AS titlu_anunt,
        min(b.beneficiar_name)              AS beneficiar_name,
        min(b.cui)                          AS beneficiar_cui,
        count(*)::int                       AS anunturi,
        coalesce(sum(l.buget_lei), 0)::numeric AS buget_total
      FROM fonduri.beneficiar_anunt b
      LEFT JOIN fonduri.beneficiar_anunt_lot l ON l.anunt_id = b.id
      WHERE b.smis_proiect_id IS NOT NULL
      GROUP BY b.smis_proiect_id
    )
    SELECT
      agg.id,
      agg.smis_code,
      coalesce(p.titlu, agg.titlu_anunt) AS titlu,
      coalesce(p.beneficiar_name, agg.beneficiar_name) AS beneficiar_name,
      agg.beneficiar_cui,
      p.program_op_cod,
      agg.anunturi,
      agg.buget_total,
      sp.anunt_id AS sample_anunt_id
    FROM agg
    LEFT JOIN fonduri.beneficiar_proiect p ON p.id = agg.id
    LEFT JOIN sample_per_proiect sp ON sp.smis_proiect_id = agg.id
    ORDER BY agg.buget_total DESC NULLS LAST
    LIMIT 20
  `);

  const topJudete = await query<PrivateEuCountyRow>(`
    SELECT
      coalesce(NULLIF(trim(b.beneficiar_judet), ''), '(neprecizat)') AS judet,
      count(*)::int AS anunturi,
      coalesce(sum(l.buget_lei), 0)::numeric AS buget_total
    FROM fonduri.beneficiar_anunt b
    LEFT JOIN fonduri.beneficiar_anunt_lot l ON l.anunt_id = b.id
    GROUP BY 1
    ORDER BY buget_total DESC NULLS LAST
    LIMIT 10
  `);

  const programs = await query<PrivateEuProgramRow>(`
    SELECT
      program_op_cod,
      min(program_op_text) AS program_op_text,
      count(*)::int AS proiecte
    FROM fonduri.beneficiar_proiect
    WHERE program_op_cod IS NOT NULL
    GROUP BY 1
    ORDER BY proiecte DESC NULLS LAST
    LIMIT 12
  `);

  return {
    anunturi_total: Number(a?.anunturi || 0),
    proiecte_total: Number(a?.proiecte || 0),
    firme_matched: Number(a?.firme || 0),
    buget_total: Number(a?.buget || 0),
    proiecte_scraped: Number(a?.scraped || 0),
    proiecte_target: Number(a?.target || 0),
    top_firme: topFirme.rows.map(r => ({
      ...r,
      anunturi: Number(r.anunturi),
      buget_total: Number(r.buget_total),
    })),
    top_proiecte: topProiecte.rows.map(r => ({
      ...r,
      anunturi: Number(r.anunturi),
      buget_total: Number(r.buget_total),
      sample_anunt_id: r.sample_anunt_id == null ? null : Number(r.sample_anunt_id),
    })),
    top_judete: topJudete.rows.map(r => ({
      ...r,
      anunturi: Number(r.anunturi),
      buget_total: Number(r.buget_total),
    })),
    programs: programs.rows.map(r => ({
      ...r,
      proiecte: Number(r.proiecte),
    })),
  };
}

export async function getTopEUContracts(limit = 100): Promise<DashboardContractRow[]> {
  const r = await query<DashboardContractRow>(`
    SELECT
      a.id, a.ref_number, a.type, a.title,
      a.authority_name, a.authority_cui,
      cl.county AS authority_county,
      a.supplier_name, a.supplier_cui,
      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.procedure_type,
      a.contract_has_lots AS has_lots, a.lots_count,
      a.framework_agreement,
      a.source, a.eu_funded, a.eu_program
    FROM seap.announcements a
    LEFT JOIN seap.cpv_codes c ON c.code = a.cpv_division
    LEFT JOIN seap.cui_location cl ON cl.cui = a.authority_cui
    WHERE a.publication_date >= now() - interval '12 months'
      AND a.eu_funded ILIKE 'da%'
      AND a.awarded_value IS NOT NULL
      AND a.awarded_value > 0
      AND a.type IN (${AWARDED_TYPES})
    ORDER BY a.awarded_value DESC NULLS LAST
    LIMIT $1
  `, [limit]);
  return r.rows;
}
