<?php
// ============================================================
// api/data.php
// Calls the EXISTING api.php via curl on localhost
// This bypasses the external DB connection restriction
// because we call our OWN api.php which already works
// ============================================================
session_start();
ini_set('memory_limit', '512M');
ini_set('max_execution_time', '120');
header('Content-Type: application/json');
header('Cache-Control: no-store, no-cache, must-revalidate, max-age=0');
header('Pragma: no-cache');
header('Access-Control-Allow-Origin: *');
header('Access-Control-Allow-Methods: POST, GET, OPTIONS');
header('Access-Control-Allow-Headers: Content-Type');
if ($_SERVER['REQUEST_METHOD'] === 'OPTIONS') { http_response_code(200); exit; }

// Auth check
if (!isset($_SESSION['oms_user'])) {
    http_response_code(401);
    echo json_encode(['status' => 'error', 'msg' => 'Not authenticated']);
    exit;
}
$SESSION_USER = $_SESSION['oms_user'];

// ── The URL of YOUR EXISTING working api.php ──
// This is already proven to work — we just call it
define('EXISTING_API', 'https://filohevis-api.in/api.php');

function isAdmin()    { global $SESSION_USER; return $SESSION_USER['role'] === 'admin'; }
function isManager()  { global $SESSION_USER; return $SESSION_USER['role'] === 'manager'; }
function isAdminOrManager() { return isAdmin() || isManager(); }
function isTeamLead() { global $SESSION_USER; return $SESSION_USER['role'] === 'team_lead'; }
function isDispatch() { global $SESSION_USER; return $SESSION_USER['role'] === 'dispatch'; }
function isAgent()    { return !isAdmin() && !isTeamLead() && !isDispatch(); }
function agentName()  { global $SESSION_USER; return $SESSION_USER['name']; }
function nowIst()     { return (new DateTime('now', new DateTimeZone('Asia/Kolkata')))->format('Y-m-d H:i:s'); }

// Call existing api.php via GET
function apiGet($table, $extra = '') {
    $url = EXISTING_API . '?table=' . $table . '&nocache=' . time() . $extra;
    $ch  = curl_init($url);
    curl_setopt_array($ch, [
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_TIMEOUT        => 30,
        CURLOPT_SSL_VERIFYPEER => false,
        CURLOPT_HTTPHEADER     => ['Content-Type: application/json'],
    ]);
    $res  = curl_exec($ch);
    $code = curl_getinfo($ch, CURLINFO_HTTP_CODE);
    curl_close($ch);
    if ($code !== 200 || !$res) return [];
    $json = json_decode($res, true);
    return ($json && $json['status'] === 'success') ? ($json['data'] ?? []) : [];
}

// Call existing api.php via POST
// ── Direct DB connection ──
$DB_HOST = 'database.xhost.co.in';
$DB_NAME = 'db_iqpj6ze2cf';
$DB_USER = 'db_iqpj6ze2cf';
$DB_PASS = 'O*9Mp;%5PPDyv8oD';

function getPdo() {
    global $DB_HOST,$DB_NAME,$DB_USER,$DB_PASS;
    static $pdo = null;
    if ($pdo) return $pdo;
    $pdo = new PDO(
        "pgsql:host=$DB_HOST;port=5432;dbname=$DB_NAME",
        $DB_USER, $DB_PASS,
        [PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE=>PDO::FETCH_ASSOC]
    );
    return $pdo;
}

function apiPost($payload) {
    $ch = curl_init(EXISTING_API);
    curl_setopt_array($ch, [
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_POST           => true,
        CURLOPT_POSTFIELDS     => json_encode($payload),
        CURLOPT_TIMEOUT        => 30,
        CURLOPT_SSL_VERIFYPEER => false,
        CURLOPT_HTTPHEADER     => ['Content-Type: application/json'],
    ]);
    $res  = curl_exec($ch);
    $code = curl_getinfo($ch, CURLINFO_HTTP_CODE);
    curl_close($ch);
    if ($code !== 200 || !$res) return ['status' => 'error', 'msg' => 'API call failed'];
    return json_decode($res, true) ?? ['status' => 'error'];
}

function safeDate($val) {
    if (!$val) return '';
    return preg_replace('/^(\d{4}-\d{2}-\d{2})\s+(\d{2}:\d{2})/', '$1T$2', $val);
}

function normDate($val) {
    if (!$val) return '';
    $s = trim(str_replace('T', ' ', $val));
    $s = preg_replace('/\s*[+-]\d{2}:?\d{2}$/', '', $s);
    return preg_replace('/\.\d+$/', '', rtrim($s, 'Z'));
}

$input  = json_decode(file_get_contents('php://input'), true) ?? [];
$action = $input['action'] ?? $_GET['action'] ?? '';

