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

export interface BeneficiarAnunt {
  id: number;
  smis_proiect_id: number | null;
  smis_proiect_code: string | null;
  smis_proiect_name: string | null;
  beneficiar_name: string;
  beneficiar_program_tag: string | null;
  beneficiar_adresa: string | null;
  beneficiar_contact: string | null;
  beneficiar_telefon: string | null;
  beneficiar_regiune: string | null;
  beneficiar_judet: string | null;
  beneficiar_localitate: string | null;
  procedura_status: string | null;
  data_publicare: string | null;
  data_limita_oferta: string | null;
  ora_limita_oferta: string | null;
  judet: string | null;
  tip_contract: string | null;
  cui: string | null;
  cui_match_score: number | null;
  cui_match_method: string | null;
}

export interface BeneficiarLot {
  lot_no: number;
  lot_label: string | null;
  durata_contract: string | null;
  buget_lei: number | null;
  cpv_cod: string | null;
  descriere_url: string | null;
  spec_url: string | null;
}

export async function getBeneficiarAnunt(id: number): Promise<BeneficiarAnunt | null> {
  const r = await query<BeneficiarAnunt>(
    `SELECT id, smis_proiect_id, smis_proiect_code, smis_proiect_name,
            beneficiar_name, beneficiar_program_tag, beneficiar_adresa,
            beneficiar_contact, beneficiar_telefon, beneficiar_regiune,
            beneficiar_judet, beneficiar_localitate,
            procedura_status,
            to_char(data_publicare, 'YYYY-MM-DD') AS data_publicare,
            to_char(data_limita_oferta, 'YYYY-MM-DD') AS data_limita_oferta,
            ora_limita_oferta,
            judet, tip_contract,
            cui, cui_match_score, cui_match_method
     FROM fonduri.beneficiar_anunt WHERE id = $1`,
    [id],
  );
  return r.rows[0] || null;
}

export async function getBeneficiarLots(anuntId: number): Promise<BeneficiarLot[]> {
  const r = await query<BeneficiarLot>(
    `SELECT lot_no, lot_label, durata_contract, buget_lei, cpv_cod, descriere_url, spec_url
     FROM fonduri.beneficiar_anunt_lot
     WHERE anunt_id = $1
     ORDER BY lot_no`,
    [anuntId],
  );
  return r.rows;
}

// All anunturi by the same beneficiar (matched by cui if available, else name)
export async function getBeneficiarAlsoPosted(
  cui: string | null,
  name: string,
  excludeId: number,
): Promise<Array<{ id: number; data_publicare: string | null; tip_contract: string | null; titlu: string | null; total_buget: number | null }>> {
  if (cui) {
    const r = await query<any>(
      `SELECT a.id, to_char(a.data_publicare, 'YYYY-MM-DD') AS data_publicare,
              a.tip_contract, a.smis_proiect_name AS titlu,
              (SELECT SUM(buget_lei) FROM fonduri.beneficiar_anunt_lot WHERE anunt_id = a.id) AS total_buget
       FROM fonduri.beneficiar_anunt a
       WHERE a.cui = $1 AND a.id != $2
       ORDER BY a.data_publicare DESC NULLS LAST
       LIMIT 30`,
      [cui, excludeId],
    );
    return r.rows;
  }
  const r = await query<any>(
    `SELECT a.id, to_char(a.data_publicare, 'YYYY-MM-DD') AS data_publicare,
            a.tip_contract, a.smis_proiect_name AS titlu,
            (SELECT SUM(buget_lei) FROM fonduri.beneficiar_anunt_lot WHERE anunt_id = a.id) AS total_buget
     FROM fonduri.beneficiar_anunt a
     WHERE a.beneficiar_name = $1 AND a.id != $2
     ORDER BY a.data_publicare DESC NULLS LAST
     LIMIT 30`,
    [name, excludeId],
  );
  return r.rows;
}

