<?php

namespace WMS\Repositories;

class BinRepository extends BaseRepository
{
    protected string $table = 'bins';

    public function findByCode(int $warehouseId, string $code): ?array
    {
        $stmt = $this->db->prepare(
            "SELECT * FROM bins WHERE warehouse_id = ? AND code = ?"
        );
        $stmt->execute([$warehouseId, $code]);
        return $stmt->fetch() ?: null;
    }

    public function getByWarehouse(int $warehouseId, ?string $binType = null): array
    {
        $sql = "SELECT * FROM bins WHERE warehouse_id = ? AND is_active = 1";
        $params = [$warehouseId];

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

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

    /**
     * Lista ubicaciones de un almacen con filtros opcionales.
     * Filtros: bin_type, zone_id, active (0/1), q (busqueda en code/sap_bin_code).
     */
    public function getByWarehouseFiltered(int $warehouseId, array $filters): array
    {
        return $this->buildList(array_merge(['warehouse_id' => $warehouseId], $filters));
    }

    /**
     * Lista global con filtros (todos los almacenes).
     */
    public function listAll(array $filters): array
    {
        return $this->buildList($filters);
    }

    private function buildList(array $f): array
    {
        $sql = "SELECT b.*, w.code AS warehouse_code, w.name AS warehouse_name,
                       z.code AS zone_code, z.name AS zone_name
                FROM bins b
                LEFT JOIN warehouses w ON w.id = b.warehouse_id
                LEFT JOIN warehouse_zones z ON z.id = b.zone_id
                WHERE 1=1";
        $params = [];
        if (!empty($f['warehouse_id'])) {
            $sql .= " AND b.warehouse_id = ?";
            $params[] = (int) $f['warehouse_id'];
        }
        if (!empty($f['zone_id'])) {
            $sql .= " AND b.zone_id = ?";
            $params[] = (int) $f['zone_id'];
        }
        if (!empty($f['bin_type'])) {
            $sql .= " AND b.bin_type = ?";
            $params[] = (string) $f['bin_type'];
        }
        if (isset($f['active']) && $f['active'] !== '' && $f['active'] !== null) {
            $sql .= " AND b.is_active = ?";
            $params[] = (int) (bool) $f['active'];
        }
        if (!empty($f['q'])) {
            $sql .= " AND (b.code LIKE ? OR b.sap_bin_code LIKE ?)";
            $like = '%' . $f['q'] . '%';
            $params[] = $like;
            $params[] = $like;
        }
        $sql .= " ORDER BY b.warehouse_id, b.code LIMIT 5000";
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    /**
     * Update con whitelist de columnas y casts. Ignora claves desconocidas.
     */
    public function updateById(int $id, array $data): bool
    {
        $allowed = [
            'warehouse_id', 'zone_id', 'code', 'aisle', 'rack', 'level', 'position',
            'bin_type', 'max_weight_kg', 'max_volume_m3', 'is_active', 'sap_bin_code',
        ];
        $clean = [];
        foreach ($allowed as $k) {
            if (!array_key_exists($k, $data)) continue;
            $v = $data[$k];
            if ($v === '' && $k !== 'code') $v = null;
            if ($v !== null) {
                if ($k === 'warehouse_id' || $k === 'zone_id') $v = (int) $v;
                if ($k === 'is_active') $v = (int) (bool) $v;
                if ($k === 'code') $v = trim((string) $v);
            }
            $clean[$k] = $v;
        }
        if (empty($clean)) return true;
        $set = implode(', ', array_map(fn($c) => "`$c` = ?", array_keys($clean)));
        $sql = "UPDATE bins SET {$set} WHERE id = ?";
        $params = array_values($clean);
        $params[] = $id;
        return $this->db->prepare($sql)->execute($params);
    }

    public function setActive(int $id, bool $active): bool
    {
        $stmt = $this->db->prepare("UPDATE bins SET is_active = ? WHERE id = ?");
        return $stmt->execute([$active ? 1 : 0, $id]);
    }

    /**
     * Indica si la ubicacion tiene stock o tareas pendientes que la referencian.
     * Bloquea desactivacion para preservar integridad operativa.
     */
    public function hasActiveStock(int $binId): bool
    {
        $sql = "SELECT
                  (SELECT COUNT(*) FROM stock WHERE bin_id = ? AND quantity > 0)
                  + (SELECT COUNT(*) FROM warehouse_tasks WHERE (from_bin_id = ? OR to_bin_id = ?) AND status IN ('PENDING','ASSIGNED','IN_PROGRESS'))
                AS refs";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$binId, $binId, $binId]);
        return (int) $stmt->fetchColumn() > 0;
    }

    /**
     * Obtiene bins de tipo RECEIVING para un almacen.
     */
    public function getReceivingBins(int $warehouseId): array
    {
        return $this->getByWarehouse($warehouseId, 'RECEIVING');
    }

    /**
     * Sugiere un bin de storage disponible para putaway.
     */
    public function suggestStorageBin(int $warehouseId, int $itemId): ?array
    {
        $sql = "SELECT b.* FROM bins b
                WHERE b.warehouse_id = ? AND b.bin_type = 'STORAGE' AND b.is_active = 1
                ORDER BY b.code ASC LIMIT 1";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$warehouseId]);
        return $stmt->fetch() ?: null;
    }
}