#!/usr/bin/env -S npx tsx

import { createHash } from "node:crypto";
import {
  chmod,
  mkdir,
  readFile,
  rename,
  writeFile,
} from "node:fs/promises";
import path from "node:path";
import { fileURLToPath } from "node:url";

type DatabaseRow = Record<string, unknown>;

interface CliOptions {
  outputRoot: string;
  pageSize: number;
  help: boolean;
}

interface ExportFile {
  path: string;
  dataset: string;
  page: number;
  rowCount: number;
  bytes: number;
  sha256: string;
}

interface DatasetManifest {
  table: string;
  cursorColumn: string;
  columns: string[];
  sensitive: boolean;
  sourceCountBefore: number;
  sourceCountAfter: number;
  rowCount: number;
  pages: number;
}

interface DatasetSpec {
  name: string;
  schema: string;
  table: string;
  cursorColumn: string;
  outputDirectory: string;
  sensitive: boolean;
}

const scriptDirectory = path.dirname(fileURLToPath(import.meta.url));
const backendDirectory = path.resolve(scriptDirectory, "..");
const defaultOutputRoot = path.join(backendDirectory, "data", "migration");
const envFile = path.join(backendDirectory, ".env");
const managementApiBaseUrl = "https://api.supabase.com/v1";
const toolVersion = 1;

const datasets: DatasetSpec[] = [
  {
    name: "kv",
    schema: "public",
    table: "kv_store_7249dcd9",
    cursorColumn: "key",
    outputDirectory: "kv",
    sensitive: true,
  },
  {
    name: "authUsers",
    schema: "auth",
    table: "users",
    cursorColumn: "id",
    outputDirectory: "auth/users",
    sensitive: true,
  },
  {
    name: "authIdentities",
    schema: "auth",
    table: "identities",
    cursorColumn: "id",
    outputDirectory: "auth/identities",
    sensitive: true,
  },
  {
    name: "storageBuckets",
    schema: "storage",
    table: "buckets",
    cursorColumn: "id",
    outputDirectory: "storage/buckets",
    sensitive: false,
  },
  {
    name: "storageObjects",
    schema: "storage",
    table: "objects",
    cursorColumn: "id",
    outputDirectory: "storage/objects",
    sensitive: false,
  },
];

function printHelp(): void {
  console.log(`Usage: npx tsx backend/scripts/export-source.ts [options]

Exports the Supabase source through read-only Management API queries.

Options:
  --output-root <path>  Parent directory for timestamped exports
  --page-size <number>  Rows per JSON part (100-5000, default: 500)
  --help                Show this help

Required environment variables (backend/.env is loaded automatically):
  SUPABASE_ACCESS_TOKEN
  SUPABASE_SOURCE_PROJECT_REF (or SUPABASE_PROJECT_REF)`);
}

function parseCli(arguments_: string[]): CliOptions {
  const options: CliOptions = {
    outputRoot: defaultOutputRoot,
    pageSize: 500,
    help: false,
  };

  for (let index = 0; index < arguments_.length; index += 1) {
    const argument = arguments_[index];
    if (argument === "--help" || argument === "-h") {
      options.help = true;
      continue;
    }

    if (argument === "--output-root") {
      const value = arguments_[index + 1];
      if (!value) throw new Error("--output-root requires a path");
      options.outputRoot = path.resolve(value);
      index += 1;
      continue;
    }

    if (argument === "--page-size") {
      const value = Number(arguments_[index + 1]);
      if (!Number.isInteger(value) || value < 100 || value > 5000) {
        throw new Error("--page-size must be an integer between 100 and 5000");
      }
      options.pageSize = value;
      index += 1;
      continue;
    }

    throw new Error(`Unknown argument: ${argument}`);
  }

  return options;
}

function parseDotEnv(contents: string): Record<string, string> {
  const parsed: Record<string, string> = {};

  for (const rawLine of contents.replace(/^\uFEFF/, "").split(/\r?\n/)) {
    const line = rawLine.trim();
    if (!line || line.startsWith("#")) continue;

    const match = /^(?:export\s+)?([A-Za-z_][A-Za-z0-9_]*)\s*=\s*(.*)$/.exec(line);
    if (!match) continue;

    const key = match[1];
    const rawValue = match[2];
    if (key === undefined || rawValue === undefined) continue;
    let value = rawValue.trim();
    if (
      value.length >= 2 &&
      ((value.startsWith('"') && value.endsWith('"')) ||
        (value.startsWith("'") && value.endsWith("'")))
    ) {
      const quote = value[0];
      value = value.slice(1, -1);
      if (quote === '"') {
        value = value.replace(/\\(n|r|t|\\|")/g, (_match, escaped: string) => {
          const replacements: Record<string, string> = {
            n: "\n",
            r: "\r",
            t: "\t",
            "\\": "\\",
            '"': '"',
          };
          return replacements[escaped] ?? escaped;
        });
      }
    }
    parsed[key] = value;
  }

  return parsed;
}

