import Database from "better-sqlite3";

/**
 * Stage A measurement (TECHNICAL-SPEC §8, functional spec §9): anonymous counters
 * only — what was searched, what each store answered, and which offers were clicked.
 * No personal data, no cookies, no user accounts. `search_results` doubles as the
 * breakage-detection log (a store whose parse_ok rate drops changed its markup).
 */

export interface ResultLog {
  store: string;
  offersFound: number;
  /** 200 for a live fetch that succeeded, the error status when the store answered
   *  with one, null when nothing reached HTTP (timeout, network error, cache hit). */
  httpStatus: number | null;
  parseOk: boolean;
  cached: boolean;
  durationMs: number;
}

export interface StoreStats {
  store: string;
  results: number;
  withOffers: number;
  errors: number;
  cached: number;
  avgLiveMs: number | null;
  clickouts: number;
}

export interface Stats {
  searches: number;
  /** Searches where no store returned a single offer (bad input or the cross-referencing gap). */
  zeroResultSearches: number;
  /** Searches that led to at least one clickout — the ≥30% success-gate numerator. */
  searchesWithClickout: number;
  clickouts: number;
  stores: StoreStats[];
  topQueries: { query: string; count: number; lastTs: number }[];
}

export class Measurement {
  private db: Database.Database;

  constructor(dbPath: string) {
    this.db = new Database(dbPath);
    this.db.pragma("journal_mode = WAL");
    this.db.exec(`
      CREATE TABLE IF NOT EXISTS searches (
        id                INTEGER PRIMARY KEY AUTOINCREMENT,
        ts                INTEGER NOT NULL,
        raw_query         TEXT NOT NULL,
        normalized_query  TEXT NOT NULL
      );
      CREATE TABLE IF NOT EXISTS search_results (
        search_id    INTEGER,          -- null when the client didn't register the search
        store        TEXT NOT NULL,
        offers_found INTEGER NOT NULL,
        http_status  INTEGER,
        parse_ok     INTEGER NOT NULL,
        cached       INTEGER NOT NULL,
        duration_ms  INTEGER NOT NULL
      );
      CREATE INDEX IF NOT EXISTS idx_search_results_search ON search_results(search_id);
      CREATE TABLE IF NOT EXISTS clickouts (
        id         INTEGER PRIMARY KEY AUTOINCREMENT,
        ts         INTEGER NOT NULL,
        search_id  INTEGER,
        store      TEXT NOT NULL,
        url        TEXT NOT NULL
      );
    `);
  }

  /** Records one user search and returns its id (the `sid` the client passes around). */
  logSearch(rawQuery: string, normalizedQuery: string): number {
    const info = this.db
      .prepare("INSERT INTO searches (ts, raw_query, normalized_query) VALUES (?, ?, ?)")
      .run(Date.now(), rawQuery, normalizedQuery);
    return Number(info.lastInsertRowid);
  }

  logResult(searchId: number | null, r: ResultLog): void {
    this.db
      .prepare(
        `INSERT INTO search_results (search_id, store, offers_found, http_status, parse_ok, cached, duration_ms)
         VALUES (?, ?, ?, ?, ?, ?, ?)`,
      )
      .run(searchId, r.store, r.offersFound, r.httpStatus, r.parseOk ? 1 : 0, r.cached ? 1 : 0, r.durationMs);
  }

  logClickout(searchId: number | null, store: string, url: string): void {
    this.db
      .prepare("INSERT INTO clickouts (ts, search_id, store, url) VALUES (?, ?, ?, ?)")
      .run(Date.now(), searchId, store, url);
  }

  stats(): Stats {
    const one = (sql: string): number =>
      (this.db.prepare(sql).get() as { n: number }).n;

    const searches = one("SELECT COUNT(*) n FROM searches");
    const zeroResultSearches = one(`
      SELECT COUNT(*) n FROM searches s
      WHERE NOT EXISTS (SELECT 1 FROM search_results r WHERE r.search_id = s.id AND r.offers_found > 0)
    `);
    const searchesWithClickout = one(
      "SELECT COUNT(DISTINCT search_id) n FROM clickouts WHERE search_id IS NOT NULL",
    );
    const clickouts = one("SELECT COUNT(*) n FROM clickouts");

    const stores = this.db
      .prepare(
        `SELECT r.store,
                COUNT(*) AS results,
                SUM(r.offers_found > 0) AS withOffers,
                SUM(r.parse_ok = 0) AS errors,
                SUM(r.cached) AS cached,
                CAST(AVG(CASE WHEN r.cached = 0 AND r.parse_ok = 1 THEN r.duration_ms END) AS INTEGER) AS avgLiveMs,
                (SELECT COUNT(*) FROM clickouts c WHERE c.store = r.store) AS clickouts
         FROM search_results r GROUP BY r.store ORDER BY r.store`,
      )
      .all() as StoreStats[];

    const topQueries = (
      this.db
        .prepare(
          `SELECT normalized_query AS query, COUNT(*) AS count, MAX(ts) AS lastTs
           FROM searches GROUP BY normalized_query ORDER BY count DESC, lastTs DESC LIMIT 20`,
        )
        .all() as { query: string; count: number; lastTs: number }[]
    );

    return { searches, zeroResultSearches, searchesWithClickout, clickouts, stores, topQueries };
  }
}