<?php

declare(strict_types=1);

namespace PpPhp\Modules\Query\Controllers;

use PpPhp\Core\Config;
use PpPhp\Core\ModuleManager;
use PpPhp\Core\DB;
use PpPhp\Core\DatabaseConnection;
use PpPhp\Core\Database\Dialect\DatabaseDialectRegistry;
use PpPhp\Core\Database\Dialect\DatabaseDialectInterface;
use PpPhp\Core\Security;
use PpPhp\Core\ProjectNav;
use PpPhp\Core\ModuleState;
use PpPhp\Core\SqlVars;

class QueryController
{
    private array $project = [];
    private string $slug = '';
    private array $databaseConfig = [];
    private ?DatabaseDialectInterface $dialect = null;

    public function __construct(
        private Config $config,
        private ModuleManager $moduleManager
    ) {}

    /**
     * Main SPA page.
     */
    public function index(string $slug): string
    {
        $this->initProject($slug);
        $project = $this->project;

        // Load Query module state from its own file (config/query.json)
        $queryState = ModuleState::load($slug, 'query');

        $tablePresets   = $queryState['byTable'] ?? [];
        $currentTableName = $queryState['activeTable'] ?? null;
        // Saved queries live as JSON files under config/queries (written by save()).
        // Read them from there so the sidebar matches what's actually on disk.
        $savedQueries   = $this->getSavedQueries();

        // Load Tables module state (selected tables)
        $tablesState = ModuleState::load($slug, 'tables');
        $selectedTableNames = $tablesState['selectedTables'] ?? [];

        $tables = [];
        if (empty($selectedTableNames)) {
            // Fallback: show all tables if none selected. Use the project's dialect
            // so this works on every engine (SHOW FULL TABLES is MySQL-only).
            try {
                if ($this->dialect !== null) {
                    $tables = $this->dialect->listObjects(DB::Pdo(), $this->databaseConfig);
                } else {
                    $rows = DB::Query('SHOW FULL TABLES')->fetchAll(\PDO::FETCH_NUM);
                    foreach ($rows as $row) {
                        $tables[] = ['name' => $row[0], 'type' => ($row[1] === 'VIEW') ? 'VIEW' : 'TABLE'];
                    }
                }
            } catch (\Throwable $e) {
                $tables = [];
            }
            usort($tables, fn($a, $b) => $a['name'] <=> $b['name']);
        } else {
            foreach ($selectedTableNames as $tableName) {
                $tables[] = ['name' => $tableName, 'type' => 'TABLE'];
            }
        }

        $navItems  = ProjectNav::items('/query');
        $baseUrl   = $this->config->get('base_url');
        $assetsUrl = $this->config->get('assets_url');
        $user      = $_SESSION['user'];
        $pageTitle = 'Query';

        ob_start();
        include __DIR__ . '/../Views/index.php';
        return ob_get_clean();
    }

    // ── Private helpers (identical pattern as TablesController) ──────

    private function initProject(string $slug): void
    {
        Security::RequireAuth();
        $this->slug = $slug;

        DB::Init(); // central DB
        $project = DB::Select('projects', ['slug' => $slug, 'created_by' => Security::CurrentUserData()['id']]);
        if (empty($project) && Security::IsAdmin()) {
            $project = DB::Select('projects', ['slug' => $slug]);
        }
        if (empty($project)) {
            http_response_code(404);
            echo 'Project not found.';
            exit;
        }
        $this->project = $project[0];
        if (\PpPhp\Core\ProjectDatabaseProfile::isStandaloneSlug($slug)) {
            $_SESSION['project_flash'] = 'Query isn’t available for standalone apps — this project has no database.';
            redirect('/project/' . rawurlencode($slug) . '/workspace');
        }
        DB::Init($slug);
        DB::SetConnection($slug);

        $configPath = \PpPhp\Core\ProjectLocator::dir($this->slug) . '/config/database.php';
        if (is_file($configPath)) {
            $this->databaseConfig = DatabaseConnection::normalize((array) require $configPath);
            $this->dialect = DatabaseDialectRegistry::forConfig($this->databaseConfig);
        }
    }

    /** Quote an identifier with the project's dialect (falls back to MySQL backticks). */
    private function quoteId(string $identifier): string
    {
        if ($this->dialect !== null) {
            return $this->dialect->quoteIdentifier($identifier);
        }
        return '`' . str_replace('`', '``', $identifier) . '`';
    }

