<?php

declare(strict_types=1);

namespace PpPhp\Core\Projects;

use PDO;
use PpPhp\Core\DatabaseConnection;
use PpPhp\Core\Database\Dialect\DatabaseDialectRegistry;
use PpPhp\Core\Database\Dialect\DatabaseDialectInterface;
use PpPhp\Core\SystemTables;

/**
 * Generates a portable SQL script (structure and/or data) for a project's
 * database — the "Generate SQL Script" feature. Think of it as an in-app,
 * dialect-aware mysqldump: a single .sql file you can run on another server to
 * recreate the schema and re-seed the data, or keep as a plain-text backup.
 *
 *  • Structure uses each engine's own authoritative DDL where possible
 *    (MySQL/MariaDB SHOW CREATE, SQLite sqlite_master) and a portable
 *    describe()-based CREATE elsewhere.
 *  • Data is emitted as parameter-safe INSERTs (values quoted through PDO,
 *    binary as hex), optionally batched into multi-row statements and capped to
 *    a per-table row limit.
 *  • Everything streams to a writable resource so a large dump never has to sit
 *    in memory as one string.
 *
 * Read-only against the source database: it only ever SELECTs and reads schema
 * metadata; it never alters the connected database.
 */
final class SqlScriptService
{
    private array $config;
    private PDO $pdo;
    private DatabaseDialectInterface $dialect;
    private string $dialectId;

    /** Target-dialect conversion state (null/empty = native dump, no conversion). */
    private ?DatabaseDialectInterface $target = null;
    private string $targetId = '';
    private array $targetConfig = [];
    /** @var array<string,array{fields:array,autoInc:string}> per-table converted metadata cache */
    private array $convMeta = [];

    public function __construct(private string $projectDir, array $connection)
    {
        $this->config    = DatabaseConnection::normalize($connection);
        $this->pdo       = DatabaseConnection::connect($this->config, $projectDir);
        $this->dialect   = DatabaseDialectRegistry::forConfig($this->config);
        $this->dialectId = strtolower((string) ($this->config['type'] ?? 'mysql'));
    }

    /** Engine ids a script can be converted TO (everything the platform lists). */
    public static function targetOptions(): array
    {
        $out = [];
        foreach (DatabaseConnection::definitions() as $type => $def) {
            $out[] = ['id' => $type, 'label' => (string) $def['label']];
        }
        return $out;
    }

    /** The dialect whose SQL flavour the OUTPUT is written in. */
    private function outDialect(): DatabaseDialectInterface
    {
        return $this->target ?? $this->dialect;
    }

    private function outId(): string
    {
        return $this->targetId !== '' ? $this->targetId : $this->dialectId;
    }

    private function qi(string $name): string
    {
        return $this->outDialect()->quoteIdentifier($name);
    }

    /** Human label + engine id for the modal header. */
    public function info(): array
    {
        return [
            'dialect'  => ['id' => $this->dialect->id(), 'label' => $this->dialect->label()],
            'database' => (string) ($this->config['database'] ?? $this->config['name'] ?? ''),
            'host'     => (string) ($this->config['host'] ?? ''),
        ];
    }

    /**
     * The objects available to export for a scope, with row counts, so the modal
     * can preview what a script will contain and offer a per-object picker.
     *
     * @return array<int,array{name:string,type:string,rows:int,system:bool}>
     */
    public function objects(string $scope): array
    {
        $selected = $this->projectTables();
        $out = [];
        foreach ($this->dialect->listObjects($this->pdo, $this->config) as $object) {
            $name = (string) $object['name'];
            $type = strtoupper((string) ($object['type'] ?? 'TABLE'));
            $system = SystemTables::isSystem($name);
            $inProject = in_array($name, $selected, true);
            if ($scope === 'project' && !$inProject) continue;
            $out[] = ['name' => $name, 'type' => $type, 'rows' => $type === 'VIEW' ? 0 : $this->rowCount($name), 'system' => $system, 'in_project' => $inProject];
        }
        usort($out, static fn ($a, $b) => strcasecmp($a['name'], $b['name']));
        return $out;
    }

