<?php
// config/db.php - Database Connection Handler (PDO MySQL with SQLite Fallback)

function getDbConnection()
{
    $mysqlHost = 'localhost';
    $mysqlDb   = 'hos_dep_news';
    $charset   = 'utf8mb4';

    // List of credentials to try (Local MySQL / XAMPP / Custom Passwords)
    $credentials = [
        ['user' => 'root', 'pass' => '@Samui@10742']
    ];

    // 1. Try Connecting to MySQL Server
    foreach ($credentials as $cred) {
        try {
            $pdoServer = new PDO("mysql:host=$mysqlHost;charset=$charset", $cred['user'], $cred['pass'], [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_TIMEOUT => 1
            ]);
            $pdoServer->exec("CREATE DATABASE IF NOT EXISTS `$mysqlDb` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");

            $pdo = new PDO("mysql:host=$mysqlHost;dbname=$mysqlDb;charset=$charset", $cred['user'], $cred['pass'], [
                PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES   => false,
            ]);

            $check = $pdo->query("SHOW TABLES LIKE 'news'");
            if ($check->rowCount() === 0) {
                seedDatabase($pdo, 'mysql');
            } else {
                ensureAdminTables($pdo, 'mysql');
            }

            return ['pdo' => $pdo, 'driver' => 'mysql'];
        } catch (\Throwable $e) {
            continue; // Try next credential
        }
    }

    // 2. Fallback to SQLite if MySQL is offline or not configured
    try {
        $sqliteFile = __DIR__ . '/../database.sqlite';
        $pdo = new PDO("sqlite:$sqliteFile", null, null, [
            PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        ]);

        $pdo->exec("PRAGMA foreign_keys = ON;");

        $check = $pdo->query("SELECT name FROM sqlite_master WHERE type='table' AND name='news'");
        if (!$check->fetch()) {
            seedDatabase($pdo, 'sqlite');
        } else {
            ensureAdminTables($pdo, 'sqlite');
        }

        return ['pdo' => $pdo, 'driver' => 'sqlite'];
    } catch (\Throwable $e) {
        throw new \Exception("Database Connection Error: " . $e->getMessage());
    }
}

