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

import type { PriceHistoryRow, PriceObservation, PriceState } from "@/interfaces";
import { isPriceChange } from "@/services/price-diff";

interface PriceHistoryRecord {
  row_id: number;
  id: string;
  price_yen: number;
  on_promo: number;
  observed_at: number;
  source: "listing" | "detail";
}

const toRow = (r: PriceHistoryRecord): PriceHistoryRow => ({
  id: r.id,
  priceYen: r.price_yen,
  onPromo: r.on_promo === 1,
  observedAt: r.observed_at,
  source: r.source
});

export interface RecordResult {
  changed: boolean;
  previous: PriceState | null;
}

/**
 * Append-on-change: inserts a price_history row only when
 * (price_yen, on_promo) differs from the most recent row for this work.
 * On every successful observation it also updates works.latest_* columns.
 *
 * @returns Whether a new row was appended plus the prior state (if any).
 */
export const recordPriceIfChanged = (
  db: Database,
  obs: PriceObservation,
  source: "listing" | "detail"
): RecordResult => {
  const prevRec = db
    .query<
      PriceHistoryRecord,
      [string]
    >(`SELECT * FROM price_history WHERE id = ? ORDER BY observed_at DESC LIMIT 1`)
    .get(obs.id);
  const previous: PriceState | null = prevRec
    ? { priceYen: prevRec.price_yen, onPromo: prevRec.on_promo === 1 }
    : null;
  const next: PriceState = { priceYen: obs.priceYen, onPromo: obs.onPromo };

  const changed = isPriceChange(previous, next);
  if (!changed) return { changed: false, previous };

  db.transaction(() => {
    db.query(
      `INSERT INTO price_history (id, price_yen, on_promo, observed_at, source) VALUES (?, ?, ?, ?, ?)`
    ).run(obs.id, obs.priceYen, obs.onPromo ? 1 : 0, obs.observedAt, source);
    db.query(
      `UPDATE works SET latest_price_yen = ?, latest_on_promo = ?, latest_priced_at = ? WHERE id = ?`
    ).run(obs.priceYen, obs.onPromo ? 1 : 0, obs.observedAt, obs.id);
  })();

  return { changed: true, previous };
};

export const getTimeline = (db: Database, id: string): PriceHistoryRow[] => {
  const records = db
    .query<
      PriceHistoryRecord,
      [string]
    >(`SELECT * FROM price_history WHERE id = ? ORDER BY observed_at ASC`)
    .all(id);
  return records.map(toRow);
};