import { all, run } from "./db";
import { repoReadableSql } from "./repos";
import { invalidateUnread } from "./user-inbox";

/**
 * 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 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})`,
    ...["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}`;

async function purge(repoFilter: string, userFilter: string, ...params: string[]): Promise<void> {
  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));
}

/** 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);
}

/** 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);
}

/** 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);
}