/**
 * Support system metrics queries.
 *
 * All queries accept a `from`/`to` date range. Results are computed server-side
 * via parameterized SQL. No PII is returned.
 */

import { and, gte, lte, sql, count } from 'drizzle-orm';
import type { DrizzleClient } from '../client.js';
import { executeRows, firstExecuteRow } from '../execute-rows.js';
import { supportTickets, transactionCases, caseResolutions } from '../schema.js';

// ─── Types ─────────────────────────────────────────────────────────────────────

export type TicketStatus =
  | 'open'
  | 'ai_handling'
  | 'awaiting_user'
  | 'escalated'
  | 'awaiting_agent'
  | 'human_handling'
  | 'resolved'
  | 'closed'
  | 'reopened';

export type CaseStatus =
  | 'opened'
  | 'vendor_review'
  | 'vendor_offered'
  | 'escalated'
  | 'ai_handling'
  | 'awaiting_info'
  | 'human_review'
  | 'resolved'
  | 'closed'
  | 'reopened';

export type CaseOfferOutcome = 'refund_full' | 'refund_partial' | 'replacement' | 'deny';

export interface SupportMetricsResult {
  counts: {
    tickets: Record<TicketStatus, number>;
    cases: Record<CaseStatus, number>;
  };
  slaCompliancePct: { tickets: number; cases: number };
  aiHandoffRatePct: number;
  aiAutoResolveRatePct: number;
  refundOutcomeMix: Record<CaseOfferOutcome, number>;
  meanTimeToResolveMs: { tickets: number; cases: number };
  perVendorMissRate: Array<{
    vendorId: string;
    vendorName: string;
    missCount: number;
    totalCount: number;
    missPct: number;
  }>;
  tokenCostShekelsPerCase: number;
  timeSeries: Array<{
    date: string;
    ticketsOpened: number;
    casesOpened: number;
    slaBreaches: number;
  }>;
}

// ─── Helpers ───────────────────────────────────────────────────────────────────

function zeroTicketCounts(): Record<TicketStatus, number> {
  return {
    open: 0,
    ai_handling: 0,
    awaiting_user: 0,
    escalated: 0,
    awaiting_agent: 0,
    human_handling: 0,
    resolved: 0,
    closed: 0,
    reopened: 0,
  };
}

function zeroCaseCounts(): Record<CaseStatus, number> {
  return {
    opened: 0,
    vendor_review: 0,
    vendor_offered: 0,
    escalated: 0,
    ai_handling: 0,
    awaiting_info: 0,
    human_review: 0,
    resolved: 0,
    closed: 0,
    reopened: 0,
  };
}

function zeroOutcomeMix(): Record<CaseOfferOutcome, number> {
  return { refund_full: 0, refund_partial: 0, replacement: 0, deny: 0 };
}

// ─── Individual metric functions ───────────────────────────────────────────────

export async function getTicketCounts(
  db: DrizzleClient,
  from: Date,
  to: Date,
): Promise<Record<TicketStatus, number>> {
  const rows = await db
    .select({ status: supportTickets.status, n: count() })
    .from(supportTickets)
    .where(and(gte(supportTickets.createdAt, from), lte(supportTickets.createdAt, to)))
    .groupBy(supportTickets.status);

  const result = zeroTicketCounts();
  for (const r of rows) {
    result[r.status as TicketStatus] = Number(r.n);
  }
  return result;
}

export async function getCaseCounts(
  db: DrizzleClient,
  from: Date,
  to: Date,
): Promise<Record<CaseStatus, number>> {
  const rows = await db
    .select({ status: transactionCases.status, n: count() })
    .from(transactionCases)
    .where(and(gte(transactionCases.createdAt, from), lte(transactionCases.createdAt, to)))
    .groupBy(transactionCases.status);

  const result = zeroCaseCounts();
  for (const r of rows) {
    result[r.status as CaseStatus] = Number(r.n);
  }
  return result;
}

export async function getSlaCompliance(
  db: DrizzleClient,
  from: Date,
  to: Date,
): Promise<{ tickets: number; cases: number }> {
  // Tickets: support_tickets has no SLA deadline columns — SLA tracking not yet implemented.
  // Return 0% compliance (0 compliant out of N closed) so the shape matches the API contract.
  const ticketRows = await db.execute(
    sql`SELECT
      COUNT(*) FILTER (WHERE closed_at IS NOT NULL) AS total,
      0 AS compliant
    FROM support_tickets
    WHERE created_at >= ${from} AND created_at <= ${to}`,
  );
  // Unused beyond confirming the query runs without error; value drives future SLA work.
  void firstExecuteRow<{ total: string; compliant: string }>(ticketRows);
  const ticketPct = 0; // no SLA columns on support_tickets; always 0 until columns are added

  // Cases: closed_at <= sla_human_due_at (or sla_ai_due_at if no human due, or vendor_window_expires_at)
  const caseRows = await db.execute(
    sql`SELECT
      COUNT(*) FILTER (WHERE closed_at IS NOT NULL) AS total,
      COUNT(*) FILTER (
        WHERE closed_at IS NOT NULL AND
          closed_at <= COALESCE(sla_human_due_at, sla_ai_due_at, vendor_window_expires_at)
      ) AS compliant
    FROM transaction_cases
    WHERE created_at >= ${from} AND created_at <= ${to}`,
  );
  const cr = firstExecuteRow<{ total: string; compliant: string }>(caseRows);
  const casePct = cr && Number(cr.total) > 0 ? (Number(cr.compliant) / Number(cr.total)) * 100 : 0;

  return { tickets: Math.round(ticketPct * 10) / 10, cases: Math.round(casePct * 10) / 10 };
}