// EU project metadata from fonduri.beneficiar_proiect (populated incrementally
// by services/seap-scraper/src/scrape-beneficiar-proiect.ts). Returns null if
// the project page hasn't been scraped yet — the caller handles that gracefully.
export interface ProiectContext {
  id: number;
  smis_code: string | null;
  titlu: string | null;
  program_op_cod: string | null;
  program_op_text: string | null;
  axa_cod: string | null;
  axa_text: string | null;
  domeniul_cod: string | null;
  domeniul_text: string | null;
  operatiune_cod: string | null;
  operatiune_text: string | null;
  data_contract: string | null;
}

export async function getProiectContext(smisProiectId: number): Promise<ProiectContext | null> {
  const r = await query<ProiectContext>(
    `SELECT id, smis_code, titlu,
            program_op_cod, program_op_text,
            axa_cod, axa_text,
            domeniul_cod, domeniul_text,
            operatiune_cod, operatiune_text,
            to_char(data_contract, 'YYYY-MM-DD') AS data_contract
     FROM fonduri.beneficiar_proiect
     WHERE id = $1`,
    [smisProiectId],
  );
  return r.rows[0] || null;
}

// Full proiect detail for /proiect/[id] page — combines beneficiar_proiect
// metadata with aggregated anunturi stats and the matched firma profile (if any).
export interface ProiectFull {
  id: number;
  proiect_type: number;
  smis_code: string | null;
  titlu: string | null;
  beneficiar_name: string | null;
  beneficiar_cui: string | null;          // matched firma CUI (from beneficiar_anunt.cui MIN)
  beneficiar_judet: string | null;
  program_op_cod: string | null;
  program_op_text: string | null;
  axa_cod: string | null;
  axa_text: string | null;
  domeniul_cod: string | null;
  domeniul_text: string | null;
  operatiune_cod: string | null;
  operatiune_text: string | null;
  data_contract: string | null;
  anunturi_count: number;                 // total announcements under this project
  buget_total: number;                    // sum of all lot budgets across anunturi
  data_publicare_min: string | null;      // earliest anunt date
  data_publicare_max: string | null;      // latest anunt date
}

export async function getProiectFull(id: number): Promise<ProiectFull | null> {
  const r = await query<ProiectFull>(
    `WITH agg AS (
       SELECT b.smis_proiect_id AS id,
              MIN(b.beneficiar_judet) AS beneficiar_judet,
              MIN(b.cui) AS beneficiar_cui,
              COUNT(*) AS anunturi_count,
              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.smis_proiect_id = $1), 0)::numeric AS buget_total,
              MIN(b.data_publicare) AS data_publicare_min,
              MAX(b.data_publicare) AS data_publicare_max
       FROM fonduri.beneficiar_anunt b
       WHERE b.smis_proiect_id = $1
       GROUP BY b.smis_proiect_id
     )
     SELECT p.id, p.proiect_type, p.smis_code, p.titlu, p.beneficiar_name,
            agg.beneficiar_cui, agg.beneficiar_judet,
            p.program_op_cod, p.program_op_text,
            p.axa_cod, p.axa_text,
            p.domeniul_cod, p.domeniul_text,
            p.operatiune_cod, p.operatiune_text,
            to_char(p.data_contract, 'YYYY-MM-DD') AS data_contract,
            COALESCE(agg.anunturi_count, 0)::int AS anunturi_count,
            agg.buget_total,
            to_char(agg.data_publicare_min, 'YYYY-MM-DD') AS data_publicare_min,
            to_char(agg.data_publicare_max, 'YYYY-MM-DD') AS data_publicare_max
     FROM fonduri.beneficiar_proiect p
     LEFT JOIN agg ON agg.id = p.id
     WHERE p.id = $1`,
    [id],
  );
  if (!r.rows[0]) return null;
  const row = r.rows[0];
  return {
    ...row,
    anunturi_count: Number(row.anunturi_count),
    buget_total: Number(row.buget_total) || 0,
  };
}

