<?php
/**
 * Enterprise HRM ERP System - New Joinee Statutory
 * PF/ESIC registration for new employees
 */

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

$pageTitle = 'New Joinee Statutory';
$currentModule = 'employee';
$currentTab = 'new_joinee';

$siteId = getCurrentSiteId();

// Get site name from Home
$db = getDB();
$stmt = $db->prepare("SELECT site_name FROM sites WHERE site_id = ?");
$stmt->execute([$siteId]);
$site = $stmt->fetch();
$siteName = $site ? $site['site_name'] : 'Base Office';

// Get filter parameters - Default to 'all' to show all employees including new ones
$filterType = isset($_GET['filter_type']) ? sanitize($_GET['filter_type']) : 'all'; // 'all', 'year', 'month'
$selectedMonth = isset($_GET['month']) ? sanitize($_GET['month']) : date('m');
$selectedYear = isset($_GET['year']) ? sanitize($_GET['year']) : date('Y');
$statusFilter = isset($_GET['status']) ? sanitize($_GET['status']) : ''; // 'all', 'active', 'inactive'
$search = isset($_GET['search']) ? sanitize($_GET['search']) : '';

// Pagination
$page = isset($_GET['page']) ? max(1, (int)$_GET['page']) : 1;
$perPage = 50; // 50 employees per page
$offset = ($page - 1) * $perPage;

// Validate year input
$selectedYear = (int)$selectedYear;
if ($selectedYear < 2000 || $selectedYear > 2100) {
    $selectedYear = date('Y');
}

// Format month name for display
$monthName = '';
if ($filterType === 'month' && !empty($selectedMonth) && !empty($selectedYear)) {
    $monthName = date('M-y', strtotime($selectedYear . '-' . $selectedMonth . '-01'));
} elseif ($filterType === 'year' && !empty($selectedYear)) {
    $monthName = $selectedYear;
} else {
    $monthName = 'All Employees';
}

// Handle form submission
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['save_statutory'])) {
    try {
        $employeeId = (int)($_POST['employee_id'] ?? 0);
        
        if ($employeeId <= 0) {
            throw new Exception('Invalid employee ID');
        }
        
        $userId = getCurrentUserId();
        
        // Check which columns exist in employees table
        $stmt = $db->query("SHOW COLUMNS FROM employees");
        $existingColumns = $stmt->fetchAll(PDO::FETCH_COLUMN);
        
        // Get all form fields
        $updateFields = [];
        $updateValues = [];
        
        // Debug: Log all POST data for this employee
        $postKeys = array_keys($_POST);
        $employeePostKeys = array_filter($postKeys, function($key) use ($employeeId) {
            return strpos($key, (string)$employeeId) !== false || $key === 'employee_id';
        });
        error_log("Employee {$employeeId} - POST keys found: " . implode(', ', $employeePostKeys));
        
        // Basic fields
        $designationKey = 'designation_' . $employeeId;
        if (isset($_POST[$designationKey]) || array_key_exists($designationKey, $_POST)) {
            $updateFields[] = 'designation_id = ?';
            $updateValues[] = !empty($_POST[$designationKey]) ? (int)$_POST[$designationKey] : null;
        }
        if (isset($_POST['department_' . $employeeId])) {
            $updateFields[] = 'department_id = ?';
            $updateValues[] = !empty($_POST['department_' . $employeeId]) ? (int)$_POST['department_' . $employeeId] : null;
        }
        if (isset($_POST['category_' . $employeeId])) {
            $updateFields[] = 'employee_category = ?';
            $updateValues[] = sanitize($_POST['category_' . $employeeId] ?? '');
        }
        if (isset($_POST['emp_type_' . $employeeId])) {
            $updateFields[] = 'employment_type = ?';
            $updateValues[] = sanitize($_POST['emp_type_' . $employeeId] ?? '');
        }
        if (isset($_POST['gender_' . $employeeId])) {
            $updateFields[] = 'gender = ?';
            $updateValues[] = sanitize($_POST['gender_' . $employeeId] ?? '');
        }
        if (isset($_POST['dob_' . $employeeId])) {
            $updateFields[] = 'date_of_birth = ?';
            $updateValues[] = !empty($_POST['dob_' . $employeeId]) ? $_POST['dob_' . $employeeId] : null;
        }
        if (isset($_POST['doj_' . $employeeId])) {
            $updateFields[] = 'date_of_joining = ?';
            $updateValues[] = !empty($_POST['doj_' . $employeeId]) ? $_POST['doj_' . $employeeId] : null;
        }
        
        // Statutory fields - ALWAYS save if column exists (even if empty)
        $esicKey = 'esic_no_' . $employeeId;
        $uanKey = 'uan_no_' . $employeeId;
        $panKey = 'pan_no_' . $employeeId;
        $aadharKey = 'aadhar_' . $employeeId;
        
        // Always include these fields if columns exist
        if (in_array('esic_number', $existingColumns)) {
            $updateFields[] = 'esic_number = ?';
            $esicValue = '';
            if (isset($_POST[$esicKey])) {
                $esicValue = sanitize($_POST[$esicKey]);
            } elseif (isset($_POST['esic_number'])) {
                $esicValue = sanitize($_POST['esic_number']);
            }
            $updateValues[] = $esicValue;
        }
        if (in_array('uan_number', $existingColumns)) {
            $updateFields[] = 'uan_number = ?';
            $uanValue = '';
            if (isset($_POST[$uanKey])) {
                $uanValue = sanitize($_POST[$uanKey]);
            } elseif (isset($_POST['uan_number'])) {
                $uanValue = sanitize($_POST['uan_number']);
            }
            $updateValues[] = $uanValue;
        }
        if (in_array('pan_number', $existingColumns)) {
            $updateFields[] = 'pan_number = ?';
            $panValue = '';
            if (isset($_POST[$panKey])) {
                $panValue = sanitize($_POST[$panKey]);
            } elseif (isset($_POST['pan_number'])) {
                $panValue = sanitize($_POST['pan_number']);
            }
            $updateValues[] = $panValue;
        }
        if (in_array('aadhar_number', $existingColumns)) {
            $updateFields[] = 'aadhar_number = ?';
            $aadharValue = '';
            if (isset($_POST[$aadharKey])) {
                $aadharValue = sanitize($_POST[$aadharKey]);
            } elseif (isset($_POST['aadhar_number'])) {
                $aadharValue = sanitize($_POST['aadhar_number']);
            }
            $updateValues[] = $aadharValue;
        }
        
        // Bank fields
        if (isset($_POST['bank_name_' . $employeeId])) {
            $updateFields[] = 'bank_name = ?';
            $updateValues[] = sanitize($_POST['bank_name_' . $employeeId] ?? '');
        }
        if (isset($_POST['bank_ac_' . $employeeId])) {
            $updateFields[] = 'bank_account_number = ?';
            $updateValues[] = sanitize($_POST['bank_ac_' . $employeeId] ?? '');
        }
        if (isset($_POST['ifsc_' . $employeeId])) {
            $updateFields[] = 'bank_ifsc = ?';
            $updateValues[] = sanitize($_POST['ifsc_' . $employeeId] ?? '');
        }
        
        // Payment fields
        if (in_array('pay_mode', $existingColumns) && isset($_POST['pay_mode_' . $employeeId])) {
            $updateFields[] = 'pay_mode = ?';
            $updateValues[] = sanitize($_POST['pay_mode_' . $employeeId] ?? '');
        }
        if (in_array('bank_mode', $existingColumns) && isset($_POST['bank_mode_' . $employeeId])) {
            $updateFields[] = 'bank_mode = ?';
            $updateValues[] = sanitize($_POST['bank_mode_' . $employeeId] ?? '');
        }
        
        // Status
        if (isset($_POST['status_' . $employeeId])) {
            $updateFields[] = 'employment_status = ?';
            $updateValues[] = sanitize($_POST['status_' . $employeeId] ?? '');
        }
        
        // Remarks - ALWAYS save if column exists (even if empty)
        $remarksKey = 'remarks_' . $employeeId;
        if (in_array('remarks', $existingColumns)) {
            $updateFields[] = 'remarks = ?';
            $remarksValue = '';
            if (isset($_POST[$remarksKey])) {
                $remarksValue = sanitize($_POST[$remarksKey]);
            } elseif (isset($_POST['remarks'])) {
                $remarksValue = sanitize($_POST['remarks']);
            }
            $updateValues[] = $remarksValue;
        }
        
        // Always update updated_by and updated_at
        $updateFields[] = 'updated_by = ?';
        $updateValues[] = $userId;
        $updateFields[] = 'updated_at = CURRENT_TIMESTAMP';
        
        // Add employee_id and site_id for WHERE clause
        $updateValues[] = $employeeId;
        $updateValues[] = $siteId;
        
        if (empty($updateFields)) {
            // Even if no other fields, at least update the timestamp
            $updateFields[] = 'updated_at = CURRENT_TIMESTAMP';
            $updateFields[] = 'updated_by = ?';
            $updateValues = [$userId, $employeeId, $siteId];
            error_log("Warning: No fields to update for employee {$employeeId}, only updating timestamp");
        }
        
        $sql = "UPDATE employees SET " . implode(', ', $updateFields) . " WHERE employee_id = ? AND site_id = ?";
        error_log("Update SQL: " . $sql);
        error_log("Update values count: " . count($updateValues));
        
        $stmt = $db->prepare($sql);
        $result = $stmt->execute($updateValues);
        
        if (!$result) {
            $errorInfo = $stmt->errorInfo();
            throw new Exception('Database update failed: ' . ($errorInfo[2] ?? 'Unknown error'));
        }
        
        if (!$result) {
            throw new Exception('Database update failed');
        }
        
        $affectedRows = $stmt->rowCount();
        
        // Log the update with details
        $logData = [
            'employee_id' => $employeeId,
            'affected_rows' => $affectedRows,
            'fields_updated' => count($updateFields),
            'pan_number' => $_POST[$panKey] ?? $_POST['pan_number'] ?? 'N/A',
            'aadhar_number' => $_POST[$aadharKey] ?? $_POST['aadhar_number'] ?? 'N/A',
            'remarks' => substr($_POST[$remarksKey] ?? $_POST['remarks'] ?? 'N/A', 0, 50)
        ];
        error_log("Statutory data saved: " . json_encode($logData));
        
        if ($affectedRows > 0) {
            $_SESSION['success_message'] = 'Employee statutory details saved successfully! (Updated ' . $affectedRows . ' record(s))';
        } else {
            $_SESSION['success_message'] = 'Data submitted but no rows were updated. Please check if employee ID and site ID are correct.';
        }
        header('Location: ' . $_SERVER['PHP_SELF'] . '?saved=' . $employeeId);
        exit;
    } catch (Exception $e) {
        error_log("Statutory save error: " . $e->getMessage());
        error_log("POST data: " . print_r($_POST, true));
        error_log("Employee ID from POST: " . ($_POST['employee_id'] ?? 'NOT SET'));
        
        // Debug: Show what fields were found
        $foundFields = [];
        foreach ($_POST as $key => $value) {
            if (strpos($key, (string)$employeeId) !== false || in_array($key, ['employee_id', 'save_statutory'])) {
                $foundFields[$key] = substr($value, 0, 50);
            }
        }
        error_log("Relevant POST fields: " . json_encode($foundFields));
        
        $_SESSION['error_message'] = 'Error saving employee data: ' . htmlspecialchars($e->getMessage());
        if (isset($_GET['debug'])) {
            $_SESSION['error_message'] .= '<br><small>Debug: Check error logs for details. POST keys: ' . implode(', ', array_keys($foundFields)) . '</small>';
        }
        header('Location: ' . $_SERVER['PHP_SELF']);
        exit;
    }
}

