<?php
session_name('super_admin_session');
if (!is_dir(dirname(__DIR__) . '/session_data/super')) { mkdir(dirname(__DIR__) . '/session_data/super', 0700, true); } session_save_path(dirname(__DIR__) . '/session_data/super');
session_set_cookie_params(['path' => '/', 'httponly' => true, 'samesite' => 'Strict']);
session_start();
error_reporting(E_ALL);
ini_set('display_errors', 1);
include('includes/dbconnection.php');

if (!isset($_SESSION['sid']) || strlen($_SESSION['sid']) == 0) {
    header('location:logout.php');
    exit();
}

require_once('vendor/autoload.php');

use Dotenv\Dotenv;
use Mpdf\Mpdf;

// Load environment variables
$dotenv = Dotenv::createImmutable(__DIR__);
$dotenv->load();

// Remark generator
function generateRemark($percentage)
{
    if ($percentage >= 75) {
        return 'Outstanding Performance!';
    } elseif ($percentage >= 60) {
        return 'Commendable performance!';
    } elseif ($percentage >= 50) {
        return 'Satisfactory Performance!';
    } elseif ($percentage >= 40) {
        return 'Marginal Performance!';
    } else {
        return 'Unsatisfactory Performance!';
    }
}

// Get filter parameters from URL
$session_id = $_GET['session_id'] ?? null;
$term_id = $_GET['term_id'] ?? null;
$class = $_GET['class'] ?? null;
$section = $_GET['section'] ?? null;

// Validate required parameters
if (!$session_id || !$term_id || !$class) {
    die("Session ID, Term ID, and Class are required parameters");
}

// Get selected names
$session_query = mysqli_query($con, "SELECT session_name FROM school_sessions WHERE id = '$session_id'");
$term_query = mysqli_query($con, "SELECT term_name FROM school_terms WHERE id = '$term_id'");

if (!$session_query || !$term_query) {
    die("Error fetching session or term information: " . mysqli_error($con));
}

$session_row = mysqli_fetch_assoc($session_query);
$term_row = mysqli_fetch_assoc($term_query);

if (!$session_row || !$term_row) {
    die("Invalid session or term ID");
}

$selected_session = $session_row['session_name'];
$selected_term = $term_row['term_name'];

// Build students query based on section
$students_query = "SELECT id, studentName, class, section, gender, age, studentImage, stream, demote_status 
                   FROM students WHERE class = '$class'";
if ($section && $section != 'All') {
    $students_query .= " AND section = '$section'";
}
$students_result = $con->query($students_query);

if (!$students_result) {
    die("Error fetching students: " . $con->error);
}

if ($students_result->num_rows == 0) {
    die("No students found in the selected class/section");
}

// Get environment variables
$school_name = $_ENV['SCHOOL_NAME_1'] ?? 'School Name';
$logo = $_ENV['WEBSITE_LOGO'] ?? '';
$address = $_ENV['id_address'] ?? '';
$school_email = $_ENV['SCHOOL_EMAIL'] ?? '';
$signature = $_ENV['PRINCIPAL_SIGNATURE'] ?? '';
$website = $_ENV['SCHOOL_WEBSITE'] ?? '';

// Create unique download folder
$downloadFolder = 'temp_downloads_' . uniqid();
if (!file_exists($downloadFolder)) {
    mkdir($downloadFolder, 0777, true);
}

// Debug counters
$total_students = $students_result->num_rows;
$processed_count = 0;
$skipped_payment = 0;
$skipped_no_results = 0;

