<?php
// export_attendance.php

include('session_validator.php');
include('includes/db_pdo.php');

// Get export parameters
$export_type = $_POST['export_type'] ?? 'excel';
$academic_year = $_POST['academic_year'] ?? '';
$class_id = $_POST['class_id'] ?? '';
$section_ids = $_POST['section_ids'] ?? [];
$nepali_months = $_POST['nepali_months'] ?? [];
$min_attendance = $_POST['min_attendance'] ?? '';
$max_attendance = $_POST['max_attendance'] ?? '';
$status_filter = $_POST['status_filter'] ?? '';

// Build the query using only named parameters
$sql = "SELECT 
            s.student_id,
            s.name as student_name,
            s.class as roll_no,
            c.class as class_name,
            sec.section_name,
            COALESCE(SUM(ma.total_working_days), 0) as total_days,
            COALESCE(SUM(ma.days_present), 0) as days_present,
            COALESCE(SUM(ma.days_absent), 0) as days_absent,
            GROUP_CONCAT(DISTINCT ma.nepali_month ORDER BY FIELD(ma.nepali_month, 
                'Baisakh', 'Jestha', 'Ashadh', 'Shrawan', 'Bhadra', 'Ashwin', 
                'Kartik', 'Mangsir', 'Poush', 'Magh', 'Falgun', 'Chaitra')) as months
        FROM students s
        INNER JOIN student_class_section_data scsd ON s.student_id = scsd.student_id
        INNER JOIN classes c ON scsd.class_id = c.class_id
        INNER JOIN sections sec ON scsd.section_id = sec.section_id
        LEFT JOIN monthly_attendance ma ON s.student_id = ma.student_id 
            AND ma.class_id = scsd.class_id 
            AND ma.section_id = scsd.section_id
            AND ma.academic_year = scsd.academic_year
        WHERE s.status = 'active' 
            AND scsd.status = 'active'";

$params = [];

// Add filters using named parameters
if (!empty($academic_year)) {
    $sql .= " AND scsd.academic_year = :academic_year";
    $params[':academic_year'] = $academic_year;
}

if (!empty($class_id)) {
    $sql .= " AND scsd.class_id = :class_id";
    $params[':class_id'] = $class_id;
}

if (!empty($section_ids)) {
    $section_placeholders = [];
    foreach ($section_ids as $index => $id) {
        $param_name = ":section_id_" . $index;
        $section_placeholders[] = $param_name;
        $params[$param_name] = $id;
    }
    $sql .= " AND scsd.section_id IN (" . implode(',', $section_placeholders) . ")";
}

if (!empty($nepali_months)) {
    $month_placeholders = [];
    foreach ($nepali_months as $index => $month) {
        $param_name = ":month_" . $index;
        $month_placeholders[] = $param_name;
        $params[$param_name] = $month;
    }
    $sql .= " AND ma.nepali_month IN (" . implode(',', $month_placeholders) . ")";
}

$sql .= " GROUP BY s.student_id, s.name, s.class, c.class, sec.section_name
          ORDER BY c.class_numeric, sec.section_name, CAST(s.class AS UNSIGNED)";

// Prepare and execute with named parameters
$stmt = $conn->prepare($sql);

// Bind all parameters
foreach ($params as $key => &$value) {
    $stmt->bindParam($key, $value);
}

$stmt->execute();
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Process data for export
$exportData = [];
$headers = [
    'S.No.',
    'Student ID',
    'Student Name',
    'Class',
    'Section',
    'Roll No.',
    'Months',
    'Total Days',
    'Days Present',
    'Days Absent',
    'Attendance %',
    'Status'
];

$sno = 1;
foreach ($results as $row) {
    $totalDays = $row['total_days'];
    $daysPresent = $row['days_present'];
    
    if ($totalDays > 0) {
        $percentage = round(($daysPresent / $totalDays) * 100, 2);
    } else {
        $percentage = 0;
    }
    
    // Apply percentage filters
    if (!empty($min_attendance) && $percentage < $min_attendance) continue;
    if (!empty($max_attendance) && $percentage > $max_attendance) continue;
    
    if (!empty($status_filter)) {
        if ($status_filter == 'high' && $percentage < 75) continue;
        if ($status_filter == 'medium' && ($percentage < 60 || $percentage >= 75)) continue;
        if ($status_filter == 'low' && $percentage >= 60) continue;
    }
    
    $status = ($percentage >= 75) ? 'Good' : (($percentage >= 60) ? 'Average' : 'Poor');
    
    $exportData[] = [
        $sno++,
        $row['student_id'],
        $row['student_name'],
        $row['class_name'],
        $row['section_name'],
        $row['roll_no'],
        $row['months'],
        $totalDays,
        $daysPresent,
        ($totalDays - $daysPresent),
        $percentage . '%',
        $status
    ];
}

