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

export interface CategoryProfile {
  cpv_division: string;       // 8-digit normalized division (e.g. '33000000')
  cpv_division_name: string | null;
  emoji: string | null;
  total_contracts: number;
  total_awarded: number | null;
  total_estimated: number | null;
  avg_contract: number | null;
  distinct_authorities: number;
  distinct_suppliers: number;
  first_contract_date: string | null;
  last_contract_date: string | null;
  top_authority_name: string | null;
  top_supplier_name: string | null;
}

export interface CategoryContract {
  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;
  publication_date: string | null;
  awarded_value: number | null;
  estimated_value: number | null;
  currency: string | null;
  procedure_type: string | null;
  authority_name: string | null;
  authority_cui: string | null;
  supplier_name: string | null;
  supplier_cui: string | null;
  has_lots: boolean | null;
  lots_count: number | null;
  source: string;
  seap_url: string | null;
}

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

export interface SubCpvBreakdown {
  cpv_code: string;
  cpv_name: string | null;
  contracts: number;
  total_value: number;
}

/** Normalize a CPV input to its 8-digit division code (e.g. '33000000'). */
export function normalizeCpvDivision(input: string): string {
  const digits = (input || '').replace(/[^0-9]/g, '');
  if (!digits) return '';
  // pad/truncate to 8 digits, then collapse last 6 to zeros for division-level
  const eight = (digits + '00000000').slice(0, 8);
  return eight.slice(0, 2) + '000000';
}


export async function getCategoryProfile(cpvDivision: string): Promise<CategoryProfile | null> {
  const result = await query<CategoryProfile>(`
    WITH base AS (
      SELECT * FROM seap.announcements WHERE cpv_division = $1
    ),
    top_auth AS (
      SELECT authority_name FROM base
      WHERE authority_cui IS NOT NULL
      GROUP BY authority_cui, authority_name
      ORDER BY count(*) DESC NULLS LAST
      LIMIT 1
    ),
    top_supp AS (
      SELECT supplier_name FROM base
      WHERE supplier_cui IS NOT NULL
      GROUP BY supplier_cui, supplier_name
      ORDER BY count(*) DESC NULLS LAST
      LIMIT 1
    )
    SELECT
      $1::text AS cpv_division,
      (SELECT name_ro FROM seap.cpv_codes WHERE code = $1) AS cpv_division_name,
      (SELECT emoji FROM seap.cpv_codes WHERE code = $1) AS emoji,
      count(*)::int AS total_contracts,
      sum(awarded_value)::numeric AS total_awarded,
      sum(estimated_value)::numeric AS total_estimated,
      avg(awarded_value)::numeric AS avg_contract,
      count(DISTINCT authority_cui)::int AS distinct_authorities,
      count(DISTINCT supplier_cui)::int AS distinct_suppliers,
      min(publication_date) AS first_contract_date,
      max(publication_date) AS last_contract_date,
      (SELECT authority_name FROM top_auth) AS top_authority_name,
      (SELECT supplier_name FROM top_supp) AS top_supplier_name
    FROM base
  `, [cpvDivision]);
  const row = result.rows[0];
  if (!row || !row.total_contracts) return null;
  return row;
}


export async function getCategoryContracts(
  cpvDivision: string,
  limit = 30,
  offset = 0,
): Promise<CategoryContract[]> {
  const result = await query<CategoryContract>(`
    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,
      a.publication_date, a.awarded_value, a.estimated_value, a.currency,
      a.procedure_type,
      a.authority_name, a.authority_cui,
      a.supplier_name, a.supplier_cui,
      a.contract_has_lots AS has_lots, a.lots_count,
      a.source, a.seap_url
    FROM seap.announcements a
    LEFT JOIN seap.cpv_codes c ON c.code = a.cpv_division
    WHERE a.cpv_division = $1
    ORDER BY a.publication_date DESC NULLS LAST
    LIMIT $2 OFFSET $3
  `, [cpvDivision, limit, offset]);
  return result.rows;
}


export async function getCategoryTopBuyers(
  cpvDivision: string,
  limit = 10,
): Promise<PartyTop[]> {
  const result = await query<PartyTop>(`
    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.cpv_division = $1 AND a.authority_cui IS NOT NULL
    GROUP BY a.authority_cui
    ORDER BY 5 DESC NULLS LAST
    LIMIT $2
  `, [cpvDivision, limit]);
  return result.rows;
}


export async function getCategoryTopSuppliers(
  cpvDivision: string,
  limit = 10,
): Promise<PartyTop[]> {
  const result = await query<PartyTop>(`
    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.cpv_division = $1 AND a.supplier_cui IS NOT NULL
    GROUP BY a.supplier_cui
    ORDER BY 5 DESC NULLS LAST
    LIMIT $2
  `, [cpvDivision, limit]);
  return result.rows;
}


/** Sub-CPVs (4-digit roll-up under the same division). */
export async function getCategorySubCpvs(
  cpvDivision: string,
  limit = 12,
): Promise<SubCpvBreakdown[]> {
  // First 4 digits identify a sub-group; we group by left(cpv_code, 4)
  // and join cpv_codes to get a name (best-effort: pick a level=2 row or first match).
  const result = await query<SubCpvBreakdown>(`
    WITH grouped AS (
      SELECT
        substr(coalesce(a.cpv_code, a.cpv_division), 1, 4) AS sub4,
        count(*)::int AS contracts,
        coalesce(sum(a.awarded_value), 0)::numeric AS total_value
      FROM seap.announcements a
      WHERE a.cpv_division = $1
        AND a.cpv_code IS NOT NULL
      GROUP BY 1
    )
    SELECT
      g.sub4 || '0000' AS cpv_code,
      (
        SELECT name_ro FROM seap.cpv_codes c
        WHERE substr(c.code, 1, 4) = g.sub4
        ORDER BY (CASE WHEN c.code = g.sub4 || '0000' THEN 0 ELSE 1 END), c.level
        LIMIT 1
      ) AS cpv_name,
      g.contracts,
      g.total_value
    FROM grouped g
    ORDER BY g.total_value DESC NULLS LAST
    LIMIT $2
  `, [cpvDivision, limit]);
  return result.rows;
}