export async function getAiHandoffRate(db: DrizzleClient, from: Date, to: Date): Promise<number> {
  const rows = await db.execute(
    sql`SELECT
      COUNT(*) AS total,
      COUNT(*) FILTER (WHERE decision = 'escalate') AS handoffs
    FROM ai_interventions
    WHERE created_at >= ${from} AND created_at <= ${to}`,
  );
  const r = firstExecuteRow<{ total: string; handoffs: string }>(rows);
  if (!r || Number(r.total) === 0) return 0;
  return Math.round((Number(r.handoffs) / Number(r.total)) * 1000) / 10;
}

export async function getAiAutoResolveRate(
  db: DrizzleClient,
  from: Date,
  to: Date,
): Promise<number> {
  const rows = await db.execute(
    sql`SELECT
      COUNT(*) AS total,
      COUNT(*) FILTER (WHERE decision = 'auto') AS autos
    FROM ai_interventions
    WHERE created_at >= ${from} AND created_at <= ${to}`,
  );
  const r = firstExecuteRow<{ total: string; autos: string }>(rows);
  if (!r || Number(r.total) === 0) return 0;
  return Math.round((Number(r.autos) / Number(r.total)) * 1000) / 10;
}

export async function getRefundOutcomeMix(
  db: DrizzleClient,
  from: Date,
  to: Date,
): Promise<Record<CaseOfferOutcome, number>> {
  const rows = await db
    .select({ outcome: caseResolutions.outcome, n: count() })
    .from(caseResolutions)
    .where(and(gte(caseResolutions.executedAt, from), lte(caseResolutions.executedAt, to)))
    .groupBy(caseResolutions.outcome);

  const result = zeroOutcomeMix();
  for (const r of rows) {
    result[r.outcome as CaseOfferOutcome] = Number(r.n);
  }
  return result;
}

export async function getMeanTimeToResolve(
  db: DrizzleClient,
  from: Date,
  to: Date,
): Promise<{ tickets: number; cases: number }> {
  const ticketRows = await db.execute(
    sql`SELECT AVG(EXTRACT(EPOCH FROM (resolved_at - created_at)) * 1000) AS mttr_ms
        FROM support_tickets
        WHERE created_at >= ${from} AND created_at <= ${to} AND resolved_at IS NOT NULL`,
  );
  const tr = firstExecuteRow<{ mttr_ms: string | null }>(ticketRows);
  const ticketMs = tr?.mttr_ms != null ? Math.round(Number(tr.mttr_ms)) : 0;

  const caseRows = await db.execute(
    sql`SELECT AVG(EXTRACT(EPOCH FROM (resolved_at - created_at)) * 1000) AS mttr_ms
        FROM transaction_cases
        WHERE created_at >= ${from} AND created_at <= ${to} AND resolved_at IS NOT NULL`,
  );
  const cr = firstExecuteRow<{ mttr_ms: string | null }>(caseRows);
  const caseMs = cr?.mttr_ms != null ? Math.round(Number(cr.mttr_ms)) : 0;

  return { tickets: ticketMs, cases: caseMs };
}

export async function getPerVendorMissRate(
  db: DrizzleClient,
  from: Date,
  to: Date,
): Promise<SupportMetricsResult['perVendorMissRate']> {
  const rows = await db.execute(
    sql`SELECT
      tc.vendor_id AS vendor_id,
      v.business_name AS vendor_name,
      COUNT(*) AS total_count,
      COUNT(*) FILTER (WHERE tc.escape_used = true) AS miss_count
    FROM transaction_cases tc
    LEFT JOIN vendors v ON v.id = tc.vendor_id
    WHERE tc.created_at >= ${from} AND tc.created_at <= ${to}
    GROUP BY tc.vendor_id, v.business_name
    HAVING COUNT(*) > 0
    ORDER BY miss_count DESC
    LIMIT 50`,
  );
  return executeRows<{
    vendor_id: string;
    vendor_name: string | null;
    total_count: string;
    miss_count: string;
  }>(rows).map((r) => ({
    vendorId: r.vendor_id,
    vendorName: r.vendor_name ?? r.vendor_id,
    totalCount: Number(r.total_count),
    missCount: Number(r.miss_count),
    missPct:
      Number(r.total_count) > 0
        ? Math.round((Number(r.miss_count) / Number(r.total_count)) * 1000) / 10
        : 0,
  }));
}

