import { all, run } from "./db";
import { repoReadableSql } from "./repos";
import { invalidateUnread } from "./user-inbox";
import { env } from "cloudflare:workers";
import { addCounter } from "./counters";
import { REPO_STARS } from "./counter-keys";

/**
 * Stars, watches, thread subscriptions, assignments, review requests and notifications feed the dashboard and
 * inbox; rows held by users who can no
 * longer read the repository must go when access is revoked (collaborator removal, org membership removal,
 * public -> private), otherwise private activity keeps streaming to them.
 */
const CANNOT_READ = `NOT EXISTS (SELECT 1 FROM repos r WHERE r.id = t.repo_id AND ${repoReadableSql("r", "t.user_id")})
  AND NOT EXISTS (SELECT 1 FROM users u_ WHERE u_.account_id = t.user_id AND u_.is_admin = 1)`;

function statements(repoFilter: string, userFilter: string): string[] {
  return [
    `DELETE FROM watches WHERE (user_id, repo_id) IN (SELECT t.user_id, t.repo_id FROM watches t WHERE ${repoFilter} ${userFilter} AND ${CANNOT_READ})`,
    `DELETE FROM thread_subscriptions WHERE (user_id, issue_id) IN (
       SELECT t.user_id, t.issue_id FROM (SELECT s.user_id, s.issue_id, i.repo_id FROM thread_subscriptions s JOIN issues i ON i.id = s.issue_id) t
       WHERE ${repoFilter} ${userFilter} AND ${CANNOT_READ})`,
    ...["issue_assignees", "review_requests"].map(
      (table) => `DELETE FROM ${table} WHERE (user_id, issue_id) IN (
       SELECT t.user_id, t.issue_id FROM (SELECT x.user_id, x.issue_id, i.repo_id FROM ${table} x JOIN issues i ON i.id = x.issue_id) t
       WHERE ${repoFilter} ${userFilter} AND ${CANNOT_READ})`,
    ),
  ];
}

const NOTIFICATION_TARGETS = (repoFilter: string, userFilter: string) =>
  `FROM notifications t WHERE t.repo_id IS NOT NULL AND ${repoFilter} ${userFilter} AND ${CANNOT_READ}`;

/** Removed stars must move the repo-counts counter, or the next flush would write the stale total back. */
async function purgeStars(repoFilter: string, userFilter: string, params: string[]): Promise<void> {
  const removed = await all<{ repo_id: string }>(
    `DELETE FROM stars WHERE (user_id, repo_id) IN (SELECT t.user_id, t.repo_id FROM stars t WHERE ${repoFilter} ${userFilter} AND ${CANNOT_READ})
     RETURNING repo_id`,
    ...params,
  );
  const perRepo = new Map<string, number>();
  for (const row of removed) perRepo.set(row.repo_id, (perRepo.get(row.repo_id) ?? 0) + 1);
  await Promise.all(
    [...perRepo].map(([repoId, count]) => addCounter("repo-counts", repoId, REPO_STARS, [-count, 0, 0])),
  );
}

async function purge(repoFilter: string, userFilter: string, ...params: string[]): Promise<void> {
  await purgeStars(repoFilter, userFilter, params);
  for (const sql of statements(repoFilter, userFilter)) await run(sql, ...params);
  const removed = await all<{ user_id: string }>(
    `DELETE FROM notifications WHERE id IN (SELECT t.id ${NOTIFICATION_TARGETS(repoFilter, userFilter)}) RETURNING user_id`,
    ...params,
  );
  if (removed.length > 0) await invalidateUnread(removed.map((row) => row.user_id));
}

/** MCP sessions cache granted access for ACCESS_CACHE_MS; a revocation must not wait that out. */
export async function dropMcpAccess(repoId: string): Promise<void> {
  try {
    await env.MCP_HUB.get(env.MCP_HUB.idFromName(`repo:${repoId}`)).invalidateSubscribers(repoId);
  } catch (error) {
    console.error("MCP access invalidation failed", repoId, error);
  }
}

/** After a repository turns private: drop every follower who cannot read it any more. */
export async function purgeInaccessibleFollowers(repoId: string): Promise<void> {
  await purge("t.repo_id = ?1", "", repoId);
  await dropMcpAccess(repoId);
}

/** After a collaborator is removed: drop that user's rows on the repository if they lost read access. */
export async function purgeFollowerIfRevoked(repoId: string, userId: string): Promise<void> {
  await purge("t.repo_id = ?1", "AND t.user_id = ?2", repoId, userId);
  await dropMcpAccess(repoId);
}

/** After an org membership ends (or the org's default permission drops): across every repository the org owns. */
export async function purgeOrgFollowers(orgId: string, userId: string | null): Promise<void> {
  const repoFilter = "t.repo_id IN (SELECT id FROM repos WHERE owner_id = ?1)";
  if (userId === null) await purge(repoFilter, "", orgId);
  else await purge(repoFilter, "AND t.user_id = ?2", orgId, userId);
  const repos = await all<{ id: string }>("SELECT id FROM repos WHERE owner_id = ?", orgId);
  await Promise.all(repos.map((repo) => dropMcpAccess(repo.id)));
}