<?php
/**
 * 数据库连接与建表(SQLite / MySQL 兼容)
 */
function app_config()
{
    static $cfg = null;
    if ($cfg === null) {
        $cfg = require __DIR__ . DIRECTORY_SEPARATOR . '..' . DIRECTORY_SEPARATOR . 'config.php';
    }
    return $cfg;
}

function data_dir()
{
    return dirname(__DIR__) . DIRECTORY_SEPARATOR . 'data';
}

function db_driver()
{
    $cfg = app_config();
    return isset($cfg['db']['driver']) ? $cfg['db']['driver'] : 'sqlite';
}

/**
 * 获取 PDO 单例连接(首次调用时自动建表)
 */
function db()
{
    static $pdo = null;
    if ($pdo !== null) {
        return $pdo;
    }

    $cfg = app_config();
    $driver = db_driver();
    $options = array(
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    );

    if ($driver === 'mysql') {
        $options[PDO::ATTR_EMULATE_PREPARES] = false;
        $m = $cfg['db']['mysql'];
        $dsn = 'mysql:host=' . $m['host'] . ';port=' . $m['port'] . ';dbname=' . $m['database'] . ';charset=' . $m['charset'];
        $pdo = new PDO($dsn, $m['username'], $m['password'], $options);
    } else {
        $path = $cfg['db']['sqlite']['path'];
        $dir = dirname($path);
        if (!is_dir($dir)) {
            @mkdir($dir, 0777, true);
        }
        $pdo = new PDO('sqlite:' . $path, null, null, $options);
        // 使用回滚日志(非 WAL):事务提交即写入主库文件,
        // 避免 -wal/-shm 在站点迁移、文件覆盖等场景下丢失,导致写入看似成功但未真正保存
        $pdo->exec('PRAGMA journal_mode = DELETE');
        $pdo->exec('PRAGMA synchronous = NORMAL');
        $pdo->exec('PRAGMA busy_timeout = 5000');
        $pdo->exec('PRAGMA foreign_keys = ON');
    }

    init_schema($pdo, $driver);
    return $pdo;
}

/**
 * 建表(幂等)
 */
