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

import type { FC2Work, WorkRow } from "@/interfaces";

interface WorkRecord {
  id: string;
  author: string;
  title: string;
  duration: string | null;
  published_at: number | null;
  first_seen_at: number;
  last_seen_at: number;
  latest_price_yen: number | null;
  latest_on_promo: number;
  latest_priced_at: number | null;
  detail_fetched_at: number | null;
}

const toRow = (r: WorkRecord): WorkRow => ({
  id: r.id,
  author: r.author,
  title: r.title,
  duration: r.duration,
  publishedAt: r.published_at,
  firstSeenAt: r.first_seen_at,
  lastSeenAt: r.last_seen_at,
  latestPriceYen: r.latest_price_yen,
  latestOnPromo: r.latest_on_promo === 1,
  latestPricedAt: r.latest_priced_at,
  detailFetchedAt: r.detail_fetched_at
});

export const upsertWork = (
  db: Database,
  work: FC2Work
): { inserted: boolean; updated: boolean } => {
  const existing = db
    .query<{ id: string }, [string]>("SELECT id FROM works WHERE id = ?")
    .get(work.id);

  if (!existing) {
    db.query(
      `INSERT INTO works
        (id, author, title, duration, published_at, first_seen_at, last_seen_at)
       VALUES (?, ?, ?, ?, ?, ?, ?)`
    ).run(
      work.id,
      work.author,
      work.title,
      work.duration,
      work.publishedAt,
      work.observedAt,
      work.observedAt
    );
    return { inserted: true, updated: false };
  }

  db.query(
    `UPDATE works SET
       author = ?,
       title = ?,
       duration = COALESCE(?, duration),
       published_at = COALESCE(?, published_at),
       last_seen_at = ?
     WHERE id = ?`
  ).run(work.author, work.title, work.duration, work.publishedAt, work.observedAt, work.id);
  return { inserted: false, updated: true };
};

export interface ListWorksOptions {
  author: string;
  sinceMs?: number;
  untilMs?: number;
}

export const listWorks = (db: Database, opts: ListWorksOptions): WorkRow[] => {
  const clauses: string[] = ["author = ?"];
  const params: (string | number)[] = [opts.author];

  // Filter by published_at when present, fall back to first_seen_at when NULL.
  // COALESCE pushes published_at's NULL to first_seen_at so the inclusive range
  // covers works whose real publish date we never learned.
  if (opts.sinceMs !== undefined) {
    clauses.push("COALESCE(published_at, first_seen_at) >= ?");
    params.push(opts.sinceMs);
  }
  if (opts.untilMs !== undefined) {
    clauses.push("COALESCE(published_at, first_seen_at) <= ?");
    params.push(opts.untilMs);
  }

  const sql =
    `SELECT * FROM works WHERE ${clauses.join(" AND ")} ` +
    `ORDER BY COALESCE(published_at, first_seen_at) DESC`;

  const records = db.query<WorkRecord, (string | number)[]>(sql).all(...params);
  return records.map(toRow);
};

export const getWork = (db: Database, id: string): WorkRow | null => {
  const r = db.query<WorkRecord, [string]>("SELECT * FROM works WHERE id = ?").get(id);
  return r ? toRow(r) : null;
};

export interface LatestPriceInfo {
  priceYen: number | null;
  onPromo: boolean;
  observedAt: number | null;
}

export const latestPrice = (db: Database, id: string): LatestPriceInfo => {
  const r = db
    .query<
      { latest_price_yen: number | null; latest_on_promo: number; latest_priced_at: number | null },
      [string]
    >("SELECT latest_price_yen, latest_on_promo, latest_priced_at FROM works WHERE id = ?")
    .get(id);
  if (!r) return { priceYen: null, onPromo: false, observedAt: null };
  return {
    priceYen: r.latest_price_yen,
    onPromo: r.latest_on_promo === 1,
    observedAt: r.latest_priced_at
  };
};