<?php
namespace app\Models;

use PDO;

class PayrollModel extends Model {
    
    /**
     * Get all payroll months with their years
     */
    public function getAllPayrollPeriods() {
        $sql = "
            SELECT sm.*, sy.year_no 
            FROM salary_month sm
            JOIN salary_year sy ON sm.year_id = sy.id
            ORDER BY sy.year_no DESC, sm.month_no DESC
        ";
        $stmt = $this->db->prepare($sql);
        $stmt->execute();
        return $stmt->fetchAll();
    }
    
    /**
     * Check if a specific month and year already exists
     */
    public function checkPeriodExists($yearNo, $monthNo) {
        $sql = "
            SELECT sm.id 
            FROM salary_month sm
            JOIN salary_year sy ON sm.year_id = sy.id
            WHERE sy.year_no = :yearNo AND sm.month_no = :monthNo
            LIMIT 1
        ";
        $stmt = $this->db->prepare($sql);
        $stmt->bindParam(':yearNo', $yearNo, PDO::PARAM_INT);
        $stmt->bindParam(':monthNo', $monthNo, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetch() !== false;
    }
    
    /**
     * Create a new payroll period
     */
    public function createPayrollPeriod($yearNo, $monthNo) {
        try {
            $this->db->beginTransaction();
            
            // 1. Ensure Year Exists
            $stmtYear = $this->db->prepare("SELECT id FROM salary_year WHERE year_no = :yearNo LIMIT 1");
            $stmtYear->bindParam(':yearNo', $yearNo, PDO::PARAM_INT);
            $stmtYear->execute();
            $yearRow = $stmtYear->fetch();
            
            if ($yearRow) {
                $yearId = $yearRow['id'];
            } else {
                $stmtInsertYear = $this->db->prepare("INSERT INTO salary_year (year_no) VALUES (:yearNo)");
                $stmtInsertYear->bindParam(':yearNo', $yearNo, PDO::PARAM_INT);
                $stmtInsertYear->execute();
                $yearId = $this->db->lastInsertId();
            }
            
            // 2. Create Month (Draft)
            $stmtInsertMonth = $this->db->prepare("INSERT INTO salary_month (year_id, month_no, status) VALUES (:yearId, :monthNo, 'Draft')");
            $stmtInsertMonth->bindParam(':yearId', $yearId, PDO::PARAM_INT);
            $stmtInsertMonth->bindParam(':monthNo', $monthNo, PDO::PARAM_INT);
            $stmtInsertMonth->execute();
            
            $this->db->commit();
            return true;
            
        } catch (\PDOException $e) {
            error_log("Error creating payroll period: " . $e->getMessage());
            return false;
        }
    }
    
    /**
     * Get the previous salary month ID relative to the given year and month
     */
    public function getPreviousSalaryMonth($yearNo, $monthNo) {
        $prevMonth = $monthNo - 1;
        $prevYear = $yearNo;
        if ($prevMonth < 1) {
            $prevMonth = 12;
            $prevYear--;
        }
        
        $sql = "SELECT m.id 
                FROM salary_month m 
                JOIN salary_year y ON m.year_id = y.id 
                WHERE y.year_no = :yearNo AND m.month_no = :monthNo";
        try {
            $stmt = $this->db->prepare($sql);
            $stmt->execute(['yearNo' => $prevYear, 'monthNo' => $prevMonth]);
            $result = $stmt->fetch();
            return $result ? $result['id'] : false;
        } catch (\PDOException $e) {
            error_log("Error getting previous salary month: " . $e->getMessage());
            return false;
        }
    }
    
    /**
     * Copy employees and their recurring items from previous month
     */
    public function copyFromPreviousMonth($currentMonthId, $previousMonthId) {
        try {
            $this->beginTransaction();
            
            // 1. Get ALL employees from previous month
            $sqlEmployees = "SELECT * FROM employee_salary WHERE salary_month_id = :prev_id";
            $stmtEmp = $this->db->prepare($sqlEmployees);
            $stmtEmp->execute(['prev_id' => $previousMonthId]);
            $prevEmployees = $stmtEmp->fetchAll();
            
            $copiedCount = 0;
            $mergedCount = 0;
            
            // Prepare statements for inserting NEW employee
            $stmtInsertEmp = $this->db->prepare("
                INSERT INTO employee_salary (
                    salary_month_id, employee_code, national_id, first_name, last_name, 
                    position, department, base_salary, total_income, total_deduction, net_salary
                ) VALUES (
                    :salary_month_id, :employee_code, :national_id, :first_name, :last_name,
                    :position, :department, :base_salary, :total_income, :total_deduction, :net_salary
                )
            ");
            
            // Prepare statements for getting items from previous month
            $stmtGetIncomes = $this->db->prepare("
                SELECT si.income_master_id, si.amount, im.fund_source 
                FROM salary_income si
                JOIN income_master im ON si.income_master_id = im.id
                WHERE si.employee_salary_id = ?
            ");
            $stmtGetDeductions = $this->db->prepare("
                SELECT sd.deduction_master_id, sd.amount, dm.fund_source 
                FROM salary_deduction sd
                JOIN deduction_master dm ON sd.deduction_master_id = dm.id
                WHERE sd.employee_salary_id = ?
            ");
            
            // Prepare statements for inserting items
            $stmtInsertIncome = $this->db->prepare("INSERT INTO salary_income (employee_salary_id, income_master_id, amount) VALUES (?, ?, ?)");
            $stmtInsertDeduction = $this->db->prepare("INSERT INTO salary_deduction (employee_salary_id, deduction_master_id, amount) VALUES (?, ?, ?)");
            
            // Prepare statements for checking existing items (to prevent duplicates)
            $stmtCheckIncome = $this->db->prepare("SELECT 1 FROM salary_income WHERE employee_salary_id = ? AND income_master_id = ?");
            $stmtCheckDeduction = $this->db->prepare("SELECT 1 FROM salary_deduction WHERE employee_salary_id = ? AND deduction_master_id = ?");
            
            // Prepare statement for updating totals of existing employee
            $stmtUpdateTotals = $this->db->prepare("
                UPDATE employee_salary 
                SET total_income = total_income + ?, 
                    total_deduction = total_deduction + ?, 
                    net_salary = net_salary + ? - ? 
                WHERE id = ?
            ");

            // Prepare statement to check if employee already exists in current month
            $stmtCheckEmp = $this->db->prepare("SELECT id FROM employee_salary WHERE salary_month_id = ? AND national_id = ?");

            foreach ($prevEmployees as $emp) {
                // Check if employee already exists in current month
                $stmtCheckEmp->execute([$currentMonthId, $emp['national_id']]);
                $existingEmp = $stmtCheckEmp->fetch();
                
                if (!$existingEmp) {
                    // --- CASE 1: Employee DOES NOT exist in current month (Fully copy) ---
                    $stmtInsertEmp->execute([
                        'salary_month_id' => $currentMonthId,
                        'employee_code' => $emp['employee_code'],
                        'national_id' => $emp['national_id'],
                        'first_name' => $emp['first_name'],
                        'last_name' => $emp['last_name'],
                        'position' => $emp['position'],
                        'department' => $emp['department'],
                        'base_salary' => $emp['base_salary'],
                        'total_income' => $emp['total_income'],
                        'total_deduction' => $emp['total_deduction'],
                        'net_salary' => $emp['net_salary']
                    ]);
                    $newEmpId = $this->db->lastInsertId();
                    
                    // Copy ALL incomes
                    $stmtGetIncomes->execute([$emp['id']]);
                    $incomes = $stmtGetIncomes->fetchAll();
                    foreach ($incomes as $inc) {
                        $stmtInsertIncome->execute([$newEmpId, $inc['income_master_id'], $inc['amount']]);
                    }
                    
                    // Copy ALL deductions
                    $stmtGetDeductions->execute([$emp['id']]);
                    $deductions = $stmtGetDeductions->fetchAll();
                    foreach ($deductions as $ded) {
                        $stmtInsertDeduction->execute([$newEmpId, $ded['deduction_master_id'], $ded['amount']]);
                    }
                    
                    $copiedCount++;
                } else {
                    // --- CASE 2: Employee EXISTS in current month (Merge HOSPITAL items) ---
                    $currEmpId = $existingEmp['id'];
                    $addedIncomeAmount = 0;
                    $addedDeductionAmount = 0;
                    
                    // Copy ONLY HOSPITAL incomes
                    $stmtGetIncomes->execute([$emp['id']]);
                    $incomes = $stmtGetIncomes->fetchAll();
                    foreach ($incomes as $inc) {
                        if (strtoupper($inc['fund_source']) === 'HOSPITAL') {
                            // Prevent duplicate insertion
                            $stmtCheckIncome->execute([$currEmpId, $inc['income_master_id']]);
                            if (!$stmtCheckIncome->fetch()) {
                                $stmtInsertIncome->execute([$currEmpId, $inc['income_master_id'], $inc['amount']]);
                                $addedIncomeAmount += $inc['amount'];
                            }
                        }
                    }
                    
                    // Copy ONLY HOSPITAL deductions
                    $stmtGetDeductions->execute([$emp['id']]);
                    $deductions = $stmtGetDeductions->fetchAll();
                    foreach ($deductions as $ded) {
                        if (strtoupper($ded['fund_source']) === 'HOSPITAL') {
                            // Prevent duplicate insertion
                            $stmtCheckDeduction->execute([$currEmpId, $ded['deduction_master_id']]);
                            if (!$stmtCheckDeduction->fetch()) {
                                $stmtInsertDeduction->execute([$currEmpId, $ded['deduction_master_id'], $ded['amount']]);
                                $addedDeductionAmount += $ded['amount'];
                            }
                        }
                    }
                    
                    // Update employee totals if anything was added
                    if ($addedIncomeAmount > 0 || $addedDeductionAmount > 0) {
                        $stmtUpdateTotals->execute([
                            $addedIncomeAmount,
                            $addedDeductionAmount,
                            $addedIncomeAmount, // Add to net
                            $addedDeductionAmount, // Subtract from net
                            $currEmpId
                        ]);
                    }
                    
                    $mergedCount++;
                }
            }
            
            $this->commit();
            return ['copied' => $copiedCount, 'merged' => $mergedCount];
            
        } catch (\PDOException $e) {
            $this->rollBack();
            error_log("Error copying from previous month: " . $e->getMessage());
            return false;
        }
    }
    
    // --- Methods for Import ---

    public function beginTransaction() {
        if (!$this->db->inTransaction()) {
            $this->db->beginTransaction();
        }
    }
    
    public function commit() {
        if ($this->db->inTransaction()) {
            $this->db->commit();
        }
    }
    
    public function rollBack() {
        if ($this->db->inTransaction()) {
            $this->db->rollBack();
        }
    }
    
    public function getSalaryMonthById($id) {
        $stmt = $this->db->prepare("SELECT sm.*, sy.year_no FROM salary_month sm JOIN salary_year sy ON sm.year_id = sy.id WHERE sm.id = ?");
        $stmt->execute([$id]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    public function updateSalaryMonthStatus($id, $status) {
        $stmt = $this->db->prepare("UPDATE salary_month SET status = ? WHERE id = ?");
        return $stmt->execute([$status, $id]);
    }
    
    public function logImportHistory($userId, $salaryMonthId, $fileName, $total, $success, $error, $status = 'success') {
        $sql = "INSERT INTO import_history (user_id, salary_month_id, file_name, status, total_records, success_records, error_records) 
                VALUES (?, ?, ?, ?, ?, ?, ?)";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$userId, $salaryMonthId, $fileName, $status, $total, $success, $error]);
        return $this->db->lastInsertId();
    }
    
    public function insertEmployeeSalary($data) {
        $check = $this->db->prepare("SELECT id FROM employee_salary WHERE salary_month_id = ? AND employee_code = ?");
        $check->execute([$data['salary_month_id'], $data['employee_code']]);
        $existing = $check->fetch(PDO::FETCH_ASSOC);
        
        if ($existing) {
            $sql = "UPDATE employee_salary SET national_id = ?, first_name = ?, last_name = ?, 
                    position = ?, department = ?, base_salary = ?, total_income = ?, total_deduction = ?, net_salary = ? 
                    WHERE id = ?";
            $stmt = $this->db->prepare($sql);
            $stmt->execute([
                $data['national_id'], $data['first_name'], $data['last_name'],
                $data['position'] ?? null, $data['department'] ?? null,
                $data['base_salary'], $data['total_income'], $data['total_deduction'], $data['net_salary'],
                $existing['id']
            ]);
            
            // Delete old details
            $this->db->prepare("DELETE FROM salary_income WHERE employee_salary_id = ?")->execute([$existing['id']]);
            $this->db->prepare("DELETE FROM salary_deduction WHERE employee_salary_id = ?")->execute([$existing['id']]);
            
            return $existing['id'];
        } else {
            $sql = "INSERT INTO employee_salary (salary_month_id, employee_code, national_id, first_name, last_name, position, department, base_salary, total_income, total_deduction, net_salary) 
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            $stmt = $this->db->prepare($sql);
            $stmt->execute([
                $data['salary_month_id'], $data['employee_code'], $data['national_id'], 
                $data['first_name'], $data['last_name'], $data['position'] ?? null, $data['department'] ?? null, $data['base_salary'],
                $data['total_income'], $data['total_deduction'], $data['net_salary']
            ]);
            return $this->db->lastInsertId();
        }
    }
    
    public function insertSalaryIncome($employeeSalaryId, $incomeMasterId, $amount) {
        $sql = "INSERT INTO salary_income (employee_salary_id, income_master_id, amount) VALUES (?, ?, ?)";
        $stmt = $this->db->prepare($sql);
        return $stmt->execute([$employeeSalaryId, $incomeMasterId, $amount]);
    }

    public function insertSalaryDeduction($employeeSalaryId, $deductionMasterId, $amount) {
        $sql = "INSERT INTO salary_deduction (employee_salary_id, deduction_master_id, amount) VALUES (?, ?, ?)";
        $stmt = $this->db->prepare($sql);
        return $stmt->execute([$employeeSalaryId, $deductionMasterId, $amount]);
    }

    public function getPeriodStats($salaryMonthId) {
        $stmt = $this->db->prepare("SELECT COUNT(id) as total_emp, SUM(net_salary) as total_net FROM employee_salary WHERE salary_month_id = ?");
        $stmt->execute([$salaryMonthId]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    public function clearDataForPeriod($salaryMonthId) {
        try {
            $this->beginTransaction();
            // Delete all employee salaries for this month (cascade will delete salary_income, salary_deduction, slip logs)
            $stmt = $this->db->prepare("DELETE FROM employee_salary WHERE salary_month_id = ?");
            $stmt->execute([$salaryMonthId]);

            // Update status back to Draft
            $this->updateSalaryMonthStatus($salaryMonthId, 'Draft');

            $this->commit();
            return true;
        } catch (\Exception $e) {
            $this->rollBack();
            error_log("Error clearing data: " . $e->getMessage());
            return false;
        }
    }

    public function getEmployeesBySalaryMonthId($salaryMonthId) {
        $stmt = $this->db->prepare("SELECT * FROM employee_salary WHERE salary_month_id = ? ORDER BY first_name ASC");
        $stmt->execute([$salaryMonthId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function deleteEmployeeSalary($id) {
        try {
            $this->beginTransaction();
            // Delete incomes and deductions first
            $this->db->prepare("DELETE FROM salary_income WHERE employee_salary_id = ?")->execute([$id]);
            $this->db->prepare("DELETE FROM salary_deduction WHERE employee_salary_id = ?")->execute([$id]);
            
            // Delete main record
            $stmt = $this->db->prepare("DELETE FROM employee_salary WHERE id = ?");
            $stmt->execute([$id]);
            
            $this->commit();
            return true;
        } catch (\Exception $e) {
            $this->rollBack();
            return false;
        }
    }

    public function getEmployeeSalaryById($id) {
        $stmt = $this->db->prepare("SELECT * FROM employee_salary WHERE id = ?");
        $stmt->execute([$id]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    public function getEmployeeIncomes($employeeSalaryId) {
        $stmt = $this->db->prepare("SELECT si.*, im.name, im.code, im.fund_source 
                                    FROM salary_income si 
                                    JOIN income_master im ON si.income_master_id = im.id 
                                    WHERE si.employee_salary_id = ?");
        $stmt->execute([$employeeSalaryId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getEmployeeDeductions($employeeSalaryId) {
        $stmt = $this->db->prepare("SELECT sd.*, dm.name, dm.code, dm.fund_source 
                                    FROM salary_deduction sd 
                                    JOIN deduction_master dm ON sd.deduction_master_id = dm.id 
                                    WHERE sd.employee_salary_id = ?");
        $stmt->execute([$employeeSalaryId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function updateEmployeeSalaryDetails($id, $baseSalary, $incomes, $deductions, $totalIncome, $totalDeduction, $netSalary) {
        try {
            $this->beginTransaction();

            // 1. Update main record
            $stmt = $this->db->prepare("UPDATE employee_salary 
                                        SET base_salary = ?, total_income = ?, total_deduction = ?, net_salary = ? 
                                        WHERE id = ?");
            $stmt->execute([$baseSalary, $totalIncome, $totalDeduction, $netSalary, $id]);

            // 2. Delete old incomes and deductions
            $this->db->prepare("DELETE FROM salary_income WHERE employee_salary_id = ?")->execute([$id]);
            $this->db->prepare("DELETE FROM salary_deduction WHERE employee_salary_id = ?")->execute([$id]);

            // 3. Insert new incomes
            if (!empty($incomes)) {
                $stmtInc = $this->db->prepare("INSERT INTO salary_income (employee_salary_id, income_master_id, amount) VALUES (?, ?, ?)");
                foreach ($incomes as $inc) {
                    $stmtInc->execute([$id, $inc['id'], $inc['amount']]);
                }
            }

            // 4. Insert new deductions
            if (!empty($deductions)) {
                $stmtDed = $this->db->prepare("INSERT INTO salary_deduction (employee_salary_id, deduction_master_id, amount) VALUES (?, ?, ?)");
                foreach ($deductions as $ded) {
                    $stmtDed->execute([$id, $ded['id'], $ded['amount']]);
                }
            }

            $this->commit();
            return true;
        } catch (\Exception $e) {
            $this->rollBack();
            error_log("Error updating employee salary: " . $e->getMessage());
            return false;
        }
    }
    public function getSalariesByPeriod($month, $year) {
        $stmt = $this->db->prepare("SELECT es.*, sm.month_no as month, sy.year_no as year 
                                    FROM employee_salary es
                                    JOIN salary_month sm ON es.salary_month_id = sm.id
                                    JOIN salary_year sy ON sm.year_id = sy.id
                                    WHERE sm.month_no = ? AND sy.year_no = ?
                                    ORDER BY es.first_name ASC");
        $stmt->execute([$month, $year]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getSalaryByNationalId($nationalId, $month, $year) {
        $stmt = $this->db->prepare("SELECT es.*, sm.month_no as month, sy.year_no as year 
                                    FROM employee_salary es
                                    JOIN salary_month sm ON es.salary_month_id = sm.id
                                    JOIN salary_year sy ON sm.year_id = sy.id
                                    WHERE REPLACE(es.national_id, '-', '') = ? AND sm.month_no = ? AND sy.year_no = ?");
        $stmt->execute([$nationalId, $month, $year]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    public function getYearlyTaxSummary($nationalId, $year) {
        $stmt = $this->db->prepare("
            SELECT 
                SUM(es.total_income) as total_income, 
                SUM(
                    (SELECT SUM(si.amount) FROM salary_income si 
                     JOIN income_master im ON si.income_master_id = im.id 
                     WHERE si.employee_salary_id = es.id AND im.is_taxable = 1)
                ) as total_taxable_income,
                SUM(
                    (SELECT SUM(sd.amount) FROM salary_deduction sd 
                     JOIN deduction_master dm ON sd.deduction_master_id = dm.id 
                     WHERE sd.employee_salary_id = es.id AND dm.code = 'TAX')
                ) as total_tax,
                SUM(
                    (SELECT SUM(sd.amount) FROM salary_deduction sd 
                     JOIN deduction_master dm ON sd.deduction_master_id = dm.id 
                     WHERE sd.employee_salary_id = es.id AND dm.code IN ('FUND_A', 'FUND_B'))
                ) as total_ssf
            FROM employee_salary es
            JOIN salary_month sm ON es.salary_month_id = sm.id
            JOIN salary_year sy ON sm.year_id = sy.id
            WHERE REPLACE(es.national_id, '-', '') = ? AND sy.year_no = ? AND sm.status IN ('Locked', 'Published')
        ");
        $stmt->execute([$nationalId, $year]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    public function getYearlySummary($year) {
        $sql = "SELECT sm.month_no AS month, 
                       SUM(e.total_income) AS sum_income, 
                       SUM(e.total_deduction) AS sum_deduction 
                FROM salary_month sm
                JOIN salary_year sy ON sm.year_id = sy.id
                LEFT JOIN employee_salary e ON sm.id = e.salary_month_id
                WHERE sy.year_no = :year
                GROUP BY sm.month_no 
                ORDER BY sm.month_no ASC";
        $stmt = $this->db->prepare($sql);
        $stmt->bindParam(':year', $year, \PDO::PARAM_STR);
        $stmt->execute();
        
        $results = $stmt->fetchAll(\PDO::FETCH_ASSOC);
        
        $summary = [];
        foreach($results as $row) {
            $summary[$row['month']] = [
                'income' => (float)($row['sum_income'] ?? 0),
                'deduction' => (float)($row['sum_deduction'] ?? 0)
            ];
        }
        return $summary;
    }

    public function getAvailableSlipsByNationalId($nationalId) {
        $sql = "SELECT sm.month_no, sy.year_no 
                FROM employee_salary es
                JOIN salary_month sm ON es.salary_month_id = sm.id
                JOIN salary_year sy ON sm.year_id = sy.id
                WHERE REPLACE(es.national_id, '-', '') = :nationalId AND sm.status IN ('Locked', 'Published')
                ORDER BY sy.year_no DESC, sm.month_no DESC";
        $stmt = $this->db->prepare($sql);
        $stmt->bindParam(':nationalId', $nationalId, \PDO::PARAM_STR);
        $stmt->execute();
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    public function getAvailableTaxesByNationalId($nationalId) {
        $sql = "SELECT DISTINCT sy.year_no 
                FROM employee_salary es
                JOIN salary_month sm ON es.salary_month_id = sm.id
                JOIN salary_year sy ON sm.year_id = sy.id
                WHERE REPLACE(es.national_id, '-', '') = :nationalId AND sm.status IN ('Locked', 'Published')
                ORDER BY sy.year_no DESC";
        $stmt = $this->db->prepare($sql);
        $stmt->bindParam(':nationalId', $nationalId, \PDO::PARAM_STR);
        $stmt->execute();
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }
}
?>