// Process each student
while ($student = $students_result->fetch_assoc()) {
    $student_id = $student['id'];
    $student_class = $student['class'];
    $student_stream = $student['stream'] ?? '';
    $student_repeat = $student['demote_status'] ?? 0;
    $student_section = $student['section'] ?? '';
    $studentImage = "studentimages/" . htmlspecialchars($student['studentImage'] ?? '');
    $studentName = htmlspecialchars($student['studentName'] ?? 'Unknown');
    
    // Check payment status for this specific student
    $payQuery = "SELECT pay_for FROM payment 
            WHERE student_id = ? 
            AND class = ? 
            AND (school_type = ? OR school_type IS NULL OR school_type = '')
            AND session = ?
            AND Term = ?
            AND pay_for LIKE 'School Fees%'
            LIMIT 1";
            
    $PaidStmt = $con->prepare($payQuery);
    if (!$PaidStmt) {
        error_log("Payment prepare failed: " . $con->error);
        continue;
    }
    
    $PaidStmt->bind_param("issss", $student_id, $student_class, $student_stream, $selected_session, $selected_term);
    if (!$PaidStmt->execute()) {
        error_log("Payment query failed: " . $PaidStmt->error);
        continue;
    }
    
    $PaidResult = $PaidStmt->get_result();
    $PaidRow = $PaidResult->fetch_assoc();
    $pay_for = $PaidRow['pay_for'] ?? '';

    if ($pay_for == 'School Fees') {
        $student_type = "Old";
    } elseif ($pay_for == 'School Fees (New Student)') {
        $student_type = "New";
    } else {
        // Skip this student if no payment record found
        $skipped_payment++;
        continue;
    }

    // Get default fee for this student
    $feeQuery = "SELECT amount FROM fee_records 
    WHERE class = ? 
    AND (stream = ? OR stream IS NULL OR stream = '')
    AND name = ? 
    AND student_type = ? 
    AND session = ? 
    AND term = ? 
    ORDER BY created_at DESC 
    LIMIT 1";
    
    $stmt = $con->prepare($feeQuery);
    if (!$stmt) {
        error_log("Fee prepare failed: " . $con->error);
        continue;
    }
    
    $stmt->bind_param("ssssss", $student_class, $student_stream, $pay_for, $student_type, $selected_session, $selected_term);
    if (!$stmt->execute()) {
        error_log("Fee query failed: " . $stmt->error);
        continue;
    }
    
    $feeResult = $stmt->get_result();
    $feeRow = $feeResult->fetch_assoc();
    $totalFee = $feeRow['amount'] ?? 0;
    $currentTermTotalFees = $totalFee;

    // Handle special fee cases
    if (trim($student_class) === 'SS 1' && $student_repeat === 1 && $selected_term === 'First Term') {
        $feeQuery = "SELECT amount FROM fee_records 
        WHERE class = ? 
        AND (stream = ? OR stream IS NULL OR stream = '')
        AND name = ? 
        AND student_type = ? 
        AND session = ? 
        AND term = ? 
        ORDER BY created_at DESC 
        LIMIT 1";
        
        $stmt = $con->prepare($feeQuery);
        if ($stmt) {
            $stmt->bind_param("ssssss", $student_class, $student_stream, $pay_for, $student_type, $selected_session, $selected_term);
            if ($stmt->execute()) {
                $feeResult = $stmt->get_result();
                $feeRow = $feeResult->fetch_assoc();
                $totalFee1 = $feeRow['amount'] ?? $totalFee;
                $currentTermTotalFees = $totalFee1;
            }
        }
    }
    
    if ($selected_term === 'First Term' && in_array($student_class, ['JSS 3', 'SS 1', 'SS 3'])) {
        $special_fee_names = [
            'JSS 3' => 'First Term JSS 3 School Fees',
            'SS 1'  => 'First Term SS 1 School Fees',
            'SS 3'  => 'First Term SS 3 School Fees'
        ];
        $special_fee_name = $special_fee_names[$student_class] ?? '';
        
        if ($special_fee_name) {
            $feeQuery = "SELECT amount FROM fees 
            WHERE class = ? 
            AND (stream = ? OR stream IS NULL OR stream = '')
            AND student_type = 'Old' 
            AND name = ? 
            LIMIT 1";
            
            $stmt = $con->prepare($feeQuery);
            if ($stmt) {
                $stmt->bind_param("sss", $student_class, $student_stream, $special_fee_name);
                if ($stmt->execute()) {
                    $feeResult = $stmt->get_result();
                    $feeRow = $feeResult->fetch_assoc();
                    if ($feeRow) {
                        $currentTermTotalFees = $feeRow['amount'];
                    }
                }
            }
        }
    }

    // Calculate total paid amount
    $totalPaidQuery = "SELECT SUM(amount) as total_paid FROM payment 
            WHERE student_id = ? 
            AND class = ? 
            AND pay_for = ? 
            AND (school_type = ? OR school_type IS NULL OR school_type = '')
            AND session = ?
            AND Term = ?";
            
    $totalPaidStmt = $con->prepare($totalPaidQuery);
    if (!$totalPaidStmt) {
        error_log("Total paid prepare failed: " . $con->error);
        continue;
    }
    
    $totalPaidStmt->bind_param("isssss", $student_id, $student_class, $pay_for, $student_stream, $selected_session, $selected_term);
    if (!$totalPaidStmt->execute()) {
        error_log("Total paid query failed: " . $totalPaidStmt->error);
        continue;
    }
    
    $totalPaidResult = $totalPaidStmt->get_result();
    $totalPaidRow = $totalPaidResult->fetch_assoc();
    $totalPaidAmount = $totalPaidRow['total_paid'] ?? 0;

    // Check if student paid in full (at least 70%)
    if ($currentTermTotalFees > 0 && $totalPaidAmount < ($currentTermTotalFees * 0.7)) {
        $skipped_payment++;
        continue;
    }
    
    // Fetch academic results
    $results_sql = "SELECT term_id, subject, test_score, test_score2, class_performance, weekly_assessment, remarks_test,
                        (test_score + test_score2 + class_performance + weekly_assessment) AS total_score, 
                        remarks_test, test_absent, test2_absent, class_performance_absent, weekly_assessment_absent
                    FROM result2
            WHERE student_id = '$student_id' 
            AND session_id = '$session_id' 
            AND term_id = '$term_id'
            AND class = '$class'";

    $results_result = $con->query($results_sql);
    
    if (!$results_result) {
        error_log("Results query failed for student $student_id: " . $con->error);
        $skipped_no_results++;
        continue;
    }
    
    $subjects_results = [];
    $total_score = 0;
    $total_subjects = 0;
    
    if ($results_result->num_rows > 0) {
        while ($row = $results_result->fetch_assoc()) {
            $subjects_results[] = $row;
        }
        $total_subjects = count($subjects_results);
    } else {
        $skipped_no_results++;
        continue; // Skip students with no results
    }

    $mark_obtainable = $total_subjects * 30;
    
    foreach ($subjects_results as $result) {
        $total_score += $result['total_score'] ?? 0;
    }
    
    $percentage = $total_subjects ? round(($total_score / $mark_obtainable) * 100, 2) : 0;
    $average_score = $total_subjects ? round($total_score / $total_subjects, 2) : 0;
    $principal_remarks = generateRemark($percentage);

    ob_start();
?>
<!DOCTYPE html>
<html>
<head>
    <style>
        .header {
            padding-top: 20px;
            text-align: center;
            font-family: 'Times New Roman', Times, serif;
            font-weight: bold;
        }
        .logo {
            float: left;
            margin-right: 20px;
        }
        .school-name {
            float: right;
        }
        .report-title {
            font-size: 14px;
            font-weight: bold;
            background-color: #ff0000;
            color: white;
            padding: 5px;
            text-align: center;
            width: fit-content;
            margin: 10px auto;
        }
        table {
            width: 100%;
            border-collapse: collapse;
            margin-top: 20px;
        }
        th, td {
            border: 1px solid #000;
            padding: 8px;
            text-align: left;
        }
        th {
            background-color: #f2f2f2;
        }
        .comment-section, .signature-section {
            margin-top: 20px;
        }
        .line {
            border-bottom: 1px solid #000;
            margin: 5px 0;
        }
    </style>
</head>
<body>
    <table width="100%" border="0" style="border-collapse: collapse; font-family: 'Times New Roman', Times, serif; font-size: 12px;">
        <tr>
            <td style="text-align: left; vertical-align: top; width: 10%; border:none;">
                <img src="<?php echo $logo; ?>" width="100" alt="logo">
            </td>
            <td style="text-align: center; width: 90%; border:none">
                <h1 class="mb-0 fw-bold" style="font-weight:bold; font-size: 36px;"><?php echo strtoupper($school_name) ?></h1>
                <?php echo $address; ?><br>
                <b>Website:</b> <span><?php echo $website; ?></span>
                <b> | E-mail:</b> <span><?php echo $school_email; ?></span>
            </td>
        </tr>
    </table>
    <div style="text-align: center; font-weight: bold; font-size: 18px; margin: 10px 0;">
        MID-TERM REPORT
    </div>
    <hr style="border: 1px solid #000; margin: 5px 0;">
    <div class="card-body">
        <table style="border-collapse: collapse; border: none; width: 100%; font-size: 12px; text-align: left;">
                <tr>
                    <td style="border: none; max-width: 100px;"><strong>NAME:</strong> <?php echo htmlspecialchars($student['studentName']); ?></td>
                    <td style="border: none; max-width: 100px;"><strong>CLASS:</strong> <?php echo htmlspecialchars($class); ?> - <?php echo htmlspecialchars($section); ?></td>
                    <td style="border: none; max-width: 100px;"><strong>TERM:</strong> <?php echo htmlspecialchars($selected_term); ?></td>            
                    <td style="border: none; max-width: 100px;"><strong>SESSION:</strong> <?php echo htmlspecialchars($selected_session); ?></td>
                </tr>
        </table>
<table style="font-size:12px; text-align:center; width:100%; border-collapse:collapse; table-layout:fixed;">
    <thead>
        <tr>
            <th style="font-style: italic; width:25%;">Subject</th>
            <th style="font-style: italic; text-align: center; width:15%;">1st C.A. Test</th>
            <th style="font-style: italic; text-align: center; width:15%;">2nd C.A. Test</th>
            <th style="font-style: italic; text-align: center; width:15%;">Ass/Exe</th>
            <th style="font-style: italic; text-align: center; width:15%;">Total</th>
            <th style="font-style: italic; text-align: center; width:15%;">Grade</th>
        </tr>
    </thead>
    <tbody>
                <?php if (!empty($subjects_results)) : ?>
                    <?php foreach ($subjects_results as $result) : ?>
                        <?php
                        $test_score = ($result['test_absent'] == "1") ? 'AB' : (int)$result['test_score'];
                        $test_score2 = ($result['test2_absent'] == "1") ? 'AB' : (int)$result['test_score2'];
                        $class_performance = ($result['class_performance_absent'] == "1") ? 'AB' : (int)$result['class_performance'];
                        $weekly_assessment = ($result['weekly_assessment_absent'] == "1") ? 'AB' : (int)$result['weekly_assessment'];
                        
                        if ($class_performance === 'AB' && $weekly_assessment === 'AB') {
                            $ass_exe = 'AB';
                        } elseif ($class_performance === 'AB') {
                            $ass_exe = $weekly_assessment;
                        } elseif ($weekly_assessment === 'AB') {
                            $ass_exe = $class_performance;
                        } else {
                            $ass_exe = $class_performance + $weekly_assessment;
                        }
                        
                        $total = 0;
                        foreach ([$test_score, $test_score2, $ass_exe] as $score) {
                            if (is_numeric($score)) $total += $score;
                        }
                        
                        $grade = '-';
                        if ($total > 0) {
                            if ($total >= 25) $grade = 'Excellent';
                            elseif ($total >= 20) $grade = 'Very Good';
                            elseif ($total >= 15) $grade = 'Good';
                            elseif ($total >= 10) $grade = 'Pass';
                            else $grade = 'Fair';
                        }
                        ?>
                        <tr>
                            <td><?php echo htmlspecialchars($result['subject']); ?></td>
                            <td style="text-align: center;"><?php echo htmlspecialchars($test_score); ?></td>
                            <td style="text-align: center;"><?php echo htmlspecialchars($test_score2); ?></td>
                            <td style="text-align: center;"><?php echo htmlspecialchars($ass_exe); ?></td>
                            <td style="text-align: center;"><?php echo htmlspecialchars($total); ?></td>
                            <td style="text-align: center;"><?php echo $grade; ?></td>
                        </tr>
                    <?php endforeach; ?>
                <?php else : ?>
                    <tr>
                        <td colspan="6" style="text-align: center;">No results found.</td>
                    </tr>
                <?php endif; ?>
            </tbody>
        </table>
        <table style="width: 100%; margin-top: 20px; border-collapse: collapse; border: none; font-size:12px;">
            <tr>
                <td style="padding: 10px; border: none; width: 100%;">
                    <label for="class-teacher-comment"><strong>Class Teacher's Comment:</strong></label>
                    <span><?= htmlspecialchars($result['remarks_test'] ?? '') ?></span>
                </td>
            </tr>
            <tr>
                <td style="padding: 10px; border: none; width: 100%;">
                    <label for="class-teacher-comment"><strong>Principal's Comment:</strong></label>
                    <span><?= htmlspecialchars($principal_remarks) ?></span>
                </td>
            </tr>
        </table>
    </div>
</body>
</html>
<?php
    $content = ob_get_clean();
    $filePath = $downloadFolder . '/result_' . preg_replace('/[^a-zA-Z0-9]/', '_', $studentName) . '.pdf';

    try {
        $mpdf = new Mpdf();
        $mpdf->SetWatermarkText('Not for Official Use', 0.1);
        $mpdf->showWatermarkText = true;
        $mpdf->watermarkTextAlpha = 0.1;
        $mpdf->watermark_font = 'DejaVuSans';
        $mpdf->WriteHTML($content);
        $mpdf->Output($filePath, 'F');
        $processed_count++;
    } catch (Exception $e) {
        error_log('Error generating PDF for ' . $studentName . ': ' . $e->getMessage());
        continue;
    }
}

