<?php
/**
 * API Endpoint: Get Employees for New Joinee Statutory
 * Returns real-time employee data including PAN, Aadhar, and Remarks
 */

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

header('Content-Type: application/json');

$siteId = getCurrentSiteId();
if (!$siteId) {
    $siteId = $_SESSION['site_id'] ?? null;
    if (!$siteId) {
        $stmt = getDB()->query("SELECT site_id FROM sites LIMIT 1");
        $site = $stmt->fetch();
        $siteId = $site ? $site['site_id'] : 1;
    }
}

// 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']) : '';

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

try {
    $db = getDB();
    
    // 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);
    
    // Fetch employees with filters
    $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
    ");
    $stmt->execute($queryParams);
    $employees = $stmt->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 ($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'];
        }
        
        // Ensure all fields have default values
        $emp['pan_number'] = $emp['pan_number'] ?? '';
        $emp['aadhar_number'] = $emp['aadhar_number'] ?? '';
        $emp['remarks'] = $emp['remarks'] ?? '';
    }
    unset($emp);
    
    // Get departments and designations for dropdowns
    $departments = [];
    $designations = [];
    try {
        $deptStmt = $db->prepare("SELECT department_id, department_name FROM departments WHERE site_id = ? ORDER BY department_name");
        $deptStmt->execute([$siteId]);
        $departments = $deptStmt->fetchAll(PDO::FETCH_ASSOC);
        
        $desigStmt = $db->prepare("SELECT designation_id, designation_name FROM designations WHERE site_id = ? ORDER BY designation_name");
        $desigStmt->execute([$siteId]);
        $designations = $desigStmt->fetchAll(PDO::FETCH_ASSOC);
    } catch (Exception $e) {
        error_log("Error fetching departments/designations: " . $e->getMessage());
    }
    
    echo json_encode([
        'success' => true,
        'employees' => $employees,
        'count' => count($employees),
        'departments' => $departments,
        'designations' => $designations,
        'timestamp' => date('Y-m-d H:i:s')
    ]);
    
} catch (Exception $e) {
    error_log("API Error fetching employees: " . $e->getMessage());
    http_response_code(500);
    echo json_encode([
        'success' => false,
        'message' => 'Error fetching employee data: ' . $e->getMessage()
    ]);
}

