gitfor/platform

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