import { Database } from "bun:sqlite";
import type { SQLQueryBindings } from "bun:sqlite";
import { join } from "node:path";
import { existsSync } from "node:fs";
import { MODEL_PRICES } from "../pricing/models.ts";
import { phantomFilterSql, phantomFilterSqlFor } from "./phantom.ts";
import { BYTES_PER_TOKEN_FALLBACK, dollarsSavedRow, withFtCostRow } from "./pricing.ts";
import { openStatsDb } from "./writer.ts";

export interface GainReport {
  sessions: number;
  requests: number;
  rawInputTokens: number;
  compressedInputTokens: number;
  savedTokens: number;
  byLayer: Record<string, { savedBytes: number; eventCount: number }>;
  cacheHitRatio: number;
}

export interface GainFilter { sinceIso: string; project?: string }

export interface LayerRow {
  layerId: string;
  events: number;
  tokensBefore: number;
  tokensAfter: number;
  savedTokens: number;
  savedBytes: number;
  eventCount: number;
  pctSaved: number;
}

/**
 * reportByLayer: plan-compat API — accepts dir + {since: timestamp ms}.
 */
export function reportByLayer(dir: string, opts: { since: number; project?: string }): LayerRow[] {
  const db = openStatsDb(dir, { readonly: true });
  try {
    return queryLayerRows(db, new Date(opts.since).toISOString(), opts.project);
  } finally {
    db.close();
  }
}

function queryLayerRows(db: Database, sinceIso: string, project?: string): LayerRow[] {
  const params: SQLQueryBindings[] = [sinceIso];
  let where = `WHERE r.timestamp >= ? AND r.model IS NOT NULL AND ${phantomFilterSqlFor("r")}`;
  if (project) { where += " AND r.project = ?"; params.push(project); }
  const rows = db.query<{ layer_id: string; raw_bytes: number; sent_bytes: number; saved_bytes: number; cnt: number }, SQLQueryBindings[]>(
    `SELECT se.layer_id,
            SUM(se.raw_bytes) AS raw_bytes,
            SUM(se.sent_bytes) AS sent_bytes,
            SUM(se.raw_bytes - se.sent_bytes) AS saved_bytes,
            COUNT(*) AS cnt
     FROM saving_events se
     INNER JOIN requests r ON se.request_id = r.id
     ${where}
     GROUP BY se.layer_id`
  ).all(...params);
  return rows.map((r) => {
    const tokensBefore = Math.floor(r.raw_bytes / BYTES_PER_TOKEN_FALLBACK);
    const tokensAfter = Math.floor(r.sent_bytes / BYTES_PER_TOKEN_FALLBACK);
    const savedTokens = tokensBefore - tokensAfter;
    const pctSaved = tokensBefore > 0 ? savedTokens / tokensBefore : 0;
    return {
      layerId: r.layer_id,
      events: r.cnt,
      tokensBefore,
      tokensAfter,
      savedTokens,
      savedBytes: r.saved_bytes,
      eventCount: r.cnt,
      pctSaved,
    };
  });
}

export function queryGain(db: Database, f: GainFilter): GainReport {
  const params: SQLQueryBindings[] = [f.sinceIso];
  let where = `WHERE timestamp >= ? AND model IS NOT NULL AND ${phantomFilterSql}`;
  if (f.project) { where += " AND project = ?"; params.push(f.project); }
  const agg = db.query(
    `SELECT COUNT(DISTINCT session_id) AS sessions,
            COUNT(*) AS requests,
            COALESCE(SUM(raw_input_tokens), 0) AS raw_input,
            COALESCE(SUM(compressed_input_tokens), 0) AS comp_input,
            COALESCE(SUM(cache_hit_input_tokens), 0) AS cache_hit,
            COALESCE(SUM(cache_creation_input_tokens), 0) AS cache_create
     FROM requests ${where}`
  ).get(...params) as Record<string, unknown>;
  const layerWhere = `WHERE r.timestamp >= ? AND r.model IS NOT NULL AND ${phantomFilterSqlFor("r")}` +
    (f.project ? " AND r.project = ?" : "");
  const layerRows = db.query(
    `SELECT layer_id, SUM(raw_bytes - sent_bytes) AS saved, COUNT(*) AS cnt
     FROM saving_events se
     INNER JOIN requests r ON se.request_id = r.id
     ${layerWhere}
     GROUP BY layer_id`
  ).all(...params) as Record<string, unknown>[];
  const byLayer: GainReport["byLayer"] = {};
  for (const r of layerRows) {
    byLayer[r.layer_id as string] = { savedBytes: Number(r.saved ?? 0), eventCount: Number(r.cnt) };
  }
  const totalCacheable = Number(agg.cache_hit) + Number(agg.cache_create);
  return {
    sessions: Number(agg.sessions ?? 0),
    requests: Number(agg.requests ?? 0),
    rawInputTokens: Number(agg.raw_input ?? 0),
    compressedInputTokens: Number(agg.comp_input ?? 0),
    savedTokens: Number(agg.raw_input ?? 0) - Number(agg.comp_input ?? 0),
    byLayer,
    cacheHitRatio: totalCacheable > 0 ? Number(agg.cache_hit) / totalCacheable : 0,
  };
}

