gitfor/platform

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