<?php
/* =====================================================
   File: /home/jewellersbd/public_html/erp.jewellersbd.com/api/barcode/get_barcode_list.php
   Purpose: Barcode Stock list API
   Safe mode:
   - READ ONLY API: no ALTER / INSERT / UPDATE / DELETE
   - hide deleted/cancelled items
   - supports all / in_stock / sold filters
   - shows sold invoice/date/price from barcode_sale_items + sales/barcode_sales if available
   - supports showroom / locker / location / karat / source / search filters
   ===================================================== */

ini_set('display_errors', 0);
error_reporting(E_ALL);
header('Content-Type: application/json; charset=utf-8');

require_once __DIR__ . '/../../config/session.php';
require_once __DIR__ . '/../../config/db.php';

function out_json(array $arr, int $code = 200): void
{
    http_response_code($code);
    echo json_encode($arr, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
    exit;
}

if (function_exists('is_logged_in') && !is_logged_in()) {
    out_json(['success' => false, 'status' => false, 'message' => 'লগইন প্রয়োজন'], 401);
}

if (!isset($pdo) || !($pdo instanceof PDO)) {
    out_json(['success' => false, 'status' => false, 'message' => 'PDO DB connection পাওয়া যায়নি'], 500);
}

try {
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->exec("SET NAMES utf8mb4");
} catch (Throwable $e) {
    out_json(['success' => false, 'status' => false, 'message' => 'ডাটাবেজ কানেকশন সমস্যা'], 500);
}

function clean_name($name): string
{
    return preg_replace('/[^a-zA-Z0-9_]/', '', (string)$name);
}

function qi($name): string
{
    return '`' . str_replace('`', '', clean_name($name)) . '`';
}

function table_exists(PDO $pdo, $table): bool
{
    $table = clean_name($table);
    if ($table === '') return false;
    $st = $pdo->query('SHOW TABLES LIKE ' . $pdo->quote($table));
    return (bool)$st->fetchColumn();
}

function get_columns(PDO $pdo, $table): array
{
    $table = clean_name($table);
    if ($table === '' || !table_exists($pdo, $table)) return [];

    $rows = $pdo->query('SHOW COLUMNS FROM ' . qi($table))->fetchAll(PDO::FETCH_ASSOC);
    $cols = [];
    foreach ($rows as $r) {
        if (isset($r['Field'])) $cols[$r['Field']] = true;
    }
    return $cols;
}

function has_col(array $cols, $name): bool
{
    return isset($cols[$name]);
}

function pick_col(array $cols, array $names): ?string
{
    foreach ($names as $n) {
        if (isset($cols[$n])) return $n;
    }
    return null;
}

function first_value(array $row, array $keys, $default = '')
{
    foreach ($keys as $k) {
        if (array_key_exists($k, $row) && $row[$k] !== null && $row[$k] !== '') {
            return $row[$k];
        }
    }
    return $default;
}

function first_float(array $row, array $keys, $default = 0): float
{
    foreach ($keys as $k) {
        if (array_key_exists($k, $row) && $row[$k] !== null && $row[$k] !== '') {
            return (float)$row[$k];
        }
    }
    return (float)$default;
}

function first_int(array $row, array $keys, $default = 0): int
{
    foreach ($keys as $k) {
        if (array_key_exists($k, $row) && $row[$k] !== null && $row[$k] !== '') {
            return (int)$row[$k];
        }
    }
    return (int)$default;
}

function gram_from_parts($vori, $ana, $roti, $point): float
{
    $VORI = 11.664;
    return round(
        ((float)$vori * $VORI) +
        ((float)$ana * ($VORI / 16)) +
        ((float)$roti * ($VORI / 96)) +
        ((float)$point * ($VORI / 960)),
        3
    );
}

function gram_to_parts($gram): array
{
    $VORI = 11.664;
    $totalPoint = (int)round(((float)$gram / $VORI) * 960);

    $vori = intdiv($totalPoint, 960);
    $totalPoint %= 960;

    $ana = intdiv($totalPoint, 60);
    $totalPoint %= 60;

    $roti = intdiv($totalPoint, 10);
    $point = $totalPoint % 10;

    return ['vori' => $vori, 'ana' => $ana, 'roti' => $roti, 'point' => $point];
}

function normalize_status($status, int $saleId = 0, string $soldAt = '', string $invoiceNo = ''): string
{
    $s = mb_strtolower(trim((string)$status), 'UTF-8');

    if (in_array($s, ['sold', 'sell', 'sold_out', 'sale_done', 'বিক্রি', 'বিক্রিত', 'বিক্রি হয়েছে', 'বিক্রি হয়েছে'], true)) {
        return 'sold';
    }

    if (in_array($s, ['deleted', 'cancelled', 'canceled', 'void', 'trash', 'trashed', 'বাতিল'], true)) {
        return 'deleted';
    }

    if ($saleId > 0 || $soldAt !== '' || $invoiceNo !== '') {
        return 'sold';
    }

    return 'in_stock';
}

function is_old_gold_source(string $sourceText): bool
{
    $s = mb_strtolower($sourceText, 'UTF-8');
    return (
        strpos($s, 'old') !== false ||
        strpos($s, 'old_gold') !== false ||
        strpos($s, 'puraton') !== false ||
        strpos($s, 'purchase_old') !== false ||
        strpos($s, 'scrap') !== false ||
        mb_strpos($s, 'পুরাতন') !== false ||
        mb_strpos($s, 'পুরানো') !== false ||
        mb_strpos($s, 'ভাঙা') !== false
    );
}

function load_locations(PDO $pdo): array
{
    $fallback = [
        1 => 'Factory',
        2 => 'Showroom-1',
        3 => 'Showroom-2',
        4 => 'Locker',
    ];

    if (!table_exists($pdo, 'stock_locations')) return $fallback;

    $cols = get_columns($pdo, 'stock_locations');
    $nameBnCol = pick_col($cols, ['name_bn', 'location_name_bn', 'title_bn']);
    $nameCol   = pick_col($cols, ['name', 'location_name', 'title']);

    if (!$nameBnCol && !$nameCol) return $fallback;

    $selectName = [];
    if ($nameBnCol) $selectName[] = qi($nameBnCol);
    if ($nameCol) $selectName[] = qi($nameCol);

    $sql = 'SELECT `id`, ' . implode(', ', $selectName) . ' FROM `stock_locations` ORDER BY `id` ASC';
    $rows = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);

    foreach ($rows as $r) {
        $id = (int)($r['id'] ?? 0);
        if ($id <= 0) continue;

        $name = '';
        if ($nameBnCol && !empty($r[$nameBnCol])) $name = (string)$r[$nameBnCol];
        if ($name === '' && $nameCol && !empty($r[$nameCol])) $name = (string)$r[$nameCol];

        if ($name !== '') $fallback[$id] = $name;
    }

    return $fallback;
}

