<?php

declare(strict_types=1);

namespace App\Repositories;

final class ArticleRepository extends BaseRepository
{
    public function allActive(): array
    {
        return $this->list(false);
    }

    public function all(): array
    {
        return $this->list(true);
    }

    private function list(bool $includeInactive): array
    {
        $where = $includeInactive ? '' : 'WHERE a.actif = 1';
        $sql = "
            SELECT a.*, f.libelle AS famille, m.libelle AS marque,
                   fo.raison_sociale AS fournisseur_principal_nom,
                   fo.code_fournisseur AS fournisseur_principal_code
            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
            LEFT JOIN fournisseurs fo ON fo.id = a.fournisseur_principal_id
            {$where}
            ORDER BY a.actif DESC, a.designation ASC
        ";

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

    /**
     * Liste unique des articles actifs destinée aux sélecteurs de devis, BL et factures.
     * Cette source est volontairement centralisée afin qu’un article créé dans
     * Stock → Articles soit immédiatement disponible dans tous les documents.
     */
    public function pickerActive(): array
    {
        return $this->fetchAll(
            "SELECT id, reference, designation, unite, prix_achat_ht, prix_vente_ht, taux_tva, marge_pourcentage
             FROM articles
             WHERE actif = 1
             ORDER BY designation ASC, reference ASC"
        );
    }

    public function find(int $id): ?array
    {
        return $this->fetchOne(
            "SELECT a.*, f.libelle AS famille, m.libelle AS marque,
                    fo.raison_sociale AS fournisseur_principal_nom, fo.code_fournisseur AS fournisseur_principal_code
             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
             LEFT JOIN fournisseurs fo ON fo.id = a.fournisseur_principal_id
             WHERE a.id = :id LIMIT 1",
            ['id' => $id]
        );
    }

    public function updatePurchasing(int $id, array $data): void
    {
        $sql = "
            UPDATE articles SET
                fournisseur_principal_id = :fournisseur_principal_id,
                reference_fournisseur = :reference_fournisseur,
                prix_achat_ht = :prix_achat_ht,
                prix_achat_fournisseur_ht = :prix_achat_ht,
                dernier_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,
                date_dernier_achat = :date_dernier_achat
            WHERE id = :id
        ";

        $this->execute($sql, [
            'id' => $id,
            'fournisseur_principal_id' => !empty($data['fournisseur_principal_id']) ? (int)$data['fournisseur_principal_id'] : null,
            'reference_fournisseur' => trim((string)($data['reference_fournisseur'] ?? '')) ?: null,
            'prix_achat_ht' => (float)$data['prix_achat_ht'],
            'coefficient' => (float)$data['coefficient'],
            'prix_vente_ht' => (float)$data['prix_vente_ht'],
            'taux_tva' => (float)$data['taux_tva'],
            'prix_vente_ttc' => (float)$data['prix_vente_ttc'],
            'marge_dh' => (float)$data['marge_dh'],
            'marge_pourcentage' => (float)$data['marge_pourcentage'],
            'date_dernier_achat' => trim((string)($data['date_dernier_achat'] ?? '')) ?: null,
        ]);
    }

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

    public function findByReference(string $reference): ?array
    {
        return $this->fetchOne(
            "SELECT * FROM articles WHERE reference = :reference LIMIT 1",
            ['reference' => $reference]
        );
    }

    public function referenceExists(string $reference, ?int $excludeId = null): bool
    {
        $sql = 'SELECT COUNT(*) FROM articles WHERE reference = :reference';
        $params = ['reference' => $reference];
        if ($excludeId !== null) {
            $sql .= ' AND id <> :id';
            $params['id'] = $excludeId;
        }
        return (int)$this->fetchColumn($sql, $params) > 0;
    }

    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);
            $result === 'created' ? $created++ : $updated++;
        }
        return ['created' => $created, 'updated' => $updated];
    }

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

    public function upsertFromImport(array $article): string
    {
        $existing = $this->findByReference((string)($article['reference'] ?? ''));
        if ($existing) {
            $this->updateByReference($article);
            return 'updated';
        }
        $this->create($article);
        return 'created';
    }

    public function create(array $data): int
    {
        $sql = "
            INSERT INTO articles (
                famille_id, marque_id, reference, designation, conditionnement, unite,
                fournisseur_principal_id, reference_fournisseur,
                prix_achat_ht, prix_achat_fournisseur_ht, dernier_prix_achat_ht,
                coefficient, prix_vente_ht, taux_tva, prix_vente_ttc,
                marge_dh, marge_pourcentage, stock_minimum, actif
            ) VALUES (
                :famille_id, :marque_id, :reference, :designation, :conditionnement, :unite,
                :fournisseur_principal_id, :reference_fournisseur,
                :prix_achat_ht, :prix_achat_ht, :prix_achat_ht,
                :coefficient, :prix_vente_ht, :taux_tva, :prix_vente_ttc,
                :marge_dh, :marge_pourcentage, :stock_minimum, :actif
            )
        ";
        $this->execute($sql, $this->params($data));
        $id = $this->lastInsertId();
        $this->ensureStockRowById($id, (float)($data['stock_minimum'] ?? 0));
        return $id;
    }

    public function update(int $id, array $data): void
    {
        $sql = "
            UPDATE articles SET
                famille_id = :famille_id,
                marque_id = :marque_id,
                reference = :reference,
                designation = :designation,
                conditionnement = :conditionnement,
                unite = :unite,
                fournisseur_principal_id = :fournisseur_principal_id,
                reference_fournisseur = :reference_fournisseur,
                prix_achat_ht = :prix_achat_ht,
                prix_achat_fournisseur_ht = :prix_achat_ht,
                dernier_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,
                actif = :actif
            WHERE id = :id
        ";
        $params = $this->params($data);
        $params['id'] = $id;
        $this->execute($sql, $params);
        $this->ensureStockRowById($id, (float)($data['stock_minimum'] ?? 0));
    }

    public function updateByReference(array $data): void
    {
        $existing = $this->findByReference((string)$data['reference']);
        if (!$existing) {
            return;
        }
        $merged = array_merge($existing, $data, ['actif' => 1]);
        $this->update((int)$existing['id'], $merged);
    }

    public function setActive(int $id, bool $active): void
    {
        $this->execute('UPDATE articles SET actif = :actif WHERE id = :id', [
            'id' => $id,
            'actif' => $active ? 1 : 0,
        ]);
    }

    private function ensureStockRowById(int $id, float $stockMinimum): void
    {
        if (!$this->tableExists('stocks')) {
            return;
        }
        $this->execute(
            "INSERT INTO stocks (article_id, quantite, stock_minimum, valeur_stock)
             VALUES (:article_id, 0, :stock_minimum, 0)
             ON DUPLICATE KEY UPDATE stock_minimum = VALUES(stock_minimum)",
            ['article_id' => $id, 'stock_minimum' => $stockMinimum]
        );
    }

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

    private function params(array $data): array
    {
        return [
            'famille_id' => !empty($data['famille_id']) ? (int)$data['famille_id'] : null,
            'marque_id' => !empty($data['marque_id']) ? (int)$data['marque_id'] : null,
            'reference' => trim((string)($data['reference'] ?? '')),
            'designation' => trim((string)($data['designation'] ?? '')),
            'conditionnement' => trim((string)($data['conditionnement'] ?? '')) ?: null,
            'unite' => trim((string)($data['unite'] ?? 'unité')) ?: 'unité',
            'fournisseur_principal_id' => !empty($data['fournisseur_principal_id']) ? (int)$data['fournisseur_principal_id'] : null,
            'reference_fournisseur' => trim((string)($data['reference_fournisseur'] ?? '')) ?: null,
            'prix_achat_ht' => (float)($data['prix_achat_ht'] ?? 0),
            'coefficient' => (float)($data['coefficient'] ?? 0),
            'prix_vente_ht' => (float)($data['prix_vente_ht'] ?? 0),
            'taux_tva' => (float)($data['taux_tva'] ?? 20),
            'prix_vente_ttc' => (float)($data['prix_vente_ttc'] ?? 0),
            'marge_dh' => (float)($data['marge_dh'] ?? 0),
            'marge_pourcentage' => (float)($data['marge_pourcentage'] ?? 0),
            'stock_minimum' => (float)($data['stock_minimum'] ?? 0),
            'actif' => isset($data['actif']) ? (int)(bool)$data['actif'] : 1,
        ];
    }
}
