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

export interface UatStats {
  siruta: string;
  uat_name: string;
  county: string;
  da_count: number;
  da_total_value: number;
  notice_count: number;
  notice_total_value: number;
  total_contracts: number;
  total_value: number;
}

export interface NationalStats {
  total_authorities: number;
  total_suppliers: number;
  total_da: number;
  total_contracts: number;
  total_initiere: number;
  total_all: number;
  total_value: number;
  uats_with_data: number;
  unmatched_cuis: number;
}

export interface CountyStats {
  county: string;
  uat_count: number;
  total_contracts: number;
  total_value: number;
}

export interface FeedItem {
  id: number;
  type: string;
  name: string;
  value: number;
  date: string;
  authority_name: string;
  authority_county: string;
  supplier_name: string | null;
  cpv_code: string;
  cpv_name: string;
  seap_url: string | null;
}

/** Choropleth data: value per UAT */
export async function getChoroplethData(year?: number): Promise<UatStats[]> {
  if (year) {
    const result = await query<UatStats>(`
      SELECT
        u.siruta, u.name AS uat_name, u.county,
        COUNT(*) FILTER (WHERE a.type = 'da')::int AS da_count,
        COALESCE(SUM(a.awarded_value) FILTER (WHERE a.type = 'da'), 0)::numeric AS da_total_value,
        COUNT(*) FILTER (WHERE a.type IN ('contract','atribuire_fara'))::int AS notice_count,
        COALESCE(SUM(a.awarded_value) FILTER (WHERE a.type IN ('contract','atribuire_fara')), 0)::numeric AS notice_total_value,
        COUNT(*)::int AS total_contracts,
        COALESCE(SUM(COALESCE(a.awarded_value, a.estimated_value)), 0)::numeric AS total_value
      FROM public."GisUat" u
      JOIN seap.announcements a ON a.authority_siruta = u.siruta
      WHERE EXTRACT(YEAR FROM COALESCE(a.publication_date, a.contract_date::timestamptz)) = $1
      GROUP BY u.siruta, u.name, u.county
      HAVING COUNT(*) > 0
    `, [year]);
    return result.rows;
  }

  const result = await query<UatStats>(
    'SELECT * FROM seap.uat_procurement_stats WHERE total_contracts > 0'
  );
  return result.rows;
}

export type ChoroplethMetric = 'value' | 'direct' | 'hhi' | 'topshare' | 'q4spike';

export interface UatMetric {
  siruta: string;
  metric_value: number;
  total_contracts: number;
  total_value: number;
  distinct_suppliers: number;
}

export interface FlowEdge {
  supplier_siruta: string;
  supplier_name: string;
  supplier_lng: number;
  supplier_lat: number;
  contracts: number;
  total_value: number;
  last_contract: string | null;
}

export interface FlowGraph {
  authority: { siruta: string; name: string; lng: number; lat: number } | null;
  flows: FlowEdge[];
}

/** Top supplier flows for an authority UAT — for /harta flow lines overlay */
export async function getUatFlows(siruta: string, limit = 10): Promise<FlowGraph> {
  const authResult = await query<any>(`
    SELECT
      siruta,
      name,
      ST_X(ST_Centroid(ST_Transform(geom, 4326)))::float8 AS lng,
      ST_Y(ST_Centroid(ST_Transform(geom, 4326)))::float8 AS lat
    FROM public."GisUat"
    WHERE siruta = $1
  `, [siruta]);
  const authority = authResult.rows[0] || null;

  const flowsResult = await query<any>(`
    SELECT
      a.supplier_siruta,
      MIN(a.supplier_name) AS supplier_name,
      ST_X(ST_Centroid(ST_Transform(gu.geom, 4326)))::float8 AS supplier_lng,
      ST_Y(ST_Centroid(ST_Transform(gu.geom, 4326)))::float8 AS supplier_lat,
      COUNT(*)::int AS contracts,
      COALESCE(SUM(a.awarded_value), 0)::numeric AS total_value,
      MAX(a.publication_date)::text AS last_contract
    FROM seap.announcements a
    JOIN public."GisUat" gu ON gu.siruta = a.supplier_siruta
    WHERE a.authority_siruta = $1
      AND a.supplier_siruta IS NOT NULL
      AND a.supplier_siruta != $1
    GROUP BY a.supplier_siruta, gu.geom
    ORDER BY total_value DESC NULLS LAST
    LIMIT $2
  `, [siruta, limit]);

  return {
    authority,
    flows: flowsResult.rows,
  };
}

/** Per-UAT metric for /harta v2 dual-mode choropleth */
export async function getUatMetric(metric: ChoroplethMetric): Promise<UatMetric[]> {
  const minContracts = (metric === 'value') ? 0 : 5; // Filter sparse UATs for risk indicators

  const expr = (() => {
    switch (metric) {
      case 'value':    return 'total_value';
      case 'direct':   return 'direct_pct';
      case 'hhi':      return 'hhi_suppliers';
      case 'topshare': return 'top_supplier_share';
      case 'q4spike':  return 'COALESCE(q4_spike, 0)';
      default:         return 'total_value';
    }
  })();

  const result = await query<UatMetric>(`
    SELECT
      siruta,
      ${expr}::numeric AS metric_value,
      total_contracts,
      total_value,
      distinct_suppliers
    FROM seap.uat_kpi
    WHERE total_contracts >= $1
  `, [minContracts]);
  return result.rows;
}

