<?php
namespace Peak;

use PDO;
use PDOException;

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

    public static function get(): PDO {
        if (self::$pdo === null) {
            try {
                self::$pdo = new PDO(
                    'mysql:host=' . Config::$DB_HOST . ';dbname=' . Config::$DB_NAME . ';charset=utf8mb4',
                    Config::$DB_USER,
                    Config::$DB_PASS,
                    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_EMULATE_PREPARES => false]
                );
            } catch (PDOException $e) {
                self::$pdo = new PDO(
                    'mysql:host=' . Config::$DB_HOST . ';charset=utf8mb4',
                    Config::$DB_USER,
                    Config::$DB_PASS,
                    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
                );
                self::$pdo->exec("CREATE DATABASE IF NOT EXISTS `" . Config::$DB_NAME . "` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
                self::$pdo->exec("USE `" . Config::$DB_NAME . "`");
                self::$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
                self::$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
            }
        }
        if (!self::$initialized) {
            self::autoInit();
            self::$initialized = true;
        }
        return self::$pdo;
    }

    private static function autoInit(): void {
        try {
            self::$pdo->query("SELECT 1 FROM users LIMIT 1");
        } catch (PDOException $e) {
            if (in_array($e->getCode(), ['42S02', '42S22'], true) || strpos($e->getMessage(), 'Base table') !== false || preg_match("/Table\s+'.*?'\s+doesn't\s+exist/i", $e->getMessage())) {
                self::ensureTables();
                self::ensureSeedData();
            } else {
                throw $e;
            }
        }
    }

    public static function ensureTables(?PDO $pdo = null): void {
        $pdo ??= self::$pdo;
        $pdo->exec("CREATE TABLE IF NOT EXISTS users (id INT AUTO_INCREMENT PRIMARY KEY, full_name VARCHAR(255) NOT NULL DEFAULT 'Peak Admin', email VARCHAR(255) NOT NULL DEFAULT 'admin@peakplatform.com', password VARCHAR(255) DEFAULT NULL, role ENUM('Admin','Editor','Viewer') NOT NULL DEFAULT 'Admin', email_verified TINYINT(1) DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
        $pdo->exec("CREATE TABLE IF NOT EXISTS password_reset_tokens (id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, token VARCHAR(255) NOT NULL, expires_at DATETIME NOT NULL, used TINYINT(1) DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
        $pdo->exec("CREATE TABLE IF NOT EXISTS settings (id INT AUTO_INCREMENT PRIMARY KEY, setting_key VARCHAR(255) NOT NULL UNIQUE, setting_value TEXT, setting_type ENUM('text','toggle','select') NOT NULL DEFAULT 'text', category VARCHAR(100) NOT NULL DEFAULT 'general', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
        $pdo->exec("CREATE TABLE IF NOT EXISTS workflows (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, status ENUM('active','inactive','draft') NOT NULL DEFAULT 'active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
        $pdo->exec("CREATE TABLE IF NOT EXISTS activity_log (id INT AUTO_INCREMENT PRIMARY KEY, action VARCHAR(255) NOT NULL, details TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
        $pdo->exec("CREATE TABLE IF NOT EXISTS verification_codes (id INT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(255) NOT NULL, code VARCHAR(6) NOT NULL, full_name VARCHAR(255) NOT NULL, password_hash VARCHAR(255) NOT NULL, expires_at DATETIME NOT NULL, used TINYINT(1) DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_email_code (email, code), INDEX idx_expires (expires_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    }

    public static function ensureSeedData(?PDO $pdo = null): void {
        $pdo ??= self::$pdo;
        $defaults = [
            ['full_name', 'Peak Admin', 'text', 'profile'], ['email', 'admin@peakplatform.com', 'text', 'profile'], ['role', 'Admin', 'select', 'profile'],
            ['email_notifications', '1', 'toggle', 'notifications'], ['workflow_alerts', '1', 'toggle', 'notifications'], ['weekly_digest', '0', 'toggle', 'notifications'],
            ['theme', 'Dark', 'select', 'appearance'], ['compact_mode', '0', 'toggle', 'appearance'], ['two_factor_auth', '0', 'toggle', 'account'],
            ['currency_symbol', '$', 'text', 'pricing'], ['ai_system_prompt', 'You are Peak AI, an intelligent assistant for the Peak workflow platform.', 'text', 'ai'],
            ['ai_model', 'groq-compound', 'text', 'ai'], ['ai_temperature', '0.7', 'text', 'ai'], ['ai_max_tokens', '1024', 'text', 'ai'], ['ai_knowledge', '', 'text', 'ai'],
        ];
        $stmt = $pdo->prepare("INSERT IGNORE INTO settings (setting_key, setting_value, setting_type, category) VALUES (?, ?, ?, ?)");
        foreach ($defaults as $d) $stmt->execute($d);
        $count = $pdo->query("SELECT COUNT(*) AS c FROM users")->fetch(PDO::FETCH_ASSOC);
        if ($count['c'] === 0) {
            $hash = password_hash('admin123', PASSWORD_BCRYPT);
            $pdo->prepare("INSERT INTO users (full_name, email, password, role, email_verified) VALUES (?, ?, ?, ?, 1)")->execute(['Peak Admin', 'admin@peakplatform.com', $hash, 'Admin']);
        }
    }

    public static function query(string $sql, array $params = []): \PDOStatement {
        $s = self::get()->prepare($sql); $s->execute($params); return $s;
    }

    public static function fetch(string $sql, array $params = []): ?array {
        $r = self::query($sql, $params)->fetch(); return $r ?: null;
    }

    public static function fetchAll(string $sql, array $params = []): array {
        return self::query($sql, $params)->fetchAll();
    }

    public static function insert(string $sql, array $params = []): string {
        self::query($sql, $params); return self::get()->lastInsertId();
    }
}
