<?php

declare(strict_types=1);

namespace App\Models;

use App\Core\Model;
use PDO;

final class Article extends Model
{
    public function allActive(): array
    {
        $sql = "
            SELECT a.*, f.libelle AS famille, m.libelle AS marque
            FROM articles a
            LEFT JOIN familles_articles f ON f.id = a.famille_id
            LEFT JOIN marques_articles m ON m.id = a.marque_id
            WHERE a.actif = 1
            ORDER BY a.designation ASC
        ";

        return $this->db->query($sql)->fetchAll(PDO::FETCH_ASSOC);
    }

    public function countActive(): int
    {
        return (int) $this->db->query("SELECT COUNT(*) FROM articles WHERE actif = 1")->fetchColumn();
    }

    public function upsertFromImport(array $article): string
    {
        $existing = $this->findByReference($article['reference']);
        $data = $this->withCalculations($article);

        if ($existing) {
            $this->updateByReference($data);
            return 'updated';
        }

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

    public function replaceAll(array $articles): array
    {
        $this->db->beginTransaction();
        try {
            $this->db->exec("DELETE FROM articles");
            $created = 0;
            foreach ($articles as $article) {
                $this->create($this->withCalculations($article));
                $created++;
            }
            $this->db->commit();
            return ['created' => $created, 'updated' => 0];
        } catch (\Throwable $e) {
            $this->db->rollBack();
            throw $e;
        }
    }

    public function importMany(array $articles, string $mode = 'upsert'): array
    {
        if ($mode === 'replace') {
            return $this->replaceAll($articles);
        }

        $created = 0;
        $updated = 0;

        foreach ($articles as $article) {
            $result = $this->upsertFromImport($article);
            if ($result === 'created') {
                $created++;
            } else {
                $updated++;
            }
        }

        return ['created' => $created, 'updated' => $updated];
    }

    private function findByReference(string $reference): ?array
    {
        $stmt = $this->db->prepare("SELECT * FROM articles WHERE reference = :reference LIMIT 1");
        $stmt->execute(['reference' => $reference]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    private function create(array $data): void
    {
        $sql = "
            INSERT INTO articles (
                reference, designation, prix_achat_ht, coefficient, prix_vente_ht, taux_tva,
                prix_vente_ttc, marge_dh, marge_pourcentage, stock_minimum, unite, actif
            ) VALUES (
                :reference, :designation, :prix_achat_ht, :coefficient, :prix_vente_ht, :taux_tva,
                :prix_vente_ttc, :marge_dh, :marge_pourcentage, :stock_minimum, :unite, 1
            )
        ";
        $this->db->prepare($sql)->execute($this->params($data));
        $this->ensureStockRowByReference($data['reference']);
    }

    private function updateByReference(array $data): void
    {
        $sql = "
            UPDATE articles SET
                designation = :designation,
                prix_achat_ht = :prix_achat_ht,
                coefficient = :coefficient,
                prix_vente_ht = :prix_vente_ht,
                taux_tva = :taux_tva,
                prix_vente_ttc = :prix_vente_ttc,
                marge_dh = :marge_dh,
                marge_pourcentage = :marge_pourcentage,
                stock_minimum = :stock_minimum,
                unite = :unite,
                actif = 1
            WHERE reference = :reference
        ";
        $this->db->prepare($sql)->execute($this->params($data));
        $this->ensureStockRowByReference($data['reference']);
    }

    private function ensureStockRowByReference(string $reference): void
    {
        if (!$this->tableExists('stocks')) {
            return;
        }

        $stmt = $this->db->prepare("
            INSERT INTO stocks (article_id, quantite, stock_minimum, valeur_stock)
            SELECT id, 0, COALESCE(stock_minimum, 0), 0
            FROM articles
            WHERE reference = :reference
            ON DUPLICATE KEY UPDATE stock_minimum = VALUES(stock_minimum)
        " );
        $stmt->execute(['reference' => $reference]);
    }

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

    private function withCalculations(array $article): array
    {
        $pua = (float) ($article['prix_achat_ht'] ?? 0);
        $coef = (float) ($article['coefficient'] ?? 1);
        $puv = (float) ($article['prix_vente_ht'] ?? 0);
        $tva = (float) ($article['taux_tva'] ?? 20);

        if ($puv <= 0 && $pua > 0 && $coef > 0) {
            $puv = round($pua * $coef, 2);
        }

        $prixTtc = round($puv * (1 + ($tva / 100)), 2);
        $margeDh = round($puv - $pua, 2);
        $margePourcentage = $puv > 0 ? round(($margeDh / $puv) * 100, 2) : 0;

        return [
            'reference' => trim((string) ($article['reference'] ?? '')),
            'designation' => trim((string) ($article['designation'] ?? '')),
            'prix_achat_ht' => $pua,
            'coefficient' => $coef,
            'prix_vente_ht' => $puv,
            'taux_tva' => $tva,
            'prix_vente_ttc' => $prixTtc,
            'marge_dh' => $margeDh,
            'marge_pourcentage' => $margePourcentage,
            'stock_minimum' => (float) ($article['stock_minimum'] ?? 0),
            'unite' => trim((string) ($article['unite'] ?? 'unité')) ?: 'unité',
        ];
    }

    private function params(array $data): array
    {
        return [
            'reference' => $data['reference'],
            'designation' => $data['designation'],
            'prix_achat_ht' => $data['prix_achat_ht'],
            'coefficient' => $data['coefficient'],
            'prix_vente_ht' => $data['prix_vente_ht'],
            'taux_tva' => $data['taux_tva'],
            'prix_vente_ttc' => $data['prix_vente_ttc'],
            'marge_dh' => $data['marge_dh'],
            'marge_pourcentage' => $data['marge_pourcentage'],
            'stock_minimum' => $data['stock_minimum'],
            'unite' => $data['unite'],
        ];
    }
}
