<?php

require_once __DIR__ . '/../config/config.php';
require_once __DIR__ . '/Employee.php';
require_once __DIR__ . '/TimeRecord.php';

class Payroll {
    private $pdo;
    
    public function __construct() {
        try {
            $this->pdo = new PDO(
                "mysql:host=" . DB_HOST . ";dbname=" . DB_NAME . ";charset=utf8mb4",
                DB_USER,
                DB_PASS,
                [
                    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                    PDO::ATTR_EMULATE_PREPARES => false
                ]
            );
        } catch (PDOException $e) {
            throw new Exception("Erro na conexão com o banco de dados: " . $e->getMessage());
        }
    }
    
    /**
     * Criar nova folha de pagamento
     */
    public function create($data) {
        // Calcular totais
        $total_earnings = ($data['base_salary'] ?? 0) + ($data['overtime_amount'] ?? 0);
        $total_discounts = ($data['inss_discount'] ?? 0) + ($data['irrf_discount'] ?? 0) + 
                          ($data['transport_discount'] ?? 0) + ($data['meal_discount'] ?? 0) + 
                          ($data['absence_discount'] ?? 0);
        
        $sql = "INSERT INTO payrolls (employee_id, reference_month, reference_year, 
                base_salary, overtime_amount, total_earnings, 
                inss_discount, irrf_discount, transport_discount, meal_discount, absence_discount, 
                total_discounts, net_salary, worked_days, absence_days, overtime_hours) 
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        
        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute([
            $data['employee_id'],
            $data['reference_month'],
            $data['reference_year'],
            $data['base_salary'],
            $data['overtime_amount'] ?? 0,
            $total_earnings,
            $data['inss_discount'] ?? 0,
            $data['irrf_discount'] ?? 0,
            $data['transport_discount'] ?? 0,
            $data['meal_discount'] ?? 0,
            $data['absence_discount'] ?? 0,
            $total_discounts,
            $data['net_salary'],
            $data['worked_days'] ?? 0,
            $data['absence_days'] ?? 0,
            $data['overtime_hours'] ?? 0
        ]);
    }
    
    /**
     * Buscar folha de pagamento por ID
     */
    public function findById($id) {
        $sql = "SELECT p.*, e.full_name as employee_name, e.email as employee_email, 
                e.job_title as position, e.cpf, e.salary as employee_salary
                FROM payrolls p 
                JOIN employees e ON p.employee_id = e.id 
                WHERE p.id = ?";
        
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([$id]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }
    
    /**
     * Buscar folhas de pagamento com filtros
     */
    public function findAll($filters = []) {
        $sql = "SELECT p.*, e.full_name as employee_name, e.job_title as position, e.cpf 
                FROM payrolls p 
                JOIN employees e ON p.employee_id = e.id 
                WHERE 1=1";
        
        $params = [];
        
        if (!empty($filters['employee_id'])) {
            $sql .= " AND p.employee_id = ?";
            $params[] = $filters['employee_id'];
        }
        
        if (!empty($filters['reference_month'])) {
            $sql .= " AND p.reference_month = ?";
            $params[] = $filters['reference_month'];
        }
        
        if (!empty($filters['reference_year'])) {
            $sql .= " AND p.reference_year = ?";
            $params[] = $filters['reference_year'];
        }
        
        if (!empty($filters['status'])) {
            $sql .= " AND p.status = ?";
            $params[] = $filters['status'];
        }
        
        $sql .= " ORDER BY p.reference_year DESC, p.reference_month DESC, e.full_name ASC";
        
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    
    /**
     * Atualizar folha de pagamento
     */
    public function update($id, $data) {
        // Calcular totais
        $total_earnings = ($data['base_salary'] ?? 0) + ($data['overtime_amount'] ?? 0);
        $total_discounts = ($data['inss_discount'] ?? 0) + ($data['irrf_discount'] ?? 0) + 
                          ($data['transport_discount'] ?? 0) + ($data['meal_discount'] ?? 0) + 
                          ($data['absence_discount'] ?? 0);
        
        $sql = "UPDATE payrolls SET 
                base_salary = ?, overtime_amount = ?, total_earnings = ?, 
                inss_discount = ?, irrf_discount = ?, 
                transport_discount = ?, meal_discount = ?, absence_discount = ?, 
                total_discounts = ?, net_salary = ?, worked_days = ?, absence_days = ?, 
                overtime_hours = ? 
                WHERE id = ?";
        
        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute([
            $data['base_salary'],
            $data['overtime_amount'] ?? 0,
            $total_earnings,
            $data['inss_discount'] ?? 0,
            $data['irrf_discount'] ?? 0,
            $data['transport_discount'] ?? 0,
            $data['meal_discount'] ?? 0,
            $data['absence_discount'] ?? 0,
            $total_discounts,
            $data['net_salary'],
            $data['worked_days'] ?? 0,
            $data['absence_days'] ?? 0,
            $data['overtime_hours'] ?? 0,
            $id
        ]);
    }
    
    /**
     * Excluir folha de pagamento
     */
    public function delete($id) {
        $sql = "DELETE FROM payrolls WHERE id = ?";
        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute([$id]);
    }
    
    /**
     * Verificar se já existe folha para o colaborador no mês/ano
     */
    public function existsForPeriod($employeeId, $month, $year, $excludeId = null) {
        $sql = "SELECT COUNT(*) FROM payrolls 
                WHERE employee_id = ? AND reference_month = ? AND reference_year = ?";
        $params = [$employeeId, $month, $year];
        
        if ($excludeId) {
            $sql .= " AND id != ?";
            $params[] = $excludeId;
        }
        
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchColumn() > 0;
    }
    
    /**
     * Calcular folha de pagamento automaticamente
     */
    public function calculatePayroll($employeeId, $month, $year) {
        // Buscar dados do colaborador
        $employee = new Employee();
        $employeeData = $employee->findById($employeeId);
        
        if (!$employeeData) {
            throw new Exception('Colaborador não encontrado');
        }
        
        // Buscar horas trabalhadas no período
        $timeRecord = new TimeRecord();
        $hoursData = $timeRecord->getHoursReport($employeeId, $month, $year);
        
        $baseSalary = floatval($employeeData['salary']);
        $workedHours = $hoursData['total_hours'] ?? 0;
        $expectedHours = 160; // 8h x 20 dias úteis (pode ser configurável)
        
        // Calcular horas extras (acima de 160h/mês)
        $overtimeHours = max(0, $workedHours - $expectedHours);
        $overtimeRate = $baseSalary / $expectedHours * 1.5; // 50% adicional
        $overtimeAmount = $overtimeHours * $overtimeRate;
        
        // Calcular salário bruto
        $grossSalary = $baseSalary + $overtimeAmount;
        
        // Calcular descontos
        $inssDiscount = $this->calculateINSS($grossSalary);
        $irrfDiscount = $this->calculateIRRF($grossSalary - $inssDiscount);
        
        // Calcular salário líquido
        $netSalary = $grossSalary - $inssDiscount - $irrfDiscount;
        
        return [
            'employee_id' => $employeeId,
            'reference_month' => $month,
            'reference_year' => $year,
            'base_salary' => $baseSalary,
            'overtime_hours' => $overtimeHours,
            'overtime_amount' => $overtimeAmount,
            'total_earnings' => $grossSalary,
            'inss_discount' => $inssDiscount,
            'irrf_discount' => $irrfDiscount,
            'transport_discount' => 0,
            'meal_discount' => 0,
            'absence_discount' => 0,
            'total_discounts' => $inssDiscount + $irrfDiscount,
            'net_salary' => $netSalary,
            'worked_days' => $hoursData['total_days'] ?? 0,
            'absence_days' => $hoursData['absence_days'] ?? 0
        ];
    }
    
    /**
     * Calcular desconto do INSS
     */
    private function calculateINSS($grossSalary) {
        // Tabela INSS 2024 (valores aproximados)
        $brackets = [
            ['min' => 0, 'max' => 1412.00, 'rate' => 0.075],
            ['min' => 1412.01, 'max' => 2666.68, 'rate' => 0.09],
            ['min' => 2666.69, 'max' => 4000.03, 'rate' => 0.12],
            ['min' => 4000.04, 'max' => 7786.02, 'rate' => 0.14]
        ];
        
        $discount = 0;
        $remainingSalary = $grossSalary;
        
        foreach ($brackets as $bracket) {
            if ($remainingSalary <= 0) break;
            
            $bracketMax = $bracket['max'] ?? PHP_FLOAT_MAX;
            $bracketAmount = min($remainingSalary, $bracketMax - $bracket['min']);
            
            if ($bracketAmount > 0) {
                $discount += $bracketAmount * $bracket['rate'];
                $remainingSalary -= $bracketAmount;
            }
        }
        
        return round($discount, 2);
    }
    
    /**
     * Calcular desconto do IRRF
     */
    private function calculateIRRF($taxableIncome) {
        // Tabela IRRF 2024 (valores aproximados)
        $brackets = [
            ['min' => 0, 'max' => 2112.00, 'rate' => 0, 'deduction' => 0],
            ['min' => 2112.01, 'max' => 2826.65, 'rate' => 0.075, 'deduction' => 158.40],
            ['min' => 2826.66, 'max' => 3751.05, 'rate' => 0.15, 'deduction' => 370.40],
            ['min' => 3751.06, 'max' => 4664.68, 'rate' => 0.225, 'deduction' => 651.73],
            ['min' => 4664.69, 'max' => PHP_FLOAT_MAX, 'rate' => 0.275, 'deduction' => 884.96]
        ];
        
        foreach ($brackets as $bracket) {
            if ($taxableIncome >= $bracket['min'] && $taxableIncome <= $bracket['max']) {
                $discount = ($taxableIncome * $bracket['rate']) - $bracket['deduction'];
                return max(0, round($discount, 2));
            }
        }
        
        return 0;
    }
    
    /**
     * Gerar folhas de pagamento em lote para todos os colaboradores
     */
    public function generateBatchPayroll($month, $year, $employeeIds = null) {
        $employee = new Employee();
        
        if ($employeeIds) {
            $employees = [];
            foreach ($employeeIds as $id) {
                $emp = $employee->findById($id);
                if ($emp) $employees[] = $emp;
            }
        } else {
            $employees = $employee->getAll();
            // Filtrar apenas colaboradores ativos
            $employees = array_filter($employees, function($emp) {
                return $emp['status'] === 'ativo'; // Alterado 'active' para 'ativo' conforme enum da tabela
            });
        }
        
        $results = [];
        
        foreach ($employees as $emp) {
            try {
                // Verificar se já existe folha para este período
                if ($this->existsForPeriod($emp['id'], $month, $year)) {
                    $results[] = [
                        'employee_id' => $emp['id'],
                        'employee_name' => $emp['full_name'],
                        'success' => false,
                        'message' => 'Folha já existe para este período'
                    ];
                    continue;
                }
                
                // Calcular e criar folha
                $payrollData = $this->calculatePayroll($emp['id'], $month, $year);
                $success = $this->create($payrollData);
                
                $results[] = [
                    'employee_id' => $emp['id'],
                    'employee_name' => $emp['full_name'],
                    'success' => $success,
                    'message' => $success ? 'Folha criada com sucesso' : 'Erro ao criar folha'
                ];
                
            } catch (Exception $e) {
                $results[] = [
                    'employee_id' => $emp['id'],
                    'employee_name' => $emp['full_name'],
                    'success' => false,
                    'message' => $e->getMessage()
                ];
            }
        }
        
        return $results;
    }
    
    /**
     * Obter estatísticas da folha de pagamento
     */
    public function getStatistics($month = null, $year = null) {
        $sql = "SELECT 
                COUNT(*) as total_payrolls,
                SUM(net_salary) as total_net_salary,
                SUM(base_salary) as total_base_salary,
                SUM(inss_discount + irrf_discount + transport_discount + meal_discount + absence_discount) as total_discounts,
                AVG(net_salary) as avg_net_salary,
                SUM(worked_days) as total_worked_days,
                SUM(absence_days) as total_absence_days,
                SUM(overtime_hours) as total_overtime_hours
                FROM payrolls WHERE 1=1";
        
        $params = [];
        
        if ($month) {
            $sql .= " AND reference_month = ?";
            $params[] = $month;
        }
        
        if ($year) {
            $sql .= " AND reference_year = ?";
            $params[] = $year;
        }
        
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }
    
    /**
     * Formatar valor monetário
     */
    public static function formatMoney($value) {
        return 'R$ ' . number_format($value, 2, ',', '.');
    }
    
    /**
     * Obter nome do mês
     */
    public static function getMonthName($month) {
        $months = [
            1 => 'Janeiro', 2 => 'Fevereiro', 3 => 'Março', 4 => 'Abril',
            5 => 'Maio', 6 => 'Junho', 7 => 'Julho', 8 => 'Agosto',
            9 => 'Setembro', 10 => 'Outubro', 11 => 'Novembro', 12 => 'Dezembro'
        ];
        
        return $months[$month] ?? 'Mês inválido';
    }
    
    /**
     * Validar dados da folha de pagamento
     */
    public function validate($data) {
        $errors = [];
        
        if (empty($data['employee_id'])) {
            $errors[] = 'Colaborador é obrigatório';
        }
        
        if (empty($data['reference_month']) || $data['reference_month'] < 1 || $data['reference_month'] > 12) {
            $errors[] = 'Mês de referência inválido';
        }
        
        if (empty($data['reference_year']) || $data['reference_year'] < 2020) {
            $errors[] = 'Ano de referência inválido';
        }
        
        if (!isset($data['base_salary']) || $data['base_salary'] < 0) {
            $errors[] = 'Salário base deve ser maior ou igual a zero';
        }
        
        if (isset($data['overtime_hours']) && $data['overtime_hours'] < 0) {
            $errors[] = 'Horas extras não podem ser negativas';
        }
        
        if (isset($data['net_salary']) && $data['net_salary'] < 0) {
            $errors[] = 'Salário líquido não pode ser negativo';
        }
        
        return $errors;
    }
}
