<?php

declare(strict_types=1);

namespace App\Models;

use App\Core\Model;
use PDO;
use RuntimeException;

final class Facture extends Model
{
    public function all(): array
    {
        if (!$this->tableExists('factures')) {
            return [];
        }

        return $this->db->query("\n            SELECT f.*, c.raison_sociale AS client_nom, bl.numero AS bl_numero, d.id AS document_id\n            FROM factures f\n            INNER JOIN clients c ON c.id = f.client_id\n            LEFT JOIN bons_livraison bl ON bl.id = f.bon_livraison_id\n            LEFT JOIN documents d\n                ON d.actif = 1\n               AND d.type_document = 'FACTURE'\n               AND (\n                    (d.source_table = 'factures' AND d.source_id = f.id)\n                    OR d.numero = f.numero\n               )\n            WHERE f.actif = 1\n            ORDER BY f.date_creation DESC, f.id DESC\n        ")->fetchAll(PDO::FETCH_ASSOC);
    }

    public function find(int $id): ?array
    {
        if (!$this->tableExists('factures')) {
            return null;
        }

        $stmt = $this->db->prepare("\n            SELECT f.*, c.raison_sociale AS client_nom, c.adresse, c.code_postal, c.ville, c.pays, c.telephone, c.mobile, c.email, c.ice AS client_ice, bl.numero AS bl_numero, d.id AS document_id\n            FROM factures f\n            INNER JOIN clients c ON c.id = f.client_id\n            LEFT JOIN bons_livraison bl ON bl.id = f.bon_livraison_id\n            LEFT JOIN documents d\n                ON d.actif = 1\n               AND d.type_document = 'FACTURE'\n               AND (\n                    (d.source_table = 'factures' AND d.source_id = f.id)\n                    OR d.numero = f.numero\n               )\n            WHERE f.id = :id AND f.actif = 1\n            LIMIT 1\n        ");
        $stmt->execute(['id' => $id]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    public function lines(int $factureId): array
    {
        if (!$this->tableExists('factures_lignes')) {
            return [];
        }

        $stmt = $this->db->prepare("\n            SELECT *\n            FROM factures_lignes\n            WHERE facture_id = :id\n            ORDER BY ordre ASC, id ASC\n        ");
        $stmt->execute(['id' => $factureId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function nextNumber(): string
    {
        $year = date('Y');
        $prefix = 'FA' . $year . '-';
        $stmt = $this->db->prepare('SELECT numero FROM factures WHERE numero LIKE :prefix ORDER BY id DESC LIMIT 1');
        $stmt->execute(['prefix' => $prefix . '%']);
        $last = (string) ($stmt->fetchColumn() ?: '');
        if ($last === '') {
            return $prefix . '000001';
        }
        $number = (int) substr($last, -6);
        return $prefix . str_pad((string) ($number + 1), 6, '0', STR_PAD_LEFT);
    }

    public function createFromBonLivraison(int $bonLivraisonId): int
    {
        if (!$this->tableExists('factures') || !$this->tableExists('factures_lignes')) {
            throw new RuntimeException('Les tables factures ne sont pas installées. Importez database/migrations/007_factures.sql.');
        }

        $blModel = new BonLivraison();
        $bon = $blModel->find($bonLivraisonId);
        if (!$bon) {
            throw new RuntimeException('Bon de livraison introuvable.');
        }

        $existing = $this->findByBonLivraison($bonLivraisonId);
        if ($existing) {
            return (int) $existing['id'];
        }

        $lines = $blModel->lines($bonLivraisonId);
        if (empty($lines)) {
            throw new RuntimeException('Impossible de facturer un bon de livraison sans ligne.');
        }

        $totals = $this->calculateTotals($lines);
        $dateFacture = date('Y-m-d');
        $dateEcheance = date('Y-m-d', strtotime('+30 days'));

        $this->db->beginTransaction();
        try {
            $stmt = $this->db->prepare("\n                INSERT INTO factures (\n                    client_id, bon_livraison_id, numero, date_facture, date_echeance, statut,\n                    total_ht, total_tva, total_ttc, montant_regle, reste_du, marge_ht, marge_pourcentage, observations\n                ) VALUES (\n                    :client_id, :bon_livraison_id, :numero, :date_facture, :date_echeance, :statut,\n                    :total_ht, :total_tva, :total_ttc, :montant_regle, :reste_du, :marge_ht, :marge_pourcentage, :observations\n                )\n            ");
            $stmt->execute([
                'client_id' => (int) $bon['client_id'],
                'bon_livraison_id' => $bonLivraisonId,
                'numero' => $this->nextNumber(),
                'date_facture' => $dateFacture,
                'date_echeance' => $dateEcheance,
                'statut' => 'émise',
                'total_ht' => $totals['total_ht'],
                'total_tva' => $totals['total_tva'],
                'total_ttc' => $totals['total_ttc'],
                'montant_regle' => 0,
                'reste_du' => $totals['total_ttc'],
                'marge_ht' => $totals['marge_ht'],
                'marge_pourcentage' => $totals['marge_pourcentage'],
                'observations' => trim((string) ($bon['observations'] ?? '')),
            ]);
            $factureId = (int) $this->db->lastInsertId();

            $lineStmt = $this->db->prepare("\n                INSERT INTO factures_lignes (\n                    facture_id, article_id, reference, designation, unite, quantite,\n                    prix_achat_ht, prix_vente_ht, remise, taux_tva, total_ht, total_tva, total_ttc,\n                    marge_ht, marge_pourcentage, ordre\n                ) VALUES (\n                    :facture_id, :article_id, :reference, :designation, :unite, :quantite,\n                    :prix_achat_ht, :prix_vente_ht, :remise, :taux_tva, :total_ht, :total_tva, :total_ttc,\n                    :marge_ht, :marge_pourcentage, :ordre\n                )\n            ");

            foreach ($lines as $index => $line) {
                $quantity = (float) ($line['quantite'] ?? 0);
                $prixAchat = (float) ($line['prix_achat_ht'] ?? 0);
                $totalHt = (float) ($line['total_ht'] ?? 0);
                $margeHt = round($totalHt - ($quantity * $prixAchat), 2);
                $margePct = $totalHt > 0 ? round(($margeHt / $totalHt) * 100, 2) : 0;

                $lineStmt->execute([
                    'facture_id' => $factureId,
                    'article_id' => $line['article_id'] ?: null,
                    'reference' => $line['reference'] ?? '',
                    'designation' => $line['designation'] ?? '',
                    'unite' => $line['unite'] ?? 'unité',
                    'quantite' => $quantity,
                    'prix_achat_ht' => $prixAchat,
                    'prix_vente_ht' => (float) ($line['prix_vente_ht'] ?? 0),
                    'remise' => (float) ($line['remise'] ?? 0),
                    'taux_tva' => (float) ($line['taux_tva'] ?? 20),
                    'total_ht' => $totalHt,
                    'total_tva' => (float) ($line['total_tva'] ?? 0),
                    'total_ttc' => (float) ($line['total_ttc'] ?? 0),
                    'marge_ht' => $margeHt,
                    'marge_pourcentage' => $margePct,
                    'ordre' => $index + 1,
                ]);
            }

            $this->db->prepare("UPDATE bons_livraison SET statut = 'facturé' WHERE id = :id")->execute(['id' => $bonLivraisonId]);
            $this->db->commit();
            return $factureId;
        } catch (\Throwable $exception) {
            $this->db->rollBack();
            throw $exception;
        }
    }


    public function createFromDevis(int $devisId): int
    {
        if (!$this->tableExists('factures') || !$this->tableExists('factures_lignes')) {
            throw new RuntimeException('Les tables factures ne sont pas installées.');
        }

        $devisModel = new Devis();
        $devis = $devisModel->find($devisId);
        if (!$devis) {
            throw new RuntimeException('Devis introuvable.');
        }

        if ($this->columnExists('factures', 'devis_id')) {
            $stmt = $this->db->prepare('SELECT id FROM factures WHERE devis_id = :id AND actif = 1 LIMIT 1');
            $stmt->execute(['id' => $devisId]);
            $existingId = (int)($stmt->fetchColumn() ?: 0);
            if ($existingId > 0) {
                return $existingId;
            }
        }

        $lines = $devisModel->lines($devisId);
        if (empty($lines)) {
            throw new RuntimeException('Impossible de facturer un devis sans ligne.');
        }

        $totals = $this->calculateTotals($lines);
        $dateFacture = date('Y-m-d');
        $dateEcheance = date('Y-m-d', strtotime('+30 days'));
        $hasDevisId = $this->columnExists('factures', 'devis_id');

        $this->db->beginTransaction();
        try {
            $columns = 'client_id, bon_livraison_id, numero, date_facture, date_echeance, statut, total_ht, total_tva, total_ttc, montant_regle, reste_du, marge_ht, marge_pourcentage, observations';
            $values = ':client_id, NULL, :numero, :date_facture, :date_echeance, :statut, :total_ht, :total_tva, :total_ttc, 0, :reste_du, :marge_ht, :marge_pourcentage, :observations';
            if ($hasDevisId) {
                $columns = 'devis_id, ' . $columns;
                $values = ':devis_id, ' . $values;
            }
            $stmt = $this->db->prepare("INSERT INTO factures ($columns) VALUES ($values)");
            $params = [
                'client_id' => (int)$devis['client_id'],
                'numero' => $this->nextNumber(),
                'date_facture' => $dateFacture,
                'date_echeance' => $dateEcheance,
                'statut' => 'émise',
                'total_ht' => $totals['total_ht'],
                'total_tva' => $totals['total_tva'],
                'total_ttc' => $totals['total_ttc'],
                'reste_du' => $totals['total_ttc'],
                'marge_ht' => $totals['marge_ht'],
                'marge_pourcentage' => $totals['marge_pourcentage'],
                'observations' => trim((string)($devis['observations'] ?? '')),
            ];
            if ($hasDevisId) {
                $params['devis_id'] = $devisId;
            }
            $stmt->execute($params);
            $factureId = (int)$this->db->lastInsertId();

            $lineStmt = $this->db->prepare("\n                INSERT INTO factures_lignes (facture_id, article_id, reference, designation, unite, quantite, prix_achat_ht, prix_vente_ht, remise, taux_tva, total_ht, total_tva, total_ttc, marge_ht, marge_pourcentage, ordre)\n                VALUES (:facture_id, :article_id, :reference, :designation, :unite, :quantite, :prix_achat_ht, :prix_vente_ht, :remise, :taux_tva, :total_ht, :total_tva, :total_ttc, :marge_ht, :marge_pourcentage, :ordre)\n            ");
            foreach ($lines as $index => $line) {
                $lineStmt->execute([
                    'facture_id' => $factureId,
                    'article_id' => !empty($line['article_id']) ? (int)$line['article_id'] : null,
                    'reference' => $line['reference'] ?? '',
                    'designation' => $line['designation'] ?? '',
                    'unite' => $line['unite'] ?? 'unité',
                    'quantite' => (float)($line['quantite'] ?? 0),
                    'prix_achat_ht' => (float)($line['prix_achat_ht'] ?? 0),
                    'prix_vente_ht' => (float)($line['prix_vente_ht'] ?? 0),
                    'remise' => (float)($line['remise'] ?? 0),
                    'taux_tva' => (float)($line['taux_tva'] ?? 20),
                    'total_ht' => (float)($line['total_ht'] ?? 0),
                    'total_tva' => (float)($line['total_tva'] ?? 0),
                    'total_ttc' => (float)($line['total_ttc'] ?? 0),
                    'marge_ht' => (float)($line['marge_ht'] ?? 0),
                    'marge_pourcentage' => (float)($line['marge_pourcentage'] ?? 0),
                    'ordre' => $index + 1,
                ]);
            }
            $this->db->prepare("UPDATE devis SET statut = 'transformé' WHERE id = :id")->execute(['id' => $devisId]);
            $this->db->commit();
            return $factureId;
        } catch (\Throwable $exception) {
            $this->db->rollBack();
            throw $exception;
        }
    }

    public function updatePaymentStatus(int $factureId, float $montantRegle): void
    {
        $facture = $this->find($factureId);
        if (!$facture) {
            throw new RuntimeException('Facture introuvable.');
        }
        $total = (float) ($facture['total_ttc'] ?? 0);
        $montantRegle = max(0, min($total, $montantRegle));
        $resteDu = round($total - $montantRegle, 2);
        $statut = $resteDu <= 0 ? 'réglée' : ($montantRegle > 0 ? 'partiellement réglée' : 'émise');

        $stmt = $this->db->prepare("\n            UPDATE factures\n            SET montant_regle = :montant_regle, reste_du = :reste_du, statut = :statut\n            WHERE id = :id\n        ");
        $stmt->execute([
            'montant_regle' => $montantRegle,
            'reste_du' => $resteDu,
            'statut' => $statut,
            'id' => $factureId,
        ]);
    }

    private function findByBonLivraison(int $bonLivraisonId): ?array
    {
        $stmt = $this->db->prepare('SELECT * FROM factures WHERE bon_livraison_id = :id AND actif = 1 LIMIT 1');
        $stmt->execute(['id' => $bonLivraisonId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    private function calculateTotals(array $lines): array
    {
        $totalHt = array_sum(array_map(fn(array $line): float => (float)($line['total_ht'] ?? 0), $lines));
        $totalTva = array_sum(array_map(fn(array $line): float => (float)($line['total_tva'] ?? 0), $lines));
        $totalTtc = array_sum(array_map(fn(array $line): float => (float)($line['total_ttc'] ?? 0), $lines));
        $margeHt = 0.0;
        foreach ($lines as $line) {
            $margeHt += (float)($line['total_ht'] ?? 0) - ((float)($line['quantite'] ?? 0) * (float)($line['prix_achat_ht'] ?? 0));
        }

        return [
            'total_ht' => round($totalHt, 2),
            'total_tva' => round($totalTva, 2),
            'total_ttc' => round($totalTtc, 2),
            'marge_ht' => round($margeHt, 2),
            'marge_pourcentage' => $totalHt > 0 ? round(($margeHt / $totalHt) * 100, 2) : 0,
        ];
    }


    private function columnExists(string $table, string $column): bool
    {
        $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;
    }

    private function tableExists(string $table): bool
    {
        $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;
    }
}