// Handle different export types
switch ($export_type) {
    case 'excel':
        exportExcel($headers, $exportData);
        break;
    case 'csv':
        exportCSV($headers, $exportData);
        break;
    case 'pdf':
        exportPDF($headers, $exportData);
        break;
    case 'print':
        exportPrint($headers, $exportData);
        break;
    default:
        exportExcel($headers, $exportData);
}

/**
 * Export to Excel format
 */
function exportExcel($headers, $data) {
    // Set headers for Excel download
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment; filename="attendance_report_' . date('Y-m-d') . '.xls"');
    header('Cache-Control: max-age=0');
    
    // Create output stream
    $output = fopen('php://output', 'w');
    
    // Add title
    fwrite($output, "Attendance Report - " . date('Y-m-d H:i:s') . "\n\n");
    
    // Add headers
    foreach ($headers as $index => $header) {
        if ($index > 0) fwrite($output, "\t");
        fwrite($output, $header);
    }
    fwrite($output, "\n");
    
    // Add data
    foreach ($data as $row) {
        $rowData = [];
        foreach ($row as $cell) {
            // Handle cells with commas by wrapping in quotes
            if (strpos($cell, ',') !== false || strpos($cell, '"') !== false || strpos($cell, "\n") !== false) {
                $cell = '"' . str_replace('"', '""', $cell) . '"';
            }
            $rowData[] = $cell;
        }
        fwrite($output, implode("\t", $rowData) . "\n");
    }
    
    // Add summary
    fwrite($output, "\n");
    fwrite($output, "Total Records:\t" . count($data) . "\n");
    fwrite($output, "Generated On:\t" . date('Y-m-d H:i:s') . "\n");
    
    fclose($output);
}

/**
 * Export to CSV format
 */
function exportCSV($headers, $data) {
    // Set headers for CSV download
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="attendance_report_' . date('Y-m-d') . '.csv"');
    header('Cache-Control: max-age=0');
    
    // Create output stream
    $output = fopen('php://output', 'w');
    
    // Add UTF-8 BOM for Excel compatibility
    fprintf($output, chr(0xEF).chr(0xBB).chr(0xBF));
    
    // Add headers
    fputcsv($output, $headers);
    
    // Add data
    foreach ($data as $row) {
        fputcsv($output, $row);
    }
    
    // Add empty row
    fputcsv($output, []);
    
    // Add summary
    fputcsv($output, ['Total Records:', count($data)]);
    fputcsv($output, ['Generated On:', date('Y-m-d H:i:s')]);
    
    fclose($output);
}

/**
 * Export to PDF format (HTML-based for simplicity)
 */
