<?php

declare(strict_types=1);

namespace App\Services;

use App\Core\Database;
use App\Models\Stock;
use PDO;
use Throwable;

final class DashboardService extends BaseService
{
    private PDO $db;

    public function __construct()
    {
        $this->db = Database::connection();
    }

    public function general(): array
    {
        $stock = new Stock();
        (new StockService($stock))->initialize();

        $stockSummary = $stock->summary();
        $stockValuation = $stock->valuationSummary();
        $lowStock = $stock->lowStockArticles();

        return [
            'commercial' => $this->commercialStats(),
            'achats' => $this->achatsStats(),
            'fournisseurs' => $this->fournisseursStats(),
            'stockSummary' => $stockSummary,
            'stockValuation' => $stockValuation,
            'stockAlerts' => array_slice($lowStock, 0, 8),
            'stockRuptures' => array_slice(array_values(array_filter($lowStock, static fn (array $row): bool => ($row['etat_stock'] ?? '') === 'rupture')), 0, 8),
            'recentMovements' => $stock->recentMovements(8),
            'recentDocuments' => $this->recentDocuments(),
            'quickLinks' => $this->quickLinks(),
            'version' => 'Phoenix P3.6.9',
        ];
    }

    private function commercialStats(): array
    {
        $stats = [
            'ca_ht' => 0.0,
            'ca_ttc' => 0.0,
            'marge_ht' => 0.0,
            'reste_du' => 0.0,
            'nb_factures' => 0,
            'nb_devis' => 0,
            'nb_documents' => 0,
        ];

        if ($this->tableExists('factures')) {
            $row = $this->fetchOne("\n                SELECT\n                    COALESCE(SUM(total_ht), 0) AS ca_ht,\n                    COALESCE(SUM(total_ttc), 0) AS ca_ttc,\n                    COALESCE(SUM(marge_ht), 0) AS marge_ht,\n                    COALESCE(SUM(reste_du), 0) AS reste_du,\n                    COUNT(*) AS nb_factures\n                FROM factures\n                WHERE actif = 1\n            ");
            $stats['ca_ht'] = (float)($row['ca_ht'] ?? 0);
            $stats['ca_ttc'] = (float)($row['ca_ttc'] ?? 0);
            $stats['marge_ht'] = (float)($row['marge_ht'] ?? 0);
            $stats['reste_du'] = (float)($row['reste_du'] ?? 0);
            $stats['nb_factures'] = (int)($row['nb_factures'] ?? 0);
        }

        if ($this->tableExists('devis')) {
            $stats['nb_devis'] = (int)$this->scalar('SELECT COUNT(*) FROM devis WHERE actif = 1');
        }

        if ($this->tableExists('documents')) {
            $stats['nb_documents'] = (int)$this->scalar('SELECT COUNT(*) FROM documents WHERE actif = 1');
        }

        return $stats;
    }

    private function achatsStats(): array
    {
        $stats = [
            'commandes_en_cours' => 0,
            'receptions_attendues' => 0,
            'commandes_retard' => 0,
            'total_commandes_ttc' => 0.0,
            'recentOrders' => [],
        ];

        if (!$this->tableExists('commandes_fournisseurs')) {
            return $stats;
        }

        $row = $this->fetchOne("\n            SELECT\n                SUM(CASE WHEN actif = 1 AND statut NOT IN ('reçue', 'annulée') THEN 1 ELSE 0 END) AS commandes_en_cours,\n                SUM(CASE WHEN actif = 1 AND statut IN ('validée', 'envoyée', 'partiellement_reçue') THEN 1 ELSE 0 END) AS receptions_attendues,\n                SUM(CASE WHEN actif = 1 AND date_livraison_prevue IS NOT NULL AND date_livraison_prevue < CURDATE() AND statut NOT IN ('reçue', 'annulée') THEN 1 ELSE 0 END) AS commandes_retard,\n                COALESCE(SUM(CASE WHEN actif = 1 THEN total_ttc ELSE 0 END), 0) AS total_commandes_ttc\n            FROM commandes_fournisseurs\n        ");

        $stats['commandes_en_cours'] = (int)($row['commandes_en_cours'] ?? 0);
        $stats['receptions_attendues'] = (int)($row['receptions_attendues'] ?? 0);
        $stats['commandes_retard'] = (int)($row['commandes_retard'] ?? 0);
        $stats['total_commandes_ttc'] = (float)($row['total_commandes_ttc'] ?? 0);
        $stats['recentOrders'] = $this->recentPurchaseOrders();

        return $stats;
    }

    private function fournisseursStats(): array
    {
        $stats = [
            'total_du' => 0.0,
            'total_retard' => 0.0,
            'a_payer_7j' => 0.0,
            'a_payer_30j' => 0.0,
            'nb_a_payer' => 0,
            'nb_retard' => 0,
            'upcomingFactures' => [],
            'overdueFactures' => [],
        ];

        if (!$this->tableExists('factures_fournisseurs')) {
            return $stats;
        }

        $row = $this->fetchOne("\n            SELECT\n                COALESCE(SUM(CASE WHEN actif = 1 AND reste_du > 0 THEN reste_du ELSE 0 END), 0) AS total_du,\n                COALESCE(SUM(CASE WHEN actif = 1 AND reste_du > 0 AND date_echeance IS NOT NULL AND date_echeance < CURDATE() THEN reste_du ELSE 0 END), 0) AS total_retard,\n                COALESCE(SUM(CASE WHEN actif = 1 AND reste_du > 0 AND date_echeance BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY) THEN reste_du ELSE 0 END), 0) AS a_payer_7j,\n                COALESCE(SUM(CASE WHEN actif = 1 AND reste_du > 0 AND date_echeance BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY) THEN reste_du ELSE 0 END), 0) AS a_payer_30j,\n                SUM(CASE WHEN actif = 1 AND reste_du > 0 THEN 1 ELSE 0 END) AS nb_a_payer,\n                SUM(CASE WHEN actif = 1 AND reste_du > 0 AND date_echeance IS NOT NULL AND date_echeance < CURDATE() THEN 1 ELSE 0 END) AS nb_retard\n            FROM factures_fournisseurs\n        ");

        foreach (['total_du', 'total_retard', 'a_payer_7j', 'a_payer_30j'] as $key) {
            $stats[$key] = (float)($row[$key] ?? 0);
        }
        $stats['nb_a_payer'] = (int)($row['nb_a_payer'] ?? 0);
        $stats['nb_retard'] = (int)($row['nb_retard'] ?? 0);
        $stats['upcomingFactures'] = $this->upcomingSupplierInvoices(false);
        $stats['overdueFactures'] = $this->upcomingSupplierInvoices(true);

        return $stats;
    }

    private function recentPurchaseOrders(): array
    {
        if (!$this->tableExists('commandes_fournisseurs')) {
            return [];
        }

        $join = $this->tableExists('fournisseurs') ? 'LEFT JOIN fournisseurs f ON f.id = c.fournisseur_id' : '';
        return $this->fetchAll("\n            SELECT c.id, c.numero, c.date_commande, c.statut, c.total_ttc, COALESCE(f.nom_commercial, f.raison_sociale, '') AS fournisseur_nom\n            FROM commandes_fournisseurs c\n            {$join}\n            WHERE c.actif = 1\n            ORDER BY c.date_commande DESC, c.id DESC\n            LIMIT 6\n        ");
    }

    private function upcomingSupplierInvoices(bool $overdue): array
    {
        if (!$this->tableExists('factures_fournisseurs')) {
            return [];
        }

        $join = $this->tableExists('fournisseurs') ? 'LEFT JOIN fournisseurs f ON f.id = ff.fournisseur_id' : '';
        $whereDate = $overdue
            ? 'ff.date_echeance IS NOT NULL AND ff.date_echeance < CURDATE()'
            : '(ff.date_echeance IS NULL OR ff.date_echeance >= CURDATE())';

        return $this->fetchAll("\n            SELECT ff.id, ff.numero_interne, ff.numero_facture_fournisseur, ff.date_echeance, ff.reste_du, ff.statut, COALESCE(f.nom_commercial, f.raison_sociale, '') AS fournisseur_nom\n            FROM factures_fournisseurs ff\n            {$join}\n            WHERE ff.actif = 1 AND ff.reste_du > 0 AND {$whereDate}\n            ORDER BY CASE WHEN ff.date_echeance IS NULL THEN 1 ELSE 0 END, ff.date_echeance ASC, ff.id DESC\n            LIMIT 6\n        ");
    }

    private function recentDocuments(): array
    {
        if (!$this->tableExists('documents')) {
            return [];
        }

        return $this->fetchAll("\n            SELECT id, type_document, numero, date_document, statut, total_ttc\n            FROM documents\n            WHERE actif = 1\n            ORDER BY date_document DESC, id DESC\n            LIMIT 6\n        ");
    }

