<?php
/**
 * license-php/lib/Database.php
 * PDO Database Connection & Schema Initialization.
 * Strictly uses prepared statements. No raw SQL interpolation.
 */

class Database {
    private static ?PDO $pdo = null;

    /**
     * Get or create PDO connection instance.
     */
    public static function getConnection(array $config): PDO {
        if (self::$pdo !== null) {
            return self::$pdo;
        }

        $dbCfg = $config['db'] ?? [];
        $driver = $dbCfg['driver'] ?? 'mysql';

        $options = [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => true,
        ];

        try {
            if ($driver === 'sqlite') {
                $sqlitePath = $dbCfg['sqlite_path'] ?? (dirname(__DIR__) . '/data/license.sqlite');
                $dir = dirname($sqlitePath);
                if (!file_exists($dir)) {
                    mkdir($dir, 0755, true);
                }
                self::$pdo = new PDO("sqlite:{$sqlitePath}", null, null, $options);
            } else {
                $host = $dbCfg['host'] ?? 'localhost';
                $port = $dbCfg['port'] ?? '3306';
                $name = $dbCfg['name'] ?? 'presensi_license_db';
                $charset = $dbCfg['charset'] ?? 'utf8mb4';
                $user = $dbCfg['user'] ?? 'root';
                $pass = $dbCfg['password'] ?? '';

                $dsn = "mysql:host={$host};port={$port};dbname={$name};charset={$charset}";
                self::$pdo = new PDO($dsn, $user, $pass, $options);
            }
        } catch (PDOException $e) {
            // Local resilient fallback: If MySQL is not reachable, gracefully use SQLite
            $sqlitePath = $dbCfg['sqlite_path'] ?? (dirname(__DIR__) . '/data/license.sqlite');
            $dir = dirname($sqlitePath);
            if (!file_exists($dir)) {
                mkdir($dir, 0755, true);
            }
            self::$pdo = new PDO("sqlite:{$sqlitePath}", null, null, $options);
            $driver = 'sqlite';
        }

        self::ensureSchema(self::$pdo, $driver);
        return self::$pdo;
    }

    /**
     * Manually set PDO connection (useful for unit/integration tests).
     */
    public static function setConnection(?PDO $pdo): void {
        self::$pdo = $pdo;
    }

    /**
     * Ensure minimal schema exists.
     */
    public static function ensureSchema(PDO $pdo, string $driver): void {
        if ($driver === 'sqlite') {
            $pdo->exec("
                CREATE TABLE IF NOT EXISTS licenses (
                    id TEXT PRIMARY KEY,
                    license_key TEXT UNIQUE NOT NULL,
                    license_key_hash TEXT NOT NULL,
                    product TEXT NOT NULL,
                    plan TEXT NOT NULL,
                    customer_name TEXT NOT NULL,
                    customer_email TEXT,
                    domain TEXT,
                    status TEXT NOT NULL,
                    license_type TEXT NOT NULL,
                    max_activation INTEGER DEFAULT 1,
                    activation_count INTEGER DEFAULT 0,
                    activated_at TEXT,
                    expires_at TEXT,
                    last_verified_at TEXT,
                    created_at TEXT NOT NULL,
                    updated_at TEXT NOT NULL,
                    notes TEXT
                );
                CREATE INDEX IF NOT EXISTS idx_licenses_hash ON licenses(license_key_hash);
                CREATE INDEX IF NOT EXISTS idx_licenses_domain ON licenses(domain);
                CREATE INDEX IF NOT EXISTS idx_licenses_status ON licenses(status);

                CREATE TABLE IF NOT EXISTS license_audit_logs (
                    id TEXT PRIMARY KEY,
                    license_id TEXT,
                    action TEXT NOT NULL,
                    domain TEXT,
                    ip_address TEXT,
                    user_agent TEXT,
                    metadata TEXT,
                    created_at TEXT NOT NULL
                );
                CREATE INDEX IF NOT EXISTS idx_logs_lic_id ON license_audit_logs(license_id);
                CREATE INDEX IF NOT EXISTS idx_logs_action ON license_audit_logs(action);
            ");
        } else {
            $pdo->exec("
                CREATE TABLE IF NOT EXISTS licenses (
                    id VARCHAR(64) NOT NULL PRIMARY KEY,
                    license_key VARCHAR(64) NOT NULL UNIQUE,
                    license_key_hash VARCHAR(64) NOT NULL,
                    product VARCHAR(64) NOT NULL DEFAULT 'presensi-smansa-pro',
                    plan VARCHAR(32) NOT NULL DEFAULT 'PRO',
                    customer_name VARCHAR(255) NOT NULL,
                    customer_email VARCHAR(255) NULL,
                    domain VARCHAR(255) NULL,
                    status VARCHAR(32) NOT NULL DEFAULT 'unused',
                    license_type VARCHAR(32) NOT NULL DEFAULT 'Subscription',
                    max_activation INT NOT NULL DEFAULT 1,
                    activation_count INT NOT NULL DEFAULT 0,
                    activated_at VARCHAR(64) NULL,
                    expires_at VARCHAR(64) NULL,
                    last_verified_at VARCHAR(64) NULL,
                    created_at VARCHAR(64) NOT NULL,
                    updated_at VARCHAR(64) NOT NULL,
                    notes TEXT NULL,
                    INDEX idx_licenses_hash (license_key_hash),
                    INDEX idx_licenses_domain (domain),
                    INDEX idx_licenses_status (status)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

                CREATE TABLE IF NOT EXISTS license_audit_logs (
                    id VARCHAR(64) NOT NULL PRIMARY KEY,
                    license_id VARCHAR(64) NULL,
                    action VARCHAR(64) NOT NULL,
                    domain VARCHAR(255) NULL,
                    ip_address VARCHAR(64) NULL,
                    user_agent VARCHAR(512) NULL,
                    metadata TEXT NULL,
                    created_at VARCHAR(64) NOT NULL,
                    INDEX idx_logs_lic_id (license_id),
                    INDEX idx_logs_action (action)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
            ");
        }
    }
}