function exportPDF($headers, $data) {
    // Set headers for HTML download (can be converted to PDF using browser)
    header('Content-Type: text/html');
    header('Content-Disposition: attachment; filename="attendance_report_' . date('Y-m-d') . '.html"');
    header('Cache-Control: max-age=0');
    
    // Generate HTML content
    echo '<!DOCTYPE html>';
    echo '<html>';
    echo '<head>';
    echo '<meta charset="UTF-8">';
    echo '<title>Attendance Report</title>';
    echo '<style>';
    echo 'body { font-family: Arial, sans-serif; margin: 20px; }';
    echo 'h2 { color: #333; text-align: center; }';
    echo '.header-info { text-align: right; color: #666; margin-bottom: 20px; }';
    echo 'table { width: 100%; border-collapse: collapse; margin-top: 20px; }';
    echo 'th { background-color: #4CAF50; color: white; padding: 10px; text-align: left; }';
    echo 'td { padding: 8px; border: 1px solid #ddd; }';
    echo 'tr:nth-child(even) { background-color: #f2f2f2; }';
    echo '.footer { margin-top: 20px; text-align: center; color: #666; font-size: 12px; }';
    echo '.good { color: #28a745; font-weight: bold; }';
    echo '.average { color: #ffc107; font-weight: bold; }';
    echo '.poor { color: #dc3545; font-weight: bold; }';
    echo '@media print { th { background-color: #4CAF50 !important; color: white !important; } }';
    echo '</style>';
    echo '</head>';
    echo '<body>';
    
    echo '<h2>Attendance Report</h2>';
    echo '<div class="header-info">';
    echo 'Generated on: ' . date('Y-m-d H:i:s') . '<br>';
    echo 'Total Records: ' . count($data);
    echo '</div>';
    
    echo '<table>';
    
    // Headers
    echo '<thead><tr>';
    foreach ($headers as $header) {
        echo '<th>' . htmlspecialchars($header) . '</th>';
    }
    echo '</tr></thead>';
    
    // Data
    echo '<tbody>';
    foreach ($data as $row) {
        echo '<tr>';
        foreach ($row as $index => $cell) {
            $class = '';
            // Add status class for the Status column
            if ($index == 11) { // Status column
                if ($cell == 'Good') $class = 'good';
                elseif ($cell == 'Average') $class = 'average';
                elseif ($cell == 'Poor') $class = 'poor';
            }
            // Add percentage class for Attendance % column
            if ($index == 10) { // Attendance % column
                $percent = floatval(str_replace('%', '', $cell));
                if ($percent >= 75) $class = 'good';
                elseif ($percent >= 60) $class = 'average';
                else $class = 'poor';
            }
            
            echo '<td class="' . $class . '">' . htmlspecialchars($cell) . '</td>';
        }
        echo '</tr>';
    }
    echo '</tbody>';
    
    echo '</table>';
    
    echo '<div class="footer">';
    echo '<p>This is a computer-generated report. Signature not required.</p>';
    echo '</div>';
    
    echo '</body>';
    echo '</html>';
}

/**
 * Export to Print (redirect to print-friendly page)
 */
function exportPrint($headers, $data) {
    // Store data in session for print page
    session_start();
    $_SESSION['print_data'] = [
        'headers' => $headers,
        'data' => $data,
        'title' => 'Attendance Report',
        'generated_on' => date('Y-m-d H:i:s')
    ];
    
    // Redirect to print page
    header('Location: print_attendance.php');
    exit;
}

/**
 * Alternative PDF export using TCPDF if available
 * Uncomment and use if you have TCPDF library installed
 */
/*
function exportPDF_TCPDF($headers, $data) {
    require_once('tcpdf/tcpdf.php');
    
    // Create new PDF document
    $pdf = new TCPDF(PDF_PAGE_ORIENTATION, PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);
    
    // Set document information
    $pdf->SetCreator('SRMS');
    $pdf->SetAuthor('SRMS Admin');
    $pdf->SetTitle('Attendance Report');
    
    // Remove default header/footer
    $pdf->setPrintHeader(false);
    $pdf->setPrintFooter(false);
    
    // Add a page
    $pdf->AddPage();
    
    // Set font
    $pdf->SetFont('helvetica', '', 10);
    
    // Title
    $pdf->Cell(0, 10, 'Attendance Report', 0, 1, 'C');
    $pdf->Cell(0, 10, 'Generated on: ' . date('Y-m-d H:i:s'), 0, 1, 'R');
    $pdf->Ln(5);
    
    // Create table
    $html = '<table border="1" cellpadding="4">';
    
    // Headers
    $html .= '<thead><tr style="background-color:#4CAF50; color:#fff;">';
    foreach ($headers as $header) {
        $html .= '<th>' . $header . '</th>';
    }
    $html .= '</tr></thead>';
    
    // Data
    $html .= '<tbody>';
    foreach ($data as $row) {
        $html .= '<tr>';
        foreach ($row as $cell) {
            $html .= '<td>' . $cell . '</td>';
        }
        $html .= '</tr>';
    }
    $html .= '</tbody>';
    $html .= '</table>';
    
    // Output the HTML content
    $pdf->writeHTML($html, true, false, true, false, '');
    
    // Close and output PDF document
    $pdf->Output('attendance_report_' . date('Y-m-d') . '.pdf', 'D');
    exit;
}
*/
?>