function init_schema($pdo, $driver)
{
    if ($driver === 'mysql') {
        $pdo->exec("CREATE TABLE IF NOT EXISTS users (
            id VARCHAR(32) NOT NULL,
            username VARCHAR(32) NOT NULL,
            password_hash VARCHAR(255) NOT NULL,
            role VARCHAR(16) NOT NULL DEFAULT 'user',
            disabled TINYINT NOT NULL DEFAULT 0,
            must_change_password TINYINT NOT NULL DEFAULT 0,
            created_at VARCHAR(32) DEFAULT NULL,
            last_login_at VARCHAR(32) DEFAULT NULL,
            login_count INT NOT NULL DEFAULT 0,
            PRIMARY KEY (id),
            UNIQUE KEY uk_users_username (username)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        $pdo->exec("CREATE TABLE IF NOT EXISTS sessions (
            token VARCHAR(64) NOT NULL,
            user_id VARCHAR(32) NOT NULL,
            created_at VARCHAR(32) DEFAULT NULL,
            expires_at BIGINT NOT NULL,
            ip VARCHAR(45) DEFAULT NULL,
            user_agent VARCHAR(255) DEFAULT NULL,
            PRIMARY KEY (token),
            KEY idx_sessions_user (user_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        $pdo->exec("CREATE TABLE IF NOT EXISTS topologies (
            id VARCHAR(32) NOT NULL,
            name VARCHAR(120) NOT NULL,
            owner_id VARCHAR(32) NOT NULL,
            visibility VARCHAR(16) NOT NULL DEFAULT 'private',
            data LONGTEXT,
            node_count INT NOT NULL DEFAULT 0,
            edge_count INT NOT NULL DEFAULT 0,
            created_at VARCHAR(32) DEFAULT NULL,
            updated_at VARCHAR(32) DEFAULT NULL,
            PRIMARY KEY (id),
            KEY idx_topo_owner (owner_id),
            KEY idx_topo_updated (updated_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        $pdo->exec("CREATE TABLE IF NOT EXISTS activity_logs (
            id INT NOT NULL AUTO_INCREMENT,
            user_id VARCHAR(32) DEFAULT NULL,
            username VARCHAR(32) DEFAULT NULL,
            action VARCHAR(40) DEFAULT NULL,
            target VARCHAR(64) DEFAULT NULL,
            detail VARCHAR(255) DEFAULT NULL,
            ip VARCHAR(45) DEFAULT NULL,
            created_at VARCHAR(32) DEFAULT NULL,
            PRIMARY KEY (id),
            KEY idx_logs_created (created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        $pdo->exec("CREATE TABLE IF NOT EXISTS settings (
            skey VARCHAR(64) NOT NULL,
            sval LONGTEXT,
            updated_at VARCHAR(32) DEFAULT NULL,
            PRIMARY KEY (skey)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        $pdo->exec("CREATE TABLE IF NOT EXISTS user_groups (
            id VARCHAR(32) NOT NULL,
            name VARCHAR(64) NOT NULL,
            description VARCHAR(255) DEFAULT NULL,
            created_at VARCHAR(32) DEFAULT NULL,
            updated_at VARCHAR(32) DEFAULT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY uk_groups_name (name)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        $pdo->exec("CREATE TABLE IF NOT EXISTS user_group_members (
            group_id VARCHAR(32) NOT NULL,
            user_id VARCHAR(32) NOT NULL,
            created_at VARCHAR(32) DEFAULT NULL,
            PRIMARY KEY (group_id, user_id),
            KEY idx_ugm_user (user_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        $pdo->exec("CREATE TABLE IF NOT EXISTS topo_shares (
            id VARCHAR(32) NOT NULL,
            topo_id VARCHAR(32) NOT NULL,
            target_type VARCHAR(8) NOT NULL,
            target_id VARCHAR(32) NOT NULL,
            permission VARCHAR(8) NOT NULL,
            created_by VARCHAR(32) DEFAULT NULL,
            created_at VARCHAR(32) DEFAULT NULL,
            PRIMARY KEY (id),
            KEY idx_shares_topo (topo_id),
            KEY idx_shares_target (target_type, target_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        $pdo->exec("CREATE TABLE IF NOT EXISTS topo_presence (
            topo_id VARCHAR(32) NOT NULL,
            user_id VARCHAR(32) NOT NULL,
            username VARCHAR(32) DEFAULT NULL,
            last_seen BIGINT NOT NULL DEFAULT 0,
            PRIMARY KEY (topo_id, user_id),
            KEY idx_presence_seen (last_seen)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        migrate_columns($pdo, $driver);
        return;
    }

    /* ---------- SQLite ---------- */
    $pdo->exec("CREATE TABLE IF NOT EXISTS users (
        id TEXT NOT NULL PRIMARY KEY,
        username TEXT NOT NULL UNIQUE,
        password_hash TEXT NOT NULL,
        role TEXT NOT NULL DEFAULT 'user',
        disabled INTEGER NOT NULL DEFAULT 0,
        must_change_password INTEGER NOT NULL DEFAULT 0,
        created_at TEXT,
        last_login_at TEXT,
        login_count INTEGER NOT NULL DEFAULT 0
    )");

    $pdo->exec("CREATE TABLE IF NOT EXISTS sessions (
        token TEXT NOT NULL PRIMARY KEY,
        user_id TEXT NOT NULL,
        created_at TEXT,
        expires_at INTEGER NOT NULL,
        ip TEXT,
        user_agent TEXT
    )");

    $pdo->exec("CREATE TABLE IF NOT EXISTS topologies (
        id TEXT NOT NULL PRIMARY KEY,
        name TEXT NOT NULL,
        owner_id TEXT NOT NULL,
        visibility TEXT NOT NULL DEFAULT 'private',
        data TEXT,
        node_count INTEGER NOT NULL DEFAULT 0,
        edge_count INTEGER NOT NULL DEFAULT 0,
        created_at TEXT,
        updated_at TEXT
    )");

    $pdo->exec("CREATE TABLE IF NOT EXISTS activity_logs (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id TEXT,
        username TEXT,
        action TEXT,
        target TEXT,
        detail TEXT,
        ip TEXT,
        created_at TEXT
    )");

    $pdo->exec("CREATE TABLE IF NOT EXISTS settings (
        skey TEXT NOT NULL PRIMARY KEY,
        sval TEXT,
        updated_at TEXT
    )");

    $pdo->exec("CREATE TABLE IF NOT EXISTS user_groups (
        id TEXT NOT NULL PRIMARY KEY,
        name TEXT NOT NULL UNIQUE,
        description TEXT,
        created_at TEXT,
        updated_at TEXT
    )");

    $pdo->exec("CREATE TABLE IF NOT EXISTS user_group_members (
        group_id TEXT NOT NULL,
        user_id TEXT NOT NULL,
        created_at TEXT,
        PRIMARY KEY (group_id, user_id)
    )");

    $pdo->exec("CREATE TABLE IF NOT EXISTS topo_shares (
        id TEXT NOT NULL PRIMARY KEY,
        topo_id TEXT NOT NULL,
        target_type TEXT NOT NULL,
        target_id TEXT NOT NULL,
        permission TEXT NOT NULL,
        created_by TEXT,
        created_at TEXT
    )");

    $pdo->exec("CREATE TABLE IF NOT EXISTS topo_presence (
        topo_id TEXT NOT NULL,
        user_id TEXT NOT NULL,
        username TEXT,
        last_seen INTEGER NOT NULL DEFAULT 0,
        PRIMARY KEY (topo_id, user_id)
    )");

    $pdo->exec("CREATE INDEX IF NOT EXISTS idx_sessions_user ON sessions(user_id)");
    $pdo->exec("CREATE INDEX IF NOT EXISTS idx_topo_owner ON topologies(owner_id)");
    $pdo->exec("CREATE INDEX IF NOT EXISTS idx_logs_created ON activity_logs(created_at)");
    $pdo->exec("CREATE INDEX IF NOT EXISTS idx_ugm_user ON user_group_members(user_id)");
    $pdo->exec("CREATE INDEX IF NOT EXISTS idx_shares_topo ON topo_shares(topo_id)");
    $pdo->exec("CREATE INDEX IF NOT EXISTS idx_shares_target ON topo_shares(target_type, target_id)");
    $pdo->exec("CREATE INDEX IF NOT EXISTS idx_presence_seen ON topo_presence(last_seen)");

    migrate_columns($pdo, $driver);
}

/**
 * 幂等加列:已存在的库平滑升级(表名/列名均为代码内常量,无注入风险)
 */
function ensure_column($pdo, $driver, $table, $col, $ddl)
{
    if ($driver === 'mysql') {
        $cols = array();
        foreach ($pdo->query('SHOW COLUMNS FROM ' . $table) as $r) {
            $cols[] = isset($r['Field']) ? $r['Field'] : '';
        }
        if (!in_array($col, $cols, true)) {
            $pdo->exec('ALTER TABLE ' . $table . ' ADD COLUMN ' . $ddl);
        }
        return;
    }
    $cols = array();
    foreach ($pdo->query('PRAGMA table_info(' . $table . ')') as $r) {
        $cols[] = $r['name'];
    }
    if (!in_array($col, $cols, true)) {
        $pdo->exec('ALTER TABLE ' . $table . ' ADD COLUMN ' . $ddl);
    }
}

/**
 * 列迁移:为老库补齐新增字段
 */
function migrate_columns($pdo, $driver)
{
    ensure_column($pdo, $driver, 'users', 'protected', 'protected INTEGER NOT NULL DEFAULT 0');
    ensure_column($pdo, $driver, 'topologies', 'rev', 'rev INTEGER NOT NULL DEFAULT 0');
    ensure_column($pdo, $driver, 'topologies', 'share_mode', 'share_mode INTEGER NOT NULL DEFAULT 0');
}