<?php

declare(strict_types=1);

namespace PpPhp\Core\Database;

/**
 * Splits a free-form SQL script into individual statements and executes them,
 * one by one, against an already-connected PDO handle — returning a per-statement
 * report (status, message, affected rows, timing, and a bounded result set for
 * queries that return rows).
 *
 * The splitter is dialect-agnostic: it understands `--` and `#` line comments,
 * `/* … *\/` block comments, single/double-quoted strings (with `''` doubling and
 * backslash escapes, as MySQL dumps use), backtick identifiers, and PostgreSQL
 * dollar-quoted strings (`$tag$ … $tag$`) so a semicolon inside any of those is
 * never mistaken for a statement boundary. Execution itself goes through PDO, so
 * it works for every database type the platform can connect to.
 *
 * This is an OWNER/developer tool: the caller is intentionally running their own
 * SQL against their own project database (like a phpMyAdmin console). Access is
 * gated upstream by the controller (login + project ownership); this class does
 * not sanitise the SQL — that would defeat the purpose.
 */
final class SqlScriptRunner
{
    public function __construct(private \PDO $pdo)
    {
    }

    /**
     * Break a script into executable statements. Comment-only / empty fragments
     * are dropped. Statement text keeps its inline comments (databases accept them).
     *
     * @return list<string>
     */
    public static function split(string $sql): array
    {
        return array_map(static fn(array $s): string => $s['sql'], self::scan($sql));
    }

    /**
     * Like split(), but each entry also carries the 1-based editor line where the
     * statement's real SQL begins (leading whitespace/comments skipped) — used to
     * highlight a failing statement back in the editor.
     *
     * @return list<array{sql:string,line:int}>
     */
    public static function scan(string $sql): array
    {
        $sql = str_replace(["\r\n", "\r"], "\n", $sql);
        // Strip a UTF-8 BOM if a file was pasted with one.
        if (str_starts_with($sql, "\xEF\xBB\xBF")) {
            $sql = substr($sql, 3);
        }

        $segments = [];
        $buf = '';
        $len = strlen($sql);
        $i = 0;
        $segStart = 0;

        $push = static function (string $buf, int $segStart) use ($sql, $len, &$segments): void {
            if (!self::hasSql($buf)) {
                return;
            }
            // Advance past leading whitespace and comments to the first real SQL char.
            $p = $segStart;
            while ($p < $len) {
                $c = $sql[$p];
                if ($c === ' ' || $c === "\t" || $c === "\n" || $c === "\r") { $p++; continue; }
                if ($c === '#' || ($c === '-' && $p + 1 < $len && $sql[$p + 1] === '-')) {
                    $nl = strpos($sql, "\n", $p);
                    $p = $nl === false ? $len : $nl + 1;
                    continue;
                }
                if ($c === '/' && $p + 1 < $len && $sql[$p + 1] === '*') {
                    $ce = strpos($sql, '*/', $p + 2);
                    $p = $ce === false ? $len : $ce + 2;
                    continue;
                }
                break;
            }
            $segments[] = ['sql' => trim($buf), 'line' => 1 + substr_count($sql, "\n", 0, min($p, $len))];
        };

        while ($i < $len) {
            $ch = $sql[$i];
            $next = $i + 1 < $len ? $sql[$i + 1] : '';

            // Line comment: -- …  or  # …
            if (($ch === '-' && $next === '-') || $ch === '#') {
                $eol = strpos($sql, "\n", $i);
                $stop = $eol === false ? $len : $eol;
                $buf .= substr($sql, $i, $stop - $i);
                $i = $stop;
                continue;
            }

            // Block comment: /* … */
            if ($ch === '/' && $next === '*') {
                $end = strpos($sql, '*/', $i + 2);
                $stop = $end === false ? $len : $end + 2;
                $buf .= substr($sql, $i, $stop - $i);
                $i = $stop;
                continue;
            }

            // Quoted regions: '…'  "…"  `…`
            if ($ch === "'" || $ch === '"' || $ch === '`') {
                $j = self::scanQuoted($sql, $i, $ch, $len);
                $buf .= substr($sql, $i, $j - $i);
                $i = $j;
                continue;
            }

            // PostgreSQL dollar-quoted string: $tag$ … $tag$
            if ($ch === '$') {
                $tagEnd = $i + 1;
                while ($tagEnd < $len && (ctype_alnum($sql[$tagEnd]) || $sql[$tagEnd] === '_')) {
                    $tagEnd++;
                }
                if ($tagEnd < $len && $sql[$tagEnd] === '$') {
                    $tag = substr($sql, $i, $tagEnd - $i + 1); // e.g. "$body$"
                    $close = strpos($sql, $tag, $tagEnd + 1);
                    $stop = $close === false ? $len : $close + strlen($tag);
                    $buf .= substr($sql, $i, $stop - $i);
                    $i = $stop;
                    continue;
                }
            }

            // Statement boundary.
            if ($ch === ';') {
                $push($buf, $segStart);
                $buf = '';
                $i++;
                $segStart = $i;
                continue;
            }

            $buf .= $ch;
            $i++;
        }

        $push($buf, $segStart);

        return $segments;
    }