async function loadLocalEnvironment(): Promise<void> {
  try {
    const parsed = parseDotEnv(await readFile(envFile, "utf8"));
    for (const [key, value] of Object.entries(parsed)) {
      if (process.env[key] === undefined) process.env[key] = value;
    }
  } catch (error) {
    const code = (error as NodeJS.ErrnoException).code;
    if (code !== "ENOENT") throw error;
  }
}

function requireEnvironment(name: string, aliases: string[] = []): string {
  for (const candidate of [name, ...aliases]) {
    const value = process.env[candidate]?.trim();
    if (value) return value;
  }
  throw new Error(`Missing required environment variable: ${name}`);
}

function assertSelectOnly(query: string): void {
  const normalized = query.trim();
  if (!/^(SELECT|WITH)\b/i.test(normalized)) {
    throw new Error("Refusing to run a non-SELECT source query");
  }

  const forbidden = /\b(ALTER|CALL|COPY|CREATE|DELETE|DO|DROP|GRANT|INSERT|REINDEX|REVOKE|TRUNCATE|UPDATE|VACUUM)\b/i;
  if (forbidden.test(normalized)) {
    throw new Error("Refusing to run a source query containing a write operation");
  }

  if (normalized.includes(";")) {
    throw new Error("Refusing to run multiple SQL statements");
  }
}

function responseRows(payload: unknown): DatabaseRow[] {
  if (Array.isArray(payload)) return payload as DatabaseRow[];
  if (!payload || typeof payload !== "object") {
    throw new Error("Management API returned an unexpected query response");
  }

  const record = payload as Record<string, unknown>;
  for (const key of ["data", "result", "rows"]) {
    if (Array.isArray(record[key])) return record[key] as DatabaseRow[];
  }

  throw new Error("Management API query response did not contain rows");
}

function delay(milliseconds: number): Promise<void> {
  return new Promise((resolve) => setTimeout(resolve, milliseconds));
}

function safeApiError(payload: unknown): string {
  if (!payload || typeof payload !== "object") return "No structured error details";
  const record = payload as Record<string, unknown>;
  const candidate = record.message ?? record.error ?? record.msg;
  if (typeof candidate !== "string") return "No structured error details";

  return candidate
    .replace(/Bearer\s+\S+/gi, "Bearer [REDACTED]")
    .replace(/(token|password|secret)\s*[:=]\s*\S+/gi, "$1=[REDACTED]")
    .slice(0, 500);
}

function createQueryClient(accessToken: string, projectRef: string) {
  const endpoint = `${managementApiBaseUrl}/projects/${encodeURIComponent(projectRef)}/database/query`;

  return async (query: string, parameters: unknown[] = []): Promise<DatabaseRow[]> => {
    assertSelectOnly(query);

    for (let attempt = 1; attempt <= 5; attempt += 1) {
      try {
        const response = await fetch(endpoint, {
          method: "POST",
          headers: {
            Authorization: `Bearer ${accessToken}`,
            "Content-Type": "application/json",
          },
          body: JSON.stringify({ query, parameters, read_only: true }),
          signal: AbortSignal.timeout(120_000),
        });

        const text = await response.text();
        let payload: unknown;
        try {
          payload = text ? JSON.parse(text) : null;
        } catch {
          payload = null;
        }

        if (response.ok) return responseRows(payload);

        const retryable = response.status === 408 || response.status === 425 ||
          response.status === 429 || response.status >= 500;
        if (!retryable || attempt === 5) {
          const requestId = response.headers.get("x-request-id");
          const suffix = requestId ? ` (request ${requestId})` : "";
          throw new Error(
            `Supabase Management API returned HTTP ${response.status}${suffix}: ${safeApiError(payload)}`,
          );
        }
      } catch (error) {
        if (attempt === 5) throw error;
        if (error instanceof Error && error.message.startsWith("Supabase Management API returned HTTP 4")) {
          throw error;
        }
      }

      const backoff = 500 * (2 ** (attempt - 1)) + Math.floor(Math.random() * 250);
      await delay(backoff);
    }

    throw new Error("Supabase Management API retry loop ended unexpectedly");
  };
}

