<?php
require_once '../includes/config.php';

header('Content-Type: application/json');

// Check if user is logged in and is admin
if (!isset($_SESSION['logged_in']) || $_SESSION['logged_in'] !== true || $_SESSION['role'] !== 'admin') {
    echo json_encode(['success' => false, 'message' => 'Not authorized']);
    exit();
}

// Get filter parameters
$start_date = $_GET['start_date'] ?? date('Y-m-01');
$end_date = $_GET['end_date'] ?? date('Y-m-t');
$activity_id = $_GET['activity_id'] ?? '';
$course_id = $_GET['course_id'] ?? '';
$department_id = $_GET['department_id'] ?? '';
$status = $_GET['status'] ?? '';

// Build SQL query with filters
$where_conditions = ["DATE(a.created_at) BETWEEN '$start_date' AND '$end_date'"];
$join_tables = "";

if ($activity_id) {
    $where_conditions[] = "a.activity_id = " . intval($activity_id);
}

if ($course_id) {
    $join_tables .= " LEFT JOIN students s ON a.student_id = s.id";
    $where_conditions[] = "s.course_id = " . intval($course_id);
}

if ($department_id) {
    if (strpos($join_tables, 'students s') === false) {
        $join_tables .= " LEFT JOIN students s ON a.student_id = s.id";
    }
    $where_conditions[] = "s.department_id = " . intval($department_id);
}

if ($status && in_array($status, ['present', 'late', 'absent', 'excused'])) {
    $where_conditions[] = "a.status = '$status'";
}

$where_clause = count($where_conditions) > 0 ? "WHERE " . implode(" AND ", $where_conditions) : "";

// Get attendance records
$sql = "SELECT 
            DATE_FORMAT(a.created_at, '%Y-%m-%d') as date,
            DATE_FORMAT(a.created_at, '%H:%i:%s') as time,
            s.student_id,
            s.full_name as student_name,
            s.year_level,
            c.code as course_code,
            c.name as course_name,
            d.code as department_code,
            d.name as department_name,
            ac.name as activity_name,
            ac.location as activity_location,
            a.time_in,
            a.time_out,
            a.status,
            u.full_name as recorded_by,
            a.notes
        FROM attendance a
        LEFT JOIN students s ON a.student_id = s.id
        LEFT JOIN courses c ON s.course_id = c.id
        LEFT JOIN departments d ON s.department_id = d.id
        LEFT JOIN activities ac ON a.activity_id = ac.id
        LEFT JOIN users u ON a.created_by = u.id
        $join_tables
        $where_clause
        ORDER BY a.created_at DESC";

$result = query($conn, $sql);
$records = [];

while ($row = mysqli_fetch_assoc($result)) {
    $records[] = $row;
}

// Create CSV file
$filename = 'attendance_report_' . date('Y-m-d_H-i-s') . '.csv';
$filepath = '../exports/' . $filename;

// Create exports directory if not exists
if (!file_exists('../exports')) {
    mkdir('../exports', 0777, true);
}

// Open file for writing
$file = fopen($filepath, 'w');

// Add UTF-8 BOM for Excel compatibility
fputs($file, $bom = (chr(0xEF) . chr(0xBB) . chr(0xBF)));

// Add headers
$headers = [
    'Date',
    'Time',
    'Student ID',
    'Student Name',
    'Year Level',
    'Course Code',
    'Course Name',
    'Department Code',
    'Department Name',
    'Activity Name',
    'Activity Location',
    'Time In',
    'Time Out',
    'Status',
    'Recorded By',
    'Notes'
];
fputcsv($file, $headers);

// Add data rows
foreach ($records as $record) {
    fputcsv($file, [
        $record['date'],
        $record['time'],
        $record['student_id'],
        $record['student_name'],
        $record['year_level'],
        $record['course_code'],
        $record['course_name'],
        $record['department_code'],
        $record['department_name'],
        $record['activity_name'],
        $record['activity_location'],
        $record['time_in'],
        $record['time_out'],
        ucfirst($record['status']),
        $record['recorded_by'],
        $record['notes']
    ]);
}

fclose($file);

// Get statistics for summary sheet
$stats_sql = "SELECT 
                COUNT(*) as total,
                SUM(CASE WHEN status = 'present' THEN 1 ELSE 0 END) as present,
                SUM(CASE WHEN status = 'late' THEN 1 ELSE 0 END) as late,
                SUM(CASE WHEN status = 'absent' THEN 1 ELSE 0 END) as absent,
                SUM(CASE WHEN status = 'excused' THEN 1 ELSE 0 END) as excused
              FROM attendance 
              WHERE DATE(created_at) BETWEEN '$start_date' AND '$end_date'";
$stats_result = query($conn, $stats_sql);
$stats = mysqli_fetch_assoc($stats_result);

echo json_encode([
    'success' => true,
    'message' => 'Export completed',
    'download_url' => '../exports/' . $filename,
    'count' => count($records),
    'stats' => $stats,
    'period' => "$start_date to $end_date"
]);
?>