<?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 isNdr()      { global $SESSION_USER; return $SESSION_USER['role'] === 'ndr'; }
function isDispatch() { global $SESSION_USER; return $SESSION_USER['role'] === 'dispatch'; }
function isAgent()    { return !isAdminOrManager() && !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'=>isAdminOrManager(),'isShalu'=>isTeamLead(),'isDispatch'=>isDispatch(),
            'userRole'=>$SESSION_USER['role'] ?? '',
            'permissions'=>(function() use ($SESSION_USER) {
                try {
                    $p = getPdo();
                    $p->exec("ALTER TABLE agent_users ADD COLUMN IF NOT EXISTS permissions TEXT DEFAULT ''");
                    $st = $p->prepare("SELECT permissions FROM agent_users WHERE id=:id");
                    $st->execute([':id'=>$SESSION_USER['id'] ?? 0]);
                    $r = $st->fetch(PDO::FETCH_ASSOC);
                    return $r ? trim($r['permissions'] ?? '') : '';
                } catch (Exception $e) { return ''; }
            })(),
            'agents'=>$agents,'vendors'=>$vendors,
        ], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
        break;

    // ──────────────────────────────────────────────────────────
    // GET RAW DATA
    // ──────────────────────────────────────────────────────────
    case 'get_raw_data':
        if (!isAdmin()&&!isTeamLead()&&!isManager()){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()&&!isManager()){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()&&!isManager()){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,permissions 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','ndr'])) $cr = 'agent';
        $pdo = getPdo();
        $pdo->exec("ALTER TABLE agent_users ADD COLUMN IF NOT EXISTS permissions TEXT DEFAULT ''");
        $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; }
        $cperm = trim($input['permissions'] ?? '');
        $ch = password_hash($cp, PASSWORD_DEFAULT);
        $ins = $pdo->prepare("INSERT INTO agent_users (username,password_hash,name,role,is_active,permissions) VALUES (:u,:p,:n,:r,TRUE,:perm)");
        $ins->execute([':u'=>$cu,':p'=>$ch,':n'=>$cn,':r'=>$cr,':perm'=>$cperm]);
        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();
        $pdo->exec("ALTER TABLE agent_users ADD COLUMN IF NOT EXISTS permissions TEXT DEFAULT ''");
        $uperm = trim($input['permissions'] ?? '');
        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,permissions=:perm,password_hash=:p".($uname?",username=:u":"")." WHERE id=:id");
            $params=[':n'=>$un,':r'=>$ur,':a'=>$ua?'t':'f',':perm'=>$uperm,':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,permissions=:perm".($uname?",username=:u":"")." WHERE id=:id");
            $params=[':n'=>$un,':r'=>$ur,':a'=>$ua?'t':'f',':perm'=>$uperm,':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;
    // ─────────────────────────────────────────────────────────
    // UPLOAD FILES — payment_files table
    // ─────────────────────────────────────────────────────────
    case 'save_payment_batch':
        $fileType = trim($input['file_type'] ?? '');
        $records  = $input['records'] ?? [];
        $batchId  = trim($input['batch_id'] ?? date('Y-m-d'));
        if (!$fileType) { echo json_encode(['status'=>'error','msg'=>'file_type required']); break; }
        if (!is_array($records) || !count($records)) { echo json_encode(['status'=>'error','msg'=>'No records']); break; }
        $pdo = getPdo();
        // Create table if it doesn't exist (no-op if already created)
        $pdo->exec("CREATE TABLE IF NOT EXISTS payment_files (
            id SERIAL PRIMARY KEY,
            file_type VARCHAR(50) NOT NULL,
            order_id VARCHAR(150) DEFAULT '',
            awb VARCHAR(150) DEFAULT '',
            amount VARCHAR(50) DEFAULT '',
            gateway VARCHAR(100) DEFAULT '',
            payment_method VARCHAR(100) DEFAULT '',
            utr VARCHAR(200) DEFAULT '',
            batch_id VARCHAR(20) DEFAULT '',
            created_at TIMESTAMP DEFAULT NOW()
        )");
        // Table may pre-exist with a different schema — add any missing columns (no-op if present)
        $pdo->exec("ALTER TABLE payment_files
            ADD COLUMN IF NOT EXISTS file_type VARCHAR(50) DEFAULT '',
            ADD COLUMN IF NOT EXISTS order_id VARCHAR(150) DEFAULT '',
            ADD COLUMN IF NOT EXISTS awb VARCHAR(150) DEFAULT '',
            ADD COLUMN IF NOT EXISTS amount VARCHAR(50) DEFAULT '',
            ADD COLUMN IF NOT EXISTS gateway VARCHAR(100) DEFAULT '',
            ADD COLUMN IF NOT EXISTS payment_method VARCHAR(100) DEFAULT '',
            ADD COLUMN IF NOT EXISTS utr VARCHAR(200) DEFAULT '',
            ADD COLUMN IF NOT EXISTS batch_id VARCHAR(20) DEFAULT '',
            ADD COLUMN IF NOT EXISTS created_at TIMESTAMP DEFAULT NOW()");
        $pdo->beginTransaction();
        try {
            // Replace same-day batch for this file type (prevents duplicates on re-save)
            $del = $pdo->prepare("DELETE FROM payment_files WHERE file_type = :ft AND batch_id = :bid");
            $del->execute([':ft'=>$fileType, ':bid'=>$batchId]);
            $ins = $pdo->prepare("INSERT INTO payment_files (file_type, order_id, awb, amount, gateway, payment_method, utr, batch_id)
                                  VALUES (:ft, :oid, :awb, :amt, :gw, :pm, :utr, :bid)");
            $count = 0;
            foreach ($records as $rec) {
                if (!is_array($rec)) continue;
                $oid = trim((string)($rec['orderId'] ?? ''));
                $awb = trim((string)($rec['awb'] ?? ''));
                if ($oid === '' && $awb === '') continue;
                $ins->execute([
                    ':ft'  => $fileType,
                    ':oid' => $oid,
                    ':awb' => $awb,
                    ':amt' => trim((string)($rec['amount'] ?? '')),
                    ':gw'  => trim((string)($rec['gateway'] ?? '')),
                    ':pm'  => trim((string)($rec['paymentMethod'] ?? '')),
                    ':utr' => trim((string)($rec['utr'] ?? '')),
                    ':bid' => $batchId
                ]);
                $count++;
            }
            $pdo->commit();
            echo json_encode(['status'=>'success','count'=>$count,'msg'=>$count.' records saved for '.$fileType]);
        } catch (Exception $e) {
            $pdo->rollBack();
            echo json_encode(['status'=>'error','msg'=>'DB error: '.$e->getMessage()]);
        }
        break;

    case 'get_payment_summary':
        $pdo = getPdo();
        try {
            $chk = $pdo->query("SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name='payment_files')")->fetch();
            $exists = ($chk['exists'] === 't' || $chk['exists'] === true);
            if (!$exists) { echo json_encode(['status'=>'success','data'=>[]]); break; }
            $pdo->exec("ALTER TABLE payment_files
                ADD COLUMN IF NOT EXISTS file_type VARCHAR(50) DEFAULT '',
                ADD COLUMN IF NOT EXISTS created_at TIMESTAMP DEFAULT NOW()");
            $rows = $pdo->query("SELECT file_type, COUNT(*) AS cnt, MAX(created_at) AS last_upload
                                 FROM payment_files GROUP BY file_type ORDER BY file_type")->fetchAll();
            echo json_encode(['status'=>'success','data'=>$rows]);
        } catch (Exception $e) {
            echo json_encode(['status'=>'error','msg'=>'DB error: '.$e->getMessage()]);
        }
        break;

    case 'get_payment_data':
        // Payment rows only (COD remittance + Gokwik prepaid) for lifecycle matching
        $pdo = getPdo();
        try {
            $chk = $pdo->query("SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name='payment_files')")->fetch();
            $exists = ($chk['exists'] === 't' || $chk['exists'] === true);
            if (!$exists) { echo json_encode(['status'=>'success','data'=>[]]); break; }
            $pdo->exec("ALTER TABLE payment_files
                ADD COLUMN IF NOT EXISTS file_type VARCHAR(50) DEFAULT '',
                ADD COLUMN IF NOT EXISTS order_id VARCHAR(150) DEFAULT '',
                ADD COLUMN IF NOT EXISTS awb VARCHAR(150) DEFAULT '',
                ADD COLUMN IF NOT EXISTS amount VARCHAR(50) DEFAULT '',
                ADD COLUMN IF NOT EXISTS gateway VARCHAR(100) DEFAULT '',
                ADD COLUMN IF NOT EXISTS payment_method VARCHAR(100) DEFAULT ''");
            $rows = $pdo->query("SELECT order_id, awb, amount, gateway, payment_method
                                 FROM payment_files
                                 WHERE file_type IN ('dtdc_cod','shiprocket_cod','bluedart_cod','delhivery_cod','gokwik_com','gokwik_in')")->fetchAll();
            echo json_encode(['status'=>'success','data'=>$rows]);
        } catch (Exception $e) {
            echo json_encode(['status'=>'error','msg'=>'DB error: '.$e->getMessage()]);
        }
        break;
    // ════════════════════════════════════════════════════════
    //  NDR AUTOMATION (additive — mirrors Apps Script v6.6)
    //  Tables: ndr_raw2 (Clickpost NDR), ndr_raw3 (status),
    //          ndr_records (working NDR + RTO).
    //  shopify_orders is read via existing apiGet, never modified.
    // ════════════════════════════════════════════════════════

    case 'save_ndr_raw2':
        if (!isAdminOrManager() && !isNdr()) { echo json_encode(['status'=>'error','msg'=>'Not authorized']); break; }
        $records = $input['records'] ?? [];
        if (!is_array($records) || !count($records)) { echo json_encode(['status'=>'error','msg'=>'No records']); break; }
        $pdo = getPdo();
        $pdo->exec("CREATE TABLE IF NOT EXISTS ndr_raw2 (
            id SERIAL PRIMARY KEY,
            courier_partner VARCHAR(120) DEFAULT '',
            ndr_timestamp   VARCHAR(60)  DEFAULT '',
            awb             VARCHAR(150) DEFAULT '',
            ndr_reason      VARCHAR(300) DEFAULT '',
            ndr_remark      VARCHAR(500) DEFAULT '',
            current_status  VARCHAR(120) DEFAULT '',
            order_id        VARCHAR(150) DEFAULT '',
            ofd_count       VARCHAR(20)  DEFAULT '',
            order_type      VARCHAR(40)  DEFAULT '',
            uploaded_at     TIMESTAMP DEFAULT NOW()
        )");
        // Full replace — Clickpost NDR export is a complete snapshot each time
        $pdo->exec("TRUNCATE TABLE ndr_raw2");
        $pdo->beginTransaction();
        try {
            $ins = $pdo->prepare("INSERT INTO ndr_raw2
                (courier_partner,ndr_timestamp,awb,ndr_reason,ndr_remark,current_status,order_id,ofd_count,order_type)
                VALUES (:cp,:ts,:awb,:rsn,:rmk,:st,:oid,:ofd,:ot)");
            $n = 0;
            foreach ($records as $r) {
                if (!is_array($r)) continue;
                $awb = trim($r['awb'] ?? '');
                if ($awb === '') continue;
                $ins->execute([
                    ':cp'=>substr(trim($r['courier_partner']??''),0,120),
                    ':ts'=>substr(trim($r['ndr_timestamp']??''),0,60),
                    ':awb'=>substr($awb,0,150),
                    ':rsn'=>substr(trim($r['ndr_reason']??''),0,300),
                    ':rmk'=>substr(trim($r['ndr_remark']??''),0,500),
                    ':st'=>substr(trim($r['current_status']??''),0,120),
                    ':oid'=>substr(trim($r['order_id']??''),0,150),
                    ':ofd'=>substr(trim((string)($r['ofd_count']??'')),0,20),
                    ':ot'=>substr(trim($r['order_type']??''),0,40),
                ]);
                $n++;
            }
            $pdo->commit();
            echo json_encode(['status'=>'success','count'=>$n]);
        } catch (Exception $e) {
            $pdo->rollBack();
            echo json_encode(['status'=>'error','msg'=>$e->getMessage()]);
        }
        break;

    case 'save_ndr_raw3':
        if (!isAdminOrManager() && !isNdr()) { echo json_encode(['status'=>'error','msg'=>'Not authorized']); break; }
        $records = $input['records'] ?? [];
        if (!is_array($records) || !count($records)) { echo json_encode(['status'=>'error','msg'=>'No records']); break; }
        $pdo = getPdo();
        $pdo->exec("CREATE TABLE IF NOT EXISTS ndr_raw3 (
            id SERIAL PRIMARY KEY,
            awb VARCHAR(150) DEFAULT '',
            latest_status VARCHAR(120) DEFAULT '',
            uploaded_at TIMESTAMP DEFAULT NOW()
        )");
        $pdo->exec("TRUNCATE TABLE ndr_raw3");
        $pdo->beginTransaction();
        try {
            $ins = $pdo->prepare("INSERT INTO ndr_raw3 (awb,latest_status) VALUES (:awb,:st)");
            $n = 0;
            foreach ($records as $r) {
                if (!is_array($r)) continue;
                $awb = trim($r['awb'] ?? '');
                if ($awb === '') continue;
                $ins->execute([':awb'=>substr($awb,0,150),':st'=>substr(trim($r['latest_status']??''),0,120)]);
                $n++;
            }
            $pdo->commit();
            echo json_encode(['status'=>'success','count'=>$n]);
        } catch (Exception $e) {
            $pdo->rollBack();
            echo json_encode(['status'=>'error','msg'=>$e->getMessage()]);
        }
        break;

    case 'ndr_run_refresh':
        if (!isAdminOrManager() && !isNdr()) { echo json_encode(['status'=>'error','msg'=>'Not authorized']); break; }
        $pdo = getPdo();
        // Ensure persistent records table
        $pdo->exec("CREATE TABLE IF NOT EXISTS ndr_records (
            id SERIAL PRIMARY KEY,
            awb             VARCHAR(150) UNIQUE,
            order_id        VARCHAR(150) DEFAULT '',
            ofd_count       VARCHAR(20)  DEFAULT '',
            billing_state   VARCHAR(120) DEFAULT '',
            shipping_addr   TEXT DEFAULT '',
            ndr_reason      VARCHAR(300) DEFAULT '',
            ndr_remark      VARCHAR(500) DEFAULT '',
            billing_zip     VARCHAR(20)  DEFAULT '',
            sku             VARCHAR(300) DEFAULT '',
            customer_name   VARCHAR(200) DEFAULT '',
            tags            VARCHAR(400) DEFAULT '',
            total           VARCHAR(40)  DEFAULT '',
            courier_partner VARCHAR(120) DEFAULT '',
            order_type      VARCHAR(40)  DEFAULT '',
            first_status    VARCHAR(120) DEFAULT '',
            first_date      VARCHAR(20)  DEFAULT '',
            final_status    VARCHAR(120) DEFAULT '',
            final_date      VARCHAR(20)  DEFAULT '',
            last_updated    VARCHAR(40)  DEFAULT '',
            ageing          INTEGER DEFAULT 0,
            billing_phone   VARCHAR(40)  DEFAULT '',
            alternate_phone VARCHAR(40)  DEFAULT '',
            pasted_date     VARCHAR(20)  DEFAULT '',
            new_old         VARCHAR(10)  DEFAULT '',
            action          VARCHAR(40)  DEFAULT '',
            remark1         VARCHAR(500) DEFAULT '',
            remark2         VARCHAR(500) DEFAULT '',
            is_rto          BOOLEAN DEFAULT FALSE,
            created_at      TIMESTAMP DEFAULT NOW()
        )");

        // ---- Load raw inputs ----
        $raw2 = $pdo->query("SELECT * FROM ndr_raw2")->fetchAll(PDO::FETCH_ASSOC);

        // Delivered map (awb -> Delivered). Primary source: live clickpost_webhook
        // 'status' column. This replaces the manual Raw3 upload — the webhook already
        // holds the latest status for every AWB, including ones that dropped out of NDR.
        $deliveredMap = [];
        try {
            $cw = $pdo->query("SELECT waybill, status FROM clickpost_webhook")->fetchAll(PDO::FETCH_ASSOC);
            foreach ($cw as $c) {
                $awb = trim($c['waybill'] ?? '');
                $st  = strtolower(trim($c['status'] ?? ''));
                // Treat 'delivered' but not 'rto-delivered' as a customer delivery
                if ($awb !== '' && strpos($st, 'delivered') !== false && strpos($st, 'rto') === false) {
                    $deliveredMap[$awb] = true;
                }
            }
        } catch (Exception $e) { /* webhook optional */ }

        // Optional fallback: if a Raw3 file was ever uploaded, merge its delivered rows too
        try {
            $hasRaw3 = $pdo->query("SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name='ndr_raw3')")->fetch();
            if ($hasRaw3 && ($hasRaw3['exists'] === 't' || $hasRaw3['exists'] === true)) {
                $raw3 = $pdo->query("SELECT awb, latest_status FROM ndr_raw3")->fetchAll(PDO::FETCH_ASSOC);
                foreach ($raw3 as $r3) {
                    $awb = trim($r3['awb'] ?? '');
                    if ($awb !== '' && strtolower(trim($r3['latest_status'] ?? '')) === 'delivered') $deliveredMap[$awb] = true;
                }
            }
        } catch (Exception $e) { /* ignore */ }

        // Shopify map (order_id with leading # -> details), pulled via existing API
        $shopifyMap = [];
        try {
            $shopRows = apiGet('shopify_orders');
            if (is_array($shopRows)) {
                foreach ($shopRows as $so) {
                    $oid = trim($so['order_name'] ?? $so['name'] ?? '');
                    if ($oid === '') continue;
                    if ($oid[0] !== '#') $oid = '#'.$oid;
                    // line_items JSON -> sku
                    $sku = '';
                    if (!empty($so['line_items'])) {
                        $li = is_string($so['line_items']) ? json_decode($so['line_items'], true) : $so['line_items'];
                        if (is_array($li)) {
                            $skus = [];
                            foreach ($li as $it) { if (!empty($it['sku'])) $skus[] = $it['sku']; }
                            $sku = implode(', ', $skus);
                        }
                    }
                    $shopifyMap[$oid] = [
                        'email'        => $so['email'] ?? '',
                        'total'        => $so['total'] ?? $so['total_price'] ?? '',
                        'sku'          => $sku,
                        'customer_name'=> $so['billing_name'] ?? $so['customer_name'] ?? '',
                        'billing_zip'  => $so['billing_zip'] ?? '',
                        'billing_phone'=> $so['billing_phone'] ?? $so['phone'] ?? '',
                        'shipping_addr'=> trim(($so['billing_address1'] ?? '').' '.($so['billing_address2'] ?? '')),
                        'tags'         => $so['tags'] ?? '',
                        'billing_state'=> $so['billing_province'] ?? $so['billing_province_name'] ?? '',
                    ];
                }
            }
        } catch (Exception $e) { /* shopify optional; continue with blanks */ }

        // Existing ndr_records by awb
        $existing = [];
        foreach ($pdo->query("SELECT * FROM ndr_records")->fetchAll(PDO::FETCH_ASSOC) as $er) {
            $existing[trim($er['awb'])] = $er;
        }

        $today = (new DateTime('now', new DateTimeZone('Asia/Kolkata')))->format('d/m/Y');
        $nowStr = (new DateTime('now', new DateTimeZone('Asia/Kolkata')))->format('d/m/Y H:i');

        // OTP/Rejection phrases -> RTO
        $otpPhrases = ['otp mismatch','otp unavailable','otp not shared','otp not available',
                       'otp-based cancellation','otp based cancellation','otp mismatch or unavailable','rejected by customer'];

        $ageingDays = function($firstDate) use ($today) {
            if (!$firstDate) return 0;
            $p = explode('/', explode(' ', $firstDate)[0]);
            if (count($p) !== 3) return 0;
            try {
                $d1 = new DateTime($p[2].'-'.$p[1].'-'.$p[0]);
                $t  = explode('/', $today);
                $d2 = new DateTime($t[2].'-'.$t[1].'-'.$t[0]);
                $diff = (int)$d1->diff($d2)->format('%r%a');
                return $diff >= 0 ? $diff : 0;
            } catch (Exception $e) { return 0; }
        };

        // Dedupe raw2 by awb (keep last)
        $cpDedup = [];
        foreach ($raw2 as $r) { $awb = trim($r['awb'] ?? ''); if ($awb !== '') $cpDedup[$awb] = $r; }

        $rows = [];          // final assembled rows keyed by awb
        $updatedAwbs = [];

        foreach ($cpDedup as $awb => $cp) {
            $orderId = trim($cp['order_id'] ?? '');
            if ($orderId !== '' && $orderId[0] !== '#') $orderId = '#'.$orderId;
            $cpStatus = trim($cp['current_status'] ?? '');
            $finalStatus = isset($deliveredMap[$awb]) ? 'Delivered' : $cpStatus;
            $sh = $shopifyMap[$orderId] ?? [];

            if (isset($existing[$awb])) {
                $ex = $existing[$awb];
                $prevFinal = trim($ex['final_status'] ?? '');
                $pasted = trim($ex['pasted_date'] ?? '');
                $row = [
                    'awb'=>$awb,
                    'order_id'=>$orderId ?: $ex['order_id'],
                    'ofd_count'=>$cp['ofd_count'] ?? $ex['ofd_count'],
                    'billing_state'=>$ex['billing_state'],
                    'shipping_addr'=>$ex['shipping_addr'],
                    'ndr_reason'=>trim($cp['ndr_reason'] ?? ''),
                    'ndr_remark'=>trim($cp['ndr_remark'] ?? ''),
                    'billing_zip'=>$ex['billing_zip'],
                    'sku'=>$ex['sku'],
                    'customer_name'=>$ex['customer_name'],
                    'tags'=>$ex['tags'],
                    'total'=>$ex['total'],
                    'courier_partner'=>trim($cp['courier_partner'] ?? '') ?: $ex['courier_partner'],
                    'order_type'=>trim($cp['order_type'] ?? '') ?: $ex['order_type'],
                    'first_status'=>$ex['first_status'],
                    'first_date'=>$ex['first_date'],
                    'final_status'=>$finalStatus,
                    'final_date'=>($finalStatus !== $prevFinal) ? $today : $ex['final_date'],
                    'last_updated'=>$nowStr,
                    'ageing'=>$ageingDays($ex['first_date']),
                    'billing_phone'=>(trim((string)($ex['billing_phone'] ?? '')) !== '') ? $ex['billing_phone'] : ($sh['billing_phone'] ?? ''),
                    'alternate_phone'=>$ex['alternate_phone'],
                    'pasted_date'=>$pasted,
                    'new_old'=>($pasted === $today) ? 'New' : 'Old',
                    'action'=>$ex['action'],
                    'remark1'=>$ex['remark1'],
                    'remark2'=>$ex['remark2'],
                ];
            } else {
                $row = [
                    'awb'=>$awb,
                    'order_id'=>$orderId,
                    'ofd_count'=>$cp['ofd_count'] ?? '',
                    'billing_state'=>$sh['billing_state'] ?? '',
                    'shipping_addr'=>$sh['shipping_addr'] ?? '',
                    'ndr_reason'=>trim($cp['ndr_reason'] ?? ''),
                    'ndr_remark'=>trim($cp['ndr_remark'] ?? ''),
                    'billing_zip'=>$sh['billing_zip'] ?? '',
                    'sku'=>$sh['sku'] ?? '',
                    'customer_name'=>$sh['customer_name'] ?? '',
                    'tags'=>$sh['tags'] ?? '',
                    'total'=>$sh['total'] ?? '',
                    'courier_partner'=>trim($cp['courier_partner'] ?? ''),
                    'order_type'=>trim($cp['order_type'] ?? ''),
                    'first_status'=>$finalStatus,
                    'first_date'=>$today,
                    'final_status'=>$finalStatus,
                    'final_date'=>$today,
                    'last_updated'=>$nowStr,
                    'ageing'=>0,
                    'billing_phone'=>$sh['billing_phone'] ?? '',
                    'alternate_phone'=>'',
                    'pasted_date'=>$today,
                    'new_old'=>'New',
                    'action'=>'',
                    'remark1'=>'',
                    'remark2'=>'',
                ];
            }
            $updatedAwbs[$awb] = true;
            $rows[$awb] = $row;
        }

        // Apply Raw3 delivered to existing rows not present in raw2
        foreach ($existing as $awb => $ex) {
            if (isset($updatedAwbs[$awb])) continue;
            $row = $ex; // carry forward as-is
            unset($row['id']); unset($row['created_at']); unset($row['is_rto']);
            if (isset($deliveredMap[$awb]) && trim($ex['final_status']) !== 'Delivered') {
                $row['final_status'] = 'Delivered';
                $row['final_date']   = $today;
                $row['last_updated'] = $nowStr;
                $row['ageing']       = $ageingDays($ex['first_date']);
                $pasted = trim($ex['pasted_date']);
                $row['new_old'] = ($pasted === $today) ? 'New' : 'Old';
            }
            $rows[$awb] = $row;
        }

        // RTO classification
        $isRto = function($r) use ($otpPhrases) {
            $fs = strtolower(trim($r['final_status'] ?? ''));
            $nr = strtolower(trim($r['ndr_reason'] ?? ''));
            if (strpos($fs, 'rto') === 0) return true;
            foreach ($otpPhrases as $p) { if (strpos($nr, $p) !== false) return true; }
            return false;
        };

        // Persist (upsert by awb)
        $pdo->beginTransaction();
        try {
            $up = $pdo->prepare("INSERT INTO ndr_records
                (awb,order_id,ofd_count,billing_state,shipping_addr,ndr_reason,ndr_remark,billing_zip,sku,customer_name,tags,total,courier_partner,order_type,first_status,first_date,final_status,final_date,last_updated,ageing,billing_phone,alternate_phone,pasted_date,new_old,action,remark1,remark2,is_rto)
                VALUES (:awb,:oid,:ofd,:bs,:sa,:rsn,:rmk,:bz,:sku,:cn,:tg,:tot,:cp,:ot,:fs,:fd,:fns,:fnd,:lu,:ag,:bp,:ap,:pd,:no,:act,:r1,:r2,:rto)
                ON CONFLICT (awb) DO UPDATE SET
                    order_id=EXCLUDED.order_id, ofd_count=EXCLUDED.ofd_count,
                    ndr_reason=EXCLUDED.ndr_reason, ndr_remark=EXCLUDED.ndr_remark,
                    courier_partner=EXCLUDED.courier_partner, order_type=EXCLUDED.order_type,
                    final_status=EXCLUDED.final_status, final_date=EXCLUDED.final_date,
                    last_updated=EXCLUDED.last_updated, ageing=EXCLUDED.ageing,
                    new_old=EXCLUDED.new_old, is_rto=EXCLUDED.is_rto,
                    billing_state=CASE WHEN ndr_records.billing_state='' THEN EXCLUDED.billing_state ELSE ndr_records.billing_state END,
                    shipping_addr=CASE WHEN ndr_records.shipping_addr='' THEN EXCLUDED.shipping_addr ELSE ndr_records.shipping_addr END,
                    sku=CASE WHEN ndr_records.sku='' THEN EXCLUDED.sku ELSE ndr_records.sku END,
                    customer_name=CASE WHEN ndr_records.customer_name='' THEN EXCLUDED.customer_name ELSE ndr_records.customer_name END,
                    total=CASE WHEN ndr_records.total='' THEN EXCLUDED.total ELSE ndr_records.total END,
                    billing_phone=CASE WHEN ndr_records.billing_phone='' THEN EXCLUDED.billing_phone ELSE ndr_records.billing_phone END,
                    tags=CASE WHEN ndr_records.tags='' THEN EXCLUDED.tags ELSE ndr_records.tags END,
                    billing_zip=CASE WHEN ndr_records.billing_zip='' THEN EXCLUDED.billing_zip ELSE ndr_records.billing_zip END");
            $nNew=0;$nUpd=0;$nRto=0;
            foreach ($rows as $awb=>$r) {
                $rto = $isRto($r);
                if ($rto) $nRto++;
                if (isset($existing[$awb])) $nUpd++; else $nNew++;
                $up->execute([
                    ':awb'=>$awb, ':oid'=>$r['order_id'], ':ofd'=>(string)$r['ofd_count'],
                    ':bs'=>$r['billing_state'], ':sa'=>$r['shipping_addr'], ':rsn'=>$r['ndr_reason'],
                    ':rmk'=>$r['ndr_remark'], ':bz'=>$r['billing_zip'], ':sku'=>$r['sku'],
                    ':cn'=>$r['customer_name'], ':tg'=>$r['tags'], ':tot'=>(string)$r['total'],
                    ':cp'=>$r['courier_partner'], ':ot'=>$r['order_type'], ':fs'=>$r['first_status'],
                    ':fd'=>$r['first_date'], ':fns'=>$r['final_status'], ':fnd'=>$r['final_date'],
                    ':lu'=>$r['last_updated'], ':ag'=>(int)$r['ageing'], ':bp'=>$r['billing_phone'],
                    ':ap'=>$r['alternate_phone'], ':pd'=>$r['pasted_date'], ':no'=>$r['new_old'],
                    ':act'=>$r['action'], ':r1'=>$r['remark1'], ':r2'=>$r['remark2'], ':rto'=>$rto ? 't' : 'f',
                ]);
            }
            $pdo->commit();
            echo json_encode(['status'=>'success','new'=>$nNew,'updated'=>$nUpd,'rto'=>$nRto,'total'=>count($rows)]);
        } catch (Exception $e) {
            $pdo->rollBack();
            echo json_encode(['status'=>'error','msg'=>$e->getMessage()]);
        }
        break;

    case 'ndr_get_data':
        if (!isAdminOrManager() && !isNdr()) { echo json_encode(['status'=>'error','msg'=>'Not authorized']); break; }
        $pdo = getPdo();
        $chk = $pdo->query("SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name='ndr_records')")->fetch();
        if (!($chk['exists'] === 't' || $chk['exists'] === true)) { echo json_encode(['status'=>'success','ndr'=>[],'rto'=>[]]); break; }
        $ndr = $pdo->query("SELECT * FROM ndr_records WHERE is_rto = FALSE ORDER BY ageing DESC, awb")->fetchAll(PDO::FETCH_ASSOC);
        $rto = $pdo->query("SELECT * FROM ndr_records WHERE is_rto = TRUE  ORDER BY final_date DESC, awb")->fetchAll(PDO::FETCH_ASSOC);
        echo json_encode(['status'=>'success','ndr'=>$ndr,'rto'=>$rto]);
        break;

    case 'ndr_update_row':
        // Save manual edits: action, alternate_phone, remark1, remark2
        if (!isAdminOrManager() && !isNdr()) { echo json_encode(['status'=>'error','msg'=>'Not authorized']); break; }
        $awb = trim($input['awb'] ?? '');
        if ($awb === '') { echo json_encode(['status'=>'error','msg'=>'AWB required']); break; }
        try {
            $pdo = getPdo();
            // Ensure table + columns exist (no-op if already there)
            $pdo->exec("CREATE TABLE IF NOT EXISTS ndr_records (id SERIAL PRIMARY KEY, awb VARCHAR(150) UNIQUE)");
            $pdo->exec("ALTER TABLE ndr_records
                ADD COLUMN IF NOT EXISTS action VARCHAR(40) DEFAULT '',
                ADD COLUMN IF NOT EXISTS alternate_phone VARCHAR(40) DEFAULT '',
                ADD COLUMN IF NOT EXISTS remark1 VARCHAR(500) DEFAULT '',
                ADD COLUMN IF NOT EXISTS remark2 VARCHAR(500) DEFAULT ''");
            $up = $pdo->prepare("UPDATE ndr_records SET action=:act, alternate_phone=:ap, remark1=:r1, remark2=:r2 WHERE awb=:awb");
            $up->execute([
                ':act'=>substr(trim($input['ndr_action'] ?? ''),0,40),
                ':ap'=>substr(trim($input['alternate_phone'] ?? ''),0,40),
                ':r1'=>substr(trim($input['remark1'] ?? ''),0,500),
                ':r2'=>substr(trim($input['remark2'] ?? ''),0,500),
                ':awb'=>$awb,
            ]);
            if ($up->rowCount() > 0) {
                echo json_encode(['status'=>'success']);
            } else {
                echo json_encode(['status'=>'error','msg'=>'No NDR row found for AWB '.$awb.' — run Full Refresh first']);
            }
        } catch (Exception $e) {
            echo json_encode(['status'=>'error','msg'=>'DB error: '.$e->getMessage()]);
        }
        break;

    // ─────────────────────────────────────────────────────────

    // ════════════════════════════════════════════════════════
    //  COGS MASTER (additive) — editable Article / SP / COGS
    // ════════════════════════════════════════════════════════
    case 'cogs_get':
        if (!isAdminOrManager()) { echo json_encode(['status'=>'error','msg'=>'Not authorized']); break; }
        $pdo = getPdo();
        $pdo->exec("CREATE TABLE IF NOT EXISTS cogs_master (
            id SERIAL PRIMARY KEY,
            article VARCHAR(120) UNIQUE NOT NULL,
            sp NUMERIC(12,2) DEFAULT 0,
            cogs NUMERIC(12,2) DEFAULT 0,
            updated_at TIMESTAMP DEFAULT NOW()
        )");
        // Seed once if empty
        $cnt = $pdo->query("SELECT COUNT(*) AS c FROM cogs_master")->fetch();
        if ((int)$cnt['c'] === 0) {
            $seed = [['C-507',399,200],['C-512',499,300],['C-551',499,250],['C-555',499,250],['C-558',499,250],['C-561',499,225],['Combo F-112',1499,690],['Combo F-115',1499,690],['Combo F-511',1499,690],['Combo F-543',1499,590],['Combo FP-103',1399,670],['Cufflinks',249,100],['D-C-T-123',1199,450],['F-102',799,325],['F-103',799,335],['F-104',1199,425],['F-105',1199,350],['F-107',499,170],['F-109',799,225],['F-112',799,325],['F-113',1199,325],['F-114',799,250],['F-115',899,335],['F-117',1499,650],['F-118',499,200],['F-119',499,250],['F-120',999,300],['F-122',699,200],['F-131',499,200],['F-136',499,250],['F-141',499,200],['F-142',499,300],['F-143',1499,650],['F-144',999,335],['F-158',1699,450],['F-283',899,350],['F-511',799,350],['F-523',699,200],['F-529',499,175],['F-534',499,200],['F-539',499,250],['F-543',799,280],['FK-202',999,500],['FK-206',999,500],['FK-209',999,500],['FK-210',1999,600],['FK-211',1999,570],['FK-212',1099,350],['FK-215',1099,350],['FK-216',1099,350],['FP-103',799,335],['FP-122',499,200],['FP-124',999,375],['FP-125',999,375],['FP-126',899,315],['FP-128',1499,650],['FP-129',999,400],['FP-130',799,275],['FP-132',899,200],['FP-133',499,200],['FP-134',1499,450],['FP-135',1499,450],['FP-137',1299,425],['FP-138',1499,450],['FP-139',1199,450],['FP-147',1499,450],['FP-148',1499,450],['FP-155',999,375],['FP-156',1199,435],['FP-252',899,325],['FP-267',899,350],['FP-268',899,350],['FP-270',899,350],['FP-271',899,350],['FP-272',899,350],['FP-273',899,350],['FP-274',899,350],['FP-275',899,350],['FP-278',899,350],['FP-279',899,350],['Fp-283',899,350],['FP-511',799,335],['FW-149',1500,900],['FW-150',1500,900],['FW-151',1500,900],['FW-152',1500,900],['FW-154',999,500],['HP-531',699,275],['HPF-140',699,275],['MY-251',899,350],];
            $ins = $pdo->prepare("INSERT INTO cogs_master (article,sp,cogs) VALUES (:a,:s,:c) ON CONFLICT (article) DO NOTHING");
            foreach ($seed as $row) {
                $ins->execute([':a'=>$row[0], ':s'=>$row[1], ':c'=>$row[2]]);
            }
        }
        $rows = $pdo->query("SELECT id,article,sp,cogs FROM cogs_master ORDER BY article")->fetchAll(PDO::FETCH_ASSOC);
        echo json_encode(['status'=>'success','data'=>$rows]);
        break;

    case 'cogs_save':
        if (!isAdminOrManager()) { echo json_encode(['status'=>'error','msg'=>'Not authorized']); break; }
        $article = trim($input['article'] ?? '');
        if ($article === '') { echo json_encode(['status'=>'error','msg'=>'Article required']); break; }
        $sp   = is_numeric($input['sp'] ?? null) ? (float)$input['sp'] : 0;
        $cogs = is_numeric($input['cogs'] ?? null) ? (float)$input['cogs'] : 0;
        try {
            $pdo = getPdo();
            $pdo->exec("CREATE TABLE IF NOT EXISTS cogs_master (id SERIAL PRIMARY KEY, article VARCHAR(120) UNIQUE NOT NULL, sp NUMERIC(12,2) DEFAULT 0, cogs NUMERIC(12,2) DEFAULT 0, updated_at TIMESTAMP DEFAULT NOW())");
            $up = $pdo->prepare("INSERT INTO cogs_master (article,sp,cogs,updated_at) VALUES (:a,:s,:c,NOW())
                ON CONFLICT (article) DO UPDATE SET sp=EXCLUDED.sp, cogs=EXCLUDED.cogs, updated_at=NOW()");
            $up->execute([':a'=>$article, ':s'=>$sp, ':c'=>$cogs]);
            echo json_encode(['status'=>'success']);
        } catch (Exception $e) { echo json_encode(['status'=>'error','msg'=>$e->getMessage()]); }
        break;

    case 'cogs_delete':
        if (!isAdminOrManager()) { echo json_encode(['status'=>'error','msg'=>'Not authorized']); break; }
        $id = (int)($input['id'] ?? 0);
        $article = trim($input['article'] ?? '');
        try {
            $pdo = getPdo();
            if ($id > 0) { $d = $pdo->prepare("DELETE FROM cogs_master WHERE id=:id"); $d->execute([':id'=>$id]); }
            elseif ($article !== '') { $d = $pdo->prepare("DELETE FROM cogs_master WHERE article=:a"); $d->execute([':a'=>$article]); }
            echo json_encode(['status'=>'success']);
        } catch (Exception $e) { echo json_encode(['status'=>'error','msg'=>$e->getMessage()]); }
        break;


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