<?php

namespace WMS\Core;

/**
 * HanaDatabase - MOCK para desarrollo/testing.
 *
 * Reemplaza la conexión ODBC a SAP HANA. Los catálogos maestros
 * (OITM, OITB, OWHS, OBIN, OBTN, OIBQ, OITW) se leen en vivo desde
 * wms_pro para que el mock sea siempre un espejo fiel de la data real
 * del WMS. Las transacciones abiertas de SAP (OPOR/POR1, ORDR/RDR1,
 * OCRD) no tienen tabla equivalente en wms_pro (viven solo en SAP),
 * así que quedan como datos de prueba armados a mano.
 *
 * Instalado por enable_mock.sh, restaurable con disable_mock.sh.
 *
 * Soporta las consultas principales que hace HanaQueryService:
 * OITM, OITB, OWHS, OBIN, OBTN, OIBQ, OITW, OPOR, POR1, ORDR, RDR1, OCRD
 */
class HanaDatabase
{
    private static array $data = [];

    private static function getData(): array
    {
        if (!empty(self::$data)) return self::$data;

        $pdo = Database::getConnection();

        $oitm = $pdo->query("
            SELECT i.item_code AS ItemCode,
                   i.item_name AS ItemName,
                   i.barcode AS CodeBars,
                   i.uom AS InvntryUom,
                   IF(i.requires_lot = 1, 'Y', 'N') AS ManBtchNum,
                   IF(i.requires_serial = 1, 'Y', 'N') AS ManSerNum,
                   i.item_group AS ItmsGrpCod,
                   COALESCE(ig.name, '') AS ItmsGrpNam,
                   IF(i.is_active = 1, 'N', 'Y') AS frozenFor,
                   i.u_subgrupo AS U_subgrupo,
                   i.u_xpert_mtpp AS U_XPERT_MTPP
            FROM items i
            LEFT JOIN item_groups ig ON ig.code = i.item_group
        ")->fetchAll();

        $oitb = $pdo->query("
            SELECT code AS ItmsGrpCod, name AS ItmsGrpNam
            FROM item_groups
        ")->fetchAll();

        $owhs = $pdo->query("
            SELECT code AS WhsCode,
                   name AS WhsName,
                   COALESCE(address, '') AS Street,
                   '' AS City,
                   IF(is_active = 1, 'N', 'Y') AS Inactive
            FROM warehouses
        ")->fetchAll();

        $obin = $pdo->query("
            SELECT b.id AS AbsEntry,
                   b.code AS BinCode,
                   w.code AS WhsCode,
                   COALESCE(b.aisle, '') AS SL1Code,
                   COALESCE(b.rack, '') AS SL2Code,
                   COALESCE(b.level, '') AS SL3Code,
                   COALESCE(b.position, '') AS SL4Code,
                   b.code AS Descr,
                   IF(b.is_active = 1, 'N', 'Y') AS Disabled
            FROM bins b
            JOIN warehouses w ON w.id = b.warehouse_id
        ")->fetchAll();

        $obtn = $pdo->query("
            SELECT bt.batch_number AS DistNumber,
                   i.item_code AS ItemCode,
                   bt.expiry_date AS ExpDate,
                   bt.manufacturing_date AS MnfDate,
                   COALESCE(bt.supplier_batch, '') AS MnfSerial,
                   IF(bt.status = 'AVAILABLE', 0, 1) AS Status,
                   COALESCE((SELECT SUM(s.quantity) FROM stock s WHERE s.batch_id = bt.id), 0) AS Quantity
            FROM batches bt
            JOIN items i ON i.id = bt.item_id
        ")->fetchAll();

        $oibq = $pdo->query("
            SELECT i.item_code AS ItemCode,
                   w.code AS WhsCode,
                   s.bin_id AS BinAbs,
                   COALESCE(bt.batch_number, '') AS DistNumber,
                   s.quantity AS OnHandQty,
                   bt.expiry_date AS ExpDate
            FROM stock s
            JOIN items i ON i.id = s.item_id
            JOIN warehouses w ON w.id = s.warehouse_id
            LEFT JOIN batches bt ON bt.id = s.batch_id
        ")->fetchAll();

        $oitw = $pdo->query("
            SELECT i.item_code AS ItemCode,
                   w.code AS WhsCode,
                   SUM(s.quantity) AS OnHand,
                   SUM(s.reserved_qty) AS IsCommited,
                   0 AS OnOrder
            FROM stock s
            JOIN items i ON i.id = s.item_id
            JOIN warehouses w ON w.id = s.warehouse_id
            GROUP BY i.item_code, w.code
        ")->fetchAll();

        self::$data = [
            'OITM' => $oitm,
            'OITB' => $oitb,
            'OWHS' => $owhs,
            'OBIN' => $obin,
            'OBTN' => $obtn,
            'OIBQ' => $oibq,
            'OITW' => $oitw,
            'OPOR' => [
                ['DocEntry'=>1001,'DocNum'=>1001,'DocDate'=>date('Y-m-d'),'CardCode'=>'P00100','CardName'=>'LABORATORIOS ROCHE S.A.','DocStatus'=>'O','U_XPERT_WMS'=>'Y'],
                ['DocEntry'=>1002,'DocNum'=>1002,'DocDate'=>date('Y-m-d'),'CardCode'=>'P00200','CardName'=>'PFIZER CORP. PARAGUAY','DocStatus'=>'O','U_XPERT_WMS'=>'Y'],
                ['DocEntry'=>1003,'DocNum'=>1003,'DocDate'=>date('Y-m-d', strtotime('-2 days')),'CardCode'=>'P00300','CardName'=>'BAYER S.A.','DocStatus'=>'O','U_XPERT_WMS'=>'Y'],
            ],
            'POR1' => [
                ['DocEntry'=>1001,'LineNum'=>0,'ItemCode'=>'MED-001','Dscription'=>'AMOXICILINA 500MG CAPS x100','Quantity'=>500.0,'OpenQty'=>500.0,'Price'=>12.50,'WhsCode'=>'CENMRA'],
                ['DocEntry'=>1001,'LineNum'=>1,'ItemCode'=>'MED-006','Dscription'=>'ATORVASTATINA 20MG COMP x30','Quantity'=>200.0,'OpenQty'=>200.0,'Price'=>18.75,'WhsCode'=>'CENMRA'],
                ['DocEntry'=>1002,'LineNum'=>0,'ItemCode'=>'MED-005','Dscription'=>'PARACETAMOL 500MG COMP x24','Quantity'=>1000.0,'OpenQty'=>1000.0,'Price'=>5.20,'WhsCode'=>'CENMRA'],
                ['DocEntry'=>1003,'LineNum'=>0,'ItemCode'=>'MED-002','Dscription'=>'IBUPROFENO 400MG COMP x30','Quantity'=>300.0,'OpenQty'=>300.0,'Price'=>8.90,'WhsCode'=>'CENMRA'],
                ['DocEntry'=>1003,'LineNum'=>1,'ItemCode'=>'MED-003','Dscription'=>'LORATADINA 10MG COMP x20','Quantity'=>150.0,'OpenQty'=>150.0,'Price'=>11.30,'WhsCode'=>'CENMRA'],
            ],
            'ORDR' => [
                ['DocEntry'=>2001,'DocNum'=>2001,'DocDate'=>date('Y-m-d'),'CardCode'=>'C00001','CardName'=>'FARMACIA DEL PUEBLO S.A.','DocStatus'=>'O'],
                ['DocEntry'=>2002,'DocNum'=>2002,'DocDate'=>date('Y-m-d'),'CardCode'=>'C00002','CardName'=>'CLINICA SANTA MARIA','DocStatus'=>'O'],
            ],
            'RDR1' => [
                ['DocEntry'=>2001,'LineNum'=>0,'ItemCode'=>'MED-001','Dscription'=>'AMOXICILINA 500MG CAPS x100','Quantity'=>50.0,'OpenQty'=>50.0,'Price'=>25.00,'WhsCode'=>'CENMRA'],
                ['DocEntry'=>2001,'LineNum'=>1,'ItemCode'=>'MED-005','Dscription'=>'PARACETAMOL 500MG COMP x24','Quantity'=>100.0,'OpenQty'=>100.0,'Price'=>9.50,'WhsCode'=>'CENMRA'],
                ['DocEntry'=>2002,'LineNum'=>0,'ItemCode'=>'MED-004','Dscription'=>'OMEPRAZOL 20MG CAPS x28','Quantity'=>80.0,'OpenQty'=>80.0,'Price'=>22.00,'WhsCode'=>'CENMRA'],
                ['DocEntry'=>2002,'LineNum'=>1,'ItemCode'=>'MED-007','Dscription'=>'METFORMINA 850MG COMP x60','Quantity'=>60.0,'OpenQty'=>60.0,'Price'=>15.80,'WhsCode'=>'CENMRA'],
            ],
            'OCRD' => [
                ['CardCode'=>'P00100','CardName'=>'LABORATORIOS ROCHE S.A.','CardType'=>'S'],
                ['CardCode'=>'P00200','CardName'=>'PFIZER CORP. PARAGUAY','CardType'=>'S'],
                ['CardCode'=>'P00261','CardName'=>'LABORATOIRE BIODERMA','CardType'=>'S'],
                ['CardCode'=>'P00300','CardName'=>'BAYER S.A.','CardType'=>'S'],
                ['CardCode'=>'C00001','CardName'=>'FARMACIA DEL PUEBLO S.A.','CardType'=>'C'],
                ['CardCode'=>'C00002','CardName'=>'CLINICA SANTA MARIA','CardType'=>'C'],
            ],
        ];

        return self::$data;
    }

    /**
     * Detecta la tabla principal de un SQL y filtra columnas/condiciones básicas.
     * Suficiente para las consultas que hace HanaQueryService.
     */
    private static function resolveTable(string $sql): string
    {
        if (preg_match('/FROM\s+([A-Z0-9_]+)\s*/i', $sql, $m)) {
            return strtoupper($m[1]);
        }
        return '';
    }

    private static function applyFilter(array $rows, string $sql, array $params): array
    {
        // Filtro simple: si el SQL tiene WHERE y parámetros, filtramos por igualdad en el primer campo
        if (empty($params) || !str_contains(strtoupper($sql), 'WHERE')) {
            return $rows;
        }

        // Extraer columna del WHERE (heurística)
        // e.g. WHERE T0."ItemCode" = ? → busca ItemCode
        if (preg_match('/WHERE\s+(?:T\d+\.)?"?(\w+)"?\s*=\s*\?/i', $sql, $m)) {
            $col   = $m[1];
            $value = $params[0];
            return array_values(array_filter($rows, fn($r) => isset($r[$col]) && (string)$r[$col] === (string)$value));
        }

        // DocEntry filter
        if (preg_match('/WHERE\s+(?:T\d+\.)?"?DocEntry"?\s*=\s*\?/i', $sql, $m)) {
            return array_values(array_filter($rows, fn($r) => isset($r['DocEntry']) && (int)$r['DocEntry'] === (int)$params[0]));
        }

        return $rows;
    }

    private static function applyLimitOffset(array $rows, string $sql, array $params): array
    {
        if (preg_match('/LIMIT\s+\?\s+OFFSET\s+\?/i', $sql, $m)) {
            // Los últimos dos params son LIMIT y OFFSET
            $n     = count($params);
            $limit  = (int)($params[$n - 2] ?? 500);
            $offset = (int)($params[$n - 1] ?? 0);
            return array_slice($rows, $offset, $limit);
        }
        return $rows;
    }

    public static function getConnection(): \PDO
    {
        throw new \RuntimeException('[HANA MOCK] Conexión ODBC deshabilitada en modo mock. Usa query()/queryOne().');
    }

    public static function query(string $sql, array $params = []): array
    {
        $table = self::resolveTable($sql);
        $data  = self::getData();
        $rows  = $data[$table] ?? [];
        $rows  = self::applyFilter($rows, $sql, $params);
        $rows  = self::applyLimitOffset($rows, $sql, $params);
        return $rows;
    }

    public static function queryOne(string $sql, array $params = []): ?array
    {
        $rows = self::query($sql, $params);
        return $rows[0] ?? null;
    }

    public static function scalar(string $sql, array $params = [])
    {
        $row = self::queryOne($sql, $params);
        return $row ? reset($row) : null;
    }
}