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

export function markRead(db: DatabaseSync, userId: number, itemId: number): void {
  db.prepare(
    `
    INSERT INTO read_state (user_id, item_id, read_at) VALUES (?, ?, ?)
    ON CONFLICT(user_id, item_id) DO UPDATE SET read_at = excluded.read_at
  `,
  ).run(userId, itemId, new Date().toISOString());
}

export function markUnread(db: DatabaseSync, userId: number, itemId: number): void {
  db.prepare('DELETE FROM read_state WHERE user_id = ? AND item_id = ?').run(userId, itemId);
}

export function isRead(db: DatabaseSync, userId: number, itemId: number): boolean {
  return !!db
    .prepare('SELECT 1 FROM read_state WHERE user_id = ? AND item_id = ?')
    .get(userId, itemId);
}

export function readIdsForUser(db: DatabaseSync, userId: number, itemIds: number[]): Set<number> {
  if (!itemIds.length) return new Set();
  const placeholders = itemIds.map(() => '?').join(',');
  const rows = asRows<{ item_id: number }>(
    db
      .prepare(`SELECT item_id FROM read_state WHERE user_id = ? AND item_id IN (${placeholders})`)
      .all(userId, ...itemIds),
  );
  return new Set(rows.map((r) => r.item_id));
}

export function unreadCount(db: DatabaseSync, userId: number): number {
  const feedRows = asRows<{ feed_id: number }>(
    db.prepare('SELECT feed_id FROM subscriptions WHERE user_id = ?').all(userId),
  );
  const feedIds = feedRows.map((r) => r.feed_id);
  if (!feedIds.length) return 0;
  const placeholders = feedIds.map(() => '?').join(',');
  const total = asRow<{ c: number }>(
    db
      .prepare(`SELECT COUNT(*) AS c FROM items WHERE feed_id IN (${placeholders})`)
      .get(...feedIds),
  );
  const read = asRow<{ c: number }>(
    db
      .prepare(
        `
    SELECT COUNT(*) AS c FROM read_state rs JOIN items i ON i.id = rs.item_id
    WHERE rs.user_id = ? AND i.feed_id IN (${placeholders})
  `,
      )
      .get(userId, ...feedIds),
  );
  return (total?.c ?? 0) - (read?.c ?? 0);
}

export function markAllRead(db: DatabaseSync, userId: number, feedId: number): void {
  const items = asRows<{ id: number }>(
    db.prepare('SELECT id FROM items WHERE feed_id = ?').all(feedId),
  );
  const now = new Date().toISOString();
  for (const it of items) {
    db.prepare(
      `
      INSERT INTO read_state (user_id, item_id, read_at) VALUES (?, ?, ?)
      ON CONFLICT(user_id, item_id) DO UPDATE SET read_at = excluded.read_at
    `,
    ).run(userId, it.id, now);
  }
}