<?php

namespace WMS\Controllers;

use WMS\Core\Database;
use WMS\Core\Request;
use WMS\Core\Response;
use WMS\Core\HanaDatabase;
use WMS\Integrations\Sap\HanaQueryService;
use WMS\Integrations\Sap\SapServiceLayerClient;

class SapController
{
    /**
     * GET /api/sap/purchase-orders
     * Lista órdenes de compra abiertas desde HANA.
     */
    public static function purchaseOrders(array $params): void
    {
        try {
            try {
                $hana = new HanaQueryService();
                $orders = $hana->getOpenPurchaseOrders();
            } catch (\Throwable $e) {
                $sl = new SapServiceLayerClient();
                $sl->login();
                $r = $sl->getOpenPurchaseOrders();
                $orders = $r['value'] ?? $r;
                $sl->logout();
            }
            Response::success($orders);
        } catch (\Throwable $e) {
            Response::error('SAP no disponible: ' . $e->getMessage(), 503);
        }
    }

    /**
     * GET /api/sap/purchase-orders/{docEntry}/lines
     * Líneas de una orden de compra desde HANA.
     */
    public static function purchaseOrderLines(array $params): void
    {
        try {
            $docEntry = (int) $params['docEntry'];
            try {
                $hana = new HanaQueryService();
                $lines = $hana->getPurchaseOrderLines($docEntry);
            } catch (\Throwable $e) {
                $sl = new SapServiceLayerClient();
                $sl->login();
                $doc = $sl->getPurchaseOrder($docEntry);
                $lines = $doc['DocumentLines'] ?? [];
                $sl->logout();
            }
            Response::success($lines);
        } catch (\Throwable $e) {
            Response::error('SAP no disponible: ' . $e->getMessage(), 503);
        }
    }

    /**
     * GET /api/sap/sales-orders
     * Lista pedidos de venta abiertos desde HANA.
     */
    public static function salesOrders(array $params): void
    {
        try {
            try {
                $hana = new HanaQueryService();
                $orders = $hana->getOpenSalesOrders();
            } catch (\Throwable $e) {
                $sl = new SapServiceLayerClient();
                $sl->login();
                $r = $sl->getOpenSalesOrders();
                $orders = $r['value'] ?? $r;
                $sl->logout();
            }
            Response::success($orders);
        } catch (\Throwable $e) {
            Response::error('SAP no disponible: ' . $e->getMessage(), 503);
        }
    }

    /**
     * GET /api/sap/sales-orders/{docEntry}/lines
     * Líneas de un pedido de venta desde HANA.
     */
    public static function salesOrderLines(array $params): void
    {
        try {
            $docEntry = (int) $params['docEntry'];
            try {
                $hana = new HanaQueryService();
                $lines = $hana->getSalesOrderLines($docEntry);
            } catch (\Throwable $e) {
                $sl = new SapServiceLayerClient();
                $sl->login();
                $doc = $sl->getSalesOrder($docEntry);
                $lines = $doc['DocumentLines'] ?? [];
                $sl->logout();
            }
            Response::success($lines);
        } catch (\Throwable $e) {
            Response::error('SAP no disponible: ' . $e->getMessage(), 503);
        }
    }

    /**
     * GET /api/sap/items
     * Lista artículos desde HANA.
     */
    public static function items(array $params): void
    {
        try {
            $top  = (int) (Request::query('top', 500));
            $skip = (int) (Request::query('skip', 0));
            try {
                $hana = new HanaQueryService();
                $items = $hana->getItems($top, $skip);
            } catch (\Throwable $e) {
                $sl = new SapServiceLayerClient();
                $sl->login();
                $r = $sl->getItems($top, $skip);
                $items = $r['value'] ?? $r;
                $sl->logout();
            }
            Response::success($items);
        } catch (\Throwable $e) {
            Response::error('SAP no disponible: ' . $e->getMessage(), 503);
        }
    }