// Create ZIP archive of all generated PDFs
$pdfFiles = glob($downloadFolder . '/*.pdf');
if (count($pdfFiles) > 0) {
    $zipFilename = 'Fully_Paid_Mid_Term_' . $class . ($section ? '_' . $section : '') . '_' . $selected_term . '.zip';
    $zipFile = $downloadFolder . '/' . $zipFilename;
    $zip = new ZipArchive();

    if ($zip->open($zipFile, ZipArchive::CREATE) === TRUE) {
        foreach ($pdfFiles as $file) {
            $zip->addFile($file, basename($file));
        }
        $zip->close();

        // Clear output buffers
        while (ob_get_level()) {
            ob_end_clean();
        }

        // Set headers and output the ZIP file
        header('Content-Type: application/zip');
        header('Content-Disposition: attachment; filename="' . $zipFilename . '"');
        header('Content-Length: ' . filesize($zipFile));
        readfile($zipFile);

        // Clean up - remove all files
        array_map('unlink', glob($downloadFolder . '/*'));
        rmdir($downloadFolder);
        exit;
    } else {
        // Clean up if ZIP creation fails
        array_map('unlink', glob($downloadFolder . '/*'));
        rmdir($downloadFolder);
        die('Failed to create ZIP archive. Please check directory permissions.');
    }
} else {
    // Clean up if no PDFs were generated
    if (file_exists($downloadFolder)) {
        array_map('unlink', glob($downloadFolder . '/*'));
        rmdir($downloadFolder);
    }
    
    // Provide detailed error message
    $error_msg = "No PDFs generated. Details:\n";
    $error_msg .= "Total students: $total_students\n";
    $error_msg .= "Skipped due to payment issues: $skipped_payment\n";
    $error_msg .= "Skipped due to no results: $skipped_no_results\n";
    $error_msg .= "Successfully processed: $processed_count";
    
    die($error_msg);
}
?>