<?php

declare(strict_types=1);

namespace App\Repositories;

final class FactureFournisseurRepository extends BaseRepository
{
    private static bool $schemaChecked = false;

    public function __construct(?\PDO $db = null)
    {
        parent::__construct($db);
        $this->ensureP262bSchema();
    }

    public function all(array $filters = []): array
    {
        $where = [];
        $params = [];
        $etat=(string)($filters['etat']??'actives');
        if ($etat==='annulees') $where[]='ff.actif = 0'; elseif ($etat!=='toutes') $where[]='ff.actif = 1';

        if (!empty($filters['fournisseur_id'])) {
            $where[] = 'ff.fournisseur_id = :fournisseur_id';
            $params['fournisseur_id'] = (int)$filters['fournisseur_id'];
        }

        if (!empty($filters['statut'])) {
            $where[] = 'ff.statut = :statut';
            $params['statut'] = (string)$filters['statut'];
        }

        if (!empty($filters['q'])) {
            $where[] = '(ff.numero_interne LIKE :q OR ff.numero_facture_fournisseur LIKE :q OR f.raison_sociale LIKE :q OR cf.numero LIKE :q)';
            $params['q'] = '%' . trim((string)$filters['q']) . '%';
        }

        $sql = "
            SELECT ff.*, f.raison_sociale AS fournisseur_nom, f.code_fournisseur, cf.numero AS commande_numero
            FROM factures_fournisseurs ff
            INNER JOIN fournisseurs f ON f.id = ff.fournisseur_id
            LEFT JOIN commandes_fournisseurs cf ON cf.id = ff.commande_fournisseur_id
            WHERE " . ($where ? implode(' AND ', $where) : '1=1') . "
            ORDER BY ff.date_facture DESC, ff.id DESC
        ";

        return $this->fetchAll($sql, $params);
    }

    public function echeancier(array $filters = []): array
    {
        $where = ['ff.actif = 1', 'ff.reste_du > 0'];
        $params = [];

        if (!empty($filters['fournisseur_id'])) {
            $where[] = 'ff.fournisseur_id = :fournisseur_id';
            $params['fournisseur_id'] = (int)$filters['fournisseur_id'];
        }

        $today = date('Y-m-d');
        $periode = (string)($filters['periode'] ?? 'toutes');
        if ($periode === 'retard') {
            $where[] = 'ff.date_echeance IS NOT NULL AND ff.date_echeance < :today';
            $params['today'] = $today;
        } elseif ($periode === '7_jours') {
            $where[] = 'ff.date_echeance IS NOT NULL AND ff.date_echeance BETWEEN :today AND :date_fin_auto';
            $params['today'] = $today;
            $params['date_fin_auto'] = date('Y-m-d', strtotime('+7 days'));
        } elseif ($periode === '30_jours') {
            $where[] = 'ff.date_echeance IS NOT NULL AND ff.date_echeance BETWEEN :today AND :date_fin_auto';
            $params['today'] = $today;
            $params['date_fin_auto'] = date('Y-m-d', strtotime('+30 days'));
        } elseif ($periode === 'sans_echeance') {
            $where[] = 'ff.date_echeance IS NULL';
        }

        if (!empty($filters['date_debut'])) {
            $where[] = 'ff.date_echeance IS NOT NULL AND ff.date_echeance >= :date_debut';
            $params['date_debut'] = (string)$filters['date_debut'];
        }
        if (!empty($filters['date_fin'])) {
            $where[] = 'ff.date_echeance IS NOT NULL AND ff.date_echeance <= :date_fin';
            $params['date_fin'] = (string)$filters['date_fin'];
        }

        $sql = "
            SELECT ff.*, f.raison_sociale AS fournisseur_nom, f.code_fournisseur, cf.numero AS commande_numero
            FROM factures_fournisseurs ff
            INNER JOIN fournisseurs f ON f.id = ff.fournisseur_id
            LEFT JOIN commandes_fournisseurs cf ON cf.id = ff.commande_fournisseur_id
            WHERE " . implode(' AND ', $where) . "
            ORDER BY
                CASE WHEN ff.date_echeance IS NULL THEN 1 ELSE 0 END,
                ff.date_echeance ASC,
                f.raison_sociale ASC,
                ff.id ASC
        ";

        return $this->fetchAll($sql, $params);
    }