    /**
     * Write a SQL script to $out (a writable stream resource) per $options.
     *
     * @param array{scope?:string,mode?:string,rowLimit?:int,tables?:array,dropTable?:bool,
     *   ifNotExists?:bool,disableForeignKeys?:bool,transaction?:bool,extendedInsert?:bool,
     *   comments?:bool} $options
     * @param resource $out
     * @return array{tables:int,rows:int,bytes:int}
     */
    public function generate(array $options, $out): array
    {
        $scope    = ($options['scope'] ?? 'project') === 'all' ? 'all' : 'project';
        $mode     = (string) ($options['mode'] ?? 'both');
        if (!in_array($mode, ['structure', 'data', 'both'], true)) $mode = 'both';
        $rowLimit = max(0, (int) ($options['rowLimit'] ?? 0));
        $dropTable   = (bool) ($options['dropTable'] ?? true);
        $ifNotExists = (bool) ($options['ifNotExists'] ?? false);
        $disableFks  = (bool) ($options['disableForeignKeys'] ?? true);
        $transaction = (bool) ($options['transaction'] ?? true);
        $extended    = (bool) ($options['extendedInsert'] ?? true);
        $comments    = (bool) ($options['comments'] ?? true);
        $wantStruct  = $mode === 'structure' || $mode === 'both';
        $wantData    = $mode === 'data' || $mode === 'both';

        // Target-dialect conversion: write the script in ANOTHER engine's SQL so
        // it can be hosted live on whatever server the user actually has.
        $targetType = strtolower(trim((string) ($options['targetType'] ?? '')));
        $this->target = null; $this->targetId = ''; $this->targetConfig = []; $this->convMeta = [];
        if ($targetType !== '' && $targetType !== 'native' && $targetType !== $this->dialectId) {
            $tCfg = DatabaseConnection::normalize(['type' => $targetType, 'dbname' => 'export']); // throws on unknown type
            $this->target       = DatabaseDialectRegistry::forConfig($tCfg);
            $this->targetId     = strtolower((string) $tCfg['type']);
            $this->targetConfig = $tCfg;
        }
        $outId       = $this->outId();
        $isMysql     = in_array($outId, ['mysql', 'mariadb', 'tidb'], true);
        $isPg        = in_array($outId, ['pgsql', 'cockroachdb'], true);

        // Resolve the object set: an explicit picker list wins, else the whole scope.
        $available = $this->objects($scope);
        $pick = array_values(array_unique(array_map('strval', (array) ($options['tables'] ?? []))));
        $objects = $pick === []
            ? $available
            : array_values(array_filter($available, static fn ($o) => in_array($o['name'], $pick, true)));
        if ($objects === []) {
            throw new \InvalidArgumentException('No tables match the selected scope. Choose at least one object to export.');
        }

        // Views are emitted after tables so their dependencies exist.
        usort($objects, static fn ($a, $b) => ($a['type'] === 'VIEW' ? 1 : 0) <=> ($b['type'] === 'VIEW' ? 1 : 0));

        $w = static function (string $s) use ($out): int { return (int) fwrite($out, $s); };
        $bytes = 0; $rowsTotal = 0;

        if ($comments) {
            $info = $this->info();
            $bytes += $w("-- PPPHP SQL script\n");
            $bytes += $w('-- Generated: ' . date('Y-m-d H:i:s') . "\n");
            $bytes += $w('-- Engine:    ' . $info['dialect']['label'] . ($info['database'] !== '' ? ' · database ' . $info['database'] : '') . "\n");
            if ($this->target !== null) {
                $bytes += $w('-- Converted: written for ' . $this->target->label() . " — run it on that engine.\n");
            }
            $bytes += $w('-- Scope:     ' . ($scope === 'all' ? 'all database tables' : 'project tables only') . ' · ' . count($objects) . " object(s)\n");
            $bytes += $w('-- Contents:  ' . ($mode === 'both' ? 'structure and data' : $mode) . ($wantData && $rowLimit > 0 ? ' (up to ' . $rowLimit . ' rows/table)' : '') . "\n");
            $bytes += $w("-- Restore this by running it against an empty database on the target server.\n\n");
        }

        if ($isMysql) {
            $bytes += $w("SET NAMES utf8mb4;\n");
            if ($disableFks) $bytes += $w("SET FOREIGN_KEY_CHECKS=0;\n");
        } elseif ($outId === 'sqlite' && $disableFks) {
            $bytes += $w("PRAGMA foreign_keys=OFF;\n");
        }
        if ($transaction) {
            $bytes += $w(match (true) {
                $isMysql            => "SET autocommit=0;\nSTART TRANSACTION;\n",
                $outId === 'sqlsrv' => "BEGIN TRANSACTION;\n",
                $outId === 'oci'    => '',                    // Oracle transactions open implicitly
                default             => "BEGIN;\n",
            });
        }
        $bytes += $w("\n");

        foreach ($objects as $object) {
            $name = $object['name'];
            $type = $object['type'];
            $q    = $this->qi($name);

            if ($comments) $bytes += $w("-- ----------------------------\n-- " . ($type === 'VIEW' ? 'View' : 'Table') . ": {$name}\n-- ----------------------------\n");

            if ($wantStruct) {
                if ($this->target !== null && $type === 'VIEW') {
                    // View SQL is engine-specific — an automatic translation would
                    // be a guess. Say so honestly instead of emitting broken DDL.
                    $bytes += $w("-- View {$name}: skipped — recreate views by hand on " . $this->target->label() . ".\n\n");
                    continue;
                }
                if ($dropTable) $bytes += $w('DROP ' . ($type === 'VIEW' ? 'VIEW' : 'TABLE') . " IF EXISTS {$q};\n");
                $ddl = $this->target !== null && $type !== 'VIEW'
                    ? $this->convertedCreate($name)
                    : $this->structure($name, $type);
                if ($ifNotExists && !$dropTable) $ddl = $this->applyIfNotExists($ddl, $type);
                $bytes += $w(rtrim($ddl, "; \n") . ";\n\n");
            }

            if ($wantData && $type !== 'VIEW') {
                $written = $this->streamData($name, $rowLimit, $extended, $out);
                $rowsTotal += $written['rows'];
                $bytes += $written['bytes'];
                if ($written['rows'] > 0) $bytes += $w("\n");
            }
        }

        if ($transaction) $bytes += $w($outId === 'sqlsrv' ? "COMMIT TRANSACTION;\n" : "COMMIT;\n");
        if ($isMysql && $disableFks) $bytes += $w("SET FOREIGN_KEY_CHECKS=1;\n");
        elseif ($outId === 'sqlite' && $disableFks) $bytes += $w("PRAGMA foreign_keys=ON;\n");

        return ['tables' => count($objects), 'rows' => $rowsTotal, 'bytes' => $bytes];
    }

