<?php

namespace WMS\Repositories;

/**
 * Repositorio de Agenda de Recepcion.
 * Consultas especializadas sobre reception_agendas y tablas relacionadas.
 */
class AgendaRepository extends BaseRepository
{
    protected string $table = 'reception_agendas';

    /**
     * Obtiene una agenda con sus referencias, lineas y escaneos.
     */
    public function getWithDetails(int $id): ?array
    {
        $agenda = $this->findById($id);
        if (!$agenda) {
            return null;
        }

        // Referencias (OCs asociadas)
        $stmt = $this->db->prepare(
            "SELECT r.*
             FROM reception_agenda_refs r
             WHERE r.agenda_id = ?
             ORDER BY r.id"
        );
        $stmt->execute([$id]);
        $agenda['references'] = $stmt->fetchAll();

        // Lineas con datos de item
        $stmt = $this->db->prepare(
            "SELECT l.*,
                    r.sap_doc_num AS ref_doc_num,
                    r.supplier_name
             FROM reception_agenda_lines l
             LEFT JOIN reception_agenda_refs r ON l.ref_id = r.id
             WHERE l.agenda_id = ?
             ORDER BY l.ref_id, l.line_num"
        );
        $stmt->execute([$id]);
        $agenda['lines'] = $stmt->fetchAll();

        // Escaneos
        $stmt = $this->db->prepare(
            "SELECT s.*, u.full_name AS operator_name
             FROM reception_scans s
             LEFT JOIN users u ON s.operator_id = u.id
             WHERE s.agenda_id = ?
             ORDER BY s.scanned_at DESC"
        );
        $stmt->execute([$id]);
        $agenda['scans'] = $stmt->fetchAll();

        return $agenda;
    }

    /**
     * Lista agendas por almacen, opcionalmente filtradas por estado.
     */
    public function getByWarehouse(int $warehouseId, ?string $status = null): array
    {
        $sql = "SELECT a.*, u.full_name AS created_by_name,
                       (SELECT COUNT(*) FROM reception_agenda_refs WHERE agenda_id = a.id) AS ref_count,
                       (SELECT COUNT(*) FROM reception_agenda_lines WHERE agenda_id = a.id) AS line_count
                FROM reception_agendas a
                LEFT JOIN users u ON a.created_by = u.id
                WHERE a.warehouse_id = ?";
        $params = [$warehouseId];

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

        $sql .= " ORDER BY a.planned_date DESC, a.id DESC";
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    /**
     * Obtiene todas las lineas de una agenda con cantidades esperadas vs recibidas.
     */
    public function getLines(int $agendaId): array
    {
        $stmt = $this->db->prepare(
            "SELECT l.*,
                    r.sap_doc_num AS ref_doc_num,
                    r.supplier_name,
                    (l.received_qty - l.expected_qty) AS difference_qty
             FROM reception_agenda_lines l
             LEFT JOIN reception_agenda_refs r ON l.ref_id = r.id
             WHERE l.agenda_id = ?
             ORDER BY l.ref_id, l.line_num"
        );
        $stmt->execute([$agendaId]);
        return $stmt->fetchAll();
    }

    /**
     * Obtiene todos los escaneos de una agenda, agrupados por operador.
     */
    public function getScans(int $agendaId): array
    {
        $stmt = $this->db->prepare(
            "SELECT s.*, u.full_name AS operator_name
             FROM reception_scans s
             LEFT JOIN users u ON s.operator_id = u.id
             WHERE s.agenda_id = ?
             ORDER BY s.scanned_at DESC"
        );
        $stmt->execute([$agendaId]);
        return $stmt->fetchAll();
    }

    /**
     * Obtiene lineas donde la cantidad recibida difiere de la esperada.
     */
    public function getDifferences(int $agendaId): array
    {
        $stmt = $this->db->prepare(
            "SELECT l.item_code, l.item_name, l.expected_qty, l.received_qty,
                    (l.received_qty - l.expected_qty) AS difference_qty,
                    l.batch_number, l.status,
                    r.sap_doc_num AS ref_doc_num
             FROM reception_agenda_lines l
             LEFT JOIN reception_agenda_refs r ON l.ref_id = r.id
             WHERE l.agenda_id = ? AND l.received_qty != l.expected_qty
             ORDER BY l.item_code"
        );
        $stmt->execute([$agendaId]);
        return $stmt->fetchAll();
    }
}