<?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,
                    t.id AS task_id, t.status AS task_status, t.assigned_to AS task_assigned_to
             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
             LEFT JOIN warehouse_tasks t
                    ON t.reference_type = 'PICK_LIST'
                   AND t.reference_id = pl.pick_list_id
                   AND t.task_type = 'PICK'
                   AND t.item_id = pl.item_id
                   AND (t.batch_id <=> pl.batch_id)
                   AND (t.from_bin_id <=> pl.from_bin_id)
                   AND t.status NOT IN ('COMPLETED','CANCELLED')
             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();
    }

    /**
     * Lista pick lists recientes del almacen sin filtrar por estado.
     */
    public function getRecentByWarehouse(int $warehouseId, ?string $status = null, int $days = 30, int $limit = 100): array
    {
        $where = ['pl.warehouse_id = ?', 'pl.created_at >= DATE_SUB(NOW(), INTERVAL ? DAY)'];
        $args  = [$warehouseId, $days];
        if ($status !== null && $status !== '') {
            $where[] = 'pl.status = ?';
            $args[]  = $status;
        }
        $sql = "SELECT pl.*,
                       (SELECT COUNT(*) FROM pick_list_lines WHERE pick_list_id = pl.id) AS line_count,
                       (SELECT COUNT(*) FROM pick_list_lines WHERE pick_list_id = pl.id AND status='PICKED') AS picked_lines
                FROM pick_lists pl
                WHERE " . implode(' AND ', $where) . "
                ORDER BY pl.id DESC
                LIMIT " . (int)$limit;
        $stmt = $this->db->prepare($sql);
        $stmt->execute($args);
        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();
    }
}