<?php

declare(strict_types=1);

namespace App\Models;

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

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

        return $this->db->query("\n            SELECT d.*, c.raison_sociale AS client_nom\n            FROM devis d\n            INNER JOIN clients c ON c.id = d.client_id\n            WHERE d.actif = 1\n            ORDER BY d.date_creation DESC, d.id DESC\n        ")->fetchAll(PDO::FETCH_ASSOC);
    }

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

        $stmt = $this->db->prepare("\n            SELECT d.*, 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\n            FROM devis d\n            INNER JOIN clients c ON c.id = d.client_id\n            WHERE d.id = :id AND d.actif = 1\n            LIMIT 1\n        ");
        $stmt->execute(['id' => $id]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

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

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

    public function nextNumber(): string
    {
        $year = date('Y');
        $prefix = 'DV' . $year . '-';
        $stmt = $this->db->prepare("SELECT numero FROM devis 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 create(array $data): int
    {
        if (!$this->tableExists('devis') || !$this->tableExists('devis_lignes')) {
            throw new RuntimeException('Les tables devis ne sont pas installées. Importez database/migrations/005_devis.sql.');
        }

        $lines = $this->normalizeLines($data['lignes'] ?? []);
        if (empty($lines)) {
            throw new RuntimeException('Veuillez ajouter au moins une ligne au devis.');
        }

        $totals = $this->calculateTotals($lines);
        $numero = trim((string) ($data['numero'] ?? '')) ?: $this->nextNumber();

        $this->db->beginTransaction();
        try {
            $stmt = $this->db->prepare("\n                INSERT INTO devis (\n                    client_id, numero, date_devis, validite, statut,\n                    total_ht, total_tva, total_ttc, marge_ht, marge_pourcentage, observations\n                ) VALUES (\n                    :client_id, :numero, :date_devis, :validite, :statut,\n                    :total_ht, :total_tva, :total_ttc, :marge_ht, :marge_pourcentage, :observations\n                )\n            ");
            $stmt->execute([
                'client_id' => (int) ($data['client_id'] ?? 0),
                'numero' => $numero,
                'date_devis' => $data['date_devis'] ?? date('Y-m-d'),
                'validite' => $data['validite'] ?: null,
                'statut' => 'brouillon',
                'total_ht' => $totals['total_ht'],
                'total_tva' => $totals['total_tva'],
                'total_ttc' => $totals['total_ttc'],
                'marge_ht' => $totals['marge_ht'],
                'marge_pourcentage' => $totals['marge_pourcentage'],
                'observations' => trim((string) ($data['observations'] ?? '')),
            ]);

            $devisId = (int) $this->db->lastInsertId();
            $this->insertLines($devisId, $lines);

            $this->db->commit();
            return $devisId;
        } catch (\Throwable $exception) {
            $this->db->rollBack();
            throw $exception;
        }
    }



    public function update(int $id, array $data): void
    {
        $devis = $this->find($id);
        if (!$devis) {
            throw new RuntimeException('Devis introuvable.');
        }

        $lines = $this->normalizeLines($data['lignes'] ?? []);
        if (empty($lines)) {
            throw new RuntimeException('Veuillez ajouter au moins une ligne au devis.');
        }

        $totals = $this->calculateTotals($lines);
        $this->db->beginTransaction();
        try {
            $stmt = $this->db->prepare("\n                UPDATE devis SET\n                    client_id = :client_id, date_devis = :date_devis, validite = :validite,\n                    total_ht = :total_ht, total_tva = :total_tva, total_ttc = :total_ttc,\n                    marge_ht = :marge_ht, marge_pourcentage = :marge_pourcentage,\n                    observations = :observations\n                WHERE id = :id AND actif = 1\n            ");
            $stmt->execute([
                'client_id' => (int)($data['client_id'] ?? 0),
                'date_devis' => $data['date_devis'] ?? date('Y-m-d'),
                'validite' => !empty($data['validite']) ? $data['validite'] : null,
                'total_ht' => $totals['total_ht'],
                'total_tva' => $totals['total_tva'],
                'total_ttc' => $totals['total_ttc'],
                'marge_ht' => $totals['marge_ht'],
                'marge_pourcentage' => $totals['marge_pourcentage'],
                'observations' => trim((string)($data['observations'] ?? '')),
                'id' => $id,
            ]);

            $this->db->prepare('DELETE FROM devis_lignes WHERE devis_id = :id')->execute(['id' => $id]);
            $this->insertLines($id, $lines);
            $this->db->commit();
        } catch (\Throwable $exception) {
            $this->db->rollBack();
            throw $exception;
        }
    }

    public function duplicate(int $id): int
    {
        $devis = $this->find($id);
        if (!$devis) {
            throw new RuntimeException('Devis introuvable pour duplication.');
        }

        $lines = $this->lines($id);
        if (empty($lines)) {
            throw new RuntimeException('Impossible de dupliquer un devis sans ligne.');
        }

        $data = [
            'client_id' => (int) $devis['client_id'],
            'numero' => $this->nextNumber(),
            'date_devis' => date('Y-m-d'),
            'validite' => date('Y-m-d', strtotime('+30 days')),
            'observations' => trim((string)($devis['observations'] ?? '')),
            'lignes' => [],
        ];

        foreach ($lines as $line) {
            $data['lignes'][] = [
                'article_id' => (int)($line['article_id'] ?? 0),
                'reference' => $line['reference'] ?? '',
                'designation' => $line['designation'] ?? '',
                'unite' => $line['unite'] ?? 'unité',
                'quantite' => (float)($line['quantite'] ?? 1),
                '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),
            ];
        }

        return $this->create($data);
    }

    public function clientsForSelect(): array
    {
        return $this->db->query("\n            SELECT id, code_client, raison_sociale\n            FROM clients\n            WHERE actif = 1\n            ORDER BY raison_sociale ASC\n        ")->fetchAll(PDO::FETCH_ASSOC);
    }

    public function articlesForPicker(): array
    {
        return $this->db->query("\n            SELECT id, reference, designation, unite, prix_achat_ht, prix_vente_ht, taux_tva, marge_pourcentage\n            FROM articles\n            WHERE actif = 1\n            ORDER BY designation ASC\n        ")->fetchAll(PDO::FETCH_ASSOC);
    }

    private function insertLines(int $devisId, array $lines): void
    {
        $stmt = $this->db->prepare("\n            INSERT INTO devis_lignes (\n                devis_id, article_id, reference, designation, unite, quantite,\n                prix_achat_ht, prix_vente_ht, remise, taux_tva,\n                total_ht, total_tva, total_ttc, marge_ht, marge_pourcentage, ordre\n            ) VALUES (\n                :devis_id, :article_id, :reference, :designation, :unite, :quantite,\n                :prix_achat_ht, :prix_vente_ht, :remise, :taux_tva,\n                :total_ht, :total_tva, :total_ttc, :marge_ht, :marge_pourcentage, :ordre\n            )\n        ");

        foreach ($lines as $index => $line) {
            $stmt->execute([
                'devis_id' => $devisId,
                'article_id' => $line['article_id'] ?: null,
                'reference' => $line['reference'],
                'designation' => $line['designation'],
                'unite' => $line['unite'],
                'quantite' => $line['quantite'],
                'prix_achat_ht' => $line['prix_achat_ht'],
                'prix_vente_ht' => $line['prix_vente_ht'],
                'remise' => $line['remise'],
                'taux_tva' => $line['taux_tva'],
                'total_ht' => $line['total_ht'],
                'total_tva' => $line['total_tva'],
                'total_ttc' => $line['total_ttc'],
                'marge_ht' => $line['marge_ht'],
                'marge_pourcentage' => $line['marge_pourcentage'],
                'ordre' => $index + 1,
            ]);
        }
    }

    private function normalizeLines(array $rawLines): array
    {
        $lines = [];
        foreach ($rawLines as $line) {
            $designation = trim((string) ($line['designation'] ?? ''));
            $quantity = $this->number($line['quantite'] ?? 0);
            if ($designation === '' || $quantity <= 0) {
                continue;
            }

            $prixAchat = $this->number($line['prix_achat_ht'] ?? 0);
            $prixVente = $this->number($line['prix_vente_ht'] ?? 0);
            $remise = max(0, min(100, $this->number($line['remise'] ?? 0)));
            $tva = $this->number($line['taux_tva'] ?? 20);
            $netHt = round($quantity * $prixVente * (1 - ($remise / 100)), 2);
            $totalTva = round($netHt * ($tva / 100), 2);
            $totalTtc = round($netHt + $totalTva, 2);
            $margeHt = round($netHt - ($quantity * $prixAchat), 2);
            $margePourcentage = $netHt > 0 ? round(($margeHt / $netHt) * 100, 2) : 0;

            $lines[] = [
                'article_id' => (int) ($line['article_id'] ?? 0),
                'reference' => trim((string) ($line['reference'] ?? '')),
                'designation' => $designation,
                'unite' => trim((string) ($line['unite'] ?? 'unité')) ?: 'unité',
                'quantite' => $quantity,
                'prix_achat_ht' => $prixAchat,
                'prix_vente_ht' => $prixVente,
                'remise' => $remise,
                'taux_tva' => $tva,
                'total_ht' => $netHt,
                'total_tva' => $totalTva,
                'total_ttc' => $totalTtc,
                'marge_ht' => $margeHt,
                'marge_pourcentage' => $margePourcentage,
            ];
        }
        return $lines;
    }

    private function calculateTotals(array $lines): array
    {
        $totalHt = array_sum(array_column($lines, 'total_ht'));
        $totalTva = array_sum(array_column($lines, 'total_tva'));
        $totalTtc = array_sum(array_column($lines, 'total_ttc'));
        $margeHt = array_sum(array_column($lines, 'marge_ht'));

        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 number(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;
    }

    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;
    }
}
