<?php

declare(strict_types=1);

namespace PpPhp\Core\Database;

/**
 * Static, read-only analysis of a SQL script — a "plan" the editor shows BEFORE
 * anything touches the database: how many statements, what each one does, which
 * tables it targets, a rough row-count for inserts, and which operations are
 * destructive (DROP / TRUNCATE / DELETE or UPDATE without a WHERE).
 *
 * It never connects to a database. Splitting + line numbers come from
 * SqlScriptRunner::scan(); classification is keyword-based on each statement.
 */
final class SqlScriptAnalyzer
{
    /** @return array{statements:list<array<string,mixed>>,summary:array<string,int>} */
    public static function analyze(string $sql): array
    {
        $segments = SqlScriptRunner::scan($sql);
        $statements = [];
        $summary = [
            'total' => 0, 'creates' => 0, 'inserts' => 0, 'insertRows' => 0,
            'alters' => 0, 'drops' => 0, 'reads' => 0, 'writes' => 0, 'destructive' => 0, 'other' => 0,
        ];

        foreach ($segments as $n => $seg) {
            $entry = self::classify($seg['sql']);
            $entry['n'] = $n + 1;
            $entry['line'] = $seg['line'];
            $statements[] = $entry;

            $summary['total']++;
            switch ($entry['action']) {
                case 'create-table': case 'create-view': case 'create-index': $summary['creates']++; break;
                case 'insert': $summary['inserts']++; $summary['insertRows'] += (int) ($entry['rows'] ?? 0); break;
                case 'alter': $summary['alters']++; break;
                case 'drop': case 'truncate': $summary['drops']++; break;
                case 'select': $summary['reads']++; break;
                case 'update': case 'delete': $summary['writes']++; break;
                default: $summary['other']++;
            }
            if (!empty($entry['destructive'])) {
                $summary['destructive']++;
            }
        }

        return ['statements' => $statements, 'summary' => $summary];
    }

    private static function classify(string $stmt): array
    {
        // Strip a leading comment block so the keyword match sees real SQL.
        $s = ltrim(preg_replace('~^(?:\s|--[^\n]*\n|#[^\n]*\n|/\*.*?\*/)*~s', '', $stmt) ?? $stmt);
        $ic = '[`"\[]?([A-Za-z_][A-Za-z0-9_.]*)';

        if (preg_match('/^create\s+(?:temporary\s+)?table\s+(?:if\s+not\s+exists\s+)?' . $ic . '/i', $s, $m)) {
            return ['action' => 'create-table', 'label' => 'Create table', 'object' => self::bare($m[1]), 'icon' => 'table', 'destructive' => false];
        }
        if (preg_match('/^create\s+(?:or\s+replace\s+)?(?:materialized\s+)?view\s+(?:if\s+not\s+exists\s+)?' . $ic . '/i', $s, $m)) {
            return ['action' => 'create-view', 'label' => 'Create view', 'object' => self::bare($m[1]), 'icon' => 'eye', 'destructive' => false];
        }
        if (preg_match('/^create\s+(?:unique\s+)?index\s+(?:if\s+not\s+exists\s+)?' . $ic . '/i', $s, $m)) {
            return ['action' => 'create-index', 'label' => 'Create index', 'object' => self::bare($m[1]), 'icon' => 'key', 'destructive' => false];
        }
        if (preg_match('/^alter\s+table\s+' . $ic . '/i', $s, $m)) {
            $drops = (bool) preg_match('/\bdrop\s+(?:column|constraint)\b/i', $s);
            return ['action' => 'alter', 'label' => 'Alter table', 'object' => self::bare($m[1]), 'icon' => 'pencil', 'destructive' => $drops, 'note' => $drops ? 'drops a column/constraint' : ''];
        }
        if (preg_match('/^drop\s+(table|view|index|database|schema)\s+(?:if\s+exists\s+)?' . $ic . '/i', $s, $m)) {
            return ['action' => 'drop', 'label' => 'Drop ' . strtolower($m[1]), 'object' => self::bare($m[2]), 'icon' => 'trash', 'destructive' => true];
        }
        if (preg_match('/^truncate\s+(?:table\s+)?' . $ic . '/i', $s, $m)) {
            return ['action' => 'truncate', 'label' => 'Truncate', 'object' => self::bare($m[1]), 'icon' => 'trash', 'destructive' => true, 'note' => 'empties the table'];
        }
        if (preg_match('/^delete\s+from\s+' . $ic . '/i', $s, $m)) {
            $noWhere = !preg_match('/\bwhere\b/i', $s);
            return ['action' => 'delete', 'label' => 'Delete from', 'object' => self::bare($m[1]), 'icon' => 'trash', 'destructive' => true, 'note' => $noWhere ? 'NO WHERE — deletes every row' : ''];
        }
        if (preg_match('/^update\s+' . $ic . '/i', $s, $m)) {
            $noWhere = !preg_match('/\bwhere\b/i', $s);
            return ['action' => 'update', 'label' => 'Update', 'object' => self::bare($m[1]), 'icon' => 'pencil', 'destructive' => $noWhere, 'note' => $noWhere ? 'NO WHERE — updates every row' : ''];
        }
        if (preg_match('/^insert\s+(?:ignore\s+)?into\s+' . $ic . '/i', $s, $m)) {
            $rows = self::countInsertRows($s);
            return ['action' => 'insert', 'label' => 'Insert', 'object' => self::bare($m[1]), 'icon' => 'plus', 'destructive' => false, 'rows' => $rows];
        }
        if (preg_match('/^(select|with)\b/i', $s)) {
            return ['action' => 'select', 'label' => 'Query', 'object' => '', 'icon' => 'search', 'destructive' => false];
        }
        if (preg_match('/^(show|explain|describe|desc|pragma)\b/i', $s)) {
            return ['action' => 'select', 'label' => 'Inspect', 'object' => '', 'icon' => 'search', 'destructive' => false];
        }
        return ['action' => 'other', 'label' => self::firstWord($s), 'object' => '', 'icon' => 'code', 'destructive' => false];
    }

    /** Count value tuples in an INSERT … VALUES (…),(…) — depth-aware, or null for INSERT … SELECT. */
    private static function countInsertRows(string $s): ?int
    {
        // mask strings so a ")" or "(" inside a value doesn't skew the depth count
        $masked = preg_replace("/'(?:[^'\\\\]|\\\\.|'')*'/s", "''", $s) ?? $s;
        if (!preg_match('/\bvalues\b/i', $masked)) {
            return null; // INSERT … SELECT / DEFAULT VALUES
        }
        $pos = stripos($masked, 'values');
        $tail = substr($masked, $pos + 6);
        $depth = 0; $rows = 0; $len = strlen($tail);
        for ($i = 0; $i < $len; $i++) {
            $c = $tail[$i];
            if ($c === '(') { if ($depth === 0) { $rows++; } $depth++; }
            elseif ($c === ')') { $depth = max(0, $depth - 1); }
        }
        return $rows > 0 ? $rows : null;
    }

    private static function bare(string $ident): string
    {
        $ident = trim($ident, '`"[]');
        $parts = explode('.', $ident);
        return trim(end($parts), '`"[]');
    }

    private static function firstWord(string $s): string
    {
        return preg_match('/^\s*([A-Za-z_]+)/', $s, $m) ? ucfirst(strtolower($m[1])) : 'Statement';
    }
}
