#!/usr/bin/env bun
/**
 * Generates src/server/scoped-db/schema.gen.ts: the DDL each scoped Durable Object (RepoState, AccountState)
 * creates in its own SQLite, derived from the D1 migrations (which created and later dropped these tables in
 * D1). Foreign keys to tables that live elsewhere are dropped (the referenced row is in another object or the D1
 * directory); keys within a scope are kept.
 *
 *   bun scripts/gen-scoped-schema.ts
 */
import { Database } from "bun:sqlite";
import { readdirSync, readFileSync, writeFileSync } from "node:fs";
import { join } from "node:path";

const ROOT = join(import.meta.dir, "..");
/**
 * Access and social edges stay in the D1 directory on purpose: `collaborators`, `org_members`, `stars`,
 * `watches` and `follows` are what "which repositories can this user see" and the social lists are computed
 * from, and they are lookups rather than repository or account data.
 */

/** Tables that live in a repository's RepoState (and in a repo-less site's own 'site:<id>' object). */
export const REPO_TABLES: string[] = [
  "issues",
  "issues_fts",
  "comments",
  "issue_events",
  "labels",
  "issue_labels",
  "milestones",
  "issue_types",
  "issue_assignees",
  "pinned_issues",
  "reactions",
  "pull_requests",
  "reviews",
  "review_comments",
  "review_requests",
  "pull_issue_links",
  "pull_auto_merges",
  "issue_redirects",
  "thread_subscriptions",
  "branch_protections",
  "webhooks",
  "webhook_deliveries",
  "secrets",
  "repo_variables",
  "environments",
  "environment_secrets",
  "environment_variables",
  "deploy_tokens",
  "deployments",
  "repo_security_settings",
  "repo_mergefix_settings",
  "runner_policies",
  "workflow_runs",
  "workflow_jobs",
  "job_steps",
  "job_artifacts",
  "commit_statuses",
  "workflow_triggers",
  "workflow_schedules",
  "action_caches",
  "job_tokens",
  "job_token_pushes",
  "ci_notify_state",
  "merge_queue_entries",
  "mergefix_runs",
  "hidden_ref_cleanups",
  "releases",
  "release_assets",
  "lfs_objects",
  "lfs_locks",
  "code_scanning_alerts",
  "sarif_uploads",
  "secret_alerts",
  "secret_push_blocks",
  "security_advisories",
  "advisory_collaborators",
  "advisory_comments",
  "dependencies",
  "dependency_manifests",
  "security_counters",
  "discussions",
  "discussion_categories",
  "discussion_comments",
  "discussion_votes",
  "push_events",
  "activity",
  "attachments",
  "repo_storage_estimates",
  // A repo-less site (upload scratch, claimed or not) is its own scope, keyed 'site:<id>'.
  "sites",
  "site_deployments",
  "site_env",
  "site_usage",
  "site_preview_comments",
  "site_pr_aliases",
];

/** Tables that live in an account's AccountState. */
export const ACCOUNT_TABLES: string[] = [
  "sessions",
  "passkeys",
  "recovery_codes",
  "access_tokens",
  "access_token_repos",
  "auth_tokens",
  "oauth_codes",
  "oauth_refresh_tokens",
  "mcp_token_agents",
  "agents",
  "signing_keys",
  "notification_settings",
  "notification_category_prefs",
  "notification_email_counters",
  "notification_unsubscribe_tokens",
  "notification_digest_items",
  "notification_deliveries",
  "notifications",
  "org_invitations",
  "owner_secrets",
  "owner_variables",
  "security_managers",
  "projects",
  "project_fields",
  "project_items",
  "project_item_values",
  "account_billing",
  "billing_customers",
  "billing_addons",
  "credit_balances",
  "credit_ledger",
  "credit_draws",
  "usage_events",
  "usage_repo_periods",
  "usage_days",
  "usage_gauges",
  "usage_periods",
  "usage_reservations",
  "usage_claims",
  "usage_alerts",
  "quota_notices",
  "mcp_usage_daily",
  "mcp_audit",
  "lfs_usage",
  "lfs_account_objects",
  "integration_usage_daily",
  // Repository runners live with the repository owner (owner_id NULL, repo_id set).
  "runners",
  // Repository runners live with the repository owner (owner_id NULL, repo_id set).
  "runner_registration_tokens",
  "runner_usage_events",
  "editor_container_sessions",
  // Both scopes: a repository's endpoints live in an AccountState named by the repo id (owner_id = repo id).
  "ai_endpoints",
];

