<?php

namespace WMS\Core;

/**
 * HanaDatabase - MOCK para desarrollo/testing.
 *
 * Reemplaza la conexión ODBC a SAP HANA con datos estáticos.
 * 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;

        $today = date('Y-m-d');

        self::$data = [
            'OITM' => [
                ['ItemCode'=>'MED-001','ItemName'=>'AMOXICILINA 500MG CAPS x100','CodeBars'=>'7790123000011','InvntryUom'=>'UN','ManBtchNum'=>'Y','ManSerNum'=>'N','ItmsGrpCod'=>103,'ItmsGrpNam'=>'NA-RX','frozenFor'=>'N','U_subgrupo'=>'RX-ANTIBIOTICOS','U_XPERT_MTPP'=>''],
                ['ItemCode'=>'MED-002','ItemName'=>'IBUPROFENO 400MG COMP x30','CodeBars'=>'7790123000028','InvntryUom'=>'UN','ManBtchNum'=>'Y','ManSerNum'=>'N','ItmsGrpCod'=>104,'ItmsGrpNam'=>'NA-OTC','frozenFor'=>'N','U_subgrupo'=>'OTC-ANALGESICOS','U_XPERT_MTPP'=>''],
                ['ItemCode'=>'MED-003','ItemName'=>'LORATADINA 10MG COMP x20','CodeBars'=>'7790123000035','InvntryUom'=>'UN','ManBtchNum'=>'Y','ManSerNum'=>'N','ItmsGrpCod'=>104,'ItmsGrpNam'=>'NA-OTC','frozenFor'=>'N','U_subgrupo'=>'OTC-ALERGIAS','U_XPERT_MTPP'=>''],
                ['ItemCode'=>'MED-004','ItemName'=>'OMEPRAZOL 20MG CAPS x28','CodeBars'=>'7790123000042','InvntryUom'=>'UN','ManBtchNum'=>'Y','ManSerNum'=>'N','ItmsGrpCod'=>103,'ItmsGrpNam'=>'NA-RX','frozenFor'=>'N','U_subgrupo'=>'RX-GASTRO','U_XPERT_MTPP'=>''],
                ['ItemCode'=>'MED-005','ItemName'=>'PARACETAMOL 500MG COMP x24','CodeBars'=>'7790123000059','InvntryUom'=>'UN','ManBtchNum'=>'Y','ManSerNum'=>'N','ItmsGrpCod'=>104,'ItmsGrpNam'=>'NA-OTC','frozenFor'=>'N','U_subgrupo'=>'OTC-ANALGESICOS','U_XPERT_MTPP'=>''],
                ['ItemCode'=>'MED-006','ItemName'=>'ATORVASTATINA 20MG COMP x30','CodeBars'=>'7790123000066','InvntryUom'=>'UN','ManBtchNum'=>'Y','ManSerNum'=>'N','ItmsGrpCod'=>103,'ItmsGrpNam'=>'NA-RX','frozenFor'=>'N','U_subgrupo'=>'RX-CARDIOVASCULAR','U_XPERT_MTPP'=>''],
                ['ItemCode'=>'MED-007','ItemName'=>'METFORMINA 850MG COMP x60','CodeBars'=>'7790123000073','InvntryUom'=>'UN','ManBtchNum'=>'Y','ManSerNum'=>'N','ItmsGrpCod'=>103,'ItmsGrpNam'=>'NA-RX','frozenFor'=>'N','U_subgrupo'=>'RX-DIABETES','U_XPERT_MTPP'=>''],
                ['ItemCode'=>'INS-001','ItemName'=>'CAJA CORRUGADA 30x20x15','CodeBars'=>'7790123000080','InvntryUom'=>'UN','ManBtchNum'=>'N','ManSerNum'=>'N','ItmsGrpCod'=>124,'ItmsGrpNam'=>'MATERIALES Y EMPAQUES','frozenFor'=>'N','U_subgrupo'=>'EMPAQUES','U_XPERT_MTPP'=>''],
            ],
            'OITB' => [
                ['ItmsGrpCod'=>100,'ItmsGrpNam'=>'IMPORTADOS'],
                ['ItmsGrpCod'=>103,'ItmsGrpNam'=>'NA-RX'],
                ['ItmsGrpCod'=>104,'ItmsGrpNam'=>'NA-OTC'],
                ['ItmsGrpCod'=>124,'ItmsGrpNam'=>'MATERIALES Y EMPAQUES'],
            ],
            'OWHS' => [
                ['WhsCode'=>'CENMRA','WhsName'=>'Central de Almacenes','BinActivat'=>'Y'],
                ['WhsCode'=>'DEVOLU','WhsName'=>'Devoluciones','BinActivat'=>'Y'],
                ['WhsCode'=>'CUAREN','WhsName'=>'Cuarentena','BinActivat'=>'Y'],
                ['WhsCode'=>'EXPEDI','WhsName'=>'Expedición','BinActivat'=>'Y'],
            ],
            'OBIN' => [
                ['AbsEntry'=>1,'WhsCode'=>'CENMRA','BinCode'=>'CENMRA-A-01-01','Descr'=>'Pasillo A Rack 1 Nivel 1','Attr1Val'=>'A','Attr2Val'=>'01','Attr3Val'=>'01','Attr4Val'=>''],
                ['AbsEntry'=>2,'WhsCode'=>'CENMRA','BinCode'=>'CENMRA-A-01-02','Descr'=>'Pasillo A Rack 1 Nivel 2','Attr1Val'=>'A','Attr2Val'=>'01','Attr3Val'=>'02','Attr4Val'=>''],
                ['AbsEntry'=>3,'WhsCode'=>'CENMRA','BinCode'=>'CENMRA-A-02-01','Descr'=>'Pasillo A Rack 2 Nivel 1','Attr1Val'=>'A','Attr2Val'=>'02','Attr3Val'=>'01','Attr4Val'=>''],
                ['AbsEntry'=>4,'WhsCode'=>'CENMRA','BinCode'=>'CENMRA-B-01-01','Descr'=>'Pasillo B Rack 1 Nivel 1','Attr1Val'=>'B','Attr2Val'=>'01','Attr3Val'=>'01','Attr4Val'=>''],
                ['AbsEntry'=>5,'WhsCode'=>'CENMRA','BinCode'=>'CENMRA-B-01-02','Descr'=>'Pasillo B Rack 1 Nivel 2','Attr1Val'=>'B','Attr2Val'=>'01','Attr3Val'=>'02','Attr4Val'=>''],
                ['AbsEntry'=>10,'WhsCode'=>'CUAREN','BinCode'=>'CUAREN-IN-01','Descr'=>'Cuarentena Entrada','Attr1Val'=>'IN','Attr2Val'=>'01','Attr3Val'=>'','Attr4Val'=>''],
            ],
            'OBTN' => [
                ['DistNumber'=>'LOT-2025-001','ItemCode'=>'MED-001','ExpDate'=>'2027-06-30','MnfDate'=>'2025-01-15','Status'=>0,'Quantity'=>480.0],
                ['DistNumber'=>'LOT-2025-002','ItemCode'=>'MED-001','ExpDate'=>'2026-12-31','MnfDate'=>'2025-03-01','Status'=>0,'Quantity'=>200.0],
                ['DistNumber'=>'LOT-2025-010','ItemCode'=>'MED-002','ExpDate'=>'2027-03-31','MnfDate'=>'2025-02-10','Status'=>0,'Quantity'=>290.0],
                ['DistNumber'=>'LOT-2025-011','ItemCode'=>'MED-003','ExpDate'=>'2027-09-30','MnfDate'=>'2025-01-20','Status'=>0,'Quantity'=>145.0],
                ['DistNumber'=>'LOT-2025-020','ItemCode'=>'MED-004','ExpDate'=>'2028-01-31','MnfDate'=>'2025-04-05','Status'=>0,'Quantity'=>78.0],
                ['DistNumber'=>'LOT-2025-030','ItemCode'=>'MED-005','ExpDate'=>'2027-06-30','MnfDate'=>'2025-02-28','Status'=>0,'Quantity'=>960.0],
            ],
            'OIBQ' => [
                ['ItemCode'=>'MED-001','WhsCode'=>'CENMRA','BinAbs'=>1,'DistNumber'=>'LOT-2025-001','OnHandQty'=>250.0,'ExpDate'=>'2027-06-30'],
                ['ItemCode'=>'MED-001','WhsCode'=>'CENMRA','BinAbs'=>2,'DistNumber'=>'LOT-2025-002','OnHandQty'=>200.0,'ExpDate'=>'2026-12-31'],
                ['ItemCode'=>'MED-002','WhsCode'=>'CENMRA','BinAbs'=>3,'DistNumber'=>'LOT-2025-010','OnHandQty'=>290.0,'ExpDate'=>'2027-03-31'],
                ['ItemCode'=>'MED-003','WhsCode'=>'CENMRA','BinAbs'=>4,'DistNumber'=>'LOT-2025-011','OnHandQty'=>145.0,'ExpDate'=>'2027-09-30'],
                ['ItemCode'=>'MED-004','WhsCode'=>'CENMRA','BinAbs'=>1,'DistNumber'=>'LOT-2025-020','OnHandQty'=>78.0,'ExpDate'=>'2028-01-31'],
                ['ItemCode'=>'MED-005','WhsCode'=>'CENMRA','BinAbs'=>5,'DistNumber'=>'LOT-2025-030','OnHandQty'=>960.0,'ExpDate'=>'2027-06-30'],
            ],
            'OITW' => [
                ['ItemCode'=>'MED-001','WhsCode'=>'CENMRA','OnHand'=>450.0,'IsCommited'=>50.0,'OnOrder'=>500.0],
                ['ItemCode'=>'MED-002','WhsCode'=>'CENMRA','OnHand'=>290.0,'IsCommited'=>30.0,'OnOrder'=>300.0],
                ['ItemCode'=>'MED-003','WhsCode'=>'CENMRA','OnHand'=>145.0,'IsCommited'=>0.0,'OnOrder'=>150.0],
                ['ItemCode'=>'MED-004','WhsCode'=>'CENMRA','OnHand'=>78.0,'IsCommited'=>80.0,'OnOrder'=>0.0],
                ['ItemCode'=>'MED-005','WhsCode'=>'CENMRA','OnHand'=>960.0,'IsCommited'=>100.0,'OnOrder'=>1000.0],
            ],
            '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;
    }
}