<?php

namespace WMS\Integrations\Sap;

use WMS\Core\HanaDatabase;

/**
 * Consultas directas a tablas SAP Business One en HANA.
 * Permite leer datos maestros y transaccionales en tiempo real.
 *
 * Tablas principales SAP B1:
 * - OITM: Artículos
 * - OITB: Grupos de artículos
 * - OWHS: Almacenes
 * - OBIN: Bin Locations
 * - OBTN: Lotes (Batch Numbers)
 * - OIBQ: Stock por bin/lote
 * - OITW: Stock por almacén
 * - OPOR/POR1: Órdenes de compra
 * - ORDR/RDR1: Pedidos de venta
 * - OPDN/PDN1: Entradas de mercadería
 * - ODLN/DLN1: Entregas (Delivery Notes)
 * - OCRD: Socios de negocio
 */
class HanaQueryService
{
    // ── MAESTRO DE ARTÍCULOS ────────────────────────────────────

    /**
     * Obtiene artículos activos de SAP.
     */
    public function getItems(int $top = 500, int $skip = 0): array
    {
        return HanaDatabase::query(
            "SELECT T0.\"ItemCode\", T0.\"ItemName\", T0.\"CodeBars\", T0.\"InvntryUom\",
                    T0.\"ManBtchNum\", T0.\"ManSerNum\", T0.\"ItmsGrpCod\",
                    T1.\"ItmsGrpNam\", T0.\"frozenFor\"
             FROM OITM T0
             LEFT JOIN OITB T1 ON T0.\"ItmsGrpCod\" = T1.\"ItmsGrpCod\"
             WHERE T0.\"frozenFor\" = 'N'
             ORDER BY T0.\"ItemCode\"
             LIMIT ? OFFSET ?",
            [$top, $skip]
        );
    }

    /**
     * Busca un artículo por código o código de barras.
     */
    public function findItem(string $codeOrBarcode): ?array
    {
        return HanaDatabase::queryOne(
            "SELECT T0.\"ItemCode\", T0.\"ItemName\", T0.\"CodeBars\", T0.\"InvntryUom\",
                    T0.\"ManBtchNum\", T0.\"ManSerNum\", T0.\"ItmsGrpCod\"
             FROM OITM T0
             WHERE T0.\"ItemCode\" = ? OR T0.\"CodeBars\" = ?",
            [$codeOrBarcode, $codeOrBarcode]
        );
    }

    // ── ALMACENES Y BINS ────────────────────────────────────────

    /**
     * Obtiene almacenes de SAP.
     */
    public function getWarehouses(): array
    {
        return HanaDatabase::query(
            "SELECT \"WhsCode\", \"WhsName\", \"Street\", \"City\", \"Inactive\"
             FROM OWHS
             WHERE \"Inactive\" = 'N'
             ORDER BY \"WhsCode\""
        );
    }

    /**
     * Obtiene bin locations de un almacén.
     */
    public function getBinLocations(string $warehouseCode): array
    {
        return HanaDatabase::query(
            "SELECT \"AbsEntry\", \"BinCode\", \"WhsCode\", \"SL1Code\" AS \"Aisle\",
                    \"SL2Code\" AS \"Rack\", \"SL3Code\" AS \"Level\", \"SL4Code\" AS \"Position\",
                    \"Descr\", \"Disabled\"
             FROM OBIN
             WHERE \"WhsCode\" = ? AND \"Disabled\" = 'N'
             ORDER BY \"BinCode\"",
            [$warehouseCode]
        );
    }

    // ── LOTES ───────────────────────────────────────────────────

    /**
     * Obtiene lotes de un artículo.
     */
    public function getBatchesByItem(string $itemCode): array
    {
        return HanaDatabase::query(
            "SELECT \"DistNumber\" AS \"BatchNumber\", \"ItemCode\",
                    \"ExpDate\" AS \"ExpiryDate\", \"MnfDate\" AS \"ManufacturingDate\",
                    \"MnfSerial\" AS \"SupplierBatch\", \"Status\", \"Quantity\"
             FROM OBTN
             WHERE \"ItemCode\" = ?
             ORDER BY \"ExpDate\" ASC",
            [$itemCode]
        );
    }

    // ── STOCK EN SAP ────────────────────────────────────────────

    /**
     * Obtiene stock por almacén y artículo (tabla OITW).
     */
    public function getStockByWarehouse(string $warehouseCode): array
    {
        return HanaDatabase::query(
            "SELECT T0.\"ItemCode\", T1.\"ItemName\", T0.\"WhsCode\",
                    T0.\"OnHand\", T0.\"IsCommited\", T0.\"OnOrder\"
             FROM OITW T0
             JOIN OITM T1 ON T0.\"ItemCode\" = T1.\"ItemCode\"
             WHERE T0.\"WhsCode\" = ? AND T0.\"OnHand\" > 0
             ORDER BY T0.\"ItemCode\"",
            [$warehouseCode]
        );
    }