export async function getTokenCostPerCase(
  db: DrizzleClient,
  from: Date,
  to: Date,
  usdIlsRate = 3.7,
): Promise<number> {
  const rows = await db.execute(
    sql`SELECT
      SUM(CAST(cost_usd AS NUMERIC)) AS total_usd,
      COUNT(DISTINCT parent_id) FILTER (WHERE parent_type = 'case') AS distinct_cases
    FROM ai_interventions
    WHERE created_at >= ${from} AND created_at <= ${to}`,
  );
  const r = firstExecuteRow<{ total_usd: string | null; distinct_cases: string }>(rows);
  if (!r || !r.total_usd || Number(r.distinct_cases) === 0) return 0;
  return Math.round(((Number(r.total_usd) * usdIlsRate) / Number(r.distinct_cases)) * 100) / 100;
}

export async function getTimeSeries(
  db: DrizzleClient,
  from: Date,
  to: Date,
): Promise<SupportMetricsResult['timeSeries']> {
  const ticketSeries = await db.execute(
    sql`SELECT
      DATE_TRUNC('day', created_at)::date::text AS date,
      COUNT(*) AS opened
    FROM support_tickets
    WHERE created_at >= ${from} AND created_at <= ${to}
    GROUP BY 1 ORDER BY 1`,
  );

  const caseSeries = await db.execute(
    sql`SELECT
      DATE_TRUNC('day', created_at)::date::text AS date,
      COUNT(*) AS opened
    FROM transaction_cases
    WHERE created_at >= ${from} AND created_at <= ${to}
    GROUP BY 1 ORDER BY 1`,
  );

  // SLA breaches: support_state_transitions where to_state contains 'breached'
  // Approximate: cases where sla_ai_due_at < now() and not yet resolved, per day
  const slaBreachSeries = await db.execute(
    sql`SELECT
      DATE_TRUNC('day', created_at)::date::text AS date,
      COUNT(*) AS breaches
    FROM support_state_transitions
    WHERE created_at >= ${from} AND created_at <= ${to}
      AND to_state LIKE '%breached%'
    GROUP BY 1 ORDER BY 1`,
  );

  // Merge into a single series keyed by date
  const byDate = new Map<
    string,
    { ticketsOpened: number; casesOpened: number; slaBreaches: number }
  >();

  const ensureDate = (d: string) => {
    if (!byDate.has(d)) byDate.set(d, { ticketsOpened: 0, casesOpened: 0, slaBreaches: 0 });
    return byDate.get(d)!;
  };

  for (const r of executeRows<{ date: string; opened: string }>(ticketSeries)) {
    ensureDate(r.date).ticketsOpened = Number(r.opened);
  }
  for (const r of executeRows<{ date: string; opened: string }>(caseSeries)) {
    ensureDate(r.date).casesOpened = Number(r.opened);
  }
  for (const r of executeRows<{ date: string; breaches: string }>(slaBreachSeries)) {
    ensureDate(r.date).slaBreaches = Number(r.breaches);
  }

  return Array.from(byDate.entries())
    .sort(([a], [b]) => a.localeCompare(b))
    .map(([date, v]) => ({ date, ...v }));
}

// ─── Aggregator ────────────────────────────────────────────────────────────────

export async function computeAllMetrics(
  db: DrizzleClient,
  { from, to }: { from: Date; to: Date },
  usdIlsRate = 3.7,
): Promise<SupportMetricsResult> {
  const [
    ticketCounts,
    caseCounts,
    slaCompliance,
    aiHandoffRatePct,
    aiAutoResolveRatePct,
    refundOutcomeMix,
    meanTimeToResolveMs,
    perVendorMissRate,
    tokenCostShekelsPerCase,
    timeSeries,
  ] = await Promise.all([
    getTicketCounts(db, from, to),
    getCaseCounts(db, from, to),
    getSlaCompliance(db, from, to),
    getAiHandoffRate(db, from, to),
    getAiAutoResolveRate(db, from, to),
    getRefundOutcomeMix(db, from, to),
    getMeanTimeToResolve(db, from, to),
    getPerVendorMissRate(db, from, to),
    getTokenCostPerCase(db, from, to, usdIlsRate),
    getTimeSeries(db, from, to),
  ]);

  return {
    counts: { tickets: ticketCounts, cases: caseCounts },
    slaCompliancePct: slaCompliance,
    aiHandoffRatePct,
    aiAutoResolveRatePct,
    refundOutcomeMix,
    meanTimeToResolveMs,
    perVendorMissRate,
    tokenCostShekelsPerCase,
    timeSeries,
  };
}