// All anunturi tied to a project (for the project page listing).
export async function getProiectAllAnunturi(smisProiectId: number): Promise<any[]> {
  const r = await query<any>(
    `SELECT a.id, a.beneficiar_name,
            to_char(a.data_publicare, 'YYYY-MM-DD') AS data_publicare,
            a.tip_contract,
            a.titlu,
            a.procedura_status,
            a.beneficiar_judet,
            (SELECT sum(buget_lei) FROM fonduri.beneficiar_anunt_lot WHERE anunt_id = a.id)::numeric AS total_buget
     FROM fonduri.beneficiar_anunt a
     WHERE a.smis_proiect_id = $1
     ORDER BY a.data_publicare DESC NULLS LAST`,
    [smisProiectId],
  );
  return r.rows.map((row) => ({ ...row, total_buget: row.total_buget == null ? null : Number(row.total_buget) }));
}

// Other anunturi from the same EU project
export async function getProiectAnunturi(smisProiectId: number, excludeId: number): Promise<any[]> {
  const r = await query<any>(
    `SELECT a.id, a.beneficiar_name, to_char(a.data_publicare, 'YYYY-MM-DD') AS data_publicare,
            a.tip_contract, a.smis_proiect_name AS titlu,
            (SELECT SUM(buget_lei) FROM fonduri.beneficiar_anunt_lot WHERE anunt_id = a.id) AS total_buget
     FROM fonduri.beneficiar_anunt a
     WHERE a.smis_proiect_id = $1 AND a.id != $2
     ORDER BY a.data_publicare DESC NULLS LAST
     LIMIT 20`,
    [smisProiectId, excludeId],
  );
  return r.rows;
}

// ============================================================================
//  BROWSE / SEARCH API for /fonduri-ue/anunturi
//  41,494 anunturi total · paginated · facet counts on filtered subset
// ============================================================================

export interface BrowseFilters {
  q?: string;                  // free text (titlu / beneficiar_name / SMIS)
  program?: string;            // program_op_cod
  judet?: string;              // upper-case normalized: 'CLUJ', 'BUCURESTI'
  tip_contract?: string;       // 'Furnizare' | 'Servicii' | 'Lucrări' | 'N/A'
  status?: 'open' | 'closed';
  axa?: string;                // axa_cod from beneficiar_proiect
  buget_min?: number;
  buget_max?: number;
  date_from?: string;          // 'YYYY-MM-DD'
  date_to?: string;
  sort?: 'date_desc' | 'date_asc' | 'buget_desc' | 'buget_asc';
  limit?: number;              // default 30, max 100
  offset?: number;
}

export interface BrowseRow {
  id: number;
  titlu: string | null;
  beneficiar_name: string;
  beneficiar_judet: string | null;
  cui: string | null;
  data_publicare: string | null;
  tip_contract: string | null;
  procedura_status: string | null;
  smis_proiect_id: number | null;
  smis_proiect_code: string | null;
  smis_proiect_name: string | null;
  program_op_cod: string | null;
  program_op_text: string | null;
  axa_cod: string | null;
  buget_total: number;
  loturi: number;
  lat: number | null;
  lng: number | null;
}

export interface BrowseFacets {
  programs: { code: string; text: string | null; count: number }[];
  judete: { judet: string; count: number }[];
  tip_contract: { tip: string; count: number }[];
  axe: { code: string; text: string | null; count: number }[];
  status: { status: 'open' | 'closed'; count: number }[];
}

export interface BrowseResponse {
  total: number;
  buget_sum: number;
  rows: BrowseRow[];
  facets: BrowseFacets;
}

