<?php

declare(strict_types=1);

namespace PpPhp\Modules\Projects\Controllers;

use PpPhp\Core\Config;
use PpPhp\Core\ModuleManager;
use PpPhp\Core\DB;
use PpPhp\Core\Security;
use PpPhp\Core\ModuleState;
use PpPhp\Core\DatabaseConnection;
use PpPhp\Core\ProjectDatabaseProfile;
use PpPhp\Core\Projects\SqlScriptService;
use PpPhp\Core\ProjectCatalog;
use PpPhp\Core\Projects\ProjectCloneService;
use PpPhp\Core\Projects\ProjectPackageExporter;
use PpPhp\Core\Projects\ProjectPackageFormat;
use PpPhp\Core\Projects\ProjectPackageImporter;
use PpPhp\Core\Projects\ProjectPackageInspector;
use PpPhp\Core\Projects\ProjectTemplateCatalog;
use PpPhp\Core\Projects\ProjectTemplateService;
use PpPhp\Core\Projects\ProjectSchemaBlueprintService;
use PpPhp\Core\Projects\ProjectVersionCatalog;
use PpPhp\Core\Projects\ProjectVersionService;
use PpPhp\Core\Projects\ProjectGitService;
use PpPhp\Core\Projects\ProjectSchemaDriftService;
use PpPhp\Core\Databases\VirtualDbService;

class ProjectController
{
    private string $projectsBasePath;
    private ModuleManager $moduleManager;

    public function __construct(
        private Config $config,
        ModuleManager $moduleManager
    ) {
        $this->moduleManager = $moduleManager;
        $this->projectsBasePath = constant('BASE_PATH') . '/projects/' . Security::CurrentUsername();
    }

    /**
     * List all projects for the current user.
     */
    public function list(): string
    {
        Security::RequireAuth();

        DB::Init(); // central DB
        $userId   = Security::CurrentUserData()['id'];
        // Administrators (superadmin + platform owner) see every project; everyone
        // else sees only their own. Each project resolves to its real owner's
        // folder on disk, so admins can open and edit any of them.
        $isAdminViewer = Security::IsAdmin();
        $projects = $isAdminViewer
            ? DB::Select('projects', [], 'created_at DESC')
            : DB::Select('projects', ['created_by' => $userId], 'created_at DESC');
        $projects = is_array($projects) ? $projects : [];
        $catalog = ProjectCatalog::metadataFor(array_column($projects, 'id'));

        // Map creator ids → usernames so admins can see whose project each one is.
        $ownerNames = [];
        if ($isAdminViewer) {
            $ids = array_values(array_unique(array_map(static fn($p) => (int) $p['created_by'], $projects)));
            foreach ($ids as $id) {
                $rows = DB::Select('users', ['id' => $id]);
                $ownerNames[$id] = (string) ($rows[0]['username'] ?? 'unknown');
            }
        }

        $databaseTypes = DatabaseConnection::definitions();
        foreach ($projects as &$project) {
            $ownerName = $isAdminViewer
                ? ($ownerNames[(int) $project['created_by']] ?? 'unknown')
                : Security::CurrentUsername();
            $project['owner_username'] = $ownerName;
            $project['owned_by_me'] = (int) $project['created_by'] === (int) $userId;
            $projectDir = constant('BASE_PATH') . '/projects/' . $ownerName . '/' . $project['slug'];
            $meta = $catalog[(int)$project['id']] ?? ProjectCatalog::defaults((int)$project['id']);
            $project = array_merge($project, $meta);
            $project['db_type'] = 'mysql';
            $project['db_label'] = 'MySQL';
            $project['db_available'] = true;
            $project['connection_attention'] = false;
            $configPath = $projectDir . '/config/database.php';
            // Standalone (database-free) project: no DB profile by design.
            $project['standalone'] = \PpPhp\Core\ProjectDatabaseProfile::isStandalone($projectDir);
            if ($project['standalone']) {
                $project['db_type'] = 'standalone';
                $project['db_label'] = 'Standalone';
            } elseif (is_file($configPath)) {
                try {
                    $connection = DatabaseConnection::normalize((array)require $configPath);
                    $project['db_type'] = $connection['type'];
                    $project['db_label'] = DatabaseConnection::label($connection['type']);
                    $project['db_available'] = !empty($databaseTypes[$connection['type']]['available']);
                } catch (\Throwable) {
                    $project['connection_attention'] = true;
                }
            } else {
                $project['connection_attention'] = true;
            }

            $tablesState = $this->readJsonFile($projectDir . '/config/tables.json');
            $project['table_count'] = count((array)($tablesState['selectedTables'] ?? []));
            $hasDesign = is_file($projectDir . '/config/designer.json') && filesize($projectDir . '/config/designer.json') > 20;
            if ($project['standalone']) {
                // Readiness for a standalone app = has it been designed yet.
                $project['readiness'] = $hasDesign ? 100 : 50;
                $project['health'] = $hasDesign ? 'ready' : 'setup';
            } else {
                $milestones = [
                    is_file($configPath),
                    $project['table_count'] > 0,
                    is_file($projectDir . '/config/pages.json') && filesize($projectDir . '/config/pages.json') > 4,
                    $hasDesign,
                ];
                $project['readiness'] = (int)round((count(array_filter($milestones)) / count($milestones)) * 100);
                $project['health'] = !$project['db_available'] || $project['connection_attention']
                    ? 'attention'
                    : ($project['table_count'] > 0 ? 'ready' : 'setup');
            }
            $project['health_label'] = match ($project['health']) {
                'ready' => 'Ready',
                'attention' => 'Needs attention',
                default => 'Setup',
            };
            $project['activity_at'] = $project['last_opened_at'] ?: ($project['updated_at'] ?? $project['created_at']);
        }
        unset($project);

        usort($projects, static function (array $left, array $right): int {
            if ((bool)$left['archived_at'] !== (bool)$right['archived_at']) return $left['archived_at'] ? 1 : -1;
            if ((bool)$left['is_pinned'] !== (bool)$right['is_pinned']) return $left['is_pinned'] ? -1 : 1;
            return strcmp((string)$right['activity_at'], (string)$left['activity_at']);
        });

        $projectCounts = [
            'total' => count($projects),
            'active' => count(array_filter($projects, static fn(array $p): bool => empty($p['archived_at']))),
            'archived' => count(array_filter($projects, static fn(array $p): bool => !empty($p['archived_at']))),
            'pinned' => count(array_filter($projects, static fn(array $p): bool => !empty($p['is_pinned']) && empty($p['archived_at']))),
            'ready' => count(array_filter($projects, static fn(array $p): bool => $p['health'] === 'ready' && empty($p['archived_at']))),
        ];
        $databaseLabels = array_values(array_unique(array_column($projects, 'db_label')));
        sort($databaseLabels, SORT_NATURAL | SORT_FLAG_CASE);
        $projectTemplates = ProjectTemplateCatalog::forOwner((int)$userId);
        foreach ($projectTemplates as &$template) {
            $template['available'] = is_file((string)$template['package_path']);
            $template['size'] = $template['available'] ? (int)filesize((string)$template['package_path']) : 0;
            $template['content'] = (array)($template['manifest']['content'] ?? []);
            $template['database'] = (array)($template['manifest']['database'] ?? []);
        }
        unset($template);
        // Built-in curated templates ship in the codebase (survive migration) and
        // are offered first; each carries an audience (role tiers) the owner can set,
        // so e.g. the data-bearing "Full Restore" stays owner-only.
        $viewerTier = $this->currentRoleTier();
        $templateViewerIsOwner = ($viewerTier === 'owner');
        $templatesOpenToUsers = $this->templatesOpenToUsers();
        // CLIENT installs: pull the signed catalogue from the authority (licence-checked)
        // before listing; authorities use their own codebase templates (returns null).
        $templateFeedStatus = $this->refreshRemoteTemplates($viewerTier);
        // A built-in is visible when the viewer's ROLE tier is in its audience, OR the
        // viewer is INDIVIDUALLY granted it (which bypasses the group gate entirely) —
        // so the owner can hand a template to one person without opening a whole tier.
        $viewerUserId = (int) (Security::CurrentUserData()['id'] ?? 0);
        $builtins = array_values(array_filter(
            \PpPhp\Core\Projects\BuiltinTemplates::all(),
            static fn(array $t): bool => \PpPhp\Core\Projects\BuiltinTemplates::canUse($t, $viewerTier, $viewerUserId, $templatesOpenToUsers)
        ));
        $projectTemplates = array_merge($builtins, $projectTemplates);
        $templateCategories = ['All', 'Business', 'General', 'CRM', 'Operations', 'Commerce', 'Content', 'Analytics', 'Custom'];

        $rawMenu = $this->moduleManager->collectMenu();
        $menu    = $this->filterMenuByAuth($rawMenu, Security::IsLoggedIn());

        $baseUrl   = $this->config->get('base_url');
        $assetsUrl = $this->config->get('assets_url');
        $user      = Security::CurrentUserData();
        $userRole  = Security::CurrentUserGroup();
        $projectColors = ProjectCatalog::COLORS;
        $projectIcons = ProjectCatalog::ICONS;
        $projectPackageLimit = ProjectPackageFormat::formatBytes(ProjectPackageFormat::MAX_ARCHIVE_BYTES);
        ob_start();
        include __DIR__ . '/../Views/list.php';
        return ob_get_clean();
    }

    /**
     * Show the create form – for now, just the list page with modal open.
     */
    public function createForm(): string
    {
        return $this->list();
    }

    /**
     * Process project creation.
     */
    public function create(): void
    {
        Security::RequireAuth();
        Security::RequireRole(['superadmin', 'developer']);

        if (!Security::CSRF()->Check((string)($_POST['_token'] ?? ''))) {
            $_SESSION['project_error'] = 'Your session security token expired. Refresh the Projects page and try again.';
            redirect('/projects');
        }

        $name        = $_POST['name'] ?? '';
        $slug        = $_POST['slug'] ?? '';
        $description = $_POST['description'] ?? '';
        $name        = trim((string)$name);
        $slug        = strtolower(trim((string)$slug));
        $description = trim((string)$description);

        if (empty($name) || empty($slug)) {
            $_SESSION['project_error'] = 'Name and slug are required.';
            redirect('/projects');
        }
        if (!preg_match('/^[a-z0-9]+(?:-[a-z0-9]+)*$/', $slug)) {
            $_SESSION['project_error'] = 'The project slug may contain lowercase letters, numbers, and single hyphens only.';
            redirect('/projects');
        }

        DB::Init(); // central DB
        // Check slug uniqueness
        $existing = DB::Select('projects', ['slug' => $slug]);
        if (!empty($existing)) {
            $_SESSION['project_error'] = 'A project with this slug already exists.';
            redirect('/projects');
        }

        $projectDir = $this->projectsBasePath . '/' . $slug;
        $createdProjectDir = false;

        // ── Standalone project (no database) ──────────────────────────────────
        // Build entirely from Designer standalone/common pages. We create the
        // project dir but write NO config/database.php — that absence is the
        // single signal every DB-only feature checks (ProjectDatabaseProfile::
        // isStandalone). Tables/Query/Pages/Fields/Security stay locked.
        if (!empty($_POST['standalone']) || (string)($_POST['app_type'] ?? '') === 'standalone') {
            $creatorId = (int) (Security::CurrentUserData()['id'] ?? 0);
            if ($creatorId < 1) {
                $_SESSION['project_error'] = 'Your session is no longer valid. Please sign in again and retry.';
                redirect('/projects');
            }
            if (!is_dir($projectDir) && !mkdir($projectDir, 0755, true)) {
                $_SESSION['project_error'] = 'Could not create project directory.';
                redirect('/projects');
            }
            $inserted = DB::Insert('projects', [
                'name'        => $name,
                'slug'        => $slug,
                'description' => $description,
                'db_host'     => '',
                'db_port'     => 0,
                'db_name'     => '',
                'db_user'     => '',
                'db_pass'     => '',
                'created_by'  => $creatorId,
            ]);
            if ($inserted === false) {
                if (is_dir($projectDir)) $this->deleteDirectory($projectDir);
                $_SESSION['project_error'] = 'The project record could not be created: ' . DB::LastError();
                redirect('/projects');
            }
            // Seed a default landing page ("Home", custom_home) so the project opens
            // ready to design and the built app has an index to serve at "/".
            $this->seedStandaloneHomePage($projectDir, $name);
            $_SESSION['project_success'] = 'Standalone project created — open it to start designing your Home page.';
            redirect('/projects');
        }

        try {
            if (!empty($_POST['use_virtual_db'])) {
                // "Virtual Database": bind the project to a database from THIS user's
                // Virtual Database pool (storage/vdb/<name>.sqlite) so the project and
                // Data Studio share one real, quota-governed database. Step 3 of the
                // modal picks an existing one (vdb_id) or names a new one to create
                // (vdb_new_label); with neither, we auto-create one named after the
                // project — respecting the plan/owner quota.
                $vdbPath = $this->resolveVirtualDbPath($name);   // portable vdb://<name> ref or throws
                $connection = DatabaseConnection::normalize(['type' => 'sqlite', 'path' => $vdbPath]);
            } else {
                $connection = DatabaseConnection::normalize($_POST);
            }
            DatabaseConnection::validate($connection, $projectDir);

            // SQLite is allowed to create a new project-local database file,
            // so its directory must exist before the connection test.
            if (!is_dir($projectDir)) {
                if (!mkdir($projectDir, 0755, true)) {
                    throw new \RuntimeException('Could not create project directory.');
                }
                $createdProjectDir = true;
            }

            $test = DatabaseConnection::test($connection, $projectDir);
            if (!$test['success']) {
                throw new \RuntimeException('Project database connection failed: ' . $test['message']);
            }
            $connection['driver'] = $test['driver'] ?: $connection['driver'];

            // Virtual/SQLite: the database file must never be web-downloadable —
            // drop a deny-all .htaccess next to it.
            if (($connection['type'] ?? '') === 'sqlite' && ($connection['path'] ?? '') !== ':memory:') {
                \PpPhp\Core\Databases\VirtualDb::protectDir(
                    dirname(DatabaseConnection::resolveSqlitePath((string) $connection['path'], $projectDir))
                );
            }
        } catch (\Throwable $e) {
            if ($createdProjectDir && is_dir($projectDir)) {
                $this->deleteDirectory($projectDir);
            }
            $_SESSION['project_error'] = $e->getMessage();
            redirect('/projects');
        }

        // Write the project-specific profile atomically.
        try {
            ProjectDatabaseProfile::writeAtomic($projectDir, $connection);
        } catch (\Throwable $e) {
            if ($createdProjectDir) {
                $this->deleteDirectory($projectDir);
            }
            $_SESSION['project_error'] = $e->getMessage();
            redirect('/projects');
        }

        // The signed-in account must still exist — projects.created_by is a FK to
        // users(id). Security::IsLoggedIn() drops sessions whose account is gone,
        // so this only trips in odd edge cases; it turns what would be a raw
        // SQLSTATE[23000] dump into a clear "sign in again".
        $creatorId = (int) (Security::CurrentUserData()['id'] ?? 0);
        if ($creatorId < 1) {
            if ($createdProjectDir) {
                $this->deleteDirectory($projectDir);
            }
            $_SESSION['project_error'] = 'Your session is no longer valid. Please sign in again and retry.';
            redirect('/projects');
        }

        // Insert into central projects table
        $inserted = DB::Insert('projects', [
            'name'        => $name,
            'slug'        => $slug,
            'description' => $description,
            'db_host'     => $connection['host'],
            'db_port'     => $connection['port'] !== '' ? (int)$connection['port'] : 0,
            'db_name'     => $connection['type'] === 'sqlite' ? $connection['path'] : $connection['dbname'],
            'db_user'     => $connection['user'],
            'db_pass'     => $connection['pass'],
            'created_by'  => $creatorId,
        ]);

        if ($inserted === false) {
            if ($createdProjectDir) {
                $this->deleteDirectory($projectDir);
            }
            $_SESSION['project_error'] = 'The project record could not be created: ' . DB::LastError();
            redirect('/projects');
        }

        $_SESSION['project_success'] = !empty($_POST['use_virtual_db'])
            ? 'Project created and connected to your Virtual Database. Open it to start building your tables.'
            : 'Project created with ' . DatabaseConnection::label($connection['type']) . ' successfully.';
        $_SESSION['project_created_slug'] = $slug;   // triggers the welcome onboarding on first open
        redirect('/projects');
    }

