<?php

namespace WMS\Repositories;

class PickListRepository extends BaseRepository
{
    protected string $table = 'pick_lists';

    public function getWithLines(int $pickListId): ?array
    {
        $doc = $this->findById($pickListId);
        if (!$doc) {
            return null;
        }

        $stmt = $this->db->prepare(
            "SELECT pl.*, i.item_code, i.item_name, bt.batch_number, bt.expiry_date,
                    b.code AS from_bin_code
             FROM pick_list_lines pl
             JOIN items i ON pl.item_id = i.id
             LEFT JOIN batches bt ON pl.batch_id = bt.id
             LEFT JOIN bins b ON pl.from_bin_id = b.id
             WHERE pl.pick_list_id = ?
             ORDER BY pl.line_num"
        );
        $stmt->execute([$pickListId]);
        $doc['lines'] = $stmt->fetchAll();

        return $doc;
    }

    public function getOpenPickLists(int $warehouseId): array
    {
        $stmt = $this->db->prepare(
            "SELECT * FROM pick_lists
             WHERE warehouse_id = ? AND status IN ('OPEN','IN_PROGRESS')
             ORDER BY ship_date ASC, created_at ASC"
        );
        $stmt->execute([$warehouseId]);
        return $stmt->fetchAll();
    }

    /**
     * Pick lists del almacen aptas para crear packing:
     *  - status COMPLETED o IN_PROGRESS
     *  - sin packing_order activo (excluye los CANCELLED para permitir re-empaque)
     *  - tiene al menos 1 linea PICKED con qty > 0
     */
    public function getPackablePickLists(int $warehouseId): array
    {
        $stmt = $this->db->prepare(
            "SELECT pl.*,
                    (SELECT COUNT(*) FROM pick_list_lines
                     WHERE pick_list_id = pl.id AND status = 'PICKED' AND picked_qty > 0) AS picked_lines
             FROM pick_lists pl
             WHERE pl.warehouse_id = ?
               AND pl.status IN ('COMPLETED','IN_PROGRESS')
               AND NOT EXISTS (
                   SELECT 1 FROM packing_orders po
                   WHERE po.pick_list_id = pl.id AND po.status != 'CANCELLED'
               )
             HAVING picked_lines > 0
             ORDER BY pl.ship_date ASC, pl.created_at ASC"
        );
        $stmt->execute([$warehouseId]);
        return $stmt->fetchAll();
    }
}