<?php
/**
 * Enterprise HRM ERP System - Attendance Sheet (EXACT EXCEL REPLICATION)
 * Excel-like format with daily columns (1-31) and COUNTIF formulas
 */

require_once __DIR__ . '/../../includes/init.php';
requireLogin();

$pageTitle = 'Attendance';
$currentModule = 'attendance';
$currentTab = 'attendance';

$siteId = getCurrentSiteId();
$selectedMonth = isset($_GET['month']) ? $_GET['month'] : date('Y-m');
$selectedYearNum = (int)explode('-', $selectedMonth)[0];
$selectedMonthNum = (int)explode('-', $selectedMonth)[1];

try {
    $db = getDB();
    
    // Get month name (reference from Home like Excel)
    $monthName = date('M-y', strtotime("$selectedYearNum-$selectedMonthNum-01"));
    
    // Get all employees for this site
    $stmt = $db->prepare("
        SELECT e.*, des.designation_name, d.department_name
        FROM employees e
        LEFT JOIN designations des ON e.designation_id = des.designation_id
        LEFT JOIN departments d ON e.department_id = d.department_id
        WHERE e.site_id = ? AND e.employment_status = 'active'
        ORDER BY e.employee_code
    ");
    $stmt->execute([$siteId]);
    $employees = $stmt->fetchAll();
    
    // Get days in month
    $totalDays = cal_days_in_month(CAL_GREGORIAN, $selectedMonthNum, $selectedYearNum);
    
    // Get attendance data for the month
    $startDate = date('Y-m-01', strtotime("$selectedYearNum-$selectedMonthNum-01"));
    $endDate = date('Y-m-t', strtotime($startDate));
    
    $attendanceMap = [];
    if (!empty($employees)) {
        $empIds = array_column($employees, 'employee_id');
        $placeholders = implode(',', array_fill(0, count($empIds), '?'));
        
        $stmt = $db->prepare("
            SELECT employee_id, DAY(attendance_date) as day, attendance_status
            FROM attendance
            WHERE employee_id IN ($placeholders) 
            AND attendance_date >= ? AND attendance_date <= ?
        ");
        $params = array_merge($empIds, [$startDate, $endDate]);
        $stmt->execute($params);
        $attendanceRecords = $stmt->fetchAll();
        
        // Build attendance map [employee_id][day] = status_code
        foreach ($attendanceRecords as $rec) {
            $empId = $rec['employee_id'];
            $day = (int)$rec['day'];
            $status = $rec['attendance_status'];
            
            // Convert to Excel codes
            $code = 'A'; // Default absent
            if ($status == 'present') $code = 'P';
            elseif ($status == 'half_day') $code = 'H';
            elseif ($status == 'on_leave') {
                // Check leave type
                $stmt2 = $db->prepare("
                    SELECT lt.leave_code 
                    FROM leave_applications la
                    INNER JOIN leave_types lt ON la.leave_type_id = lt.leave_type_id
                    WHERE la.employee_id = ? 
                    AND DATE(la.start_date) <= ?
                    AND DATE(la.end_date) >= ?
                    AND la.application_status = 'approved'
                    LIMIT 1
                ");
                $dateStr = date('Y-m-d', strtotime("$selectedYearNum-$selectedMonthNum-$day"));
                $stmt2->execute([$empId, $dateStr, $dateStr]);
                $leave = $stmt2->fetch();
                if ($leave) {
                    $leaveCode = strtoupper($leave['leave_code']);
                    if ($leaveCode == 'CL') $code = 'CL';
                    elseif ($leaveCode == 'PL') $code = 'PL';
                    elseif ($leaveCode == 'SL') $code = 'SL';
                }
            }
            
            if (!isset($attendanceMap[$empId])) {
                $attendanceMap[$empId] = [];
            }
            $attendanceMap[$empId][$day] = $code;
        }
        
        // Add Week Offs (Sundays by default)
        $weekOffDay = getSetting('WEEK_OFF_DAY', 0); // 0=Sunday
        for ($day = 1; $day <= $totalDays; $day++) {
            $date = date('Y-m-d', strtotime("$selectedYearNum-$selectedMonthNum-$day"));
            $dayOfWeek = (int)date('w', strtotime($date));
            
            if ($dayOfWeek == $weekOffDay) {
                foreach ($empIds as $empId) {
                    if (!isset($attendanceMap[$empId][$day])) {
                        $attendanceMap[$empId][$day] = 'WO';
                    }
                }
            }
        }
    }
    
    // Calculate COUNTIF formulas for each employee
    $attendanceSummary = [];
    foreach ($employees as $emp) {
        $empId = $emp['employee_id'];
        $dailyAttendance = $attendanceMap[$empId] ?? [];
        
        // Excel COUNTIF formulas
        $summary = [
            'P' => 0,   // =COUNTIF(I8:AM8,"P")
            'H' => 0,   // =COUNTIF(J8:AN8,"H")
            'CL' => 0,  // =COUNTIF(K8:AO8,"CL")
            'PL' => 0,  // =COUNTIF(L8:AP8,"PL")
            'SL' => 0,  // =COUNTIF(M8:AQ8,"SL")
            'A' => 0,   // =COUNTIF(K8:AM8,"A")
            'WO' => 0   // =COUNTIF(I8:AM8,"WO")
        ];
        
        // Count codes in daily attendance
        foreach ($dailyAttendance as $day => $code) {
            if ($code == 'P') $summary['P']++;
            elseif ($code == 'H') $summary['H']++;
            elseif ($code == 'CL') $summary['CL']++;
            elseif ($code == 'PL') $summary['PL']++;
            elseif ($code == 'SL') $summary['SL']++;
            elseif ($code == 'A') $summary['A']++;
            elseif ($code == 'WO') $summary['WO']++;
        }
        
        // Count absent days (days without any record)
        for ($day = 1; $day <= $totalDays; $day++) {
            if (!isset($dailyAttendance[$day])) {
                $date = date('Y-m-d', strtotime("$selectedYearNum-$selectedMonthNum-$day"));
                $dayOfWeek = (int)date('w', strtotime($date));
                if ($dayOfWeek != $weekOffDay) {
                    $summary['A']++;
                }
            }
        }
        
        $attendanceSummary[$empId] = $summary;
    }
    
} catch (Exception $e) {
    error_log("Attendance Excel Format error: " . $e->getMessage());
    $employees = [];
    $totalDays = 31;
    $attendanceMap = [];
    $attendanceSummary = [];
    $monthName = date('M-y');
}

include __DIR__ . '/../../includes/header.php';
include __DIR__ . '/../payroll/_navigation.php';
?>

<style>
.excel-attendance {
    font-family: Calibri, Arial, sans-serif;
    font-size: 11px;
    padding: 20px;
    background: #f5f5f5;
}

.excel-attendance table {
    border-collapse: collapse;
    width: 100%;
    min-width: 2500px;
}

.excel-attendance th,
.excel-attendance td {
    border: 1px solid #000;
    padding: 3px 5px;
    text-align: center;
    white-space: nowrap;
}

.excel-attendance th {
    background: #1F3864;
    color: #FFF;
    font-weight: bold;
    font-size: 11px;
    position: sticky;
    top: 0;
    z-index: 10;
}

.excel-attendance .header-row {
    background: #1F3864;
    color: #FFF;
}

.excel-attendance .month-header {
    background: #CCFFFF;
    font-weight: bold;
}

.excel-attendance input[type="text"] {
    width: 100%;
    border: none;
    background: transparent;
    text-align: center;
    font-size: 11px;
    padding: 2px;
}

.excel-attendance .employee-name {
    text-align: left;
    min-width: 150px;
}

.excel-attendance .summary-cell {
    background: #DEEAF6;
    font-weight: bold;
}

.excel-attendance .day-cell {
    min-width: 35px;
    max-width: 35px;
}

.excel-attendance .code-P { background: #90EE90; }
.excel-attendance .code-A { background: #FFB6C1; }
.excel-attendance .code-H { background: #FFFF99; }
.excel-attendance .code-CL { background: #87CEEB; }
.excel-attendance .code-PL { background: #DDA0DD; }
.excel-attendance .code-SL { background: #F0E68C; }
.excel-attendance .code-WO { background: #D3D3D3; }
</style>

<div class="excel-attendance">
    <!-- Month Header (Row 2-3) -->
    <div style="margin-bottom: 10px;">
        <table style="width: auto; border: none;">
            <tr>
                <td style="border: none; padding: 5px;"><strong>Month:</strong></td>
                <td style="border: 1px solid #000; padding: 5px; background: #CCFFFF; font-weight: bold;">
                    <?php echo htmlspecialchars($monthName); ?>
                </td>
                <td style="border: none; padding: 5px; width: 50px;"></td>
                <td style="border: none;">
                    <select name="month" onchange="window.location.href='?month='+this.value" style="padding: 5px;">
                        <?php
                        for ($y = date('Y') - 1; $y <= date('Y') + 1; $y++) {
                            for ($m = 1; $m <= 12; $m++) {
                                $monthValue = sprintf('%04d-%02d', $y, $m);
                                $monthNameOpt = date('M-y', strtotime("$y-$m-01"));
                                $selected = ($selectedMonth == $monthValue) ? 'selected' : '';
                                echo "<option value='$monthValue' $selected>$monthNameOpt</option>";
                            }
                        }
                        ?>
                    </select>
                </td>
            </tr>
        </table>
    </div>
    
    <!-- Main Attendance Table -->
    <div style="overflow-x: auto;">
        <table>
            <thead>
                <!-- Row 5: Payroll | Attendance | Remarks -->
                <tr class="header-row">
                    <th rowspan="3" style="vertical-align: middle; min-width: 50px;">S/L No</th>
                    <th rowspan="3" style="vertical-align: middle; min-width: 150px;">Name</th>
                    <th rowspan="3" style="vertical-align: middle; min-width: 120px;">Designation</th>
                    <th rowspan="3" style="vertical-align: middle; min-width: 100px;">Employee Type</th>
                    <th rowspan="3" style="vertical-align: middle; min-width: 100px;">Category</th>
                    <th rowspan="3" style="vertical-align: middle; min-width: 80px;">Status</th>
                    <th colspan="<?php echo $totalDays; ?>" style="text-align: center; background: #1F3864;">Attendance</th>
                    <th colspan="7" style="text-align: center; background: #1F3864;">Attendance</th>
                    <th rowspan="3" style="vertical-align: middle; min-width: 100px;">Remarks</th>
                </tr>
                <!-- Row 6: Day numbers -->
                <tr class="header-row">
                    <?php for ($day = 1; $day <= $totalDays; $day++): ?>
                    <th class="day-cell"><?php echo $day; ?></th>
                    <?php endfor; ?>
                    <th>P</th>
                    <th>H</th>
                    <th>CL</th>
                    <th>PL</th>
                    <th>SL</th>
                    <th>A</th>
                    <th>WO</th>
                </tr>
            </thead>
            <tbody>
                <?php 
                $rowNum = 8; // Excel rows start from 8
                foreach ($employees as $index => $emp): 
                    $empId = $emp['employee_id'];
                    $dailyAtt = $attendanceMap[$empId] ?? [];
                    $summary = $attendanceSummary[$empId] ?? ['P' => 0, 'H' => 0, 'CL' => 0, 'PL' => 0, 'SL' => 0, 'A' => 0, 'WO' => 0];
                ?>
                <tr>
                    <td style="text-align: center;"><?php echo $index + 1; ?></td>
                    <td class="employee-name"><?php echo htmlspecialchars(trim($emp['first_name'] . ' ' . ($emp['middle_name'] ?? '') . ' ' . $emp['last_name'])); ?></td>
                    <td style="text-align: left;"><?php echo htmlspecialchars($emp['designation_name'] ?? 'N/A'); ?></td>
                    <td style="text-align: center;"><?php echo htmlspecialchars($emp['employment_type'] ?? 'Regular'); ?></td>
                    <td style="text-align: center;"><?php echo htmlspecialchars($emp['employee_category'] ?? 'Highly Skilled'); ?></td>
                    <td style="text-align: center;"><?php echo htmlspecialchars($emp['employment_status'] ?? 'Active'); ?></td>
                    
                    <!-- Daily Attendance Columns (1-31) -->
                    <?php for ($day = 1; $day <= $totalDays; $day++): 
                        $code = $dailyAtt[$day] ?? '';
                        $class = $code ? "code-$code" : '';
                    ?>
                    <td class="day-cell <?php echo $class; ?>">
                        <input type="text" value="<?php echo htmlspecialchars($code); ?>" 
                               style="width: 30px; text-align: center;" readonly>
                    </td>
                    <?php endfor; ?>
                    
                    <!-- Summary Columns (COUNTIF formulas) -->
                    <td class="summary-cell"><?php echo $summary['P']; ?></td>
                    <td class="summary-cell"><?php echo $summary['H']; ?></td>
                    <td class="summary-cell"><?php echo $summary['CL']; ?></td>
                    <td class="summary-cell"><?php echo $summary['PL']; ?></td>
                    <td class="summary-cell"><?php echo $summary['SL']; ?></td>
                    <td class="summary-cell"><?php echo $summary['A']; ?></td>
                    <td class="summary-cell"><?php echo $summary['WO']; ?></td>
                    
                    <td style="text-align: left;">
                        <input type="text" style="width: 100px; text-align: left;">
                    </td>
                </tr>
                <?php 
                $rowNum++;
                endforeach; 
                ?>
            </tbody>
        </table>
    </div>
    
    <!-- Legend -->
    <div style="margin-top: 20px; padding: 10px; background: white; border: 1px solid #000;">
        <strong>Legend:</strong>
        <span style="background: #90EE90; padding: 2px 8px; margin: 0 5px;">P = Present</span>
        <span style="background: #FFB6C1; padding: 2px 8px; margin: 0 5px;">A = Absent</span>
        <span style="background: #FFFF99; padding: 2px 8px; margin: 0 5px;">H = Half Day</span>
        <span style="background: #87CEEB; padding: 2px 8px; margin: 0 5px;">CL = Casual Leave</span>
        <span style="background: #DDA0DD; padding: 2px 8px; margin: 0 5px;">PL = Privileged Leave</span>
        <span style="background: #F0E68C; padding: 2px 8px; margin: 0 5px;">SL = Sick Leave</span>
        <span style="background: #D3D3D3; padding: 2px 8px; margin: 0 5px;">WO = Week Off</span>
    </div>
</div>

<?php include __DIR__ . '/../../includes/footer.php'; ?>
