<?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()]);
}
?>
