import { all, first, placeholders } from "../db";
import { accountDb, repoDb } from "../scoped-db";
import { CAPS } from "../limits";
import { repoReadableSql } from "../repos";
import type { DirectNotifyMessage } from "../events/emit";

type Access = DirectNotifyMessage["access"];

const READ_SQL = `SELECT 1 AS ok FROM repos r WHERE r.id = ?1 AND r.hidden_at IS NULL AND ${repoReadableSql("r", "?2")}`;

const ADMIN_SQL = `SELECT 1 AS ok FROM repos r WHERE r.id = ?1 AND (r.owner_id = ?2
  OR EXISTS (SELECT 1 FROM collaborators c WHERE c.repo_id = r.id AND c.user_id = ?2 AND c.permission = 'admin')
  OR EXISTS (SELECT 1 FROM org_members m WHERE m.org_id = r.owner_id AND m.user_id = ?2 AND m.role = 'owner'))`;

/**
 * Org security managers: listed in the org's AccountState, and only while still members of the org (D1).
 * Returns the subset of `userIds` (all managers when omitted) that qualify for the repository's owner.
 */
async function securityManagers(repoId: string, userIds?: string[]): Promise<string[]> {
  const repo = await first<{ owner_id: string }>("SELECT owner_id FROM repos WHERE id = ?", repoId);
  if (!repo) return [];
  const listed = (
    await accountDb(repo.owner_id).all<{ user_id: string }>(
      "SELECT user_id FROM security_managers WHERE account_id = ?",
      repo.owner_id,
    )
  )
    .map((row) => row.user_id)
    .filter((id) => !userIds || userIds.includes(id));
  if (listed.length === 0) return [];
  const members = await all<{ user_id: string }>(
    `SELECT user_id FROM org_members WHERE org_id = ? AND user_id IN (${placeholders(listed.length)})`,
    repo.owner_id,
    ...listed,
  );
  return members.map((row) => row.user_id);
}

async function hasSecurityAccess(repoId: string, userId: string): Promise<boolean> {
  if ((await first(ADMIN_SQL, repoId, userId)) !== null) return true;
  return (await securityManagers(repoId, [userId])).length > 0;
}

const ACCOUNT_SQL = `SELECT 1 AS ok WHERE ?1 = ?2
  OR EXISTS (SELECT 1 FROM org_members m WHERE m.org_id = ?1 AND m.user_id = ?2 AND m.role = 'owner')`;

/** Runs in the repository's RepoState. */
const ADVISORY_SQL = `SELECT 1 AS ok FROM security_advisories a WHERE a.repo_id = ?1 AND a.ghsa_id = ?3
  AND (a.author_id = ?2 OR EXISTS (SELECT 1 FROM advisory_collaborators c WHERE c.advisory_id = a.id AND c.user_id = ?2))`;

export type AccessTarget = { access: Access; repoId: string | null; accountId: string | null; subject?: string | null };

/** Re-checked for every recipient at send time, so access revoked after the event drops the notification. */
export async function recipientHasAccess(target: AccessTarget, userId: string): Promise<boolean> {
  const { access, repoId, accountId } = target;
  if (access === "advisory") {
    if (!repoId || !target.subject) return false;
    if (await hasSecurityAccess(repoId, userId)) return true;
    return (await repoDb(repoId).first(ADVISORY_SQL, repoId, userId, target.subject)) !== null;
  }
  if (access === "account") return accountId !== null && (await first(ACCOUNT_SQL, accountId, userId)) !== null;
  if (!repoId) return false;
  if (access === "read") return (await first(READ_SQL, repoId, userId)) !== null;
  if (access === "admin") return (await first(ADMIN_SQL, repoId, userId)) !== null;
  return hasSecurityAccess(repoId, userId);
}

/** Repository admins: the owning user, org owners and admin collaborators, plus org security managers if asked. */
export async function repoAdmins(repoId: string, includeSecurityManagers = false): Promise<string[]> {
  const rows = await all<{ id: string }>(
    `SELECT a.id FROM repos r JOIN accounts a ON a.id = r.owner_id WHERE r.id = ?1 AND a.kind = 'user'
     UNION SELECT m.user_id FROM repos r JOIN org_members m ON m.org_id = r.owner_id WHERE r.id = ?1 AND m.role = 'owner'
     UNION SELECT user_id FROM collaborators WHERE repo_id = ?1 AND permission = 'admin'
     LIMIT ?2`,
    repoId,
    CAPS.notificationRecipients,
  );
  const admins = rows.map((row) => row.id);
  if (!includeSecurityManagers) return admins;
  const managers = await securityManagers(repoId);
  return [...new Set([...admins, ...managers])].slice(0, CAPS.notificationRecipients);
}

/** People who manage an account's usage and billing: the user themself, or an org's owners. */
export async function accountAdmins(accountId: string): Promise<string[]> {
  const rows = await all<{ id: string }>(
    `SELECT id FROM accounts WHERE id = ?1 AND kind = 'user'
     UNION SELECT user_id FROM org_members WHERE org_id = ?1 AND role = 'owner'
     LIMIT ?2`,
    accountId,
    CAPS.notificationRecipients,
  );
  return rows.map((row) => row.id);
}

/** Users watching the repository at the "all" level (ignored, releases-only and participating are excluded). */
export async function repoWatchers(repoId: string): Promise<string[]> {
  const rows = await all<{ user_id: string }>(
    "SELECT user_id FROM watches WHERE repo_id = ? AND level = 'all' LIMIT ?",
    repoId,
    CAPS.notificationRecipients,
  );
  return rows.map((row) => row.user_id);
}

export async function ignoredWatchers(repoId: string): Promise<Set<string>> {
  const rows = await all<{ user_id: string }>(
    "SELECT user_id FROM watches WHERE repo_id = ? AND level = 'ignore' LIMIT ?",
    repoId,
    CAPS.notificationRecipients,
  );
  return new Set(rows.map((row) => row.user_id));
}