function ensureAdminTables($pdo, $driver = 'mysql')
{
    try {
        if ($driver === 'sqlite') {
            $pdo->exec("
                CREATE TABLE IF NOT EXISTS system_settings (
                    setting_key VARCHAR(100) PRIMARY KEY,
                    setting_value TEXT NOT NULL
                )
            ");
            $pdo->exec("
                CREATE TABLE IF NOT EXISTS templates (
                    id VARCHAR(50) PRIMARY KEY,
                    agency_id VARCHAR(10) NOT NULL,
                    name VARCHAR(100) NOT NULL,
                    category_id VARCHAR(10) NOT NULL,
                    doc_num_format VARCHAR(100) NOT NULL,
                    title VARCHAR(255) NOT NULL,
                    content TEXT NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ");
            // Check if admin_password column exists in agencies
            $cols = $pdo->query("PRAGMA table_info(agencies)")->fetchAll();
            $hasPass = false;
            foreach ($cols as $c) {
                if ($c['name'] === 'admin_password') {
                    $hasPass = true;
                    break;
                }
            }
            if (!$hasPass) {
                $pdo->exec("ALTER TABLE agencies ADD COLUMN admin_password VARCHAR(255) DEFAULT '1234'");
            }
            // Check if gdrive_folder_id column exists in agencies
            $hasGdriveFolder = false;
            foreach ($cols as $c) {
                if ($c['name'] === 'gdrive_folder_id') {
                    $hasGdriveFolder = true;
                    break;
                }
            }
            if (!$hasGdriveFolder) {
                $pdo->exec("ALTER TABLE agencies ADD COLUMN gdrive_folder_id VARCHAR(255) DEFAULT NULL");
            }
            
            // Check if is_hidden column exists in news
            $newsCols = $pdo->query("PRAGMA table_info(news)")->fetchAll();
            $hasHidden = false;
            foreach ($newsCols as $c) {
                if ($c['name'] === 'is_hidden') {
                    $hasHidden = true;
                    break;
                }
            }
            if (!$hasHidden) {
                $pdo->exec("ALTER TABLE news ADD COLUMN is_hidden INTEGER NOT NULL DEFAULT 0");
            }

            // Check if file_url column exists in attachments
            $attCols = $pdo->query("PRAGMA table_info(attachments)")->fetchAll();
            $hasFileUrl = false;
            foreach ($attCols as $c) {
                if ($c['name'] === 'file_url') {
                    $hasFileUrl = true;
                    break;
                }
            }
            if (!$hasFileUrl) {
                $pdo->exec("ALTER TABLE attachments ADD COLUMN file_url TEXT DEFAULT NULL");
            }
            
            // Check if agency_id column exists in categories
            $catCols = $pdo->query("PRAGMA table_info(categories)")->fetchAll();
            $hasCatAgency = false;
            foreach ($catCols as $c) {
                if ($c['name'] === 'agency_id') {
                    $hasCatAgency = true;
                    break;
                }
            }
            if (!$hasCatAgency) {
                $pdo->exec("ALTER TABLE categories ADD COLUMN agency_id VARCHAR(20) DEFAULT 'SUPER_ADMIN'");
            }
            
            // Create activity_logs table
            $pdo->exec("
                CREATE TABLE IF NOT EXISTS activity_logs (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    user_id VARCHAR(50) NOT NULL,
                    user_name VARCHAR(100) NOT NULL,
                    action VARCHAR(100) NOT NULL,
                    details TEXT,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ");
        } else {
            $pdo->exec("
                CREATE TABLE IF NOT EXISTS `system_settings` (
                    `setting_key` VARCHAR(100) NOT NULL,
                    `setting_value` TEXT NOT NULL,
                    PRIMARY KEY (`setting_key`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            ");

            $pdo->exec("
                CREATE TABLE IF NOT EXISTS `categories` (
                `id` VARCHAR(10) NOT NULL,
                `name` VARCHAR(255) NOT NULL,
                `agency_id` VARCHAR(20) DEFAULT 'SUPER_ADMIN',
                PRIMARY KEY (`id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
            ");

            $pdo->exec("

                CREATE TABLE IF NOT EXISTS `templates` (
                    `id` VARCHAR(50) NOT NULL,
                    `agency_id` VARCHAR(10) NOT NULL,
                    `name` VARCHAR(100) NOT NULL,
                    `category_id` VARCHAR(10) NOT NULL,
                    `doc_num_format` VARCHAR(100) NOT NULL,
                    `title` VARCHAR(255) NOT NULL,
                    `content` TEXT NOT NULL,
                    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    PRIMARY KEY (`id`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            ");
            $cols = $pdo->query("SHOW COLUMNS FROM agencies LIKE 'admin_password'")->fetchAll();
            if (count($cols) === 0) {
                $pdo->exec("ALTER TABLE agencies ADD COLUMN admin_password VARCHAR(255) DEFAULT '1234'");
            }

            $colsGdrive = $pdo->query("SHOW COLUMNS FROM agencies LIKE 'gdrive_folder_id'")->fetchAll();
            if (count($colsGdrive) === 0) {
                $pdo->exec("ALTER TABLE agencies ADD COLUMN gdrive_folder_id VARCHAR(255) DEFAULT NULL");
            }
            
            $newsCols = $pdo->query("SHOW COLUMNS FROM news LIKE 'is_hidden'")->fetchAll();
            if (count($newsCols) === 0) {
                $pdo->exec("ALTER TABLE news ADD COLUMN is_hidden TINYINT(1) NOT NULL DEFAULT 0");
            }

            $attCols = $pdo->query("SHOW COLUMNS FROM attachments LIKE 'file_url'")->fetchAll();
            if (count($attCols) === 0) {
                $pdo->exec("ALTER TABLE attachments ADD COLUMN file_url TEXT DEFAULT NULL");
            }

            $catCols = $pdo->query("SHOW COLUMNS FROM categories LIKE 'agency_id'")->fetchAll();
            if (count($catCols) === 0) {
                $pdo->exec("ALTER TABLE categories ADD COLUMN agency_id VARCHAR(20) DEFAULT 'SUPER_ADMIN'");
            }

            // Create activity_logs table
            $pdo->exec("
                CREATE TABLE IF NOT EXISTS `activity_logs` (
                    `id` INT AUTO_INCREMENT PRIMARY KEY,
                    `user_id` VARCHAR(50) NOT NULL,
                    `user_name` VARCHAR(100) NOT NULL,
                    `action` VARCHAR(100) NOT NULL,
                    `details` TEXT,
                    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            ");
        }

        // Insert default system settings if empty
        $stmt = $pdo->query("SELECT COUNT(*) AS total FROM system_settings");
        if ($stmt->fetch()['total'] == 0) {
            $initSettings = [
                'hospital_name' => 'โรงพยาบาลศูนย์ / โรงพยาบาลทั่วไป กระทรวงสาธารณสุข',
                'system_title' => 'ระบบข่าวประชาสัมพันธ์และหนังสือเวียน',
                'hospital_logo_url' => 'https://samuihospital.moph.go.th/dashboard/waiting/assets/img/logo.png',
                'super_admin_password' => '1234',
                'ticker_text' => 'ระบบข่าวสารและหนังสือเวียนภายในองค์กร โรงพยาบาลเปิดให้บริการตามปกติ',
                'gdrive_gas_url' => '',
                'footer_text' => 'กระทรวงสาธารณสุข - Ministry of Public Health Thailand\nพัฒนาเพื่อการปฏิบัติงานภายในโรงพยาบาลและหน่วยงานในสังกัด | รองรับระบบรักษาความปลอดภัยสารสนเทศ'
            ];
            $ins = $pdo->prepare("INSERT INTO system_settings (setting_key, setting_value) VALUES (?, ?)");
            foreach ($initSettings as $k => $v) {
                $ins->execute([$k, $v]);
            }
        }
        // Check for missing footer_text
        $stmtF = $pdo->query("SELECT COUNT(*) AS c FROM system_settings WHERE setting_key = 'footer_text'");
        if ($stmtF->fetch()['c'] == 0) {
            $pdo->exec("INSERT INTO system_settings (setting_key, setting_value) VALUES ('footer_text', 'กระทรวงสาธารณสุข - Ministry of Public Health Thailand\nพัฒนาเพื่อการปฏิบัติงานภายในโรงพยาบาลและหน่วยงานในสังกัด | รองรับระบบรักษาความปลอดภัยสารสนเทศ')");
        }
    } catch (\Exception $e) {
        error_log("ensureAdminTables Error: " . $e->getMessage());
        // Silently continue if table already upgraded
    }
}

function seedDatabase($pdo, $driver = 'mysql')
{
    if ($driver === 'sqlite') {
        $pdo->exec("
            CREATE TABLE IF NOT EXISTS agencies (
                id VARCHAR(10) PRIMARY KEY,
                name VARCHAR(255) NOT NULL,
                code VARCHAR(10) NOT NULL,
                admin_password VARCHAR(255) DEFAULT '1234',
                gdrive_folder_id VARCHAR(255) DEFAULT NULL
            );

            CREATE TABLE IF NOT EXISTS categories (
                id VARCHAR(10) PRIMARY KEY,
                name VARCHAR(255) NOT NULL,
                agency_id VARCHAR(20) DEFAULT 'SUPER_ADMIN'
            );

            CREATE TABLE IF NOT EXISTS news (
                id VARCHAR(20) PRIMARY KEY,
                doc_num VARCHAR(100) NOT NULL,
                title VARCHAR(500) NOT NULL,
                agency_id VARCHAR(10) NOT NULL,
                category_id VARCHAR(10) NOT NULL,
                content TEXT NOT NULL,
                publish_date DATE NOT NULL,
                is_pinned INTEGER NOT NULL DEFAULT 0,
                is_urgent INTEGER NOT NULL DEFAULT 0,
                views INTEGER NOT NULL DEFAULT 0,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (agency_id) REFERENCES agencies(id) ON DELETE CASCADE,
                FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
            );

            CREATE TABLE IF NOT EXISTS attachments (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                news_id VARCHAR(20) NOT NULL,
                file_name VARCHAR(255) NOT NULL,
                file_type VARCHAR(20) NOT NULL DEFAULT 'pdf',
                file_size VARCHAR(50) DEFAULT '1.0 MB',
                file_url TEXT DEFAULT NULL,
                FOREIGN KEY (news_id) REFERENCES news(id) ON DELETE CASCADE
            );

            CREATE TABLE IF NOT EXISTS system_settings (
                setting_key VARCHAR(100) PRIMARY KEY,
                setting_value TEXT NOT NULL
            );

            CREATE TABLE IF NOT EXISTS templates (
                id VARCHAR(50) PRIMARY KEY,
                agency_id VARCHAR(10) NOT NULL,
                name VARCHAR(100) NOT NULL,
                category_id VARCHAR(10) NOT NULL,
                doc_num_format VARCHAR(100) NOT NULL,
                title VARCHAR(255) NOT NULL,
                content TEXT NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            );
        ");

        // Seed Default Agencies
        $agencies = [
            ['MED', 'องค์กรแพทย์ / กลุ่มงานการแพทย์', 'MED', '1234'],
            ['NURSE', 'กลุ่มงานการพยาบาล', 'NRS', '1234'],
            ['PHARM', 'กลุ่มงานเภสัชกรรมและคลังยา', 'RX', '1234'],
            ['IT', 'ศูนย์เทคโนโลยีสารสนเทศ (HIS)', 'IT', '1234'],
            ['ADMIN', 'กลุ่มงานบริหารทั่วไป / สารบรรณ', 'ADM', '1234']
        ];
        $stmt = $pdo->prepare("INSERT INTO agencies (id, name, code, admin_password) VALUES (?, ?, ?, ?)");
        foreach ($agencies as $a) {
            $stmt->execute($a);
        }

        // Seed Default Categories
        $cats = [
            ['MEMO', '📜 บันทึกข้อความ / แจ้งเวียน'],
            ['ORDERS', '⚖️ คำสั่ง / ประกาศโรงพยาบาล'],
            ['CPG', '🩺 แนวทาง CPG / ระเบียบ SOP'],
            ['MEETING', '🎓 ข่าวประชุมวิชาการ / ฝึกอบรม'],
            ['HR', '🎁 สวัสดิการ / ทรัพยากรบุคคล']
        ];
        $stmtCat = $pdo->prepare("INSERT INTO categories (id, name) VALUES (?, ?)");
        foreach ($cats as $c) {
            $stmtCat->execute($c);
        }

        // Seed Default Templates
        $templates = [
            ['TPL-001', 'SUPER_ADMIN', 'แนวทาง CPG', 'CPG', 'สธ 0202/ว {random}', 'แนวทางเวชปฏิบัติ (CPG) การดูแลรักษาผู้ป่วย... ฉบับปรับปรุงใหม่', "เรียน คณะแพทย์, พยาบาล และบุคลากรทางการแพทย์ทุกท่าน\n\nกลุ่มงานการแพทย์ขอแจ้งอนุมัติใช้แนวทางเวชปฏิบัติ (CPG) และคู่มือระเบียบปฏิบัติ (SOP) เรื่อง... ประจำปีงบประมาณ 2569\n\nโปรดศึกษารายละเอียดแนวทาง CPG ตามเอกสารแนบเพื่อถือปฏิบัติตามมาตรฐานเดียวกัน"],
            ['TPL-002', 'SUPER_ADMIN', 'หนังสือเวียนด่วน', 'MEMO', 'สธ 0201/ว {random}', 'บันทึกข้อความแจ้งเวียนด่วน เรื่อง ...', "เรียน หัวหน้ากลุ่มงานและผู้รับผิดชอบงานทุกฝ่าย\n\nตามที่โรงพยาบาลได้กำหนดมาตรการ... จึงขอเรียนแจ้งแนวทางปฏิบัติเพิ่มเติมดังนี้\n1. ...\n2. ...\n\nจึงเรียนมาเพื่อโปรดทราบและถือปฏิบัติต่อไป"],
            ['TPL-003', 'SUPER_ADMIN', 'คำสั่งโรงพยาบาล', 'ORDERS', 'คำสั่ง รพ. ที่ {random}/2569', 'คำสั่งแต่งตั้งคณะกรรมการ/คณะทำงาน ...', "เพื่อให้การดำเนินงานด้าน... เป็นไปด้วยความเรียบร้อยและมีประสิทธิภาพ\n\nอาศัยอำนาจตามระเบียบบริหารราชการแผ่นดิน จึงแต่งตั้งคณะทำงานโดยมีรายนามตามเอกสารแนบท้ายคำสั่งนี้"]
        ];
        $stmtTpl = $pdo->prepare("INSERT INTO templates (id, agency_id, name, category_id, doc_num_format, title, content) VALUES (?, ?, ?, ?, ?, ?, ?)");
        foreach ($templates as $t) {
            $stmtTpl->execute($t);
        }

        ensureAdminTables($pdo, 'sqlite');
    } else {
        $sql = file_get_contents(__DIR__ . '/../schema.sql');
        $pdo->exec($sql);
        ensureAdminTables($pdo, 'mysql');
        return;
    }
}