function quoteIdentifier(identifier: string): string {
  if (!identifier || identifier.includes("\0")) {
    throw new Error("Invalid PostgreSQL identifier returned by the source");
  }
  return `"${identifier.replaceAll('"', '""')}"`;
}

function qualifiedTable(spec: DatasetSpec): string {
  return `${quoteIdentifier(spec.schema)}.${quoteIdentifier(spec.table)}`;
}

function parseCount(rows: DatabaseRow[], label: string): number {
  const rawCount = rows[0]?.count;
  const count = typeof rawCount === "number" ? rawCount : Number(rawCount);
  if (!Number.isSafeInteger(count) || count < 0) {
    throw new Error(`Invalid source count for ${label}`);
  }
  return count;
}

async function sourceCount(
  query: ReturnType<typeof createQueryClient>,
  spec: DatasetSpec,
): Promise<number> {
  const rows = await query(`SELECT count(*)::text AS count FROM ${qualifiedTable(spec)}`);
  return parseCount(rows, spec.name);
}

async function sourceColumns(
  query: ReturnType<typeof createQueryClient>,
  spec: DatasetSpec,
): Promise<string[]> {
  const rows = await query(
    `SELECT column_name FROM information_schema.columns
     WHERE table_schema = $1 AND table_name = $2
     ORDER BY ordinal_position`,
    [spec.schema, spec.table],
  );
  const columns = rows.map((row) => row.column_name).filter(
    (column): column is string => typeof column === "string" && column.length > 0,
  );

  if (columns.length === 0) throw new Error(`Source table not found: ${spec.schema}.${spec.table}`);
  if (!columns.includes(spec.cursorColumn)) {
    throw new Error(`Source table ${spec.schema}.${spec.table} has no ${spec.cursorColumn} column`);
  }
  return columns;
}

function sha256(contents: Uint8Array): string {
  return createHash("sha256").update(contents).digest("hex");
}

async function writePrivateFile(filePath: string, contents: Uint8Array): Promise<void> {
  await mkdir(path.dirname(filePath), { recursive: true, mode: 0o700 });
  const temporaryPath = `${filePath}.tmp-${process.pid}`;
  await writeFile(temporaryPath, contents, { flag: "wx", mode: 0o600 });
  await rename(temporaryPath, filePath);
  await chmod(filePath, 0o600).catch(() => undefined);
}

async function writePage(
  exportDirectory: string,
  spec: DatasetSpec,
  page: number,
  rows: DatabaseRow[],
): Promise<ExportFile> {
  const fileName = `part-${String(page).padStart(6, "0")}.json`;
  const relativePath = path.posix.join(spec.outputDirectory, fileName);
  const diskPath = path.join(exportDirectory, ...relativePath.split("/"));
  const payload = {
    formatVersion: toolVersion,
    dataset: spec.name,
    page,
    rowCount: rows.length,
    rows,
  };
  const contents = Buffer.from(`${JSON.stringify(payload)}\n`, "utf8");
  await writePrivateFile(diskPath, contents);

  return {
    path: relativePath,
    dataset: spec.name,
    page,
    rowCount: rows.length,
    bytes: contents.byteLength,
    sha256: sha256(contents),
  };
}

