<?php
require 'config.php';
$secret = $_GET['secret'] ?? '';
if ($secret !== 'super_secret_migration_key_1234') {
    die('Unauthorized');
}

try {
    $db = getDB();
    $db->exec("SET FOREIGN_KEY_CHECKS=0;");
    
    // Drop old tables
    $db->exec("DROP TABLE IF EXISTS course_fees");
    $db->exec("DROP TABLE IF EXISTS course_sub_items");
    $db->exec("DROP TABLE IF EXISTS courses");
    $db->exec("DROP TABLE IF EXISTS course_sub_categories");
    $db->exec("DROP TABLE IF EXISTS course_categories");
    
    // 1. Create course_categories
    $db->exec("CREATE TABLE course_categories (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        slug VARCHAR(255) NOT NULL,
        description TEXT,
        image VARCHAR(255),
        display_order INT DEFAULT 0,
        is_active TINYINT(1) DEFAULT 1,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");
    
    // 2. Create course_sub_categories
    $db->exec("CREATE TABLE course_sub_categories (
        id INT AUTO_INCREMENT PRIMARY KEY,
        category_id INT NOT NULL,
        name VARCHAR(255) NOT NULL,
        slug VARCHAR(255) NOT NULL,
        description TEXT,
        image VARCHAR(255),
        display_order INT DEFAULT 0,
        is_active TINYINT(1) DEFAULT 1,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (category_id) REFERENCES course_categories(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");

    // 3. Create courses
    $db->exec("CREATE TABLE courses (
        id INT AUTO_INCREMENT PRIMARY KEY,
        category_id INT NOT NULL,
        sub_category_id INT NOT NULL,
        course_type VARCHAR(100) NOT NULL,
        course_name VARCHAR(255) NOT NULL,
        course_code VARCHAR(100) NOT NULL,
        short_name VARCHAR(100),
        eligibility VARCHAR(255),
        duration_text VARCHAR(100),
        duration_months INT DEFAULT 0,
        duration_value DECIMAL(5,2) DEFAULT 0,
        dur_years INT DEFAULT 0,
        dur_months INT DEFAULT 0,
        mode VARCHAR(50) DEFAULT 'offline',
        assessment_pass_percent DECIMAL(5,2) DEFAULT 50,
        description TEXT,
        syllabus_text TEXT,
        syllabus_pdf VARCHAR(255),
        image VARCHAR(255),
        is_active TINYINT(1) DEFAULT 1,
        display_order INT DEFAULT 0,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (category_id) REFERENCES course_categories(id) ON DELETE CASCADE,
        FOREIGN KEY (sub_category_id) REFERENCES course_sub_categories(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");

    // 4. Create course_fees
    $db->exec("CREATE TABLE course_fees (
        id INT AUTO_INCREMENT PRIMARY KEY,
        course_id INT NOT NULL,
        user_type ENUM('student','college','admin') DEFAULT 'college',
        admission_fee DECIMAL(10,2) DEFAULT 0,
        exam_fee DECIMAL(10,2) DEFAULT 0,
        certificate_fee DECIMAL(10,2) DEFAULT 0,
        total_fee DECIMAL(10,2) DEFAULT 0,
        valid_from DATE,
        FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");

    $db->exec("SET FOREIGN_KEY_CHECKS=1;");
    echo "Migration completed successfully!";
} catch (Exception $e) {
    echo "Error: " . $e->getMessage();
}