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

// ─── SANKEY ────────────────────────────────────────────────────────────────

export interface SankeyNode {
  id: string;        // unique id, e.g. "county:CJ" / "auth:Primarie" / "supplier:RO123"
  label: string;     // display label
  level: 0 | 1 | 2;  // 0=county, 1=auth_type, 2=supplier
  meta?: {
    code?: string;   // county code or supplier cui
    raw?: string;    // raw value for navigation
  };
}

export interface SankeyLink {
  source: string;
  target: string;
  value: number;
}

export interface SankeyDataset {
  nodes: SankeyNode[];
  links: SankeyLink[];
  totalValue: number;
  topCounties: Array<{ code: string; value: number }>;
}

interface FlowRow {
  county: string;
  auth_type: string;
  supplier: string;
  supplier_cui: string | null;
  value: string;
}

/**
 * Sankey: Județ → Tip autoritate → Top firmă (ultimele N luni)
 *
 * Strategy: select top 8 counties, top 6 auth types, top 30 suppliers
 * (within those filters); rest pooled into "Alții".
 */
export async function getSankeyFlows(months = 12): Promise<SankeyDataset> {
  const result = await query<FlowRow>(`
    SELECT
      coalesce(a.county_code, '?') AS county,
      coalesce(a.authority_type, 'Necategorizat') AS auth_type,
      coalesce(a.supplier_name, '?') AS supplier,
      a.supplier_cui AS supplier_cui,
      sum(a.awarded_value)::numeric AS value
    FROM seap.announcements a
    WHERE a.awarded_value IS NOT NULL
      AND a.publication_date >= now() - ($1 || ' months')::interval
      AND a.county_code IS NOT NULL
      AND a.supplier_name IS NOT NULL
    GROUP BY 1, 2, 3, 4
    HAVING sum(a.awarded_value) > 1000000
    ORDER BY value DESC
    LIMIT 600
  `, [String(months)]);

  const rows = result.rows.map(r => ({
    county: r.county,
    authType: r.auth_type,
    supplier: r.supplier,
    supplierCui: (r.supplier_cui || '').replace(/^RO\s*/i, '').trim() || null,
    value: Number(r.value),
  }));

  // Top-N truncation
  const TOP_COUNTIES = 8;
  const TOP_AUTH = 6;
  const TOP_SUPPLIERS = 24;

  const sumBy = <T,>(arr: T[], k: (x: T) => string, v: (x: T) => number) => {
    const m = new Map<string, number>();
    for (const x of arr) m.set(k(x), (m.get(k(x)) || 0) + v(x));
    return m;
  };

  const countyTotals = sumBy(rows, r => r.county, r => r.value);
  const authTotals = sumBy(rows, r => r.authType, r => r.value);
  const supplierKey = (r: typeof rows[0]) => r.supplierCui ? `cui:${r.supplierCui}` : `name:${r.supplier}`;
  const supplierTotals = sumBy(rows, supplierKey, r => r.value);

  const topCounties = [...countyTotals.entries()].sort((a, b) => b[1] - a[1]).slice(0, TOP_COUNTIES).map(([k]) => k);
  const topAuth = [...authTotals.entries()].sort((a, b) => b[1] - a[1]).slice(0, TOP_AUTH).map(([k]) => k);
  const topSuppliers = [...supplierTotals.entries()].sort((a, b) => b[1] - a[1]).slice(0, TOP_SUPPLIERS).map(([k]) => k);

  const setC = new Set(topCounties);
  const setA = new Set(topAuth);
  const setS = new Set(topSuppliers);

  // Map rows to truncated buckets
  type Bucket = { county: string; authType: string; supplierKey: string; supplierLabel: string; supplierCui: string | null; value: number };
  const bucketed: Bucket[] = rows.map(r => {
    const sk = supplierKey(r);
    return {
      county: setC.has(r.county) ? r.county : 'AL',  // "Alții" judet
      authType: setA.has(r.authType) ? r.authType : 'Alte autorități',
      supplierKey: setS.has(sk) ? sk : 'name:Alți furnizori',
      supplierLabel: setS.has(sk) ? r.supplier : 'Alți furnizori',
      supplierCui: setS.has(sk) ? r.supplierCui : null,
      value: r.value,
    };
  });

  // Build nodes
  const nodes: SankeyNode[] = [];
  const seen = new Set<string>();

  // counties (level 0)
  const countyOrder = [...new Set(bucketed.map(b => b.county))]
    .sort((a, b) => (countyTotals.get(b) || 0) - (countyTotals.get(a) || 0));
  for (const c of countyOrder) {
    const id = `county:${c}`;
    if (!seen.has(id)) {
      nodes.push({ id, label: c === 'AL' ? 'ALȚI JUDEȚE' : c, level: 0, meta: { code: c, raw: c } });
      seen.add(id);
    }
  }
  // auth types (level 1)
  const authOrder = [...new Set(bucketed.map(b => b.authType))]
    .sort((a, b) => (authTotals.get(b) || 0) - (authTotals.get(a) || 0));
  for (const a of authOrder) {
    const id = `auth:${a}`;
    if (!seen.has(id)) {
      nodes.push({ id, label: a, level: 1, meta: { raw: a } });
      seen.add(id);
    }
  }
  // suppliers (level 2)
  const supSeen = new Map<string, { label: string; cui: string | null }>();
  for (const b of bucketed) {
    if (!supSeen.has(b.supplierKey)) supSeen.set(b.supplierKey, { label: b.supplierLabel, cui: b.supplierCui });
  }
  const supplierOrder = [...supSeen.keys()]
    .sort((a, b) => (supplierTotals.get(b) || 0) - (supplierTotals.get(a) || 0));
  for (const sk of supplierOrder) {
    const id = `supplier:${sk}`;
    if (!seen.has(id)) {
      const info = supSeen.get(sk)!;
      nodes.push({ id, label: info.label, level: 2, meta: { code: info.cui || undefined, raw: sk } });
      seen.add(id);
    }
  }

  // Build links — aggregate again to consolidate buckets
  const linkMap = new Map<string, number>();
  const addLink = (s: string, t: string, v: number) => {
    const k = `${s}>>>${t}`;
    linkMap.set(k, (linkMap.get(k) || 0) + v);
  };
  for (const b of bucketed) {
    addLink(`county:${b.county}`, `auth:${b.authType}`, b.value);
    addLink(`auth:${b.authType}`, `supplier:${b.supplierKey}`, b.value);
  }
  const links: SankeyLink[] = [];
  for (const [k, v] of linkMap) {
    const [source, target] = k.split('>>>');
    links.push({ source, target, value: v });
  }

  const totalValue = rows.reduce((a, b) => a + b.value, 0);

  return {
    nodes,
    links,
    totalValue,
    topCounties: [...countyTotals.entries()]
      .sort((a, b) => b[1] - a[1])
      .slice(0, 5)
      .map(([code, value]) => ({ code, value })),
  };
}