    private function quickLinks(): array
    {
        return [
            ['label' => 'Documents', 'page' => 'documents', 'description' => 'Devis, BL, factures', 'icon' => 'bi bi-file-earmark-text', 'color' => '#2563eb'],
            ['label' => 'Clients', 'page' => 'clients', 'description' => 'Base clients', 'icon' => 'bi bi-people-fill', 'color' => '#0ea5e9'],
            ['label' => 'Articles', 'page' => 'articles', 'description' => 'Catalogue produits', 'icon' => 'bi bi-box-seam-fill', 'color' => '#7c3aed'],
            ['label' => 'Fournisseurs', 'page' => 'fournisseurs', 'description' => 'Base fournisseurs', 'icon' => 'bi bi-buildings-fill', 'color' => '#0891b2'],
            ['label' => 'Devis', 'page' => 'devis', 'description' => 'Offres commerciales', 'icon' => 'bi bi-clipboard2-check-fill', 'color' => '#2563eb'],
            ['label' => 'Factures', 'page' => 'factures', 'description' => 'Facturation client', 'icon' => 'bi bi-credit-card-2-front-fill', 'color' => '#16a34a'],
            ['label' => 'Commandes', 'page' => 'commandes_fournisseurs', 'description' => 'Achats fournisseurs', 'icon' => 'bi bi-cart-fill', 'color' => '#f97316'],
            ['label' => 'Stock', 'page' => 'stock_dashboard', 'description' => 'Pilotage stock', 'icon' => 'bi bi-shop', 'color' => '#dc2626'],
            ['label' => 'Statistiques', 'page' => 'dashboard_commercial', 'description' => 'Tableaux de bord', 'icon' => 'bi bi-bar-chart-line-fill', 'color' => '#4f46e5'],
            ['label' => 'Relances', 'page' => 'commercial_actions', 'description' => 'Actions commerciales', 'icon' => 'bi bi-megaphone-fill', 'color' => '#db2777'],
            ['label' => 'Paramètres', 'page' => 'parametres_societe', 'description' => 'Société & réglages', 'icon' => 'bi bi-gear-fill', 'color' => '#475569'],
            ['label' => 'Maintenance', 'page' => 'maintenance', 'description' => 'Santé système', 'icon' => 'bi bi-tools', 'color' => '#0f172a'],
        ];
    }


    public function commercialDashboard(): array
    {
        return [
            'kpis' => $this->commercialKpis(),
            'devisStatus' => $this->devisStatusStats(),
            'topClients' => $this->topClients(),
            'topArticles' => $this->topArticlesVendus(),
            'facturesAEncaisser' => $this->facturesAEncaisser(),
            'recentEncaissements' => $this->recentEncaissements(),
            'recentDocuments' => $this->recentDocuments(),
            'version' => 'Phoenix P3.6.3',
        ];
    }

    private function commercialKpis(): array
    {
        $stats = [
            'ca_ht_total' => 0.0,
            'ca_ttc_total' => 0.0,
            'ca_ht_mois' => 0.0,
            'ca_ttc_mois' => 0.0,
            'marge_ht_total' => 0.0,
            'marge_ht_mois' => 0.0,
            'marge_pourcentage_moyenne' => 0.0,
            'reste_a_encaisser' => 0.0,
            'montant_encaisse_mois' => 0.0,
            'nb_factures' => 0,
            'nb_factures_mois' => 0,
            'nb_devis' => 0,
            'nb_devis_mois' => 0,
            'nb_documents_mois' => 0,
        ];

        if ($this->tableExists('factures')) {
            $row = $this->fetchOne("\n                SELECT\n                    COALESCE(SUM(total_ht), 0) AS ca_ht_total,\n                    COALESCE(SUM(total_ttc), 0) AS ca_ttc_total,\n                    COALESCE(SUM(CASE WHEN date_facture >= DATE_FORMAT(CURDATE(), '%Y-%m-01') THEN total_ht ELSE 0 END), 0) AS ca_ht_mois,\n                    COALESCE(SUM(CASE WHEN date_facture >= DATE_FORMAT(CURDATE(), '%Y-%m-01') THEN total_ttc ELSE 0 END), 0) AS ca_ttc_mois,\n                    COALESCE(SUM(marge_ht), 0) AS marge_ht_total,\n                    COALESCE(SUM(CASE WHEN date_facture >= DATE_FORMAT(CURDATE(), '%Y-%m-01') THEN marge_ht ELSE 0 END), 0) AS marge_ht_mois,\n                    COALESCE(AVG(NULLIF(marge_pourcentage, 0)), 0) AS marge_pourcentage_moyenne,\n                    COALESCE(SUM(reste_du), 0) AS reste_a_encaisser,\n                    COUNT(*) AS nb_factures,\n                    SUM(CASE WHEN date_facture >= DATE_FORMAT(CURDATE(), '%Y-%m-01') THEN 1 ELSE 0 END) AS nb_factures_mois\n                FROM factures\n                WHERE actif = 1\n            ");
            foreach (['ca_ht_total','ca_ttc_total','ca_ht_mois','ca_ttc_mois','marge_ht_total','marge_ht_mois','marge_pourcentage_moyenne','reste_a_encaisser'] as $key) {
                $stats[$key] = (float)($row[$key] ?? 0);
            }
            $stats['nb_factures'] = (int)($row['nb_factures'] ?? 0);
            $stats['nb_factures_mois'] = (int)($row['nb_factures_mois'] ?? 0);
        }

        if ($this->tableExists('encaissements')) {
            $stats['montant_encaisse_mois'] = (float)$this->scalar("\n                SELECT COALESCE(SUM(montant), 0)\n                FROM encaissements\n                WHERE actif = 1 AND date_encaissement >= DATE_FORMAT(CURDATE(), '%Y-%m-01')\n            ");
        }

        if ($this->tableExists('devis')) {
            $row = $this->fetchOne("\n                SELECT COUNT(*) AS nb_devis,\n                       SUM(CASE WHEN date_devis >= DATE_FORMAT(CURDATE(), '%Y-%m-01') THEN 1 ELSE 0 END) AS nb_devis_mois\n                FROM devis\n                WHERE actif = 1\n            ");
            $stats['nb_devis'] = (int)($row['nb_devis'] ?? 0);
            $stats['nb_devis_mois'] = (int)($row['nb_devis_mois'] ?? 0);
        }

        if ($this->tableExists('documents')) {
            $stats['nb_documents_mois'] = (int)$this->scalar("\n                SELECT COUNT(*)\n                FROM documents\n                WHERE actif = 1 AND date_document >= DATE_FORMAT(CURDATE(), '%Y-%m-01')\n            ");
        }

        return $stats;
    }

    private function devisStatusStats(): array
    {
        if (!$this->tableExists('devis')) {
            return [];
        }
        return $this->fetchAll("\n            SELECT statut, COUNT(*) AS nombre, COALESCE(SUM(total_ttc), 0) AS total_ttc\n            FROM devis\n            WHERE actif = 1\n            GROUP BY statut\n            ORDER BY nombre DESC, statut ASC\n        ");
    }

    private function topClients(): array
    {
        if (!$this->tableExists('factures')) {
            return [];
        }
        $join = $this->tableExists('clients') ? 'LEFT JOIN clients c ON c.id = f.client_id' : '';
        return $this->fetchAll("\n            SELECT f.client_id, COALESCE(c.raison_sociale, CONCAT('Client #', f.client_id)) AS client_nom,\n                   COUNT(*) AS nb_factures, COALESCE(SUM(f.total_ttc), 0) AS total_ttc,\n                   COALESCE(SUM(f.marge_ht), 0) AS marge_ht\n            FROM factures f\n            {$join}\n            WHERE f.actif = 1\n            GROUP BY f.client_id, client_nom\n            ORDER BY total_ttc DESC\n            LIMIT 10\n        ");
    }

    private function topArticlesVendus(): array
    {
        if (!$this->tableExists('factures_lignes')) {
            return [];
        }
        return $this->fetchAll("\n            SELECT article_id, reference, designation,\n                   COALESCE(SUM(quantite), 0) AS quantite_vendue,\n                   COALESCE(SUM(total_ht), 0) AS total_ht,\n                   COALESCE(SUM(marge_ht), 0) AS marge_ht\n            FROM factures_lignes\n            GROUP BY article_id, reference, designation\n            ORDER BY total_ht DESC\n            LIMIT 10\n        ");
    }

    private function facturesAEncaisser(): array
    {
        if (!$this->tableExists('factures')) {
            return [];
        }
        $join = $this->tableExists('clients') ? 'LEFT JOIN clients c ON c.id = f.client_id' : '';
        return $this->fetchAll("\n            SELECT f.id, f.numero, f.date_facture, f.date_echeance, f.total_ttc, f.montant_regle, f.reste_du,\n                   COALESCE(c.raison_sociale, '') AS client_nom\n            FROM factures f\n            {$join}\n            WHERE f.actif = 1 AND f.reste_du > 0\n            ORDER BY CASE WHEN f.date_echeance IS NULL THEN 1 ELSE 0 END, f.date_echeance ASC, f.date_facture DESC\n            LIMIT 10\n        ");
    }

    private function recentEncaissements(): array
    {
        if (!$this->tableExists('encaissements')) {
            return [];
        }
        $joinClient = $this->tableExists('clients') ? 'LEFT JOIN clients c ON c.id = e.client_id' : '';
        $joinFacture = $this->tableExists('factures') ? 'LEFT JOIN factures f ON f.id = e.facture_id' : '';
        return $this->fetchAll("\n            SELECT e.id, e.date_encaissement, e.mode_reglement, e.montant, e.reference_reglement,\n                   COALESCE(c.raison_sociale, '') AS client_nom, COALESCE(f.numero, '') AS facture_numero\n            FROM encaissements e\n            {$joinClient}\n            {$joinFacture}\n            WHERE e.actif = 1\n            ORDER BY e.date_encaissement DESC, e.id DESC\n            LIMIT 10\n        ");
    }


