import type { DatabaseSync } from 'node:sqlite';
import { asRow, asRows } from '../row.ts';
import type { ItemRow } from '../../types/db.js';

export interface NewItem {
  feed_id: number;
  dedup_key: string;
  url: string;
  title: string;
  published_at: string;
  fetched_at: string;
  guid?: string | null;
  content_html?: string | null;
  content_text?: string | null;
  author?: string | null;
}

export interface ItemCursor {
  publishedAt: string;
  id: number;
}

export interface ListItemsOptions {
  feedIds: number[];
  cursor?: ItemCursor | null;
  limit?: number;
}

export function insertItem(db: DatabaseSync, item: NewItem): ItemRow | null {
  try {
    const info = db
      .prepare(
        `
      INSERT INTO items (feed_id, dedup_key, guid, url, title, content_html, content_text, author, published_at, fetched_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `,
      )
      .run(
        item.feed_id,
        item.dedup_key,
        item.guid ?? null,
        item.url,
        item.title,
        item.content_html ?? null,
        item.content_text ?? null,
        item.author ?? null,
        item.published_at,
        item.fetched_at,
      );
    return getItemById(db, Number(info.lastInsertRowid));
  } catch (e) {
    if (e instanceof Error && e.message.includes('UNIQUE')) return null;
    throw e;
  }
}

export function getItemById(db: DatabaseSync, id: number): ItemRow | null {
  return asRow<ItemRow>(db.prepare('SELECT * FROM items WHERE id = ?').get(id)) ?? null;
}

export function listItems(
  db: DatabaseSync,
  { feedIds, cursor, limit = 50 }: ListItemsOptions,
): ItemRow[] {
  if (!feedIds || !feedIds.length) return [];
  const placeholders = feedIds.map(() => '?').join(',');
  let sql = `SELECT * FROM items WHERE feed_id IN (${placeholders})`;
  const params: (string | number)[] = [...feedIds];
  if (cursor) {
    sql += ' AND (published_at < ? OR (published_at = ? AND id < ?))';
    params.push(cursor.publishedAt, cursor.publishedAt, cursor.id);
  }
  sql += ' ORDER BY published_at DESC, id DESC LIMIT ?';
  params.push(limit);
  return asRows<ItemRow>(db.prepare(sql).all(...params));
}

export function deleteOldItems(db: DatabaseSync, beforeIso: string): void {
  db.prepare('DELETE FROM items WHERE published_at < ?').run(beforeIso);
}