<?php

declare(strict_types=1);

namespace PpPhp\Modules\Security\Controllers;

use Exception;

use PpPhp\Core\Config;
use PpPhp\Core\DB;
use PpPhp\Core\Security;
use PpPhp\Core\ProjectNav;
use PpPhp\Core\ModuleState;

class SecurityController
{
    private array $project = [];
    private string $slug = '';
    private bool $tablesInitialized = false;

    public function __construct(private Config $config) {}

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

        // Ensure security tables exist if auth is enabled
        $settings = ModuleState::load($slug, 'security');
        if (($settings['db_auth_enabled'] ?? false)) {
            $this->ensureTablesExist();
        }

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

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

    // -------------------------------------------------------
    //  Initialize security tables (called automatically if missing)
    // -------------------------------------------------------
    public function init(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        // In existing-table mode `users` is a compatibility VIEW; createTables()
        // would try to build FK'd satellite tables against it (a view can't be
        // FK-referenced) and fail. Those satellites are created FK-free on demand
        // by the runtime instead, so there is nothing to initialise here.
        $sec = ModuleState::load($slug, 'security');
        $existingMode = !empty($sec['db_auth_enabled']) && empty($sec['create_new_table'])
            && strtolower(trim((string) ($sec['users_table'] ?? 'users'))) !== 'users';
        if ($existingMode) {
            echo json_encode(['success' => true, 'message' => 'Using your existing table — no extra tables needed.']);
            exit;
        }
        $this->createTables();
        echo json_encode(['success' => true, 'message' => 'Security tables initialized.']);
        exit;
    }

    // -------------------------------------------------------
    //  Security Settings
    // -------------------------------------------------------
    public function getSettings(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        $settings = ModuleState::load($slug, 'security');
        $defaults = [
            'db_auth_enabled' => false,
            'users_table' => 'users',
            'username_field' => 'username',
            'password_field' => 'password',
            'fullname_field' => 'fullname',
            'email_field' => 'email'
        ];
        $settings = array_merge($defaults, $settings);
        $settings['social_auth'] = $this->redactSocialAuth((array)($settings['social_auth'] ?? []));
        // The Login Appearance preview needs the app's own brand to show the same
        // fallback initials and logo the build renders. Resolved exactly the way
        // BuildWriter does it (active theme shell logo, then project title), so
        // the preview cannot drift from the generated page.
        $shellLogo = [];
        try {
            $activeTheme = \PpPhp\Core\Theme\ThemeRegistry::resolvedForBuild(
                $slug,
                \PpPhp\Core\Theme\ThemeRegistry::activeDefault($slug)
            ) ?? [];
            $shellLogo = (array) (($activeTheme['shell'] ?? [])['logo'] ?? []);
        } catch (\Throwable $e) {
            // a missing/broken theme must not break the settings endpoint
        }
        $settings['app_name']  = trim((string) ($shellLogo['text'] ?? ''))
            ?: (trim((string) ($this->project['name'] ?? '')) ?: $slug);
        $settings['app_logo']  = trim((string) ($shellLogo['image'] ?? ''));
        echo json_encode(['success' => true, 'settings' => $settings]);
        exit;
    }

    public function saveSettings(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        $input = json_decode(file_get_contents('php://input'), true) ?? $_POST;
        $settings = $input['settings'] ?? [];
        $existing = ModuleState::load($slug, 'security');
        // MERGE, never replace. Each settings page posts only its own keys, so
        // saving one page used to delete every other page's settings from the
        // shared blob — the Database page would silently wipe login appearance,
        // registration rules and the rest.
        $settings = array_merge($existing, (array) $settings);
        if (isset($settings['social_auth'])) {
            $settings['social_auth'] = $this->preserveSocialAuthSecrets(
                (array)($existing['social_auth'] ?? []),
                (array)$settings['social_auth']
            );
        } elseif (isset($existing['social_auth'])) {
            $settings['social_auth'] = $existing['social_auth'];
        }
        // If db_auth_enabled is turned on and a table is specified, ensure the table exists
        $bridge = null;
        if (!empty($settings['db_auth_enabled']) && !empty($settings['users_table'])) {
            // Optionally create table if new flag is set
            if (!empty($settings['create_new_table'])) {
                $this->createUserTableFromDefinition($settings);
            } else {
                // "Use existing table": if it isn't literally named `users`, bridge
                // it to the auth runtime (which addresses a `users` table) with a
                // compatibility VIEW that renames the mapped columns to the logical
                // names login / register / reset / 2FA / the admin area expect.
                $bridge = $this->ensureUsersCompatView($settings);
            }
        }
        // The mapping is always persisted (so the build can still emit it); the
        // bridge result only tunes what the screen tells the developer.
        ModuleState::save($slug, 'security', $settings);
        $resp = ['success' => true, 'message' => 'Settings saved.'];
        if (is_array($bridge) && ($bridge['view'] || $bridge['added'] !== [] || !$bridge['ok'])) {
            $resp['bridge'] = $bridge;
            if (!$bridge['ok']) {
                $resp['message'] = $bridge['message'];   // surface the blocker prominently
            }
        }
        echo json_encode($resp);
        exit;
    }

    public function getSocialAuthSettings(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        $settings = ModuleState::load($slug, 'security');
        $social = array_replace_recursive($this->socialAuthDefaults(), (array)($settings['social_auth'] ?? []));
        echo json_encode([
            'success'  => true,
            'settings' => $this->redactSocialAuth($social),
            'roles'    => $this->availableNonAdminRoles($settings),
            // The exact callback each provider console must whitelist. Typing
            // these by hand is the single biggest source of setup failures.
            'callback_base' => $this->appCallbackBase($slug),
            'linked'   => $this->socialLinkedCounts(),
        ]);
        exit;
    }

    /**
     * Public URL of the generated app, used to build callback URLs.
     * Falls back to '' so the UI can say "set your app URL" instead of
     * printing a confident, wrong address.
     */
    private function appCallbackBase(string $slug): string
    {
        $out = ModuleState::load($slug, 'output');
        foreach (['baseUrl', 'base_url', 'appUrl', 'url'] as $key) {
            $v = trim((string) ($out[$key] ?? ''));
            if ($v !== '') return rtrim($v, '/');
        }
        // Deployment target is the next best guess.
        $deploy = ModuleState::load($slug, 'deploy');
        $v = trim((string) ($deploy['siteUrl'] ?? $deploy['baseUrl'] ?? ''));
        return $v !== '' ? rtrim($v, '/') : '';
    }

    /**
     * How many accounts are linked per provider, so an administrator can see
     * a provider is genuinely in use before switching it off.
     *
     * @return array<string,int>
     */
    private function socialLinkedCounts(): array
    {
        $out = [];
        try {
            if (DB::Query("SHOW TABLES LIKE 'social_auth_accounts'")->rowCount() === 0) {
                return $out;
            }
            foreach ((array) DB::FetchAll('SELECT provider, COUNT(*) AS c FROM social_auth_accounts GROUP BY provider') as $row) {
                $out[(string) ($row['provider'] ?? '')] = (int) ($row['c'] ?? 0);
            }
        } catch (\Throwable $e) {
            // a missing table must not break the settings page
        }
        return $out;
    }

    public function saveSocialAuthSettings(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        $input = json_decode(file_get_contents('php://input'), true) ?? [];
        $incoming = (array)($input['settings'] ?? []);
        $all = ModuleState::load($slug, 'security');
        $current = array_replace_recursive($this->socialAuthDefaults(), (array)($all['social_auth'] ?? []));
        $social = $this->preserveSocialAuthSecrets($current, $incoming);

        $social['enabled'] = !empty($social['enabled']);
        $social['allow_login'] = !empty($social['allow_login']);
        $social['allow_registration'] = !empty($social['allow_registration']);
        $social['auto_create_users'] = !empty($social['auto_create_users']);
        $social['link_by_verified_email'] = !empty($social['link_by_verified_email']);
        $requestedRole = preg_replace('/[^a-zA-Z0-9 _-]/', '', (string)($social['default_role'] ?? 'registered')) ?: 'registered';
        $availableRoles = $this->availableNonAdminRoles($all);
        $social['default_role'] = in_array($requestedRole, $availableRoles, true) ? $requestedRole : 'registered';
        foreach (['google', 'facebook', 'apple'] as $provider) {
            $social['providers'][$provider]['enabled'] = !empty($social['providers'][$provider]['enabled']);
            foreach ($social['providers'][$provider] as $key => $value) {
                if (is_string($value) && $key !== 'private_key') {
                    $social['providers'][$provider][$key] = trim($value);
                }
            }
        }
        $version = (string)($social['providers']['facebook']['api_version'] ?? 'v25.0');
        $social['providers']['facebook']['api_version'] = preg_match('/^v\d+\.\d+$/', $version) ? $version : 'v25.0';

        $all['social_auth'] = $social;
        ModuleState::save($slug, 'security', $all);
        echo json_encode(['success' => true, 'message' => 'Social login providers saved.', 'settings' => $this->redactSocialAuth($social)]);
        exit;
    }

    /** Create a privileged account in the configured generated-app users table. */
    public function createAdminUser(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        $settings = ModuleState::load($slug, 'security');
        if (empty($settings['db_auth_enabled'])) {
            http_response_code(400);
            echo json_encode(['success' => false, 'message' => 'Enable and save database authentication first.']);
            exit;
        }

        $table = $this->safeIdentifier((string)($settings['users_table'] ?? ''));
        $usernameField = $this->safeIdentifier((string)($settings['username_field'] ?? ''));
        $emailField = $this->safeIdentifier((string)($settings['email_field'] ?? ''));
        $passwordField = $this->safeIdentifier((string)($settings['password_field'] ?? ''));
        $roleField = $this->safeIdentifier((string)($settings['role_field'] ?? ''));
        if ($table === '' || $usernameField === '' || $emailField === '' || $passwordField === '' || $roleField === '') {
            http_response_code(400);
            echo json_encode(['success' => false, 'message' => 'Save a valid user table and map Username, Email, Password, and Role before adding an administrator.']);
            exit;
        }

        $input = json_decode(file_get_contents('php://input'), true) ?? [];
        $username = trim((string)($input['username'] ?? ''));
        $email = trim((string)($input['email'] ?? ''));
        $fullname = trim((string)($input['fullname'] ?? 'Administrator'));
        $password = (string)($input['password'] ?? '');
        if ($username === '' || !filter_var($email, FILTER_VALIDATE_EMAIL)) {
            http_response_code(422);
            echo json_encode(['success' => false, 'message' => 'A username and valid email address are required.']);
            exit;
        }
        $policy = (array)($settings['registration']['password_policy'] ?? []);
        $minimum = max(8, (int)($policy['min_length'] ?? 8));
        if (strlen($password) < $minimum) {
            http_response_code(422);
            echo json_encode(['success' => false, 'message' => "Password must be at least {$minimum} characters."]);
            exit;
        }
        if (!empty($policy['require_numbers']) && !preg_match('/\d/', $password)) {
            http_response_code(422);
            echo json_encode(['success' => false, 'message' => 'Password must include a number.']);
            exit;
        }
        if (!empty($policy['require_mixed_case']) && (!preg_match('/[a-z]/', $password) || !preg_match('/[A-Z]/', $password))) {
            http_response_code(422);
            echo json_encode(['success' => false, 'message' => 'Password must include upper- and lowercase letters.']);
            exit;
        }
        if (!empty($policy['require_special']) && !preg_match('/[^A-Za-z0-9]/', $password)) {
            http_response_code(422);
            echo json_encode(['success' => false, 'message' => 'Password must include a special character.']);
            exit;
        }

        $quotedTable = DB::QuoteIdentifier($table);
        $quotedUsername = DB::QuoteIdentifier($usernameField);
        $quotedEmail = DB::QuoteIdentifier($emailField);
        try {
            $existing = DB::FetchAll("SELECT {$quotedUsername}, {$quotedEmail} FROM {$quotedTable} WHERE {$quotedUsername} = ? OR {$quotedEmail} = ?", [$username, $email]);
            if ($existing) {
                http_response_code(409);
                echo json_encode(['success' => false, 'message' => 'That username or email already exists in the configured user table.']);
                exit;
            }

            $data = [
                $usernameField => $username,
                $emailField => $email,
                $passwordField => Security::HashPassword($password),
            ];
            $fullnameField = $this->safeIdentifier((string)($settings['fullname_field'] ?? ''));
            $statusField = $this->safeIdentifier((string)($settings['status_field'] ?? ''));
            if ($fullnameField !== '') $data[$fullnameField] = $fullname !== '' ? $fullname : 'Administrator';
            $data[$roleField] = 'superadmin';
            if ($statusField !== '') $data[$statusField] = 'active';

            if (DB::Insert($table, $data) === false) throw new \RuntimeException('The administrator could not be inserted.');
            echo json_encode([
                'success' => true,
                'message' => 'Administrator account created.',
                'login' => ['username' => $username, 'email' => $email, 'identifier_fields' => [$usernameField, $emailField]],
            ]);
        } catch (\Throwable $e) {
            error_log('Security admin provisioning failed for project ' . $slug . ': ' . $e->getMessage());
            http_response_code(400);
            echo json_encode(['success' => false, 'message' => 'Unable to create the administrator. Verify the saved table and field mappings.']);
        }
        exit;
    }

