<?php

declare(strict_types=1);

namespace App\Repositories;

final class StockRepository extends BaseRepository
{
    public function initializeMissingRows(): int
    {
        if (!$this->tableExists('stocks') || !$this->tableExists('articles')) {
            return 0;
        }

        $sql = "
            INSERT INTO stocks (article_id, quantite, stock_minimum, valeur_stock)
            SELECT a.id, 0, COALESCE(a.stock_minimum, 0), 0
            FROM articles a
            LEFT JOIN stocks s ON s.article_id = a.id
            WHERE a.actif = 1 AND s.id IS NULL
        ";

        $this->db->exec($sql);
        return 0;
    }

    public function summary(): array
    {
        if (!$this->tableExists('stocks') || !$this->tableExists('articles')) {
            return ['total_articles' => 0, 'valeur_stock' => 0.0, 'ruptures' => 0, 'alertes' => 0];
        }

        $this->initializeMissingRows();

        $row = $this->fetchOne("            
            SELECT
                COUNT(*) AS total_articles,
                COALESCE(SUM(s.valeur_stock), 0) AS valeur_stock,
                SUM(CASE WHEN COALESCE(s.quantite, 0) <= 0 THEN 1 ELSE 0 END) AS ruptures,
                SUM(CASE WHEN COALESCE(s.quantite, 0) > 0 AND COALESCE(s.quantite, 0) <= COALESCE(s.stock_minimum, 0) THEN 1 ELSE 0 END) AS alertes
            FROM articles a
            LEFT JOIN stocks s ON s.article_id = a.id
            WHERE a.actif = 1
        ") ?? [];

        return [
            'total_articles' => (int) ($row['total_articles'] ?? 0),
            'valeur_stock' => (float) ($row['valeur_stock'] ?? 0),
            'ruptures' => (int) ($row['ruptures'] ?? 0),
            'alertes' => (int) ($row['alertes'] ?? 0),
        ];
    }

    public function all(): array
    {
        if (!$this->tableExists('stocks') || !$this->tableExists('articles')) {
            return [];
        }

        $this->initializeMissingRows();

        return $this->fetchAll("            
            SELECT
                a.id AS article_id,
                a.reference,
                a.designation,
                a.prix_achat_ht,
                a.unite,
                s.quantite,
                s.stock_minimum,
                s.valeur_stock,
                s.date_maj
            FROM articles a
            INNER JOIN stocks s ON s.article_id = a.id
            WHERE a.actif = 1
            ORDER BY a.designation ASC
        ");
    }

    public function articlesForSelect(): array
    {
        if (!$this->tableExists('articles')) {
            return [];
        }

        return $this->fetchAll("            
            SELECT id, reference, designation, prix_achat_ht, unite
            FROM articles
            WHERE actif = 1
            ORDER BY designation ASC
        ");
    }

    public function findArticle(int $articleId): ?array
    {
        if (!$this->tableExists('articles')) {
            return null;
        }

        return $this->fetchOne("            
            SELECT a.*, s.quantite, s.stock_minimum, s.valeur_stock, s.date_maj
            FROM articles a
            LEFT JOIN stocks s ON s.article_id = a.id
            WHERE a.id = :id AND a.actif = 1
            LIMIT 1
        ", ['id' => $articleId]);
    }

    public function movements(int $articleId, int $limit = 100): array
    {
        if (!$this->tableExists('mouvements_stock')) {
            return [];
        }

        $limit = max(1, min(500, $limit));

        return $this->fetchAll("            
            SELECT *
            FROM mouvements_stock
            WHERE article_id = :article_id
            ORDER BY date_mouvement DESC, id DESC
            LIMIT {$limit}
        ", ['article_id' => $articleId]);
    }

    public function recentMovements(int $limit = 10): array
    {
        if (!$this->tableExists('mouvements_stock') || !$this->tableExists('articles')) {
            return [];
        }

        $limit = max(1, min(100, $limit));

        return $this->fetchAll("            
            SELECT m.*, a.reference, a.designation
            FROM mouvements_stock m
            INNER JOIN articles a ON a.id = m.article_id
            ORDER BY m.date_mouvement DESC, m.id DESC
            LIMIT {$limit}
        ");
    }

    public function allMovements(array $filters = [], int $limit = 300): array
    {
        if (!$this->tableExists('mouvements_stock') || !$this->tableExists('articles')) {
            return [];
        }

        $where = [];
        $params = [];

        if (!empty($filters['article'])) {
            $where[] = '(a.reference LIKE :article OR a.designation LIKE :article)';
            $params['article'] = '%' . $filters['article'] . '%';
        }

        if (!empty($filters['type'])) {
            $where[] = 'm.type_mouvement = :type';
            $params['type'] = $filters['type'];
        }

        if (!empty($filters['date_debut'])) {
            $where[] = 'DATE(m.date_mouvement) >= :date_debut';
            $params['date_debut'] = $filters['date_debut'];
        }

        if (!empty($filters['date_fin'])) {
            $where[] = 'DATE(m.date_mouvement) <= :date_fin';
            $params['date_fin'] = $filters['date_fin'];
        }

        $sql = "
            SELECT m.*, a.reference, a.designation, a.unite
            FROM mouvements_stock m
            INNER JOIN articles a ON a.id = m.article_id
        ";

        if ($where) {
            $sql .= ' WHERE ' . implode(' AND ', $where);
        }

        $sql .= ' ORDER BY m.date_mouvement DESC, m.id DESC LIMIT ' . max(1, min(1000, $limit));

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

    public function movementTypes(): array
    {
        if (!$this->tableExists('mouvements_stock')) {
            return [];
        }

        return $this->fetchAll("            
            SELECT DISTINCT type_mouvement
            FROM mouvements_stock
            WHERE type_mouvement IS NOT NULL AND type_mouvement <> ''
            ORDER BY type_mouvement ASC
        ");
    }

    public function movementTypeValues(): array
    {
        return array_map(static fn (array $row): string => (string) ($row['type_mouvement'] ?? ''), $this->movementTypes());
    }

    public function currentQuantity(int $articleId): float
    {
        if (!$this->tableExists('stocks')) {
            return 0.0;
        }

        return (float) ($this->fetchColumn(
            'SELECT quantite FROM stocks WHERE article_id = :article_id LIMIT 1',
            ['article_id' => $articleId]
        ) ?: 0);
    }

    public function createMovement(array $data): void
    {
        $sql = "
            INSERT INTO mouvements_stock (
                article_id, type_mouvement, quantite, prix_achat_ht, origine, origine_id, commentaire, utilisateur
            ) VALUES (
                :article_id, :type_mouvement, :quantite, :prix_achat_ht, :origine, :origine_id, :commentaire, :utilisateur
            )
        ";

        $this->execute($sql, [
            'article_id' => $data['article_id'],
            'type_mouvement' => $data['type_mouvement'],
            'quantite' => $data['quantite'],
            'prix_achat_ht' => $data['prix_achat_ht'] ?? 0,
            'origine' => $data['origine'] ?? null,
            'origine_id' => $data['origine_id'] ?? null,
            'commentaire' => $data['commentaire'] ?? null,
            'utilisateur' => $data['utilisateur'] ?? 'Admin',
        ]);
    }

    public function updateStock(int $articleId, float $quantity, float $stockMinimum): void
    {
        $article = $this->findArticle($articleId);
        $prixAchat = (float) ($article['prix_achat_ht'] ?? 0);
        $value = round($quantity * $prixAchat, 2);

        $this->execute("            
            INSERT INTO stocks (article_id, quantite, stock_minimum, valeur_stock, date_maj)
            VALUES (:article_id, :quantite, :stock_minimum, :valeur_stock, NOW())
            ON DUPLICATE KEY UPDATE
                quantite = VALUES(quantite),
                stock_minimum = VALUES(stock_minimum),
                valeur_stock = VALUES(valeur_stock),
                date_maj = NOW()
        ", [
            'article_id' => $articleId,
            'quantite' => $quantity,
            'stock_minimum' => $stockMinimum,
            'valeur_stock' => $value,
        ]);
    }

    public function transaction(callable $callback): mixed
    {
        $this->beginTransaction();
        try {
            $result = $callback();
            $this->commit();
            return $result;
        } catch (\Throwable $exception) {
            $this->rollBack();
            throw $exception;
        }
    }

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