<?php

namespace WMS\Repositories;

class DispatchRepository extends BaseRepository
{
    protected string $table = 'dispatches';

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

        $stmt = $this->db->prepare(
            "SELECT dl.*, po.pack_number, po.status AS packing_status,
                    po.sap_delivery_num, po.sap_delivery_entry
             FROM dispatch_lines dl
             LEFT JOIN packing_orders po ON dl.packing_order_id = po.id
             WHERE dl.dispatch_id = ?
             ORDER BY dl.line_num"
        );
        $stmt->execute([$dispatchId]);
        $doc['lines'] = $stmt->fetchAll();

        return $doc;
    }

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

    /**
     * Lista dispatches recientes del almacen sin filtrar por estado.
     */
    public function getRecentByWarehouse(int $warehouseId, ?string $status = null, int $days = 60, int $limit = 100): array
    {
        $where = ['d.warehouse_id = ?', 'd.created_at >= DATE_SUB(NOW(), INTERVAL ? DAY)'];
        $args  = [$warehouseId, $days];
        if ($status !== null && $status !== '') {
            $where[] = 'd.status = ?';
            $args[]  = $status;
        }
        $sql = "SELECT d.*,
                       (SELECT COUNT(*) FROM dispatch_lines WHERE dispatch_id = d.id) AS order_count,
                       (SELECT GROUP_CONCAT(DISTINCT pl.sap_doc_num ORDER BY pl.sap_doc_num SEPARATOR ',')
                        FROM dispatch_lines dl
                        JOIN pick_lists pl ON pl.id = dl.pick_list_id
                        WHERE dl.dispatch_id = d.id AND pl.sap_doc_num IS NOT NULL) AS so_doc_nums
                FROM dispatches d
                WHERE " . implode(' AND ', $where) . "
                ORDER BY d.id DESC
                LIMIT " . (int)$limit;
        $stmt = $this->db->prepare($sql);
        $stmt->execute($args);
        return $stmt->fetchAll();
    }
}