<?php

namespace WMS\Repositories;

/**
 * Repositorio de Contenedores / LPN.
 */
class ContainerRepository extends BaseRepository
{
    protected string $table = 'containers';

    /**
     * Subquery que deriva la ubicación física (bin) actual del LPN a partir del
     * stock de sus líneas. Un contenedor no tiene bin propio en el esquema: su
     * ubicación = el/los bin(s) donde hoy reside el stock (item+lote) de sus líneas.
     *
     * Optima: el JOIN a stock usa el índice idx_stock_wh_item (warehouse_id, item_id)
     * y la búsqueda en container_lines usa uq_container_line (container_id, ...).
     *
     * %WH% y %CID% se reemplazan por la referencia al contenedor (alias SQL o '?').
     */
    private const SQL_LOCATION =
        "(SELECT GROUP_CONCAT(DISTINCT bb.code ORDER BY bb.code SEPARATOR ', ')
            FROM container_lines clx
            JOIN bins bb ON bb.id = COALESCE(
                 -- EXACTO: bin destino del último movimiento de ESTE contenedor para el item/lote
                 -- (putaway por contenedor = CONTAINER; reubicación = RELOCATION -> relocations.container_id)
                 (SELECT mm.to_bin_id
                    FROM stock_movements mm
                    LEFT JOIN relocations rl ON mm.reference_type = 'RELOCATION' AND rl.id = mm.reference_id
                   WHERE mm.item_id = clx.item_id
                     AND (mm.batch_id <=> clx.batch_id)
                     AND ( (mm.reference_type = 'CONTAINER'  AND mm.reference_id = clx.container_id)
                        OR (mm.reference_type = 'RELOCATION' AND rl.container_id   = clx.container_id) )
                   ORDER BY mm.id DESC LIMIT 1),
                 -- FALLBACK (LPN ubicado por flujo de tarea, sin movimiento atado al contenedor):
                 -- un solo bin STORAGE del item/lote, el de cantidad más parecida a la de la línea,
                 -- para no listar bins de OTROS LPN del mismo lote.
                 (SELECT s2.bin_id
                    FROM stock s2
                    JOIN bins b2 ON b2.id = s2.bin_id
                   WHERE s2.warehouse_id = %WH%
                     AND s2.item_id = clx.item_id
                     AND (clx.batch_id IS NULL OR s2.batch_id <=> clx.batch_id)
                     AND s2.quantity > 0
                     AND b2.bin_type = 'STORAGE'
                   ORDER BY ABS(s2.quantity - clx.quantity) ASC, s2.id ASC LIMIT 1)
            )
           WHERE clx.container_id = %CID% AND clx.quantity > 0)";

    /**
     * Obtiene un contenedor con sus líneas de detalle y su ubicación derivada.
     */
    public function getWithLines(int $id): ?array
    {
        $container = $this->findById($id);
        if (!$container) {
            return null;
        }

        // Por línea agregamos bin_code EXACTO: el bin destino del último movimiento de
        // ESTE contenedor para ese item/lote (putaway = reference_type CONTAINER; reubicación
        // = RELOCATION → relocations.container_id). Resuelve la ambigüedad de derivar la
        // ubicación por item/lote (que traía todos los bins del lote, no el de este LPN).
        $stmt = $this->db->prepare(
            "SELECT cl.*, i.item_code, i.item_name, bt.batch_number, bt.expiry_date,
                    COALESCE(
                      (SELECT bx.code
                         FROM stock_movements mm
                         LEFT JOIN relocations rl ON mm.reference_type = 'RELOCATION' AND rl.id = mm.reference_id
                         JOIN bins bx ON bx.id = mm.to_bin_id
                        WHERE mm.item_id = cl.item_id
                          AND (mm.batch_id <=> cl.batch_id)
                          AND ( (mm.reference_type = 'CONTAINER'  AND mm.reference_id = cl.container_id)
                             OR (mm.reference_type = 'RELOCATION' AND rl.container_id   = cl.container_id) )
                        ORDER BY mm.id DESC
                        LIMIT 1),
                      -- Fallback: LPN ubicado por el flujo de tarea (PUTAWAY reference_type='TASK'),
                      -- que no ata el bin al contenedor. Derivamos por el stock actual del item+lote
                      -- en almacén (qty>0). Si el lote está repartido en varios bins (varios LPN del
                      -- mismo lote), elegimos UN solo bin: el de cantidad más parecida a la de esta
                      -- línea, para no listar bins de OTROS LPN del mismo lote (evita falsos 'compartido').
                      (SELECT bn2.code
                         FROM stock s2
                         JOIN bins bn2 ON bn2.id = s2.bin_id
                        WHERE s2.warehouse_id = ?
                          AND s2.item_id = cl.item_id
                          AND (cl.batch_id IS NULL OR s2.batch_id <=> cl.batch_id)
                          AND s2.quantity > 0
                          AND bn2.bin_type = 'STORAGE'
                        ORDER BY ABS(s2.quantity - cl.quantity) ASC, s2.id ASC
                        LIMIT 1)
                    ) AS bin_code
             FROM container_lines cl
             JOIN items i ON cl.item_id = i.id
             LEFT JOIN batches bt ON cl.batch_id = bt.id
             WHERE cl.container_id = ?
             ORDER BY cl.line_num"
        );
        $stmt->execute([(int) $container['warehouse_id'], $id]);
        $container['lines'] = $stmt->fetchAll();

        // Ubicación EXACTA del LPN: bins destino de sus líneas (sin duplicados).
        $exact = [];
        foreach ($container['lines'] as $ln) {
            if (!empty($ln['bin_code'])) { $exact[$ln['bin_code']] = true; }
        }
        $container['location'] = $exact ? implode(', ', array_keys($exact)) : null;

        // Ubicación derivada del stock actual del LPN (compat: campo histórico bin_code).
        $locSql = str_replace(['%WH%', '%CID%'], ['?', '?'], self::SQL_LOCATION);
        $locStmt = $this->db->prepare("SELECT " . $locSql . " AS bin_code");
        $locStmt->execute([(int) $container['warehouse_id'], $id]);
        $container['bin_code'] = $locStmt->fetchColumn() ?: null;

        return $container;
    }

    /**
     * Busca un contenedor por su código LPN.
     */
    public function findByCode(string $code): ?array
    {
        $stmt = $this->db->prepare("SELECT * FROM {$this->table} WHERE container_code = ?");
        $stmt->execute([$code]);
        return $stmt->fetch() ?: null;
    }

    /**
     * Obtiene contenedores de un almacén, opcionalmente filtrados por estado.
     * Incluye bin_code derivado (ubicación actual del stock del LPN).
     */
    public function getByWarehouse(int $warehouseId, ?string $status = null): array
    {
        $locSql = str_replace(['%WH%', '%CID%'], ['c.warehouse_id', 'c.id'], self::SQL_LOCATION);

        $sql = "SELECT c.*, w.code AS warehouse_code,
                       (SELECT COUNT(*) FROM container_lines cl WHERE cl.container_id = c.id) AS line_count,
                       " . $locSql . " AS bin_code
                FROM containers c
                JOIN warehouses w ON c.warehouse_id = w.id
                WHERE c.warehouse_id = ?";
        $params = [$warehouseId];

        if ($status) {
            $sql .= " AND c.status = ?";
            $params[] = $status;
        }

        $sql .= " ORDER BY c.created_at DESC";
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }
}