async function exportDataset(
  query: ReturnType<typeof createQueryClient>,
  exportDirectory: string,
  spec: DatasetSpec,
  pageSize: number,
  files: ExportFile[],
): Promise<DatasetManifest> {
  const columns = await sourceColumns(query, spec);
  const countBefore = await sourceCount(query, spec);
  const selectedColumns = columns.map(quoteIdentifier).join(", ");
  const cursorColumn = quoteIdentifier(spec.cursorColumn);
  const table = qualifiedTable(spec);

  let cursor: unknown;
  let exportedCount = 0;
  let page = 0;

  while (true) {
    const hasCursor = cursor !== undefined;
    const where = hasCursor ? `WHERE ${cursorColumn} > $1` : "";
    const parameters = hasCursor ? [cursor] : [];
    const rows = await query(
      `SELECT ${selectedColumns} FROM ${table} ${where} ORDER BY ${cursorColumn} ASC LIMIT ${pageSize}`,
      parameters,
    );

    if (rows.length === 0) {
      if (page === 0) {
        page = 1;
        files.push(await writePage(exportDirectory, spec, page, rows));
      }
      break;
    }

    if (rows.length > pageSize) throw new Error(`Source exceeded page size for ${spec.name}`);
    const lastCursor = rows.at(-1)?.[spec.cursorColumn];
    if (lastCursor === undefined || lastCursor === null) {
      throw new Error(`Null pagination cursor in ${spec.name}`);
    }
    if (hasCursor && Object.is(lastCursor, cursor)) {
      throw new Error(`Pagination cursor did not advance for ${spec.name}`);
    }

    page += 1;
    files.push(await writePage(exportDirectory, spec, page, rows));
    exportedCount += rows.length;
    cursor = lastCursor;

    console.log(`${spec.name}: ${exportedCount}/${countBefore} rows exported`);
    if (rows.length < pageSize) break;
  }

  const countAfter = await sourceCount(query, spec);
  if (countBefore !== countAfter || exportedCount !== countAfter) {
    throw new Error(
      `${spec.name} changed during export (before=${countBefore}, exported=${exportedCount}, after=${countAfter}); run again`,
    );
  }

  return {
    table: `${spec.schema}.${spec.table}`,
    cursorColumn: spec.cursorColumn,
    columns,
    sensitive: spec.sensitive,
    sourceCountBefore: countBefore,
    sourceCountAfter: countAfter,
    rowCount: exportedCount,
    pages: page,
  };
}

function timestampDirectoryName(date: Date): string {
  return date.toISOString().replace(/[:.]/g, "-");
}

async function main(): Promise<void> {
  const options = parseCli(process.argv.slice(2));
  if (options.help) {
    printHelp();
    return;
  }

  await loadLocalEnvironment();
  const accessToken = requireEnvironment("SUPABASE_ACCESS_TOKEN");
  const projectRef = requireEnvironment("SUPABASE_SOURCE_PROJECT_REF", ["SUPABASE_PROJECT_REF"]);
  if (!/^[a-z0-9]+$/i.test(projectRef)) throw new Error("Invalid Supabase project reference");

  const startedAt = new Date();
  const exportDirectory = path.join(options.outputRoot, timestampDirectoryName(startedAt));
  await mkdir(options.outputRoot, { recursive: true, mode: 0o700 });
  await mkdir(exportDirectory, { recursive: false, mode: 0o700 });
  await chmod(exportDirectory, 0o700).catch(() => undefined);

  const query = createQueryClient(accessToken, projectRef);
  const files: ExportFile[] = [];
  const datasetManifest: Record<string, DatasetManifest> = {};

  console.log("Starting read-only Supabase export. No source writes or binary downloads will be made.");
  for (const spec of datasets) {
    datasetManifest[spec.name] = await exportDataset(
      query,
      exportDirectory,
      spec,
      options.pageSize,
      files,
    );
  }

  const completedAt = new Date();
  const manifest = {
    format: "tonline-supabase-source-export",
    formatVersion: toolVersion,
    createdAt: startedAt.toISOString(),
    completedAt: completedAt.toISOString(),
    source: {
      provider: "supabase",
      projectRef,
      queryMode: "management-api-read-only",
    },
    export: {
      pageSize: options.pageSize,
      binaryObjectsDownloaded: false,
      transactionalSnapshot: false,
      containsSensitiveAuthData: true,
      rowCount: Object.values(datasetManifest).reduce((sum, item) => sum + item.rowCount, 0),
      fileCount: files.length,
    },
    datasets: datasetManifest,
    files,
  };
  const manifestContents = Buffer.from(`${JSON.stringify(manifest, null, 2)}\n`, "utf8");
  const manifestHash = sha256(manifestContents);
  await writePrivateFile(path.join(exportDirectory, "manifest.json"), manifestContents);
  await writePrivateFile(
    path.join(exportDirectory, "manifest.sha256"),
    Buffer.from(`${manifestHash}  manifest.json\n`, "ascii"),
  );

  console.log(`Export complete: ${exportDirectory}`);
  console.log(`Rows: ${manifest.export.rowCount}; data files: ${manifest.export.fileCount}`);
  console.log(`Manifest SHA-256: ${manifestHash}`);
  console.log("Storage objects were inventoried only; binary contents were not downloaded.");
}

main().catch((error: unknown) => {
  const message = error instanceof Error ? error.message : "Unknown export error";
  console.error(`Export failed: ${message}`);
  process.exitCode = 1;
});