/**
 * DDL-Auto-Export.
 *
 * Generates pure CREATE-TABLE DDL bundles per dialect at:
 *   - docs/ddl/sqlite.sql
 *   - docs/ddl/mysql.sql
 *
 * Sources:
 *   - SQLite: `drizzle-kit export --config=drizzle.sqlite.config.ts` (preferred)
 *             with a fallback to concatenating committed migration SQLs in
 *             `drizzle/sqlite/`.
 *   - MySQL:  same approach. Until the MySQL schema port (Phase D-Full) is
 *             complete, this will emit an empty/stub file with a leading
 *             comment, NOT fail — so the rest of the pipeline keeps working.
 *
 * Run:  npm run db:export-ddl
 *
 * Why both strategies? `drizzle-kit export` always reflects the current
 * Drizzle schema (handy for in-progress edits), while concatenated migration
 * files reflect what an applied migration sequence produced (handy for
 * verifying that the generated migration matches what we expect).
 *
 * The concatenated migration files are the authoritative source for hosting
 * docs; `drizzle-kit export` output is treated as a verification artifact.
 */

import { execFileSync } from 'node:child_process';
import { mkdirSync, readFileSync, readdirSync, writeFileSync, existsSync } from 'node:fs';
import { join, dirname } from 'node:path';
import { fileURLToPath } from 'node:url';

const __dirname = dirname(fileURLToPath(import.meta.url));
const REPO_ROOT = join(__dirname, '..');
const DDL_OUT_DIR = join(REPO_ROOT, 'docs', 'ddl');

type Dialect = 'sqlite' | 'mysql';

interface DialectInfo {
  dialect: Dialect;
  configFile: string;
  migrationsDir: string;
  outFile: string;
  /** True if the Drizzle schema for this dialect is fully ported. */
  schemaReady: boolean;
}

const DIALECTS: DialectInfo[] = [
  {
    dialect: 'sqlite',
    configFile: 'drizzle.sqlite.config.ts',
    migrationsDir: join(REPO_ROOT, 'drizzle', 'sqlite'),
    outFile: join(DDL_OUT_DIR, 'sqlite.sql'),
    schemaReady: true,
  },
  {
    dialect: 'mysql',
    configFile: 'drizzle.mysql.config.ts',
    migrationsDir: join(REPO_ROOT, 'drizzle', 'mysql'),
    outFile: join(DDL_OUT_DIR, 'mysql.sql'),
    schemaReady: existsSync(
      join(REPO_ROOT, 'src', 'core', 'db', 'schema', 'mysql', 'index.ts'),
    ) &&
      !readFileSync(
        join(REPO_ROOT, 'src', 'core', 'db', 'schema', 'mysql', 'index.ts'),
        'utf8',
      ).includes('schema not yet implemented'),
  },
];

function header(dialect: Dialect, source: string): string {
  return [
    `-- Mailforge Plugin — ${dialect.toUpperCase()} DDL`,
    `-- Generated by scripts/export-ddl.ts on ${new Date().toISOString()}`,
    `-- Source: ${source}`,
    '-- Do NOT edit by hand. Re-run \`npm run db:export-ddl\` after schema changes.',
    '',
  ].join('\n');
}

function loadFromMigrations(info: DialectInfo): string | null {
  if (!existsSync(info.migrationsDir)) return null;
  const files = readdirSync(info.migrationsDir)
    .filter((f) => f.endsWith('.sql'))
    .sort();
  if (files.length === 0) return null;
  return files
    .map((f) => `-- ${f}\n${readFileSync(join(info.migrationsDir, f), 'utf8')}`)
    .join('\n\n');
}

function loadFromDrizzleExport(info: DialectInfo): string | null {
  try {
    const out = execFileSync(
      'npx',
      [
        '--no-install',
        'drizzle-kit',
        'export',
        '--sql',
        `--config=${info.configFile}`,
      ],
      { cwd: REPO_ROOT, encoding: 'utf8', stdio: ['ignore', 'pipe', 'pipe'] },
    );
    return out.trim().length > 0 ? out : null;
  } catch {
    return null;
  }
}

function writeStub(info: DialectInfo): void {
  const body = [
    header(info.dialect, 'stub (schema not yet ported)'),
    `-- The ${info.dialect} schema is not yet implemented in src/core/db/schema/${info.dialect}/.`,
    '-- See docs/SCHEMA.md and the dual-driver section in CLAUDE.md for the porting plan.',
    '-- This stub keeps integration docs valid while the port is in progress.',
    '',
  ].join('\n');
  writeFileSync(info.outFile, body);
  console.log(`  ⚠  ${info.dialect}: schema not ready — wrote stub to ${info.outFile}`);
}

function run(): void {
  mkdirSync(DDL_OUT_DIR, { recursive: true });
  console.log('🔧 Exporting DDL bundles to docs/ddl/');

  for (const info of DIALECTS) {
    if (!info.schemaReady) {
      writeStub(info);
      continue;
    }

    const fromMigrations = loadFromMigrations(info);
    if (fromMigrations) {
      writeFileSync(
        info.outFile,
        `${header(info.dialect, `concat of ${info.migrationsDir.replace(REPO_ROOT, '.')}/*.sql`)}\n${fromMigrations}\n`,
      );
      console.log(`  ✓ ${info.dialect}: ${info.outFile}`);
      continue;
    }

    const fromExport = loadFromDrizzleExport(info);
    if (fromExport) {
      writeFileSync(
        info.outFile,
        `${header(info.dialect, `drizzle-kit export --config=${info.configFile}`)}\n${fromExport}\n`,
      );
      console.log(`  ✓ ${info.dialect}: ${info.outFile}  (from drizzle-kit export)`);
      continue;
    }

    writeStub(info);
  }

  console.log('✓ Done.');
}

run();
