gitfor/platform

import { backfillDone, backfillOnce } from "../backfill";
import { all, batch, first, run, stmt } from "../db";
import { repoDb } from "../scoped-db";
import type { ScopedValue } from "../scoped-db/types";
import { locatedScopes } from "../scoped-db/locators";

const INDEX = "site-directory";

/** What the directory keeps of each site, read from the scope's own `sites` table. */
export const SITE_DIRECTORY_SELECT = `SELECT id, owner_id, scratch, expires_at, paused_at,
  CASE WHEN disabled_reason IS NULL AND active_deployment_id IS NOT NULL THEN 1 ELSE 0 END AS active,
  CASE WHEN disabled_reason IS NULL THEN 0 ELSE 1 END AS disabled FROM sites`;

type DirectoryRow = {
  id: string;
  owner_id: string | null;
  scratch: number;
  expires_at: number | null;
  paused_at: number | null;
  active: number;
  disabled: number;
};

type Sql = { exec<T extends Record<string, ScopedValue>>(sql: string, ...params: ScopedValue[]): { toArray(): T[] } };

async function write(scopeId: string, rows: DirectoryRow[], now: number): Promise<void> {
  await batch([
    stmt("DELETE FROM site_directory WHERE scope_id = ?", scopeId),
    ...rows.map((row) =>
      stmt(
        `INSERT INTO site_directory (site_id, scope_id, owner_id, active, scratch, expires_at, paused_at, disabled, updated_at)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
         ON CONFLICT (site_id) DO UPDATE SET scope_id = excluded.scope_id, owner_id = excluded.owner_id,
           active = excluded.active, scratch = excluded.scratch, expires_at = excluded.expires_at,
           paused_at = excluded.paused_at, disabled = excluded.disabled, updated_at = excluded.updated_at`,
        row.id,
        scopeId,
        row.owner_id,
        row.active,
        row.scratch,