    public function dashboardStats(): array
    {
        $today = date('Y-m-d');
        $seven = date('Y-m-d', strtotime('+7 days'));
        $thirty = date('Y-m-d', strtotime('+30 days'));
        $monthStart = date('Y-m-01');
        $monthEnd = date('Y-m-t');

        $row = $this->fetchOne(
            "SELECT
                COALESCE(SUM(CASE WHEN actif = 1 AND reste_du > 0 THEN reste_du ELSE 0 END), 0) AS total_du,
                COALESCE(SUM(CASE WHEN actif = 1 AND reste_du > 0 AND date_echeance IS NOT NULL AND date_echeance < :today THEN reste_du ELSE 0 END), 0) AS total_retard,
                COALESCE(SUM(CASE WHEN actif = 1 AND reste_du > 0 AND date_echeance IS NOT NULL AND date_echeance BETWEEN :today AND :seven THEN reste_du ELSE 0 END), 0) AS total_7_jours,
                COALESCE(SUM(CASE WHEN actif = 1 AND reste_du > 0 AND date_echeance IS NOT NULL AND date_echeance BETWEEN :today AND :thirty THEN reste_du ELSE 0 END), 0) AS total_30_jours,
                COALESCE(SUM(CASE WHEN actif = 1 AND reste_du > 0 AND date_echeance IS NULL THEN reste_du ELSE 0 END), 0) AS total_sans_echeance,
                COUNT(CASE WHEN actif = 1 AND reste_du > 0 THEN 1 END) AS nb_a_payer,
                COUNT(CASE WHEN actif = 1 AND reste_du > 0 AND date_echeance IS NOT NULL AND date_echeance < :today THEN 1 END) AS nb_retard,
                COUNT(CASE WHEN actif = 1 AND statut = 'reglee' THEN 1 END) AS nb_reglees
             FROM factures_fournisseurs",
            ['today' => $today, 'seven' => $seven, 'thirty' => $thirty]
        ) ?? [];

        $paidMonth = $this->fetchColumn(
            "SELECT COALESCE(SUM(montant), 0)
             FROM reglements_fournisseurs
             WHERE actif = 1 AND date_reglement BETWEEN :start AND :end",
            ['start' => $monthStart, 'end' => $monthEnd]
        );

        $row['total_regle_mois'] = (float)$paidMonth;
        return $row;
    }

    public function dashboardOverdueFactures(int $limit = 8): array
    {
        $limit = max(1, min(50, $limit));
        return $this->fetchAll(
            "SELECT ff.*, f.raison_sociale AS fournisseur_nom, f.code_fournisseur
             FROM factures_fournisseurs ff
             INNER JOIN fournisseurs f ON f.id = ff.fournisseur_id
             WHERE ff.actif = 1 AND ff.reste_du > 0 AND ff.date_echeance IS NOT NULL AND ff.date_echeance < :today
             ORDER BY ff.date_echeance ASC, ff.reste_du DESC
             LIMIT {$limit}",
            ['today' => date('Y-m-d')]
        );
    }

    public function dashboardUpcomingFactures(int $limit = 8): array
    {
        $limit = max(1, min(50, $limit));
        return $this->fetchAll(
            "SELECT ff.*, f.raison_sociale AS fournisseur_nom, f.code_fournisseur
             FROM factures_fournisseurs ff
             INNER JOIN fournisseurs f ON f.id = ff.fournisseur_id
             WHERE ff.actif = 1 AND ff.reste_du > 0 AND ff.date_echeance IS NOT NULL AND ff.date_echeance >= :today
             ORDER BY ff.date_echeance ASC, ff.reste_du DESC
             LIMIT {$limit}",
            ['today' => date('Y-m-d')]
        );
    }