export interface GainTotals {
  rawInputTokens: number;
  compressedInputTokens: number;
  savedTokens: number;
  pctSaved: number;
  cacheHitTokens?: number;
  cacheHitInputTokens: number;
  cacheCreationTokens?: number;
  cacheCreationInputTokens: number;
  cacheHitRatio: number | null;
  outputTokens?: number;
  rawOutputTokens: number;
  sessions: number;
  requests: number;
  dollarsSaved: number;
  /** Models seen in DB that were not in the pricing table. */
  unknownModels: string[];
  /** Authoritative wire bytes saved (from saving_events). */
  wireSavedBytes: number;
  /** Authoritative wire bytes total (from saving_events). */
  wireTotalBytes: number;
}

export interface TopProject { project: string; savedTokens: number; sharePct: number }
export interface TopReadFile { path: string; hits: number; tokens: number }
export interface ProviderRow { provider: string; requests: number; savedTokens: number; sharePct: number }
export interface ContentKindRow {
  kind: string;
  contentKind: string;
  savedTokens: number;
  savedBytes: number;
  eventCount: number;
}

export interface GainSummary {
  window: { sinceMs: number; label: string };
  totals: GainTotals;
  today: GainTotals;
  layers: LayerRow[];
  topProjects: TopProject[];
  topReadFiles: TopReadFile[];
  unknownModels: string[];
  topProviders: ProviderRow[];
  byContentKind?: ContentKindRow[];
}

function startOfTodayMs(): number {
  const d = new Date();
  d.setHours(0, 0, 0, 0);
  return d.getTime();
}

export interface AggregateTotalsOpts {
  since?: string;
  project?: string;
  defaultModel?: string;
}