    /**
     * POST /api/sap/sync-items
     * Importa artículos de SAP HANA a la tabla items de WMS (upsert por item_code).
     */
    public static function syncItems(array $params): void
    {
        $startedAt = date('Y-m-d H:i:s');
        $tStart = microtime(true);
        $user = \WMS\Middleware\AuthMiddleware::user();
        $userId = $user['id'] ?? null;
        $username = $user['username'] ?? null;

        $stats = ['processed' => 0, 'created' => 0, 'updated' => 0, 'unchanged' => 0, 'errors' => 0];
        $source = 'unknown';
        $status = 'OK';
        $errMsg = null;

        // Sync de item_groups (familia) — Service Layer paginado.
        // Hacemos esto SIEMPRE antes de items porque el JOIN en putaway
        // depende de que item_groups.name esté al día (SAP permite rename
        // del GroupName conservando el Number, lo que invalida la caché).
        $groupsStats = ['fetched' => 0, 'upserted' => 0];
        try {
            $slg = new SapServiceLayerClient();
            $slg->login();
            $groups = [];
            $gSkip = 0; $gPage = 100;
            do {
                $gResp = $slg->getItemGroups($gPage, $gSkip);
                $gPageData = $gResp['value'] ?? $gResp;
                if (!is_array($gPageData) || empty($gPageData)) break;
                $groups = array_merge($groups, $gPageData);
                $gSkip += count($gPageData);
            } while (count($gPageData) >= $gPage);
            $slg->logout();

            $groupsStats['fetched'] = count($groups);
            if ($groups) {
                $gDb = Database::getConnection();
                $gUpsert = $gDb->prepare(
                    "INSERT INTO item_groups (code, name) VALUES (?, ?)
                     ON DUPLICATE KEY UPDATE name = VALUES(name)"
                );
                foreach ($groups as $g) {
                    $code = (string)($g['Number'] ?? '');
                    $name = (string)($g['GroupName'] ?? '');
                    if ($code === '' || $name === '') continue;
                    try { $gUpsert->execute([$code, $name]); $groupsStats['upserted']++; }
                    catch (\Throwable $ge) { /* skip group errors */ }
                }
            }
        } catch (\Throwable $gErr) {
            // No abortamos el sync de items si fallan los grupos; queda en el log.
            error_log('[syncItems] item_groups sync failed: ' . $gErr->getMessage());
        }

        try {
            $sapItems = [];
            try {
                $hana = new HanaQueryService();
                $sapItems = $hana->getItems(10000, 0);
                $source = 'HANA';
            } catch (\Throwable $e) {
                $sl = new SapServiceLayerClient();
                $sl->login();
                $sapItems = [];
                $skip = 0;
                $pageSize = 20;
                do {
                    $response = $sl->getItems($pageSize, $skip);
                    $page = $response['value'] ?? $response;
                    if (!is_array($page) || empty($page)) break;
                    $sapItems = array_merge($sapItems, $page);
                    $skip += count($page);
                } while (count($page) >= $pageSize);
                $source = 'ServiceLayer';
                $sl->logout();
            }

            $db = Database::getConnection();
            $db->beginTransaction();

            $selStmt = $db->prepare("SELECT id, item_name, barcode, uom, requires_lot, requires_serial, item_group, u_subgrupo, sap_item_code FROM items WHERE item_code = ?");
            $insStmt = $db->prepare("INSERT INTO items (item_code, item_name, barcode, uom, requires_lot, requires_serial, item_group, u_subgrupo, sap_item_code) VALUES (?,?,?,?,?,?,?,?,?)");
            $updStmt = $db->prepare("UPDATE items SET item_name=?, barcode=?, uom=?, requires_lot=?, requires_serial=?, item_group=?, u_subgrupo=?, sap_item_code=? WHERE id=?");

            foreach ($sapItems as $item) {
                try {
                    $code     = $item['ItemCode'];
                    $name     = $item['ItemName'] ?? '';
                    $barcode  = $item['CodeBars'] ?? $item['BarCode'] ?? null;
                    $uom      = $item['InvntryUom'] ?? $item['InventoryUOM'] ?? 'UN';
                    $batch    = $item['ManBtchNum'] ?? $item['ManageBatchNumbers'] ?? 'tNO';
                    $serial   = $item['ManSerNum'] ?? $item['ManageSerialNumbers'] ?? 'tNO';
                    $group    = $item['ItmsGrpNam'] ?? $item['ItemsGroupCode'] ?? null;
                    $group    = $group !== null ? (string)$group : null;
                    $subgrupo = $item['U_subgrupo'] ?? null;
                    $subgrupo = ($subgrupo !== null && $subgrupo !== '') ? (string)$subgrupo : null;
                    $reqLot   = ($batch === 'Y' || $batch === 'tYES') ? 1 : 0;
                    $reqSer   = ($serial === 'Y' || $serial === 'tYES') ? 1 : 0;

                    $selStmt->execute([$code]);
                    $existing = $selStmt->fetch(\PDO::FETCH_ASSOC);
                    $stats['processed']++;
                    if ($existing) {
                        $changed = (
                            (string)$existing['item_name']     !== (string)$name    ||
                            (string)($existing['barcode'] ?? '') !== (string)($barcode ?? '') ||
                            (string)$existing['uom']           !== (string)$uom     ||
                            (int)$existing['requires_lot']     !== (int)$reqLot     ||
                            (int)$existing['requires_serial']  !== (int)$reqSer     ||
                            (string)($existing['item_group'] ?? '') !== (string)($group ?? '') ||
                            (string)($existing['u_subgrupo'] ?? '') !== (string)($subgrupo ?? '') ||
                            (string)($existing['sap_item_code'] ?? '') !== (string)$code
                        );
                        if ($changed) {
                            $updStmt->execute([$name, $barcode, $uom, $reqLot, $reqSer, $group, $subgrupo, $code, (int)$existing['id']]);
                            $stats['updated']++;
                        } else {
                            $stats['unchanged']++;
                        }
                    } else {
                        $insStmt->execute([$code, $name, $barcode, $uom, $reqLot, $reqSer, $group, $subgrupo, $code]);
                        $stats['created']++;
                    }
                } catch (\Throwable $ie) {
                    $stats['errors']++;
                }
            }

            $db->commit();
            if ($stats['errors'] > 0 && $stats['errors'] < $stats['processed']) {
                $status = 'PARTIAL';
            }
        } catch (\Throwable $e) {
            if (isset($db) && $db->inTransaction()) $db->rollBack();
            $status = 'FAILED';
            $errMsg = $e->getMessage();
        }

        // Registrar en sap_sync_log (schema existente: sync_type/direction/status/response_payload/error_message)
        try {
            $logDb = Database::getConnection();
            $finishedAt = date('Y-m-d H:i:s');
            $durMs = (int) round((microtime(true) - $tStart) * 1000);
            $payload = json_encode([
                'started_at'  => $startedAt,
                'finished_at' => $finishedAt,
                'duration_ms' => $durMs,
                'source'      => $source,
                'processed'   => $stats['processed'],
                'created'     => $stats['created'],
                'updated'     => $stats['updated'],
                'unchanged'   => $stats['unchanged'],
                'errors'      => $stats['errors'],
                'executed_by_user_id' => $userId,
                'executed_by_username'=> $username,
            ], JSON_UNESCAPED_UNICODE);
            $logStatus = ($status === 'OK') ? 'SUCCESS' : (($status === 'FAILED') ? 'ERROR' : 'SUCCESS');
            $logSql = "INSERT INTO sap_sync_log (sync_type, direction, status, response_payload, error_message) VALUES (?,?,?,?,?)";
            $logStmt = $logDb->prepare($logSql);
            $logStmt->execute(['MASTER_ITEMS', 'SAP_TO_WMS', $logStatus, $payload, $errMsg]);
        } catch (\Throwable $logErr) { /* no romper la respuesta por log */ }

        if ($status === 'FAILED') {
            Response::error('Error al sincronizar articulos: ' . $errMsg, 500);
            return;
        }

        Response::success([
            'message'   => "Sincronizacion completada via {$source}",
            'item_groups_fetched'  => $groupsStats['fetched'],
            'item_groups_upserted' => $groupsStats['upserted'],
            'source'    => $source,
            'synced'    => $stats['processed'],
            'processed' => $stats['processed'],
            'created'   => $stats['created'],
            'updated'   => $stats['updated'],
            'unchanged' => $stats['unchanged'],
            'errors'    => $stats['errors'],
            'duration_ms' => isset($durMs) ? $durMs : null,
        ]);

    }

