<?php

declare(strict_types=1);

namespace App\Models;

use App\Core\Model;
use PDO;

final class Stock extends Model
{
    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
        ";
        return $this->db->exec($sql) ?: 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->db->query("
            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
        ")->fetch(PDO::FETCH_ASSOC) ?: [];

        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->db->query("
            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
        ")->fetchAll(PDO::FETCH_ASSOC);
    }


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

        $this->initializeMissingRows();

        return $this->db->query("
            SELECT
                a.id AS article_id,
                a.reference,
                a.designation,
                a.prix_achat_ht,
                a.unite,
                COALESCE(s.quantite, 0) AS quantite,
                COALESCE(s.stock_minimum, 0) AS stock_minimum,
                COALESCE(s.valeur_stock, 0) AS valeur_stock,
                CASE
                    WHEN COALESCE(s.quantite, 0) <= 0 THEN 'rupture'
                    WHEN COALESCE(s.stock_minimum, 0) > 0 AND COALESCE(s.quantite, 0) <= COALESCE(s.stock_minimum, 0) THEN 'alerte'
                    ELSE 'ok'
                END AS etat_stock,
                GREATEST(COALESCE(s.stock_minimum, 0) - COALESCE(s.quantite, 0), 0) AS besoin_reassort
            FROM articles a
            INNER JOIN stocks s ON s.article_id = a.id
            WHERE a.actif = 1
              AND (COALESCE(s.quantite, 0) <= 0
                   OR (COALESCE(s.stock_minimum, 0) > 0 AND COALESCE(s.quantite, 0) <= COALESCE(s.stock_minimum, 0)))
            ORDER BY
                CASE WHEN COALESCE(s.quantite, 0) <= 0 THEN 0 ELSE 1 END,
                besoin_reassort DESC,
                a.designation ASC
        ")->fetchAll(PDO::FETCH_ASSOC);
    }

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

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



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

        $this->initializeMissingRows();

        return $this->db->query("
            SELECT
                a.id,
                a.reference,
                a.designation,
                a.prix_achat_ht,
                a.unite,
                COALESCE(s.quantite, 0) AS quantite,
                COALESCE(s.stock_minimum, 0) AS stock_minimum
            FROM articles a
            LEFT JOIN stocks s ON s.article_id = a.id
            WHERE a.actif = 1
            ORDER BY a.designation ASC
        ")->fetchAll(PDO::FETCH_ASSOC);
    }

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

        $stmt = $this->db->prepare("
            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
        ");
        $stmt->execute(['id' => $articleId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

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

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

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

        return $this->db->query("
            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}
        ")->fetchAll(PDO::FETCH_ASSOC);
    }


    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['article_id'])) {
            $where[] = 'm.article_id = :article_id';
            $params['article_id'] = (int) $filters['article_id'];
        }

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

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

        if (($filters['sens'] ?? '') === 'entree') {
            $where[] = 'm.quantite > 0';
        } elseif (($filters['sens'] ?? '') === 'sortie') {
            $where[] = 'm.quantite < 0';
        }

        $sql = "
            SELECT
                m.*,
                a.reference,
                a.designation,
                a.unite,
                ROUND(COALESCE(m.quantite, 0) * COALESCE(m.prix_achat_ht, 0), 2) AS valeur_mouvement_ht
            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));

        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function movementSummary(array $filters = []): array
    {
        $rows = $this->allMovements($filters, 1000);
        $summary = [
            'total_mouvements' => count($rows),
            'entrees' => 0.0,
            'sorties' => 0.0,
            'valeur_entrees_ht' => 0.0,
            'valeur_sorties_ht' => 0.0,
            'inventaires' => 0,
            'ajustements' => 0,
        ];

        foreach ($rows as $row) {
            $quantite = (float) ($row['quantite'] ?? 0);
            $valeur = (float) ($row['valeur_mouvement_ht'] ?? 0);
            $type = (string) ($row['type_mouvement'] ?? '');

            if ($quantite >= 0) {
                $summary['entrees'] += $quantite;
                $summary['valeur_entrees_ht'] += max(0, $valeur);
            } else {
                $summary['sorties'] += abs($quantite);
                $summary['valeur_sorties_ht'] += abs($valeur);
            }

            if ($type === 'INV') {
                $summary['inventaires']++;
            }
            if ($type === 'ADJ') {
                $summary['ajustements']++;
            }
        }

        return $summary;
    }

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

        return $this->db->query("
            SELECT DISTINCT origine
            FROM mouvements_stock
            WHERE origine IS NOT NULL AND origine <> ''
            ORDER BY origine ASC
        ")->fetchAll(PDO::FETCH_COLUMN);
    }

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

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

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

        $stmt = $this->db->prepare('SELECT quantite FROM stocks WHERE article_id = :article_id LIMIT 1');
        $stmt->execute(['article_id' => $articleId]);
        return (float) ($stmt->fetchColumn() ?: 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->db->prepare($sql)->execute([
            '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);

        $stmt = $this->db->prepare("
            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()
        ");
        $stmt->execute([
            'article_id' => $articleId,
            'quantite' => $quantity,
            'stock_minimum' => $stockMinimum,
            'valeur_stock' => $value,
        ]);

        if ($this->tableExists('articles')) {
            $sync = $this->db->prepare('UPDATE articles SET stock_minimum = :stock_minimum WHERE id = :article_id');
            $sync->execute([
                'article_id' => $articleId,
                'stock_minimum' => $stockMinimum,
            ]);
        }
    }

public function updateStockValuation(
    int $articleId,
    float $quantity,
    float $stockMinimum,
    float $stockValue
): void {
    $quantity = round($quantity, 0);
    $stockMinimum = round(max(0, $stockMinimum), 0);
    $stockValue = round(max(0, $stockValue), 2);

    $stmt = $this->db->prepare("
        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()
    ");

    $stmt->execute([
        'article_id' => $articleId,
        'quantite' => $quantity,
        'stock_minimum' => $stockMinimum,
        'valeur_stock' => $stockValue,
    ]);

    if ($this->tableExists('articles')) {
        $sync = $this->db->prepare("
            UPDATE articles
            SET stock_minimum = :stock_minimum
            WHERE id = :article_id
        ");

        $sync->execute([
            'article_id' => $articleId,
            'stock_minimum' => $stockMinimum,
        ]);
    }
}

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

        $this->initializeMissingRows();

        return $this->db->query("
            SELECT
                a.id AS article_id,
                a.reference,
                a.designation,
                a.prix_achat_ht,
                a.unite,
                COALESCE(s.quantite, 0) AS stock_theorique,
                COALESCE(s.stock_minimum, 0) AS stock_minimum,
                COALESCE(s.valeur_stock, 0) AS valeur_stock
            FROM articles a
            LEFT JOIN stocks s ON s.article_id = a.id
            WHERE a.actif = 1
            ORDER BY a.designation ASC
        ")->fetchAll(PDO::FETCH_ASSOC);
    }

    public function inventories(int $limit = 20): array
    {
        if (!$this->tableExists('inventaires_stock')) {
            return [];
        }

        return $this->db->query("
            SELECT *
            FROM inventaires_stock
            ORDER BY date_inventaire DESC, id DESC
            LIMIT " . max(1, min(100, $limit)) . "
        ")->fetchAll(PDO::FETCH_ASSOC);
    }

    public function inventoryWithLines(int $id): ?array
    {
        if (!$this->tableExists('inventaires_stock') || !$this->tableExists('inventaires_stock_lignes')) {
            return null;
        }

        $stmt = $this->db->prepare('SELECT * FROM inventaires_stock WHERE id = :id LIMIT 1');
        $stmt->execute(['id' => $id]);
        $inventory = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$inventory) {
            return null;
        }

        $stmt = $this->db->prepare("
            SELECT l.*, a.reference, a.designation, a.unite
            FROM inventaires_stock_lignes l
            INNER JOIN articles a ON a.id = l.article_id
            WHERE l.inventaire_id = :id
            ORDER BY a.designation ASC
        ");
        $stmt->execute(['id' => $id]);
        $inventory['lignes'] = $stmt->fetchAll(PDO::FETCH_ASSOC);

        return $inventory;
    }

    public function createInventoryHeader(array $data): int
    {
        $stmt = $this->db->prepare("
            INSERT INTO inventaires_stock (
                date_inventaire, commentaire, nb_articles, nb_ecarts,
                total_ecart_quantite, valeur_ecart_ht, utilisateur
            ) VALUES (
                NOW(), :commentaire, :nb_articles, :nb_ecarts,
                :total_ecart_quantite, :valeur_ecart_ht, :utilisateur
            )
        ");
        $stmt->execute([
            'commentaire' => $data['commentaire'] ?? null,
            'nb_articles' => $data['nb_articles'] ?? 0,
            'nb_ecarts' => $data['nb_ecarts'] ?? 0,
            'total_ecart_quantite' => $data['total_ecart_quantite'] ?? 0,
            'valeur_ecart_ht' => $data['valeur_ecart_ht'] ?? 0,
            'utilisateur' => $data['utilisateur'] ?? 'Admin',
        ]);
        return (int) $this->db->lastInsertId();
    }

    public function createInventoryLine(array $data): void
    {
        $stmt = $this->db->prepare("
            INSERT INTO inventaires_stock_lignes (
                inventaire_id, article_id, stock_theorique, stock_reel,
                ecart, prix_achat_ht, valeur_ecart_ht
            ) VALUES (
                :inventaire_id, :article_id, :stock_theorique, :stock_reel,
                :ecart, :prix_achat_ht, :valeur_ecart_ht
            )
        ");
        $stmt->execute([
            'inventaire_id' => $data['inventaire_id'],
            'article_id' => $data['article_id'],
            'stock_theorique' => $data['stock_theorique'],
            'stock_reel' => $data['stock_reel'],
            'ecart' => $data['ecart'],
            'prix_achat_ht' => $data['prix_achat_ht'],
            'valeur_ecart_ht' => $data['valeur_ecart_ht'],
        ]);
    }


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

        $this->initializeMissingRows();

        return $this->db->query("
            SELECT
                a.id AS article_id,
                a.reference,
                a.designation,
                a.unite,
                COALESCE(s.quantite, 0) AS quantite,
                COALESCE(a.prix_achat_ht, 0) AS prix_achat_ht,
                COALESCE(a.prix_vente_ht, 0) AS prix_vente_ht,
                ROUND(COALESCE(s.quantite, 0) * COALESCE(a.prix_achat_ht, 0), 2) AS valeur_achat_ht,
                ROUND(COALESCE(s.quantite, 0) * COALESCE(a.prix_vente_ht, 0), 2) AS valeur_vente_ht,
                ROUND((COALESCE(s.quantite, 0) * COALESCE(a.prix_vente_ht, 0)) - (COALESCE(s.quantite, 0) * COALESCE(a.prix_achat_ht, 0)), 2) AS marge_theorique_ht,
                CASE
                    WHEN COALESCE(s.quantite, 0) * COALESCE(a.prix_achat_ht, 0) > 0
                    THEN ROUND((((COALESCE(s.quantite, 0) * COALESCE(a.prix_vente_ht, 0)) - (COALESCE(s.quantite, 0) * COALESCE(a.prix_achat_ht, 0))) / (COALESCE(s.quantite, 0) * COALESCE(a.prix_achat_ht, 0))) * 100, 2)
                    ELSE 0
                END AS taux_marge_theorique,
                COALESCE(s.date_maj, '') AS date_maj
            FROM articles a
            LEFT JOIN stocks s ON s.article_id = a.id
            WHERE a.actif = 1
            ORDER BY valeur_achat_ht DESC, a.designation ASC
        ")->fetchAll(PDO::FETCH_ASSOC);
    }

    public function valuationSummary(): array
    {
        $rows = $this->valuationRows();
        $summary = [
            'total_articles' => count($rows),
            'valeur_achat_ht' => 0.0,
            'valeur_vente_ht' => 0.0,
            'marge_theorique_ht' => 0.0,
            'taux_marge_theorique' => 0.0,
            'articles_en_stock' => 0,
            'articles_valeur_forte' => 0,
        ];

        foreach ($rows as $row) {
            $quantite = (float) ($row['quantite'] ?? 0);
            $valeurAchat = (float) ($row['valeur_achat_ht'] ?? 0);
            $summary['valeur_achat_ht'] += $valeurAchat;
            $summary['valeur_vente_ht'] += (float) ($row['valeur_vente_ht'] ?? 0);
            $summary['marge_theorique_ht'] += (float) ($row['marge_theorique_ht'] ?? 0);
            if ($quantite > 0) {
                $summary['articles_en_stock']++;
            }
            if ($valeurAchat >= 1000) {
                $summary['articles_valeur_forte']++;
            }
        }

        if ($summary['valeur_achat_ht'] > 0) {
            $summary['taux_marge_theorique'] = round(($summary['marge_theorique_ht'] / $summary['valeur_achat_ht']) * 100, 2);
        }

        return $summary;
    }

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

	public function transaction(callable $callback): mixed
	{
		$startedHere = false;

		if (!$this->db->inTransaction()) {
			$this->db->beginTransaction();
			$startedHere = true;
		}

		try {
			$result = $callback();

			if ($startedHere && $this->db->inTransaction()) {
				$this->db->commit();
			}

			return $result;
		} catch (\Throwable $exception) {
			if ($startedHere && $this->db->inTransaction()) {
				$this->db->rollBack();
			}

			throw $exception;
		}
	}
}