    /**
     * Obtiene stock por bin location y lote (tabla OIBQ).
     */
    public function getStockByBinAndBatch(string $warehouseCode): array
    {
        return HanaDatabase::query(
            "SELECT T0.\"ItemCode\", T1.\"ItemName\", T0.\"BinAbs\",
                    T2.\"BinCode\", T0.\"SnBMDAbs\" AS \"BatchAbsEntry\",
                    T3.\"DistNumber\" AS \"BatchNumber\", T3.\"ExpDate\",
                    T0.\"OnHandQty\"
             FROM OIBQ T0
             JOIN OITM T1 ON T0.\"ItemCode\" = T1.\"ItemCode\"
             JOIN OBIN T2 ON T0.\"BinAbs\" = T2.\"AbsEntry\"
             LEFT JOIN OBTN T3 ON T0.\"SnBMDAbs\" = T3.\"AbsEntry\"
             WHERE T0.\"WhsCode\" = ? AND T0.\"OnHandQty\" > 0
             ORDER BY T0.\"ItemCode\", T3.\"ExpDate\" ASC",
            [$warehouseCode]
        );
    }

    // ── DOCUMENTOS ──────────────────────────────────────────────

    /**
     * Obtiene órdenes de compra abiertas.
     */
    public function getOpenPurchaseOrders(): array
    {
        return HanaDatabase::query(
            "SELECT T0.\"DocEntry\", T0.\"DocNum\", T0.\"CardCode\", T0.\"CardName\",
                    T0.\"DocDate\", T0.\"DocDueDate\", T0.\"DocTotal\", T0.\"DocCur\"
             FROM OPOR T0
             WHERE T0.\"DocStatus\" = 'O' AND T0.\"CANCELED\" = 'N'
             ORDER BY T0.\"DocDate\" DESC"
        );
    }

    /**
     * Obtiene líneas de una orden de compra.
     */
    public function getPurchaseOrderLines(int $docEntry): array
    {
        return HanaDatabase::query(
            "SELECT T0.\"LineNum\", T0.\"ItemCode\", T1.\"ItemName\",
                    T0.\"Quantity\", T0.\"OpenQty\", T0.\"UomCode\",
                    T0.\"WhsCode\", T0.\"ShipDate\"
             FROM POR1 T0
             JOIN OITM T1 ON T0.\"ItemCode\" = T1.\"ItemCode\"
             WHERE T0.\"DocEntry\" = ? AND T0.\"LineStatus\" = 'O'
             ORDER BY T0.\"LineNum\"",
            [$docEntry]
        );
    }

    /**
     * Obtiene pedidos de venta abiertos.
     */
    public function getOpenSalesOrders(): array
    {
        return HanaDatabase::query(
            "SELECT T0.\"DocEntry\", T0.\"DocNum\", T0.\"CardCode\", T0.\"CardName\",
                    T0.\"DocDate\", T0.\"DocDueDate\", T0.\"DocTotal\", T0.\"DocCur\"
             FROM ORDR T0
             WHERE T0.\"DocStatus\" = 'O' AND T0.\"CANCELED\" = 'N'
             ORDER BY T0.\"DocDueDate\" ASC"
        );
    }

    /**
     * Obtiene líneas de un pedido de venta.
     */
    public function getSalesOrderLines(int $docEntry): array
    {
        return HanaDatabase::query(
            "SELECT T0.\"LineNum\", T0.\"ItemCode\", T1.\"ItemName\",
                    T0.\"Quantity\", T0.\"OpenQty\", T0.\"UomCode\",
                    T0.\"WhsCode\", T0.\"ShipDate\"
             FROM RDR1 T0
             JOIN OITM T1 ON T0.\"ItemCode\" = T1.\"ItemCode\"
             WHERE T0.\"DocEntry\" = ? AND T0.\"LineStatus\" = 'O'
             ORDER BY T0.\"LineNum\"",
            [$docEntry]
        );
    }

    // ── SOCIOS DE NEGOCIO ───────────────────────────────────────

    /**
     * Obtiene proveedores activos.
     */
    public function getSuppliers(): array
    {
        return HanaDatabase::query(
            "SELECT \"CardCode\", \"CardName\", \"Phone1\", \"E_Mail\", \"Address\"
             FROM OCRD
             WHERE \"CardType\" = 'S' AND \"frozenFor\" = 'N'
             ORDER BY \"CardName\""
        );
    }

    /**
     * Obtiene clientes activos.
     */
    public function getCustomers(): array
    {
        return HanaDatabase::query(
            "SELECT \"CardCode\", \"CardName\", \"Phone1\", \"E_Mail\", \"Address\"
             FROM OCRD
             WHERE \"CardType\" = 'C' AND \"frozenFor\" = 'N'
             ORDER BY \"CardName\""
        );
    }

    /**
     * Busca socio de negocio por código.
     */
    public function findBusinessPartner(string $cardCode): ?array
    {
        return HanaDatabase::queryOne(
            "SELECT \"CardCode\", \"CardName\", \"CardType\", \"Phone1\", \"E_Mail\", \"Address\"
             FROM OCRD WHERE \"CardCode\" = ?",
            [$cardCode]
        );
    }
}