<?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,
                    (SELECT t.id FROM warehouse_tasks t
                       WHERE t.reference_type = 'PICK_LIST'
                         AND t.reference_id = pl.pick_list_id
                         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 IN ('PENDING','ASSIGNED','IN_PROGRESS')
                       ORDER BY t.id DESC LIMIT 1) AS task_id,
                    (SELECT t.assigned_to FROM warehouse_tasks t
                       WHERE t.reference_type = 'PICK_LIST'
                         AND t.reference_id = pl.pick_list_id
                         AND t.item_id = pl.item_id
                         AND (t.batch_id <=> pl.batch_id)
                         AND (t.from_bin_id <=> pl.from_bin_id)
                       ORDER BY t.id DESC LIMIT 1) AS task_assignee
             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 getRecentPickLists(int $warehouseId, int $days = 30): array
    {
        $stmt = $this->db->prepare(
            "SELECT * FROM pick_lists
             WHERE warehouse_id = ?
               AND (status IN ('OPEN','IN_PROGRESS') OR created_at >= (NOW() - INTERVAL ? DAY))
             ORDER BY status='OPEN' DESC, status='IN_PROGRESS' DESC, created_at DESC"
        );
        $stmt->execute([$warehouseId, $days]);
        return $stmt->fetchAll();
    }

    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();
    }
}