// ─── TREEMAP ───────────────────────────────────────────────────────────────

export interface TreemapLeaf {
  code: string;
  name: string;
  emoji: string | null;
  value: number;
  contracts: number;
}

export interface TreemapDataset {
  leaves: TreemapLeaf[];
  totalValue: number;
  totalContracts: number;
}

interface CpvRow {
  cpv_code: string;
  name_ro: string | null;
  emoji: string | null;
  contracts: string;
  value: string;
}

export async function getTreemapData(months = 12): Promise<TreemapDataset> {
  const result = await query<CpvRow>(`
    SELECT
      a.cpv_division AS cpv_code,
      c.name_ro,
      c.emoji,
      count(*)::int AS contracts,
      sum(a.awarded_value)::numeric AS value
    FROM seap.announcements a
    LEFT JOIN seap.cpv_codes c ON c.code = a.cpv_division
    WHERE a.publication_date >= now() - ($1 || ' months')::interval
      AND a.awarded_value IS NOT NULL
      AND a.cpv_division IS NOT NULL
    GROUP BY a.cpv_division, c.name_ro, c.emoji
    ORDER BY value DESC NULLS LAST
    LIMIT 45
  `, [String(months)]);

  const leaves: TreemapLeaf[] = result.rows.map(r => ({
    code: r.cpv_code,
    name: r.name_ro || `CPV ${r.cpv_code}`,
    emoji: r.emoji,
    value: Number(r.value),
    contracts: Number(r.contracts),
  })).filter(l => l.value > 0);

  const totalValue = leaves.reduce((a, b) => a + b.value, 0);
  const totalContracts = leaves.reduce((a, b) => a + b.contracts, 0);

  return { leaves, totalValue, totalContracts };
}

