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

export type RiskSeverity = 'high' | 'medium' | 'low';

export interface RiskFlag {
  code: string;
  severity: RiskSeverity;
  label: string;
  detail?: number | string | null;
}

export interface RiskContract {
  id: number;
  ref_number: string;
  type: string;
  title: string | null;
  publication_date: string | null;
  deadline_submission: string | null;
  awarded_value: number | null;
  estimated_value: number | null;
  currency: string | null;
  cpv_division: string | null;
  cpv_division_name: string | null;
  authority_name: string | null;
  authority_cui: string | null;
  authority_county: string | null;
  supplier_name: string | null;
  supplier_cui: string | null;
  num_offers: number | null;
  risk_flags: RiskFlag[] | null;
  // Indicator-specific
  savings_pct?: number | null;
  median_value?: number | null;
  ratio_to_median?: number | null;
  days_gap?: number | null;
}

export interface AuthorityConcentration {
  authority_cui: string;
  authority_name: string | null;
  year: number;
  top_supplier_cui: string;
  top_supplier_name: string | null;
  top_supplier_value: number;
  top_supplier_contracts: number;
  year_total: number;
  year_contracts: number;
  top_supplier_share: number;  // 0..1
}

export interface RiskOverview {
  single_bidder: number;
  short_deadline: number;
  suspicious_savings: number;
  authority_concentration: number;
  overprice: number;
  any_flag: number;
}


// ── 1. Single bidder ──
export async function getSingleBidderContracts(
  limit = 50,
  offset = 0,
): Promise<RiskContract[]> {
  const result = await query<RiskContract>(`
    SELECT
      a.id, a.ref_number, a.type, a.title,
      a.publication_date, a.deadline_submission,
      a.awarded_value, a.estimated_value, a.currency,
      a.cpv_division, c.name_ro AS cpv_division_name,
      a.authority_name, a.authority_cui,
      cl.county AS authority_county,
      a.supplier_name, a.supplier_cui,
      a.num_offers,
      a.risk_flags
    FROM seap.v_single_bidder a
    LEFT JOIN seap.cpv_codes c   ON c.code = a.cpv_division
    LEFT JOIN seap.cui_location cl ON cl.cui = a.authority_cui
    ORDER BY a.awarded_value DESC NULLS LAST, a.publication_date DESC NULLS LAST
    LIMIT $1 OFFSET $2
  `, [limit, offset]);
  return result.rows;
}

export async function getSingleBidderCount(): Promise<number> {
  const r = await query<{ n: number }>(`SELECT count(*)::int AS n FROM seap.v_single_bidder`);
  return r.rows[0]?.n ?? 0;
}

export async function getSingleBidderTopAuthorities(
  limit = 10,
): Promise<{ authority_cui: string; authority_name: string; total: number; share: number }[]> {
  const r = await query<{ authority_cui: string; authority_name: string; total: number; share: number }>(`
    WITH per_auth AS (
      SELECT
        authority_cui,
        MIN(authority_name) AS authority_name,
        count(*)::int       AS contracts,
        count(*) FILTER (WHERE
          num_offers = 1
          OR (details IS NOT NULL
              AND jsonb_typeof(details->'all_winners') = 'array'
              AND jsonb_array_length(details->'all_winners') = 1)
        )::int AS single_bidder_contracts
      FROM seap.announcements
      WHERE type = 'ca_notice' AND authority_cui IS NOT NULL
      GROUP BY authority_cui
      HAVING count(*) >= 5
    )
    SELECT
      authority_cui,
      authority_name,
      single_bidder_contracts AS total,
      (single_bidder_contracts::numeric / NULLIF(contracts, 0))::numeric(5,3) AS share
    FROM per_auth
    WHERE single_bidder_contracts >= 3
    ORDER BY share DESC NULLS LAST, total DESC
    LIMIT $1
  `, [limit]);
  return r.rows;
}