function sql_in_placeholders(int $count): string
{
    return implode(',', array_fill(0, max(0, $count), '?'));
}

function coalesce_expr(string $alias, array $cols, array $names, string $fallback): string
{
    $parts = [];
    foreach ($names as $n) {
        if (isset($cols[$n])) $parts[] = $alias . '.' . qi($n);
    }
    $parts[] = $fallback;
    return 'COALESCE(' . implode(', ', $parts) . ')';
}

function load_sale_info(PDO $pdo, array $stockRows, string $idCol): array
{
    $empty = [
        'sale_id' => 0,
        'invoice_no' => '',
        'sold_at' => '',
        'sold_price' => 0.0,
        'sale_rate_per_vori' => 0.0
    ];

    $result = ['by_id' => [], 'by_barcode' => []];

    if (empty($stockRows) || !table_exists($pdo, 'barcode_sale_items')) {
        return $result;
    }

    $itemCols = get_columns($pdo, 'barcode_sale_items');
    if (empty($itemCols)) return $result;

    $ids = [];
    $barcodes = [];

    foreach ($stockRows as $r) {
        $id = isset($r[$idCol]) ? (int)$r[$idCol] : 0;
        if ($id > 0) $ids[$id] = $id;

        foreach (['barcode_no', 'barcode', 'barcode_code'] as $bc) {
            if (isset($r[$bc]) && trim((string)$r[$bc]) !== '') {
                $barcodes[trim((string)$r[$bc])] = trim((string)$r[$bc]);
                break;
            }
        }
    }

    $whereParts = [];
    $params = [];

    if (isset($itemCols['barcode_stock_id']) && !empty($ids)) {
        $whereParts[] = 'bsi.`barcode_stock_id` IN (' . sql_in_placeholders(count($ids)) . ')';
        foreach ($ids as $v) $params[] = $v;
    }

    if (isset($itemCols['barcode']) && !empty($barcodes)) {
        $whereParts[] = 'bsi.`barcode` IN (' . sql_in_placeholders(count($barcodes)) . ')';
        foreach ($barcodes as $v) $params[] = $v;
    }

    if (isset($itemCols['barcode_no']) && !empty($barcodes)) {
        $whereParts[] = 'bsi.`barcode_no` IN (' . sql_in_placeholders(count($barcodes)) . ')';
        foreach ($barcodes as $v) $params[] = $v;
    }

    if (empty($whereParts)) return $result;

    $select = [];
    $select[] = isset($itemCols['id']) ? 'bsi.`id` AS bsi_id' : '0 AS bsi_id';
    $select[] = isset($itemCols['barcode_stock_id']) ? 'bsi.`barcode_stock_id` AS barcode_stock_id' : '0 AS barcode_stock_id';
    $select[] = isset($itemCols['barcode']) ? 'bsi.`barcode` AS barcode' : "'' AS barcode";
    $select[] = isset($itemCols['barcode_no']) ? 'bsi.`barcode_no` AS barcode_no' : "'' AS barcode_no";
    $select[] = isset($itemCols['sale_id']) ? 'bsi.`sale_id` AS sale_id' : '0 AS sale_id';

    $select[] = coalesce_expr('bsi', $itemCols, ['item_price', 'total_price', 'line_total', 'sale_price', 'selling_price', 'amount', 'price'], '0') . ' AS sold_price';
    $select[] = coalesce_expr('bsi', $itemCols, ['sale_rate_per_vori', 'rate_per_vori', 'gold_rate', 'rate'], '0') . ' AS sale_rate_per_vori';
    $select[] = coalesce_expr('bsi', $itemCols, ['sold_at', 'sale_date', 'created_at'], "''") . ' AS item_sold_at';

    $joins = [];
    $invoiceExprParts = [];
    $dateExprParts = [];

    if (isset($itemCols['sale_id']) && table_exists($pdo, 'sales')) {
        $salesCols = get_columns($pdo, 'sales');
        if (isset($salesCols['id'])) {
            $joins[] = 'LEFT JOIN `sales` s ON s.`id` = bsi.`sale_id`';

            foreach (['invoice_no', 'invoice', 'sale_invoice_no'] as $c) {
                if (isset($salesCols[$c])) $invoiceExprParts[] = 's.' . qi($c);
            }

            foreach (['sale_date', 'sold_at', 'created_at'] as $c) {
                if (isset($salesCols[$c])) $dateExprParts[] = 's.' . qi($c);
            }
        }
    }

    if (isset($itemCols['sale_id']) && table_exists($pdo, 'barcode_sales')) {
        $bsCols = get_columns($pdo, 'barcode_sales');
        if (isset($bsCols['id'])) {
            $joins[] = 'LEFT JOIN `barcode_sales` bsl ON bsl.`id` = bsi.`sale_id`';

            foreach (['invoice_no', 'invoice', 'sale_invoice_no'] as $c) {
                if (isset($bsCols[$c])) $invoiceExprParts[] = 'bsl.' . qi($c);
            }

            foreach (['sale_date', 'sold_at', 'created_at'] as $c) {
                if (isset($bsCols[$c])) $dateExprParts[] = 'bsl.' . qi($c);
            }
        }
    }

    foreach (['invoice_no', 'invoice', 'sale_invoice_no'] as $c) {
        if (isset($itemCols[$c])) $invoiceExprParts[] = 'bsi.' . qi($c);
    }

    foreach (['sold_at', 'sale_date', 'created_at'] as $c) {
        if (isset($itemCols[$c])) $dateExprParts[] = 'bsi.' . qi($c);
    }

    $invoiceExprParts[] = "''";
    $dateExprParts[] = "''";

    $select[] = 'COALESCE(' . implode(', ', $invoiceExprParts) . ') AS invoice_no';
    $select[] = 'COALESCE(' . implode(', ', $dateExprParts) . ') AS sold_at';

    $order = isset($itemCols['id']) ? 'bsi.`id` DESC' : (isset($itemCols['created_at']) ? 'bsi.`created_at` DESC' : '1 DESC');

    $sql = '
        SELECT ' . implode(', ', $select) . '
        FROM `barcode_sale_items` bsi
        ' . implode("\n", $joins) . '
        WHERE (' . implode(' OR ', $whereParts) . ')
        ORDER BY ' . $order . '
        LIMIT 5000
    ';

    $st = $pdo->prepare($sql);
    $st->execute($params);
    $rows = $st->fetchAll(PDO::FETCH_ASSOC);

    foreach ($rows as $r) {
        $info = $empty;
        $info['sale_id'] = (int)($r['sale_id'] ?? 0);
        $info['invoice_no'] = trim((string)($r['invoice_no'] ?? ''));
        $info['sold_at'] = trim((string)($r['sold_at'] ?? $r['item_sold_at'] ?? ''));
        $info['sold_price'] = (float)($r['sold_price'] ?? 0);
        $info['sale_rate_per_vori'] = (float)($r['sale_rate_per_vori'] ?? 0);

        $sid = (int)($r['barcode_stock_id'] ?? 0);
        $bc = trim((string)($r['barcode'] ?? ''));
        if ($bc === '') $bc = trim((string)($r['barcode_no'] ?? ''));

        if ($sid > 0 && !isset($result['by_id'][$sid])) $result['by_id'][$sid] = $info;
        if ($bc !== '' && !isset($result['by_barcode'][$bc])) $result['by_barcode'][$bc] = $info;
    }

    return $result;
}