/** National aggregate stats */
export async function getNationalStats(): Promise<NationalStats> {
  const result = await query<NationalStats>(`
    SELECT
      (SELECT COUNT(DISTINCT authority_cui) FROM seap.announcements WHERE authority_cui IS NOT NULL)::int AS total_authorities,
      (SELECT COUNT(DISTINCT supplier_cui) FROM seap.announcements WHERE supplier_cui IS NOT NULL)::int AS total_suppliers,
      (SELECT COUNT(*) FROM seap.announcements WHERE type = 'da')::int AS total_da,
      (SELECT COUNT(*) FROM seap.announcements WHERE type IN ('contract','atribuire_fara'))::int AS total_contracts,
      (SELECT COUNT(*) FROM seap.announcements WHERE type = 'initiere')::int AS total_initiere,
      (SELECT COUNT(*) FROM seap.announcements)::int AS total_all,
      (SELECT COALESCE(SUM(COALESCE(awarded_value, estimated_value)), 0) FROM seap.announcements)::numeric AS total_value,
      (SELECT COUNT(*) FROM seap.uat_procurement_stats WHERE total_contracts > 0)::int AS uats_with_data,
      (SELECT COUNT(DISTINCT authority_cui) FROM seap.announcements WHERE authority_cui IS NOT NULL AND authority_siruta IS NULL)::int AS unmatched_cuis
  `);
  return result.rows[0];
}

/** Stats per county */
export async function getCountyStats(): Promise<CountyStats[]> {
  const result = await query<CountyStats>(`
    SELECT county,
      COUNT(siruta)::int AS uat_count,
      SUM(total_contracts)::int AS total_contracts,
      SUM(total_value)::numeric AS total_value
    FROM seap.uat_procurement_stats
    WHERE total_contracts > 0
    GROUP BY county
    ORDER BY total_value DESC
  `);
  return result.rows;
}

/** Detail for a specific UAT */
export async function getUatDetail(siruta: string) {
  const [stats, topSuppliers, recent, byType] = await Promise.all([
    query('SELECT * FROM seap.uat_procurement_stats WHERE siruta = $1', [siruta]),

    query(`
      SELECT a.supplier_cui, a.supplier_name,
        COUNT(*)::int AS contract_count,
        SUM(COALESCE(a.awarded_value, 0))::numeric AS total_value
      FROM seap.announcements a
      WHERE a.authority_siruta = $1 AND a.supplier_name IS NOT NULL
      GROUP BY a.supplier_cui, a.supplier_name
      ORDER BY total_value DESC
      LIMIT 10
    `, [siruta]),

    query(`
      SELECT a.id, a.type, a.title AS name,
        COALESCE(a.awarded_value, a.estimated_value) AS value,
        COALESCE(a.publication_date, a.contract_date::timestamptz) AS date,
        a.authority_name, a.supplier_name,
        a.cpv_code, a.cpv_name, a.seap_url
      FROM seap.announcements a
      WHERE a.authority_siruta = $1
      ORDER BY COALESCE(a.publication_date, a.contract_date::timestamptz) DESC
      LIMIT 20
    `, [siruta]),

    query(`
      SELECT a.type, COUNT(*)::int AS count,
        SUM(COALESCE(a.awarded_value, a.estimated_value))::numeric AS value
      FROM seap.announcements a
      WHERE a.authority_siruta = $1
      GROUP BY a.type ORDER BY value DESC
    `, [siruta]),
  ]);

  return {
    stats: stats.rows[0] || null,
    topSuppliers: topSuppliers.rows,
    recentAcquisitions: recent.rows,
    byType: byType.rows,
  };
}

/** Live feed */
export async function getFeed(limit = 20): Promise<FeedItem[]> {
  const result = await query<FeedItem>(`
    SELECT a.id, a.type, a.title AS name,
      COALESCE(a.awarded_value, a.estimated_value) AS value,
      COALESCE(a.publication_date, a.contract_date::timestamptz) AS date,
      a.authority_name, cl.county AS authority_county,
      a.supplier_name, a.cpv_code, a.cpv_name, a.seap_url
    FROM seap.announcements a
    LEFT JOIN seap.cui_location cl ON cl.cui = a.authority_cui
    ORDER BY COALESCE(a.publication_date, a.contract_date::timestamptz) DESC
    LIMIT $1
  `, [limit]);
  return result.rows;
}

/** Single announcement with full details */
export async function getAnnouncementDetail(id: number) {
  const result = await query(`
    SELECT
      a.*,
      cl.city AS authority_city,
      cl.county AS authority_county_name,
      scl.city AS supplier_city,
      scl.county AS supplier_county_name
    FROM seap.announcements a
    LEFT JOIN seap.cui_location cl ON cl.cui = a.authority_cui
    LEFT JOIN seap.cui_location scl ON scl.cui = a.supplier_cui
    WHERE a.id = $1
  `, [id]);
  return result.rows[0] || null;
}
