import type { Database } from "bun:sqlite";

export interface RequestStatsRow {
  raw_input_tokens: number;
  compressed_input_tokens: number;
  raw_output_tokens: number;
  cache_hit_input_tokens: number;
  cache_creation_input_tokens: number;
}

export interface PhantomCounts {
  totalRows: number;
  phantomTotal: number;
  aborted: number;
  missingBaseline: number;
  corruptUsage: number;
}

function phantomPredicateFor(t: string): string {
  const p = t ? `${t}.` : "";
  return [
    `(${p}compressed_input_tokens = 0 AND ${p}raw_output_tokens = 0)`,
    `${p}raw_input_tokens = 0`,
    `${p}compressed_input_tokens < (${p}cache_hit_input_tokens + ${p}cache_creation_input_tokens)`,
  ].join(" OR ");
}

/** Bare-table phantom filter (no alias). */
export const phantomFilterSql = `NOT (${phantomPredicateFor("")})`;

/** Aliased phantom filter — safe with any table alias, no replaceAll cascade. */
export function phantomFilterSqlFor(alias: string): string {
  return `NOT (${phantomPredicateFor(alias)})`;
}

export function isPhantom(row: RequestStatsRow): boolean {
  if (row.compressed_input_tokens === 0 && row.raw_output_tokens === 0) return true;
  if (row.raw_input_tokens === 0) return true;
  if (row.compressed_input_tokens < row.cache_hit_input_tokens + row.cache_creation_input_tokens) return true;
  return false;
}

export function countPhantoms(db: Database): PhantomCounts {
  const predicate = phantomPredicateFor("");
  const row = db.query<PhantomCounts, []>(
    `SELECT
       COUNT(*) AS totalRows,
       SUM(CASE WHEN ${predicate} THEN 1 ELSE 0 END) AS phantomTotal,
       SUM(CASE WHEN (compressed_input_tokens = 0 AND raw_output_tokens = 0) THEN 1 ELSE 0 END) AS aborted,
       SUM(CASE WHEN raw_input_tokens = 0 THEN 1 ELSE 0 END) AS missingBaseline,
       SUM(CASE WHEN compressed_input_tokens < (cache_hit_input_tokens + cache_creation_input_tokens) THEN 1 ELSE 0 END) AS corruptUsage
     FROM requests`,
  ).get();
  return {
    totalRows: row?.totalRows ?? 0,
    phantomTotal: row?.phantomTotal ?? 0,
    aborted: row?.aborted ?? 0,
    missingBaseline: row?.missingBaseline ?? 0,
    corruptUsage: row?.corruptUsage ?? 0,
  };
}