// Build a WHERE clause from filters. Always operates on alias `a` (beneficiar_anunt)
// joined LEFT to `p` (beneficiar_proiect) and a buget-CTE.
function buildBrowseWhere(f: BrowseFilters, params: any[]): string {
  const conds: string[] = [];

  if (f.q && f.q.trim()) {
    const q = f.q.trim();
    params.push(`%${q}%`);
    const likeIdx = params.length;
    params.push(q);
    const eqIdx = params.length;
    conds.push(`(
      a.beneficiar_name ILIKE $${likeIdx}
      OR a.smis_proiect_name ILIKE $${likeIdx}
      OR a.smis_proiect_code = $${eqIdx}
      OR a.cui = regexp_replace(upper($${eqIdx}), '(^RO)|\\s+', '', 'g')
    )`);
  }

  if (f.program) {
    params.push(f.program);
    conds.push(`p.program_op_cod = $${params.length}`);
  }

  if (f.judet) {
    params.push(f.judet);
    conds.push(`a.judet = $${params.length}`);
  }

  if (f.tip_contract) {
    params.push(f.tip_contract);
    conds.push(`a.tip_contract = $${params.length}`);
  }

  if (f.axa) {
    params.push(f.axa);
    conds.push(`p.axa_cod = $${params.length}`);
  }

  if (f.status === 'open') {
    conds.push(`a.procedura_status ILIKE '%curs%'`);
  } else if (f.status === 'closed') {
    conds.push(`a.procedura_status ILIKE '%închisă%'`);
  }

  if (f.date_from) {
    params.push(f.date_from);
    conds.push(`a.data_publicare >= $${params.length}::date`);
  }
  if (f.date_to) {
    params.push(f.date_to);
    conds.push(`a.data_publicare <= $${params.length}::date`);
  }

  // Budget filter operates on aggregated lot sum — applied via HAVING-shaped subquery
  if (f.buget_min !== undefined && f.buget_min !== null) {
    params.push(f.buget_min);
    conds.push(`COALESCE((SELECT sum(buget_lei) FROM fonduri.beneficiar_anunt_lot WHERE anunt_id = a.id), 0) >= $${params.length}`);
  }
  if (f.buget_max !== undefined && f.buget_max !== null) {
    params.push(f.buget_max);
    conds.push(`COALESCE((SELECT sum(buget_lei) FROM fonduri.beneficiar_anunt_lot WHERE anunt_id = a.id), 0) <= $${params.length}`);
  }

  return conds.length ? 'WHERE ' + conds.join(' AND ') : '';
}

