<?php
/**
 * Database - SQLite database management library
 * Uses the native SQLite3 PHP extension (not PDO).
 */

class Database
{
    private static ?Database $instance = null;
    private SQLite3 $db;

    private function __construct()
    {
        $this->ensureSecuriteDir();

        $this->db = new SQLite3(DB_PATH);
        $this->db->enableExceptions(true);
        $this->db->busyTimeout(5000);
        $this->db->exec('PRAGMA foreign_keys = ON;');
        $this->db->exec('PRAGMA journal_mode = WAL;');

        $this->initialize();
    }

    public static function getInstance(): Database
    {
        if (self::$instance === null) {
            self::$instance = new Database();
        }
        return self::$instance;
    }

    /**
     * Close the DB connection and reset the singleton.
     * Needed before replacing database.sqlite on disk (import/restore).
     */
    public static function close(): void
    {
        if (self::$instance !== null) {
            self::$instance->db->close();
            self::$instance = null;
        }
    }

    // -------------------------------------------------------------------------
    // Query helpers
    // -------------------------------------------------------------------------

    /**
     * Execute a query with bound parameters. Returns SQLite3Result.
     */
    public function query(string $sql, array $params = []): SQLite3Result
    {
        $stmt = $this->prepare($sql, $params);
        $result = $stmt->execute();
        if ($result === false) {
            throw new RuntimeException('SQLite3 query failed: ' . $this->db->lastErrorMsg());
        }
        return $result;
    }

    /**
     * Fetch a single row as associative array, or null if not found.
     */
    public function fetchOne(string $sql, array $params = []): ?array
    {
        $result = $this->query($sql, $params);
        $row = $result->fetchArray(SQLITE3_ASSOC);
        $result->finalize();
        return $row ?: null;
    }