// Get all employees (treating this as employee registration display)
try {
    // Check if site_id is valid
    if (!$siteId) {
        $siteId = $_SESSION['site_id'] ?? null;
        if (!$siteId) {
            // Get first available site
            $stmt = $db->query("SELECT site_id FROM sites LIMIT 1");
            $site = $stmt->fetch();
            $siteId = $site ? $site['site_id'] : 1;
        }
    }
    
    // Check which columns exist in employees table
    $stmt = $db->query("SHOW COLUMNS FROM employees");
    $existingColumns = $stmt->fetchAll(PDO::FETCH_COLUMN);
    
    // Build SELECT query dynamically based on existing columns
    $selectColumns = ['e.employee_id', 'e.site_id', 'e.employee_code', 'e.first_name', 'e.middle_name', 'e.last_name',
                      'e.email', 'e.phone', 'e.date_of_birth', 'e.gender', 'e.marital_status', 'e.national_id',
                      'e.address_line1', 'e.address_line2', 'e.city', 'e.state', 'e.postal_code', 'e.country',
                      'e.department_id', 'e.designation_id', 'e.date_of_joining', 'e.employment_type', 
                      'e.employment_status', 'e.employee_category', 'e.salary',
                      'e.bank_name', 'e.bank_account_number', 'e.bank_ifsc',
                      'e.emergency_contact_name', 'e.emergency_contact_phone', 'e.emergency_contact_relation',
                      'e.esic_number', 'e.uan_number', 'e.pf_number',
                      'e.profile_picture', 'e.created_by', 'e.created_at', 'e.updated_by', 'e.updated_at'];
    
    // Add optional columns if they exist
    if (in_array('pan_number', $existingColumns)) {
        $selectColumns[] = 'e.pan_number';
    }
    if (in_array('aadhar_number', $existingColumns)) {
        $selectColumns[] = 'e.aadhar_number';
    }
    if (in_array('remarks', $existingColumns)) {
        $selectColumns[] = 'e.remarks';
    }
    if (in_array('pay_mode', $existingColumns)) {
        $selectColumns[] = 'e.pay_mode';
    }
    if (in_array('bank_mode', $existingColumns)) {
        $selectColumns[] = 'e.bank_mode';
    }
    
    $selectColumns[] = 'd.department_name';
    $selectColumns[] = 'des.designation_name';
    
    $selectClause = implode(', ', $selectColumns);
    
    // Build WHERE clause with filters
    $whereConditions = ['e.site_id = ?'];
    $queryParams = [$siteId];
    
    // Status filter
    if ($statusFilter === 'active') {
        $whereConditions[] = "e.employment_status = 'active'";
    } elseif ($statusFilter === 'inactive') {
        $whereConditions[] = "e.employment_status = 'inactive'";
    }
    // If statusFilter is empty or 'all', show all statuses
    
    // Search filter
    if (!empty($search)) {
        $whereConditions[] = "(e.employee_code LIKE ? OR e.first_name LIKE ? OR e.last_name LIKE ? OR e.email LIKE ? OR CONCAT(e.first_name, ' ', COALESCE(e.middle_name, ''), ' ', e.last_name) LIKE ?)";
        $searchParam = "%{$search}%";
        $queryParams = array_merge($queryParams, [$searchParam, $searchParam, $searchParam, $searchParam, $searchParam]);
    }
    
    // Filter by date_of_joining based on filter type
    // Only apply date filter if date_of_joining is not NULL
    if ($filterType === 'month' && !empty($selectedMonth) && !empty($selectedYear)) {
        // Filter by specific month and year (based on date_of_joining)
        $firstDay = $selectedYear . '-' . str_pad($selectedMonth, 2, '0', STR_PAD_LEFT) . '-01';
        $lastDay = date('Y-m-t', strtotime($firstDay)); // Last day of the month
        
        $whereConditions[] = 'e.date_of_joining IS NOT NULL';
        $whereConditions[] = 'DATE(e.date_of_joining) >= ?';
        $whereConditions[] = 'DATE(e.date_of_joining) <= ?';
        $queryParams[] = $firstDay;
        $queryParams[] = $lastDay;
    } elseif ($filterType === 'year' && !empty($selectedYear)) {
        // Filter by year only (all employees who joined in that year)
        $firstDay = $selectedYear . '-01-01';
        $lastDay = $selectedYear . '-12-31';
        
        $whereConditions[] = 'e.date_of_joining IS NOT NULL';
        $whereConditions[] = 'DATE(e.date_of_joining) >= ?';
        $whereConditions[] = 'DATE(e.date_of_joining) <= ?';
        $queryParams[] = $firstDay;
        $queryParams[] = $lastDay;
    }
    // If filterType is 'all', no date filter is applied - show all employees
    
    $whereClause = implode(' AND ', $whereConditions);
    
    // Get total count for pagination
    $countSql = "SELECT COUNT(*) FROM employees e WHERE {$whereClause}";
    $countStmt = $db->prepare($countSql);
    $countStmt->execute($queryParams);
    $totalRecords = $countStmt->fetchColumn();
    $totalPages = ceil($totalRecords / $perPage);
    
    // Debug: Log the query and parameters
    if (isset($_GET['debug']) && isSuperAdmin()) {
        error_log("Filter Query: SELECT ... WHERE {$whereClause}");
        error_log("Filter Params: " . print_r($queryParams, true));
        error_log("Filter Type: {$filterType}, Month: {$selectedMonth}, Year: {$selectedYear}, Status: {$statusFilter}, Search: {$search}");
        error_log("Total Records: {$totalRecords}, Page: {$page}, Total Pages: {$totalPages}");
    }
    
    // Add pagination to query
    $paginationParams = $queryParams;
    $paginationParams[] = $perPage;
    $paginationParams[] = $offset;
    
    // Fetch employees with pagination
    $stmt = $db->prepare("
        SELECT {$selectClause}
        FROM employees e
        LEFT JOIN departments d ON e.department_id = d.department_id
        LEFT JOIN designations des ON e.designation_id = des.designation_id
        WHERE {$whereClause}
        ORDER BY e.date_of_joining DESC, e.employee_code
        LIMIT ? OFFSET ?
    ");
    
    $stmt->execute($paginationParams);
    $employees = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Log result count for debugging
    if (isset($_GET['debug']) && isSuperAdmin()) {
        error_log("Employees found on page {$page}: " . count($employees));
    }
    
    // Get salary components for each employee
    // Check if employee_salary_structure table exists
    $salaryTableExists = false;
    try {
        $checkStmt = $db->query("SHOW TABLES LIKE 'employee_salary_structure'");
        $salaryTableExists = $checkStmt->rowCount() > 0;
    } catch (Exception $e) {
        $salaryTableExists = false;
    }
    
    foreach ($employees as &$emp) {
        // Initialize salary components
        $emp['basic_salary'] = 0;
        $emp['hra'] = 0;
        $emp['da'] = 0;
        $emp['ta'] = 0;
        $emp['special_allowance'] = 0;
        
        // Try to get from employee_salary_structure if table exists
        if ($salaryTableExists) {
            try {
                $salaryStmt = $db->prepare("
                    SELECT sc.component_code, ess.amount
                    FROM employee_salary_structure ess
                    JOIN salary_components sc ON ess.component_id = sc.component_id
                    WHERE ess.employee_id = ? AND ess.structure_status = 'active'
                ");
                $salaryStmt->execute([$emp['employee_id']]);
                $salaryComponents = $salaryStmt->fetchAll(PDO::FETCH_KEY_PAIR);
                
                $emp['basic_salary'] = (float)($salaryComponents['BASIC'] ?? 0);
                $emp['hra'] = (float)($salaryComponents['HRA'] ?? 0);
                $emp['da'] = (float)($salaryComponents['DA'] ?? 0);
                $emp['ta'] = (float)($salaryComponents['TA'] ?? 0);
                $emp['special_allowance'] = (float)($salaryComponents['SPECIAL_ALLOWANCE'] ?? 0);
            } catch (Exception $e) {
                error_log("Error fetching salary for employee {$emp['employee_id']}: " . $e->getMessage());
            }
        }
        
        // If no salary structure, try to get from salary field
        if ($emp['basic_salary'] == 0 && !empty($emp['salary'])) {
            $emp['basic_salary'] = (float)$emp['salary'];
        }
    }
    unset($emp);
    
} catch (Exception $e) {
    error_log("Error fetching employees: " . $e->getMessage());
    $employees = [];
    $_SESSION['error_message'] = 'Error loading employees: ' . $e->getMessage();
}

// 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 `new_joinee_statutory_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 new_joinee_statutory_to_names WHERE site_id = ?");
        $checkStmt->execute([$siteId]);
        $existing = $checkStmt->fetch();
        
        if ($existing) {
            $updateStmt = $db->prepare("UPDATE new_joinee_statutory_to_names SET to_name = ? WHERE site_id = ?");
            $updateStmt->execute([$toName, $siteId]);
        } else {
            $insertStmt = $db->prepare("INSERT INTO new_joinee_statutory_to_names (site_id, to_name) VALUES (?, ?)");
            $insertStmt->execute([$siteId, $toName]);
        }
        
        $_SESSION['success_message'] = 'To Name saved successfully!';
        // Redirect to prevent form resubmission
        $redirectUrl = $_SERVER['PHP_SELF'] . '?to_saved=1';
        if (!empty($filterType)) $redirectUrl .= '&filter_type=' . urlencode($filterType);
        if (!empty($selectedMonth)) $redirectUrl .= '&month=' . urlencode($selectedMonth);
        if (!empty($selectedYear)) $redirectUrl .= '&year=' . urlencode($selectedYear);
        if (!empty($statusFilter)) $redirectUrl .= '&status=' . urlencode($statusFilter);
        header('Location: ' . $redirectUrl);
        exit;
    } catch (Exception $e) {
        error_log("Error saving new joinee statutory To Name: " . $e->getMessage());
        $_SESSION['error_message'] = 'Error saving To Name: ' . $e->getMessage();
    }
}

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