export function aggregateTotals(db: Database, opts: AggregateTotalsOpts): GainTotals {
  const sinceIso = opts.since ?? "1970-01-01T00:00:00.000Z";
  const params: SQLQueryBindings[] = [sinceIso];
  let where = `WHERE timestamp >= ? AND model IS NOT NULL AND ${phantomFilterSql}`;
  if (opts.project) { where += " AND project = ?"; params.push(opts.project); }
  const agg = db.query<{
    sessions: number; requests: number;
    raw_in: number; comp_in: number; cache_hit: number; cache_create: number; raw_out: number;
  }, SQLQueryBindings[]>(
    `SELECT COUNT(DISTINCT session_id) AS sessions, COUNT(*) AS requests,
            COALESCE(SUM(raw_input_tokens),0) AS raw_in,
            COALESCE(SUM(compressed_input_tokens),0) AS comp_in,
            COALESCE(SUM(cache_hit_input_tokens),0) AS cache_hit,
            COALESCE(SUM(cache_creation_input_tokens),0) AS cache_create,
            COALESCE(SUM(raw_output_tokens),0) AS raw_out
     FROM requests ${where}`
  ).get(...params)!;
  let wireWhere = `WHERE r.timestamp >= ? AND r.model IS NOT NULL AND ${phantomFilterSqlFor("r")}`;
  if (opts.project) wireWhere += " AND r.project = ?";
  const wireAgg = db.query<{ total_raw: number; total_sent: number }, SQLQueryBindings[]>(
    `SELECT COALESCE(SUM(se.raw_bytes), 0) AS total_raw, COALESCE(SUM(se.sent_bytes), 0) AS total_sent
     FROM saving_events se
     INNER JOIN requests r ON se.request_id = r.id
     ${wireWhere}`
  ).get(...params) ?? { total_raw: 0, total_sent: 0 };
  const perModel = db.query<{
    model: string;
    raw_in: number;
    comp_in: number;
    cache_hit: number;
    cache_create: number;
    raw_out: number;
  }, SQLQueryBindings[]>(
    `SELECT COALESCE(model, '<unknown>') AS model,
            COALESCE(SUM(raw_input_tokens), 0) AS raw_in,
            COALESCE(SUM(compressed_input_tokens), 0) AS comp_in,
            COALESCE(SUM(cache_hit_input_tokens), 0) AS cache_hit,
            COALESCE(SUM(cache_creation_input_tokens), 0) AS cache_create,
            COALESCE(SUM(raw_output_tokens), 0) AS raw_out
     FROM requests ${where}
     GROUP BY model`
  ).all(...params);
  let dollars = 0;
  const unknown: string[] = [];
  for (const row of perModel) {
    const price = MODEL_PRICES[row.model] ?? (opts.defaultModel ? MODEL_PRICES[opts.defaultModel] : undefined);
    if (!price) {
      unknown.push(row.model);
      continue;
    }
    dollars += dollarsSavedRow(price, {
      rawInputTokens: row.raw_in,
      compressedInputTokens: row.comp_in,
      cacheHitInputTokens: row.cache_hit,
      cacheCreationInputTokens: row.cache_create,
      rawOutputTokens: row.raw_out,
    });
  }
  const saved = agg.raw_in - agg.comp_in;
  const totalCacheable = agg.cache_hit + agg.cache_create;
  return {
    rawInputTokens: agg.raw_in,
    compressedInputTokens: agg.comp_in,
    savedTokens: saved,
    pctSaved: agg.raw_in > 0 ? saved / agg.raw_in : 0,
    cacheHitTokens: agg.cache_hit,
    cacheHitInputTokens: agg.cache_hit,
    cacheCreationTokens: agg.cache_create,
    cacheCreationInputTokens: agg.cache_create,
    cacheHitRatio: totalCacheable > 0 ? agg.cache_hit / totalCacheable : null,
    outputTokens: agg.raw_out,
    rawOutputTokens: agg.raw_out,
    sessions: agg.sessions,
    requests: agg.requests,
    dollarsSaved: dollars,
    unknownModels: unknown,
    wireSavedBytes: wireAgg.total_raw - wireAgg.total_sent,
    wireTotalBytes: wireAgg.total_raw,
  };
}

function topProjectsQuery(db: Database, sinceIso: string, project?: string): TopProject[] {
  const params: SQLQueryBindings[] = [sinceIso];
  let where = `WHERE timestamp >= ? AND model IS NOT NULL AND ${phantomFilterSql}`;
  if (project) { where += " AND project = ?"; params.push(project); }
  const rows = db.query<{ project: string | null; saved: number }, SQLQueryBindings[]>(
    `SELECT project, COALESCE(SUM(raw_input_tokens - compressed_input_tokens), 0) AS saved
     FROM requests ${where} GROUP BY project ORDER BY saved DESC`
  ).all(...params);
  const total = rows.reduce((a, r) => a + (r.saved ?? 0), 0);
  if (total <= 0) return [];
  const top: TopProject[] = rows.slice(0, 3).map((r) => ({
    project: r.project ?? "<no-project>",
    savedTokens: r.saved ?? 0,
    sharePct: total > 0 ? (r.saved ?? 0) / total : 0,
  }));
  const rest = rows.slice(3).reduce((a, r) => a + (r.saved ?? 0), 0);
  if (rest > 0) top.push({ project: "others", savedTokens: rest, sharePct: total > 0 ? rest / total : 0 });
  return top;
}

function topReadFilesQuery(rootDir: string): TopReadFile[] {
  const cachePath = join(rootDir, "read-cache.db");
  if (!existsSync(cachePath)) return [];
  let db: Database | null = null;
  try {
    db = new Database(cachePath, { readonly: true });
    const rows = db.query<{ path: string; seen: number; tokens: number }, []>(
      `SELECT path, seen_count AS seen, tokens FROM read_cache
       WHERE path != '' ORDER BY (seen_count * tokens) DESC LIMIT 5`
    ).all();
    return rows.map((r) => ({ path: r.path, hits: r.seen, tokens: r.tokens }));
  } catch {
    return [];
  } finally {
    if (db) db.close();
  }
}