// ── 2. Short deadline ──
export async function getShortDeadlineContracts(
  limit = 50,
  offset = 0,
  daysThreshold = 10,
): Promise<RiskContract[]> {
  const r = await query<RiskContract>(`
    SELECT
      a.id, a.ref_number, a.type, a.title,
      a.publication_date, a.deadline_submission,
      a.awarded_value, a.estimated_value, a.currency,
      a.cpv_division, c.name_ro AS cpv_division_name,
      a.authority_name, a.authority_cui,
      cl.county AS authority_county,
      a.supplier_name, a.supplier_cui,
      a.num_offers,
      a.risk_flags,
      EXTRACT(EPOCH FROM (a.deadline_submission - a.publication_date))/86400.0 AS days_gap
    FROM seap.announcements a
    LEFT JOIN seap.cpv_codes c     ON c.code = a.cpv_division
    LEFT JOIN seap.cui_location cl ON cl.cui = a.authority_cui
    WHERE a.type IN ('c_notice','rfq_invitation')
      AND a.publication_date IS NOT NULL
      AND a.deadline_submission IS NOT NULL
      AND (a.deadline_submission - a.publication_date) < make_interval(days => $3)
    ORDER BY (a.deadline_submission - a.publication_date) ASC
    LIMIT $1 OFFSET $2
  `, [limit, offset, daysThreshold]);
  return r.rows;
}

export async function getShortDeadlineCount(daysThreshold = 10): Promise<number> {
  const r = await query<{ n: number }>(`
    SELECT count(*)::int AS n
    FROM seap.announcements
    WHERE type IN ('c_notice','rfq_invitation')
      AND publication_date IS NOT NULL
      AND deadline_submission IS NOT NULL
      AND (deadline_submission - publication_date) < make_interval(days => $1)
  `, [daysThreshold]);
  return r.rows[0]?.n ?? 0;
}


// ── 3. Suspicious savings ──
export async function getSuspiciousSavings(
  limit = 50,
  offset = 0,
): Promise<RiskContract[]> {
  const r = await query<RiskContract>(`
    SELECT
      a.id, a.ref_number, a.type, a.title,
      a.publication_date, a.deadline_submission,
      a.awarded_value, a.estimated_value, a.currency,
      a.cpv_division, c.name_ro AS cpv_division_name,
      a.authority_name, a.authority_cui,
      cl.county AS authority_county,
      a.supplier_name, a.supplier_cui,
      a.num_offers,
      a.risk_flags,
      round(100.0 * (1 - a.awarded_value / NULLIF(a.estimated_value, 0)))::int AS savings_pct
    FROM seap.announcements a
    LEFT JOIN seap.cpv_codes c     ON c.code = a.cpv_division
    LEFT JOIN seap.cui_location cl ON cl.cui = a.authority_cui
    WHERE a.awarded_value IS NOT NULL
      AND a.estimated_value IS NOT NULL
      AND a.awarded_value > 0
      AND a.estimated_value > 0
      AND a.awarded_value < 0.5 * a.estimated_value
    ORDER BY (a.estimated_value - a.awarded_value) DESC NULLS LAST
    LIMIT $1 OFFSET $2
  `, [limit, offset]);
  return r.rows;
}

export async function getSuspiciousSavingsCount(): Promise<number> {
  const r = await query<{ n: number }>(`
    SELECT count(*)::int AS n
    FROM seap.announcements
    WHERE awarded_value IS NOT NULL
      AND estimated_value IS NOT NULL
      AND awarded_value > 0
      AND estimated_value > 0
      AND awarded_value < 0.5 * estimated_value
  `);
  return r.rows[0]?.n ?? 0;
}


// ── 4. Authority supplier concentration ──
export async function getAuthorityConcentrations(
  threshold = 0.6,
  limit = 100,
): Promise<AuthorityConcentration[]> {
  const r = await query<AuthorityConcentration>(`
    SELECT
      authority_cui, authority_name, year,
      top_supplier_cui, top_supplier_name,
      top_supplier_value::float AS top_supplier_value,
      top_supplier_contracts,
      year_total::float    AS year_total,
      year_contracts,
      top_supplier_share::float AS top_supplier_share
    FROM seap.mv_authority_concentration
    WHERE top_supplier_share >= $1
    ORDER BY top_supplier_share DESC, year_total DESC
    LIMIT $2
  `, [threshold, limit]);
  return r.rows;
}

export async function getConcentrationCount(threshold = 0.6): Promise<number> {
  const r = await query<{ n: number }>(`
    SELECT count(*)::int AS n
    FROM seap.mv_authority_concentration
    WHERE top_supplier_share >= $1
  `, [threshold]);
  return r.rows[0]?.n ?? 0;
}