    /** True for engines that understand `LIMIT n [OFFSET m]` (everything here except SQL Server / Oracle). */
    private function isLimitFamily(): bool
    {
        $id = $this->dialect !== null ? $this->dialect->id() : 'mysql';
        return !in_array($id, ['sqlsrv', 'oci'], true);
    }

    private function getSavedQueries(): array
    {
        $dir = $this->getQueriesDir();
        if (!is_dir($dir)) return [];
        $queries = [];
        foreach (scandir($dir) as $file) {
            if (pathinfo($file, PATHINFO_EXTENSION) === 'json') {
                $queries[] = [
                    'name' => pathinfo($file, PATHINFO_FILENAME),
                    'file' => $file
                ];
            }
        }
        return $queries;
    }

    private function getQueriesDir(): string
    {
        return \PpPhp\Core\ProjectLocator::dir($this->slug) . '/config/queries';
    }


    /**
     * Generate SQL from the designer state (JSON) and return it.
     */
    public function generateSql(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);

        $input = json_decode(file_get_contents('php://input'), true) ?? $_POST;
        $fields    = $input['fields'] ?? [];
        $tables    = $input['tables'] ?? [];
        $joins     = $input['joins'] ?? [];
        $whereTabs = $input['whereTabs'] ?? [];
        $distinct  = !empty($input['distinct']);
        $limit     = (int) ($input['limit'] ?? 0);
        if ($limit < 0) $limit = 0;

        // Simple SQL builder – we'll make it smarter later
        $select = [];
        $from   = $tables;
        $where  = [];
        $order  = [];
        $group  = [];
        $having = [];
        $aggFns = ['COUNT', 'COUNT DISTINCT', 'SUM', 'AVG', 'MIN', 'MAX'];
        // Bind an operator-only criteria ("> 100") to an expression; a full
        // expression is used verbatim. Shared by WHERE and HAVING.
        $bindCriteria = static function (string $expr, string $crit): string {
            $crit = trim($crit);
            // Symbol operators match without a trailing \b (which fails before a
            // space, e.g. "> 5"); word operators keep \b so "instock" isn't "IN".
            return preg_match('/^(?:<=|>=|<>|!=|=|<|>|LIKE\b|NOT\s+LIKE\b|IN\b|NOT\s+IN\b|BETWEEN\b|IS\b)/i', $crit)
                ? $expr . ' ' . $crit
                : $crit;
        };

        // When the query targets a single table (no joins), the `table.` prefix
        // on every field is redundant — strip it so the generated SQL reads
        // cleanly (SELECT std_id, name FROM db_aus). With joins we keep the
        // qualifier to disambiguate columns across tables.
        $singleTable = count($from) <= 1 && empty($joins);
        $qualifier   = ($singleTable && !empty($from[0])) ? $from[0] . '.' : '';
        $shorten = static function (string $field) use ($qualifier): string {
            if ($qualifier !== '' && str_starts_with($field, $qualifier)) {
                return substr($field, strlen($qualifier));
            }
            return $field;
        };

        foreach ($fields as $f) {
            if (empty($f['output'])) continue;
            $field = $shorten((string) $f['field']);
            $gb    = strtoupper(trim((string) ($f['groupBy'] ?? '')));
            $isAgg = in_array($gb, $aggFns, true);

            // Build the output expression: aggregate-wrapped or the plain field.
            if ($isAgg) {
                $exprCore = $gb === 'COUNT DISTINCT' ? "COUNT(DISTINCT $field)" : "$gb($field)";
            } else {
                $exprCore = $field;
            }
            $expr = $exprCore;
            if (!empty($f['alias'])) {
                $expr .= ' AS ' . $f['alias'];
            }
            $select[] = $expr;

            // Criteria on an aggregate filters groups (HAVING); otherwise rows (WHERE).
            if (!empty($f['criteria'])) {
                if ($isAgg) {
                    $having[] = $bindCriteria($exprCore, (string) $f['criteria']);
                } else {
                    $where[] = $bindCriteria($field, (string) $f['criteria']);
                }
            }
            if (!empty($f['sortType'])) {
                $dir = ($f['sortType'] === 'Descending') ? 'DESC' : 'ASC';
                // Sort an aggregate by its alias when present, else the expression.
                $sortExpr = $isAgg ? (!empty($f['alias']) ? $f['alias'] : $exprCore) : $field;
                $order[] = $sortExpr . ' ' . $dir;
            }
            if (!$isAgg && ($gb === 'GROUP BY' || str_starts_with($gb, 'GROUP BY'))) {
                $group[] = $field;
            }
        }

