<?php

declare(strict_types=1);

namespace App\Repositories;

final class CommandeFournisseurRepository extends BaseRepository
{
    public function finalisationCycle(array $filters = []): array
    {
        $where = ['cf.actif = 1'];
        $params = [];

        if (!empty($filters['fournisseur_id'])) {
            $where[] = 'cf.fournisseur_id = :fournisseur_id';
            $params['fournisseur_id'] = (int)$filters['fournisseur_id'];
        }
        if (!empty($filters['etat'])) {
            $etat = (string)$filters['etat'];
            if ($etat === 'attente_reception') {
                $where[] = "cf.statut IN ('validée', 'partiellement_reçue')";
            } elseif ($etat === 'a_facturer') {
                $where[] = "cf.statut = 'reçue' AND COALESCE(fa.nb_factures, 0) = 0";
            } elseif ($etat === 'avec_ecart') {
                $where[] = 'COALESCE(fa.nb_factures, 0) > 0 AND ABS(COALESCE(fa.montant_facture_ttc, 0) - cf.total_ttc) > 0.01';
            } elseif ($etat === 'finalise') {
                $where[] = "cf.statut = 'reçue' AND COALESCE(fa.nb_factures, 0) > 0 AND ABS(COALESCE(fa.montant_facture_ttc, 0) - cf.total_ttc) <= 0.01";
            }
        }
        if (!empty($filters['q'])) {
            $where[] = '(cf.numero LIKE :q OR f.raison_sociale LIKE :q OR cf.reference_fournisseur LIKE :q OR fa.numero_facture LIKE :q)';
            $params['q'] = '%' . trim((string)$filters['q']) . '%';
        }

        return $this->fetchAll(
            "SELECT
                cf.id,
                cf.numero,
                cf.date_commande,
                cf.date_livraison_prevue,
                cf.reference_fournisseur,
                cf.statut,
                cf.total_ht AS montant_commande_ht,
                cf.total_ttc AS montant_commande_ttc,
                f.raison_sociale AS fournisseur_nom,
                COALESCE(q.quantite_commandee, 0) AS quantite_commandee,
                COALESCE(q.quantite_recue, 0) AS quantite_recue,
                GREATEST(COALESCE(q.quantite_commandee, 0) - COALESCE(q.quantite_recue, 0), 0) AS quantite_restante,
                COALESCE(q.montant_recu_ht, 0) AS montant_recu_ht,
                COALESCE(q.montant_recu_ttc, 0) AS montant_recu_ttc,
                COALESCE(r.nb_receptions, 0) AS nb_receptions,
                COALESCE(fa.nb_factures, 0) AS nb_factures,
                fa.facture_id,
                fa.numero_facture,
                COALESCE(fa.montant_facture_ht, 0) AS montant_facture_ht,
                COALESCE(fa.montant_facture_ttc, 0) AS montant_facture_ttc,
                ROUND(COALESCE(fa.montant_facture_ttc, 0) - cf.total_ttc, 2) AS ecart_facture_commande,
                ROUND(COALESCE(fa.montant_facture_ttc, 0) - COALESCE(q.montant_recu_ttc, 0), 2) AS ecart_facture_reception
             FROM commandes_fournisseurs cf
             INNER JOIN fournisseurs f ON f.id = cf.fournisseur_id
             LEFT JOIN (
                SELECT commande_id,
                       SUM(quantite) AS quantite_commandee,
                       SUM(quantite_recue) AS quantite_recue,
                       SUM(quantite_recue * prix_achat_ht) AS montant_recu_ht,
                       SUM(quantite_recue * prix_achat_ht * (1 + taux_tva / 100)) AS montant_recu_ttc
                FROM commandes_fournisseurs_lignes
                GROUP BY commande_id
             ) q ON q.commande_id = cf.id
             LEFT JOIN (
                SELECT commande_id, COUNT(*) AS nb_receptions
                FROM receptions_fournisseurs
                WHERE statut = 'active'
                GROUP BY commande_id
             ) r ON r.commande_id = cf.id
             LEFT JOIN (
                SELECT commande_fournisseur_id,
                       COUNT(*) AS nb_factures,
                       MAX(id) AS facture_id,
                       MAX(numero_interne) AS numero_facture,
                       SUM(montant_ht) AS montant_facture_ht,
                       SUM(montant_ttc) AS montant_facture_ttc
                FROM factures_fournisseurs
                WHERE actif = 1 AND commande_fournisseur_id IS NOT NULL
                GROUP BY commande_fournisseur_id
             ) fa ON fa.commande_fournisseur_id = cf.id
             WHERE " . implode(' AND ', $where) . "
             ORDER BY
                CASE
                    WHEN cf.statut IN ('validée', 'partiellement_reçue') THEN 1
                    WHEN COALESCE(fa.nb_factures, 0) = 0 AND cf.statut = 'reçue' THEN 2
                    WHEN ABS(COALESCE(fa.montant_facture_ttc, 0) - cf.total_ttc) > 0.01 THEN 3
                    ELSE 4
                END,
                cf.date_commande DESC,
                cf.id DESC",
            $params
        );
    }

