import { run } from "./db";
import { repoReadableSql } from "./repos";

/**
 * Stars, watches and thread subscriptions feed the dashboard and notifications; 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 stars WHERE (user_id, repo_id) IN (SELECT t.user_id, t.repo_id FROM stars t WHERE ${repoFilter} ${userFilter} AND ${CANNOT_READ})`,
    `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})`,
  ];
}

/** After a repository turns private: drop every follower who cannot read it any more. */
export async function purgeInaccessibleFollowers(repoId: string): Promise<void> {
  for (const sql of statements("t.repo_id = ?1", "")) await run(sql, 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> {
  for (const sql of statements("t.repo_id = ?1", "AND t.user_id = ?2")) await run(sql, repoId, userId);
}

/** 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) {
    for (const sql of statements(repoFilter, "")) await run(sql, orgId);
    return;
  }
  for (const sql of statements(repoFilter, "AND t.user_id = ?2")) await run(sql, orgId, userId);
}