<?php
/**
 * Digital Practice - Database Connection & Setup
 */
require_once __DIR__ . '/config.php';

try {
    // Connect without database first to create it
    $pdo_init = new PDO("mysql:host=" . DB_HOST, DB_USER, DB_PASS, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);
    
    // Create database if not exists
    $pdo_init->exec("CREATE DATABASE IF NOT EXISTS `" . DB_NAME . "` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
    $pdo_init = null;

    // Connect to the database
    $pdo = new PDO("mysql:host=" . DB_HOST . ";dbname=" . DB_NAME . ";charset=utf8mb4", DB_USER, DB_PASS, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
    ]);

    // Create tables
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS `admins` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `username` VARCHAR(50) NOT NULL UNIQUE,
            `password` VARCHAR(255) NOT NULL,
            `full_name` VARCHAR(100) NOT NULL,
            `email` VARCHAR(100) NOT NULL,
            `role` ENUM('super_admin','admin','editor') DEFAULT 'admin',
            `avatar` VARCHAR(255) DEFAULT NULL,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ) ENGINE=InnoDB;

        CREATE TABLE IF NOT EXISTS `services` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `title` VARCHAR(150) NOT NULL,
            `slug` VARCHAR(150) NOT NULL UNIQUE,
            `short_desc` TEXT,
            `full_desc` LONGTEXT,
            `icon` VARCHAR(50) DEFAULT 'fas fa-cogs',
            `image` VARCHAR(255) DEFAULT NULL,
            `sort_order` INT DEFAULT 0,
            `is_active` TINYINT(1) DEFAULT 1,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB;

        CREATE TABLE IF NOT EXISTS `products` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `name` VARCHAR(150) NOT NULL,
            `slug` VARCHAR(150) NOT NULL UNIQUE,
            `tagline` VARCHAR(255),
            `description` LONGTEXT,
            `subdomain` VARCHAR(100) DEFAULT NULL,
            `icon` VARCHAR(50) DEFAULT 'fas fa-box',
            `image` VARCHAR(255) DEFAULT NULL,
            `url` VARCHAR(255) DEFAULT NULL,
            `sort_order` INT DEFAULT 0,
            `is_active` TINYINT(1) DEFAULT 1,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ) ENGINE=InnoDB;

        CREATE TABLE IF NOT EXISTS `team_members` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `name` VARCHAR(100) NOT NULL,
            `position` VARCHAR(100) NOT NULL,
            `category` ENUM('board','executive','team') DEFAULT 'team',
            `bio` LONGTEXT,
            `photo` VARCHAR(255) DEFAULT NULL,
            `linkedin` VARCHAR(255) DEFAULT NULL,
            `twitter` VARCHAR(255) DEFAULT NULL,
            `sort_order` INT DEFAULT 0,
            `is_active` TINYINT(1) DEFAULT 1,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ) ENGINE=InnoDB;

        CREATE TABLE IF NOT EXISTS `blog_categories` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `name` VARCHAR(100) NOT NULL,
            `slug` VARCHAR(100) NOT NULL UNIQUE,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ) ENGINE=InnoDB;

        CREATE TABLE IF NOT EXISTS `blog_posts` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `title` VARCHAR(255) NOT NULL,
            `slug` VARCHAR(255) NOT NULL UNIQUE,
            `excerpt` TEXT,
            `content` LONGTEXT,
            `featured_image` VARCHAR(255) DEFAULT NULL,
            `category` VARCHAR(100) DEFAULT 'General',
            `author_id` INT DEFAULT NULL,
            `is_published` TINYINT(1) DEFAULT 0,
            `published_at` DATETIME DEFAULT NULL,
            `views` INT DEFAULT 0,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (`author_id`) REFERENCES `admins`(`id`) ON DELETE SET NULL
        ) ENGINE=InnoDB;

        CREATE TABLE IF NOT EXISTS `case_studies` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `title` VARCHAR(255) NOT NULL,
            `slug` VARCHAR(255) NOT NULL UNIQUE,
            `client_name` VARCHAR(150),
            `problem` LONGTEXT,
            `solution` LONGTEXT,
            `results` LONGTEXT,
            `stat_label` VARCHAR(100) DEFAULT NULL,
            `stat_value` VARCHAR(50) DEFAULT NULL,
            `image` VARCHAR(255) DEFAULT NULL,
            `is_published` TINYINT(1) DEFAULT 0,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ) ENGINE=InnoDB;

        CREATE TABLE IF NOT EXISTS `contact_submissions` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `first_name` VARCHAR(100) NOT NULL,
            `last_name` VARCHAR(100) NOT NULL,
            `email` VARCHAR(150) NOT NULL,
            `phone` VARCHAR(30) DEFAULT NULL,
            `company` VARCHAR(150) DEFAULT NULL,
            `job_title` VARCHAR(100) DEFAULT NULL,
            `service_interest` VARCHAR(150) DEFAULT NULL,
            `message` LONGTEXT,
            `is_read` TINYINT(1) DEFAULT 0,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ) ENGINE=InnoDB;

        CREATE TABLE IF NOT EXISTS `site_settings` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `setting_key` VARCHAR(100) NOT NULL UNIQUE,
            `setting_value` LONGTEXT,
            `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB;

        CREATE TABLE IF NOT EXISTS `partners` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `name` VARCHAR(100) NOT NULL,
            `logo` VARCHAR(255) DEFAULT NULL,
            `url` VARCHAR(255) DEFAULT NULL,
            `sort_order` INT DEFAULT 0,
            `is_active` TINYINT(1) DEFAULT 1
        ) ENGINE=InnoDB;

        CREATE TABLE IF NOT EXISTS `testimonials` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `client_name` VARCHAR(100) NOT NULL,
            `client_position` VARCHAR(100),
            `client_company` VARCHAR(100),
            `content` TEXT NOT NULL,
            `photo` VARCHAR(255) DEFAULT NULL,
            `rating` TINYINT DEFAULT 5,
            `is_active` TINYINT(1) DEFAULT 1,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ) ENGINE=InnoDB;
    ");

    // Seed default admin if not exists
    $stmt = $pdo->query('SELECT COUNT(*) as cnt FROM admins');
    if ($stmt->fetch()['cnt'] == 0) {
        $hash = password_hash('password123', PASSWORD_BCRYPT);
        $pdo->prepare('INSERT INTO admins (username, password, full_name, email, role) VALUES (?, ?, ?, ?, ?)')->execute([
            'admin', $hash, 'Site Administrator', ADMIN_EMAIL, 'super_admin'
        ]);
    }

    // Seed default services if not exists
    $stmt = $pdo->query('SELECT COUNT(*) as cnt FROM services');
    if ($stmt->fetch()['cnt'] == 0) {
        $services = [
            ['Digital Product Development', 'digital-product-development', 'We design and build scalable, user-centric digital products that solve real-world business challenges.', 'fas fa-laptop-code', 1],
            ['AI & Data Solutions', 'ai-data-solutions', 'Intelligent Document Processing, Predictive Analytics, and Conversational AI that unlock productivity.', 'fas fa-brain', 2],
            ['Cloud Solutions', 'cloud-solutions', 'Cloud migration, infrastructure modernization, and managed cloud services for enterprise scale.', 'fas fa-cloud', 3],
            ['IT Consulting', 'it-consulting', 'Transform your operations and achieve measurable outcomes with our bespoke consulting solutions.', 'fas fa-chart-line', 4],
            ['Managed Services', 'managed-services', 'End-to-end IT management so you can focus on what matters most — your business.', 'fas fa-server', 5],
            ['Cybersecurity', 'cybersecurity', 'Protect your enterprise with our comprehensive security assessments, monitoring, and response.', 'fas fa-shield-alt', 6],
        ];
        $stmt = $pdo->prepare('INSERT INTO services (title, slug, short_desc, icon, sort_order) VALUES (?, ?, ?, ?, ?)');
        foreach ($services as $s) {
            $stmt->execute($s);
        }
    }

    // Seed default products if not exists
    $stmt = $pdo->query('SELECT COUNT(*) as cnt FROM products');
    if ($stmt->fetch()['cnt'] == 0) {
        $products = [
            ['DP Meet', 'dp-meet', 'Enterprise video conferencing and collaboration', 'meet.digitalpractice.org', 'fas fa-video', 1],
            ['DP Invoice', 'dp-invoice', 'Smart e-invoicing and billing platform', 'e-invoice.digitalpractice.org', 'fas fa-file-invoice-dollar', 2],
            ['DP Analytics', 'dp-analytics', 'Business intelligence and data visualization', 'analytics.digitalpractice.org', 'fas fa-chart-bar', 3],
            ['DP Workspace', 'dp-workspace', 'Unified digital workplace for teams', 'workspace.digitalpractice.org', 'fas fa-th-large', 4],
        ];
        $stmt = $pdo->prepare('INSERT INTO products (name, slug, tagline, subdomain, icon, sort_order) VALUES (?, ?, ?, ?, ?, ?)');
        foreach ($products as $p) {
            $stmt->execute($p);
        }
    }

    // Seed team members
    $stmt = $pdo->query('SELECT COUNT(*) as cnt FROM team_members');
    if ($stmt->fetch()['cnt'] == 0) {
        $members = [
            ['John Doe', 'Chief Executive Officer', 'executive', 'A visionary technology leader with over 15 years of experience driving digital transformation across Africa.'],
            ['Jane Smith', 'Chief Technology Officer', 'executive', 'An accomplished engineer with expertise in cloud architecture, AI systems, and enterprise platforms.'],
            ['Michael Adebayo', 'Head of Consulting', 'executive', 'A seasoned consultant who has led digital transformation projects for Fortune 500 companies.'],
            ['Sarah Okonkwo', 'Head of Product', 'executive', 'A product strategist passionate about building user-centric digital solutions for African businesses.'],
        ];
        $stmt = $pdo->prepare('INSERT INTO team_members (name, position, category, bio) VALUES (?, ?, ?, ?)');
        foreach ($members as $m) {
            $stmt->execute($m);
        }
    }

} catch (PDOException $e) {
    error_log('Database Error: ' . $e->getMessage());
    die('A database error occurred. Please check your configuration.');
}