    /**
     * Fetch all rows as array of associative arrays.
     */
    public function fetchAll(string $sql, array $params = []): array
    {
        $result = $this->query($sql, $params);
        $rows = [];
        while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
            $rows[] = $row;
        }
        $result->finalize();
        return $rows;
    }

    /**
     * Fetch a single scalar value from the first column of the first row.
     */
    public function fetchScalar(string $sql, array $params = []): mixed
    {
        $result = $this->query($sql, $params);
        $row = $result->fetchArray(SQLITE3_NUM);
        $result->finalize();
        return ($row !== false) ? $row[0] : null;
    }

    /**
     * Execute a non-SELECT statement. Returns number of affected rows.
     */
    public function execute(string $sql, array $params = []): int
    {
        $stmt = $this->prepare($sql, $params);
        $stmt->execute();
        return $this->db->changes();
    }

    /**
     * Return the rowid of the last INSERT.
     */
    public function lastInsertId(): int
    {
        return $this->db->lastInsertRowID();
    }

    // -------------------------------------------------------------------------
    // Internals
    // -------------------------------------------------------------------------

    /**
     * Prepare a statement and bind named or positional parameters.
     * Supports both positional (?) and named (:name) placeholders.
     */
    private function prepare(string $sql, array $params): SQLite3Stmt
    {
        $stmt = $this->db->prepare($sql);
        if ($stmt === false) {
            throw new RuntimeException('SQLite3 prepare failed: ' . $this->db->lastErrorMsg() . ' — SQL: ' . $sql);
        }

        foreach ($params as $key => $value) {
            // Named param: pass key as-is (must include ':')
            // Positional param: pass integer index (1-based)
            $bindKey = is_int($key) ? $key + 1 : $key;
            $type = match(true) {
                is_int($value)   => SQLITE3_INTEGER,
                is_float($value) => SQLITE3_FLOAT,
                $value === null  => SQLITE3_NULL,
                default          => SQLITE3_TEXT,
            };
            $stmt->bindValue($bindKey, $value, $type);
        }

        return $stmt;
    }

    // -------------------------------------------------------------------------
    // Schema initialization
    // -------------------------------------------------------------------------

    private function initialize(): void
    {
        $this->db->exec("
            CREATE TABLE IF NOT EXISTS oracle_groups (
                id         INTEGER PRIMARY KEY AUTOINCREMENT,
                name       TEXT NOT NULL UNIQUE,
                created_at TEXT NOT NULL DEFAULT (datetime('now'))
            );

            CREATE TABLE IF NOT EXISTS oracle_databases (
                id           INTEGER PRIMARY KEY AUTOINCREMENT,
                group_id     INTEGER NOT NULL REFERENCES oracle_groups(id) ON DELETE CASCADE,
                subgroup     TEXT NOT NULL,
                name         TEXT NOT NULL,
                host         TEXT NOT NULL,
                port         INTEGER NOT NULL DEFAULT 1521,
                service_name TEXT NOT NULL,
                username     TEXT,
                password     TEXT,
                sysdba       INTEGER NOT NULL DEFAULT 0,
                description  TEXT,
                created_at   TEXT NOT NULL DEFAULT (datetime('now')),
                updated_at   TEXT NOT NULL DEFAULT (datetime('now'))
            );

            CREATE TABLE IF NOT EXISTS oracle_access (
                id       INTEGER PRIMARY KEY AUTOINCREMENT,
                group_id INTEGER NOT NULL REFERENCES oracle_groups(id) ON DELETE CASCADE,
                subgroup TEXT,
                user_id  INTEGER REFERENCES users(id) ON DELETE CASCADE,
                ad_group TEXT,
                right    TEXT NOT NULL DEFAULT 'consultation',
                UNIQUE(group_id, subgroup, user_id, ad_group)
            );

            CREATE TABLE IF NOT EXISTS oracle_roles (
                id          INTEGER PRIMARY KEY AUTOINCREMENT,
                name        TEXT NOT NULL UNIQUE,
                description TEXT,
                created_at  TEXT NOT NULL DEFAULT (datetime('now'))
            );

            CREATE TABLE IF NOT EXISTS oracle_role_perms (
                id       INTEGER PRIMARY KEY AUTOINCREMENT,
                role_id  INTEGER NOT NULL REFERENCES oracle_roles(id) ON DELETE CASCADE,
                group_id INTEGER REFERENCES oracle_groups(id) ON DELETE CASCADE,
                subgroup TEXT,
                right    TEXT NOT NULL DEFAULT 'consultation'
            );

            CREATE TABLE IF NOT EXISTS oracle_role_grants (
                id       INTEGER PRIMARY KEY AUTOINCREMENT,
                role_id  INTEGER NOT NULL REFERENCES oracle_roles(id) ON DELETE CASCADE,
                user_id  INTEGER REFERENCES users(id) ON DELETE CASCADE,
                ad_group TEXT,
                UNIQUE(role_id, user_id, ad_group)
            );

            CREATE TABLE IF NOT EXISTS oracle_role_includes (
                id               INTEGER PRIMARY KEY AUTOINCREMENT,
                role_id          INTEGER NOT NULL REFERENCES oracle_roles(id) ON DELETE CASCADE,
                included_role_id INTEGER NOT NULL REFERENCES oracle_roles(id) ON DELETE CASCADE,
                UNIQUE(role_id, included_role_id)
            );

            CREATE TABLE IF NOT EXISTS users (
                id                   INTEGER PRIMARY KEY AUTOINCREMENT,
                username             TEXT NOT NULL UNIQUE,
                email                TEXT,
                password_hash        TEXT,
                auth_method          TEXT NOT NULL DEFAULT 'local',
                language             TEXT,
                theme                TEXT,
                active               INTEGER NOT NULL DEFAULT 1,
                -- Préférences Oracle (per-user)
                oracle_landing       TEXT,                 -- '' / 'realtime' / 'history/detail' / ...
                oracle_report_format TEXT,                 -- 'html' (défaut) ou 'active'
                postgresql_landing   TEXT,                 -- '' / 'config' / 'ash' / ...
                created_at           TEXT NOT NULL DEFAULT (datetime('now')),
                last_login           TEXT
            );

            CREATE TABLE IF NOT EXISTS settings (
                key   TEXT PRIMARY KEY,
                value TEXT
            );

            CREATE TABLE IF NOT EXISTS auth_config (
                method  TEXT PRIMARY KEY,
                enabled INTEGER NOT NULL DEFAULT 0,
                config  TEXT
            );

            CREATE TABLE IF NOT EXISTS modules (
                name        TEXT PRIMARY KEY,
                label       TEXT NOT NULL,
                description TEXT,
                active      INTEGER NOT NULL DEFAULT 0,
                config      TEXT
            );

            -- Système de rôles (remplace progressivement les profils unitaires) :
            -- chaque module déclare ses rôles (au minimum <module>_admin et <module>_user)
            -- et un user peut en avoir plusieurs. app_admin est implicite : il accorde
            -- l'accès à tout (cf. Auth::hasRole / Module::canAccessAsAdmin).
            CREATE TABLE IF NOT EXISTS roles (
                id        INTEGER PRIMARY KEY AUTOINCREMENT,
                name      TEXT NOT NULL UNIQUE,
                label     TEXT NOT NULL,
                module    TEXT,                      -- NULL pour les rôles système (app_admin)
                is_system INTEGER NOT NULL DEFAULT 0  -- 1 = non supprimable
            );

            CREATE TABLE IF NOT EXISTS user_roles (
                user_id    INTEGER NOT NULL,
                role_id    INTEGER NOT NULL,
                granted_at TEXT NOT NULL DEFAULT (datetime('now')),
                PRIMARY KEY (user_id, role_id),
                FOREIGN KEY (user_id) REFERENCES users(id)  ON DELETE CASCADE,
                FOREIGN KEY (role_id) REFERENCES roles(id)  ON DELETE CASCADE
            );

            -- Catalogues RMAN (module CMDB) : connexions aux bases catalogue de
            -- recovery, interrogées par modules/CMDB/exploitation/collect_rman.php pour le
            -- rapport de sauvegardes. password chiffré via Crypto (enc_v1:...).
            CREATE TABLE IF NOT EXISTS cmdb_rman_catalogs (
                id             INTEGER PRIMARY KEY AUTOINCREMENT,
                label          TEXT NOT NULL,
                db_unique_name TEXT,
                host           TEXT NOT NULL,
                port           INTEGER NOT NULL DEFAULT 1521,
                service_name   TEXT NOT NULL,
                username       TEXT NOT NULL,
                password       TEXT,
                description    TEXT,
                active         INTEGER NOT NULL DEFAULT 1,
                created_at     TEXT NOT NULL DEFAULT (datetime('now')),
                updated_at     TEXT NOT NULL DEFAULT (datetime('now'))
            );
        ");

        // Migrations: add columns introduced after initial schema
        $this->migrate();

        $this->seedInitialData();
    }

    private function migrate(): void
    {
        // ── Phase C : drop users.profile_id + table profiles ─────────────────
        // Pré-requis : la table 'profiles' a été migrée en rôles (phase A).
        // On exécute ici la migration profile→role en filet de sécurité,
        // puis on recrée la table users sans profile_id et on droppe profiles.
        $userCols = array_column(
            $this->fetchAll("PRAGMA table_info(users)"),
            'name'
        );
        if (in_array('profile_id', $userCols, true)) {
            $hasProfilesTable = (int)$this->fetchScalar(
                "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='profiles'"
            ) > 0;

            if ($hasProfilesTable) {
                // Filet : si des users ont un profil legacy mais aucun rôle, créer le rôle équivalent
                $this->execute("
                    INSERT OR IGNORE INTO user_roles (user_id, role_id)
                    SELECT u.id, r.id
                    FROM users u
                    JOIN profiles p ON p.id = u.profile_id
                    JOIN roles    r ON r.name = CASE p.name
                                                WHEN 'admin'        THEN 'app_admin'
                                                WHEN 'oracle_admin' THEN 'oracle_admin'
                                                WHEN 'cnp_admin'    THEN 'cnp_admin'
                                                ELSE NULL
                                               END
                    WHERE NOT EXISTS (SELECT 1 FROM user_roles ur WHERE ur.user_id = u.id)
                ");
            }

            // Recréer la table users sans profile_id (SQLite ne supporte pas
            // DROP COLUMN avant 3.35 ; on prend le pattern table-temporaire universel).
            $this->db->exec('PRAGMA foreign_keys = OFF');
            $this->db->exec("
                CREATE TABLE users_new (
                    id            INTEGER PRIMARY KEY AUTOINCREMENT,
                    username      TEXT NOT NULL UNIQUE,
                    email         TEXT,
                    password_hash TEXT,
                    auth_method   TEXT NOT NULL DEFAULT 'local',
                    language      TEXT,
                    theme         TEXT,
                    active        INTEGER NOT NULL DEFAULT 1,
                    created_at    TEXT NOT NULL DEFAULT (datetime('now')),
                    last_login    TEXT
                );
                INSERT INTO users_new (id, username, email, password_hash, auth_method,
                                       language, theme, active, created_at, last_login)
                SELECT id, username, email, password_hash, auth_method,
                       language, theme, active, created_at, last_login
                FROM users;
                DROP TABLE users;
                ALTER TABLE users_new RENAME TO users;
            ");
            if ($hasProfilesTable) {
                $this->db->exec("DROP TABLE profiles");
            }
            $this->db->exec('PRAGMA foreign_keys = ON');
        } elseif ((int)$this->fetchScalar(
                "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='profiles'"
            ) > 0) {
            // Cas rare : profile_id déjà retiré mais profiles encore là (install bizarre)
            $this->db->exec("DROP TABLE profiles");
        }

        $cols = array_column(
            $this->fetchAll("PRAGMA table_info(oracle_databases)"),
            'name'
        );
        // oracle_databases.sysdba (added in v1.1)
        if (!in_array('sysdba', $cols, true)) {
            $this->db->exec(
                "ALTER TABLE oracle_databases ADD COLUMN sysdba INTEGER NOT NULL DEFAULT 0"
            );
        }
        // oracle_databases cloud columns (added in v1.2)
        if (!in_array('conn_type', $cols, true)) {
            $this->db->exec(
                "ALTER TABLE oracle_databases ADD COLUMN conn_type TEXT NOT NULL DEFAULT 'standard'"
            );
        }
        if (!in_array('tns_alias', $cols, true)) {
            $this->db->exec(
                "ALTER TABLE oracle_databases ADD COLUMN tns_alias TEXT"
            );
        }
        if (!in_array('wallet_path', $cols, true)) {
            $this->db->exec(
                "ALTER TABLE oracle_databases ADD COLUMN wallet_path TEXT"
            );
        }

        // oracle_access.right (added in v1.3 — roles feature)
        $accessCols = array_column(
            $this->fetchAll("PRAGMA table_info(oracle_access)"),
            'name'
        );
        if (!in_array('right', $accessCols, true)) {
            $this->db->exec(
                "ALTER TABLE oracle_access ADD COLUMN right TEXT NOT NULL DEFAULT 'consultation'"
            );
        }

        // users.oracle_landing + users.oracle_report_format — préférences Oracle par utilisateur
        $userColsPref = array_column(
            $this->fetchAll("PRAGMA table_info(users)"),
            'name'
        );
        if (!in_array('oracle_landing', $userColsPref, true)) {
            $this->db->exec("ALTER TABLE users ADD COLUMN oracle_landing TEXT");
        }
        if (!in_array('oracle_report_format', $userColsPref, true)) {
            $this->db->exec("ALTER TABLE users ADD COLUMN oracle_report_format TEXT");
        }
        // users.postgresql_landing — même préférence côté module PostgreSQL
        // ('' / 'config' / 'ash' / …), validée contre pg_landing_pages().
        if (!in_array('postgresql_landing', $userColsPref, true)) {
            $this->db->exec("ALTER TABLE users ADD COLUMN postgresql_landing TEXT");
        }

        // oracle_role_perms v1.4: group_id must be nullable (NULL = all groups).
        // Earlier schema had group_id INTEGER NOT NULL which broke the FK when
        // inserting the "all groups" sentinel (foreign_keys = ON).
        // Recreate the table if group_id is still NOT NULL.
        $rpCols = array_column(
            $this->fetchAll("PRAGMA table_info(oracle_role_perms)"),
            null,
            'name'
        );
        if (isset($rpCols['group_id']) && (int)$rpCols['group_id']['notnull'] === 1) {
            // Temporarily disable FK checks so the DROP/RENAME succeeds even if
            // the table is referenced by nothing (should be no dependents here).
            $this->db->exec('PRAGMA foreign_keys = OFF');
            $this->db->exec('
                CREATE TABLE oracle_role_perms_new (
                    id       INTEGER PRIMARY KEY AUTOINCREMENT,
                    role_id  INTEGER NOT NULL REFERENCES oracle_roles(id) ON DELETE CASCADE,
                    group_id INTEGER REFERENCES oracle_groups(id) ON DELETE CASCADE,
                    subgroup TEXT,
                    right    TEXT NOT NULL DEFAULT \'consultation\'
                );
                INSERT OR IGNORE INTO oracle_role_perms_new
                    SELECT id, role_id, group_id, subgroup, right FROM oracle_role_perms;
                DROP TABLE oracle_role_perms;
                ALTER TABLE oracle_role_perms_new RENAME TO oracle_role_perms;
            ');
            $this->db->exec('PRAGMA foreign_keys = ON');
        }

        // COALESCE-based unique index so that NULL group_id rows deduplicate properly.
        // (Standard UNIQUE treats every NULL as distinct, so we use a functional index.)
        $this->db->exec(
            "CREATE UNIQUE INDEX IF NOT EXISTS idx_oracle_role_perms_uniq
             ON oracle_role_perms(role_id, COALESCE(group_id, 0), COALESCE(subgroup, ''))"
        );

        // ── Report subscriptions ─────────────────────────────────────────────
        $this->db->exec("CREATE TABLE IF NOT EXISTS report_schedules (
            report_key   TEXT PRIMARY KEY,
            frequency    TEXT NOT NULL DEFAULT 'daily',
            day_config   TEXT,
            time_of_day  TEXT NOT NULL DEFAULT '08:00',
            enabled      INTEGER NOT NULL DEFAULT 0,
            updated_at   TEXT NOT NULL DEFAULT (datetime('now'))
        )");
        $this->db->exec("CREATE TABLE IF NOT EXISTS report_subscribers (
            report_key  TEXT NOT NULL,
            user_id     INTEGER NOT NULL,
            created_at  TEXT NOT NULL DEFAULT (datetime('now')),
            PRIMARY KEY (report_key, user_id),
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        )");
        $this->db->exec("CREATE TABLE IF NOT EXISTS report_send_log (
            id              INTEGER PRIMARY KEY AUTOINCREMENT,
            report_key      TEXT NOT NULL,
            sent_at         TEXT NOT NULL DEFAULT (datetime('now')),
            recipient_count INTEGER NOT NULL DEFAULT 0,
            status          TEXT NOT NULL DEFAULT 'ok',
            details         TEXT,
            duration_ms     INTEGER
        )");

        $this->db->exec("CREATE TABLE IF NOT EXISTS cnp_aif_databases (
            id              INTEGER PRIMARY KEY AUTOINCREMENT,
            db_service      TEXT NOT NULL UNIQUE,
            db_name         TEXT NOT NULL DEFAULT '',
            owner_user      TEXT NOT NULL DEFAULT 'GMUP00_OWNER',
            owner_password  TEXT NOT NULL DEFAULT '',
            created_at      TEXT NOT NULL DEFAULT (datetime('now'))
        )");

        // ── Chiffrement at-rest des secrets stockés (idempotent) ─────────────
        // Convertit les mots de passe en clair vers le format chiffré
        // (préfixe "enc_v1:") via lib/Crypto. Les valeurs déjà chiffrées
        // sont laissées telles quelles. La clé maître est auto-générée à
        // la première utilisation dans securite/master.key.
        $this->migrateEncryptSecrets();
    }

    /**
     * Migration ponctuelle des secrets en clair vers leur version chiffrée.
     * Sûr à exécuter à chaque bootstrap (idempotent : Crypto::encrypt
     * détecte le préfixe et n'agit qu'une fois).
     */
    private function migrateEncryptSecrets(): void
    {
        // 1. oracle_databases.password
        $rows = $this->fetchAll(
            "SELECT id, password FROM oracle_databases
             WHERE password IS NOT NULL AND password != ''"
        );
        foreach ($rows as $r) {
            if (!Crypto::isEncrypted($r['password'])) {
                $this->execute(
                    "UPDATE oracle_databases SET password = ? WHERE id = ?",
                    [Crypto::encrypt($r['password']), (int)$r['id']]
                );
            }
        }

        // 2. modules.config (cmdb) — clé "password"
        $row = $this->fetchOne("SELECT config FROM modules WHERE name = ?", ['cmdb']);
        if ($row && !empty($row['config'])) {
            $cfg = json_decode($row['config'], true) ?? [];
            if (!empty($cfg['password']) && !Crypto::isEncrypted($cfg['password'])) {
                $cfg['password'] = Crypto::encrypt($cfg['password']);
                $this->execute(
                    "UPDATE modules SET config = ? WHERE name = ?",
                    [json_encode($cfg, JSON_UNESCAPED_UNICODE), 'cmdb']
                );
            }
        }

        // 3. auth_config (ldap) — clé "bind_password"
        $row = $this->fetchOne("SELECT config FROM auth_config WHERE method = ?", [AUTH_LDAP]);
        if ($row && !empty($row['config'])) {
            $cfg = json_decode($row['config'], true) ?? [];
            if (!empty($cfg['bind_password']) && !Crypto::isEncrypted($cfg['bind_password'])) {
                $cfg['bind_password'] = Crypto::encrypt($cfg['bind_password']);
                $this->execute(
                    "UPDATE auth_config SET config = ? WHERE method = ?",
                    [json_encode($cfg), AUTH_LDAP]
                );
            }
        }

        // 4. auth_config (oauth) — clé "client_secret"
        $row = $this->fetchOne("SELECT config FROM auth_config WHERE method = ?", [AUTH_OAUTH]);
        if ($row && !empty($row['config'])) {
            $cfg = json_decode($row['config'], true) ?? [];
            if (!empty($cfg['client_secret']) && !Crypto::isEncrypted($cfg['client_secret'])) {
                $cfg['client_secret'] = Crypto::encrypt($cfg['client_secret']);
                $this->execute(
                    "UPDATE auth_config SET config = ? WHERE method = ?",
                    [json_encode($cfg), AUTH_OAUTH]
                );
            }
        }
    }

    private function seedInitialData(): void
    {
        // Rôles : un par module (admin + user) + app_admin transverse.
        // Convention : <module>_admin, <module>_user. app_admin est système.
        $roles = [
            ['app_admin',    'Application Administrator', null,     1],
            ['oracle_admin', 'Oracle Administrator',      'oracle', 0],
            ['oracle_user',  'Oracle User',               'oracle', 0],
            ['cmdb_admin',   'CMDB Administrator',        'cmdb',   0],
            ['cmdb_user',    'CMDB User',                 'cmdb',   0],
            ['cnp_admin',    'CNP Administrator',         'cnp',    0],
            ['cnp_user',     'CNP User',                  'cnp',    0],
        ];
        foreach ($roles as [$name, $label, $module, $sys]) {
            $this->execute(
                "INSERT OR IGNORE INTO roles (name, label, module, is_system) VALUES (?, ?, ?, ?)",
                [$name, $label, $module, $sys]
            );
        }

        // Default settings
        $defaults = [
            'default_theme'    => DEFAULT_THEME,
            'default_language' => DEFAULT_LANGUAGE,
            'app_title'        => 'Mon Application',
            'app_logo'         => '',   // relative URL or empty
            'app_favicon'      => '',   // relative URL or empty
            'debug_mode'       => '0',
            'trace_mode'       => '0',
            'session_lifetime' => (string)SESSION_LIFETIME,  // secondes (défaut 3600)

            // URLs CDN des librairies front (modifiables via le module
            // Developer). INSERT OR IGNORE = on n'écrase pas une valeur
            // déjà personnalisée par l'admin.
            'cdn_font_awesome_css' => 'https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.7.2/css/all.min.css',
            'cdn_d3_js'            => 'https://cdn.jsdelivr.net/npm/d3@7/dist/d3.min.js',
            'cdn_plot_js'          => 'https://cdn.jsdelivr.net/npm/@observablehq/plot@0.6/dist/plot.umd.min.js',
            'cdn_chartjs_js'       => 'https://cdn.jsdelivr.net/npm/chart.js@4/dist/chart.umd.min.js',
            'cdn_highlight_css'    => 'https://cdnjs.cloudflare.com/ajax/libs/highlight.js/11.9.0/styles/github.min.css',
            'cdn_highlight_js'     => 'https://cdnjs.cloudflare.com/ajax/libs/highlight.js/11.9.0/highlight.min.js',
            'cdn_highlight_sql_js' => 'https://cdnjs.cloudflare.com/ajax/libs/highlight.js/11.9.0/languages/sql.min.js',
            'cdn_sql_formatter_js' => 'https://cdn.jsdelivr.net/npm/sql-formatter@14/dist/sql-formatter.min.js',
            'cdn_mermaid_js'       => 'https://cdn.jsdelivr.net/npm/mermaid@11/dist/mermaid.min.js',

            // SMTP — configuration email
            'smtp_host'          => '',
            'smtp_port'          => '587',
            'smtp_encryption'    => 'tls',      // '', 'tls', 'ssl'
            'smtp_auth_enabled'  => '1',          // '0' = relais anonyme (pas d'auth)
            'smtp_username'      => '',
            'smtp_password'      => '',          // chiffré via Crypto
            'smtp_from_address'  => '',
            'smtp_from_name'     => '',

            // Thème "custom" — éditable via Developer > Thème.
            // Couleurs initialisées sur les valeurs de light.css.
            'theme_custom_color_bg'         => '#f1f5f9',
            'theme_custom_color_surface'    => '#ffffff',
            'theme_custom_color_border'     => '#cbd5e1',
            'theme_custom_color_text'       => '#1e293b',
            'theme_custom_color_text_muted' => '#64748b',
            'theme_custom_color_primary'    => '#2563eb',
            'theme_custom_color_primary_h'  => '#1d4ed8',
            'theme_custom_color_secondary'  => '#64748b',
            'theme_custom_color_danger'     => '#dc2626',
            'theme_custom_color_success'    => '#16a34a',
            'theme_custom_color_warning'    => '#d97706',
            'theme_custom_color_nav_bg'     => '#1e293b',
            'theme_custom_color_nav_text'   => '#e2e8f0',
            'theme_custom_color_nav_hover'  => '#3b82f6',
            // Choix de fonte : 'system', 'curated:<id>' ou 'uploaded:<filename>'.
            'theme_custom_font_sans' => 'system',
            'theme_custom_font_mono' => 'system',
        ];

        foreach ($defaults as $key => $value) {
            $this->execute(
                "INSERT OR IGNORE INTO settings (key, value) VALUES (?, ?)",
                [$key, $value]
            );
        }

        // Auth methods (only local enabled by default)
        $methods = [
            [AUTH_LOCAL, 1, null],
            [AUTH_OAUTH, 0, json_encode(['client_id' => '', 'client_secret' => '', 'redirect_uri' => '', 'auth_url' => '', 'token_url' => ''])],
            [AUTH_LDAP,  0, json_encode([
                'host'            => '',
                'port'            => 389,
                'base_dn'         => '',
                'bind_dn'         => '',
                'bind_password'   => '',
                'user_dn_pattern' => 'uid={user},{base_dn}',
                'tls_insecure'    => false,
            ])],
        ];
        foreach ($methods as [$method, $enabled, $config]) {
            $this->execute(
                "INSERT OR IGNORE INTO auth_config (method, enabled, config) VALUES (?, ?, ?)",
                [$method, $enabled, $config]
            );
        }

        // Oracle default group
        $this->execute(
            "INSERT OR IGNORE INTO oracle_groups (name) VALUES (?)",
            [ORACLE_DEFAULT_GROUP]
        );

        // Report schedules (seed known reports, disabled by default)
        foreach ([
            ['cnp/ad_anomalies',  'weekly',  '{"dow":1}', '08:00'],
            ['cmdb/db_inventory', 'weekly',  '{"dow":1}', '08:00'],
            ['cnp/cmdb_sync',    'weekly',  '{"dow":1}', '08:00'],
        ] as [$rk, $freq, $dc, $tod]) {
            $this->execute(
                "INSERT OR IGNORE INTO report_schedules (report_key, frequency, day_config, time_of_day, enabled) VALUES (?, ?, ?, ?, 0)",
                [$rk, $freq, $dc, $tod]
            );
        }

        // Admin user (only if no users exist) — créé avec le rôle app_admin.
        // Sécurité : PAS de mot de passe par défaut en dur. On génère un mot de
        // passe aléatoire unique par installation, écrit une seule fois dans
        // securite/initial_admin_password.txt (0600, hors DocumentRoot) et
        // journalisé. L'opérateur se connecte, change le mot de passe, supprime
        // le fichier.
        $count = (int)$this->fetchScalar("SELECT COUNT(*) FROM users");
        if ($count === 0) {
            // Déploiements packagés (module developer) : la SQLite embarquée
            // pose le marqueur `deploy_default_admin` → on crée le compte admin
            // avec le mot de passe par défaut CONNU (admin123) pour permettre la
            // première connexion, quel que soit le mot de passe modifié sur la
            // source. Installations manuelles (pas de marqueur) → mot de passe
            // aléatoire unique (durcissement).
            $useDefault = false;
            try {
                $flag = $this->fetchScalar("SELECT value FROM settings WHERE key = 'deploy_default_admin'");
                $useDefault = ($flag === '1' || $flag === 1);
            } catch (\Throwable $e) {
                // Table settings absente au tout premier boot → mot de passe aléatoire.
            }

            $initialPassword = $useDefault ? 'admin123' : bin2hex(random_bytes(12));
            $hash = password_hash($initialPassword, PASSWORD_DEFAULT);
            $this->execute(
                "INSERT INTO users (username, email, password_hash, auth_method, active)
                 VALUES (?, ?, ?, ?, 1)",
                ['admin', 'admin@localhost', $hash, AUTH_LOCAL]
            );
            $newAdminId = (int)$this->lastInsertId();
            $this->execute(
                "INSERT OR IGNORE INTO user_roles (user_id, role_id)
                 SELECT ?, id FROM roles WHERE name = 'app_admin'",
                [$newAdminId]
            );

            if ($useDefault) {
                // Marqueur consommé (évite de le relire si les users sont purgés).
                $this->execute("DELETE FROM settings WHERE key = 'deploy_default_admin'");
                @error_log("[sw] Compte 'admin' initial créé avec le mot de passe par défaut (déploiement) — à changer immédiatement.");
            } else {
                $pwFile = SECURITE_PATH . '/initial_admin_password.txt';
                $body = "Compte administrateur initial de l'application\n"
                      . "================================================\n\n"
                      . "  utilisateur : admin\n"
                      . "  mot de passe : {$initialPassword}\n\n"
                      . "IMPORTANT : connectez-vous, changez ce mot de passe, puis\n"
                      . "SUPPRIMEZ ce fichier.\n";
                if (@file_put_contents($pwFile, $body) !== false) {
                    @chmod($pwFile, 0600);
                }
                @error_log("[sw] Compte 'admin' initial créé — mot de passe dans {$pwFile} (à changer + supprimer).");
            }
        }
    }

    private function ensureSecuriteDir(): void
    {
        if (!is_dir(SECURITE_PATH)) {
            mkdir(SECURITE_PATH, 0750, true);
        }
        // Défense en profondeur : refuse tout accès web à securite/ au cas où
        // le DocumentRoot serait mal configuré (le dossier est normalement hors
        // racine web). Le dossier est gitignoré → on écrit le garde à l'exécution.
        $htaccess = SECURITE_PATH . '/.htaccess';
        if (!file_exists($htaccess)) {
            @file_put_contents($htaccess, "Require all denied\n");
        }
    }
}