function topProvidersQuery(db: Database, sinceIso: string, project?: string): ProviderRow[] {
  const params: SQLQueryBindings[] = [sinceIso];
  let where = `WHERE timestamp >= ? AND ${phantomFilterSql}`;
  if (project) { where += " AND project = ?"; params.push(project); }
  const rows = db.query<{ provider: string | null; requests: number; saved: number }, SQLQueryBindings[]>(
    `SELECT COALESCE(provider, '<unknown>') AS provider,
            COUNT(*) AS requests,
            COALESCE(SUM(raw_input_tokens - compressed_input_tokens), 0) AS saved
     FROM requests ${where}
     GROUP BY provider ORDER BY saved DESC`
  ).all(...params);
  const total = rows.reduce((a, r) => a + (r.saved ?? 0), 0);
  return rows.map((r) => ({
    provider: r.provider ?? "<unknown>",
    requests: r.requests ?? 0,
    savedTokens: r.saved ?? 0,
    sharePct: total > 0 ? (r.saved ?? 0) / total : 0,
  }));
}

function contentKindsQuery(db: Database, sinceIso: string, project?: string): ContentKindRow[] {
  const params: SQLQueryBindings[] = [sinceIso];
  let where = `WHERE r.timestamp >= ? AND r.model IS NOT NULL AND ${phantomFilterSqlFor("r")}`;
  if (project) { where += " AND r.project = ?"; params.push(project); }
  const contentKindExpr = `COALESCE(
      CASE WHEN se.meta IS NOT NULL AND json_valid(se.meta) THEN json_extract(se.meta, '$.contentKind') END,
      CASE WHEN se.meta IS NOT NULL AND json_valid(se.meta) THEN json_extract(se.meta, '$.adapterId') END,
      se.layer_id
    )`;
  const rows = db.query<{ kind: string; saved_bytes: number; cnt: number }, SQLQueryBindings[]>(
    `SELECT CAST(${contentKindExpr} AS TEXT) AS kind,
            COALESCE(SUM(se.raw_bytes - se.sent_bytes), 0) AS saved_bytes,
            COUNT(*) AS cnt
     FROM saving_events se
     INNER JOIN requests r ON se.request_id = r.id
     ${where}
     GROUP BY ${contentKindExpr}
     ORDER BY saved_bytes DESC`
  ).all(...params);
  return rows.map((r) => ({
    kind: r.kind,
    contentKind: r.kind,
    savedTokens: Math.floor((r.saved_bytes ?? 0) / BYTES_PER_TOKEN_FALLBACK),
    savedBytes: r.saved_bytes ?? 0,
    eventCount: r.cnt ?? 0,
  }));
}