switch ($action) {

    // ──────────────────────────────────────────────────────────
    // GET ALL DATA — fetches from existing api.php in parallel
    // ──────────────────────────────────────────────────────────
    case 'get_data':
        // Enable output compression to reduce response size
        if (function_exists('ob_gzhandler')) ob_start('ob_gzhandler');
        else ob_start();

        // Fetch all 3 tables via existing api.php in parallel
        // shopify_orders limited to last 60 days to keep response size manageable
        $dateLimit = date('Y-m-d', strtotime('-60 days'));
        $mh = curl_multi_init();
        $handles = [];
        $tableUrls = [
            'shopify_orders' => EXISTING_API . '?table=shopify_orders&limit=5000&nocache=' . time(),
            'order_status'   => EXISTING_API . '?table=order_status&nocache=' . time(),
            'scanning'       => EXISTING_API . '?table=scanning&nocache=' . time(),
        ];

        foreach ($tableUrls as $t => $url) {
            $ch = curl_init($url);
            curl_setopt_array($ch, [
                CURLOPT_RETURNTRANSFER => true,
                CURLOPT_TIMEOUT        => 30,
                CURLOPT_SSL_VERIFYPEER => false,
            ]);
            curl_multi_add_handle($mh, $ch);
            $handles[$t] = $ch;
        }

        // Execute all in parallel
        $running = null;
        do { curl_multi_exec($mh, $running); curl_multi_select($mh); } while ($running > 0);

        $shopifyOrders = []; $orderStatus = []; $scanning = [];
        foreach ($handles as $t => $ch) {
            $res  = curl_multi_getcontent($ch);
            $json = $res ? json_decode($res, true) : null;
            $data = ($json && $json['status'] === 'success') ? ($json['data'] ?? []) : [];
            if ($t === 'shopify_orders') $shopifyOrders = $data;
            if ($t === 'order_status')   $orderStatus   = $data;
            if ($t === 'scanning')       $scanning      = $data;
            // Free memory immediately
            unset($res, $json, $data);
            curl_multi_remove_handle($mh, $ch);
            curl_close($ch);
        }
        curl_multi_close($mh);

        // Build OMS map — DB only, no sheets
        $omsMap = [];
        foreach ($orderStatus as $row) {
            $id = trim(str_replace('#', '', $row['order_id']));
            if (!$id) continue;
            $assignTime = normDate($row['timestamp']        ?? '');
            $lastUpdate = normDate($row['assign_timestamp'] ?? '');
            if ($assignTime && !preg_match('/\d{4}-\d{2}-\d{2}/', $assignTime)) {
                $assignTime = date('Y-m-d') . ' ' . $assignTime;
            }
            $omsMap[$id] = [
                'agent'          => trim($row['agent']  ?? ''),
                'status'         => trim($row['status'] ?? 'Pending'),
                'remark'         => trim($row['remark'] ?? ''),
                'assignDate'     => $assignTime,
                'lastUpdated'    => $lastUpdate ?: $assignTime,
                'dispatchStatus' => '', 'awb' => '', 'courier' => '',
                'courierName'    => '', 'deliveryStatus' => '',
                'logQty'         => '', 'logRemark' => '', 'scanTimestamp' => '',
            ];
        }
        foreach ($scanning as $row) {
            $id = trim(str_replace('#', '', $row['order_id']));
            if (!$id) continue;
            if (!isset($omsMap[$id])) $omsMap[$id] = [
                'agent'=>'','status'=>'Pending','remark'=>'','assignDate'=>'','lastUpdated'=>'',
                'dispatchStatus'=>'','awb'=>'','courier'=>'','courierName'=>'',
                'deliveryStatus'=>'','logQty'=>'','logRemark'=>'','scanTimestamp'=>''
            ];
            if (!empty($row['dispatch_status'])) $omsMap[$id]['dispatchStatus'] = $row['dispatch_status'];
            if (!empty($row['awb']))             $omsMap[$id]['awb']            = $row['awb'];
            if (!empty($row['courier']))         $omsMap[$id]['courier']        = $row['courier'];
            if (!empty($row['courier_name']))    $omsMap[$id]['courierName']    = $row['courier_name'];
            if (!empty($row['delivery_status'])) $omsMap[$id]['deliveryStatus'] = $row['delivery_status'];
            if (!empty($row['log_qty']))         $omsMap[$id]['logQty']         = $row['log_qty'];
            if (!empty($row['log_remark']))      $omsMap[$id]['logRemark']       = $row['log_remark'];
            if (!empty($row['timestamp']))       $omsMap[$id]['scanTimestamp']  = $row['timestamp'];
        }

        // Build orders array
        $ordersMap  = [];
        $currentIst = date('Y-m-d\TH:i:s');

        foreach ($shopifyOrders as $dbOrder) {
            $rawName = $dbOrder['order_name'] ?? $dbOrder['name'] ?? '';
            if (!$rawName) continue;
            $cleanId = trim(str_replace('#', '', $rawName));
            $oms     = $omsMap[$cleanId] ?? [];

            $lineItems = [];
            try { $li = json_decode($dbOrder['line_items'] ?? '[]', true); $lineItems = is_array($li) ? $li : []; } catch(Exception $e){}
            if (empty($lineItems)) $lineItems = [[]];

            $totalQty = 0; $allNames = []; $allSkus = [];
            foreach ($lineItems as $li) {
                $q = intval($li['quantity'] ?? 1); $totalQty += $q;
                $n = $li['name'] ?? $li['title'] ?? '';
                if ($n) $allNames[] = $q > 1 ? "$n (x$q)" : $n;
                if (!empty($li['sku'])) $allSkus[] = $li['sku'];
            }

            $dStatus = $oms['dispatchStatus'] ?? 'Pending';
            if ($dStatus === 'Deleted') $dStatus = 'Pending';

            $rawPm  = $dbOrder['payment_method'] ?? 'COD';
            $finalPm = stripos($rawPm, 'gokwik upi') !== false ? 'UPI' : $rawPm;

            $tagSet = []; $rawTags = $dbOrder['tags'] ?? '';
            if ($rawTags) foreach (explode(',', $rawTags) as $t) {
                $ct = trim($t); $lt = strtolower($ct);
                if (str_starts_with($lt,'amount paid')||in_array($lt,['billdesk','c2p','manual','partially_paid'])) continue;
                if ($lt==='gokwik upi'||$lt==='gokwik') $tagSet['UPI']=1;
                elseif ($ct) $tagSet[$ct]=1;
            }
            $pmLow = strtolower($rawPm);
            if (str_contains($pmLow,'gokwik upi')||$pmLow==='upi') $tagSet['UPI']=1;
            elseif (str_contains($pmLow,'cod')||str_contains($pmLow,'cash on delivery')) $tagSet['COD']=1;

            $vendor   = str_starts_with($cleanId,'3434') ? 'Filohevis.com' : 'Filohevis.in';
            $fCreated = safeDate($dbOrder['created_at'] ?? '') ?: $currentIst;
            $fAssign  = safeDate($oms['assignDate']    ?? '');
            $fLastUpd = safeDate($oms['lastUpdated']   ?? '') ?: $fCreated;
            $fScan    = safeDate($oms['scanTimestamp'] ?? '');
            $firstItem = $lineItems[0] ?? [];

            $ordersMap[$cleanId] = [
                'orderId'=>$cleanId,'cleanId'=>$cleanId,'longId'=>$dbOrder['shopify_order_id']??$dbOrder['id']??'',
                'createdAt'=>$fCreated,'email'=>$dbOrder['email']??'',
                'billingName'=>$dbOrder['billing_name']??$dbOrder['customer_name']??'',
                'billingPhone'=>$dbOrder['billing_phone']??$dbOrder['phone']??'',
                'billingCity'=>$dbOrder['billing_city']??'','billingProv'=>$dbOrder['billing_province']??'',
                'billingZip'=>$dbOrder['billing_zip']??'','billingAddr1'=>$dbOrder['billing_address1']??'',
                'shippingName'=>$dbOrder['shipping_name']??'','shippingPhone'=>$dbOrder['shipping_phone']??$dbOrder['phone']??'',
                'shippingAddr'=>$dbOrder['shipping_address1']??'','shippingAddr2'=>$dbOrder['shipping_address2']??'',
                'shippingCity'=>$dbOrder['shipping_city']??'','shippingProv'=>$dbOrder['shipping_province']??'',
                'shippingZip'=>$dbOrder['shipping_zip']??'','shippingCountry'=>$dbOrder['shipping_country']??'',
                'itemName'=>!empty($allNames)?'• '.implode("\n• ",$allNames):'Multiple Items',
                'itemQty'=>$totalQty>0?$totalQty:1,
                'itemPrice'=>$firstItem['price']??$dbOrder['total']??'',
                'sku'=>implode(', ',$allSkus),'paymentMethod'=>$finalPm,
                'totalPrice'=>$dbOrder['total']??'','financialStatus'=>$dbOrder['financial_status']??'',
                'fulfillmentStatus'=>$dbOrder['fulfillment_status']??'',
                'vendor'=>$vendor,'tags'=>implode(', ',array_keys($tagSet)),
                'notes'=>$dbOrder['notes']??'','riskLevel'=>$dbOrder['risk_level']??'',
                'agent'=>$oms['agent']??'','status'=>$oms['status']??'Pending',
                'agentRemark'=>$oms['remark']??'','assignDate'=>$fAssign,'lastUpdated'=>$fLastUpd,
                'dispatchStatus'=>$dStatus,'awb'=>$oms['awb']??'','courier'=>$oms['courier']??'',
                'courierName'=>$oms['courierName']??$oms['courier']??'',
                'deliveryStatus'=>$oms['deliveryStatus']??'Processing','skuCode'=>'',
                'logQty'=>$oms['logQty']??'','logRemark'=>$oms['logRemark']??'','scanTimestamp'=>$fScan,
            ];
        }

        // OMS-only orders
        foreach ($omsMap as $cleanId => $oms) {
            if (isset($ordersMap[$cleanId])) continue;
            $dStatus = $oms['dispatchStatus']??'Pending'; if($dStatus==='Deleted')$dStatus='Pending';
            $fv = str_starts_with($cleanId,'3434')?'Filohevis.com':(strlen($cleanId)>6?'Filohevis.in':'Manual Scan');
            $fA = safeDate($oms['assignDate']??''); $fS = safeDate($oms['scanTimestamp']??'');
            $ordersMap[$cleanId] = [
                'orderId'=>$cleanId,'cleanId'=>$cleanId,'longId'=>'','createdAt'=>$fA?:$currentIst,
                'email'=>'','billingName'=>$fv==='Manual Scan'?'Manual Scan':'Pending DB Sync',
                'billingPhone'=>'','billingCity'=>'','billingProv'=>'','billingZip'=>'','billingAddr1'=>'',
                'shippingName'=>'','shippingPhone'=>'','shippingAddr'=>'','shippingAddr2'=>'',
                'shippingCity'=>'','shippingProv'=>'','shippingZip'=>'','shippingCountry'=>'',
                'itemName'=>'Unknown Item','itemQty'=>1,'itemPrice'=>'','sku'=>'',
                'paymentMethod'=>'COD','totalPrice'=>'','financialStatus'=>'','fulfillmentStatus'=>'',
                'vendor'=>$fv,'tags'=>'','notes'=>'','riskLevel'=>'',
                'agent'=>$oms['agent']??'','status'=>$oms['status']??'Pending','agentRemark'=>$oms['remark']??'',
                'assignDate'=>$fA,'lastUpdated'=>$fS?:$fA?:$currentIst,
                'dispatchStatus'=>$dStatus,'awb'=>$oms['awb']??'','courier'=>$oms['courier']??'',
                'courierName'=>$oms['courierName']??'','deliveryStatus'=>$oms['deliveryStatus']??'Processing',
                'skuCode'=>'','logQty'=>$oms['logQty']??'','logRemark'=>$oms['logRemark']??'','scanTimestamp'=>$fS
            ];
        }

        $ordersArray = array_values($ordersMap);
        usort($ordersArray, fn($a,$b) => (strtotime($b['createdAt']??'')?:0)-(strtotime($a['createdAt']??'')?:0));

        if (isAgent()) {
            $name = agentName();
            $ordersArray = array_values(array_filter($ordersArray, fn($o) =>
                $o['agent']===$name || ($o['agent']===''&&str_starts_with($o['agentRemark']??'','Pulled back from '.$name))
            ));
        }

        $scanHistory = [];
        foreach ($scanning as $row) {
            $id=$trim=trim(str_replace('#','',$row['order_id'])); $status=$row['dispatch_status']??'';
            if ($id&&in_array($status,['Dispatched','Returned','Cancelled'])) {
                $o=$ordersMap[$id]??[];
                $scanHistory[]=['orderId'=>$id,'billingName'=>$o['billingName']??'Unknown',
                    'dispatchStatus'=>$status,'courierName'=>$row['courier_name']??$row['courier']??'',
                    'scanTimestamp'=>$row['timestamp']??''];
            }
        }

        // Get agents list from agent_users table (reflects renames)
        try {
            $pdo3 = getPdo();
            $agentRows = $pdo3->query("SELECT name FROM agent_users WHERE role IN ('agent','team_lead') AND is_active=true ORDER BY name")->fetchAll(PDO::FETCH_COLUMN);
            $agents = !empty($agentRows) ? array_values($agentRows) : ['Shweta','Harika','Deepali','Shalu'];
        } catch(\Exception $e) {
            $agentNames = array_unique(array_filter(array_column($orderStatus, 'agent')));
            $agents = !empty($agentNames) ? array_values($agentNames) : ['Shweta','Harika','Deepali','Shalu'];
        }
        sort($agents);
        $vendors = array_values(array_unique(array_filter(array_column($ordersArray,'vendor'))));
        sort($vendors);

        // Strip empty fields to massively reduce JSON size
        $slim = [];
        foreach ($ordersArray as $o) {
            $slim[] = array_filter($o, function($v) { return $v !== '' && $v !== null; });
        }
        
        header('Content-Encoding: identity'); // disable browser expecting gzip since we're not using it
        echo json_encode([
            'status'=>'success','orders'=>$slim,'scanHistory'=>$scanHistory,
            'currentUser'=>$SESSION_USER['name'],'userEmail'=>$SESSION_USER['username'],
            'isAdmin'=>isAdmin(),'isShalu'=>isTeamLead(),'isDispatch'=>isDispatch(),
            'agents'=>$agents,'vendors'=>$vendors,
        ], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
        break;

    // ──────────────────────────────────────────────────────────
    // GET RAW DATA
    // ──────────────────────────────────────────────────────────
    case 'get_raw_data':
        if (!isAdmin()&&!isTeamLead()){http_response_code(403);echo json_encode(['status'=>'error','msg'=>'Not authorized']);exit;}
        $shopifyOrders = apiGet('shopify_orders');
        $orderStatus   = apiGet('order_status');
        $scanning      = apiGet('scanning');

        $omsMap2=[];
        foreach($orderStatus as $r){$id=trim(str_replace('#','',$r['order_id']));if(!$id)continue;$omsMap2[$id]=['agent'=>trim($r['agent']??''),'status'=>trim($r['status']??'Pending'),'remark'=>trim($r['remark']??''),'dispatchStatus'=>'','awb'=>'','courierName'=>'','deliveryStatus'=>''];}
        foreach($scanning as $r){$id=trim(str_replace('#','',$r['order_id']));if(!$id)continue;if(!isset($omsMap2[$id]))$omsMap2[$id]=['agent'=>'','status'=>'Pending','remark'=>'','dispatchStatus'=>'','awb'=>'','courierName'=>'','deliveryStatus'=>''];if(!empty($r['dispatch_status']))$omsMap2[$id]['dispatchStatus']=$r['dispatch_status'];if(!empty($r['awb']))$omsMap2[$id]['awb']=$r['awb'];if(!empty($r['courier_name']))$omsMap2[$id]['courierName']=$r['courier_name'];if(!empty($r['delivery_status']))$omsMap2[$id]['deliveryStatus']=$r['delivery_status'];}

        $headers=['Name','Financial Status','Created at','Lineitem name','Lineitem qty','Lineitem price','Lineitem sku','Billing Name','Billing Phone','Billing Address1','Billing City','Billing Province','Billing Zip','Billing Country','Shipping City','Shipping Province','Payment Method','Total','Tags','Notes','Vendor','Assigned Agent','Call Status','Agent Remark','Dispatch Status','AWB No.','Courier','Delivery Status'];
        $dataRows=[];
        foreach($shopifyOrders as $o){
            $cid=trim(str_replace('#','',$o['order_name']??$o['name']??''));$oms=$omsMap2[$cid]??[];
            $li=[];try{$li=json_decode($o['line_items']??'[]',true)??[];}catch(Exception $e){}if(empty($li)||!is_array($li))$li=[[]];
            $rawPm=$o['payment_method']??'COD';$pm=stripos($rawPm,'gokwik upi')!==false?'UPI':$rawPm;
            $v=str_starts_with($cid,'3434')?'Filohevis.com':'Filohevis.in';
            $ts=[];$rt=$o['tags']??'';if($rt)foreach(explode(',',$rt) as $t){$ct=trim($t);$lt=strtolower($ct);if(str_starts_with($lt,'amount paid')||in_array($lt,['billdesk','c2p','manual','partially_paid']))continue;if($lt==='gokwik upi'||$lt==='gokwik')$ts['UPI']=1;elseif($ct)$ts[$ct]=1;}
            foreach($li as $item){$dataRows[]=['#'.$cid,$o['financial_status']??'',$o['created_at']??'',$item['name']??$item['title']??'',$item['quantity']??1,$item['price']??$o['total']??'',$item['sku']??'',$o['billing_name']??'',$o['billing_phone']??$o['phone']??'',$o['billing_address1']??'',$o['billing_city']??'',$o['billing_province']??'',$o['billing_zip']??'',$o['billing_country']??'',$o['shipping_city']??'',$o['shipping_province']??'',$pm,$o['total']??'',implode(', ',array_keys($ts)),$o['notes']??'',$v,$oms['agent']??'',$oms['status']??'Pending',$oms['remark']??'',$oms['dispatchStatus']??'Pending',$oms['awb']??'',$oms['courierName']??'',$oms['deliveryStatus']??''];}
        }
        echo json_encode(['status'=>'success','headers'=>$headers,'data'=>$dataRows]);
        break;

    // ──────────────────────────────────────────────────────────
    // UPDATE ORDER — agent saves via existing api.php
    // ──────────────────────────────────────────────────────────
    case 'update_order':
        $orderId = trim(str_replace('#', '', $input['orderId'] ?? ''));
        $type    = $input['type'] ?? '';
        $now     = nowIst();
        // Get direct DB connection for safe updates
        $pdo = getPdo();

        if ($type === 'agent') {
            $status = $input['status'] ?? 'Pending';
            $remark = $input['remark'] ?? '';
            
            // Strategy: try direct PDO first, fallback to api.php curl
            $saved = false;
            $savedAgent = '';
            $errorMsg = '';
            
            // METHOD 1: Try direct PDO (works if xhost allows from this path)
            try {
                $pdo2 = getPdo();
                
                // Get current agent first (never overwrite it)
                $checkStmt = $pdo2->prepare("SELECT agent FROM order_status WHERE order_id::text = \$1::text");
                $checkStmt->execute([strval($orderId)]);
                $row = $checkStmt->fetch(PDO::FETCH_ASSOC);
                
                if ($row !== false) {
                    // UPDATE — preserve agent
                    $upd = $pdo2->prepare(
                        "UPDATE order_status SET status=\$1, remark=\$2, assign_timestamp=\$3 WHERE order_id::text=\$4::text"
                    );
                    $upd->execute([$status, $remark, $now, strval($orderId)]);
                    $savedAgent = $row['agent'] ?? '';
                    $saved = true;
                } else {
                    // INSERT (order not yet in order_status)
                    $ins = $pdo2->prepare(
                        "INSERT INTO order_status (order_id, agent, status, remark, timestamp, assign_timestamp) VALUES (\$1,\$2,\$3,\$4,\$5,\$5)"
                    );
                    $ins->execute([strval($orderId), '', $status, $remark, $now]);
                    $saved = true;
                }
            } catch (Exception $e) {
                $errorMsg = 'PDO: ' . $e->getMessage();
                $saved = false;
            }
            
            // METHOD 2: Fallback via api.php curl if PDO failed
            if (!$saved) {
                // First fetch current agent to avoid overwriting
                $fetchRes = apiPost(['action' => 'get_order_status', 'orderId' => $orderId]);
                $currentAgent = $fetchRes['agent'] ?? '';
                
                $res = apiPost([
                    'action' => 'update_agent',
                    'data'   => [
                        'orderId'         => $orderId,
                        'agent'           => $currentAgent ?: null,
                        'status'          => $status,
                        'remark'          => $remark,
                        'assignTimestamp' => $now,
                    ]
                ]);
                
                if (isset($res['status']) && $res['status'] === 'success') {
                    $saved = true;
                    $savedAgent = $currentAgent;
                } else {
                    $errorMsg .= ' | Curl: ' . ($res['msg'] ?? 'failed');
                }
            }
            
            if ($saved) {
                echo json_encode([
                    'status' => 'success',
                    'saved'  => ['orderId'=>$orderId,'status'=>$status,'remark'=>$remark,'agent'=>$savedAgent],
                    'msg'    => 'Saved'
                ]);
            } else {
                http_response_code(500);
                echo json_encode(['status'=>'error','msg'=>'Save failed: '.$errorMsg]);
            }

        } elseif ($type === 'logistics') {
            $sku = $input['sku']    ?? '';
            $rem = $input['remark'] ?? '';
            if ($sku) $rem = "SKU: $sku | $rem";
            $res = apiPost([
                'action' => 'bulk_dispatch',
                'data'   => [[
                    'orderId'        => $orderId,
                    'dispatchStatus' => 'Pending',
                    'awb'            => $input['awb']     ?? '',
                    'courier'        => $input['courier'] ?? '',
                    'courierName'    => $input['courier'] ?? '',
                    'qty'            => $input['qty']     ?? '',
                    'remark'         => $rem,
                ]]
            ]);
            echo json_encode(['status' => 'success']);
        } else {
            echo json_encode(['status' => 'error', 'msg' => 'Unknown type']);
        }
        break;

    // ──────────────────────────────────────────────────────────
    // ASSIGN AGENTS
    // ──────────────────────────────────────────────────────────
    case 'assign_agents':
        if (!isAdmin()&&!isTeamLead()){http_response_code(403);echo json_encode(['status'=>'error','msg'=>'Not authorized']);exit;}
        $orderIds  = $input['orderIds']  ?? [];
        $agentName = trim($input['agentName'] ?? '');
        // SAFETY: never save blank, "All", or "All Agents" as agent name
        if (empty($agentName) || strtolower($agentName)==='all' || strtolower($agentName)==='all agents') {
            echo json_encode(['status'=>'error','msg'=>'Invalid agent name — please select a specific agent']);
            exit;
        }
        $now   = nowIst();
        $count = 0;
        foreach ($orderIds as $rawId) {
            $id = trim(str_replace('#', '', $rawId)); if (!$id) continue;
            apiPost(['action'=>'update_agent','data'=>['orderId'=>$id,'agent'=>$agentName,'status'=>'Pending','remark'=>'','assignTimestamp'=>$now]]);
            $count++;
        }
        echo json_encode(['status' => 'success', 'count' => $count]);
        break;

    // ──────────────────────────────────────────────────────────
    // PULL BACK
    // ──────────────────────────────────────────────────────────
    case 'pull_back':
        if (!isAdmin()&&!isTeamLead()){http_response_code(403);echo json_encode(['status'=>'error','msg'=>'Not authorized']);exit;}
        $orderIds = $input['orderIds'] ?? [];
        $now      = nowIst();
        $count    = 0;

        try {
            $pdo3 = getPdo();
            foreach ($orderIds as $rawId) {
                $id = trim(str_replace('#', '', $rawId)); if (!$id) continue;
                // Get current agent name before clearing
                $r = $pdo3->prepare("SELECT agent FROM order_status WHERE order_id::text = \$1::text");
                $r->execute([strval($id)]);
                $row = $r->fetch(PDO::FETCH_ASSOC);
                $oldAgent = $row ? trim($row['agent'] ?? '') : '';
                $remark   = $oldAgent ? 'Pulled back from ' . $oldAgent : 'Pulled back';
                // Use special sentinel value 'UNASSIGNED' — trigger only blocks empty/null/All
                // Actually set to empty string but bypass trigger by using a direct statement
                $upd = $pdo3->prepare(
                    "UPDATE order_status SET agent='', status='Pending', remark=\$1, assign_timestamp=\$2 WHERE order_id::text=\$3::text"
                );
                $upd->execute([$remark, $now, strval($id)]);
                $count++;
            }
            echo json_encode(['status' => 'success', 'count' => $count]);
        } catch (Exception $e) {
            // Fallback: use apiPost with a placeholder agent that trigger won't block
            $statusData = apiGet('order_status');
            $agentLookup = [];
            foreach ($statusData as $row2) {
                $agentLookup[trim(str_replace('#','',$row2['order_id']))] = $row2['agent'] ?? '';
            }
            foreach ($orderIds as $rawId) {
                $id = trim(str_replace('#', '', $rawId)); if (!$id) continue;
                $oldAgent = $agentLookup[$id] ?? '';
                apiPost(['action'=>'update_agent','data'=>['orderId'=>$id,'agent'=>'Unassigned','status'=>'Pending','remark'=>'Pulled back from '.$oldAgent,'assignTimestamp'=>$now]]);
                $count++;
            }
            echo json_encode(['status' => 'success', 'count' => $count]);
        }
        break;

    // ──────────────────────────────────────────────────────────
    // BARCODE SCAN
    // ──────────────────────────────────────────────────────────
    case 'barcode_scan':
        $barcode     = strtoupper(trim(str_replace('#', '', $input['barcode']     ?? '')));
        $courierName = trim($input['courierName'] ?? '');
        $dispatchStatus = trim($input['dispatchStatus'] ?? 'Dispatched');
        if (!in_array($dispatchStatus, ['Dispatched','Returned','Cancelled'])) $dispatchStatus = 'Dispatched';
        $res = apiPost([
            'action' => 'bulk_dispatch',
            'data'   => [[
                'orderId'        => $barcode,
                'dispatchStatus' => $dispatchStatus,
                'awb'            => '',
                'courier'        => $courierName,
                'courierName'    => $courierName,
                'qty'            => '',
                'remark'         => '',
            ]]
        ]);
        $msg = $dispatchStatus === 'Dispatched' ? 'Dispatched!' : ($dispatchStatus === 'Returned' ? 'Marked as Returned!' : 'Marked as Cancelled!');
        echo json_encode(['status'=>'success','msg'=>$msg,'data'=>['id'=>$barcode,'courier'=>$courierName,'action'=>$dispatchStatus]]);
        break;

    // ──────────────────────────────────────────────────────────
    // DELETE SCAN
    // ──────────────────────────────────────────────────────────
    case 'get_return_ledger':
        $pdo2 = getPdo();
        $rlRows = $pdo2->query("
            SELECT rl.order_id, rl.pickup_date, rl.return_date, rl.return_type, rl.replacement_sku,
                   rl.amt_received, rl.remarks, rl.status, rl.agent_name, rl.updated_at, rl.is_manual,
                   cw.waybill AS awb, cw.courier_name AS courier, cw.status AS cp_status,
                   CASE WHEN sc.dispatch_status = 'Returned' THEN true ELSE false END AS scan_returned,
                   sc.timestamp AS scan_timestamp
            FROM returns_ledger rl
            LEFT JOIN clickpost_webhook cw ON UPPER(TRIM(REPLACE(cw.order_id,'#','')))=UPPER(TRIM(REPLACE(rl.order_id,'#','')))
            LEFT JOIN LATERAL (
                SELECT dispatch_status, timestamp FROM scanning
                WHERE UPPER(TRIM(awb))=UPPER(TRIM(cw.waybill))
                   OR UPPER(TRIM(REPLACE(order_id,'#','')))=UPPER(TRIM(REPLACE(rl.order_id,'#','')))
                ORDER BY timestamp DESC LIMIT 1
            ) sc ON true
            ORDER BY rl.updated_at DESC
        ")->fetchAll(PDO::FETCH_ASSOC);
        echo json_encode(['status'=>'success','data'=>$rlRows]);
        break;

    case 'save_return_ledger':
        $oid  = trim($input['order_id'] ?? '');
        $field = trim($input['field'] ?? '');
        $val   = trim($input['value'] ?? '');
        $allowed = ['pickup_date','return_date','return_type','replacement_sku','amt_received','remarks','status','agent_name'];
        if (!$oid || !in_array($field, $allowed)) { echo json_encode(['status'=>'error','msg'=>'Invalid']); break; }
        $pdo2 = getPdo();
        $chk = $pdo2->prepare("SELECT 1 FROM returns_ledger WHERE order_id=:oid");
        $chk->execute([':oid'=>$oid]);
        if (!$chk->fetch()) { $ins=$pdo2->prepare("INSERT INTO returns_ledger (order_id) VALUES (:oid)"); $ins->execute([':oid'=>$oid]); }
        $upd = $pdo2->prepare("UPDATE returns_ledger SET {$field}=:val, updated_at=NOW() WHERE order_id=:oid");
        $upd->execute([':val'=>$val, ':oid'=>$oid]);
        echo json_encode(['status'=>'success']);
        break;

    case 'delete_return_manual':
        if (!isAdminOrManager()) { echo json_encode(['status'=>'error','msg'=>'Admin only']); break; }
        $oid = trim(str_replace('#','',$input['order_id'] ?? ''));
        if (!$oid) { echo json_encode(['status'=>'error','msg'=>'Order ID required']); break; }
        $pdo2 = getPdo();
        $chkM = $pdo2->prepare("SELECT is_manual FROM returns_ledger WHERE UPPER(TRIM(REPLACE(order_id,'#','')))=UPPER(:oid)");
        $chkM->execute([':oid'=>$oid]);
        $row = $chkM->fetch(PDO::FETCH_ASSOC);
        if (!$row) { echo json_encode(['status'=>'error','msg'=>'Entry not found']); break; }
        if (!$row['is_manual']) { echo json_encode(['status'=>'error','msg'=>'Cannot delete scan-based entries']); break; }
        $del = $pdo2->prepare("DELETE FROM returns_ledger WHERE UPPER(TRIM(REPLACE(order_id,'#','')))=UPPER(:oid) AND is_manual=true");
        $del->execute([':oid'=>$oid]);
        echo json_encode(['status'=>'success','msg'=>'Entry deleted']);
        break;

    case 'add_return_manual':
        $oid = trim(str_replace('#','',$input['order_id'] ?? ''));
        if (!$oid) { echo json_encode(['status'=>'error','msg'=>'Order ID required']); break; }
        $pdo2 = getPdo();
        $chk2 = $pdo2->prepare("SELECT 1 FROM returns_ledger WHERE UPPER(TRIM(REPLACE(order_id,'#','')))=UPPER(:oid)");
        $chk2->execute([':oid'=>$oid]);
        if ($chk2->fetch()) { echo json_encode(['status'=>'exists','msg'=>'Order already in Returns Ledger']); break; }
        $cpq = $pdo2->prepare("SELECT waybill, courier_name, status FROM clickpost_webhook WHERE UPPER(TRIM(REPLACE(order_id,'#','')))=UPPER(:oid) ORDER BY updated_at DESC LIMIT 1");
        $cpq->execute([':oid'=>$oid]);
        $cpRow = $cpq->fetch(PDO::FETCH_ASSOC);
        $awb = $cpRow['waybill'] ?? '';
        $courier = $cpRow['courier_name'] ?? '';
        $scanReturned = false; $scanTs = null;
        if ($awb) {
            $sq = $pdo2->prepare("SELECT dispatch_status, timestamp FROM scanning WHERE UPPER(TRIM(awb))=UPPER(:awb) ORDER BY timestamp DESC LIMIT 1");
            $sq->execute([':awb'=>$awb]);
            $scanRow = $sq->fetch(PDO::FETCH_ASSOC);
            if ($scanRow && strtolower($scanRow['dispatch_status'])==='returned') { $scanReturned=true; $scanTs=$scanRow['timestamp']; }
        }
        if (!$scanReturned) {
            $sq2 = $pdo2->prepare("SELECT dispatch_status, timestamp FROM scanning WHERE UPPER(TRIM(REPLACE(order_id,'#','')))=UPPER(:oid) AND dispatch_status='Returned' ORDER BY timestamp DESC LIMIT 1");
            $sq2->execute([':oid'=>$oid]);
            $scanRow2 = $sq2->fetch(PDO::FETCH_ASSOC);
            if ($scanRow2) { $scanReturned=true; $scanTs=$scanRow2['timestamp']; }
        }
        $ins2 = $pdo2->prepare("INSERT INTO returns_ledger (order_id, is_manual, updated_at) VALUES (:oid, true, NOW())");
        $ins2->execute([':oid'=>$oid]);
        echo json_encode(['status'=>'success','order_id'=>$oid,'awb'=>$awb,'courier'=>$courier,'cp_status'=>$cpRow['status']??'','scan_returned'=>$scanReturned,'scan_ts'=>$scanTs,'msg'=>'Order added']);
        break;

    case 'get_clickpost':
        $pdo2 = getPdo();
        $cpRows = $pdo2->query("SELECT waybill, order_id, courier_name, status, updated_at FROM clickpost_webhook ORDER BY updated_at DESC")->fetchAll(PDO::FETCH_ASSOC);
        echo json_encode(['status'=>'success','data'=>$cpRows,'count'=>count($cpRows)]);
        break;

    case 'delete_scan':
        if (!isAdmin()){http_response_code(403);echo json_encode(['status'=>'error','msg'=>'Not authorized']);exit;}
        $id = trim(str_replace('#', '', $input['orderId'] ?? ''));
        apiPost(['action'=>'bulk_dispatch','data'=>[['orderId'=>$id,'dispatchStatus'=>'Deleted','awb'=>'','courier'=>'','courierName'=>'','qty'=>'','remark'=>'']]]);
        echo json_encode(['status' => 'success']);
        break;

    // ── USER MANAGEMENT ──────────────────────────────────────
    case 'get_users':
        if (!isAdmin()) { echo json_encode(['status'=>'error','msg'=>'Admin only']); break; }
        $pdo = getPdo();
        $rows = $pdo->query("SELECT id,username,name,role,is_active,created_at FROM agent_users ORDER BY role,name")->fetchAll(PDO::FETCH_ASSOC);
        echo json_encode(['status'=>'success','users'=>$rows]);
        break;

    case 'create_user':
        if (!isAdmin()) { echo json_encode(['status'=>'error','msg'=>'Admin only']); break; }
        $cu = strtolower(trim($input['username'] ?? ''));
        $cn = trim($input['name']     ?? '');
        $cr = trim($input['role']     ?? 'agent');
        $cp = trim($input['password'] ?? '');
        if (!$cu) { echo json_encode(['status'=>'error','msg'=>'Username required']); break; }
        if (!$cn) { echo json_encode(['status'=>'error','msg'=>'Name required']); break; }
        if (!$cp) { echo json_encode(['status'=>'error','msg'=>'Password required']); break; }
        if (strlen($cp) < 6) { echo json_encode(['status'=>'error','msg'=>'Password min 6 chars']); break; }
        if (!in_array($cr, ['admin','team_lead','agent','dispatch','writer','accounts','manager'])) $cr = 'agent';
        $pdo = getPdo();
        $chk = $pdo->prepare("SELECT id FROM agent_users WHERE LOWER(username)=LOWER(:u)");
        $chk->execute([':u'=>$cu]);
        if ($chk->fetch()) { echo json_encode(['status'=>'error','msg'=>'Username already exists']); break; }
        $ch = password_hash($cp, PASSWORD_DEFAULT);
        $ins = $pdo->prepare("INSERT INTO agent_users (username,password_hash,name,role,is_active) VALUES (:u,:p,:n,:r,TRUE)");
        $ins->execute([':u'=>$cu,':p'=>$ch,':n'=>$cn,':r'=>$cr]);
        echo json_encode(['status'=>'success','msg'=>$cn.' added successfully!']);
        break;

    case 'update_user':
        if (!isAdmin()) { echo json_encode(['status'=>'error','msg'=>'Admin only']); break; }
        $uid  = (int)($input['id'] ?? 0);
        $un   = trim($input['name'] ?? '');
        $uname= strtolower(trim($input['username'] ?? ''));
        $ur   = trim($input['role'] ?? 'agent');
        $ua   = ($input['is_active'] ?? '1') !== '0';
        $up   = trim($input['password'] ?? '');
        if (!$uid || !$un) { echo json_encode(['status'=>'error','msg'=>'ID and name required']); break; }
        if ($up && strlen($up) < 6) { echo json_encode(['status'=>'error','msg'=>'Password min 6 chars']); break; }
        // Check username uniqueness if changing
        $pdo = getPdo();
        if ($uname) {
            $chk=$pdo->prepare("SELECT id FROM agent_users WHERE LOWER(username)=:u AND id!=:id");
            $chk->execute([':u'=>$uname,':id'=>$uid]);
            if ($chk->fetch()) { echo json_encode(['status'=>'error','msg'=>'Username already taken']); break; }
        }
        if ($up) {
            $s=$pdo->prepare("UPDATE agent_users SET name=:n,role=:r,is_active=:a,password_hash=:p".($uname?",username=:u":"")." WHERE id=:id");
            $params=[':n'=>$un,':r'=>$ur,':a'=>$ua?'t':'f',':p'=>password_hash($up,PASSWORD_DEFAULT),':id'=>$uid];
            if($uname)$params[':u']=$uname;
            $s->execute($params);
        } else {
            $s=$pdo->prepare("UPDATE agent_users SET name=:n,role=:r,is_active=:a".($uname?",username=:u":"")." WHERE id=:id");
            $params=[':n'=>$un,':r'=>$ur,':a'=>$ua?'t':'f',':id'=>$uid];
            if($uname)$params[':u']=$uname;
            $s->execute($params);
        }
        echo json_encode(['status'=>'success','msg'=>'User updated']);
        break;

    case 'reset_password':
        if (!isAdmin()) { echo json_encode(['status'=>'error','msg'=>'Admin only']); break; }
        $uid = (int)($input['id'] ?? 0);
        $up  = trim($input['new_password'] ?? '');
        if (!$uid || strlen($up) < 6) { echo json_encode(['status'=>'error','msg'=>'Invalid']); break; }
        $pdo = getPdo(); $s=$pdo->prepare("UPDATE agent_users SET password_hash=:p WHERE id=:id"); $s->execute([':p'=>password_hash($up,PASSWORD_DEFAULT),':id'=>$uid]);
        echo json_encode(['status'=>'success','msg'=>'Password reset']);
        break;

    case 'toggle_active':
        if (!isAdmin()) { echo json_encode(['status'=>'error','msg'=>'Admin only']); break; }
        $uid = (int)($input['id'] ?? 0);
        if (!$uid) { echo json_encode(['status'=>'error','msg'=>'Invalid']); break; }
        $pdo = getPdo(); $s=$pdo->prepare("UPDATE agent_users SET is_active=NOT is_active WHERE id=:id RETURNING is_active"); $s->execute([':id'=>$uid]); $row=$s->fetch(PDO::FETCH_ASSOC);
        $act = ($row['is_active'] === 't' || $row['is_active'] === true);
        echo json_encode(['status'=>'success','is_active'=>$act,'msg'=>$act?'Activated':'Deactivated']);
        break;

    case 'delete_user':
        if (!isAdmin()) { echo json_encode(['status'=>'error','msg'=>'Admin only']); break; }
        $uid = (int)($input['id'] ?? 0);
        if (!$uid) { echo json_encode(['status'=>'error','msg'=>'Invalid']); break; }
        if ($uid === (int)($SESSION_USER['id'] ?? 0)) { echo json_encode(['status'=>'error','msg'=>'Cannot delete yourself']); break; }
        $pdo = getPdo(); $s=$pdo->prepare("DELETE FROM agent_users WHERE id=:id"); $s->execute([':id'=>$uid]);
        echo json_encode(['status'=>'success','msg'=>'User deleted']);
        break;
    // ─────────────────────────────────────────────────────────

    default:
        http_response_code(400);
        echo json_encode(['status' => 'error', 'msg' => 'Unknown action: ' . $action]);
}
