/**
 * Pure-Prisma snapshot of the live MySQL database.
 *
 * Uses the @prisma/client that's already installed — no new dependencies,
 * no mysqldump binary, no TLS-on-npm hassle. Produces a single SQL file
 * containing:
 *   - DROP TABLE IF EXISTS for every table
 *   - CREATE TABLE statements (via SHOW CREATE TABLE)
 *   - INSERT statements for every row
 *
 * Output goes to prisma/backups/snapshot-<timestamp>.sql which is gitignored.
 *
 * SAFETY NOTES:
 *   - Read-only against the remote DB (only runs SELECT + SHOW queries).
 *   - No table locks: we open one Prisma session and read tables sequentially.
 *     For a strict point-in-time snapshot on a busy DB, run during low-traffic.
 *   - File contains bcrypt hashes, audit log, possibly plaintext channel
 *     configs. KEEP IT OFF GIT (the .gitignore rule below handles this).
 *
 * Restore plan (if you ever need to roll back):
 *   mysql -h HOST -P 3306 -u USER -p DBNAME < snapshot-YYYY-MM-DD-HHMMSS.sql
 *
 * Run with:
 *   npx tsx scripts/dump-live-db.ts
 */

import { PrismaClient } from '@prisma/client';
import fs from 'fs';
import path from 'path';
import crypto from 'crypto';

const prisma = new PrismaClient();

function pad(n: number): string { return n.toString().padStart(2, '0'); }
function timestamp(): string {
    const d = new Date();
    return `${d.getFullYear()}-${pad(d.getMonth() + 1)}-${pad(d.getDate())}-${pad(d.getHours())}${pad(d.getMinutes())}${pad(d.getSeconds())}`;
}

function sqlEscape(value: unknown): string {
    if (value === null || value === undefined) return 'NULL';
    if (typeof value === 'number') return Number.isFinite(value) ? String(value) : 'NULL';
    if (typeof value === 'boolean') return value ? '1' : '0';
    if (value instanceof Date) return `'${value.toISOString().slice(0, 19).replace('T', ' ')}'`;
    if (Buffer.isBuffer(value)) return `0x${value.toString('hex')}`;
    // Everything else: stringify and quote
    const s = typeof value === 'string' ? value : String(value);
    return `'${s.replace(/\\/g, '\\\\').replace(/'/g, "''").replace(/\0/g, '\\0').replace(/\n/g, '\\n').replace(/\r/g, '\\r').replace(/\t/g, '\\t')}'`;
}

async function main() {
    const dbName = await getDatabaseName();
    const outDir = path.join(process.cwd(), 'prisma', 'backups');
    fs.mkdirSync(outDir, { recursive: true });
    const outFile = path.join(outDir, `snapshot-${timestamp()}.sql`);
    const stream = fs.createWriteStream(outFile, { encoding: 'utf8' });

    const write = (s: string) => new Promise<void>((resolve, reject) => {
        stream.write(s, err => err ? reject(err) : resolve());
    });

    console.log(`[dump] database: ${dbName}`);
    console.log(`[dump] writing to: ${outFile}`);

    await write(`-- uptime-sentinel snapshot\n`);
    await write(`-- generated: ${new Date().toISOString()}\n`);
    await write(`-- source database: ${dbName}\n`);
    await write(`-- NOTE: contains sensitive data (bcrypt hashes, audit log, channel configs)\n\n`);
    await write(`SET FOREIGN_KEY_CHECKS=0;\n`);
    await write(`SET UNIQUE_CHECKS=0;\n`);
    await write(`SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO';\n\n`);

    const tables = await listTables();
    console.log(`[dump] ${tables.length} tables to snapshot`);

    let totalRows = 0;
    for (const t of tables) {
        process.stdout.write(`[dump]   ${t.padEnd(40)} ... `);
        const rows = await dumpTable(t, write);
        totalRows += rows;
        console.log(`${rows.toString().padStart(8)} rows`);
    }

    await write(`SET FOREIGN_KEY_CHECKS=1;\n`);
    await write(`SET UNIQUE_CHECKS=1;\n`);
    stream.end();
    await new Promise<void>(resolve => stream.on('finish', () => resolve()));

    const stats = fs.statSync(outFile);
    const sha256 = await sha256File(outFile);
    console.log('');
    console.log(`[dump] DONE`);
    console.log(`[dump]   file:      ${outFile}`);
    console.log(`[dump]   size:      ${(stats.size / (1024 * 1024)).toFixed(2)} MiB (${stats.size.toLocaleString()} bytes)`);
    console.log(`[dump]   sha256:    ${sha256}`);
    console.log(`[dump]   rows:      ${totalRows.toLocaleString()}`);
    console.log('');
    console.log(`[dump] restore (when needed):`);
    console.log(`[dump]   mysql -h <host> -P 3306 -u <user> -p <dbname> < "${outFile}"`);
}

async function getDatabaseName(): Promise<string> {
    const rows = await prisma.$queryRawUnsafe<{ db: string }[]>(`SELECT DATABASE() AS db`);
    return rows[0]?.db ?? '(unknown)';
}

async function listTables(): Promise<string[]> {
    const rows = await prisma.$queryRawUnsafe<{ name: string }[]>(`
        SELECT TABLE_NAME AS name
        FROM information_schema.TABLES
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_TYPE = 'BASE TABLE'
        ORDER BY TABLE_NAME
    `);
    return rows.map(r => r.name);
}

async function dumpTable(table: string, write: (s: string) => Promise<void>): Promise<number> {
    // Fence with backticks; SHOW CREATE TABLE returns its own ddl with backticks already.
    const ddl = await prisma.$queryRawUnsafe<{ Table: string; 'Create Table': string }[]>(`SHOW CREATE TABLE \`${table}\``);
    await write(`-- ------------------------------------------\n`);
    await write(`-- ${table}\n`);
    await write(`-- ------------------------------------------\n`);
    await write(`DROP TABLE IF EXISTS \`${table}\`;\n`);
    await write(`${ddl[0]['Create Table']};\n\n`);

    // Stream rows in batches to keep memory bounded
    const BATCH = 1000;
    let offset = 0;
    let total = 0;
    while (true) {
        const rows = await prisma.$queryRawUnsafe<Record<string, unknown>[]>(
            `SELECT * FROM \`${table}\` LIMIT ${BATCH} OFFSET ${offset}`
        );
        if (rows.length === 0) break;
        const cols = Object.keys(rows[0]);
        for (const row of rows) {
            const values = cols.map(c => sqlEscape(row[c])).join(', ');
            await write(`INSERT INTO \`${table}\` (\`${cols.join('`, `')}\`) VALUES (${values});\n`);
        }
        total += rows.length;
        offset += BATCH;
        if (rows.length < BATCH) break;
    }
    await write(`\n`);
    return total;
}

async function sha256File(p: string): Promise<string> {
    const hash = crypto.createHash('sha256');
    return new Promise((resolve, reject) => {
        const s = fs.createReadStream(p);
        s.on('data', chunk => hash.update(chunk));
        s.on('end', () => resolve(hash.digest('hex')));
        s.on('error', reject);
    });
}

main()
    .catch(e => { console.error('[dump] FAILED:', e); process.exit(1); })
    .finally(() => prisma.$disconnect());