/**
 * `everystack db:template:refresh` + `everystack db:branch` — per-branch
 * dev databases from a models-built template (brick 5-remainder,
 * decision 14).
 *
 *   db:template:refresh [--database-url <url>] [--models <barrel>]
 *                       [--sql-dir db/sql] [--seed "<cmd>"] [--no-seed]
 *   db:branch                       # mint (or find) the current branch's DB
 *   db:branch --list                # every branch DB, mapped to its branch
 *   db:branch --drop --confirm      # drop the current branch's DB
 *   db:branch --prune --confirm     # drop DBs whose git branch is gone
 *
 * The template is built FROM THE DECLARED STATE (both layers, fingerprint
 * MATCH bar) plus seed-as-code — the app's own `db:seed` script, run as a
 * subprocess with DATABASE_URL pointed at the fresh template. Never a data
 * copy: deterministic, PII-free (real data enters branch testing through
 * stage forks, brick 10). Branch databases mint via `CREATE DATABASE …
 * TEMPLATE`, keyed on the git branch, and `db:sync` evolves them with the
 * checkout — disposable by construction, never long-lived, never poisoned.
 */

import fs from 'node:fs/promises';
import { spawnSync } from 'node:child_process';
import type { ModelDescriptor } from '@everystack/model';
import {
  executeTemplateRefresh,
  ensureBranchDatabase,
  listBranchDatabases,
  readTemplateProvenance,
  prunableBranchDatabases,
  dropBranchDatabase,
  branchDatabaseName,
  baseNameFromUrl,
  templateName,
  currentBranch,
  localBranches,
} from '../branch-db.js';
import { currentGitRef } from '../state-apply.js';
import { fingerprintModels } from '../schema-fingerprint.js';
import { resolveDbSource, type DbSource } from '../db-source.js';
import { resolveModelsPath } from '../models-path.js';
import { readSqlDirIfPresent } from './db-sync.js';
import { loadModels } from './db-generate.js';
import { step, success, fail, info, warn } from '../output.js';

function requireUrl(flags: Record<string, string>, verb: string): string {
  let dbSource: DbSource;
  try {
    dbSource = resolveDbSource(flags);
  } catch (err: any) {
    fail(err.message);
    process.exit(1);
  }
  if (dbSource.kind !== 'url') {
    fail(`${verb} manages local databases — it needs a direct connection (--database-url or DATABASE_URL).`);
    process.exit(1);
  }
  return dbSource.url;
}

/** The seed convention: the app's own `db:seed` script from package.json. */
async function detectSeedCommand(): Promise<string | null> {
  try {
    const pkg = JSON.parse(await fs.readFile('package.json', 'utf8'));
    return pkg?.scripts?.['db:seed'] ? 'npm run db:seed' : null;
  } catch {
    return null;
  }
}

function runSeedSubprocess(command: string, templateUrl: string): void {
  const result = spawnSync(command, {
    shell: true,
    stdio: 'inherit',
    env: { ...process.env, DATABASE_URL: templateUrl },
  });
  if (result.status !== 0) {
    throw new Error(`seed command failed (exit ${result.status}): ${command}`);
  }
}

export async function dbTemplateRefreshCommand(flags: Record<string, string>): Promise<void> {
  const url = requireUrl(flags, 'db:template:refresh');

  const modelsPath = resolveModelsPath(flags.models);
  let models: ModelDescriptor[];
  try {
    step(`Loading models from ${modelsPath}...`);
    models = await loadModels(modelsPath);
    info(`${models.length} model(s).`);
  } catch (err: any) {
    fail(err.message);
    process.exit(1);
  }
  const sources = await readSqlDirIfPresent(flags['sql-dir'] || 'db/sql');

  let seedCommand: string | null = null;
  if (flags['no-seed'] === 'true') {
    info('Seeding skipped (--no-seed).');
  } else if (flags.seed && flags.seed !== 'true') {
    seedCommand = flags.seed;
  } else {
    seedCommand = await detectSeedCommand();
    if (seedCommand) info(`Seed: package.json db:seed script (override with --seed "<cmd>" / --no-seed).`);
    else warn('No db:seed script in package.json — the template builds UNSEEDED (declare one, or pass --seed "<cmd>").');
  }

  const template = templateName(baseNameFromUrl(url));
  step(`Rebuilding ${template} from the declared state (drop → create → sync both layers → seed)...`);
  try {
    const result = await executeTemplateRefresh(url, models, {
      sources,
      actor: process.env.USER ?? null,
      gitRef: currentGitRef(),
      seed: seedCommand === null ? null : (templateUrl) => {
        step(`Seeding: ${seedCommand} (DATABASE_URL → ${redactUrl(templateUrl)})...`);
        runSeedSubprocess(seedCommand!, templateUrl);
        return Promise.resolve();
      },
    });
    for (const line of result.report) info(`  ${line}`);
    success(`Template ${result.database} is the declared state at ${result.fingerprint.slice(0, 12)}${seedCommand ? ', seeded' : ''}. Mint branch databases: everystack db:branch`);
  } catch (err: any) {
    fail(`Template refresh failed — no template was left behind (all or nothing): ${err.message}`);
    process.exit(1);
  }
}

