<?php
require_once '../includes/auth.php';
require_once '../includes/db.php';

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

if (!isLoggedIn() || !hasRole(['owner', 'finance executive'])) {
    echo json_encode(['success' => false, 'message' => 'Unauthorized']);
    exit;
}

if (!isModuleEnabled('payroll')) {
    echo json_encode(['success' => false, 'message' => 'Payroll module is disabled']);
    exit;
}

$data = json_decode(file_get_contents('php://input'), true);
$action = $_POST['action'] ?? $data['action'] ?? $_GET['action'] ?? '';

$company_id = $_SESSION['company_id'];
$user_id = $_SESSION['user_id'];

// Auto-upgrade database schema if needed
function ensurePayrollSchemaUpToDate() {
    try {
        query("
            CREATE TABLE IF NOT EXISTS payroll_configurations (
                id INT AUTO_INCREMENT PRIMARY KEY,
                company_id BIGINT NOT NULL,
                paid_working_days INT DEFAULT NULL,
                epf_employee_percent DECIMAL(5,2) DEFAULT 0.00,
                epf_employer_percent DECIMAL(5,2) DEFAULT 0.00,
                etf_percent DECIMAL(5,2) DEFAULT 0.00,
                cycle_start_day INT DEFAULT 1,
                cycle_end_day INT DEFAULT 0,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                UNIQUE KEY (company_id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
        ");
        
        query("
            CREATE TABLE IF NOT EXISTS salary_advances (
                id INT AUTO_INCREMENT PRIMARY KEY,
                employee_id BIGINT NOT NULL,
                company_id BIGINT NOT NULL,
                advance_date DATETIME NOT NULL,
                amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                KEY (employee_id),
                KEY (company_id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
        ");
        
        query("
            CREATE TABLE IF NOT EXISTS payroll_adjustments (
                id INT AUTO_INCREMENT PRIMARY KEY,
                employee_id BIGINT NOT NULL,
                company_id BIGINT NOT NULL,
                month INT NOT NULL,
                year INT NOT NULL,
                production_incentive DECIMAL(15,2) NOT NULL DEFAULT 0.00,
                other_allowances DECIMAL(15,2) NOT NULL DEFAULT 0.00,
                other_deductions DECIMAL(15,2) NOT NULL DEFAULT 0.00,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                UNIQUE KEY (employee_id, company_id, month, year)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
        ");
        
        // Add base_salary column to employees
        $empCols = fetchAll("SHOW COLUMNS FROM employees");
        $empColNames = array_map(function($c) { return strtolower($c['Field']); }, $empCols);
        if (!in_array('base_salary', $empColNames)) {
            query("ALTER TABLE employees ADD COLUMN base_salary DECIMAL(15,2) DEFAULT 0.00 AFTER hourly_rate");
        }
        
        // Add attendance incentive columns to payroll_configurations
        $configCols = fetchAll("SHOW COLUMNS FROM payroll_configurations");
        $configColNames = array_map(function($c) { return strtolower($c['Field']); }, $configCols);
        
        if (!in_array('attendance_incentive_perfect', $configColNames)) {
            query("ALTER TABLE payroll_configurations ADD COLUMN attendance_incentive_perfect DECIMAL(15,2) DEFAULT 0.00");
        }
        if (!in_array('attendance_incentive_one_absent', $configColNames)) {
            query("ALTER TABLE payroll_configurations ADD COLUMN attendance_incentive_one_absent DECIMAL(15,2) DEFAULT 0.00");
        }

        // Add detailed tracking columns to payrolls
        $columns = fetchAll("SHOW COLUMNS FROM payrolls");
        $columnNames = array_map(function($c) { return strtolower($c['Field']); }, $columns);
        
        if (!in_array('epf_employee_amount', $columnNames)) {
            query("ALTER TABLE payrolls ADD COLUMN epf_employee_amount DECIMAL(15,2) DEFAULT 0.00 AFTER net_salary");
        }
        if (!in_array('epf_employer_amount', $columnNames)) {
            query("ALTER TABLE payrolls ADD COLUMN epf_employer_amount DECIMAL(15,2) DEFAULT 0.00 AFTER epf_employee_amount");
        }
        if (!in_array('etf_amount', $columnNames)) {
            query("ALTER TABLE payrolls ADD COLUMN etf_amount DECIMAL(15,2) DEFAULT 0.00 AFTER epf_employer_amount");
        }
        
        if (!in_array('regular_hours', $columnNames)) {
            query("ALTER TABLE payrolls ADD COLUMN regular_hours DECIMAL(10,2) DEFAULT 0.00 AFTER paid_leaves");
        }
        if (!in_array('ot_hours', $columnNames)) {
            query("ALTER TABLE payrolls ADD COLUMN ot_hours DECIMAL(10,2) DEFAULT 0.00 AFTER regular_hours");
        }
        if (!in_array('ot_pay', $columnNames)) {
            query("ALTER TABLE payrolls ADD COLUMN ot_pay DECIMAL(15,2) DEFAULT 0.00 AFTER total_hours_worked");
        }
        if (!in_array('attendance_incentive', $columnNames)) {
            query("ALTER TABLE payrolls ADD COLUMN attendance_incentive DECIMAL(15,2) DEFAULT 0.00 AFTER allowances");
        }
        if (!in_array('production_incentive', $columnNames)) {
            query("ALTER TABLE payrolls ADD COLUMN production_incentive DECIMAL(15,2) DEFAULT 0.00 AFTER attendance_incentive");
        }
        if (!in_array('other_allowances', $columnNames)) {
            query("ALTER TABLE payrolls ADD COLUMN other_allowances DECIMAL(15,2) DEFAULT 0.00 AFTER production_incentive");
        }
        if (!in_array('salary_advance', $columnNames)) {
            query("ALTER TABLE payrolls ADD COLUMN salary_advance DECIMAL(15,2) DEFAULT 0.00 AFTER deductions");
        }
        if (!in_array('other_deductions', $columnNames)) {
            query("ALTER TABLE payrolls ADD COLUMN other_deductions DECIMAL(15,2) DEFAULT 0.00 AFTER salary_advance");
        }
        if (!in_array('no_pay', $columnNames)) {
            query("ALTER TABLE payrolls ADD COLUMN no_pay DECIMAL(15,2) DEFAULT 0.00 AFTER other_deductions");
        }
    } catch (Exception $e) {
        // Log or handle schema migration error
    }
}
ensurePayrollSchemaUpToDate();

switch ($action) {
    case 'get_config':
        $config = fetch("SELECT * FROM payroll_configurations WHERE company_id = ?", [$company_id]);
        if (!$config) {
            $config = [
                'paid_working_days' => null,
                'epf_employee_percent' => 0.00,
                'epf_employer_percent' => 0.00,
                'etf_percent' => 0.00,
                'cycle_start_day' => 1,
                'cycle_end_day' => 0,
                'attendance_incentive_perfect' => 0.00,
                'attendance_incentive_one_absent' => 0.00
            ];
        } else {
            $config['paid_working_days'] = $config['paid_working_days'] !== null ? intval($config['paid_working_days']) : null;
            $config['epf_employee_percent'] = floatval($config['epf_employee_percent']);
            $config['epf_employer_percent'] = floatval($config['epf_employer_percent']);
            $config['etf_percent'] = floatval($config['etf_percent']);
            $config['cycle_start_day'] = intval($config['cycle_start_day']);
            $config['cycle_end_day'] = intval($config['cycle_end_day']);
            $config['attendance_incentive_perfect'] = floatval($config['attendance_incentive_perfect'] ?? 0.00);
            $config['attendance_incentive_one_absent'] = floatval($config['attendance_incentive_one_absent'] ?? 0.00);
        }
        echo json_encode(['success' => true, 'config' => $config]);
        break;

    case 'save_config':
        $paidWorkingDays = $_POST['paid_working_days'] ?? $data['paid_working_days'] ?? null;
        $epfEmployee = floatval($_POST['epf_employee_percent'] ?? $data['epf_employee_percent'] ?? 0);
        $epfEmployer = floatval($_POST['epf_employer_percent'] ?? $data['epf_employer_percent'] ?? 0);
        $etf = floatval($_POST['etf_percent'] ?? $data['etf_percent'] ?? 0);
        $startDay = intval($_POST['cycle_start_day'] ?? $data['cycle_start_day'] ?? 1);
        $endDay = intval($_POST['cycle_end_day'] ?? $data['cycle_end_day'] ?? 0);
        $attnPerfect = floatval($_POST['attendance_incentive_perfect'] ?? $data['attendance_incentive_perfect'] ?? 0);
        $attnOneAbsent = floatval($_POST['attendance_incentive_one_absent'] ?? $data['attendance_incentive_one_absent'] ?? 0);
        
        $paidWorkingDays = ($paidWorkingDays === '' || $paidWorkingDays === null) ? null : intval($paidWorkingDays);
        
        if ($startDay < 1 || $startDay > 31 || $endDay < 0 || $endDay > 31) {
            echo json_encode(['success' => false, 'message' => 'Invalid cycle day values']);
            exit;
        }
        
        try {
            query("
                INSERT INTO payroll_configurations (
                    company_id, paid_working_days, epf_employee_percent, epf_employer_percent, etf_percent, cycle_start_day, cycle_end_day,
                    attendance_incentive_perfect, attendance_incentive_one_absent
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
                ON DUPLICATE KEY UPDATE 
                    paid_working_days = VALUES(paid_working_days),
                    epf_employee_percent = VALUES(epf_employee_percent),
                    epf_employer_percent = VALUES(epf_employer_percent),
                    etf_percent = VALUES(etf_percent),
                    cycle_start_day = VALUES(cycle_start_day),
                    cycle_end_day = VALUES(cycle_end_day),
                    attendance_incentive_perfect = VALUES(attendance_incentive_perfect),
                    attendance_incentive_one_absent = VALUES(attendance_incentive_one_absent)
            ", [$company_id, $paidWorkingDays, $epfEmployee, $epfEmployer, $etf, $startDay, $endDay, $attnPerfect, $attnOneAbsent]);
            
            echo json_encode(['success' => true, 'message' => 'Payroll configuration saved successfully']);
        } catch (Exception $e) {
            echo json_encode(['success' => false, 'message' => 'Error: ' . $e->getMessage()]);
        }
        break;
    case 'calculate':
        $month = intval($_POST['month'] ?? $data['month'] ?? $_GET['month'] ?? date('m'));
        $year = intval($_POST['year'] ?? $data['year'] ?? $_GET['year'] ?? date('Y'));
        
        $config = fetch("SELECT * FROM payroll_configurations WHERE company_id = ?", [$company_id]);
        $startDay = $config ? intval($config['cycle_start_day']) : 1;
        $endDay = $config ? intval($config['cycle_end_day']) : 0;
        $configuredWorkingDays = ($config && $config['paid_working_days'] !== null) ? intval($config['paid_working_days']) : null;
        $epfEePct = $config ? floatval($config['epf_employee_percent']) : 0.00;
        $epfErPct = $config ? floatval($config['epf_employer_percent']) : 0.00;
        $etfPct = $config ? floatval($config['etf_percent']) : 0.00;
        
        if ($startDay == 1) {
            $firstDay = sprintf('%04d-%02d-01', $year, $month);
            $lastDay = date('Y-m-t', strtotime($firstDay));
        } else {
            $prevMonth = $month - 1;
            $prevYear = $year;
            if ($prevMonth == 0) {
                $prevMonth = 12;
                $prevYear = $year - 1;
            }
            $firstDay = sprintf('%04d-%02d-%02d', $prevYear, $prevMonth, $startDay);
            
            $actualEndDay = $endDay > 0 ? $endDay : ($startDay - 1);
            $daysInMonth = intval(date('t', strtotime(sprintf('%04d-%02d-01', $year, $month))));
            if ($actualEndDay > $daysInMonth) {
                $actualEndDay = $daysInMonth;
            }
            $lastDay = sprintf('%04d-%02d-%02d', $year, $month, $actualEndDay);
        }
        
        // Calculate standard working days (Mon-Fri) in the month
        $workingDays = 0;
        if ($configuredWorkingDays !== null) {
            $workingDays = $configuredWorkingDays;
        } else {
            $start = new DateTime($firstDay);
            $end = new DateTime($lastDay);
            $end->modify('+1 day');
            $interval = new DateInterval('P1D');
            $period = new DatePeriod($start, $interval, $end);
            foreach ($period as $date) {
                $w = $date->format('N');
                if ($w < 6) {
                    $workingDays++;
                }
            }
        }
        
        // Fetch all employees
        $employees = fetchAll("SELECT id, name, hourly_rate, base_salary FROM employees WHERE company_id = ? ORDER BY name ASC", [$company_id]);
        $payrollList = [];
        
        foreach ($employees as $emp) {
            $empId = $emp['id'];
            
            // Check if payroll already exists and is confirmed
            $existing = fetch("
                SELECT * FROM payrolls 
                WHERE employee_id = ? AND company_id = ? AND period_start = ? AND period_end = ? AND (status = 'confirmed' OR status = 'paid')
            ", [$empId, $company_id, $firstDay, $lastDay]);
            
            if ($existing) {
                // Fetch dynamic salary advance sum to ensure it is always fresh (unless paid/locked)
                $salaryAdvanceSum = floatval(fetchValue("
                    SELECT SUM(amount) FROM salary_advances 
                    WHERE employee_id = ? AND company_id = ? 
                      AND advance_date >= CONCAT(?, ' 00:00:00') 
                      AND advance_date <= CONCAT(?, ' 23:59:59')
                ", [$empId, $company_id, $firstDay, $lastDay]));
                
                // If exists, load it
                $otherDeductions = floatval($existing['other_deductions'] ?? 0.00);
                $noPay = floatval($existing['no_pay'] ?? 0.00);
                $deductions = floatval($existing['deductions'] ?? 0.00);
                $netSalary = floatval($existing['base_salary']) + floatval($existing['ot_pay'] ?? 0.00) + floatval($existing['allowances']) - $deductions - $salaryAdvanceSum - floatval($existing['epf_employee_amount'] ?? 0.00);
                
                $payrollList[] = [
                    'id' => $existing['id'],
                    'employee_id' => $empId,
                    'name' => $emp['name'],
                    'hourly_rate' => $emp['hourly_rate'],
                    'total_working_days' => intval($existing['total_working_days'] ?? 0),
                    'days_present' => intval($existing['days_present'] ?? 0),
                    'days_absent' => intval($existing['days_absent'] ?? 0),
                    'half_days' => intval($existing['half_days'] ?? 0),
                    'short_leaves' => intval($existing['short_leaves'] ?? 0),
                    'paid_leaves' => intval($existing['paid_leaves'] ?? 0),
                    'total_hours_worked' => floatval($existing['total_hours_worked'] ?? 0),
                    'regular_hours' => floatval($existing['regular_hours'] ?? 0),
                    'ot_hours' => floatval($existing['ot_hours'] ?? 0),
                    'ot_pay' => floatval($existing['ot_pay'] ?? 0),
                    'base_salary' => floatval($existing['base_salary'] ?? 0),
                    'allowances' => floatval($existing['allowances'] ?? 0),
                    'attendance_incentive' => floatval($existing['attendance_incentive'] ?? 0),
                    'production_incentive' => floatval($existing['production_incentive'] ?? 0),
                    'other_allowances' => floatval($existing['other_allowances'] ?? 0),
                    'deductions' => $deductions,
                    'salary_advance' => $salaryAdvanceSum,
                    'other_deductions' => $otherDeductions,
                    'no_pay' => $noPay,
                    'epf_employee_amount' => floatval($existing['epf_employee_amount'] ?? 0),
                    'epf_employer_amount' => floatval($existing['epf_employer_amount'] ?? 0),
                    'etf_amount' => floatval($existing['etf_amount'] ?? 0),
                    'net_salary' => round($netSalary, 2),
                    'status' => $existing['status']
                ];
            } else {
                // Perform dynamic calculation
                
                // Fetch employee shift details (working hours & minimum working hours)
                $empShift = fetch("
                    SELECT s.working_hours, s.minimum_working_hours 
                    FROM employees e
                    LEFT JOIN shifts s ON e.shift_id = s.id
                    WHERE e.id = ?
                ", [$empId]);
                $std_hours = $empShift && $empShift['working_hours'] !== null ? (float)$empShift['working_hours'] : 8.00;
                $min_hours = $empShift && $empShift['minimum_working_hours'] !== null ? (float)$empShift['minimum_working_hours'] : 0.00;

                // 1. Fetch attendance sessions
                $sessions = fetchAll("
                    SELECT clock_in, clock_out, date FROM attendance 
                    WHERE employee_id = ? AND company_id = ? AND date BETWEEN ? AND ?
                ", [$empId, $company_id, $firstDay, $lastDay]);
                
                $dailySeconds = [];
                foreach ($sessions as $s) {
                    if ($s['clock_out']) {
                        $dt = $s['date'];
                        if (!isset($dailySeconds[$dt])) {
                            $dailySeconds[$dt] = 0;
                        }
                        $dailySeconds[$dt] += strtotime($s['clock_out']) - strtotime($s['clock_in']);
                    }
                }
                
                $dailyWorkedHours = [];
                $regularHours = 0;
                foreach ($dailySeconds as $dt => $sec) {
                    $dayHrs = round($sec / 3600, 2);
                    $dailyWorkedHours[$dt] = $dayHrs;
                    $regularHours += min($dayHrs, $std_hours);
                }
                
                $approvedOvertimeHours = floatval(fetchValue("
                    SELECT SUM(hours) FROM overtime_requests 
                    WHERE employee_id = ? AND company_id = ? AND date BETWEEN ? AND ? AND status = 'approved'
                ", [$empId, $company_id, $firstDay, $lastDay]));
                
                $hoursWorked = $regularHours + $approvedOvertimeHours;
                
                // 2. Fetch approved leaves
                $leaves = fetchAll("
                    SELECT duration_type, start_date, end_date FROM leave_requests 
                    WHERE employee_id = ? AND company_id = ? AND status = 'approved'
                      AND (start_date <= ? AND end_date >= ?)
                ", [$empId, $company_id, $lastDay, $firstDay]);
                
                $leaveDates = [];
                $paidLeaveHours = 0.0;
                $halfDaysCount = 0;
                $shortLeavesCount = 0;
                $fullDaysCount = 0;
                
                foreach ($leaves as $l) {
                    $lStart = new DateTime($l['start_date']);
                    $lEnd = new DateTime($l['end_date']);
                    
                    // Populate calendar days covered by this leave within the cycle
                    $current = clone $lStart;
                    while ($current <= $lEnd) {
                        $dateStr = $current->format('Y-m-d');
                        if ($dateStr >= $firstDay && $dateStr <= $lastDay) {
                            $leaveDates[$dateStr] = true;
                        }
                        $current->modify('+1 day');
                    }
                    
                    // Math for total leave compensation (only on working days & non-holidays)
                    if ($l['duration_type'] === 'full') {
                        $lEndForDiff = clone $lEnd;
                        $lEndForDiff->modify('+1 day');
                        $lPeriod = new DatePeriod($lStart, new DateInterval('P1D'), $lEndForDiff);
                        $days = 0;
                        foreach ($lPeriod as $d) {
                            $w = $d->format('N');
                            $dateStr = $d->format('Y-m-d');
                            $isHoliday = fetchValue("SELECT COUNT(*) FROM public_holidays WHERE company_id = ? AND holiday_date = ?", [$company_id, $dateStr]);
                            if ($w < 6 && !$isHoliday) {
                                $days++;
                            }
                        }
                        $fullDaysCount += $days;
                        $paidLeaveHours += $days * 8.0;
                    } elseif ($l['duration_type'] === 'half_day') {
                        $halfDaysCount++;
                        $paidLeaveHours += 4.0;
                    } elseif ($l['duration_type'] === 'short_leave') {
                        $shortLeavesCount++;
                        $paidLeaveHours += 2.0;
                    }
                }
                
                // 3. Determine unique days present (Worked >= Min Hours OR has Approved Leave)
                $presentDates = [];
                foreach ($dailyWorkedHours as $dt => $hrs) {
                    if ($hrs >= $min_hours) {
                        $presentDates[$dt] = true;
                    }
                }
                foreach ($leaveDates as $dt => $val) {
                    $presentDates[$dt] = true;
                }
                // Add weekday public holidays to present dates since they are paid by default
                $holidays = fetchAll("
                    SELECT holiday_date FROM public_holidays 
                    WHERE company_id = ? AND holiday_date BETWEEN ? AND ?
                ", [$company_id, $firstDay, $lastDay]);
                foreach ($holidays as $h) {
                    $w = date('N', strtotime($h['holiday_date']));
                    if ($w < 6) {
                        $presentDates[$h['holiday_date']] = true;
                    }
                }
                $daysPresent = count($presentDates);
                
                // Days absent (with clamp to 0)
                $daysAbsent = $workingDays - $daysPresent;
                if ($daysAbsent < 0) $daysAbsent = 0;
                
                // Calculate base salary (Using configured Base Salary from Employee)
                $baseSalary = floatval($emp['base_salary'] ?? 0.00);
                
                // Calculate OT Pay (Overtime hours are paid at 1.5x hourly rate, NOT subject to EPF/ETF)
                $otPay = $approvedOvertimeHours * ($emp['hourly_rate'] * 1.5);
                
                // Calculate EPF / ETF (computed strictly on baseSalary)
                $epfEeAmount = round($baseSalary * ($epfEePct / 100), 2);
                $epfErAmount = round($baseSalary * ($epfErPct / 100), 2);
                $etfAmount = round($baseSalary * ($etfPct / 100), 2);
                
                // Calculate automatic Attendance Incentive
                $cfg_attn_incentive_perfect = $config ? floatval($config['attendance_incentive_perfect'] ?? 0.00) : 0.00;
                $cfg_attn_incentive_one_absent = $config ? floatval($config['attendance_incentive_one_absent'] ?? 0.00) : 0.00;
                $attendance_incentive = 0.00;
                if ($daysAbsent == 0) {
                    $attendance_incentive = $cfg_attn_incentive_perfect;
                } elseif ($daysAbsent == 1) {
                    $attendance_incentive = $cfg_attn_incentive_one_absent;
                }
                
                // Fetch dynamic adjustments from payroll_adjustments
                $adj = fetch("
                    SELECT production_incentive, other_allowances, other_deductions 
                    FROM payroll_adjustments 
                    WHERE employee_id = ? AND company_id = ? AND month = ? AND year = ?
                ", [$empId, $company_id, $month, $year]);
                
                $productionIncentive = $adj ? floatval($adj['production_incentive']) : 0.00;
                $otherAllowances = $adj ? floatval($adj['other_allowances']) : 0.00;
                $otherDeductions = $adj ? floatval($adj['other_deductions']) : 0.00;
                
                $allowances = $attendance_incentive + $productionIncentive + $otherAllowances;
                
                // Calculate No-Pay Deduction
                $perDaySalary = $workingDays > 0 ? ($baseSalary / $workingDays) : 0.00;
                $noPay = round($daysAbsent * $perDaySalary, 2);
                $deductions = $otherDeductions + $noPay;
                
                // Fetch dynamic salary advance sum
                $salaryAdvanceSum = floatval(fetchValue("
                    SELECT SUM(amount) FROM salary_advances 
                    WHERE employee_id = ? AND company_id = ? 
                      AND advance_date >= CONCAT(?, ' 00:00:00') 
                      AND advance_date <= CONCAT(?, ' 23:59:59')
                ", [$empId, $company_id, $firstDay, $lastDay]));
                
                $netSalary = $baseSalary + $otPay + $allowances - $deductions - $salaryAdvanceSum - $epfEeAmount;
                
                $payrollList[] = [
                    'id' => null,
                    'employee_id' => $empId,
                    'name' => $emp['name'],
                    'hourly_rate' => $emp['hourly_rate'],
                    'total_working_days' => $workingDays,
                    'days_present' => $daysPresent,
                    'days_absent' => $daysAbsent,
                    'half_days' => $halfDaysCount,
                    'short_leaves' => $shortLeavesCount,
                    'paid_leaves' => $fullDaysCount,
                    'total_hours_worked' => $hoursWorked,
                    'regular_hours' => round($regularHours, 1),
                    'ot_hours' => round($approvedOvertimeHours, 1),
                    'ot_pay' => round($otPay, 2),
                    'base_salary' => round($baseSalary, 2),
                    'allowances' => round($allowances, 2),
                    'attendance_incentive' => round($attendance_incentive, 2),
                    'production_incentive' => round($productionIncentive, 2),
                    'other_allowances' => round($otherAllowances, 2),
                    'deductions' => round($deductions, 2),
                    'salary_advance' => round($salaryAdvanceSum, 2),
                    'other_deductions' => round($otherDeductions, 2),
                    'no_pay' => round($noPay, 2),
                    'epf_employee_amount' => $epfEeAmount,
                    'epf_employer_amount' => $epfErAmount,
                    'etf_amount' => $etfAmount,
                    'net_salary' => round($netSalary, 2),
                    'status' => 'draft'
                ];
            }
        }
        
        echo json_encode(['success' => true, 'payroll' => $payrollList, 'period_start' => $firstDay, 'period_end' => $lastDay]);
        break;

    case 'save_adjustment':
        $empId = $_POST['employee_id'] ?? $data['employee_id'] ?? null;
        $status = ($_POST['status'] ?? $data['status'] ?? 'draft') === 'paid' ? 'paid' : 'draft';
        $month = intval($_POST['month'] ?? $data['month'] ?? date('m'));
        $year = intval($_POST['year'] ?? $data['year'] ?? date('Y'));
        $productionIncentive = floatval($_POST['production_incentive'] ?? $data['production_incentive'] ?? 0);
        $otherAllowances = floatval($_POST['other_allowances'] ?? $data['other_allowances'] ?? 0);
        $otherDeductions = floatval($_POST['other_deductions'] ?? $data['other_deductions'] ?? 0);
        
        $config = fetch("SELECT * FROM payroll_configurations WHERE company_id = ?", [$company_id]);
        $startDay = $config ? intval($config['cycle_start_day']) : 1;
        $endDay = $config ? intval($config['cycle_end_day']) : 0;
        $configuredWorkingDays = ($config && $config['paid_working_days'] !== null) ? intval($config['paid_working_days']) : null;
        $epfEePct = $config ? floatval($config['epf_employee_percent']) : 0.00;
        $epfErPct = $config ? floatval($config['epf_employer_percent']) : 0.00;
        $etfPct = $config ? floatval($config['etf_percent']) : 0.00;
        $cfg_attn_incentive_perfect = $config ? floatval($config['attendance_incentive_perfect'] ?? 0.00) : 0.00;
        $cfg_attn_incentive_one_absent = $config ? floatval($config['attendance_incentive_one_absent'] ?? 0.00) : 0.00;
        
        if ($startDay == 1) {
            $firstDay = sprintf('%04d-%02d-01', $year, $month);
            $lastDay = date('Y-m-t', strtotime($firstDay));
        } else {
            $prevMonth = $month - 1;
            $prevYear = $year;
            if ($prevMonth == 0) {
                $prevMonth = 12;
                $prevYear = $year - 1;
            }
            $firstDay = sprintf('%04d-%02d-%02d', $prevYear, $prevMonth, $startDay);
            
            $actualEndDay = $endDay > 0 ? $endDay : ($startDay - 1);
            $daysInMonth = intval(date('t', strtotime(sprintf('%04d-%02d-01', $year, $month))));
            if ($actualEndDay > $daysInMonth) {
                $actualEndDay = $daysInMonth;
            }
            $lastDay = sprintf('%04d-%02d-%02d', $year, $month, $actualEndDay);
        }
        
        if (!$empId) {
            echo json_encode(['success' => false, 'message' => 'Missing employee ID']);
            exit;
        }
        
        try {
            // Check if there is an existing confirmed/paid record
            $existingConfirmed = fetch("
                SELECT * FROM payrolls 
                WHERE employee_id = ? AND company_id = ? AND period_start = ? AND period_end = ? AND (status = 'confirmed' OR status = 'paid')
            ", [$empId, $company_id, $firstDay, $lastDay]);
            
            if ($existingConfirmed) {
                echo json_encode(['success' => false, 'message' => 'Cannot modify confirmed/paid payroll']);
                exit;
            }

            // 1. Save or update the adjustments in the payroll_adjustments table
            query("
                INSERT INTO payroll_adjustments (
                    employee_id, company_id, month, year, production_incentive, other_allowances, other_deductions
                ) VALUES (?, ?, ?, ?, ?, ?, ?)
                ON DUPLICATE KEY UPDATE 
                    production_incentive = VALUES(production_incentive),
                    other_allowances = VALUES(other_allowances),
                    other_deductions = VALUES(other_deductions)
            ", [$empId, $company_id, $month, $year, $productionIncentive, $otherAllowances, $otherDeductions]);

            if ($status === 'paid' || $status === 'confirmed') {
                // Perform dynamic calculation and save/update confirmed record in payrolls
                $emp = fetch("SELECT name, hourly_rate, shift_id, base_salary FROM employees WHERE id = ?", [$empId]);
                
                // Fetch employee shift details (working hours & minimum working hours)
                $empShift = fetch("
                    SELECT s.working_hours, s.minimum_working_hours 
                    FROM employees e
                    LEFT JOIN shifts s ON e.shift_id = s.id
                    WHERE e.id = ?
                ", [$empId]);
                $std_hours = $empShift && $empShift['working_hours'] !== null ? (float)$empShift['working_hours'] : 8.00;
                $min_hours = $empShift && $empShift['minimum_working_hours'] !== null ? (float)$empShift['minimum_working_hours'] : 0.00;

                // 1. Fetch attendance sessions
                $sessions = fetchAll("
                    SELECT clock_in, clock_out, date FROM attendance 
                    WHERE employee_id = ? AND company_id = ? AND date BETWEEN ? AND ?
                ", [$empId, $company_id, $firstDay, $lastDay]);
                
                $dailySeconds = [];
                foreach ($sessions as $s) {
                    if ($s['clock_out']) {
                        $dt = $s['date'];
                        if (!isset($dailySeconds[$dt])) {
                            $dailySeconds[$dt] = 0;
                        }
                        $dailySeconds[$dt] += strtotime($s['clock_out']) - strtotime($s['clock_in']);
                    }
                }
                
                $dailyWorkedHours = [];
                $regularHours = 0;
                foreach ($dailySeconds as $dt => $sec) {
                    $dayHrs = round($sec / 3600, 2);
                    $dailyWorkedHours[$dt] = $dayHrs;
                    $regularHours += min($dayHrs, $std_hours);
                }
                
                $approvedOvertimeHours = floatval(fetchValue("
                    SELECT SUM(hours) FROM overtime_requests 
                    WHERE employee_id = ? AND company_id = ? AND date BETWEEN ? AND ? AND status = 'approved'
                ", [$empId, $company_id, $firstDay, $lastDay]));
                
                $hoursWorked = $regularHours + $approvedOvertimeHours;
                
                // 2. Fetch approved leaves
                $leaves = fetchAll("
                    SELECT duration_type, start_date, end_date FROM leave_requests 
                    WHERE employee_id = ? AND company_id = ? AND status = 'approved'
                      AND (start_date <= ? AND end_date >= ?)
                ", [$empId, $company_id, $lastDay, $firstDay]);
                
                $leaveDates = [];
                $paidLeaveHours = 0.0;
                $halfDaysCount = 0;
                $shortLeavesCount = 0;
                $fullDaysCount = 0;
                
                foreach ($leaves as $l) {
                    $lStart = new DateTime($l['start_date']);
                    $lEnd = new DateTime($l['end_date']);
                    
                    // Populate calendar days covered by this leave within the cycle
                    $current = clone $lStart;
                    while ($current <= $lEnd) {
                        $dateStr = $current->format('Y-m-d');
                        if ($dateStr >= $firstDay && $dateStr <= $lastDay) {
                            $leaveDates[$dateStr] = true;
                        }
                        $current->modify('+1 day');
                    }
                    
                    // Math for total leave compensation (only on working days & non-holidays)
                    if ($l['duration_type'] === 'full') {
                        $lEndForDiff = clone $lEnd;
                        $lEndForDiff->modify('+1 day');
                        $lPeriod = new DatePeriod($lStart, new DateInterval('P1D'), $lEndForDiff);
                        $days = 0;
                        foreach ($lPeriod as $d) {
                            $w = $d->format('N');
                            $dateStr = $d->format('Y-m-d');
                            $isHoliday = fetchValue("SELECT COUNT(*) FROM public_holidays WHERE company_id = ? AND holiday_date = ?", [$company_id, $dateStr]);
                            if ($w < 6 && !$isHoliday) {
                                $days++;
                            }
                        }
                        $fullDaysCount += $days;
                        $paidLeaveHours += $days * 8.0;
                    } elseif ($l['duration_type'] === 'half_day') {
                        $halfDaysCount++;
                        $paidLeaveHours += 4.0;
                    } elseif ($l['duration_type'] === 'short_leave') {
                        $shortLeavesCount++;
                        $paidLeaveHours += 2.0;
                    }
                }
                
                // 3. Determine unique days present (Worked >= Min Hours OR has Approved Leave)
                $presentDates = [];
                foreach ($dailyWorkedHours as $dt => $hrs) {
                    if ($hrs >= $min_hours) {
                        $presentDates[$dt] = true;
                    }
                }
                foreach ($leaveDates as $dt => $val) {
                    $presentDates[$dt] = true;
                }
                // Add weekday public holidays to present dates since they are paid by default
                $holidays = fetchAll("
                    SELECT holiday_date FROM public_holidays 
                    WHERE company_id = ? AND holiday_date BETWEEN ? AND ?
                ", [$company_id, $firstDay, $lastDay]);
                foreach ($holidays as $h) {
                    $w = date('N', strtotime($h['holiday_date']));
                    if ($w < 6) {
                        $presentDates[$h['holiday_date']] = true;
                    }
                }
                $daysPresent = count($presentDates);
                
                // Calculate working days
                $workingDays = 0;
                if ($configuredWorkingDays !== null) {
                    $workingDays = $configuredWorkingDays;
                } else {
                    $start = new DateTime($firstDay);
                    $end = new DateTime($lastDay);
                    $end->modify('+1 day');
                    $period = new DatePeriod($start, new DateInterval('P1D'), $end);
                    foreach ($period as $date) {
                        $w = $date->format('N');
                        if ($w < 6) $workingDays++;
                    }
                }
                
                $daysAbsent = $workingDays - $daysPresent;
                if ($daysAbsent < 0) $daysAbsent = 0;
                
                $baseSalary = floatval($emp['base_salary'] ?? 0.00);
                $otPay = $approvedOvertimeHours * ($emp['hourly_rate'] * 1.5);
                $epfEeAmount = round($baseSalary * ($epfEePct / 100), 2);
                $epfErAmount = round($baseSalary * ($epfErPct / 100), 2);
                $etfAmount = round($baseSalary * ($etfPct / 100), 2);
                
                // Fetch dynamic salary advance sum
                $salaryAdvanceSum = floatval(fetchValue("
                    SELECT SUM(amount) FROM salary_advances 
                    WHERE employee_id = ? AND company_id = ? 
                      AND advance_date >= CONCAT(?, ' 00:00:00') 
                      AND advance_date <= CONCAT(?, ' 23:59:59')
                ", [$empId, $company_id, $firstDay, $lastDay]));

                $attendanceIncentive = 0.00;
                if ($daysAbsent == 0) {
                    $attendanceIncentive = $cfg_attn_incentive_perfect;
                } elseif ($daysAbsent == 1) {
                    $attendanceIncentive = $cfg_attn_incentive_one_absent;
                }
                
                $allowances = $attendanceIncentive + $productionIncentive + $otherAllowances;
                $perDaySalary = $workingDays > 0 ? ($baseSalary / $workingDays) : 0.00;
                $noPay = round($daysAbsent * $perDaySalary, 2);
                $deductions = $otherDeductions + $noPay;
                $netSalary = $baseSalary + $otPay + $allowances - $deductions - $salaryAdvanceSum - $epfEeAmount;

                // Save or update the record in the payrolls table with confirmed status
                $existingPayroll = fetch("
                    SELECT id FROM payrolls 
                    WHERE employee_id = ? AND company_id = ? AND period_start = ? AND period_end = ?
                ", [$empId, $company_id, $firstDay, $lastDay]);
                
                if ($existingPayroll) {
                    query("
                        UPDATE payrolls 
                        SET total_working_days = ?, days_present = ?, days_absent = ?, half_days = ?, short_leaves = ?, paid_leaves = ?,
                            total_hours_worked = ?, regular_hours = ?, ot_hours = ?, ot_pay = ?, base_salary = ?,
                            allowances = ?, attendance_incentive = ?, production_incentive = ?, other_allowances = ?,
                            deductions = ?, salary_advance = ?, other_deductions = ?, no_pay = ?, net_salary = ?,
                            epf_employee_amount = ?, epf_employer_amount = ?, etf_amount = ?, status = 'confirmed', processed_by = ?
                        WHERE id = ?
                    ", [
                        $workingDays, $daysPresent, $daysAbsent, $halfDaysCount, $shortLeavesCount, $fullDaysCount,
                        $hoursWorked, $regularHours, $approvedOvertimeHours, $otPay, $baseSalary,
                        $allowances, $attendanceIncentive, $productionIncentive, $otherAllowances,
                        $deductions, $salaryAdvanceSum, $otherDeductions, $noPay, $netSalary,
                        $epfEeAmount, $epfErAmount, $etfAmount, $user_id, $existingPayroll['id']
                    ]);
                } else {
                    query("
                        INSERT INTO payrolls (
                            employee_id, company_id, period_start, period_end, total_working_days, 
                            days_present, days_absent, half_days, short_leaves, paid_leaves, 
                            total_hours_worked, regular_hours, ot_hours, ot_pay, base_salary, 
                            allowances, attendance_incentive, production_incentive, other_allowances, 
                            deductions, salary_advance, other_deductions, no_pay, net_salary, 
                            epf_employee_amount, epf_employer_amount, etf_amount, status, processed_by
                        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'confirmed', ?)
                    ", [
                        $empId, $company_id, $firstDay, $lastDay, $workingDays, 
                        $daysPresent, $daysAbsent, $halfDaysCount, $shortLeavesCount, $fullDaysCount, 
                        $hoursWorked, $regularHours, $approvedOvertimeHours, $otPay, $baseSalary, 
                        $allowances, $attendanceIncentive, $productionIncentive, $otherAllowances, 
                        $deductions, $salaryAdvanceSum, $otherDeductions, $noPay, $netSalary,
                        $epfEeAmount, $epfErAmount, $etfAmount, $user_id
                    ]);
                }
            } else {
                // Delete any existing draft record in payrolls table to ensure clean state
                query("
                    DELETE FROM payrolls 
                    WHERE employee_id = ? AND company_id = ? AND period_start = ? AND period_end = ? AND status = 'draft'
                ", [$empId, $company_id, $firstDay, $lastDay]);
            }
            $msg = $status === 'paid' ? 'Payroll confirmed successfully' : 'Adjustments saved successfully';
            echo json_encode(['success' => true, 'message' => $msg]);
        } catch (Exception $e) {
            echo json_encode(['success' => false, 'message' => 'Error: ' . $e->getMessage()]);
        }
        break;

    case 'mark_paid':
        $month = intval($_POST['month'] ?? $data['month'] ?? date('m'));
        $year = intval($_POST['year'] ?? $data['year'] ?? date('Y'));
        
        $config = fetch("SELECT * FROM payroll_configurations WHERE company_id = ?", [$company_id]);
        $startDay = $config ? intval($config['cycle_start_day']) : 1;
        $endDay = $config ? intval($config['cycle_end_day']) : 0;
        $configuredWorkingDays = ($config && $config['paid_working_days'] !== null) ? intval($config['paid_working_days']) : null;
        $epfEePct = $config ? floatval($config['epf_employee_percent']) : 0.00;
        $epfErPct = $config ? floatval($config['epf_employer_percent']) : 0.00;
        $etfPct = $config ? floatval($config['etf_percent']) : 0.00;
        
        if ($startDay == 1) {
            $firstDay = sprintf('%04d-%02d-01', $year, $month);
            $lastDay = date('Y-m-t', strtotime($firstDay));
        } else {
            $prevMonth = $month - 1;
            $prevYear = $year;
            if ($prevMonth == 0) {
                $prevMonth = 12;
                $prevYear = $year - 1;
            }
            $firstDay = sprintf('%04d-%02d-%02d', $prevYear, $prevMonth, $startDay);
            
            $actualEndDay = $endDay > 0 ? $endDay : ($startDay - 1);
            $daysInMonth = intval(date('t', strtotime(sprintf('%04d-%02d-01', $year, $month))));
            if ($actualEndDay > $daysInMonth) {
                $actualEndDay = $daysInMonth;
            }
            $lastDay = sprintf('%04d-%02d-%02d', $year, $month, $actualEndDay);
        }
        
        // Fetch all employees who don't have payroll stored yet, or are in draft status
        $employees = fetchAll("SELECT id FROM employees WHERE company_id = ?", [$company_id]);
        
        try {
            global $pdo;
            $pdo->beginTransaction();
            
            foreach ($employees as $emp) {
                $empId = $emp['id'];
                
                // Check if payroll already exists
                $existing = fetch("
                    SELECT * FROM payrolls 
                    WHERE employee_id = ? AND company_id = ? AND period_start = ? AND period_end = ?
                ", [$empId, $company_id, $firstDay, $lastDay]);
                
                if ($existing && ($existing['status'] === 'confirmed' || $existing['status'] === 'paid')) {
                    // Already confirmed/paid, leave it untouched
                    continue;
                }
                
                // Otherwise (does not exist, or exists as draft), perform dynamic calculation
                $empData = fetch("SELECT name, hourly_rate, shift_id, base_salary FROM employees WHERE id = ?", [$empId]);
                
                // Fetch employee shift details (working hours & minimum working hours)
                $empShift = fetch("
                    SELECT s.working_hours, s.minimum_working_hours 
                    FROM employees e
                    LEFT JOIN shifts s ON e.shift_id = s.id
                    WHERE e.id = ?
                ", [$empId]);
                $std_hours = $empShift && $empShift['working_hours'] !== null ? (float)$empShift['working_hours'] : 8.00;
                $min_hours = $empShift && $empShift['minimum_working_hours'] !== null ? (float)$empShift['minimum_working_hours'] : 0.00;

                // 1. Fetch attendance sessions
                $sessions = fetchAll("
                    SELECT clock_in, clock_out, date FROM attendance 
                    WHERE employee_id = ? AND company_id = ? AND date BETWEEN ? AND ?
                ", [$empId, $company_id, $firstDay, $lastDay]);
                
                $dailySeconds = [];
                foreach ($sessions as $s) {
                    if ($s['clock_out']) {
                        $dt = $s['date'];
                        if (!isset($dailySeconds[$dt])) {
                            $dailySeconds[$dt] = 0;
                        }
                        $dailySeconds[$dt] += strtotime($s['clock_out']) - strtotime($s['clock_in']);
                    }
                }
                
                $dailyWorkedHours = [];
                $regularHours = 0;
                foreach ($dailySeconds as $dt => $sec) {
                    $dayHrs = round($sec / 3600, 2);
                    $dailyWorkedHours[$dt] = $dayHrs;
                    $regularHours += min($dayHrs, $std_hours);
                }
                
                $approvedOvertimeHours = floatval(fetchValue("
                    SELECT SUM(hours) FROM overtime_requests 
                    WHERE employee_id = ? AND company_id = ? AND date BETWEEN ? AND ? AND status = 'approved'
                ", [$empId, $company_id, $firstDay, $lastDay]));
                
                $hoursWorked = $regularHours + $approvedOvertimeHours;
                
                // 2. Fetch approved leaves
                $leaves = fetchAll("
                    SELECT duration_type, start_date, end_date FROM leave_requests 
                    WHERE employee_id = ? AND company_id = ? AND status = 'approved'
                      AND (start_date <= ? AND end_date >= ?)
                ", [$empId, $company_id, $lastDay, $firstDay]);
                
                $leaveDates = [];
                $paidLeaveHours = 0.0;
                $halfDaysCount = 0;
                $shortLeavesCount = 0;
                $fullDaysCount = 0;
                
                foreach ($leaves as $l) {
                    $lStart = new DateTime($l['start_date']);
                    $lEnd = new DateTime($l['end_date']);
                    
                    // Populate calendar days covered by this leave within the cycle
                    $current = clone $lStart;
                    while ($current <= $lEnd) {
                        $dateStr = $current->format('Y-m-d');
                        if ($dateStr >= $firstDay && $dateStr <= $lastDay) {
                            $leaveDates[$dateStr] = true;
                        }
                        $current->modify('+1 day');
                    }
                    
                    // Math for total leave compensation (only on working days & non-holidays)
                    if ($l['duration_type'] === 'full') {
                        $lEndForDiff = clone $lEnd;
                        $lEndForDiff->modify('+1 day');
                        $lPeriod = new DatePeriod($lStart, new DateInterval('P1D'), $lEndForDiff);
                        $days = 0;
                        foreach ($lPeriod as $d) {
                            $w = $d->format('N');
                            $dateStr = $d->format('Y-m-d');
                            $isHoliday = fetchValue("SELECT COUNT(*) FROM public_holidays WHERE company_id = ? AND holiday_date = ?", [$company_id, $dateStr]);
                            if ($w < 6 && !$isHoliday) {
                                $days++;
                            }
                        }
                        $fullDaysCount += $days;
                        $paidLeaveHours += $days * 8.0;
                    } elseif ($l['duration_type'] === 'half_day') {
                        $halfDaysCount++;
                        $paidLeaveHours += 4.0;
                    } elseif ($l['duration_type'] === 'short_leave') {
                        $shortLeavesCount++;
                        $paidLeaveHours += 2.0;
                    }
                }
                
                // 3. Determine unique days present (Worked >= Min Hours OR has Approved Leave)
                $presentDates = [];
                foreach ($dailyWorkedHours as $dt => $hrs) {
                    if ($hrs >= $min_hours) {
                        $presentDates[$dt] = true;
                    }
                }
                foreach ($leaveDates as $dt => $val) {
                    $presentDates[$dt] = true;
                }
                // Add weekday public holidays to present dates since they are paid by default
                $holidays = fetchAll("
                    SELECT holiday_date FROM public_holidays 
                    WHERE company_id = ? AND holiday_date BETWEEN ? AND ?
                ", [$company_id, $firstDay, $lastDay]);
                foreach ($holidays as $h) {
                    $w = date('N', strtotime($h['holiday_date']));
                    if ($w < 6) {
                        $presentDates[$h['holiday_date']] = true;
                    }
                }
                $daysPresent = count($presentDates);
                
                // Calculate working days
                $workingDays = 0;
                if ($configuredWorkingDays !== null) {
                    $workingDays = $configuredWorkingDays;
                } else {
                    $start = new DateTime($firstDay);
                    $end = new DateTime($lastDay);
                    $end->modify('+1 day');
                    $period = new DatePeriod($start, new DateInterval('P1D'), $end);
                    foreach ($period as $date) {
                        $w = $date->format('N');
                        if ($w < 6) $workingDays++;
                    }
                }
                
                $daysAbsent = $workingDays - $daysPresent;
                if ($daysAbsent < 0) $daysAbsent = 0;
                
                // Calculate base salary (Using configured Base Salary from Employee)
                $baseSalary = floatval($empData['base_salary'] ?? 0.00);
                
                // Calculate OT Pay
                $otPay = $approvedOvertimeHours * ($empData['hourly_rate'] * 1.5);
                
                // Calculate EPF / ETF
                $epfEeAmount = round($baseSalary * ($epfEePct / 100), 2);
                $epfErAmount = round($baseSalary * ($epfErPct / 100), 2);
                $etfAmount = round($baseSalary * ($etfPct / 100), 2);
                
                // Calculate automatic Attendance Incentive
                $attendanceIncentive = 0.00;
                if ($daysAbsent == 0) {
                    $attendanceIncentive = $cfg_attn_incentive_perfect;
                } elseif ($daysAbsent == 1) {
                    $attendanceIncentive = $cfg_attn_incentive_one_absent;
                }
                
                // Fetch dynamic adjustments from payroll_adjustments
                $adj = fetch("
                    SELECT production_incentive, other_allowances, other_deductions 
                    FROM payroll_adjustments 
                    WHERE employee_id = ? AND company_id = ? AND month = ? AND year = ?
                ", [$empId, $company_id, $month, $year]);
                
                $productionIncentive = $adj ? floatval($adj['production_incentive']) : 0.00;
                $otherAllowances = $adj ? floatval($adj['other_allowances']) : 0.00;
                $otherDeductions = $adj ? floatval($adj['other_deductions']) : 0.00;
                
                $allowances = $attendanceIncentive + $productionIncentive + $otherAllowances;
                $perDaySalary = $workingDays > 0 ? ($baseSalary / $workingDays) : 0.00;
                $noPay = round($daysAbsent * $perDaySalary, 2);
                $deductions = $otherDeductions + $noPay;
                
                // Fetch dynamic salary advance sum
                $salaryAdvanceSum = floatval(fetchValue("
                    SELECT SUM(amount) FROM salary_advances 
                    WHERE employee_id = ? AND company_id = ? 
                      AND advance_date >= CONCAT(?, ' 00:00:00') 
                      AND advance_date <= CONCAT(?, ' 23:59:59')
                ", [$empId, $company_id, $firstDay, $lastDay]));
                
                $netSalary = $baseSalary + $otPay + $allowances - $deductions - $salaryAdvanceSum - $epfEeAmount;
                
                if ($existing) {
                    query("
                        UPDATE payrolls 
                        SET total_working_days = ?, days_present = ?, days_absent = ?, half_days = ?, short_leaves = ?, paid_leaves = ?,
                            total_hours_worked = ?, regular_hours = ?, ot_hours = ?, ot_pay = ?, base_salary = ?,
                            allowances = ?, attendance_incentive = ?, production_incentive = ?, other_allowances = ?,
                            deductions = ?, salary_advance = ?, other_deductions = ?, no_pay = ?, net_salary = ?,
                            epf_employee_amount = ?, epf_employer_amount = ?, etf_amount = ?, status = 'confirmed', processed_by = ?
                        WHERE id = ?
                    ", [
                        $workingDays, $daysPresent, $daysAbsent, $halfDaysCount, $shortLeavesCount, $fullDaysCount,
                        $hoursWorked, $regularHours, $approvedOvertimeHours, $otPay, $baseSalary,
                        $allowances, $attendanceIncentive, $productionIncentive, $otherAllowances,
                        $deductions, $salaryAdvanceSum, $otherDeductions, $noPay, $netSalary,
                        $epfEeAmount, $epfErAmount, $etfAmount, $user_id, $existing['id']
                    ]);
                } else {
                    query("
                        INSERT INTO payrolls (
                            employee_id, company_id, period_start, period_end, total_working_days, 
                            days_present, days_absent, half_days, short_leaves, paid_leaves, 
                            total_hours_worked, regular_hours, ot_hours, ot_pay, base_salary, 
                            allowances, attendance_incentive, production_incentive, other_allowances, 
                            deductions, salary_advance, other_deductions, no_pay, net_salary, 
                            epf_employee_amount, epf_employer_amount, etf_amount, status, processed_by
                        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'confirmed', ?)
                    ", [
                        $empId, $company_id, $firstDay, $lastDay, $workingDays, 
                        $daysPresent, $daysAbsent, $halfDaysCount, $shortLeavesCount, $fullDaysCount, 
                        $hoursWorked, $regularHours, $approvedOvertimeHours, $otPay, $baseSalary, 
                        $allowances, $attendanceIncentive, $productionIncentive, $otherAllowances, 
                        $deductions, $salaryAdvanceSum, $otherDeductions, $noPay, $netSalary,
                        $epfEeAmount, $epfErAmount, $etfAmount, $user_id
                    ]);
                }
            }
            
            $pdo->commit();
            logAudit('Payroll Confirmed', "Finalized and confirmed payroll for $firstDay to $lastDay");
            echo json_encode(['success' => true, 'message' => 'Payroll processed and confirmed successfully']);
        } catch (Exception $e) {
            if ($pdo->inTransaction()) $pdo->rollBack();
            echo json_encode(['success' => false, 'message' => 'Error: ' . $e->getMessage()]);
        }
        break;

    case 'get_advances':
        $empId = intval($_GET['employee_id'] ?? $_POST['employee_id'] ?? $data['employee_id'] ?? 0);
        $month = intval($_GET['month'] ?? $_POST['month'] ?? $data['month'] ?? date('m'));
        $year = intval($_GET['year'] ?? $_POST['year'] ?? $data['year'] ?? date('Y'));
        
        $config = fetch("SELECT * FROM payroll_configurations WHERE company_id = ?", [$company_id]);
        $startDay = $config ? intval($config['cycle_start_day']) : 1;
        $endDay = $config ? intval($config['cycle_end_day']) : 0;
        
        if ($startDay == 1) {
            $firstDay = sprintf('%04d-%02d-01', $year, $month);
            $lastDay = date('Y-m-t', strtotime($firstDay));
        } else {
            $prevMonth = $month - 1;
            $prevYear = $year;
            if ($prevMonth == 0) {
                $prevMonth = 12;
                $prevYear = $year - 1;
            }
            $firstDay = sprintf('%04d-%02d-%02d', $prevYear, $prevMonth, $startDay);
            $actualEndDay = $endDay > 0 ? $endDay : ($startDay - 1);
            $daysInMonth = intval(date('t', strtotime(sprintf('%04d-%02d-01', $year, $month))));
            if ($actualEndDay > $daysInMonth) $actualEndDay = $daysInMonth;
            $lastDay = sprintf('%04d-%02d-%02d', $year, $month, $actualEndDay);
        }
        
        $advances = fetchAll("
            SELECT id, advance_date, amount 
            FROM salary_advances 
            WHERE employee_id = ? AND company_id = ? 
              AND advance_date >= CONCAT(?, ' 00:00:00') 
              AND advance_date <= CONCAT(?, ' 23:59:59')
            ORDER BY advance_date DESC
        ", [$empId, $company_id, $firstDay, $lastDay]);
        
        echo json_encode(['success' => true, 'advances' => $advances]);
        break;
 
    case 'add_advance':
        $empId = intval($_POST['employee_id'] ?? $data['employee_id'] ?? 0);
        $amount = floatval($_POST['amount'] ?? $data['amount'] ?? 0);
        $advanceDate = $_POST['advance_date'] ?? $data['advance_date'] ?? date('Y-m-d H:i:s');
        $month = intval($_POST['month'] ?? $data['month'] ?? date('m'));
        $year = intval($_POST['year'] ?? $data['year'] ?? date('Y'));
        
        if (!$empId || $amount <= 0) {
            echo json_encode(['success' => false, 'message' => 'Invalid parameters']);
            exit;
        }
        
        // Check if payroll is already paid/locked for this month
        $config = fetch("SELECT * FROM payroll_configurations WHERE company_id = ?", [$company_id]);
        $startDay = $config ? intval($config['cycle_start_day']) : 1;
        $endDay = $config ? intval($config['cycle_end_day']) : 0;
        if ($startDay == 1) {
            $firstDay = sprintf('%04d-%02d-01', $year, $month);
            $lastDay = date('Y-m-t', strtotime($firstDay));
        } else {
            $prevMonth = $month - 1;
            $prevYear = $year;
            if ($prevMonth == 0) {
                $prevMonth = 12;
                $prevYear = $year - 1;
            }
            $firstDay = sprintf('%04d-%02d-%02d', $prevYear, $prevMonth, $startDay);
            $actualEndDay = $endDay > 0 ? $endDay : ($startDay - 1);
            $daysInMonth = intval(date('t', strtotime(sprintf('%04d-%02d-01', $year, $month))));
            if ($actualEndDay > $daysInMonth) $actualEndDay = $daysInMonth;
            $lastDay = sprintf('%04d-%02d-%02d', $year, $month, $actualEndDay);
        }
        
        $existing = fetch("
            SELECT id, status FROM payrolls 
            WHERE employee_id = ? AND company_id = ? AND period_start = ? AND period_end = ?
        ", [$empId, $company_id, $firstDay, $lastDay]);
        
        if ($existing && ($existing['status'] === 'confirmed' || $existing['status'] === 'paid')) {
            echo json_encode(['success' => false, 'message' => 'Cannot add advance to a confirmed/locked payroll period']);
            exit;
        }
        
        try {
            query("
                INSERT INTO salary_advances (employee_id, company_id, advance_date, amount) 
                VALUES (?, ?, ?, ?)
            ", [$empId, $company_id, $advanceDate, $amount]);
            
            echo json_encode(['success' => true, 'message' => 'Salary advance recorded successfully']);
        } catch (Exception $e) {
            echo json_encode(['success' => false, 'message' => 'Error: ' . $e->getMessage()]);
        }
        break;
 
    case 'delete_advance':
        $advanceId = intval($_POST['id'] ?? $data['id'] ?? 0);
        $empId = intval($_POST['employee_id'] ?? $data['employee_id'] ?? 0);
        $month = intval($_POST['month'] ?? $data['month'] ?? date('m'));
        $year = intval($_POST['year'] ?? $data['year'] ?? date('Y'));
        
        if (!$advanceId || !$empId) {
            echo json_encode(['success' => false, 'message' => 'Invalid parameters']);
            exit;
        }
        
        $config = fetch("SELECT * FROM payroll_configurations WHERE company_id = ?", [$company_id]);
        $startDay = $config ? intval($config['cycle_start_day']) : 1;
        $endDay = $config ? intval($config['cycle_end_day']) : 0;
        if ($startDay == 1) {
            $firstDay = sprintf('%04d-%02d-01', $year, $month);
            $lastDay = date('Y-m-t', strtotime($firstDay));
        } else {
            $prevMonth = $month - 1;
            $prevYear = $year;
            if ($prevMonth == 0) {
                $prevMonth = 12;
                $prevYear = $year - 1;
            }
            $firstDay = sprintf('%04d-%02d-%02d', $prevYear, $prevMonth, $startDay);
            $actualEndDay = $endDay > 0 ? $endDay : ($startDay - 1);
            $daysInMonth = intval(date('t', strtotime(sprintf('%04d-%02d-01', $year, $month))));
            if ($actualEndDay > $daysInMonth) $actualEndDay = $daysInMonth;
            $lastDay = sprintf('%04d-%02d-%02d', $year, $month, $actualEndDay);
        }
        
        $existing = fetch("
            SELECT id, status FROM payrolls 
            WHERE employee_id = ? AND company_id = ? AND period_start = ? AND period_end = ?
        ", [$empId, $company_id, $firstDay, $lastDay]);
        
        if ($existing && ($existing['status'] === 'confirmed' || $existing['status'] === 'paid')) {
            echo json_encode(['success' => false, 'message' => 'Cannot delete advance from a confirmed/locked payroll period']);
            exit;
        }
        
        try {
            query("DELETE FROM salary_advances WHERE id = ? AND employee_id = ? AND company_id = ?", [$advanceId, $empId, $company_id]);
            
            echo json_encode(['success' => true, 'message' => 'Salary advance deleted successfully']);
        } catch (Exception $e) {
            echo json_encode(['success' => false, 'message' => 'Error: ' . $e->getMessage()]);
        }
        break;

    case 'mark_cycle_paid':
        $month = intval($_POST['month'] ?? $data['month'] ?? date('m'));
        $year = intval($_POST['year'] ?? $data['year'] ?? date('Y'));

        $config = fetch("SELECT * FROM payroll_configurations WHERE company_id = ?", [$company_id]);
        $startDay = $config ? intval($config['cycle_start_day']) : 1;
        $endDay = $config ? intval($config['cycle_end_day']) : 0;

        if ($startDay == 1) {
            $firstDay = sprintf('%04d-%02d-01', $year, $month);
            $lastDay = date('Y-m-t', strtotime($firstDay));
        } else {
            $prevMonth = $month - 1;
            $prevYear = $year;
            if ($prevMonth == 0) { $prevMonth = 12; $prevYear = $year - 1; }
            $firstDay = sprintf('%04d-%02d-%02d', $prevYear, $prevMonth, $startDay);
            $actualEndDay = $endDay > 0 ? $endDay : ($startDay - 1);
            $daysInMonth = intval(date('t', strtotime(sprintf('%04d-%02d-01', $year, $month))));
            if ($actualEndDay > $daysInMonth) $actualEndDay = $daysInMonth;
            $lastDay = sprintf('%04d-%02d-%02d', $year, $month, $actualEndDay);
        }

        // Ensure ALL records in this cycle are confirmed before allowing paid
        $notConfirmed = fetchValue("
            SELECT COUNT(*) FROM employees WHERE company_id = ?
        ", [$company_id]);
        $confirmedCount = fetchValue("
            SELECT COUNT(*) FROM payrolls 
            WHERE company_id = ? AND period_start = ? AND period_end = ? AND (status = 'confirmed' OR status = 'paid')
        ", [$company_id, $firstDay, $lastDay]);
        $totalEmployees = fetchValue("SELECT COUNT(*) FROM employees WHERE company_id = ?", [$company_id]);

        if (intval($confirmedCount) < intval($totalEmployees)) {
            echo json_encode(['success' => false, 'message' => 'Not all employees have been confirmed. Please confirm all employees before marking the cycle as Paid.']);
            exit;
        }

        try {
            query("
                UPDATE payrolls 
                SET status = 'paid'
                WHERE company_id = ? AND period_start = ? AND period_end = ? AND status = 'confirmed'
            ", [$company_id, $firstDay, $lastDay]);

            logAudit('Payroll Paid', "Marked payroll cycle as paid for $firstDay to $lastDay");
            echo json_encode(['success' => true, 'message' => 'Payroll cycle has been marked as Paid successfully']);
        } catch (Exception $e) {
            echo json_encode(['success' => false, 'message' => 'Error: ' . $e->getMessage()]);
        }
        break;

    case 'export_payroll':
        $month = intval($_POST['month'] ?? $data['month'] ?? date('m'));
        $year = intval($_POST['year'] ?? $data['year'] ?? date('Y'));

        $config = fetch("SELECT * FROM payroll_configurations WHERE company_id = ?", [$company_id]);
        $startDay = $config ? intval($config['cycle_start_day']) : 1;
        $endDay = $config ? intval($config['cycle_end_day']) : 0;

        if ($startDay == 1) {
            $firstDay = sprintf('%04d-%02d-01', $year, $month);
            $lastDay = date('Y-m-t', strtotime($firstDay));
        } else {
            $prevMonth = $month - 1;
            $prevYear = $year;
            if ($prevMonth == 0) { $prevMonth = 12; $prevYear = $year - 1; }
            $firstDay = sprintf('%04d-%02d-%02d', $prevYear, $prevMonth, $startDay);
            $actualEndDay = $endDay > 0 ? $endDay : ($startDay - 1);
            $daysInMonth = intval(date('t', strtotime(sprintf('%04d-%02d-01', $year, $month))));
            if ($actualEndDay > $daysInMonth) $actualEndDay = $daysInMonth;
            $lastDay = sprintf('%04d-%02d-%02d', $year, $month, $actualEndDay);
        }

        $records = fetchAll("
            SELECT 
                p.employee_id, e.name, e.hourly_rate,
                p.total_working_days, p.days_present, p.days_absent,
                p.regular_hours, p.ot_hours, p.ot_pay,
                p.base_salary, p.attendance_incentive, p.production_incentive,
                p.other_allowances, p.allowances,
                p.no_pay, p.epf_employee_amount, p.epf_employer_amount, p.etf_amount,
                p.other_deductions, p.deductions,
                p.salary_advance, p.net_salary, p.status,
                p.period_start, p.period_end
            FROM payrolls p
            JOIN employees e ON e.id = p.employee_id
            WHERE p.company_id = ? AND p.period_start = ? AND p.period_end = ?
            ORDER BY e.name ASC
        ", [$company_id, $firstDay, $lastDay]);

        echo json_encode(['success' => true, 'records' => $records, 'period_start' => $firstDay, 'period_end' => $lastDay]);
        break;

    case 'get_payroll_history':
        // Return all payroll cycles grouped by period_start/period_end with aggregate totals
        $cycles = fetchAll("
            SELECT 
                period_start,
                period_end,
                COUNT(*) AS employee_count,
                SUM(base_salary) AS total_base,
                SUM(ot_pay) AS total_ot,
                SUM(allowances) AS total_allowances,
                SUM(deductions) AS total_deductions,
                SUM(salary_advance) AS total_advances,
                SUM(net_salary) AS total_net,
                SUM(epf_employee_amount) AS total_epf_ee,
                SUM(epf_employer_amount) AS total_epf_er,
                SUM(etf_amount) AS total_etf,
                MAX(status) AS cycle_status,
                MAX(updated_at) AS last_updated
            FROM payrolls
            WHERE company_id = ?
            GROUP BY period_start, period_end
            ORDER BY period_start DESC
        ", [$company_id]);

        // For each cycle, also get the per-employee records
        $history = [];
        foreach ($cycles as $cycle) {
            $employees = fetchAll("
                SELECT 
                    p.employee_id,
                    e.name,
                    p.total_working_days, p.days_present, p.days_absent,
                    p.regular_hours, p.ot_hours, p.ot_pay,
                    p.base_salary, p.attendance_incentive, p.production_incentive,
                    p.other_allowances, p.allowances,
                    p.no_pay, p.epf_employee_amount, p.salary_advance,
                    p.other_deductions, p.deductions, p.net_salary, p.status
                FROM payrolls p
                JOIN employees e ON e.id = p.employee_id
                WHERE p.company_id = ? AND p.period_start = ? AND p.period_end = ?
                ORDER BY e.name ASC
            ", [$company_id, $cycle['period_start'], $cycle['period_end']]);

            $history[] = array_merge($cycle, ['employees' => $employees]);
        }

        echo json_encode(['success' => true, 'history' => $history]);
        break;

    default:
        echo json_encode(['success' => false, 'message' => 'Invalid action']);
        break;
}
