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

export interface SubscriptionWithFeed extends SubscriptionRow {
  url: string;
  title: string | null;
}

export function subscribe(db: DatabaseSync, userId: number, feedId: number): void {
  try {
    db.prepare('INSERT INTO subscriptions (user_id, feed_id, created_at) VALUES (?, ?, ?)').run(
      userId,
      feedId,
      new Date().toISOString(),
    );
  } catch (e) {
    if (!(e instanceof Error && e.message.includes('UNIQUE'))) throw e;
  }
}

export function unsubscribe(db: DatabaseSync, userId: number, feedId: number): void {
  db.prepare('DELETE FROM subscriptions WHERE user_id = ? AND feed_id = ?').run(userId, feedId);
}

export function listFeedIdsForUser(db: DatabaseSync, userId: number): number[] {
  const rows = asRows<{ feed_id: number }>(
    db.prepare('SELECT feed_id FROM subscriptions WHERE user_id = ?').all(userId),
  );
  return rows.map((r) => r.feed_id);
}

export function listSubscriptions(db: DatabaseSync, userId: number): SubscriptionWithFeed[] {
  return asRows<SubscriptionWithFeed>(
    db
      .prepare(
        `
    SELECT s.*, f.url, f.title FROM subscriptions s JOIN feeds f ON f.id = s.feed_id WHERE s.user_id = ?
  `,
      )
      .all(userId),
  );
}

export function isSubscribed(db: DatabaseSync, userId: number, feedId: number): boolean {
  return !!db
    .prepare('SELECT 1 FROM subscriptions WHERE user_id = ? AND feed_id = ?')
    .get(userId, feedId);
}