    public function devisAnalysis(array $filters = []): array
    {
        $filters = [
            'statut' => trim((string)($filters['statut'] ?? '')),
            'client_id' => (int)($filters['client_id'] ?? 0),
            'date_from' => trim((string)($filters['date_from'] ?? '')),
            'date_to' => trim((string)($filters['date_to'] ?? '')),
        ];

        return [
            'filters' => $filters,
            'kpis' => $this->devisAnalysisKpis($filters),
            'devisStatus' => $this->devisAnalysisStatus($filters),
            'devisList' => $this->devisAnalysisList($filters),
            'devisRelance' => $this->devisAnalysisRelance($filters),
            'topClientsDevis' => $this->topClientsDevis($filters),
            'clients' => $this->clientsForFilter(),
            'version' => 'Phoenix P3.6.3',
        ];
    }

    private function devisWhere(array $filters, string $alias = 'd'): array
    {
        $where = ["{$alias}.actif = 1"];
        $params = [];

        if (($filters['statut'] ?? '') !== '') {
            $where[] = "{$alias}.statut = :statut";
            $params['statut'] = $filters['statut'];
        }
        if ((int)($filters['client_id'] ?? 0) > 0) {
            $where[] = "{$alias}.client_id = :client_id";
            $params['client_id'] = (int)$filters['client_id'];
        }
        if (($filters['date_from'] ?? '') !== '') {
            $where[] = "{$alias}.date_devis >= :date_from";
            $params['date_from'] = $filters['date_from'];
        }
        if (($filters['date_to'] ?? '') !== '') {
            $where[] = "{$alias}.date_devis <= :date_to";
            $params['date_to'] = $filters['date_to'];
        }

        return [implode(' AND ', $where), $params];
    }

    private function devisAnalysisKpis(array $filters): array
    {
        $stats = [
            'nb_total' => 0,
            'nb_en_attente' => 0,
            'nb_acceptes' => 0,
            'nb_refuses' => 0,
            'nb_transformes' => 0,
            'total_ttc' => 0.0,
            'potentiel_ttc' => 0.0,
            'accepte_ttc' => 0.0,
            'refuse_ttc' => 0.0,
            'taux_transformation' => 0.0,
            'taux_refus' => 0.0,
        ];
        if (!$this->tableExists('devis')) {
            return $stats;
        }

        [$where, $params] = $this->devisWhere($filters, 'd');
        $row = $this->fetchPreparedOne("\n            SELECT\n                COUNT(*) AS nb_total,\n                COALESCE(SUM(d.total_ttc), 0) AS total_ttc,\n                SUM(CASE WHEN d.statut IN ('brouillon','validé','en attente') THEN 1 ELSE 0 END) AS nb_en_attente,\n                SUM(CASE WHEN d.statut = 'accepté' THEN 1 ELSE 0 END) AS nb_acceptes,\n                SUM(CASE WHEN d.statut = 'transformé' THEN 1 ELSE 0 END) AS nb_transformes,\n                SUM(CASE WHEN d.statut = 'refusé' THEN 1 ELSE 0 END) AS nb_refuses,\n                COALESCE(SUM(CASE WHEN d.statut IN ('brouillon','validé','en attente') THEN d.total_ttc ELSE 0 END), 0) AS potentiel_ttc,\n                COALESCE(SUM(CASE WHEN d.statut IN ('accepté','transformé') THEN d.total_ttc ELSE 0 END), 0) AS accepte_ttc,\n                COALESCE(SUM(CASE WHEN d.statut = 'refusé' THEN d.total_ttc ELSE 0 END), 0) AS refuse_ttc\n            FROM devis d\n            WHERE {$where}\n        ", $params);

        foreach (['nb_total','nb_en_attente','nb_acceptes','nb_transformes','nb_refuses'] as $key) {
            $stats[$key] = (int)($row[$key] ?? 0);
        }
        foreach (['total_ttc','potentiel_ttc','accepte_ttc','refuse_ttc'] as $key) {
            $stats[$key] = (float)($row[$key] ?? 0);
        }

        $decided = $stats['nb_acceptes'] + $stats['nb_transformes'] + $stats['nb_refuses'];
        if ($decided > 0) {
            $stats['taux_transformation'] = round((($stats['nb_acceptes'] + $stats['nb_transformes']) / $decided) * 100, 2);
            $stats['taux_refus'] = round(($stats['nb_refuses'] / $decided) * 100, 2);
        }
        return $stats;
    }

    private function devisAnalysisStatus(array $filters): array
    {
        if (!$this->tableExists('devis')) {
            return [];
        }
        [$where, $params] = $this->devisWhere($filters, 'd');
        return $this->fetchPreparedAll("\n            SELECT d.statut, COUNT(*) AS nombre, COALESCE(SUM(d.total_ttc), 0) AS total_ttc\n            FROM devis d\n            WHERE {$where}\n            GROUP BY d.statut\n            ORDER BY FIELD(d.statut, 'brouillon','validé','en attente','accepté','transformé','refusé'), d.statut\n        ", $params);
    }

    private function devisAnalysisList(array $filters): array
    {
        if (!$this->tableExists('devis')) {
            return [];
        }
        [$where, $params] = $this->devisWhere($filters, 'd');
        $join = $this->tableExists('clients') ? 'LEFT JOIN clients c ON c.id = d.client_id' : '';
        return $this->fetchPreparedAll("\n            SELECT d.id, d.numero, d.date_devis, d.validite, d.statut, d.total_ht, d.total_ttc, d.marge_ht, d.marge_pourcentage,\n                   COALESCE(c.raison_sociale, CONCAT('Client #', d.client_id)) AS client_nom,\n                   CASE\n                       WHEN d.statut IN ('brouillon','validé','en attente') AND d.validite IS NOT NULL AND d.validite < CURDATE() THEN 1\n                       WHEN d.statut IN ('brouillon','validé','en attente') AND d.date_devis < DATE_SUB(CURDATE(), INTERVAL 15 DAY) THEN 1\n                       ELSE 0\n                   END AS a_relancer\n            FROM devis d\n            {$join}\n            WHERE {$where}\n            ORDER BY a_relancer DESC, d.date_devis DESC, d.id DESC\n            LIMIT 100\n        ", $params);
    }

    private function devisAnalysisRelance(array $filters): array
    {
        if (!$this->tableExists('devis')) {
            return [];
        }
        [$where, $params] = $this->devisWhere($filters, 'd');
        $join = $this->tableExists('clients') ? 'LEFT JOIN clients c ON c.id = d.client_id' : '';
        return $this->fetchPreparedAll("\n            SELECT d.id, d.numero, d.date_devis, d.validite, d.statut, d.total_ttc,\n                   COALESCE(c.raison_sociale, CONCAT('Client #', d.client_id)) AS client_nom,\n                   CASE\n                       WHEN d.validite IS NOT NULL AND d.validite < CURDATE() THEN 'Validité dépassée'\n                       WHEN d.date_devis < DATE_SUB(CURDATE(), INTERVAL 15 DAY) THEN 'Devis ancien'\n                       ELSE 'À suivre'\n                   END AS motif_relance\n            FROM devis d\n            {$join}\n            WHERE {$where}\n              AND d.statut IN ('brouillon','validé','en attente')\n              AND ((d.validite IS NOT NULL AND d.validite < CURDATE()) OR d.date_devis < DATE_SUB(CURDATE(), INTERVAL 15 DAY))\n            ORDER BY CASE WHEN d.validite IS NULL THEN 1 ELSE 0 END, d.validite ASC, d.date_devis ASC\n            LIMIT 20\n        ", $params);
    }

    private function topClientsDevis(array $filters): array
    {
        if (!$this->tableExists('devis')) {
            return [];
        }
        [$where, $params] = $this->devisWhere($filters, 'd');
        $join = $this->tableExists('clients') ? 'LEFT JOIN clients c ON c.id = d.client_id' : '';
        return $this->fetchPreparedAll("\n            SELECT d.client_id, COALESCE(c.raison_sociale, CONCAT('Client #', d.client_id)) AS client_nom,\n                   COUNT(*) AS nb_devis, COALESCE(SUM(d.total_ttc), 0) AS total_ttc,\n                   COALESCE(SUM(CASE WHEN d.statut IN ('brouillon','validé','en attente') THEN d.total_ttc ELSE 0 END), 0) AS potentiel_ttc\n            FROM devis d\n            {$join}\n            WHERE {$where}\n            GROUP BY d.client_id, client_nom\n            ORDER BY potentiel_ttc DESC, total_ttc DESC\n            LIMIT 10\n        ", $params);
    }

    private function clientsForFilter(): array
    {
        if (!$this->tableExists('clients')) {
            return [];
        }
        return $this->fetchAll("\n            SELECT id, raison_sociale\n            FROM clients\n            WHERE actif = 1\n            ORDER BY raison_sociale ASC\n            LIMIT 500\n        ");
    }


    public function facturesClients(array $filters = []): array
    {
        $filters = [
            'statut' => trim((string)($filters['statut'] ?? '')),
            'client_id' => (int)($filters['client_id'] ?? 0),
            'date_from' => trim((string)($filters['date_from'] ?? '')),
            'date_to' => trim((string)($filters['date_to'] ?? '')),
        ];

        return [
            'filters' => $filters,
            'kpis' => $this->facturesClientsKpis($filters),
            'factures' => $this->facturesClientsList($filters),
            'enRetard' => $this->facturesClientsRetard($filters),
            'topClientsDus' => $this->topClientsDus($filters),
            'recentEncaissements' => $this->recentEncaissements(),
            'clients' => $this->clientsForFilter(),
            'version' => 'Phoenix P3.6.3',
        ];
    }

