<!--<?php-->
<!--error_reporting(E_ALL);-->
<!--ini_set('display_errors', 0);-->
<!--ini_set('memory_limit', '1024M'); -->
<!--header('Access-Control-Allow-Origin: *');-->
<!--header('Content-Type: application/json');-->
<!--header('Access-Control-Allow-Methods: GET, POST, OPTIONS');-->
<!--header('Access-Control-Allow-Headers: Content-Type');-->
<!--if ($_SERVER['REQUEST_METHOD'] === 'OPTIONS') { exit(0); }-->

<!--$host = 'database.xhost.co.in'; -->
<!--$db   = 'db_iqpj6ze2cf';-->
<!--$user = 'db_iqpj6ze2cf';-->
<!--$pass = 'O*9Mp;%5PPDyv8oD';-->

<!--try {-->
<!--    $pdo = new PDO("pgsql:host=$host;dbname=$db", $user, $pass);-->
<!--    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);-->
<!--    $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);-->

    // ==========================================
    // 1. HANDLE READ REQUESTS (GET)
    // ==========================================
<!--    if ($_SERVER['REQUEST_METHOD'] === 'GET') {-->
<!--        $table = isset($_GET['table']) ? $_GET['table'] : 'shopify_orders';-->
<!--        $allowed_tables = ['shopify_orders', 'order_status', 'scanning', 'last_year_orders'];-->

<!--        if (!in_array($table, $allowed_tables)) {-->
<!--            echo json_encode(["status" => "error", "message" => "Invalid table requested."]);-->
<!--            exit;-->
<!--        }-->

        // ---- Special slim fetch for last_year_orders (only 3 columns needed) ----
<!--        if ($table === 'last_year_orders') {-->
<!--            $stmt = $pdo->query("-->
<!--                SELECT -->
<!--                    (\"Created at\"::date)::text                       AS d,-->
<!--                    \"Lineitem sku\"                                    AS s,-->
<!--                    COALESCE(\"Lineitem quantity\"::integer, 1)        AS q-->
<!--                FROM last_year_orders-->
<!--                WHERE \"Lineitem sku\" IS NOT NULL -->
<!--                  AND \"Lineitem sku\" != ''-->
<!--                ORDER BY \"Created at\"::date ASC-->
<!--            ");-->
<!--            $data = $stmt->fetchAll();-->
<!--            echo json_encode([-->
<!--                "status"     => "success",-->
<!--                "table"      => "last_year_orders",-->
<!--                "total_rows" => count($data),-->
<!--                "data"       => $data-->
<!--            ]);-->
<!--            exit;-->
<!--        }-->

        // ---- Standard table fetch ----
<!--        $orderBy = ($table === 'shopify_orders') ? 'created_at DESC' : 'timestamp DESC';-->
<!--        $limit   = isset($_GET['limit']) && is_numeric($_GET['limit']) ? (int)$_GET['limit'] : 12000;-->
<!--        $stmt    = $pdo->query("SELECT * FROM $table ORDER BY $orderBy LIMIT $limit");-->
<!--        $data    = $stmt->fetchAll();-->

<!--        echo json_encode([-->
<!--            "status"     => "success",-->
<!--            "table"      => $table,-->
<!--            "total_rows" => count($data),-->
<!--            "data"       => $data-->
<!--        ]);-->
<!--        exit;-->
<!--    }-->

    // ==========================================
    // 2. HANDLE WRITE/UPDATE REQUESTS (POST)
    // ==========================================
<!--    if ($_SERVER['REQUEST_METHOD'] === 'POST') {-->
<!--        $input = json_decode(file_get_contents('php://input'), true);-->

<!--        if (!$input) {-->
<!--            echo json_encode(['status' => 'error', 'message' => 'Invalid JSON payload']);-->
<!--            exit;-->
<!--        }-->

<!--        $action = $input['action'] ?? '';-->
<!--        $data   = $input['data']   ?? [];-->
<!--        $now    = date('Y-m-d H:i:s');-->

        // --- A. AGENT VIEW UPDATES (Order Status) ---
<!--        if ($action === 'update_agent') {-->
<!--            $order_id         = $data['orderId'];-->
<!--            $agent            = $data['agent']           ?? '';-->
<!--            $status           = $data['status']          ?? 'Pending';-->
<!--            $remark           = $data['remark']          ?? '';-->
<!--            $assign_timestamp = !empty($data['assignTimestamp']) ? $data['assignTimestamp'] : null;-->

<!--            $sql = "INSERT INTO order_status (order_id, agent, status, remark, assign_timestamp, timestamp) -->
<!--                    VALUES (?, ?, ?, ?, ?, ?) -->
<!--                    ON CONFLICT (order_id) -->
<!--                    DO UPDATE SET -->
<!--                        agent            = EXCLUDED.agent, -->
<!--                        status           = EXCLUDED.status, -->
<!--                        remark           = EXCLUDED.remark, -->
<!--                        assign_timestamp = COALESCE(EXCLUDED.assign_timestamp, order_status.assign_timestamp),-->
<!--                        timestamp        = EXCLUDED.timestamp";-->

<!--            $stmt = $pdo->prepare($sql);-->
<!--            $stmt->execute([$order_id, $agent, $status, $remark, $assign_timestamp, $now]);-->
<!--            echo json_encode(['status' => 'success', 'message' => 'Order status updated in database']);-->
<!--            exit;-->
<!--        }-->

        // --- B. DISPATCH/SCANNING UPDATES ---
<!--        if ($action === 'update_logistics' || $action === 'bulk_dispatch') {-->
<!--            $ordersToProcess = ($action === 'update_logistics') ? [$data] : $data;-->
<!--            $pdo->beginTransaction(); -->
<!--            foreach ($ordersToProcess as $order) {-->
<!--                $order_id  = $order['orderId'];-->
<!--                $checkStmt = $pdo->prepare("SELECT id FROM scanning WHERE order_id = ?");-->
<!--                $checkStmt->execute([$order_id]);-->
<!--                $exists = $checkStmt->fetch();-->

<!--                if ($exists) {-->
<!--                    $updateSql    = "UPDATE scanning SET ";-->
<!--                    $updateParams = [];-->
<!--                    if (isset($order['dispatchStatus'])) { $updateSql .= "dispatch_status = ?, "; $updateParams[] = $order['dispatchStatus']; }-->
<!--                    if (isset($order['awb']))            { $updateSql .= "awb = ?, ";              $updateParams[] = $order['awb']; }-->
<!--                    if (isset($order['courier']))        { $updateSql .= "courier = ?, ";          $updateParams[] = $order['courier']; }-->
<!--                    if (isset($order['courierName']))    { $updateSql .= "courier_name = ?, ";     $updateParams[] = $order['courierName']; }-->
<!--                    if (isset($order['sku']))            { $updateSql .= "log_remark = ?, ";       $updateParams[] = $order['sku']; } -->
<!--                    if (isset($order['qty']))            { $updateSql .= "log_qty = ?, ";          $updateParams[] = $order['qty']; }-->
<!--                    if (isset($order['remark']))         { $updateSql .= "log_remark = ?, ";       $updateParams[] = $order['remark']; }-->
<!--                    $updateSql   .= "timestamp = ? WHERE order_id = ?";-->
<!--                    $updateParams[] = $now;-->
<!--                    $updateParams[] = $order_id;-->
<!--                    $stmt = $pdo->prepare($updateSql);-->
<!--                    $stmt->execute($updateParams);-->
<!--                } else {-->
<!--                    $insertSql = "INSERT INTO scanning (order_id, dispatch_status, awb, courier, courier_name, log_qty, log_remark, timestamp) -->
<!--                                  VALUES (?, ?, ?, ?, ?, ?, ?, ?)";-->
<!--                    $stmt = $pdo->prepare($insertSql);-->
<!--                    $stmt->execute([-->
<!--                        $order_id,-->
<!--                        $order['dispatchStatus'] ?? 'Pending',-->
<!--                        $order['awb']            ?? '',-->
<!--                        $order['courier']        ?? '',-->
<!--                        $order['courierName']    ?? '',-->
<!--                        $order['qty']            ?? '',-->
<!--                        $order['remark']         ?? '',-->
<!--                        $now-->
<!--                    ]);-->
<!--                }-->
<!--            }-->
<!--            $pdo->commit();-->
<!--            echo json_encode(['status' => 'success', 'message' => 'Logistics/Scans updated in database']);-->
<!--            exit;-->
<!--        }-->

<!--        echo json_encode(['status' => 'error', 'message' => 'Invalid action passed via POST']);-->
<!--    }-->

<!--} catch (PDOException $e) {-->
<!--    if (isset($pdo) && $pdo->inTransaction()) { $pdo->rollBack(); }-->
<!--    echo json_encode(["status" => "error", "message" => "Database error: " . $e->getMessage()]);-->
<!--}-->
<!--?>-->