<?php

namespace WMS\Repositories;

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

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

        $stmt = $this->db->prepare(
            "SELECT cl.*, i.item_code, i.item_name, bt.batch_number, bt.expiry_date
             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([$id]);
        $container['lines'] = $stmt->fetchAll();

        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.
     */
    public function getByWarehouse(int $warehouseId, ?string $status = null): array
    {
        $sql = "SELECT c.*, w.code AS warehouse_code,
                       (SELECT COUNT(*) FROM container_lines cl WHERE cl.container_id = c.id) AS line_count
                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();
    }
}