<?php
try {
    $pdo = new PDO('mysql:host=127.0.0.1;dbname=projects_membershipdb;charset=utf8mb4', 'root', '', [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

    // 1. Create cms_pages table
    $pdo->exec("CREATE TABLE IF NOT EXISTS `cms_pages` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `slug` VARCHAR(255) NOT NULL UNIQUE,
        `title` VARCHAR(255) NOT NULL,
        `content` LONGTEXT,
        `meta_title` VARCHAR(255) DEFAULT NULL,
        `meta_description` TEXT,
        `status` ENUM('draft', 'published') NOT NULL DEFAULT 'draft',
        `created_by` INT DEFAULT NULL,
        `updated_by` INT DEFAULT NULL,
        `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    echo "Created cms_pages table\n";

    // Insert default home page
    $stmt = $pdo->prepare("INSERT IGNORE INTO `cms_pages` (`slug`, `title`, `status`) VALUES ('home', 'Home Page', 'draft')");
    $stmt->execute();
    echo "Inserted default home page\n";

    // 2. Add SMS columns to password_resets
    $stmt = $pdo->query("SHOW COLUMNS FROM password_resets LIKE 'reset_method'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE password_resets ADD COLUMN reset_method ENUM('email','sms') DEFAULT 'email' AFTER `token`");
        $pdo->exec("ALTER TABLE password_resets ADD COLUMN phone VARCHAR(20) DEFAULT NULL AFTER `reset_method`");
        $pdo->exec("ALTER TABLE password_resets ADD COLUMN otp VARCHAR(10) DEFAULT NULL AFTER `phone`");
        echo "Added SMS columns to password_resets\n";
    } else {
        echo "password_resets SMS columns already exist\n";
    }

    // 3. Insert new settings
    $settings = [
        ['default_user_role', 'viewer', 'select', 'general'],
        ['auto_approve_registration', 'false', 'boolean', 'general'],
        ['enable_registration', 'true', 'boolean', 'general'],
        ['company_address', '', 'text', 'general'],
        ['company_phone', '', 'text', 'general'],
        ['sms_provider', 'hubtel', 'select', 'mail'],
        ['sms_api_key', '', 'text', 'mail'],
        ['sms_api_secret', '', 'text', 'mail'],
        ['sms_sender_id', 'GAS', 'text', 'mail'],
        ['home_hero_title', 'Advancing Anaesthesiology in Ghana', 'text', 'appearance'],
        ['home_hero_subtitle', 'The Ghana Anaesthesiologists Society is the professional body uniting anaesthesiologists across the nation.', 'textarea', 'appearance'],
        ['home_show_announcements', 'true', 'boolean', 'appearance'],
        ['home_show_events', 'true', 'boolean', 'appearance'],
    ];

    $insert = $pdo->prepare("INSERT IGNORE INTO `settings` (`key`, `value`, `type`, `group`) VALUES (?, ?, ?, ?)");
    foreach ($settings as $s) {
        $insert->execute($s);
    }
    echo 'Inserted ' . count($settings) . " new settings\n";

    echo "Migration 010 completed successfully\n";
} catch (Exception $e) {
    echo 'Error: ' . $e->getMessage() . "\n";
    exit(1);
}