/** Redact credentials when echoing a URL. */
function redactUrl(url: string): string {
  return url.replace(/\/\/[^@/]+@/, '//***@');
}

export async function dbBranchCommand(flags: Record<string, string>): Promise<void> {
  const url = requireUrl(flags, 'db:branch');

  if (flags.list === 'true') {
    const provenance = await readTemplateProvenance(url);
    if (provenance) {
      info(`template ${templateName(baseNameFromUrl(url))}: fingerprint ${provenance.fingerprint.slice(0, 12)}, built ${provenance.builtAt}${provenance.gitRef ? ` at ${provenance.gitRef.slice(0, 12)}` : ''}`);
    } else {
      warn(`no template — build it once: everystack db:template:refresh`);
    }
    const all = await listBranchDatabases(url);
    if (all.length === 0) {
      info('no branch databases.');
      return;
    }
    const live = new Set(localBranches());
    for (const d of all) {
      const state = d.branch === null ? 'branch unknown (comment lost)' : live.has(d.branch) ? d.branch : `${d.branch} — branch GONE (db:branch --prune)`;
      info(`  ${d.database}  ←  ${state}`);
    }
    return;
  }

  if (flags.prune === 'true') {
    const dead = prunableBranchDatabases(await listBranchDatabases(url), localBranches());
    if (dead.length === 0) {
      success('Nothing to prune — every branch database maps to a live branch.');
      return;
    }
    for (const d of dead) info(`  ${d.database}  ←  ${d.branch} (gone)`);
    if (flags.confirm !== 'true') {
      fail(`Would drop the ${dead.length} database(s) above — confirm with --confirm.`);
      process.exit(1);
    }
    for (const d of dead) {
      await dropBranchDatabase(url, d.database);
      info(`dropped ${d.database}`);
    }
    success(`Pruned ${dead.length} branch database(s).`);
    return;
  }

  const branch = currentBranch();
  if (branch === null) {
    fail('db:branch is keyed on the git branch — not a repository, or detached HEAD.');
    process.exit(1);
  }

  if (flags.drop === 'true') {
    const database = branchDatabaseName(baseNameFromUrl(url), branch);
    if (flags.confirm !== 'true') {
      fail(`Would drop ${database} (branch ${branch}) — confirm with --confirm.`);
      process.exit(1);
    }
    await dropBranchDatabase(url, database);
    success(`Dropped ${database}.`);
    return;
  }

  const result = await ensureBranchDatabase(url, branch);
  switch (result.status) {
    case 'no-template':
      fail(`No template ${result.template} — build it once: everystack db:template:refresh. Branch databases mint from it.`);
      process.exit(1);
      break;
    case 'exists':
      info(`${result.database} already exists for branch ${branch}.`);
      break;
    case 'created': {
      success(`Minted ${result.database} from ${result.template} (branch ${branch}).`);
      try {
        const provenance = await readTemplateProvenance(url);
        const declared = fingerprintModels(await loadModels(resolveModelsPath(flags.models))).hash;
        if (provenance && provenance.fingerprint !== declared) {
          info(`· your checkout declares ${declared.slice(0, 12)}; the template was built at ${provenance.fingerprint.slice(0, 12)} — db:sync below evolves it (refresh the template when it drifts far).`);
        }
      } catch {
        // The staleness note is best-effort — minting already succeeded.
      }
      break;
    }
  }
  console.log('');
  console.log(`  export DATABASE_URL="${result.url}"`);
  console.log('');
  info(`then: everystack db:sync    # evolve it with your checkout as you edit`);
}