export async function browseBeneficiarAnunturi(f: BrowseFilters): Promise<BrowseResponse> {
  const limit = Math.min(f.limit ?? 30, 100);
  const offset = Math.max(0, f.offset ?? 0);

  // --- main paginated query
  const params: any[] = [];
  const where = buildBrowseWhere(f, params);

  let orderBy = 'a.data_publicare DESC NULLS LAST, a.id DESC';
  if (f.sort === 'date_asc') orderBy = 'a.data_publicare ASC NULLS LAST, a.id ASC';
  else if (f.sort === 'buget_desc') orderBy = 'buget_total DESC NULLS LAST, a.id DESC';
  else if (f.sort === 'buget_asc') orderBy = 'buget_total ASC NULLS LAST, a.id ASC';

  params.push(limit);
  const limitIdx = params.length;
  params.push(offset);
  const offsetIdx = params.length;

  const rowsSql = `
    SELECT a.id, a.titlu, a.beneficiar_name, a.beneficiar_judet, a.cui,
           to_char(a.data_publicare, 'YYYY-MM-DD') AS data_publicare,
           a.tip_contract, a.procedura_status,
           a.smis_proiect_id, a.smis_proiect_code, a.smis_proiect_name,
           p.program_op_cod, p.program_op_text, p.axa_cod,
           COALESCE((SELECT sum(buget_lei) FROM fonduri.beneficiar_anunt_lot WHERE anunt_id = a.id), 0)::numeric AS buget_total,
           COALESCE((SELECT count(*) FROM fonduri.beneficiar_anunt_lot WHERE anunt_id = a.id), 0)::int AS loturi,
           e.lat, e.lng
    FROM fonduri.beneficiar_anunt a
    LEFT JOIN fonduri.beneficiar_proiect p ON p.id = a.smis_proiect_id
    LEFT JOIN firms.entities e ON e.cui = a.cui
    ${where}
    ORDER BY ${orderBy}
    LIMIT $${limitIdx} OFFSET $${offsetIdx}`;

  // total + buget_sum
  const totalParams = params.slice(0, params.length - 2);
  const totalSql = `
    SELECT count(*)::int AS total,
           COALESCE(sum(COALESCE((SELECT sum(buget_lei) FROM fonduri.beneficiar_anunt_lot WHERE anunt_id = a.id), 0)), 0)::numeric AS buget_sum
    FROM fonduri.beneficiar_anunt a
    LEFT JOIN fonduri.beneficiar_proiect p ON p.id = a.smis_proiect_id
    ${where}`;

  // --- facet queries · same WHERE used for all (UX: facets shrink as filters apply)
  const whereAnd = where ? `${where} AND ` : 'WHERE ';
  const facetSqlPrograms = `
    SELECT p.program_op_cod AS code, MIN(p.program_op_text) AS text, count(*)::int AS count
    FROM fonduri.beneficiar_anunt a
    LEFT JOIN fonduri.beneficiar_proiect p ON p.id = a.smis_proiect_id
    ${whereAnd}p.program_op_cod IS NOT NULL
    GROUP BY p.program_op_cod
    ORDER BY count DESC
    LIMIT 20`;

  const facetSqlJudete = `
    SELECT a.judet, count(*)::int AS count
    FROM fonduri.beneficiar_anunt a
    LEFT JOIN fonduri.beneficiar_proiect p ON p.id = a.smis_proiect_id
    ${whereAnd}a.judet IS NOT NULL
    GROUP BY a.judet
    ORDER BY count DESC
    LIMIT 50`;

  const facetSqlTip = `
    SELECT a.tip_contract AS tip, count(*)::int AS count
    FROM fonduri.beneficiar_anunt a
    LEFT JOIN fonduri.beneficiar_proiect p ON p.id = a.smis_proiect_id
    ${whereAnd}a.tip_contract IS NOT NULL
    GROUP BY a.tip_contract
    ORDER BY count DESC`;

  const facetSqlAxe = `
    SELECT p.axa_cod AS code, MIN(p.axa_text) AS text, count(*)::int AS count
    FROM fonduri.beneficiar_anunt a
    LEFT JOIN fonduri.beneficiar_proiect p ON p.id = a.smis_proiect_id
    ${whereAnd}p.axa_cod IS NOT NULL
    GROUP BY p.axa_cod
    ORDER BY count DESC
    LIMIT 25`;

  const facetSqlStatus = `
    SELECT CASE WHEN a.procedura_status ILIKE '%curs%' THEN 'open' ELSE 'closed' END AS status,
           count(*)::int AS count
    FROM fonduri.beneficiar_anunt a
    LEFT JOIN fonduri.beneficiar_proiect p ON p.id = a.smis_proiect_id
    ${where}
    GROUP BY 1
    ORDER BY count DESC`;

  const [rowsR, totalR, programsR, judeteR, tipR, axeR, statusR] = await Promise.all([
    query<BrowseRow>(rowsSql, params),
    query<{ total: number; buget_sum: number }>(totalSql, totalParams),
    query<{ code: string; text: string | null; count: number }>(facetSqlPrograms, totalParams),
    query<{ judet: string; count: number }>(facetSqlJudete, totalParams),
    query<{ tip: string; count: number }>(facetSqlTip, totalParams),
    query<{ code: string; text: string | null; count: number }>(facetSqlAxe, totalParams),
    query<{ status: 'open' | 'closed'; count: number }>(facetSqlStatus, totalParams),
  ]);

  return {
    total: Number(totalR.rows[0]?.total || 0),
    buget_sum: Number(totalR.rows[0]?.buget_sum || 0),
    rows: rowsR.rows.map(r => ({
      ...r,
      buget_total: Number(r.buget_total) || 0,
      loturi: Number(r.loturi) || 0,
      lat: r.lat != null ? Number(r.lat) : null,
      lng: r.lng != null ? Number(r.lng) : null,
    })),
    facets: {
      programs: programsR.rows,
      judete: judeteR.rows,
      tip_contract: tipR.rows,
      axe: axeR.rows,
      status: statusR.rows,
    },
  };
}