    /**
     * POST /api/sap/sync-warehouses
     * Importa almacenes y bins de SAP HANA a WMS (upsert por code).
     */
    public static function syncWarehouses(array $params): void
    {
        try {
            // Try HANA first, fall back to Service Layer
            $sapWarehouses = [];
            $hanaAvailable = false;
            try {
                $hana = new HanaQueryService();
                $sapWarehouses = $hana->getWarehouses();
                $hanaAvailable = true;
            } catch (\Throwable $e) {
                $sl = new SapServiceLayerClient();
                $sl->login();
                $response = $sl->getWarehouses();
                $sapWarehouses = [];
                $raw = $response['value'] ?? $response;
                foreach ($raw as $w) {
                    $sapWarehouses[] = [
                        'WhsCode' => $w['WarehouseCode'] ?? $w['WhsCode'] ?? '',
                        'WhsName' => $w['WarehouseName'] ?? $w['WhsName'] ?? '',
                        'Street'  => $w['Street'] ?? null,
                    ];
                }
                $sl->logout();
            }

            $db = Database::getConnection();
            $db->beginTransaction();

            $upsertWhSql = "INSERT INTO warehouses (code, name, address, sap_warehouse_code)
                            VALUES (?, ?, ?, ?)
                            ON DUPLICATE KEY UPDATE
                                name = VALUES(name),
                                address = VALUES(address),
                                sap_warehouse_code = VALUES(sap_warehouse_code)";
            $whStmt = $db->prepare($upsertWhSql);

            $upsertBinSql = "INSERT INTO bins (warehouse_id, code, aisle, rack, level, position, sap_bin_code)
                             VALUES (?, ?, ?, ?, ?, ?, ?)
                             ON DUPLICATE KEY UPDATE
                                 aisle = VALUES(aisle),
                                 rack = VALUES(rack),
                                 level = VALUES(level),
                                 position = VALUES(position),
                                 sap_bin_code = VALUES(sap_bin_code)";
            $binStmt = $db->prepare($upsertBinSql);

            $whSynced  = 0;
            $binSynced = 0;

            foreach ($sapWarehouses as $wh) {
                $whStmt->execute([
                    $wh['WhsCode'],
                    $wh['WhsName'],
                    $wh['Street'] ?? null,
                    $wh['WhsCode'],
                ]);
                $whSynced++;

                // Obtener el ID del almacén (ya sea insertado o existente)
                $whIdStmt = $db->prepare("SELECT id FROM warehouses WHERE code = ?");
                $whIdStmt->execute([$wh['WhsCode']]);
                $whId = (int) $whIdStmt->fetchColumn();

                // Sincronizar bins del almacen (solo si HANA disponible)
                if (!$hanaAvailable) { continue; }
                $sapBins = $hana->getBinLocations($wh['WhsCode']);
                foreach ($sapBins as $bin) {
                    $binStmt->execute([
                        $whId,
                        $bin['BinCode'],
                        $bin['Aisle'] ?? null,
                        $bin['Rack'] ?? null,
                        $bin['Level'] ?? null,
                        $bin['Position'] ?? null,
                        $bin['BinCode'],
                    ]);
                    $binSynced++;
                }
            }

            $db->commit();

            Response::success([
                'message'          => 'Sincronización de almacenes completada',
                'warehouses_synced' => $whSynced,
                'bins_synced'       => $binSynced,
            ]);
        } catch (\Throwable $e) {
            if (isset($db) && $db->inTransaction()) {
                $db->rollBack();
            }
            Response::error('Error al sincronizar almacenes: ' . $e->getMessage(), 500);
        }
    }

