<?php
require_once('source/session.php');
require_once('source/error.php');
require_once('source/dbconfig.php');
require_once('source/function.php');
require_once('source/restrict_admin.php');

// Database connection
$host = 'localhost'; 
$dbname = 'vdacocmz_schdb_coll'; 
$user = 'vdacocmz_mendohz'; 
$pass = '@Dei22*#';

try {
    $DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
    $DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    die("Database connection failed: " . $e->getMessage());
}

// Second database connection for backup
try {
    $db = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    die("Database connection failed: " . $e->getMessage());
}

if(isset($_POST['importSubmit'])) {
    $teacher_no = strip_tags($_POST['teacher_no']);
    $s_term = strip_tags($_POST['term']);
    $s_year = strip_tags($_POST['year']);
    
    try {
        // Get teacher's class
        $stmt = $DBH->prepare("SELECT class, subclass FROM tbl_user WHERE hash = ?");
        $stmt->execute([$teacher_no]);
        $teacher_data = $stmt->fetch(PDO::FETCH_ASSOC);
        
        if ($teacher_data) {
            $s_class = $teacher_data['class'];
            $s_subclass = $teacher_data['subclass'];
        } else {
            throw new Exception("Teacher not found");
        }
        
        $key = mt_rand(100000, 999999); // Generate unique CSV number
        
        // Validate uploaded file
        $csvMimes = array('text/x-comma-separated-values', 'text/comma-separated-values', 
                         'application/octet-stream', 'application/vnd.ms-excel', 
                         'application/x-csv', 'text/x-csv', 'text/csv', 
                         'application/csv', 'application/excel', 
                         'application/vnd.msexcel', 'text/plain');
        
        if(!empty($_FILES['file']['name']) && in_array($_FILES['file']['type'], $csvMimes)) {
            if(is_uploaded_file($_FILES['file']['tmp_name'])) {
                $csvFile = fopen($_FILES['file']['tmp_name'], 'r');
                fgetcsv($csvFile); // Skip header
                
                $success_count = 0;
                $error_count = 0;
                $error_details = [];
                
                while(($line = fgetcsv($csvFile)) !== FALSE) {
                    $row_num = $success_count + $error_count + 2; // Account for header
                    
                    try {
                        // Check for required columns
                        if(count($line) < 7) {
                            $error_details[] = "Row {$row_num}: Insufficient columns (expected 7, found " . count($line) . ")";
                            $error_count++;
                            continue;
                        }
                        
                        $name = trim($line[0]);
                        $registration_no = trim($line[1]);
                        $present = trim($line[2]);
                        $sch_open = trim($line[3]);
                        $date_of_report = trim($line[4]);
                        $next_term_begins = trim($line[5]);
                        $teachers_remarks = trim($line[6]);
                        
                        if(empty($name) || empty($registration_no)) {
                            $error_details[] = "Row {$row_num}: Missing name or registration number";
                            $error_count++;
                            continue;
                        }
                        
                        // Check if record exists
                        $check_stmt = $db->prepare("SELECT id FROM attendance WHERE registration_no = ? AND csv_no = ?");
                        $check_stmt->execute([$registration_no, $key]);
                        
                        if($check_stmt->fetch()) {
                            // Update existing record
                            $update_stmt = $db->prepare("UPDATE attendance SET 
                                name = ?, Present = ?, SchOpen = ?, DateOfReport = ?, 
                                NextTermBegins = ?, TeachersRemarks = ?, 
                                class = ?, subclass = ?, year = ?, teacher_no = ?, term = ?
                                WHERE registration_no = ? AND csv_no = ?");
                            
                            $update_stmt->execute([
                                $name, $present, $sch_open, $date_of_report, 
                                $next_term_begins, $teachers_remarks,
                                $s_class, $s_subclass, $s_year, $teacher_no, $s_term,
                                $registration_no, $key
                            ]);
                        } else {
                            // Insert new record
                            $insert_stmt = $db->prepare("INSERT INTO attendance 
                                (name, registration_no, Present, SchOpen, DateOfReport, 
                                NextTermBegins, TeachersRemarks, class, subclass, 
                                year, teacher_no, csv_no, term) 
                                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
                            
                            $insert_stmt->execute([
                                $name, $registration_no, $present, $sch_open, $date_of_report,
                                $next_term_begins, $teachers_remarks, $s_class, $s_subclass,
                                $s_year, $teacher_no, $key, $s_term
                            ]);
                        }
                        $success_count++;
                        
                    } catch(PDOException $e) {
                        $error_details[] = "Row {$row_num}: Database error - " . $e->getMessage();
                        $error_count++;
                    }
                }
                
                fclose($csvFile);
                
                // Set session messages
                if($success_count > 0) {
                    $_SESSION['success_message'] = "Successfully processed {$success_count} attendance records";
                    
                    if($error_count > 0) {
                        $_SESSION['warning_message'] = "{$error_count} records had errors";
                        $_SESSION['error_details'] = $error_details;
                    }
                    
                    // Store key in session for reference
                    $_SESSION['last_csv_no'] = $key;
                    
                    // Redirect with success parameters
                    $qstring = "?status=success&val=" . $key . "&records=" . $success_count;
                } else {
                    $_SESSION['error_message'] = "No records were imported. Please check your CSV file format.";
                    $_SESSION['error_details'] = $error_details;
                    $qstring = "?status=error&type=no_records";
                }
                
            } else {
                $_SESSION['error_message'] = "File upload failed";
                $qstring = "?status=error&type=upload_failed";
            }
        } else {
            $_SESSION['error_message'] = "Invalid file type. Please upload a CSV file.";
            $qstring = "?status=error&type=invalid_file";
        }
        
    } catch(Exception $e) {
        $_SESSION['error_message'] = "Error: " . $e->getMessage();
        $qstring = "?status=error&type=exception";
    }
    
    header("Location: post_attendance.php" . $qstring);
    exit();
}

// If no form submitted, redirect back
header("Location: post_attendance.php");
exit();
?>