<?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;
}

// Insert rows into the scanning table robustly. Each $rows item: [order_id, dispatch_status, courier_name, awb, timestamp].
// Handles an 'id' primary key that may not auto-increment by assigning MAX(id)+1 manually.
// Returns ['imported'=>int, 'failed'=>int, 'errors'=>array].
function insertScanRows($pdo, array $rows) {
    $imported = 0; $failed = 0; $errors = [];
    if (!count($rows)) return ['imported'=>0,'failed'=>0,'errors'=>[]];

    // Discover columns present on the scanning table.
    $cols = [];
    try {
        $ci = $pdo->query("SELECT column_name FROM information_schema.columns WHERE table_name='scanning'")->fetchAll(PDO::FETCH_COLUMN);
        foreach ($ci as $c) $cols[strtolower($c)] = true;
    } catch (\Exception $e) {}

    $colOrderId = isset($cols['order_id']) ? 'order_id' : null;
    $colStatus  = isset($cols['dispatch_status']) ? 'dispatch_status' : (isset($cols['status']) ? 'status' : null);
    $colCourier = isset($cols['courier_name']) ? 'courier_name' : (isset($cols['courier']) ? 'courier' : null);
    $colAwb     = isset($cols['awb']) ? 'awb' : (isset($cols['waybill']) ? 'waybill' : null);
    $colTs      = isset($cols['timestamp']) ? 'timestamp' : (isset($cols['scan_date']) ? 'scan_date' : (isset($cols['created_at']) ? 'created_at' : null));

    // Determine id handling.
    $hasId = isset($cols['id']); $idManual = false; $nextId = 1;
    if ($hasId) {
        try {
            $def = $pdo->query("SELECT column_default FROM information_schema.columns WHERE table_name='scanning' AND column_name='id'")->fetchColumn();
            if (!$def) { $idManual = true; $nextId = intval($pdo->query("SELECT COALESCE(MAX(id),0) FROM scanning")->fetchColumn()) + 1; }
        } catch (\Exception $e) {
            $idManual = true;
            try { $nextId = intval($pdo->query("SELECT COALESCE(MAX(id),0) FROM scanning")->fetchColumn()) + 1; } catch (\Exception $e2) {}
        }
    }

    // Build column list + field order.
    $insCols = []; $order = [];
    if ($idManual)   { $insCols[]='id';          $order[]='id'; }
    if ($colOrderId) { $insCols[]=$colOrderId;   $order[]='oid'; }
    if ($colStatus)  { $insCols[]=$colStatus;    $order[]='ds'; }
    if ($colCourier) { $insCols[]=$colCourier;   $order[]='courier'; }
    if ($colAwb)     { $insCols[]=$colAwb;       $order[]='awb'; }
    if ($colTs)      { $insCols[]=$colTs;        $order[]='ts'; }
    if (empty($insCols)) return ['imported'=>0,'failed'=>count($rows),'errors'=>['no matching scanning columns']];

    // Re-map incoming positional rows ([oid,ds,courier,awb,ts]) into the discovered field order.
    $startId = $nextId; // remember the first id so we can re-number compactly after de-duplication
    $built = [];
    foreach ($rows as $r) {
        $map = ['oid'=>$r[0] ?? '', 'ds'=>$r[1] ?? 'Dispatched', 'courier'=>$r[2] ?? '', 'awb'=>$r[3] ?? '', 'ts'=>$r[4] ?? date('Y-m-d H:i:s')];
        if ($idManual) $map['id'] = $nextId++;
        $rowVals = []; foreach ($order as $f) $rowVals[] = $map[$f];
        $built[] = $rowVals;
    }

    // De-duplicate within this batch by order_id (keep the LAST occurrence). UPSERT cannot touch
    // the same conflict target twice in one statement, and the scanning table is unique on order_id.
    if ($colOrderId) {
        $oidPos = array_search('oid', $order);
        if ($oidPos !== false) {
            $byOid = [];
            foreach ($built as $row) { $byOid[(string)$row[$oidPos]] = $row; }
            $built = array_values($byOid);
            // ids were assigned before dedupe; if manual, re-number compactly so they stay unique & gapless
            if ($idManual) {
                $idPos = array_search('id', $order);
                if ($idPos !== false) { $nid = $startId; foreach ($built as &$rw) { $rw[$idPos] = $nid++; } unset($rw); }
            }
        }
    }

    $colList = '(' . implode(',', $insCols) . ')';
    $oneTpl  = '(' . implode(',', array_fill(0, count($insCols), '?')) . ')';

    // If order_id is unique, UPSERT so already-scanned orders get updated instead of erroring.
    $hasUnique = false;
    if ($colOrderId) {
        try {
            $uq = $pdo->query("SELECT 1 FROM information_schema.table_constraints tc
                               JOIN information_schema.key_column_usage kcu
                                 ON tc.constraint_name = kcu.constraint_name
                               WHERE tc.table_name='scanning'
                                 AND tc.constraint_type IN ('UNIQUE','PRIMARY KEY')
                                 AND kcu.column_name='order_id' LIMIT 1")->fetchColumn();
            $hasUnique = (bool)$uq;
        } catch (\Exception $e) { $hasUnique = true; } // assume unique (safer given the known constraint)
    }
    $conflict = '';
    if ($hasUnique) {
        $sets = [];
        if ($colStatus)  $sets[] = "$colStatus = EXCLUDED.$colStatus";
        if ($colCourier) $sets[] = "$colCourier = EXCLUDED.$colCourier";
        if ($colAwb)     $sets[] = "$colAwb = EXCLUDED.$colAwb";
        if ($colTs)      $sets[] = "$colTs = EXCLUDED.$colTs";
        $conflict = empty($sets)
            ? " ON CONFLICT (order_id) DO NOTHING"
            : " ON CONFLICT (order_id) DO UPDATE SET " . implode(', ', $sets);
    }

    $pdo->beginTransaction();
    try {
        $CK = 500;
        for ($i=0; $i<count($built); $i+=$CK) {
            $slice = array_slice($built, $i, $CK);
            $ph = []; $vals = [];
            foreach ($slice as $row) { $ph[]=$oneTpl; foreach ($row as $v) $vals[]=$v; }
            $stmt = $pdo->prepare("INSERT INTO scanning $colList VALUES ".implode(',', $ph) . $conflict);
            $stmt->execute($vals);
            $imported += count($slice);
        }
        $pdo->commit();
    } catch (\Exception $e) {
        if ($pdo->inTransaction()) $pdo->rollBack();
        // Row-by-row fallback so one bad row doesn't fail everything; capture the real error.
        $imported = 0; $stmt = $pdo->prepare("INSERT INTO scanning $colList VALUES $oneTpl" . $conflict);
        foreach ($built as $row) {
            try { $stmt->execute($row); $imported++; }
            catch (\Exception $e2) { $failed++; if (count($errors) < 3) $errors[] = $e2->getMessage(); }
        }
    }
    return ['imported'=>$imported,'failed'=>$failed,'errors'=>$errors];
}

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&limit=15000&nocache=' . time(),
            'scanning'       => EXISTING_API . '?table=scanning&limit=10000&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 = []; $skuList = [];
            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'];
                    // Extract a rough article code; frontend re-runs a COGS-aware extractor on the raw sku.
                    $skuU = strtoupper(trim($li['sku']));
                    if (preg_match('/^([A-Z]{1,3}(?:-[A-Z]{1,3})*-\d+)/', $skuU, $m)) {
                        $skuList[] = ['a'=>$m[1], 'q'=>$q, 'raw'=>$li['sku']];
                    } else {
                        $parts = explode('-', $skuU);
                        $art = count($parts) >= 2 ? $parts[0].'-'.$parts[1] : $skuU;
                        $skuList[] = ['a'=>$art, 'q'=>$q, 'raw'=>$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),'skuList'=>$skuList,'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 = [];
        // Load only the last 60 days of scans into the initial page response so it stays small and
        // never truncates. Older scan history is fetched on demand via the 'scan_history' endpoint
        // when the user picks an earlier date range in Dispatch & Scan.
        // Build scan history from the already-fetched $scanning array (no extra DB query on page load).
        // Older/full scan history is loaded on demand via the 'scan_history' endpoint by Dispatch & Scan.
        foreach ($scanning as $row) {
            $id = 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'] ?? '',
                    'awb'            => $row['awb'] ?? '',
                    '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 AND preserve the ORIGINAL assign_timestamp.
                    // Previously this always overwrote assign_timestamp to "now" on every status
                    // change (Done/Cancel/Force Done/Bulk Update), which incorrectly made old
                    // orders look like they were freshly assigned today just because their status
                    // was touched today. COALESCE only fills it in if it was ever NULL/empty —
                    // once set, the true original assignment date is never overwritten again.
                    $upd = $pdo2->prepare(
                        "UPDATE order_status SET status=\$1, remark=\$2, assign_timestamp=COALESCE(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 'bulk_import_scans':
        // Import missed/backdated scan records into the scanning table (with the given scan_date).
        $records = $input['records'] ?? [];
        if (!is_array($records) || !count($records)) { echo json_encode(['status'=>'error','msg'=>'No records']); break; }
        $skipped = 0;
        try {
            $pdo = getPdo();
            $rows = [];
            foreach ($records as $r) {
                if (!is_array($r)) { $skipped++; continue; }
                $oid = trim(str_replace('#','', $r['orderId'] ?? ''));
                if ($oid === '') { $skipped++; continue; }
                // Normalise case: CSVs often carry RETURNED/returned/dispatched. Whitelist check is
                // case-sensitive, so without this normalisation any non-'Dispatched' status silently
                // fell through to 'Dispatched'.
                $ds = ucfirst(strtolower(trim($r['status'] ?? 'Dispatched')));
                if (!in_array($ds, ['Dispatched','Returned','Cancelled'])) $ds = 'Dispatched';
                $courier = trim($r['courier'] ?? '');
                $awb = trim($r['awb'] ?? '');
                // Normalise scan date -> timestamp. Accept YYYY-MM-DD or DD-MM-YYYY.
                $d = trim($r['scanDate'] ?? ''); $ts = null;
                if ($d !== '') {
                    if (preg_match('/^(\d{4})-(\d{2})-(\d{2})/', $d, $m))            $ts = "$m[1]-$m[2]-$m[3] 12:00:00";
                    elseif (preg_match('#^(\d{2})[-/](\d{2})[-/](\d{4})#', $d, $m))  $ts = "$m[3]-$m[2]-$m[1] 12:00:00";
                }
                if ($ts === null) $ts = date('Y-m-d H:i:s');
                $rows[] = [$oid, $ds, $courier, $awb, $ts];
            }
            $res = insertScanRows($pdo, $rows);
            echo json_encode(['status'=>'success','imported'=>$res['imported'],'failed'=>$res['failed']+$skipped,'errors'=>$res['errors']]);
        } catch (\Exception $e) {
            echo json_encode(['status'=>'error','msg'=>'DB error: '.$e->getMessage()]);
        }
        break;

    case 'barcode_scan_batch':
        // Accept many scans at once. Write DIRECTLY to the scanning table (PDO) so every scan is
        // recorded — the external bulk_dispatch could drop/dedupe rows, losing scans on submit.
        $scans = $input['scans'] ?? [];
        if (!is_array($scans) || !count($scans)) { echo json_encode(['status'=>'error','msg'=>'No scans']); break; }
        $rows = [];
        foreach ($scans as $s) {
            if (!is_array($s)) continue;
            $bc = strtoupper(trim(str_replace('#', '', $s['barcode'] ?? '')));
            if ($bc === '') continue;
            $ds = trim($s['dispatchStatus'] ?? 'Dispatched');
            if (!in_array($ds, ['Dispatched','Returned','Cancelled'])) $ds = 'Dispatched';
            $rows[] = [$bc, $ds, trim($s['courierName'] ?? ''), trim($s['awb'] ?? ''), date('Y-m-d H:i:s')];
        }
        if (!count($rows)) { echo json_encode(['status'=>'error','msg'=>'No valid scans']); break; }
        try {
            $pdo = getPdo();
            $res = insertScanRows($pdo, $rows);
            if ($res['imported'] > 0) {
                echo json_encode(['status'=>'success','count'=>$res['imported'],'failed'=>$res['failed'],'via'=>'db']);
                break;
            }
            // Nothing inserted -> fall through to the external API as a last resort.
            throw new \Exception($res['errors'][0] ?? 'no rows inserted');
        } catch (\Exception $e) {
            $payload = array_map(function($r){ return ['orderId'=>$r[0],'dispatchStatus'=>$r[1],'awb'=>$r[3],'courier'=>$r[2],'courierName'=>$r[2],'qty'=>'','remark'=>'']; }, $rows);
            $apiRes = apiPost(['action'=>'bulk_dispatch','data'=>$payload]);
            echo json_encode(['status'=>'success','count'=>count($payload),'data'=>$apiRes,'via'=>'api']);
        }
        break;

    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 'orders_by_phone':
        $pdo2 = getPdo();
        $phoneRaw = trim($input['phone'] ?? $_GET['phone'] ?? '');
        $phoneDigits = preg_replace('/\D/', '', $phoneRaw);
        if ($phoneDigits === '') { echo json_encode(['status'=>'success','data'=>[]]); break; }
        $excludeOrderId = trim(str_replace('#','', $input['excludeOrderId'] ?? $_GET['excludeOrderId'] ?? ''));
        $results = [];
        foreach (['shopify_orders','last_year_orders'] as $tbl) {
            try {
                $stmt = $pdo2->prepare("SELECT order_name, created_at, financial_status, fulfillment_status,
                    tags, total, line_items, telecaller_status
                    FROM $tbl
                    WHERE regexp_replace(COALESCE(billing_phone,''), '[^0-9]', '', 'g') LIKE :ph
                       OR regexp_replace(COALESCE(phone,''), '[^0-9]', '', 'g') LIKE :ph
                    ORDER BY created_at DESC LIMIT 20");
                $stmt->execute([':ph' => '%'.substr($phoneDigits, -10)]);
                foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
                    $oid = trim(str_replace('#','', $row['order_name'] ?? ''));
                    if ($oid === '' || $oid === $excludeOrderId) continue;
                    $itemName = '';
                    if (!empty($row['line_items'])) {
                        $li = is_string($row['line_items']) ? json_decode($row['line_items'], true) : $row['line_items'];
                        if (is_array($li) && count($li)) $itemName = $li[0]['title'] ?? ($li[0]['sku'] ?? '');
                    }
                    $results[$oid] = [
                        'orderId'         => $oid,
                        'createdAt'       => $row['created_at'] ?? '',
                        'financialStatus' => $row['financial_status'] ?? '',
                        'fulfillmentStatus'=> $row['fulfillment_status'] ?? '',
                        'tags'            => $row['tags'] ?? '',
                        'total'           => $row['total'] ?? '',
                        'itemName'        => $itemName,
                        'status'          => $row['telecaller_status'] ?? '',
                    ];
                }
            } catch (Exception $e) { /* table optional */ }
        }

        // Enrich with REAL order status (dispatch/delivery) instead of just the generic
        // telecaller lead status — pulled from scanning (dispatch) and clickpost_webhook (delivery).
        if (count($results)) {
            $oidList = array_keys($results);
            $placeholders = implode(',', array_fill(0, count($oidList), '?'));
            try {
                $scanRows = $pdo2->prepare("SELECT order_id, dispatch_status FROM scanning
                    WHERE TRIM(REPLACE(order_id,'#','')) IN ($placeholders)");
                $scanRows->execute($oidList);
                foreach ($scanRows->fetchAll(PDO::FETCH_ASSOC) as $sr) {
                    $oid = trim(str_replace('#','', $sr['order_id'] ?? ''));
                    if (isset($results[$oid])) $results[$oid]['realStatus'] = $sr['dispatch_status'] ?? '';
                }
            } catch (Exception $e) { /* scanning optional */ }
            try {
                $cwRows = $pdo2->prepare("SELECT order_id, status FROM clickpost_webhook
                    WHERE TRIM(REPLACE(order_id,'#','')) IN ($placeholders)
                    ORDER BY updated_at DESC");
                $cwRows->execute($oidList);
                foreach ($cwRows->fetchAll(PDO::FETCH_ASSOC) as $cr) {
                    $oid = trim(str_replace('#','', $cr['order_id'] ?? ''));
                    if (!isset($results[$oid])) continue;
                    $st = strtolower($cr['status'] ?? '');
                    // Delivered/RTO from Clickpost takes priority as the most "real" terminal status
                    if (strpos($st,'deliver') !== false || strpos($st,'rto') !== false) {
                        if (empty($results[$oid]['realStatus']) || strpos($st,'deliver') !== false) {
                            $results[$oid]['realStatus'] = $cr['status'];
                        }
                    }
                }
            } catch (Exception $e) { /* clickpost optional */ }
            // Final fallback chain: realStatus (dispatch/delivery) > fulfillment_status > telecaller_status
            foreach ($results as $oid => $r) {
                if (!empty($r['realStatus'])) {
                    $results[$oid]['status'] = $r['realStatus'];
                } elseif (!empty($r['fulfillmentStatus']) && strtolower($r['fulfillmentStatus']) !== 'unfulfilled') {
                    $results[$oid]['status'] = $r['fulfillmentStatus'];
                }
                // else: keep telecaller_status (e.g. "New Lead") as the last-resort fallback
            }
        }
        // Sort combined results by date desc, cap at 10
        $out = array_values($results);
        usort($out, function($a,$b){ return strtotime($b['createdAt']) <=> strtotime($a['createdAt']); });
        echo json_encode(['status'=>'success','data'=>array_slice($out,0,10)]);
        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 'scan_history':
        // Return scan rows (Dispatched/Returned/Cancelled) for a date range, built directly from the
        // scanning table. Loaded on demand by Dispatch & Scan so the main page load stays small/fast.
        $start = trim($input['start'] ?? '');
        $end   = trim($input['end']   ?? '');
        try {
            $pdo = getPdo();
            if ($start !== '' && $end !== '') {
                $stmt = $pdo->prepare("SELECT order_id, dispatch_status, courier_name, awb, timestamp
                                       FROM scanning
                                       WHERE dispatch_status IN ('Dispatched','Returned','Cancelled')
                                         AND LEFT(timestamp::text,10) BETWEEN :s AND :e
                                       ORDER BY timestamp DESC
                                       LIMIT 50000");
                $stmt->execute([':s'=>$start, ':e'=>$end]);
            } else {
                // No range -> last 60 days only (keeps the payload bounded)
                $cutoff60 = date('Y-m-d', strtotime('-60 days'));
                $stmt = $pdo->prepare("SELECT order_id, dispatch_status, courier_name, awb, timestamp
                                       FROM scanning
                                       WHERE dispatch_status IN ('Dispatched','Returned','Cancelled')
                                         AND LEFT(timestamp::text,10) >= :cutoff
                                       ORDER BY timestamp DESC
                                       LIMIT 20000");
                $stmt->execute([':cutoff'=>$cutoff60]);
            }
            $out = [];
            $neededIds = [];
            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                $id = trim(str_replace('#','',$row['order_id'] ?? ''));
                if ($id === '') continue;
                $neededIds[$id] = true;
                $out[] = [
                    'orderId'        => $id,
                    'billingName'    => 'Unknown',
                    'dispatchStatus' => $row['dispatch_status'] ?? '',
                    'courierName'    => $row['courier_name'] ?? '',
                    'awb'            => $row['awb'] ?? '',
                    'scanTimestamp'  => $row['timestamp'] ?? '',
                ];
            }
            // Enrich with real customer names — targeted lookup (only the order IDs actually
            // in this result set) so it stays fast even with thousands of scan rows.
            if (count($neededIds)) {
                $nameMap = [];
                $idList = array_keys($neededIds);
                foreach (['shopify_orders','last_year_orders'] as $tbl) {
                    try {
                        $chunks = array_chunk($idList, 1000);
                        foreach ($chunks as $chunk) {
                            $ph = implode(',', array_fill(0, count($chunk), '?'));
                            $nStmt = $pdo->prepare("SELECT order_name, billing_name, customer_name FROM $tbl
                                WHERE TRIM(REPLACE(order_name,'#','')) IN ($ph)");
                            $nStmt->execute($chunk);
                            foreach ($nStmt->fetchAll(PDO::FETCH_ASSOC) as $nr) {
                                $nid = trim(str_replace('#','', $nr['order_name'] ?? ''));
                                if ($nid === '' || isset($nameMap[$nid])) continue;
                                $nm = trim($nr['billing_name'] ?? $nr['customer_name'] ?? '');
                                if ($nm !== '') $nameMap[$nid] = $nm;
                            }
                        }
                    } catch (\Exception $e) { /* table optional */ }
                }
                foreach ($out as &$r) {
                    if (isset($nameMap[$r['orderId']])) $r['billingName'] = $nameMap[$r['orderId']];
                }
                unset($r);
            }
            echo json_encode(['status'=>'success','data'=>$out,'count'=>count($out)]);
        } catch (\Exception $e) {
            echo json_encode(['status'=>'error','msg'=>'DB error: '.$e->getMessage()]);
        }
        break;

    case 'pnl_orders':
        // Fetch orders for a date range DIRECTLY from the DB (shopify_orders + last_year_orders)
        // so the P&L works for ANY period, not just the latest 5000 the API returns.
        $start = trim($input['start'] ?? '');
        $end   = trim($input['end']   ?? '');
        if ($start === '' || $end === '') { echo json_encode(['status'=>'error','msg'=>'start/end required']); break; }
        try {
            $pdo = getPdo();
            // Which historical table exists?
            $tables = ['shopify_orders'];
            try {
                $hasLY = $pdo->query("SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name='last_year_orders')")->fetch();
                if ($hasLY && ($hasLY['exists']==='t' || $hasLY['exists']===true)) $tables[] = 'last_year_orders';
            } catch (\Exception $e) {}

            $orders = [];
            $seen = [];
            foreach ($tables as $tbl) {
              try {
                // Shopify created_at looks like "2026-06-23 23:53:52 +0530"; the date is the first 10 chars.
                $sql = "SELECT * FROM $tbl WHERE LEFT(created_at::text,10) BETWEEN :s AND :e";
                $stmt = $pdo->prepare($sql);
                $stmt->execute([':s'=>$start, ':e'=>$end]);
                while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                    $rawName = $row['order_name'] ?? $row['name'] ?? '';
                    $cleanId = trim(str_replace('#','',$rawName));
                    if ($cleanId === '' || isset($seen[$cleanId])) continue;
                    $seen[$cleanId] = true;
                    // keyed below by $cleanId so the scanning-enrichment pass after this loop
                    // can attach dispatchStatus/awb/courierName by order id in O(1).

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

                    $skuList = []; $totalQty = 0;
                    foreach ($lineItems as $li) {
                        $q = intval($li['quantity'] ?? 1); $totalQty += $q;
                        if (!empty($li['sku'])) {
                            $skuU = strtoupper(trim($li['sku']));
                            if (preg_match('/^([A-Z]+(?:-[A-Z]+)*-\d+)/', $skuU, $m)) {
                                $skuList[] = ['a'=>$m[1], 'q'=>$q, 'raw'=>$li['sku']];
                            } else {
                                $parts = explode('-', $skuU);
                                $art = count($parts) >= 2 ? $parts[0].'-'.$parts[1] : $skuU;
                                $skuList[] = ['a'=>$art, 'q'=>$q, 'raw'=>$li['sku']];
                            }
                        }
                    }

                    $vendor = str_starts_with($cleanId,'3434') ? 'Filohevis.com' : 'Filohevis.in';
                    $orders[$cleanId] = [
                        'orderId'         => $cleanId,
                        'longId'          => $row['shopify_order_id'] ?? $row['id'] ?? '',
                        'createdAt'       => $row['created_at'] ?? '',
                        'skuList'         => $skuList,
                        'itemQty'         => $totalQty > 0 ? $totalQty : 1,
                        'totalPrice'      => $row['total'] ?? '',
                        'financialStatus' => $row['financial_status'] ?? '',
                        'paymentMethod'   => $row['payment_method'] ?? '',
                        'tags'            => $row['tags'] ?? '',
                        'cancelledAt'     => $row['cancelled_at'] ?? '',
                        'vendor'          => $vendor,
                        // enriched below from the scanning table (dispatch is what was previously missing,
                        // which made the P&L Courier column always compute to ₹0)
                        'dispatchStatus'  => '',
                        'awb'             => '',
                        'courierName'     => '',
                    ];
                }
              } catch (\Exception $e) { /* skip this table, try the next */ }
            }

            // Enrich with scanning (dispatch_status/awb/courier_name) so the P&L's dispatch-count-driven
            // figures (Dispatch COD/PP, Courier cost = 125*COD-dispatch + 80*PP-dispatch) are correct for
            // ANY date range — not just orders whose scan happens to be in the last 10k rows kept in memory
            // client-side. Mirrors the same pattern already used by lifecycle_orders.
            if (count($orders)) {
                try {
                    $sc = $pdo->query("SELECT order_id, dispatch_status, awb, courier_name, timestamp
                                       FROM scanning ORDER BY timestamp ASC")->fetchAll(PDO::FETCH_ASSOC);
                    foreach ($sc as $r) {
                        $id = trim(str_replace('#','',$r['order_id'] ?? ''));
                        if ($id==='' || !isset($orders[$id])) continue;
                        $st = $r['dispatch_status'] ?? '';
                        if (!empty($st))                $orders[$id]['dispatchStatus'] = $st;
                        if (!empty($r['awb']))          $orders[$id]['awb']            = $r['awb'];
                        if (!empty($r['courier_name'])) $orders[$id]['courierName']    = $r['courier_name'];
                    }
                } catch (\Exception $e) { /* scanning enrichment is best-effort; orders still return without it */ }
            }

            echo json_encode(['status'=>'success','data'=>array_values($orders),'count'=>count($orders)]);
        } catch (\Exception $e) {
            echo json_encode(['status'=>'error','msg'=>'DB error: '.$e->getMessage()]);
        }
        break;

    case 'lifecycle_orders':
        // Fetch ALL orders in a date range from the DB and enrich with order_status (agent/status/timestamps)
        // + scanning (dispatch/awb/courier). Lets the Order Lifecycle cover any period, not just recent orders.
        $start = trim($input['start'] ?? '');
        $end   = trim($input['end']   ?? '');
        if ($start === '' || $end === '') { echo json_encode(['status'=>'error','msg'=>'start/end required']); break; }
        try {
            $pdo = getPdo();

            // 1) Orders in range from shopify_orders (+ last_year_orders if present)
            $tables = ['shopify_orders'];
            try {
                $hasLY = $pdo->query("SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name='last_year_orders')")->fetch();
                if ($hasLY && ($hasLY['exists']==='t' || $hasLY['exists']===true)) $tables[] = 'last_year_orders';
            } catch (\Exception $e) {}

            $orders = []; $seen = []; $ids = [];
            foreach ($tables as $tbl) {
              try {
                $stmt = $pdo->prepare("SELECT * FROM $tbl WHERE LEFT(created_at::text,10) BETWEEN :s AND :e");
                $stmt->execute([':s'=>$start, ':e'=>$end]);
                while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                    $rawName = $row['order_name'] ?? $row['name'] ?? '';
                    $cleanId = trim(str_replace('#','',$rawName));
                    if ($cleanId === '' || isset($seen[$cleanId])) continue;
                    $seen[$cleanId] = true; $ids[] = $cleanId;

                    $lineItems = [];
                    try { $li = json_decode($row['line_items'] ?? '[]', true); $lineItems = is_array($li) ? $li : []; } catch (\Exception $e) {}
                    $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'];
                    }
                    $vendor = str_starts_with($cleanId,'3434') ? 'Filohevis.com' : 'Filohevis.in';
                    $orders[$cleanId] = [
                        'orderId'=>$cleanId, 'cleanId'=>$cleanId,
                        'longId'=>$row['shopify_order_id'] ?? $row['id'] ?? '',
                        'createdAt'=>$row['created_at'] ?? '',
                        'billingName'=>$row['billing_name'] ?? $row['customer_name'] ?? '',
                        'billingPhone'=>$row['billing_phone'] ?? $row['phone'] ?? '',
                        'sku'=>implode(', ',$allSkus),
                        'itemName'=>!empty($allNames)?'• '.implode("\n• ",$allNames):'Multiple Items',
                        'itemQty'=>$totalQty>0?$totalQty:1,
                        'totalPrice'=>$row['total'] ?? '',
                        'financialStatus'=>$row['financial_status'] ?? '',
                        'paymentMethod'=>$row['payment_method'] ?? 'COD',
                        'vendor'=>$vendor, 'tags'=>$row['tags'] ?? '',
                        // enriched below
                        'agent'=>'', 'status'=>'Pending', 'agentRemark'=>'', 'assignDate'=>'', 'lastUpdated'=>'',
                        'dispatchStatus'=>'Pending', 'awb'=>'', 'courierName'=>'', 'scanTimestamp'=>'', 'deliveryStatus'=>'',
                    ];
                }
              } catch (\Exception $e) { /* skip table */ }
            }

            if (count($ids)) {
                // 2) Enrich with order_status (agent/status/assign/lastUpdated)
                try {
                    $os = $pdo->query("SELECT order_id, agent, status, remark, timestamp, assign_timestamp FROM order_status")->fetchAll(PDO::FETCH_ASSOC);
                    foreach ($os as $r) {
                        $id = trim(str_replace('#','',$r['order_id'] ?? ''));
                        if ($id==='' || !isset($orders[$id])) continue;
                        $orders[$id]['agent']       = trim($r['agent'] ?? '');
                        $orders[$id]['status']      = trim($r['status'] ?? 'Pending');
                        $orders[$id]['agentRemark'] = trim($r['remark'] ?? '');
                        $orders[$id]['assignDate']  = $r['timestamp'] ?? '';
                        $orders[$id]['lastUpdated'] = ($r['assign_timestamp'] ?? '') ?: ($r['timestamp'] ?? '');
                    }
                } catch (\Exception $e) {}

                // 3) Enrich with scanning. Track latest dispatch/awb/courier AND whether the order
                //    was EVER scanned as 'Returned' (so Return Status can show "Returned to HO").
                try {
                    $sc = $pdo->query("SELECT order_id, dispatch_status, awb, courier_name, timestamp
                                       FROM scanning ORDER BY timestamp ASC")->fetchAll(PDO::FETCH_ASSOC);
                    foreach ($sc as $r) {
                        $id = trim(str_replace('#','',$r['order_id'] ?? ''));
                        if ($id==='' || !isset($orders[$id])) continue;
                        $st = $r['dispatch_status'] ?? '';
                        if (!empty($st))                   $orders[$id]['dispatchStatus'] = $st;
                        if (!empty($r['awb']))             $orders[$id]['awb']            = $r['awb'];
                        if (!empty($r['courier_name']))    $orders[$id]['courierName']    = $r['courier_name'];
                        if (!empty($r['timestamp']))       $orders[$id]['scanTimestamp']  = $r['timestamp'];
                        if (strcasecmp(trim($st),'Returned')===0) $orders[$id]['scannedReturned'] = true;
                    }
                } catch (\Exception $e) {}

                // 3b) Also catch returns scanned by AWB only: match scanning.awb -> clickpost -> order.
                //     (Scan team sometimes scans just the AWB when a parcel comes back.)
                try {
                    // AWBs of orders in this range
                    $awbToId = [];
                    foreach ($orders as $oid => $od) {
                        $wb = strtoupper(trim($od['awb'] ?? ''));
                        if ($wb !== '') $awbToId[$wb] = $oid;
                    }
                    if (count($awbToId)) {
                        $retAwbs = $pdo->query("SELECT DISTINCT UPPER(TRIM(awb)) AS awb FROM scanning WHERE dispatch_status='Returned' AND awb IS NOT NULL AND awb<>''")->fetchAll(PDO::FETCH_ASSOC);
                        foreach ($retAwbs as $ra) {
                            $wb = $ra['awb'] ?? '';
                            if ($wb !== '' && isset($awbToId[$wb])) $orders[$awbToId[$wb]]['scannedReturned'] = true;
                        }
                    }
                } catch (\Exception $e) {}
            }

            echo json_encode(['status'=>'success','data'=>array_values($orders),'count'=>count($orders)]);
        } 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 enrichment map is built further below, after we know which order_ids we actually need —
        // this avoids loading the entire shopify_orders/last_year_orders tables into memory on every refresh.)

        // 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');
        $yesterday = (new DateTime('now', new DateTimeZone('Asia/Kolkata')))->modify('-1 day')->format('d/m/Y');

        // New/Old classification: based on the NDR's ACTUAL date (first_date), not on whether
        // the row happened to be touched during this specific refresh run. Rolling window:
        // New = first_date is today or yesterday; Old = anything older. Recomputed fresh on
        // every refresh, so yesterday's "New" rows automatically become "Old" the next morning,
        // and nothing is ever deleted — this only relabels the new_old flag.
        $classifyNewOld = function($firstDate) use ($today, $yesterday) {
            $d = substr(trim($firstDate), 0, 10);
            return ($d === $today || $d === $yesterday) ? 'New' : 'Old';
        };
        $nowStr = (new DateTime('now', new DateTimeZone('Asia/Kolkata')))->format('d/m/Y H:i');

        // Convert Excel serial date number (e.g. 46210.627) or various date strings to DD/MM/YYYY
        $parseNdrDate = function($raw) {
            if (empty($raw)) return '';
            $raw = trim($raw);
            // Excel serial number (pure numeric, possibly with decimal)
            if (is_numeric($raw) && (float)$raw > 40000 && (float)$raw < 60000) {
                // Excel epoch: 1899-12-30; serial 1 = 1900-01-01
                $unix = ((float)$raw - 25569) * 86400;
                return date('d/m/Y', (int)$unix);
            }
            // Already in DD/MM/YYYY format
            if (preg_match('#^(\d{2})/(\d{2})/(\d{4})#', $raw)) return substr($raw, 0, 10);
            // ISO format YYYY-MM-DD
            if (preg_match('#^(\d{4})-(\d{2})-(\d{2})#', $raw, $m)) return $m[3].'/'.$m[2].'/'.$m[1];
            return '';
        };

        // 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; }

        // Live supplement: pull NDR cases straight from clickpost_webhook (ndr_reason/ndr_remark,
        // captured in real time by clickpost_webhook.php) for any AWB NOT already covered by the
        // manual Raw2 upload above. Raw2 always wins when both exist, since it carries fields
        // (ofd_count, order_type, account code) that the webhook payload doesn't send. This means
        // a fresh Failed Delivery now shows up in NDR Working immediately, without waiting for the
        // next manual Clickpost NDR export.
        try {
            $cw = $pdo->query("SELECT order_id, waybill, courier_name, status, ndr_reason, ndr_remark, updated_at
                                FROM clickpost_webhook
                                WHERE (ndr_reason <> '' OR ndr_remark <> '')")->fetchAll(PDO::FETCH_ASSOC);
            foreach ($cw as $w) {
                $awb = trim($w['waybill'] ?? '');
                if ($awb === '' || isset($cpDedup[$awb])) continue; // Raw2 already has this AWB
                $cpDedup[$awb] = [
                    'order_id'       => trim($w['order_id'] ?? ''),
                    'current_status' => trim($w['status'] ?? ''),
                    'ndr_reason'     => trim($w['ndr_reason'] ?? ''),
                    'ndr_remark'     => trim($w['ndr_remark'] ?? ''),
                    'ofd_count'      => '',
                    'courier_partner'=> trim($w['courier_name'] ?? ''),
                    'order_type'     => '',
                    'ndr_timestamp'  => trim($w['updated_at'] ?? ''),
                ];
            }
        } catch (Exception $e) { /* webhook NDR columns optional; refresh still works without them */ }

        // Shopify enrichment map — queried DIRECTLY from shopify_orders/last_year_orders
        // (not the capped legacy external API), targeted to only the order_ids we actually need
        // from cpDedup, so blank fields (billing state, shipping address, SKU, customer name,
        // tags, total) populate correctly regardless of order age, without loading full tables.
        $shopifyMap = [];
        try {
            $neededIds = [];
            foreach ($cpDedup as $cp) {
                $oid = trim(str_replace('#','',$cp['order_id'] ?? ''));
                if ($oid !== '') $neededIds[$oid] = true;
            }
            // Also collect order IDs from existing rows that have blank Shopify fields —
            // those rows need $shopifyMap lookups too, for the carry-forward backfill loop below.
            foreach ($existing as $ex) {
                $blankSh = trim((string)($ex['billing_state'] ?? '')) === ''
                        || trim((string)($ex['shipping_addr'] ?? '')) === ''
                        || trim((string)($ex['customer_name'] ?? '')) === '';
                if (!$blankSh) continue;
                $oid = trim(str_replace('#','', $ex['order_id'] ?? ''));
                if ($oid !== '') $neededIds[$oid] = true;
            }
            if (count($neededIds)) {
                $idList = array_keys($neededIds);
                foreach (['shopify_orders','last_year_orders'] as $tbl) {
                    $chunks = array_chunk($idList, 1000);
                    foreach ($chunks as $chunk) {
                        $placeholders = implode(',', array_fill(0, count($chunk), '?'));
                        $stmt = $pdo->prepare("SELECT order_name, billing_name, customer_name, total, line_items,
                            billing_zip, billing_phone, phone, billing_address1, billing_address2,
                            tags, billing_province FROM $tbl WHERE TRIM(REPLACE(order_name,'#','')) IN ($placeholders)");
                        $stmt->execute($chunk);
                        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $so) {
                            $oid = trim($so['order_name'] ?? '');
                            if ($oid === '') continue;
                            if ($oid[0] !== '#') $oid = '#'.$oid;
                            if (isset($shopifyMap[$oid])) continue; // shopify_orders (fresher) takes priority
                            $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'        => '',
                                'total'        => $so['total'] ?? '',
                                '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'] ?? '',
                            ];
                        }
                    }
                }
            }
        } catch (Exception $e) { /* shopify optional; continue with blanks */ }

        $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'=>(trim((string)($ex['billing_state'] ?? '')) !== '') ? $ex['billing_state'] : ($sh['billing_state'] ?? ''),
                    'shipping_addr'=>(trim((string)($ex['shipping_addr'] ?? '')) !== '') ? $ex['shipping_addr'] : ($sh['shipping_addr'] ?? ''),
                    'ndr_reason'=>trim($cp['ndr_reason'] ?? ''),
                    'ndr_remark'=>trim($cp['ndr_remark'] ?? ''),
                    'billing_zip'=>(trim((string)($ex['billing_zip'] ?? '')) !== '') ? $ex['billing_zip'] : ($sh['billing_zip'] ?? ''),
                    'sku'=>(trim((string)($ex['sku'] ?? '')) !== '') ? $ex['sku'] : ($sh['sku'] ?? ''),
                    'customer_name'=>(trim((string)($ex['customer_name'] ?? '')) !== '') ? $ex['customer_name'] : ($sh['customer_name'] ?? ''),
                    'tags'=>(trim((string)($ex['tags'] ?? '')) !== '') ? $ex['tags'] : ($sh['tags'] ?? ''),
                    'total'=>(trim((string)($ex['total'] ?? '')) !== '') ? $ex['total'] : ($sh['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'=>$classifyNewOld($ex['first_date']),
                    'action'=>$ex['action'],
                    'remark1'=>$ex['remark1'],
                    'remark2'=>$ex['remark2'],
                ];
            } else {
                // Parse actual NDR date from raw2 timestamp (could be Excel serial like 46210.627 or a date string)
                $rawTs = $cp['ndr_timestamp'] ?? '';
                $actualDate = $parseNdrDate($rawTs);
                $firstDate = $actualDate ?: $today;
                $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'=>$firstDate,
                    'final_status'=>$finalStatus,
                    'final_date'=>$firstDate,
                    'last_updated'=>$nowStr,
                    'ageing'=>$ageingDays($firstDate),
                    'billing_phone'=>$sh['billing_phone'] ?? '',
                    'alternate_phone'=>'',
                    'pasted_date'=>$today,
                    'new_old'=>$classifyNewOld($firstDate),
                    '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']);
            // Always recompute new_old + ageing on every refresh so the rolling window
            // (New = today/yesterday, Old = older) stays correct even for rows this
            // particular refresh didn't otherwise touch.
            $row['new_old'] = $classifyNewOld($ex['first_date']);
            $row['ageing']  = $ageingDays($ex['first_date']);
            // Backfill blank Shopify fields from $shopifyMap when the stored value is empty —
            // this fixes historical rows built by earlier data.php versions that couldn't reach
            // Shopify (external API was capped/stale), so on subsequent refreshes those blanks
            // get populated instead of staying frozen forever.
            $exOid = trim($ex['order_id'] ?? '');
            if ($exOid !== '' && $exOid[0] !== '#') $exOid = '#'.$exOid;
            $shBackfill = $shopifyMap[$exOid] ?? [];
            if ($shBackfill) {
                foreach (['billing_state','shipping_addr','billing_zip','sku','customer_name','tags','total','billing_phone'] as $fld) {
                    if (trim((string)($row[$fld] ?? '')) === '' && !empty($shBackfill[$fld])) {
                        $row[$fld] = $shBackfill[$fld];
                    }
                }
            }
            if (isset($deliveredMap[$awb]) && trim($ex['final_status']) !== 'Delivered') {
                $row['final_status'] = 'Delivered';
                $row['final_date']   = $today;
                $row['last_updated'] = $nowStr;
            }
            $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)
        set_time_limit(180); // this refresh does real work; give it more room than the default before we optimized it
        $pdo->beginTransaction();
        try {
            $conflictSql = "
                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";
            $cols = "(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)";

            $nNew=0;$nUpd=0;$nRto=0;
            $awbList = array_keys($rows);
            $BATCH = 300;
            for ($i = 0; $i < count($awbList); $i += $BATCH) {
                $chunkAwbs = array_slice($awbList, $i, $BATCH);
                $valueGroups = [];
                $params = [];
                foreach ($chunkAwbs as $bi => $awb) {
                    $r = $rows[$awb];
                    $rto = $isRto($r);
                    if ($rto) $nRto++;
                    if (isset($existing[$awb])) $nUpd++; else $nNew++;
                    $valueGroups[] = "(:awb{$bi},:oid{$bi},:ofd{$bi},:bs{$bi},:sa{$bi},:rsn{$bi},:rmk{$bi},:bz{$bi},:sku{$bi},:cn{$bi},:tg{$bi},:tot{$bi},:cp{$bi},:ot{$bi},:fs{$bi},:fd{$bi},:fns{$bi},:fnd{$bi},:lu{$bi},:ag{$bi},:bp{$bi},:ap{$bi},:pd{$bi},:no{$bi},:act{$bi},:r1{$bi},:r2{$bi},:rto{$bi})";
                    $params[":awb{$bi}"]=$awb; $params[":oid{$bi}"]=$r['order_id']; $params[":ofd{$bi}"]=(string)$r['ofd_count'];
                    $params[":bs{$bi}"]=$r['billing_state']; $params[":sa{$bi}"]=$r['shipping_addr']; $params[":rsn{$bi}"]=$r['ndr_reason'];
                    $params[":rmk{$bi}"]=$r['ndr_remark']; $params[":bz{$bi}"]=$r['billing_zip']; $params[":sku{$bi}"]=$r['sku'];
                    $params[":cn{$bi}"]=$r['customer_name']; $params[":tg{$bi}"]=$r['tags']; $params[":tot{$bi}"]=(string)$r['total'];
                    $params[":cp{$bi}"]=$r['courier_partner']; $params[":ot{$bi}"]=$r['order_type']; $params[":fs{$bi}"]=$r['first_status'];
                    $params[":fd{$bi}"]=$r['first_date']; $params[":fns{$bi}"]=$r['final_status']; $params[":fnd{$bi}"]=$r['final_date'];
                    $params[":lu{$bi}"]=$r['last_updated']; $params[":ag{$bi}"]=(int)$r['ageing']; $params[":bp{$bi}"]=$r['billing_phone'];
                    $params[":ap{$bi}"]=$r['alternate_phone']; $params[":pd{$bi}"]=$r['pasted_date']; $params[":no{$bi}"]=$r['new_old'];
                    $params[":act{$bi}"]=$r['action']; $params[":r1{$bi}"]=$r['remark1']; $params[":r2{$bi}"]=$r['remark2'];
                    $params[":rto{$bi}"]=$rto ? 't' : 'f';
                }
                $sql = "INSERT INTO ndr_records $cols VALUES " . implode(',', $valueGroups) . $conflictSql;
                $stmt = $pdo->prepare($sql);
                $stmt->execute($params);
            }
            $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; }
        // Sorted oldest -> latest by the row's ACTUAL date (parsed from the DD/MM/YYYY text
        // column, since a plain string ORDER BY sorts lexicographically and gives the wrong
        // sequence — e.g. "05/07/2026" would incorrectly sort before "20/06/2026" as text).
        // Delivered/RTO-Delivered rows are NOT excluded here anymore — they're kept so the
        // dashboard's Delivered/Delivery% stats have permanent, complete history. The frontend
        // hides them from the NDR Working table specifically, without losing them for stats.
        $dateSort = function($col) {
            return "CASE WHEN $col ~ '^\\d{2}/\\d{2}/\\d{4}' THEN TO_DATE(SUBSTRING($col,1,10),'DD/MM/YYYY') ELSE NULL END ASC NULLS LAST";
        };
        $ndr = $pdo->query("SELECT * FROM ndr_records WHERE is_rto = FALSE AND COALESCE(TRIM(order_id),'') <> ''
            ORDER BY " . $dateSort('first_date') . ", awb")->fetchAll(PDO::FETCH_ASSOC);
        $rto = $pdo->query("SELECT * FROM ndr_records WHERE is_rto = TRUE AND COALESCE(TRIM(order_id),'') <> ''
            ORDER BY " . $dateSort('final_date') . ", 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;

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


    // ════════════════════════════════════════════════════════
    //  SKU → ARTICLE MAP (built from Aricle_Wise.xlsx master)
    // ════════════════════════════════════════════════════════
    case 'sku_article_get':
    $skuArticleMap = [
        'C-551-Orange-XXL' => 'C-551',
        'C-551-White-XXL' => 'C-551',
        'C-555-Black-XL' => 'C-555',
        'C-555-Navy-Blue-L' => 'C-555',
        'C-555-Navy-Blue-XL' => 'C-555',
        'C-555-White-L' => 'C-555',
        'C-555-White-XL' => 'C-555',
        'C-558-Black-XL' => 'C-558',
        'C-558-Navy-Blue-XL' => 'C-558',
        'C-558-White-XL' => 'C-558',
        'C-561-Grey-XXL' => 'C-561',
        'Cufflinks' => 'Cufflinks',
        'Cufflinks-A' => 'Cufflinks',
        'Cufflinks-D' => 'Cufflinks',
        'Cufflinks-K' => 'Cufflinks',
        'Cufflinks-N' => 'Cufflinks',
        'Cufflinks-R' => 'Cufflinks',
        'Cufflinks-V' => 'Cufflinks',
        'F-104-Black-L' => 'F-104',
        'F-104-Black-M' => 'F-104',
        'F-104-Black-XL' => 'F-104',
        'F-104-Black-XXL' => 'F-104',
        'F-104-Cream-2XL' => 'F-104',
        'F-104-Cream-L' => 'F-104',
        'F-104-Cream-M' => 'F-104',
        'F-104-Cream-XL' => 'F-104',
        'F-104-Grey-L' => 'F-104',
        'F-104-Grey-M' => 'F-104',
        'F-104-Grey-XL' => 'F-104',
        'F-104-Grey-XXL' => 'F-104',
        'F-104-Natural-Beige-L' => 'F-104',
        'F-104-Natural-Beige-M' => 'F-104',
        'F-104-Natural-Beige-XL' => 'F-104',
        'F-104-Natural-Beige-XXL' => 'F-104',
        'F-104-Navy-Blue-2XL' => 'F-104',
        'F-104-Navy-Blue-L' => 'F-104',
        'F-104-Navy-Blue-M' => 'F-104',
        'F-104-Navy-Blue-XL' => 'F-104',
        'F-104-Pink-L' => 'F-104',
        'F-104-Pink-M' => 'F-104',
        'F-104-Pink-XL' => 'F-104',
        'F-104-Pink-XXL' => 'F-104',
        'F-104-Pista-Green-L' => 'F-104',
        'F-104-Pista-Green-M' => 'F-104',
        'F-104-Pista-Green-XL' => 'F-104',
        'F-104-Pista-Green-XXL' => 'F-104',
        'F-104-Sky-L' => 'F-104',
        'F-104-Sky-M' => 'F-104',
        'F-104-Sky-XL' => 'F-104',
        'F-104-Sky-XXL' => 'F-104',
        'F-104-White-L' => 'F-104',
        'F-104-White-M' => 'F-104',
        'F-104-White-XL' => 'F-104',
        'F-104-White-XXL' => 'F-104',
        'F-104-Yellow-2XL' => 'F-104',
        'F-104-Yellow-L' => 'F-104',
        'F-104-Yellow-M' => 'F-104',
        'F-104-Yellow-XL' => 'F-104',
        'F-104-Yellow-XXL' => 'F-104',
        'F-105-Beige-L' => 'F-105',
        'F-107-Black-L' => 'F-107',
        'F-107-Black-M' => 'F-107',
        'F-107-Black-S' => 'F-107',
        'F-107-Black-XXL' => 'F-107',
        'F-107-Brown-L' => 'F-107',
        'F-107-Brown-M' => 'F-107',
        'F-107-Brown-XL' => 'F-107',
        'F-107-Dark-Green-L' => 'F-107',
        'F-107-Light-Grey-L' => 'F-107',
        'F-107-Light-Grey-M' => 'F-107',
        'F-107-Light-Pink-2XL' => 'F-107',
        'F-107-Light-Pink-L' => 'F-107',
        'F-107-Light-Pink-M' => 'F-107',
        'F-107-Light-Pink-XL' => 'F-107',
        'F-107-Light-Sky-L' => 'F-107',
        'F-107-Light-Sky-M' => 'F-107',
        'F-107-Light-Sky-XL' => 'F-107',
        'F-107-Light-Sky-XXL' => 'F-107',
        'F-107-Mehandi-Olive-L' => 'F-107',
        'F-107-Mehandi-Olive-XL' => 'F-107',
        'F-107-Navy-Blue-L' => 'F-107',
        'F-107-Navy-Blue-M' => 'F-107',
        'F-107-Navy-Blue-S' => 'F-107',
        'F-107-Navy-Blue-XL' => 'F-107',
        'F-107-Navy-Blue-XXL' => 'F-107',
        'F-107-Sky-L' => 'F-107',
        'F-107-Sky-M' => 'F-107',
        'F-107-Sky-S' => 'F-107',
        'F-107-Sky-XL' => 'F-107',
        'F-107-Sky-XXL' => 'F-107',
        'F-107-White-2XL' => 'F-107',
        'F-107-White-L' => 'F-107',
        'F-107-White-M' => 'F-107',
        'F-107-White-S' => 'F-107',
        'F-107-White-XL' => 'F-107',
        'F-107-Wine-L' => 'F-107',
        'F-107-Wine-M' => 'F-107',
        'F-107-Wine-S' => 'F-107',
        'F-107-Wine-XL' => 'F-107',
        'F-107-Wine-XXL' => 'F-107',
        'F-109-Beige-2XL' => 'F-109',
        'F-109-Beige-L' => 'F-109',
        'F-109-Beige-M' => 'F-109',
        'F-109-Beige-S' => 'F-109',
        'F-109-Beige-XL' => 'F-109',
        'F-109-Black-L' => 'F-109',
        'F-109-Black-M' => 'F-109',
        'F-109-Black-S' => 'F-109',
        'F-109-Black-XL' => 'F-109',
        'F-109-Black-XXL' => 'F-109',
        'F-109-Brown-3XL' => 'F-109',
        'F-109-Brown-4XL' => 'F-109',
        'F-109-Brown-L' => 'F-109',
        'F-109-Brown-M' => 'F-109',
        'F-109-Brown-S' => 'F-109',
        'F-109-Brown-XL' => 'F-109',
        'F-109-Brown-XXL' => 'F-109',
        'F-109-Green-3XL' => 'F-109',
        'F-109-Green-L' => 'F-109',
        'F-109-Green-M' => 'F-109',
        'F-109-Green-S' => 'F-109',
        'F-109-Green-XL' => 'F-109',
        'F-109-Green-XXL' => 'F-109',
        'F-109-Light-Grey-L' => 'F-109',
        'F-109-Light-Grey-M' => 'F-109',
        'F-109-Light-Grey-S' => 'F-109',
        'F-109-Light-Grey-XL' => 'F-109',
        'F-109-Light-Grey-XXL' => 'F-109',
        'F-109-Navy-Blue-3XL' => 'F-109',
        'F-109-Navy-Blue-L' => 'F-109',
        'F-109-Navy-Blue-M' => 'F-109',
        'F-109-Navy-Blue-S' => 'F-109',
        'F-109-Navy-Blue-XL' => 'F-109',
        'F-109-Navy-Blue-XXL' => 'F-109',
        'F-109-Peach-3XL' => 'F-109',
        'F-109-Peach-XL' => 'F-109',
        'F-109-Sky-2XL' => 'F-109',
        'F-109-Sky-3XL' => 'F-109',
        'F-109-Sky-L' => 'F-109',
        'F-109-Sky-M' => 'F-109',
        'F-109-Sky-S' => 'F-109',
        'F-109-Sky-XL' => 'F-109',
        'F-109-White-3XL' => 'F-109',
        'F-109-White-L' => 'F-109',
        'F-109-White-M' => 'F-109',
        'F-109-White-S' => 'F-109',
        'F-109-White-XL' => 'F-109',
        'F-109-White-XXL' => 'F-109',
        'F-109-Wine-3XL' => 'F-109',
        'F-109-Wine-5XL' => 'F-109',
        'F-109-Wine-L' => 'F-109',
        'F-109-Wine-M' => 'F-109',
        'F-109-Wine-S' => 'F-109',
        'F-109-Wine-XL' => 'F-109',
        'F-109-Wine-XXL' => 'F-109',
        'F-112-Beige-L' => 'F-112',
        'F-112-Beige-XL' => 'F-112',
        'F-112-Beige-XXL' => 'F-112',
        'F-112-Black-L' => 'F-112',
        'F-112-Black-Light-Green-L' => 'F-112',
        'F-112-Black-Light-Green-M' => 'F-112',
        'F-112-Black-M' => 'F-112',
        'F-112-Black-Sky-XL' => 'F-112',
        'F-112-Black-XL' => 'F-112',
        'F-112-Black-XXL' => 'F-112',
        'F-112-Brown-M' => 'F-112',
        'F-112-Brown-XL' => 'F-112',
        'F-112-Dark-Green-L' => 'F-112',
        'F-112-Dark-Green-XL' => 'F-112',
        'F-112-Dark-Maroon-L' => 'F-112',
        'F-112-Dark-Maroon-M' => 'F-112',
        'F-112-Dark-Maroon-XL' => 'F-112',
        'F-112-Green-L' => 'F-112',
        'F-112-Green-M' => 'F-112',
        'F-112-Grey-L' => 'F-112',
        'F-112-Grey-M' => 'F-112',
        'F-112-Grey-XL' => 'F-112',
        'F-112-Grey-XXL' => 'F-112',
        'F-112-Light-Brown-2XL' => 'F-112',
        'F-112-Light-Brown-L' => 'F-112',
        'F-112-Light-Brown-XL' => 'F-112',
        'F-112-Light-Green-L' => 'F-112',
        'F-112-Light-Green-M' => 'F-112',
        'F-112-Light-Green-XL' => 'F-112',
        'F-112-Light-Green-XXL' => 'F-112',
        'F-112-Light-Grey-L' => 'F-112',
        'F-112-Light-Grey-M' => 'F-112',
        'F-112-Light-Grey-XL' => 'F-112',
        'F-112-Mehandi-Green-L' => 'F-112',
        'F-112-Mehandi-Green-M' => 'F-112',
        'F-112-Mehandi-Green-XL' => 'F-112',
        'F-112-Mehandi-Green-XXL' => 'F-112',
        'F-112-Navy-Blue-L' => 'F-112',
        'F-112-Navy-Blue-M' => 'F-112',
        'F-112-Navy-Blue-XL' => 'F-112',
        'F-112-Navy-Blue-XXL' => 'F-112',
        'F-112-Red-L' => 'F-112',
        'F-112-Red-XL' => 'F-112',
        'F-112-Sky-L' => 'F-112',
        'F-112-Sky-M' => 'F-112',
        'F-112-Sky-XL' => 'F-112',
        'F-112-Sky-XXL' => 'F-112',
        'F-112-Teal-Blue-M' => 'F-112',
        'F-112-Teal-Blue-XL' => 'F-112',
        'F-112-Turquoise-Green-2XL' => 'F-112',
        'F-112-Turquoise-Green-L' => 'F-112',
        'F-112-White-Beige-XL' => 'F-112',
        'F-112-White-Gray-XL' => 'F-112',
        'F-112-White-L' => 'F-112',
        'F-112-White-M' => 'F-112',
        'F-112-White-Red-L' => 'F-112',
        'F-112-White-XL' => 'F-112',
        'F-112-White-XXL' => 'F-112',
        'F-114-Dark Grey-XL' => 'F-114',
        'F-114-Dark-Grey-XL' => 'F-114',
        'F-114-Light-Brown-M' => 'F-114',
        'F-114-Sky-Blue-L' => 'F-114',
        'F-115-Black-3XL' => 'F-115',
        'F-115-Black-4XL' => 'F-115',
        'F-115-Black-Cream-L' => 'F-115',
        'F-115-Black-Cream-M' => 'F-115',
        'F-115-Black-Cream-XL' => 'F-115',
        'F-115-Black-Cream-XXL' => 'F-115',
        'F-115-Black-Grey-L' => 'F-115',
        'F-115-Black-Grey-M' => 'F-115',
        'F-115-Black-Grey-XL' => 'F-115',
        'F-115-Black-Grey-XXL' => 'F-115',
        'F-115-Black-L' => 'F-115',
        'F-115-Black-Light-Mehndi-L' => 'F-115',
        'F-115-Black-Light-Mehndi-M' => 'F-115',
        'F-115-Black-Light-Mehndi-XL' => 'F-115',
        'F-115-Black-M' => 'F-115',
        'F-115-Black-Maroon-L' => 'F-115',
        'F-115-Black-Maroon-XL' => 'F-115',
        'F-115-Black-Maroon-XXL' => 'F-115',
        'F-115-Black-Navy-Blue-L' => 'F-115',
        'F-115-Black-Navy-Blue-M' => 'F-115',
        'F-115-Black-Navy-Blue-XL' => 'F-115',
        'F-115-Black-Onion-L' => 'F-115',
        'F-115-Black-Onion-M' => 'F-115',
        'F-115-Black-Onion-XL' => 'F-115',
        'F-115-Black-Onion-XXL' => 'F-115',
        'F-115-Black-Orange-L' => 'F-115',
        'F-115-Black-Orange-XL' => 'F-115',
        'F-115-Black-Orange-XXL' => 'F-115',
        'F-115-Black-Pink-L' => 'F-115',
        'F-115-Black-Pink-M' => 'F-115',
        'F-115-Black-Pink-XL' => 'F-115',
        'F-115-Black-Pink-XXL' => 'F-115',
        'F-115-Black-Pista-Green-L' => 'F-115',
        'F-115-Black-Pista-Green-M' => 'F-115',
        'F-115-Black-Pista-Green-XL' => 'F-115',
        'F-115-Black-Pista-Green-XXL' => 'F-115',
        'F-115-Black-Royal-Blue-L' => 'F-115',
        'F-115-Black-Royal-Blue-M' => 'F-115',
        'F-115-Black-S' => 'F-115',
        'F-115-Black-Sky-L' => 'F-115',
        'F-115-Black-Sky-M' => 'F-115',
        'F-115-Black-Sky-XL' => 'F-115',
        'F-115-Black-Sky-XXL' => 'F-115',
        'F-115-Black-Teal-Blue-L' => 'F-115',
        'F-115-Black-Teal-Blue-M' => 'F-115',
        'F-115-Black-Teal-Blue-XL' => 'F-115',
        'F-115-Black-Teal-Blue-XXL' => 'F-115',
        'F-115-Black-Teal-Green-L' => 'F-115',
        'F-115-Black-Teal-Green-M' => 'F-115',
        'F-115-Black-Teal-Green-XL' => 'F-115',
        'F-115-Black-Teal-Green-XXL' => 'F-115',
        'F-115-Black-White-L' => 'F-115',
        'F-115-Black-White-M' => 'F-115',
        'F-115-Black-White-XL' => 'F-115',
        'F-115-Black-White-XXL' => 'F-115',
        'F-115-Black-Wine-L' => 'F-115',
        'F-115-Black-Wine-M' => 'F-115',
        'F-115-Black-Wine-XL' => 'F-115',
        'F-115-Black-Wine-XXL' => 'F-115',
        'F-115-Black-XL' => 'F-115',
        'F-115-Black-XXL' => 'F-115',
        'F-115-Brown-2XL' => 'F-115',
        'F-115-Brown-L' => 'F-115',
        'F-115-Brown-M' => 'F-115',
        'F-115-Brown-S' => 'F-115',
        'F-115-Brown-XL' => 'F-115',
        'F-115-Cream-L' => 'F-115',
        'F-115-Cream-M' => 'F-115',
        'F-115-Cream-S' => 'F-115',
        'F-115-Cream-XL' => 'F-115',
        'F-115-Cream-XXL' => 'F-115',
        'F-115-Grey-3XL' => 'F-115',
        'F-115-Grey-4XL' => 'F-115',
        'F-115-Grey-L' => 'F-115',
        'F-115-Grey-M' => 'F-115',
        'F-115-Grey-S' => 'F-115',
        'F-115-Grey-XL' => 'F-115',
        'F-115-Grey-XXL' => 'F-115',
        'F-115-Light-Mehndi-L' => 'F-115',
        'F-115-Light-Mehndi-M' => 'F-115',
        'F-115-Light-Mehndi-XL' => 'F-115',
        'F-115-Light-Mehndi-XXL' => 'F-115',
        'F-115-Light-Mehndi-XXXL' => 'F-115',
        'F-115-Maroon-3XL' => 'F-115',
        'F-115-Maroon-4XL' => 'F-115',
        'F-115-Maroon-L' => 'F-115',
        'F-115-Maroon-M' => 'F-115',
        'F-115-Maroon-S' => 'F-115',
        'F-115-Maroon-XL' => 'F-115',
        'F-115-Maroon-XXL' => 'F-115',
        'F-115-Navy-Blue-3XL' => 'F-115',
        'F-115-Navy-Blue-4XL' => 'F-115',
        'F-115-Navy-Blue-L' => 'F-115',
        'F-115-Navy-Blue-M' => 'F-115',
        'F-115-Navy-Blue-S' => 'F-115',
        'F-115-Navy-Blue-XL' => 'F-115',
        'F-115-Navy-Blue-XXL' => 'F-115',
        'F-115-Onion-4XL' => 'F-115',
        'F-115-Onion-L' => 'F-115',
        'F-115-Onion-M' => 'F-115',
        'F-115-Onion-S' => 'F-115',
        'F-115-Onion-XL' => 'F-115',
        'F-115-Onion-XXL' => 'F-115',
        'F-115-Onion-XXXL' => 'F-115',
        'F-115-Orange-L' => 'F-115',
        'F-115-Orange-M' => 'F-115',
        'F-115-Orange-XL' => 'F-115',
        'F-115-Orange-XXL' => 'F-115',
        'F-115-Pink-3XL' => 'F-115',
        'F-115-Pink-L' => 'F-115',
        'F-115-Pink-M' => 'F-115',
        'F-115-Pink-S' => 'F-115',
        'F-115-Pink-XL' => 'F-115',
        'F-115-Pink-XXL' => 'F-115',
        'F-115-Pista-Green-L' => 'F-115',
        'F-115-Pista-Green-M' => 'F-115',
        'F-115-Pista-Green-S' => 'F-115',
        'F-115-Pista-Green-XL' => 'F-115',
        'F-115-Pista-Green-XXL' => 'F-115',
        'F-115-Royal-Blue-L' => 'F-115',
        'F-115-Royal-Blue-M' => 'F-115',
        'F-115-Royal-Blue-S' => 'F-115',
        'F-115-Royal-Blue-XL' => 'F-115',
        'F-115-Royal-Blue-XXL' => 'F-115',
        'F-115-Sky-3XL' => 'F-115',
        'F-115-Sky-4XL' => 'F-115',
        'F-115-Sky-L' => 'F-115',
        'F-115-Sky-M' => 'F-115',
        'F-115-Sky-S' => 'F-115',
        'F-115-Sky-XL' => 'F-115',
        'F-115-Sky-XXL' => 'F-115',
        'F-115-Teal-Blue-4XL' => 'F-115',
        'F-115-Teal-Blue-L' => 'F-115',
        'F-115-Teal-Blue-M' => 'F-115',
        'F-115-Teal-Blue-S' => 'F-115',
        'F-115-Teal-Blue-XL' => 'F-115',
        'F-115-Teal-Blue-XXL' => 'F-115',
        'F-115-Teal-Blue-XXXL' => 'F-115',
        'F-115-Teal-Green-L' => 'F-115',
        'F-115-Teal-Green-M' => 'F-115',
        'F-115-Teal-Green-XL' => 'F-115',
        'F-115-Teal-Green-XXL' => 'F-115',
        'F-115-Teal-Green-XXXL' => 'F-115',
        'F-115-White-3XL' => 'F-115',
        'F-115-White-4XL' => 'F-115',
        'F-115-White-Black-Button-2XL' => 'F-115',
        'F-115-White-Black-Button-3XL' => 'F-115',
        'F-115-White-Black-Button-4XL' => 'F-115',
        'F-115-White-Black-Button-L' => 'F-115',
        'F-115-White-Black-Button-M' => 'F-115',
        'F-115-White-Black-Button-S' => 'F-115',
        'F-115-White-Black-Button-XL' => 'F-115',
        'F-115-White-Cream-L' => 'F-115',
        'F-115-White-Cream-M' => 'F-115',
        'F-115-White-Cream-XL' => 'F-115',
        'F-115-White-Cream-XXL' => 'F-115',
        'F-115-White-Grey-L' => 'F-115',
        'F-115-White-Grey-M' => 'F-115',
        'F-115-White-Grey-XL' => 'F-115',
        'F-115-White-Grey-XXL' => 'F-115',
        'F-115-White-L' => 'F-115',
        'F-115-White-Light-Mehndi-L' => 'F-115',
        'F-115-White-Light-Mehndi-M' => 'F-115',
        'F-115-White-Light-Mehndi-XL' => 'F-115',
        'F-115-White-Light-Mehndi-XXL' => 'F-115',
        'F-115-White-M' => 'F-115',
        'F-115-White-Maroon-L' => 'F-115',
        'F-115-White-Maroon-M' => 'F-115',
        'F-115-White-Maroon-XL' => 'F-115',
        'F-115-White-Maroon-XXL' => 'F-115',
        'F-115-White-Navy-Blue-L' => 'F-115',
        'F-115-White-Navy-Blue-M' => 'F-115',
        'F-115-White-Navy-Blue-XL' => 'F-115',
        'F-115-White-Navy-Blue-XXL' => 'F-115',
        'F-115-White-Onion-L' => 'F-115',
        'F-115-White-Onion-M' => 'F-115',
        'F-115-White-Onion-XL' => 'F-115',
        'F-115-White-Onion-XXL' => 'F-115',
        'F-115-White-Orange-L' => 'F-115',
        'F-115-White-Orange-M' => 'F-115',
        'F-115-White-Orange-XL' => 'F-115',
        'F-115-White-Orange-XXL' => 'F-115',
        'F-115-White-Pink-L' => 'F-115',
        'F-115-White-Pink-M' => 'F-115',
        'F-115-White-Pink-XL' => 'F-115',
        'F-115-White-Pink-XXL' => 'F-115',
        'F-115-White-Pista-Green-L' => 'F-115',
        'F-115-White-Pista-Green-M' => 'F-115',
        'F-115-White-Pista-Green-XL' => 'F-115',
        'F-115-White-Pista-Green-XXL' => 'F-115',
        'F-115-White-Royal-Blue-L' => 'F-115',
        'F-115-White-Royal-Blue-M' => 'F-115',
        'F-115-White-Royal-Blue-XL' => 'F-115',
        'F-115-White-Royal-Blue-XXL' => 'F-115',
        'F-115-White-S' => 'F-115',
        'F-115-White-Sky-L' => 'F-115',
        'F-115-White-Sky-M' => 'F-115',
        'F-115-White-Sky-XL' => 'F-115',
        'F-115-White-Sky-XXL' => 'F-115',
        'F-115-White-Teal-Blue-L' => 'F-115',
        'F-115-White-Teal-Blue-M' => 'F-115',
        'F-115-White-Teal-Blue-XL' => 'F-115',
        'F-115-White-Teal-Blue-XXL' => 'F-115',
        'F-115-White-Teal-Green-L' => 'F-115',
        'F-115-White-Teal-Green-M' => 'F-115',
        'F-115-White-Teal-Green-XL' => 'F-115',
        'F-115-White-Teal-Green-XXL' => 'F-115',
        'F-115-White-Wine-L' => 'F-115',
        'F-115-White-Wine-M' => 'F-115',
        'F-115-White-Wine-XL' => 'F-115',
        'F-115-White-Wine-XXL' => 'F-115',
        'F-115-White-XL' => 'F-115',
        'F-115-White-XXL' => 'F-115',
        'F-115-Wine-L' => 'F-115',
        'F-115-Wine-M' => 'F-115',
        'F-115-Wine-S' => 'F-115',
        'F-115-Wine-XL' => 'F-115',
        'F-115-Wine-XXL' => 'F-115',
        'F-115-pink-XXL' => 'F-115',
        'F-118-Beige-L' => 'F-118',
        'F-118-Light-Green-L' => 'F-118',
        'F-118-Light-Green-XL' => 'F-118',
        'F-118-Teal-Blue-2XL' => 'F-118',
        'F-119-Black-L' => 'F-119',
        'F-119-Black-S' => 'F-119',
        'F-119-Black-XXL' => 'F-119',
        'F-119-Brown-L' => 'F-119',
        'F-119-Brown-M' => 'F-119',
        'F-119-Green-L' => 'F-119',
        'F-119-Green-XL' => 'F-119',
        'F-119-Green-XXL' => 'F-119',
        'F-119-Pista-Green-L' => 'F-119',
        'F-119-Pista-Green-M' => 'F-119',
        'F-119-Pista-Green-S' => 'F-119',
        'F-119-Pista-Green-XL' => 'F-119',
        'F-119-Pista-Green-XXL' => 'F-119',
        'F-119-Sky-Blue-M' => 'F-119',
        'F-119-White-L' => 'F-119',
        'F-119-White-XL' => 'F-119',
        'F-119-Wine-XL' => 'F-119',
        'F-120-Beige-L' => 'F-120',
        'F-120-Beige-XL' => 'F-120',
        'F-120-Black-M' => 'F-120',
        'F-120-Grey-L' => 'F-120',
        'F-120-Grey-XL' => 'F-120',
        'F-120-Mehendi-Green-L' => 'F-120',
        'F-120-Mehendi-Green-XL' => 'F-120',
        'F-120-Navy-Blue-XL' => 'F-120',
        'F-120-Olive-Green-M' => 'F-120',
        'F-120-Olive-Green-XL' => 'F-120',
        'F-120-Pink-L' => 'F-120',
        'F-120-Pink-XXL' => 'F-120',
        'F-120-Red-XL' => 'F-120',
        'F-120-Red-XXL' => 'F-120',
        'F-120-Sky-M' => 'F-120',
        'F-120-Sky-XL' => 'F-120',
        'F-120-Sky-XXL' => 'F-120',
        'F-120-Teal-Blue-XL' => 'F-120',
        'F-120-Teal-Blue-XXL' => 'F-120',
        'F-120-White-L' => 'F-120',
        'F-120-White-XL' => 'F-120',
        'F-120-Yellow-XL' => 'F-120',
        'F-131-Blue-M' => 'F-131',
        'F-131-Purple-XL' => 'F-131',
        'F-131-White-L' => 'F-131',
        'F-131-White-XL' => 'F-131',
        'F-141-Beige-2XL' => 'F-141',
        'F-141-Beige-L' => 'F-141',
        'F-141-Beige-M' => 'F-141',
        'F-141-Beige-XL' => 'F-141',
        'F-141-Brown-2XL' => 'F-141',
        'F-141-Brown-L' => 'F-141',
        'F-141-Brown-M' => 'F-141',
        'F-141-Brown-XL' => 'F-141',
        'F-141-Green-2XL' => 'F-141',
        'F-141-Green-L' => 'F-141',
        'F-141-Green-M' => 'F-141',
        'F-141-Green-XL' => 'F-141',
        'F-141-Navy-Blue-2XL' => 'F-141',
        'F-141-Navy-Blue-L' => 'F-141',
        'F-141-Navy-Blue-M' => 'F-141',
        'F-141-Navy-Blue-XL' => 'F-141',
        'F-141-Peach-2XL' => 'F-141',
        'F-141-Peach-L' => 'F-141',
        'F-141-Peach-M' => 'F-141',
        'F-141-Peach-XL' => 'F-141',
        'F-141-Wine-2XL' => 'F-141',
        'F-141-Wine-L' => 'F-141',
        'F-141-Wine-M' => 'F-141',
        'F-141-Wine-XL' => 'F-141',
        'F-144-Black-M' => 'F-144',
        'F-144-Brown-L' => 'F-144',
        'F-144-Brown-M' => 'F-144',
        'F-144-Brown-XL' => 'F-144',
        'F-144-Mustard-Yellow-L' => 'F-144',
        'F-144-Mustard-Yellow-XL' => 'F-144',
        'F-144-Purple-2XL' => 'F-144',
        'F-144-Purple-M' => 'F-144',
        'F-144-Teal-Green-L' => 'F-144',
        'F-144-Teal-Green-XL' => 'F-144',
        'F-144-White-2XL' => 'F-144',
        'F-144-White-L' => 'F-144',
        'F-144-White-M' => 'F-144',
        'F-158-Beige-M' => 'F-158',
        'F-158-Black-L' => 'F-158',
        'F-158-Brown-XL' => 'F-158',
        'F-283-Beige-L' => 'F-283',
        'F-283-Beige-XL' => 'F-283',
        'F-283-Black-L' => 'F-283',
        'F-283-Black-M' => 'F-283',
        'F-283-Dark-Grey-L' => 'F-283',
        'F-511-Beige-3XL' => 'F-511',
        'F-511-Beige-4XL' => 'F-511',
        'F-511-Beige-L' => 'F-511',
        'F-511-Beige-M' => 'F-511',
        'F-511-Beige-S' => 'F-511',
        'F-511-Beige-XL' => 'F-511',
        'F-511-Beige-XXL' => 'F-511',
        'F-511-Black-3XL' => 'F-511',
        'F-511-Black-4XL' => 'F-511',
        'F-511-Black-5XL' => 'F-511',
        'F-511-Black-Beige-M' => 'F-511',
        'F-511-Black-Beige-XL' => 'F-511',
        'F-511-Black-Dark-Green-2XL' => 'F-511',
        'F-511-Black-Dark-Maroon-M' => 'F-511',
        'F-511-Black-Grey-2XL' => 'F-511',
        'F-511-Black-Grey-L' => 'F-511',
        'F-511-Black-Grey-M' => 'F-511',
        'F-511-Black-Grey-XL' => 'F-511',
        'F-511-Black-L' => 'F-511',
        'F-511-Black-M' => 'F-511',
        'F-511-Black-Navy-Blue-2XL' => 'F-511',
        'F-511-Black-Navy-Blue-L' => 'F-511',
        'F-511-Black-Pista-Green-L' => 'F-511',
        'F-511-Black-S' => 'F-511',
        'F-511-Black-XL' => 'F-511',
        'F-511-Black-XXL' => 'F-511',
        'F-511-Brown-L' => 'F-511',
        'F-511-Brown-M' => 'F-511',
        'F-511-Brown-S' => 'F-511',
        'F-511-Brown-XL' => 'F-511',
        'F-511-Brown-XXL' => 'F-511',
        'F-511-Dark-Green-L' => 'F-511',
        'F-511-Dark-Green-M' => 'F-511',
        'F-511-Dark-Green-XL' => 'F-511',
        'F-511-Dark-Green-XXL' => 'F-511',
        'F-511-Dark-Grey-L' => 'F-511',
        'F-511-Dark-Grey-M' => 'F-511',
        'F-511-Dark-Grey-S' => 'F-511',
        'F-511-Dark-Grey-XL' => 'F-511',
        'F-511-Dark-Grey-XXL' => 'F-511',
        'F-511-Dark-Maroon-L' => 'F-511',
        'F-511-Dark-Maroon-M' => 'F-511',
        'F-511-Dark-Maroon-S' => 'F-511',
        'F-511-Dark-Maroon-XL' => 'F-511',
        'F-511-Dark-Maroon-XXL' => 'F-511',
        'F-511-Light-Grey-3XL' => 'F-511',
        'F-511-Light-Grey-4XL' => 'F-511',
        'F-511-Light-Grey-L' => 'F-511',
        'F-511-Light-Grey-M' => 'F-511',
        'F-511-Light-Grey-S' => 'F-511',
        'F-511-Light-Grey-XL' => 'F-511',
        'F-511-Light-Grey-XXL' => 'F-511',
        'F-511-Maroon-L' => 'F-511',
        'F-511-Maroon-Pista-Green--L' => 'F-511',
        'F-511-Maroon-S' => 'F-511',
        'F-511-Maroon-XL' => 'F-511',
        'F-511-Maroon-XXL' => 'F-511',
        'F-511-Navy-Blue-3XL' => 'F-511',
        'F-511-Navy-Blue-4XL' => 'F-511',
        'F-511-Navy-Blue-L' => 'F-511',
        'F-511-Navy-Blue-M' => 'F-511',
        'F-511-Navy-Blue-S' => 'F-511',
        'F-511-Navy-Blue-XL' => 'F-511',
        'F-511-Navy-Blue-XXL' => 'F-511',
        'F-511-Olive-Green-L' => 'F-511',
        'F-511-Olive-Green-M' => 'F-511',
        'F-511-Olive-Green-S' => 'F-511',
        'F-511-Olive-Green-XL' => 'F-511',
        'F-511-Olive-Green-XXL' => 'F-511',
        'F-511-Onion-2XL' => 'F-511',
        'F-511-Onion-L' => 'F-511',
        'F-511-Onion-M' => 'F-511',
        'F-511-Onion-S' => 'F-511',
        'F-511-Onion-XL' => 'F-511',
        'F-511-Orange-2XL' => 'F-511',
        'F-511-Orange-M' => 'F-511',
        'F-511-Orange-S' => 'F-511',
        'F-511-Orange-XL' => 'F-511',
        'F-511-Pink-2XL' => 'F-511',
        'F-511-Pink-L' => 'F-511',
        'F-511-Pink-M' => 'F-511',
        'F-511-Pink-S' => 'F-511',
        'F-511-Pink-XL' => 'F-511',
        'F-511-Pista-Green-3XL' => 'F-511',
        'F-511-Pista-Green-L' => 'F-511',
        'F-511-Pista-Green-M' => 'F-511',
        'F-511-Pista-Green-XL' => 'F-511',
        'F-511-Pista-Green-XXL' => 'F-511',
        'F-511-Red-L' => 'F-511',
        'F-511-Red-M' => 'F-511',
        'F-511-Red-S' => 'F-511',
        'F-511-Red-XL' => 'F-511',
        'F-511-Red-XXL' => 'F-511',
        'F-511-Royal-Blue-L' => 'F-511',
        'F-511-Sky-L' => 'F-511',
        'F-511-Sky-M' => 'F-511',
        'F-511-Sky-S' => 'F-511',
        'F-511-Sky-XL' => 'F-511',
        'F-511-Sky-XXL' => 'F-511',
        'F-511-Teal-Blue-L' => 'F-511',
        'F-511-Teal-Blue-M' => 'F-511',
        'F-511-Teal-Blue-S' => 'F-511',
        'F-511-Teal-Blue-XL' => 'F-511',
        'F-511-Teal-Blue-XXL' => 'F-511',
        'F-511-White-3XL' => 'F-511',
        'F-511-White-4XL' => 'F-511',
        'F-511-White-Beige-2XL' => 'F-511',
        'F-511-White-Beige-L' => 'F-511',
        'F-511-White-Beige-S' => 'F-511',
        'F-511-White-Black-Button-4XL' => 'F-511',
        'F-511-White-Black-Button-L' => 'F-511',
        'F-511-White-Black-Button-M' => 'F-511',
        'F-511-White-Black-Button-S' => 'F-511',
        'F-511-White-Black-Button-XL' => 'F-511',
        'F-511-White-Black-Button-XXL' => 'F-511',
        'F-511-White-Black-L' => 'F-511',
        'F-511-White-Black-M' => 'F-511',
        'F-511-White-Black-S' => 'F-511',
        'F-511-White-Dark-Maroon-2XL' => 'F-511',
        'F-511-White-Dark-Maroon-L' => 'F-511',
        'F-511-White-Dark-Maroon-M' => 'F-511',
        'F-511-White-Grey-XL' => 'F-511',
        'F-511-White-L' => 'F-511',
        'F-511-White-M' => 'F-511',
        'F-511-White-Navy-Blue-2XL' => 'F-511',
        'F-511-White-Pista-Green-M' => 'F-511',
        'F-511-White-Royal-Blue-L' => 'F-511',
        'F-511-White-S' => 'F-511',
        'F-511-White-Sky-L' => 'F-511',
        'F-511-White-Sky-M' => 'F-511',
        'F-511-White-Sky-XL' => 'F-511',
        'F-511-White-XL' => 'F-511',
        'F-511-White-XXL' => 'F-511',
        'F-523-Black-42' => 'F-523',
        'F-523-Denim-Blue-40' => 'F-523',
        'F-523-Denim-Blue-44' => 'F-523',
        'F-523-Light-Brown-42' => 'F-523',
        'F-523-Light-Green-40' => 'F-523',
        'F-523-Light-Green-42' => 'F-523',
        'F-523-NAVY-BLUE-40' => 'F-523',
        'F-523-NAVY-BLUE-42' => 'F-523',
        'F-523-NAVY-BLUE-44' => 'F-523',
        'F-523-Sky-38' => 'F-523',
        'F-523-Sky-42' => 'F-523',
        'F-523-White-38' => 'F-523',
        'F-523-White-40' => 'F-523',
        'F-523-White-42' => 'F-523',
        'F-534-Black-L' => 'F-534',
        'F-534-Black-S' => 'F-534',
        'F-534-Bottle-Green-M' => 'F-534',
        'F-534-Maroon-L' => 'F-534',
        'F-534-Maroon-XXL' => 'F-534',
        'F-534-Olive-Green-XL' => 'F-534',
        'F-534-Olive-Green-XXL' => 'F-534',
        'F-534-Pista-Green-XL' => 'F-534',
        'F-534-Sky-L' => 'F-534',
        'F-534-Sky-S' => 'F-534',
        'F-534-Teal-Blue-2XL' => 'F-534',
        'F-534-White-M' => 'F-534',
        'F-534-White-XL' => 'F-534',
        'F-534-Wine-L' => 'F-534',
        'F-534-Wine-S' => 'F-534',
        'F-543-Blue-Light-Greyish-Green-XL' => 'F-543',
        'F-543-Blue-Peach-XXL' => 'F-543',
        'F-543-Brown-3XL' => 'F-543',
        'F-543-Brown-L' => 'F-543',
        'F-543-Brown-M' => 'F-543',
        'F-543-Brown-S' => 'F-543',
        'F-543-Brown-XL' => 'F-543',
        'F-543-Brown-XXL' => 'F-543',
        'F-543-Cream-3XL' => 'F-543',
        'F-543-Cream-L' => 'F-543',
        'F-543-Cream-M' => 'F-543',
        'F-543-Cream-S' => 'F-543',
        'F-543-Cream-XL' => 'F-543',
        'F-543-Cream-XXL' => 'F-543',
        'F-543-Green-36' => 'F-543',
        'F-543-Green-38' => 'F-543',
        'F-543-Green-3XL' => 'F-543',
        'F-543-Green-40' => 'F-543',
        'F-543-Green-42' => 'F-543',
        'F-543-Green-44' => 'F-543',
        'F-543-Grey-3XL' => 'F-543',
        'F-543-Grey-L' => 'F-543',
        'F-543-Grey-M' => 'F-543',
        'F-543-Grey-XL' => 'F-543',
        'F-543-Grey-XXL' => 'F-543',
        'F-543-Light-Greyish-Green-3XL' => 'F-543',
        'F-543-Light-Greyish-Green-L' => 'F-543',
        'F-543-Light-Greyish-Green-M' => 'F-543',
        'F-543-Light-Greyish-Green-S' => 'F-543',
        'F-543-Light-Greyish-Green-XL' => 'F-543',
        'F-543-Light-Greyish-Green-XXL' => 'F-543',
        'F-543-Light-Pink-36' => 'F-543',
        'F-543-Light-Pink-38' => 'F-543',
        'F-543-Light-Pink-3XL' => 'F-543',
        'F-543-Light-Pink-40' => 'F-543',
        'F-543-Light-Pink-42' => 'F-543',
        'F-543-Light-Pink-44' => 'F-543',
        'F-543-Light-pastel-green-3XL' => 'F-543',
        'F-543-Light-pastel-green-L' => 'F-543',
        'F-543-Light-pastel-green-M' => 'F-543',
        'F-543-Light-pastel-green-S' => 'F-543',
        'F-543-Light-pastel-green-XL' => 'F-543',
        'F-543-Light-pastel-green-XXL' => 'F-543',
        'F-543-Orange-36' => 'F-543',
        'F-543-Orange-38' => 'F-543',
        'F-543-Orange-3XL' => 'F-543',
        'F-543-Orange-40' => 'F-543',
        'F-543-Orange-42' => 'F-543',
        'F-543-Orange-44' => 'F-543',
        'F-543-Peach-38' => 'F-543',
        'F-543-Peach-3XL' => 'F-543',
        'F-543-Peach-40' => 'F-543',
        'F-543-Peach-42' => 'F-543',
        'F-543-Peach-44' => 'F-543',
        'F-543-Pink-36' => 'F-543',
        'F-543-Pink-38' => 'F-543',
        'F-543-Pink-40' => 'F-543',
        'F-543-Pink-42' => 'F-543',
        'F-543-Pink-44' => 'F-543',
        'F-543-Purple-36' => 'F-543',
        'F-543-Purple-38' => 'F-543',
        'F-543-Purple-40' => 'F-543',
        'F-543-Purple-42' => 'F-543',
        'F-543-Purple-44' => 'F-543',
        'F-543-Purple-Cream-XL' => 'F-543',
        'F-543-Royal-Blue-3XL' => 'F-543',
        'F-543-Royal-Blue-L' => 'F-543',
        'F-543-Royal-Blue-M' => 'F-543',
        'F-543-Royal-Blue-XL' => 'F-543',
        'F-543-Royal-Blue-XXL' => 'F-543',
        'F-543-Sky-3XL' => 'F-543',
        'F-543-Sky-Blue-L' => 'F-543',
        'F-543-Sky-Blue-XXL' => 'F-543',
        'F-543-Sky-Cream-L' => 'F-543',
        'F-543-Sky-Cream-M' => 'F-543',
        'F-543-Sky-Grey-XL' => 'F-543',
        'F-543-Sky-Grey-XXL' => 'F-543',
        'F-543-Sky-L' => 'F-543',
        'F-543-Sky-M' => 'F-543',
        'F-543-Sky-S' => 'F-543',
        'F-543-Sky-XL' => 'F-543',
        'F-543-Sky-XXL' => 'F-543',
        'F-543-White-36' => 'F-543',
        'F-543-White-38' => 'F-543',
        'F-543-White-3XL' => 'F-543',
        'F-543-White-40' => 'F-543',
        'F-543-White-42' => 'F-543',
        'F-543-White-44' => 'F-543',
        'F-543-White-Grey-L' => 'F-543',
        'F-543-White-Light-Pastel-Green-XL' => 'F-543',
        'F-543-White-Sky-XXL' => 'F-543',
        'F-543-Yellow-3XL' => 'F-543',
        'F-543-Yellow-L' => 'F-543',
        'F-543-Yellow-M' => 'F-543',
        'F-543-Yellow-XL' => 'F-543',
        'F-543-Yellow-XXL' => 'F-543',
        'FK-206-Light-Blue-XL' => 'FK-206',
        'FK-206-Purple-XL' => 'FK-206',
        'FK-209-Green-XXL' => 'FK-209',
        'FK-211-Maroon-M' => 'FK-211',
        'FP-103- BROWN-M' => 'FP-103',
        'FP-103- BROWN-XXL' => 'FP-103',
        'FP-103-BLACK-4XL' => 'FP-103',
        'FP-103-BLACK-L' => 'FP-103',
        'FP-103-BLACK-M' => 'FP-103',
        'FP-103-BLACK-S' => 'FP-103',
        'FP-103-BLACK-XL' => 'FP-103',
        'FP-103-BLACK-XXL' => 'FP-103',
        'FP-103-BLACK-XXXL' => 'FP-103',
        'FP-103-BROWN-L' => 'FP-103',
        'FP-103-BROWN-S' => 'FP-103',
        'FP-103-BROWN-XL' => 'FP-103',
        'FP-103-Cream-Mehndi-L' => 'FP-103',
        'FP-103-Cream-Mehndi-M' => 'FP-103',
        'FP-103-Cream-Teal-Green-L' => 'FP-103',
        'FP-103-Cream-Teal-Green-XXL' => 'FP-103',
        'FP-103-LIGHT-GREY-L' => 'FP-103',
        'FP-103-LIGHT-GREY-M' => 'FP-103',
        'FP-103-LIGHT-GREY-S' => 'FP-103',
        'FP-103-LIGHT-GREY-XL' => 'FP-103',
        'FP-103-LIGHT-GREY-XXL' => 'FP-103',
        'FP-103-Light-Grey-Maroon-L' => 'FP-103',
        'FP-103-Light-Grey-Maroon-S' => 'FP-103',
        'FP-103-Light-Grey-Maroon-XL' => 'FP-103',
        'FP-103-Light-Grey-Sky-L' => 'FP-103',
        'FP-103-Light-Grey-Sky-M' => 'FP-103',
        'FP-103-Light-Grey-Sky-XL' => 'FP-103',
        'FP-103-Light-Grey-Sky-XXL' => 'FP-103',
        'FP-103-Light-Onion-XXL' => 'FP-103',
        'FP-103-Light-Pink-Black-XL' => 'FP-103',
        'FP-103-Light-Pink-Light-Grey-XL' => 'FP-103',
        'FP-103-Light-Pink-Light-Grey-XXL' => 'FP-103',
        'FP-103-Light-Pink-Sky-Blue-L' => 'FP-103',
        'FP-103-Light-Pink-Sky-Blue-M' => 'FP-103',
        'FP-103-Light-Pink-Sky-Blue-XL' => 'FP-103',
        'FP-103-Light-Pink-White-M' => 'FP-103',
        'FP-103-Light-Pink-Wine-L' => 'FP-103',
        'FP-103-Light-Pink-Wine-XL' => 'FP-103',
        'FP-103-Mehndi-L' => 'FP-103',
        'FP-103-NAVY-BLUE-4XL' => 'FP-103',
        'FP-103-NAVY-BLUE-L' => 'FP-103',
        'FP-103-NAVY-BLUE-M' => 'FP-103',
        'FP-103-NAVY-BLUE-S' => 'FP-103',
        'FP-103-NAVY-BLUE-XL' => 'FP-103',
        'FP-103-NAVY-BLUE-XXL' => 'FP-103',
        'FP-103-Navy-Blue-Black-M' => 'FP-103',
        'FP-103-Navy-Blue-Light-Grey-L' => 'FP-103',
        'FP-103-Navy-Blue-Light-Grey-M' => 'FP-103',
        'FP-103-Navy-Blue-Light-Grey-XL' => 'FP-103',
        'FP-103-Navy-Blue-Light-Grey-XXL' => 'FP-103',
        'FP-103-Navy-Blue-Light-Pink-M' => 'FP-103',
        'FP-103-Navy-Blue-Maroon-L' => 'FP-103',
        'FP-103-Navy-Blue-White-L' => 'FP-103',
        'FP-103-Navy-Blue-Wine-L' => 'FP-103',
        'FP-103-Navy-Blue-Wine-M' => 'FP-103',
        'FP-103-Navy-Blue-XXXL' => 'FP-103',
        'FP-103-Pista-Green-L' => 'FP-103',
        'FP-103-Pista-Green-M' => 'FP-103',
        'FP-103-Pista-Green-S' => 'FP-103',
        'FP-103-Pista-Green-XL' => 'FP-103',
        'FP-103-Pista-Green-XXL' => 'FP-103',
        'FP-103-Pista-Green-XXXL' => 'FP-103',
        'FP-103-Sky-Blue-Black-M' => 'FP-103',
        'FP-103-Sky-Blue-Black-S' => 'FP-103',
        'FP-103-Sky-Blue-Black-XL' => 'FP-103',
        'FP-103-Sky-Blue-Light-Grey-S' => 'FP-103',
        'FP-103-Sky-Blue-Light-Grey-XL' => 'FP-103',
        'FP-103-Sky-Blue-Maroon-XL' => 'FP-103',
        'FP-103-Sky-Blue-Navy-Blue-L' => 'FP-103',
        'FP-103-Sky-Blue-Wine-S' => 'FP-103',
        'FP-103-Sky-Blue-Wine-XL' => 'FP-103',
        'FP-103-Sky-Blue-Wine-XXL' => 'FP-103',
        'FP-103-Sky-L' => 'FP-103',
        'FP-103-Sky-M' => 'FP-103',
        'FP-103-Sky-S' => 'FP-103',
        'FP-103-Sky-XL' => 'FP-103',
        'FP-103-Sky-XXL' => 'FP-103',
        'FP-103-Sky-XXXL' => 'FP-103',
        'FP-103-TEAL-GREEN-L' => 'FP-103',
        'FP-103-TEAL-GREEN-M' => 'FP-103',
        'FP-103-TEAL-GREEN-S' => 'FP-103',
        'FP-103-TEAL-GREEN-XL' => 'FP-103',
        'FP-103-TEAL-GREEN-XXL' => 'FP-103',
        'FP-103-Teal-Green-XXXL' => 'FP-103',
        'FP-103-White-3XL' => 'FP-103',
        'FP-103-White-4XL' => 'FP-103',
        'FP-103-White-Black-L' => 'FP-103',
        'FP-103-White-Black-M' => 'FP-103',
        'FP-103-White-Black-S' => 'FP-103',
        'FP-103-White-Black-XXL' => 'FP-103',
        'FP-103-White-L' => 'FP-103',
        'FP-103-White-Light-Grey-M' => 'FP-103',
        'FP-103-White-Light-Grey-S' => 'FP-103',
        'FP-103-White-Light-Grey-XL' => 'FP-103',
        'FP-103-White-M' => 'FP-103',
        'FP-103-White-Maroon-L' => 'FP-103',
        'FP-103-White-Maroon-M' => 'FP-103',
        'FP-103-White-Maroon-XXL' => 'FP-103',
        'FP-103-White-S' => 'FP-103',
        'FP-103-White-Sky-Blue-M' => 'FP-103',
        'FP-103-White-Sky-Blue-S' => 'FP-103',
        'FP-103-White-Sky-Blue-XXL' => 'FP-103',
        'FP-103-White-Wine-M' => 'FP-103',
        'FP-103-White-XL' => 'FP-103',
        'FP-103-White-XXL' => 'FP-103',
        'FP-103-Wine-Black-M' => 'FP-103',
        'FP-103-Wine-Light-Grey-L' => 'FP-103',
        'FP-103-Wine-Light-Grey-M' => 'FP-103',
        'FP-103-Wine-Light-Grey-S' => 'FP-103',
        'FP-103-Wine-Onion-L' => 'FP-103',
        'FP-103-Wine-Onion-M' => 'FP-103',
        'FP-103-Wine-Onion-XL' => 'FP-103',
        'FP-103-Yellow-Light-Grey-M' => 'FP-103',
        'FP-103-Yellow-Pista-Green-L' => 'FP-103',
        'FP-124-Black-L' => 'FP-124',
        'FP-124-Black-M' => 'FP-124',
        'FP-124-Black-XL' => 'FP-124',
        'FP-124-Black-XXL' => 'FP-124',
        'FP-124-Grey-3XL' => 'FP-124',
        'FP-124-Grey-XL' => 'FP-124',
        'FP-124-Grey-XXL' => 'FP-124',
        'FP-124-LIGHT-GREEN-M' => 'FP-124',
        'FP-124-LIGHT-GREEN-XL' => 'FP-124',
        'FP-124-Sky-Blue-XXL' => 'FP-124',
        'FP-124-Yellow-L' => 'FP-124',
        'FP-124-Yellow-M' => 'FP-124',
        'FP-124-Yellow-XL' => 'FP-124',
        'FP-125-Amrican-Brown-M' => 'FP-125',
        'FP-125-Amrican-Brown-XL' => 'FP-125',
        'FP-125-Black-2XL' => 'FP-125',
        'FP-125-Black-L' => 'FP-125',
        'FP-125-Black-M' => 'FP-125',
        'FP-125-Black-XL' => 'FP-125',
        'FP-125-Brown-3XL' => 'FP-125',
        'FP-125-Brown-L' => 'FP-125',
        'FP-125-Brown-M' => 'FP-125',
        'FP-125-Brown-XL' => 'FP-125',
        'FP-125-Check-Sky-Blue-3XL' => 'FP-125',
        'FP-125-Check-Sky-Blue-L' => 'FP-125',
        'FP-125-Check-Sky-Blue-M' => 'FP-125',
        'FP-125-Check-Sky-Blue-S' => 'FP-125',
        'FP-125-Check-Sky-Blue-XL' => 'FP-125',
        'FP-125-Check-Sky-Blue-XXL' => 'FP-125',
        'FP-125-Cream-2XL' => 'FP-125',
        'FP-125-Cream-L' => 'FP-125',
        'FP-125-Cream-M' => 'FP-125',
        'FP-125-Cream-XL' => 'FP-125',
        'FP-125-Dark-Green-4XL' => 'FP-125',
        'FP-125-Dark-Green-L' => 'FP-125',
        'FP-125-Dark-Green-M' => 'FP-125',
        'FP-125-Dark-Green-S' => 'FP-125',
        'FP-125-Dark-Green-XL' => 'FP-125',
        'FP-125-Dark-Green-XXL' => 'FP-125',
        'FP-125-Dark-Maroon-2XL' => 'FP-125',
        'FP-125-Dark-Maroon-L' => 'FP-125',
        'FP-125-Dark-Maroon-M' => 'FP-125',
        'FP-125-Dark-Maroon-XL' => 'FP-125',
        'FP-125-Green-L' => 'FP-125',
        'FP-125-Green-M' => 'FP-125',
        'FP-125-Green-XL' => 'FP-125',
        'FP-125-Grey-L' => 'FP-125',
        'FP-125-Grey-XL' => 'FP-125',
        'FP-125-Light-Green-3XL' => 'FP-125',
        'FP-125-Light-Green-L' => 'FP-125',
        'FP-125-Light-Green-M' => 'FP-125',
        'FP-125-Light-Green-S' => 'FP-125',
        'FP-125-Light-Green-XL' => 'FP-125',
        'FP-125-Light-Green-XXL' => 'FP-125',
        'FP-125-Light-Grey-2XL' => 'FP-125',
        'FP-125-Light-Grey-L' => 'FP-125',
        'FP-125-Light-Grey-M' => 'FP-125',
        'FP-125-Light-Grey-XL' => 'FP-125',
        'FP-125-Light-Pink-2XL' => 'FP-125',
        'FP-125-Light-Pink-XL' => 'FP-125',
        'FP-125-Navy-Blue-2XL' => 'FP-125',
        'FP-125-Navy-Blue-L' => 'FP-125',
        'FP-125-Navy-Blue-M' => 'FP-125',
        'FP-125-Navy-Blue-XL' => 'FP-125',
        'FP-125-Olive-Green-2XL' => 'FP-125',
        'FP-125-Olive-Green-L' => 'FP-125',
        'FP-125-Olive-Green-M' => 'FP-125',
        'FP-125-Olive-Green-XL' => 'FP-125',
        'FP-125-Pink-L' => 'FP-125',
        'FP-125-Pink-M' => 'FP-125',
        'FP-125-Pink-S' => 'FP-125',
        'FP-125-Pink-XL' => 'FP-125',
        'FP-125-Pink-XXL' => 'FP-125',
        'FP-125-Pista-Green-2XL' => 'FP-125',
        'FP-125-Pista-Green-L' => 'FP-125',
        'FP-125-Pista-Green-M' => 'FP-125',
        'FP-125-Pista-Green-XL' => 'FP-125',
        'FP-125-Royal-Blue-2XL' => 'FP-125',
        'FP-125-Royal-Blue-L' => 'FP-125',
        'FP-125-Royal-Blue-M' => 'FP-125',
        'FP-125-Royal-Blue-XL' => 'FP-125',
        'FP-125-Sky-2XL' => 'FP-125',
        'FP-125-Sky-L' => 'FP-125',
        'FP-125-Sky-M' => 'FP-125',
        'FP-125-Sky-XL' => 'FP-125',
        'FP-126-Beige-L' => 'FP-126',
        'FP-126-Beige-XL' => 'FP-126',
        'FP-126-Beige-XXL' => 'FP-126',
        'FP-126-Black-XXL' => 'FP-126',
        'FP-126-Dark-Green-XXL' => 'FP-126',
        'FP-126-Grey-XL' => 'FP-126',
        'FP-126-Light-Blue-XL' => 'FP-126',
        'FP-126-Mud-Grey-L' => 'FP-126',
        'FP-126-Mud-Grey-XL' => 'FP-126',
        'FP-126-Mud-Grey-XXL' => 'FP-126',
        'FP-126-White-L' => 'FP-126',
        'FP-126-White-XL' => 'FP-126',
        'FP-129-Black-3XL' => 'FP-129',
        'FP-129-Black-5XL' => 'FP-129',
        'FP-129-GRAYISH-GREEN-3XL' => 'FP-129',
        'FP-129-GRAYISH-GREEN-4XL' => 'FP-129',
        'FP-129-GRAYISH-GREEN-5XL' => 'FP-129',
        'FP-129-NAVY-BLUE-5XL' => 'FP-129',
        'FP-129-PINK-5XL' => 'FP-129',
        'FP-129-Pista-Green-5XL' => 'FP-129',
        'FP-129-SKY-BLUE-3XL' => 'FP-129',
        'FP-129-SKY-BLUE-4XL' => 'FP-129',
        'FP-129-SKY-BLUE-5XL' => 'FP-129',
        'FP-129-White-4XL' => 'FP-129',
        'FP-129-White-5XL' => 'FP-129',
        'FP-130-1-Blue-XL' => 'FP-130',
        'FP-130-1-Grey-XXL' => 'FP-130',
        'FP-130-2-Blue-L' => 'FP-130',
        'FP-130-2-White-XXL' => 'FP-130',
        'FP-130-3-Green-L' => 'FP-130',
        'FP-130-3-Green-XL' => 'FP-130',
        'FP-130-3-White-M' => 'FP-130',
        'FP-130-4-Sky-XL' => 'FP-130',
        'FP-130-4-Sky-XXL' => 'FP-130',
        'FP-130-5-Grey-L' => 'FP-130',
        'FP-130-5-Yellow-L' => 'FP-130',
        'FP-130-6-Grey-M' => 'FP-130',
        'FP-130-6-White-L' => 'FP-130',
        'FP-130-6-White-XL' => 'FP-130',
        'FP-130-6-White-XXL' => 'FP-130',
        'FP-132-Peach-Pink-XL' => 'FP-132',
        'FP-132-Sea-Green-XL' => 'FP-132',
        'FP-132-Sky-M' => 'FP-132',
        'FP-137-Black-L' => 'FP-137',
        'FP-137-Black-M' => 'FP-137',
        'FP-137-Black-XL' => 'FP-137',
        'FP-137-Black-XXL' => 'FP-137',
        'FP-137-Brown-2XL' => 'FP-137',
        'FP-137-Brown-L' => 'FP-137',
        'FP-137-Brown-M' => 'FP-137',
        'FP-137-Brown-XL' => 'FP-137',
        'FP-137-Cream-L' => 'FP-137',
        'FP-137-Cream-M' => 'FP-137',
        'FP-137-Cream-XL' => 'FP-137',
        'FP-137-Cream-XXL' => 'FP-137',
        'FP-137-Dark-Green-L' => 'FP-137',
        'FP-137-Dark-Green-M' => 'FP-137',
        'FP-137-Dark-Green-XL' => 'FP-137',
        'FP-137-Dark-Green-XXL' => 'FP-137',
        'FP-137-Grey-2XL' => 'FP-137',
        'FP-137-Grey-L' => 'FP-137',
        'FP-137-Grey-M' => 'FP-137',
        'FP-137-Grey-XL' => 'FP-137',
        'FP-137-Mustard-L' => 'FP-137',
        'FP-137-Mustard-M' => 'FP-137',
        'FP-137-Mustard-XL' => 'FP-137',
        'FP-137-Mustard-XXL' => 'FP-137',
        'FP-137-Navy-Blue-L' => 'FP-137',
        'FP-137-Navy-Blue-M' => 'FP-137',
        'FP-137-Navy-Blue-XL' => 'FP-137',
        'FP-137-Navy-Blue-XXL' => 'FP-137',
        'FP-137-Olive-Green-L' => 'FP-137',
        'FP-137-Olive-Green-XL' => 'FP-137',
        'FP-137-Olive-Green-XXL' => 'FP-137',
        'FP-137-Red-L' => 'FP-137',
        'FP-137-Red-M' => 'FP-137',
        'FP-137-Red-XL' => 'FP-137',
        'FP-137-Red-XXL' => 'FP-137',
        'FP-137-Wine-M' => 'FP-137',
        'FP-137-Wine-XL' => 'FP-137',
        'FP-137-Wine-XXL' => 'FP-137',
        'FP-139-Dark-Maroon-M' => 'FP-139',
        'FP-139-Denim-Blue-M' => 'FP-139',
        'FP-139-Denim-Blue-XL' => 'FP-139',
        'FP-139-Light-Onion-2XL' => 'FP-139',
        'FP-139-Light-Sky-XXL' => 'FP-139',
        'FP-147-Beige-M' => 'FP-147',
        'FP-147-Beige-XL' => 'FP-147',
        'FP-147-Black-2XL' => 'FP-147',
        'FP-147-Black-L' => 'FP-147',
        'FP-147-Black-M' => 'FP-147',
        'FP-147-Black-XL' => 'FP-147',
        'FP-147-Maroon-M' => 'FP-147',
        'FP-147-Maroon-XL' => 'FP-147',
        'FP-147-Mehndi-Green-M' => 'FP-147',
        'FP-147-Onion-L' => 'FP-147',
        'FP-147-Onion-M' => 'FP-147',
        'FP-147-Sky-2XL' => 'FP-147',
        'FP-147-Sky-L' => 'FP-147',
        'FP-147-Teal-Blue-2XL' => 'FP-147',
        'FP-147-Teal-Blue-L' => 'FP-147',
        'FP-147-Teal-Blue-M' => 'FP-147',
        'FP-147-White-2XL' => 'FP-147',
        'FP-147-White-M' => 'FP-147',
        'FP-155-Light-Green-2XL' => 'FP-155',
        'FP-155-Light-Green-L' => 'FP-155',
        'FP-155-Light-Green-M' => 'FP-155',
        'FP-155-Light-Green-XL' => 'FP-155',
        'FP-155-Light-Grey-2XL' => 'FP-155',
        'FP-155-Light-Grey-L' => 'FP-155',
        'FP-155-Light-Grey-M' => 'FP-155',
        'FP-155-Light-Grey-XL' => 'FP-155',
        'FP-155-Light-Olive-2XL' => 'FP-155',
        'FP-155-Light-Olive-L' => 'FP-155',
        'FP-155-Light-Olive-M' => 'FP-155',
        'FP-155-Light-Olive-XL' => 'FP-155',
        'FP-155-Maroon-2XL' => 'FP-155',
        'FP-155-Maroon-L' => 'FP-155',
        'FP-155-Maroon-M' => 'FP-155',
        'FP-155-Maroon-XL' => 'FP-155',
        'FP-155-Medium-Grey-2XL' => 'FP-155',
        'FP-155-Medium-Grey-L' => 'FP-155',
        'FP-155-Medium-Grey-M' => 'FP-155',
        'FP-155-Medium-Grey-XL' => 'FP-155',
        'FP-155-Peach-2XL' => 'FP-155',
        'FP-155-Peach-L' => 'FP-155',
        'FP-155-Peach-M' => 'FP-155',
        'FP-155-Peach-XL' => 'FP-155',
        'FP-156-Black-2XL' => 'FP-156',
        'FP-156-Black-L' => 'FP-156',
        'FP-156-Black-M' => 'FP-156',
        'FP-156-Black-XL' => 'FP-156',
        'FP-156-Brown-2XL' => 'FP-156',
        'FP-156-Brown-L' => 'FP-156',
        'FP-156-Brown-M' => 'FP-156',
        'FP-156-Brown-XL' => 'FP-156',
        'FP-156-Cream-2XL' => 'FP-156',
        'FP-156-Cream-L' => 'FP-156',
        'FP-156-Cream-M' => 'FP-156',
        'FP-156-Cream-XL' => 'FP-156',
        'FP-156-Dark-Grey-2XL' => 'FP-156',
        'FP-156-Dark-Grey-L' => 'FP-156',
        'FP-156-Dark-Grey-M' => 'FP-156',
        'FP-156-Dark-Grey-XL' => 'FP-156',
        'FP-156-Light-Brown-2XL' => 'FP-156',
        'FP-156-Light-Brown-L' => 'FP-156',
        'FP-156-Light-Brown-M' => 'FP-156',
        'FP-156-Light-Brown-XL' => 'FP-156',
        'FP-156-Light-Grey-2XL' => 'FP-156',
        'FP-156-Light-Grey-L' => 'FP-156',
        'FP-156-Light-Grey-M' => 'FP-156',
        'FP-156-Light-Grey-XL' => 'FP-156',
        'FP-156-Light-Greyish-Green-2XL' => 'FP-156',
        'FP-156-Light-Greyish-Green-L' => 'FP-156',
        'FP-156-Light-Greyish-Green-M' => 'FP-156',
        'FP-156-Light-Greyish-Green-XL' => 'FP-156',
        'FP-156-Light-Onion-2XL' => 'FP-156',
        'FP-156-Light-Onion-L' => 'FP-156',
        'FP-156-Light-Onion-M' => 'FP-156',
        'FP-156-Light-Onion-XL' => 'FP-156',
        'FP-156-Olive-2XL' => 'FP-156',
        'FP-156-Olive-Green-2XL' => 'FP-156',
        'FP-156-Olive-Green-L' => 'FP-156',
        'FP-156-Olive-Green-M' => 'FP-156',
        'FP-156-Olive-Green-XL' => 'FP-156',
        'FP-156-Olive-L' => 'FP-156',
        'FP-156-Olive-M' => 'FP-156',
        'FP-156-Olive-XL' => 'FP-156',
        'FP-156-Peach-2XL' => 'FP-156',
        'FP-156-Peach-L' => 'FP-156',
        'FP-156-Peach-M' => 'FP-156',
        'FP-156-Peach-XL' => 'FP-156',
        'FP-156-Teal-Blue-2XL' => 'FP-156',
        'FP-156-Teal-Blue-L' => 'FP-156',
        'FP-156-Teal-Blue-M' => 'FP-156',
        'FP-156-Teal-Blue-XL' => 'FP-156',
        'FP-156-White-2XL' => 'FP-156',
        'FP-156-White-L' => 'FP-156',
        'FP-156-White-M' => 'FP-156',
        'FP-156-White-XL' => 'FP-156',
        'FP-156-Wine-2XL' => 'FP-156',
        'FP-156-Wine-L' => 'FP-156',
        'FP-156-Wine-M' => 'FP-156',
        'FP-156-Wine-S' => 'FP-156',
        'FP-156-Wine-XL' => 'FP-156',
        'FP-252-Maroon-L' => 'FP-252',
        'FP-252-Onion-M' => 'FP-252',
        'FP-252-wine-L' => 'FP-252',
        'FP-267-Blue-L' => 'FP-267',
        'FP-267-Blue-M' => 'FP-267',
        'FP-267-Green-L' => 'FP-267',
        'FP-268-Navy-XL' => 'FP-268',
        'FP-270-Beige-M' => 'FP-270',
        'FP-271-Beige-M' => 'FP-271',
        'FP-271-Green-XL' => 'FP-271',
        'FP-272-Blue-L' => 'FP-272',
        'FP-272-Blue-M' => 'FP-272',
        'FP-272-Blue-XL' => 'FP-272',
        'FP-272-Blue-XXL' => 'FP-272',
        'FP-272-Brown-L' => 'FP-272',
        'FP-273-Blue-M' => 'FP-273',
        'FP-274-Blue-XXL' => 'FP-274',
        'FP-275-Green-M' => 'FP-275',
        'FP-283-Dark-Green-L' => 'FP-283',
        'FP-283-Dark-Maroon-XXL' => 'FP-283',
        'FP-283-Olive-Green-L' => 'FP-283',
        'FP-283-Olive-Green-M' => 'FP-283',
        'FP-283-Olive-Green-XXL' => 'FP-283',
        'FW-149-Black-M' => 'FW-149',
        'FW-149-Black-XL' => 'FW-149',
        'FW-149-Brown-L' => 'FW-149',
        'HP-531-Light-Grey-M' => 'HP-531',
        'HP-531-Light-Grey-XL' => 'HP-531',
        'HP-531-Orange-M' => 'HP-531',
        'HP-531-Pista-Green-XL' => 'HP-531',
        'HP-531-Pista-Green-XXL' => 'HP-531',
        'HP-531-White-XL' => 'HP-531',
        'HPF-140-Cream-L' => 'HPF-140',
        'HPF-140-Dark-Grey-XL' => 'HPF-140',
        'HPF-140-White-L' => 'HPF-140',
        'HPF-140-White-XL' => 'HPF-140',
        'MY-251-Printed-Dark-Green-M' => 'MY-251',
        'MY-251-Printed-Navy-Blue-L' => 'MY-251',
    ];
    echo json_encode(['status'=>'success','data'=>$skuArticleMap]);
    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]);
}