        if (empty($from) || empty($from[0])) {
            echo json_encode(['success' => true, 'sql' => '']);
            exit;
        }

        // Build join clauses (identifiers quoted with the project's dialect).
        $joinClauses = [];
        foreach ($joins as $j) {
            $type = strtoupper(trim((string) ($j['type'] ?? 'INNER')));
            $rt = $this->quoteId((string) $j['rightTable']);
            $lf = $this->quoteId((string) $j['leftTable']) . '.' . $this->quoteId((string) $j['leftField']);
            $rf = $this->quoteId((string) $j['rightTable']) . '.' . $this->quoteId((string) $j['rightField']);
            $joinClauses[] = "{$type} JOIN {$rt} ON {$lf} = {$rf}";
        }

        $sql = 'SELECT ' . ($distinct ? 'DISTINCT ' : '') . (empty($select) ? '*' : implode(', ', $select))
            . ' FROM ' . $this->quoteId((string) $from[0]);
        if (!empty($joinClauses)) {
            $sql .= ' ' . implode(' ', $joinClauses);
        }
        if (!empty($where)) {
            $sql .= ' WHERE ' . implode(' AND ', $where);
        }
        if (!empty($group)) {
            $sql .= ' GROUP BY ' . implode(', ', $group);
        }
        if (!empty($having)) {
            $sql .= ' HAVING ' . implode(' AND ', $having);
        }
        if (!empty($order)) {
            $sql .= ' ORDER BY ' . implode(', ', $order);
        }
        if ($limit > 0 && $this->isLimitFamily()) {
            // SQL Server / Oracle don't use LIMIT; skip it there (the preview cap
            // and pagination still bound the result on those engines).
            $sql .= ' LIMIT ' . $limit;
        }

