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

export interface CountyProfile {
  county_code: string;
  county_name: string;
  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;
}

export interface CountyContract {
  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 CountyParty {
  cui: string;
  name: string;
  contracts: number;
  total_value: number;
}

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

// Map of NUTS code → Romanian county name.
export const COUNTY_MAP: Record<string, string> = {
  RO111: 'Bihor', RO112: 'Bistrița-Năsăud', RO113: 'Cluj', RO114: 'Maramureș',
  RO115: 'Satu Mare', RO116: 'Sălaj',
  RO121: 'Alba', RO122: 'Brașov', RO123: 'Covasna', RO124: 'Harghita',
  RO125: 'Mureș', RO126: 'Sibiu',
  RO211: 'Bacău', RO212: 'Botoșani', RO213: 'Iași', RO214: 'Neamț',
  RO215: 'Suceava', RO216: 'Vaslui',
  RO221: 'Brăila', RO222: 'Buzău', RO223: 'Constanța', RO224: 'Galați',
  RO225: 'Tulcea', RO226: 'Vrancea',
  RO311: 'Argeș', RO312: 'Călărași', RO313: 'Dâmbovița', RO314: 'Giurgiu',
  RO315: 'Ialomița', RO316: 'Prahova', RO317: 'Teleorman',
  RO321: 'București', RO322: 'Ilfov',
  RO411: 'Dolj', RO412: 'Gorj', RO413: 'Mehedinți', RO414: 'Olt', RO415: 'Vâlcea',
  RO421: 'Arad', RO422: 'Caraș-Severin', RO423: 'Hunedoara', RO424: 'Timiș',
};

// Map of 2-letter abbreviations and slugs to NUTS code.
const SLUG_MAP: Record<string, string> = {};
function slugify(s: string): string {
  return s.toLowerCase()
    .normalize('NFD').replace(/[̀-ͯ]/g, '')
    .replace(/ș/g, 's').replace(/ț/g, 't').replace(/ă/g, 'a').replace(/â/g, 'a').replace(/î/g, 'i')
    .replace(/[^a-z0-9]+/g, '-').replace(/^-+|-+$/g, '');
}
for (const [code, name] of Object.entries(COUNTY_MAP)) {
  SLUG_MAP[slugify(name)] = code;
}
// Also accept simple 2-letter codes for the common cases
const ABBREV_MAP: Record<string, string> = {
  b: 'RO321', bc: 'RO211', bh: 'RO111', bn: 'RO112', bt: 'RO212', br: 'RO221',
  bv: 'RO122', bz: 'RO222', cs: 'RO422', cl: 'RO312', cj: 'RO113', ct: 'RO223',
  cv: 'RO123', db: 'RO313', dj: 'RO411', gl: 'RO224', gr: 'RO314', gj: 'RO412',
  hr: 'RO124', hd: 'RO423', il: 'RO315', is: 'RO213', if: 'RO322', mm: 'RO114',
  mh: 'RO413', ms: 'RO125', nt: 'RO214', ot: 'RO414', ph: 'RO316', sm: 'RO115',
  sj: 'RO116', sb: 'RO126', sv: 'RO215', tr: 'RO317', tm: 'RO424', tl: 'RO225',
  vs: 'RO216', vl: 'RO415', vn: 'RO226', ar: 'RO421', ag: 'RO311', ab: 'RO121',
};

/** Resolve any input form (NUTS code, slug, abbreviation) to NUTS code + name. */
export function resolveCounty(input: string): { code: string; name: string } | null {
  if (!input) return null;
  const upper = input.toUpperCase().trim();
  if (COUNTY_MAP[upper]) return { code: upper, name: COUNTY_MAP[upper] };
  const lower = input.toLowerCase().trim();
  if (ABBREV_MAP[lower]) return { code: ABBREV_MAP[lower], name: COUNTY_MAP[ABBREV_MAP[lower]] };
  const slug = slugify(input);
  if (SLUG_MAP[slug]) return { code: SLUG_MAP[slug], name: COUNTY_MAP[SLUG_MAP[slug]] };
  return null;
}


export async function getCountyProfile(countyCode: string): Promise<CountyProfile | null> {
  const result = await query<CountyProfile>(`
    SELECT
      $1::text AS county_code,
      $2::text AS county_name,
      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
    FROM seap.announcements
    WHERE county_code = $1
  `, [countyCode, COUNTY_MAP[countyCode] ?? countyCode]);
  const row = result.rows[0];
  if (!row || !row.total_contracts) return null;
  return row;
}


export async function getCountyContracts(
  countyCode: string,
  limit = 30,
  offset = 0,
): Promise<CountyContract[]> {
  const result = await query<CountyContract>(`
    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.county_code = $1
    ORDER BY a.publication_date DESC NULLS LAST
    LIMIT $2 OFFSET $3
  `, [countyCode, limit, offset]);
  return result.rows;
}


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


/**
 * Top suppliers active in this county. We approximate "active in county" two ways:
 * (a) suppliers whose cui_location.county matches the county name, or
 * (b) suppliers winning contracts published with this county_code.
 * We use (b) — most reliable and consistent with the announcements set.
 */
export async function getCountyTopSuppliers(
  countyCode: string,
  limit = 10,
): Promise<CountyParty[]> {
  const result = await query<CountyParty>(`
    SELECT
      a.supplier_cui 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.county_code = $1 AND a.supplier_cui IS NOT NULL
    GROUP BY a.supplier_cui
    ORDER BY 4 DESC NULLS LAST
    LIMIT $2
  `, [countyCode, limit]);
  return result.rows;
}


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