    // ── target-dialect conversion ─────────────────────────────────────────

    /**
     * Describe a table on the SOURCE engine and translate its fields into the
     * TARGET dialect's vocabulary (same canonical mapping the schema-blueprint
     * portability path uses). Cached per table — structure and data both need it.
     *
     * @return array{fields:array,autoInc:string}
     */
    private function convertedFields(string $name): array
    {
        if (isset($this->convMeta[$name])) return $this->convMeta[$name];
        $details = $this->dialect->describeTable($this->pdo, $name, $this->config);
        $ui = $this->target !== null ? $this->target->uiConfig() : $this->dialect->uiConfig();
        $fields = [];
        $autoInc = '';
        foreach ((array) ($details['columns'] ?? []) as $column) {
            $field = ProjectSchemaBlueprintService::forDialect(ProjectSchemaBlueprintService::portableField($column), $ui);
            if ((string) ($field['name'] ?? '') === '') continue;
            if (!empty($field['auto_increment'])) $autoInc = (string) $field['name'];
            $fields[] = $field;
        }
        return $this->convMeta[$name] = ['fields' => $fields, 'autoInc' => $autoInc];
    }

    /** CREATE TABLE in the TARGET dialect, built from the source's described columns. */
    private function convertedCreate(string $name): string
    {
        $meta = $this->convertedFields($name);
        if ($meta['fields'] === []) {
            throw new \RuntimeException("Table {$name} has no describable columns to convert.");
        }
        return (string) $this->target->compileCreateTable($name, $meta['fields'], $this->targetConfig);
    }