    private function facturesClientsWhere(array $filters, string $alias = 'f'): array
    {
        $where = ["{$alias}.actif = 1"];
        $params = [];

        if ((int)($filters['client_id'] ?? 0) > 0) {
            $where[] = "{$alias}.client_id = :client_id";
            $params['client_id'] = (int)$filters['client_id'];
        }
        if (($filters['date_from'] ?? '') !== '') {
            $where[] = "{$alias}.date_facture >= :date_from";
            $params['date_from'] = $filters['date_from'];
        }
        if (($filters['date_to'] ?? '') !== '') {
            $where[] = "{$alias}.date_facture <= :date_to";
            $params['date_to'] = $filters['date_to'];
        }

        switch ((string)($filters['statut'] ?? '')) {
            case 'non_reglee':
                $where[] = "COALESCE({$alias}.montant_regle, 0) <= 0 AND COALESCE({$alias}.reste_du, {$alias}.total_ttc) > 0";
                break;
            case 'partielle':
                $where[] = "COALESCE({$alias}.montant_regle, 0) > 0 AND COALESCE({$alias}.reste_du, 0) > 0";
                break;
            case 'reglee':
                $where[] = "COALESCE({$alias}.reste_du, 0) <= 0";
                break;
            case 'en_retard':
                $where[] = "COALESCE({$alias}.reste_du, 0) > 0 AND {$alias}.date_echeance IS NOT NULL AND {$alias}.date_echeance < CURDATE()";
                break;
        }

        return [implode(' AND ', $where), $params];
    }

    private function facturesClientsKpis(array $filters): array
    {
        $stats = [
            'nb_factures' => 0,
            'nb_non_reglees' => 0,
            'nb_partielles' => 0,
            'nb_reglees' => 0,
            'nb_retard' => 0,
            'total_ttc' => 0.0,
            'total_regle' => 0.0,
            'reste_a_encaisser' => 0.0,
            'retard_ttc' => 0.0,
            'encaisse_mois' => 0.0,
        ];
        if (!$this->tableExists('factures')) {
            return $stats;
        }

        [$where, $params] = $this->facturesClientsWhere($filters, 'f');
        $row = $this->fetchPreparedOne("\n            SELECT\n                COUNT(*) AS nb_factures,\n                SUM(CASE WHEN COALESCE(f.montant_regle, 0) <= 0 AND COALESCE(f.reste_du, f.total_ttc) > 0 THEN 1 ELSE 0 END) AS nb_non_reglees,\n                SUM(CASE WHEN COALESCE(f.montant_regle, 0) > 0 AND COALESCE(f.reste_du, 0) > 0 THEN 1 ELSE 0 END) AS nb_partielles,\n                SUM(CASE WHEN COALESCE(f.reste_du, 0) <= 0 THEN 1 ELSE 0 END) AS nb_reglees,\n                SUM(CASE WHEN COALESCE(f.reste_du, 0) > 0 AND f.date_echeance IS NOT NULL AND f.date_echeance < CURDATE() THEN 1 ELSE 0 END) AS nb_retard,\n                COALESCE(SUM(f.total_ttc), 0) AS total_ttc,\n                COALESCE(SUM(f.montant_regle), 0) AS total_regle,\n                COALESCE(SUM(f.reste_du), 0) AS reste_a_encaisser,\n                COALESCE(SUM(CASE WHEN COALESCE(f.reste_du, 0) > 0 AND f.date_echeance IS NOT NULL AND f.date_echeance < CURDATE() THEN f.reste_du ELSE 0 END), 0) AS retard_ttc\n            FROM factures f\n            WHERE {$where}\n        ", $params);

        foreach (['nb_factures','nb_non_reglees','nb_partielles','nb_reglees','nb_retard'] as $key) {
            $stats[$key] = (int)($row[$key] ?? 0);
        }
        foreach (['total_ttc','total_regle','reste_a_encaisser','retard_ttc'] as $key) {
            $stats[$key] = (float)($row[$key] ?? 0);
        }

        if ($this->tableExists('encaissements')) {
            $stats['encaisse_mois'] = (float)$this->scalar("\n                SELECT COALESCE(SUM(montant), 0)\n                FROM encaissements\n                WHERE actif = 1 AND date_encaissement >= DATE_FORMAT(CURDATE(), '%Y-%m-01')\n            ");
        }

        return $stats;
    }

    private function facturesClientsList(array $filters): array
    {
        if (!$this->tableExists('factures')) {
            return [];
        }
        [$where, $params] = $this->facturesClientsWhere($filters, 'f');
        $join = $this->tableExists('clients') ? 'LEFT JOIN clients c ON c.id = f.client_id' : '';
        return $this->fetchPreparedAll("\n            SELECT f.id, f.numero, f.date_facture, f.date_echeance, f.statut, f.total_ttc, f.montant_regle, f.reste_du,\n                   COALESCE(c.raison_sociale, CONCAT('Client #', f.client_id)) AS client_nom,\n                   CASE\n                       WHEN COALESCE(f.reste_du, 0) <= 0 THEN 'Réglée'\n                       WHEN COALESCE(f.montant_regle, 0) > 0 THEN 'Partielle'\n                       ELSE 'Non réglée'\n                   END AS statut_reglement,\n                   CASE WHEN COALESCE(f.reste_du, 0) > 0 AND f.date_echeance IS NOT NULL AND f.date_echeance < CURDATE() THEN 1 ELSE 0 END AS en_retard,\n                   CASE WHEN f.date_echeance IS NULL THEN NULL ELSE DATEDIFF(CURDATE(), f.date_echeance) END AS jours_retard\n            FROM factures f\n            {$join}\n            WHERE {$where}\n            ORDER BY en_retard DESC, CASE WHEN f.date_echeance IS NULL THEN 1 ELSE 0 END, f.date_echeance ASC, f.date_facture DESC, f.id DESC\n            LIMIT 150\n        ", $params);
    }

    private function facturesClientsRetard(array $filters): array
    {
        if (!$this->tableExists('factures')) {
            return [];
        }
        $retardFilters = $filters;
        $retardFilters['statut'] = 'en_retard';
        [$where, $params] = $this->facturesClientsWhere($retardFilters, 'f');
        $join = $this->tableExists('clients') ? 'LEFT JOIN clients c ON c.id = f.client_id' : '';
        return $this->fetchPreparedAll("\n            SELECT f.id, f.numero, f.date_facture, f.date_echeance, f.total_ttc, f.montant_regle, f.reste_du,\n                   COALESCE(c.raison_sociale, CONCAT('Client #', f.client_id)) AS client_nom,\n                   DATEDIFF(CURDATE(), f.date_echeance) AS jours_retard\n            FROM factures f\n            {$join}\n            WHERE {$where}\n            ORDER BY f.date_echeance ASC, f.id ASC\n            LIMIT 20\n        ", $params);
    }

    private function topClientsDus(array $filters): array
    {
        if (!$this->tableExists('factures')) {
            return [];
        }
        [$where, $params] = $this->facturesClientsWhere($filters, 'f');
        $join = $this->tableExists('clients') ? 'LEFT JOIN clients c ON c.id = f.client_id' : '';
        return $this->fetchPreparedAll("\n            SELECT f.client_id, COALESCE(c.raison_sociale, CONCAT('Client #', f.client_id)) AS client_nom,\n                   COUNT(*) AS nb_factures, COALESCE(SUM(f.reste_du), 0) AS reste_du, COALESCE(SUM(f.total_ttc), 0) AS total_ttc\n            FROM factures f\n            {$join}\n            WHERE {$where} AND COALESCE(f.reste_du, 0) > 0\n            GROUP BY f.client_id, client_nom\n            ORDER BY reste_du DESC\n            LIMIT 10\n        ", $params);
    }