    private function socialAuthDefaults(): array
    {
        return [
            'enabled' => false,
            'allow_login' => true,
            'allow_registration' => true,
            'auto_create_users' => true,
            'link_by_verified_email' => true,
            'default_role' => 'registered',
            'providers' => [
                'google' => ['enabled' => false, 'client_id' => '', 'client_secret' => '', 'redirect_uri' => ''],
                'microsoft' => ['enabled' => false, 'client_id' => '', 'client_secret' => '', 'tenant' => 'common', 'redirect_uri' => ''],
                'apple' => ['enabled' => false, 'services_id' => '', 'team_id' => '', 'key_id' => '', 'private_key' => '', 'redirect_uri' => ''],
                'facebook' => ['enabled' => false, 'app_id' => '', 'app_secret' => '', 'api_version' => 'v25.0', 'redirect_uri' => ''],
                'github' => ['enabled' => false, 'client_id' => '', 'client_secret' => '', 'redirect_uri' => ''],
                'linkedin' => ['enabled' => false, 'client_id' => '', 'client_secret' => '', 'redirect_uri' => ''],
            ],
        ];
    }

    /** Which field holds each provider's secret. Single source for both directions. */
    private function socialSecretKeys(): array
    {
        return [
            'google' => 'client_secret', 'microsoft' => 'client_secret', 'apple' => 'private_key',
            'facebook' => 'app_secret', 'github' => 'client_secret', 'linkedin' => 'client_secret',
        ];
    }

    private function preserveSocialAuthSecrets(array $current, array $incoming): array
    {
        $merged = array_replace_recursive($current, $incoming);
        $secretKeys = $this->socialSecretKeys();
        foreach ($secretKeys as $provider => $key) {
            $value = trim((string)($incoming['providers'][$provider][$key] ?? ''));
            if ($value === '' || $value === '********') {
                $merged['providers'][$provider][$key] = (string)($current['providers'][$provider][$key] ?? '');
            }
        }
        return $merged;
    }

    private function redactSocialAuth(array $social): array
    {
        $secretKeys = $this->socialSecretKeys();
        foreach ($secretKeys as $provider => $key) {
            $value = (string)($social['providers'][$provider][$key] ?? '');
            $social['providers'][$provider][$key] = '';
            $social['providers'][$provider]['has_' . $key] = $value !== '';
        }
        return $social;
    }

    /** @return string[] Roles already used by the project, excluding privileged/admin values. */
    private function availableNonAdminRoles(array $settings): array
    {
        $roles = ['registered'];
        $table = $this->safeIdentifier((string)($settings['users_table'] ?? ''));
        $roleField = $this->safeIdentifier((string)($settings['role_field'] ?? ''));
        if ($table !== '' && $roleField !== '') {
            try {
                $column = DB::QuoteIdentifier($roleField);
                $rows = DB::FetchAll('SELECT DISTINCT ' . $column . ' AS role_name FROM ' . DB::QuoteIdentifier($table) . ' WHERE ' . $column . ' IS NOT NULL');
                foreach ($rows as $row) {
                    $role = trim((string)($row['role_name'] ?? ''));
                    if ($role !== '' && !preg_match('/admin/i', $role)) $roles[] = $role;
                }
            } catch (\Throwable) {
                // A new project may not have created/mapped the table yet.
            }
        }
        $roles = array_values(array_unique(array_filter($roles, static fn(string $role): bool => !preg_match('/admin/i', $role))));
        natcasesort($roles);
        return array_values($roles);
    }

    private function safeIdentifier(string $identifier): string
    {
        $identifier = trim($identifier);
        return preg_match('/^[A-Za-z_][A-Za-z0-9_]*$/', $identifier) ? $identifier : '';
    }