    /**
     * GET /api/sap/connection-status
     * Prueba conectividad con SAP HANA y Service Layer.
     */
    public static function connectionStatus(array $params): void
    {
        $hanaStatus = ['connected' => false];
        $slStatus   = ['connected' => false];

        // Test HANA
        try {
            HanaDatabase::getConnection();
            $hanaStatus['connected'] = true;
        } catch (\Throwable $e) {
            $hanaStatus['error'] = $e->getMessage();
        }

        // Test Service Layer
        try {
            $sl = new SapServiceLayerClient();
            $sl->login();
            $slStatus['connected'] = true;
            $sl->logout();
        } catch (\Throwable $e) {
            $slStatus['error'] = $e->getMessage();
        }

        Response::success([
            'hana'          => $hanaStatus,
            'service_layer' => $slStatus,
        ]);
    }

    /**
     * GET /api/sap/credit-notes
     */
    public static function creditNotes(array $params): void
    {
        try {
            $sap = new \WMS\Integrations\Sap\SapServiceLayerClient();
            $sap->login();
            $result = $sap->getOpenCreditNotes();
            $sap->logout();
            Response::success($result['value'] ?? $result);
        } catch (\Throwable $e) {
            Response::error('SAP no disponible: ' . $e->getMessage());
        }
    }