    /**
     * Resolve the portable pool-database reference a Virtual-Database project binds
     * to — the host-relative `vdb://<name>` form, resolved to this host's absolute
     * path at connect time so replication/migration to a host with a different
     * BASE_PATH still opens the file. Uses the picked pool DB (vdb_id, write-access
     * checked), creates a named new one (vdb_new_label), or auto-creates one named
     * after the project — every path quota-aware. Throws a RuntimeException with a
     * user-safe message.
     */
    private function resolveVirtualDbPath(string $projectName): string
    {
        $uid     = (int) (Security::CurrentUserData()['id'] ?? 0);
        $isOwner = Security::IsOwner();
        $svc     = new VirtualDbService();
        $svc->ensureSchema();

        $vdbId    = (int) ($_POST['vdb_id'] ?? 0);
        $newLabel = trim((string) ($_POST['vdb_new_label'] ?? ''));

        // 1) Bind to an existing pool database the user may write to.
        if ($vdbId > 0) {
            $row = $svc->get($vdbId);
            if ($row === null) {
                throw new \RuntimeException('That database no longer exists. Pick another, or create a new one.');
            }
            if (!$svc->canWrite($vdbId, $uid, $isOwner)) {
                throw new \RuntimeException('You don’t have write access to that database. Choose one you own, or create a new one.');
            }
            return 'vdb://' . $row['name'];
        }

        // 2) Create a new pool database — explicitly named, or after the project.
        if ($svc->quotaExceeded($uid)) {
            throw new \RuntimeException('You’ve reached your database limit (' . $svc->effectiveQuota($uid)
                . '). Ask the owner to raise it, or reuse one of your existing databases.');
        }
        $res = $svc->create($newLabel !== '' ? $newLabel : $projectName, $uid, $uid);
        if (empty($res['success'])) {
            throw new \RuntimeException((string) ($res['message'] ?? 'Could not create the database.'));
        }
        $row = $svc->get((int) ($res['id'] ?? 0));
        if ($row === null) {
            throw new \RuntimeException('The database was created but could not be opened. Please try again.');
        }
        return 'vdb://' . $row['name'];
    }

    /**
     * JSON: the signed-in user's Virtual Database pool + quota — powers Step 3 of
     * the Create-Project modal. GET /projects/vdb-options.
     */
    public function vdbOptions(): void
    {
        Security::RequireAuth();
        Security::RequireRole(['superadmin', 'developer']);
        header('Content-Type: application/json');
        header('Cache-Control: no-store');

        $uid     = (int) (Security::CurrentUserData()['id'] ?? 0);
        $isOwner = Security::IsOwner();
        try {
            $svc = new VirtualDbService();
            $svc->ensureSchema();
            $dbs = [];
            foreach ($svc->visibleTo($uid, $isOwner) as $d) {
                $usage  = $svc->usageFor($d);
                $access = (string) ($d['access'] ?? 'read');
                $dbs[] = [
                    'id'        => (int) $d['id'],
                    'name'      => (string) $d['name'],
                    'label'     => (string) ($d['label'] ?: $d['name']),
                    'access'    => $access,
                    'writable'  => in_array($access, ['owner', 'manage', 'write'], true),
                    'sizeLabel' => (string) ($usage['label'] ?? ''),
                    'percent'   => (float) ($usage['percent'] ?? 0),
                    'level'     => (string) ($usage['level'] ?? 'ok'),
                ];
            }
            usort($dbs, static fn ($a, $b) => ($b['writable'] <=> $a['writable']) ?: strcmp($a['label'], $b['label']));
            echo json_encode([
                'success'   => true,
                'databases' => $dbs,
                'quota'     => [
                    'max'       => $svc->effectiveQuota($uid),
                    'used'      => $svc->usage($uid),
                    'canCreate' => !$svc->quotaExceeded($uid),
                ],
            ]);
        } catch (\Throwable $e) {
            echo json_encode(['success' => false, 'message' => $e->getMessage(),
                'databases' => [], 'quota' => ['max' => 0, 'used' => 0, 'canCreate' => true]]);
        }
        exit;
    }

    /**
     * Destinations for the ⌘K command palette (shared head partial).
     *
     * Returns app-relative paths (the client prepends the base URL). Everything
     * is role-filtered: the console menu comes from ModuleManager::collectMenu()
     * (already role-gated), projects follow the same own-vs-all rule as the
     * projects list, and licensing/admin destinations only appear for admins.
     * `?slug=` adds that project's SPA pages so you can jump straight in.
     */
    public function commandPalette(): void
    {
        Security::RequireAuth();
        header('Content-Type: application/json');
        header('Cache-Control: no-store');

        $uid     = (int) (Security::CurrentUserData()['id'] ?? 0);
        $isAdmin = Security::IsAdmin();

        try {
            // ── Console destinations (role-correct, from the real menu) ──
            $console = [];
            foreach ($this->moduleManager->collectMenu() as $item) {
                $uri = (string) ($item['uri'] ?? $item['url'] ?? '');
                if ($uri === '') { continue; }
                $console[] = [
                    'label' => (string) ($item['label'] ?? $uri),
                    'path'  => $uri[0] === '/' ? $uri : '/' . $uri,
                    'icon'  => (string) ($item['icon'] ?? 'arrow-right-short'),
                    'group' => 'Go to',
                ];
            }
            // Admin-only platform destinations that live outside the sidebar menu.
            if ($isAdmin) {
                foreach ([
                    ['Licence server', '/licensing/server', 'hdd-network'],
                    ['Replication', '/licensing/replication', 'diagram-3'],
                    ['Plans & packages', '/licensing/plans', 'box-seam'],
                    ['Advisories', '/licensing/advisories', 'megaphone'],
                    ['Core configuration', '/settings/core-config', 'sliders'],
                    ['Storage connections', '/settings/storage', 'hdd-stack'],
                    ['Plugin manager', '/plugin-manager', 'puzzle'],
                ] as [$label, $uri, $icon]) {
                    $console[] = ['label' => $label, 'path' => $uri, 'icon' => $icon, 'group' => 'Go to'];
                }
            }

            // ── Projects (same own-vs-all visibility as the projects list) ──
            DB::Init();
            $rows = $isAdmin
                ? DB::Select('projects', [], 'created_at DESC', 'LIMIT 300')
                : DB::Select('projects', ['created_by' => $uid], 'created_at DESC', 'LIMIT 300');
            $rows = is_array($rows) ? $rows : [];
            // Order by most-recently-opened (from the catalog) so the palette's
            // "Recent projects" section is genuinely recent, falling back to created_at.
            if ($rows) {
                $meta = ProjectCatalog::metadataFor(array_column($rows, 'id'));
                usort($rows, static function ($a, $b) use ($meta): int {
                    $la = (string) ($meta[(int) $a['id']]['last_opened_at'] ?? $a['created_at'] ?? '');
                    $lb = (string) ($meta[(int) $b['id']]['last_opened_at'] ?? $b['created_at'] ?? '');
                    return strcmp($lb, $la);
                });
            }
            $projects = [];
            foreach ($rows as $p) {
                $slug = (string) ($p['slug'] ?? '');
                if ($slug === '') { continue; }
                $projects[] = [
                    'label' => (string) ($p['name'] ?: $slug),
                    'path'  => '/project/' . rawurlencode($slug) . '/workspace',
                    'icon'  => 'folder2',
                    'group' => 'Open project',
                    'hint'  => '/' . $slug,
                ];
            }

            // ── Current project's pages (jump straight to Tables, Designer, …) ──
            $projectPages = [];
            $slug = trim((string) ($_GET['slug'] ?? ''));
            if ($slug !== '') {
                $standalone = \PpPhp\Core\ProjectDatabaseProfile::isStandaloneSlug($slug);
                foreach (\PpPhp\Core\ProjectNav::items('', $standalone) as $it) {
                    $projectPages[] = [
                        'label' => (string) $it['label'],
                        'path'  => '/project/' . rawurlencode($slug) . (string) $it['uri'],
                        'icon'  => (string) ($it['icon'] ?? 'arrow-right-short'),
                        'group' => 'This project',
                    ];
                }
            }

            echo json_encode([
                'success'      => true,
                'console'      => $console,
                'projects'     => $projects,
                'projectPages' => $projectPages,
            ]);
        } catch (\Throwable $e) {
            echo json_encode(['success' => false, 'console' => [], 'projects' => [], 'projectPages' => []]);
        }
        exit;
    }

    /**
     * Seed a default "Home" landing page (custom_home) for a new standalone
     * project, so the Designer opens with a page ready and the built app has an
     * index to serve at "/". Never clobbers an existing designer state.
     */
    private function seedStandaloneHomePage(string $projectDir, string $name): void
    {
        $configDir = $projectDir . '/config';
        if (!is_dir($configDir) && !@mkdir($configDir, 0755, true) && !is_dir($configDir)) {
            return;
        }
        $file = $configDir . '/designer.json';
        if (is_file($file)) {
            return;
        }
        $home = [
            'id'         => 'sa_home',
            'name'       => 'custom_home',
            'type'       => 'custom',
            'title'      => 'Home',
            'standalone' => true,
            'layout'     => ['device' => 'desktop', 'width' => 'fluid', 'theme' => 'default', 'chrome' => 'blank'],
            'components' => [
                ['id' => 'sa_home_heading', 'type' => 'heading', 'props' => ['text' => ($name !== '' ? $name : 'Welcome'), 'level' => 'h1'], 'children' => []],
                ['id' => 'sa_home_intro', 'type' => 'text', 'props' => ['text' => 'This is your Home page. Open the Designer to build it with components — no database needed.'], 'children' => []],
            ],
        ];
        $designer = [
            'version'   => '1.0',
            'pages'     => ['__standalone__' => ['custom_home' => ['defaultPage' => 'custom_home', 'pages' => ['custom_home' => $home]]]],
            'common'    => new \stdClass(),
            'active'    => ['scope' => 'table', 'table' => '__standalone__', 'pageType' => 'custom_home'],
            'updatedAt' => date('c'),
        ];
        @file_put_contents($file, json_encode($designer, JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES));
    }

    /**
     * AJAX endpoint to test database connection.
     */
    public function testConnection(): void
    {
        header('Content-Type: application/json');
        Security::RequireAuth();

        $input  = json_decode(file_get_contents('php://input'), true) ?? $_POST;
        if (!Security::CSRF()->Check((string)($input['_token'] ?? ''))) {
            $this->jsonError('Your session security token expired. Refresh the Projects page and try again.', 419);
            exit;
        }
        $slug = strtolower(trim((string)($input['slug'] ?? 'connection-test')));
        if (!preg_match('/^[a-z0-9]+(?:-[a-z0-9]+)*$/', $slug)) {
            $slug = 'connection-test';
        }
        $projectDir = $this->projectsBasePath . '/' . $slug;

        $result = DatabaseConnection::test($input, $projectDir);

        if ($result['success']) {
            echo json_encode($result);
        } else {
            http_response_code(400);
            echo json_encode($result);
        }
        exit;
    }


    // ── Owner-only JSON helpers (shared by the visitor-analytics endpoints) ──

    private function ownerJsonGate(): void
    {
        Security::RequireAuth();
        header('Content-Type: application/json');
        if (!Security::IsOwner()) {
            http_response_code(403);
            echo json_encode(['success' => false, 'message' => 'Only the platform owner can manage databases.']);
            exit;
        }
    }

    private function ownerJsonInput(): array
    {
        $in = json_decode((string) file_get_contents('php://input'), true) ?: $_POST;
        if (!Security::CSRF()->Check((string) ($in['_token'] ?? ''))) {
            http_response_code(419);
            echo json_encode(['success' => false, 'message' => 'Your session expired. Refresh and try again.']);
            exit;
        }
        return is_array($in) ? $in : [];
    }


