<?php
session_start();
include "../include/config.php";

if (isset($_POST['uploadCsv'])) {
    if ($_FILES['csvFile']['error'] == UPLOAD_ERR_OK) {
        $fileName = $_FILES['csvFile']['tmp_name'];
        
        if (($handle = fopen($fileName, "r")) !== FALSE) {
            $successCount = 0;
            $errorCount   = 0;
            $errors       = [];
            
            // Start transaction
            $conn->begin_transaction();
            
            try {
                // Skip header row (if exists)
                fgetcsv($handle);
                
                while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
                    // Skip empty rows
                    if (empty(implode('', $data))) {
                        continue;
                    }
                    
                    if (count($data) >= 4) {
                        $course   = mysqli_real_escape_string($conn, trim($data[0]));
                        $semester = mysqli_real_escape_string($conn, trim($data[1]));
                        $year     = mysqli_real_escape_string($conn, trim($data[2]));
                        $program  = mysqli_real_escape_string($conn, trim($data[3]));
                        
                        // Validate data
                        if (empty($course) || empty($semester) || empty($program)) {
                            $errorCount++;
                            $errors[] = "Row with data: " . implode(", ", $data) . " - Missing required fields";
                            continue;
                        }
                        
                        // Check if course already exists for same program and semester
                        $checkSql = "SELECT * FROM course 
                                     WHERE course = '$course' AND semester = '$semester' AND program = '$program'";
                        $checkResult = $conn->query($checkSql);
                        
                        if ($checkResult->num_rows > 0) {
                            $errorCount++;
                            $errors[] = "Course '$course' already exists for program '$program' in semester $semester";
                            continue;
                        }
                        
                        // Insert new course (with program in same table)
                        $sql = "INSERT INTO course (course, semester, year, program) 
                                VALUES ('$course', '$semester', '$year', '$program')";
                        
                        if ($conn->query($sql)) {
                            $successCount++;
                        } else {
                            $errorCount++;
                            $errors[] = "Database error for '$course': " . $conn->error;
                            continue;
                        }
                    } else {
                        $errorCount++;
                        $errors[] = "Invalid data format (expected 4 columns): " . implode(", ", $data);
                    }
                }
                
                // Commit transaction
                $conn->commit();
                fclose($handle);
                
                // Prepare result message
                $message = "Upload completed. Success: $successCount, Errors: $errorCount";
                if ($errorCount > 0) {
                    $message .= "<br>Error details:<br>" . implode("<br>", array_slice($errors, 0, 5));
                    if (count($errors) > 5) {
                        $message .= "<br>... and " . (count($errors) - 5) . " more errors";
                    }
                    header("Location: course.php?error=" . urlencode($message));
                } else {
                    header("Location: course.php?success=" . urlencode($message));
                }
                
            } catch (Exception $e) {
                // Rollback on error
                $conn->rollback();
                fclose($handle);
                header("Location: course.php?error=" . urlencode($e->getMessage()));
            }
        } else {
            header("Location: course.php?error=Could not open CSV file");
        }
    } else {
        header("Location: course.php?error=File upload error");
    }
} else {
    header("Location: course.php");
}
exit();
?>