<?php
define('IBVTE_ACCESS', true);
require 'config.php';
require 'includes/db.php';

try {
    $pdo = new PDO("mysql:host=" . DB_HOST . ";dbname=" . DB_NAME, DB_USERNAME, DB_PASSWORD);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $isLive = false;
    try { $pdo->query("SELECT category_id FROM courses LIMIT 1"); $isLive = true; } catch(Exception $e) {}

    $targetCats = ['Aviation Education', 'Automobile Education', 'Beautician Education', 'Business Education', 'Child Education'];
    $placeholders = str_repeat('?,', count($targetCats) - 1) . '?';

    // Find old category IDs
    $stmt = $pdo->prepare("SELECT id FROM course_categories WHERE display_order = 0 AND name IN ($placeholders)");
    $stmt->execute($targetCats);
    $oldCatIds = $stmt->fetchAll(PDO::FETCH_COLUMN);

    if (!empty($oldCatIds)) {
        $idPlaceholders = str_repeat('?,', count($oldCatIds) - 1) . '?';

        // Deactivate course_sub_categories on LIVE
        if ($isLive) {
            $pdo->prepare("UPDATE course_sub_categories SET is_active = 0 WHERE category_id IN ($idPlaceholders)")->execute($oldCatIds);
        }

        // Find old courses to deactivate
        if ($isLive) {
            $stmt = $pdo->prepare("SELECT id FROM courses WHERE category_id IN ($idPlaceholders)");
            $stmt->execute($oldCatIds);
            $oldCourseIds = $stmt->fetchAll(PDO::FETCH_COLUMN);
        } else {
            $stmt = $pdo->prepare("SELECT id FROM courses WHERE short_name IN ($placeholders) AND id < 120");
            $stmt->execute($targetCats);
            $oldCourseIds = $stmt->fetchAll(PDO::FETCH_COLUMN);
        }

        if (!empty($oldCourseIds)) {
            $courseIdPlaceholders = str_repeat('?,', count($oldCourseIds) - 1) . '?';
            $pdo->prepare("UPDATE courses SET is_active = 0 WHERE id IN ($courseIdPlaceholders)")->execute($oldCourseIds);
            echo "Deactivated " . count($oldCourseIds) . " old courses.\n";
        }

        // Deactivate old categories
        $pdo->prepare("UPDATE course_categories SET is_active = 0 WHERE id IN ($idPlaceholders)")->execute($oldCatIds);
        echo "Deactivated " . count($oldCatIds) . " old categories.\n";
    }

    // Update new categories display_order to 0
    $stmt = $pdo->prepare("UPDATE course_categories SET display_order = 0 WHERE display_order = 99");
    $stmt->execute();
    echo "Updated new categories display_order.\n";

    echo "Cleanup completed successfully!\n";

} catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}