<?php
/**
 * Attendance Monthly Grid - Excel Format
 * Exact replica of Excel Attendance Sheet
 */

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

$pageTitle = "Attendance - Monthly Grid";
$currentTab = 'attendance'; // For navigation highlighting
$siteId = getCurrentSiteId();
$db = getDB();

// Get month and year from URL or default
$selectedMonthValue = isset($_GET['m']) ? $_GET['m'] : date('M');

// Default year: Use current year if 2025 or later, otherwise use '25' (2025)
$currentYear = (int)date('Y');
$defaultYear = ($currentYear >= 2025) ? date('y') : '25'; // '25' = 2025
$selectedYearValue = isset($_GET['y']) ? $_GET['y'] : $defaultYear;

// Combine for database query (Format: Jan-25)
$selectedMonth = $selectedMonthValue . '-' . $selectedYearValue;

// Convert selected month to display format
$selectedMonthDisplay = date('F Y', strtotime('01-' . $selectedMonth));

// Handle save "To" name POST request
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['save_to_name'])) {
    try {
        $toName = trim($_POST['to_name'] ?? '');
        
        // Create table if not exists
        $db->exec("
            CREATE TABLE IF NOT EXISTS `attendance_to_names` (
                `id` int(11) NOT NULL AUTO_INCREMENT,
                `site_id` int(11) NOT NULL,
                `to_name` varchar(255) DEFAULT NULL,
                `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
                `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (`id`),
                UNIQUE KEY `unique_site` (`site_id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        ");
        
        // Check if record exists for this site
        $checkStmt = $db->prepare("SELECT id FROM attendance_to_names WHERE site_id = ?");
        $checkStmt->execute([$siteId]);
        $existing = $checkStmt->fetch();
        
        if ($existing) {
            $updateStmt = $db->prepare("UPDATE attendance_to_names SET to_name = ? WHERE site_id = ?");
            $updateStmt->execute([$toName, $siteId]);
        } else {
            $insertStmt = $db->prepare("INSERT INTO attendance_to_names (site_id, to_name) VALUES (?, ?)");
            $insertStmt->execute([$siteId, $toName]);
        }
        
        $successMessage = 'To Name saved successfully!';
        // Redirect to prevent form resubmission
        header('Location: ?m=' . urlencode($selectedMonthValue) . '&y=' . urlencode($selectedYearValue) . '&to_saved=1');
        exit;
    } catch (Exception $e) {
        error_log("Error saving attendance To Name: " . $e->getMessage());
        $errorMessage = 'Error saving To Name: ' . $e->getMessage();
    }
}

// Check for to_saved parameter
if (isset($_GET['to_saved']) && $_GET['to_saved'] === '1') {
    $successMessage = 'To Name saved successfully!';
}

// Handle creation of new attendance period
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['create_new_period'])) {
    $newMonth = $_POST['new_month'];
    $newYear = $_POST['new_year'];
    $newPeriod = $newMonth . '-' . $newYear;
    $newPeriodDisplay = date('F Y', strtotime('01-' . $newPeriod));
    
    // Check if period already exists in attendance_periods table
    $checkStmt = $db->prepare("
        SELECT COUNT(*) as count 
        FROM attendance_periods 
        WHERE attendance_month = ? AND site_id = ?
    ");
    $checkStmt->execute([$newPeriod, $siteId]);
    $periodExists = $checkStmt->fetch()['count'] > 0;
    
    if ($periodExists) {
        $errorMessage = "Attendance period for " . $newPeriodDisplay . " already exists!";
    } else {
        // FIX: Check and fix period_id AUTO_INCREMENT BEFORE starting transaction
        try {
            $colCheck = $db->query("SHOW COLUMNS FROM attendance_periods LIKE 'period_id'");
            $colInfo = $colCheck->fetch(PDO::FETCH_ASSOC);
            
            if ($colInfo && strpos(strtolower($colInfo['Extra'] ?? ''), 'auto_increment') === false) {
                error_log("Fixing period_id AUTO_INCREMENT BEFORE transaction...");
                // Update any NULL or 0 period_id values first
                $nullCount = (int)$db->query("SELECT COUNT(*) FROM attendance_periods WHERE period_id IS NULL OR period_id = 0")->fetchColumn();
                if ($nullCount > 0) {
                    $maxId = (int)$db->query("SELECT COALESCE(MAX(period_id), 0) FROM attendance_periods WHERE period_id > 0")->fetchColumn();
                    for ($i = 0; $i < $nullCount; $i++) {
                        $maxId++;
                        $db->exec("UPDATE attendance_periods SET period_id = {$maxId} WHERE (period_id IS NULL OR period_id = 0) LIMIT 1");
                    }
                }
                // Add AUTO_INCREMENT
                $db->exec("ALTER TABLE attendance_periods MODIFY COLUMN `period_id` int(11) NOT NULL AUTO_INCREMENT");
                error_log("Fixed period_id AUTO_INCREMENT successfully");
            }
        } catch (Exception $autoIncErr) {
            error_log("Error checking/fixing period_id AUTO_INCREMENT: " . $autoIncErr->getMessage());
        }
        
        // Initialize attendance records for all active employees
        $activeEmployeesStmt = $db->prepare("
            SELECT employee_id 
            FROM employees 
            WHERE site_id = ? AND employment_status = 'active'
        ");
        $activeEmployeesStmt->execute([$siteId]);
        $activeEmployees = $activeEmployeesStmt->fetchAll();
        
        if (count($activeEmployees) > 0) {
            $db->beginTransaction();
            try {
                // 1. Insert into attendance_periods table to register this period
                $periodInsertStmt = $db->prepare("
                    INSERT INTO attendance_periods 
                    (site_id, attendance_month, period_display, employee_count, created_by, created_at) 
                    VALUES (?, ?, ?, ?, ?, NOW())
                ");
                $currentUserId = $_SESSION['user_id'] ?? null;
                $periodInsertStmt->execute([
                    $siteId, 
                    $newPeriod, 
                    $newPeriodDisplay, 
                    count($activeEmployees),
                    $currentUserId
                ]);
                
                // 2. Create attendance records for all active employees
                // CRITICAL: Ensure attendance_grid_id has AUTO_INCREMENT before insert
                try {
                    $columnCheck = $db->query("SHOW COLUMNS FROM attendance_monthly_grid LIKE 'attendance_grid_id'");
                    $columnInfo = $columnCheck->fetch(PDO::FETCH_ASSOC);
                    if ($columnInfo && stripos($columnInfo['Extra'] ?? '', 'auto_increment') === false) {
                        $db->exec("ALTER TABLE `attendance_monthly_grid` MODIFY COLUMN `attendance_grid_id` INT(11) NOT NULL AUTO_INCREMENT");
                    }
                } catch (Exception $e) {
                    // Continue - table might already be correct
                }
                
                $insertSql = "INSERT INTO attendance_monthly_grid (
                    site_id, employee_id, attendance_month, created_at
                ) VALUES (?, ?, ?, NOW())";
                $insertStmt = $db->prepare($insertSql);
                
                foreach ($activeEmployees as $emp) {
                    $insertStmt->execute([$siteId, $emp['employee_id'], $newPeriod]);
                }
                
                $db->commit();
                $successMessage = "New attendance period created for " . $newPeriodDisplay . " with " . count($activeEmployees) . " employees!";
                
                // Redirect to the newly created period
                header("Location: ?m=" . urlencode($newMonth) . "&y=" . urlencode($newYear) . "&created=1");
                exit;
            } catch (Exception $e) {
                if ($db->inTransaction()) {
                    $db->rollBack();
                }
                $errorMessage = "Error creating attendance period: " . $e->getMessage();
            }
        } else {
            $errorMessage = "No active employees found to create attendance period!";
        }
    }
}

// Check for created parameter
if (isset($_GET['created']) && $_GET['created'] === '1') {
    $successMessage = "New attendance period created successfully for " . $selectedMonthDisplay . "! You can now fill attendance data.";
}

// Handle form submission for saving attendance
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['save_attendance'])) {
    try {
        // CRITICAL: Check and fix table structure if needed (attendance_grid_id must be AUTO_INCREMENT)
        try {
            $columnCheck = $db->query("SHOW COLUMNS FROM attendance_monthly_grid LIKE 'attendance_grid_id'");
            $columnInfo = $columnCheck->fetch(PDO::FETCH_ASSOC);
            if ($columnInfo && stripos($columnInfo['Extra'] ?? '', 'auto_increment') === false) {
                // Fix the table - attendance_grid_id should be AUTO_INCREMENT
                error_log("Attendance save: Fixing attendance_grid_id column - adding AUTO_INCREMENT");
                
                // Try multiple methods to fix
                $fixed = false;
                
                // Method 1: Direct modify
                try {
                    $db->exec("ALTER TABLE `attendance_monthly_grid` MODIFY COLUMN `attendance_grid_id` INT(11) NOT NULL AUTO_INCREMENT");
                    $fixed = true;
                    error_log("Attendance save: Table fixed with Method 1");
                } catch (Exception $m1) {
                    error_log("Attendance save: Method 1 failed: " . $m1->getMessage());
                }
                
                // Method 2: Change column
                if (!$fixed) {
                    try {
                        $db->exec("ALTER TABLE `attendance_monthly_grid` CHANGE `attendance_grid_id` `attendance_grid_id` INT(11) NOT NULL AUTO_INCREMENT FIRST");
                        $fixed = true;
                        error_log("Attendance save: Table fixed with Method 2");
                    } catch (Exception $m2) {
                        error_log("Attendance save: Method 2 failed: " . $m2->getMessage());
                    }
                }
                
                // Method 3: Drop and recreate primary key
                if (!$fixed) {
                    try {
                        $db->exec("ALTER TABLE `attendance_monthly_grid` DROP PRIMARY KEY, ADD PRIMARY KEY (`attendance_grid_id`)");
                        $db->exec("ALTER TABLE `attendance_monthly_grid` MODIFY COLUMN `attendance_grid_id` INT(11) NOT NULL AUTO_INCREMENT");
                        $fixed = true;
                        error_log("Attendance save: Table fixed with Method 3");
                    } catch (Exception $m3) {
                        error_log("Attendance save: Method 3 failed: " . $m3->getMessage());
                    }
                }
                
                if (!$fixed) {
                    throw new Exception("Database table 'attendance_monthly_grid' needs to be fixed. Please run fix_attendance_now.php or contact administrator.");
                }
            }
        } catch (Exception $tableFixEx) {
            error_log("Attendance save: Table check/fix error: " . $tableFixEx->getMessage());
            throw new Exception("Database table issue: " . $tableFixEx->getMessage() . ". Please run fix_attendance_now.php first.");
        }
        
        // DEBUG: Log all POST data structure
        error_log("=== ATTENDANCE SAVE DEBUG START ===");
        error_log("POST keys: " . implode(", ", array_keys($_POST)));
        
        // Validate that attendance data exists
        if (empty($_POST['attendance']) || !is_array($_POST['attendance'])) {
            error_log("Attendance save: No attendance data received. POST keys: " . implode(", ", array_keys($_POST)));
            throw new Exception("No attendance data received. Please check the form and try again.");
        }
        
        // DEBUG: Log the structure
        error_log("Attendance array has " . count($_POST['attendance']) . " employees");
        foreach ($_POST['attendance'] as $key => $value) {
            $dayCount = is_array($value) ? count($value) : 0;
            error_log("  - Key: '$key' (type: " . gettype($key) . "), Days: $dayCount");
        }
        
        // Use POST hidden field value if available, otherwise use GET parameter
        $saveMonth = isset($_POST['selected_month']) ? $_POST['selected_month'] : $selectedMonth;
        if (empty($saveMonth)) {
            throw new Exception("Month information is missing. Please refresh the page and try again.");
        }
        
        // Log for debugging - Dump the entire POST structure
        error_log("Attendance save: Processing " . count($_POST['attendance']) . " employees for month: $saveMonth");
        error_log("Attendance save: POST attendance keys: " . implode(", ", array_keys($_POST['attendance'])));
        
        // Debug: Log the structure of POST data
        foreach ($_POST['attendance'] as $empId => $empData) {
            $dayCount = is_array($empData) ? count($empData) : 0;
            error_log("Attendance save: Employee ID in POST: $empId, Days data count: $dayCount");
            if (is_array($empData)) {
                $dayKeys = array_keys($empData);
                error_log("Attendance save: Employee $empId day keys: " . implode(", ", array_slice($dayKeys, 0, 5)) . "...");
            }
        }
        
        $db->beginTransaction();
        
        $savedCount = 0;
        $errorDetails = [];
        $processedEmployees = [];
        
        // Process employees in the order they appear in POST to maintain correct mapping
        // CRITICAL: Use a separate scope for each employee to prevent variable contamination
        // Each iteration processes ONE employee completely before moving to the next
        $employeeProcessingOrder = []; // Track processing order for debugging
        
        foreach ($_POST['attendance'] as $employeeIdKey => $days) {
            // CRITICAL: Start fresh for each employee - clear ALL variables to prevent contamination
            unset($lockedEmployeeId, $lockedGridId, $lockedInsertEmployeeId, $verifyEmployeeId, $insertEmployeeId, 
                  $currentGridId, $freshRecord, $updateParams, $finalParams, $existingRecord);
            
            // Track which employee we're processing
            $employeeProcessingOrder[] = (int)$employeeIdKey;
            // CRITICAL: Preserve the exact employee ID from form field name in a local scope
            // The key should be the employee_id from the form: attendance[employee_id][day_X]
            $currentEmployeeIdKey = (string)$employeeIdKey;
            $currentEmployeeId = (int)$employeeIdKey;
            
            // Log the original key to debug
            error_log("Attendance save: Processing POST key: '$currentEmployeeIdKey' -> employee_id: $currentEmployeeId");
            
            if ($currentEmployeeId <= 0) {
                $errorDetails[] = "Invalid employee ID: $currentEmployeeIdKey";
                error_log("Attendance save: Invalid employee ID received: '$currentEmployeeIdKey' (converted to: $currentEmployeeId)");
                continue; // Skip invalid employee IDs
            }
            
            // Ensure $days is an array
            if (!is_array($days)) {
                $errorDetails[] = "Invalid data format for employee ID: $currentEmployeeId";
                error_log("Attendance save: Invalid data format for employee ID: $currentEmployeeId. Data type: " . gettype($days));
                continue;
            }
            
            $processedEmployees[] = $currentEmployeeId;
            error_log("Attendance save: Processing employee ID: $currentEmployeeId (from key: '$currentEmployeeIdKey')");
            
            // Log sample of data received for this employee
            $sampleDays = array_slice($days, 0, 5, true);
            error_log("Attendance save: Employee $currentEmployeeId sample data: " . json_encode($sampleDays));
            
            // Use local variable to prevent contamination
            $employeeId = $currentEmployeeId; // Use this throughout the rest of the loop
            
            // Calculate summary counts
            $counts = [
                'P' => 0, 'H' => 0, 'CL' => 0, 'PL' => 0, 'SL' => 0, 'A' => 0, 'WO' => 0
            ];
            
            for ($day = 1; $day <= 31; $day++) {
                $status = isset($days["day_$day"]) ? trim($days["day_$day"]) : '';
                // Only count non-empty, valid status codes
                if (!empty($status) && $status !== '-' && isset($counts[$status])) {
                    $counts[$status]++;
                }
            }
            
            // Check if record exists - Use the correct employee ID
            // CRITICAL: Use the current employee ID from the loop to ensure correct mapping
            $lookupEmployeeId = $currentEmployeeId; // Use the employee ID we're currently processing
            error_log("Attendance save: Looking up record for employee_id: $lookupEmployeeId, month: $saveMonth, site_id: $siteId");
            
            $checkStmt = $db->prepare("
                SELECT attendance_grid_id, employee_id FROM attendance_monthly_grid 
                WHERE employee_id = ? AND attendance_month = ? AND site_id = ?
            ");
            $checkStmt->execute([$lookupEmployeeId, $saveMonth, $siteId]);
            $existingRecord = $checkStmt->fetch();
            
            // Log for debugging and verify employee_id matches
            if ($existingRecord) {
                $foundEmployeeId = $existingRecord['employee_id'] ?? 'N/A';
                if ($foundEmployeeId != $lookupEmployeeId) {
                    error_log("Attendance save: CRITICAL ERROR - Found record with wrong employee_id! Expected: $lookupEmployeeId, Found: $foundEmployeeId");
                    $errorDetails[] = "Data integrity error: Wrong employee ID in database record for grid_id: " . $existingRecord['attendance_grid_id'];
                    continue; // Skip this to prevent wrong update
                }
                error_log("Attendance save: Found existing record for employee $lookupEmployeeId, grid_id: " . $existingRecord['attendance_grid_id']);
            } else {
                error_log("Attendance save: No existing record for employee $lookupEmployeeId, will insert new");
            }
            
            if ($existingRecord) {
                // CRITICAL: Re-fetch the record right before update to ensure we have the latest data
                // This prevents using stale data from previous iterations
                $recheckStmt = $db->prepare("
                    SELECT attendance_grid_id, employee_id 
                    FROM attendance_monthly_grid 
                    WHERE employee_id = ? AND attendance_month = ? AND site_id = ?
                ");
                $recheckStmt->execute([$lookupEmployeeId, $saveMonth, $siteId]);
                $freshRecord = $recheckStmt->fetch();
                
                if (!$freshRecord || $freshRecord['employee_id'] != $lookupEmployeeId) {
                    error_log("Attendance save: CRITICAL - Re-check failed for employee $lookupEmployeeId. Will try to insert new record.");
                    $existingRecord = false; // Mark as non-existent to trigger insert
                } else {
                    // Use the fresh record's grid_id
                    $currentGridId = (int)($freshRecord['attendance_grid_id'] ?? 0);
                    
                    // CRITICAL: Check if grid_id is valid (must be > 0)
                    if ($currentGridId <= 0) {
                        error_log("Attendance save: CRITICAL ERROR - Invalid grid_id ($currentGridId) for employee $lookupEmployeeId. Record may be corrupted. Will try to insert new record.");
                        $existingRecord = false; // Mark as non-existent to trigger insert
                    } else {
                        error_log("Attendance save: Using fresh grid_id: $currentGridId for employee: $lookupEmployeeId");
                        
                        // CRITICAL: Verify that the grid_id actually belongs to this employee
                        // This prevents using a grid_id that belongs to a different employee
                        $gridIdVerifyStmt = $db->prepare("
                            SELECT employee_id 
                            FROM attendance_monthly_grid 
                            WHERE attendance_grid_id = ? AND site_id = ?
                        ");
                        $gridIdVerifyStmt->execute([$currentGridId, $siteId]);
                        $gridIdOwner = $gridIdVerifyStmt->fetch();
                        
                        if (!$gridIdOwner || $gridIdOwner['employee_id'] != $lookupEmployeeId) {
                            $actualOwner = $gridIdOwner['employee_id'] ?? 'unknown';
                            error_log("Attendance save: CRITICAL ERROR - Grid ID $currentGridId belongs to employee $actualOwner, not $lookupEmployeeId! Will try to insert new record.");
                            $existingRecord = false; // Mark as non-existent to trigger insert
                        }
                    }
                }
            }
            
            // Update existing record only if we have a valid record with valid grid_id
            if ($existingRecord && isset($currentGridId) && $currentGridId > 0) {
                // Update existing record
                $updateSql = "UPDATE attendance_monthly_grid SET ";
                $updateParams = []; // Use different variable name to avoid confusion
                $updateFields = [];
                
                for ($day = 1; $day <= 31; $day++) {
                    $updateFields[] = "day_$day = ?";
                    $status = isset($days["day_$day"]) ? trim($days["day_$day"]) : '';
                    // Convert empty string or '-' to NULL for database
                    $updateParams[] = (!empty($status) && $status !== '-' && $status !== '') ? $status : null;
                }
                
                $updateSql .= implode(", ", $updateFields) . ", ";
                $updateSql .= "
                    present_count = ?, half_day_count = ?, cl_count = ?, 
                    pl_count = ?, sl_count = ?, absent_count = ?, wo_count = ?,
                    remarks = ?, updated_at = NOW()
                    WHERE attendance_grid_id = ? AND employee_id = ?
                ";
                
                // Build params array - ensure employee ID is preserved correctly
                // CRITICAL: Use the fresh grid_id and locked employee ID
                // Build in a way that ensures no contamination
                $summaryParams = [
                    $counts['P'], $counts['H'], $counts['CL'], 
                    $counts['PL'], $counts['SL'], $counts['A'], $counts['WO'],
                    isset($_POST['remarks'][$lookupEmployeeId]) ? trim($_POST['remarks'][$lookupEmployeeId]) : '',
                    $currentGridId, // Grid ID for WHERE clause
                    $lookupEmployeeId // Employee ID for WHERE clause - EXTRA SAFETY
                ];
                
                // Combine all params - grid_id and employee_id should be at the end
                $finalParams = array_merge($updateParams, $summaryParams);
                
                // CRITICAL: Verify the last two parameters (grid_id and employee_id) for THIS employee
                $lastParamIndex = count($finalParams) - 1;
                $secondLastParamIndex = count($finalParams) - 2;
                
                // Verify grid_id (second to last)
                $finalGridId = $finalParams[$secondLastParamIndex];
                if ($finalGridId != $currentGridId) {
                    error_log("Attendance save: CRITICAL - Grid ID mismatch for employee $lookupEmployeeId! Expected: $currentGridId, Got: $finalGridId");
                    // Force fix it - this is the employee we're currently processing
                    $finalParams[$secondLastParamIndex] = $currentGridId;
                    error_log("Attendance save: Fixed grid_id parameter for employee $lookupEmployeeId");
                }
                
                // Verify employee_id (last)
                $finalEmployeeId = $finalParams[$lastParamIndex];
                if ($finalEmployeeId != $lookupEmployeeId) {
                    error_log("Attendance save: CRITICAL - Employee ID mismatch in WHERE clause! Expected: $lookupEmployeeId, Got: $finalEmployeeId");
                    // Force fix it
                    $finalParams[$lastParamIndex] = $lookupEmployeeId;
                    error_log("Attendance save: Fixed employee_id parameter");
                }
                
                // CRITICAL: Verify grid_id belongs to this employee one more time
                $finalVerify = $db->prepare("
                    SELECT employee_id FROM attendance_monthly_grid 
                    WHERE attendance_grid_id = ? AND employee_id = ?
                ");
                $finalVerify->execute([$currentGridId, $lookupEmployeeId]);
                $finalCheck = $finalVerify->fetch();
                
                if (!$finalCheck || $finalCheck['employee_id'] != $lookupEmployeeId) {
                    error_log("Attendance save: CRITICAL ERROR - Final verification failed! Grid ID $currentGridId does not belong to employee $lookupEmployeeId");
                    $errorDetails[] = "Final verification failed for employee: $lookupEmployeeId";
                    continue; // Skip this employee - DO NOT UPDATE
                }
                
                $params = $finalParams;
                error_log("Attendance save: All verifications passed for employee $lookupEmployeeId, grid_id: $currentGridId");
                
                try {
                    // CRITICAL: Lock the employee ID and grid_id for this specific update operation
                    // Use the lookup employee ID - this is the employee we're currently processing
                    $lockedEmployeeId = (int)$lookupEmployeeId; // Lock it as integer - THIS EMPLOYEE ONLY
                    $lockedGridId = (int)$currentGridId; // Use the fresh grid_id we just fetched - THIS GRID_ID ONLY
                    
                    // CRITICAL: One final check - ensure grid_id and employee_id match
                    error_log("Attendance save: Locked values - Employee: $lockedEmployeeId, Grid ID: $lockedGridId");
                    
                    // Triple-verify: Check that the grid_id actually belongs to this specific employee
                    $verifyStmt = $db->prepare("
                        SELECT employee_id, attendance_grid_id 
                        FROM attendance_monthly_grid 
                        WHERE attendance_grid_id = ? AND employee_id = ? AND site_id = ?
                    ");
                    $verifyStmt->execute([$lockedGridId, $lockedEmployeeId, $siteId]);
                    $verification = $verifyStmt->fetch();
                    
                    if (!$verification) {
                        error_log("Attendance save: CRITICAL ERROR - Grid ID $lockedGridId does not belong to employee $lockedEmployeeId!");
                        $errorDetails[] = "Data integrity error: Grid ID $lockedGridId does not match employee $lockedEmployeeId";
                        continue; // Skip this employee to prevent wrong data update
                    }
                    
                    if ($verification['employee_id'] != $lockedEmployeeId) {
                        error_log("Attendance save: CRITICAL ERROR - Employee ID mismatch! Expected: $lockedEmployeeId, Found: " . $verification['employee_id']);
                        $errorDetails[] = "Data integrity error: Employee ID mismatch for grid_id $lockedGridId";
                        continue; // Skip this employee
                    }
                    
                    error_log("Attendance save: Verified - Updating employee ID: $lockedEmployeeId with grid_id: $lockedGridId");
                    
                    // CRITICAL: Verify both grid_id and employee_id in WHERE clause params
                    // This prevents cross-contamination between employees
                    $lastParamIndex = count($params) - 1;
                    $secondLastParamIndex = count($params) - 2;
                    
                    // Verify grid_id (second to last parameter)
                    if ($params[$secondLastParamIndex] != $lockedGridId) {
                        error_log("Attendance save: WARNING - Grid ID parameter mismatch! Expected: $lockedGridId, Got: " . $params[$secondLastParamIndex]);
                        // Force fix it
                        $params[$secondLastParamIndex] = $lockedGridId;
                    }
                    
                    // Verify employee_id (last parameter)
                    if ($params[$lastParamIndex] != $lockedEmployeeId) {
                        error_log("Attendance save: WARNING - Employee ID parameter mismatch! Expected: $lockedEmployeeId, Got: " . $params[$lastParamIndex]);
                        // Force fix it
                        $params[$lastParamIndex] = $lockedEmployeeId;
                    }
                    
                    // Final verification: Log what we're about to update
                    error_log("Attendance save: Final update - Employee: $lockedEmployeeId, Grid ID: $lockedGridId, Params count: " . count($params));
                    error_log("Attendance save: WHERE clause params - Grid ID: " . $params[$secondLastParamIndex] . ", Employee ID: " . $params[$lastParamIndex]);
                    
                    // Execute update with locked parameters
                    $updateStmt = $db->prepare($updateSql);
                    $result = $updateStmt->execute($params);
                    
                    // CRITICAL: Immediately verify which record was actually updated
                    $verifyUpdated = $db->prepare("
                        SELECT employee_id, attendance_grid_id 
                        FROM attendance_monthly_grid 
                        WHERE attendance_grid_id = ?
                    ");
                    $verifyUpdated->execute([$lockedGridId]);
                    $updatedRecord = $verifyUpdated->fetch();
                    
                    if ($updatedRecord && $updatedRecord['employee_id'] != $lockedEmployeeId) {
                        error_log("Attendance save: CRITICAL ERROR - Wrong employee updated! Expected: $lockedEmployeeId, Found: " . $updatedRecord['employee_id']);
                        $errorDetails[] = "CRITICAL: Wrong employee record was updated! Expected employee: $lockedEmployeeId, but grid_id $lockedGridId belongs to employee: " . $updatedRecord['employee_id'];
                        // Rollback this specific update if possible
                        continue;
                    }
                    
                    // Check if update was successful
                    if ($result !== false) {
                        $rowsAffected = $updateStmt->rowCount();
                        
                        // Verify the update actually affected the correct record
                        $postUpdateCheck = $db->prepare("
                            SELECT employee_id FROM attendance_monthly_grid 
                            WHERE attendance_grid_id = ? AND employee_id = ?
                        ");
                        $postUpdateCheck->execute([$lockedGridId, $lockedEmployeeId]);
                        $postUpdate = $postUpdateCheck->fetch();
                        
                        if ($postUpdate && $postUpdate['employee_id'] == $lockedEmployeeId) {
                            error_log("Attendance save: Successfully updated employee $lockedEmployeeId (grid_id: $lockedGridId). Rows affected: $rowsAffected");
                            $savedCount++;
                        } else {
                            error_log("Attendance save: WARNING - Update completed but verification failed for employee $lockedEmployeeId");
                            $errorDetails[] = "Update verification failed for employee ID: $lockedEmployeeId";
                        }
                    } else {
                        $errorInfo = $updateStmt->errorInfo();
                        $errorDetails[] = "Failed to update employee ID: $lockedEmployeeId - " . ($errorInfo[2] ?? 'Unknown error');
                        error_log("Update failed for employee $lockedEmployeeId. Error: " . print_r($errorInfo, true));
                        error_log("SQL: " . $updateSql);
                        error_log("Params count: " . count($params) . ", Last param (grid_id): " . $params[count($params) - 1]);
                    }
                } catch (Exception $e) {
                    $errorDetails[] = "Exception updating employee ID: $lockedEmployeeId - " . $e->getMessage();
                    error_log("Exception updating employee $lockedEmployeeId: " . $e->getMessage());
                    error_log("SQL: " . $updateSql);
                }
            } else {
                // Insert new record
                // First, check if there's a corrupted record (with invalid grid_id) and delete it
                // This can happen if a record exists but has grid_id = 0 or belongs to wrong employee
                $checkCorruptedStmt = $db->prepare("
                    SELECT attendance_grid_id 
                    FROM attendance_monthly_grid 
                    WHERE employee_id = ? AND attendance_month = ? AND site_id = ?
                ");
                $checkCorruptedStmt->execute([$lookupEmployeeId, $saveMonth, $siteId]);
                $corruptedRecord = $checkCorruptedStmt->fetch();
                
                if ($corruptedRecord) {
                    $corruptedGridId = (int)($corruptedRecord['attendance_grid_id'] ?? 0);
                    if ($corruptedGridId <= 0) {
                        // Delete corrupted record with invalid grid_id
                        $deleteStmt = $db->prepare("
                            DELETE FROM attendance_monthly_grid 
                            WHERE employee_id = ? AND attendance_month = ? AND site_id = ?
                        ");
                        $deleteStmt->execute([$lookupEmployeeId, $saveMonth, $siteId]);
                        error_log("Attendance save: Deleted corrupted record (invalid grid_id) for employee $lookupEmployeeId before inserting new record.");
                    }
                }
                
                $insertSql = "INSERT INTO attendance_monthly_grid (
                    site_id, employee_id, attendance_month, ";
                
                for ($day = 1; $day <= 31; $day++) {
                    $insertSql .= "day_$day, ";
                }
                
                $insertSql .= "present_count, half_day_count, cl_count, pl_count, 
                               sl_count, absent_count, wo_count, remarks, created_at, updated_at
                ) VALUES (?, ?, ?, ";
                
                // CRITICAL: Lock the employee ID for insert - use lookup employee ID
                $lockedInsertEmployeeId = (int)$lookupEmployeeId; // Lock it as integer
                error_log("Attendance save: Preparing insert for employee ID: $lockedInsertEmployeeId");
                $params = [$siteId, $lockedInsertEmployeeId, $saveMonth];
                
                for ($day = 1; $day <= 31; $day++) {
                    $insertSql .= "?, ";
                    $status = isset($days["day_$day"]) ? trim($days["day_$day"]) : '';
                    // Convert empty string or '-' to NULL for database
                    $params[] = (!empty($status) && $status !== '-' && $status !== '') ? $status : null;
                }
                
                $insertSql .= "?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())";
                
                $params = array_merge($params, [
                    $counts['P'], $counts['H'], $counts['CL'], 
                    $counts['PL'], $counts['SL'], $counts['A'], $counts['WO'],
                    isset($_POST['remarks'][$lookupEmployeeId]) ? trim($_POST['remarks'][$lookupEmployeeId]) : ''
                ]);
                
                try {
                    // CRITICAL: Verify the employee_id in params before insert (should be at index 1)
                    if (!isset($params[1]) || $params[1] != $lockedInsertEmployeeId) {
                        error_log("Attendance save: CRITICAL - Employee ID mismatch in insert params! Expected: $lockedInsertEmployeeId, Got: " . ($params[1] ?? 'NOT SET'));
                        // Force fix it
                        $params[1] = $lockedInsertEmployeeId;
                    }
                    
                    // Final verification before insert
                    error_log("Attendance save: Inserting - Employee: $lockedInsertEmployeeId, Month: $saveMonth, Site: $siteId");
                    error_log("Attendance save: Insert params employee_id (index 1): " . $params[1]);
                    
                    $insertStmt = $db->prepare($insertSql);
                    $result = $insertStmt->execute($params);
                    
                    // Check if insert was successful
                    if ($result !== false) {
                        $insertedId = $db->lastInsertId();
                        
                        // Verify the inserted record has the correct employee_id
                        $verifyInsert = $db->prepare("
                            SELECT employee_id FROM attendance_monthly_grid 
                            WHERE attendance_grid_id = ? AND employee_id = ?
                        ");
                        $verifyInsert->execute([$insertedId, $lockedInsertEmployeeId]);
                        $insertVerification = $verifyInsert->fetch();
                        
                        if ($insertVerification && $insertVerification['employee_id'] == $lockedInsertEmployeeId) {
                            error_log("Attendance save: Successfully inserted employee $lockedInsertEmployeeId. New grid_id: $insertedId");
                            $savedCount++;
                        } else {
                            error_log("Attendance save: WARNING - Insert completed but verification failed for employee $lockedInsertEmployeeId");
                            $errorDetails[] = "Insert verification failed for employee ID: $lockedInsertEmployeeId";
                        }
                    } else {
                        $errorInfo = $insertStmt->errorInfo();
                        $errorDetails[] = "Failed to insert employee ID: $lockedInsertEmployeeId - " . ($errorInfo[2] ?? 'Unknown error');
                        error_log("Insert failed for employee $lockedInsertEmployeeId. Error: " . print_r($errorInfo, true));
                        error_log("SQL: " . $insertSql);
                        error_log("Params employee_id (index 1): " . ($params[1] ?? 'NOT SET'));
                    }
                } catch (Exception $e) {
                    $errorDetails[] = "Exception inserting employee ID: $lockedInsertEmployeeId - " . $e->getMessage();
                    error_log("Exception inserting employee $lockedInsertEmployeeId: " . $e->getMessage());
                    error_log("SQL: " . $insertSql);
                }
            }
        }
        
        // Log processing results for debugging
        error_log("=== ATTENDANCE SAVE DEBUG END ===");
        error_log("Attendance save: Processed " . count($processedEmployees) . " employees, saved: $savedCount");
        error_log("Processed employee IDs: " . implode(", ", $processedEmployees));
        
        if ($savedCount === 0) {
            if ($db->inTransaction()) {
                $db->rollBack();
            }
            $errorMsg = "No valid attendance records were saved. ";
            $errorMsg .= "Processed " . count($processedEmployees) . " employee(s) but none were saved. ";
            if (!empty($errorDetails)) {
                $errorMsg .= "Errors: " . implode("; ", array_slice($errorDetails, 0, 5));
            } else {
                $errorMsg .= "Please check if the data was actually changed or if there were database errors.";
            }
            throw new Exception($errorMsg);
        }
        
        // Commit transaction only if at least one record was saved
        if ($db->inTransaction()) {
            $db->commit();
        }
        
        // Clear any cached data and refresh
        $successMessage = "Attendance saved successfully for " . $selectedMonthDisplay . "! (" . $savedCount . " employee(s) updated)";
        
        // Show warnings if there were some errors but some records were saved
        if (!empty($errorDetails) && $savedCount > 0) {
            $successMessage .= " (Note: Some records had issues: " . implode("; ", array_slice($errorDetails, 0, 3)) . ")";
        }
        
        // Redirect to maintain URL parameters after save and force refresh with sync
        header("Location: ?m=" . urlencode($selectedMonthValue) . "&y=" . urlencode($selectedYearValue) . "&saved=1&sync=1&t=" . time());
        exit;
    } catch (Exception $e) {
        if ($db->inTransaction()) {
            $db->rollBack();
        }
        error_log("Error saving attendance: " . $e->getMessage());
        $errorMessage = "Error saving attendance: " . $e->getMessage();
    }
}

// Check for saved parameter
if (isset($_GET['saved']) && $_GET['saved'] === '1') {
    $successMessage = "Attendance saved successfully for " . $selectedMonthDisplay . "!";
}

// Handle export to Excel (XLSX)
if (isset($_GET['export']) && $_GET['export'] === 'excel') {
    $exportFilename = str_replace(' ', '_', $selectedMonthDisplay);
    
    // Check if PhpSpreadsheet is available
    $phpspreadsheet_available = class_exists('\PhpOffice\PhpSpreadsheet\Spreadsheet');
    
    if ($phpspreadsheet_available) {
        // Use PhpSpreadsheet for proper Excel format
        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Attendance');
        
        // Get "To" name for this site
        $exportToName = '';
        try {
            $toNameStmt = $db->prepare("SELECT to_name FROM attendance_to_names WHERE site_id = ?");
            $toNameStmt->execute([$siteId]);
            $toNameRecord = $toNameStmt->fetch();
            $exportToName = $toNameRecord ? $toNameRecord['to_name'] : '';
        } catch (Exception $e) {
            // Table might not exist yet
        }
        
        // Add "To" name row at top if exists
        $startRow = 1;
        if (!empty($exportToName)) {
            $sheet->setCellValue('A1', 'To:');
            $sheet->setCellValue('B1', $exportToName);
            $sheet->getStyle('A1')->getFont()->setBold(true);
            $sheet->getStyle('B1')->getFont()->setBold(true);
            $sheet->mergeCells('B1:F1');
            $startRow = 3; // Leave a blank row
        }
        
        // Set header row
        $headers = ['S/L No', 'Name', 'Designation', 'Employee Type', 'Category', 'Status'];
        for ($i = 1; $i <= 31; $i++) {
            $headers[] = $i;
        }
        $headers = array_merge($headers, ['P', 'H', 'CL', 'PL', 'SL', 'A', 'WO', 'Remarks']);
        
        // Style for header row (Dark blue background, white text)
        $headerStyle = [
            'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF'], 'size' => 11],
            'fill' => ['fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID, 'startColor' => ['rgb' => '003366']],
            'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER, 'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER],
            'borders' => ['allBorders' => ['borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN]]
        ];
        
        // Write headers
        $col = 'A';
        foreach ($headers as $header) {
            $sheet->setCellValue($col . $startRow, $header);
            $sheet->getStyle($col . $startRow)->applyFromArray($headerStyle);
            $col++;
        }
        
        // Get employees and their attendance
        $employeesStmt = $db->prepare("
            SELECT DISTINCT
                e.employee_id,
                e.employee_code,
                CONCAT(e.first_name, ' ', IFNULL(e.middle_name, ''), ' ', e.last_name) as full_name,
                d.designation_name,
                e.employment_status,
                e.employment_type,
                ems.category
            FROM employees e
            LEFT JOIN designations d ON e.designation_id = d.designation_id
            LEFT JOIN employee_master_sheet ems ON e.employee_id = ems.employee_id
            WHERE e.site_id = ? AND e.employment_status = 'active'
            ORDER BY e.employee_code
        ");
        $employeesStmt->execute([$siteId]);
        $employees = $employeesStmt->fetchAll();
        
        $row = $startRow + 1; // Start data rows after header
        $slNo = 1;
        
        // Color mapping for attendance status
        $statusColors = [
            'P' => '28a745',  // Green
            'A' => 'dc3545',  // Red
            'H' => 'ffc107',  // Yellow/Orange
            'CL' => '17a2b8', // Teal
            'PL' => '6f42c1', // Purple
            'SL' => 'ffc107', // Yellow
            'WO' => '6c757d', // Grey
            '' => 'FFFFFF'    // White
        ];
        
        foreach ($employees as $emp) {
            $attStmt = $db->prepare("
                SELECT * FROM attendance_monthly_grid 
                WHERE employee_id = ? AND attendance_month = ? AND site_id = ?
            ");
            $attStmt->execute([$emp['employee_id'], $selectedMonth, $siteId]);
            $attendance = $attStmt->fetch();
            
            $col = 'A';
            
            // Employee Type mapping
            $empType = $emp['employment_type'] ?? 'full_time';
            $typeMap = [
                'full_time' => 'Regular',
                'part_time' => 'Part Time',
                'contract' => 'Contract',
                'temporary' => 'Temporary'
            ];
            $employeeType = $typeMap[$empType] ?? 'Regular';
            
            // Write employee data
            $sheet->setCellValue($col++, $slNo++);
            $sheet->setCellValue($col++, $emp['full_name']);
            $sheet->setCellValue($col++, $emp['designation_name'] ?? '');
            $sheet->setCellValue($col++, $employeeType);
            $sheet->setCellValue($col++, $emp['category'] ?? 'Highly Skilled');
            $sheet->setCellValue($col++, ucfirst($emp['employment_status']));
            
            // Write daily attendance with colors
            for ($day = 1; $day <= 31; $day++) {
                $dayValue = $attendance["day_$day"] ?? '';
                $sheet->setCellValue($col, $dayValue);
                
                // Apply color based on status
                $color = $statusColors[$dayValue] ?? 'FFFFFF';
                $sheet->getStyle($col . $row)->applyFromArray([
                    'fill' => ['fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID, 'startColor' => ['rgb' => $color]],
                    'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER],
                    'borders' => ['allBorders' => ['borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN]]
                ]);
                
                $col++;
            }
            
            // Summary columns
            $sheet->setCellValue($col++, $attendance['present_count'] ?? 0);
            $sheet->setCellValue($col++, $attendance['half_day_count'] ?? 0);
            $sheet->setCellValue($col++, $attendance['cl_count'] ?? 0);
            $sheet->setCellValue($col++, $attendance['pl_count'] ?? 0);
            $sheet->setCellValue($col++, $attendance['sl_count'] ?? 0);
            $sheet->setCellValue($col++, $attendance['absent_count'] ?? 0);
            $sheet->setCellValue($col++, $attendance['wo_count'] ?? 0);
            $sheet->setCellValue($col++, $attendance['remarks'] ?? '');
            
            // Apply borders to entire row
            $sheet->getStyle('A' . $row . ':' . $col . $row)->applyFromArray([
                'borders' => ['allBorders' => ['borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN]]
            ]);
            
            $row++;
        }
        
        // Auto-size columns
        foreach (range('A', $col) as $columnID) {
            $sheet->getColumnDimension($columnID)->setAutoSize(true);
        }
        
        // Set headers and output
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment; filename="Attendance_' . $exportFilename . '.xlsx"');
        header('Cache-Control: max-age=0');
        
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        $writer->save('php://output');
        exit;
    } else {
        // Fallback to HTML/Excel format
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="Attendance_' . $exportFilename . '.xls"');
        header('Cache-Control: max-age=0');
        
        // Get "To" name for this site
        $exportToName = '';
        try {
            $toNameStmt = $db->prepare("SELECT to_name FROM attendance_to_names WHERE site_id = ?");
            $toNameStmt->execute([$siteId]);
            $toNameRecord = $toNameStmt->fetch();
            $exportToName = $toNameRecord ? $toNameRecord['to_name'] : '';
        } catch (Exception $e) {
            // Table might not exist yet
        }
        
        echo '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40">';
        echo '<head><meta http-equiv="Content-Type" content="text/html; charset=utf-8">';
        echo '<style>
            table { border-collapse: collapse; width: 100%; font-family: Arial, sans-serif; font-size: 10pt; }
            th { background-color: #003366; color: white; font-weight: bold; padding: 8px; text-align: center; border: 1px solid #000; }
            td { padding: 5px; border: 1px solid #ccc; text-align: center; }
            .status-P { background-color: #28a745 !important; color: white !important; font-weight: bold; }
            .status-A { background-color: #dc3545 !important; color: white !important; font-weight: bold; }
            .status-H { background-color: #ffc107 !important; color: #000 !important; font-weight: bold; }
            .status-CL { background-color: #17a2b8 !important; color: white !important; font-weight: bold; }
            .status-PL { background-color: #6f42c1 !important; color: white !important; font-weight: bold; }
            .status-SL { background-color: #ffc107 !important; color: #000 !important; font-weight: bold; }
            .status-WO { background-color: #6c757d !important; color: white !important; font-weight: bold; }
            .status-empty { background-color: #ffffff !important; }
            .summary-cell { background-color: #E8F5E9 !important; font-weight: bold; }
            .to-name { font-weight: bold; font-size: 12pt; padding: 10px; border: none; }
        </style></head><body>';
        
        // Add "To" name row at top if exists
        if (!empty($exportToName)) {
            echo '<table style="margin-bottom: 10px; border: none;">';
            echo '<tr><td class="to-name" style="border: none;"><strong>To:</strong> ' . htmlspecialchars($exportToName) . '</td></tr>';
            echo '</table>';
        }
        
        echo '<table>';
        
        // Headers
        $headers = ['S/L No', 'Name', 'Designation', 'Employee Type', 'Category', 'Status'];
        for ($i = 1; $i <= 31; $i++) {
            $headers[] = $i;
        }
        $headers = array_merge($headers, ['P', 'H', 'CL', 'PL', 'SL', 'A', 'WO', 'Remarks']);
        
        echo '<tr>';
        foreach ($headers as $header) {
            echo '<th>' . htmlspecialchars($header) . '</th>';
        }
        echo '</tr>';
        
        // Get employees
        $employeesStmt = $db->prepare("
            SELECT DISTINCT
                e.employee_id,
                e.employee_code,
                CONCAT(e.first_name, ' ', IFNULL(e.middle_name, ''), ' ', e.last_name) as full_name,
                d.designation_name,
                e.employment_status,
                e.employment_type,
                ems.category
            FROM employees e
            LEFT JOIN designations d ON e.designation_id = d.designation_id
            LEFT JOIN employee_master_sheet ems ON e.employee_id = ems.employee_id
            WHERE e.site_id = ? AND e.employment_status = 'active'
            ORDER BY e.employee_code
        ");
        $employeesStmt->execute([$siteId]);
        $employees = $employeesStmt->fetchAll();
        
        $slNo = 1;
        foreach ($employees as $emp) {
            $attStmt = $db->prepare("
                SELECT * FROM attendance_monthly_grid 
                WHERE employee_id = ? AND attendance_month = ? AND site_id = ?
            ");
            $attStmt->execute([$emp['employee_id'], $selectedMonth, $siteId]);
            $attendance = $attStmt->fetch();
            
            $empType = $emp['employment_type'] ?? 'full_time';
            $typeMap = [
                'full_time' => 'Regular',
                'part_time' => 'Part Time',
                'contract' => 'Contract',
                'temporary' => 'Temporary'
            ];
            $employeeType = $typeMap[$empType] ?? 'Regular';
            
            echo '<tr>';
            echo '<td>' . $slNo++ . '</td>';
            echo '<td>' . htmlspecialchars($emp['full_name']) . '</td>';
            echo '<td>' . htmlspecialchars($emp['designation_name'] ?? '') . '</td>';
            echo '<td>' . htmlspecialchars($employeeType) . '</td>';
            echo '<td>' . htmlspecialchars($emp['category'] ?? 'Highly Skilled') . '</td>';
            echo '<td>' . ucfirst($emp['employment_status']) . '</td>';
            
            for ($day = 1; $day <= 31; $day++) {
                $dayValue = $attendance["day_$day"] ?? '';
                $statusClass = $dayValue ? 'status-' . $dayValue : 'status-empty';
                echo '<td class="' . $statusClass . '">' . htmlspecialchars($dayValue) . '</td>';
            }
            
            echo '<td class="summary-cell">' . ($attendance['present_count'] ?? 0) . '</td>';
            echo '<td class="summary-cell">' . ($attendance['half_day_count'] ?? 0) . '</td>';
            echo '<td class="summary-cell">' . ($attendance['cl_count'] ?? 0) . '</td>';
            echo '<td class="summary-cell">' . ($attendance['pl_count'] ?? 0) . '</td>';
            echo '<td class="summary-cell">' . ($attendance['sl_count'] ?? 0) . '</td>';
            echo '<td class="summary-cell">' . ($attendance['absent_count'] ?? 0) . '</td>';
            echo '<td class="summary-cell">' . ($attendance['wo_count'] ?? 0) . '</td>';
            echo '<td>' . htmlspecialchars($attendance['remarks'] ?? '') . '</td>';
            echo '</tr>';
        }
        
        echo '</table></body></html>';
        exit;
    }
}


// Check if this period exists in attendance_periods table (official created periods only)
$periodCheckStmt = $db->prepare("
    SELECT period_id, employee_count, period_display, is_finalized
    FROM attendance_periods 
    WHERE attendance_month = ? AND site_id = ?
");
$periodCheckStmt->execute([$selectedMonth, $siteId]);
$periodInfo = $periodCheckStmt->fetch();
$periodExists = !empty($periodInfo);

// Fetch ALL active employees for the site (not just those with attendance records)
// This ensures all registered employees are shown
    $employeesStmt = $db->prepare("
        SELECT DISTINCT
            e.employee_id,
            e.employee_code,
            CONCAT(e.first_name, ' ', IFNULL(e.middle_name, ''), ' ', e.last_name) as full_name,
            d.designation_name,
            e.employment_status,
        e.employment_type,
            ems.category
        FROM employees e
        LEFT JOIN designations d ON e.designation_id = d.designation_id
        LEFT JOIN employee_master_sheet ems ON e.employee_id = ems.employee_id
        WHERE e.site_id = ? 
      AND e.employment_status = 'active'
        ORDER BY e.employee_code
    ");
$employeesStmt->execute([$siteId]);
    $employees = $employeesStmt->fetchAll();

// Auto-sync attendance records with employees table - Realtime sync
// This ensures all active employees from employees table have attendance records
if ($periodExists && !empty($employees)) {
    $db->beginTransaction();
    try {
        $syncedCount = 0;
        $newRecordsCount = 0;
        
        // CRITICAL: Ensure attendance_grid_id has AUTO_INCREMENT before any inserts
        try {
            $columnCheck = $db->query("SHOW COLUMNS FROM attendance_monthly_grid LIKE 'attendance_grid_id'");
            $columnInfo = $columnCheck->fetch(PDO::FETCH_ASSOC);
            if ($columnInfo && stripos($columnInfo['Extra'] ?? '', 'auto_increment') === false) {
                $db->exec("ALTER TABLE `attendance_monthly_grid` MODIFY COLUMN `attendance_grid_id` INT(11) NOT NULL AUTO_INCREMENT");
                error_log("Attendance sync: Fixed attendance_grid_id AUTO_INCREMENT");
            }
        } catch (Exception $tableEx) {
            // Continue - table might already be correct
        }
        
        foreach ($employees as $emp) {
            // Check if attendance record exists for this employee and month
            $checkStmt = $db->prepare("
                SELECT attendance_grid_id 
                FROM attendance_monthly_grid 
                WHERE employee_id = ? AND attendance_month = ? AND site_id = ?
            ");
            $checkStmt->execute([$emp['employee_id'], $selectedMonth, $siteId]);
            $existingRecord = $checkStmt->fetch();
            
            // If no record exists, create one - This syncs new employees automatically
            if (!$existingRecord) {
                try {
                    $insertStmt = $db->prepare("
                        INSERT INTO attendance_monthly_grid 
                        (site_id, employee_id, attendance_month, created_at, updated_at)
                        VALUES (?, ?, ?, NOW(), NOW())
                    ");
                    $insertStmt->execute([$siteId, $emp['employee_id'], $selectedMonth]);
                    $newRecordsCount++;
                    error_log("Attendance sync: Created new attendance record for employee {$emp['employee_id']} ({$emp['full_name']})");
                } catch (Exception $e) {
                    error_log("Error auto-creating attendance record for employee {$emp['employee_id']}: " . $e->getMessage());
                }
            }
            $syncedCount++;
        }
        
        // Also check for employees that might have been deactivated - remove their attendance if needed
        // (Optional: You can uncomment this if you want to remove attendance for deactivated employees)
        /*
        $attendanceEmployeesStmt = $db->prepare("
            SELECT DISTINCT amg.employee_id 
            FROM attendance_monthly_grid amg
            LEFT JOIN employees e ON amg.employee_id = e.employee_id
            WHERE amg.attendance_month = ? AND amg.site_id = ?
            AND (e.employee_id IS NULL OR e.employment_status != 'active')
        ");
        $attendanceEmployeesStmt->execute([$selectedMonth, $siteId]);
        $inactiveEmployees = $attendanceEmployeesStmt->fetchAll();
        
        foreach ($inactiveEmployees as $inactive) {
            // Optionally remove or mark attendance for inactive employees
        }
        */
        
        $db->commit();
        
        if ($newRecordsCount > 0) {
            error_log("Attendance sync: Synced $syncedCount employees, created $newRecordsCount new attendance records");
        }
    } catch (Exception $e) {
        if ($db->inTransaction()) {
            $db->rollBack();
        }
        error_log("Error during attendance sync: " . $e->getMessage());
    }
}

// Get attendance data for filtered employees - Fetch fresh data after save
$attendanceData = [];
foreach ($employees as $emp) {
    $attStmt = $db->prepare("
        SELECT * FROM attendance_monthly_grid 
        WHERE employee_id = ? AND attendance_month = ? AND site_id = ?
    ");
    $attStmt->execute([$emp['employee_id'], $selectedMonth, $siteId]);
    $attRecord = $attStmt->fetch();
    if ($attRecord) {
        // Convert NULL values to empty string for display
        for ($day = 1; $day <= 31; $day++) {
            if ($attRecord["day_$day"] === null) {
                $attRecord["day_$day"] = '';
            }
        }
        $attendanceData[$emp['employee_id']] = $attRecord;
    } else {
        $attendanceData[$emp['employee_id']] = [];
    }
}

// Generate months array (Jan to Dec)
$months = [
    'Jan' => 'January',
    'Feb' => 'February',
    'Mar' => 'March',
    'Apr' => 'April',
    'May' => 'May',
    'Jun' => 'June',
    'Jul' => 'July',
    'Aug' => 'August',
    'Sep' => 'September',
    'Oct' => 'October',
    'Nov' => 'November',
    'Dec' => 'December'
];

// Get created periods from attendance_periods table (REAL DATA ONLY)
$createdPeriodsStmt = $db->prepare("
    SELECT DISTINCT 
        attendance_month,
        period_display,
        employee_count,
        SUBSTRING(attendance_month, -2) as year_short,
        CONCAT('20', SUBSTRING(attendance_month, -2)) as year_full
    FROM attendance_periods 
    WHERE site_id = ?
    ORDER BY year_full DESC, attendance_month DESC
");
$createdPeriodsStmt->execute([$siteId]);
$createdPeriods = $createdPeriodsStmt->fetchAll();

// Extract years from created periods
$yearsFromDB = [];
foreach ($createdPeriods as $period) {
    $yearsFromDB[$period['year_short']] = $period['year_full'];
}

// Generate years array - start from 2025 and add next years
$startYear = 2025;
$currentYear = (int)date('Y');
$maxYear = max($currentYear + 2, 2027); // At least up to 2027

$years = [];
for ($y = $startYear; $y <= $maxYear; $y++) {
    $yearShort = substr($y, -2); // Get last 2 digits (25 from 2025)
    $years[$yearShort] = $y;
}

// Also include years from database if they're not in the range (for old data)
foreach ($yearsFromDB as $yearShort => $yearFull) {
    if (!isset($years[$yearShort])) {
        $years[$yearShort] = (int)$yearFull;
    }
}

// Sort years in descending order (latest first)
arsort($years);

// Get list of created period month-year combos (for highlighting)
$createdPeriodsList = array_column($createdPeriods, 'attendance_month');

// Create attendance_to_names table if not exists and fetch current To name
$currentToName = '';
try {
    $db->exec("
        CREATE TABLE IF NOT EXISTS `attendance_to_names` (
            `id` int(11) NOT NULL AUTO_INCREMENT,
            `site_id` int(11) NOT NULL,
            `to_name` varchar(255) DEFAULT NULL,
            `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
            `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (`id`),
            UNIQUE KEY `unique_site` (`site_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");
    
    $toNameStmt = $db->prepare("SELECT to_name FROM attendance_to_names WHERE site_id = ?");
    $toNameStmt->execute([$siteId]);
    $toNameRecord = $toNameStmt->fetch();
    $currentToName = $toNameRecord ? $toNameRecord['to_name'] : '';
} catch (Exception $e) {
    error_log("Error fetching attendance To Name: " . $e->getMessage());
}

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

<style>
body {
    font-family: Arial, sans-serif;
    font-size: 13px;
}

.attendance-container {
    background: white;
    padding: 20px;
    overflow-x: auto;
}

.controls-bar {
    background: #f8f9fa;
    padding: 15px;
    border-radius: 8px;
    margin-bottom: 20px;
    display: flex;
    justify-content: space-between;
    align-items: center;
    flex-wrap: wrap;
    gap: 15px;
}

.controls-left {
    display: flex;
    gap: 15px;
    align-items: center;
}

.controls-right {
    display: flex;
    gap: 10px;
}

.period-selector {
    padding: 8px 12px;
    border: 2px solid #007bff;
    border-radius: 6px;
    font-size: 13px;
    font-weight: 500;
    min-width: 140px;
    background: white;
    cursor: pointer;
    transition: all 0.3s;
}

.period-selector:hover {
    border-color: #0056b3;
    box-shadow: 0 2px 5px rgba(0,123,255,0.2);
}

.period-selector:focus {
    outline: none;
    border-color: #0056b3;
    box-shadow: 0 0 0 3px rgba(0,123,255,0.1);
}

.period-selector option {
    padding: 8px;
}

.attendance-table {
    width: 100%;
    border-collapse: collapse;
    table-layout: auto;
    min-width: 3000px;
}

/* Payroll Section Headers */
.section-header {
    background: #696969;
    color: white;
    font-weight: bold;
    text-align: center;
    padding: 10px;
    font-size: 16px;
    border: 1px solid #333;
}

.section-header-attendance {
    background: #000080;
    color: white;
    font-weight: bold;
    text-align: center;
    padding: 10px;
    font-size: 16px;
    border: 1px solid #000060;
}

/* Column Headers */
.attendance-table th {
    background: #000080;
    color: white;
    padding: 10px 6px;
    text-align: center;
    font-weight: bold;
    font-size: 12px;
    border: 1px solid #000060;
    white-space: nowrap;
    overflow: hidden;
    text-overflow: ellipsis;
}

/* Employee Info Columns */
.col-slno { width: 60px; min-width: 60px; }
.col-name { width: 160px; min-width: 160px; }
.col-designation { width: 140px; min-width: 140px; }
.col-emp-type { width: 120px; min-width: 120px; }
.col-category { width: 140px; min-width: 140px; }
.col-status { width: 80px; min-width: 80px; }

/* Day Columns */
.col-day { 
    width: 65px;
    min-width: 65px;
}

/* Summary Columns */
.col-summary { 
    width: 50px;
    min-width: 50px;
}
.col-remarks { 
    width: 300px;
    min-width: 300px;
}

/* Data Cells */
.attendance-table td {
    padding: 10px 8px;
    border: 1px dotted #ccc;
    text-align: center;
    font-size: 12px;
    white-space: nowrap;
    overflow: hidden;
    text-overflow: ellipsis;
}

.attendance-table td.text-left {
    text-align: left;
    padding-left: 10px;
    white-space: nowrap;
}

/* Dropdown styling */
.att-select {
    width: 100%;
    padding: 6px 5px;
    border: 1px solid #ddd;
    border-radius: 3px;
    font-size: 13px;
    text-align: center;
    background: white;
    font-weight: 500;
    min-height: 32px;
}

/* Color coding for attendance status */
.att-select option[value="P"] { background: #90EE90; }
.att-select option[value="WO"] { background: #FF6B6B; }
.att-select option[value="A"] { background: #FFB6C1; }
.att-select option[value="H"] { background: #FFE4B5; }
.att-select option[value="CL"],
.att-select option[value="PL"],
.att-select option[value="SL"] { background: #FFD700; }

/* Background colors for selected values */
.att-select.status-P { background: #90EE90; }
.att-select.status-WO { background: #FF6B6B; color: white; }
.att-select.status-A { background: #FFB6C1; }
.att-select.status-H { background: #FFE4B5; }
.att-select.status-CL,
.att-select.status-PL,
.att-select.status-SL { background: #FFD700; }

/* Summary cell styling */
.summary-cell {
    background: #f0f0f0;
    font-weight: bold;
}

/* Remarks input */
.remarks-input {
    width: 100%;
    padding: 8px 10px;
    border: 1px solid #ddd;
    border-radius: 3px;
    font-size: 13px;
    min-width: 280px;
}

/* Buttons */
.btn {
    padding: 10px 20px;
    border: none;
    border-radius: 6px;
    font-size: 13px;
    font-weight: 500;
    cursor: pointer;
    transition: all 0.3s;
}

.btn-success {
    background: #28a745;
    color: white;
}

.btn-primary {
    background: #007bff;
    color: white;
}

.btn-secondary {
    background: #6c757d;
    color: white;
}

.btn:hover {
    transform: translateY(-1px);
    box-shadow: 0 4px 8px rgba(0,0,0,0.15);
}

.alert {
    padding: 12px 20px;
    border-radius: 6px;
    margin-bottom: 20px;
}

.alert-success {
    background: #d4edda;
    color: #155724;
    border: 1px solid #c3e6cb;
}

.alert-danger {
    background: #f8d7da;
    color: #721c24;
    border: 1px solid #f5c6cb;
}

/* Button Styles */
.btn-success {
    background: linear-gradient(135deg, #28a745 0%, #20c997 100%);
    border: none;
}

.btn-success:hover {
    background: linear-gradient(135deg, #218838 0%, #1aa179 100%);
}

/* Modal Styles */
.modal {
    display: none;
    position: fixed;
    z-index: 9999;
    left: 0;
    top: 0;
    width: 100%;
    height: 100%;
    overflow: auto;
    background-color: rgba(0,0,0,0.5);
    align-items: center;
    justify-content: center;
}

.modal-content {
    background-color: #fefefe;
    margin: auto;
    border-radius: 12px;
    box-shadow: 0 10px 40px rgba(0,0,0,0.3);
    width: 90%;
    max-width: 600px;
    animation: slideDown 0.3s ease-out;
}

@keyframes slideDown {
    from {
        transform: translateY(-50px);
        opacity: 0;
    }
    to {
        transform: translateY(0);
        opacity: 1;
    }
}

.modal-header {
    padding: 20px 25px;
    background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
    color: white;
    border-radius: 12px 12px 0 0;
    display: flex;
    justify-content: space-between;
    align-items: center;
}

.modal-header h2 {
    margin: 0;
    font-size: 22px;
}

.close {
    color: white;
    font-size: 32px;
    font-weight: bold;
    cursor: pointer;
    transition: all 0.2s;
    line-height: 1;
}

.close:hover {
    transform: scale(1.2);
}

.modal-body {
    padding: 25px;
}

.form-group {
    margin-bottom: 15px;
}

.form-group label {
    display: block;
    margin-bottom: 8px;
    color: #333;
    font-size: 14px;
}

.form-control {
    width: 100%;
    padding: 10px 12px;
    border: 2px solid #ddd;
    border-radius: 6px;
    font-size: 14px;
    transition: all 0.3s;
}

.form-control:focus {
    outline: none;
    border-color: #007bff;
    box-shadow: 0 0 0 3px rgba(0,123,255,0.1);
}

/* Print styles */
@media print {
    .controls-bar, .btn, .modal { display: none !important; }
    body { font-size: 9px; }
    .attendance-table { min-width: auto; }
}
</style>

<div class="attendance-container">
    <?php include __DIR__ . '/../payroll/_navigation.php'; ?>
    
    <div style="display: flex; justify-content: space-between; align-items: center; margin: 20px 0;">
        <h1 style="margin: 0;">📅 Attendance - <span style="color: #007bff;"><?php echo htmlspecialchars($selectedMonthDisplay); ?></span></h1>
        <div style="background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); color: white; padding: 10px 20px; border-radius: 8px; font-weight: 600;">
            Viewing: <?php echo htmlspecialchars($selectedMonthDisplay); ?>
        </div>
    </div>
    
    <?php if (isset($successMessage)): ?>
        <div class="alert alert-success"><?php echo $successMessage; ?></div>
    <?php endif; ?>
    
    <?php if (isset($errorMessage)): ?>
        <div class="alert alert-danger"><?php echo $errorMessage; ?></div>
    <?php endif; ?>
    
    <!-- Data Filter Info -->
    <div style="background: <?php echo $periodExists ? '#f8f9fa' : '#fff3cd'; ?>; padding: 15px; border-radius: 8px; margin-bottom: 20px; border-left: 4px solid <?php echo $periodExists ? '#007bff' : '#ffc107'; ?>;">
        <div style="display: flex; justify-content: space-between; align-items: center;">
            <div>
                <strong style="color: <?php echo $periodExists ? '#007bff' : '#856404'; ?>;">
                    <?php echo $periodExists ? '📊 Viewing Period:' : '⚠️ Period Not Created:'; ?>
                </strong>
                <span style="margin-left: 10px;">Month: <strong><?php echo htmlspecialchars($months[$selectedMonthValue]); ?></strong></span>
                <span style="margin-left: 15px;">Year: <strong><?php echo htmlspecialchars($years[$selectedYearValue]); ?></strong></span>
                <span style="margin-left: 15px;">Query: <code style="background: #fff; padding: 2px 6px; border-radius: 3px;"><?php echo htmlspecialchars($selectedMonth); ?></code></span>
            </div>
            <div>
                <?php
                $totalEmployees = count($employees);
                ?>
                <span style="font-weight: 600;">
                    <?php if ($periodExists && $totalEmployees > 0): ?>
                        <span style="color: #28a745;">✓ <?php echo $totalEmployees; ?> employees with data from database</span>
                    <?php elseif ($periodExists && $totalEmployees === 0): ?>
                        <span style="color: #ffc107;">⚠️ Period created but no employees found</span>
                    <?php else: ?>
                        <span style="color: #dc3545;">❌ This period doesn't exist in database</span>
                    <?php endif; ?>
                </span>
            </div>
        </div>
        <div style="margin-top: 10px; padding-top: 10px; border-top: 1px solid #dee2e6; display: flex; justify-content: space-between;">
            <div>
                <small style="color: #6c757d;">
                    <strong>Database Status:</strong> 
                    <?php if ($periodExists): ?>
                        <span style="color: #007bff;">✓ Showing real data from attendance_periods table for <?php echo $selectedMonthDisplay; ?></span>
                    <?php else: ?>
                        <span style="color: #856404;">⚠️ This period hasn't been created yet. Click "New Attendance Period" to create it.</span>
                    <?php endif; ?>
                </small>
            </div>
            <?php if (count($createdPeriods) > 0): ?>
            <div>
                <small style="color: #6c757d;">
                    <strong>Created Periods:</strong> <?php echo count($createdPeriods); ?> total
                    <?php if (count($yearsFromDB) > 0): ?>
                        | <strong>Years:</strong> 
                        <?php 
                        $yearsList = array_map(function($y) { return $y; }, $yearsFromDB);
                        echo implode(', ', $yearsList);
                        ?>
                    <?php endif; ?>
                </small>
            </div>
            <?php endif; ?>
        </div>
    </div>
    
    <?php if (!$periodExists): ?>
        <!-- Period Not Created Warning -->
        <div style="background: #fff3cd; border: 2px solid #ffc107; border-radius: 8px; padding: 20px; margin-bottom: 20px; text-align: center;">
            <h3 style="color: #856404; margin: 0 0 10px 0;">⚠️ Attendance Period Not Created</h3>
            <p style="color: #856404; margin: 0 0 15px 0; font-size: 14px;">
                The attendance period for <strong><?php echo $selectedMonthDisplay; ?></strong> hasn't been created in the database yet.
            </p>
            <p style="color: #856404; margin: 0 0 15px 0; font-size: 14px;">
                Please click the <strong>"➕ New Attendance Period"</strong> button above to create this period first.
            </p>
            <div style="padding: 15px; background: #fff; border-radius: 6px; text-align: left; max-width: 600px; margin: 0 auto;">
                <strong style="color: #007bff;">📋 Available Periods:</strong>
                <?php if (count($createdPeriods) > 0): ?>
                    <ul style="margin: 10px 0; padding-left: 20px;">
                        <?php foreach ($createdPeriods as $cp): ?>
                            <li style="margin: 5px 0;">
                                <a href="?m=<?php echo substr($cp['attendance_month'], 0, 3); ?>&y=<?php echo substr($cp['attendance_month'], -2); ?>" 
                                   style="color: #007bff; text-decoration: none;">
                                    <?php echo $cp['period_display']; ?> (<?php echo $cp['employee_count']; ?> employees)
                                </a>
                            </li>
                        <?php endforeach; ?>
                    </ul>
                <?php else: ?>
                    <p style="margin: 10px 0; color: #6c757d; font-style: italic;">No periods created yet. Create your first period to start!</p>
                <?php endif; ?>
            </div>
        </div>
    <?php endif; ?>
    
    <div class="controls-bar">
        <div class="controls-left">
            <div style="display: flex; align-items: flex-start; gap: 10px;">
                <!-- Month Selection -->
                <div style="display: flex; flex-direction: column; min-height: 60px;">
                    <label style="font-size: 10px; color: #666; margin-bottom: 3px;"><strong>Month:</strong></label>
                    <select id="month-selector" class="period-selector" style="padding: 6px 8px; font-size: 12px; border-radius: 4px; border: 1px solid #ced4da;">
                        <?php foreach ($months as $key => $name): ?>
                            <option value="<?php echo $key; ?>" <?php echo $key === $selectedMonthValue ? 'selected' : ''; ?>>
                                <?php echo $name; ?>
                            </option>
                        <?php endforeach; ?>
                    </select>
                </div>
                
                <!-- Year Input -->
                <div style="display: flex; flex-direction: column; min-height: 60px;">
                    <label style="font-size: 10px; color: #666; margin-bottom: 3px;"><strong>Year:</strong></label>
                    <input 
                        type="text" 
                        id="year-selector" 
                        class="period-selector" 
                        value="<?php echo $selectedYearValue; ?>"
                        placeholder="e.g., 25"
                        maxlength="4"
                        style="text-align: center; font-weight: 600; width: 70px; padding: 6px 8px; font-size: 12px;"
                        title="Enter year in 2-digit (25) or 4-digit (2025) format"
                    />
                    <?php if (count($yearsFromDB) > 0): ?>
                        <small style="font-size: 9px; color: #28a745; margin-top: 3px; display: block;">
                            ✓ <?php echo implode(', ', array_map(function($y) { return substr($y, -2); }, $yearsFromDB)); ?>
                        </small>
                    <?php endif; ?>
                </div>
            
            <!-- Go Button -->
            <button onclick="loadAttendance()" class="btn btn-sm" style="margin-left: 8px; padding: 6px 14px; font-size: 11px; background: #007bff; border: none; color: white; border-radius: 4px; cursor: pointer;">
                Go →
            </button>
            
            <!-- Refresh Button -->
            <button onclick="refreshAttendanceData()" class="btn btn-sm" id="refresh-btn" style="margin-left: 6px; padding: 6px 10px; font-size: 11px; background: #17a2b8; border: none; color: white; border-radius: 4px; cursor: pointer; display: inline-flex; align-items: center; gap: 4px;">
                <span style="font-size: 12px;">🔄</span>
                <span>Sync</span>
            </button>
            
            <span style="margin-left: 10px; font-size: 10px; color: #666;">
                <?php if ($periodExists): ?>
                    <span style="color: #28a745; font-weight: 500;">✓ <?php echo count($employees); ?> emp</span>
                <?php else: ?>
                    <span style="color: #dc3545; font-weight: 500;">❌ Create first</span>
                <?php endif; ?>
            </span>
        </div>
        
        <script>
        // Refresh attendance data in real-time - Sync with employees table
        function refreshAttendanceData() {
            const month = document.getElementById('month-selector').value;
            let year = document.getElementById('year-selector').value.trim();
            
            // Validate and convert year format
            if (year === '') {
                alert('Please enter a year!');
                return;
            }
            
            year = year.replace(/\D/g, '');
            if (year.length === 4) {
                year = year.substring(2);
            } else if (year.length !== 2) {
                alert('Please enter a valid year (e.g., 25 or 2025)');
                return;
            }
            
            // Show loading indicator
            const refreshBtn = document.getElementById('refresh-btn');
            const originalText = refreshBtn.innerHTML;
            refreshBtn.innerHTML = '<span style="font-size: 14px;">⏳</span><span>Syncing...</span>';
            refreshBtn.disabled = true;
            
            // Call API to refresh data - This syncs with employees table
            fetch('<?php echo BASE_URL; ?>/api/refresh_attendance_data.php', {
                method: 'POST',
                headers: {
                    'Content-Type': 'application/json',
                },
                body: JSON.stringify({
                    month: month,
                    year: year,
                    site_id: <?php echo $siteId; ?>
                })
            })
            .then(response => response.json())
            .then(data => {
                if (data.success) {
                    // Show success message
                    const message = `Sync successful! Created: ${data.created || 0} new records, Total employees: ${data.total_employees || 0}`;
                    alert(message);
                    // Reload the page to show updated data
                    window.location.reload();
                } else {
                    alert('Error syncing data: ' + (data.message || 'Unknown error'));
                    refreshBtn.innerHTML = originalText;
                    refreshBtn.disabled = false;
                }
            })
            .catch(error => {
                console.error('Error:', error);
                alert('Error syncing data. Please try again.');
                refreshBtn.innerHTML = originalText;
                refreshBtn.disabled = false;
            });
        }
        
        // Auto-sync on page load to ensure realtime data
        document.addEventListener('DOMContentLoaded', function() {
            // Check if we need to auto-sync (e.g., if URL has sync parameter)
            const urlParams = new URLSearchParams(window.location.search);
            if (urlParams.get('auto_sync') === '1') {
                // Auto-sync in background
                setTimeout(function() {
                    refreshAttendanceData();
                }, 500);
            }
        });
        
        function loadAttendance() {
            const month = document.getElementById('month-selector').value;
            let year = document.getElementById('year-selector').value.trim();
            
            // Validate and convert year format
            if (year === '') {
                alert('Please enter a year!');
                return;
            }
            
            // Remove any non-numeric characters
            year = year.replace(/\D/g, '');
            
            // Convert 4-digit year to 2-digit (e.g., 2025 → 25)
            if (year.length === 4) {
                year = year.substring(2);
            } else if (year.length === 2) {
                // Already in correct format
            } else {
                alert('Please enter a valid year (e.g., 25 or 2025)');
                return;
            }
            
            // Validate year range (e.g., 00-99)
            const yearNum = parseInt(year);
            if (isNaN(yearNum) || yearNum < 0 || yearNum > 99) {
                alert('Please enter a valid year between 2000-2099');
                return;
            }
            
            // Update input to show 2-digit format
            document.getElementById('year-selector').value = year;
            
            window.location.href = '?m=' + month + '&y=' + year;
        }
        
        // Allow Enter key to submit
        document.getElementById('month-selector').addEventListener('keypress', function(e) {
            if (e.key === 'Enter') loadAttendance();
        });
        
        document.getElementById('year-selector').addEventListener('keypress', function(e) {
            if (e.key === 'Enter') loadAttendance();
        });
        
        // Auto-format year input on blur
        document.getElementById('year-selector').addEventListener('blur', function(e) {
            let year = e.target.value.trim().replace(/\D/g, '');
            if (year.length === 4) {
                e.target.value = year.substring(2);
            } else if (year.length === 2) {
                e.target.value = year;
            }
        });
        
        // Only allow numbers in year input
        document.getElementById('year-selector').addEventListener('input', function(e) {
            // Allow only digits
            e.target.value = e.target.value.replace(/\D/g, '');
        });
        </script>
        
        <div class="controls-right" style="display: flex; align-items: center; gap: 8px; flex-wrap: wrap;">
            <!-- To Name Input Field -->
            <form method="POST" style="display: inline-flex; align-items: center; gap: 5px; background: #f8f9fa; padding: 5px 10px; border-radius: 6px; border: 1px solid #dee2e6;">
                <label style="font-size: 11px; font-weight: 600; color: #495057; white-space: nowrap;">To:</label>
                <input type="text" name="to_name" value="<?php echo htmlspecialchars($currentToName); ?>" 
                       placeholder="Recipient name" 
                       style="padding: 5px 8px; border: 1px solid #ced4da; border-radius: 4px; font-size: 11px; width: 140px;">
                <button type="submit" name="save_to_name" 
                        style="padding: 5px 8px; font-size: 10px; background: #28a745; border: none; color: white; border-radius: 4px; cursor: pointer;" 
                        title="Save To Name">
                    ✓
                </button>
            </form>
            
            <button type="button" class="btn btn-sm" onclick="showNewPeriodModal()" style="padding: 6px 12px; font-size: 11px; background: #6c757d; border: none; color: white; border-radius: 4px; cursor: pointer;">
                ➕ New Period
            </button>
            <?php if ($periodExists): ?>
                <button type="submit" form="attendance-form" name="save_attendance" class="btn btn-sm" style="padding: 6px 12px; font-size: 11px; background: linear-gradient(135deg, #28a745 0%, #20c997 100%); border: none; color: white; border-radius: 4px; cursor: pointer; box-shadow: 0 2px 4px rgba(40,167,69,0.2);">
                    ✓ Save
                </button>
            <?php endif; ?>
            <a href="?m=<?php echo urlencode($selectedMonthValue); ?>&y=<?php echo urlencode($selectedYearValue); ?>&export=excel" class="btn btn-sm" style="padding: 6px 12px; font-size: 11px; background: #17a2b8; border: none; color: white; border-radius: 4px; text-decoration: none; display: inline-block;">
                📊 Export
            </a>
        </div>
    </div>
    
    <!-- Create New Period Modal -->
    <div id="newPeriodModal" class="modal" style="display: none;">
        <div class="modal-content" style="max-width: 500px;">
            <div class="modal-header">
                <h2>📅 Create New Attendance Period</h2>
                <span class="close" onclick="closeNewPeriodModal()">&times;</span>
            </div>
            <form method="POST" class="modal-body">
                <div style="margin-bottom: 20px; padding: 15px; background: #e8f4fd; border-left: 4px solid #007bff; border-radius: 4px;">
                    <strong style="color: #007bff;">ℹ️ Info:</strong>
                    <p style="margin: 5px 0 0 0; color: #555; font-size: 14px;">
                        Create a new monthly attendance period. All active employees will be automatically added to this period.
                    </p>
                </div>
                
                <div style="display: grid; grid-template-columns: 1fr 1fr; gap: 20px;">
                    <div class="form-group">
                        <label><strong>Select Month:</strong></label>
                        <select name="new_month" required class="form-control">
                            <?php 
                            $allMonths = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'];
                            foreach ($allMonths as $m): 
                            ?>
                                <option value="<?php echo $m; ?>" <?php echo $m === date('M') ? 'selected' : ''; ?>>
                                    <?php echo date('F', strtotime($m . ' 2025')); ?>
                                </option>
                            <?php endforeach; ?>
                        </select>
                    </div>
                    
                    <div class="form-group">
                        <label><strong>Enter Year:</strong></label>
                        <input 
                            type="text" 
                            name="new_year" 
                            id="new-year-input"
                            required 
                            class="form-control"
                            value="<?php echo date('y'); ?>"
                            placeholder="e.g., 25 or 2025"
                            maxlength="4"
                            style="text-align: center; font-weight: 600;"
                            title="Enter year in 2-digit (25) or 4-digit (2025) format"
                        />
                        <small style="color: #666; font-size: 12px; margin-top: 5px; display: block;">
                            💡 Enter any year (e.g., 25 for 2025, or 30 for 2030)
                        </small>
                    </div>
                </div>
                
                <div style="margin-top: 20px; padding: 12px; background: #fff3cd; border-left: 4px solid #ffc107; border-radius: 4px;">
                    <strong style="color: #856404;">⚠️ Note:</strong>
                    <ul style="margin: 5px 0 0 20px; color: #856404; font-size: 13px;">
                        <li>Only active employees will be added</li>
                        <li>You can fill attendance data after creation</li>
                        <li>Cannot create duplicate periods</li>
                    </ul>
                </div>
                
                <div class="modal-actions" style="margin-top: 25px; display: flex; gap: 10px; justify-content: flex-end;">
                    <button type="button" onclick="closeNewPeriodModal()" class="btn btn-secondary">
                        Cancel
                    </button>
                    <button type="submit" name="create_new_period" class="btn btn-success">
                        ✓ Create Period
                    </button>
                </div>
            </form>
        </div>
    </div>
    
    <script>
    function showNewPeriodModal() {
        document.getElementById('newPeriodModal').style.display = 'flex';
    }
    
    function closeNewPeriodModal() {
        document.getElementById('newPeriodModal').style.display = 'none';
    }
    
    // Close modal when clicking outside
    window.onclick = function(event) {
        const modal = document.getElementById('newPeriodModal');
        if (event.target === modal) {
            closeNewPeriodModal();
        }
    }
    
    // Close modal on ESC key
    document.addEventListener('keydown', function(event) {
        if (event.key === 'Escape') {
            closeNewPeriodModal();
        }
    });
    
    // Year input validation for modal
    const newYearInput = document.getElementById('new-year-input');
    
    // Only allow numbers
    newYearInput.addEventListener('input', function(e) {
        e.target.value = e.target.value.replace(/\D/g, '');
    });
    
    // Auto-format on blur
    newYearInput.addEventListener('blur', function(e) {
        let year = e.target.value.trim().replace(/\D/g, '');
        if (year.length === 4) {
            e.target.value = year.substring(2);
        } else if (year.length === 2) {
            e.target.value = year;
        }
    });
    
    // Validate before form submission
    document.querySelector('#newPeriodModal form').addEventListener('submit', function(e) {
        let year = newYearInput.value.trim().replace(/\D/g, '');
        
        if (year === '') {
            e.preventDefault();
            alert('Please enter a year!');
            return false;
        }
        
        // Convert 4-digit to 2-digit
        if (year.length === 4) {
            year = year.substring(2);
        } else if (year.length !== 2) {
            e.preventDefault();
            alert('Please enter a valid year (e.g., 25 or 2025)');
            return false;
        }
        
        // Validate range
        const yearNum = parseInt(year);
        if (isNaN(yearNum) || yearNum < 0 || yearNum > 99) {
            e.preventDefault();
            alert('Please enter a valid year between 2000-2099');
            return false;
        }
        
        // Update to 2-digit format before submission
        newYearInput.value = year;
    });
    </script>
    
    <?php if ($periodExists): ?>
    <!-- Attendance Form - Only shown for created periods -->
    <form id="attendance-form" method="POST" action="?m=<?php echo urlencode($selectedMonthValue); ?>&y=<?php echo urlencode($selectedYearValue); ?>">
        <!-- Hidden fields to preserve month-year selection -->
        <input type="hidden" name="selected_month" value="<?php echo htmlspecialchars($selectedMonth); ?>">
        <input type="hidden" name="selected_month_value" value="<?php echo htmlspecialchars($selectedMonthValue); ?>">
        <input type="hidden" name="selected_year_value" value="<?php echo htmlspecialchars($selectedYearValue); ?>">
        <input type="hidden" name="month_display" value="<?php echo htmlspecialchars($selectedMonthDisplay); ?>">
        
        <table class="attendance-table">
            <thead>
                <!-- Section Headers Row -->
                <tr>
                    <th colspan="6" class="section-header">Payroll</th>
                    <th colspan="31" class="section-header-attendance">Attendance</th>
                    <th colspan="7" class="section-header-attendance">Attendance</th>
                    <th rowspan="2" class="section-header">Remarks</th>
                </tr>
                
                <!-- Column Headers Row -->
                <tr>
                    <th class="col-slno">S/L No</th>
                    <th class="col-name">Name</th>
                    <th class="col-designation">Designation</th>
                    <th class="col-emp-type">Employee Type</th>
                    <th class="col-category">Category</th>
                    <th class="col-status">Status</th>
                    
                    <?php for ($day = 1; $day <= 31; $day++): ?>
                        <th class="col-day"><?php echo $day; ?></th>
                    <?php endfor; ?>
                    
                    <th class="col-summary" title="Present">P</th>
                    <th class="col-summary" title="Half Day">H</th>
                    <th class="col-summary" title="Casual Leave">CL</th>
                    <th class="col-summary" title="Paid Leave">PL</th>
                    <th class="col-summary" title="Sick Leave">SL</th>
                    <th class="col-summary" title="Absent">A</th>
                    <th class="col-summary" title="Weekly Off">WO</th>
                </tr>
            </thead>
            <tbody>
                <?php 
                $slNo = 1;
                // Display ALL registered employees
                foreach ($employees as $emp): 
                    // Get attendance data for selected month
                    $attendance = $attendanceData[$emp['employee_id']] ?? [];
                    
                    // If no attendance record exists yet, create an empty array
                    // The attendance record will be auto-created when period exists
                ?>
                    <tr>
                        <!-- Employee Info -->
                        <td><?php echo $slNo++; ?></td>
                        <td class="text-left"><?php echo htmlspecialchars($emp['full_name']); ?></td>
                        <td><?php echo htmlspecialchars($emp['designation_name'] ?? ''); ?></td>
                        <td><?php 
                            $empType = $emp['employment_type'] ?? 'full_time';
                            // Convert enum values to display format
                            $typeMap = [
                                'full_time' => 'Regular',
                                'part_time' => 'Part Time',
                                'contract' => 'Contract',
                                'temporary' => 'Temporary'
                            ];
                            echo htmlspecialchars($typeMap[$empType] ?? 'Regular');
                        ?></td>
                        <td><?php echo htmlspecialchars($emp['category'] ?? 'Highly Skilled'); ?></td>
                        <td><?php echo ucfirst($emp['employment_status']); ?></td>
                        
                        <!-- Daily Attendance (Days 1-31) -->
                        <?php for ($day = 1; $day <= 31; $day++): 
                            $currentValue = isset($attendance["day_$day"]) && $attendance["day_$day"] !== null ? $attendance["day_$day"] : '';
                        ?>
                            <td>
                                <select name="attendance[<?php echo $emp['employee_id']; ?>][day_<?php echo $day; ?>]" 
                                        class="att-select status-<?php echo $currentValue; ?>"
                                        onchange="this.className='att-select status-' + this.value">
                                    <option value="">-</option>
                                    <option value="P" <?php echo $currentValue === 'P' ? 'selected' : ''; ?>>P</option>
                                    <option value="A" <?php echo $currentValue === 'A' ? 'selected' : ''; ?>>A</option>
                                    <option value="H" <?php echo $currentValue === 'H' ? 'selected' : ''; ?>>H</option>
                                    <option value="CL" <?php echo $currentValue === 'CL' ? 'selected' : ''; ?>>CL</option>
                                    <option value="PL" <?php echo $currentValue === 'PL' ? 'selected' : ''; ?>>PL</option>
                                    <option value="SL" <?php echo $currentValue === 'SL' ? 'selected' : ''; ?>>SL</option>
                                    <option value="WO" <?php echo $currentValue === 'WO' ? 'selected' : ''; ?>>WO</option>
                                </select>
                            </td>
                        <?php endfor; ?>
                        
                        <!-- Summary Columns (Auto-calculated on save) -->
                        <td class="summary-cell" id="p_<?php echo $emp['employee_id']; ?>">
                            <?php echo $attendance['present_count'] ?? 0; ?>
                        </td>
                        <td class="summary-cell" id="h_<?php echo $emp['employee_id']; ?>">
                            <?php echo $attendance['half_day_count'] ?? 0; ?>
                        </td>
                        <td class="summary-cell" id="cl_<?php echo $emp['employee_id']; ?>">
                            <?php echo $attendance['cl_count'] ?? 0; ?>
                        </td>
                        <td class="summary-cell" id="pl_<?php echo $emp['employee_id']; ?>">
                            <?php echo $attendance['pl_count'] ?? 0; ?>
                        </td>
                        <td class="summary-cell" id="sl_<?php echo $emp['employee_id']; ?>">
                            <?php echo $attendance['sl_count'] ?? 0; ?>
                        </td>
                        <td class="summary-cell" id="a_<?php echo $emp['employee_id']; ?>">
                            <?php echo $attendance['absent_count'] ?? 0; ?>
                        </td>
                        <td class="summary-cell" id="wo_<?php echo $emp['employee_id']; ?>">
                            <?php echo $attendance['wo_count'] ?? 0; ?>
                        </td>
                        
                        <!-- Remarks -->
                        <td>
                            <input type="text" 
                                   name="remarks[<?php echo $emp['employee_id']; ?>]" 
                                   class="remarks-input"
                                   value="<?php echo htmlspecialchars($attendance['remarks'] ?? ''); ?>"
                                   placeholder="Remarks...">
                        </td>
                    </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    </form>
    <?php else: ?>
        <!-- Period not created - Show message (already shown above in warning box) -->
        <div style="text-align: center; padding: 40px; color: #6c757d;">
            <p style="font-size: 16px; margin: 0;">
                No attendance data available for this period.<br>
                Please create the attendance period using the <strong>"➕ New Attendance Period"</strong> button above.
            </p>
        </div>
    <?php endif; ?>
</div>

<script>
// Add real-time summary calculation preview
document.addEventListener('DOMContentLoaded', function() {
    // You can add JavaScript here for real-time calculation preview if needed
});
</script>

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