    /**
     * Execute every statement in order.
     *
     * @param array{stopOnError?:bool,transaction?:bool,maxRows?:int} $options
     * @return array{ok:bool,statements:list<array<string,mixed>>,executed:int,failed:int,affected:int,elapsedMs:float,transaction:string}
     */
    public function execute(string $sql, array $options = []): array
    {
        $stopOnError = $options['stopOnError'] ?? true;
        $dryRun      = !empty($options['dryRun']);
        $useTx       = !empty($options['transaction']) || $dryRun;
        $maxRows     = max(1, min(2000, (int) ($options['maxRows'] ?? 200)));

        $list = self::scan($sql);
        $report = [];
        $executed = 0;
        $failed = 0;
        $affectedTotal = 0;
        $aborted = false;
        $txState = 'none';

        $startedTx = false;
        if ($useTx) {
            try {
                $this->pdo->beginTransaction();
                $startedTx = true;
                $txState = 'open';
            } catch (\Throwable) {
                $txState = 'unsupported';
            }
        }

        $t0 = microtime(true);
        foreach ($list as $n => $seg) {
            $stmt = $seg['sql'];
            $line = $seg['line'];
            if ($aborted) {
                $report[] = $this->row($n + 1, $stmt, 'skipped', 'Skipped — a previous statement failed.', ['line' => $line]);
                continue;
            }

            $entry = $this->runOne($n + 1, $stmt, $maxRows);
            $entry['line'] = $line;
            $report[] = $entry;

            if ($entry['status'] === 'error') {
                $failed++;
                if ($stopOnError) {
                    $aborted = true;
                }
            } else {
                $executed++;
                $affectedTotal += (int) ($entry['affected'] ?? 0);
            }
        }
        $elapsed = (microtime(true) - $t0) * 1000;

        if ($startedTx) {
            try {
                if ($dryRun || $failed > 0) {
                    // Dry run always rolls back — nothing is ever saved.
                    $this->pdo->rollBack();
                    $txState = $dryRun ? 'dry_run' : 'rolled_back';
                } else {
                    $this->pdo->commit();
                    $txState = 'committed';
                }
            } catch (\Throwable) {
                // DDL on engines like MySQL auto-commits, so the handle may no longer
                // be in a transaction — nothing more we can do; report best-effort.
                $txState = $failed > 0 ? 'rollback_failed' : 'committed';
            }
        }

        return [
            'ok'          => $failed === 0,
            'statements'  => $report,
            'executed'    => $executed,
            'failed'      => $failed,
            'affected'    => $affectedTotal,
            'elapsedMs'   => round($elapsed, 1),
            'transaction' => $txState,
            'dryRun'      => $dryRun,
        ];
    }

    private function runOne(int $n, string $stmt, int $maxRows): array
    {
        $t0 = microtime(true);
        try {
            $result = $this->pdo->query($stmt);
            $ms = round((microtime(true) - $t0) * 1000, 1);

            if ($result instanceof \PDOStatement && $result->columnCount() > 0) {
                // Fetch incrementally and stop one past the display cap — never pull a
                // giant result set fully into memory (this is an interactive console).
                $rows = [];
                $more = false;
                while (($r = $result->fetch(\PDO::FETCH_ASSOC)) !== false) {
                    if (count($rows) >= $maxRows) { $more = true; break; }
                    $rows[] = $r;
                }
                $columns = $rows ? array_keys($rows[0]) : [];
                $result->closeCursor();
                return $this->row($n, $stmt, 'ok', '', [
                    'kind'      => 'read',
                    'affected'  => 0,
                    'rowCount'  => count($rows),
                    'truncated' => $more,
                    'columns'   => $columns,
                    'rows'      => array_map('array_values', $rows),
                    'ms'        => $ms,
                ]);
            }

            $affected = $result instanceof \PDOStatement ? $result->rowCount() : 0;
            if ($result instanceof \PDOStatement) {
                $result->closeCursor();
            }
            return $this->row($n, $stmt, 'ok', '', [
                'kind'     => 'write',
                'affected' => $affected,
                'ms'       => $ms,
            ]);
        } catch (\PDOException $e) {
            return $this->row($n, $stmt, 'error', $e->getMessage(), [
                'ms' => round((microtime(true) - $t0) * 1000, 1),
            ]);
        }
    }

    private function row(int $n, string $stmt, string $status, string $message = '', array $extra = []): array
    {
        $preview = trim(preg_replace('/\s+/', ' ', $stmt) ?? $stmt);
        if (strlen($preview) > 240) {
            $preview = substr($preview, 0, 237) . '…';
        }
        return [
            'n'       => $n,
            'sql'     => $preview,
            'status'  => $status,
            'message' => $message,
        ] + $extra;
    }

    /** Advance past a quoted region, honouring doubled quotes and backslash escapes. */
    private static function scanQuoted(string $sql, int $i, string $quote, int $len): int
    {
        $j = $i + 1;
        while ($j < $len) {
            $c = $sql[$j];
            if ($c === '\\' && $quote !== '`') { // backticks don't use backslash escapes
                $j += 2;
                continue;
            }
            if ($c === $quote) {
                if ($j + 1 < $len && $sql[$j + 1] === $quote) { // doubled → escaped quote
                    $j += 2;
                    continue;
                }
                return $j + 1; // closing quote
            }
            $j++;
        }
        return $len;
    }

    /** True when the fragment holds real SQL (not just whitespace/comments). */
    private static function hasSql(string $fragment): bool
    {
        $stripped = preg_replace('~/\*.*?\*/~s', ' ', $fragment) ?? $fragment;
        $stripped = preg_replace('~(^|\n)\s*(--|#)[^\n]*~', ' ', $stripped) ?? $stripped;
        return trim($stripped) !== '';
    }
}
