import { env } from "cloudflare:workers";
import { all, chunks, first, placeholders } from "../db";
import { repoDb } from "../scoped-db";
import { NOT_TAKEN_DOWN_SQL } from "../repos";

const PACKAGES_PER_QUERY = 80;

type IndexedAdvisory = {
  id: string;
  repo_id: string;
  ghsa_id: string;
  cve_id: string | null;
  title: string;
  severity: string;
  state: string;
  ecosystem: string | null;
  package_name: string | null;
  affected_versions: string | null;
  patched_versions: string | null;
  published_at: number | null;
  updated_at: number;
};

export function packageKey(ecosystem: string, name: string): string {
  return `${ecosystem}\0${name}`;
}

function rowStatements(advisory: IndexedAdvisory): D1PreparedStatement[] {
  const remove = env.ADVISORY_DB.prepare("DELETE FROM published_advisory_packages WHERE advisory_id = ?").bind(
    advisory.id,
  );
  if (advisory.state !== "published" || !advisory.ecosystem || !advisory.package_name) return [remove];
  return [
    remove,
    env.ADVISORY_DB.prepare(
      `INSERT INTO published_advisory_packages (advisory_id, repo_id, ghsa_id, cve_id, title, severity, ecosystem,
         package_name, affected_versions, patched_versions, published_at, updated_at)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
    ).bind(
      advisory.id,
      advisory.repo_id,
      advisory.ghsa_id,
      advisory.cve_id,
      advisory.title,
      advisory.severity,
      advisory.ecosystem,
      advisory.package_name,
      advisory.affected_versions,
      advisory.patched_versions,
      advisory.published_at,
      advisory.updated_at,
    ),
  ];
}

const ADVISORY_COLUMNS = `id, repo_id, ghsa_id, cve_id, title, severity, state, ecosystem, package_name, affected_versions,
  patched_versions, published_at, updated_at`;

/** Only public repositories feed the shared index: counts on other repos' dependency pages must not reveal private ones. */
async function repoIsPublic(repoId: string): Promise<boolean> {
  const row = await first<{ visibility: string }>("SELECT visibility FROM repos WHERE id = ?", repoId);
  return row?.visibility === "public";
}

/**
 * Re-derives one advisory's index rows from its RepoState: present only while it is published with a package in a
 * public repository.
 */
export async function indexAdvisory(repoId: string, advisoryId: string): Promise<void> {
  const advisory = (await repoIsPublic(repoId))
    ? await repoDb(repoId).first<IndexedAdvisory>(
        `SELECT ${ADVISORY_COLUMNS} FROM security_advisories WHERE id = ? AND repo_id = ?`,
        advisoryId,
        repoId,
      )
    : null;
  if (!advisory) {
    await env.ADVISORY_DB.prepare("DELETE FROM published_advisory_packages WHERE advisory_id = ?")
      .bind(advisoryId)
      .run();
    return;
  }
  await env.ADVISORY_DB.batch(rowStatements(advisory));
}

/** After a visibility change: a private repository drops out of the index, a public one has its advisories re-added. */
export async function syncRepoAdvisoryIndex(repoId: string): Promise<void> {
  if (!(await repoIsPublic(repoId))) {
    await dropRepoAdvisoryIndex(repoId);
    return;
  }
  const advisories = await repoDb(repoId).all<IndexedAdvisory>(
    `SELECT ${ADVISORY_COLUMNS} FROM security_advisories WHERE repo_id = ? AND state = 'published'`,
    repoId,
  );
  const statements = advisories.flatMap(rowStatements);
  if (statements.length > 0) await env.ADVISORY_DB.batch(statements);
}

/** Call when a repository is deleted so its advisories stop matching other repositories' dependencies. */
export async function dropRepoAdvisoryIndex(repoId: string): Promise<void> {
  await env.ADVISORY_DB.prepare("DELETE FROM published_advisory_packages WHERE repo_id = ?").bind(repoId).run();
}

/**
 * Published advisories per `packageKey`, for the given packages only. Rows are re-checked against the directory so
 * an advisory indexed before its repository went private (or was hidden) never shows up in another repo's counts.
 */
export async function publishedAdvisoryCounts(
  packages: Array<{ ecosystem: string; name: string }>,
): Promise<Map<string, number>> {
  const counts = new Map<string, number>();
  if (packages.length === 0) return counts;
  const byEcosystem = new Map<string, Set<string>>();
  for (const item of packages) {
    const names = byEcosystem.get(item.ecosystem) ?? new Set<string>();
    names.add(item.name);
    byEcosystem.set(item.ecosystem, names);
  }
  const rows: Array<{ ecosystem: string; package_name: string; repo_id: string; count: number }> = [];
  for (const [ecosystem, names] of byEcosystem) {
    for (const part of chunks([...names], PACKAGES_PER_QUERY)) {
      const { results } = await env.ADVISORY_DB.prepare(
        `SELECT package_name, repo_id, COUNT(*) AS count FROM published_advisory_packages
         WHERE ecosystem = ? AND package_name IN (${part.map(() => "?").join(", ")}) GROUP BY package_name, repo_id`,
      )
        .bind(ecosystem, ...part)
        .all<{ package_name: string; repo_id: string; count: number }>();
      for (const row of results) rows.push({ ecosystem, ...row });
    }
  }
  const publicRepos = new Set<string>();
  for (const part of chunks([...new Set(rows.map((row) => row.repo_id))], PACKAGES_PER_QUERY)) {
    const visible = await all<{ id: string }>(
      `SELECT r.id FROM repos r WHERE r.visibility = 'public' AND r.hidden_at IS NULL AND ${NOT_TAKEN_DOWN_SQL}
       AND r.id IN (${placeholders(part.length)})`,
      ...part,
    );
    for (const row of visible) publicRepos.add(row.id);
  }
  for (const row of rows) {
    if (!publicRepos.has(row.repo_id)) continue;
    const key = packageKey(row.ecosystem, row.package_name);
    counts.set(key, (counts.get(key) ?? 0) + row.count);
  }
  return counts;
}