import { all, chunks, first, placeholders } from "./db";
import { accountDb } from "./scoped-db";
import { repoReadableSql } from "./repos";

/**
 * Notifications live in the recipient's AccountState; whether the recipient may still read a notification's
 * repository is a D1 directory question. These helpers apply `inboxReadableSql`'s rule in code: account-level
 * rows always show, repository rows only while the repository is visible to the recipient (site admins see all).
 */
export async function readableRepoIds(userId: string, repoIds: Iterable<string>): Promise<Set<string>> {
  const ids = [...new Set(repoIds)];
  if (ids.length === 0) return new Set();
  const admin = await first<{ ok: number }>("SELECT 1 AS ok FROM users WHERE account_id = ? AND is_admin = 1", userId);
  if (admin) return new Set(ids);
  const out = new Set<string>();
  for (const part of chunks(ids)) {
    const rows = await all<{ id: string }>(
      `SELECT r.id FROM repos r WHERE r.id IN (${placeholders(part.length)}) AND r.hidden_at IS NULL
       AND ${repoReadableSql("r", "?")}`,
      ...part,
      ...Array<string>(repoReadableSqlUserSlots).fill(userId),
    );
    for (const row of rows) out.add(row.id);
  }
  return out;
}

/** `repoReadableSql(alias, "?")` binds the user once per mention. */
const repoReadableSqlUserSlots = (repoReadableSql("r", "?").match(/\?/g) ?? []).length;

/** Keeps rows the recipient can read (see `readableRepoIds`). */
export async function filterReadable<T extends { repo_id: string | null }>(userId: string, rows: T[]): Promise<T[]> {
  const readable = await readableRepoIds(
    userId,
    rows.flatMap((row) => (row.repo_id ? [row.repo_id] : [])),
  );
  return rows.filter((row) => row.repo_id === null || readable.has(row.repo_id));
}

/** Unread notifications the user can still read, for the inbox badge. */
export async function unreadNotificationCount(userId: string): Promise<number> {
  const groups = await accountDb(userId).all<{ repo_id: string | null; count: number }>(
    "SELECT repo_id, COUNT(*) AS count FROM notifications WHERE user_id = ? AND read_at IS NULL GROUP BY repo_id",
    userId,
  );
  const readable = await filterReadable(userId, groups);
  return readable.reduce((sum, group) => sum + group.count, 0);
}