export function summary(dir: string, opts: { sinceMs: number; project?: string; defaultModel?: string }): GainSummary {
  const db = openStatsDb(dir, { readonly: true });
  try {
    const sinceIso = new Date(opts.sinceMs).toISOString();
    const todayIso = new Date(startOfTodayMs()).toISOString();
    const allParams: SQLQueryBindings[] = [sinceIso];
    let whereAll = `WHERE r.timestamp >= ? AND r.model IS NOT NULL AND ${phantomFilterSqlFor("r")}`;
    if (opts.project) { whereAll += " AND r.project = ?"; allParams.push(opts.project); }
    const layerRowsRaw = db.query<
      { layer_id: string; raw_bytes: number; sent_bytes: number; saved_bytes: number; cnt: number },
      SQLQueryBindings[]
    >(
      `SELECT se.layer_id,
              SUM(se.raw_bytes) AS raw_bytes,
              SUM(se.sent_bytes) AS sent_bytes,
              SUM(se.raw_bytes - se.sent_bytes) AS saved_bytes,
              COUNT(*) AS cnt
       FROM saving_events se INNER JOIN requests r ON se.request_id = r.id
       ${whereAll} GROUP BY se.layer_id ORDER BY saved_bytes DESC`
    ).all(...allParams);
    const layers: LayerRow[] = layerRowsRaw.map((r) => {
      const tb = Math.floor(r.raw_bytes / BYTES_PER_TOKEN_FALLBACK);
      const ta = Math.floor(r.sent_bytes / BYTES_PER_TOKEN_FALLBACK);
      return {
        layerId: r.layer_id, events: r.cnt, tokensBefore: tb, tokensAfter: ta,
        savedTokens: tb - ta, savedBytes: r.saved_bytes, eventCount: r.cnt,
        pctSaved: tb > 0 ? (tb - ta) / tb : 0,
      };
    });
    const allAgg = aggregateTotals(db, {
      since: sinceIso,
      ...(opts.project !== undefined ? { project: opts.project } : {}),
      ...(opts.defaultModel !== undefined ? { defaultModel: opts.defaultModel } : {}),
    });
    const todayAgg = aggregateTotals(db, {
      since: todayIso,
      ...(opts.project !== undefined ? { project: opts.project } : {}),
      ...(opts.defaultModel !== undefined ? { defaultModel: opts.defaultModel } : {}),
    });
    return {
      window: { sinceMs: opts.sinceMs, label: opts.sinceMs === 0 ? "all-time" : new Date(opts.sinceMs).toISOString() },
      totals: allAgg,
      today: todayAgg,
      layers,
      topProjects: topProjectsQuery(db, sinceIso, opts.project),
      topReadFiles: topReadFilesQuery(dir),
      unknownModels: allAgg.unknownModels,
      topProviders: topProvidersQuery(db, sinceIso, opts.project),
      byContentKind: contentKindsQuery(db, sinceIso, opts.project),
    };
  } finally {
    db.close();
  }
}

// ── focused aggregation queries (Wave 1 B) ─────────────────────────────────

export interface NorthStar {
  tokensSaved: number;   // sum(raw_input_tokens - compressed_input_tokens)
  requests: number;
}

export interface VolumeRow {
  requests: number;
  cacheHitTokens: number;  // sum(cache_hit_input_tokens)
  cacheHitPct: number;     // cacheHitTokens / sum(raw_input_tokens); 0 if no traffic
  avgLatencyMs: number;
  p99LatencyMs: number;
}

export interface ProjectRow {
  project: string;
  requests: number;
  tokensSaved: number;
}

export function reportNorthStar(
  dir: string,
  opts: { since: number; project?: string },
): NorthStar {
  const db = openStatsDb(dir, { readonly: true });
  try {
    return queryNorthStar(db, opts);
  } finally {
    db.close();
  }
}

function queryNorthStar(db: Database, opts: { since: number; project?: string }): NorthStar {
  const sinceIso = new Date(opts.since).toISOString();
  const params: SQLQueryBindings[] = [sinceIso];
  let where = `WHERE timestamp >= ? AND model IS NOT NULL AND ${phantomFilterSql}`;
  if (opts.project) { where += " AND project = ?"; params.push(opts.project); }
  const row = db.query<{ tokensSaved: number; requests: number }, SQLQueryBindings[]>(
    `SELECT COALESCE(SUM(raw_input_tokens - compressed_input_tokens), 0) AS tokensSaved,
            COUNT(*) AS requests
     FROM requests ${where}`,
  ).get(...params);
  return { tokensSaved: row?.tokensSaved ?? 0, requests: row?.requests ?? 0 };
}

export function reportVolume(
  dir: string,
  opts: { since: number; project?: string },
): VolumeRow {
  const db = openStatsDb(dir, { readonly: true });
  try {
    return queryVolume(db, opts);
  } finally {
    db.close();
  }
}

