<?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) {}

    $coursesData = [
        // Tourism Education
        ['IBVTE-TOU-001', 'Diploma in Travel & Tourism Management', 'Diploma', '1 Year', '10+2', 'Regular', 'Theory + Project + Viva', '95000', 'Tourism Education'],
        ['IBVTE-TOU-002', 'Diploma in Tourism Management', 'Diploma', '1 Year', '10+2', 'Regular', 'Theory + Practical', '85000', 'Tourism Education'],
        ['IBVTE-TOU-003', 'Diploma in International Tourism Management', 'Diploma', '1 Year', '10+2', 'Regular', 'Project + Internship', '110000', 'Tourism Education'],
        ['IBVTE-TOU-004', 'Diploma in Travel Agency & Tour Operations', 'Diploma', '1 Year', '10+2', 'Regular', 'Theory + Practical', '80000', 'Tourism Education'],
        ['IBVTE-TOU-005', 'Diploma in Air Ticketing & Tourism Services', 'Diploma', '1 Year', '10+2', 'Regular', 'Practical + Viva', '90000', 'Tourism Education'],
        ['IBVTE-TOU-006', 'Diploma in Heritage & Cultural Tourism', 'Diploma', '1 Year', '10+2', 'Regular', 'Project Work', '85000', 'Tourism Education'],
        ['IBVTE-TOU-007', 'Diploma in Adventure Tourism Management', 'Diploma', '1 Year', '10+2', 'Regular', 'Practical + Field Work', '95000', 'Tourism Education'],
        ['IBVTE-TOU-008', 'Diploma in Eco-Tourism & Sustainable Tourism', 'Diploma', '1 Year', '10+2', 'Regular', 'Project + Practical', '90000', 'Tourism Education'],
        ['IBVTE-TOU-009', 'Diploma in Tourism Marketing & Destination Management', 'Diploma', '1 Year', '10+2', 'Regular', 'Theory + Project', '100000', 'Tourism Education'],
        ['IBVTE-TOU-010', 'Diploma in Cruise & Tourism Operations', 'Diploma', '1 Year', '10+2', 'Regular', 'Internship + Viva', '115000', 'Tourism Education'],
        ['IBVTE-TOU-011', 'Certificate in Travel & Tourism', 'Certificate', '6 Months', '10th Pass', 'Regular', 'Theory + Practical', '35000', 'Tourism Education'],
        ['IBVTE-TOU-012', 'Certificate in Air Ticketing & Reservation', 'Certificate', '6 Months', '10th Pass', 'Regular', 'Practical Assessment', '45000', 'Tourism Education'],
        ['IBVTE-TOU-013', 'Certificate in Tour Guiding & Escort Services', 'Certificate', '6 Months', '10th Pass', 'Regular', 'Practical + Viva', '40000', 'Tourism Education'],
        ['IBVTE-TOU-014', 'Certificate in Travel Agency Operations', 'Certificate', '6 Months', '10th Pass', 'Regular', 'Theory + Project', '35000', 'Tourism Education'],
        ['IBVTE-TOU-015', 'Certificate in Tourism Marketing', 'Certificate', '6 Months', '10th Pass', 'Regular', 'Project Assessment', '42000', 'Tourism Education'],
        ['IBVTE-TOU-016', 'Certificate in Cultural Tourism', 'Certificate', '6 Months', '10th Pass', 'Regular', 'Practical Assessment', '35000', 'Tourism Education'],
        ['IBVTE-TOU-017', 'Certificate in Adventure Tourism', 'Certificate', '6 Months', '10th Pass', 'Regular', 'Field Assessment', '45000', 'Tourism Education'],
        ['IBVTE-TOU-018', 'Certificate in Tourism Entrepreneurship', 'Certificate', '6 Months', '10th Pass', 'Regular', 'Project + Viva', '40000', 'Tourism Education'],
        ['IBVTE-TOU-019', 'Short Term Course in Travel Consultant Skills', 'Short Term', '3 Months', '8th Pass', 'Regular', 'Practical Test', '15000', 'Tourism Education'],
        ['IBVTE-TOU-020', 'Short Term Course in Tour Package Planning', 'Short Term', '3 Months', '8th Pass', 'Regular', 'Project Assessment', '18000', 'Tourism Education'],
        ['IBVTE-TOU-021', 'Short Term Course in Tourism Communication Skills', 'Short Term', '3 Months', '8th Pass', 'Regular', 'Viva + Practical', '12000', 'Tourism Education'],
        ['IBVTE-TOU-022', 'Short Term Course in Travel Reservation Systems', 'Short Term', '3 Months', '8th Pass', 'Regular', 'Practical Assessment', '20000', 'Tourism Education'],
        ['IBVTE-TOU-023', 'Short Term Course in Tour Guide Assistant Training', 'Short Term', '3 Months', '8th Pass', 'Regular', 'Field Test', '15000', 'Tourism Education'],
        ['IBVTE-TOU-024', 'Short Term Course in Tourism Sales & Customer Service', 'Short Term', '3 Months', '8th Pass', 'Regular', 'Practical Test', '15000', 'Tourism Education'],
        ['IBVTE-TOU-025', 'Short Term Course in Digital Tourism & Travel Booking', 'Short Term', '3 Months', '8th Pass', 'Regular', 'Practical + Project', '22000', 'Tourism Education'],
    ];

    $count = 0;
    foreach ($coursesData as $course) {
        $code = $course[0];
        $name = $course[1];
        $type = $course[2];
        $duration = $course[3];
        $eligibility = $course[4];
        $mode = 'offline';
        $fee = floatval($course[7]);
        $catName = $course[8];

        $durParts = explode(' ', $duration);
        $dur_v = $durParts[0];
        $dur_t = count($durParts) > 1 ? strtolower($durParts[1]) : 'months';
        if ($dur_t == 'year') $dur_t = 'years';
        
        $dur_m = 0;
        if ($dur_t == 'years') {
            $dur_m = intval($dur_v) * 12;
        } else if ($dur_t == 'months') {
            $dur_m = intval($dur_v);
        }

        $typeSlugLive = '';
        $typeSlugLocal = '';
        if (stripos($type, 'diploma') !== false) {
            $typeSlugLive = '2'; 
            $typeSlugLocal = 'diploma';
        } else if (stripos($type, 'short term') !== false) {
            $typeSlugLive = '1';
            $typeSlugLocal = 'certification'; 
        } else if (stripos($type, 'certificate') !== false) {
            $typeSlugLive = '1';
            $typeSlugLocal = 'certification';
        }

        if ($isLive) {
            // Find category
            $slug = strtolower(trim(preg_replace('/[^A-Za-z0-9-]+/', '-', $catName), '-'));
            $stmt = $pdo->prepare("SELECT id FROM course_categories WHERE slug = ? OR name = ? ORDER BY display_order ASC LIMIT 1");
            $stmt->execute([$slug, $catName]);
            $catId = $stmt->fetchColumn();

            if (!$catId) {
                $stmt = $pdo->prepare("INSERT INTO course_categories (name, slug, display_order, is_active) VALUES (?, ?, 0, 1)");
                $stmt->execute([$catName, $slug]);
                $catId = $pdo->lastInsertId();
            }

            // Find or create sub_category
            $subStmt = $pdo->prepare("SELECT id FROM course_sub_categories WHERE category_id = ? AND name LIKE ?");
            $subStmt->execute([$catId, $type . '%']);
            $subCatId = $subStmt->fetchColumn();
            if (!$subCatId) {
                $subSlug = $slug . '-' . strtolower(str_replace(' ', '-', $type));
                $pdo->prepare("INSERT INTO course_sub_categories (category_id, name, slug, is_active) VALUES (?, ?, ?, 1)")->execute([$catId, $type . ' Course', $subSlug]);
                $subCatId = $pdo->lastInsertId();
            }

            // Insert course
            $stmt = $pdo->prepare("INSERT INTO courses (category_id, sub_category_id, course_type, course_name, course_code, short_name, duration_text, duration_months, duration_value, eligibility, mode, is_active) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
            $stmt->execute([$catId, $subCatId, $typeSlugLive, $name, $code, $catName, $duration, $dur_m, $dur_v, $eligibility, $mode, 1]);
            $courseId = $pdo->lastInsertId();
            
            // Insert fee
            $stmt = $pdo->prepare("INSERT INTO course_fees (course_id, user_type, admission_fee, exam_fee, total_fee, valid_from) VALUES (?, ?, 0, 0, ?, '2026-01-01')");
            $stmt->execute([$courseId, 'college', $fee]);
            
        } else {
            // Local Schema
            $stmt = $pdo->prepare("INSERT INTO courses (course_code, course_name, short_name, category, course_type, duration, duration_months, duration_type, duration_value, eligibility, mode, is_active) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
            $stmt->execute([$code, $name, $catName, $type . ' Course', $typeSlugLocal, $duration, $dur_m, $dur_t, $dur_v, $eligibility, $mode, 1]);
            $courseId = $pdo->lastInsertId();
            
            // Insert fee
            $stmt = $pdo->prepare("INSERT INTO course_fees (course_id, user_type, admission_fee, exam_fee, total_fee, valid_from) VALUES (?, ?, 0, 0, ?, '2026-01-01')");
            $stmt->execute([$courseId, 'college', $fee]);

            // Add category if not exists
            $slug = strtolower(trim(preg_replace('/[^A-Za-z0-9-]+/', '-', $catName), '-'));
            $stmt = $pdo->prepare("SELECT id FROM course_categories WHERE name = ?");
            $stmt->execute([$catName]);
            if (!$stmt->fetchColumn()) {
                $stmt = $pdo->prepare("INSERT INTO course_categories (name, slug, display_order, is_active) VALUES (?, ?, 0, 1)");
                $stmt->execute([$catName, $slug]);
            }
        }
        $count++;
    }

    echo "Successfully imported $count courses!\n";

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