function add_summary_bucket(array &$bucket, string $key, float $weight, int $qty = 1): void
{
    if ($key === '') $key = 'Unknown';

    if (!isset($bucket[$key])) {
        $bucket[$key] = ['items' => 0, 'weight' => 0.0];
    }

    $bucket[$key]['items'] += $qty;
    $bucket[$key]['weight'] = round($bucket[$key]['weight'] + $weight, 3);
}

try {
    if (!table_exists($pdo, 'barcode_stock')) {
        out_json([
            'success' => true,
            'status' => true,
            'message' => 'barcode_stock table পাওয়া যায়নি',
            'data' => ['items' => [], 'barcodes' => [], 'summary' => []],
            'items' => [],
            'barcodes' => [],
            'summary' => []
        ]);
    }

    $cols = get_columns($pdo, 'barcode_stock');
    $idCol = pick_col($cols, ['id', 'barcode_stock_id', 'barcode_id']);

    if (!$idCol) {
        out_json(['success' => false, 'status' => false, 'message' => 'barcode_stock ID column পাওয়া যায়নি'], 500);
    }

    $statusParam = mb_strtolower(trim((string)($_GET['status'] ?? 'all')), 'UTF-8');
    $search = trim((string)($_GET['search'] ?? $_GET['q'] ?? ''));
    $karatFilter = trim((string)($_GET['karat'] ?? $_GET['purity'] ?? ''));
    $sourceFilter = mb_strtolower(trim((string)($_GET['source'] ?? '')), 'UTF-8');
    $locationId = (int)($_GET['location_id'] ?? $_GET['stock_location_id'] ?? 0);
    $dateFrom = trim((string)($_GET['date_from'] ?? $_GET['from'] ?? ''));
    $dateTo = trim((string)($_GET['date_to'] ?? $_GET['to'] ?? ''));
    $limit = (int)($_GET['limit'] ?? 2000);
    $page = (int)($_GET['page'] ?? 1);

    if ($limit <= 0) $limit = 2000;
    if ($limit > 5000) $limit = 5000;
    if ($page <= 0) $page = 1;

    $offset = ($page - 1) * $limit;

    $where = ['1=1'];
    $params = [];

    if (has_col($cols, 'is_deleted')) {
        $where[] = '(bs.`is_deleted` IS NULL OR bs.`is_deleted` = 0)';
    }

    if (has_col($cols, 'deleted_at')) {
        $where[] = 'bs.`deleted_at` IS NULL';
    }

    if (has_col($cols, 'status')) {
        $where[] = "LOWER(TRIM(COALESCE(bs.`status`, ''))) NOT IN ('deleted','cancelled','canceled','void','trash','trashed','বাতিল')";
    }

    $soldStatuses = ['sold', 'sell', 'sold_out', 'sale_done', 'বিক্রি', 'বিক্রিত', 'বিক্রি হয়েছে', 'বিক্রি হয়েছে'];
    $stockStatuses = ['', 'in_stock', 'stock', 'available', 'ready'];

    if (in_array($statusParam, ['in_stock', 'stock', 'available'], true)) {
        $parts = [];

        if (has_col($cols, 'status')) {
            $parts[] = "LOWER(TRIM(COALESCE(bs.`status`, ''))) IN (" . sql_in_placeholders(count($stockStatuses)) . ')';
            foreach ($stockStatuses as $v) $params[] = $v;
        }

        if (has_col($cols, 'sale_id')) {
            $parts[] = '(bs.`sale_id` IS NULL OR bs.`sale_id` = 0)';
        }

        if (has_col($cols, 'sold_at')) {
            $parts[] = "(bs.`sold_at` IS NULL OR bs.`sold_at` = '' OR bs.`sold_at` = '0000-00-00 00:00:00')";
        }

        if (has_col($cols, 'sold_invoice_no')) {
            $parts[] = "(bs.`sold_invoice_no` IS NULL OR bs.`sold_invoice_no` = '')";
        }

        if (!empty($parts)) {
            $where[] = '(' . implode(' AND ', $parts) . ')';
        }
    } elseif (in_array($statusParam, ['sold', 'sell', 'sold_out'], true)) {
        $parts = [];

        if (has_col($cols, 'status')) {
            $parts[] = "LOWER(TRIM(COALESCE(bs.`status`, ''))) IN (" . sql_in_placeholders(count($soldStatuses)) . ')';
            foreach ($soldStatuses as $v) $params[] = $v;
        }

        if (has_col($cols, 'sale_id')) {
            $parts[] = '(bs.`sale_id` IS NOT NULL AND bs.`sale_id` > 0)';
        }

        if (has_col($cols, 'sold_at')) {
            $parts[] = "(bs.`sold_at` IS NOT NULL AND bs.`sold_at` <> '' AND bs.`sold_at` <> '0000-00-00 00:00:00')";
        }

        if (has_col($cols, 'sold_invoice_no')) {
            $parts[] = "(bs.`sold_invoice_no` IS NOT NULL AND bs.`sold_invoice_no` <> '')";
        }

        if (!empty($parts)) {
            $where[] = '(' . implode(' OR ', $parts) . ')';
        }
    }

    if ($locationId > 0) {
        $locCol = pick_col($cols, ['location_id', 'stock_location_id']);
        if ($locCol) {
            $where[] = 'bs.' . qi($locCol) . ' = ?';
            $params[] = $locationId;
        }
    }

    if ($karatFilter !== '') {
        $karatCol = pick_col($cols, ['karat', 'purity']);
        if ($karatCol) {
            $where[] = 'bs.' . qi($karatCol) . ' = ?';
            $params[] = $karatFilter;
        }
    }

    if ($sourceFilter !== '') {
        $sourceParts = [];

        foreach (['source', 'source_type', 'stock_source', 'purchase_type', 'source_json', 'note', 'remarks'] as $c) {
            if (has_col($cols, $c)) {
                $sourceParts[] = 'LOWER(COALESCE(bs.' . qi($c) . ", '')) LIKE ?";
                $params[] = '%' . $sourceFilter . '%';
            }
        }

        if (!empty($sourceParts)) {
            $where[] = '(' . implode(' OR ', $sourceParts) . ')';
        }
    }

    if ($search !== '') {
        $searchParts = [];

        foreach (['barcode_no', 'barcode', 'barcode_code', 'item_name', 'name', 'product_name', 'karat', 'purity', 'note', 'remarks', 'location_name', 'source', 'source_type'] as $c) {
            if (has_col($cols, $c)) {
                $searchParts[] = 'bs.' . qi($c) . ' LIKE ?';
                $params[] = '%' . $search . '%';
            }
        }

        if (!empty($searchParts)) {
            $where[] = '(' . implode(' OR ', $searchParts) . ')';
        }
    }

    $dateCol = pick_col($cols, ['created_at', 'purchase_date', 'date']);

    if ($dateCol && preg_match('/^\d{4}-\d{2}-\d{2}$/', $dateFrom)) {
        $where[] = 'DATE(bs.' . qi($dateCol) . ') >= ?';
        $params[] = $dateFrom;
    }

    if ($dateCol && preg_match('/^\d{4}-\d{2}-\d{2}$/', $dateTo)) {
        $where[] = 'DATE(bs.' . qi($dateCol) . ') <= ?';
        $params[] = $dateTo;
    }

    $orderCol = pick_col($cols, ['created_at', 'id']);
    $orderSql = $orderCol ? ' ORDER BY bs.' . qi($orderCol) . ' DESC ' : '';

    $sql = 'SELECT bs.* FROM `barcode_stock` bs WHERE ' . implode(' AND ', $where) . $orderSql . ' LIMIT ' . (int)$limit . ' OFFSET ' . (int)$offset;

    $st = $pdo->prepare($sql);
    $st->execute($params);
    $rows = $st->fetchAll(PDO::FETCH_ASSOC);

    $saleInfo = load_sale_info($pdo, $rows, $idCol);
    $locations = load_locations($pdo);

    $items = [];

    $summary = [
        'total_items' => 0,
        'total_weight' => 0.0,
        'in_stock' => 0,
        'in_stock_weight' => 0.0,
        'sold' => 0,
        'sold_weight' => 0.0,
        'zero_weight' => 0,
        'showroom_stock' => 0,
        'showroom_weight' => 0.0,
        'locker_stock' => 0,
        'locker_weight' => 0.0,
        'factory_stock' => 0,
        'factory_weight' => 0.0,
        'old_gold_purchase_stock' => 0,
        'old_gold_purchase_weight' => 0.0,
        'by_location' => [],
        'by_status' => [],
        'by_karat' => [],
        'by_source' => []
    ];

    foreach ($rows as $r) {
        $id = (int)$r[$idCol];

        $barcodeNo = trim((string)first_value($r, ['barcode_no', 'barcode', 'barcode_code'], ''));
        $itemName = (string)first_value($r, ['item_name', 'name', 'product_name'], 'আইটেম নাম নেই');

        $karat = (string)first_value($r, ['karat', 'purity'], '');
        if ($karat === '') $karat = '22K';

        $vori = first_float($r, ['vori', 'bhori'], 0);
        $ana = first_float($r, ['ana', 'aana'], 0);
        $roti = first_float($r, ['roti', 'rati'], 0);
        $point = first_float($r, ['point', 'points'], 0);

        $weight = first_float($r, [
            'gross_weight_gram',
            'gross_weight',
            'weight_gram',
            'total_weight_gram',
            'net_weight',
            'weight'
        ], 0);

        if ($weight <= 0 && ($vori > 0 || $ana > 0 || $roti > 0 || $point > 0)) {
            $weight = gram_from_parts($vori, $ana, $roti, $point);
        }

        if ($weight > 0 && $vori <= 0 && $ana <= 0 && $roti <= 0 && $point <= 0) {
            $parts = gram_to_parts($weight);
            $vori = $parts['vori'];
            $ana = $parts['ana'];
            $roti = $parts['roti'];
            $point = $parts['point'];
        }

        $pureWeight = first_float($r, ['pure_weight_gram', 'pure_weight', 'pure_gold_gram'], 0);

        $locationIdRow = first_int($r, ['location_id', 'stock_location_id'], 0);

        $locationName = trim((string)first_value($r, ['location_name', 'location'], ''));
        if ($locationName === '' && $locationIdRow > 0 && isset($locations[$locationIdRow])) {
            $locationName = $locations[$locationIdRow];
        }
        if ($locationName === '') $locationName = '—';

        $rawStatus = (string)first_value($r, ['status'], 'in_stock');

        $rowSaleInfo = [
            'sale_id' => 0,
            'invoice_no' => '',
            'sold_at' => '',
            'sold_price' => 0.0,
            'sale_rate_per_vori' => 0.0
        ];

        if (isset($saleInfo['by_id'][$id])) {
            $rowSaleInfo = $saleInfo['by_id'][$id];
        } elseif ($barcodeNo !== '' && isset($saleInfo['by_barcode'][$barcodeNo])) {
            $rowSaleInfo = $saleInfo['by_barcode'][$barcodeNo];
        }

        $saleId = (int)$rowSaleInfo['sale_id'];
        if ($saleId <= 0) $saleId = first_int($r, ['sale_id'], 0);

        $invoiceNo = trim((string)$rowSaleInfo['invoice_no']);
        if ($invoiceNo === '') {
            $invoiceNo = trim((string)first_value($r, ['sold_invoice_no', 'invoice_no'], ''));
        }

        $soldAt = trim((string)$rowSaleInfo['sold_at']);
        if ($soldAt === '') {
            $soldAt = trim((string)first_value($r, ['sold_at'], ''));
        }

        $normStatus = normalize_status($rawStatus, $saleId, $soldAt, $invoiceNo);
        if ($normStatus === 'deleted') continue;

        $listPrice = first_float($r, ['sale_price', 'selling_price', 'item_price', 'price'], 0);
        $costPrice = first_float($r, ['cost_price', 'purchase_price', 'buy_price'], 0);

        $soldPrice = (float)$rowSaleInfo['sold_price'];
        if ($soldPrice <= 0) $soldPrice = first_float($r, ['sold_price'], 0);
        if ($normStatus === 'sold' && $soldPrice <= 0) $soldPrice = $listPrice;

        $saleRatePerVori = (float)$rowSaleInfo['sale_rate_per_vori'];
        if ($saleRatePerVori <= 0) {
            $saleRatePerVori = first_float($r, ['sale_rate_per_vori', 'rate_per_vori', 'gold_rate'], 0);
        }

        $purchaseId = first_int($r, ['purchase_id', 'source_purchase_id', 'purchase_stock_id'], 0);

        $sourceText = trim((string)first_value($r, ['source', 'source_type', 'stock_source', 'purchase_type'], ''));
        $sourceJson = trim((string)first_value($r, ['source_json'], ''));
        $note = trim((string)first_value($r, ['note', 'remarks'], ''));

        if ($sourceText === '') {
            if ($purchaseId > 0) $sourceText = 'purchase';
            else $sourceText = 'manual';
        }

        $sourceSearchText = $sourceText . ' ' . $sourceJson . ' ' . $note . ' ' . $itemName;
        $isOldGold = is_old_gold_source($sourceSearchText);

        $createdAt = (string)first_value($r, ['created_at', 'purchase_date', 'date'], '');
        $transferStatus = (string)first_value($r, ['transfer_status'], 'none');

        $item = [
            'id' => $id,
            'barcode_id' => $id,
            'barcode_stock_id' => $id,

            'barcode_no' => $barcodeNo,
            'barcode' => $barcodeNo,
            'barcode_code' => $barcodeNo,

            'item_name' => $itemName,
            'name' => $itemName,
            'product_name' => $itemName,

            'karat' => $karat,
            'purity' => $karat,

            'vori' => $vori,
            'ana' => $ana,
            'roti' => $roti,
            'rati' => $roti,
            'point' => $point,

            'gross_weight_gram' => round($weight, 3),
            'gross_weight' => round($weight, 3),
            'weight' => round($weight, 3),
            'weight_gram' => round($weight, 3),

            'pure_weight_gram' => round($pureWeight, 3),
            'pure_weight' => round($pureWeight, 3),

            'location_id' => $locationIdRow,
            'stock_location_id' => $locationIdRow,
            'location_name' => $locationName,
            'location' => $locationName,

            'purchase_id' => $purchaseId,
            'source' => $sourceText,
            'source_type' => $sourceText,
            'is_old_gold_purchase' => $isOldGold ? 1 : 0,

            'status' => $normStatus,
            'raw_status' => $rawStatus,
            'transfer_status' => $transferStatus,

            'sale_id' => $saleId,
            'invoice_no' => $invoiceNo,
            'sold_invoice_no' => $invoiceNo,
            'sold_at' => $soldAt,
            'sold_date' => $soldAt,

            'cost_price' => round($costPrice, 2),
            'list_price' => round($listPrice, 2),
            'sale_price' => round($normStatus === 'sold' ? $soldPrice : $listPrice, 2),
            'selling_price' => round($normStatus === 'sold' ? $soldPrice : $listPrice, 2),
            'item_price' => round($normStatus === 'sold' ? $soldPrice : $listPrice, 2),
            'sold_price' => round($normStatus === 'sold' ? $soldPrice : 0, 2),
            'sale_rate_per_vori' => round($saleRatePerVori, 2),

            'note' => $note,
            'created_at' => $createdAt,

            'is_zero_weight' => $weight <= 0 ? 1 : 0,
            'is_deleted' => 0
        ];

        $items[] = $item;

        $summary['total_items']++;
        $summary['total_weight'] = round($summary['total_weight'] + $weight, 3);

        if ($normStatus === 'sold') {
            $summary['sold']++;
            $summary['sold_weight'] = round($summary['sold_weight'] + $weight, 3);
        } else {
            $summary['in_stock']++;
            $summary['in_stock_weight'] = round($summary['in_stock_weight'] + $weight, 3);

            $locationLower = mb_strtolower($locationName, 'UTF-8');

            if (mb_strpos($locationLower, 'locker') !== false || mb_strpos($locationLower, 'লকার') !== false || $locationIdRow === 4) {
                $summary['locker_stock']++;
                $summary['locker_weight'] = round($summary['locker_weight'] + $weight, 3);
            } elseif (mb_strpos($locationLower, 'showroom') !== false || mb_strpos($locationLower, 'শোরুম') !== false || in_array($locationIdRow, [2, 3], true)) {
                $summary['showroom_stock']++;
                $summary['showroom_weight'] = round($summary['showroom_weight'] + $weight, 3);
            } elseif (mb_strpos($locationLower, 'factory') !== false || mb_strpos($locationLower, 'কারখানা') !== false || $locationIdRow === 1) {
                $summary['factory_stock']++;
                $summary['factory_weight'] = round($summary['factory_weight'] + $weight, 3);
            }
        }

        if ($isOldGold) {
            $summary['old_gold_purchase_stock']++;
            $summary['old_gold_purchase_weight'] = round($summary['old_gold_purchase_weight'] + $weight, 3);
        }

        if ($weight <= 0) {
            $summary['zero_weight']++;
        }

        add_summary_bucket($summary['by_location'], $locationName, $weight);
        add_summary_bucket($summary['by_status'], $normStatus, $weight);
        add_summary_bucket($summary['by_karat'], $karat, $weight);
        add_summary_bucket($summary['by_source'], $sourceText, $weight);
    }

    out_json([
        'success' => true,
        'status' => true,
        'message' => 'বারকোড স্টক লোড হয়েছে',
        'meta' => [
            'page' => $page,
            'limit' => $limit,
            'returned' => count($items),
            'read_only' => true,
            'filters' => [
                'status' => $statusParam,
                'search' => $search,
                'karat' => $karatFilter,
                'source' => $sourceFilter,
                'location_id' => $locationId,
                'date_from' => $dateFrom,
                'date_to' => $dateTo
            ]
        ],
        'data' => [
            'items' => $items,
            'barcodes' => $items,
            'summary' => $summary
        ],
        'items' => $items,
        'barcodes' => $items,
        'summary' => $summary
    ]);

} catch (Throwable $e) {
    out_json([
        'success' => false,
        'status' => false,
        'message' => 'বারকোড তালিকা লোড সমস্যা: ' . $e->getMessage()
    ], 500);
}