function queryVolume(db: Database, opts: { since: number; project?: string }): VolumeRow {
  const sinceIso = new Date(opts.since).toISOString();
  const params: SQLQueryBindings[] = [sinceIso];
  let where = "WHERE timestamp >= ? AND model IS NOT NULL";
  if (opts.project) { where += " AND project = ?"; params.push(opts.project); }

  const agg = db.query<
    { requests: number; cacheHitTokens: number; rawTokens: number; avgLatencyMs: number },
    SQLQueryBindings[]
  >(
    `SELECT COUNT(*) AS requests,
            COALESCE(SUM(cache_hit_input_tokens), 0) AS cacheHitTokens,
            COALESCE(SUM(raw_input_tokens), 0) AS rawTokens,
            COALESCE(AVG(latency_ms), 0) AS avgLatencyMs
     FROM requests ${where}`,
  ).get(...params);

  if (!agg || agg.requests === 0) {
    return { requests: 0, cacheHitTokens: 0, cacheHitPct: 0, avgLatencyMs: 0, p99LatencyMs: 0 };
  }

  const cacheHitPct = agg.rawTokens > 0 ? agg.cacheHitTokens / agg.rawTokens : 0;

  // p99 via OFFSET: ceil(count * 0.99) - 1 gives the 99th-percentile row index
  const count = agg.requests;
  const offset = Math.max(0, Math.ceil(count * 0.99) - 1);
  const p99Params: SQLQueryBindings[] = [...params, offset];
  const p99Row = db.query<{ latency_ms: number }, SQLQueryBindings[]>(
    `SELECT latency_ms FROM requests ${where} ORDER BY latency_ms LIMIT 1 OFFSET ?`,
  ).get(...p99Params);
  const p99LatencyMs = p99Row?.latency_ms ?? agg.avgLatencyMs;

  return {
    requests: agg.requests,
    cacheHitTokens: agg.cacheHitTokens,
    cacheHitPct,
    avgLatencyMs: agg.avgLatencyMs,
    p99LatencyMs,
  };
}

export function reportProjects(
  dir: string,
  opts: { since: number; limit?: number },
): ProjectRow[] {
  const db = openStatsDb(dir, { readonly: true });
  try {
    return queryProjects(db, opts);
  } finally {
    db.close();
  }
}

function queryProjects(db: Database, opts: { since: number; limit?: number }): ProjectRow[] {
  const sinceIso = new Date(opts.since).toISOString();
  const lim = opts.limit ?? 10;
  return db.query<{ project: string; requests: number; tokensSaved: number }, SQLQueryBindings[]>(
    `SELECT project,
            COUNT(*) AS requests,
            COALESCE(SUM(raw_input_tokens - compressed_input_tokens), 0) AS tokensSaved
     FROM requests
     WHERE timestamp >= ? AND model IS NOT NULL AND project IS NOT NULL
     GROUP BY project
     ORDER BY tokensSaved DESC
     LIMIT ?`,
  ).all(sinceIso, lim);
}

export interface SessionGain {
  sessionId: string;
  requests: number;
  tokensSaved: number;
  dollarsSaved: number;
  dollarsSpentWithFt: number;
  dollarsCounterfactual: number;
}

export function getSessionGain(db: Database, sessionId: string, fallbackModel = "claude-sonnet-4-6"): SessionGain {
  const rows = db.query<{
    raw_input_tokens: number; compressed_input_tokens: number;
    cache_hit_input_tokens: number; cache_creation_input_tokens: number;
    raw_output_tokens: number; model: string | null; provider: string | null;
  }, [string]>(
    `SELECT raw_input_tokens, compressed_input_tokens,
            cache_hit_input_tokens, cache_creation_input_tokens,
            raw_output_tokens, model, provider
       FROM requests
      WHERE session_id = ? AND ${phantomFilterSql}`
  ).all(sessionId);

  let tokensSaved = 0, dollarsSaved = 0, withFt = 0;
  for (const r of rows) {
    const modelKey = r.model ?? fallbackModel;
    const price = MODEL_PRICES[modelKey] ?? MODEL_PRICES[fallbackModel];
    if (!price) continue;
    tokensSaved += r.raw_input_tokens - r.compressed_input_tokens;
    const mirrorInput = {
      rawInputTokens: r.raw_input_tokens,
      compressedInputTokens: r.compressed_input_tokens,
      cacheHitInputTokens: r.cache_hit_input_tokens,
      cacheCreationInputTokens: r.cache_creation_input_tokens,
      rawOutputTokens: r.raw_output_tokens,
    };
    withFt += withFtCostRow(price, mirrorInput);
    dollarsSaved += dollarsSavedRow(price, mirrorInput);
  }

  return {
    sessionId, requests: rows.length, tokensSaved, dollarsSaved,
    dollarsSpentWithFt: withFt, dollarsCounterfactual: withFt + dollarsSaved,
  };
}

export function sessionSavings(db: Database, sessionId: string): number {
  return getSessionGain(db, sessionId).tokensSaved;
}