    // ── structure ─────────────────────────────────────────────────────────

    /** Authoritative CREATE statement for a table/view, dialect by dialect. */
    private function structure(string $name, string $type): string
    {
        if (in_array($this->dialectId, ['mysql', 'mariadb'], true)) {
            $q = $this->dialect->quoteIdentifier($name);
            $row = $this->pdo->query('SHOW CREATE ' . ($type === 'VIEW' ? 'VIEW' : 'TABLE') . " {$q}")->fetch(PDO::FETCH_ASSOC) ?: [];
            $ddl = (string) ($row['Create Table'] ?? $row['Create View'] ?? '');
            // Views carry a DEFINER=`user`@`host` clause that breaks the import on
            // any other server — strip it + force INVOKER so the dump is portable.
            if ($type === 'VIEW' && $ddl !== '') return \PpPhp\Core\Database\SqlPortability::view($ddl);
            if ($ddl !== '') return $ddl;
        }
        if ($this->dialectId === 'sqlite') {
            $st = $this->pdo->prepare("SELECT sql FROM sqlite_master WHERE name = ? AND sql IS NOT NULL LIMIT 1");
            $st->execute([$name]);
            $ddl = (string) ($st->fetchColumn() ?: '');
            if ($ddl !== '') return $ddl;
        }
        // Portable fallback (PostgreSQL, SQL Server, or anything without a native
        // "show create"): rebuild a CREATE TABLE from the described columns.
        return $this->portableCreate($name);
    }

    /** A best-effort CREATE TABLE from describe() — columns, nullability, PK. */
    private function portableCreate(string $name): string
    {
        $details = $this->dialect->describeTable($this->pdo, $name, $this->config);
        $columns = (array) ($details['columns'] ?? []);
        if (method_exists($this->dialect, 'compileCreateTable')) {
            try {
                $fields = $this->normalizeFields($columns);
                $sql = $this->dialect->compileCreateTable($name, $fields, $this->config);
                if (is_string($sql) && $sql !== '') return $sql;
            } catch (\Throwable $e) { /* fall through to the hand-rolled form */ }
        }
        $lines = []; $pk = [];
        foreach ($columns as $c) {
            $col = (string) ($c['name'] ?? $c['COLUMN_NAME'] ?? '');
            if ($col === '') continue;
            $type = strtoupper((string) ($c['type'] ?? $c['DATA_TYPE'] ?? 'VARCHAR'));
            $size = (int) ($c['size'] ?? $c['CHARACTER_MAXIMUM_LENGTH'] ?? 0);
            $nullable = array_key_exists('nullable', $c) ? (bool) $c['nullable'] : strtoupper((string) ($c['IS_NULLABLE'] ?? 'YES')) === 'YES';
            $def = $this->dialect->quoteIdentifier($col) . ' ' . $type . ($size > 0 && in_array($type, ['VARCHAR', 'CHAR'], true) ? "({$size})" : '') . ($nullable ? '' : ' NOT NULL');
            $lines[] = '  ' . $def;
            if (!empty($c['primary_key']) || ($c['COLUMN_KEY'] ?? '') === 'PRI') $pk[] = $this->dialect->quoteIdentifier($col);
        }
        if ($pk) $lines[] = '  PRIMARY KEY (' . implode(', ', $pk) . ')';
        return 'CREATE TABLE ' . $this->dialect->quoteIdentifier($name) . " (\n" . implode(",\n", $lines) . "\n)";
    }

