<?php

namespace WMS\Repositories;

/**
 * Repositorio de Stock.
 * Consultas especializadas sobre inventario en tiempo real.
 */
class StockRepository extends BaseRepository
{
    protected string $table = 'stock';

    /**
     * Obtiene stock por item, opcionalmente filtrado por almacén, bin, lote, estado.
     */
    public function getStockByItem(int $itemId, ?int $warehouseId = null, ?string $status = null): array
    {
        $sql = "SELECT s.*, b.code AS bin_code, bt.batch_number, bt.expiry_date,
                       i.item_code, i.item_name, w.code AS warehouse_code
                FROM stock s
                JOIN bins b ON s.bin_id = b.id
                JOIN items i ON s.item_id = i.id
                JOIN warehouses w ON s.warehouse_id = w.id
                LEFT JOIN batches bt ON s.batch_id = bt.id
                WHERE s.item_id = ? AND s.quantity > 0";
        $params = [$itemId];

        if ($warehouseId) {
            $sql .= " AND s.warehouse_id = ?";
            $params[] = $warehouseId;
        }
        if ($status) {
            $sql .= " AND s.stock_status = ?";
            $params[] = $status;
        }

        $sql .= " ORDER BY bt.expiry_date ASC"; // FEFO
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    /**
     * Obtiene stock disponible para picking de un item, ordenado por FEFO.
     * Solo stock AVAILABLE con cantidad no reservada.
     */
    public function getAvailableForPicking(int $itemId, int $warehouseId): array
    {
        $sql = "SELECT s.*, b.code AS bin_code, bt.batch_number, bt.expiry_date,
                       (s.quantity - s.reserved_qty) AS available_qty
                FROM stock s
                JOIN bins b ON s.bin_id = b.id
                LEFT JOIN batches bt ON s.batch_id = bt.id
                WHERE s.item_id = ?
                  AND s.warehouse_id = ?
                  AND s.stock_status = 'AVAILABLE'
                  AND (s.quantity - s.reserved_qty) > 0
                ORDER BY bt.expiry_date ASC, s.id ASC"; // FEFO
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$itemId, $warehouseId]);
        return $stmt->fetchAll();
    }

    /**
     * Obtiene stock por bin location.
     */
    public function getStockByBin(int $binId): array
    {
        $sql = "SELECT s.*, i.item_code, i.item_name, bt.batch_number, bt.expiry_date
                FROM stock s
                JOIN items i ON s.item_id = i.id
                LEFT JOIN batches bt ON s.batch_id = bt.id
                WHERE s.bin_id = ? AND s.quantity > 0
                ORDER BY i.item_code, bt.expiry_date";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$binId]);
        return $stmt->fetchAll();
    }

    /**
     * Busca o crea un registro de stock para la combinación dada.
     * Retorna el ID del registro.
     */
    public function findOrCreatePosition(
        int $warehouseId,
        int $binId,
        int $itemId,
        ?int $batchId,
        string $status,
        string $uom = 'UN'
    ): int {
        $sql = "SELECT id FROM stock
                WHERE warehouse_id = ? AND bin_id = ? AND item_id = ?
                  AND batch_id <=> ? AND stock_status = ?";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$warehouseId, $binId, $itemId, $batchId, $status]);
        $row = $stmt->fetch();

        if ($row) {
            return (int) $row['id'];
        }

        return $this->insert([
            'warehouse_id' => $warehouseId,
            'bin_id'       => $binId,
            'item_id'      => $itemId,
            'batch_id'     => $batchId,
            'stock_status' => $status,
            'quantity'     => 0,
            'reserved_qty' => 0,
            'uom'          => $uom,
        ]);
    }

    /**
     * Incrementa la cantidad de un registro de stock.
     */
    public function addQuantity(int $stockId, float $qty): void
    {
        $this->db->prepare(
            "UPDATE stock SET quantity = quantity + ? WHERE id = ?"
        )->execute([$qty, $stockId]);
    }

    /**
     * Decrementa la cantidad de un registro de stock.
     */
    public function subtractQuantity(int $stockId, float $qty): void
    {
        $this->db->prepare(
            "UPDATE stock SET quantity = quantity - ? WHERE id = ? AND quantity >= ?"
        )->execute([$qty, $stockId, $qty]);
    }

    /**
     * Reserva cantidad para picking.
     */
    public function reserve(int $stockId, float $qty): bool
    {
        $stmt = $this->db->prepare(
            "UPDATE stock SET reserved_qty = reserved_qty + ?
             WHERE id = ? AND (quantity - reserved_qty) >= ?"
        );
        $stmt->execute([$qty, $stockId, $qty]);
        return $stmt->rowCount() > 0;
    }

    /**
     * Libera reserva de picking.
     */
    public function unreserve(int $stockId, float $qty): void
    {
        $this->db->prepare(
            "UPDATE stock SET reserved_qty = GREATEST(reserved_qty - ?, 0) WHERE id = ?"
        )->execute([$qty, $stockId]);
    }

    /**
     * Consulta resumen de stock por item y almacén.
     */
    public function getSummaryByWarehouse(int $warehouseId): array
    {
        $sql = "SELECT i.item_code, i.item_name, s.stock_status,
                       SUM(s.quantity) AS total_qty,
                       SUM(s.reserved_qty) AS total_reserved,
                       COUNT(DISTINCT s.bin_id) AS bin_count
                FROM stock s
                JOIN items i ON s.item_id = i.id
                WHERE s.warehouse_id = ? AND s.quantity > 0
                GROUP BY i.id, i.item_code, i.item_name, s.stock_status
                ORDER BY i.item_code, s.stock_status";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$warehouseId]);
        return $stmt->fetchAll();
    }

    /**
     * Detalle de stock por almacén: una fila por posición (bin/item/lote/estado).
     * Incluye bin_code, batch_number, expiry_date para mostrar en UI.
     */
    public function getDetailByWarehouse(int $warehouseId, ?string $stockStatus = null): array
    {
        $sql = "SELECT s.id, s.warehouse_id, s.bin_id, s.item_id, s.batch_id,
                       s.serial_number, s.stock_status, s.quantity, s.reserved_qty,
                       s.uom, s.created_at, s.updated_at,
                       bn.code AS bin_code, bn.bin_type,
                       b.batch_number, b.expiry_date,
                       i.item_code, i.item_name, i.barcode,
                       w.code AS warehouse_code
                FROM stock s
                JOIN items i ON i.id = s.item_id
                LEFT JOIN batches b ON b.id = s.batch_id
                LEFT JOIN bins bn ON bn.id = s.bin_id
                LEFT JOIN warehouses w ON w.id = s.warehouse_id
                WHERE s.warehouse_id = ? AND s.quantity > 0";
        $params = [$warehouseId];
        if ($stockStatus) {
            $sql .= " AND s.stock_status = ?";
            $params[] = $stockStatus;
        }
        $sql .= " ORDER BY i.item_code, bn.code";
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }
}