export function parseBrowseParams(url: URL): BrowseFilters {
  const sp = url.searchParams;
  const f: BrowseFilters = {};
  if (sp.get('q')) f.q = sp.get('q')!;
  if (sp.get('program')) f.program = sp.get('program')!;
  if (sp.get('judet')) f.judet = sp.get('judet')!;
  if (sp.get('tip')) f.tip_contract = sp.get('tip')!;
  if (sp.get('axa')) f.axa = sp.get('axa')!;
  const status = sp.get('status');
  if (status === 'open' || status === 'closed') f.status = status;
  if (sp.get('buget_min')) f.buget_min = parseFloat(sp.get('buget_min')!);
  if (sp.get('buget_max')) f.buget_max = parseFloat(sp.get('buget_max')!);
  if (sp.get('date_from')) f.date_from = sp.get('date_from')!;
  if (sp.get('date_to')) f.date_to = sp.get('date_to')!;
  const sort = sp.get('sort');
  if (sort === 'date_desc' || sort === 'date_asc' || sort === 'buget_desc' || sort === 'buget_asc') f.sort = sort;
  if (sp.get('limit')) f.limit = parseInt(sp.get('limit')!);
  if (sp.get('offset')) f.offset = parseInt(sp.get('offset')!);
  return f;
}

export function buildBrowseUrl(base: string, f: BrowseFilters): string {
  const sp = new URLSearchParams();
  if (f.q) sp.set('q', f.q);
  if (f.program) sp.set('program', f.program);
  if (f.judet) sp.set('judet', f.judet);
  if (f.tip_contract) sp.set('tip', f.tip_contract);
  if (f.axa) sp.set('axa', f.axa);
  if (f.status) sp.set('status', f.status);
  if (f.buget_min !== undefined) sp.set('buget_min', String(f.buget_min));
  if (f.buget_max !== undefined) sp.set('buget_max', String(f.buget_max));
  if (f.date_from) sp.set('date_from', f.date_from);
  if (f.date_to) sp.set('date_to', f.date_to);
  if (f.sort) sp.set('sort', f.sort);
  if (f.offset) sp.set('offset', String(f.offset));
  if (f.limit && f.limit !== 30) sp.set('limit', String(f.limit));
  const s = sp.toString();
  return s ? `${base}?${s}` : base;
}

// Quick SEAP cross-link counts (for set of supplier CUIs on the rendered page).
// Mirrors the pattern from cauta.astro but in the other direction.
export async function getSeapCountsForCuis(cuis: string[]): Promise<Map<string, { contracts: number; total: number }>> {
  if (cuis.length === 0) return new Map();
  const r = await query<{ cui: string; contracts: number; total: number }>(
    `SELECT supplier_cui AS cui, count(*)::int AS contracts,
            COALESCE(sum(awarded_value), 0)::numeric AS total
     FROM seap.announcements
     WHERE supplier_cui = ANY($1) AND awarded_value IS NOT NULL
     GROUP BY supplier_cui`,
    [cuis],
  );
  const map = new Map<string, { contracts: number; total: number }>();
  for (const row of r.rows) map.set(row.cui, { contracts: Number(row.contracts), total: Number(row.total) });
  return map;
}