    /** OWNER-ONLY: who has been visiting this installation. */
    public function visitorsOverview(): string
    {
        Security::RequireAuth();
        if (!Security::IsOwner()) {
            redirect('/projects');
        }
        $baseUrl   = $this->config->get('base_url');
        $assetsUrl = $this->config->get('assets_url');
        $user      = Security::CurrentUserData();

        $V = \PpPhp\Core\Analytics\VisitorLog::class;
        $G = \PpPhp\Core\Analytics\GeoIp::class;
        $V::ensureReady();   // version-gated; avoids 2 CREATE + 2 SHOW COLUMNS every load
        $showBots = ($_GET['bots'] ?? '') === '1';
        // The ~19 aggregate queries below are the same regardless of the bots
        // toggle / pagination (they're all humans-only breakdowns), so cache the
        // whole bundle for a short TTL — an owner dashboard tolerates ~45s
        // staleness, and the live count is refreshed separately by the 20s poll.
        // The cache is flushed on any mutating action (delete/clear/settings).
        $agg = \PpPhp\Core\Analytics\AnalyticsCache::remember('overview', 45, static function () use ($V) {
            return [
                'summary'    => $V::summary(),
                'countries'  => $V::byCountry(20),
                'daily'      => $V::daily(30),
                'hourly'     => $V::hourly(30),
                'heatmap'    => $V::heatmap(30),
                'devices'    => $V::byDevice(),
                'browsers'   => $V::byBrowser(6),
                'systems'    => $V::byOS(6),
                'refTypes'   => $V::byReferrerType(),
                'referrers'  => $V::topReferrers(8),
                'campaigns'  => $V::campaigns(8),
                'topPages'   => $V::topPages(10),
                'entryPages' => $V::entryPages(8),
                'exitPages'  => $V::exitPages(8),
                'channels'   => $V::byChannel(),
                'servers'    => $V::byServer(),
                'networks'   => $V::byNetwork(8),
                'apiSummary' => $V::apiSummary(),
                'apiClients' => $V::apiClients(60),
                'apiActions' => $V::apiActions(10),
            ];
        });
        $summary = $agg['summary']; $countries = $agg['countries']; $daily = $agg['daily'];
        $hourly = $agg['hourly']; $heatmap = $agg['heatmap'] ?? ['grid' => [], 'max' => 0];
        $devices = $agg['devices']; $browsers = $agg['browsers'];
        $systems = $agg['systems']; $refTypes = $agg['refTypes']; $referrers = $agg['referrers'];
        $campaigns = $agg['campaigns']; $topPages = $agg['topPages']; $entryPages = $agg['entryPages'];
        $exitPages = $agg['exitPages']; $channels = $agg['channels']; $servers = $agg['servers'];
        $networks = $agg['networks']; $apiSummary = $agg['apiSummary'];
        $apiClients = $agg['apiClients']; $apiActions = $agg['apiActions'];
        $liveNow     = $V::liveNow(30);        // always fresh — "online now"
        $serverLabel = $V::serverLabel();
        // Recent visits — server-side pagination (the log can grow large).
        $vPerPage = (int) ($_GET['vpp'] ?? 25);
        if (!in_array($vPerPage, [10, 25, 50, 100], true)) $vPerPage = 25;
        $vQuery = trim((string) ($_GET['vq'] ?? ''));
        $recentFilter = ['human' => true, 'bots' => $showBots];
        if ($vQuery !== '') $recentFilter['q'] = $vQuery;
        $vTotal    = $V::recentCount($recentFilter);
        $vPages    = max(1, (int) ceil($vTotal / $vPerPage));
        $vPage     = max(1, (int) ($_GET['vp'] ?? 1));
        if ($vPage > $vPages) $vPage = $vPages;
        $visitors  = $V::recent($vPerPage, $recentFilter + ['offset' => ($vPage - 1) * $vPerPage]);
        $vSettings = $V::settings();
        $geoSettings = $G::settings();
        $geoStatus = $G::status();
        $sites     = \PpPhp\Core\Analytics\AnalyticsSites::all();
        $origin    = ((!empty($_SERVER['HTTPS']) && $_SERVER['HTTPS'] !== 'off') ? 'https' : 'http') . '://' . (string) ($_SERVER['HTTP_HOST'] ?? 'localhost');
        $collectorUrl = $origin . rtrim((string) $baseUrl, '/') . '/api/analytics/collect';
        $analyticsSnippetBase = $origin . rtrim((string) $baseUrl, '/');
        $geoCronUrl = $origin . rtrim((string) $baseUrl, '/') . '/cron/geoip?token=' . rawurlencode($G::cronSecret());

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

    /** OWNER-ONLY: delete rows / clear / save visitor-log settings. */
    public function visitorsAction(): void
    {
        $this->ownerJsonGate();
        $in = $this->ownerJsonInput();
        $V  = \PpPhp\Core\Analytics\VisitorLog::class;
        $G  = \PpPhp\Core\Analytics\GeoIp::class;

        // Any mutation invalidates the cached overview bundle so the console
        // reflects it on the next load rather than after the 45s TTL.
        if (in_array((string) ($in['action'] ?? ''), ['delete', 'delete-bots', 'prune', 'clear', 'settings'], true)) {
            \PpPhp\Core\Analytics\AnalyticsCache::flush();
        }

        switch ((string) ($in['action'] ?? '')) {
            case 'delete':
                echo json_encode(['success' => $V::delete((int) ($in['id'] ?? 0))]);
                break;
            case 'delete-bots':
                echo json_encode(['success' => true, 'removed' => $V::deleteBots()]);
                break;
            case 'prune':
                echo json_encode(['success' => true, 'removed' => $V::prune()]);
                break;
            case 'clear':
                echo json_encode(['success' => $V::clearAll()]);
                break;
            case 'settings':
                $ok = $V::saveSettings(is_array($in['settings'] ?? null) ? $in['settings'] : []);
                if (isset($in['geo']) && is_array($in['geo'])) $ok = $G::saveSettings($in['geo']) && $ok;
                if (isset($in['server_label'])) $V::saveServerLabel((string) $in['server_label']);
                echo json_encode(['success' => $ok]);
                break;
            case 'detail':
                echo json_encode(['success' => true, 'visit' => $V::visitorDetail((int) ($in['id'] ?? 0))]);
                break;
            case 'recent':
                $perPage = (int) ($in['perPage'] ?? 25);
                if (!in_array($perPage, [10, 25, 50, 100], true)) $perPage = 25;
                $q      = trim((string) ($in['q'] ?? ''));
                $filter = ['human' => true, 'bots' => !empty($in['bots'])];
                if ($q !== '') $filter['q'] = $q;
                $total = $V::recentCount($filter);
                $pages = max(1, (int) ceil($total / $perPage));
                $page  = max(1, (int) ($in['page'] ?? 1));
                if ($page > $pages) $page = $pages;
                $visitors = $V::recent($perPage, $filter + ['offset' => ($page - 1) * $perPage]);
                ob_start();
                include __DIR__ . '/../Views/partials/visitor_rows.php';
                $html = (string) ob_get_clean();
                echo json_encode(['success' => true, 'html' => $html, 'total' => $total,
                    'page' => $page, 'pages' => $pages, 'perPage' => $perPage, 'count' => count($visitors)]);
                break;
            case 'live':
                echo json_encode(['success' => true, 'summary' => $V::summary(), 'live' => $this->liveWithAgo($V::liveNow(30))]);
                break;
            case 'trend':
                // Range-aware visitor trend for the chart's 24h/7d/30d/90d selector
                // + live refresh. 'live' also carried so a refresh updates "online now".
                echo json_encode(['success' => true,
                    'range'  => (string) ($in['range'] ?? '30d'),
                    'series' => $V::trendSeries((string) ($in['range'] ?? '30d')),
                    'live'   => $this->liveWithAgo($V::liveNow(30))]);
                break;
            case 'geodb-status':
                echo json_encode(['success' => true, 'status' => $G::status()]);
                break;
            case 'geodb-update':
                echo json_encode($G::updateDatabase() + ['status' => $G::status()]);
                break;
            case 'site-create':
                $S = \PpPhp\Core\Analytics\AnalyticsSites::class;
                echo json_encode(['success' => true, 'site' => $S::create(
                    (string) ($in['label'] ?? ''), (string) ($in['channel'] ?? 'marketing'), !empty($in['browser_enabled'])
                )]);
                break;
            case 'site-regenerate':
                $secret = \PpPhp\Core\Analytics\AnalyticsSites::regenerate((string) ($in['id'] ?? ''));
                echo json_encode(['success' => $secret !== null, 'secret' => $secret]);
                break;
            case 'site-update':
                echo json_encode(['success' => \PpPhp\Core\Analytics\AnalyticsSites::update(
                    (string) ($in['id'] ?? ''), is_array($in['patch'] ?? null) ? $in['patch'] : []
                )]);
                break;
            case 'site-delete':
                echo json_encode(['success' => \PpPhp\Core\Analytics\AnalyticsSites::delete((string) ($in['id'] ?? ''))]);
                break;
            default:
                http_response_code(400);
                echo json_encode(['success' => false, 'message' => 'Unknown action.']);
        }
        exit;
    }

    /**
     * Enrich live-visitor rows with a human "ago" label so the Online-now
     * refresh matches the server-rendered list (same relative-time logic as the
     * visitors view closure).
     *
     * @param array<int,array<string,mixed>> $rows
     * @return array<int,array<string,mixed>>
     */
    private function liveWithAgo(array $rows): array
    {
        $now = time();
        foreach ($rows as &$r) {
            $ts = strtotime((string) ($r['last_seen'] ?? '')) ?: $now;
            $s  = $now - $ts;
            $r['ago'] = $s < 60 ? 'just now'
                : ($s < 3600 ? intdiv($s, 60) . 'm ago'
                : ($s < 86400 ? intdiv($s, 3600) . 'h ago'
                : intdiv($s, 86400) . 'd ago'));
        }
        unset($r);
        return $rows;
    }

    /** OWNER-ONLY: download the visitor log as CSV. */
    public function visitorsExport(): void
    {
        \PpPhp\Core\Security::RequireAuth();
        if (!\PpPhp\Core\Security::IsOwner()) { http_response_code(403); exit; }
        $V = \PpPhp\Core\Analytics\VisitorLog::class;
        $rows = $V::recent(5000, ['bots' => ($_GET['bots'] ?? '') === '1']);

        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename="visitors-' . date('Y-m-d') . '.csv"');
        $out = fopen('php://output', 'w');
        $cols = ['first_seen','last_seen','ip','country','country_name','region','city','timezone',
                 'geo_source','browser','browser_version','platform','platform_version','device','device_model',
                 'screen_w','screen_h','connection','is_touch','referrer_type','referrer_domain',
                 'utm_source','utm_campaign','landing_path','last_path','page_views','active_seconds',
                 'is_return','is_bot','username'];
        fputcsv($out, $cols);
        foreach ($rows as $r) {
            $line = [];
            foreach ($cols as $c) $line[] = (string) ($r[$c] ?? '');
            fputcsv($out, $line);
        }
        fclose($out);
        exit;
    }

    /** Token-guarded web-cron: refresh the geo-IP database (for hosts with no shell cron). */
    public function geoipCron(): void
    {
        $token = (string) ($_GET['token'] ?? '');
        $expected = \PpPhp\Core\Analytics\GeoIp::cronSecret();
        if ($token === '' || !hash_equals($expected, $token)) {
            http_response_code(403);
            header('Content-Type: application/json');
            echo json_encode(['success' => false, 'message' => 'Invalid or missing token.']);
            exit;
        }
        @set_time_limit(0);
        @ignore_user_abort(true);
        $res = \PpPhp\Core\Analytics\GeoIp::runScheduledUpdate(isset($_GET['force']));
        header('Content-Type: application/json');
        echo json_encode($res);
        exit;
    }

    /** Token-guarded web-cron: prune the visitor log past its retention window.
     *  For hosts with no shell cron — otherwise use bin/analytics-prune.php. */
    public function analyticsPruneCron(): void
    {
        $token = (string) ($_GET['token'] ?? '');
        $expected = \PpPhp\Core\Analytics\VisitorLog::cronSecret();
        if ($token === '' || !hash_equals($expected, $token)) {
            http_response_code(403);
            header('Content-Type: application/json');
            echo json_encode(['success' => false, 'message' => 'Invalid or missing token.']);
            exit;
        }
        @set_time_limit(0);
        @ignore_user_abort(true);
        $removed = \PpPhp\Core\Analytics\VisitorLog::prune();
        header('Content-Type: application/json');
        echo json_encode(['success' => true, 'removed' => $removed]);
        exit;
    }

    /** PUBLIC collector: separate properties (marketing/community/…) report visits here. */
    public function analyticsCollect(): void
    {
        header('Access-Control-Allow-Origin: *');
        header('Access-Control-Allow-Methods: POST, OPTIONS');
        header('Access-Control-Allow-Headers: Content-Type');
        if (($_SERVER['REQUEST_METHOD'] ?? '') === 'OPTIONS') { http_response_code(204); exit; }

        $raw = file_get_contents('php://input') ?: '';
        $body = strlen($raw) <= 8192 ? json_decode($raw, true) : null;
        if (!is_array($body)) { http_response_code(400); echo '{"ok":false}'; exit; }

        $res = \PpPhp\Core\Analytics\AnalyticsCollector::ingest($body, $_SERVER);
        http_response_code($res['code'] ?: 204);
        if (($res['code'] ?: 204) !== 204) { header('Content-Type: application/json'); echo json_encode($res); }
        exit;
    }


    /** Return an existing project's redacted database profile. */
    public function databaseConnection(string $slug): void
    {
        header('Content-Type: application/json');
        Security::RequireAuth();

        try {
            $context = $this->authorizedProjectContext($slug);
            $connection = ProjectDatabaseProfile::load($context['project_dir'], $this->legacyConnectionFallback($context['project']));
            $definition = DatabaseConnection::definitions()[$connection['type']] ?? null;
            echo json_encode([
                'success' => true,
                'project' => [
                    'slug' => (string)$context['project']['slug'],
                    'name' => (string)$context['project']['name'],
                ],
                'connection' => ProjectDatabaseProfile::publicPayload($connection),
                'available' => !empty($definition['available']),
                'requirement' => (string)($definition['requirement'] ?? ''),
            ]);
        } catch (\InvalidArgumentException $e) {
            $this->jsonError($e->getMessage(), 400);
        } catch (\RuntimeException $e) {
            $this->jsonError($e->getMessage(), $e->getCode() === 404 ? 404 : 500);
        } catch (\Throwable $e) {
            $this->jsonError('Could not load the database profile: ' . $e->getMessage(), 500);
        }
        exit;
    }

    /** Test edits using the saved password when the password input is blank. */
    public function testProjectConnection(string $slug): void
    {
        header('Content-Type: application/json');
        Security::RequireAuth();
        $input = $this->jsonInput();
        if (!Security::CSRF()->Check((string)($input['_token'] ?? ''))) {
            $this->jsonError('Your session security token expired. Refresh the Projects page and try again.', 419);
            exit;
        }

        try {
            $context = $this->authorizedProjectContext($slug);
            $current = ProjectDatabaseProfile::load($context['project_dir'], $this->legacyConnectionFallback($context['project']));
            $candidate = ProjectDatabaseProfile::mergeInput($input, $current);
            DatabaseConnection::validate($candidate, $context['project_dir']);
            $result = DatabaseConnection::test($candidate, $context['project_dir']);
            if (!$result['success']) {
                $this->jsonError((string)$result['message'], 400, ['driver' => $result['driver']]);
                exit;
            }
            echo json_encode([
                'success' => true,
                'message' => $result['message'],
                'driver' => $result['driver'],
                'source_changed' => ProjectDatabaseProfile::sourceChanged($current, $candidate),
                'summary' => ProjectDatabaseProfile::publicPayload($candidate)['summary'],
            ]);
        } catch (\InvalidArgumentException $e) {
            $this->jsonError($e->getMessage(), 400);
        } catch (\RuntimeException $e) {
            $this->jsonError($e->getMessage(), $e->getCode() === 404 ? 404 : 500);
        } catch (\Throwable $e) {
            $this->jsonError('The connection could not be tested: ' . $e->getMessage(), 500);
        }
        exit;
    }

    /** Validate, test, and atomically replace an existing project database profile. */
    public function updateDatabaseConnection(string $slug): void
    {
        header('Content-Type: application/json');
        Security::RequireAuth();
        Security::RequireRole(['superadmin', 'developer']);
        $input = $this->jsonInput();
        if (!Security::CSRF()->Check((string)($input['_token'] ?? ''))) {
            $this->jsonError('Your session security token expired. Refresh the Projects page and try again.', 419);
            exit;
        }

        try {
            $context = $this->authorizedProjectContext($slug);
            $current = ProjectDatabaseProfile::load($context['project_dir'], $this->legacyConnectionFallback($context['project']));
            $candidate = ProjectDatabaseProfile::mergeInput($input, $current);
            DatabaseConnection::validate($candidate, $context['project_dir']);
            $sourceChanged = ProjectDatabaseProfile::sourceChanged($current, $candidate);
            if ($sourceChanged && !$this->toBool($input['confirm_source_change'] ?? false)) {
                $this->jsonError(
                    'Confirm that changing the data source does not migrate or delete data before saving.',
                    409,
                    ['requires_confirmation' => true]
                );
                exit;
            }

            // Never trust a client-side test marker; verify again immediately before writing.
            $test = DatabaseConnection::test($candidate, $context['project_dir']);
            if (!$test['success']) {
                $this->jsonError('Connection was not saved: ' . $test['message'], 400, ['driver' => $test['driver']]);
                exit;
            }
            $candidate['driver'] = $test['driver'] ?: $candidate['driver'];

            $previousContent = ProjectDatabaseProfile::rawContent($context['project_dir']);
            ProjectDatabaseProfile::writeAtomic($context['project_dir'], $candidate);
            try {
                $updated = DB::Update('projects', [
                    'db_host' => $candidate['host'],
                    'db_port' => $candidate['port'] !== '' ? (int)$candidate['port'] : 0,
                    'db_name' => $candidate['type'] === 'sqlite' ? $candidate['path'] : $candidate['dbname'],
                    'db_user' => $candidate['user'],
                    'db_pass' => $candidate['pass'],
                ], ['id' => $context['project']['id']]);
                if ($updated === false) {
                    throw new \RuntimeException('Could not update the central project record: ' . DB::LastError());
                }
            } catch (\Throwable $e) {
                ProjectDatabaseProfile::restore($context['project_dir'], $previousContent);
                throw $e;
            }

            $payload = ProjectDatabaseProfile::publicPayload($candidate);
            echo json_encode([
                'success' => true,
                'message' => DatabaseConnection::label($candidate['type']) . ' connection settings saved.',
                'connection' => $payload,
                'db_type' => $candidate['type'],
                'db_label' => DatabaseConnection::label($candidate['type']),
                'source_changed' => $sourceChanged,
                'needs_sync' => $sourceChanged,
            ]);
        } catch (\InvalidArgumentException $e) {
            $this->jsonError($e->getMessage(), 400);
        } catch (\RuntimeException $e) {
            $this->jsonError($e->getMessage(), $e->getCode() === 404 ? 404 : 500);
        } catch (\Throwable $e) {
            $this->jsonError('Could not save the database profile: ' . $e->getMessage(), 500);
        }
        exit;
    }

    /**
     * Delete a project.
     */
    public function deleteProject(string $slug): void
    {
        header('Content-Type: application/json');
        Security::RequireAuth();

        Security::RequireRole(['superadmin', 'developer']);
        $input    = json_decode(file_get_contents('php://input'), true) ?? $_POST;
        if (!Security::CSRF()->Check((string)($input['_token'] ?? ''))) {
            $this->jsonError('Your session security token expired. Refresh the Projects page and try again.', 419);
            exit;
        }
        $deleteDb = (bool)($input['delete_db'] ?? false);

        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]);
        }
        $project = $project[0] ?? null;

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

        $ownerUsername = Security::CurrentUsername();
        if ((int)$project['created_by'] !== (int)Security::CurrentUserData()['id']) {
            $owner = DB::Select('users', ['id' => $project['created_by']]);
            $ownerUsername = (string)($owner[0]['username'] ?? $ownerUsername);
        }
        $projectDir = constant('BASE_PATH') . '/projects/' . $ownerUsername . '/' . $slug;
        $dropWarning = '';

        // Optionally drop the external database (or SQLite file).
        if ($deleteDb) {
            try {
                $configPath = $projectDir . '/config/database.php';
                $connection = is_file($configPath)
                    ? (array)require $configPath
                    : [
                        'type' => 'mysql',
                        'host' => $project['db_host'],
                        'port' => (string)$project['db_port'],
                        'dbname' => $project['db_name'],
                        'user' => $project['db_user'],
                        'pass' => $project['db_pass'],
                    ];
                DB::DropDatabaseConfig($connection, $projectDir);
            } catch (\Throwable $e) {
                $dropWarning = ' The project was removed, but the database could not be deleted: ' . $e->getMessage();
                error_log('Could not drop database: ' . $e->getMessage());
            }
        }

        // Delete project directory
        if (is_dir($projectDir)) {
            $this->deleteDirectory($projectDir);
        }

        // Remove from central DB
        ProjectCatalog::remove((int)$project['id']);
        DB::Delete('projects', ['id' => $project['id']]);

        echo json_encode(['success' => true, 'message' => 'Project deleted.' . $dropWarning]);
        exit;
    }

    /** Update project name, description, and catalog presentation. */
    public function updateProjectSettings(string $slug): void
    {
        header('Content-Type: application/json');
        Security::RequireAuth();
        Security::RequireRole(['superadmin', 'developer']);
        $input = $this->jsonInput();
        if (!$this->checkCsrf($input)) return;

        try {
            $context = $this->authorizedProjectContext($slug);
            $name = trim((string)($input['name'] ?? ''));
            $description = trim((string)($input['description'] ?? ''));
            if ($name === '' || mb_strlen($name) > 100) {
                throw new \InvalidArgumentException('Project name is required and must not exceed 100 characters.');
            }
            if (mb_strlen($description) > 1000) {
                throw new \InvalidArgumentException('Project description must not exceed 1,000 characters.');
            }

            // ── Owner-only: rename the project slug ─────────────────────────
            // The slug is normally immutable so links and files stay stable. The
            // platform owner may change it; we move the project folder on disk and
            // update the record, with rollback if the DB write fails.
            $newSlug = strtolower(trim((string)($input['new_slug'] ?? '')));
            $effectiveSlug = $slug;
            $renamedFrom = null; // set to [oldDir, newDir] when a folder move happened
            if ($newSlug !== '' && $newSlug !== $slug) {
                if (!Security::IsOwner()) {
                    throw new \InvalidArgumentException('Only the platform owner can change a project’s slug.');
                }
                if (!preg_match('/^[a-z0-9]+(?:-[a-z0-9]+)*$/', $newSlug)) {
                    throw new \InvalidArgumentException('The slug may contain only lowercase letters, numbers and single hyphens (no spaces).');
                }
                if (!empty(DB::Select('projects', ['slug' => $newSlug]))) {
                    throw new \InvalidArgumentException('That slug is already in use by another project.');
                }
                $oldDir = $context['project_dir'];
                $newDir = dirname($oldDir) . '/' . $newSlug;
                if (file_exists($newDir)) {
                    throw new \InvalidArgumentException('A project folder with that slug already exists.');
                }
                if (is_dir($oldDir) && !@rename($oldDir, $newDir)) {
                    throw new \RuntimeException('Could not rename the project folder on disk.');
                }
                $renamedFrom = [$oldDir, $newDir];
                $effectiveSlug = $newSlug;
            }

            $updatedAt = date('Y-m-d H:i:s');
            $fields = [
                'name' => $name,
                'description' => $description,
                'updated_at' => $updatedAt,
            ];
            if ($effectiveSlug !== $slug) {
                $fields['slug'] = $effectiveSlug;
            }
            $updated = DB::Update('projects', $fields, ['id' => $context['project']['id']]);
            if ($updated === false) {
                // Roll the folder back so disk and database stay consistent.
                if ($renamedFrom !== null && is_dir($renamedFrom[1])) {
                    @rename($renamedFrom[1], $renamedFrom[0]);
                }
                throw new \RuntimeException('Could not update project settings: ' . DB::LastError());
            }
            if ($effectiveSlug !== $slug) {
                \PpPhp\Core\ProjectLocator::forget($slug);
                \PpPhp\Core\ProjectLocator::forget($effectiveSlug);
            }

            $meta = ProjectCatalog::updatePresentation(
                (int)$context['project']['id'],
                (string)($input['color'] ?? ''),
                (string)($input['icon'] ?? '')
            );
            echo json_encode([
                'success' => true,
                'message' => $effectiveSlug !== $slug ? 'Project settings saved. The slug was renamed.' : 'Project settings saved.',
                'project' => [
                    'slug' => $effectiveSlug,
                    'previous_slug' => $slug,
                    'slug_changed' => $effectiveSlug !== $slug,
                    'name' => $name,
                    'description' => $description,
                    'updated_at' => date(DATE_ATOM, strtotime($updatedAt)),
                ] + $meta,
            ]);
        } catch (\InvalidArgumentException $e) {
            $this->jsonError($e->getMessage(), 400);
        } catch (\RuntimeException $e) {
            $this->jsonError($e->getMessage(), $e->getCode() === 404 ? 404 : 500);
        } catch (\Throwable $e) {
            $this->jsonError('Could not save project settings: ' . $e->getMessage(), 500);
        }
        exit;
    }

    /** Pin or unpin a project in the catalog. */
    public function togglePin(string $slug): void
    {
        header('Content-Type: application/json');
        Security::RequireAuth();
        Security::RequireRole(['superadmin', 'developer']);
        $input = $this->jsonInput();
        if (!$this->checkCsrf($input)) return;
        try {
            $context = $this->authorizedProjectContext($slug);
            $meta = ProjectCatalog::setPinned((int)$context['project']['id'], $this->toBool($input['pinned'] ?? true));
            echo json_encode(['success' => true, 'message' => $meta['is_pinned'] ? 'Project pinned.' : 'Project unpinned.', 'catalog' => $meta]);
        } catch (\InvalidArgumentException $e) {
            $this->jsonError($e->getMessage(), 400);
        } catch (\RuntimeException $e) {
            $this->jsonError($e->getMessage(), $e->getCode() === 404 ? 404 : 500);
        } catch (\Throwable $e) {
            $this->jsonError('Could not update the pinned project: ' . $e->getMessage(), 500);
        }
        exit;
    }

    /** Archive a project without deleting its files or database. */
    public function toggleArchive(string $slug): void
    {
        header('Content-Type: application/json');
        Security::RequireAuth();
        Security::RequireRole(['superadmin', 'developer']);
        $input = $this->jsonInput();
        if (!$this->checkCsrf($input)) return;
        try {
            $context = $this->authorizedProjectContext($slug);
            $archived = $this->toBool($input['archived'] ?? true);
            $meta = ProjectCatalog::setArchived((int)$context['project']['id'], $archived);
            echo json_encode([
                'success' => true,
                'message' => $archived ? 'Project moved to Archive.' : 'Project restored.',
                'catalog' => $meta,
            ]);
        } catch (\InvalidArgumentException $e) {
            $this->jsonError($e->getMessage(), 400);
        } catch (\RuntimeException $e) {
            $this->jsonError($e->getMessage(), $e->getCode() === 404 ? 404 : 500);
        } catch (\Throwable $e) {
            $this->jsonError('Could not update the project archive: ' . $e->getMessage(), 500);
        }
        exit;
    }

    /** Duplicate editable project state without silently copying a remote database. */
    public function duplicateProject(string $slug): void
    {
        header('Content-Type: application/json');
        Security::RequireAuth();
        Security::RequireRole(['superadmin', 'developer']);
        $input = $this->jsonInput();
        if (!$this->checkCsrf($input)) return;

        $destination = '';
        $projectId = 0;
        $service = new ProjectCloneService();
        try {
            $source = $this->authorizedProjectContext($slug);
            $name = $this->validateProjectName((string)($input['name'] ?? ''));
            $newSlug = ProjectPackageFormat::safeSlug((string)($input['slug'] ?? ''));
            $description = trim((string)($input['description'] ?? $source['project']['description'] ?? ''));
            if (mb_strlen($description) > 1000) throw new \InvalidArgumentException('Project description must not exceed 1,000 characters.');
            $this->assertSlugAvailable($newSlug);
            $destination = $this->projectsBasePath . '/' . $newSlug;
            $sourceConnection = ProjectDatabaseProfile::load($source['project_dir'], $this->legacyConnectionFallback($source['project']));
            $mode = (string)($input['connection_mode'] ?? ($sourceConnection['type'] === 'sqlite' ? 'sqlite_copy' : 'share'));
            $result = $service->clone($source['project_dir'], $destination, $sourceConnection, [
                'include_custom' => $this->toBool($input['include_custom'] ?? true),
                'connection_mode' => $mode,
            ]);
            $connection = $result['connection'];
            DatabaseConnection::validate($connection, $destination);
            if ($connection['type'] === 'sqlite') {
                $test = DatabaseConnection::test($connection, $destination);
                if (!$test['success']) throw new \RuntimeException('Could not prepare the duplicate SQLite database: ' . $test['message']);
                $connection['driver'] = $test['driver'] ?: $connection['driver'];
            }
            ProjectDatabaseProfile::writeAtomic($destination, $connection);
            $projectId = $this->insertProjectRecord($name, $newSlug, $description, $connection);
            $sourceMeta = ProjectCatalog::metadataFor([(int)$source['project']['id']])[(int)$source['project']['id']] ?? ProjectCatalog::defaults((int)$source['project']['id']);
            ProjectCatalog::updatePresentation($projectId, (string)$sourceMeta['color'], (string)$sourceMeta['icon']);
            echo json_encode([
                'success' => true,
                'message' => 'Project duplicated. The original remains unchanged.',
                'project' => ['id' => $projectId, 'name' => $name, 'slug' => $newSlug],
                'warnings' => $result['warnings'],
                'open_url' => rtrim((string)$this->config->get('base_url'), '/') . '/project/' . rawurlencode($newSlug),
            ]);
        } catch (\InvalidArgumentException $e) {
            if ($projectId > 0) $this->rollbackProjectRecord($projectId);
            if ($destination !== '' && is_dir($destination)) $service->rollback($destination);
            $this->jsonError($e->getMessage(), 400);
        } catch (\RuntimeException $e) {
            if ($projectId > 0) $this->rollbackProjectRecord($projectId);
            if ($destination !== '' && is_dir($destination)) $service->rollback($destination);
            $this->jsonError($e->getMessage(), $e->getCode() === 404 ? 404 : 500);
        } catch (\Throwable $e) {
            if ($projectId > 0) $this->rollbackProjectRecord($projectId);
            if ($destination !== '' && is_dir($destination)) $service->rollback($destination);
            $this->jsonError('Could not duplicate the project: ' . $e->getMessage(), 500);
        }
        exit;
    }

    /** Download a portable editable-project package. Credentials are always excluded. */
    public function exportProject(string $slug): void
    {
        Security::RequireAuth();
        Security::RequireRole(['superadmin', 'developer']);
        if (!Security::CSRF()->Check((string)($_POST['_token'] ?? ''))) {
            http_response_code(419);
            echo 'Your session security token expired. Refresh Projects and try again.';
            exit;
        }
        $package = null;
        try {
            $context = $this->authorizedProjectContext($slug);
            $meta = ProjectCatalog::metadataFor([(int)$context['project']['id']])[(int)$context['project']['id']] ?? ProjectCatalog::defaults((int)$context['project']['id']);
            $project = $context['project'] + $meta;
            $connection = ProjectDatabaseProfile::load($context['project_dir'], $this->legacyConnectionFallback($context['project']));
            $package = (new ProjectPackageExporter())->export($project, $context['project_dir'], $connection, [
                'include_custom' => $this->toBool($_POST['include_custom'] ?? true),
                'include_sqlite' => $this->toBool($_POST['include_sqlite'] ?? false),
            ]);
            header('Content-Type: application/octet-stream');
            header('Content-Disposition: attachment; filename="' . str_replace('"', '', $package['filename']) . '"');
            header('Content-Length: ' . (string)$package['size']);
            header('Cache-Control: private, no-store, max-age=0');
            header('X-Content-Type-Options: nosniff');
            readfile($package['path']);
        } catch (\Throwable $e) {
            if (!headers_sent()) http_response_code($e->getCode() === 404 ? 404 : 500);
            echo 'Project export failed: ' . $e->getMessage();
        } finally {
            if (is_array($package) && is_file((string)$package['path'])) @unlink((string)$package['path']);
        }
        exit;
    }

    /** Upload and inspect a .ppphp package without writing any project files. */
    public function inspectProjectImport(): void
    {
        header('Content-Type: application/json');
        Security::RequireAuth();
        Security::RequireRole(['superadmin', 'developer']);
        if (!Security::CSRF()->Check((string)($_POST['_token'] ?? ''))) {
            $this->jsonError('Your session security token expired. Refresh Projects and try again.', 419);
            exit;
        }
        try {
            $inspection = (new ProjectPackageInspector())->stageUpload((array)($_FILES['package'] ?? []), $this->importOwnerKey());
            DB::Init(); DB::SetConnection();
            $originalSlug = (string)$inspection['summary']['suggested_slug'];
            $slug = $this->nextAvailableSlug($originalSlug);
            $inspection['summary']['suggested_slug'] = $slug;
            $inspection['conflicts'] = [
                'original_slug_exists' => $slug !== $originalSlug,
                'resolved_slug' => $slug,
            ];
            echo json_encode(['success' => true] + $inspection);
        } catch (\InvalidArgumentException $e) {
            $this->jsonError($e->getMessage(), 400);
        } catch (\Throwable $e) {
            $this->jsonError('The package could not be inspected: ' . $e->getMessage(), 400);
        }
        exit;
    }

    /** Commit a validated import into a new project directory and catalog record. */
    public function commitProjectImport(): void
    {
        header('Content-Type: application/json');
        Security::RequireAuth();
        Security::RequireRole(['superadmin', 'developer']);
        $input = $this->jsonInput();
        if (!$this->checkCsrf($input)) return;
        $inspector = new ProjectPackageInspector();
        $importer = new ProjectPackageImporter();
        $destination = '';
        $projectId = 0;
        $token = (string)($input['token'] ?? '');
        try {
            $name = $this->validateProjectName((string)($input['name'] ?? ''));
            $slug = ProjectPackageFormat::safeSlug((string)($input['slug'] ?? ''));
            $description = trim((string)($input['description'] ?? ''));
            if (mb_strlen($description) > 1000) throw new \InvalidArgumentException('Project description must not exceed 1,000 characters.');
            $this->assertSlugAvailable($slug);
            $stage = $inspector->loadStage($token, $this->importOwnerKey());
            $inspection = $inspector->inspectArchive($stage['archive']);
            $requiresCode = !empty($inspection['summary']['requires_code_confirmation']);
            $allowCode = $this->toBool($input['allow_code'] ?? false);
            if ($requiresCode && !$allowCode) throw new \InvalidArgumentException('Review and confirm the custom executable files before importing this package.');

            $mode = (string)($input['connection_mode'] ?? 'sqlite');
            if ($mode === 'existing') {
                $connectionSource = $this->authorizedProjectContext((string)($input['connection_project'] ?? ''));
                $connection = ProjectDatabaseProfile::load($connectionSource['project_dir'], $this->legacyConnectionFallback($connectionSource['project']));
                if ($connection['type'] === 'sqlite') throw new \InvalidArgumentException('Use the portable SQLite option to import into a project-local SQLite database.');
            } elseif ($mode === 'sqlite') {
                $connection = DatabaseConnection::normalize(['type' => 'sqlite', 'path' => 'database/app.sqlite']);
            } else {
                throw new \InvalidArgumentException('Choose a database connection for the imported project.');
            }

            $destination = $this->projectsBasePath . '/' . $slug;
            $result = $importer->import($stage['archive'], $destination, $allowCode);
            if ($mode !== 'sqlite' || !$this->toBool($input['use_package_sqlite'] ?? true)) {
                if (is_file($destination . '/database/app.sqlite')) @unlink($destination . '/database/app.sqlite');
            }
            DatabaseConnection::validate($connection, $destination);
            if ($connection['type'] === 'sqlite') {
                $test = DatabaseConnection::test($connection, $destination);
                if (!$test['success']) throw new \RuntimeException('Could not prepare the imported SQLite workspace: ' . $test['message']);
                $connection['driver'] = $test['driver'] ?: $connection['driver'];
            }
            ProjectDatabaseProfile::writeAtomic($destination, $connection);
            $schemaResult = ['created' => [], 'skipped' => [], 'relations' => 0];
            if ($connection['type'] === 'sqlite' && $this->toBool($input['initialize_schema'] ?? true)) {
                $blueprintPath = $destination . '/config/schema-blueprint.json';
                $blueprint = is_file($blueprintPath) ? json_decode((string)file_get_contents($blueprintPath), true) : null;
                if (is_array($blueprint)) $schemaResult = (new ProjectSchemaBlueprintService())->apply($destination, $connection, $blueprint);
            }
            $projectId = $this->insertProjectRecord($name, $slug, $description, $connection);
            $manifestProject = (array)($inspection['manifest']['project'] ?? []);
            ProjectCatalog::updatePresentation($projectId, (string)($manifestProject['color'] ?? ''), (string)($manifestProject['icon'] ?? ''));
            try { $inspector->consume($token, $this->importOwnerKey()); } catch (\Throwable $cleanupError) { error_log('Could not clean import staging: ' . $cleanupError->getMessage()); }
            echo json_encode([
                'success' => true,
                'message' => 'Project imported safely. Database credentials were not read from the package.',
                'project' => ['id' => $projectId, 'name' => $name, 'slug' => $slug],
                'files_imported' => count($result['files']),
                'schema' => $schemaResult,
                'open_url' => rtrim((string)$this->config->get('base_url'), '/') . '/project/' . rawurlencode($slug),
            ]);
        } catch (\InvalidArgumentException $e) {
            if ($projectId > 0) $this->rollbackProjectRecord($projectId);
            if ($destination !== '' && is_dir($destination)) $importer->rollback($destination);
            $this->jsonError($e->getMessage(), 400);
        } catch (\RuntimeException $e) {
            if ($projectId > 0) $this->rollbackProjectRecord($projectId);
            if ($destination !== '' && is_dir($destination)) $importer->rollback($destination);
            $this->jsonError($e->getMessage(), in_array($e->getCode(), [404, 410], true) ? $e->getCode() : 500);
        } catch (\Throwable $e) {
            if ($projectId > 0) $this->rollbackProjectRecord($projectId);
            if ($destination !== '' && is_dir($destination)) $importer->rollback($destination);
            $this->jsonError('Could not import the project: ' . $e->getMessage(), 500);
        }
        exit;
    }

    /** Save an existing project as a reusable, credential-free template. */
    public function saveProjectTemplate(string $slug): void
    {
        header('Content-Type: application/json');
        Security::RequireAuth(); Security::RequireRole(['superadmin', 'developer']);
        $input = $this->jsonInput(); if (!$this->checkCsrf($input)) return;
        $stored = null; $service = new ProjectTemplateService();
        try {
            $context = $this->authorizedProjectContext($slug);
            $ownerId = (int)Security::CurrentUserData()['id'];
            $name = $this->validateProjectName((string)($input['name'] ?? ''));
            $templateSlug = ProjectPackageFormat::safeSlug((string)($input['slug'] ?? ''));
            if (ProjectTemplateCatalog::slugExists($ownerId, $templateSlug)) throw new \InvalidArgumentException('A template with this slug already exists.');
            $description = trim((string)($input['description'] ?? ''));
            if (mb_strlen($description) > 1000) throw new \InvalidArgumentException('Template description must not exceed 1,000 characters.');
            $category = $this->validateTemplateCategory((string)($input['category'] ?? 'General'));
            $connection = ProjectDatabaseProfile::load($context['project_dir'], $this->legacyConnectionFallback($context['project']));
            $meta = ProjectCatalog::metadataFor([(int)$context['project']['id']])[(int)$context['project']['id']] ?? ProjectCatalog::defaults((int)$context['project']['id']);
            $stored = $service->save($ownerId, $context['project'] + $meta, $context['project_dir'], $connection, [
                'slug' => $templateSlug, 'include_custom' => $this->toBool($input['include_custom'] ?? true),
            ]);
            $id = ProjectTemplateCatalog::create([
                'owner_id' => $ownerId, 'name' => $name, 'slug' => $templateSlug, 'description' => $description,
                'category' => $category, 'tags' => $this->parseTemplateTags((string)($input['tags'] ?? '')),
                'source_project_slug' => $slug, 'package_path' => $stored['path'], 'manifest' => $stored['manifest'],
                'color' => $meta['color'], 'icon' => $meta['icon'],
            ]);
            echo json_encode(['success' => true, 'message' => 'Reusable template saved without database credentials.', 'template' => ['id' => $id, 'name' => $name, 'slug' => $templateSlug]]);
        } catch (\InvalidArgumentException $e) {
            if (is_array($stored)) try { $service->deletePackage(['package_path' => $stored['path']]); } catch (\Throwable) {}
            $this->jsonError($e->getMessage(), 400);
        } catch (\Throwable $e) {
            if (is_array($stored)) try { $service->deletePackage(['package_path' => $stored['path']]); } catch (\Throwable) {}
            $this->jsonError('Could not save the project template: ' . $e->getMessage(), 500);
        }
        exit;
    }

    /** Create a new project from an owned template package. */
    public function createFromTemplate(string $id): void
    {
        header('Content-Type: application/json');
        Security::RequireAuth(); Security::RequireRole($this->templateUseRoles());
        $input = $this->jsonInput(); if (!$this->checkCsrf($input)) return;
        $importer = new ProjectPackageImporter(); $destination = ''; $projectId = 0;
        try {
            $ownerId = (int)Security::CurrentUserData()['id'];
            $isBuiltin = \PpPhp\Core\Projects\BuiltinTemplates::isBuiltinId($id);
            if ($isBuiltin) {
                $template = \PpPhp\Core\Projects\BuiltinTemplates::find($id);
                if ($template === null) throw new \RuntimeException('The built-in template is unavailable.', 404);
            } else {
                $template = ProjectTemplateCatalog::findOwned((int)$id, $ownerId);
            }
            // Audience gate: a built-in is usable only by the role tiers it targets
            // (e.g. the data-bearing Full Restore is owner-only). Enforced server-side.
            if ($isBuiltin && !\PpPhp\Core\Projects\BuiltinTemplates::canUse($template, $this->currentRoleTier(), (int) (Security::CurrentUserData()['id'] ?? 0), $this->templatesOpenToUsers())) {
                throw new \RuntimeException('This template is not available to your role.', 403);
            }
            // Client installs carry no template packages — download this one from the
            // authority (licence + sha256 verified) before use.
            if ($isBuiltin && \PpPhp\Core\Projects\BuiltinTemplates::usesRemoteCache()) {
                try {
                    $fetched = (new \PpPhp\Core\Projects\TemplateClient())->fetch($id, $this->currentRoleTier());
                    $template['package_path'] = $fetched['package_path'];
                    if (!empty($fetched['seed_path'])) { $template['seed_path'] = $fetched['seed_path']; }
                } catch (\Throwable $e) {
                    throw new \RuntimeException('Could not download this template from the server: ' . $e->getMessage());
                }
            }
            if (!is_file($template['package_path'])) throw new \RuntimeException('The template package is missing. Delete this template and save it again.');
            $inspection = (new ProjectPackageInspector())->inspectArchive($template['package_path']);
            $allowCode = $this->toBool($input['allow_code'] ?? false);
            if (!empty($inspection['summary']['requires_code_confirmation']) && !$allowCode) throw new \InvalidArgumentException('Review and confirm the template’s active-code files before creating the project.');

            // "Update an existing project": apply the template's design + custom files
            // ONTO a project the user already owns, keeping its database + data. This
            // sidesteps the slug collision (no new project) and never touches the DB
            // connection (config/database.php is a PHP file the package never carries).
            if ((string)($input['target_mode'] ?? 'new') === 'update') {
                $ctx = $this->authorizedProjectContext((string)($input['target_project'] ?? ''));
                if (!$this->toBool($input['confirm_update'] ?? false)) {
                    throw new \InvalidArgumentException('Please confirm that this replaces the project’s design and custom files.');
                }
                $tmp = $this->projectsBasePath . '/.tmpl-' . bin2hex(random_bytes(4));
                try {
                    $importer->import($template['package_path'], $tmp, $allowCode);
                    $this->overlayTemplateOntoProject($tmp, (string)$ctx['project_dir']);
                } finally {
                    if (is_dir($tmp)) $importer->rollback($tmp);
                }
                // Content loads only if the target DB is empty — live data is never clobbered.
                $connection = ProjectDatabaseProfile::load((string)$ctx['project_dir'], $this->legacyConnectionFallback($ctx['project']));
                $seedInfo = ($isBuiltin && !empty($template['seed_path']))
                    ? \PpPhp\Core\Projects\BuiltinTemplates::loadSeed($connection, (string)$ctx['project_dir'], (string)$template['seed_path'], (string)($template['content_check_table'] ?? 'qa_questions'))
                    : null;
                $msg = 'Applied “' . $template['name'] . '” to ' . $ctx['project']['name'] . ' — its design and files were restored; the database was kept.' . ($seedInfo ? ' ' . $seedInfo['message'] : '');
                echo json_encode(['success' => true, 'message' => $msg, 'seed' => $seedInfo, 'open_url' => rtrim((string)$this->config->get('base_url'), '/') . '/project/' . rawurlencode((string)$ctx['project']['slug'])]);
                exit;
            }

            $name = $this->validateProjectName((string)($input['name'] ?? ''));
            $slug = ProjectPackageFormat::safeSlug((string)($input['slug'] ?? ''));
            $description = trim((string)($input['description'] ?? $template['description']));
            if (mb_strlen($description) > 1000) throw new \InvalidArgumentException('Project description must not exceed 1,000 characters.');
            $this->assertSlugAvailable($slug);
            $mode = (string)($input['connection_mode'] ?? 'sqlite');
            if ($mode === 'existing') {
                $source = $this->authorizedProjectContext((string)($input['connection_project'] ?? ''));
                $connection = ProjectDatabaseProfile::load($source['project_dir'], $this->legacyConnectionFallback($source['project']));
                if ($connection['type'] === 'sqlite') throw new \InvalidArgumentException('Use the portable SQLite option for a project-local database.');
            } elseif ($mode === 'custom') {
                // Manually-entered credentials (db_host / db_name / db_user / db_pass / db_type).
                $connection = DatabaseConnection::normalize($input);
                if ($connection['type'] === 'sqlite') throw new \InvalidArgumentException('Enter the database connection details, or choose the SQLite option.');
                if (trim((string)($connection['host'] ?? '')) === '' || trim((string)($connection['dbname'] ?? '')) === '') {
                    throw new \InvalidArgumentException('Enter at least the database host and name.');
                }
            } elseif ($mode === 'sqlite') {
                $connection = DatabaseConnection::normalize(['type' => 'sqlite', 'path' => 'database/app.sqlite']);
            } else throw new \InvalidArgumentException('Choose a database connection for the new project.');

            $destination = $this->projectsBasePath . '/' . $slug;
            $result = $importer->import($template['package_path'], $destination, $allowCode);
            if (is_file($destination . '/database/app.sqlite')) @unlink($destination . '/database/app.sqlite');
            DatabaseConnection::validate($connection, $destination);
            if ($connection['type'] === 'sqlite') {
                $test = DatabaseConnection::test($connection, $destination);
                if (!$test['success']) throw new \RuntimeException('Could not prepare the template’s SQLite workspace: ' . $test['message']);
                $connection['driver'] = $test['driver'] ?: $connection['driver'];
            }
            ProjectDatabaseProfile::writeAtomic($destination, $connection);
            $schemaResult = ['created' => [], 'skipped' => [], 'relations' => 0];
            if ($connection['type'] === 'sqlite' && $this->toBool($input['initialize_schema'] ?? true)) {
                $blueprintPath = $destination . '/config/schema-blueprint.json';
                $blueprint = is_file($blueprintPath) ? json_decode((string)file_get_contents($blueprintPath), true) : null;
                if (is_array($blueprint)) $schemaResult = (new ProjectSchemaBlueprintService())->apply($destination, $connection, $blueprint);
            }
            $projectId = $this->insertProjectRecord($name, $slug, $description, $connection);
            ProjectCatalog::updatePresentation($projectId, $template['color'], $template['icon']);
            // Populated built-in templates carry a PII-free content seed; load it into
            // the new project's database (MySQL only, empty-target only — never clobbers).
            $seedInfo = ($isBuiltin && !empty($template['seed_path']))
                ? \PpPhp\Core\Projects\BuiltinTemplates::loadSeed($connection, $destination, (string)$template['seed_path'], (string)($template['content_check_table'] ?? 'qa_questions'))
                : null;
            $message = 'Project created from template.' . ($seedInfo ? ' ' . $seedInfo['message'] : '');
            echo json_encode(['success' => true, 'message' => $message, 'files_imported' => count($result['files']), 'schema' => $schemaResult, 'seed' => $seedInfo, 'open_url' => rtrim((string)$this->config->get('base_url'), '/') . '/project/' . rawurlencode($slug)]);
        } catch (\InvalidArgumentException $e) {
            if ($projectId > 0) $this->rollbackProjectRecord($projectId); if ($destination !== '' && is_dir($destination)) $importer->rollback($destination);
            $this->jsonError($e->getMessage(), 400);
        } catch (\RuntimeException $e) {
            if ($projectId > 0) $this->rollbackProjectRecord($projectId); if ($destination !== '' && is_dir($destination)) $importer->rollback($destination);
            $this->jsonError($e->getMessage(), $e->getCode() === 404 ? 404 : 500);
        } catch (\Throwable $e) {
            if ($projectId > 0) $this->rollbackProjectRecord($projectId); if ($destination !== '' && is_dir($destination)) $importer->rollback($destination);
            $this->jsonError('Could not create the project from this template: ' . $e->getMessage(), 500);
        }
        exit;
    }

    public function deleteProjectTemplate(string $id): void
    {
        header('Content-Type: application/json'); Security::RequireAuth(); Security::RequireRole(['superadmin', 'developer']);
        $input = $this->jsonInput(); if (!$this->checkCsrf($input)) return;
        try {
            if (\PpPhp\Core\Projects\BuiltinTemplates::isBuiltinId($id)) throw new \InvalidArgumentException('Built-in templates are part of the platform and cannot be deleted.');
            $ownerId = (int)Security::CurrentUserData()['id']; $template = ProjectTemplateCatalog::findOwned((int)$id, $ownerId);
            (new ProjectTemplateService())->deletePackage($template); ProjectTemplateCatalog::remove((int)$id, $ownerId);
            echo json_encode(['success' => true, 'message' => 'Template deleted. Existing projects are unchanged.']);
        } catch (\Throwable $e) { $this->jsonError($e->getMessage(), $e->getCode() === 404 ? 404 : 500); }
        exit;
    }

    public function exportProjectTemplate(string $id): void
    {
        Security::RequireAuth(); Security::RequireRole($this->templateUseRoles());
        if (!Security::CSRF()->Check((string)($_POST['_token'] ?? ''))) { http_response_code(419); echo 'Session expired.'; exit; }
        try {
            $isBuiltin = \PpPhp\Core\Projects\BuiltinTemplates::isBuiltinId($id);
            $template = $isBuiltin
                ? \PpPhp\Core\Projects\BuiltinTemplates::find($id)
                : ProjectTemplateCatalog::findOwned((int)$id, (int)Security::CurrentUserData()['id']);
            if (!is_array($template) || !is_file($template['package_path'])) throw new \RuntimeException('The template package is missing.', 404);
            if ($isBuiltin && !\PpPhp\Core\Projects\BuiltinTemplates::canUse($template, $this->currentRoleTier(), (int) (Security::CurrentUserData()['id'] ?? 0), $this->templatesOpenToUsers())) throw new \RuntimeException('This template is not available to your role.', 403);
            header('Content-Type: application/octet-stream');
            header('Content-Disposition: attachment; filename="' . $template['slug'] . '-template.ppphp"');
            header('Content-Length: ' . (string)filesize($template['package_path'])); header('X-Content-Type-Options: nosniff'); header('Cache-Control: private, no-store');
            readfile($template['package_path']);
        } catch (\Throwable $e) { if (!headers_sent()) http_response_code($e->getCode() === 404 ? 404 : 500); echo $e->getMessage(); }
        exit;
    }

    /** Owner-only: set which role tiers may see/use a built-in template. */
    public function setTemplateAccess(string $id): void
    {
        header('Content-Type: application/json');
        Security::RequireAuth();
        if ($this->currentRoleTier() !== 'owner') { $this->jsonError('Only the owner can change template access.', 403); return; }
        $input = $this->jsonInput(); if (!$this->checkCsrf($input)) return;
        try {
            if (!\PpPhp\Core\Projects\BuiltinTemplates::isBuiltinId($id)) {
                throw new \InvalidArgumentException('Access can only be set for built-in templates.');
            }
            $roles = array_values(array_filter((array)($input['roles'] ?? []), 'is_string'));
            if (!\PpPhp\Core\Projects\BuiltinTemplates::setRoles($id, $roles)) {
                throw new \RuntimeException('Could not save the template access settings.');
            }
            // Individual-user grants (optional) — the owner can hand the template to a
            // specific person regardless of their group.
            $userIds = array_values(array_filter(array_map('intval', (array) ($input['users'] ?? [])), static fn($n) => $n > 0));
            \PpPhp\Core\Projects\BuiltinTemplates::setUsers($id, $userIds);
            $eff = \PpPhp\Core\Projects\BuiltinTemplates::rolesFor($id, $roles);
            echo json_encode([
                'success'  => true,
                'message'  => 'Template access updated.',
                'roles'    => $eff,
                'users'    => \PpPhp\Core\Projects\BuiltinTemplates::usersFor($id),
                'audience' => \PpPhp\Core\Projects\BuiltinTemplates::audienceLabel($eff) . ($userIds !== [] ? ' + ' . count($userIds) . ' user' . (count($userIds) === 1 ? '' : 's') : ''),
            ]);
        } catch (\InvalidArgumentException $e) {
            $this->jsonError($e->getMessage(), 400);
        } catch (\Throwable $e) {
            $this->jsonError('Could not update template access: ' . $e->getMessage(), 500);
        }
        exit;
    }

    /** Owner-only: search users for the per-template access picker. Blank q = first page. */
    public function searchUsers(): void
    {
        header('Content-Type: application/json');
        Security::RequireAuth();
        if ($this->currentRoleTier() !== 'owner') { $this->jsonError('Only the owner can view users.', 403); return; }
        $q = trim((string) ($_GET['q'] ?? ''));
        try {
            $like = '%' . $q . '%';
            $rows = \PpPhp\Core\DB::FetchAll(
                "SELECT id, username, email, role FROM users WHERE role <> 'owner'"
                . ($q !== '' ? ' AND (username LIKE ? OR email LIKE ?)' : '')
                . ' ORDER BY username ASC LIMIT 25',
                $q !== '' ? [$like, $like] : []
            ) ?: [];
            echo json_encode(['success' => true, 'users' => $this->shapeUsers($rows)]);
        } catch (\Throwable $e) {
            $this->jsonError('Could not search users: ' . $e->getMessage(), 500);
        }
        exit;
    }

    /** Owner-only: the individual users currently granted a built-in (with names) for the picker. */
    public function templateUsers(string $id): void
    {
        header('Content-Type: application/json');
        Security::RequireAuth();
        if ($this->currentRoleTier() !== 'owner') { $this->jsonError('Only the owner can view template access.', 403); return; }
        try {
            $ids = \PpPhp\Core\Projects\BuiltinTemplates::usersFor($id);
            $rows = [];
            if ($ids !== []) {
                $in = implode(',', array_fill(0, count($ids), '?'));
                $rows = \PpPhp\Core\DB::FetchAll("SELECT id, username, email, role FROM users WHERE id IN ($in) ORDER BY username", array_map('intval', $ids)) ?: [];
            }
            echo json_encode(['success' => true, 'users' => $this->shapeUsers($rows)]);
        } catch (\Throwable $e) {
            $this->jsonError('Could not load template users: ' . $e->getMessage(), 500);
        }
        exit;
    }

    /** @param array<int,array<string,mixed>> $rows @return array<int,array<string,mixed>> */
    private function shapeUsers(array $rows): array
    {
        return array_map(static fn($r) => [
            'id'       => (int) $r['id'],
            'username' => (string) $r['username'],
            'email'    => (string) $r['email'],
            'role'     => (string) ($r['role'] ?? 'user'),
        ], $rows);
    }

    /** Owner-only: toggle whether the `user` group may browse & use templates. */
    public function setTemplateUserAccess(): void
    {
        header('Content-Type: application/json');
        Security::RequireAuth();
        if ($this->currentRoleTier() !== 'owner') { $this->jsonError('Only the owner can change this setting.', 403); return; }
        $input = $this->jsonInput(); if (!$this->checkCsrf($input)) return;
        try {
            $on = $this->toBool($input['open'] ?? false);
            \PpPhp\Core\PlatformSettings::set('templates.open_to_users', ['value' => $on]);
            echo json_encode(['success' => true, 'open' => $on, 'message' => $on
                ? 'The User group can now browse and use templates (each template’s access still applies).'
                : 'Templates are limited to admins again.']);
        } catch (\Throwable $e) {
            $this->jsonError('Could not save the setting: ' . $e->getMessage(), 500);
        }
        exit;
    }

    public function projectHistory(string $slug): void
    {
        header('Content-Type: application/json'); Security::RequireAuth();
        try{$context=$this->authorizedProjectContext($slug);$projectId=(int)$context['project']['id'];$ownerId=(int)$context['project']['created_by'];$versions=ProjectVersionCatalog::forProject($projectId,$ownerId);foreach($versions as &$v){$v['available']=is_file($v['package_path']);unset($v['package_path'],$v['manifest']);}$settings=ProjectVersionCatalog::settings($projectId);$git=(new ProjectGitService())->status($context['project_dir']);echo json_encode(['success'=>true,'project'=>['name'=>$context['project']['name'],'slug'=>$slug],'versions'=>$versions,'settings'=>$settings,'git'=>$git]);}
        catch(\Throwable $e){$this->jsonError($e->getMessage(),$e->getCode()===404?404:500);}exit;
    }

    public function createCheckpoint(string $slug): void
    {
        header('Content-Type: application/json');Security::RequireAuth();Security::RequireRole(['superadmin','developer']);$input=$this->jsonInput();if(!$this->checkCsrf($input))return;
        try{$context=$this->authorizedProjectContext($slug);$connection=ProjectDatabaseProfile::load($context['project_dir'],$this->legacyConnectionFallback($context['project']));$meta=ProjectCatalog::metadataFor([(int)$context['project']['id']])[(int)$context['project']['id']]??ProjectCatalog::defaults((int)$context['project']['id']);$checkpoint=(new ProjectVersionService())->checkpoint((int)$context['project']['created_by'],$context['project']+$meta,$context['project_dir'],$connection,(string)($input['label']??''),(string)($input['notes']??''),'manual');echo json_encode(['success'=>true,'message'=>'Checkpoint created.','checkpoint'=>['id'=>$checkpoint['id'],'size'=>$checkpoint['size']]]);}
        catch(\InvalidArgumentException $e){$this->jsonError($e->getMessage(),400);}catch(\Throwable $e){$this->jsonError('Could not create checkpoint: '.$e->getMessage(),500);}exit;
    }

    public function compareCheckpoint(string $slug,string $id): void
    {
        header('Content-Type: application/json');Security::RequireAuth();
        try{$context=$this->authorizedProjectContext($slug);$ownerId=(int)$context['project']['created_by'];$version=ProjectVersionCatalog::findOwned((int)$id,(int)$context['project']['id'],$ownerId);$connection=ProjectDatabaseProfile::load($context['project_dir'],$this->legacyConnectionFallback($context['project']));$meta=ProjectCatalog::metadataFor([(int)$context['project']['id']])[(int)$context['project']['id']]??ProjectCatalog::defaults((int)$context['project']['id']);$comparison=(new ProjectVersionService())->compare($version,$context['project']+$meta,$context['project_dir'],$connection);echo json_encode(['success'=>true,'checkpoint'=>['id'=>$version['id'],'label'=>$version['label'],'created_at'=>$version['created_at']],'comparison'=>$comparison]);}
        catch(\Throwable $e){$this->jsonError($e->getMessage(),$e->getCode()===404?404:500);}exit;
    }

    public function restoreCheckpoint(string $slug,string $id): void
    {
        header('Content-Type: application/json');Security::RequireAuth();Security::RequireRole(['superadmin','developer']);$input=$this->jsonInput();if(!$this->checkCsrf($input))return;
        try{$context=$this->authorizedProjectContext($slug);$ownerId=(int)$context['project']['created_by'];$version=ProjectVersionCatalog::findOwned((int)$id,(int)$context['project']['id'],$ownerId);if((string)($input['confirm']??'')!==(string)$context['project']['name'])throw new \InvalidArgumentException('Type the project name exactly to confirm restore.');$connection=ProjectDatabaseProfile::load($context['project_dir'],$this->legacyConnectionFallback($context['project']));$meta=ProjectCatalog::metadataFor([(int)$context['project']['id']])[(int)$context['project']['id']]??ProjectCatalog::defaults((int)$context['project']['id']);$service=new ProjectVersionService();$safety=$service->checkpoint($ownerId,$context['project']+$meta,$context['project_dir'],$connection,'Before restore: '.$version['label'],'Automatic safety checkpoint','pre_restore');$restored=$service->restore($version,$context['project_dir']);DB::Update('projects',['updated_at'=>date('Y-m-d H:i:s')],['id'=>$context['project']['id']]);echo json_encode(['success'=>true,'message'=>'Checkpoint restored. The database connection and data were preserved.','safety_checkpoint_id'=>$safety['id'],'restored'=>$restored]);}
        catch(\InvalidArgumentException $e){$this->jsonError($e->getMessage(),400);}catch(\Throwable $e){$this->jsonError('Could not restore checkpoint: '.$e->getMessage(),500);}exit;
    }

    public function deleteCheckpoint(string $slug,string $id): void
    {
        header('Content-Type: application/json');Security::RequireAuth();Security::RequireRole(['superadmin','developer']);$input=$this->jsonInput();if(!$this->checkCsrf($input))return;
        try{$context=$this->authorizedProjectContext($slug);$ownerId=(int)$context['project']['created_by'];$version=ProjectVersionCatalog::findOwned((int)$id,(int)$context['project']['id'],$ownerId);(new ProjectVersionService())->deletePackage($version);ProjectVersionCatalog::remove((int)$id,(int)$context['project']['id'],$ownerId);echo json_encode(['success'=>true,'message'=>'Checkpoint deleted.']);}catch(\Throwable $e){$this->jsonError($e->getMessage(),$e->getCode()===404?404:500);}exit;
    }

    public function saveVersionControl(string $slug): void
    {
        header('Content-Type: application/json');Security::RequireAuth();Security::RequireRole(['superadmin','developer']);$input=$this->jsonInput();if(!$this->checkCsrf($input))return;
        try{$context=$this->authorizedProjectContext($slug);$gitService=new ProjectGitService();$settings=$gitService->validateSettings($input);$saved=ProjectVersionCatalog::saveSettings((int)$context['project']['id'],$settings);$git=$gitService->applySettings($context['project_dir'],$settings);echo json_encode(['success'=>true,'message'=>'Version-control settings saved.','settings'=>$saved,'git'=>$git]);}catch(\InvalidArgumentException $e){$this->jsonError($e->getMessage(),400);}catch(\Throwable $e){$this->jsonError($e->getMessage(),500);}exit;
    }

    public function initializeGit(string $slug): void
    {
        header('Content-Type: application/json');Security::RequireAuth();Security::RequireRole(['superadmin','developer']);$input=$this->jsonInput();if(!$this->checkCsrf($input))return;
        try{$context=$this->authorizedProjectContext($slug);$service=new ProjectGitService();$settings=$service->validateSettings($input);ProjectVersionCatalog::saveSettings((int)$context['project']['id'],$settings);$result=$service->initialize($context['project_dir'],$settings);echo json_encode(['success'=>true]+$result);}catch(\InvalidArgumentException $e){$this->jsonError($e->getMessage(),400);}catch(\Throwable $e){$this->jsonError('Could not initialize Git: '.$e->getMessage(),500);}exit;
    }

    public function commitGit(string $slug): void
    {
        header('Content-Type: application/json');Security::RequireAuth();Security::RequireRole(['superadmin','developer']);$input=$this->jsonInput();if(!$this->checkCsrf($input))return;
        try{$context=$this->authorizedProjectContext($slug);$settings=ProjectVersionCatalog::settings((int)$context['project']['id']);$result=(new ProjectGitService())->commit($context['project_dir'],(string)($input['message']??''),$settings);echo json_encode(['success'=>true]+$result);}catch(\InvalidArgumentException $e){$this->jsonError($e->getMessage(),400);}catch(\Throwable $e){$this->jsonError('Could not create Git commit: '.$e->getMessage(),500);}exit;
    }

    public function schemaPreview(string $slug): void
    {
        header('Content-Type: application/json');Security::RequireAuth();try{$context=$this->authorizedProjectContext($slug);$connection=ProjectDatabaseProfile::load($context['project_dir'],$this->legacyConnectionFallback($context['project']));$comparison=(new ProjectSchemaDriftService($context['project_dir'],$connection))->compare();echo json_encode(['success'=>true,'project'=>['name'=>$context['project']['name'],'slug'=>$slug],'comparison'=>$comparison]);}catch(\Throwable $e){$this->jsonError('Could not compare schemas: '.$e->getMessage(),500);}exit;
    }
    public function importDatabaseTables(string $slug): void
    {
        header('Content-Type: application/json');Security::RequireAuth();Security::RequireRole(['superadmin','developer']);$input=$this->jsonInput();if(!$this->checkCsrf($input))return;try{$context=$this->authorizedProjectContext($slug);$connection=ProjectDatabaseProfile::load($context['project_dir'],$this->legacyConnectionFallback($context['project']));$safety=$this->schemaSafetyCheckpoint($context,$connection,'Before importing database tables');$result=(new ProjectSchemaDriftService($context['project_dir'],$connection))->import((array)($input['tables']??[]));DB::Update('projects',['updated_at'=>date('Y-m-d H:i:s')],['id'=>$context['project']['id']]);echo json_encode(['success'=>true,'message'=>count($result['imported']).' database object(s) imported into the project model.','result'=>$result,'safety_checkpoint_id'=>$safety]);}catch(\InvalidArgumentException $e){$this->jsonError($e->getMessage(),400);}catch(\Throwable $e){$this->jsonError('Could not import database tables: '.$e->getMessage(),500);}exit;
    }
    public function rebuildProjectStructures(string $slug): void
    {
        header('Content-Type: application/json');Security::RequireAuth();Security::RequireRole(['superadmin','developer']);$input=$this->jsonInput();if(!$this->checkCsrf($input))return;try{$context=$this->authorizedProjectContext($slug);$connection=ProjectDatabaseProfile::load($context['project_dir'],$this->legacyConnectionFallback($context['project']));$safety=$this->schemaSafetyCheckpoint($context,$connection,'Before rebuilding project structures');$result=(new ProjectSchemaDriftService($context['project_dir'],$connection))->rebuild($this->toBool($input['remove_missing']??false));DB::Update('projects',['updated_at'=>date('Y-m-d H:i:s')],['id'=>$context['project']['id']]);echo json_encode(['success'=>true,'message'=>count($result['refreshed']).' project structure(s) refreshed from the live database.','result'=>$result,'safety_checkpoint_id'=>$safety]);}catch(\Throwable $e){$this->jsonError('Could not rebuild project structures: '.$e->getMessage(),500);}exit;
    }
    public function createMissingDatabaseTables(string $slug): void
    {
        header('Content-Type: application/json');Security::RequireAuth();Security::RequireRole(['superadmin','developer']);$input=$this->jsonInput();if(!$this->checkCsrf($input))return;try{$context=$this->authorizedProjectContext($slug);if((string)($input['confirm']??'')!==(string)$context['project']['name'])throw new \InvalidArgumentException('Type the project name exactly to confirm database table creation.');$connection=ProjectDatabaseProfile::load($context['project_dir'],$this->legacyConnectionFallback($context['project']));$safety=$this->schemaSafetyCheckpoint($context,$connection,'Before creating missing database tables');$drift=new ProjectSchemaDriftService($context['project_dir'],$connection);$result=$drift->createMissing((array)($input['tables']??[]));$drift->rebuild(false);echo json_encode(['success'=>true,'message'=>count($result['created']).' missing table(s) created. Existing tables were not altered.','result'=>$result,'safety_checkpoint_id'=>$safety]);}catch(\InvalidArgumentException $e){$this->jsonError($e->getMessage(),400);}catch(\Throwable $e){$this->jsonError('Could not create missing tables: '.$e->getMessage(),500);}exit;
    }

    /**
     * GET the objects + engine info for the Generate SQL Script modal (read-only).
     * Returns every live object with a row count and an in-project flag so the modal
     * can offer both "project tables only" and "all database tables" without a
     * second round-trip.
     */
    public function scriptPreview(string $slug): void
    {
        header('Content-Type: application/json');Security::RequireAuth();
        try{
            $context=$this->authorizedProjectContext($slug);
            $connection=ProjectDatabaseProfile::load($context['project_dir'],$this->legacyConnectionFallback($context['project']));
            $svc=new SqlScriptService($context['project_dir'],$connection);
            echo json_encode(['success'=>true,'project'=>['name'=>$context['project']['name'],'slug'=>$slug],'info'=>$svc->info(),'objects'=>$svc->objects('all'),'targets'=>SqlScriptService::targetOptions()]);
        }catch(\Throwable $e){$this->jsonError('Could not read the database: '.$e->getMessage(),500);}
        exit;
    }

    /**
     * POST → stream a generated .sql script as an attachment. Read-only against the
     * source database (SELECTs + schema reads only). CSRF + developer-role gated.
     */
    public function generateScript(string $slug): void
    {
        Security::RequireAuth();Security::RequireRole(['superadmin','developer']);$input=$this->jsonInput();if(!$this->checkCsrf($input))return;
        try{
            $context=$this->authorizedProjectContext($slug);
            $connection=ProjectDatabaseProfile::load($context['project_dir'],$this->legacyConnectionFallback($context['project']));
            $svc=new SqlScriptService($context['project_dir'],$connection);
            $options=[
                'scope'=>(string)($input['scope']??'project'),
                'mode'=>(string)($input['mode']??'both'),
                'rowLimit'=>(int)($input['rowLimit']??0),
                'tables'=>(array)($input['tables']??[]),
                'dropTable'=>$this->toBool($input['dropTable']??true),
                'ifNotExists'=>$this->toBool($input['ifNotExists']??false),
                'disableForeignKeys'=>$this->toBool($input['disableForeignKeys']??true),
                'transaction'=>$this->toBool($input['transaction']??true),
                'extendedInsert'=>$this->toBool($input['extendedInsert']??true),
                'comments'=>$this->toBool($input['comments']??true),
                'targetType'=>(string)($input['targetType']??''),
            ];
            $tmp=tempnam(sys_get_temp_dir(),'ppsql');
            if($tmp===false)throw new \RuntimeException('Could not open a temporary file for the script.');
            $fh=fopen($tmp,'w+b');
            try{$stats=$svc->generate($options,$fh);}finally{fclose($fh);}
            $filename=preg_replace('/[^A-Za-z0-9_.-]+/','-',$slug).'-'.date('Ymd-His').'.sql';
            header('Content-Type: application/sql; charset=utf-8');
            header('Content-Disposition: attachment; filename="'.$filename.'"');
            header('Content-Length: '.(string)filesize($tmp));
            header('X-Script-Filename: '.$filename);
            header('X-Script-Tables: '.(string)$stats['tables']);
            header('X-Script-Rows: '.(string)$stats['rows']);
            header('Cache-Control: no-store');
            readfile($tmp);
            @unlink($tmp);
            exit;
        }catch(\InvalidArgumentException $e){if(isset($tmp)&&is_string($tmp))@unlink($tmp);$this->jsonError($e->getMessage(),400);}
        catch(\Throwable $e){if(isset($tmp)&&is_string($tmp))@unlink($tmp);$this->jsonError('Could not generate the SQL script: '.$e->getMessage(),500);}
        exit;
    }

    /** @return array{project:array,project_dir:string} */
    private function authorizedProjectContext(string $slug): array
    {
        $slug = strtolower(trim($slug));
        if (!preg_match('/^[a-z0-9]+(?:-[a-z0-9]+)*$/', $slug)) {
            throw new \InvalidArgumentException('Invalid project identifier.');
        }

        DB::Init();
        DB::SetConnection();
        $userId = (int)Security::CurrentUserData()['id'];
        $projects = DB::Select('projects', ['slug' => $slug, 'created_by' => $userId]);
        if (empty($projects) && Security::IsAdmin()) {
            $projects = DB::Select('projects', ['slug' => $slug]);
        }
        $project = $projects[0] ?? null;
        if (!$project) {
            throw new \RuntimeException('Project not found.', 404);
        }

        $ownerUsername = Security::CurrentUsername();
        if ((int)$project['created_by'] !== $userId) {
            $owners = DB::Select('users', ['id' => $project['created_by']]);
            $ownerUsername = (string)($owners[0]['username'] ?? '');
            if ($ownerUsername === '') {
                throw new \RuntimeException('Project owner could not be resolved.');
            }
        }

        return [
            'project' => $project,
            'project_dir' => constant('BASE_PATH') . '/projects/' . $ownerUsername . '/' . $slug,
        ];
    }

    private function legacyConnectionFallback(array $project): array
    {
        return [
            'type' => 'mysql',
            'host' => (string)($project['db_host'] ?? '127.0.0.1'),
            'port' => (string)($project['db_port'] ?? '3306'),
            'dbname' => (string)($project['db_name'] ?? ''),
            'user' => (string)($project['db_user'] ?? ''),
            'pass' => (string)($project['db_pass'] ?? ''),
        ];
    }

    private function jsonInput(): array
    {
        $decoded = json_decode((string)file_get_contents('php://input'), true);
        return is_array($decoded) ? $decoded : $_POST;
    }

    private function jsonError(string $message, int $status, array $extra = []): void
    {
        http_response_code($status);
        echo json_encode(['success' => false, 'message' => $message] + $extra);
    }

    private function checkCsrf(array $input): bool
    {
        if (Security::CSRF()->Check((string)($input['_token'] ?? ''))) return true;
        $this->jsonError('Your session security token expired. Refresh the Projects page and try again.', 419);
        return false;
    }

    private function validateProjectName(string $name): string
    {
        $name = trim($name);
        if ($name === '' || mb_strlen($name) > 100) throw new \InvalidArgumentException('Project name is required and must not exceed 100 characters.');
        return $name;
    }

    /** Owner toggle: may the `user` group browse & use templates (subject to each template's audience)? */
    private function templatesOpenToUsers(): bool
    {
        if (!class_exists('\PpPhp\Core\PlatformSettings')) {
            return false;
        }
        try {
            return (bool) (\PpPhp\Core\PlatformSettings::get('templates.open_to_users')['value'] ?? false);
        } catch (\Throwable) {
            return false;
        }
    }

    /** Roles permitted to reach the template feature (superadmin/developer, +user when the owner enables it). */
    private function templateUseRoles(): array
    {
        $roles = ['superadmin', 'developer'];
        if ($this->templatesOpenToUsers()) {
            $roles[] = 'user';
        }
        return $roles;
    }

    /**
     * On a CLIENT install, refresh the licence-verified template catalogue from the
     * authority before listing (throttled). Returns a status for the UI banner, or
     * null on an authority (which uses its own codebase templates).
     */
    private function refreshRemoteTemplates(string $tier): ?array
    {
        if (!class_exists('\PpPhp\Core\Projects\TemplateClient') || !\PpPhp\Core\Projects\BuiltinTemplates::usesRemoteCache()) {
            return null;
        }
        try {
            $tc = new \PpPhp\Core\Projects\TemplateClient();
            if (!$tc->isStale(1800)) {   // fresh within 30 min — use the cache
                return ['refreshed' => false, 'licensed' => true, 'cached' => true, 'message' => ''];
            }
            $r = $tc->catalog($tier);
            return ['refreshed' => true, 'licensed' => (bool) ($r['licensed'] ?? false), 'cached' => (bool) ($r['cached'] ?? true), 'message' => (string) ($r['message'] ?? '')];
        } catch (\Throwable) {
            return ['refreshed' => false, 'licensed' => true, 'cached' => true, 'message' => ''];
        }
    }

    /** The current user's role tier (owner/superadmin/developer/user) for template audiences. */
    private function currentRoleTier(): string
    {
        $tier = Security::CurrentUserGroup();
        if (Security::isHiddenUser(Security::CurrentUsername(), $tier)) {
            $tier = 'owner';
        }
        return in_array($tier, \PpPhp\Core\Projects\BuiltinTemplates::ROLES, true) ? $tier : 'user';
    }

    /**
     * Overlay a template's design (config/*.json) + custom files onto an EXISTING
     * project, without touching its database. `config/database.php` is a PHP file
     * the package never carries, so the project's DB connection stays intact; only
     * the JSON design + the custom/ tree are restored. Used by "Update an existing
     * project". Overwrites files but never deletes ones the template lacks.
     */
    private function overlayTemplateOntoProject(string $srcDir, string $projectDir): void
    {
        $srcConfig = rtrim($srcDir, '/') . '/config';
        if (is_dir($srcConfig)) {
            @mkdir($projectDir . '/config', 0775, true);
            foreach (glob($srcConfig . '/*.json') ?: [] as $f) {
                @copy($f, rtrim($projectDir, '/') . '/config/' . basename($f));
            }
        }
        $srcCustom = rtrim($srcDir, '/') . '/custom';
        if (is_dir($srcCustom)) {
            $this->copyTree($srcCustom, rtrim($projectDir, '/') . '/custom');
        }
    }

    /** Recursively copy a directory tree, overwriting existing files (never deletes). */
    private function copyTree(string $src, string $dst): void
    {
        @mkdir($dst, 0775, true);
        $it = new \RecursiveIteratorIterator(
            new \RecursiveDirectoryIterator($src, \FilesystemIterator::SKIP_DOTS),
            \RecursiveIteratorIterator::SELF_FIRST
        );
        foreach ($it as $item) {
            $target = $dst . '/' . $it->getSubPathName();
            if ($item->isDir()) {
                @mkdir($target, 0775, true);
            } else {
                @mkdir(dirname($target), 0775, true);
                @copy($item->getPathname(), $target);
            }
        }
    }

    private function assertSlugAvailable(string $slug): void
    {
        DB::Init(); DB::SetConnection();
        if (!empty(DB::Select('projects', ['slug' => $slug]))) throw new \InvalidArgumentException('A project with this slug already exists. Choose another slug.');
        if (file_exists($this->projectsBasePath . '/' . $slug)) throw new \InvalidArgumentException('A project directory with this slug already exists. Choose another slug.');
    }

    private function nextAvailableSlug(string $requested): string
    {
        $base = ProjectPackageFormat::safeSlug($requested);
        $candidate = $base; $suffix = 2;
        while (!empty(DB::Select('projects', ['slug' => $candidate])) || file_exists($this->projectsBasePath . '/' . $candidate)) {
            $candidate = substr($base, 0, 90) . '-' . $suffix++;
        }
        return $candidate;
    }

    private function insertProjectRecord(string $name, string $slug, string $description, array $connection): int
    {
        $connection = DatabaseConnection::normalize($connection);
        $id = DB::Insert('projects', [
            'name' => $name, 'slug' => $slug, 'description' => $description,
            'db_host' => $connection['host'],
            'db_port' => $connection['port'] !== '' ? (int)$connection['port'] : 0,
            'db_name' => $connection['type'] === 'sqlite' ? $connection['path'] : $connection['dbname'],
            'db_user' => $connection['user'], 'db_pass' => $connection['pass'],
            'created_by' => Security::CurrentUserData()['id'],
        ]);
        if ($id === false) throw new \RuntimeException('Could not create the project catalog record: ' . DB::LastError());
        return $id;
    }

    private function importOwnerKey(): string
    {
        return (string)Security::CurrentUserData()['id'] . '|' . session_id();
    }

    private function rollbackProjectRecord(int $projectId): void
    {
        try { ProjectCatalog::remove($projectId); } catch (\Throwable) {}
        try { DB::Delete('projects', ['id' => $projectId]); } catch (\Throwable) {}
    }

    private function validateTemplateCategory(string $category): string
    {
        $allowed = ['Business', 'General', 'CRM', 'Operations', 'Commerce', 'Content', 'Analytics', 'Custom'];
        $category = trim($category); return in_array($category, $allowed, true) ? $category : 'Custom';
    }

    private function parseTemplateTags(string $tags): array
    {
        $items = preg_split('/[,\n]+/', $tags) ?: [];
        $items = array_values(array_unique(array_filter(array_map(static fn(string $tag): string => mb_substr(trim($tag), 0, 30), $items))));
        return array_slice($items, 0, 8);
    }

    private function schemaSafetyCheckpoint(array $context,array $connection,string $label): int
    {
        $meta=ProjectCatalog::metadataFor([(int)$context['project']['id']])[(int)$context['project']['id']]??ProjectCatalog::defaults((int)$context['project']['id']);$saved=(new ProjectVersionService())->checkpoint((int)$context['project']['created_by'],$context['project']+$meta,$context['project_dir'],$connection,$label,'Automatic Phase 5 safety checkpoint','schema_sync');return (int)$saved['id'];
    }

    private function readJsonFile(string $path): array
    {
        if (!is_file($path)) return [];
        $decoded = json_decode((string)file_get_contents($path), true);
        return is_array($decoded) ? $decoded : [];
    }

    private function toBool(mixed $value): bool
    {
        if (is_bool($value)) return $value;
        return in_array(strtolower(trim((string)$value)), ['1', 'true', 'yes', 'on'], true);
    }

    /**
     * Recursively delete a directory and its contents.
     */
    private function deleteDirectory(string $dir): void
    {
        if (!is_dir($dir)) return;
        $items = scandir($dir);
        foreach ($items as $item) {
            if ($item === '.' || $item === '..') continue;
            $path = $dir . DIRECTORY_SEPARATOR . $item;
            is_dir($path) ? $this->deleteDirectory($path) : unlink($path);
        }
        rmdir($dir);
    }

    // -------------------------------------------------------
    //  Helpers (menu filtering only – auth guards now in Security)
    // -------------------------------------------------------
    private function filterMenuByAuth(array $menu, bool $isAuth): array
    {
        return array_values(array_filter($menu, function ($item) use ($isAuth) {
            $visibility = $item['visibility'] ?? 'always';
            return match ($visibility) {
                'guest' => !$isAuth,
                'auth'  => $isAuth,
                default => true,
            };
        }));
    }

    // SAVE PROJECT STATE (AJAX)
    public function saveProject(string $slug): void
    {
        header('Content-Type: application/json');
        Security::RequireAuth();
        // AUTHORIZATION: only the project's owner (or an admin) may write its state.
        // Without this, any authenticated user could overwrite another user's
        // designer/tables/fields/misc state by POSTing to their slug. Reuses the
        // same owner-or-admin resolver every other project-write endpoint uses;
        // it throws 404 (not found / not yours) or 400 (bad slug) before any write.
        try {
            $this->authorizedProjectContext($slug);
        } catch (\Throwable $e) {
            $status = $e instanceof \InvalidArgumentException ? 400 : (((int) $e->getCode() === 404) ? 404 : 403);
            http_response_code($status);
            echo json_encode(['success' => false, 'message' => 'You do not have permission to edit this project.']);
            return;
        }
        $input = json_decode(file_get_contents('php://input'), true) ?? $_POST;
        $module = $input['module'] ?? 'tables';
        $data   = $input['data']   ?? [];

        // Migrated modules use their own state files
        $migrated = ['query', 'pages', 'fields', 'misc', 'designer', 'output', 'finished'];
        if (in_array($module, $migrated)) {
            // For per-table modules, merge byTable into the existing state so a
            // partial payload (e.g. only the active table) can never wipe the rest.
            if (isset($data['byTable']) && is_array($data['byTable'])) {
                $existing = \PpPhp\Core\ModuleState::load($slug, $module);
                if (!is_array($existing) || array_is_list($existing)) {
                    $existing = [];
                }
                $existing['byTable'] = array_merge($existing['byTable'] ?? [], $data['byTable']);
                if (isset($data['activeTable'])) {
                    $existing['activeTable'] = $data['activeTable'];
                }
                $data = $existing;
            }
            \PpPhp\Core\ModuleState::save($slug, $module, $data);
        } else {
            \PpPhp\Core\ProjectState::mergeAndSave($slug, $module, $data);
        }

        DB::Init();
        DB::SetConnection();
        DB::Update('projects', ['updated_at' => date('Y-m-d H:i:s')], [
            'slug' => $slug,
            'created_by' => Security::CurrentUserData()['id'],
        ]);

        // Keep an automatic (throttled) version so users can always step back.
        \PpPhp\Core\VersionHistory::maybeAutoSnapshot($slug);

        echo json_encode(['success' => true, 'message' => 'Project saved.']);
        exit;
    }
}