    private function normalizeFields(array $columns): array
    {
        $out = [];
        foreach ($columns as $c) {
            $name = (string) ($c['name'] ?? $c['COLUMN_NAME'] ?? '');
            if ($name === '') continue;
            $out[] = [
                'name' => $name,
                'type' => strtoupper((string) ($c['type'] ?? $c['DATA_TYPE'] ?? 'VARCHAR')),
                'size' => (int) ($c['size'] ?? $c['CHARACTER_MAXIMUM_LENGTH'] ?? 0),
                'nullable' => array_key_exists('nullable', $c) ? (bool) $c['nullable'] : strtoupper((string) ($c['IS_NULLABLE'] ?? 'YES')) === 'YES',
                'primary_key' => !empty($c['primary_key']) || ($c['COLUMN_KEY'] ?? '') === 'PRI',
                'auto_increment' => !empty($c['auto_increment']) || ($c['EXTRA'] ?? '') === 'auto_increment',
            ];
        }
        return $out;
    }

    private function applyIfNotExists(string $ddl, string $type): string
    {
        $needle = 'CREATE ' . ($type === 'VIEW' ? 'VIEW' : 'TABLE') . ' ';
        if (stripos($ddl, $needle . 'IF NOT EXISTS') !== false) return $ddl;
        $pos = stripos($ddl, $needle);
        if ($pos === false) return $ddl;
        return substr($ddl, 0, $pos + strlen($needle)) . 'IF NOT EXISTS ' . substr($ddl, $pos + strlen($needle));
    }

    // ── data ──────────────────────────────────────────────────────────────

    /**
     * Stream INSERTs for one table straight to $out.
     *
     * @param resource $out
     * @return array{rows:int,bytes:int}
     */
    private function streamData(string $name, int $rowLimit, bool $extended, $out): array
    {
        // Rows are always READ with the source dialect's quoting…
        $srcQ = $this->dialect->quoteIdentifier($name);
        $sql = "SELECT * FROM {$srcQ}";
        if ($rowLimit > 0) $sql .= ' LIMIT ' . $rowLimit;

        $stmt = $this->pdo->query($sql);
        if ($stmt === false) return ['rows' => 0, 'bytes' => 0];

        // …and WRITTEN with the output dialect's quoting (same thing unless converting).
        $q = $this->qi($name);

        // Column names + which are binary (emit as hex, not quoted text).
        $colCount = $stmt->columnCount();
        $cols = []; $binary = [];
        for ($i = 0; $i < $colCount; $i++) {
            $m = $stmt->getColumnMeta($i) ?: [];
            $cols[] = $this->qi((string) ($m['name'] ?? "col{$i}"));
            $native = strtolower((string) ($m['native_type'] ?? ''));
            $binary[$i] = str_contains($native, 'blob') || in_array($native, ['bytea'], true) || in_array('blob', (array) ($m['flags'] ?? []), true);
        }
        $colList = implode(', ', $cols);
        $prefix  = "INSERT INTO {$q} ({$colList}) VALUES ";

        $rows = 0; $bytes = 0; $batch = []; $batchBytes = 0;
        $flush = function () use (&$batch, &$batchBytes, $out, $prefix, &$bytes) {
            if (!$batch) return;
            $chunk = $prefix . implode(",\n", $batch) . ";\n";
            $bytes += (int) fwrite($out, $chunk);
            $batch = []; $batchBytes = 0;
        };

        // SQL Server refuses explicit values in an IDENTITY column unless the
        // table is put into IDENTITY_INSERT mode for the duration of the seed.
        $autoInc = $this->target !== null ? $this->convertedFields($name)['autoInc'] : '';
        if ($this->targetId === 'sqlsrv' && $autoInc !== '') {
            $bytes += (int) fwrite($out, "SET IDENTITY_INSERT {$q} ON;\n");
        }

        while (($row = $stmt->fetch(PDO::FETCH_NUM)) !== false) {
            $vals = [];
            foreach ($row as $i => $v) $vals[] = $this->literal($v, !empty($binary[$i]));
            $tuple = '(' . implode(', ', $vals) . ')';
            $rows++;
            if (!$extended) {
                $bytes += (int) fwrite($out, $prefix . $tuple . ";\n");
                continue;
            }
            $batch[] = $tuple; $batchBytes += strlen($tuple);
            // Cap a multi-row statement so no single INSERT gets unwieldy.
            if (count($batch) >= 200 || $batchBytes >= 768 * 1024) $flush();
        }
        $flush();

        if ($this->targetId === 'sqlsrv' && $autoInc !== '') {
            $bytes += (int) fwrite($out, "SET IDENTITY_INSERT {$q} OFF;\n");
        }
        // PostgreSQL-family sequences don't advance on explicit-id INSERTs — leave
        // the sequence pointing past the seeded rows so the next insert works.
        if (in_array($this->targetId, ['pgsql', 'cockroachdb'], true) && $autoInc !== '' && $rows > 0) {
            $col = $this->qi($autoInc);
            $lit = "'" . str_replace("'", "''", $name) . "'";
            $colLit = "'" . str_replace("'", "''", $autoInc) . "'";
            $bytes += (int) fwrite($out, "SELECT setval(pg_get_serial_sequence({$lit}, {$colLit}), (SELECT COALESCE(MAX({$col}), 1) FROM {$q}));\n");
        }
        return ['rows' => $rows, 'bytes' => $bytes];
    }