    /**
     * GET /api/sap/stock-transfers
     */
    public static function stockTransfers(array $params): void
    {
        try {
            $sap = new \WMS\Integrations\Sap\SapServiceLayerClient();
            $sap->login();
            $result = $sap->getStockTransfers();
            $sap->logout();
            Response::success($result['value'] ?? $result);
        } catch (\Throwable $e) {
            Response::error('SAP no disponible: ' . $e->getMessage());
        }
    }

    /**
     * GET /api/sap/stock-transfers/{docEntry}
     */
    public static function stockTransferDetail(array $params): void
    {
        try {
            $sap = new \WMS\Integrations\Sap\SapServiceLayerClient();
            $sap->login();
            $doc = $sap->getStockTransfer((int) ($params['docEntry'] ?? 0));
            $sap->logout();
            Response::success($doc);
        } catch (\Throwable $e) {
            Response::error('SAP no disponible: ' . $e->getMessage());
        }
    }

    /**
     * GET /api/sap/transfer-requests/{docEntry}
     */
    public static function transferRequestDetail(array $params): void
    {
        try {
            $sap = new \WMS\Integrations\Sap\SapServiceLayerClient();
            $sap->login();
            $doc = $sap->getTransferRequest((int) ($params['docEntry'] ?? 0));
            $sap->logout();
            Response::success($doc);
        } catch (\Throwable $e) {
            Response::error('SAP no disponible: ' . $e->getMessage());
        }
    }

    /**
     * GET /api/sap/transfer-requests
     */
    public static function transferRequests(array $params): void
    {
        try {
            $sap = new \WMS\Integrations\Sap\SapServiceLayerClient();
            $sap->login();
            $result = $sap->getTransferRequests();
            $sap->logout();
            Response::success($result['value'] ?? $result);
        } catch (\Throwable $e) {
            Response::error('SAP no disponible: ' . $e->getMessage());
        }
    }

    /**
     * GET /api/sap/delivery-notes
     */
    public static function deliveryNotes(array $params): void
    {
        try {
            $sap = new \WMS\Integrations\Sap\SapServiceLayerClient();
            $sap->login();
            $result = $sap->getDeliveryNotes();
            $sap->logout();
            Response::success($result['value'] ?? $result);
        } catch (\Throwable $e) {
            Response::error('SAP no disponible: ' . $e->getMessage());
        }
    }

    /**
     * GET /api/sap/goods-receipts
     */
    /**
     * GET /api/sap/production-orders
     */
    
