<?php
// db.php
$dbHost = 'localhost';
$dbUser = 'root';
$dbPass = '';
$dbName = 'mahfuza_corporate_holdings';

try {
    // 1. Connect without dbName first to ensure the database exists
    $pdo = new PDO("mysql:host=$dbHost", $dbUser, $dbPass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // Create database if it does not exist
    $pdo->exec("CREATE DATABASE IF NOT EXISTS `$dbName` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
    
    // 2. Re-connect with dbName
    $pdo = new PDO("mysql:host=$dbHost;dbname=$dbName;charset=utf8mb4", $dbUser, $dbPass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

    // Check if tables exist, if not, create them
    $tableCheck = $pdo->query("SHOW TABLES LIKE 'settings'")->fetch();
    if (!$tableCheck) {
        // Create settings table with all dynamic fields
        $pdo->exec("CREATE TABLE IF NOT EXISTS settings (
            id INT AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(255) DEFAULT '',
            subtitle TEXT DEFAULT NULL,
            about_text TEXT DEFAULT NULL,
            address VARCHAR(255) DEFAULT '',
            logo TEXT DEFAULT NULL,
            video TEXT DEFAULT NULL,
            about_title VARCHAR(255) DEFAULT 'Driving Progress Across Horizons',
            about_feat1_title VARCHAR(255) DEFAULT 'Innovation First',
            about_feat1_desc TEXT DEFAULT NULL,
            about_feat1_icon VARCHAR(50) DEFAULT 'bi-cpu',
            about_feat2_title VARCHAR(255) DEFAULT 'Global Standard',
            about_feat2_desc TEXT DEFAULT NULL,
            about_feat2_icon VARCHAR(50) DEFAULT 'bi-globe-americas',
            stat1_value VARCHAR(50) DEFAULT '04+',
            stat1_label VARCHAR(100) DEFAULT 'Operating Sectors',
            stat2_value VARCHAR(50) DEFAULT '250+',
            stat2_label VARCHAR(100) DEFAULT 'Expert Workforce',
            stat3_value VARCHAR(50) DEFAULT '12+',
            stat3_label VARCHAR(100) DEFAULT 'Global Partners',
            stat4_value VARCHAR(50) DEFAULT '100%',
            stat4_label VARCHAR(100) DEFAULT 'Compliance',
            companies_title VARCHAR(255) DEFAULT 'Our Commercial Enterprises',
            team_title VARCHAR(255) DEFAULT 'Corporate Leadership',
            contact_title VARCHAR(255) DEFAULT 'Corporate HQ',
            hero_btn1_text VARCHAR(100) DEFAULT 'Explore Portfolio',
            hero_btn1_link VARCHAR(255) DEFAULT '#companies',
            hero_btn2_text VARCHAR(100) DEFAULT 'Get In Touch',
            hero_btn2_link VARCHAR(255) DEFAULT '#contact'
        ) ENGINE=InnoDB");

        // Create companies table with image banner support
        $pdo->exec("CREATE TABLE IF NOT EXISTS companies (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            link TEXT NOT NULL,
            img TEXT DEFAULT NULL,
            sort_order INT DEFAULT 0
        ) ENGINE=InnoDB");

        // Create team table
        $pdo->exec("CREATE TABLE IF NOT EXISTS team (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            role VARCHAR(255) NOT NULL,
            img TEXT NOT NULL,
            sort_order INT DEFAULT 0
        ) ENGINE=InnoDB");

        // Insert initial default settings
        $stmt = $pdo->prepare("INSERT INTO settings (
            id, title, subtitle, about_text, address, logo, video,
            about_title, about_feat1_title, about_feat1_desc, about_feat1_icon,
            about_feat2_title, about_feat2_desc, about_feat2_icon,
            stat1_value, stat1_label, stat2_value, stat2_label,
            stat3_value, stat3_label, stat4_value, stat4_label,
            companies_title, team_title, contact_title,
            hero_btn1_text, hero_btn1_link, hero_btn2_text, hero_btn2_link
        ) VALUES (1, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        $stmt->execute([
            "Mahfuza Corporate Holdings",
            "Accelerating modern engineering, human deployment workflows, and comprehensive technical solutions globally.",
            "Our multi-disciplinary business entity operates across tech ecosystems, dynamic infrastructures, modern global operations, and human resource solutions. We build ecosystems centered around premium sustainability.",
            "Mohakhali C/A, Dhaka, Bangladesh",
            "https://placehold.co/80x80/0284c7/ffffff?text=M",
            "",
            "Driving Progress Across Horizons",
            "Innovation First",
            "Harnessing advanced operational dynamics to reshape platforms.",
            "bi-cpu",
            "Global Standard",
            "Delivering export-ready skill management globally.",
            "bi-globe-americas",
            "04+", "Operating Sectors",
            "250+", "Expert Workforce",
            "12+", "Global Partners",
            "100%", "Compliance",
            "Our Commercial Enterprises",
            "Corporate Leadership",
            "Corporate HQ",
            "Explore Portfolio", "#companies",
            "Get In Touch", "#contact"
        ]);

        // Insert initial default companies
        $stmtComp = $pdo->prepare("INSERT INTO companies (name, link, img, sort_order) VALUES (?, ?, ?, ?)");
        $stmtComp->execute(["Smart IT Solution", "https://smartitsolution-demo.com", null, 0]);
        $stmtComp->execute(["Mahfuza Overseas Employment", "https://mahfuzaoverseas-demo.com", null, 1]);

        // Insert initial default team members
        $stmtTeam = $pdo->prepare("INSERT INTO team (name, role, img, sort_order) VALUES (?, ?, ?, ?)");
        $stmtTeam->execute(["Ibrahim Khalil", "Chief Executive Officer / Managing Director", "https://placehold.co/300x300/0f172a/ffffff?text=IK", 0]);
        $stmtTeam->execute(["Rahman Chowdhury", "Head of Technical Architecture", "https://placehold.co/300x300/1e293b/ffffff?text=RC", 1]);
    } else {
        // Run migrations for settings table if columns are missing
        $settingsColumns = [
            'about_title' => 'VARCHAR(255) DEFAULT "Driving Progress Across Horizons"',
            'about_feat1_title' => 'VARCHAR(255) DEFAULT "Innovation First"',
            'about_feat1_desc' => 'TEXT DEFAULT NULL',
            'about_feat1_icon' => 'VARCHAR(50) DEFAULT "bi-cpu"',
            'about_feat2_title' => 'VARCHAR(255) DEFAULT "Global Standard"',
            'about_feat2_desc' => 'TEXT DEFAULT NULL',
            'about_feat2_icon' => 'VARCHAR(50) DEFAULT "bi-globe-americas"',
            'stat1_value' => 'VARCHAR(50) DEFAULT "04+"',
            'stat1_label' => 'VARCHAR(100) DEFAULT "Operating Sectors"',
            'stat2_value' => 'VARCHAR(50) DEFAULT "250+"',
            'stat2_label' => 'VARCHAR(100) DEFAULT "Expert Workforce"',
            'stat3_value' => 'VARCHAR(50) DEFAULT "12+"',
            'stat3_label' => 'VARCHAR(100) DEFAULT "Global Partners"',
            'stat4_value' => 'VARCHAR(50) DEFAULT "100%"',
            'stat4_label' => 'VARCHAR(100) DEFAULT "Compliance"',
            'companies_title' => 'VARCHAR(255) DEFAULT "Our Commercial Enterprises"',
            'team_title' => 'VARCHAR(255) DEFAULT "Corporate Leadership"',
            'contact_title' => 'VARCHAR(255) DEFAULT "Corporate HQ"',
            'hero_btn1_text' => 'VARCHAR(100) DEFAULT "Explore Portfolio"',
            'hero_btn1_link' => 'VARCHAR(255) DEFAULT "#companies"',
            'hero_btn2_text' => 'VARCHAR(100) DEFAULT "Get In Touch"',
            'hero_btn2_link' => 'VARCHAR(255) DEFAULT "#contact"'
        ];

        foreach ($settingsColumns as $colName => $colDef) {
            $checkCol = $pdo->query("SHOW COLUMNS FROM `settings` LIKE '$colName'")->fetch();
            if (!$checkCol) {
                $pdo->exec("ALTER TABLE `settings` ADD COLUMN `$colName` $colDef");
                
                // Seed initial description values which are not covered by default definition constraints
                if ($colName === 'about_feat1_desc') {
                    $pdo->exec("UPDATE `settings` SET `about_feat1_desc` = 'Harnessing advanced operational dynamics to reshape platforms.' WHERE `id` = 1");
                } elseif ($colName === 'about_feat2_desc') {
                    $pdo->exec("UPDATE `settings` SET `about_feat2_desc` = 'Delivering export-ready skill management globally.' WHERE `id` = 1");
                }
            }
        }

        // Run migrations for companies table if img column is missing
        $checkCompCol = $pdo->query("SHOW COLUMNS FROM `companies` LIKE 'img'")->fetch();
        if (!$checkCompCol) {
            $pdo->exec("ALTER TABLE `companies` ADD COLUMN `img` TEXT DEFAULT NULL");
        }
    }
} catch (PDOException $e) {
    header('Content-Type: application/json', true, 500);
    echo json_encode([
        'success' => false, 
        'message' => 'MySQL database connection failed: ' . $e->getMessage() . '. Please verify that Laragon MySQL is running and settings in db.php are correct.'
    ]);
    exit;
}