    /**
     * Create the user table named in the settings, plus any extra columns the
     * developer added on the Database page.
     *
     * createTables() only ever builds a table literally called `users`; this
     * honours the chosen name and is safe to re-run — the base table is created
     * only if absent, and each extra column is added only if it is not already
     * there (MySQL has no ADD COLUMN IF NOT EXISTS).
     */
    private function createUserTableFromDefinition(array $config): void
    {
        $table = $this->safeIdentifier((string) ($config['users_table'] ?? ''));
        if ($table === '') {
            return;
        }

        DB::Exec("
            CREATE TABLE IF NOT EXISTS `{$table}` (
                `id` INT AUTO_INCREMENT PRIMARY KEY,
                `username` VARCHAR(100) NOT NULL UNIQUE,
                `email` VARCHAR(255) NOT NULL UNIQUE,
                `password` VARCHAR(255) NOT NULL,
                `fullname` VARCHAR(255) DEFAULT NULL,
                `role` VARCHAR(50) DEFAULT 'registered',
                `status` ENUM('active','pending','inactive','banned') DEFAULT 'active',
                `totp_secret` VARCHAR(255) DEFAULT NULL,
                `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        ");

        $fields = (array) ($config['custom_fields'] ?? []);
        if ($fields === []) {
            return;
        }

        $existing = [];
        foreach ((array) DB::FetchAll("SHOW COLUMNS FROM `{$table}`") as $row) {
            $existing[strtolower((string) ($row['Field'] ?? ''))] = true;
        }

        foreach ($fields as $field) {
            $name = $this->safeIdentifier((string) ($field['name'] ?? ''));
            $type = $this->safeColumnType((string) ($field['type'] ?? ''));
            if ($name === '' || $type === '' || isset($existing[strtolower($name)])) {
                continue;
            }
            DB::Exec("ALTER TABLE `{$table}` ADD COLUMN `{$name}` {$type} NULL");
            $existing[strtolower($name)] = true;
        }
    }

    /** Logical auth field → the standard column it maps to by default. */
    private const AUTH_FIELDS = [
        'id'          => 'id',
        'username'    => 'username',
        'email'       => 'email',
        'password'    => 'password',
        'fullname'    => 'fullname',
        'role'        => 'role',
        'status'      => 'status',
        'totp_secret' => 'totp_secret',
    ];

    /** Fields a write path (register / profile / reset) inserts, so they must be REAL columns. */
    private const AUTH_WRITABLE = ['username', 'email', 'password', 'fullname', 'role', 'status'];

    /** The configured physical column for a logical field (mapping → default). */
    private function authFieldColumn(array $settings, string $logical): string
    {
        $key = $logical === 'id' ? 'id_field' : $logical . '_field';
        $v = trim((string) ($settings[$key] ?? ''));
        return $this->safeIdentifier($v !== '' ? $v : (self::AUTH_FIELDS[$logical] ?? $logical));
    }

    /** Physical columns present on a table, keyed lower-case → real case. */
    private function tableColumns(string $table): array
    {
        $have = [];
        foreach ((array) DB::FetchAll("SHOW COLUMNS FROM `{$table}`") as $row) {
            $f = (string) ($row['Field'] ?? '');
            if ($f !== '') {
                $have[strtolower($f)] = $f;
            }
        }
        return $have;
    }

    /**
     * Report, for an existing auth table, which logical fields resolve to a real
     * column and which are missing — the data the Security screen uses to offer
     * an inline "add this column" button.
     *
     * @return array<string,array{column:string,exists:bool,writable:bool}>
     */
    private function authColumnReport(array $settings): array
    {
        $table = $this->safeIdentifier((string) ($settings['users_table'] ?? ''));
        $have  = ($table !== '') ? $this->tableColumns($table) : [];
        $report = [];
        foreach (array_keys(self::AUTH_FIELDS) as $logical) {
            $col = $this->authFieldColumn($settings, $logical);
            $report[$logical] = [
                'column'   => $col,
                'exists'   => isset($have[strtolower($col)]),
                'writable' => in_array($logical, self::AUTH_WRITABLE, true),
            ];
        }
        return $report;
    }

    /**
     * Bridge a developer's existing users table to the auth runtime (which is
     * hardcoded to a table named `users` with logical column names) via a
     * compatibility VIEW. Mapped columns are exposed under their logical names;
     * unmapped/missing ones surface as NULL (readable, so a SELECT never errors —
     * only the write paths need a real column, which the Security screen's
     * "add column" flow guarantees). Idempotent. No-op when the table already IS
     * `users`.
     */
    /**
     * @return array{ok:bool,view:bool,added:list<string>,message:string}
     */
    private function ensureUsersCompatView(array $settings): array
    {
        $none = ['ok' => true, 'view' => false, 'added' => [], 'message' => ''];
        $real = $this->safeIdentifier((string) ($settings['users_table'] ?? ''));
        if ($real === '' || strtolower($real) === 'users') {
            return $none;   // the table already IS `users` — nothing to bridge
        }
        try {
            $have = $this->tableColumns($real);
            if ($have === []) {
                return ['ok' => false, 'view' => false, 'added' => [],
                    'message' => "The selected table `{$real}` was not found (or has no columns)."];
            }
            // The primary key must already exist — it can't be synthesised, and a
            // NULL-literal id would make the whole view read-only (register/reset
            // would fail with MySQL error 1471).
            $idCol = $this->authFieldColumn($settings, 'id');
            if (!isset($have[strtolower($idCol)])) {
                return ['ok' => false, 'view' => false, 'added' => [],
                    'message' => "`{$real}` has no `{$idCol}` column for the account id — map the ID field to your primary-key column."];
            }
            // Data-loss guard, checked BEFORE any change: a `users` base table
            // holding accounts blocks the view, and we must never drop it. Bail
            // out here so a blocked link leaves the developer's table untouched.
            if ($this->strayUsersBaseState() === 'has-data') {
                return ['ok' => false, 'view' => false, 'added' => [],
                    'message' => 'A `users` table that still holds accounts is blocking the link — nothing was changed. Move or remove those rows, then save again.'];
            }
            // Auth columns to synthesise when the mapping points at a column the
            // table lacks. All NULLable or defaulted, so ADD COLUMN can never fail
            // on a table that already holds rows.
            $addType = [
                'username'    => 'VARCHAR(100) NULL',
                'email'       => 'VARCHAR(255) NULL',
                'password'    => 'VARCHAR(255) NULL',
                'fullname'    => 'VARCHAR(255) NULL',
                'role'        => "VARCHAR(50) NOT NULL DEFAULT 'registered'",
                'status'      => "VARCHAR(20) NOT NULL DEFAULT 'active'",
                'totp_secret' => 'VARCHAR(255) NULL',
            ];
            $added = [];
            $select = [];
            foreach (array_keys(self::AUTH_FIELDS) as $logical) {
                $col = $this->authFieldColumn($settings, $logical);
                if (!isset($have[strtolower($col)])) {
                    if (!isset($addType[$logical])) {   // id — handled above
                        $select[] = "NULL AS `{$logical}`";
                        continue;
                    }
                    DB::Exec("ALTER TABLE `{$real}` ADD COLUMN `{$col}` {$addType[$logical]}");
                    $have[strtolower($col)] = $col;
                    $added[] = $col;
                }
                $select[] = "`{$col}` AS `{$logical}`";
            }
            // Retire an empty leftover `users` base table so the view can take
            // its name (guarded above — it has no accounts).
            $this->dropStrayUsersBaseTable();
            DB::Exec('CREATE OR REPLACE VIEW `users` AS SELECT ' . implode(', ', $select) . " FROM `{$real}`");
            $msg = "Authentication linked to `{$real}`.";
            if ($added !== []) {
                $msg .= ' Added missing column(s): ' . implode(', ', $added) . '.';
            }
            return ['ok' => true, 'view' => true, 'added' => $added, 'message' => $msg];
        } catch (\Throwable $e) {
            error_log('ensureUsersCompatView failed: ' . $e->getMessage());
            return ['ok' => false, 'view' => false, 'added' => [],
                'message' => 'Could not link the existing table: ' . $e->getMessage()];
        }
    }

    /**
     * State of any object literally named `users`: 'has-data' (a base table with
     * rows — must not be touched), 'droppable' (an empty base table), or 'none'
     * (already a view, or absent).
     */
    private function strayUsersBaseState(): string
    {
        try {
            $t = DB::FetchAll("SELECT TABLE_TYPE FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users'");
            if (strtoupper((string) ($t[0]['TABLE_TYPE'] ?? '')) !== 'BASE TABLE') {
                return 'none';
            }
            return ((int) (DB::FetchAll('SELECT COUNT(*) AS c FROM `users`')[0]['c'] ?? 0) > 0) ? 'has-data' : 'droppable';
        } catch (\Throwable $e) {
            return 'none';
        }
    }

    /**
     * Retire a stray `users` BASE TABLE so a view can take its name — but only
     * when it holds NO accounts (data-loss guard). Drops any FK that references
     * it first. Returns 'dropped' | 'has-data' | 'none'.
     */
    private function dropStrayUsersBaseTable(): string
    {
        try {
            $t = DB::FetchAll("SELECT TABLE_TYPE FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users'");
            if (strtoupper((string) ($t[0]['TABLE_TYPE'] ?? '')) !== 'BASE TABLE') {
                return 'none';
            }
            if ((int) (DB::FetchAll('SELECT COUNT(*) AS c FROM `users`')[0]['c'] ?? 0) > 0) {
                return 'has-data';
            }
            foreach ((array) DB::FetchAll(
                "SELECT TABLE_NAME, CONSTRAINT_NAME FROM information_schema.KEY_COLUMN_USAGE
                 WHERE TABLE_SCHEMA = DATABASE() AND REFERENCED_TABLE_NAME = 'users'"
            ) as $fk) {
                $tbl = $this->safeIdentifier((string) ($fk['TABLE_NAME'] ?? ''));
                $cn  = $this->safeIdentifier((string) ($fk['CONSTRAINT_NAME'] ?? ''));
                if ($tbl !== '' && $cn !== '') {
                    DB::Exec("ALTER TABLE `{$tbl}` DROP FOREIGN KEY `{$cn}`");
                }
            }
            DB::Exec('DROP TABLE `users`');
            return 'dropped';
        } catch (\Throwable $e) {
            error_log('dropStrayUsersBaseTable: ' . $e->getMessage());
            return 'none';
        }
    }

    /**
     * A column type cannot be a bound parameter, so it is matched against an
     * allow-list of shapes instead. Anything unrecognised is rejected outright
     * rather than coerced, so a malformed entry can never reach the server as
     * raw SQL.
     */
    private function safeColumnType(string $type): string
    {
        // Matched case-insensitively and returned AS TYPED: upper-casing the
        // whole string would rewrite ENUM/SET literals, so ENUM('on hold')
        // would silently become ENUM('ON HOLD') and change what the column accepts.
        $t = trim($type);
        if ($t === '') {
            return 'VARCHAR(255)';
        }
        $patterns = [
            '/^(VAR)?CHAR\(\d{1,4}\)$/i',
            '/^(TINY|SMALL|MEDIUM|BIG)?INT(\(\d{1,3}\))?( UNSIGNED)?$/i',
            '/^(TINY|MEDIUM|LONG)?TEXT$/i',
            '/^DECIMAL\(\d{1,2},\s?\d{1,2}\)$/i',
            '/^(FLOAT|DOUBLE|BOOLEAN|BOOL|JSON|DATE|DATETIME|TIMESTAMP|TIME|YEAR)$/i',
            // ENUM/SET carry literals, so the values are constrained too — word
            // characters, spaces and hyphens only, which cannot close the quote.
            '/^(ENUM|SET)\(\'[\w -]+\'(\s*,\s*\'[\w -]+\')*\)$/i',
        ];
        foreach ($patterns as $p) {
            if (preg_match($p, $t)) {
                return $t;
            }
        }
        return '';
    }

    // -------------------------------------------------------
    //  Login security (lockout, throttles, guest-only)
    // -------------------------------------------------------
    /**
     * Presentation of the sign-in page belongs to Designer → Common Pages →
     * Login; this endpoint only reports which template is active so the page
     * can show it, and returns the protection policy it does own.
     */
    public function getLoginSecurity(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        $settings = ModuleState::load($slug, 'security');

        $policy = array_merge(
            \PpPhp\Core\Auth\AuthGuard::defaults(),
            (array) ($settings['login_security'] ?? [])
        );

        echo json_encode([
            'success'  => true,
            'settings' => $policy,
            'design'   => $this->loginDesignSummary($slug),
        ]);
        exit;
    }

    public function saveLoginSecurity(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        $input = json_decode(file_get_contents('php://input'), true) ?? [];
        $incoming = (array) ($input['settings'] ?? []);

        // Clamp everything server-side: these values become the app's only
        // brute-force defence, and a zero or negative window would disable it.
        $int = static fn($v, int $d, int $min, int $max): int
            => max($min, min($max, is_numeric($v) ? (int) $v : $d));
        $clean = [
            'lockout_enabled' => !empty($incoming['lockout_enabled']),
            'max_attempts'    => $int($incoming['max_attempts'] ?? null, 5, 1, 100),
            'attempt_window'  => $int($incoming['attempt_window'] ?? null, 15, 1, 1440),
            'lockout_minutes' => $int($incoming['lockout_minutes'] ?? null, 15, 1, 1440),
            'ip_max_attempts' => $int($incoming['ip_max_attempts'] ?? null, 20, 1, 1000),
            'guest_only'      => !empty($incoming['guest_only']),
            'register_max'    => $int($incoming['register_max'] ?? null, 5, 0, 1000),
            'register_window' => $int($incoming['register_window'] ?? null, 60, 1, 1440),
            'forgot_max'      => $int($incoming['forgot_max'] ?? null, 5, 0, 1000),
            'forgot_window'   => $int($incoming['forgot_window'] ?? null, 60, 1, 1440),
            'remember_days'   => $int($incoming['remember_days'] ?? null, 30, 1, 365),
            'generic_errors'  => !empty($incoming['generic_errors']),
        ];

        $state = ModuleState::load($slug, 'security');
        $state['login_security'] = $clean;
        // The old duplicate appearance blob is retired the first time this page
        // is saved, so the build can never pick it over the Designer design.
        unset($state['login_appearance']);
        ModuleState::save($slug, 'security', $state);

        echo json_encode(['success' => true, 'settings' => $clean, 'message' => 'Login security saved.']);
        exit;
    }

    /** Which Designer template the Login common page is currently using. */
    private function loginDesignSummary(string $slug): array
    {
        $designer = ModuleState::load($slug, 'designer');
        $page = (array) (($designer['common'] ?? [])['login'] ?? []);
        $form = [];
        foreach ((array) ($page['components'] ?? []) as $c) {
            if (is_array($c) && ($c['type'] ?? '') === 'auth_form') { $form = (array) ($c['props'] ?? []); break; }
        }
        return [
            'template' => (string) ($form['template'] ?? ($page['settings']['loginTemplate'] ?? '')),
            'title'    => (string) ($form['title'] ?? ($page['title'] ?? '')),
            'subtitle' => (string) ($form['subtitle'] ?? ''),
            'social'   => array_values((array) ($form['socialProviders'] ?? [])),
            'designed' => $form !== [],
        ];
    }

    // -------------------------------------------------------
    //  Auth email templates
    // -------------------------------------------------------
    /** Stored copies merged over the shipped defaults. */
    private function authEmailState(string $slug): array
    {
        $settings = ModuleState::load($slug, 'security');
        return (array) ($settings['auth_emails'] ?? []);
    }

    private function appNameFor(string $slug): string
    {
        try {
            $theme = \PpPhp\Core\Theme\ThemeRegistry::resolvedForBuild(
                $slug, \PpPhp\Core\Theme\ThemeRegistry::activeDefault($slug)) ?? [];
            $name = trim((string) ((($theme['shell'] ?? [])['logo'] ?? [])['text'] ?? ''));
            if ($name !== '') return $name;
        } catch (\Throwable $e) {
            // fall through to the project name
        }
        return trim((string) ($this->project['name'] ?? '')) ?: $slug;
    }

    public function getAuthEmails(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        echo json_encode([
            'success'   => true,
            'templates' => \PpPhp\Core\Auth\AuthEmails::all($this->authEmailState($slug)),
            'app_name'  => $this->appNameFor($slug),
        ]);
        exit;
    }

    /** Save one template's subject/body. Content is stored verbatim. */
    public function saveAuthEmail(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        $input = json_decode(file_get_contents('php://input'), true) ?? [];
        $key = (string) ($input['key'] ?? '');
        $defaults = \PpPhp\Core\Auth\AuthEmails::defaults();
        if (!isset($defaults[$key])) {
            echo json_encode(['success' => false, 'message' => 'Unknown template.']);
            exit;
        }
        $state = ModuleState::load($slug, 'security');
        $emails = (array) ($state['auth_emails'] ?? []);
        $emails[$key] = [
            'subject' => mb_substr(trim((string) ($input['subject'] ?? '')), 0, 300),
            'body'    => (string) ($input['body'] ?? ''),
            'enabled' => !isset($emails[$key]['enabled']) || (bool) $emails[$key]['enabled'],
        ];
        $state['auth_emails'] = $emails;
        ModuleState::save($slug, 'security', $state);
        echo json_encode([
            'success'  => true,
            'template' => \PpPhp\Core\Auth\AuthEmails::all($emails)[$key],
            'message'  => 'Template saved.',
        ]);
        exit;
    }

    /** Switch one template on or off without touching its content. */
    public function toggleAuthEmail(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        $input = json_decode(file_get_contents('php://input'), true) ?? [];
        $key = (string) ($input['key'] ?? '');
        $defaults = \PpPhp\Core\Auth\AuthEmails::defaults();
        if (!isset($defaults[$key])) {
            echo json_encode(['success' => false, 'message' => 'Unknown template.']);
            exit;
        }
        $state = ModuleState::load($slug, 'security');
        $emails = (array) ($state['auth_emails'] ?? []);
        $emails[$key] = (array) ($emails[$key] ?? []);
        $emails[$key]['enabled'] = !empty($input['enabled']);
        $state['auth_emails'] = $emails;
        ModuleState::save($slug, 'security', $state);
        echo json_encode(['success' => true, 'enabled' => $emails[$key]['enabled']]);
        exit;
    }

    /** Drop the stored copy so the shipped default applies again. */
    public function resetAuthEmail(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        $input = json_decode(file_get_contents('php://input'), true) ?? [];
        $key = (string) ($input['key'] ?? '');
        $defaults = \PpPhp\Core\Auth\AuthEmails::defaults();
        if (!isset($defaults[$key])) {
            echo json_encode(['success' => false, 'message' => 'Unknown template.']);
            exit;
        }
        $state = ModuleState::load($slug, 'security');
        $emails = (array) ($state['auth_emails'] ?? []);
        // Keep the on/off switch; only the CONTENT returns to default.
        $enabled = !isset($emails[$key]['enabled']) || (bool) $emails[$key]['enabled'];
        unset($emails[$key]);
        if (!$enabled) $emails[$key] = ['enabled' => false];
        $state['auth_emails'] = $emails;
        ModuleState::save($slug, 'security', $state);
        echo json_encode([
            'success'  => true,
            'template' => \PpPhp\Core\Auth\AuthEmails::all($emails)[$key],
            'message'  => '“' . $defaults[$key]['label'] . '” restored to its default.',
        ]);
        exit;
    }

    /**
     * Render a template exactly as it will be emailed, with sample data.
     * Returns raw HTML (not JSON) so the page can drop it straight into an
     * iframe and see precisely what a recipient sees.
     */
    public function previewAuthEmail(string $slug): void
    {
        $this->initProject($slug);
        $key = (string) ($_GET['key'] ?? '');
        $defaults = \PpPhp\Core\Auth\AuthEmails::defaults();
        header('Content-Type: text/html; charset=utf-8');
        if (!isset($defaults[$key])) {
            echo 'Unknown template.';
            exit;
        }
        $vars = \PpPhp\Core\Auth\AuthEmails::sampleVars($this->appNameFor($slug));
        // Unsaved edits can be previewed by posting them through the query.
        $saved = $this->authEmailState($slug);
        if (isset($_GET['subject']) || isset($_GET['body'])) {
            $saved[$key] = [
                'subject' => (string) ($_GET['subject'] ?? ($saved[$key]['subject'] ?? $defaults[$key]['subject'])),
                'body'    => (string) ($_GET['body'] ?? ($saved[$key]['body'] ?? $defaults[$key]['body'])),
            ];
        }
        echo \PpPhp\Core\Auth\AuthEmails::compose($key, $vars, $saved)['html'];
        exit;
    }

    // -------------------------------------------------------
    //  User endpoints
    // -------------------------------------------------------
    public function getUsers(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        $settings = ModuleState::load($slug, 'security');
        $table = $this->safeIdentifier((string) ($settings['users_table'] ?? 'users')) ?: 'users';
        if (DB::Query("SHOW TABLES LIKE '" . $table . "'")->rowCount() === 0) {
            echo json_encode(['success' => false, 'message' => 'User table not found.']);
            exit;
        }
        // NEVER select * — that shipped the password hash and totp_secret to the
        // browser for every user. Return only the columns the list actually shows.
        $secretCols = ['password', 'totp_secret', 'twofa_methods', 'remember_token', 'reset_token'];
        $cols = [];
        foreach ((array) DB::FetchAll("SHOW COLUMNS FROM " . DB::QuoteIdentifier($table)) as $c) {
            $name = (string) ($c['Field'] ?? '');
            if ($name !== '' && !in_array(strtolower($name), $secretCols, true)) {
                $cols[] = DB::QuoteIdentifier($name);
            }
        }
        $select = $cols === [] ? '*' : implode(', ', $cols);
        $users = DB::FetchAll('SELECT ' . $select . ' FROM ' . DB::QuoteIdentifier($table) . ' ORDER BY id');
        echo json_encode([
            'success' => true,
            'users'   => $users,
            // Data-driven role list and status options so the modal never has to
            // hardcode values that then silently overwrite a real one on edit.
            'roles'   => $this->allProjectRoles($settings),
            'statuses'=> $this->statusOptions($settings),
        ]);
        exit;
    }

    /**
     * Every role currently in use, plus the standard tiers, deduped.
     * Unlike availableNonAdminRoles() this KEEPS admin roles — the user editor
     * must be able to show and preserve an administrator's existing role.
     *
     * @return list<string>
     */
    private function allProjectRoles(array $settings): array
    {
        $roles = ['user', 'registered', 'editor', 'manager', 'admin'];
        $table = $this->safeIdentifier((string) ($settings['users_table'] ?? ''));
        $roleField = $this->safeIdentifier((string) ($settings['role_field'] ?? ''));
        if ($table !== '' && $roleField !== '') {
            try {
                $col = DB::QuoteIdentifier($roleField);
                foreach ((array) DB::FetchAll('SELECT DISTINCT ' . $col . ' AS r FROM ' . DB::QuoteIdentifier($table)
                    . ' WHERE ' . $col . " IS NOT NULL AND " . $col . " <> ''") as $row) {
                    $roles[] = trim((string) ($row['r'] ?? ''));
                }
            } catch (\Throwable $e) {
                // fall back to the standard set
            }
        }
        $roles = array_values(array_unique(array_filter($roles, static fn($r) => $r !== '')));
        return $roles;
    }

    /**
     * The status values the mapped status column accepts. Reads the ENUM
     * definition when there is one so 'pending' (and any custom value) always
     * appears; otherwise offers the standard lifecycle set.
     *
     * @return list<string>
     */
    private function statusOptions(array $settings): array
    {
        $table = $this->safeIdentifier((string) ($settings['users_table'] ?? ''));
        $statusField = $this->safeIdentifier((string) ($settings['status_field'] ?? 'status'));
        if ($table !== '' && $statusField !== '') {
            try {
                // $statusField is validated by safeIdentifier ([A-Za-z_][A-Za-z0-9_]*).
                $col = DB::FetchAll("SHOW COLUMNS FROM " . DB::QuoteIdentifier($table)
                    . " LIKE '" . $statusField . "'")[0] ?? null;
                $type = strtolower((string) ($col['Type'] ?? ''));
                if (str_starts_with($type, 'enum') && preg_match_all("/'([^']*)'/", $type, $m)) {
                    return array_values(array_unique($m[1]));
                }
            } catch (\Throwable $e) {
                // fall through
            }
        }
        return ['active', 'pending', 'inactive', 'banned'];
    }

    public function saveUser(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        $settings = ModuleState::load($slug, 'security');
        $table = $settings['users_table'] ?? 'users';
        $usernameField = $settings['username_field'] ?? 'username';
        $emailField = $settings['email_field'] ?? 'email';
        $passwordField = $settings['password_field'] ?? 'password';
        $fullnameField = $settings['fullname_field'] ?? 'fullname';
        $roleField = $settings['role_field'] ?? 'role';
        $statusField = $settings['status_field'] ?? 'status';

        $input = json_decode(file_get_contents('php://input'), true) ?? $_POST;
        $id = (int) ($input['id'] ?? 0);
        $username = trim($input['username'] ?? '');
        $email = trim($input['email'] ?? '');
        $fullname = trim($input['fullname'] ?? '');
        $password = (string) ($input['password'] ?? '');

        if ($username === '' || $email === '') {
            http_response_code(400);
            echo json_encode(['success' => false, 'message' => 'Username and email are required.']);
            exit;
        }
        if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
            http_response_code(400);
            echo json_encode(['success' => false, 'message' => 'Enter a valid email address.']);
            exit;
        }

        // Role and status must be values the project actually recognises — the
        // browser is not trusted to send only listed ones. An unknown value
        // falls back to the safest available default rather than being written.
        $validRoles = $this->allProjectRoles($settings);
        $validStatuses = $this->statusOptions($settings);
        $role = (string) ($input['role'] ?? '');
        if (!in_array($role, $validRoles, true)) {
            $role = in_array('user', $validRoles, true) ? 'user' : ($validRoles[0] ?? 'user');
        }
        $status = (string) ($input['status'] ?? '');
        if (!in_array($status, $validStatuses, true)) {
            $status = in_array('active', $validStatuses, true) ? 'active' : ($validStatuses[0] ?? 'active');
        }

        // Apply the SAME password policy the generated app enforces, so an admin
        // cannot create a weaker password here than a user could at sign-up.
        $reg = (array) ($settings['registration'] ?? []);
        $pol = (array) ($reg['password_policy'] ?? []);
        \PpPhp\Core\Auth\AuthGuard::configure([
            'password_min_length'         => (int) ($pol['min_length'] ?? $reg['password_min_length'] ?? 8),
            'password_require_numbers'    => (bool) ($pol['require_numbers'] ?? false),
            'password_require_mixed_case' => (bool) ($pol['require_mixed_case'] ?? false),
            'password_require_special'    => (bool) ($pol['require_special'] ?? false),
            'password_block_common'       => (bool) ($pol['block_common'] ?? true),
            'password_block_identity'     => (bool) ($pol['block_identity'] ?? true),
        ]);

        // Password policy applies to admin-created accounts too, not only public
        // sign-up. A blank password on edit means "keep the current one".
        if ($password !== '') {
            $err = \PpPhp\Core\Auth\AuthGuard::passwordError($password, [
                'username' => $username, 'email' => $email, 'fullname' => $fullname,
            ]);
            if ($err !== '') {
                http_response_code(400);
                echo json_encode(['success' => false, 'message' => $err]);
                exit;
            }
        } elseif ($id === 0) {
            http_response_code(400);
            echo json_encode(['success' => false, 'message' => 'A password is required for a new user.']);
            exit;
        }

        // Check uniqueness
        if ($id) {
            $existing = DB::FetchAll("SELECT id FROM `$table` WHERE (`$usernameField` = ? OR `$emailField` = ?) AND id != ?", [$username, $email, $id]);
        } else {
            $existing = DB::FetchAll("SELECT id FROM `$table` WHERE `$usernameField` = ? OR `$emailField` = ?", [$username, $email]);
        }
        if (!empty($existing)) {
            http_response_code(400);
            echo json_encode(['success' => false, 'message' => 'Username or email already exists.']);
            exit;
        }

        $data = [
            $usernameField => $username,
            $emailField => $email,
            $fullnameField => $fullname,
            $roleField => $role,
            $statusField => $status,
        ];
        if (!empty($password)) {
            $data[$passwordField] = Security::HashPassword($password);
        }

        if ($id) {
            DB::Update($table, $data, ['id' => $id]);
            echo json_encode(['success' => true, 'message' => 'User updated.']);
        } else {
            DB::Insert($table, $data);
            echo json_encode(['success' => true, 'message' => 'User created.']);
        }
        exit;
    }

    public function deleteUser(string $slug, string $id): void
    {
        // The router passes URL segments as strings; a strict `int` param here
        // fatals under declare(strict_types=1). Cast after receiving, exactly
        // as deleteGroup() does.
        $id = (int) $id;
        header('Content-Type: application/json');
        $this->initProject($slug);
        $settings = ModuleState::load($slug, 'security');
        $table = $this->safeIdentifier((string) ($settings['users_table'] ?? 'users')) ?: 'users';
        $roleField = $this->safeIdentifier((string) ($settings['role_field'] ?? 'role')) ?: 'role';

        // Refuse to delete the last administrator — doing so locks everyone out
        // of the generated app with no way back in.
        try {
            $row = DB::FetchAll('SELECT ' . DB::QuoteIdentifier($roleField) . ' AS r FROM '
                . DB::QuoteIdentifier($table) . ' WHERE id = ?', [$id])[0] ?? null;
            $role = strtolower((string) ($row['r'] ?? ''));
            if ($row !== null && (str_contains($role, 'admin') || in_array($role, ['owner', 'superadmin'], true))) {
                $adminCount = (int) DB::FetchOne('SELECT COUNT(*) FROM ' . DB::QuoteIdentifier($table)
                    . ' WHERE LOWER(' . DB::QuoteIdentifier($roleField) . ") LIKE '%admin%'"
                    . ' OR LOWER(' . DB::QuoteIdentifier($roleField) . ") IN ('owner','superadmin')");
                if ($adminCount <= 1) {
                    http_response_code(400);
                    echo json_encode(['success' => false, 'message' => 'This is the only administrator — create another before deleting this one.']);
                    exit;
                }
            }
        } catch (\Throwable $e) {
            // if the role check fails, fall through and let the delete proceed
        }

        DB::Delete($table, ['id' => $id]);

        // Clean up everything keyed on the user, so a deleted account leaves no
        // usable tokens or dangling links behind. Each is optional — the table
        // may not exist for a given project.
        foreach (['user_groups', 'social_auth_accounts', 'account_activations',
                  'remember_tokens', 'password_resets', 'user_2fa'] as $t) {
            try {
                if (DB::Query("SHOW TABLES LIKE '" . $t . "'")->rowCount() > 0) {
                    DB::Delete($t, ['user_id' => $id]);
                }
            } catch (\Throwable $e) {
                // ignore — a leftover row is not worth failing the delete over
            }
        }
        echo json_encode(['success' => true, 'message' => 'User deleted.']);
        exit;
    }

    // -------------------------------------------------------
    //  Group endpoints (robust version)
    // -------------------------------------------------------
    public function getGroups(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        $this->ensureTablesExist();

        try {
            $stmt = DB::Query("SELECT * FROM `groups` ORDER BY id");
            $groups = $stmt->fetchAll(\PDO::FETCH_ASSOC);
            echo json_encode(['success' => true, 'groups' => $groups]);
        } catch (\Exception $e) {
            echo json_encode(['success' => false, 'message' => $e->getMessage(), 'groups' => []]);
        }
        exit;
    }

    public function saveGroup(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        $this->ensureTablesExist();

        $input = json_decode(file_get_contents('php://input'), true) ?? $_POST;
        $id = isset($input['id']) ? (int)$input['id'] : 0;
        $name = trim($input['name'] ?? '');
        $description = trim($input['description'] ?? '');
        $isAdminGroup = !empty($input['is_admin_group']) ? 1 : 0;

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


        try {
            if ($id) {
                // Update existing group
                DB::Update('groups', [
                    'name' => $name,
                    'description' => $description,
                    'is_admin_group' => $isAdminGroup,
                ], ['id' => $id]);
                echo json_encode(['success' => true, 'message' => 'Group updated.']);
            } else {
                // Insert new group – check for duplicate name
                $existing = DB::FetchOne("SELECT id FROM `groups` WHERE name = ?", [$name]);
                if ($existing) {
                    http_response_code(400);
                    echo json_encode(['success' => false, 'message' => 'A group with this name already exists.']);
                    exit;
                }
                DB::Insert('groups', [
                    'name' => $name,
                    'description' => $description,
                    'is_admin_group' => $isAdminGroup,
                ]);
                echo json_encode(['success' => true, 'message' => 'Group created.']);
            }
        } catch (\PDOException $e) {
            http_response_code(500);
            echo json_encode(['success' => false, 'message' => 'Database error: ' . $e->getMessage()]);
        }
        exit;
    }

    public function deleteGroup(string $slug, string $id): void
    {
        $id = (int)$id;
        header('Content-Type: application/json');
        $this->initProject($slug);
        $this->ensureTablesExist();

        try {
            // Fetch the group using a direct query
            $groups = DB::FetchAll("SELECT is_admin_group FROM `groups` WHERE id = $id");
            if (empty($groups)) {
                throw new \Exception('Query failed');
            }
            $group = $groups[0] ?? null;

            if (!$group) {
                http_response_code(404);
                echo json_encode(['success' => false, 'message' => 'Group not found. ID: ' . $id]);
                exit;
            }
            if ($group['is_admin_group']) {
                http_response_code(400);
                echo json_encode(['success' => false, 'message' => 'Cannot delete the admin group.']);
                exit;
            }

            // Delete the group
            DB::Delete('groups', ['id' => $id]);
            // Remove user associations
            DB::Delete('user_groups', ['group_id' => $id]);

            echo json_encode(['success' => true, 'message' => 'Group deleted.']);
        } catch (\Exception $e) {
            http_response_code(500);
            echo json_encode(['success' => false, 'message' => 'Database error: ' . $e->getMessage()]);
        }
        exit;
    }

    public function getUserGroups(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        $this->ensureTablesExist();

        try {
            $assignments = DB::FetchAll("SELECT user_id, group_id FROM user_groups");
            echo json_encode(['success' => true, 'assignments' => $assignments]);
        } catch (\Exception $e) {
            // Table might not exist or other DB error
            echo json_encode(['success' => true, 'assignments' => []]);
        }
        exit;
    }

    public function saveUserGroups(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        $this->ensureTablesExist();

        $input = json_decode(file_get_contents('php://input'), true) ?? $_POST;
        $userId = (int)($input['user_id'] ?? 0);
        $groupIds = $input['group_ids'] ?? []; // array of group IDs

        if (!$userId) {
            http_response_code(400);
            echo json_encode(['success' => false, 'message' => 'User ID required.']);
            exit;
        }

        // Delete existing assignments for this user
        DB::Delete('user_groups', ['user_id' => $userId]);
        // Insert new ones
        foreach ($groupIds as $gid) {
            DB::Insert('user_groups', ['user_id' => $userId, 'group_id' => (int)$gid]);
        }
        echo json_encode(['success' => true, 'message' => 'Group assignments saved.']);
        exit;
    }

    // -------------------------------------------------------
    //  Permissions endpoints 
    // -------------------------------------------------------
    public function getPermissions(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        $this->ensureTablesExist();

        // Get all groups
        $groups = DB::FetchAll("SELECT id, name, description, is_admin_group FROM `groups` ORDER BY id");

        // Group permissions apply to the user's own business tables. Hide the
        // platform/system tables via the shared SystemTables classifier so the
        // matrix stays clean (one source of truth — no private exclude list).
        $allTables = DB::Query("SHOW TABLES")->fetchAll(\PDO::FETCH_COLUMN);
        $tables = \PpPhp\Core\SystemTables::projectOnly($allTables);

        // Get existing group permissions (table-level)
        $groupPermissions = DB::FetchAll("SELECT group_id, table_name, perm_add, perm_edit, perm_delete, perm_view, perm_export, perm_import, perm_admin FROM group_permissions");

        // Get existing page permissions
        $pagePermissions = DB::FetchAll("SELECT group_id, page_name, allowed FROM page_permissions");

        // Page types
        $pageTypes = ['list', 'add', 'edit', 'view', 'print', 'export', 'import', 'dashboard'];

        $security = ModuleState::load($slug, 'security');
        $mode = (($security['permissions'] ?? [])['mode'] ?? 'off');
        echo json_encode([
            'success' => true,
            'mode'    => in_array($mode, ['off', 'static', 'dynamic'], true) ? $mode : 'off',
            'tables' => $tables,
            'groups' => $groups,
            'groupPermissions' => $groupPermissions,
            'pagePermissions' => $pagePermissions,
            'pageTypes' => $pageTypes,
        ]);
        exit;
    }

    /** Off / Static / Dynamic — the authorization strategy for the built app. */
    public function savePermissionMode(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        $input = json_decode(file_get_contents('php://input'), true) ?? [];
        $mode = (string) ($input['mode'] ?? 'off');
        if (!in_array($mode, ['off', 'static', 'dynamic'], true)) {
            echo json_encode(['success' => false, 'message' => 'Unknown permission mode.']);
            exit;
        }
        // Dynamic needs the permission tables to exist in the project DB so the
        // generated app can read/write them live; create them now if absent.
        if ($mode !== 'off') {
            $this->ensureTablesExist();
        }
        $state = ModuleState::load($slug, 'security');
        $state['permissions'] = array_merge((array) ($state['permissions'] ?? []), ['mode' => $mode]);
        ModuleState::save($slug, 'security', $state);
        echo json_encode(['success' => true, 'mode' => $mode, 'message' => 'Permission mode saved.']);
        exit;
    }

    public function saveGroupPermissions(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        $this->ensureTablesExist();

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

        if (empty($permissions)) {
            echo json_encode(['success' => false, 'message' => 'No permissions data provided.']);
            exit;
        }

        foreach ($permissions as $perm) {
            $groupId = (int)$perm['group_id'];
            $tableName = $perm['table_name'];
            $existing = DB::FetchOne("SELECT id FROM group_permissions WHERE group_id = ? AND table_name = ?", [$groupId, $tableName]);
            $data = [
                'group_id' => $groupId,
                'table_name' => $tableName,
                'perm_add' => (int)($perm['perm_add'] ?? 0),
                'perm_edit' => (int)($perm['perm_edit'] ?? 0),
                'perm_delete' => (int)($perm['perm_delete'] ?? 0),
                'perm_view' => (int)($perm['perm_view'] ?? 0),
                'perm_export' => (int)($perm['perm_export'] ?? 0),
                'perm_import' => (int)($perm['perm_import'] ?? 0),
                'perm_admin' => (int)($perm['perm_admin'] ?? 0)
            ];
            if ($existing) {
                DB::Update('group_permissions', $data, ['id' => $existing['id']]);
            } else {
                DB::Insert('group_permissions', $data);
            }
        }
        echo json_encode(['success' => true, 'message' => 'Group permissions saved.']);
        exit;
    }

    public function savePagePermissions(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        $this->ensureTablesExist();

        $input = json_decode(file_get_contents('php://input'), true) ?? [];
        $pagePerms = $input['page_permissions'] ?? [];

        if (empty($pagePerms)) {
            echo json_encode(['success' => false, 'message' => 'No page permissions data provided.']);
            exit;
        }

        foreach ($pagePerms as $perm) {
            $groupId = (int)$perm['group_id'];
            $pageName = $perm['page_name'];
            $allowed = (int)($perm['allowed'] ?? 0);
            $existing = DB::FetchOne("SELECT id FROM page_permissions WHERE group_id = ? AND page_name = ?", [$groupId, $pageName]);
            if ($existing) {
                DB::Update('page_permissions', ['allowed' => $allowed], ['id' => $existing['id']]);
            } else {
                DB::Insert('page_permissions', ['group_id' => $groupId, 'page_name' => $pageName, 'allowed' => $allowed]);
            }
        }
        echo json_encode(['success' => true, 'message' => 'Page permissions saved.']);
        exit;
    }


    // Get all row security rules for a project
    public function getRowSecurityRules(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        $this->ensureTablesExist();

        $rules = DB::FetchAll("SELECT * FROM row_security_rules");
        echo json_encode(['success' => true, 'rules' => $rules]);
        exit;
    }

    // Save row security rules (bulk update)
    public function saveRowSecurityRules(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        $this->ensureTablesExist();

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

        foreach ($rules as $rule) {
            $tableName = $rule['table_name'];
            $groupId = (int)$rule['group_id'];
            $ruleType = $rule['rule_type'] ?? 'owner_column';

            // "none" clears any rule for this table/group (the group falls back to
            // seeing every row it has table permission to view).
            if ($ruleType === 'none') {
                DB::Delete('row_security_rules', ['table_name' => $tableName, 'group_id' => $groupId]);
                continue;
            }

            $ownerColumn = $rule['owner_column'] ?? null;
            $conditionJson = isset($rule['condition']) ? json_encode($rule['condition']) : null;

            $existing = DB::FetchOne("SELECT id FROM row_security_rules WHERE table_name = ? AND group_id = ?", [$tableName, $groupId]);
            if ($existing) {
                DB::Update('row_security_rules', [
                    'rule_type' => $ruleType,
                    'owner_column' => $ownerColumn,
                    'condition_json' => $conditionJson
                ], ['id' => $existing['id']]);
            } else {
                DB::Insert('row_security_rules', [
                    'table_name' => $tableName,
                    'group_id' => $groupId,
                    'rule_type' => $ruleType,
                    'owner_column' => $ownerColumn,
                    'condition_json' => $conditionJson
                ]);
            }
        }
        echo json_encode(['success' => true, 'message' => 'Row security rules saved.']);
        exit;
    }


    // Get all field encryption settings for the project
    public function getFieldEncryption(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        $this->ensureTablesExist();

        $settings = DB::FetchAll("SELECT * FROM field_encryption ORDER BY table_name, column_name");
        echo json_encode(['success' => true, 'settings' => $settings]);
        exit;
    }

    // Save field encryption settings (bulk update)
    public function saveFieldEncryption(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        $this->ensureTablesExist();

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

        foreach ($settings as $item) {
            $tableName = $item['table_name'];
            $columnName = $item['column_name'];
            $enabled = (int)($item['enabled'] ?? 0);
            $existing = DB::FetchOne("SELECT id FROM field_encryption WHERE table_name = ? AND column_name = ?", [$tableName, $columnName]);
            if ($existing) {
                DB::Update('field_encryption', ['enabled' => $enabled], ['id' => $existing['id']]);
            } else {
                DB::Insert('field_encryption', ['table_name' => $tableName, 'column_name' => $columnName, 'enabled' => $enabled]);
            }
        }
        echo json_encode(['success' => true, 'message' => 'Field encryption settings saved.']);
        exit;
    }

    // Encrypt existing plaintext rows for the currently-enabled columns, using the
    // project's persisted key (the same key the build bakes into the app).
    public function migrateFieldEncryption(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        $this->ensureTablesExist();

        try {
            $owner = \PpPhp\Core\ProjectLocator::ownerUsername($slug);
            $key   = \PpPhp\Core\Crypto\ProjectKey::get($owner, $slug);
            if (!\PpPhp\Core\Auth\FieldCrypto::useKey($key)) {
                echo json_encode(['success' => false, 'message' => 'Could not load the encryption key.']);
                exit;
            }
            $res = \PpPhp\Core\Auth\FieldCrypto::migrate();
            if (empty($res['ok'])) {
                echo json_encode(['success' => false, 'message' => $res['error'] ?? 'Migration failed.']);
                exit;
            }
            $n = (int) ($res['encrypted'] ?? 0);
            echo json_encode(['success' => true, 'encrypted' => $n, 'tables' => $res['tables'] ?? [],
                'message' => $n > 0 ? "Encrypted {$n} value(s) in existing rows." : 'Nothing to encrypt — existing rows are already protected.']);
        } catch (\Throwable $e) {
            http_response_code(500);
            echo json_encode(['success' => false, 'message' => $e->getMessage()]);
        }
        exit;
    }




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

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

    private function ensureTablesExist(): void
    {
        if ($this->tablesInitialized) return;
        $result = DB::Query("SHOW TABLES LIKE 'users'");
        if ($result->rowCount() === 0) {
            $this->createTables();
        }
        $this->tablesInitialized = true;
    }

    private function createTables(): void
    {
        // Users table
        DB::Exec("
            CREATE TABLE IF NOT EXISTS `users` (
                `id` INT AUTO_INCREMENT PRIMARY KEY,
                `username` VARCHAR(100) NOT NULL UNIQUE,
                `email` VARCHAR(255) NOT NULL UNIQUE,
                `password` VARCHAR(255) NOT NULL,
                `fullname` VARCHAR(255) DEFAULT NULL,
                `role` VARCHAR(50) DEFAULT 'registered',
                `status` ENUM('active','pending','inactive','banned') DEFAULT 'active',
                `totp_secret` VARCHAR(255) DEFAULT NULL,
                `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        ");

        // Groups table
        DB::Exec("
            CREATE TABLE IF NOT EXISTS `groups` (
                `id` INT AUTO_INCREMENT PRIMARY KEY,
                `name` VARCHAR(100) NOT NULL UNIQUE,
                `description` TEXT,
                `is_admin_group` TINYINT(1) DEFAULT 0,
                `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        ");

        // User-Groups junction
        DB::Exec("
            CREATE TABLE IF NOT EXISTS `user_groups` (
                `user_id` INT NOT NULL,
                `group_id` INT NOT NULL,
                PRIMARY KEY (`user_id`, `group_id`),
                FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
                FOREIGN KEY (`group_id`) REFERENCES `groups`(`id`) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        ");

        // Group permissions (table-level)
        DB::Exec("
            CREATE TABLE IF NOT EXISTS `group_permissions` (
                `id` INT AUTO_INCREMENT PRIMARY KEY,
                `group_id` INT NOT NULL,
                `table_name` VARCHAR(100) NOT NULL,
                `perm_add` TINYINT(1) DEFAULT 0,
                `perm_edit` TINYINT(1) DEFAULT 0,
                `perm_delete` TINYINT(1) DEFAULT 0,
                `perm_view` TINYINT(1) DEFAULT 0,
                `perm_export` TINYINT(1) DEFAULT 0,
                `perm_import` TINYINT(1) DEFAULT 0,
                `perm_admin` TINYINT(1) DEFAULT 0,
                UNIQUE KEY `group_table` (`group_id`, `table_name`),
                FOREIGN KEY (`group_id`) REFERENCES `groups`(`id`) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        ");

        // Page permissions
        DB::Exec("
            CREATE TABLE IF NOT EXISTS `page_permissions` (
                `id` INT AUTO_INCREMENT PRIMARY KEY,
                `group_id` INT NOT NULL,
                `page_name` VARCHAR(100) NOT NULL,
                `allowed` TINYINT(1) DEFAULT 1,
                UNIQUE KEY `group_page` (`group_id`, `page_name`),
                FOREIGN KEY (`group_id`) REFERENCES `groups`(`id`) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        ");

        // User 2FA table. One row per user; each user runs exactly ONE method
        // (authenticator / email / SMS) at a time — `method` holds which, `secret`
        // the TOTP seed (authenticator only, nullable), `backup_codes` the hashed
        // single-use recovery codes. Mirrors core/Auth/TwoFactor::ensureTable().
        DB::Exec("
            CREATE TABLE IF NOT EXISTS `user_2fa` (
                `user_id` INT NOT NULL PRIMARY KEY,
                `method` VARCHAR(10) DEFAULT NULL,
                `secret` VARCHAR(255) DEFAULT NULL,
                `enabled` TINYINT(1) NOT NULL DEFAULT 0,
                `backup_codes` TEXT DEFAULT NULL,
                `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        ");
        // Older installs created this table without `method`/`backup_codes` (and
        // with a NOT NULL `secret`); bring them up to the single-method schema.
        foreach ([
            'ADD COLUMN `method` VARCHAR(10) DEFAULT NULL',
            'ADD COLUMN `backup_codes` TEXT DEFAULT NULL',
            'MODIFY COLUMN `secret` VARCHAR(255) DEFAULT NULL',
        ] as $alter) {
            try { DB::Exec('ALTER TABLE `user_2fa` ' . $alter); } catch (\Throwable $e) { /* column already present */ }
        }
        // add new filed 'twofa_methods' in users table to store 2fa method
        DB::Exec("ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `twofa_methods` TEXT DEFAULT NULL COMMENT 'JSON string for 2FA methods'");

        // NOTE: the Audit Trail (`audit_log`) and Record Locks (`record_locks`) tables
        // are NOT created here. They are owned by Misc → Advanced (the single source of
        // truth): Misc creates them, with its own schema and configurable names, only
        // when the admin enables them, and the built app writes to them. The Security
        // console's Audit & Locking screen just READS whatever Misc configured. Creating
        // them here with a different schema would shadow the Misc schema (breaking the
        // built app's INSERT … ON DUPLICATE KEY lock upsert), so we deliberately don't.

        // Remember tokens
        DB::Exec("
            CREATE TABLE IF NOT EXISTS `remember_tokens` (
                `id` INT AUTO_INCREMENT PRIMARY KEY,
                `user_id` INT NOT NULL,
                `token` VARCHAR(255) NOT NULL,
                `expires_at` DATETIME NOT NULL,
                `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        ");

        // Seed groups idempotently. Administrator accounts are created
        // explicitly from Security → Database so no known default password is
        // ever placed in a generated application database.
        if (!DB::FetchOne("SELECT id FROM groups WHERE name = ?", ['Administrators'])) {
            DB::Insert('groups', [
                'name' => 'Administrators',
                'description' => 'Full system access',
                'is_admin_group' => 1
            ]);
        }
        if (!DB::FetchOne("SELECT id FROM groups WHERE name = ?", ['Registered Users'])) {
            DB::Insert('groups', [
                'name' => 'Registered Users',
                'description' => 'Default authenticated users',
                'is_admin_group' => 0
            ]);
        }

        // create table row_security_rules to store per‑table, per‑group rules using a JSON condition format
        DB::Exec("
            CREATE TABLE IF NOT EXISTS `row_security_rules` (
                `id` INT AUTO_INCREMENT PRIMARY KEY,
                `table_name` VARCHAR(100) NOT NULL,
                `group_id` INT NOT NULL,
                `rule_type` ENUM('owner_column', 'custom_condition') DEFAULT 'owner_column',
                `owner_column` VARCHAR(100) DEFAULT NULL,
                `condition_json` JSON DEFAULT NULL,  -- stores {field, operator, value}
                `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                UNIQUE KEY `table_group` (`table_name`, `group_id`),
                FOREIGN KEY (`group_id`) REFERENCES `groups`(`id`) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
        ");


        // field_encryption - encrypt sensitive column data (like SSN, credit card numbers, API keys) at the application level.
        DB::Exec("
            CREATE TABLE IF NOT EXISTS `field_encryption` (
                `id` INT AUTO_INCREMENT PRIMARY KEY,
                `table_name` VARCHAR(100) NOT NULL,
                `column_name` VARCHAR(100) NOT NULL,
                `enabled` TINYINT(1) DEFAULT 0,
                `encryption_key_id` INT DEFAULT NULL,
                `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                UNIQUE KEY `table_column` (`table_name`, `column_name`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        ");

        // (record_locks intentionally not created here — see note above; Misc owns it.)

        // -- SESSION CONTROL
        DB::Exec("
            CREATE TABLE IF NOT EXISTS `user_sessions` (
                `id` INT AUTO_INCREMENT PRIMARY KEY,
                `user_id` INT NOT NULL,
                `session_id` VARCHAR(255) NOT NULL,
                `ip_address` VARCHAR(45) DEFAULT NULL,
                `user_agent` TEXT DEFAULT NULL,
                `last_activity` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_user (`user_id`),
                INDEX idx_session (`session_id`),
                INDEX idx_last_activity (`last_activity`),
                FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
        ");
    }


    // Add these methods inside the class

    // Get list of all tables in the project database (excluding security tables)
    public function getTables(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        $rows = DB::Query("SHOW TABLES")->fetchAll(\PDO::FETCH_NUM);
        $all = array_map(static fn($r) => $r[0], $rows);
        $tables = [];
        // Only exclude internal security tables that are created by this module
        $exclude = ['groups', 'user_groups', 'group_permissions', 'page_permissions', 'user_2fa', 'audit_log', 'record_locks', 'remember_tokens'];
        foreach ($all as $table) {
            if (!in_array($table, $exclude, true)) {
                $tables[] = $table;
            }
        }
        // `tables` stays backward-compatible (Database Auth / Encryption pages rely
        // on it). `allTables` is the unfiltered list and `isOwner` lets callers such
        // as Row-Level Security apply their own policy via the shared SystemTables
        // classifier (window.PP_SYSTEM) instead of a private hard-coded list.
        echo json_encode([
            'success'   => true,
            'tables'    => $tables,
            'allTables' => $all,
            'isOwner'   => \PpPhp\Core\Security::IsOwner(),
        ]);
        exit;
    }

    // Get columns of a given table
    public function getTableColumns(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        $input = json_decode(file_get_contents('php://input'), true) ?? $_POST;
        $table = $input['table'] ?? '';
        if (!$table) {
            http_response_code(400);
            echo json_encode(['success' => false, 'message' => 'Table name required']);
            exit;
        }
        $columns = DB::FetchAll("SHOW COLUMNS FROM `$table`");
        $columnNames = array_column($columns, 'Field');
        echo json_encode(['success' => true, 'columns' => $columnNames]);
        exit;
    }


    // Delete/Drop all security-related tables (use with caution)
    public function dropTables(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        $tables = ['users', 'groups', 'user_groups', 'group_permissions', 'page_permissions', 'user_2fa', 'remember_tokens'];
        foreach ($tables as $table) {
            DB::Exec("DROP TABLE IF EXISTS `$table`");
        }
        // Remove security settings
        ModuleState::save($slug, 'security', ['db_auth_enabled' => false]);
        echo json_encode(['success' => true, 'message' => 'Security tables dropped.']);
        exit;
    }



    // -------------------------------------------------------
    //  Two-Factor Authentication (TOTP)
    // -------------------------------------------------------
    public function getTwoFactorStatus(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        Security::RequireAuth();
        $userId = Security::CurrentUserData()['id'];

        $status = DB::FetchOne("SELECT enabled, secret FROM user_2fa WHERE user_id = ?", [$userId]);
        if (!$status) {
            echo json_encode(['success' => true, 'enabled' => false, 'secret' => null]);
            exit;
        }
        echo json_encode(['success' => true, 'enabled' => (bool)$status['enabled'], 'secret' => $status['secret']]);
        exit;
    }

    public function enableTwoFactor(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        Security::RequireAuth();
        $userId = Security::CurrentUserData()['id'];
        $input = json_decode(file_get_contents('php://input'), true) ?? $_POST;
        $code = $input['code'] ?? '';
        $secret = $input['secret'] ?? '';

        if (!$secret || !$code) {
            http_response_code(400);
            echo json_encode(['success' => false, 'message' => 'Secret and verification code are required.']);
            exit;
        }

        $totp = Security::TOTP();
        if (!$totp->Verify($secret, $code)) {
            echo json_encode(['success' => false, 'message' => 'Invalid verification code.']);
            exit;
        }

        // Save or update
        $exists = DB::FetchOne("SELECT user_id FROM user_2fa WHERE user_id = ?", [$userId]);
        if ($exists) {
            DB::Update('user_2fa', ['secret' => $secret, 'enabled' => 1], ['user_id' => $userId]);
        } else {
            DB::Insert('user_2fa', ['user_id' => $userId, 'secret' => $secret, 'enabled' => 1]);
        }

        echo json_encode(['success' => true, 'message' => 'Two-factor authentication enabled.']);
        exit;
    }

    public function disableTwoFactor(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        Security::RequireAuth();
        $userId = Security::CurrentUserData()['id'];

        DB::Delete('user_2fa', ['user_id' => $userId]);
        echo json_encode(['success' => true, 'message' => 'Two-factor authentication disabled.']);
        exit;
    }

    public function generateTwoFactorSecret(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        Security::RequireAuth();
        $username = Security::CurrentUsername();

        $secret = Security::TOTP()->GenerateSecret();
        $qrCodeUrl = Security::TOTP()->GetQRCodeUrl($username, $secret, 'PPPHP');

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


    // Get global 2FA settings (admin)
    public function getTwoFactorSettings(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        $settings = ModuleState::load($slug, 'security');
        echo json_encode(['success' => true, 'settings' => self::normalizeTwoFactor($settings['twofactor'] ?? [])]);
        exit;
    }

    // Save global 2FA settings
    public function saveTwoFactorSettings(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        $input = json_decode(file_get_contents('php://input'), true) ?? [];
        $state = ModuleState::load($slug, 'security');
        // Normalise before persisting so a scalar can never grow into an array
        // (the historic array_merge_recursive bug that corrupted `enabled`).
        $state['twofactor'] = self::normalizeTwoFactor($input['settings'] ?? []);
        ModuleState::save($slug, 'security', $state);
        echo json_encode(['success' => true, 'message' => '2FA settings saved.']);
        exit;
    }

    /**
     * Clean, canonical 2FA settings: `enabled` is always a real boolean and the
     * per-method scalars are strings — repairing any earlier corruption where a
     * value became a nested array like [false, false, true].
     *
     * @param array<string,mixed> $twofa
     * @return array<string,mixed>
     */
    public static function normalizeTwoFactor(array $twofa): array
    {
        $bool = static function ($v): bool { while (is_array($v)) { $v = end($v); } return (bool) $v; };
        $str  = static function ($v, string $d): string { while (is_array($v)) { $v = end($v); } $v = (string) $v; return $v !== '' ? $v : $d; };
        $m = (array) ($twofa['methods'] ?? []);
        return [
            'remember_machine' => $bool($twofa['remember_machine'] ?? true),
            'methods' => [
                'totp'  => ['enabled' => $bool($m['totp']['enabled'] ?? true)],
                'email' => [
                    'enabled'     => $bool($m['email']['enabled'] ?? false),
                    'email_field' => $str($m['email']['email_field'] ?? 'email', 'email'),
                    'message'     => $str($m['email']['message'] ?? '', 'Your verification code is: {code}'),
                ],
                'sms' => [
                    'enabled'     => $bool($m['sms']['enabled'] ?? false),
                    'phone_field' => $str($m['sms']['phone_field'] ?? 'phone', 'phone'),
                    'message'     => $str($m['sms']['message'] ?? '', 'Your code: {code}'),
                ],
            ],
        ];
    }

    // get current user with user's role
    public function getCurrentUser(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        $user = Security::CurrentUserData();
        echo json_encode([
            'success' => true,
            'user' => [
                'id' => $user['id'],
                'username' => $user['username'],
                'role' => $user['role'],
                'is_admin' => ($user['role'] === 'superadmin')
            ]
        ]);
        exit;
    }


    // Send 2FA code via email or SMS
    public function sendTwoFactorCode(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        $input = json_decode(file_get_contents('php://input'), true) ?? [];
        $method = $input['method'] ?? '';
        $userId = Security::CurrentUserData()['id'];
        $settings = ModuleState::load($slug, 'security');
        $twofa = $settings['twofactor'] ?? [];

        if ($method === 'email') {
            // Get configured email column
            $emailField = $twofa['methods']['email']['email_field'] ?? 'email';
            if (is_array($emailField)) {
                $emailField = $emailField[0] ?? 'email';
            }
            // Validate that column exists in users table
            $columns = DB::FetchAll("SHOW COLUMNS FROM users");
            $columnNames = array_column($columns, 'Field');
            if (!in_array($emailField, $columnNames)) {
                error_log("2FA: Configured email field '$emailField' does not exist in users table. Falling back to 'email'.");
                $emailField = 'email';
            }
            $user = DB::FetchOne("SELECT id, `$emailField` as email FROM users WHERE id = ?", [$userId]);
            if (!$user || empty($user['email'])) {
                echo json_encode(['success' => false, 'message' => "Your user record has no email address in column '$emailField'. Please update your profile."]);
                exit;
            }
            $code = rand(100000, 999999);
            $_SESSION['2fa_code'] = (string) $code;
            $_SESSION['2fa_code_expires'] = time() + 300;
            $messageTemplate = $twofa['methods']['email']['message'] ?? 'Your verification code is: {code}';
            if (is_array($messageTemplate)) {
                $messageTemplate = $messageTemplate[0] ?? 'Your verification code is: {code}';
            }
            $message = str_replace('{code}', (string) $code, (string) $messageTemplate);
            $subject = 'Your 2FA Code';
            $headers = 'From: noreply@' . $_SERVER['HTTP_HOST'];
            $mailSent = mail($user['email'], $subject, $message, $headers);
            if (!$mailSent) {
                error_log("2FA email failed to send to {$user['email']}");
                echo json_encode(['success' => false, 'message' => 'Failed to send email. Check server mail configuration.']);
                exit;
            }
            echo json_encode(['success' => true, 'message' => 'Code sent to your email.']);
        } elseif ($method === 'sms') {
            echo json_encode(['success' => false, 'message' => 'SMS 2FA is not yet implemented.']);
        } else {
            echo json_encode(['success' => false, 'message' => 'Invalid method.']);
        }
        exit;
    }


    // Verify 2FA code
    public function verifyTwoFactorCode(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        $input = json_decode(file_get_contents('php://input'), true) ?? [];
        $method = $input['method'] ?? '';
        $code = $input['code'] ?? '';
        $userId = Security::CurrentUserData()['id'];

        if (!isset($_SESSION['2fa_code']) || !isset($_SESSION['2fa_code_expires']) || time() > $_SESSION['2fa_code_expires']) {
            echo json_encode(['success' => false, 'message' => 'Code expired or not requested.']);
            exit;
        }
        if ($_SESSION['2fa_code'] != $code) {
            echo json_encode(['success' => false, 'message' => 'Invalid code.']);
            exit;
        }
        // Code valid – enable method for user
        $user = DB::FetchOne("SELECT twofa_methods FROM users WHERE id = ?", [$userId]);
        $methods = $user['twofa_methods'] ? json_decode($user['twofa_methods'], true) : [];
        $methods[$method] = true;
        DB::Update('users', ['twofa_methods' => json_encode($methods)], ['id' => $userId]);
        unset($_SESSION['2fa_code'], $_SESSION['2fa_code_expires']);
        echo json_encode(['success' => true, 'message' => ucfirst($method) . ' 2FA enabled.']);
        exit;
    }


    public function disableTwoFactorMethod(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        $input = json_decode(file_get_contents('php://input'), true) ?? [];
        $method = $input['method'] ?? '';
        $userId = Security::CurrentUserData()['id'];
        $user = DB::FetchOne("SELECT twofa_methods FROM users WHERE id = ?", [$userId]);
        $methods = $user['twofa_methods'] ? json_decode($user['twofa_methods'], true) : [];
        if (isset($methods[$method])) {
            unset($methods[$method]);
            DB::Update('users', ['twofa_methods' => json_encode($methods)], ['id' => $userId]);
            echo json_encode(['success' => true, 'message' => ucfirst($method) . ' 2FA disabled.']);
        } else {
            echo json_encode(['success' => false, 'message' => 'Method not enabled.']);
        }
        exit;
    }


    public function getUserTwoFactorMethods(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        $userId = Security::CurrentUserData()['id'];
        $user = DB::FetchOne("SELECT twofa_methods FROM users WHERE id = ?", [$userId]);
        $methods = $user['twofa_methods'] ? json_decode($user['twofa_methods'], true) : [];
        echo json_encode(['success' => true, 'methods' => $methods]);
        exit;
    }



    // -------------------------------------------------------
    //  Registration & Password Settings
    // -------------------------------------------------------
    public function getRegistrationSettings(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        $settings = ModuleState::load($slug, 'security');
        // Shape matches exactly what the page saves and what
        // BuildWriter::authPolicy() reads, so nothing is dropped in between.
        $defaults = [
            'allow_registration'    => false,
            'require_username'      => true,
            'require_email'         => true,
            'require_fullname'      => false,
            'default_role'          => 'user',
            'email_domain_mode'     => 'any',
            'email_domains'         => '',
            'bot_trap'              => true,
            'min_fill_seconds'      => 2,
            'enable_password_reset' => true,
            'activation_mode'       => 'none',
            'activation_hours'      => 24,
            'admin_email'           => '',
            'password_policy'       => [
                'min_length'        => 8,
                'max_length'        => 200,
                'require_numbers'   => false,
                'require_mixed_case'=> false,
                'require_special'   => false,
                'block_common'      => true,
                'block_identity'    => true,
                'hash_algorithm'    => 'bcrypt',
            ],
            'password_reset'        => ['token_expiry_hours' => 24],
        ];
        $reg = (array) ($settings['registration'] ?? []);
        $reg = array_merge($defaults, $reg);
        // merge one level deeper so a partially-saved policy keeps its defaults
        $reg['password_policy'] = array_merge($defaults['password_policy'], (array) ($reg['password_policy'] ?? []));
        $reg['password_reset']  = array_merge($defaults['password_reset'],  (array) ($reg['password_reset'] ?? []));
        echo json_encode(['success' => true, 'settings' => $reg]);
        exit;
    }

    public function saveRegistrationSettings(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        $input = json_decode(file_get_contents('php://input'), true) ?? $_POST;
        $in = (array) ($input['settings'] ?? []);
        $pol = (array) ($in['password_policy'] ?? []);
        $int = static fn($v, int $d, int $min, int $max): int
            => max($min, min($max, is_numeric($v) ? (int) $v : $d));

        $minLen = $int($pol['min_length'] ?? null, 8, 4, 128);
        $reg = [
            'allow_registration'    => !empty($in['allow_registration']),
            'require_username'      => !empty($in['require_username']),
            'require_email'         => !empty($in['require_email']),
            'require_fullname'      => !empty($in['require_fullname']),
            'default_role'          => preg_match('/^[A-Za-z_][A-Za-z0-9_-]*$/', (string) ($in['default_role'] ?? ''))
                                        ? (string) $in['default_role'] : 'user',
            'email_domain_mode'     => in_array($in['email_domain_mode'] ?? 'any', ['any', 'allow', 'block'], true)
                                        ? (string) $in['email_domain_mode'] : 'any',
            'email_domains'         => mb_substr(trim((string) ($in['email_domains'] ?? '')), 0, 2000),
            'bot_trap'              => !empty($in['bot_trap']),
            'min_fill_seconds'      => $int($in['min_fill_seconds'] ?? null, 2, 0, 60),
            'enable_password_reset' => !empty($in['enable_password_reset']),
            'activation_mode'       => in_array($in['activation_mode'] ?? 'none', ['none', 'email', 'admin'], true)
                                        ? (string) $in['activation_mode'] : 'none',
            'activation_hours'      => $int($in['activation_hours'] ?? null, 24, 1, 720),
            'admin_email'           => filter_var((string) ($in['admin_email'] ?? ''), FILTER_VALIDATE_EMAIL) ?: '',
            'password_policy'       => [
                'min_length'         => $minLen,
                // never below the minimum, or every password would be rejected
                'max_length'         => max($minLen, $int($pol['max_length'] ?? null, 200, 20, 4096)),
                'require_numbers'    => !empty($pol['require_numbers']),
                'require_mixed_case' => !empty($pol['require_mixed_case']),
                'require_special'    => !empty($pol['require_special']),
                'block_common'       => !empty($pol['block_common']),
                'block_identity'     => !empty($pol['block_identity']),
                'hash_algorithm'     => 'bcrypt',
            ],
            'password_reset'        => [
                'token_expiry_hours' => $int(($in['password_reset']['token_expiry_hours'] ?? null), 24, 1, 720),
            ],
        ];

        $state = ModuleState::load($slug, 'security');
        $state['registration'] = $reg;
        ModuleState::save($slug, 'security', $state);

        echo json_encode(['success' => true, 'settings' => $reg, 'message' => 'Registration settings saved.']);
        exit;
    }


    // -------------------------------------------------------
    //  Audit Log  &  Record Locks
    //  Misc → Advanced is the SINGLE SOURCE OF TRUTH: it owns the tables, their
    //  (configurable) names and the enabled flags. These console screens only READ
    //  what Misc configured and the built app populated — they never create tables.
    // -------------------------------------------------------

    /** Resolved Audit/Lock config from Misc → Advanced (sanitized names + flags). */
    private function miscAuditConfig(string $slug): array
    {
        $adv   = (array) (ModuleState::load($slug, 'misc')['advanced'] ?? []);
        $clean = static fn($v, $d) => (preg_replace('/[^A-Za-z0-9_]/', '', (string) $v) ?: $d);
        $mode  = static fn($v) => in_array($v, ['static', 'dynamic'], true) ? $v : 'static';
        return [
            'auditEnabled' => !empty($adv['auditEnabled']),
            'auditTable'   => $clean($adv['auditTable'] ?? '', 'audit_log'),
            'auditMode'    => $mode($adv['auditMode'] ?? 'static'),
            'lockEnabled'  => !empty($adv['recordLocking']),
            'lockTable'    => $clean($adv['lockTable'] ?? '', 'record_locks'),
            'lockMode'     => $mode($adv['lockMode'] ?? 'static'),
        ];
    }

    /** Persist one advanced flag into the Misc module state (source of truth). */
    private function saveMiscAdvanced(string $slug, string $key, $value): void
    {
        $state = ModuleState::load($slug, 'misc');
        $state['advanced'] = (array) ($state['advanced'] ?? []);
        $state['advanced'][$key] = $value;
        ModuleState::save($slug, 'misc', $state);
    }

    /**
     * Static  = the admin reviews the audit trail here in the console (default).
     * Dynamic = the build ships an admin-only Audit Log page inside the generated
     *           app (reached from the user menu), like the Admin Area. Stored in
     *           Misc → Advanced so the build reads it alongside the enable flag.
     */
    public function saveAuditMode(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        $input = json_decode((string) file_get_contents('php://input'), true) ?? $_POST;
        $mode  = in_array($input['mode'] ?? '', ['static', 'dynamic'], true) ? $input['mode'] : 'static';
        $this->saveMiscAdvanced($slug, 'auditMode', $mode);
        echo json_encode(['success' => true, 'mode' => $mode, 'message' => $mode === 'dynamic'
            ? 'Audit Log will be built into the app. Rebuild to publish it.'
            : 'Audit Log stays in the console.']);
        exit;
    }

    public function saveLockMode(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        $input = json_decode((string) file_get_contents('php://input'), true) ?? $_POST;
        $mode  = in_array($input['mode'] ?? '', ['static', 'dynamic'], true) ? $input['mode'] : 'static';
        $this->saveMiscAdvanced($slug, 'lockMode', $mode);
        echo json_encode(['success' => true, 'mode' => $mode, 'message' => $mode === 'dynamic'
            ? 'Record Locks will be built into the app. Rebuild to publish it.'
            : 'Record Locks stays in the console.']);
        exit;
    }

    /** True when a table physically exists in the current project database. */
    private function tableExists(string $name): bool
    {
        try {
            return (bool) DB::FetchOne(
                "SELECT 1 AS ok FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = ? LIMIT 1",
                [$name]
            );
        } catch (\Throwable $e) {
            return false;
        }
    }

    public function getAuditLogs(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);

        $cfg   = $this->miscAuditConfig($slug);
        $table = $cfg['auditTable'];
        // Not enabled yet, or the built app has never written a row → nothing to show.
        // Report config state so the UI can point the admin to Misc → Advanced.
        if (!$this->tableExists($table)) {
            echo json_encode([
                'success'    => true,
                'logs'       => [],
                'total'      => 0,
                'configured' => false,
                'enabled'    => $cfg['auditEnabled'],
                'table'      => $table,
                'mode'       => $cfg['auditMode'],
            ]);
            exit;
        }

        try {
            $input = $_GET;
            $userId = isset($input['user_id']) ? (int)$input['user_id'] : null;
            $action = $input['action'] ?? null;
            $startDate = $input['start_date'] ?? null;
            $endDate = $input['end_date'] ?? null;
            $limit = isset($input['limit']) ? (int)$input['limit'] : 50;
            $offset = isset($input['offset']) ? (int)$input['offset'] : 0;

            $where = [];
            $params = [];
            if ($userId) {
                $where[] = "user_id = ?";
                $params[] = $userId;
            }
            if ($action) {
                $where[] = "action = ?";
                $params[] = $action;
            }
            if ($startDate) {
                $where[] = "created_at >= ?";
                $params[] = $startDate . " 00:00:00";
            }
            if ($endDate) {
                $where[] = "created_at <= ?";
                $params[] = $endDate . " 23:59:59";
            }
            $whereClause = empty($where) ? "" : "WHERE " . implode(" AND ", $where);

            // FetchOne returns the first column's scalar value (COUNT), not a row.
            $total = (int) DB::FetchOne("SELECT COUNT(*) FROM `$table` $whereClause", $params);

            $sql = "SELECT * FROM `$table` $whereClause ORDER BY created_at DESC LIMIT $limit OFFSET $offset";
            $logs = DB::FetchAll($sql, $params);
            if (!is_array($logs)) {
                $logs = [];
            }

            echo json_encode([
                'success'    => true,
                'logs'       => $logs,
                'total'      => (int) $total,
                'configured' => true,
                'enabled'    => $cfg['auditEnabled'],
                'table'      => $table,
                'mode'       => $cfg['auditMode'],
            ]);
        } catch (Exception $e) {
            echo json_encode(['success' => false, 'message' => $e->getMessage(), 'logs' => [], 'total' => 0]);
        }
        exit;
    }

    // ── Record Locks ───────────────────────────────────────────────────
    public function getRecordLocks(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);

        $cfg   = $this->miscAuditConfig($slug);
        $table = $cfg['lockTable'];
        if (!$this->tableExists($table)) {
            echo json_encode([
                'success'    => true,
                'locks'      => [],
                'configured' => false,
                'enabled'    => $cfg['lockEnabled'],
                'table'      => $table,
                'mode'       => $cfg['lockMode'],
            ]);
            exit;
        }

        // Clean expired locks first so the console never shows stale holds.
        try { DB::Exec("DELETE FROM `$table` WHERE expires_at IS NOT NULL AND expires_at < NOW()"); }
        catch (\Throwable $e) { /* best effort */ }

        $locks = DB::FetchAll("
            SELECT l.*, u.username
            FROM `$table` l
            LEFT JOIN users u ON l.user_id = u.id
            ORDER BY l.locked_at DESC
        ");
        if (!is_array($locks)) {
            $locks = [];
        }
        echo json_encode([
            'success'    => true,
            'locks'      => $locks,
            'configured' => true,
            'enabled'    => $cfg['lockEnabled'],
            'table'      => $table,
            'mode'       => $cfg['lockMode'],
        ]);
        exit;
    }

    public function releaseLock(string $slug, int $lockId): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);

        $table = $this->miscAuditConfig($slug)['lockTable'];
        if ($this->tableExists($table)) {
            DB::Delete($table, ['id' => $lockId]);
        }
        echo json_encode(['success' => true, 'message' => 'Lock released.']);
        exit;
    }

    public function releaseAllLocks(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);

        $table = $this->miscAuditConfig($slug)['lockTable'];
        if ($this->tableExists($table)) {
            DB::Exec("DELETE FROM `$table`");
        }
        echo json_encode(['success' => true, 'message' => 'All locks released.']);
        exit;
    }


    // -------------------------------------------------------
    //  Session Control 
    // -------------------------------------------------------
    // ── Session Control ────────────────────────────────────────────────
    public function getSessionSettings(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        $settings = ModuleState::load($slug, 'security');
        $defaults = [
            'session_timeout' => 30,      // minutes
            'session_lifetime' => 24,     // hours
            'max_concurrent' => 5,        // 0 = unlimited
        ];
        $session = $settings['session'] ?? [];
        $session = array_merge($defaults, $session);
        echo json_encode(['success' => true, 'settings' => $session]);
        exit;
    }

    public function saveSessionSettings(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        $input = json_decode(file_get_contents('php://input'), true) ?? [];
        $session = $input['settings'] ?? [];
        $state = ModuleState::load($slug, 'security');
        $state['session'] = $session;
        ModuleState::save($slug, 'security', $state);
        echo json_encode(['success' => true, 'message' => 'Session settings saved.']);
        exit;
    }

    public function getActiveSessions(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        $this->ensureTablesExist();

        // Optional: clean up stale sessions older than session_lifetime
        $settings = ModuleState::load($slug, 'security');
        $lifetimeHours = $settings['session']['session_lifetime'] ?? 24;
        $expireTime = date('Y-m-d H:i:s', strtotime("-$lifetimeHours hours"));
        DB::Exec("DELETE FROM user_sessions WHERE last_activity < ?", [$expireTime]);

        $sessions = DB::FetchAll("
        SELECT s.*, u.username 
        FROM user_sessions s
        LEFT JOIN users u ON s.user_id = u.id
        ORDER BY s.last_activity DESC
    ");
        echo json_encode(['success' => true, 'sessions' => $sessions]);
        exit;
    }

    public function killSession(string $slug, int $sessionId): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        $this->ensureTablesExist();
        DB::Delete('user_sessions', ['id' => $sessionId]);
        echo json_encode(['success' => true, 'message' => 'Session terminated.']);
        exit;
    }

    public function killAllSessions(string $slug): void
    {
        header('Content-Type: application/json');
        $this->initProject($slug);
        $this->ensureTablesExist();
        DB::Exec("DELETE FROM user_sessions");
        echo json_encode(['success' => true, 'message' => 'All sessions terminated.']);
        exit;
    }
}
