<?php
require_once '../../config/config.php';

// Ensure $pdo is accessible
global $pdo;

header('Content-Type: application/json');

function respond($ok, $msg, $extra = []) {
    echo json_encode(($ok ? ['status' => 'success'] : ['status' => 'error']) + ['message' => $msg] + $extra);
    exit;
}

try {
    $academicYear = trim($_POST['academic_year'] ?? $_GET['academic_year'] ?? '2025-2026 Odd');
    $force = (int)($_POST['force'] ?? $_GET['force'] ?? 0);
    $count = (int)($_POST['count'] ?? $_GET['count'] ?? 8);

    // Ensure we have a valid college_id
    $collegeId = 1;
    
    // Check if college exists by code, if not create one
    $collegeCheck = $pdo->prepare("SELECT college_id FROM colleges WHERE college_code = ?");
    $collegeCheck->execute(['INEC']);
    $existingCollege = $collegeCheck->fetch();
    
    if ($existingCollege) {
        // Use existing college
        $collegeId = $existingCollege['college_id'];
    } else {
        // Create a default college
        $createCollege = $pdo->prepare("INSERT INTO colleges (college_name, college_code, status) VALUES (?, ?, ?)");
        $createCollege->execute([
            'Isaac Newton Engineering College',
            'INEC',
            'Active'
        ]);
        $collegeId = $pdo->lastInsertId();
    }

    $results = [
        'halls' => ['created' => 0, 'skipped' => 0],
        'settings' => ['created' => 0, 'skipped' => 0],
        'exams' => ['created' => 0, 'skipped' => 0],
        'timetable' => ['created' => 0, 'skipped' => 0],
        'registrations' => ['created' => 0, 'skipped' => 0],
        'allocations' => ['created' => 0, 'assigned_students' => 0],
        'debug_info' => []
    ];

    // Step 1: Seed Halls and Settings
    $hallsData = [
        [
            'hall_name' => 'Main Hall A',
            'capacity' => 120,
            'block_name' => 'Block A',
            'college_id' => $collegeId,
            'settings' => [
                'rows_count' => 10, 'columns_count' => 12, 'seating_method' => 'Parallel',
                'bench_capacity' => 1, 'max_students' => 120, 'row_labeling' => 'Numeric',
                'internal_invigilators_required' => 2, 'external_invigilators_required' => 1,
                'aisle_every_n_cols' => 4, 'aisle_every_n_rows' => 0
            ]
        ],
        [
            'hall_name' => 'Main Hall B',
            'capacity' => 100,
            'block_name' => 'Block B',
            'college_id' => $collegeId,
            'settings' => [
                'rows_count' => 8, 'columns_count' => 12, 'seating_method' => 'Parallel',
                'bench_capacity' => 1, 'max_students' => 96, 'row_labeling' => 'Numeric',
                'internal_invigilators_required' => 2, 'external_invigilators_required' => 1,
                'aisle_every_n_cols' => 4, 'aisle_every_n_rows' => 0
            ]
        ],
        [
            'hall_name' => 'Seminar Hall',
            'capacity' => 80,
            'block_name' => 'Block A',
            'college_id' => $collegeId,
            'settings' => [
                'rows_count' => 8, 'columns_count' => 10, 'seating_method' => 'Curved',
                'bench_capacity' => 1, 'max_students' => 80, 'row_labeling' => 'Alphabetic',
                'internal_invigilators_required' => 1, 'external_invigilators_required' => 1,
                'aisle_every_n_cols' => 5, 'aisle_every_n_rows' => 0
            ]
        ],
        [
            'hall_name' => 'Computer Lab Hall',
            'capacity' => 60,
            'block_name' => 'Block B',
            'college_id' => $collegeId,
            'settings' => [
                'rows_count' => 6, 'columns_count' => 10, 'seating_method' => 'Parallel',
                'bench_capacity' => 1, 'max_students' => 60, 'row_labeling' => 'Numeric',
                'internal_invigilators_required' => 1, 'external_invigilators_required' => 0,
                'aisle_every_n_cols' => 5, 'aisle_every_n_rows' => 0
            ]
        ],
        [
            'hall_name' => 'Auditorium',
            'capacity' => 200,
            'block_name' => 'Central Block',
            'college_id' => $collegeId,
            'settings' => [
                'rows_count' => 15, 'columns_count' => 13, 'seating_method' => 'Curved',
                'bench_capacity' => 1, 'max_students' => 195, 'row_labeling' => 'Alphabetic',
                'internal_invigilators_required' => 3, 'external_invigilators_required' => 2,
                'aisle_every_n_cols' => 6, 'aisle_every_n_rows' => 5
            ]
        ],
        [
            'hall_name' => 'Small Hall C',
            'capacity' => 40,
            'block_name' => 'Block C',
            'college_id' => $collegeId,
            'settings' => [
                'rows_count' => 5, 'columns_count' => 8, 'seating_method' => 'Parallel',
                'bench_capacity' => 1, 'max_students' => 40, 'row_labeling' => 'Numeric',
                'internal_invigilators_required' => 1, 'external_invigilators_required' => 0,
                'aisle_every_n_cols' => 4, 'aisle_every_n_rows' => 0
            ]
        ],
        [
            'hall_name' => 'Library Hall',
            'capacity' => 50,
            'block_name' => 'Library Block',
            'college_id' => $collegeId,
            'settings' => [
                'rows_count' => 5, 'columns_count' => 10, 'seating_method' => 'Parallel',
                'bench_capacity' => 1, 'max_students' => 50, 'row_labeling' => 'Numeric',
                'internal_invigilators_required' => 1, 'external_invigilators_required' => 0,
                'aisle_every_n_cols' => 5, 'aisle_every_n_rows' => 0
            ]
        ],
        [
            'hall_name' => 'Conference Hall',
            'capacity' => 30,
            'block_name' => 'Admin Block',
            'college_id' => $collegeId,
            'settings' => [
                'rows_count' => 3, 'columns_count' => 10, 'seating_method' => 'Parallel',
                'bench_capacity' => 1, 'max_students' => 30, 'row_labeling' => 'Numeric',
                'internal_invigilators_required' => 1, 'external_invigilators_required' => 0,
                'aisle_every_n_cols' => 5, 'aisle_every_n_rows' => 0
            ]
        ]
    ];

    foreach (array_slice($hallsData, 0, $count) as $hallData) {
        $settings = $hallData['settings'];
        unset($hallData['settings']);

        // Check if hall already exists
        $existingHall = $pdo->prepare("SELECT hall_id FROM halls WHERE hall_name = ?");
        $existingHall->execute([$hallData['hall_name']]);
        $existing = $existingHall->fetch();

        if ($existing && !$force) {
            $results['halls']['skipped']++;
            continue;
        }

        if ($existing && $force) {
            // Update existing hall
            $updateHall = $pdo->prepare("UPDATE halls SET capacity = ?, block_name = ? WHERE hall_id = ?");
            $updateHall->execute([$hallData['capacity'], $hallData['block_name'], $existing['hall_id']]);
            $hallId = $existing['hall_id'];
        } else {
            // Insert new hall
            $insertHall = $pdo->prepare("INSERT INTO halls (hall_name, capacity, block_name, college_id) VALUES (?, ?, ?, ?)");
            $insertHall->execute([$hallData['hall_name'], $hallData['capacity'], $hallData['block_name'], $hallData['college_id']]);
            $hallId = $pdo->lastInsertId();
        }

        if ($hallId) {
            $results['halls']['created']++;

            // Handle hall settings
            $existingSettings = $pdo->prepare("SELECT hall_setting_id FROM hall_settings WHERE hall_id = ?");
            $existingSettings->execute([$hallId]);
            $existingSetting = $existingSettings->fetch();

            if ($existingSetting && !$force) {
                $results['settings']['skipped']++;
                continue;
            }

            if ($existingSetting && $force) {
                // Update existing settings
                $updateSettings = $pdo->prepare("
                    UPDATE hall_settings SET 
                    rows_count = ?, columns_count = ?, seating_method = ?, bench_capacity = ?, 
                    max_students = ?, row_labeling = ?, internal_invigilators_required = ?, 
                    external_invigilators_required = ?, aisle_every_n_cols = ?, aisle_every_n_rows = ?
                    WHERE hall_id = ?
                ");
                $updateSettings->execute([
                    $settings['rows_count'], $settings['columns_count'], $settings['seating_method'],
                    $settings['bench_capacity'], $settings['max_students'], $settings['row_labeling'],
                    $settings['internal_invigilators_required'], $settings['external_invigilators_required'],
                    $settings['aisle_every_n_cols'], $settings['aisle_every_n_rows'], $hallId
                ]);
            } else {
                // Insert new settings
                $insertSettings = $pdo->prepare("
                    INSERT INTO hall_settings (
                        hall_id, rows_count, columns_count, seating_method, bench_capacity,
                        max_students, row_labeling, internal_invigilators_required,
                        external_invigilators_required, aisle_every_n_cols, aisle_every_n_rows
                    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                ");
                $insertSettings->execute([
                    $hallId, $settings['rows_count'], $settings['columns_count'], $settings['seating_method'],
                    $settings['bench_capacity'], $settings['max_students'], $settings['row_labeling'],
                    $settings['internal_invigilators_required'], $settings['external_invigilators_required'],
                    $settings['aisle_every_n_cols'], $settings['aisle_every_n_rows']
                ]);
            }
            $results['settings']['created']++;
        }
    }

    // Step 2: Generate Exam Schedule
    $yearOnly = preg_match('/\b\d{4}-\d{4}\b/', $academicYear, $m) ? $m[0] : '2025-2026';
    
    // Check if exam type exists
    $examTypeStmt = $pdo->prepare("SELECT exam_type_id FROM exam_types WHERE type_name = 'SEM' LIMIT 1");
    $examTypeStmt->execute();
    $examTypeId = $examTypeStmt->fetchColumn();

    if (!$examTypeId) {
        $insertExamType = $pdo->prepare("INSERT INTO exam_types (type_name, description) VALUES ('SEM', 'Semester Examination')");
        $insertExamType->execute();
        $examTypeId = $pdo->lastInsertId();
    }

    // Generate exams for semesters 1, 3, 5, 7
    $semesters = [1, 3, 5, 7];
    foreach ($semesters as $semester) {
        $examName = "SEM - AY $yearOnly - Sem $semester";
        
        // Check for existing exam by academic year, semester, and exam type to prevent duplicates
        $existingExam = $pdo->prepare("
            SELECT exam_id FROM exams 
            WHERE academic_year = ? AND semester = ? AND exam_type_id = ?
        ");
        $existingExam->execute([$yearOnly, $semester, $examTypeId]);
        $existing = $existingExam->fetch();

        if ($existing && !$force) {
            $results['exams']['skipped']++;
            continue;
        }

        if ($existing && $force) {
            $examId = $existing['exam_id'];
        } else {
            $insertExam = $pdo->prepare("INSERT INTO exams (exam_name, exam_type_id, academic_year, semester, status) VALUES (?, ?, ?, ?, 'Active')");
            $insertExam->execute([$examName, $examTypeId, $yearOnly, $semester]);
            $examId = $pdo->lastInsertId();
        }

        if ($examId) {
            $results['exams']['created']++;

            // Generate exam subjects and timetable
            $subjectsStmt = $pdo->prepare("SELECT subject_id FROM subjects WHERE semester = ? AND (type <> 'Lab' OR type IS NULL) LIMIT 5");
            $subjectsStmt->execute([$semester]);
            $subjects = $subjectsStmt->fetchAll(PDO::FETCH_COLUMN);

            foreach ($subjects as $subjectId) {
                // Create exam subject - check for duplicates more thoroughly
                $existingExamSubject = $pdo->prepare("
                    SELECT es.exam_subject_id 
                    FROM exam_subjects es 
                    JOIN exams e ON es.exam_id = e.exam_id 
                    WHERE es.subject_id = ? AND e.academic_year = ? AND e.semester = ?
                ");
                $existingExamSubject->execute([$subjectId, $yearOnly, $semester]);
                $existingES = $existingExamSubject->fetch();

                if ($existingES) {
                    $examSubjectId = $existingES['exam_subject_id'];
                    continue; // Skip creating duplicate exam subject
                } else {
                    $insertExamSubject = $pdo->prepare("INSERT INTO exam_subjects (exam_id, subject_id) VALUES (?, ?)");
                    $insertExamSubject->execute([$examId, $subjectId]);
                    $examSubjectId = $pdo->lastInsertId();
                }

                if ($examSubjectId) {
                    // Create timetable entries (FN and AN sessions)
                    $sessions = ['FN', 'AN'];
                    foreach ($sessions as $session) {
                        // Check for existing timetable entry more thoroughly
                        $existingTimetable = $pdo->prepare("
                            SELECT et.timetable_id 
                            FROM exam_timetable et 
                            JOIN exam_subjects es ON et.exam_subject_id = es.exam_subject_id 
                            JOIN exams e ON es.exam_id = e.exam_id 
                            WHERE es.subject_id = ? AND e.academic_year = ? AND e.semester = ? AND et.session = ?
                        ");
                        $existingTimetable->execute([$subjectId, $yearOnly, $semester, $session]);
                        $existingTT = $existingTimetable->fetch();

                        if ($existingTT && !$force) {
                            $results['timetable']['skipped']++;
                            continue;
                        }

                        // Generate consecutive exam dates: 2025-09-14, 2025-09-15, 2025-09-16
                        $baseDate = '2025-09-14';
                        $dayOffset = ($semester - 1) % 3; // Map semesters 1,3,5,7 to days 0,1,2,0
                        $examDate = date('Y-m-d', strtotime("$baseDate +{$dayOffset} days"));
                        $startTime = $session === 'FN' ? '09:00:00' : '14:00:00';
                        $endTime = $session === 'FN' ? '12:00:00' : '17:00:00';

                        if ($existingTT && $force) {
                            $updateTimetable = $pdo->prepare("UPDATE exam_timetable SET exam_date = ?, start_time = ?, end_time = ? WHERE timetable_id = ?");
                            $updateTimetable->execute([$examDate, $startTime, $endTime, $existingTT['timetable_id']]);
                            $results['timetable']['created']++;
                        } else if (!$existingTT) {
                            try {
                                $insertTimetable = $pdo->prepare("INSERT INTO exam_timetable (exam_subject_id, exam_date, session, start_time, end_time) VALUES (?, ?, ?, ?, ?)");
                                $insertTimetable->execute([$examSubjectId, $examDate, $session, $startTime, $endTime]);
                                $results['timetable']['created']++;
                            } catch (PDOException $e) {
                                if ($e->getCode() == 23000) { // Duplicate entry
                                    $results['timetable']['skipped']++;
                                    error_log("Duplicate timetable entry skipped: exam_subject_id={$examSubjectId}, session={$session}");
                                } else {
                                    throw $e;
                                }
                            }
                        }
                    }
                }
            }
        }
    }

    // Step 3: Generate Student Registrations
    $studentsStmt = $pdo->query("SELECT student_id, course_id FROM students WHERE status = 'Active' LIMIT 50");
    $students = $studentsStmt->fetchAll(PDO::FETCH_ASSOC);

    $examSubjectsStmt = $pdo->prepare("SELECT es.exam_subject_id, es.exam_id, s.course_id FROM exam_subjects es JOIN subjects s ON es.subject_id = s.subject_id JOIN exams e ON es.exam_id = e.exam_id WHERE e.academic_year = ?");
    $examSubjectsStmt->execute([$yearOnly]);
    $examSubjects = $examSubjectsStmt->fetchAll(PDO::FETCH_ASSOC);

    foreach ($students as $student) {
        foreach ($examSubjects as $examSubject) {
            if ($student['course_id'] == $examSubject['course_id']) {
                // Check if registration already exists
                $existingReg = $pdo->prepare("SELECT registration_id FROM student_registrations WHERE student_id = ? AND exam_subject_id = ?");
                $existingReg->execute([$student['student_id'], $examSubject['exam_subject_id']]);
                $existing = $existingReg->fetch();

                if ($existing && !$force) {
                    $results['registrations']['skipped']++;
                    continue;
                }

                if ($existing && $force) {
                    continue; // Skip existing registrations
                }

                // Get fee structure
                $feeStmt = $pdo->prepare("SELECT fee_structure_id FROM course_fee_structure WHERE course_id = ? LIMIT 1");
                $feeStmt->execute([$student['course_id']]);
                $feeStructureId = $feeStmt->fetchColumn();

                $insertReg = $pdo->prepare("INSERT INTO student_registrations (student_id, exam_subject_id, is_arrear, fee_structure_id) VALUES (?, ?, 0, ?)");
                $insertReg->execute([$student['student_id'], $examSubject['exam_subject_id'], $feeStructureId]);
                $results['registrations']['created']++;
            }
        }
    }

    // Step 4: Generate Hall Allocations
    $timetablesStmt = $pdo->prepare("
        SELECT et.timetable_id, et.session, et.exam_subject_id, es.exam_id
        FROM exam_timetable et
        JOIN exam_subjects es ON et.exam_subject_id = es.exam_subject_id
        JOIN exams e ON es.exam_id = e.exam_id
        WHERE e.academic_year = ?
    ");
    $timetablesStmt->execute([$yearOnly]);
    $timetables = $timetablesStmt->fetchAll(PDO::FETCH_ASSOC);

    $hallsStmt = $pdo->query("
        SELECT h.hall_id, h.hall_name, h.capacity,
               COALESCE(hs.max_students, h.capacity) as effective_capacity
        FROM halls h
        LEFT JOIN hall_settings hs ON h.hall_id = hs.hall_id
        ORDER BY effective_capacity DESC
    ");
    $halls = $hallsStmt->fetchAll(PDO::FETCH_ASSOC);

    foreach ($timetables as $tt) {
        $isArrear = ($tt['session'] === 'AN') ? 1 : 0;
        
        // Get registrations for this exam_subject and session
        $regsStmt = $pdo->prepare("SELECT registration_id FROM student_registrations WHERE exam_subject_id = ? AND is_arrear = ?");
        $regsStmt->execute([$tt['exam_subject_id'], $isArrear]);
        $registrations = $regsStmt->fetchAll(PDO::FETCH_COLUMN);

        if (empty($registrations)) continue;

        $remainingStudents = count($registrations);
        $studentIndex = 0;

        // Clear existing allocations
        if ($force) {
            $oldAllocStmt = $pdo->prepare("SELECT allocation_id FROM hall_allocations WHERE timetable_id = ?");
            $oldAllocStmt->execute([$tt['timetable_id']]);
            $oldAllocIds = $oldAllocStmt->fetchAll(PDO::FETCH_COLUMN);

            if ($oldAllocIds) {
                $in = implode(',', array_fill(0, count($oldAllocIds), '?'));
                $pdo->prepare("DELETE FROM student_seating WHERE allocation_id IN ($in)")->execute($oldAllocIds);
                $pdo->prepare('DELETE FROM hall_allocations WHERE timetable_id = ?')->execute([$tt['timetable_id']]);
            }
        }

        // Allocate students to halls
        foreach ($halls as $hall) {
            if ($remainingStudents <= 0) break;

            $studentsToAllocate = min($remainingStudents, $hall['effective_capacity']);

            // Create hall allocation
            $allocStmt = $pdo->prepare("INSERT INTO hall_allocations (timetable_id, hall_id, num_students) VALUES (?, ?, ?)");
            $allocStmt->execute([$tt['timetable_id'], $hall['hall_id'], $studentsToAllocate]);
            $allocationId = $pdo->lastInsertId();

            if ($allocationId) {
                $results['allocations']['created']++;

                // Get hall settings for seat numbering
                $hallSettingsStmt = $pdo->prepare("SELECT * FROM hall_settings WHERE hall_id = ?");
                $hallSettingsStmt->execute([$hall['hall_id']]);
                $hallSettings = $hallSettingsStmt->fetch(PDO::FETCH_ASSOC);

                // Create student seating
                for ($i = 0; $i < $studentsToAllocate; $i++) {
                    $regId = $registrations[$studentIndex + $i];

                    if ($hallSettings) {
                        // Generate seat number based on hall layout
                        $rows = (int)$hallSettings['rows_count'];
                        $cols = (int)$hallSettings['columns_count'];
                        $bench = (int)$hallSettings['bench_capacity'];
                        $cellsPerRow = $cols * $bench;
                        $rowIndex = (int)floor($i / $cellsPerRow) + 1;
                        $rem = $i % $cellsPerRow;
                        $colIndex = (int)floor($rem / $bench) + 1;
                        $seatIndex = $rem % $bench + 1;
                        $rowLabel = ($hallSettings['row_labeling'] === 'Alphabetic') ? chr(64 + $rowIndex) : 'R' . $rowIndex;
                        $seatNumber = $rowLabel . '-C' . $colIndex . '-S' . $seatIndex;
                    } else {
                        // Simple sequential numbering
                        $seatNumber = 'S' . ($i + 1);
                    }

                    $seatingStmt = $pdo->prepare("INSERT INTO student_seating (registration_id, allocation_id, seat_number) VALUES (?, ?, ?)");
                    $seatingStmt->execute([$regId, $allocationId, $seatNumber]);
                    $results['allocations']['assigned_students']++;
                }

                $studentIndex += $studentsToAllocate;
                $remainingStudents -= $studentsToAllocate;
            }
        }
    }

    // Debug information
    $results['debug_info'] = [
        'total_halls' => $pdo->query('SELECT COUNT(*) FROM halls')->fetchColumn(),
        'total_hall_settings' => $pdo->query('SELECT COUNT(*) FROM hall_settings')->fetchColumn(),
        'total_exams' => (function() use ($pdo, $yearOnly) { $stmt = $pdo->prepare('SELECT COUNT(*) FROM exams WHERE academic_year = ?'); $stmt->execute([$yearOnly]); return $stmt->fetchColumn(); })(),
        'total_timetable_entries' => (function() use ($pdo, $yearOnly) { $stmt = $pdo->prepare('SELECT COUNT(*) FROM exam_timetable et JOIN exam_subjects es ON et.exam_subject_id = es.exam_subject_id JOIN exams e ON es.exam_id = e.exam_id WHERE e.academic_year = ?'); $stmt->execute([$yearOnly]); return $stmt->fetchColumn(); })(),
        'total_registrations' => $pdo->query('SELECT COUNT(*) FROM student_registrations')->fetchColumn(),
        'total_allocations' => $pdo->query('SELECT COUNT(*) FROM hall_allocations')->fetchColumn(),
        'total_seating' => $pdo->query('SELECT COUNT(*) FROM student_seating')->fetchColumn()
    ];

    respond(true, 'Complete hall allocation setup completed successfully', $results);

} catch (Throwable $e) {
    http_response_code(500);
    respond(false, $e->getMessage());
}
?>