    public function dashboardTopFournisseursDue(int $limit = 8): array
    {
        $limit = max(1, min(50, $limit));
        return $this->fetchAll(
            "SELECT f.id, f.raison_sociale AS fournisseur_nom, f.code_fournisseur,
                    COALESCE(SUM(ff.reste_du), 0) AS total_du,
                    COUNT(ff.id) AS nb_factures
             FROM factures_fournisseurs ff
             INNER JOIN fournisseurs f ON f.id = ff.fournisseur_id
             WHERE ff.actif = 1 AND ff.reste_du > 0
             GROUP BY f.id, f.raison_sociale, f.code_fournisseur
             ORDER BY total_du DESC, f.raison_sociale ASC
             LIMIT {$limit}"
        );
    }

    public function dashboardRecentReglements(int $limit = 8): array
    {
        $limit = max(1, min(50, $limit));
        return $this->fetchAll(
            "SELECT rf.*, ff.numero_interne, ff.numero_facture_fournisseur, f.raison_sociale AS fournisseur_nom
             FROM reglements_fournisseurs rf
             INNER JOIN factures_fournisseurs ff ON ff.id = rf.facture_fournisseur_id
             INNER JOIN fournisseurs f ON f.id = ff.fournisseur_id
             WHERE rf.actif = 1
             ORDER BY rf.date_reglement DESC, rf.id DESC
             LIMIT {$limit}"
        );
    }

    public function find(int $id): ?array
    {
        return $this->fetchOne(
            "SELECT ff.*, f.raison_sociale AS fournisseur_nom, f.code_fournisseur, f.telephone, f.email, f.ville, cf.numero AS commande_numero, cf.statut AS commande_statut
             FROM factures_fournisseurs ff
             INNER JOIN fournisseurs f ON f.id = ff.fournisseur_id
             LEFT JOIN commandes_fournisseurs cf ON cf.id = ff.commande_fournisseur_id
             WHERE ff.id = :id LIMIT 1",
            ['id' => $id]
        );
    }

    public function lines(int $factureId): array
    {
        return $this->fetchAll('SELECT * FROM factures_fournisseurs_lignes WHERE facture_fournisseur_id = :id ORDER BY ordre, id', ['id' => $factureId]);
    }

    public function articles(): array
    {
        return $this->fetchAll('SELECT id, reference, designation, prix_achat_ht, taux_tva FROM articles WHERE actif = 1 ORDER BY designation');
    }

    public function findCommand(int $commandeId): ?array
    {
        return $this->fetchOne(
            "SELECT cf.*, f.raison_sociale AS fournisseur_nom, f.code_fournisseur
             FROM commandes_fournisseurs cf
             INNER JOIN fournisseurs f ON f.id = cf.fournisseur_id
             WHERE cf.id = :id AND cf.actif = 1 LIMIT 1",
            ['id' => $commandeId]
        );
    }

    public function commandLines(int $commandeId): array
    {
        return $this->fetchAll(
            "SELECT
                article_id,
                reference,
                designation,
                quantite,
                COALESCE(quantite_recue, 0) AS quantite_recue,
                prix_achat_ht,
                taux_tva,
                ordre
             FROM commandes_fournisseurs_lignes
             WHERE commande_id = :id
             ORDER BY ordre ASC, id ASC",
            ['id' => $commandeId]
        );
    }

    public function existingForCommand(int $commandeId): ?array
    {
        return $this->fetchOne(
            'SELECT * FROM factures_fournisseurs WHERE commande_fournisseur_id = :id AND actif = 1 LIMIT 1',
            ['id' => $commandeId]
        );
    }

    public function nextNumber(): string
    {
        $prefix = 'FF' . date('Y') . '-';
        $last = $this->fetchColumn(
            'SELECT numero_interne FROM factures_fournisseurs WHERE numero_interne LIKE :prefix ORDER BY id DESC LIMIT 1',
            ['prefix' => $prefix . '%']
        );

        if (!$last) {
            return $prefix . '000001';
        }

        $number = (int)substr((string)$last, strlen($prefix));
        return $prefix . str_pad((string)($number + 1), 6, '0', STR_PAD_LEFT);
    }