    /** A SQL literal for a fetched value: NULL, hex blob, or a quoted string. */
    private function literal(mixed $v, bool $binary): string
    {
        $outId = $this->outId();
        if ($v === null) return 'NULL';
        // Safety net: column metadata can miss a binary column (SQLite reports the
        // VALUE type, not the declared one). A null byte or invalid UTF-8 can never
        // survive a quoted text literal — always hex those. (PDO::quote on SQLite
        // outright throws on null bytes.)
        if (!$binary && is_string($v) && (str_contains($v, "\0") || !mb_check_encoding($v, 'UTF-8'))) {
            $binary = true;
        }
        if ($binary) {
            $hex = bin2hex((string) $v);
            if ($hex === '') return "''";
            return match (true) {
                in_array($outId, ['mysql', 'mariadb', 'tidb'], true) => '0x' . $hex,
                $outId === 'sqlsrv'                                   => '0x' . $hex,
                in_array($outId, ['pgsql', 'cockroachdb'], true)      => "'\\x" . $hex . "'",
                $outId === 'oci'                                      => "HEXTORAW('" . $hex . "')",
                default                                               => "X'" . $hex . "'",
            };
        }
        if ($this->target === null) {
            // Native dump: quote through the live PDO — matches the engine exactly.
            return $this->pdo->quote((string) $v);
        }
        // Converted dump: build the literal by the TARGET's rules. Doubling the
        // single quote is universal; MySQL additionally treats backslash as an
        // escape, and SQL Server needs N'…' for non-ASCII text.
        $s = str_replace("'", "''", (string) $v);
        if (in_array($outId, ['mysql', 'mariadb', 'tidb'], true)) {
            $s = str_replace('\\', '\\\\', $s);
        }
        $quoted = "'" . $s . "'";
        if ($outId === 'sqlsrv' && preg_match('/[^\x00-\x7F]/', (string) $v)) {
            $quoted = 'N' . $quoted;
        }
        return $quoted;
    }

    // ── helpers ─────────────────────────────────────────────────────────────

    /** The project's tracked table names (from config/tables.json selectedTables). */
    private function projectTables(): array
    {
        $path = $this->projectDir . '/config/tables.json';
        $state = is_file($path) ? json_decode((string) file_get_contents($path), true) : [];
        return array_values(array_map('strval', (array) (($state['selectedTables'] ?? []))));
    }

    private function rowCount(string $name): int
    {
        try {
            $q = $this->dialect->quoteIdentifier($name);
            return (int) $this->pdo->query("SELECT COUNT(*) FROM {$q}")->fetchColumn();
        } catch (\Throwable $e) {
            return 0;
        }
    }
}
