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

import { articleUrl } from "@/crawlers/fc2";
import type { NewMagnetItem, NewReleaseItem, PriceDropItem, WhatsNewPayload } from "@/interfaces";
import { nowMs } from "@/utils/dates";

export interface WhatsNewOptions {
  db: Database;
  author?: string | null;
  sinceMs: number;
}

interface NewReleaseRecord {
  id: string;
  author: string;
  title: string;
  published_at: number | null;
  first_seen_at: number;
  latest_price_yen: number | null;
  latest_on_promo: number;
}

interface PriceDropRecord {
  id: string;
  price_yen: number;
  on_promo: number;
  observed_at: number;
  prev_price: number | null;
  prev_promo: number | null;
  title: string;
  author: string;
}

interface NewMagnetRecord {
  id: string;
  magnet_id: string;
  magnet_url: string;
  first_seen_at: number;
  title: string;
  author: string;
}

export const whatsNew = (opts: WhatsNewOptions): WhatsNewPayload => {
  const { db, author, sinceMs } = opts;
  const authorArg = author ?? null;

  const releaseRows = db
    .query<NewReleaseRecord, [number, string | null, string | null]>(
      `SELECT id, author, title, published_at, first_seen_at,
              latest_price_yen, latest_on_promo
       FROM works
       WHERE first_seen_at >= ?
         AND (? IS NULL OR author = ?)
       ORDER BY first_seen_at DESC`
    )
    .all(sinceMs, authorArg, authorArg);

  const newReleases: NewReleaseItem[] = releaseRows.map((r) => ({
    id: r.id,
    author: r.author,
    title: r.title,
    url: articleUrl(r.id),
    publishedAt: r.published_at,
    firstSeenAt: r.first_seen_at,
    priceYen: r.latest_price_yen,
    onPromo: r.latest_on_promo === 1
  }));

  const dropRows = db
    .query<PriceDropRecord, [number, string | null, string | null]>(
      `WITH ranked AS (
         SELECT ph.id, ph.price_yen, ph.on_promo, ph.observed_at,
                LAG(ph.price_yen) OVER (PARTITION BY ph.id ORDER BY ph.observed_at) AS prev_price,
                LAG(ph.on_promo)  OVER (PARTITION BY ph.id ORDER BY ph.observed_at) AS prev_promo
         FROM price_history ph
       )
       SELECT r.id, r.price_yen, r.on_promo, r.observed_at, r.prev_price, r.prev_promo,
              w.title, w.author
       FROM ranked r
       JOIN works w ON w.id = r.id
       WHERE r.observed_at >= ?
         AND r.prev_price IS NOT NULL
         AND (r.prev_price > r.price_yen OR (r.prev_promo = 0 AND r.on_promo = 1))
         AND (? IS NULL OR w.author = ?)
       ORDER BY r.observed_at DESC`
    )
    .all(sinceMs, authorArg, authorArg);

  const priceDrops: PriceDropItem[] = dropRows.map((r) => {
    const from = { priceYen: r.prev_price ?? r.price_yen, onPromo: r.prev_promo === 1 };
    const to = { priceYen: r.price_yen, onPromo: r.on_promo === 1 };
    const deltaYen = to.priceYen - from.priceYen;
    const pctDrop = from.priceYen > 0 ? (from.priceYen - to.priceYen) / from.priceYen : 0;
    return {
      id: r.id,
      author: r.author,
      title: r.title,
      url: articleUrl(r.id),
      from,
      to,
      changedAt: r.observed_at,
      deltaYen,
      pctDrop
    };
  });

  const magnetRows = db
    .query<NewMagnetRecord, [number, string | null, string | null]>(
      `SELECT m.id, m.magnet_id, m.magnet_url, m.first_seen_at, w.title, w.author
       FROM sukebei_magnets m
       JOIN works w ON w.id = m.id
       WHERE m.first_seen_at >= ?
         AND (? IS NULL OR w.author = ?)
       ORDER BY m.first_seen_at DESC`
    )
    .all(sinceMs, authorArg, authorArg);

  const newMagnets: NewMagnetItem[] = magnetRows.map((r) => ({
    id: r.id,
    author: r.author,
    title: r.title,
    magnetId: r.magnet_id,
    magnetUrl: r.magnet_url,
    firstSeenAt: r.first_seen_at
  }));

  return {
    sinceMs,
    generatedAt: nowMs(),
    author: authorArg,
    newReleases,
    priceDrops,
    newMagnets,
    counts: {
      newReleases: newReleases.length,
      priceDrops: priceDrops.length,
      newMagnets: newMagnets.length
    }
  };
};