    public function create(array $data): int
    {
		$montantTtc = round((float)$data['montant_ttc'], 2);

		/*
		 * Une facture nouvellement créée ne contient jamais
		 * de règlement implicite.
		 */
		$montantRegle = 0.0;
        $resteDu = max(0, round($montantTtc - $montantRegle, 2));
        $statut = $this->computeStatus($montantTtc, $montantRegle);

        $this->beginTransaction();
        try {
        $this->execute(
            "INSERT INTO factures_fournisseurs (
                fournisseur_id, commande_fournisseur_id, numero_interne, numero_facture_fournisseur,
                date_facture, date_echeance, montant_ht, montant_tva, montant_ttc,
                montant_regle, reste_du, statut, observations, justificatif_nom, justificatif_fichier, actif
             ) VALUES (
                :fournisseur_id, :commande_fournisseur_id, :numero_interne, :numero_facture_fournisseur,
                :date_facture, :date_echeance, :montant_ht, :montant_tva, :montant_ttc,
                :montant_regle, :reste_du, :statut, :observations, :justificatif_nom, :justificatif_fichier, 1
             )",
            [
                'fournisseur_id' => (int)$data['fournisseur_id'],
                'commande_fournisseur_id' => !empty($data['commande_fournisseur_id']) ? (int)$data['commande_fournisseur_id'] : null,
                'numero_interne' => (string)$data['numero_interne'],
                'numero_facture_fournisseur' => (string)$data['numero_facture_fournisseur'],
                'date_facture' => (string)$data['date_facture'],
                'date_echeance' => !empty($data['date_echeance']) ? (string)$data['date_echeance'] : null,
                'montant_ht' => round((float)$data['montant_ht'], 2),
                'montant_tva' => round((float)$data['montant_tva'], 2),
                'montant_ttc' => $montantTtc,
                'montant_regle' => $montantRegle,
                'reste_du' => $resteDu,
                'statut' => $statut,
                'observations' => trim((string)($data['observations'] ?? '')),
                'justificatif_nom' => $data['justificatif_nom'] ?? null,
                'justificatif_fichier' => $data['justificatif_fichier'] ?? null,
            ]
        );

        $id = $this->lastInsertId();
        $this->replaceLines($id, $data['lignes'] ?? []);
        $this->commit();
        return $id;
        } catch (\Throwable $e) { $this->rollBack(); throw $e; }
    }

    public function update(int $id, array $data): void
    {
        $current = $this->find($id);
        if (!$current || !(int)$current['actif']) throw new \RuntimeException('Facture fournisseur introuvable.');
        $regle = round((float)$current['montant_regle'], 2);
        $ttc = round((float)$data['montant_ttc'], 2);
        if ($ttc + 0.001 < $regle) throw new \RuntimeException('Le nouveau total TTC ne peut pas être inférieur au montant déjà réglé.');
        $this->beginTransaction();
        try {
            $this->execute("UPDATE factures_fournisseurs SET fournisseur_id=:fournisseur_id, numero_facture_fournisseur=:numero_facture_fournisseur, date_facture=:date_facture, date_echeance=:date_echeance, montant_ht=:montant_ht, montant_tva=:montant_tva, montant_ttc=:montant_ttc, reste_du=:reste_du, statut=:statut, observations=:observations, justificatif_nom=COALESCE(:justificatif_nom, justificatif_nom), justificatif_fichier=COALESCE(:justificatif_fichier, justificatif_fichier) WHERE id=:id", [
                'fournisseur_id'=>(int)$data['fournisseur_id'], 'numero_facture_fournisseur'=>(string)$data['numero_facture_fournisseur'], 'date_facture'=>(string)$data['date_facture'], 'date_echeance'=>!empty($data['date_echeance'])?(string)$data['date_echeance']:null,
                'montant_ht'=>(float)$data['montant_ht'], 'montant_tva'=>(float)$data['montant_tva'], 'montant_ttc'=>$ttc, 'reste_du'=>max(0,$ttc-$regle), 'statut'=>$this->computeStatus($ttc,$regle), 'observations'=>(string)$data['observations'],
                'justificatif_nom'=>$data['justificatif_nom']??null, 'justificatif_fichier'=>$data['justificatif_fichier']??null, 'id'=>$id
            ]);
            $this->replaceLines($id, $data['lignes'] ?? []);
            $this->commit();
        } catch (\Throwable $e) { $this->rollBack(); throw $e; }
    }

    private function replaceLines(int $factureId, array $lines): void
    {
        $this->execute('DELETE FROM factures_fournisseurs_lignes WHERE facture_fournisseur_id = :id', ['id'=>$factureId]);
        foreach ($lines as $i => $line) {
            $this->execute("INSERT INTO factures_fournisseurs_lignes (facture_fournisseur_id,article_id,type_ligne,reference,designation,quantite,prix_achat_ht,taux_tva,total_ht,total_tva,total_ttc,ordre) VALUES (:fid,:article_id,:type_ligne,:reference,:designation,:quantite,:prix,:tva,:total_ht,:total_tva,:total_ttc,:ordre)", [
                'fid'=>$factureId, 'article_id'=>!empty($line['article_id'])?(int)$line['article_id']:null, 'type_ligne'=>$line['type_ligne'], 'reference'=>$line['reference']?:null, 'designation'=>$line['designation'], 'quantite'=>$line['quantite'], 'prix'=>$line['prix_achat_ht'], 'tva'=>$line['taux_tva'], 'total_ht'=>$line['total_ht'], 'total_tva'=>$line['total_tva'], 'total_ttc'=>$line['total_ttc'], 'ordre'=>$i+1
            ]);
        }
    }

    /**
     * Répare automatiquement une base P2.6.2a mise à jour sans import de la
     * migration 026. Le contrôle est idempotent et ne touche à aucune donnée.
     */
    private function ensureP262bSchema(): void
    {
        if (self::$schemaChecked) {
            return;
        }

        $stmt = $this->db->prepare(
            'SELECT COLUMN_NAME FROM information_schema.columns '
            . 'WHERE table_schema = DATABASE() AND table_name = :table'
        );
        $stmt->execute(['table' => 'factures_fournisseurs']);
        $existing = array_fill_keys(
            array_map('strval', $stmt->fetchAll(\PDO::FETCH_COLUMN)),
            true
        );

        if (!isset($existing['justificatif_nom'])) {
            $this->db->exec(
                'ALTER TABLE `factures_fournisseurs` '
                . 'ADD COLUMN `justificatif_nom` VARCHAR(255) NULL AFTER `observations`'
            );
        }
        if (!isset($existing['justificatif_fichier'])) {
            $after = isset($existing['justificatif_nom']) ? 'justificatif_nom' : 'justificatif_nom';
            $this->db->exec(
                'ALTER TABLE `factures_fournisseurs` '
                . 'ADD COLUMN `justificatif_fichier` VARCHAR(255) NULL AFTER `' . $after . '`'
            );
        }

        $this->db->exec(
            "CREATE TABLE IF NOT EXISTS `factures_fournisseurs_lignes` (
                `id` INT AUTO_INCREMENT PRIMARY KEY,
                `facture_fournisseur_id` INT NOT NULL,
                `article_id` INT NULL,
                `type_ligne` VARCHAR(20) NOT NULL DEFAULT 'article',
                `reference` VARCHAR(100) NULL,
                `designation` VARCHAR(255) NOT NULL,
                `quantite` DECIMAL(12,3) NOT NULL DEFAULT 1,
                `prix_achat_ht` DECIMAL(12,2) NOT NULL DEFAULT 0,
                `taux_tva` DECIMAL(5,2) NOT NULL DEFAULT 0,
                `total_ht` DECIMAL(12,2) NOT NULL DEFAULT 0,
                `total_tva` DECIMAL(12,2) NOT NULL DEFAULT 0,
                `total_ttc` DECIMAL(12,2) NOT NULL DEFAULT 0,
                `ordre` INT NOT NULL DEFAULT 0,
                INDEX `idx_ffl_facture` (`facture_fournisseur_id`),
                INDEX `idx_ffl_article` (`article_id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"
        );

        $this->db->exec(
            "CREATE TABLE IF NOT EXISTS `reglements_fournisseurs` (
                `id` INT AUTO_INCREMENT PRIMARY KEY,
                `facture_fournisseur_id` INT NOT NULL,
                `date_reglement` DATE NOT NULL,
                `montant` DECIMAL(12,2) NOT NULL DEFAULT 0,
                `mode_reglement` VARCHAR(50) NULL,
                `reference_reglement` VARCHAR(100) NULL,
                `observations` TEXT NULL,
                `actif` TINYINT(1) NOT NULL DEFAULT 1,
                `date_annulation` DATETIME NULL,
                `motif_annulation` VARCHAR(255) NULL,
                `date_creation` DATETIME DEFAULT CURRENT_TIMESTAMP,
                INDEX `idx_rf_facture` (`facture_fournisseur_id`),
                INDEX `idx_rf_date` (`date_reglement`),
                INDEX `idx_rf_actif` (`actif`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"
        );
        $reglementColumns = $this->fetchAll('SHOW COLUMNS FROM `reglements_fournisseurs`');
        $reglementExisting = array_fill_keys(array_column($reglementColumns, 'Field'), true);
        if (!isset($reglementExisting['date_annulation'])) {
            $this->db->exec('ALTER TABLE `reglements_fournisseurs` ADD COLUMN `date_annulation` DATETIME NULL AFTER `actif`');
        }
        if (!isset($reglementExisting['motif_annulation'])) {
            $this->db->exec('ALTER TABLE `reglements_fournisseurs` ADD COLUMN `motif_annulation` VARCHAR(255) NULL AFTER `date_annulation`');
        }

        self::$schemaChecked = true;
    }


    public function reglements(int $factureId): array
    {
        return $this->fetchAll(
            "SELECT * FROM reglements_fournisseurs
             WHERE facture_fournisseur_id = :id
             ORDER BY date_reglement DESC, id DESC",
            ['id' => $factureId]
        );
    }

    public function addReglement(int $factureId, array $data): int
    {
        $facture = $this->find($factureId);
        if (!$facture) {
            throw new \RuntimeException('Facture fournisseur introuvable.');
        }
        if (!(int)$facture['actif']) {
            throw new \RuntimeException('Impossible de régler une facture annulée.');
        }

        $montant = round((float)$data['montant'], 2);
        if ($montant <= 0) {
            throw new \RuntimeException('Le montant du règlement doit être positif.');
        }

        $resteActuel = round((float)$facture['reste_du'], 2);
        if ($montant > $resteActuel + 0.001) {
            throw new \RuntimeException('Le règlement ne peut pas dépasser le reste dû.');
        }

        $this->beginTransaction();
        try {
            $this->execute(
                "INSERT INTO reglements_fournisseurs (
                    facture_fournisseur_id, date_reglement, montant, mode_reglement,
                    reference_reglement, observations, actif
                 ) VALUES (
                    :facture_fournisseur_id, :date_reglement, :montant, :mode_reglement,
                    :reference_reglement, :observations, 1
                 )",
                [
                    'facture_fournisseur_id' => $factureId,
                    'date_reglement' => (string)$data['date_reglement'],
                    'montant' => $montant,
                    'mode_reglement' => (string)($data['mode_reglement'] ?? ''),
                    'reference_reglement' => trim((string)($data['reference_reglement'] ?? '')) ?: null,
                    'observations' => trim((string)($data['observations'] ?? '')),
                ]
            );
            $id = $this->lastInsertId();
            $this->recomputeTotals($factureId);
            $this->commit();
            return $id;
        } catch (\Throwable $exception) {
            $this->rollBack();
            throw $exception;
        }
    }

    public function journalReglements(array $filters = []): array
    {
        $where = ['1=1'];
        $params = [];
        if (!empty($filters['fournisseur_id'])) { $where[] = 'ff.fournisseur_id = :fournisseur_id'; $params['fournisseur_id'] = (int)$filters['fournisseur_id']; }
        if (!empty($filters['mode_reglement'])) { $where[] = 'rf.mode_reglement = :mode'; $params['mode'] = (string)$filters['mode_reglement']; }
        if (!empty($filters['date_debut'])) { $where[] = 'rf.date_reglement >= :date_debut'; $params['date_debut'] = (string)$filters['date_debut']; }
        if (!empty($filters['date_fin'])) { $where[] = 'rf.date_reglement <= :date_fin'; $params['date_fin'] = (string)$filters['date_fin']; }
        if (($filters['etat'] ?? 'actifs') === 'annules') $where[] = 'rf.actif = 0';
        elseif (($filters['etat'] ?? 'actifs') !== 'tous') $where[] = 'rf.actif = 1';
        return $this->fetchAll("SELECT rf.*, ff.numero_interne, ff.numero_facture_fournisseur,
                    f.raison_sociale AS fournisseur_nom
             FROM reglements_fournisseurs rf
             INNER JOIN factures_fournisseurs ff ON ff.id = rf.facture_fournisseur_id
             INNER JOIN fournisseurs f ON f.id = ff.fournisseur_id
             WHERE " . implode(' AND ', $where) . "
             ORDER BY rf.date_reglement DESC, rf.id DESC", $params);
    }

    public function annulerReglement(int $id, string $motif = ''): int
    {
        $reglement = $this->fetchOne('SELECT * FROM reglements_fournisseurs WHERE id = :id LIMIT 1', ['id' => $id]);
        if (!$reglement || !(int)$reglement['actif']) throw new \RuntimeException('Règlement fournisseur actif introuvable.');
        $factureId = (int)$reglement['facture_fournisseur_id'];
        $this->beginTransaction();
        try {
            $this->execute('UPDATE reglements_fournisseurs SET actif = 0, date_annulation = NOW(), motif_annulation = :motif WHERE id = :id', ['motif' => trim($motif) ?: null, 'id' => $id]);
            $this->recomputeTotals($factureId);
            $this->commit();
            return $factureId;
        } catch (\Throwable $e) { $this->rollBack(); throw $e; }
    }

    public function recomputeTotals(int $factureId): void
    {
        $facture = $this->find($factureId);
        if (!$facture) {
            throw new \RuntimeException('Facture fournisseur introuvable.');
        }

        $totalRegle = (float)$this->fetchColumn(
            'SELECT COALESCE(SUM(montant), 0) FROM reglements_fournisseurs WHERE facture_fournisseur_id = :id AND actif = 1',
            ['id' => $factureId]
        );
        $ttc = round((float)$facture['montant_ttc'], 2);
        $totalRegle = min(round($totalRegle, 2), $ttc);
        $resteDu = max(0, round($ttc - $totalRegle, 2));
        $statut = $this->computeStatus($ttc, $totalRegle);

        $this->execute(
            'UPDATE factures_fournisseurs
             SET montant_regle = :montant_regle, reste_du = :reste_du, statut = :statut
             WHERE id = :id',
            [
                'montant_regle' => $totalRegle,
                'reste_du' => $resteDu,
                'statut' => $statut,
                'id' => $factureId,
            ]
        );
    }

    public function deactivate(int $id): void
    {
        $this->execute('UPDATE factures_fournisseurs SET actif = 0 WHERE id = :id', ['id' => $id]);
    }

    public function reactivate(int $id): void { $this->execute('UPDATE factures_fournisseurs SET actif = 1 WHERE id = :id',['id'=>$id]); }

    private function computeStatus(float $ttc, float $regle): string
    {
        if ($regle <= 0) {
            return 'non_reglee';
        }
        if ($regle + 0.001 >= $ttc) {
            return 'reglee';
        }
        return 'partiellement_reglee';
    }
}
