<?php

declare(strict_types=1);

namespace App\Repositories;

final class ReceptionFournisseurRepository extends BaseRepository
{
    public function ensureSchema(): void
    {
        $this->execute("CREATE TABLE IF NOT EXISTS receptions_fournisseurs (
            id INT AUTO_INCREMENT PRIMARY KEY,
            commande_id INT NOT NULL,
            numero VARCHAR(40) NOT NULL,
            date_reception DATE NOT NULL,
            reference_bl VARCHAR(100) NULL,
            observations TEXT NULL,
            statut VARCHAR(20) NOT NULL DEFAULT 'active',
            date_annulation DATETIME NULL,
            motif_annulation TEXT NULL,
            date_creation DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY uk_reception_numero (numero),
            INDEX idx_reception_commande (commande_id),
            INDEX idx_reception_date (date_reception),
            INDEX idx_reception_statut (statut)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        $this->execute("CREATE TABLE IF NOT EXISTS receptions_fournisseurs_lignes (
            id INT AUTO_INCREMENT PRIMARY KEY,
            reception_id INT NOT NULL,
            commande_ligne_id INT NOT NULL,
            article_id INT NULL,
            quantite DECIMAL(12,3) NOT NULL,
            prix_achat_ht DECIMAL(12,4) NOT NULL DEFAULT 0,
            mouvement_stock_id INT NULL,
            INDEX idx_reception_ligne_reception (reception_id),
            INDEX idx_reception_ligne_commande (commande_ligne_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
    }

    public function nextNumber(): string
    {
        $this->ensureSchema();
        $prefix = 'RF' . date('Y') . '-';
        $last = $this->fetchColumn(
            'SELECT numero FROM receptions_fournisseurs WHERE numero LIKE :prefix ORDER BY id DESC LIMIT 1',
            ['prefix' => $prefix . '%']
        );
        $sequence = $last ? ((int)substr((string)$last, strlen($prefix)) + 1) : 1;
        return $prefix . str_pad((string)$sequence, 6, '0', STR_PAD_LEFT);
    }

    public function all(array $filters = []): array
    {
        $this->ensureSchema();
        $where = [];
        $params = [];
        if (!empty($filters['fournisseur_id'])) {
            $where[] = 'cf.fournisseur_id = :fournisseur_id';
            $params['fournisseur_id'] = (int)$filters['fournisseur_id'];
        }
        if (!empty($filters['statut'])) {
            $where[] = 'r.statut = :statut';
            $params['statut'] = (string)$filters['statut'];
        }
        if (!empty($filters['date_from'])) {
            $where[] = 'r.date_reception >= :date_from';
            $params['date_from'] = (string)$filters['date_from'];
        }
        if (!empty($filters['date_to'])) {
            $where[] = 'r.date_reception <= :date_to';
            $params['date_to'] = (string)$filters['date_to'];
        }
        if (!empty($filters['q'])) {
            $where[] = '(r.numero LIKE :q OR r.reference_bl LIKE :q OR cf.numero LIKE :q OR f.raison_sociale LIKE :q)';
            $params['q'] = '%' . trim((string)$filters['q']) . '%';
        }

        return $this->fetchAll(
            "SELECT r.*, cf.numero AS commande_numero, cf.id AS commande_id,
                    f.raison_sociale AS fournisseur_nom,
                    COALESCE(SUM(rl.quantite), 0) AS quantite_totale
             FROM receptions_fournisseurs r
             INNER JOIN commandes_fournisseurs cf ON cf.id = r.commande_id
             INNER JOIN fournisseurs f ON f.id = cf.fournisseur_id
             LEFT JOIN receptions_fournisseurs_lignes rl ON rl.reception_id = r.id
             " . ($where ? 'WHERE ' . implode(' AND ', $where) : '') . "
             GROUP BY r.id, cf.numero, cf.id, f.raison_sociale
             ORDER BY r.date_reception DESC, r.id DESC",
            $params
        );
    }

    public function forCommand(int $commandeId): array
    {
        $this->ensureSchema();
        return $this->fetchAll(
            "SELECT r.*, COALESCE(SUM(rl.quantite), 0) AS quantite_totale
             FROM receptions_fournisseurs r
             LEFT JOIN receptions_fournisseurs_lignes rl ON rl.reception_id = r.id
             WHERE r.commande_id = :id
             GROUP BY r.id ORDER BY r.date_reception DESC, r.id DESC",
            ['id' => $commandeId]
        );
    }

    public function create(int $commandeId, array $data, array $quantities): array
    {
        $this->ensureSchema();
        $numero = trim((string)($data['numero'] ?? '')) ?: $this->nextNumber();
        $commande = $this->fetchOne('SELECT * FROM commandes_fournisseurs WHERE id = :id', ['id' => $commandeId]);
        if (!$commande || (int)$commande['actif'] !== 1) {
            throw new \RuntimeException('Commande fournisseur introuvable ou archivée.');
        }
        if (!in_array((string)$commande['statut'], ['validée', 'partiellement_reçue'], true)) {
            throw new \RuntimeException('La commande doit être validée et avoir des quantités restantes.');
        }

        $lines = $this->fetchAll(
            'SELECT * FROM commandes_fournisseurs_lignes WHERE commande_id = :id ORDER BY ordre, id',
            ['id' => $commandeId]
        );
        $prepared = [];
        foreach ($lines as $line) {
            $qty = round((float)($quantities[(int)$line['id']] ?? 0), 3);
            $remaining = round((float)$line['quantite'] - (float)$line['quantite_recue'], 3);
            if ($qty < 0 || $qty > $remaining) {
                throw new \RuntimeException('Une quantité reçue est supérieure au reste à recevoir.');
            }
            if ($qty > 0) {
                $prepared[] = [$line, $qty];
            }
        }
        if (!$prepared) {
            throw new \RuntimeException('Saisissez au moins une quantité reçue.');
        }

        $this->beginTransaction();
        try {
            $this->execute(
                "INSERT INTO receptions_fournisseurs
                 (commande_id, numero, date_reception, reference_bl, observations)
                 VALUES (:commande_id, :numero, :date_reception, :reference_bl, :observations)",
                [
                    'commande_id' => $commandeId,
                    'numero' => $numero,
                    'date_reception' => (string)($data['date_reception'] ?? date('Y-m-d')),
                    'reference_bl' => trim((string)($data['reference_bl'] ?? '')),
                    'observations' => trim((string)($data['observations'] ?? '')),
                ]
            );
            $receptionId = $this->lastInsertId();
            $stockAdded = 0.0;
            foreach ($prepared as [$line, $qty]) {
                $movementId = null;
                if ((int)$line['article_id'] > 0) {
                    $movementId = $this->addStock(
                        (int)$line['article_id'],
                        $qty,
                        (float)$line['prix_achat_ht'],
                        $receptionId,
                        $numero
                    );
                    $stockAdded += $qty;
                }
                $this->execute(
                    "INSERT INTO receptions_fournisseurs_lignes
                     (reception_id, commande_ligne_id, article_id, quantite, prix_achat_ht, mouvement_stock_id)
                     VALUES (:reception_id, :commande_ligne_id, :article_id, :quantite, :prix, :mouvement_id)",
                    [
                        'reception_id' => $receptionId,
                        'commande_ligne_id' => (int)$line['id'],
                        'article_id' => (int)$line['article_id'] ?: null,
                        'quantite' => $qty,
                        'prix' => (float)$line['prix_achat_ht'],
                        'mouvement_id' => $movementId,
                    ]
                );
                $this->execute(
                    'UPDATE commandes_fournisseurs_lignes SET quantite_recue = quantite_recue + :qty WHERE id = :id',
                    ['qty' => $qty, 'id' => (int)$line['id']]
                );
            }
            $status = $this->refreshCommandStatus($commandeId);
            $this->commit();
            return ['id' => $receptionId, 'numero' => $numero, 'stock_added' => $stockAdded, 'statut' => $status];
        } catch (\Throwable $exception) {
            $this->rollBack();
            throw $exception;
        }
    }

    public function cancel(int $id, string $reason): int
    {
        $this->ensureSchema();
        $reception = $this->fetchOne('SELECT * FROM receptions_fournisseurs WHERE id = :id', ['id' => $id]);
        if (!$reception || $reception['statut'] !== 'active') {
            throw new \RuntimeException('Réception introuvable ou déjà annulée.');
        }
        if (trim($reason) === '') {
            throw new \RuntimeException('Le motif d’annulation est obligatoire.');
        }
        $lines = $this->fetchAll('SELECT * FROM receptions_fournisseurs_lignes WHERE reception_id = :id', ['id' => $id]);

        $this->beginTransaction();
        try {
            foreach ($lines as $line) {
                $qty = (float)$line['quantite'];
                if ((int)$line['article_id'] > 0) {
                    $this->removeStock((int)$line['article_id'], $qty, (float)$line['prix_achat_ht'], $id, (string)$reception['numero']);
                }
                $this->execute(
                    'UPDATE commandes_fournisseurs_lignes SET quantite_recue = GREATEST(quantite_recue - :qty, 0) WHERE id = :id',
                    ['qty' => $qty, 'id' => (int)$line['commande_ligne_id']]
                );
            }
            $this->execute(
                "UPDATE receptions_fournisseurs SET statut = 'annulée', date_annulation = NOW(), motif_annulation = :motif WHERE id = :id",
                ['motif' => trim($reason), 'id' => $id]
            );
            $this->refreshCommandStatus((int)$reception['commande_id']);
            $this->commit();
            return (int)$reception['commande_id'];
        } catch (\Throwable $exception) {
            $this->rollBack();
            throw $exception;
        }
    }

    private function refreshCommandStatus(int $commandeId): string
    {
        $totals = $this->fetchOne(
            'SELECT SUM(quantite) AS ordered_qty, SUM(quantite_recue) AS received_qty
             FROM commandes_fournisseurs_lignes WHERE commande_id = :id',
            ['id' => $commandeId]
        ) ?? [];
        $ordered = (float)($totals['ordered_qty'] ?? 0);
        $received = (float)($totals['received_qty'] ?? 0);
        $status = $received <= 0 ? 'validée' : ($received + 0.0001 >= $ordered ? 'reçue' : 'partiellement_reçue');
        $this->execute(
            'UPDATE commandes_fournisseurs SET statut = :statut, est_receptionnee = :complete WHERE id = :id',
            ['statut' => $status, 'complete' => $status === 'reçue' ? 1 : 0, 'id' => $commandeId]
        );
        return $status;
    }

    private function addStock(int $articleId, float $qty, float $price, int $receptionId, string $numero): int
    {
        $stock = $this->fetchOne('SELECT * FROM stocks WHERE article_id = :id LIMIT 1', ['id' => $articleId]) ?? [];
        $newQty = round((float)($stock['quantite'] ?? 0) + $qty, 3);
        $newValue = round((float)($stock['valeur_stock'] ?? 0) + $qty * $price, 2);
        $this->execute(
            "INSERT INTO stocks (article_id, quantite, stock_minimum, valeur_stock, date_maj)
             VALUES (:id, :qty, :minimum, :value, NOW())
             ON DUPLICATE KEY UPDATE quantite=VALUES(quantite), valeur_stock=VALUES(valeur_stock), date_maj=NOW()",
            ['id' => $articleId, 'qty' => $newQty, 'minimum' => (float)($stock['stock_minimum'] ?? 0), 'value' => $newValue]
        );
        $this->execute(
            "INSERT INTO mouvements_stock
             (article_id, type_mouvement, quantite, prix_achat_ht, origine, origine_id, commentaire, utilisateur)
             VALUES (:article, 'ENT', :qty, :prix, 'RECEPTION_FOURNISSEUR', :origine, :commentaire, 'Admin')",
            ['article' => $articleId, 'qty' => $qty, 'prix' => $price, 'origine' => $receptionId, 'commentaire' => 'Réception fournisseur ' . $numero]
        );
        return $this->lastInsertId();
    }

    private function removeStock(int $articleId, float $qty, float $price, int $receptionId, string $numero): void
    {
        $stock = $this->fetchOne('SELECT * FROM stocks WHERE article_id = :id LIMIT 1', ['id' => $articleId]) ?? [];
        $currentQty = (float)($stock['quantite'] ?? 0);
        if ($currentQty + 0.0001 < $qty) {
            throw new \RuntimeException('Annulation impossible : le stock disponible est inférieur à la quantité à retirer.');
        }
        $newQty = round($currentQty - $qty, 3);
        $newValue = max(0, round((float)($stock['valeur_stock'] ?? 0) - $qty * $price, 2));
        $this->execute(
            'UPDATE stocks SET quantite = :qty, valeur_stock = :value, date_maj = NOW() WHERE article_id = :id',
            ['qty' => $newQty, 'value' => $newValue, 'id' => $articleId]
        );
        $this->execute(
            "INSERT INTO mouvements_stock
             (article_id, type_mouvement, quantite, prix_achat_ht, origine, origine_id, commentaire, utilisateur)
             VALUES (:article, 'SOR', :qty, :prix, 'ANNULATION_RECEPTION_FOURNISSEUR', :origine, :commentaire, 'Admin')",
            ['article' => $articleId, 'qty' => $qty, 'prix' => $price, 'origine' => $receptionId, 'commentaire' => 'Annulation réception fournisseur ' . $numero]
        );
    }
}
