<?php
namespace Models;
//require_once __DIR__ . '/../models/Database.php';
require_once 'Database.php';

use PDO;
use Utils\MoisFrHelper;

class Payslip {
    private static $pdo;

// Initialiser la connexion à la base de données
    public static function init() {
        if (self::$pdo == null) {
            self::$pdo = Database::getInstance()->getConnection();
            self::$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        }
    }

    /**
     * Crée un nouveau bulletin de salaire
     */
  public function create(array $data): int {
    self::init();
    self::$pdo->beginTransaction();
    try {
      // 1. Données de base
      $employee_id = intval($data['employee_id']);
      $gross = floatval($data['gross_salary']);
      $period = trim($data['period']);
      $plus_items = $data['plus_items'] ?? [];
      $minus_items = $data['minus_items'] ?? [];

      // 2. Récupérer taux impôt de l’employé
      $stmt = self::$pdo->prepare("SELECT taux_impot FROM employees WHERE id = ?");
      $stmt->execute([$employee_id]);
      $taux_impot = floatval($stmt->fetchColumn());

      // 3. Cotisations sociales
      $total_cotisations = 0;
      $socials = self::$pdo->query("SELECT * FROM social_settings")->fetchAll();
      foreach ($socials as $s) {
        $base = $gross * ($s['gross_base'] / 100);
        $amount = $base * ($s['rate'] / 100);
        $stmt = self::$pdo->prepare("INSERT INTO payslip_social (employee_id, keyname, label, base, rate, amount, period) VALUES (?, ?, ?, ?, ?, ?, ?)");
        $stmt->execute([$employee_id, $s['keyname'], $s['value'], $base, $s['rate'], $amount, $period]);
        $total_cotisations += $amount;
      }

      // 4. Abattements (moderation_settings)
      $stmt = self::$pdo->prepare("SELECT * FROM moderation_settings WHERE employee_id = ?");
      $stmt->execute([$employee_id]);
      $abattements = $stmt->fetchAll();
      $total_abattements = 0;
      foreach ($abattements as $a) {
        $total_abattements += $a['amount'];
        $stmt2 = self::$pdo->prepare("INSERT INTO payslip_moderation (employee_id, keyname, label, amount, period) VALUES (?, ?, ?, ?, ?)");
        $stmt2->execute([$employee_id, $a['keyname'], $a['value'], $a['amount'], $period]);
      }

      // 5. Imposable = Brut - cotisations - abattements
      $imposable = $gross - $total_cotisations - $total_abattements;
      $impot = $imposable * ($taux_impot / 100);

      // 6. Enregistrement impôt
      $stmt = self::$pdo->prepare("INSERT INTO payslip_taxes (employee_id, rate, amount, period) VALUES (?, ?, ?, ?)");
      $stmt->execute([$employee_id, $taux_impot, $impot, $period]);

      // 7. Calculs Plus / Minus
      $other_plus = 0;
      foreach ($plus_items as $p) {
        $val = abs(floatval($p['amount']));
        $other_plus += $val;
        $stmt = self::$pdo->prepare("INSERT INTO payslip_plus_minus (employee_id, period, label, amount) VALUES (?, ?, ?, ?)");
        $stmt->execute([$employee_id, $period, $p['label'], $val]);
      }

      $other_minus = 0;
      foreach ($minus_items as $m) {
        $val = abs(floatval($m['amount']));
        $other_minus += $val;
        $stmt = self::$pdo->prepare("INSERT INTO payslip_plus_minus (employee_id, period, label, amount) VALUES (?, ?, ?, ?)");
        $stmt->execute([$employee_id, $period, $m['label'], -$val]);
      }

      // 8. Net après impôt
      $net_apres_impot = $imposable - $impot;
      $net_final = $net_apres_impot + $other_plus - $other_minus;

      // 9. Insertion finale
      $stmt = self::$pdo->prepare("INSERT INTO payslips (employee_id, period, gross_salary, social_charges, abattements, tax, other_plus, other_minus, net_salary) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
      $stmt->execute([
        $employee_id, $period, $gross, $total_cotisations, $total_abattements, $impot, $other_plus, $other_minus, $net_final
      ]);

      $id = self::$pdo->lastInsertId();
      self::$pdo->commit();
      return (int)$id;

    } catch (Exception $e) {
      self::$pdo->rollBack();
      throw new Exception("Erreur création bulletin : " . $e->getMessage());
    }
  }


    /**
     * Récupère un bulletin par son ID
     */
    public function findById(int $id): ?array {
        self::init();
        $stmt = self::$pdo->prepare("
            SELECT p.*, 
                   CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
                   e.matricule
            FROM payslips p
            JOIN employees e ON p.employee_id = e.id
            WHERE p.id = :id
        ");
        $stmt->execute([':id' => $id]);
        return $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
    }

    /**
     * Met à jour un bulletin existant
     */
    public function update(int $id, array $data): bool {
        self::init();
        $stmt = self::$pdo->prepare("
            UPDATE payslips SET
                gross_salary = :gross_salary,
                net_salary = :net_salary,
                social_charges = :social_charges,
                abattements = :abattements,
                tax = :tax,
                other_plus = :other_plus,
                other_minus = :other_minus
            WHERE id = :id
        ");

        return $stmt->execute([
            ':id' => $id,
            ':gross_salary' => $data['gross_salary'],
            ':net_salary' => $data['net_salary'],
            ':social_charges' => $data['social_charges'],
            ':abattements' => $data['abattements'],
            ':tax' => $data['tax'],
            ':other_plus' => $data['other_plus'] ?? 0,
            ':other_minus' => $data['other_minus'] ?? 0
        ]);
    }

    /**
     * Liste tous les bulletins (avec pagination)
     */
    public function findAll(int $page = 1, int $perPage = 10): array {
        $offset = ($page - 1) * $perPage;
        
        self::init();
        $stmt = self::$pdo->prepare("
            SELECT p.*, 
                   CONCAT(e.first_name, ' ', e.last_name) AS employee_name
            FROM payslips p
            JOIN employees e ON p.employee_id = e.id
            ORDER BY p.period DESC, p.created_at DESC
            LIMIT :limit OFFSET :offset
        ");
        
        $stmt->bindValue(':limit', $perPage, PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        $stmt->execute();
        
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * Récupère les bulletins récents
     */
    public function getRecent(int $limit = 5): array {
        self::init();
        $stmt = self::$pdo->prepare("
            SELECT p.*, 
                   CONCAT(e.first_name, ' ', e.last_name) AS employee_name
            FROM payslips p
            JOIN employees e ON p.employee_id = e.id
            ORDER BY p.period DESC, p.created_at DESC
            LIMIT :limit
        ");
        
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();
        
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * Compte le nombre total de bulletins
     */
    public function count(): int {
        self::init();
        $stmt = self::$pdo->prepare("SELECT COUNT(*) FROM payslips");
        $stmt->execute();
        return $stmt->fetchColumn();
    }

    /**
     * Compte les bulletins du mois en cours
     */
    public function countThisMonth(): int {
        self::init();
        $stmt = self::$pdo->prepare("
            SELECT COUNT(*) 
            FROM payslips 
            WHERE period LIKE CONCAT(DATE_FORMAT(NOW(), '%Y-%m'), '%')
        ");
        $stmt->execute();
        return $stmt->fetchColumn();
    }

    /**
     * Sauvegarde les cotisations sociales liées à un bulletin
     */
    public function saveSocialCharges(array $data): bool {
        self::init();
        $stmt = self::$pdo->prepare("
            INSERT INTO payslip_social (
                payslip_id, employee_id, cm_rate, cm_amount, cme_rate, cme_amount,
                cp_rate, cp_amount, sd_rate, sd_amount, period
            ) VALUES (
                :payslip_id, :employee_id, :cm_rate, :cm_amount, :cme_rate, :cme_amount,
                :cp_rate, :cp_amount, :sd_rate, :sd_amount, :period
            )
        ");
        
        return $stmt->execute($data);
    }

    /**
     * Récupère les bulletins par employé
     */
    public function findByEmployee(int $employeeId): array {
        self::init();
        $stmt = self::$pdo->prepare("
            SELECT * FROM payslips 
            WHERE employee_id = :employee_id
            ORDER BY period DESC
        ");
        $stmt->execute([':employee_id' => $employeeId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * Récupère un bulletin spécifique par employé et période
     */
    public function findByEmployeeAndPeriod(int $employeeId, string $period): ?array {
        self::init();
        $stmt = self::$pdo->prepare("
            SELECT * FROM payslips 
            WHERE employee_id = :employee_id 
            AND period = :period
        ");
        $stmt->execute([
            ':employee_id' => $employeeId,
            ':period' => $period
        ]);
        return $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
    }

    /**
     * Calcule le total des salaires bruts pour un mois donné
     */
    public function getGrossTotalForMonth(string $period): float {
        self::init();
        $stmt = self::$pdo->prepare("
            SELECT SUM(gross_salary) 
            FROM payslips 
            WHERE period LIKE :period
        ");
        $periodLike = substr($period, 0, 7) . '%';
        $stmt->execute([':period' => $periodLike]);
        return (float)$stmt->fetchColumn();
    }
    /**
     * Calcule le total des salaires nets pour un mois donné
     */
    public function getNetTotalForMonth(): float {
        self::init();
        $currentMonth = MoisFrHelper::sqlToFr(date('Y-m'));
        $stmt = self::$pdo->prepare("SELECT SUM(net_apres_impot) as total FROM payslips WHERE period = ?");
        $stmt->execute([$currentMonth]);
        $total_salary = $stmt->fetch()['total'] ?? 0;
        return $total_salary;
    }
    // Évolution sur 6 mois
    public function  getNetTotalFor6Months() {
        self::init();
        $stats = [];
        for ($i = 5; $i >= 0; $i--) {
            $month = MoisFrHelper::sqlToFr(date('Y-m', strtotime("-$i months")));
            $stmt = self::$pdo->prepare("SELECT SUM(net_apres_impot) as total FROM payslips WHERE period = ?");
            $stmt->execute([$month]);
            $stats[$month] = round($stmt->fetch()['total'] ?? 0, 2);
        }
        return $stats;
    }
    /**
     * Calcule le total des charges sociales pour un mois donné
     */
    public function getSocialChargesTotalForMonth(string $period): float {
        self::init();
        $stmt = self::$pdo->prepare("
            SELECT SUM(cm_amount + cme_amount + cp_amount + sd_amount) 
            FROM payslip_social 
            WHERE period LIKE :period
        ");
        $periodLike = substr($period, 0, 7) . '%';
        $stmt->execute([':period' => $periodLike]);
        return (float)$stmt->fetchColumn();
    }
}
?>