    public function articlesSalesAnalysis(array $filters = []): array
    {
        $tri = trim((string)($filters['tri'] ?? 'ca'));
        if (!in_array($tri, ['ca', 'quantite', 'marge', 'rotation'], true)) {
            $tri = 'ca';
        }

        $dateFrom = trim((string)($filters['date_from'] ?? ''));
        $dateTo = trim((string)($filters['date_to'] ?? ''));
        if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $dateFrom)) {
            $dateFrom = '';
        }
        if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $dateTo)) {
            $dateTo = '';
        }

        $filters = [
            'article_id' => max(0, (int)($filters['article_id'] ?? 0)),
            'date_from' => $dateFrom,
            'date_to' => $dateTo,
            'tri' => $tri,
        ];

        return [
            'filters' => $filters,
            'kpis' => $this->articlesSalesKpis($filters),
            'articlesVendus' => $this->articlesSalesList($filters),
            'articlesPeuVendus' => $this->articlesPeuVendus($filters),
            'articlesSansVente' => $this->articlesSansVente($filters),
            'articles' => $this->articlesForFilter(),
            'version' => 'Phoenix P3.6.4.4',
        ];
    }

    private function articlesSalesWhere(array $filters, string $factureAlias = 'f', string $lineAlias = 'l'): array
    {
        $where = ["{$factureAlias}.actif = 1"];
        $params = [];

        $articleId = (int)($filters['article_id'] ?? 0);
        if ($articleId > 0) {
            $article = $this->articleLookup($articleId);
            $articleConditions = ["{$lineAlias}.article_id = :article_id"];
            $params['article_id'] = $articleId;

            // Certaines anciennes lignes de facture ont été enregistrées avec une référence copiée,
            // mais sans article_id. On filtre donc aussi par référence/designation quand elles existent.
            if (!empty($article['reference'])) {
                $articleConditions[] = "{$lineAlias}.reference = :article_reference";
                $params['article_reference'] = (string)$article['reference'];
            }
            if (!empty($article['designation'])) {
                $articleConditions[] = "{$lineAlias}.designation = :article_designation";
                $params['article_designation'] = (string)$article['designation'];
            }
            $where[] = '(' . implode(' OR ', $articleConditions) . ')';
        }

        $dateColumn = $this->invoiceDateExpression($factureAlias);
        if (($filters['date_from'] ?? '') !== '') {
            $where[] = "{$dateColumn} >= :date_from";
            $params['date_from'] = $filters['date_from'];
        }
        if (($filters['date_to'] ?? '') !== '') {
            $where[] = "{$dateColumn} <= :date_to";
            $params['date_to'] = $filters['date_to'];
        }

        return [implode(' AND ', $where), $params];
    }

    private function articlesSalesKpis(array $filters): array
    {
        $stats = [
            'nb_articles_vendus' => 0,
            'quantite_vendue' => 0.0,
            'ca_ht' => 0.0,
            'ca_ttc' => 0.0,
            'marge_ht' => 0.0,
            'marge_pourcentage' => 0.0,
            'nb_lignes' => 0,
        ];
        if (!$this->tableExists('factures') || !$this->tableExists('factures_lignes')) {
            return $stats;
        }
        [$where, $params] = $this->articlesSalesWhere($filters, 'f', 'l');
        $row = $this->fetchPreparedOne("
            SELECT
                COUNT(DISTINCT COALESCE(l.article_id, l.reference, CONCAT('ligne_', l.id))) AS nb_articles_vendus,
                COUNT(*) AS nb_lignes,
                COALESCE(SUM(l.quantite), 0) AS quantite_vendue,
                COALESCE(SUM(l.total_ht), 0) AS ca_ht,
                COALESCE(SUM(l.total_ttc), 0) AS ca_ttc,
                COALESCE(SUM(l.marge_ht), 0) AS marge_ht
            FROM factures_lignes l
            INNER JOIN factures f ON f.id = l.facture_id
            WHERE {$where}
        ", $params);

        foreach (['nb_articles_vendus','nb_lignes'] as $key) {
            $stats[$key] = (int)($row[$key] ?? 0);
        }
        foreach (['quantite_vendue','ca_ht','ca_ttc','marge_ht'] as $key) {
            $stats[$key] = (float)($row[$key] ?? 0);
        }
        $stats['marge_pourcentage'] = $stats['ca_ht'] > 0 ? ($stats['marge_ht'] / $stats['ca_ht']) * 100 : 0.0;
        return $stats;
    }

    private function articlesSalesList(array $filters): array
    {
        if (!$this->tableExists('factures') || !$this->tableExists('factures_lignes')) {
            return [];
        }
        [$where, $params] = $this->articlesSalesWhere($filters, 'f', 'l');
        $joinArticle = $this->tableExists('articles')
            ? "LEFT JOIN articles a ON (a.id = l.article_id OR (l.article_id IS NULL AND a.reference = l.reference))"
            : '';
        $joinStock = $this->tableExists('stocks')
            ? 'LEFT JOIN stocks s ON s.article_id = COALESCE(l.article_id, a.id)'
            : '';
        $stockExpr = $this->tableExists('stocks')
            ? 'COALESCE(s.quantite, 0)'
            : ($this->columnExists('articles', 'stock') ? 'COALESCE(a.stock, 0)' : '0');
        $orderBy = match ((string)($filters['tri'] ?? 'ca')) {
            'quantite' => 'quantite_vendue DESC, ca_ht DESC',
            'marge' => 'marge_ht DESC, ca_ht DESC',
            'rotation' => 'nb_ventes DESC, ca_ht DESC',
            default => 'ca_ht DESC, quantite_vendue DESC',
        };

        return $this->fetchPreparedAll("
            SELECT
                COALESCE(l.article_id, a.id, 0) AS article_id,
                COALESCE(a.reference, l.reference, '') AS reference,
                COALESCE(a.designation, l.designation, '') AS designation,
                {$stockExpr} AS stock_actuel,
                COALESCE(SUM(l.quantite), 0) AS quantite_vendue,
                COUNT(*) AS nb_ventes,
                COALESCE(SUM(l.total_ht), 0) AS ca_ht,
                COALESCE(SUM(l.total_ttc), 0) AS ca_ttc,
                COALESCE(SUM(l.marge_ht), 0) AS marge_ht,
                CASE WHEN COALESCE(SUM(l.total_ht), 0) > 0 THEN (COALESCE(SUM(l.marge_ht), 0) / COALESCE(SUM(l.total_ht), 0)) * 100 ELSE 0 END AS marge_pourcentage,
                MAX(" . $this->invoiceDateExpression('f') . ") AS derniere_vente
            FROM factures_lignes l
            INNER JOIN factures f ON f.id = l.facture_id
            {$joinArticle}
            {$joinStock}
            WHERE {$where}
            GROUP BY COALESCE(l.article_id, a.id, 0), COALESCE(a.reference, l.reference, ''), COALESCE(a.designation, l.designation, ''), {$stockExpr}
            ORDER BY {$orderBy}
            LIMIT 150
        ", $params);
    }

    private function articlesPeuVendus(array $filters): array
    {
        $all = $this->articlesSalesList($filters);
        usort($all, static function (array $a, array $b): int {
            $qa = (float)($a['quantite_vendue'] ?? 0);
            $qb = (float)($b['quantite_vendue'] ?? 0);
            if ($qa === $qb) {
                return ((float)($a['ca_ht'] ?? 0)) <=> ((float)($b['ca_ht'] ?? 0));
            }
            return $qa <=> $qb;
        });
        return array_slice($all, 0, 20);
    }

    private function articlesSansVente(array $filters): array
    {
        if (!$this->tableExists('articles')) {
            return [];
        }
        $dateSql = '';
        $params = [];
        $dateColumn = $this->invoiceDateExpression('f');
        if (($filters['date_from'] ?? '') !== '') {
            $dateSql .= " AND {$dateColumn} >= :date_from";
            $params['date_from'] = $filters['date_from'];
        }
        if (($filters['date_to'] ?? '') !== '') {
            $dateSql .= " AND {$dateColumn} <= :date_to";
            $params['date_to'] = $filters['date_to'];
        }
        if ((int)($filters['article_id'] ?? 0) > 0) {
            $params['article_id'] = (int)$filters['article_id'];
            $extra = ' AND a.id = :article_id';
        } else {
            $extra = '';
        }

        $joinStock = $this->tableExists('stocks') ? 'LEFT JOIN stocks s ON s.article_id = a.id' : '';
        $stockExpr = $this->tableExists('stocks')
            ? 'COALESCE(s.quantite, 0)'
            : ($this->columnExists('articles', 'stock') ? 'COALESCE(a.stock, 0)' : '0');

        if (!$this->tableExists('factures') || !$this->tableExists('factures_lignes')) {
            return $this->fetchPreparedAll("
                SELECT a.id, a.reference, a.designation, {$stockExpr} AS stock_actuel, COALESCE(a.prix_vente_ht, 0) AS prix_vente_ht
                FROM articles a
                {$joinStock}
                WHERE a.actif = 1 {$extra}
                ORDER BY {$stockExpr} DESC, a.designation ASC
                LIMIT 50
            ", $params);
        }

        return $this->fetchPreparedAll("
            SELECT a.id, a.reference, a.designation, {$stockExpr} AS stock_actuel, COALESCE(a.prix_vente_ht, 0) AS prix_vente_ht
            FROM articles a
            {$joinStock}
            WHERE a.actif = 1 {$extra}
              AND NOT EXISTS (
                  SELECT 1
                  FROM factures_lignes l
                  INNER JOIN factures f ON f.id = l.facture_id AND f.actif = 1 {$dateSql}
                  WHERE l.article_id = a.id OR (l.article_id IS NULL AND l.reference = a.reference)
              )
            ORDER BY {$stockExpr} DESC, a.designation ASC
            LIMIT 50
        ", $params);
    }

    private function invoiceDateExpression(string $alias): string
    {
        if ($this->columnExists('factures', 'date_facture')) {
            return "DATE({$alias}.date_facture)";
        }
        if ($this->columnExists('factures', 'date_document')) {
            return "DATE({$alias}.date_document)";
        }
        if ($this->columnExists('factures', 'date_creation')) {
            return "DATE({$alias}.date_creation)";
        }
        return 'CURDATE()';
    }

    private function articleLookup(int $articleId): array
    {
        if ($articleId <= 0 || !$this->tableExists('articles')) {
            return [];
        }
        return $this->fetchPreparedOne('
            SELECT id, reference, designation
            FROM articles
            WHERE id = :article_id
            LIMIT 1
        ', ['article_id' => $articleId]);
    }

    private function articlesForFilter(): array
    {
        if (!$this->tableExists('articles')) {
            return [];
        }
        return $this->fetchAll("
            SELECT id, reference, designation
            FROM articles
            WHERE actif = 1
            ORDER BY designation ASC, reference ASC
            LIMIT 1000
        ");
    }



    public function clientsAnalysis(array $filters = []): array
    {
        $tri = trim((string)($filters['tri'] ?? 'ca'));
        if (!in_array($tri, ['ca', 'reste', 'marge', 'documents', 'factures', 'devis', 'panier', 'dernier'], true)) {
            $tri = 'ca';
        }

        $documentType = trim((string)($filters['document_type'] ?? 'tous'));
        if (!in_array($documentType, ['tous', 'factures', 'devis'], true)) {
            $documentType = 'tous';
        }

        $clientStatus = trim((string)($filters['client_status'] ?? ''));
        if (!in_array($clientStatus, ['', 'tres_actif', 'actif', 'peu_actif', 'inactif'], true)) {
            $clientStatus = '';
        }

        $dateFrom = trim((string)($filters['date_from'] ?? ''));
        $dateTo = trim((string)($filters['date_to'] ?? ''));
        if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $dateFrom)) {
            $dateFrom = '';
        }
        if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $dateTo)) {
            $dateTo = '';
        }

        $amountMin = trim((string)($filters['amount_min'] ?? ''));
        $amountMax = trim((string)($filters['amount_max'] ?? ''));
        $amountMin = $amountMin === '' ? '' : str_replace(',', '.', $amountMin);
        $amountMax = $amountMax === '' ? '' : str_replace(',', '.', $amountMax);
        if ($amountMin !== '' && !is_numeric($amountMin)) {
            $amountMin = '';
        }
        if ($amountMax !== '' && !is_numeric($amountMax)) {
            $amountMax = '';
        }

        $filters = [
            'client_id' => max(0, (int)($filters['client_id'] ?? 0)),
            'date_from' => $dateFrom,
            'date_to' => $dateTo,
            'document_type' => $documentType,
            'client_status' => $clientStatus,
            'amount_min' => $amountMin,
            'amount_max' => $amountMax,
            'tri' => $tri,
        ];

        $clientsPerformance = $this->clientsPerformanceList($filters);

        return [
            'filters' => $filters,
            'kpis' => $this->clientsAnalysisKpis($filters, $clientsPerformance),
            'clientsPerformance' => $clientsPerformance,
            'topClients' => array_slice($clientsPerformance, 0, 10),
            'clientsDus' => $this->clientsWithDebt($filters),
            'clientsInactifs' => $this->clientsInactive($filters),
            'recentEncaissements' => $this->recentEncaissements(),
            'clients' => $this->clientsForFilter(),
            'version' => 'Phoenix P3.6.5',
        ];
    }

    private function commercialDocumentUnion(array $filters): array
    {
        $parts = [];
        $params = [];
        $type = (string)($filters['document_type'] ?? 'tous');

        if (($type === 'tous' || $type === 'factures') && $this->tableExists('factures')) {
            $where = ['f.actif = 1'];
            if ((int)($filters['client_id'] ?? 0) > 0) {
                $where[] = 'f.client_id = :factures_client_id';
                $params['factures_client_id'] = (int)$filters['client_id'];
            }
            if (($filters['date_from'] ?? '') !== '') {
                $where[] = 'f.date_facture >= :factures_date_from';
                $params['factures_date_from'] = $filters['date_from'];
            }
            if (($filters['date_to'] ?? '') !== '') {
                $where[] = 'f.date_facture <= :factures_date_to';
                $params['factures_date_to'] = $filters['date_to'];
            }
            $parts[] = "
                SELECT
                    f.client_id,
                    'facture' AS document_type,
                    f.id AS document_id,
                    f.date_facture AS date_document,
                    COALESCE(f.total_ht, 0) AS total_ht,
                    COALESCE(f.total_ttc, 0) AS total_ttc,
                    COALESCE(f.marge_ht, 0) AS marge_ht,
                    COALESCE(f.reste_du, 0) AS reste_du
                FROM factures f
                WHERE " . implode(' AND ', $where);
        }

        if (($type === 'tous' || $type === 'devis') && $this->tableExists('devis')) {
            $where = ['d.actif = 1'];
            if ((int)($filters['client_id'] ?? 0) > 0) {
                $where[] = 'd.client_id = :devis_client_id';
                $params['devis_client_id'] = (int)$filters['client_id'];
            }
            if (($filters['date_from'] ?? '') !== '') {
                $where[] = 'd.date_devis >= :devis_date_from';
                $params['devis_date_from'] = $filters['date_from'];
            }
            if (($filters['date_to'] ?? '') !== '') {
                $where[] = 'd.date_devis <= :devis_date_to';
                $params['devis_date_to'] = $filters['date_to'];
            }
            $parts[] = "
                SELECT
                    d.client_id,
                    'devis' AS document_type,
                    d.id AS document_id,
                    d.date_devis AS date_document,
                    COALESCE(d.total_ht, 0) AS total_ht,
                    COALESCE(d.total_ttc, 0) AS total_ttc,
                    COALESCE(d.marge_ht, 0) AS marge_ht,
                    0 AS reste_du
                FROM devis d
                WHERE " . implode(' AND ', $where);
        }

        if (empty($parts)) {
            return ['', []];
        }

        return [implode("\nUNION ALL\n", $parts), $params];
    }

    private function clientsAnalysisKpis(array $filters, array $clientsPerformance): array
    {
        $stats = [
            'nb_clients_total' => 0,
            'nb_clients_actifs' => count($clientsPerformance),
            'nb_factures' => 0,
            'nb_devis' => 0,
            'nb_documents' => 0,
            'ca_ht' => 0.0,
            'ca_ttc' => 0.0,
            'ca_factures_ttc' => 0.0,
            'ca_devis_ttc' => 0.0,
            'marge_ht' => 0.0,
            'marge_pourcentage' => 0.0,
            'reste_du' => 0.0,
            'panier_moyen_ttc' => 0.0,
            'panier_moyen_client_ttc' => 0.0,
        ];

        if ($this->tableExists('clients')) {
            $stats['nb_clients_total'] = (int)$this->scalar('SELECT COUNT(*) FROM clients WHERE actif = 1');
        }

        foreach ($clientsPerformance as $row) {
            $stats['nb_factures'] += (int)($row['nb_factures'] ?? 0);
            $stats['nb_devis'] += (int)($row['nb_devis'] ?? 0);
            $stats['nb_documents'] += (int)($row['nb_documents'] ?? 0);
            $stats['ca_ht'] += (float)($row['ca_ht'] ?? 0);
            $stats['ca_ttc'] += (float)($row['ca_ttc'] ?? 0);
            $stats['ca_factures_ttc'] += (float)($row['ca_factures_ttc'] ?? 0);
            $stats['ca_devis_ttc'] += (float)($row['ca_devis_ttc'] ?? 0);
            $stats['marge_ht'] += (float)($row['marge_ht'] ?? 0);
            $stats['reste_du'] += (float)($row['reste_du'] ?? 0);
        }

        $stats['marge_pourcentage'] = $stats['ca_ht'] > 0 ? ($stats['marge_ht'] / $stats['ca_ht']) * 100 : 0.0;
        $stats['panier_moyen_ttc'] = $stats['nb_documents'] > 0 ? $stats['ca_ttc'] / $stats['nb_documents'] : 0.0;
        $stats['panier_moyen_client_ttc'] = $stats['nb_clients_actifs'] > 0 ? $stats['ca_ttc'] / $stats['nb_clients_actifs'] : 0.0;

        return $stats;
    }

    private function clientsPerformanceList(array $filters): array
    {
        [$unionSql, $params] = $this->commercialDocumentUnion($filters);
        if ($unionSql === '') {
            return [];
        }

        $join = $this->tableExists('clients') ? 'LEFT JOIN clients c ON c.id = x.client_id' : '';
        $rows = $this->fetchPreparedAll("
            SELECT
                x.client_id,
                COALESCE(c.raison_sociale, CONCAT('Client #', x.client_id)) AS client_nom,
                SUM(CASE WHEN x.document_type = 'facture' THEN 1 ELSE 0 END) AS nb_factures,
                SUM(CASE WHEN x.document_type = 'devis' THEN 1 ELSE 0 END) AS nb_devis,
                COUNT(*) AS nb_documents,
                COALESCE(SUM(x.total_ht), 0) AS ca_ht,
                COALESCE(SUM(x.total_ttc), 0) AS ca_ttc,
                COALESCE(SUM(CASE WHEN x.document_type = 'facture' THEN x.total_ttc ELSE 0 END), 0) AS ca_factures_ttc,
                COALESCE(SUM(CASE WHEN x.document_type = 'devis' THEN x.total_ttc ELSE 0 END), 0) AS ca_devis_ttc,
                COALESCE(SUM(x.marge_ht), 0) AS marge_ht,
                COALESCE(SUM(x.reste_du), 0) AS reste_du,
                CASE WHEN COALESCE(SUM(x.total_ht), 0) > 0 THEN (COALESCE(SUM(x.marge_ht), 0) / COALESCE(SUM(x.total_ht), 0)) * 100 ELSE 0 END AS marge_pourcentage,
                CASE WHEN COUNT(*) > 0 THEN COALESCE(SUM(x.total_ttc), 0) / COUNT(*) ELSE 0 END AS panier_moyen_ttc,
                MAX(x.date_document) AS derniere_activite
            FROM ({$unionSql}) x
            {$join}
            GROUP BY x.client_id, client_nom
        ", $params);

        $totalCa = 0.0;
        foreach ($rows as $row) {
            $totalCa += (float)($row['ca_ttc'] ?? 0);
        }

        $amountMin = ($filters['amount_min'] ?? '') === '' ? null : (float)$filters['amount_min'];
        $amountMax = ($filters['amount_max'] ?? '') === '' ? null : (float)$filters['amount_max'];
        $clientStatus = (string)($filters['client_status'] ?? '');

        $rows = array_values(array_filter(array_map(function (array $row) use ($totalCa): array {
            $row['nb_factures'] = (int)($row['nb_factures'] ?? 0);
            $row['nb_devis'] = (int)($row['nb_devis'] ?? 0);
            $row['nb_documents'] = (int)($row['nb_documents'] ?? 0);
            foreach (['ca_ht','ca_ttc','ca_factures_ttc','ca_devis_ttc','marge_ht','reste_du','marge_pourcentage','panier_moyen_ttc'] as $key) {
                $row[$key] = (float)($row[$key] ?? 0);
            }
            $row['part_ca'] = $totalCa > 0 ? ($row['ca_ttc'] / $totalCa) * 100 : 0.0;
            $row['client_status'] = $this->clientPerformanceStatus($row);
            $row['client_status_label'] = $this->clientPerformanceStatusLabel($row['client_status']);
            return $row;
        }, $rows), function (array $row) use ($amountMin, $amountMax, $clientStatus): bool {
            if ($amountMin !== null && (float)$row['ca_ttc'] < $amountMin) {
                return false;
            }
            if ($amountMax !== null && (float)$row['ca_ttc'] > $amountMax) {
                return false;
            }
            if ($clientStatus !== '' && (string)$row['client_status'] !== $clientStatus) {
                return false;
            }
            return true;
        }));

        $tri = (string)($filters['tri'] ?? 'ca');
        usort($rows, static function (array $a, array $b) use ($tri): int {
            return match ($tri) {
                'reste' => [(float)$b['reste_du'], (float)$b['ca_ttc']] <=> [(float)$a['reste_du'], (float)$a['ca_ttc']],
                'marge' => [(float)$b['marge_ht'], (float)$b['ca_ttc']] <=> [(float)$a['marge_ht'], (float)$a['ca_ttc']],
                'documents' => [(int)$b['nb_documents'], (float)$b['ca_ttc']] <=> [(int)$a['nb_documents'], (float)$a['ca_ttc']],
                'factures' => [(int)$b['nb_factures'], (float)$b['ca_ttc']] <=> [(int)$a['nb_factures'], (float)$a['ca_ttc']],
                'devis' => [(int)$b['nb_devis'], (float)$b['ca_ttc']] <=> [(int)$a['nb_devis'], (float)$a['ca_ttc']],
                'panier' => [(float)$b['panier_moyen_ttc'], (float)$b['ca_ttc']] <=> [(float)$a['panier_moyen_ttc'], (float)$a['ca_ttc']],
                'dernier' => strcmp((string)($b['derniere_activite'] ?? ''), (string)($a['derniere_activite'] ?? '')),
                default => [(float)$b['ca_ttc'], (int)$b['nb_documents']] <=> [(float)$a['ca_ttc'], (int)$a['nb_documents']],
            };
        });

        $filteredTotalCa = array_sum(array_map(static fn (array $row): float => (float)$row['ca_ttc'], $rows));
        foreach ($rows as &$row) {
            $row['part_ca'] = $filteredTotalCa > 0 ? ((float)$row['ca_ttc'] / $filteredTotalCa) * 100 : 0.0;
        }
        unset($row);

        return array_slice($rows, 0, 300);
    }

    private function clientPerformanceStatus(array $row): string
    {
        $documents = (int)($row['nb_documents'] ?? 0);
        $ca = (float)($row['ca_ttc'] ?? 0);
        $last = (string)($row['derniere_activite'] ?? '');
        $daysSinceLast = 9999;
        if ($last !== '') {
            $timestamp = strtotime($last);
            if ($timestamp !== false) {
                $daysSinceLast = (int)floor((time() - $timestamp) / 86400);
            }
        }

        if ($daysSinceLast > 180) {
            return 'inactif';
        }
        if ($documents >= 5 || $ca >= 5000) {
            return 'tres_actif';
        }
        if ($documents >= 2 || $ca >= 1000) {
            return 'actif';
        }
        return 'peu_actif';
    }

    private function clientPerformanceStatusLabel(string $status): string
    {
        return match ($status) {
            'tres_actif' => 'Très actif',
            'actif' => 'Actif',
            'peu_actif' => 'Peu actif',
            'inactif' => 'Inactif',
            default => 'À qualifier',
        };
    }

    private function clientsWithDebt(array $filters): array
    {
        if (($filters['document_type'] ?? 'tous') === 'devis' || !$this->tableExists('factures')) {
            return [];
        }

        $where = ['f.actif = 1', 'COALESCE(f.reste_du, 0) > 0'];
        $params = [];
        if ((int)($filters['client_id'] ?? 0) > 0) {
            $where[] = 'f.client_id = :client_id';
            $params['client_id'] = (int)$filters['client_id'];
        }
        if (($filters['date_from'] ?? '') !== '') {
            $where[] = 'f.date_facture >= :date_from';
            $params['date_from'] = $filters['date_from'];
        }
        if (($filters['date_to'] ?? '') !== '') {
            $where[] = 'f.date_facture <= :date_to';
            $params['date_to'] = $filters['date_to'];
        }

        $join = $this->tableExists('clients') ? 'LEFT JOIN clients c ON c.id = f.client_id' : '';
        return $this->fetchPreparedAll("
            SELECT
                f.client_id,
                COALESCE(c.raison_sociale, CONCAT('Client #', f.client_id)) AS client_nom,
                COUNT(*) AS nb_factures,
                COALESCE(SUM(f.reste_du), 0) AS reste_du,
                COALESCE(SUM(CASE WHEN f.reste_du > 0 AND f.date_echeance IS NOT NULL AND f.date_echeance < CURDATE() THEN f.reste_du ELSE 0 END), 0) AS retard_ttc,
                MIN(CASE WHEN f.reste_du > 0 THEN f.date_echeance ELSE NULL END) AS prochaine_echeance
            FROM factures f
            {$join}
            WHERE " . implode(' AND ', $where) . "
            GROUP BY f.client_id, client_nom
            ORDER BY reste_du DESC
            LIMIT 25
        ", $params);
    }

    private function clientsInactive(array $filters): array
    {
        if (!$this->tableExists('clients')) {
            return [];
        }
        $params = [];
        $clientFilter = '';
        if ((int)($filters['client_id'] ?? 0) > 0) {
            $clientFilter = ' AND c.id = :client_id';
            $params['client_id'] = (int)$filters['client_id'];
        }

        $lastFactureExpr = $this->tableExists('factures')
            ? '(SELECT MAX(f.date_facture) FROM factures f WHERE f.client_id = c.id AND f.actif = 1)'
            : 'NULL';
        $lastDevisExpr = $this->tableExists('devis')
            ? '(SELECT MAX(d.date_devis) FROM devis d WHERE d.client_id = c.id AND d.actif = 1)'
            : 'NULL';
        $caExpr = $this->tableExists('factures')
            ? '(SELECT COALESCE(SUM(f.total_ttc), 0) FROM factures f WHERE f.client_id = c.id AND f.actif = 1)'
            : '0';
        $devisCountExpr = $this->tableExists('devis')
            ? '(SELECT COUNT(*) FROM devis d WHERE d.client_id = c.id AND d.actif = 1)'
            : '0';

        $rows = $this->fetchPreparedAll("
            SELECT
                c.id AS client_id,
                c.raison_sociale AS client_nom,
                {$lastFactureExpr} AS derniere_facture,
                {$lastDevisExpr} AS dernier_devis,
                {$caExpr} AS ca_ttc,
                {$devisCountExpr} AS nb_devis
            FROM clients c
            WHERE c.actif = 1 {$clientFilter}
            HAVING derniere_facture IS NULL OR derniere_facture < DATE_SUB(CURDATE(), INTERVAL 180 DAY)
            ORDER BY CASE WHEN derniere_facture IS NULL THEN 1 ELSE 0 END DESC, derniere_facture ASC, c.raison_sociale ASC
            LIMIT 60
        ", $params);

        foreach ($rows as &$row) {
            $row['action_conseillee'] = 'Relance commerciale';
            if (empty($row['derniere_facture']) && (int)($row['nb_devis'] ?? 0) > 0) {
                $row['action_conseillee'] = 'Vérifier devis non transformé';
            } elseif (empty($row['derniere_facture'])) {
                $row['action_conseillee'] = 'Premier contact à prévoir';
            } elseif ((float)($row['ca_ttc'] ?? 0) > 0) {
                $row['action_conseillee'] = 'Envoyer une offre de réactivation';
            }
        }
        unset($row);

        return $rows;
    }


    public function commercialActions(array $filters = []): array
    {
        $priority = trim((string)($filters['priority'] ?? ''));
        if (!in_array($priority, ['', 'haute', 'moyenne', 'basse'], true)) {
            $priority = '';
        }

        $actionType = trim((string)($filters['action_type'] ?? ''));
        if (!in_array($actionType, ['', 'impaye', 'devis', 'reactivation', 'premier_contact'], true)) {
            $actionType = '';
        }

        $daysInactive = max(30, (int)($filters['days_inactive'] ?? 180));
        if ($daysInactive > 730) {
            $daysInactive = 730;
        }

        $filters = [
            'client_id' => max(0, (int)($filters['client_id'] ?? 0)),
            'priority' => $priority,
            'action_type' => $actionType,
            'days_inactive' => $daysInactive,
            'with_contact_only' => !empty($filters['with_contact_only']) ? 1 : 0,
        ];

        $actions = $this->commercialActionList($filters);

        return [
            'filters' => $filters,
            'kpis' => $this->commercialActionKpis($actions),
            'actions' => $actions,
            'clients' => $this->clientsForFilter(),
            'version' => 'Phoenix P3.6.6',
        ];
    }

    private function commercialActionList(array $filters): array
    {
        if (!$this->tableExists('clients')) {
            return [];
        }

        $params = ['days_inactive' => (int)$filters['days_inactive']];
        $where = ['c.actif = 1'];

        if ((int)($filters['client_id'] ?? 0) > 0) {
            $where[] = 'c.id = :client_id';
            $params['client_id'] = (int)$filters['client_id'];
        }

        if ((int)($filters['with_contact_only'] ?? 0) === 1) {
            $where[] = "(COALESCE(c.email, '') <> '' OR COALESCE(c.telephone, '') <> '')";
        }

        $lastFactureExpr = $this->tableExists('factures')
            ? '(SELECT MAX(f.date_facture) FROM factures f WHERE f.client_id = c.id AND f.actif = 1)'
            : 'NULL';
        $lastDevisExpr = $this->tableExists('devis')
            ? '(SELECT MAX(d.date_devis) FROM devis d WHERE d.client_id = c.id AND d.actif = 1)'
            : 'NULL';
        $caExpr = $this->tableExists('factures')
            ? '(SELECT COALESCE(SUM(f.total_ttc), 0) FROM factures f WHERE f.client_id = c.id AND f.actif = 1)'
            : '0';
        $resteExpr = $this->tableExists('factures')
            ? '(SELECT COALESCE(SUM(f.reste_du), 0) FROM factures f WHERE f.client_id = c.id AND f.actif = 1)'
            : '0';
        $retardExpr = $this->tableExists('factures')
            ? '(SELECT COALESCE(SUM(f.reste_du), 0) FROM factures f WHERE f.client_id = c.id AND f.actif = 1 AND f.reste_du > 0 AND f.date_echeance IS NOT NULL AND f.date_echeance < CURDATE())'
            : '0';
        $openDevisExpr = $this->tableExists('devis')
            ? "(SELECT COUNT(*) FROM devis d WHERE d.client_id = c.id AND d.actif = 1 AND LOWER(COALESCE(d.statut, '')) NOT IN ('accepté','accepte','transformé','transforme','refusé','refuse','annulé','annule'))"
            : '0';
        $openDevisAmountExpr = $this->tableExists('devis')
            ? "(SELECT COALESCE(SUM(d.total_ttc), 0) FROM devis d WHERE d.client_id = c.id AND d.actif = 1 AND LOWER(COALESCE(d.statut, '')) NOT IN ('accepté','accepte','transformé','transforme','refusé','refuse','annulé','annule'))"
            : '0';

        $rows = $this->fetchPreparedAll("
            SELECT
                c.id AS client_id,
                c.raison_sociale AS client_nom,
                c.email,
                c.telephone,
                {$lastFactureExpr} AS derniere_facture,
                {$lastDevisExpr} AS dernier_devis,
                {$caExpr} AS ca_ttc,
                {$resteExpr} AS reste_du,
                {$retardExpr} AS retard_ttc,
                {$openDevisExpr} AS nb_devis_ouverts,
                {$openDevisAmountExpr} AS montant_devis_ouverts,
                GREATEST(COALESCE({$lastFactureExpr}, '1900-01-01'), COALESCE({$lastDevisExpr}, '1900-01-01')) AS derniere_activite
            FROM clients c
            WHERE " . implode(' AND ', $where) . "
            HAVING retard_ttc > 0
                OR nb_devis_ouverts > 0
                OR derniere_facture IS NULL
                OR derniere_facture < DATE_SUB(CURDATE(), INTERVAL :days_inactive DAY)
            ORDER BY retard_ttc DESC, montant_devis_ouverts DESC, derniere_activite ASC, client_nom ASC
            LIMIT 250
        ", $params);

        $rows = array_values(array_map(function (array $row): array {
            foreach (['ca_ttc', 'reste_du', 'retard_ttc', 'montant_devis_ouverts'] as $key) {
                $row[$key] = (float)($row[$key] ?? 0);
            }
            $row['nb_devis_ouverts'] = (int)($row['nb_devis_ouverts'] ?? 0);
            $row['action_type'] = $this->commercialActionType($row);
            $row['action_label'] = $this->commercialActionLabel($row['action_type']);
            $row['priority'] = $this->commercialActionPriority($row);
            $row['priority_label'] = $this->commercialActionPriorityLabel($row['priority']);
            $row['message_suggere'] = $this->commercialActionMessage($row);
            return $row;
        }, $rows));

        $priority = (string)($filters['priority'] ?? '');
        $actionType = (string)($filters['action_type'] ?? '');
        return array_values(array_filter($rows, static function (array $row) use ($priority, $actionType): bool {
            if ($priority !== '' && (string)$row['priority'] !== $priority) {
                return false;
            }
            if ($actionType !== '' && (string)$row['action_type'] !== $actionType) {
                return false;
            }
            return true;
        }));
    }

    private function commercialActionKpis(array $actions): array
    {
        $kpis = [
            'nb_actions' => count($actions),
            'priorite_haute' => 0,
            'priorite_moyenne' => 0,
            'priorite_basse' => 0,
            'total_retard' => 0.0,
            'total_devis_ouverts' => 0.0,
            'clients_sans_contact' => 0,
        ];
        foreach ($actions as $row) {
            $priority = (string)($row['priority'] ?? 'basse');
            if ($priority === 'haute') {
                $kpis['priorite_haute']++;
            } elseif ($priority === 'moyenne') {
                $kpis['priorite_moyenne']++;
            } else {
                $kpis['priorite_basse']++;
            }
            $kpis['total_retard'] += (float)($row['retard_ttc'] ?? 0);
            $kpis['total_devis_ouverts'] += (float)($row['montant_devis_ouverts'] ?? 0);
            if (empty($row['email']) && empty($row['telephone'])) {
                $kpis['clients_sans_contact']++;
            }
        }
        return $kpis;
    }

    private function commercialActionType(array $row): string
    {
        if ((float)($row['retard_ttc'] ?? 0) > 0) {
            return 'impaye';
        }
        if ((int)($row['nb_devis_ouverts'] ?? 0) > 0) {
            return 'devis';
        }
        if (empty($row['derniere_facture'])) {
            return 'premier_contact';
        }
        return 'reactivation';
    }

    private function commercialActionLabel(string $type): string
    {
        return match ($type) {
            'impaye' => 'Relance règlement',
            'devis' => 'Relance devis',
            'premier_contact' => 'Premier contact',
            'reactivation' => 'Réactivation client',
            default => 'Action commerciale',
        };
    }

    private function commercialActionPriority(array $row): string
    {
        if ((float)($row['retard_ttc'] ?? 0) > 0 || (float)($row['reste_du'] ?? 0) >= 1000) {
            return 'haute';
        }
        if ((float)($row['montant_devis_ouverts'] ?? 0) >= 1000 || (float)($row['ca_ttc'] ?? 0) >= 3000) {
            return 'moyenne';
        }
        return 'basse';
    }

    private function commercialActionPriorityLabel(string $priority): string
    {
        return match ($priority) {
            'haute' => 'Haute',
            'moyenne' => 'Moyenne',
            'basse' => 'Basse',
            default => 'À traiter',
        };
    }

    private function commercialActionMessage(array $row): string
    {
        return match ((string)($row['action_type'] ?? '')) {
            'impaye' => 'Reprendre contact pour le règlement du solde restant dû.',
            'devis' => 'Relancer le devis en cours et proposer un accompagnement à la décision.',
            'premier_contact' => 'Prévoir un premier contact commercial ou une prise de nouvelles.',
            'reactivation' => 'Envoyer une offre de réactivation ou inviter le client à revenir en boutique.',
            default => 'Prévoir une action commerciale personnalisée.',
        };
    }

    private function fetchPreparedOne(string $sql, array $params): array
    {
        try {
            $stmt = $this->db->prepare($sql);
            foreach ($params as $key => $value) {
                $stmt->bindValue(':' . $key, $value, is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
            }
            $stmt->execute();
            return $stmt->fetch(PDO::FETCH_ASSOC) ?: [];
        } catch (Throwable) {
            return [];
        }
    }

    private function fetchPreparedAll(string $sql, array $params): array
    {
        try {
            $stmt = $this->db->prepare($sql);
            foreach ($params as $key => $value) {
                $stmt->bindValue(':' . $key, $value, is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
            }
            $stmt->execute();
            return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        } catch (Throwable) {
            return [];
        }
    }

    private function columnExists(string $table, string $column): bool
    {
        try {
            $stmt = $this->db->prepare('SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = :table AND column_name = :column');
            $stmt->execute(['table' => $table, 'column' => $column]);
            return (int)$stmt->fetchColumn() > 0;
        } catch (Throwable) {
            return false;
        }
    }

    private function tableExists(string $table): bool
    {
        try {
            $stmt = $this->db->prepare('SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = :table');
            $stmt->execute(['table' => $table]);
            return (int)$stmt->fetchColumn() > 0;
        } catch (Throwable) {
            return false;
        }
    }

    private function fetchOne(string $sql): array
    {
        try {
            return $this->db->query($sql)->fetch(PDO::FETCH_ASSOC) ?: [];
        } catch (Throwable) {
            return [];
        }
    }

    private function fetchAll(string $sql): array
    {
        try {
            return $this->db->query($sql)->fetchAll(PDO::FETCH_ASSOC) ?: [];
        } catch (Throwable) {
            return [];
        }
    }

    private function scalar(string $sql): mixed
    {
        try {
            return $this->db->query($sql)->fetchColumn();
        } catch (Throwable) {
            return 0;
        }
    }
}