// ─── BAR RACE ─────────────────────────────────────────────────────────────

export interface BarRaceRow {
  supplier: string;
  cui: string | null;
  value: number;       // cumulative through this month
  monthlyValue: number; // value added this month
  rank: number;
}

export interface BarRaceDataset {
  months: string[];                              // ['2024-05','2024-06',...]
  byMonth: Record<string, BarRaceRow[]>;         // top 10 cumulative per month
  topSuppliers: Array<{ name: string; cui: string | null; value: number }>;
}

interface MonthlyRow {
  month: string;
  supplier_name: string;
  supplier_cui: string;
  value: string;
}

export async function getBarRaceData(months = 12): Promise<BarRaceDataset> {
  const result = await query<MonthlyRow>(`
    SELECT
      to_char(date_trunc('month', a.publication_date), 'YYYY-MM') AS month,
      a.supplier_name,
      a.supplier_cui,
      sum(a.awarded_value)::numeric AS value
    FROM seap.announcements a
    WHERE a.publication_date >= now() - ($1 || ' months')::interval
      AND a.awarded_value IS NOT NULL
      AND a.supplier_cui IS NOT NULL
      AND a.supplier_name IS NOT NULL
    GROUP BY 1, 2, 3
    ORDER BY 1, 4 DESC
  `, [String(months)]);

  const norm = (cui: string) => (cui || '').replace(/^RO\s*/i, '').trim();

  // Build month list (sorted)
  const monthSet = new Set<string>();
  for (const r of result.rows) monthSet.add(r.month);
  const monthList = [...monthSet].sort();

  // Cumulative state per supplier
  type State = { name: string; cui: string; cumulative: number; monthly: number };
  const state = new Map<string, State>();
  const monthlyAdded = new Map<string, Map<string, number>>(); // month -> cui -> value added

  for (const r of result.rows) {
    const cui = norm(r.supplier_cui);
    if (!cui) continue;
    if (!monthlyAdded.has(r.month)) monthlyAdded.set(r.month, new Map());
    monthlyAdded.get(r.month)!.set(cui, (monthlyAdded.get(r.month)!.get(cui) || 0) + Number(r.value));
    if (!state.has(cui)) state.set(cui, { name: r.supplier_name, cui, cumulative: 0, monthly: 0 });
  }

  const byMonth: Record<string, BarRaceRow[]> = {};

  for (const m of monthList) {
    // Reset monthly, apply this month's adds
    for (const s of state.values()) s.monthly = 0;
    const adds = monthlyAdded.get(m);
    if (adds) {
      for (const [cui, v] of adds) {
        const s = state.get(cui)!;
        s.cumulative += v;
        s.monthly = v;
      }
    }
    // Pick top 10 by cumulative
    const top = [...state.values()]
      .filter(s => s.cumulative > 0)
      .sort((a, b) => b.cumulative - a.cumulative)
      .slice(0, 10)
      .map((s, i) => ({
        supplier: s.name,
        cui: s.cui,
        value: s.cumulative,
        monthlyValue: s.monthly,
        rank: i + 1,
      }));
    byMonth[m] = top;
  }

  // Final top suppliers list (overall)
  const topSuppliers = [...state.values()]
    .sort((a, b) => b.cumulative - a.cumulative)
    .slice(0, 10)
    .map(s => ({ name: s.name, cui: s.cui, value: s.cumulative }));

  return { months: monthList, byMonth, topSuppliers };
}