        echo json_encode(['success' => true, 'sql' => $sql]);
        exit;
    }

    /**
     * Execute the current query and return results.
     */
    public function execute(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);

        $input = json_decode(file_get_contents('php://input'), true) ?: [];
        $sql = trim((string) ($_POST['sql'] ?? $input['sql'] ?? ''));
        if ($sql === '') {
            http_response_code(400);
            echo json_encode(['success' => false, 'message' => 'SQL is required.']);
            exit;
        }

        // Read-only guard: the Query builder only previews SELECT results. Reject
        // anything that could mutate data or write a file, so a stray DELETE /
        // UPDATE / DROP in the editor can never run (it also auto-executes).
        if (!$this->isReadOnlyQuery($sql)) {
            http_response_code(400);
            echo json_encode(['success' => false, 'message' => 'Only a read-only SELECT query can be run here.']);
            exit;
        }

        $cap = 1000; // hard preview cap — never load an unbounded result set into memory
        $page    = max(1, (int) ($_POST['page'] ?? $input['page'] ?? 1));
        $perPage = (int) ($_POST['perPage'] ?? $input['perPage'] ?? 0);
        // perPage > 0 turns on paging by appending LIMIT/OFFSET to the query. Only
        // do this when it's safe and portable: a plain SELECT/WITH head (SHOW /
        // DESCRIBE / EXPLAIN can't take LIMIT), no LIMIT already present (don't
        // clobber the author's own), and a LIMIT-family engine. Otherwise we fall
        // back to the safety cap (perPage 0 = the cap).
        $bare = trim((string) preg_replace('#^\s*(?:--[^\n]*\n|/\*.*?\*/|\s)+#s', '', $sql));
        $canAppendLimit = (bool) preg_match('/^(?:SELECT|WITH)\b/i', $bare)
            && !preg_match('/\bLIMIT\s+\d/i', $sql)
            && $this->isLimitFamily();
        $paginated = $perPage > 0 && $canAppendLimit;
        $runSql = $sql;
        if ($paginated) {
            $perPage = min($perPage, $cap);
            $offset  = ($page - 1) * $perPage;
            // +1 row is a lookahead so we can tell the client whether a next page exists.
            $runSql  = rtrim($sql, "; \t\n\r") . ' LIMIT ' . ($perPage + 1) . ' OFFSET ' . $offset;
        }

        try {
            $start = microtime(true);
            $stmt = DB::Pdo()->query($runSql);
            $keep = $paginated ? $perPage : $cap;
            $rows = [];
            if ($stmt) {
                while (count($rows) < $keep && ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) !== false) {
                    $rows[] = $row;
                }
            }
            $hasMore = $stmt ? ($stmt->fetch(\PDO::FETCH_ASSOC) !== false) : false;
            $time = round(microtime(true) - $start, 4);
            $columns = !empty($rows) ? array_keys($rows[0]) : [];
            echo json_encode([
                'success'   => true,
                'columns'   => $columns,
                'rows'      => $rows,
                'time'      => $time,
                'count'     => count($rows),
                'truncated' => !$paginated && $hasMore,   // hit the safety cap
                'hasMore'   => $hasMore,                   // another page exists
                'page'      => $paginated ? $page : 1,
                'perPage'   => $paginated ? $perPage : 0,
            ]);
        } catch (\Throwable $e) {
            http_response_code(500);
            echo json_encode(['success' => false, 'message' => $e->getMessage()]);
        }
        exit;
    }

    /**
     * True only for a single read-only query (SELECT/WITH/SHOW/EXPLAIN/DESCRIBE)
     * that doesn't write a file. Guards the Results preview + auto-execute.
     */
    private function isReadOnlyQuery(string $sql): bool
    {
        // Reject stacked statements ("SELECT …; DROP …") — a semicolon that is not
        // inside a string/identifier literal and is followed by more non-blank SQL
        // means a second statement, which the read-only preview must never run.
        if ($this->hasStackedStatement($sql)) return false;

        // Strip a leading run of comments/whitespace, then inspect the first verb.
        $s = trim((string) preg_replace('#^\s*(?:--[^\n]*\n|/\*.*?\*/|\s)+#s', '', $sql));
        if (preg_match('/\bINTO\s+(?:OUT|DUMP)FILE\b/i', $s)) return false;
        return (bool) preg_match('/^(?:SELECT|WITH|SHOW|EXPLAIN|DESCRIBE|DESC)\b/i', $s);
    }

    /**
     * True if the SQL contains a statement-terminating semicolon followed by more
     * SQL. Quoted regions ('…', "…", `…`) and line/block comments are skipped so a
     * semicolon inside a string literal doesn't trip the guard.
     */
    private function hasStackedStatement(string $sql): bool
    {
        $len = strlen($sql);
        $i = 0;
        while ($i < $len) {
            $ch = $sql[$i];
            if ($ch === "'" || $ch === '"' || $ch === '`') {
                $quote = $ch;
                $i++;
                while ($i < $len) {
                    if ($sql[$i] === '\\' && $quote !== '`') { $i += 2; continue; } // escaped char
                    if ($sql[$i] === $quote) {
                        // doubled quote = literal quote, stay in string
                        if ($i + 1 < $len && $sql[$i + 1] === $quote) { $i += 2; continue; }
                        $i++; break;
                    }
                    $i++;
                }
                continue;
            }
            if ($ch === '-' && $i + 1 < $len && $sql[$i + 1] === '-') {
                while ($i < $len && $sql[$i] !== "\n") $i++;
                continue;
            }
            if ($ch === '/' && $i + 1 < $len && $sql[$i + 1] === '*') {
                $i += 2;
                while ($i + 1 < $len && !($sql[$i] === '*' && $sql[$i + 1] === '/')) $i++;
                $i += 2;
                continue;
            }
            if ($ch === ';') {
                // Anything other than trailing whitespace/comments after this = a 2nd statement.
                $rest = trim((string) preg_replace('#(?:--[^\n]*\n|/\*.*?\*/|\s)+#s', '', substr($sql, $i + 1)));
                if ($rest !== '') return true;
            }
            $i++;
        }
        return false;
    }

    /**
     * Persist the current query as a real database VIEW (CREATE [OR REPLACE] VIEW).
     * Reuses the project's dialect so it works across every supported engine.
     */
    public function saveView(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);

        $input = json_decode(file_get_contents('php://input'), true) ?: $_POST;
        $name = trim((string) ($input['name'] ?? ''));
        $sql  = trim((string) ($input['sql'] ?? ''));
        $replace = !empty($input['replace']);

        if ($name === '' || !preg_match('/^[A-Za-z_][A-Za-z0-9_]*$/', $name)) {
            http_response_code(400);
            echo json_encode(['success' => false, 'message' => 'A valid view name (letters, numbers, underscore) is required.']);
            exit;
        }
        if ($sql === '' || !$this->isReadOnlyQuery($sql)) {
            http_response_code(400);
            echo json_encode(['success' => false, 'message' => 'A single SELECT statement is required to create a view.']);
            exit;
        }
        if ($this->dialect === null) {
            http_response_code(500);
            echo json_encode(['success' => false, 'message' => 'The project database dialect is unavailable.']);
            exit;
        }

        try {
            $this->dialect->createView(DB::Pdo(), $name, rtrim($sql, "; \t\n\r"), $replace, $this->databaseConfig);
            echo json_encode(['success' => true, 'message' => "View “{$name}” created. Add it to your app from the Tables module.", 'view' => $name]);
        } catch (\Throwable $e) {
            http_response_code(500);
            echo json_encode(['success' => false, 'message' => 'Could not create the view: ' . $e->getMessage()]);
        }
        exit;
    }


    /**
     * Save the current query state to a JSON file.
     */
    public function save(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);

        $input = json_decode(file_get_contents('php://input'), true) ?? $_POST;
        $name = trim($input['name'] ?? '');
        if ($name === '') {
            $name = 'Untitled';
        }

        $data = [
            'name'             => $name,
            'fields'           => $input['fields'] ?? [],
            'tables'           => $input['tables'] ?? [],
            'joins'            => $input['joins'] ?? [],
            'sql'              => $input['sql'] ?? '',
            'whereTabs'        => $input['whereTabs'] ?? [],
            'whereTabsShowAll' => $input['whereTabsShowAll'] ?? true,
            'distinct'         => !empty($input['distinct']),
            'limit'            => (int) ($input['limit'] ?? 0),
            'positions'        => $input['positions'] ?? [],
            'savedAt'          => date('Y-m-d H:i:s'),
        ];

        $dir = $this->getQueriesDir();
        if (!is_dir($dir)) {
            mkdir($dir, 0755, true);
        }
        $file = $dir . '/' . preg_replace('/[^a-zA-Z0-9_-]/', '', $name) . '.json';
        file_put_contents($file, json_encode($data, JSON_PRETTY_PRINT));

        echo json_encode(['success' => true, 'message' => 'Query saved.', 'file' => basename($file)]);
        exit;
    }


    /**
     * Save state for a single table (used by auto-save).
     */
    public function saveState(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);

        $input = json_decode(file_get_contents('php://input'), true) ?? $_POST;
        $tableName = $input['tableName'] ?? '';
        $state = $input['state'] ?? [];

        if ($tableName === '' || empty($state)) {
            http_response_code(400);
            echo json_encode(['success' => false, 'message' => 'Table name and state are required.']);
            exit;
        }

        // Load existing query state
        $queryState = ModuleState::load($slug, 'query');
        if (!is_array($queryState) || array_is_list($queryState)) {
            $queryState = [];
        }
        if (!isset($queryState['byTable'])) {
            $queryState['byTable'] = [];
        }

        // Merge this table's state
        $queryState['byTable'][$tableName] = $state;
        $queryState['activeTable'] = $tableName;

        ModuleState::save($slug, 'query', $queryState);

        echo json_encode(['success' => true]);
        exit;
    }



    /**
     * Load a saved query and return its full state as JSON.
     */
    public function load(string $slug, string $name): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);

        $name = preg_replace('/[^a-zA-Z0-9_-]/', '', $name);
        $file = $this->getQueriesDir() . '/' . $name . '.json';

        if (!file_exists($file)) {
            http_response_code(404);
            echo json_encode(['success' => false, 'message' => 'Query not found.']);
            exit;
        }

        $data = json_decode(file_get_contents($file), true);
        echo json_encode(['success' => true, 'query' => $data]);
        exit;
    }


    /**
     * Delete a saved query file.
     */
    public function delete(string $slug, string $name): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);

        $name = preg_replace('/[^a-zA-Z0-9_-]/', '', $name);
        $file = $this->getQueriesDir() . '/' . $name . '.json';

        if (!file_exists($file)) {
            http_response_code(404);
            echo json_encode(['success' => false, 'message' => 'Query not found.']);
            exit;
        }

        unlink($file);
        echo json_encode(['success' => true, 'message' => 'Query deleted.']);
        exit;
    }


    // whereTabs are additional saved WHERE clauses that users can quickly apply to the current query. They are stored as part of the query JSON but can be managed separately in the UI.
    public function previewWhere(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);

        $input = json_decode(file_get_contents('php://input'), true) ?? $_POST;
        $clause = trim($input['clause'] ?? '');
        $baseSql = trim($input['baseSql'] ?? '');

        if ($clause === '') {
            http_response_code(400);
            echo json_encode(['success' => false, 'message' => 'WHERE clause is required.']);
            exit;
        }

        // Session variables (:userid, :session.x, :user.field) resolve against the
        // current session so the preview count reflects what a real user would see.
        if (SqlVars::has($clause)) {
            $clause = SqlVars::expand($clause);
        }

        // Build a COUNT query from the base SQL + additional WHERE
        $sql = $baseSql ? $baseSql : 'SELECT 1';
        // Add the WHERE clause
        if (stripos($sql, 'WHERE') !== false) {
            $sql = preg_replace('/WHERE/i', "WHERE ({$clause}) AND ", $sql, 1);
        } elseif (stripos($sql, 'GROUP BY') !== false) {
            $sql = preg_replace('/GROUP BY/i', "WHERE ({$clause}) GROUP BY ", $sql, 1);
        } else {
            $sql .= " WHERE ({$clause})";
        }

        $countSql = "SELECT COUNT(*) FROM ($sql) AS subquery";

        try {
            $count = DB::FetchOne($countSql);
            echo json_encode(['success' => true, 'count' => (int)$count]);
        } catch (\PDOException $e) {
            http_response_code(500);
            echo json_encode(['success' => false, 'message' => $e->getMessage()]);
        }
        exit;
    }


    /**
     * Return the distinct values of a field (capped) for the "Auto tabs from a
     * field" preview in the Additional WHERE editor. Read-only.
     */
    public function fieldValues(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);

        $input = json_decode(file_get_contents('php://input'), true) ?? $_POST;
        $table = trim((string) ($input['table'] ?? ''));
        $field = trim((string) ($input['field'] ?? ''));
        $ident = '/^[A-Za-z_][A-Za-z0-9_]*$/';
        if ($table === '' || $field === '' || !preg_match($ident, $table) || !preg_match($ident, $field)) {
            http_response_code(400);
            echo json_encode(['success' => false, 'message' => 'A valid table and field are required.']);
            exit;
        }

        try {
            $qf = $this->quoteId($field);
            $qt = $this->quoteId($table);
            $limitClause = $this->isLimitFamily() ? ' LIMIT 60' : '';
            $stmt = DB::Pdo()->query("SELECT DISTINCT $qf AS v FROM $qt ORDER BY $qf$limitClause");
            $raw = $stmt ? $stmt->fetchAll(\PDO::FETCH_COLUMN) : [];
            if (!$this->isLimitFamily()) $raw = array_slice($raw, 0, 60);
            $values = array_map(
                static fn($v): string => ($v === null || $v === '') ? '(empty)' : (string) $v,
                $raw
            );
            echo json_encode(['success' => true, 'values' => $values, 'count' => count($values), 'capped' => count($values) >= 60]);
        } catch (\Throwable $e) {
            http_response_code(500);
            echo json_encode(['success' => false, 'message' => $e->getMessage()]);
        }
        exit;
    }

    /**
     * Parse raw SQL back into a structured query object (tables, fields, joins, etc.).
     */
    public function parseSql(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);

        $sql = trim($_POST['sql'] ?? json_decode(file_get_contents('php://input'), true)['sql'] ?? '');
        if ($sql === '') {
            http_response_code(400);
            echo json_encode(['success' => false, 'message' => 'SQL is required.']);
            exit;
        }

        try {
            $parsed = $this->parseSqlToStruct($sql);
            echo json_encode(['success' => true, 'query' => $parsed]);
        } catch (\Exception $e) {
            http_response_code(400);
            echo json_encode(['success' => false, 'message' => $e->getMessage()]);
        }
        exit;
    }

    /**
     * A minimal SQL parser that extracts SELECT, FROM, JOIN, WHERE, GROUP BY, ORDER BY.
     */
    private function parseSqlToStruct(string $sql): array
    {
        $sql = preg_replace('/\s+/', ' ', $sql);
        $upper = strtoupper($sql);

        $fields = [];
        $tables = [];
        $joins  = [];
        $where  = '';
        $group  = [];
        $order  = [];

        // Extract SELECT fields (simplistic – handles "SELECT a, b AS c")
        if (preg_match('/SELECT\s+(.*?)\s+FROM\s+/is', $sql, $m)) {
            $fieldList = explode(',', $m[1]);
            foreach ($fieldList as $f) {
                $f = trim($f);
                if ($f === '*') continue; // skip wildcard; we'll get fields from tables later
                $alias = '';
                if (preg_match('/(.*)\s+AS\s+(\w+)/i', $f, $aliasMatch)) {
                    $f = trim($aliasMatch[1]);
                    $alias = $aliasMatch[2];
                }
                $fields[] = [
                    'field'    => $f,
                    'alias'    => $alias,
                    'output'   => true,
                    'sortType' => '',
                    'sortOrder' => '',
                    'groupBy'  => '',
                    'criteria' => '',
                ];
            }
        }

        // FROM and JOINs
        if (preg_match('/FROM\s+`?(\w+)`?\s*(.*)/is', $sql, $fromMatch)) {
            $tables[] = $fromMatch[1];
            $rest = $fromMatch[2];

            // Crude JOIN extraction
            if (preg_match_all('/(INNER|LEFT|RIGHT|FULL)?\s*JOIN\s+`?(\w+)`?\s+ON\s+`?(\w+)`?\.`?(\w+)`?\s*=\s*`?(\w+)`?\.`?(\w+)`?/i', $rest, $joinMatches, PREG_SET_ORDER)) {
                foreach ($joinMatches as $j) {
                    $joinType = strtoupper($j[1] ?: 'INNER');
                    $rightTable = $j[2];
                    $leftTable  = $j[3];
                    $leftField  = $j[4];
                    $rightField = $j[6];
                    if ($leftTable === $tables[0]) {
                        $joins[] = [
                            'id'         => 'j_' . uniqid(),
                            'type'       => $joinType,
                            'leftTable'  => $leftTable,
                            'leftField'  => $leftField,
                            'rightTable' => $rightTable,
                            'rightField' => $rightField,
                        ];
                    }
                    if (!in_array($rightTable, $tables)) {
                        $tables[] = $rightTable;
                    }
                }
            }
        }

        // WHERE
        if (preg_match('/WHERE\s+(.*?)(GROUP BY|ORDER BY|$)/is', $sql, $w)) {
            $where = trim($w[1]);
        }

        // GROUP BY
        if (preg_match('/GROUP BY\s+(.*?)(ORDER BY|$)/is', $sql, $g)) {
            $group = array_map('trim', explode(',', $g[1]));
        }

        // ORDER BY
        if (preg_match('/ORDER BY\s+(.*?)$/is', $sql, $o)) {
            $orderList = array_map('trim', explode(',', $o[1]));
            foreach ($orderList as $item) {
                $parts = explode(' ', $item);
                $fieldName = $parts[0];
                $dir = strtoupper($parts[1] ?? 'ASC');
                // Update existing field entry if exists
                $found = false;
                foreach ($fields as &$f) {
                    if ($f['field'] === $fieldName) {
                        $f['sortType'] = ($dir === 'DESC') ? 'Descending' : 'Ascending';
                        $f['sortOrder'] = '1';
                        $found = true;
                        break;
                    }
                }
                if (!$found) {
                    $fields[] = [
                        'field'    => $fieldName,
                        'alias'    => '',
                        'output'   => true,
                        'sortType' => ($dir === 'DESC') ? 'Descending' : 'Ascending',
                        'sortOrder' => '1',
                        'groupBy'  => '',
                        'criteria' => '',
                    ];
                }
            }
        }

        // If WHERE clause extracted, attach to the first field with criteria empty (simplistic)
        if ($where !== '' && !empty($fields)) {
            $fields[0]['criteria'] = $where;
        }

        // Re-qualify bare column names to the single source table so the visual
        // designer (which keys everything on `table.field`) round-trips cleanly
        // even when the SQL was written with clean, unqualified names. Only plain
        // identifiers are qualified — expressions like COUNT(*) are left alone.
        if (count($tables) === 1) {
            $sourceTable = $tables[0];
            foreach ($fields as &$field) {
                if (is_string($field['field']) && preg_match('/^[A-Za-z_][A-Za-z0-9_]*$/', $field['field'])) {
                    $field['field'] = $sourceTable . '.' . $field['field'];
                }
            }
            unset($field);
        }

        return [
            'tables'    => $tables,
            'fields'    => $fields,
            'joins'     => $joins,
            'sql'       => $sql,
            'positions' => [],
            'whereTabs' => [],
        ];
    }
}