// ── 5. Overpriced (>2x median per CPV) ──
export async function getOverpricedContracts(
  limit = 50,
  offset = 0,
): Promise<RiskContract[]> {
  const r = await query<RiskContract>(`
    SELECT
      a.id, a.ref_number, a.type, a.title,
      a.publication_date, a.deadline_submission,
      a.awarded_value, a.estimated_value, a.currency,
      a.cpv_division, c.name_ro AS cpv_division_name,
      a.authority_name, a.authority_cui,
      cl.county AS authority_county,
      a.supplier_name, a.supplier_cui,
      a.num_offers,
      a.risk_flags,
      m.median_value::float AS median_value,
      LEAST(a.awarded_value / NULLIF(m.median_value, 0), 99999)::numeric(15,2) AS ratio_to_median
    FROM seap.announcements a
    JOIN seap.mv_cpv_median_value m ON m.cpv_division = a.cpv_division
    LEFT JOIN seap.cpv_codes c     ON c.code = a.cpv_division
    LEFT JOIN seap.cui_location cl ON cl.cui = a.authority_cui
    WHERE a.awarded_value IS NOT NULL
      AND a.awarded_value > 0
      AND m.median_value > 0
      AND a.awarded_value > 2 * m.median_value
    ORDER BY (a.awarded_value / NULLIF(m.median_value, 0)) DESC NULLS LAST
    LIMIT $1 OFFSET $2
  `, [limit, offset]);
  return r.rows;
}

export async function getOverpricedCount(): Promise<number> {
  const r = await query<{ n: number }>(`
    SELECT count(*)::int AS n
    FROM seap.announcements a
    JOIN seap.mv_cpv_median_value m ON m.cpv_division = a.cpv_division
    WHERE a.awarded_value IS NOT NULL
      AND a.awarded_value > 0
      AND m.median_value > 0
      AND a.awarded_value > 2 * m.median_value
  `);
  return r.rows[0]?.n ?? 0;
}


// ── Overview: counts for all flags ──
export async function getRiskOverview(): Promise<RiskOverview> {
  const [single, shortD, savings, conc, over, any] = await Promise.all([
    getSingleBidderCount(),
    getShortDeadlineCount(),
    getSuspiciousSavingsCount(),
    getConcentrationCount(),
    getOverpricedCount(),
    query<{ n: number }>(`
      SELECT count(*)::int AS n
      FROM seap.announcements
      WHERE risk_flags IS NOT NULL
        AND jsonb_array_length(risk_flags) > 0
    `).then(r => r.rows[0]?.n ?? 0),
  ]);
  return {
    single_bidder: single,
    short_deadline: shortD,
    suspicious_savings: savings,
    authority_concentration: conc,
    overprice: over,
    any_flag: any,
  };
}


// Risk flags for a given entity (used on /autoritate/[cui] and /firma/[cui])
export async function getEntityRiskFlags(
  side: 'authority' | 'supplier',
  cui: string,
): Promise<{ code: string; count: number; severity: string }[]> {
  const cuiCondition = side === 'authority'
    ? `a.authority_cui = $1`
    : `(a.supplier_cui = $1 OR a.supplier_cui = 'RO ' || $1 OR a.supplier_cui = 'RO' || $1)`;
  const r = await query<{ code: string; count: number; severity: string }>(`
    SELECT
      flag->>'code'     AS code,
      flag->>'severity' AS severity,
      count(*)::int     AS count
    FROM seap.announcements a, jsonb_array_elements(a.risk_flags) flag
    WHERE ${cuiCondition}
      AND a.risk_flags IS NOT NULL
      AND jsonb_array_length(a.risk_flags) > 0
    GROUP BY 1, 2
    ORDER BY count DESC
  `, [cui]);
  return r.rows;
}


// Check if a result row should show a flag chip (used in /cauta)
export function summarizeFlags(flags: RiskFlag[] | null | undefined): {
  topSeverity: RiskSeverity | null;
  count: number;
  labels: string[];
} {
  if (!flags || flags.length === 0) {
    return { topSeverity: null, count: 0, labels: [] };
  }
  const order: Record<RiskSeverity, number> = { high: 3, medium: 2, low: 1 };
  let top: RiskSeverity = 'low';
  for (const f of flags) {
    if (order[f.severity] > order[top]) top = f.severity;
  }
  return {
    topSeverity: top,
    count: flags.length,
    labels: flags.map(f => f.label),
  };
}