    /**
     * GET /api/sap/invoices
     */
    public static function invoices(array $params): void
    {
        try {
            $sap = new \WMS\Integrations\Sap\SapServiceLayerClient();
            $sap->login();
            $result = $sap->getInvoices();
            $sap->logout();
            Response::success($result['value'] ?? $result);
        } catch (\Throwable $e) {
            Response::error('SAP no disponible: ' . $e->getMessage());
        }
    }

    /**
     * GET /api/sap/inventory-entries
     */
    public static function inventoryEntries(array $params): void
    {
        try {
            $sap = new \WMS\Integrations\Sap\SapServiceLayerClient();
            $sap->login();
            $result = $sap->getInventoryEntries();
            $sap->logout();
            Response::success($result['value'] ?? $result);
        } catch (\Throwable $e) {
            Response::error('SAP no disponible: ' . $e->getMessage());
        }
    }

    /**
     * GET /api/sap/inventory-exits
     */
    public static function inventoryExits(array $params): void
    {
        try {
            $sap = new \WMS\Integrations\Sap\SapServiceLayerClient();
            $sap->login();
            $result = $sap->getInventoryExits();
            $sap->logout();
            Response::success($result['value'] ?? $result);
        } catch (\Throwable $e) {
            Response::error('SAP no disponible: ' . $e->getMessage());
        }
    }
    public static function productionOrders(array $params): void
    {
        try {
            $sap = new \WMS\Integrations\Sap\SapServiceLayerClient();
            $sap->login();
            $result = $sap->getProductionOrders();
            $sap->logout();
            Response::success($result['value'] ?? $result);
        } catch (\Throwable $e) {
            Response::error('SAP no disponible: ' . $e->getMessage());
        }
    }

        public static function goodsReceipts(array $params): void
    {
        try {
            $sap = new \WMS\Integrations\Sap\SapServiceLayerClient();
            $sap->login();
            $result = $sap->getGoodsReceiptPO();
            $sap->logout();
            Response::success($result['value'] ?? $result);
        } catch (\Throwable $e) {
            Response::error('SAP no disponible: ' . $e->getMessage());
        }
    }

    /**
     * GET /api/sap/sync-log
     * Devuelve las ultimas N entradas de sap_sync_log (default 50).
     */
    public static function syncLog(array $params): void
    {
        $limit = (int) (\WMS\Core\Request::query('limit') ?? 50);
        if ($limit < 1) $limit = 50;
        if ($limit > 500) $limit = 500;
        $type  = \WMS\Core\Request::query('type');
        try {
            $db = Database::getConnection();
            $sql = "SELECT id, sync_type, direction, status, response_payload, error_message, created_at, updated_at FROM sap_sync_log";
            $params = [];
            if ($type) {
                $sql .= " WHERE sync_type = ?";
                $params[] = $type;
            }
            $sql .= " ORDER BY created_at DESC LIMIT " . $limit;
            $stmt = $db->prepare($sql);
            $stmt->execute($params);
            $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC);
            // Aplanar response_payload JSON a campos top-level para la UI
            foreach ($rows as &$row) {
                $payload = $row['response_payload'] ? json_decode($row['response_payload'], true) : null;
                if (is_array($payload)) {
                    foreach ($payload as $k => $v) {
                        $row[$k] = $v;
                    }
                }
                // Mapear nombres legacy que la UI espera
                $row['started_at']        = $row['started_at']        ?? $row['created_at'];
                $row['records_processed'] = $row['records_processed'] ?? ($row['processed'] ?? 0);
                $row['records_created']   = $row['records_created']   ?? ($row['created']   ?? 0);
                $row['records_updated']   = $row['records_updated']   ?? ($row['updated']   ?? 0);
                $row['records_unchanged'] = $row['records_unchanged'] ?? ($row['unchanged'] ?? 0);
                $row['records_errors']    = $row['errors'] ?? 0;
                $row['executed_by_username'] = $row['executed_by_username'] ?? null;
            }
            unset($row);
            Response::success($rows);
        } catch (\Throwable $e) {
            Response::error('Error obteniendo log: ' . $e->getMessage(), 500);
        }
    }

}