    public function all(array $filters = []): array
    {
        $archive = (string)($filters['archive'] ?? 'actives');
        $where = [];
        $params = [];

        if ($archive === 'archivees') {
            $where[] = 'cf.actif = 0';
        } elseif ($archive !== 'toutes') {
            $where[] = 'cf.actif = 1';
        }

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

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

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

        $sql = "
            SELECT
                cf.*,
                f.raison_sociale AS fournisseur_nom,
                f.code_fournisseur,
                COALESCE(q.quantite_commandee, 0) AS quantite_commandee,
                COALESCE(q.quantite_recue, 0) AS quantite_recue,
                GREATEST(COALESCE(q.quantite_commandee, 0) - COALESCE(q.quantite_recue, 0), 0) AS quantite_restante
            FROM commandes_fournisseurs cf
            INNER JOIN fournisseurs f ON f.id = cf.fournisseur_id
            LEFT JOIN (
                SELECT
                    commande_id,
                    SUM(quantite) AS quantite_commandee,
                    SUM(quantite_recue) AS quantite_recue
                FROM commandes_fournisseurs_lignes
                GROUP BY commande_id
            ) q ON q.commande_id = cf.id
            " . ($where ? 'WHERE ' . implode(' AND ', $where) : '') . "
            ORDER BY cf.date_commande DESC, cf.id DESC
        ";

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

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

    public function lines(int $commandeId): array
    {
        return $this->fetchAll(
            "SELECT * FROM commandes_fournisseurs_lignes WHERE commande_id = :id ORDER BY ordre ASC, id ASC",
            ['id' => $commandeId]
        );
    }

    public function nextNumber(): string
    {
        $prefix = 'CF' . date('Y') . '-';
        $last = $this->fetchColumn(
            "SELECT numero FROM commandes_fournisseurs WHERE numero 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 $header, array $lines): int
    {
        $this->beginTransaction();
        try {
            $totals = $this->computeTotals($lines);
            $sql = "
                INSERT INTO commandes_fournisseurs (
                    fournisseur_id, numero, date_commande, date_livraison_prevue,
                    reference_fournisseur, statut, total_ht, total_tva, total_ttc,
                    observations, actif
                ) VALUES (
                    :fournisseur_id, :numero, :date_commande, :date_livraison_prevue,
                    :reference_fournisseur, :statut, :total_ht, :total_tva, :total_ttc,
                    :observations, 1
                )
            ";

            $this->execute($sql, [
                'fournisseur_id' => (int)$header['fournisseur_id'],
                'numero' => (string)$header['numero'],
                'date_commande' => (string)$header['date_commande'],
                'date_livraison_prevue' => $header['date_livraison_prevue'] ?: null,
                'reference_fournisseur' => trim((string)($header['reference_fournisseur'] ?? '')),
                'statut' => trim((string)($header['statut'] ?? 'brouillon')) ?: 'brouillon',
                'total_ht' => $totals['total_ht'],
                'total_tva' => $totals['total_tva'],
                'total_ttc' => $totals['total_ttc'],
                'observations' => trim((string)($header['observations'] ?? '')),
            ]);

            $id = $this->lastInsertId();
            $this->replaceLines($id, $lines);
            $this->commit();
            return $id;
        } catch (\Throwable $exception) {
            $this->rollBack();
            throw $exception;
        }
    }

    public function update(int $id, array $header, array $lines): void
    {
        $this->beginTransaction();
        try {
            $totals = $this->computeTotals($lines);
            $sql = "
                UPDATE commandes_fournisseurs SET
                    fournisseur_id = :fournisseur_id,
                    date_commande = :date_commande,
                    date_livraison_prevue = :date_livraison_prevue,
                    reference_fournisseur = :reference_fournisseur,
                    statut = :statut,
                    total_ht = :total_ht,
                    total_tva = :total_tva,
                    total_ttc = :total_ttc,
                    observations = :observations
                WHERE id = :id
            ";

            $this->execute($sql, [
                'id' => $id,
                'fournisseur_id' => (int)$header['fournisseur_id'],
                'date_commande' => (string)$header['date_commande'],
                'date_livraison_prevue' => $header['date_livraison_prevue'] ?: null,
                'reference_fournisseur' => trim((string)($header['reference_fournisseur'] ?? '')),
                'statut' => trim((string)($header['statut'] ?? 'brouillon')) ?: 'brouillon',
                'total_ht' => $totals['total_ht'],
                'total_tva' => $totals['total_tva'],
                'total_ttc' => $totals['total_ttc'],
                'observations' => trim((string)($header['observations'] ?? '')),
            ]);

            $this->replaceLines($id, $lines);
            $this->commit();
        } catch (\Throwable $exception) {
            $this->rollBack();
            throw $exception;
        }
    }


    public function receive(int $id, array $receivedQuantities): array
    {
        $commande = $this->find($id);
        if (!$commande) {
            throw new \RuntimeException('Commande fournisseur introuvable.');
        }
        if ((int)($commande['actif'] ?? 1) !== 1) {
            throw new \RuntimeException('Impossible de réceptionner une commande archivée.');
        }
        if ((string)($commande['statut'] ?? '') === 'annulée') {
            throw new \RuntimeException('Impossible de réceptionner une commande annulée.');
        }
        if (!in_array((string)($commande['statut'] ?? ''), ['validée', 'partiellement_reçue'], true)) {
            throw new \RuntimeException('La commande doit être validée avant de pouvoir être réceptionnée.');
        }

        $lines = $this->lines($id);
        if (empty($lines)) {
            throw new \RuntimeException('La commande ne contient aucune ligne à réceptionner.');
        }

        $this->beginTransaction();
        try {
            $stockAdded = 0.0;
            $hasAnyReceived = false;
            $isComplete = true;

            foreach ($lines as $line) {
                $lineId = (int)$line['id'];
                $orderedQty = (float)$line['quantite'];
                $oldReceivedQty = (float)($line['quantite_recue'] ?? 0);
                $newReceivedQty = $this->normalizeNumber($receivedQuantities[$lineId] ?? $oldReceivedQty);

                if ($newReceivedQty < $oldReceivedQty) {
                    throw new \RuntimeException('La quantité reçue ne peut pas être inférieure à la quantité déjà réceptionnée.');
                }
                if ($newReceivedQty > $orderedQty) {
                    throw new \RuntimeException('La quantité reçue ne peut pas dépasser la quantité commandée.');
                }

                $delta = round($newReceivedQty - $oldReceivedQty, 3);
                if ($newReceivedQty > 0) {
                    $hasAnyReceived = true;
                }
                if ($newReceivedQty < $orderedQty) {
                    $isComplete = false;
                }

                $this->execute(
                    'UPDATE commandes_fournisseurs_lignes SET quantite_recue = :quantite_recue WHERE id = :id',
                    ['id' => $lineId, 'quantite_recue' => $newReceivedQty]
                );

                $articleId = (int)($line['article_id'] ?? 0);
                if ($delta > 0 && $articleId > 0) {
                    $this->addStockFromReception($articleId, $delta, (float)$line['prix_achat_ht'], $id, (string)$commande['numero']);
                    $stockAdded += $delta;
                }
            }

            $status = $isComplete ? 'reçue' : ($hasAnyReceived ? 'partiellement_reçue' : 'validée');
            $this->execute(
                "UPDATE commandes_fournisseurs
                 SET statut = :statut, est_receptionnee = :est_receptionnee, date_validation = COALESCE(date_validation, NOW())
                 WHERE id = :id",
                ['id' => $id, 'statut' => $status, 'est_receptionnee' => $isComplete ? 1 : 0]
            );

            $this->commit();
            return ['stock_added' => $stockAdded, 'statut' => $status];
        } catch (\Throwable $exception) {
            $this->rollBack();
            throw $exception;
        }
    }

private function addStockFromReception(
    int $articleId,
    float $quantity,
    float $purchasePrice,
    int $commandeId,
    string $numero
): void {
    $stock = $this->fetchOne(
        "SELECT
            quantite,
            stock_minimum,
            valeur_stock
         FROM stocks
         WHERE article_id = :article_id
         LIMIT 1",
        [
            'article_id' => $articleId,
        ]
    ) ?? [];

    $currentQuantity = round(
        (float)($stock['quantite'] ?? 0),
        3
    );

    $currentMinimum = round(
        (float)($stock['stock_minimum'] ?? 0),
        3
    );

    $currentValue = round(
        (float)($stock['valeur_stock'] ?? 0),
        2
    );

    $receivedQuantity = round($quantity, 3);
    $receivedValue = round(
        $receivedQuantity * $purchasePrice,
        2
    );

    $newQuantity = round(
        $currentQuantity + $receivedQuantity,
        3
    );

    /*
     * Valorisation PMP :
     * ancienne valeur + valeur de la nouvelle réception.
     */
    $newValue = round(
        $currentValue + $receivedValue,
        2
    );

    $weightedAveragePrice = $newQuantity > 0
        ? round($newValue / $newQuantity, 4)
        : 0.0;

    $this->execute(
        "INSERT INTO stocks (
            article_id,
            quantite,
            stock_minimum,
            valeur_stock,
            date_maj
         ) VALUES (
            :article_id,
            :quantite,
            :stock_minimum,
            :valeur_stock,
            NOW()
         )
         ON DUPLICATE KEY UPDATE
            quantite = VALUES(quantite),
            stock_minimum = VALUES(stock_minimum),
            valeur_stock = VALUES(valeur_stock),
            date_maj = NOW()",
        [
            'article_id' => $articleId,
            'quantite' => $newQuantity,
            'stock_minimum' => $currentMinimum,
            'valeur_stock' => $newValue,
        ]
    );

    $this->execute(
        "INSERT INTO mouvements_stock (
            article_id,
            type_mouvement,
            quantite,
            prix_achat_ht,
            origine,
            origine_id,
            commentaire,
            utilisateur
         ) VALUES (
            :article_id,
            'ENT',
            :quantite,
            :prix_achat_ht,
            'RECEPTION_FOURNISSEUR',
            :origine_id,
            :commentaire,
            'Admin'
         )",
        [
            'article_id' => $articleId,
            'quantite' => $receivedQuantity,
            'prix_achat_ht' => $purchasePrice,
            'origine_id' => $commandeId,
            'commentaire' =>
                'Réception commande fournisseur ' .
                $numero .
                ' — PMP après réception : ' .
                number_format(
                    $weightedAveragePrice,
                    4,
                    '.',
                    ''
                ) .
                ' MAD',
        ]
    );
}

    private function normalizeNumber(mixed $value): float
    {
        if (is_int($value) || is_float($value)) {
            return (float)$value;
        }
        $value = str_replace(["\xc2\xa0", ' ', ','], ['', '', '.'], (string)$value);
        return is_numeric($value) ? (float)$value : 0.0;
    }


    public function purchaseHistory(array $filters = []): array
    {
        $where = ['cf.actif = 1', "cf.statut <> 'annulée'"];
        $params = [];

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

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

        if (!empty($filters['date_from'])) {
            $where[] = 'cf.date_commande >= :date_from';
            $params['date_from'] = (string)$filters['date_from'];
        }

        if (!empty($filters['date_to'])) {
            $where[] = 'cf.date_commande <= :date_to';
            $params['date_to'] = (string)$filters['date_to'];
        }

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

        $sql = "
            SELECT
                cfl.*,
                cf.numero,
                cf.date_commande,
                cf.statut,
                cf.reference_fournisseur,
                f.id AS fournisseur_id,
                f.raison_sociale AS fournisseur_nom,
                f.code_fournisseur,
                a.reference AS article_reference_actuelle,
                a.designation AS article_designation_actuelle
            FROM commandes_fournisseurs_lignes cfl
            INNER JOIN commandes_fournisseurs cf ON cf.id = cfl.commande_id
            INNER JOIN fournisseurs f ON f.id = cf.fournisseur_id
            LEFT JOIN articles a ON a.id = cfl.article_id
            WHERE " . implode(' AND ', $where) . "
            ORDER BY cf.date_commande DESC, cf.id DESC, cfl.ordre ASC, cfl.id ASC
            LIMIT 500
        ";

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

    public function purchaseHistoryStats(array $filters = []): array
    {
        $where = ['cf.actif = 1', "cf.statut <> 'annulée'"];
        $params = [];

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

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

        if (!empty($filters['date_from'])) {
            $where[] = 'cf.date_commande >= :date_from';
            $params['date_from'] = (string)$filters['date_from'];
        }

        if (!empty($filters['date_to'])) {
            $where[] = 'cf.date_commande <= :date_to';
            $params['date_to'] = (string)$filters['date_to'];
        }

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

        $sql = "
            SELECT
                COUNT(*) AS nombre_lignes,
                COUNT(DISTINCT cf.id) AS nombre_commandes,
                COUNT(DISTINCT cf.fournisseur_id) AS nombre_fournisseurs,
                SUM(cfl.quantite) AS quantite_commandee,
                SUM(COALESCE(cfl.quantite_recue, 0)) AS quantite_recue,
                SUM(cfl.total_ht) AS total_ht,
                MIN(cfl.prix_achat_ht) AS prix_min,
                MAX(cfl.prix_achat_ht) AS prix_max,
                AVG(cfl.prix_achat_ht) AS prix_moyen
            FROM commandes_fournisseurs_lignes cfl
            INNER JOIN commandes_fournisseurs cf ON cf.id = cfl.commande_id
            INNER JOIN fournisseurs f ON f.id = cf.fournisseur_id
            WHERE " . implode(' AND ', $where) . "
        ";

        return $this->fetchOne($sql, $params) ?? [];
    }


    public function purchaseAnalysis(array $filters = []): array
    {
        $where = ['a.actif = 1'];
        $params = [];

        if (!empty($filters['q'])) {
            $where[] = '(a.reference LIKE :q OR a.designation LIKE :q OR fo.raison_sociale LIKE :q OR fo.code_fournisseur LIKE :q)';
            $params['q'] = '%' . trim((string)$filters['q']) . '%';
        }

        if (!empty($filters['fournisseur_id'])) {
            $where[] = 'COALESCE(a.fournisseur_principal_id, lastcf.fournisseur_id) = :fournisseur_id';
            $params['fournisseur_id'] = (int)$filters['fournisseur_id'];
        }

        $sql = "
            SELECT
                a.id,
                a.reference,
                a.designation,
                a.prix_achat_ht,
                a.coefficient,
                a.prix_vente_ht,
                a.prix_vente_ttc,
                a.taux_tva,
                a.marge_dh,
                a.marge_pourcentage,
                fo.raison_sociale AS fournisseur_principal_nom,
                fo.code_fournisseur AS fournisseur_principal_code,
                COALESCE(s.quantite, 0) AS stock_quantite,
                lastcfl.prix_achat_ht AS dernier_prix_achat_ht,
                lastcfl.quantite AS derniere_quantite_commandee,
                COALESCE(lastcfl.quantite_recue, 0) AS derniere_quantite_recue,
                lastcf.date_commande AS date_dernier_achat,
                lastcf.numero AS derniere_commande_numero,
                lastcf.id AS derniere_commande_id,
                lastf.raison_sociale AS dernier_fournisseur_nom,
                COALESCE(hist.nombre_commandes, 0) AS nombre_commandes,
                COALESCE(hist.quantite_commandee, 0) AS quantite_commandee,
                COALESCE(hist.quantite_recue, 0) AS quantite_recue,
                COALESCE(hist.total_achats_ht, 0) AS total_achats_ht,
                hist.prix_min,
                hist.prix_max,
                hist.prix_moyen
            FROM articles a
            LEFT JOIN fournisseurs fo ON fo.id = a.fournisseur_principal_id
            LEFT JOIN stocks s ON s.article_id = a.id
            LEFT JOIN commandes_fournisseurs_lignes lastcfl ON lastcfl.id = (
                SELECT cfl2.id
                FROM commandes_fournisseurs_lignes cfl2
                INNER JOIN commandes_fournisseurs cf2 ON cf2.id = cfl2.commande_id
                WHERE cfl2.article_id = a.id
                  AND cf2.actif = 1
                  AND cf2.statut <> 'annulée'
                ORDER BY cf2.date_commande DESC, cfl2.id DESC
                LIMIT 1
            )
            LEFT JOIN commandes_fournisseurs lastcf ON lastcf.id = lastcfl.commande_id
            LEFT JOIN fournisseurs lastf ON lastf.id = lastcf.fournisseur_id
            LEFT JOIN (
                SELECT
                    cfl.article_id,
                    COUNT(DISTINCT cf.id) AS nombre_commandes,
                    SUM(cfl.quantite) AS quantite_commandee,
                    SUM(COALESCE(cfl.quantite_recue, 0)) AS quantite_recue,
                    SUM(cfl.total_ht) AS total_achats_ht,
                    MIN(cfl.prix_achat_ht) AS prix_min,
                    MAX(cfl.prix_achat_ht) AS prix_max,
                    AVG(cfl.prix_achat_ht) AS prix_moyen
                FROM commandes_fournisseurs_lignes cfl
                INNER JOIN commandes_fournisseurs cf ON cf.id = cfl.commande_id
                WHERE cf.actif = 1
                  AND cf.statut <> 'annulée'
                  AND cfl.article_id IS NOT NULL
                GROUP BY cfl.article_id
            ) hist ON hist.article_id = a.id
            WHERE " . implode(' AND ', $where) . "
            ORDER BY
                (lastcfl.prix_achat_ht > a.prix_achat_ht) DESC,
                a.designation ASC
        ";

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


    public function purchaseDashboardStats(): array
    {
        $sql = "
            SELECT
                COUNT(DISTINCT CASE WHEN cf.statut IN ('brouillon', 'validée', 'envoyée', 'partiellement_reçue') THEN cf.id END) AS commandes_en_cours,
                COALESCE(SUM(CASE WHEN cf.statut IN ('brouillon', 'validée', 'envoyée', 'partiellement_reçue') THEN cf.total_ttc ELSE 0 END), 0) AS total_en_cours_ttc,
                COUNT(DISTINCT CASE WHEN cf.statut = 'partiellement_reçue' THEN cf.id END) AS receptions_partielles,
                COUNT(DISTINCT CASE WHEN cf.date_livraison_prevue IS NOT NULL AND cf.date_livraison_prevue < CURDATE() AND cf.statut NOT IN ('reçue', 'annulée') THEN cf.id END) AS commandes_en_retard,
                COALESCE(SUM(CASE WHEN cf.statut <> 'annulée' THEN GREATEST(cfl.quantite - COALESCE(cfl.quantite_recue, 0), 0) ELSE 0 END), 0) AS quantite_restante,
                COALESCE(SUM(CASE WHEN cf.statut <> 'annulée' THEN GREATEST(cfl.quantite - COALESCE(cfl.quantite_recue, 0), 0) * cfl.prix_achat_ht ELSE 0 END), 0) AS montant_restant_ht
            FROM commandes_fournisseurs cf
            LEFT JOIN commandes_fournisseurs_lignes cfl ON cfl.commande_id = cf.id
            WHERE cf.actif = 1
        ";

        return $this->fetchOne($sql) ?? [];
    }

    public function purchaseDashboardOrdersInProgress(int $limit = 8): array
    {
        $limit = max(1, min(30, $limit));
        $sql = "
            SELECT
                cf.id,
                cf.numero,
                cf.date_commande,
                cf.date_livraison_prevue,
                cf.statut,
                cf.total_ttc,
                f.raison_sociale AS fournisseur_nom,
                f.code_fournisseur,
                SUM(cfl.quantite) AS quantite_commandee,
                SUM(COALESCE(cfl.quantite_recue, 0)) AS quantite_recue,
                SUM(GREATEST(cfl.quantite - COALESCE(cfl.quantite_recue, 0), 0)) AS quantite_restante
            FROM commandes_fournisseurs cf
            INNER JOIN fournisseurs f ON f.id = cf.fournisseur_id
            LEFT JOIN commandes_fournisseurs_lignes cfl ON cfl.commande_id = cf.id
            WHERE cf.actif = 1
              AND cf.statut IN ('brouillon', 'validée', 'envoyée', 'partiellement_reçue')
            GROUP BY cf.id, cf.numero, cf.date_commande, cf.date_livraison_prevue, cf.statut, cf.total_ttc, f.raison_sociale, f.code_fournisseur
            ORDER BY
                (cf.date_livraison_prevue IS NULL) ASC,
                cf.date_livraison_prevue ASC,
                cf.date_commande DESC,
                cf.id DESC
            LIMIT " . $limit;

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

    public function purchaseDashboardExpectedReceptions(int $limit = 8): array
    {
        $limit = max(1, min(30, $limit));
        $sql = "
            SELECT
                cf.id,
                cf.numero,
                cf.date_commande,
                cf.date_livraison_prevue,
                cf.statut,
                f.raison_sociale AS fournisseur_nom,
                SUM(GREATEST(cfl.quantite - COALESCE(cfl.quantite_recue, 0), 0)) AS quantite_restante,
                SUM(GREATEST(cfl.quantite - COALESCE(cfl.quantite_recue, 0), 0) * cfl.prix_achat_ht) AS montant_restant_ht
            FROM commandes_fournisseurs cf
            INNER JOIN fournisseurs f ON f.id = cf.fournisseur_id
            INNER JOIN commandes_fournisseurs_lignes cfl ON cfl.commande_id = cf.id
            WHERE cf.actif = 1
              AND cf.statut NOT IN ('reçue', 'annulée')
            GROUP BY cf.id, cf.numero, cf.date_commande, cf.date_livraison_prevue, cf.statut, f.raison_sociale
            HAVING quantite_restante > 0
            ORDER BY
                (cf.date_livraison_prevue IS NULL) ASC,
                cf.date_livraison_prevue ASC,
                cf.date_commande DESC,
                cf.id DESC
            LIMIT " . $limit;

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

    public function purchaseDashboardTopSuppliers(int $limit = 5): array
    {
        $limit = max(1, min(20, $limit));
        $sql = "
            SELECT
                f.id,
                f.raison_sociale AS fournisseur_nom,
                f.code_fournisseur,
                COUNT(DISTINCT cf.id) AS nombre_commandes,
                COALESCE(SUM(cf.total_ht), 0) AS total_ht,
                COALESCE(SUM(cf.total_ttc), 0) AS total_ttc
            FROM commandes_fournisseurs cf
            INNER JOIN fournisseurs f ON f.id = cf.fournisseur_id
            WHERE cf.actif = 1
              AND cf.statut <> 'annulée'
              AND cf.date_commande >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
            GROUP BY f.id, f.raison_sociale, f.code_fournisseur
            ORDER BY total_ht DESC, nombre_commandes DESC
            LIMIT " . $limit;

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

    public function purchaseDashboardTopArticles(int $limit = 5): array
    {
        $limit = max(1, min(20, $limit));
        $sql = "
            SELECT
                COALESCE(a.id, cfl.article_id, 0) AS article_id,
                COALESCE(a.reference, cfl.reference) AS reference,
                COALESCE(a.designation, cfl.designation) AS designation,
                COUNT(DISTINCT cf.id) AS nombre_commandes,
                COALESCE(SUM(cfl.quantite), 0) AS quantite_commandee,
                COALESCE(SUM(COALESCE(cfl.quantite_recue, 0)), 0) AS quantite_recue,
                COALESCE(SUM(cfl.total_ht), 0) AS total_ht
            FROM commandes_fournisseurs_lignes cfl
            INNER JOIN commandes_fournisseurs cf ON cf.id = cfl.commande_id
            LEFT JOIN articles a ON a.id = cfl.article_id
            WHERE cf.actif = 1
              AND cf.statut <> 'annulée'
              AND cf.date_commande >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
            GROUP BY COALESCE(a.id, cfl.article_id, 0), COALESCE(a.reference, cfl.reference), COALESCE(a.designation, cfl.designation)
            ORDER BY total_ht DESC, quantite_commandee DESC
            LIMIT " . $limit;

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

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

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

    private function replaceLines(int $commandeId, array $lines): void
    {
        $this->execute('DELETE FROM commandes_fournisseurs_lignes WHERE commande_id = :id', ['id' => $commandeId]);

        $sql = "
            INSERT INTO commandes_fournisseurs_lignes (
                commande_id, article_id, reference, designation, quantite,
                prix_achat_ht, taux_tva, total_ht, total_tva, total_ttc, ordre
            ) VALUES (
                :commande_id, :article_id, :reference, :designation, :quantite,
                :prix_achat_ht, :taux_tva, :total_ht, :total_tva, :total_ttc, :ordre
            )
        ";

        foreach ($lines as $index => $line) {
            $quantity = (float)$line['quantite'];
            $price = (float)$line['prix_achat_ht'];
            $vatRate = (float)$line['taux_tva'];
            $totalHt = round($quantity * $price, 2);
            $totalTva = round($totalHt * ($vatRate / 100), 2);
            $totalTtc = round($totalHt + $totalTva, 2);

            $this->execute($sql, [
                'commande_id' => $commandeId,
                'article_id' => $line['article_id'] ?: null,
                'reference' => trim((string)$line['reference']),
                'designation' => trim((string)$line['designation']),
                'quantite' => $quantity,
                'prix_achat_ht' => $price,
                'taux_tva' => $vatRate,
                'total_ht' => $totalHt,
                'total_tva' => $totalTva,
                'total_ttc' => $totalTtc,
                'ordre' => $index + 1,
            ]);
        }
    }

    private function computeTotals(array $lines): array
    {
        $totalHt = 0.0;
        $totalTva = 0.0;

        foreach ($lines as $line) {
            $lineHt = round((float)$line['quantite'] * (float)$line['prix_achat_ht'], 2);
            $lineTva = round($lineHt * ((float)$line['taux_tva'] / 100), 2);
            $totalHt += $lineHt;
            $totalTva += $lineTva;
        }

        return [
            'total_ht' => round($totalHt, 2),
            'total_tva' => round($totalTva, 2),
            'total_ttc' => round($totalHt + $totalTva, 2),
        ];
    }
}