/** The D1 migration that dropped the moved tables; the scoped DDL is their state just before it. */
const DROP_MIGRATION = "0057_drop_moved_tables.sql";

function loadSchema(): Database {
  const db = new Database(":memory:");
  const dir = join(ROOT, "migrations");
  for (const file of readdirSync(dir)
    .filter((name) => name.endsWith(".sql") && name < DROP_MIGRATION)
    .sort()) {
    db.exec(readFileSync(join(dir, file), "utf8"));
  }
  return db;
}

/** Drops `REFERENCES other(col) [ON DELETE ...]` clauses that point outside the scope. */
function stripForeignKeys(sql: string, local: Set<string>): string {
  return sql.replace(
    /\s+REFERENCES\s+(\w+)\s*\([^)]*\)(\s+ON\s+(DELETE|UPDATE)\s+(CASCADE|SET NULL|SET DEFAULT|RESTRICT|NO ACTION))*/gi,
    (clause, table: string) => (local.has(table) ? clause : ""),
  );
}

/** Foreign keys that point at a row which may live in another object (a transferred issue lands in another repo). */
const CROSS_SCOPE_KEYS: Record<string, string[]> = { issue_redirects: ["issue_id"] };

function dropCrossScopeKeys(table: string, sql: string): string {
  let out = sql;
  for (const column of CROSS_SCOPE_KEYS[table] ?? []) {
    const reference = new RegExp(
      `(\\b${column}\\s+TEXT[^,]*?)\\s+REFERENCES\\s+\\w+\\s*\\([^)]*\\)(\\s+ON\\s+(DELETE|UPDATE)\\s+(CASCADE|SET NULL|SET DEFAULT|RESTRICT|NO ACTION))*`,
      "i",
    );
    out = out.replace(reference, "$1");
  }
  return out;
}

function scopeDdl(db: Database, tables: string[]): string[] {
  const local = new Set(tables);
  const rows = db
    .query("SELECT type, name, tbl_name, sql FROM sqlite_master WHERE sql IS NOT NULL ORDER BY rowid")
    .all() as Array<{ type: string; name: string; tbl_name: string; sql: string }>;
  const out: string[] = [];
  for (const row of rows) {
    if (!local.has(row.tbl_name) && !local.has(row.name)) continue;
    if (row.name.startsWith("sqlite_")) continue;
    if (row.type === "table" && /_fts_(data|idx|content|docsize|config)$/.test(row.name)) continue;
    // Triggers that touch tables outside the scope can't run in the object.
    if (row.type === "trigger") {
      const touched = [...row.sql.matchAll(/\b(?:FROM|INTO|UPDATE|JOIN)\s+(?:OF\s+\w+\s+ON\s+)?(?!SET\b)(\w+)/gi)].map(
        (match) => match[1] ?? "",
      );
      const ctes = new Set([...row.sql.matchAll(/(\w+)\s*\([^)]*\)\s+AS\s*\(/gi)].map((match) => match[1] ?? ""));
      if (touched.some((table) => !local.has(table) && !ctes.has(table))) continue;
    }
    out.push(
      dropCrossScopeKeys(row.name, stripForeignKeys(row.sql, local)).replace(
        /^CREATE (TABLE|INDEX|UNIQUE INDEX|TRIGGER|VIRTUAL TABLE) /,
        (m) => m.replace(/ $/, " IF NOT EXISTS "),
      ),
    );
  }
  return out;
}

const db = loadSchema();
for (const [scope, tables] of Object.entries({ repo: REPO_TABLES, account: ACCOUNT_TABLES })) {
  for (const table of tables) {
    if (!db.query("SELECT 1 FROM sqlite_master WHERE name = ?").get(table))
      throw new Error(`${scope}: no table ${table}`);
  }
}
const body = `// Generated by scripts/gen-scoped-schema.ts from the D1 migrations up to ${DROP_MIGRATION}. Do not edit; rerun
// the script. New scoped-table DDL goes in schema-extra.ts.
/* oxlint-disable max-lines */

export const REPO_SCHEMA: readonly string[] = ${JSON.stringify(scopeDdl(db, REPO_TABLES), null, 2)};

export const ACCOUNT_SCHEMA: readonly string[] = ${JSON.stringify(scopeDdl(db, ACCOUNT_TABLES), null, 2)};




`;
writeFileSync(join(ROOT, "src/server/scoped-db/schema.gen.ts"), body);
console.info("wrote src/server/scoped-db/schema.gen.ts");