// Handle Excel Export
if (isset($_GET['export']) && $_GET['export'] === 'excel') {
    // Get "To" name for this site
    $exportToName = '';
    try {
        $toNameStmt = $db->prepare("SELECT to_name FROM new_joinee_statutory_to_names WHERE site_id = ?");
        $toNameStmt->execute([$siteId]);
        $toNameRecord = $toNameStmt->fetch();
        $exportToName = $toNameRecord ? $toNameRecord['to_name'] : '';
    } catch (Exception $e) {
        // Table might not exist yet
    }
    
    // Build filename
    $exportFilename = 'New_Joinee_Statutory';
    if ($filterType === 'month') {
        $exportFilename .= '_' . date('M_Y', strtotime($selectedYear . '-' . $selectedMonth . '-01'));
    } elseif ($filterType === 'year') {
        $exportFilename .= '_' . $selectedYear;
    } else {
        $exportFilename .= '_All';
    }
    $exportFilename .= '_' . date('Ymd_His');
    
    // Check which columns exist in employees table
    $stmt = $db->query("SHOW COLUMNS FROM employees");
    $existingColumns = $stmt->fetchAll(PDO::FETCH_COLUMN);
    
    // Build SELECT query dynamically based on existing columns
    $selectColumns = ['e.employee_id', 'e.site_id', 'e.employee_code', 'e.first_name', 'e.middle_name', 'e.last_name',
                      'e.date_of_birth', 'e.gender', 'e.department_id', 'e.designation_id', 'e.date_of_joining', 
                      'e.employment_type', 'e.employment_status', 'e.employee_category', 'e.salary',
                      'e.bank_name', 'e.bank_account_number', 'e.bank_ifsc',
                      'e.esic_number', 'e.uan_number'];
    
    if (in_array('pan_number', $existingColumns)) {
        $selectColumns[] = 'e.pan_number';
    }
    if (in_array('aadhar_number', $existingColumns)) {
        $selectColumns[] = 'e.aadhar_number';
    }
    if (in_array('remarks', $existingColumns)) {
        $selectColumns[] = 'e.remarks';
    }
    if (in_array('pay_mode', $existingColumns)) {
        $selectColumns[] = 'e.pay_mode';
    }
    if (in_array('bank_mode', $existingColumns)) {
        $selectColumns[] = 'e.bank_mode';
    }
    
    $selectColumns[] = 'd.department_name';
    $selectColumns[] = 'des.designation_name';
    
    $selectClause = implode(', ', $selectColumns);
    
    // Build WHERE clause with filters
    $whereConditions = ['e.site_id = ?'];
    $queryParams = [$siteId];
    
    if ($statusFilter === 'active') {
        $whereConditions[] = "e.employment_status = 'active'";
    } elseif ($statusFilter === 'inactive') {
        $whereConditions[] = "e.employment_status = 'inactive'";
    }
    
    if (!empty($search)) {
        $whereConditions[] = "(e.employee_code LIKE ? OR e.first_name LIKE ? OR e.last_name LIKE ? OR CONCAT(e.first_name, ' ', COALESCE(e.middle_name, ''), ' ', e.last_name) LIKE ?)";
        $searchParam = "%{$search}%";
        $queryParams = array_merge($queryParams, [$searchParam, $searchParam, $searchParam, $searchParam]);
    }
    
    if ($filterType === 'month' && !empty($selectedMonth) && !empty($selectedYear)) {
        $firstDay = $selectedYear . '-' . str_pad($selectedMonth, 2, '0', STR_PAD_LEFT) . '-01';
        $lastDay = date('Y-m-t', strtotime($firstDay));
        $whereConditions[] = 'e.date_of_joining IS NOT NULL';
        $whereConditions[] = 'DATE(e.date_of_joining) >= ?';
        $whereConditions[] = 'DATE(e.date_of_joining) <= ?';
        $queryParams[] = $firstDay;
        $queryParams[] = $lastDay;
    } elseif ($filterType === 'year' && !empty($selectedYear)) {
        $firstDay = $selectedYear . '-01-01';
        $lastDay = $selectedYear . '-12-31';
        $whereConditions[] = 'e.date_of_joining IS NOT NULL';
        $whereConditions[] = 'DATE(e.date_of_joining) >= ?';
        $whereConditions[] = 'DATE(e.date_of_joining) <= ?';
        $queryParams[] = $firstDay;
        $queryParams[] = $lastDay;
    }
    
    $whereClause = implode(' AND ', $whereConditions);
    
    // Fetch all employees for export (no pagination)
    $exportStmt = $db->prepare("
        SELECT {$selectClause}
        FROM employees e
        LEFT JOIN departments d ON e.department_id = d.department_id
        LEFT JOIN designations des ON e.designation_id = des.designation_id
        WHERE {$whereClause}
        ORDER BY e.date_of_joining DESC, e.employee_code
    ");
    $exportStmt->execute($queryParams);
    $exportEmployees = $exportStmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Get salary components for each employee
    $salaryTableExists = false;
    try {
        $checkStmt = $db->query("SHOW TABLES LIKE 'employee_salary_structure'");
        $salaryTableExists = $checkStmt->rowCount() > 0;
    } catch (Exception $e) {
        $salaryTableExists = false;
    }
    
    foreach ($exportEmployees as &$emp) {
        $emp['basic_salary'] = 0;
        $emp['hra'] = 0;
        $emp['da'] = 0;
        $emp['ta'] = 0;
        $emp['special_allowance'] = 0;
        
        if ($salaryTableExists) {
            try {
                $salaryStmt = $db->prepare("
                    SELECT sc.component_code, ess.amount
                    FROM employee_salary_structure ess
                    JOIN salary_components sc ON ess.component_id = sc.component_id
                    WHERE ess.employee_id = ? AND ess.structure_status = 'active'
                ");
                $salaryStmt->execute([$emp['employee_id']]);
                $salaryComponents = $salaryStmt->fetchAll(PDO::FETCH_KEY_PAIR);
                
                $emp['basic_salary'] = (float)($salaryComponents['BASIC'] ?? 0);
                $emp['hra'] = (float)($salaryComponents['HRA'] ?? 0);
                $emp['da'] = (float)($salaryComponents['DA'] ?? 0);
                $emp['ta'] = (float)($salaryComponents['TA'] ?? 0);
                $emp['special_allowance'] = (float)($salaryComponents['SPECIAL_ALLOWANCE'] ?? 0);
            } catch (Exception $e) {
                // Ignore errors
            }
        }
        
        if ($emp['basic_salary'] == 0 && !empty($emp['salary'])) {
            $emp['basic_salary'] = (float)$emp['salary'];
        }
    }
    unset($emp);
    
    // Output Excel (HTML format)
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment; filename="' . $exportFilename . '.xls"');
    header('Cache-Control: max-age=0');
    
    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: #1e3a5f; color: white; font-weight: bold; padding: 8px; text-align: center; border: 1px solid #000; }
        td { padding: 5px; border: 1px solid #ccc; text-align: left; }
        .earn-header { background-color: #28a745 !important; color: white !important; }
        .deduction-header { background-color: #1e3a5f !important; color: white !important; }
        .payment-header { background-color: #9c27b0 !important; color: white !important; }
        .earnings-section { background-color: #ffeb3b !important; text-align: right; }
        .deductions-section { background-color: #ffeb3b !important; text-align: right; }
        .total-cell { background-color: #1e3a5f !important; color: white !important; font-weight: bold; text-align: right; }
        .net-payable { background-color: #1e3a5f !important; color: white !important; font-weight: bold; text-align: right; }
        .to-name { font-weight: bold; font-size: 12pt; padding: 10px; border: none; }
        .highlight { background-color: #ffeb3b !important; }
    </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>';
    
    // Header Row 1
    echo '<tr>';
    echo '<th rowspan="2">Sl. No</th>';
    echo '<th rowspan="2">Emp ID</th>';
    echo '<th rowspan="2">Name</th>';
    echo '<th rowspan="2">Designation</th>';
    echo '<th rowspan="2">Category</th>';
    echo '<th rowspan="2">Department</th>';
    echo '<th rowspan="2">Employee Type</th>';
    echo '<th rowspan="2">Gender</th>';
    echo '<th rowspan="2">DOB</th>';
    echo '<th rowspan="2">DOJ</th>';
    echo '<th rowspan="2">ESIC NO</th>';
    echo '<th rowspan="2">UAN NO</th>';
    echo '<th rowspan="2">PAN No</th>';
    echo '<th rowspan="2">Aadhar Number</th>';
    echo '<th colspan="6" class="earn-header">Earnings</th>';
    echo '<th colspan="5" class="deduction-header">Deductions</th>';
    echo '<th rowspan="2" class="deduction-header">NET PAYABLE</th>';
    echo '<th colspan="7" class="payment-header">Payment</th>';
    echo '</tr>';
    
    // Header Row 2
    echo '<tr>';
    echo '<th class="earn-header">Basic Salary</th>';
    echo '<th class="earn-header">HRA</th>';
    echo '<th class="earn-header">DA</th>';
    echo '<th class="earn-header">Special Allowances</th>';
    echo '<th class="earn-header">TA</th>';
    echo '<th class="earn-header">TOTAL</th>';
    echo '<th class="deduction-header">PF</th>';
    echo '<th class="deduction-header">ESIC</th>';
    echo '<th class="deduction-header">Professional Tax</th>';
    echo '<th class="deduction-header">TDS</th>';
    echo '<th class="deduction-header">TOTAL</th>';
    echo '<th class="payment-header">Pay Mode</th>';
    echo '<th class="payment-header">Bank Mode</th>';
    echo '<th class="payment-header">Bank Name</th>';
    echo '<th class="payment-header">Bank A/c No</th>';
    echo '<th class="payment-header">IFSC Code</th>';
    echo '<th class="payment-header">Status</th>';
    echo '<th class="payment-header">Remarks</th>';
    echo '</tr>';
    
    // Data rows
    $slNo = 1;
    foreach ($exportEmployees as $emp) {
        $basic = (float)($emp['basic_salary'] ?? 0);
        $hra = (float)($emp['hra'] ?? 0);
        $da = (float)($emp['da'] ?? 0);
        $specialAllowance = (float)($emp['special_allowance'] ?? 0);
        $ta = (float)($emp['ta'] ?? 0);
        $totalEarnings = $basic + $hra + $da + $specialAllowance + $ta;
        
        $pfDeduction = round(min($basic, 15000) * 0.12, 2);
        $esicDeduction = $totalEarnings <= 21000 ? round($totalEarnings * 0.0175, 2) : 0;
        $professionalTax = $totalEarnings < 13304 ? 0 : ($totalEarnings < 25000 ? 125 : 200);
        $tds = 0;
        $totalDeductions = $pfDeduction + $esicDeduction + $professionalTax + $tds;
        $netPayable = $totalEarnings - $totalDeductions;
        
        $fullName = trim(($emp['first_name'] ?? '') . ' ' . ($emp['middle_name'] ?? '') . ' ' . ($emp['last_name'] ?? ''));
        
        $empType = $emp['employment_type'] ?? 'full_time';
        $typeMap = ['full_time' => 'Regular', 'contract' => 'FTC', 'part_time' => 'Part Time', 'temporary' => 'Temporary'];
        $employeeType = $typeMap[$empType] ?? 'Regular';
        
        echo '<tr>';
        echo '<td>' . $slNo++ . '</td>';
        echo '<td>' . htmlspecialchars($emp['employee_code'] ?? '') . '</td>';
        echo '<td class="highlight">' . htmlspecialchars($fullName) . '</td>';
        echo '<td>' . htmlspecialchars($emp['designation_name'] ?? '') . '</td>';
        echo '<td>' . htmlspecialchars($emp['employee_category'] ?? '') . '</td>';
        echo '<td>' . htmlspecialchars($emp['department_name'] ?? '') . '</td>';
        echo '<td>' . htmlspecialchars($employeeType) . '</td>';
        echo '<td>' . ucfirst($emp['gender'] ?? '') . '</td>';
        echo '<td>' . ($emp['date_of_birth'] ? date('d-m-Y', strtotime($emp['date_of_birth'])) : '') . '</td>';
        echo '<td>' . ($emp['date_of_joining'] ? date('d-m-Y', strtotime($emp['date_of_joining'])) : '') . '</td>';
        echo '<td>' . htmlspecialchars($emp['esic_number'] ?? '') . '</td>';
        echo '<td>' . htmlspecialchars($emp['uan_number'] ?? '') . '</td>';
        echo '<td>' . htmlspecialchars($emp['pan_number'] ?? '') . '</td>';
        echo '<td>' . htmlspecialchars($emp['aadhar_number'] ?? '') . '</td>';
        echo '<td class="earnings-section">' . number_format($basic, 2) . '</td>';
        echo '<td class="earnings-section">' . number_format($hra, 2) . '</td>';
        echo '<td class="earnings-section">' . number_format($da, 2) . '</td>';
        echo '<td class="earnings-section">' . number_format($specialAllowance, 2) . '</td>';
        echo '<td class="earnings-section">' . number_format($ta, 2) . '</td>';
        echo '<td class="total-cell">' . number_format($totalEarnings, 2) . '</td>';
        echo '<td class="deductions-section">' . number_format($pfDeduction, 2) . '</td>';
        echo '<td class="deductions-section">' . number_format($esicDeduction, 2) . '</td>';
        echo '<td class="deductions-section">' . number_format($professionalTax, 2) . '</td>';
        echo '<td class="deductions-section">' . number_format($tds, 2) . '</td>';
        echo '<td class="total-cell">' . number_format($totalDeductions, 2) . '</td>';
        echo '<td class="net-payable">' . number_format($netPayable, 2) . '</td>';
        echo '<td>' . htmlspecialchars($emp['pay_mode'] ?? '') . '</td>';
        echo '<td>' . htmlspecialchars($emp['bank_mode'] ?? 'NEFT') . '</td>';
        echo '<td>' . htmlspecialchars($emp['bank_name'] ?? '') . '</td>';
        echo '<td>' . htmlspecialchars($emp['bank_account_number'] ?? '') . '</td>';
        echo '<td>' . htmlspecialchars($emp['bank_ifsc'] ?? '') . '</td>';
        echo '<td>' . ucfirst($emp['employment_status'] ?? '') . '</td>';
        echo '<td>' . htmlspecialchars($emp['remarks'] ?? '') . '</td>';
        echo '</tr>';
    }
    
    echo '</table></body></html>';
    exit;
}

// Fetch current To name for this site
$currentToName = '';
try {
    // Create table if not exists
    $db->exec("
        CREATE TABLE IF NOT EXISTS `new_joinee_statutory_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 new_joinee_statutory_to_names WHERE site_id = ?");
    $toNameStmt->execute([$siteId]);
    $toNameRecord = $toNameStmt->fetch();
    $currentToName = $toNameRecord ? $toNameRecord['to_name'] : '';
} catch (Exception $e) {
    error_log("Error fetching new joinee statutory To Name: " . $e->getMessage());
}

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

<style>
.statutory-container {
    padding: 20px;
    background: #f5f5f5;
}

.header-box {
    background: #1e3a5f;
    color: white;
    padding: 15px;
    border-radius: 5px;
    margin-bottom: 20px;
}

.header-box .row {
    display: flex;
    gap: 15px;
    align-items: center;
    flex-wrap: nowrap;
    width: 100%;
}

.header-box .field {
    display: flex;
    align-items: center;
    gap: 8px;
}

.header-box .field label {
    color: white;
    font-weight: 500;
    white-space: nowrap;
    margin: 0;
    font-size: 11px;
    min-width: auto;
}

.header-box .field .form-control,
.header-box .field input[type="text"],
.header-box .field input[type="number"],
.header-box .field select {
    background: white;
    border: 1px solid #ccc;
    border-radius: 3px;
    padding: 4px 6px;
    color: #333;
    font-size: 11px;
}

.header-box .field .form-control:focus,
.header-box .field input:focus,
.header-box .field select:focus {
    outline: none;
    border-color: #007bff;
    box-shadow: 0 0 0 2px rgba(0, 123, 255, 0.25);
}

.table-container {
    background: white;
    border-radius: 5px;
    padding: 20px;
    overflow-x: auto;
}

table {
    width: 100%;
    border-collapse: collapse;
}

th {
    background: #1e3a5f;
    color: white;
    padding: 12px;
    text-align: left;
    font-size: 11px;
    border: 1px solid #ddd;
}

td {
    padding: 8px;
    border: 1px solid #ddd;
    font-size: 11px;
}

tr:nth-child(even) {
    background: #f9f9f9;
}

input[type="text"],
input[type="date"],
select {
    width: 100%;
    padding: 5px;
    border: 1px solid #ccc;
    border-radius: 3px;
    font-size: 11px;
}

.earnings-section {
    background: #ffeb3b;
    color: #333;
    font-weight: bold;
    text-align: center;
}

.deductions-section {
    background: #ffeb3b;
    color: #333;
    font-weight: bold;
    text-align: center;
}

.earn-header {
    background: #28a745;
    color: white;
    font-weight: bold;
    text-align: center;
}

.deduction-header {
    background: #1e3a5f;
    color: white;
    font-weight: bold;
    text-align: center;
}

.payment-header {
    background: #9c27b0;
    color: white;
    font-weight: bold;
    text-align: center;
}

td.highlight {
    background: #ffeb3b;
}

input[type="text"].editable,
select.editable {
    background: #ffeb3b;
    border: 1px dotted #999;
}

.btn-save {
    background: #28a745;
    color: white;
    border: none;
    padding: 5px 15px;
    border-radius: 3px;
    cursor: pointer;
    font-size: 11px;
}

.btn-save:hover {
    background: #218838;
}

.btn-refresh {
    background: #007bff;
    color: white;
    border: none;
    padding: 4px 8px;
    border-radius: 3px;
    cursor: pointer;
    font-size: 11px;
    font-weight: 500;
    display: inline-flex;
    align-items: center;
    gap: 4px;
    white-space: nowrap;
}

.btn-refresh:hover {
    background: #0056b3;
}

.btn-refresh:active {
    transform: scale(0.98);
}

.btn-export:hover {
    background: #218838;
}

.btn-export:active {
    transform: scale(0.98);
}

.btn-refresh .refresh-icon {
    display: inline-block;
    width: 10px;
    height: 10px;
    border: 1.5px solid white;
    border-top-color: transparent;
    border-radius: 50%;
    animation: spin 1s linear infinite;
    flex-shrink: 0;
}

@keyframes spin {
    to { transform: rotate(360deg); }
}

.summary-box {
    background: #fff3cd;
    border-left: 4px solid #ffc107;
    padding: 10px;
    margin-top: 10px;
    font-size: 11px;
}
</style>

<div class="statutory-container">
    <div class="header-box">
        <div class="row" style="display: flex; align-items: center; gap: 10px; flex-wrap: wrap;">
            <div class="field">
                <label>Filter:</label>
                <select name="filter_type" id="filter_type" class="form-control" onchange="toggleFilterOptions()" style="width: 110px; padding: 4px; border: 1px solid #ccc; border-radius: 3px; font-size: 11px;">
                    <option value="all" <?php echo $filterType == 'all' ? 'selected' : ''; ?>>All Employees</option>
                    <option value="year" <?php echo $filterType == 'year' ? 'selected' : ''; ?>>By Year</option>
                    <option value="month" <?php echo $filterType == 'month' ? 'selected' : ''; ?>>By Month</option>
                </select>
            </div>
            <div class="field" id="year_filter_field" style="display: <?php echo ($filterType == 'year' || $filterType == 'month') ? 'flex' : 'none'; ?>;">
                <label>Year:</label>
                <input type="number" name="year" id="filter_year" class="form-control" 
                       value="<?php echo htmlspecialchars($selectedYear); ?>" 
                       min="2000" max="2100" 
                       placeholder="2025"
                       onchange="applyFilter()" 
                       onkeypress="if(event.key==='Enter') applyFilter()"
                       style="width: 70px; padding: 4px; border: 1px solid #ccc; border-radius: 3px; font-size: 11px;">
            </div>
            <div class="field" id="month_filter_field" style="display: <?php echo $filterType == 'month' ? 'flex' : 'none'; ?>;">
                <label>Month:</label>
                <select name="month" id="filter_month" class="form-control" onchange="applyFilter()" style="width: 90px; padding: 4px; border: 1px solid #ccc; border-radius: 3px; font-size: 11px;">
                    <option value="01" <?php echo $selectedMonth == '01' ? 'selected' : ''; ?>>Jan</option>
                    <option value="02" <?php echo $selectedMonth == '02' ? 'selected' : ''; ?>>Feb</option>
                    <option value="03" <?php echo $selectedMonth == '03' ? 'selected' : ''; ?>>Mar</option>
                    <option value="04" <?php echo $selectedMonth == '04' ? 'selected' : ''; ?>>Apr</option>
                    <option value="05" <?php echo $selectedMonth == '05' ? 'selected' : ''; ?>>May</option>
                    <option value="06" <?php echo $selectedMonth == '06' ? 'selected' : ''; ?>>Jun</option>
                    <option value="07" <?php echo $selectedMonth == '07' ? 'selected' : ''; ?>>Jul</option>
                    <option value="08" <?php echo $selectedMonth == '08' ? 'selected' : ''; ?>>Aug</option>
                    <option value="09" <?php echo $selectedMonth == '09' ? 'selected' : ''; ?>>Sep</option>
                    <option value="10" <?php echo $selectedMonth == '10' ? 'selected' : ''; ?>>Oct</option>
                    <option value="11" <?php echo $selectedMonth == '11' ? 'selected' : ''; ?>>Nov</option>
                    <option value="12" <?php echo $selectedMonth == '12' ? 'selected' : ''; ?>>Dec</option>
                </select>
            </div>
            <div class="field">
                <label>Status:</label>
                <select name="status" id="filter_status" class="form-control" onchange="applyFilter()" style="width: 80px; padding: 4px; border: 1px solid #ccc; border-radius: 3px; font-size: 11px;">
                    <option value="all" <?php echo $statusFilter == 'all' || $statusFilter == '' ? 'selected' : ''; ?>>All</option>
                    <option value="active" <?php echo $statusFilter == 'active' ? 'selected' : ''; ?>>Active</option>
                    <option value="inactive" <?php echo $statusFilter == 'inactive' ? 'selected' : ''; ?>>Inactive</option>
                </select>
            </div>
            <div class="field">
                <label>Search:</label>
                <input type="text" name="search" id="search_input" class="form-control" 
                       value="<?php echo htmlspecialchars($search); ?>" 
                       placeholder="Name/Emp ID"
                       onkeypress="if(event.key==='Enter') applyFilter()"
                       style="width: 100px; padding: 4px; border: 1px solid #ccc; border-radius: 3px; font-size: 11px;">
            </div>
            
            <!-- To Name Input Field -->
            <form method="POST" class="field" style="display: inline-flex; align-items: center; gap: 4px; background: rgba(255,255,255,0.1); padding: 3px 6px; border-radius: 3px;">
                <label style="font-size: 11px; color: #fff;">To:</label>
                <input type="text" name="to_name" value="<?php echo htmlspecialchars($currentToName); ?>" 
                       placeholder="Name" 
                       style="padding: 3px 6px; border: 1px solid #ccc; border-radius: 3px; font-size: 11px; width: 80px; background: white; color: #333;">
                <button type="submit" name="save_to_name" 
                        style="padding: 3px 6px; font-size: 10px; background: #28a745; border: none; color: white; border-radius: 3px; cursor: pointer;" 
                        title="Save To Name">
                    ✓
                </button>
            </form>
            
            <button type="button" class="btn-export" onclick="exportToExcel()" title="Export to Excel" style="background: #28a745; color: white; border: none; padding: 4px 8px; border-radius: 3px; cursor: pointer; font-size: 11px; font-weight: 500; display: inline-flex; align-items: center; gap: 4px; white-space: nowrap;">
                <svg xmlns="http://www.w3.org/2000/svg" width="12" height="12" viewBox="0 0 24 24" fill="none" stroke="currentColor" stroke-width="2" stroke-linecap="round" stroke-linejoin="round"><path d="M21 15v4a2 2 0 0 1-2 2H5a2 2 0 0 1-2-2v-4"></path><polyline points="7 10 12 15 17 10"></polyline><line x1="12" y1="15" x2="12" y2="3"></line></svg>
                Export
            </button>
            
            <button type="button" class="btn-refresh" onclick="refreshData()" title="Refresh" style="background: #007bff; color: white; border: none; padding: 4px 8px; border-radius: 3px; cursor: pointer; font-size: 11px; font-weight: 500; display: inline-flex; align-items: center; gap: 4px; white-space: nowrap;">
                <span class="refresh-icon" style="width: 10px; height: 10px; border-width: 1.5px;"></span>
                Refresh
            </button>
        </div>
    </div>
    
    <?php if (isset($_SESSION['success_message'])): ?>
        <div class="alert alert-success" style="background: #d4edda; color: #155724; padding: 15px; border-radius: 4px; margin-bottom: 20px; border-left: 4px solid #28a745;">
            <strong>✓ Success:</strong> <?php echo htmlspecialchars($_SESSION['success_message']); unset($_SESSION['success_message']); ?>
        </div>
    <?php endif; ?>
    
    <?php if (isset($_SESSION['error_message'])): ?>
        <div class="alert alert-danger" style="background: #f8d7da; color: #721c24; padding: 15px; border-radius: 4px; margin-bottom: 20px; border-left: 4px solid #dc3545;">
            <strong>✗ Error:</strong> <?php echo htmlspecialchars($_SESSION['error_message']); unset($_SESSION['error_message']); ?>
        </div>
    <?php endif; ?>
    
    <?php if (isset($_GET['saved'])): ?>
        <div class="alert alert-info" style="background: #d1ecf1; color: #0c5460; padding: 15px; border-radius: 4px; margin-bottom: 20px; border-left: 4px solid #17a2b8;">
            <strong>ℹ Info:</strong> Data saved successfully! Click "Refresh Data" to see updated values.
        </div>
    <?php endif; ?>
    
    <?php 
    // Debug info (remove in production)
    if (isset($_GET['debug']) && isSuperAdmin()): 
        try {
            $debugStmt = $db->prepare("SELECT COUNT(*) as total, site_id FROM employees GROUP BY site_id");
            $debugStmt->execute();
            $debugData = $debugStmt->fetchAll();
        } catch (Exception $e) {
            $debugData = [];
        }
    ?>
        <div class="alert alert-info" style="background: #d1ecf1; color: #0c5460; padding: 10px; border-radius: 4px; margin-bottom: 20px;">
            <strong>Debug Info:</strong><br>
            Current Site ID: <?php echo $siteId; ?><br>
            Employees by Site: 
            <?php foreach ($debugData as $row): ?>
                Site <?php echo $row['site_id']; ?>: <?php echo $row['total']; ?> employees<br>
            <?php endforeach; ?>
            Total Employees Found: <?php echo count($employees); ?><br>
            Filter Type: <?php echo htmlspecialchars($filterType); ?><br>
            Filter Month: <?php echo htmlspecialchars($selectedMonth); ?><br>
            Filter Year: <?php echo htmlspecialchars($selectedYear); ?>
        </div>
    <?php endif; ?>
    
    <?php if ($filterType !== 'all' || !empty($statusFilter) || !empty($search)): ?>
        <div class="alert alert-info" style="background: #d1ecf1; color: #0c5460; padding: 10px; border-radius: 4px; margin-bottom: 20px; border-left: 4px solid #17a2b8;">
            <strong>ℹ Filter Active:</strong> 
            <?php 
            $filterParts = [];
            if ($filterType === 'month'): 
                $filterParts[] = 'Month: ' . date('F Y', strtotime($selectedYear . '-' . $selectedMonth . '-01'));
            elseif ($filterType === 'year'): 
                $filterParts[] = 'Year: ' . $selectedYear;
            endif;
            if ($statusFilter === 'active'): 
                $filterParts[] = 'Status: Active';
            elseif ($statusFilter === 'inactive'): 
                $filterParts[] = 'Status: Inactive';
            endif;
            if (!empty($search)): 
                $filterParts[] = 'Search: ' . htmlspecialchars($search);
            endif;
            echo implode(' | ', $filterParts);
            ?>
            (<?php echo number_format($totalRecords); ?> employee(s) found, Page <?php echo $page; ?> of <?php echo $totalPages; ?>)
        </div>
    <?php endif; ?>
    
    <div class="table-container" style="overflow-x: auto;">
        <table style="min-width: 2000px;">
            <thead>
                <tr>
                    <th rowspan="2" style="background: #1e3a5f; color: white;">Sl. no</th>
                    <th rowspan="2" style="background: #1e3a5f; color: white;">Emp ID</th>
                    <th rowspan="2" style="background: #1e3a5f; color: white;">Name</th>
                    <th rowspan="2" style="background: #1e3a5f; color: white;">Designation</th>
                    <th rowspan="2" style="background: #1e3a5f; color: white;">Category</th>
                    <th rowspan="2" style="background: #1e3a5f; color: white;">Department</th>
                    <th rowspan="2" style="background: #1e3a5f; color: white;">Employee Type</th>
                    <th rowspan="2" style="background: #1e3a5f; color: white;">Gender</th>
                    <th rowspan="2" style="background: #1e3a5f; color: white;">DOB</th>
                    <th rowspan="2" style="background: #1e3a5f; color: white;">DOJ</th>
                    <th rowspan="2" style="background: #1e3a5f; color: white;">ESIC NO</th>
                    <th rowspan="2" style="background: #1e3a5f; color: white;">UAN NO</th>
                    <th rowspan="2" style="background: #1e3a5f; color: white;">PAN No</th>
                    <th rowspan="2" style="background: #1e3a5f; color: white;">Aadhar number</th>
                    <th colspan="6" class="earnings-section">Earn</th>
                    <th colspan="5" class="deduction-header">Deduction</th>
                    <th rowspan="2" class="deduction-header">NET PAYABLE</th>
                    <th colspan="7" class="payment-header">Payment</th>
                    <th rowspan="2" style="background: #1e3a5f; color: white;">Action</th>
                </tr>
                <tr>
                    <th class="earn-header">Basic Salary</th>
                    <th class="earn-header">HRA</th>
                    <th class="earn-header">DA</th>
                    <th class="earn-header">Special Allowances</th>
                    <th class="earn-header">TA</th>
                    <th class="earn-header">TOTAL</th>
                    <th class="deduction-header">Contribution to PF</th>
                    <th class="deduction-header">ESIC</th>
                    <th class="deduction-header">Professional Tax</th>
                    <th class="deduction-header">TDS</th>
                    <th class="deduction-header">TOTAL</th>
                    <th class="payment-header">Pay Mode</th>
                    <th class="payment-header">Bank Mode</th>
                    <th class="payment-header">Bank Name</th>
                    <th class="payment-header">Bank A/c No</th>
                    <th class="payment-header">IFSC Code</th>
                    <th class="payment-header">Status</th>
                    <th class="payment-header">Remarks</th>
                </tr>
            </thead>
            <tbody>
                <?php 
                // Debug: Check if employees array is empty
                if (empty($employees)): 
                    // Try to get count for debugging
                    try {
                        $countStmt = $db->prepare("SELECT COUNT(*) FROM employees WHERE site_id = ?");
                        $countStmt->execute([$siteId]);
                        $totalCount = $countStmt->fetchColumn();
                    } catch (Exception $e) {
                        $totalCount = 0;
                    }
                ?>
                    <tr>
                        <td colspan="32" style="text-align: center; padding: 20px;">
                            No employees found for this site. 
                            <?php if ($totalCount > 0): ?>
                                <br><small style="color: #666;">(Total employees in database: <?php echo $totalCount; ?>, Current Site ID: <?php echo $siteId; ?>)</small>
                            <?php endif; ?>
                            <br><a href="<?php echo BASE_URL; ?>/modules/employee/create.php">Add Employee</a>
                        </td>
                    </tr>
                <?php else: ?>
                    <?php foreach ($employees as $index => $emp): 
                        $basic = (float)($emp['basic_salary'] ?? 0);
                        $hra = (float)($emp['hra'] ?? 0);
                        $da = (float)($emp['da'] ?? 0);
                        $specialAllowance = (float)($emp['special_allowance'] ?? 0);
                        $ta = (float)($emp['ta'] ?? 0);
                        $totalEarnings = $basic + $hra + $da + $specialAllowance + $ta;
                        
                        // Deductions
                        $pfDeduction = round(min($basic, 15000) * 0.12, 2);
                        $esicDeduction = $totalEarnings <= 21000 ? round($totalEarnings * 0.0175, 2) : 0;
                        $professionalTax = $totalEarnings < 13304 ? 0 : ($totalEarnings < 25000 ? 125 : 200);
                        $tds = 0; // Calculate based on tax slab
                        $totalDeductions = $pfDeduction + $esicDeduction + $professionalTax + $tds;
                        $netPayable = $totalEarnings - $totalDeductions;
                        
                        $fullName = trim(($emp['first_name'] ?? '') . ' ' . ($emp['middle_name'] ?? '') . ' ' . ($emp['last_name'] ?? ''));
                    ?>
                    <tr>
                        <td><?php echo $index + 1; ?></td>
                        <td><?php echo htmlspecialchars($emp['employee_code'] ?? ''); ?></td>
                        <td class="highlight"><?php echo htmlspecialchars($fullName); ?></td>
                        <td>
                            <select name="designation_<?php echo $emp['employee_id']; ?>" form="form_<?php echo $emp['employee_id']; ?>" class="editable" style="width: 120px;">
                                <option value="">Select</option>
                                <?php
                                $deptStmt = $db->prepare("SELECT designation_id, designation_name FROM designations WHERE site_id = ? ORDER BY designation_name");
                                $deptStmt->execute([$siteId]);
                                $allDesignations = $deptStmt->fetchAll();
                                foreach ($allDesignations as $desig): ?>
                                <option value="<?php echo $desig['designation_id']; ?>" <?php echo ($emp['designation_id'] ?? 0) == $desig['designation_id'] ? 'selected' : ''; ?>>
                                    <?php echo htmlspecialchars($desig['designation_name']); ?>
                                </option>
                                <?php endforeach; ?>
                            </select>
                        </td>
                        <td>
                            <select name="category_<?php echo $emp['employee_id']; ?>" form="form_<?php echo $emp['employee_id']; ?>" class="editable" style="width: 120px;">
                                <option value="Unskilled" <?php echo ($emp['employee_category'] ?? '') == 'Unskilled' ? 'selected' : ''; ?>>Unskilled</option>
                                <option value="Semi-Skilled" <?php echo ($emp['employee_category'] ?? '') == 'Semi-Skilled' ? 'selected' : ''; ?>>Semi-Skilled</option>
                                <option value="Skilled" <?php echo ($emp['employee_category'] ?? '') == 'Skilled' ? 'selected' : ''; ?>>Skilled</option>
                                <option value="Highly Skilled" <?php echo ($emp['employee_category'] ?? '') == 'Highly Skilled' ? 'selected' : ''; ?>>Highly Skilled</option>
                            </select>
                        </td>
                        <td>
                            <select name="department_<?php echo $emp['employee_id']; ?>" form="form_<?php echo $emp['employee_id']; ?>" class="editable" style="width: 120px;">
                                <option value="">Select</option>
                                <?php
                                $deptStmt = $db->prepare("SELECT department_id, department_name FROM departments WHERE site_id = ? ORDER BY department_name");
                                $deptStmt->execute([$siteId]);
                                $allDepartments = $deptStmt->fetchAll();
                                foreach ($allDepartments as $dept): ?>
                                <option value="<?php echo $dept['department_id']; ?>" <?php echo ($emp['department_id'] ?? 0) == $dept['department_id'] ? 'selected' : ''; ?>>
                                    <?php echo htmlspecialchars($dept['department_name']); ?>
                                </option>
                                <?php endforeach; ?>
                            </select>
                        </td>
                        <td>
                            <select name="emp_type_<?php echo $emp['employee_id']; ?>" form="form_<?php echo $emp['employee_id']; ?>" class="editable" style="width: 100px;">
                                <option value="full_time" <?php echo ($emp['employment_type'] ?? '') == 'full_time' ? 'selected' : ''; ?>>Regular</option>
                                <option value="contract" <?php echo ($emp['employment_type'] ?? '') == 'contract' ? 'selected' : ''; ?>>FTC</option>
                                <option value="part_time" <?php echo ($emp['employment_type'] ?? '') == 'part_time' ? 'selected' : ''; ?>>Part Time</option>
                                <option value="temporary" <?php echo ($emp['employment_type'] ?? '') == 'temporary' ? 'selected' : ''; ?>>Temporary</option>
                            </select>
                        </td>
                        <td>
                            <select name="gender_<?php echo $emp['employee_id']; ?>" form="form_<?php echo $emp['employee_id']; ?>" class="editable" style="width: 80px;">
                                <option value="male" <?php echo ($emp['gender'] ?? '') == 'male' ? 'selected' : ''; ?>>Male</option>
                                <option value="female" <?php echo ($emp['gender'] ?? '') == 'female' ? 'selected' : ''; ?>>Female</option>
                                <option value="other" <?php echo ($emp['gender'] ?? '') == 'other' ? 'selected' : ''; ?>>Other</option>
                            </select>
                        </td>
                        <td>
                            <input type="date" name="dob_<?php echo $emp['employee_id']; ?>" 
                                   value="<?php echo htmlspecialchars($emp['date_of_birth'] ?? ''); ?>"
                                   form="form_<?php echo $emp['employee_id']; ?>" class="editable" style="width: 120px;">
                        </td>
                        <td>
                            <input type="date" name="doj_<?php echo $emp['employee_id']; ?>" 
                                   value="<?php echo htmlspecialchars($emp['date_of_joining'] ?? ''); ?>"
                                   form="form_<?php echo $emp['employee_id']; ?>" class="editable" style="width: 120px;">
                        </td>
                        <td>
                            <input type="text" name="esic_no_<?php echo $emp['employee_id']; ?>" 
                                   value="<?php echo htmlspecialchars($emp['esic_number'] ?? ''); ?>"
                                   form="form_<?php echo $emp['employee_id']; ?>" class="editable" style="width: 120px;">
                        </td>
                        <td>
                            <input type="text" name="uan_no_<?php echo $emp['employee_id']; ?>" 
                                   value="<?php echo htmlspecialchars($emp['uan_number'] ?? ''); ?>"
                                   form="form_<?php echo $emp['employee_id']; ?>" class="editable" style="width: 120px;">
                        </td>
                        <td>
                            <input type="text" name="pan_no_<?php echo $emp['employee_id']; ?>" 
                                   value="<?php echo htmlspecialchars($emp['pan_number'] ?? ''); ?>"
                                   form="form_<?php echo $emp['employee_id']; ?>" class="editable" style="width: 120px;" maxlength="10">
                        </td>
                        <td>
                            <input type="text" name="aadhar_<?php echo $emp['employee_id']; ?>" 
                                   value="<?php echo htmlspecialchars($emp['aadhar_number'] ?? ''); ?>"
                                   form="form_<?php echo $emp['employee_id']; ?>" class="editable" style="width: 120px;" maxlength="12">
                        </td>
                        <!-- Earnings -->
                        <td class="earnings-section"><?php echo number_format($basic, 2); ?></td>
                        <td class="earnings-section"><?php echo number_format($hra, 2); ?></td>
                        <td class="earnings-section"><?php echo number_format($da, 2); ?></td>
                        <td class="earnings-section"><?php echo number_format($specialAllowance, 2); ?></td>
                        <td class="earnings-section"><?php echo number_format($ta, 2); ?></td>
                        <td class="earnings-section" style="background: #1e3a5f; color: white; font-weight: bold;"><?php echo number_format($totalEarnings, 2); ?></td>
                        <!-- Deductions -->
                        <td class="deductions-section"><?php echo number_format($pfDeduction, 2); ?></td>
                        <td class="deductions-section"><?php echo number_format($esicDeduction, 2); ?></td>
                        <td class="deductions-section"><?php echo number_format($professionalTax, 2); ?></td>
                        <td class="deductions-section"><?php echo number_format($tds, 2); ?></td>
                        <td class="deductions-section" style="background: #1e3a5f; color: white; font-weight: bold;"><?php echo number_format($totalDeductions, 2); ?></td>
                        <!-- Net Payable -->
                        <td class="deduction-header" style="font-weight: bold;"><?php echo number_format($netPayable, 2); ?></td>
                        <!-- Payment -->
                        <td>
                            <select name="pay_mode_<?php echo $emp['employee_id']; ?>" form="form_<?php echo $emp['employee_id']; ?>" class="editable" style="width: 100px;">
                                <option value="">Select</option>
                                <option value="Cash" <?php echo (isset($emp['pay_mode']) && $emp['pay_mode'] === 'Cash') ? 'selected' : ''; ?>>Cash</option>
                                <option value="Bank Transfer" <?php echo (isset($emp['pay_mode']) && $emp['pay_mode'] === 'Bank Transfer') ? 'selected' : ''; ?>>Bank Transfer</option>
                                <option value="Cheque" <?php echo (isset($emp['pay_mode']) && $emp['pay_mode'] === 'Cheque') ? 'selected' : ''; ?>>Cheque</option>
                            </select>
                        </td>
                        <td>
                            <select name="bank_mode_<?php echo $emp['employee_id']; ?>" form="form_<?php echo $emp['employee_id']; ?>" class="editable" style="width: 100px;">
                                <option value="NEFT" <?php echo (!isset($emp['bank_mode']) || $emp['bank_mode'] === 'NEFT') ? 'selected' : ''; ?>>NEFT</option>
                                <option value="RTGS" <?php echo (isset($emp['bank_mode']) && $emp['bank_mode'] === 'RTGS') ? 'selected' : ''; ?>>RTGS</option>
                                <option value="IMPS" <?php echo (isset($emp['bank_mode']) && $emp['bank_mode'] === 'IMPS') ? 'selected' : ''; ?>>IMPS</option>
                                <option value="UPI" <?php echo (isset($emp['bank_mode']) && $emp['bank_mode'] === 'UPI') ? 'selected' : ''; ?>>UPI</option>
                            </select>
                        </td>
                        <td>
                            <input type="text" name="bank_name_<?php echo $emp['employee_id']; ?>" 
                                   value="<?php echo htmlspecialchars($emp['bank_name'] ?? ''); ?>"
                                   form="form_<?php echo $emp['employee_id']; ?>" class="editable" style="width: 150px;">
                        </td>
                        <td>
                            <input type="text" name="bank_ac_<?php echo $emp['employee_id']; ?>" 
                                   value="<?php echo htmlspecialchars($emp['bank_account_number'] ?? ''); ?>"
                                   form="form_<?php echo $emp['employee_id']; ?>" class="editable" style="width: 150px;">
                        </td>
                        <td>
                            <input type="text" name="ifsc_<?php echo $emp['employee_id']; ?>" 
                                   value="<?php echo htmlspecialchars($emp['bank_ifsc'] ?? ''); ?>"
                                   form="form_<?php echo $emp['employee_id']; ?>" class="editable" style="width: 120px;">
                        </td>
                        <td>
                            <select name="status_<?php echo $emp['employee_id']; ?>" form="form_<?php echo $emp['employee_id']; ?>" class="editable" style="width: 100px;">
                                <option value="active" <?php echo ($emp['employment_status'] ?? '') == 'active' ? 'selected' : ''; ?>>Active</option>
                                <option value="inactive" <?php echo ($emp['employment_status'] ?? '') == 'inactive' ? 'selected' : ''; ?>>Inactive</option>
                            </select>
                        </td>
                        <td>
                            <input type="text" name="remarks_<?php echo $emp['employee_id']; ?>"
                                   value="<?php echo htmlspecialchars($emp['remarks'] ?? ''); ?>"
                                   form="form_<?php echo $emp['employee_id']; ?>" class="editable" style="width: 150px;" placeholder="Remarks">
                        </td>
                        <td>
                            <form id="form_<?php echo $emp['employee_id']; ?>" method="POST" style="display: inline;">
                                <input type="hidden" name="employee_id" value="<?php echo $emp['employee_id']; ?>">
                                <button type="submit" name="save_statutory" class="btn-save">Save</button>
                            </form>
                        </td>
                    </tr>
                    <?php endforeach; ?>
                <?php endif; ?>
            </tbody>
        </table>
    </div>
    
    <!-- Pagination -->
    <?php if ($totalPages > 1): ?>
    <div class="table-footer" style="margin-top: 20px; padding: 15px; background: #f8f9fa; border-radius: 4px; display: flex; justify-content: space-between; align-items: center; flex-wrap: wrap; gap: 10px;">
        <div class="table-info" style="font-size: 14px; color: #666;">
            Showing <?php echo $offset + 1; ?> to <?php echo min($offset + $perPage, $totalRecords); ?> of <?php echo number_format($totalRecords); ?> employees
        </div>
        <div class="pagination" style="display: flex; gap: 10px; align-items: center;">
            <?php if ($page > 1): ?>
            <a href="?<?php 
                $prevParams = $_GET;
                $prevParams['page'] = $page - 1;
                echo http_build_query($prevParams);
            ?>" class="btn btn-sm" style="padding: 8px 16px; background: #007bff; color: white; text-decoration: none; border-radius: 4px; border: none; cursor: pointer; font-weight: bold;">← Previous</a>
            <?php endif; ?>
            <span class="pagination-info" style="font-size: 14px; color: #666; padding: 0 10px;">Page <?php echo $page; ?> of <?php echo $totalPages; ?></span>
            <?php if ($page < $totalPages): ?>
            <a href="?<?php 
                $nextParams = $_GET;
                $nextParams['page'] = $page + 1;
                echo http_build_query($nextParams);
            ?>" class="btn btn-sm" style="padding: 8px 16px; background: #007bff; color: white; text-decoration: none; border-radius: 4px; border: none; cursor: pointer; font-weight: bold;">Next →</a>
            <?php endif; ?>
        </div>
    </div>
    <?php endif; ?>
</div>

<!-- Store department and designation options for JavaScript -->
<script>
// Store options for dynamic table updates
window.designationOptions = <?php
    try {
        $optStmt = $db->prepare("SELECT designation_id, designation_name FROM designations WHERE site_id = ? ORDER BY designation_name");
        $optStmt->execute([$siteId]);
        $designations = $optStmt->fetchAll(PDO::FETCH_ASSOC);
        $designationOptions = [];
        foreach ($designations as $desig) {
            $designationOptions[] = ['id' => $desig['designation_id'], 'name' => $desig['designation_name']];
        }
        echo json_encode($designationOptions);
    } catch (Exception $e) {
        echo '[]';
    }
?>;
window.departmentOptions = <?php
    try {
        $optStmt = $db->prepare("SELECT department_id, department_name FROM departments WHERE site_id = ? ORDER BY department_name");
        $optStmt->execute([$siteId]);
        $departments = $optStmt->fetchAll(PDO::FETCH_ASSOC);
        $departmentOptions = [];
        foreach ($departments as $dept) {
            $departmentOptions[] = ['id' => $dept['department_id'], 'name' => $dept['department_name']];
        }
        echo json_encode($departmentOptions);
    } catch (Exception $e) {
        echo '[]';
    }
?>;
</script>

<script>
// Real-time API endpoint
const API_ENDPOINT = '<?php echo BASE_URL; ?>/api/get_employees_statutory.php';

function toggleFilterOptions() {
    const filterType = document.getElementById('filter_type')?.value || 'all';
    const yearField = document.getElementById('year_filter_field');
    const monthField = document.getElementById('month_filter_field');
    
    if (filterType === 'all') {
        if (yearField) yearField.style.display = 'none';
        if (monthField) monthField.style.display = 'none';
    } else if (filterType === 'year') {
        if (yearField) yearField.style.display = 'flex';
        if (monthField) monthField.style.display = 'none';
    } else if (filterType === 'month') {
        if (yearField) yearField.style.display = 'flex';
        if (monthField) monthField.style.display = 'flex';
    }
    
    // Apply filter after toggling
    applyFilter();
}

function exportToExcel() {
    const btn = document.querySelector('.btn-export');
    if (!btn) return;
    
    const originalText = btn.innerHTML;
    btn.disabled = true;
    btn.innerHTML = '<svg xmlns="http://www.w3.org/2000/svg" width="14" height="14" viewBox="0 0 24 24" fill="none" stroke="currentColor" stroke-width="2" stroke-linecap="round" stroke-linejoin="round"><path d="M21 15v4a2 2 0 0 1-2 2H5a2 2 0 0 1-2-2v-4"></path><polyline points="7 10 12 15 17 10"></polyline><line x1="12" y1="15" x2="12" y2="3"></line></svg> Exporting...';
    
    // Get current filter values
    const filterType = document.getElementById('filter_type')?.value || 'all';
    const monthField = document.getElementById('filter_month');
    const yearField = document.getElementById('filter_year');
    const statusField = document.getElementById('filter_status');
    const searchField = document.getElementById('search_input');
    
    const month = (monthField && monthField.offsetParent !== null) ? monthField.value : '<?php echo $selectedMonth; ?>';
    let year = (yearField && yearField.offsetParent !== null) ? yearField.value.trim() : '<?php echo $selectedYear; ?>';
    const status = statusField ? statusField.value : 'all';
    const search = searchField ? searchField.value.trim() : '';
    
    // Build export URL with current filters
    let exportUrl = window.location.pathname + '?export=excel&filter_type=' + filterType;
    if (filterType === 'month') {
        exportUrl += '&month=' + month + '&year=' + year;
    } else if (filterType === 'year') {
        exportUrl += '&year=' + year;
    }
    if (status && status !== 'all') {
        exportUrl += '&status=' + status;
    }
    if (search) {
        exportUrl += '&search=' + encodeURIComponent(search);
    }
    
    // Trigger download
    window.location.href = exportUrl;
    
    // Reset button after a short delay
    setTimeout(function() {
        btn.disabled = false;
        btn.innerHTML = originalText;
    }, 2000);
}

function applyFilter() {
    const filterType = document.getElementById('filter_type')?.value || 'all';
    const monthField = document.getElementById('filter_month');
    const yearField = document.getElementById('filter_year');
    const statusField = document.getElementById('filter_status');
    const searchField = document.getElementById('search_input');
    
    // Get values only if fields exist and are visible
    const month = (monthField && monthField.offsetParent !== null) ? monthField.value : '<?php echo date('m'); ?>';
    let year = (yearField && yearField.offsetParent !== null) ? yearField.value.trim() : '<?php echo date('Y'); ?>';
    const status = statusField ? statusField.value : 'all';
    const search = searchField ? searchField.value.trim() : '';
    
    // Validate year
    if (year && (isNaN(year) || year < 2000 || year > 2100)) {
        alert('Please enter a valid year between 2000 and 2100');
        if (yearField) {
            yearField.focus();
            yearField.value = '<?php echo date('Y'); ?>';
        }
        return;
    }
    
    // If year is empty, use current year
    if (!year || year === '') {
        year = '<?php echo date('Y'); ?>';
        if (yearField) {
            yearField.value = year;
        }
    }
    
    // Show loading indicator
    const tableContainer = document.querySelector('.table-container');
    if (tableContainer) {
        tableContainer.style.opacity = '0.5';
        tableContainer.style.pointerEvents = 'none';
    }
    
    // Reload page with filter parameters
    const url = new URL(window.location.href);
    url.searchParams.set('filter_type', filterType);
    
    if (filterType === 'month') {
        url.searchParams.set('month', month);
        url.searchParams.set('year', year);
    } else if (filterType === 'year') {
        url.searchParams.set('year', year);
        url.searchParams.delete('month');
    } else {
        url.searchParams.delete('month');
        url.searchParams.delete('year');
    }
    
    // Add status filter
    if (status && status !== 'all') {
        url.searchParams.set('status', status);
    } else {
        url.searchParams.delete('status');
    }
    
    // Add search filter
    if (search) {
        url.searchParams.set('search', search);
    } else {
        url.searchParams.delete('search');
    }
    
    // Reset to page 1 when filters change
    url.searchParams.set('page', '1');
    
    // Remove debug and saved parameters
    url.searchParams.delete('debug');
    url.searchParams.delete('saved');
    
    // Reload page
    window.location.href = url.toString();
}

function refreshData() {
    const btn = document.querySelector('.btn-refresh');
    if (!btn) return;
    
    const originalText = btn.innerHTML;
    btn.disabled = true;
    btn.innerHTML = '<span class="refresh-icon"></span> Refreshing...';
    
    // Get current filter values
    const filterType = document.getElementById('filter_type')?.value || 'all';
    const monthField = document.getElementById('filter_month');
    const yearField = document.getElementById('filter_year');
    const statusField = document.getElementById('filter_status');
    const searchField = document.getElementById('search_input');
    
    const month = (monthField && monthField.offsetParent !== null) ? monthField.value : '<?php echo $selectedMonth; ?>';
    let year = (yearField && yearField.offsetParent !== null) ? yearField.value.trim() : '<?php echo $selectedYear; ?>';
    const status = statusField ? statusField.value : 'all';
    const search = searchField ? searchField.value.trim() : '';
    
    // Validate and set default year if empty or invalid
    if (!year || year === '' || isNaN(year) || year < 2000 || year > 2100) {
        year = '<?php echo $selectedYear; ?>';
        if (yearField) {
            yearField.value = year;
        }
    }
    
    // Build API URL with filters
    let apiUrl = API_ENDPOINT + '?filter_type=' + filterType;
    if (filterType === 'month') {
        apiUrl += '&month=' + month + '&year=' + year;
    } else if (filterType === 'year') {
        apiUrl += '&year=' + year;
    }
    if (status && status !== 'all') {
        apiUrl += '&status=' + status;
    }
    if (search) {
        apiUrl += '&search=' + encodeURIComponent(search);
    }
    apiUrl += '&t=' + Date.now();
    
    // Fetch data from API with filters
    fetch(apiUrl, {
        method: 'GET',
        headers: {
            'Accept': 'application/json',
        },
        credentials: 'same-origin'
    })
    .then(response => response.json())
    .then(data => {
        if (data.success) {
            // Update department and designation options if provided
            if (data.departments) {
                window.departmentOptions = data.departments.map(d => ({id: d.department_id, name: d.department_name}));
            }
            if (data.designations) {
                window.designationOptions = data.designations.map(d => ({id: d.designation_id, name: d.designation_name}));
            }
            updateTable(data.employees);
            showSuccessMessage('✓ Data refreshed successfully! (' + data.count + ' employees)');
        } else {
            showErrorMessage('Error: ' + (data.message || 'Failed to fetch data'));
        }
    })
    .catch(error => {
        console.error('Error:', error);
        showErrorMessage('Error fetching data. Please try again.');
    })
    .finally(() => {
        btn.disabled = false;
        btn.innerHTML = originalText;
    });
}

function updateTable(employees) {
    const tbody = document.querySelector('.table-container tbody');
    if (!tbody) return;
    
    // Clear existing rows (except header)
    const existingRows = tbody.querySelectorAll('tr');
    existingRows.forEach(row => row.remove());
    
    if (employees.length === 0) {
        tbody.innerHTML = '<tr><td colspan="32" style="text-align: center; padding: 20px;">No employees found. <a href="<?php echo BASE_URL; ?>/modules/employee/create.php">Add Employee</a></td></tr>';
        return;
    }
    
    // Build new rows
    employees.forEach((emp, index) => {
        const basic = parseFloat(emp.basic_salary || 0);
        const hra = parseFloat(emp.hra || 0);
        const da = parseFloat(emp.da || 0);
        const specialAllowance = parseFloat(emp.special_allowance || 0);
        const ta = parseFloat(emp.ta || 0);
        const totalEarnings = basic + hra + da + specialAllowance + ta;
        
        // Deductions
        const pfDeduction = Math.round(Math.min(basic, 15000) * 0.12 * 100) / 100;
        const esicDeduction = totalEarnings <= 21000 ? Math.round(totalEarnings * 0.0175 * 100) / 100 : 0;
        const professionalTax = totalEarnings < 13304 ? 0 : (totalEarnings < 25000 ? 125 : 200);
        const tds = 0;
        const totalDeductions = pfDeduction + esicDeduction + professionalTax + tds;
        const netPayable = totalEarnings - totalDeductions;
        
        const fullName = [emp.first_name || '', emp.middle_name || '', emp.last_name || ''].filter(Boolean).join(' ');
        const panNumber = emp.pan_number || '';
        const aadharNumber = emp.aadhar_number || '';
        const remarks = emp.remarks || '';
        
        const row = document.createElement('tr');
        row.innerHTML = `
            <td>${index + 1}</td>
            <td>${escapeHtml(emp.employee_code || '')}</td>
            <td class="highlight">${escapeHtml(fullName)}</td>
            <td>
                <select name="designation_${emp.employee_id}" form="form_${emp.employee_id}" class="editable" style="width: 120px;">
                    <option value="">Select</option>
                    ${getDesignationOptions(emp.designation_id)}
                </select>
            </td>
            <td>
                <select name="category_${emp.employee_id}" form="form_${emp.employee_id}" class="editable" style="width: 120px;">
                    <option value="Unskilled" ${(emp.employee_category || '') === 'Unskilled' ? 'selected' : ''}>Unskilled</option>
                    <option value="Semi-Skilled" ${(emp.employee_category || '') === 'Semi-Skilled' ? 'selected' : ''}>Semi-Skilled</option>
                    <option value="Skilled" ${(emp.employee_category || '') === 'Skilled' ? 'selected' : ''}>Skilled</option>
                    <option value="Highly Skilled" ${(emp.employee_category || '') === 'Highly Skilled' ? 'selected' : ''}>Highly Skilled</option>
                </select>
            </td>
            <td>
                <select name="department_${emp.employee_id}" form="form_${emp.employee_id}" class="editable" style="width: 120px;">
                    <option value="">Select</option>
                    ${getDepartmentOptions(emp.department_id)}
                </select>
            </td>
            <td>
                <select name="emp_type_${emp.employee_id}" form="form_${emp.employee_id}" class="editable" style="width: 100px;">
                    <option value="full_time" ${(emp.employment_type || '') === 'full_time' ? 'selected' : ''}>Regular</option>
                    <option value="contract" ${(emp.employment_type || '') === 'contract' ? 'selected' : ''}>FTC</option>
                    <option value="part_time" ${(emp.employment_type || '') === 'part_time' ? 'selected' : ''}>Part Time</option>
                    <option value="temporary" ${(emp.employment_type || '') === 'temporary' ? 'selected' : ''}>Temporary</option>
                </select>
            </td>
            <td>
                <select name="gender_${emp.employee_id}" form="form_${emp.employee_id}" class="editable" style="width: 80px;">
                    <option value="male" ${(emp.gender || '') === 'male' ? 'selected' : ''}>Male</option>
                    <option value="female" ${(emp.gender || '') === 'female' ? 'selected' : ''}>Female</option>
                    <option value="other" ${(emp.gender || '') === 'other' ? 'selected' : ''}>Other</option>
                </select>
            </td>
            <td>
                <input type="date" name="dob_${emp.employee_id}" value="${emp.date_of_birth || ''}" form="form_${emp.employee_id}" class="editable" style="width: 120px;">
            </td>
            <td>
                <input type="date" name="doj_${emp.employee_id}" value="${emp.date_of_joining || ''}" form="form_${emp.employee_id}" class="editable" style="width: 120px;">
            </td>
            <td>
                <input type="text" name="esic_no_${emp.employee_id}" value="${escapeHtml(emp.esic_number || '')}" form="form_${emp.employee_id}" class="editable" style="width: 120px;">
            </td>
            <td>
                <input type="text" name="uan_no_${emp.employee_id}" value="${escapeHtml(emp.uan_number || '')}" form="form_${emp.employee_id}" class="editable" style="width: 120px;">
            </td>
            <td>
                <input type="text" name="pan_no_${emp.employee_id}" value="${escapeHtml(panNumber)}" form="form_${emp.employee_id}" class="editable" style="width: 120px;" maxlength="10">
            </td>
            <td>
                <input type="text" name="aadhar_${emp.employee_id}" value="${escapeHtml(aadharNumber)}" form="form_${emp.employee_id}" class="editable" style="width: 120px;" maxlength="12">
            </td>
            <td class="earnings-section">${formatNumber(basic)}</td>
            <td class="earnings-section">${formatNumber(hra)}</td>
            <td class="earnings-section">${formatNumber(da)}</td>
            <td class="earnings-section">${formatNumber(specialAllowance)}</td>
            <td class="earnings-section">${formatNumber(ta)}</td>
            <td class="earnings-section" style="background: #1e3a5f; color: white; font-weight: bold;">${formatNumber(totalEarnings)}</td>
            <td class="deductions-section">${formatNumber(pfDeduction)}</td>
            <td class="deductions-section">${formatNumber(esicDeduction)}</td>
            <td class="deductions-section">${formatNumber(professionalTax)}</td>
            <td class="deductions-section">${formatNumber(tds)}</td>
            <td class="deductions-section" style="background: #1e3a5f; color: white; font-weight: bold;">${formatNumber(totalDeductions)}</td>
            <td class="deduction-header" style="font-weight: bold;">${formatNumber(netPayable)}</td>
            <td>
                <select name="pay_mode_${emp.employee_id}" form="form_${emp.employee_id}" class="editable" style="width: 100px;">
                    <option value="">Select</option>
                    <option value="Cash" ${(emp.pay_mode || '') === 'Cash' ? 'selected' : ''}>Cash</option>
                    <option value="Bank Transfer" ${(emp.pay_mode || '') === 'Bank Transfer' ? 'selected' : ''}>Bank Transfer</option>
                    <option value="Cheque" ${(emp.pay_mode || '') === 'Cheque' ? 'selected' : ''}>Cheque</option>
                </select>
            </td>
            <td>
                <select name="bank_mode_${emp.employee_id}" form="form_${emp.employee_id}" class="editable" style="width: 100px;">
                    <option value="NEFT" ${(!emp.bank_mode || emp.bank_mode === 'NEFT') ? 'selected' : ''}>NEFT</option>
                    <option value="RTGS" ${(emp.bank_mode || '') === 'RTGS' ? 'selected' : ''}>RTGS</option>
                    <option value="IMPS" ${(emp.bank_mode || '') === 'IMPS' ? 'selected' : ''}>IMPS</option>
                    <option value="UPI" ${(emp.bank_mode || '') === 'UPI' ? 'selected' : ''}>UPI</option>
                </select>
            </td>
            <td>
                <input type="text" name="bank_name_${emp.employee_id}" value="${escapeHtml(emp.bank_name || '')}" form="form_${emp.employee_id}" class="editable" style="width: 150px;">
            </td>
            <td>
                <input type="text" name="bank_ac_${emp.employee_id}" value="${escapeHtml(emp.bank_account_number || '')}" form="form_${emp.employee_id}" class="editable" style="width: 150px;">
            </td>
            <td>
                <input type="text" name="ifsc_${emp.employee_id}" value="${escapeHtml(emp.bank_ifsc || '')}" form="form_${emp.employee_id}" class="editable" style="width: 120px;">
            </td>
            <td>
                <select name="status_${emp.employee_id}" form="form_${emp.employee_id}" class="editable" style="width: 100px;">
                    <option value="active" ${(emp.employment_status || '') === 'active' ? 'selected' : ''}>Active</option>
                    <option value="inactive" ${(emp.employment_status || '') === 'inactive' ? 'selected' : ''}>Inactive</option>
                </select>
            </td>
            <td>
                <input type="text" name="remarks_${emp.employee_id}" value="${escapeHtml(remarks)}" form="form_${emp.employee_id}" class="editable" style="width: 150px;" placeholder="Remarks">
            </td>
            <td>
                <form id="form_${emp.employee_id}" method="POST" style="display: inline;">
                    <input type="hidden" name="employee_id" value="${emp.employee_id}">
                    <button type="submit" name="save_statutory" class="btn-save">Save</button>
                </form>
            </td>
        `;
        tbody.appendChild(row);
    });
    
    // Scroll to top
    window.scrollTo(0, 0);
}

function escapeHtml(text) {
    const div = document.createElement('div');
    div.textContent = text;
    return div.innerHTML;
}

function formatNumber(num) {
    return parseFloat(num || 0).toFixed(2).replace(/\B(?=(\d{3})+(?!\d))/g, ",");
}

function getDesignationOptions(selectedId) {
    if (!window.designationOptions) return '<option value="">Select</option>';
    let options = '<option value="">Select</option>';
    window.designationOptions.forEach(desig => {
        const selected = (selectedId == desig.id) ? 'selected' : '';
        options += `<option value="${desig.id}" ${selected}>${escapeHtml(desig.name)}</option>`;
    });
    return options;
}

function getDepartmentOptions(selectedId) {
    if (!window.departmentOptions) return '<option value="">Select</option>';
    let options = '<option value="">Select</option>';
    window.departmentOptions.forEach(dept => {
        const selected = (selectedId == dept.id) ? 'selected' : '';
        options += `<option value="${dept.id}" ${selected}>${escapeHtml(dept.name)}</option>`;
    });
    return options;
}

function showSuccessMessage(message) {
    const successMsg = document.createElement('div');
    successMsg.style.cssText = 'position: fixed; top: 20px; right: 20px; background: #28a745; color: white; padding: 15px 20px; border-radius: 5px; z-index: 9999; box-shadow: 0 4px 6px rgba(0,0,0,0.1); font-weight: bold;';
    successMsg.innerHTML = message;
    document.body.appendChild(successMsg);
    
    setTimeout(function() {
        successMsg.style.transition = 'opacity 0.5s';
        successMsg.style.opacity = '0';
        setTimeout(function() {
            successMsg.remove();
        }, 500);
    }, 3000);
}

function showErrorMessage(message) {
    const errorMsg = document.createElement('div');
    errorMsg.style.cssText = 'position: fixed; top: 20px; right: 20px; background: #dc3545; color: white; padding: 15px 20px; border-radius: 5px; z-index: 9999; box-shadow: 0 4px 6px rgba(0,0,0,0.1); font-weight: bold;';
    errorMsg.innerHTML = message;
    document.body.appendChild(errorMsg);
    
    setTimeout(function() {
        errorMsg.style.transition = 'opacity 0.5s';
        errorMsg.style.opacity = '0';
        setTimeout(function() {
            errorMsg.remove();
        }, 500);
    }, 3000);
}

// Store department and designation options on page load
document.addEventListener('DOMContentLoaded', function() {
    // Store options from existing selects for use in dynamic rows
    window.designationOptions = {};
    window.departmentOptions = {};
    
    document.querySelectorAll('select[name^="designation_"]').forEach(select => {
        const empId = select.name.replace('designation_', '');
        window.designationOptions[empId] = select.innerHTML;
    });
    
    document.querySelectorAll('select[name^="department_"]').forEach(select => {
        const empId = select.name.replace('department_', '');
        window.departmentOptions[empId] = select.innerHTML;
    });
});
</script>

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