<?php

namespace App\Services\V2026;

use App\Http\Controllers\Auth\authentication;
use Exception;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

/**
 * v2026 sandbox: **persistent** staging tables `tbl_temp_v2026_*` (normal InnoDB tables, not TEMPORARY).
 * Enable with V2026_TEMP_SANDBOX=true in .env
 *
 * **Reads** (prices / news / tenders / commodity reports list APIs): only `SELECT` from these tables — fast, no rebuild per request.
 * `GET v1/prices/india` reads only {@see self::TABLE_TEMP_PRICES} (domestic `f` rows imported from `tbl_price_static` on `type=setup`).
 *
 * **Maintenance (single endpoint only):** `GET` or `POST` `/v2026/v1/sandbox/staging`
 *   - `type=setup` — `DROP TABLE IF EXISTS` each staging table, `CREATE TABLE`, then import the **latest 200 rows** per table (no `commodity_id`).
 *   - `type=drop` — drops all four staging tables only; does **not** recreate or import.
 *
 * | Staging table             | Live sources |
 * |---------------------------|--------------|
 * | tbl_temp_v2026_prices     | tbl_dom_master + tbl_price_static; tbl_exim_master + tbl_price_static |
 * | tbl_temp_v2026_news       | tbl_news_post |
 * | tbl_temp_v2026_tenders    | tbl_tenders + tbl_tenders_region |
 * | tbl_temp_v2026_reports    | tbl_steelreport + tbl_steelreports_category |
 *
 * Read filters aligned with legacy `ApiQuery` / v2019 app behaviour where practical:
 *   - `app_type` + `accessToken` resolve user (SteelMint vs CoalMint token table).
 *   - `selected_market`: when set, prices use `FIND_IN_SET` on staged `f_market` (from `tbl_dom_master` / `tbl_exim_master`);
 *     tenders restrict commodity / sub / sub-sub IDs to `tbl_region_commodity_master` rows whose `f_market` contains that token.
 *   - CoalMint (`app_type=CM`): tenders and prices reads add `commodity_id = 401` like v2019 (`commodityID` coal scope).
 */
class V2026TempTableSandbox
{
    /** Rows imported per staging table on `type=setup`. */
    const SANDBOX_IMPORT_ROW_LIMIT = 200;

    const TABLE_TEMP_PRICES = 'tbl_temp_v2026_prices';

    const TABLE_TEMP_NEWS = 'tbl_temp_v2026_news';

    const TABLE_TEMP_TENDERS = 'tbl_temp_v2026_tenders';

    const TABLE_TEMP_REPORTS = 'tbl_temp_v2026_reports';

    public static function enabled()
    {
        return filter_var(env('V2026_TEMP_SANDBOX', false), FILTER_VALIDATE_BOOLEAN);
    }

    public static function resolveAccess(Request $request)
    {
        $appType = strtoupper((string) $request->input('app_type', $request->query('app_type', 'SM'))) === 'CM' ? 'CM' : 'SM';
        $token = $request->input('accessToken', $request->query('accessToken'));
        $memid = 0;
        if ($token !== null && $token !== '') {
            $auth = new authentication();
            $memid = (int) $auth->authToken($token, $appType);
        }

        return [
            'memid' => $memid,
            'authenticated' => $memid > 0,
            'app_type' => $appType,
            'access_level' => $memid > 0 ? 'member' : 'guest',
        ];
    }

    /**
     * Routes set PDO::FETCH_ASSOC globally; {@see Connection::select()} returns rows as arrays.
     * Sandbox mappers use object property access — normalize here.
     *
     * @param array|object|null $row
     *
     * @return \stdClass
     */
    private static function dbRowToObject($row)
    {
        if ($row === null) {
            return (object) [];
        }
        if (is_array($row)) {
            return (object) $row;
        }

        return $row;
    }

    public static function paginateParams(Request $request)
    {
        $page = max(1, (int) $request->query('page', $request->input('page', 1)));
        $perPage = min(100, max(1, (int) $request->query('per_page', $request->input('per_page', 20))));
        $offset = ($page - 1) * $perPage;

        return [$page, $perPage, $offset];
    }

    /** Same token as v2019 `filter['selected_market']` on app listings. */
    private static function selectedMarketToken(Request $request)
    {
        return trim((string) $request->query('selected_market', $request->input('selected_market', '')));
    }

    /** True when the client sent this key (query or body) with a non-empty value. */
    private static function filterProvided(Request $request, $key)
    {
        if (!$request->has($key)) {
            return false;
        }
        $v = $request->input($key, $request->query($key));

        return !($v === null || $v === '');
    }

    /**
     * v2019 / commonController-style labels (ferrous, non-ferrous, coal, …) — not product brand names.
     *
     * @param string $slug lower-case market key from f_market / selected_market
     *
     * @return string
     */
    private static function marketSlugToDisplayLabel($slug)
    {
        $slug = strtolower(trim(str_replace('_', '-', (string) $slug)));
        if ($slug === 'nonferrous') {
            $slug = 'non-ferrous';
        }
        $map = [
            'ferrous' => 'Ferrous Metals',
            'non-ferrous' => 'Non Ferrous',
            'coal' => 'Coal & Energy',
            'scrap-recycling' => 'Scrap Recycling',
            'agriculture' => 'Agriculture',
            'future_prices' => 'Future Prices',
        ];
        if (isset($map[$slug])) {
            return $map[$slug];
        }
        if (strpos($slug, '=') !== false) {
            $parts = explode('=', $slug, 2);
            $left = isset($parts[0]) ? trim($parts[0]) : '';
            $right = isset($parts[1]) ? trim($parts[1]) : '';
            if ($left === 'future_prices' && $right !== '') {
                return self::marketSlugToDisplayLabel($right).' (Future)';
            }
        }

        return $slug !== '' ? ucwords(str_replace(['-', '_'], ' ', $slug)) : '';
    }

    /**
     * Mirrors {@see config('constants.php')} `allowed_ids_market_wise_new` (that key is currently after an early `return` in config, so may be unset at runtime).
     *
     * @return array
     */
    private static function allowedIdsMarketWiseFallback()
    {
        return [
            'ferrous' => [
                'commodity' => [408, 410, 413, 403, 412, 980, 407, 402],
                'subcommodity' => [415, 416, 417, 431],
            ],
            'non-ferrous' => [
                'commodity' => [972],
                'subcommodity' => [],
            ],
            'coal' => [
                'commodity' => [401, 407],
                'subcommodity' => [],
            ],
            'scrap-recycling' => [
                'commodity' => [412, 980],
                'subcommodity' => [439, 440, 442, 973, 996, 997],
            ],
            'agriculture' => [
                'commodity' => [2083, 2084, 2085, 2086, 2087, 2088, 2089, 2090, 2091, 2092],
                'subcommodity' => [],
            ],
        ];
    }

    /**
     * Resolve vertical market from commodity id using {@see config('constants.allowed_ids_market_wise_new')} when available.
     *
     * @param int    $commodityId route or filter commodity id
     * @param string $appType      SM|CM
     *
     * @return string
     */
    private static function marketLabelFromCommodityScope($commodityId, $appType)
    {
        $cid = (int) $commodityId;
        if ($cid <= 0) {
            return strtoupper((string) $appType) === 'CM' ? 'Coal & Energy' : 'Ferrous Metals';
        }
        $cfg = config('constants.allowed_ids_market_wise_new');
        if (!is_array($cfg)) {
            $cfg = self::allowedIdsMarketWiseFallback();
        }
        foreach ($cfg as $slug => $groups) {
            if (!is_array($groups)) {
                continue;
            }
            foreach (['commodity', 'subcommodity'] as $bucket) {
                if (empty($groups[$bucket]) || !is_array($groups[$bucket])) {
                    continue;
                }
                foreach ($groups[$bucket] as $allowedId) {
                    if ((int) $allowedId === $cid) {
                        return self::marketSlugToDisplayLabel((string) $slug);
                    }
                }
            }
        }

        return 'Ferrous Metals';
    }

    /**
     * Canonical market slug (e.g. ferrous, non-ferrous) from commodity id + app scope.
     *
     * @param int    $commodityId
     * @param string $appType SM|CM
     *
     * @return string
     */
    private static function marketSlugFromCommodityScope($commodityId, $appType)
    {
        $cid = (int) $commodityId;
        if ($cid <= 0) {
            return strtoupper((string) $appType) === 'CM' ? 'coal' : 'ferrous';
        }
        $cfg = config('constants.allowed_ids_market_wise_new');
        if (! is_array($cfg)) {
            $cfg = self::allowedIdsMarketWiseFallback();
        }
        foreach ($cfg as $slug => $groups) {
            if (! is_array($groups)) {
                continue;
            }
            foreach (['commodity', 'subcommodity'] as $bucket) {
                if (empty($groups[$bucket]) || ! is_array($groups[$bucket])) {
                    continue;
                }
                foreach ($groups[$bucket] as $allowedId) {
                    if ((int) $allowedId === $cid) {
                        return strtolower((string) $slug);
                    }
                }
            }
        }

        return 'ferrous';
    }

    /**
     * All commodity / sub-commodity ids mapped to a vertical market slug in {@see allowedIdsMarketWiseFallback()}.
     *
     * @param string $slug normalized market slug
     *
     * @return int[]
     */
    private static function commodityIdsForMarketSlug($slug)
    {
        $slug = self::normalizeMarketSlugToken($slug);
        if ($slug === '') {
            return [];
        }
        $cfg = config('constants.allowed_ids_market_wise_new');
        if (! is_array($cfg)) {
            $cfg = self::allowedIdsMarketWiseFallback();
        }
        if (! isset($cfg[$slug]) || ! is_array($cfg[$slug])) {
            return [];
        }
        $ids = [];
        foreach (['commodity', 'subcommodity'] as $bucket) {
            if (empty($cfg[$slug][$bucket]) || ! is_array($cfg[$slug][$bucket])) {
                continue;
            }
            foreach ($cfg[$slug][$bucket] as $id) {
                $ids[] = (int) $id;
            }
        }

        return array_values(array_unique(array_filter($ids)));
    }

    /**
     * Normalize a token from `f_market` CSV or query param to a canonical slug.
     *
     * @param string $token
     *
     * @return string
     */
    private static function normalizeMarketSlugToken($token)
    {
        $s = strtolower(trim(str_replace([' ', '_'], ['', '-'], (string) $token)));
        if ($s === 'nonferrous') {
            $s = 'non-ferrous';
        }

        return $s;
    }

    /**
     * Distinct normalized slugs from `tbl_region_commodity_master.f_market` CSV.
     *
     * @param string $csv
     *
     * @return string[]
     */
    private static function distinctSlugsFromFMarketCsv($csv)
    {
        $csv = trim((string) $csv);
        if ($csv === '') {
            return [];
        }
        $out = [];
        foreach (explode(',', $csv) as $part) {
            $slug = self::normalizeMarketSlugToken($part);
            if ($slug === '') {
                continue;
            }
            $out[$slug] = $slug;
        }

        return array_values($out);
    }

    /**
     * Display label: {@see bigmint_market_slug_to_label()} when available, else {@see marketSlugToDisplayLabel()}.
     *
     * @param string $slug
     *
     * @return string
     */
    private static function displayLabelForMarketSlug($slug)
    {
        $slug = self::normalizeMarketSlugToken($slug);
        if ($slug === '') {
            return '';
        }
        if (function_exists('bigmint_market_slug_to_label')) {
            $label = bigmint_market_slug_to_label($slug);
            if ($label !== '') {
                return $label;
            }
        }

        return self::marketSlugToDisplayLabel($slug);
    }

    /**
     * Stable numeric id for API (order from {@see bigmint_market_slugs()} when possible).
     *
     * @param string $slug
     *
     * @return int
     */
    private static function marketNumericIdForSlug($slug)
    {
        $slug = self::normalizeMarketSlugToken($slug);
        $order = function_exists('bigmint_market_slugs') ? bigmint_market_slugs() : array_keys(self::allowedIdsMarketWiseFallback());
        $idx = array_search($slug, $order, true);
        if ($idx !== false) {
            return $idx + 1;
        }

        return 1000 + (abs(crc32($slug)) % 9000);
    }

    /**
     * Slugs to place a news row under (from `f_market` or commodity fallback).
     *
     * @param string $fMarketCsv from joined master row
     * @param int    $commodityId
     * @param string $appType
     *
     * @return string[]
     */
    private static function resolvedMarketSlugsForNewsRow($fMarketCsv, $commodityId, $appType)
    {
        $slugs = self::distinctSlugsFromFMarketCsv($fMarketCsv);
        if ($slugs === []) {
            return [self::marketSlugFromCommodityScope($commodityId, $appType)];
        }

        return $slugs;
    }

    /**
     * Order market buckets: canonical slug order first, then any remaining keys.
     *
     * @param array<string, array<string, mixed>> $buckets slug => market block
     *
     * @return array<int, array<string, mixed>>
     */
    private static function orderedNewsMarketBuckets(array $buckets)
    {
        $out = [];
        $seen = [];
        $preferred = function_exists('bigmint_market_slugs') ? bigmint_market_slugs() : array_keys(self::allowedIdsMarketWiseFallback());
        foreach ($preferred as $slug) {
            if (isset($buckets[$slug])) {
                $out[] = $buckets[$slug];
                $seen[$slug] = true;
            }
        }
        $rest = array_diff_key($buckets, $seen);
        ksort($rest, SORT_STRING);
        foreach ($rest as $block) {
            $out[] = $block;
        }

        return $out;
    }

    /**
     * User-facing market line: selected_market slug → display name (commonController $market_arr),
     * else commodity-scoped vertical from constants, else app default vertical.
     *
     * @param int|null $commodityIdForScope route commodity id or news commodity_id filter
     *
     * @return string
     */
    private static function displayMarketLabel(Request $request, $appType, $commodityIdForScope = null)
    {
        $token = self::selectedMarketToken($request);
        if ($token !== '') {
            $norm = strtolower(str_replace(' ', '-', trim($token)));
            $norm = str_replace('_', '-', $norm);

            return self::marketSlugToDisplayLabel($norm);
        }
        if ($commodityIdForScope !== null && (int) $commodityIdForScope > 0) {
            return self::marketLabelFromCommodityScope((int) $commodityIdForScope, $appType);
        }

        return strtoupper((string) $appType) === 'CM' ? 'Coal & Energy' : 'Ferrous Metals';
    }

    /**
     * @param \Illuminate\Database\Connection $conn
     * @param int                          $commodityId
     *
     * @return string
     */
    private static function commodityDisplayName($conn, $commodityId)
    {
        $cid = (int) $commodityId;
        if ($cid <= 0) {
            return '';
        }
        try {
            $row = self::dbRowToObject($conn->selectOne(
                'SELECT `itemName` AS `name` FROM `tbl_region_commodity_master` WHERE `itemType` = \'commodity\' AND `id` = ? LIMIT 1',
                [$cid]
            ));

            return isset($row->name) && (string) $row->name !== '' ? (string) $row->name : '';
        } catch (Exception $e) {
            return '';
        }
    }

    /**
     * @param string $type drop|setup
     *
     * @return array{status:bool,message:string,data:array}
     */
    public static function maintain(Request $request, $type)
    {
        $type = strtolower(trim((string) $type));
        $conn = DB::connection('mysql');

        if ($type === 'drop') {
            foreach ([self::TABLE_TEMP_PRICES, self::TABLE_TEMP_NEWS, self::TABLE_TEMP_TENDERS, self::TABLE_TEMP_REPORTS] as $tbl) {
                try {
                    $conn->statement('DROP TABLE IF EXISTS `'.$tbl.'`');
                } catch (Exception $e) {
                    // continue best-effort
                }
            }

            return [
                'status' => true,
                'message' => 'Sandbox staging tables dropped (nothing recreated).',
                'data' => [
                    'type' => 'drop',
                    'tables' => [
                        self::TABLE_TEMP_PRICES,
                        self::TABLE_TEMP_NEWS,
                        self::TABLE_TEMP_TENDERS,
                        self::TABLE_TEMP_REPORTS,
                    ],
                ],
            ];
        }

        if ($type === 'setup') {
            try {
                self::dropTable($conn, self::TABLE_TEMP_PRICES);
                self::dropTable($conn, self::TABLE_TEMP_NEWS);
                self::dropTable($conn, self::TABLE_TEMP_TENDERS);
                self::dropTable($conn, self::TABLE_TEMP_REPORTS);

                self::createTablePrices($conn);
                self::createTableNews($conn);
                self::createTableTenders($conn);
                self::createTableReports($conn);

                self::importPricesLatest($conn);
                self::importNewsData($conn);
                self::importTendersData($conn);
                self::importReportsData($conn);

                $lim = self::SANDBOX_IMPORT_ROW_LIMIT;
                $newsCount = (int) self::dbRowToObject($conn->selectOne('SELECT COUNT(*) AS c FROM `'.self::TABLE_TEMP_NEWS.'`'))->c;
                $tendersCount = (int) self::dbRowToObject($conn->selectOne('SELECT COUNT(*) AS c FROM `'.self::TABLE_TEMP_TENDERS.'`'))->c;
                $pricesCount = (int) self::dbRowToObject($conn->selectOne('SELECT COUNT(*) AS c FROM `'.self::TABLE_TEMP_PRICES.'`'))->c;
                $reportsCount = (int) self::dbRowToObject($conn->selectOne('SELECT COUNT(*) AS c FROM `'.self::TABLE_TEMP_REPORTS.'`'))->c;

                return [
                    'status' => true,
                    'message' => 'Sandbox tables rebuilt and imported (latest '.$lim.' rows per table).',
                    'data' => [
                        'type' => 'setup',
                        'import_limit_per_table' => $lim,
                        'row_counts' => [
                            self::TABLE_TEMP_PRICES => $pricesCount,
                            self::TABLE_TEMP_NEWS => $newsCount,
                            self::TABLE_TEMP_TENDERS => $tendersCount,
                            self::TABLE_TEMP_REPORTS => $reportsCount,
                        ],
                    ],
                ];
            } catch (Exception $e) {
                return [
                    'status' => false,
                    'message' => 'Sandbox setup failed: '.$e->getMessage(),
                    'data' => ['type' => 'setup', 'error' => true],
                ];
            }
        }

        return [
            'status' => false,
            'message' => 'Invalid type. Use type=drop or type=setup.',
            'data' => ['allowed' => ['drop', 'setup']],
        ];
    }

    private static function dropTable($conn, $name)
    {
        $conn->statement('DROP TABLE IF EXISTS `'.$name.'`');
    }

    private static function createTablePrices($conn)
    {
        $t = self::TABLE_TEMP_PRICES;
        $conn->statement(
            'CREATE TABLE `'.$t.'` (
              `relation_id` INT NOT NULL,
              `title` VARCHAR(512) NOT NULL DEFAULT \'\',
              `title_search` VARCHAR(512) NOT NULL DEFAULT \'\',
              `commodity_id` INT NOT NULL DEFAULT 0,
              `sub_commodity_id` INT NOT NULL DEFAULT 0,
              `sub_sub_commodity_id` INT NOT NULL DEFAULT 0,
              `sub_commodity_label` VARCHAR(255) NOT NULL DEFAULT \'\',
              `commodity_label` VARCHAR(255) NOT NULL DEFAULT \'\',
              `f_trade_code` VARCHAR(8) NOT NULL DEFAULT \'\',
              `currency` VARCHAR(32) NOT NULL DEFAULT \'\',
              `price` VARCHAR(64) NOT NULL DEFAULT \'\',
              `change_raw` VARCHAR(64) NOT NULL DEFAULT \'\',
              `size_grade` VARCHAR(512) NOT NULL DEFAULT \'\',
              `published_sort` DATETIME NULL,
              `f_market` VARCHAR(512) NOT NULL DEFAULT \'\',
              `access_level` VARCHAR(16) NOT NULL DEFAULT \'member_only\',
              KEY `idx_access` (`access_level`),
              KEY `idx_commodity` (`commodity_id`),
              KEY `idx_sub` (`sub_commodity_id`),
              KEY `idx_trade` (`f_trade_code`),
              KEY `idx_currency` (`currency`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8'
        );
    }

    private static function createTableNews($conn)
    {
        $t = self::TABLE_TEMP_NEWS;
        $conn->statement(
            'CREATE TABLE `'.$t.'` (
              `post_id` INT NOT NULL,
              `post_title` VARCHAR(512) NOT NULL DEFAULT \'\',
              `title_search` VARCHAR(512) NOT NULL DEFAULT \'\',
              `commodity_id` INT NOT NULL DEFAULT 0,
              `sub_label` VARCHAR(255) NOT NULL DEFAULT \'\',
              `post_date` DATETIME NULL,
              `href` VARCHAR(1024) NOT NULL DEFAULT \'\',
              `thumb` VARCHAR(1024) NOT NULL DEFAULT \'\',
              `news_type` VARCHAR(32) NOT NULL DEFAULT \'News\',
              `access_level` VARCHAR(16) NOT NULL DEFAULT \'member_only\',
              KEY `idx_access` (`access_level`),
              KEY `idx_commodity` (`commodity_id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8'
        );
    }

    private static function createTableTenders($conn)
    {
        $t = self::TABLE_TEMP_TENDERS;
        $conn->statement(
            'CREATE TABLE `'.$t.'` (
              `tender_id` INT NOT NULL,
              `title` VARCHAR(512) NOT NULL DEFAULT \'\',
              `title_search` VARCHAR(512) NOT NULL DEFAULT \'\',
              `buy_sell` VARCHAR(64) NOT NULL DEFAULT \'\',
              `qty_label` VARCHAR(128) NOT NULL DEFAULT \'\',
              `region_label` VARCHAR(512) NOT NULL DEFAULT \'\',
              `due_sort` DATETIME NULL,
              `offer_type` VARCHAR(128) NOT NULL DEFAULT \'\',
              `floor_price` VARCHAR(64) NOT NULL DEFAULT \'\',
              `commodity_id` INT NOT NULL DEFAULT 0,
              `sub_commodity_id` INT NOT NULL DEFAULT 0,
              `sub_sub_commodity_id` INT NOT NULL DEFAULT 0,
              `access_level` VARCHAR(16) NOT NULL DEFAULT \'member_only\',
              KEY `idx_access` (`access_level`),
              KEY `idx_due` (`due_sort`),
              KEY `idx_buy` (`buy_sell`),
              KEY `idx_cmdty` (`commodity_id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8'
        );
    }

    private static function createTableReports($conn)
    {
        $t = self::TABLE_TEMP_REPORTS;
        $conn->statement(
            'CREATE TABLE `'.$t.'` (
              `report_id` INT NOT NULL,
              `title` VARCHAR(512) NOT NULL DEFAULT \'\',
              `title_search` VARCHAR(512) NOT NULL DEFAULT \'\',
              `commodity_id` INT NOT NULL DEFAULT 0,
              `sub_commodity_id` INT NOT NULL DEFAULT 0,
              `commodity_label` VARCHAR(255) NOT NULL DEFAULT \'\',
              `sub_commodity_label` VARCHAR(255) NOT NULL DEFAULT \'\',
              `report_sort` DATETIME NULL,
              `date_display` VARCHAR(64) NOT NULL DEFAULT \'\',
              `detail_href` VARCHAR(1024) NOT NULL DEFAULT \'\',
              `cover_image` VARCHAR(1024) NOT NULL DEFAULT \'\',
              `f_special_regular` VARCHAR(8) NOT NULL DEFAULT \'r\',
              `ui_category` VARCHAR(16) NOT NULL DEFAULT \'intel\',
              `region_code` VARCHAR(32) NOT NULL DEFAULT \'\',
              `access_level` VARCHAR(16) NOT NULL DEFAULT \'member_only\',
              KEY `idx_access` (`access_level`),
              KEY `idx_commodity` (`commodity_id`),
              KEY `idx_sub` (`sub_commodity_id`),
              KEY `idx_ui_cat` (`ui_category`),
              KEY `idx_report_sort` (`report_sort`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8'
        );
    }

    /**
     * Latest N price rows (domestic + exim union), ordered by `date_time` descending.
     */
    private static function importPricesLatest($conn)
    {
        $t = self::TABLE_TEMP_PRICES;
        $lim = (int) self::SANDBOX_IMPORT_ROW_LIMIT;

        $sql = 'INSERT INTO `'.$t.'`
            (`relation_id`, `title`, `title_search`, `commodity_id`, `sub_commodity_id`, `sub_sub_commodity_id`, `sub_commodity_label`, `commodity_label`, `f_trade_code`, `currency`, `price`, `change_raw`, `size_grade`, `published_sort`, `f_market`, `access_level`)
            SELECT
                `relation_id`,
                `title`,
                `title_search`,
                `commodity_id`,
                `sub_commodity_id`,
                `sub_sub_commodity_id`,
                `sub_commodity_label`,
                `commodity_label`,
                `f_trade_code`,
                `currency`,
                `price`,
                `change_raw`,
                `size_grade`,
                `published_sort`,
                `f_market`,
                `access_level`
            FROM (
                SELECT
                    m.`id` AS `relation_id`,
                    IFNULL(n.`title`, \'\') AS `title`,
                    LOWER(IFNULL(n.`title`, \'\')) AS `title_search`,
                    IFNULL(n.`commodityID`, 0) AS `commodity_id`,
                    IFNULL(n.`subCommodityID`, 0) AS `sub_commodity_id`,
                    IFNULL(n.`subSubCommodityID`, 0) AS `sub_sub_commodity_id`,
                    IFNULL((SELECT rc.`itemName` FROM `tbl_region_commodity_master` rc WHERE rc.`itemType` = \'commodity\' AND rc.`id` = n.`subCommodityID` LIMIT 1), \'\') AS `sub_commodity_label`,
                    IFNULL((SELECT rc.`itemName` FROM `tbl_region_commodity_master` rc WHERE rc.`itemType` = \'commodity\' AND rc.`id` = n.`commodityID` LIMIT 1), \'\') AS `commodity_label`,
                    IFNULL(n.`f_cnf_fob`, \'\') AS `f_trade_code`,
                    IFNULL(m.`currency`, \'\') AS `currency`,
                    IFNULL(n.`price`, \'\') AS `price`,
                    IFNULL(n.`change`, \'\') AS `change_raw`,
                    IFNULL(n.`size_grade`, \'\') AS `size_grade`,
                    IFNULL(n.`date_time`, NOW()) AS `published_sort`,
                    IFNULL(m.`f_market`, \'\') AS `f_market`,
                    CASE WHEN IFNULL(m.`showOnlyInSteelmint`, 0) = 1 THEN \'guest_ok\' ELSE \'member_only\' END AS `access_level`
                FROM `tbl_dom_master` m
                INNER JOIN `tbl_price_static` n ON m.`id` = n.`relation_id`
                WHERE n.`continue_or_discontinue` = 1
                  AND n.`f_cnf_fob` <> \'\'
                  AND m.`platform` LIKE \'%app%\'
                  AND n.`f_cnf_fob` = \'f\'
                UNION ALL
                SELECT
                    m.`id`,
                    IFNULL(n.`title`, \'\'),
                    LOWER(IFNULL(n.`title`, \'\')),
                    IFNULL(n.`commodityID`, 0),
                    IFNULL(n.`subCommodityID`, 0),
                    IFNULL(n.`subSubCommodityID`, 0),
                    IFNULL((SELECT rc.`itemName` FROM `tbl_region_commodity_master` rc WHERE rc.`itemType` = \'commodity\' AND rc.`id` = n.`subCommodityID` LIMIT 1), \'\'),
                    IFNULL((SELECT rc.`itemName` FROM `tbl_region_commodity_master` rc WHERE rc.`itemType` = \'commodity\' AND rc.`id` = n.`commodityID` LIMIT 1), \'\'),
                    IFNULL(n.`f_cnf_fob`, \'\'),
                    IFNULL(m.`currency`, \'\'),
                    IFNULL(n.`price`, \'\'),
                    IFNULL(n.`change`, \'\'),
                    IFNULL(n.`size_grade`, \'\'),
                    IFNULL(n.`date_time`, NOW()),
                    IFNULL(m.`f_market`, \'\'),
                    CASE WHEN IFNULL(m.`showOnlyInSteelmint`, 0) = 1 THEN \'guest_ok\' ELSE \'member_only\' END
                FROM `tbl_exim_master` m
                INNER JOIN `tbl_price_static` n ON m.`id` = n.`relation_id`
                WHERE n.`continue_or_discontinue` = 1
                  AND IFNULL(m.`continue_or_discontinue`, 1) <> 0
                  AND n.`f_cnf_fob` <> \'\'
                  AND m.`platform` LIKE \'%app%\'
                  AND n.`f_cnf_fob` IN (\'cnf\', \'fob\')
            ) AS `combined`
            ORDER BY `combined`.`published_sort` DESC
            LIMIT '.$lim;
        try {
            $conn->statement($sql);
        } catch (Exception $e) {
            try {
                self::importPricesLatestDomOnly($conn, $lim);
            } catch (Exception $e2) {
                throw $e;
            }
        }
    }

    /**
     * Fallback if UNION insert fails on older MySQL: domestic rows only.
     */
    private static function importPricesLatestDomOnly($conn, $lim)
    {
        $t = self::TABLE_TEMP_PRICES;
        $sql = 'INSERT INTO `'.$t.'`
            (`relation_id`, `title`, `title_search`, `commodity_id`, `sub_commodity_id`, `sub_sub_commodity_id`, `sub_commodity_label`, `commodity_label`, `f_trade_code`, `currency`, `price`, `change_raw`, `size_grade`, `published_sort`, `f_market`, `access_level`)
            SELECT
                m.`id`,
                IFNULL(n.`title`, \'\'),
                LOWER(IFNULL(n.`title`, \'\')),
                IFNULL(n.`commodityID`, 0),
                IFNULL(n.`subCommodityID`, 0),
                IFNULL(n.`subSubCommodityID`, 0),
                IFNULL((SELECT rc.`itemName` FROM `tbl_region_commodity_master` rc WHERE rc.`itemType` = \'commodity\' AND rc.`id` = n.`subCommodityID` LIMIT 1), \'\'),
                IFNULL((SELECT rc.`itemName` FROM `tbl_region_commodity_master` rc WHERE rc.`itemType` = \'commodity\' AND rc.`id` = n.`commodityID` LIMIT 1), \'\'),
                IFNULL(n.`f_cnf_fob`, \'\'),
                IFNULL(m.`currency`, \'\'),
                IFNULL(n.`price`, \'\'),
                IFNULL(n.`change`, \'\'),
                IFNULL(n.`size_grade`, \'\'),
                IFNULL(n.`date_time`, NOW()),
                IFNULL(m.`f_market`, \'\'),
                CASE WHEN IFNULL(m.`showOnlyInSteelmint`, 0) = 1 THEN \'guest_ok\' ELSE \'member_only\' END
            FROM `tbl_dom_master` m
            INNER JOIN `tbl_price_static` n ON m.`id` = n.`relation_id`
            WHERE n.`continue_or_discontinue` = 1
              AND n.`f_cnf_fob` <> \'\'
              AND m.`platform` LIKE \'%app%\'
              AND n.`f_cnf_fob` = \'f\'
            ORDER BY n.`date_time` DESC
            LIMIT '.(int) $lim;
        $conn->statement($sql);
    }

    private static function importNewsData($conn)
    {
        $t = self::TABLE_TEMP_NEWS;
        $ins = 'INSERT INTO `'.$t.'`
            (`post_id`, `post_title`, `title_search`, `commodity_id`, `sub_label`, `post_date`, `href`, `thumb`, `news_type`, `access_level`)
            SELECT
                p.`post_id`,
                IFNULL(p.`post_title`, \'\'),
                LOWER(IFNULL(p.`post_title`, \'\')),
                IFNULL(p.`commodityId`, 0),
                IFNULL((SELECT rc.`itemName` FROM `tbl_region_commodity_master` rc WHERE rc.`itemType` = \'commodity\' AND rc.`id` = p.`commodityId` LIMIT 1), \'\'),
                STR_TO_DATE(CONCAT(IFNULL(p.`post_date`, \'1970-01-01\'), \' \', IFNULL(p.`post_time`, \'00:00:00\')), \'%Y-%m-%d %H:%i:%s\'),
                IFNULL(p.`href`, \'\'),
                IFNULL(p.`medium_image`, \'\'),
                \'Insights\',
                CASE WHEN (p.`post_id` % 5) < 2 THEN \'guest_ok\' ELSE \'member_only\' END
            FROM `tbl_news_post` p
            WHERE p.`post_id` > 0
            ORDER BY p.`post_date` DESC, p.`post_time` DESC
            LIMIT '.(int) self::SANDBOX_IMPORT_ROW_LIMIT.'
        ';
        $conn->statement($ins);
    }

    private static function importTendersData($conn)
    {
        $t = self::TABLE_TEMP_TENDERS;
        $dueExpr = 'COALESCE(STR_TO_DATE(n.`due_date`, \'%Y-%m-%d %H:%i:%s\'), STR_TO_DATE(n.`due_date`, \'%Y-%m-%d\'))';
        $whereSql = implode(' AND ', [
            'n.`verified` = 1',
            "n.`due_date` <> '0000-00-00 00:00:00'",
            'n.`due_date` IS NOT NULL',
            '('.$dueExpr.' IS NOT NULL AND '.$dueExpr.' >= CURDATE())',
        ]);

        $ins = 'INSERT INTO `'.$t.'`
            (`tender_id`, `title`, `title_search`, `buy_sell`, `qty_label`, `region_label`, `due_sort`, `offer_type`, `floor_price`, `commodity_id`, `sub_commodity_id`, `sub_sub_commodity_id`, `access_level`)
            SELECT
                n.`id`,
                IFNULL(n.`title`, \'\'),
                LOWER(IFNULL(n.`title`, \'\')),
                IFNULL(n.`buy_sell`, \'\'),
                TRIM(CONCAT(IFNULL(n.`qty`, \'\'), \' \', IFNULL(n.`qty_unit`, \'\'))),
                IFNULL((
                    SELECT CONCAT_WS(\', \', ttr.`city_name`, ttr.`state_name`, ttr.`country_name`)
                    FROM `tbl_tenders_region` ttr
                    WHERE ttr.`tender_id` = n.`id`
                    LIMIT 1
                ), \'\'),
                COALESCE(STR_TO_DATE(n.`due_date`, \'%Y-%m-%d %H:%i:%s\'), STR_TO_DATE(n.`due_date`, \'%Y-%m-%d\')),
                IFNULL(n.`offer_type`, \'\'),
                IFNULL(n.`floor_price`, \'\'),
                IFNULL(n.`commodityID`, 0),
                IFNULL(n.`subCommodityID`, 0),
                IFNULL(n.`subsubCommodityID`, 0),
                CASE WHEN (n.`id` % 5) < 2 THEN \'guest_ok\' ELSE \'member_only\' END
            FROM `tbl_tenders` n
            WHERE '.$whereSql.'
            ORDER BY n.`id` DESC
            LIMIT '.(int) self::SANDBOX_IMPORT_ROW_LIMIT.'
        ';
        $conn->statement($ins);
    }

    /**
     * Latest N report rows from legacy `tbl_steelreport` (+ category for commodity scope).
     */
    private static function importReportsData($conn)
    {
        $t = self::TABLE_TEMP_REPORTS;
        $dateSortExpr = 'COALESCE(FROM_UNIXTIME(NULLIF(n.`date`, 0)), n.`date`)';
        $ins = 'INSERT INTO `'.$t.'`
            (`report_id`, `title`, `title_search`, `commodity_id`, `sub_commodity_id`, `commodity_label`, `sub_commodity_label`, `report_sort`, `date_display`, `detail_href`, `cover_image`, `f_special_regular`, `ui_category`, `region_code`, `access_level`)
            SELECT
                n.`id`,
                COALESCE(NULLIF(TRIM(n.`title`), \'\'), NULLIF(TRIM(c.`title`), \'\'), \'\'),
                LOWER(COALESCE(NULLIF(TRIM(n.`title`), \'\'), NULLIF(TRIM(c.`title`), \'\'), \'\')),
                IFNULL(NULLIF(c.`commodityID`, 0), 0),
                IFNULL(NULLIF(c.`subCommodityID`, 0), 0),
                IFNULL((SELECT rc.`itemName` FROM `tbl_region_commodity_master` rc WHERE rc.`itemType` = \'commodity\' AND rc.`id` = c.`commodityID` LIMIT 1), \'\'),
                IFNULL((SELECT rc.`itemName` FROM `tbl_region_commodity_master` rc WHERE rc.`itemType` = \'commodity\' AND rc.`id` = c.`subCommodityID` LIMIT 1), \'\'),
                '.$dateSortExpr.',
                DATE_FORMAT('.$dateSortExpr.', \'%e %M %Y\'),
                CONCAT(\'/v2026/v1/reports/\', n.`id`),
                COALESCE(NULLIF(TRIM(n.`cover_image`), \'\'), NULLIF(TRIM(c.`thumbnail`), \'\'), \'\'),
                IFNULL(NULLIF(TRIM(c.`f_special_regular`), \'\'), \'r\'),
                ELT((n.`id` % 4) + 1, \'intel\', \'dom\', \'imp\', \'exp\'),
                IFNULL(CAST(c.`regionID` AS CHAR), \'\'),
                CASE WHEN (n.`id` % 5) < 2 THEN \'guest_ok\' ELSE \'member_only\' END
            FROM `tbl_steelreport` n
            LEFT JOIN `tbl_steelreports_category` c ON c.`id` = n.`steelreports_category_id`
            WHERE n.`id` > 0
            ORDER BY '.$dateSortExpr.' DESC
            LIMIT '.(int) self::SANDBOX_IMPORT_ROW_LIMIT.'
        ';
        $conn->statement($ins);
    }

    private static function missingTableMessage($table)
    {
        return 'Staging table `'.$table.'` is missing or unreadable. Call POST/GET `/v2026/v1/sandbox/staging?type=setup` first, or `type=drop` to remove staging tables.';
    }

    /** Rows scanned to build `pricesIndia` nested markets (domestic only). */
    const PRICES_INDIA_ROW_LIMIT = 500;

    /**
     * Domestic India price tree for `GET v1/prices/india`.
     * Always reads {@see self::TABLE_TEMP_PRICES} (no direct `tbl_price_static` / `tbl_dom_master` on this route).
     *
     * @return array{lastUpdated?:string,status:bool,message:string,data:array}
     */
    public static function pricesIndia(Request $request)
    {
        $access = self::resolveAccess($request);
        $conn = DB::connection('mysql');
        $marketFilter = trim((string) $request->query('market', $request->input('market', '')));

        try {
            $rows = self::fetchIndiaDomesticRows($conn, $request, $access);
        } catch (Exception $e) {
            return [
                'status' => false,
                'message' => 'India prices could not be loaded: '.$e->getMessage(),
                'data' => [],
            ];
        }

        $filtered = [];
        foreach ($rows as $r) {
            $r = self::dbRowToObject($r);
            if ($marketFilter !== '') {
                $hay = strtolower(
                    self::marketLabelFromCommodityScope((int) $r->commodity_id, $access['app_type'])
                    .' '.(isset($r->commodity_label) ? (string) $r->commodity_label : '')
                    .' '.(isset($r->sub_commodity_label) ? (string) $r->sub_commodity_label : '')
                    .' '.(isset($r->title) ? (string) $r->title : '')
                );
                if (strpos($hay, strtolower($marketFilter)) === false) {
                    continue;
                }
            }
            $filtered[] = $r;
        }

        $lastTs = null;
        $byMarket = [];
        foreach ($filtered as $r) {
            if (isset($r->published_sort) && $r->published_sort) {
                $ts = strtotime((string) $r->published_sort);
                if ($ts !== false && ($lastTs === null || $ts > $lastTs)) {
                    $lastTs = $ts;
                }
            }
            $mk = self::marketLabelFromCommodityScope((int) $r->commodity_id, $access['app_type']);
            $comm = (isset($r->commodity_label) && (string) $r->commodity_label !== '')
                ? (string) $r->commodity_label
                : 'Commodity '.(int) $r->commodity_id;
            $sub = (isset($r->sub_commodity_label) && (string) $r->sub_commodity_label !== '')
                ? (string) $r->sub_commodity_label
                : 'General';
            if (!isset($byMarket[$mk])) {
                $byMarket[$mk] = [];
            }
            if (!isset($byMarket[$mk][$comm])) {
                $byMarket[$mk][$comm] = [];
            }
            if (!isset($byMarket[$mk][$comm][$sub])) {
                $byMarket[$mk][$comm][$sub] = [];
            }
            $byMarket[$mk][$comm][$sub][] = $r;
        }

        $data = [];
        $marketIndex = 0;
        foreach ($byMarket as $marketName => $commodities) {
            ++$marketIndex;
            $items = [];
            $commodityIndex = 0;
            $assessmentCount = 0;
            foreach ($commodities as $commodityName => $subs) {
                ++$commodityIndex;
                $subItems = [];
                $subIndex = 0;
                foreach ($subs as $subName => $listRows) {
                    ++$subIndex;
                    $list = [];
                    foreach ($listRows as $row) {
                        ++$assessmentCount;
                        $list[] = self::mapTempPriceRowToCommodityPriceItem($row);
                    }
                    $subItems[] = [
                        'id' => (int) (abs(crc32($marketName.'|'.$commodityName.'|'.$subName)) % 2147480000) ?: $subIndex,
                        'sub_commodity' => $subName,
                        'list' => $list,
                    ];
                }
                $items[] = [
                    'id' => (int) (abs(crc32($marketName.'|'.$commodityName)) % 2147480000) ?: $commodityIndex,
                    'commodity' => $commodityName,
                    'sub_items' => $subItems,
                ];
            }
            $data[] = [
                'id' => $marketIndex,
                'market' => $marketName,
                'assessments' => $assessmentCount,
                'items' => $items,
            ];
        }

        $lastUpdated = $lastTs !== null
            ? date('c', $lastTs)
            : date('c');

        return [
            'lastUpdated' => $lastUpdated,
            'status' => true,
            'message' => 'India prices updates fetched successfully',
            'data' => $data,
        ];
    }

    /**
     * @param \Illuminate\Database\Connection $conn
     * @param array{memid:int,authenticated:bool,app_type:string,access_level:string} $access
     *
     * @return array
     */
    private static function fetchIndiaDomesticRows($conn, Request $request, array $access)
    {
        $limit = (int) self::PRICES_INDIA_ROW_LIMIT;
        $token = self::selectedMarketToken($request);
        $t = self::TABLE_TEMP_PRICES;
        $where = ["t.`f_trade_code` = 'f'"];
        $bind = [];
        if ($access['app_type'] === 'CM') {
            $where[] = 't.`commodity_id` = 401';
        }
        if ($token !== '') {
            $where[] = 'FIND_IN_SET(?, IFNULL(t.`f_market`, \'\')) > 0';
            $bind[] = $token;
        }
        if (!$access['authenticated']) {
            $where[] = "t.`access_level` = 'guest_ok'";
        }
        $whereSql = implode(' AND ', $where);

        return $conn->select(
            'SELECT t.* FROM `'.$t.'` t WHERE '.$whereSql.' ORDER BY t.`published_sort` DESC LIMIT '.$limit,
            $bind
        );
    }

    /**
     * Flattened price row for list UIs — same shape as `GET v1/commodities/{id}/prices` sandbox `items[]`.
     *
     * @param array|\stdClass $row Row from {@see self::TABLE_TEMP_PRICES}
     *
     * @return array
     */
    private static function mapTempPriceRowToCommodityPriceItem($row)
    {
        $r = self::dbRowToObject($row);
        $trade = 'domestic';
        if (isset($r->f_trade_code) && $r->f_trade_code === 'cnf') {
            $trade = 'import';
        } elseif (isset($r->f_trade_code) && $r->f_trade_code === 'fob') {
            $trade = 'export';
        }
        $chg = isset($r->change_raw) ? (string) $r->change_raw : '';
        $arrow = 'flat';
        if ($chg !== '' && ($chg[0] === '+' || $chg[0] === '-')) {
            $arrow = $chg[0] === '-' ? 'down' : 'up';
        }
        $rid = (int) $r->relation_id;

        return [
            'assesment_id' => $rid,
            'commodity' => isset($r->commodity_label) ? (string) $r->commodity_label : '',
            'sub_commodity' => isset($r->sub_commodity_label) ? (string) $r->sub_commodity_label : '',
            'price' => isset($r->price) ? (string) $r->price : '',
            'currency' => isset($r->currency) ? (string) $r->currency : '',
            'trade_type' => $trade,
            'change' => $chg,
            'change_percent' => '',
            'arrow' => $arrow,
            'title' => isset($r->title) ? (string) $r->title : '',
            'size_grade' => isset($r->size_grade) ? (string) $r->size_grade : '',
            'published_date' => (isset($r->published_sort) && $r->published_sort) ? date('Y-m-d', strtotime((string) $r->published_sort)) : '',
            'frequency' => 'Daily',
            'access_level' => isset($r->access_level) ? (string) $r->access_level : 'member_only',
            'url' => '/price/'.$rid,
        ];
    }

    /**
     * @return array{status:bool,message:string,data:array}
     */
    public static function commoditiesPrices(Request $request, $commodityId, $selectedCurrency, $selectedCategory, $selectedRegion)
    {
        $access = self::resolveAccess($request);
        list($page, $perPage, $offset) = self::paginateParams($request);
        $q = trim((string) $request->query('q', $request->input('q', '')));

        $conn = DB::connection('mysql');
        $t = self::TABLE_TEMP_PRICES;
        $cid = (int) $commodityId;
        $selectedMarket = self::selectedMarketToken($request);

        try {
            $where = ['1=1'];
            $bind = [];
            $where[] = '(t.`commodity_id` = ? OR t.`sub_commodity_id` = ? OR IFNULL(t.`sub_sub_commodity_id`, 0) = ?)';
            $bind[] = $cid;
            $bind[] = $cid;
            $bind[] = $cid;

            if ($access['app_type'] === 'CM') {
                $where[] = 't.`commodity_id` = 401';
            }
            if ($selectedMarket !== '') {
                $where[] = 'FIND_IN_SET(?, IFNULL(t.`f_market`, \'\')) > 0';
                $bind[] = $selectedMarket;
            }

            if (!$access['authenticated']) {
                $where[] = 't.`access_level` = ?';
                $bind[] = 'guest_ok';
            }
            if ($selectedCurrency && $selectedCurrency !== 'ALL') {
                $where[] = 't.`currency` = ?';
                $bind[] = $selectedCurrency;
            }
            if ($selectedCategory === 'DOM') {
                $where[] = "t.`f_trade_code` = 'f'";
            } elseif ($selectedCategory === 'IMP') {
                $where[] = "t.`f_trade_code` = 'cnf'";
            } elseif ($selectedCategory === 'EXP') {
                $where[] = "t.`f_trade_code` = 'fob'";
            }
            if ($selectedRegion === 'INDIA') {
                $where[] = "t.`f_trade_code` = 'f'";
            } elseif ($selectedRegion === 'INTL' || $selectedRegion === 'CHINA') {
                $where[] = "(t.`f_trade_code` IN ('cnf','fob'))";
            }
            if ($q !== '') {
                $where[] = 't.`title_search` LIKE ?';
                $norm = function_exists('mb_strtolower') ? mb_strtolower($q, 'UTF-8') : strtolower($q);
                $bind[] = '%'.str_replace(['%', '_'], ['\\%', '\\_'], $norm).'%';
            }

            $whereSql = implode(' AND ', $where);
            $countRow = self::dbRowToObject($conn->selectOne('SELECT COUNT(*) AS c FROM `'.$t.'` t WHERE '.$whereSql, $bind));
            $total = (int) $countRow->c;

            $listBind = $bind;
            $listBind[] = (int) $perPage;
            $listBind[] = (int) $offset;
            $rows = $conn->select(
                'SELECT t.* FROM `'.$t.'` t WHERE '.$whereSql.' ORDER BY t.`published_sort` DESC LIMIT ? OFFSET ?',
                $listBind
            );
        } catch (Exception $e) {
            return [
                'status' => false,
                'message' => self::missingTableMessage($t).' ('.$e->getMessage().')',
                'data' => ['error' => 'missing_or_invalid_table', 'temp_table' => $t],
            ];
        }

        $items = [];
        foreach ($rows as $r) {
            $items[] = self::mapTempPriceRowToCommodityPriceItem($r);
        }

        $lastPage = max(1, (int) ceil($total / $perPage));

        $appliedFilters = [
            'commodity_id' => $cid,
            'app_type' => $access['app_type'],
            'v2019_coal_scope' => $access['app_type'] === 'CM',
        ];
        if (self::filterProvided($request, 'currency')) {
            $appliedFilters['currency'] = $selectedCurrency;
        }
        if (self::filterProvided($request, 'category')) {
            $appliedFilters['category'] = $selectedCategory;
        }
        if (self::filterProvided($request, 'region')) {
            $appliedFilters['region'] = $selectedRegion;
        }
        if ($q !== '') {
            $appliedFilters['q'] = $q;
        }
        if ($selectedMarket !== '') {
            $appliedFilters['selected_market'] = $selectedMarket;
        }

        return [
            'status' => true,
            'message' => 'Price list fetched successfully',
            'data' => [
                'meta' => [
                    'access' => [
                        'level' => $access['access_level'],
                        'memid' => $access['memid'],
                        'app_type' => $access['app_type'],
                    ],
                    'pagination' => [
                        'current_page' => $page,
                        'per_page' => $perPage,
                        'total' => $total,
                        'last_page' => $lastPage,
                    ],
                    'applied_filters' => $appliedFilters,
                ],
                'filters' => self::priceFilterUi($request, $selectedCurrency, $selectedCategory, $selectedRegion),
                'market' => self::displayMarketLabel($request, $access['app_type'], $cid),
                'items' => $items,
                'temp_table' => self::TABLE_TEMP_PRICES,
                'import_sources' => [
                    'tbl_dom_master',
                    'tbl_price_static',
                    'tbl_exim_master',
                    'tbl_region_commodity_master',
                ],
            ],
        ];
    }

    private static function priceFilterUi(Request $request, $selectedCurrency, $selectedCategory, $selectedRegion)
    {
        return [
            'currency' => [
                'selected' => self::filterProvided($request, 'currency') ? $selectedCurrency : null,
                'options' => [
                    ['code' => 'INR', 'name' => 'Indian Rupee', 'symbol' => '₹'],
                    ['code' => 'USD', 'name' => 'US Dollar', 'symbol' => '$'],
                    ['code' => 'EUR', 'name' => 'Euro', 'symbol' => '€'],
                    ['code' => 'GBP', 'name' => 'British Pound', 'symbol' => '£'],
                    ['code' => 'JPY', 'name' => 'Japanese Yen', 'symbol' => '¥'],
                    ['code' => 'CNY', 'name' => 'Chinese Yuan', 'symbol' => '¥'],
                ],
            ],
            'category' => [
                'selected' => self::filterProvided($request, 'category') ? $selectedCategory : null,
                'options' => [
                    ['key' => 'ALL', 'label' => 'All Categories'],
                    ['key' => 'DOM', 'label' => 'Domestic'],
                    ['key' => 'IMP', 'label' => 'Import'],
                    ['key' => 'EXP', 'label' => 'Export'],
                ],
            ],
            'region' => [
                'selected' => self::filterProvided($request, 'region') ? $selectedRegion : null,
                'options' => [
                    ['key' => 'ALL', 'label' => 'All Regions'],
                    ['key' => 'INDIA', 'label' => 'India', 'description' => 'Domestic markets'],
                    ['key' => 'INTL', 'label' => 'International', 'description' => 'Import & global'],
                    ['key' => 'CHINA', 'label' => 'China'],
                ],
            ],
        ];
    }

    /**
     * @return array{status:bool,message:string,data:array}
     */
    public static function newsFeatured(Request $request)
    {
        $access = self::resolveAccess($request);
        list($page, $perPage, $offset) = self::paginateParams($request);
        $commodityFilter = (int) $request->query('commodity_id', $request->input('commodity_id', 0));
        $q = trim((string) $request->query('q', $request->input('q', '')));
        $selectedMarket = self::selectedMarketToken($request);
        $selectedSlug = self::normalizeMarketSlugToken($selectedMarket);

        $conn = DB::connection('mysql');
        $t = self::TABLE_TEMP_NEWS;

        try {
            $where = ['1=1'];
            $bind = [];
            if (! $access['authenticated']) {
                $where[] = 't.`access_level` = ?';
                $bind[] = 'guest_ok';
            }
            if ($commodityFilter > 0) {
                $where[] = 't.`commodity_id` = ?';
                $bind[] = $commodityFilter;
            }
            if ($q !== '') {
                $where[] = 't.`title_search` LIKE ?';
                $norm = function_exists('mb_strtolower') ? mb_strtolower($q, 'UTF-8') : strtolower($q);
                $bind[] = '%'.str_replace(['%', '_'], ['\\%', '\\_'], $norm).'%';
            }
            if ($selectedSlug !== '') {
                $cmIds = self::commodityIdsForMarketSlug($selectedSlug);
                if ($cmIds !== []) {
                    $inPh = implode(',', array_fill(0, count($cmIds), '?'));
                    $where[] = '(FIND_IN_SET(?, IFNULL(m.`f_market`, \'\')) > 0 OR t.`commodity_id` IN ('.$inPh.'))';
                    $bind[] = $selectedSlug;
                    foreach ($cmIds as $id) {
                        $bind[] = $id;
                    }
                } else {
                    $where[] = 'FIND_IN_SET(?, IFNULL(m.`f_market`, \'\')) > 0';
                    $bind[] = $selectedSlug;
                }
            }

            $whereSql = implode(' AND ', $where);
            $joinSql = 'LEFT JOIN `tbl_region_commodity_master` m ON m.`id` = t.`commodity_id` AND LOWER(m.`itemType`) = \'commodity\'';

            $countRow = self::dbRowToObject($conn->selectOne(
                'SELECT COUNT(*) AS c FROM `'.$t.'` t '.$joinSql.' WHERE '.$whereSql,
                $bind
            ));
            $total = (int) $countRow->c;

            $listBind = $bind;
            $listBind[] = (int) $perPage;
            $listBind[] = (int) $offset;
            $rows = $conn->select(
                'SELECT t.*, IFNULL(m.`f_market`, \'\') AS `commodity_f_market` FROM `'.$t.'` t '.$joinSql.' WHERE '.$whereSql.' ORDER BY t.`post_date` DESC LIMIT ? OFFSET ?',
                $listBind
            );
        } catch (Exception $e) {
            return [
                'status' => false,
                'message' => self::missingTableMessage($t).' ('.$e->getMessage().')',
                'data' => ['error' => 'missing_or_invalid_table', 'temp_table' => $t],
            ];
        }

        $buckets = [];
        foreach ($rows as $r) {
            $r = self::dbRowToObject($r);
            $pd = $r->post_date ? date('d M H:i', strtotime($r->post_date)).' IST' : '';
            $item = [
                'news_id' => (int) $r->post_id,
                'news_type' => (string) $r->news_type,
                'commodity' => (string) $r->sub_label,
                'sub_commodity' => '',
                'title' => (string) $r->post_title,
                'thumbnail_image' => (string) $r->thumb,
                'published_date' => $pd,
                'read_time' => '3 min read',
                'detail_link' => (string) $r->href,
                'access_level' => (string) $r->access_level,
            ];
            $fm = isset($r->commodity_f_market) ? (string) $r->commodity_f_market : '';
            if ($selectedSlug !== '') {
                $slugs = [$selectedSlug];
            } else {
                $slugs = self::resolvedMarketSlugsForNewsRow($fm, (int) $r->commodity_id, $access['app_type']);
            }
            foreach ($slugs as $slug) {
                $slug = self::normalizeMarketSlugToken($slug);
                if ($slug === '') {
                    continue;
                }
                if (! isset($buckets[$slug])) {
                    $buckets[$slug] = [
                        'market_id' => self::marketNumericIdForSlug($slug),
                        'market_slug' => $slug,
                        'market' => self::displayLabelForMarketSlug($slug),
                        'view_all_link' => null,
                        'items' => [],
                    ];
                }
                $buckets[$slug]['items'][] = $item;
            }
        }

        $allMarkets = self::orderedNewsMarketBuckets($buckets);

        $lastPage = max(1, (int) ceil($total / $perPage));

        return [
            'status' => true,
            'message' => 'Featured news fetched successfully',
            'data' => [
                'tab_type' => 'featured',
                'meta' => [
                    'access' => [
                        'level' => $access['access_level'],
                        'memid' => $access['memid'],
                        'app_type' => $access['app_type'],
                    ],
                    'pagination' => [
                        'current_page' => $page,
                        'per_page' => $perPage,
                        'total' => $total,
                        'last_page' => $lastPage,
                    ],
                    'applied_filters' => [
                        'commodity_id' => $commodityFilter,
                        'q' => $q,
                        'selected_market' => $selectedMarket !== '' ? $selectedMarket : null,
                        'app_type' => $access['app_type'],
                    ],
                ],
                'all_markets' => $allMarkets,
                'temp_table' => self::TABLE_TEMP_NEWS,
                'import_sources' => [
                    'tbl_news_post',
                    'tbl_region_commodity_master',
                ],
            ],
        ];
    }

    /**
     * News list for a route commodity id (staging `tbl_temp_v2026_news`).
     * Staging rows do not store industries/category/region/Language — those filters are echoed for UI only.
     *
     * @param int $commodityId from `/commodities/{id}/news`
     *
     * @return array{status:bool,message:string,data:array}
     */
    public static function commoditiesNews(Request $request, $commodityId)
    {
        $access = self::resolveAccess($request);
        list($page, $perPage, $offset) = self::paginateParams($request);
        $q = trim((string) $request->query('q', $request->input('q', '')));
        $cid = (int) $commodityId;

        $selectedIndustry = strtolower((string) $request->query('industries', $request->input('industries', 'all')));
        $selectedCategory = (string) $request->query('category', $request->input('category', 'ALL'));
        $selectedRegion = (string) $request->query('region', $request->input('region', 'ALL'));
        $selectedLanguage = (string) $request->query('Language', $request->input('Language', 'ALL'));

        $conn = DB::connection('mysql');
        $t = self::TABLE_TEMP_NEWS;

        try {
            $where = ['1=1'];
            $bind = [];
            if (!$access['authenticated']) {
                $where[] = 't.`access_level` = ?';
                $bind[] = 'guest_ok';
            }
            $where[] = '(t.`commodity_id` = ? OR t.`commodity_id` IN (SELECT `id` FROM `tbl_region_commodity_master` WHERE `itemType` = \'commodity\' AND `parentID` = ?))';
            $bind[] = $cid;
            $bind[] = $cid;
            if ($q !== '') {
                $where[] = 't.`title_search` LIKE ?';
                $norm = function_exists('mb_strtolower') ? mb_strtolower($q, 'UTF-8') : strtolower($q);
                $bind[] = '%'.str_replace(['%', '_'], ['\\%', '\\_'], $norm).'%';
            }

            $whereSql = implode(' AND ', $where);
            $countRow = self::dbRowToObject($conn->selectOne('SELECT COUNT(*) AS c FROM `'.$t.'` t WHERE '.$whereSql, $bind));
            $total = (int) $countRow->c;

            $listBind = $bind;
            $listBind[] = (int) $perPage;
            $listBind[] = (int) $offset;
            $rows = $conn->select(
                'SELECT t.* FROM `'.$t.'` t WHERE '.$whereSql.' ORDER BY t.`post_date` DESC LIMIT ? OFFSET ?',
                $listBind
            );
        } catch (Exception $e) {
            return [
                'status' => false,
                'message' => self::missingTableMessage($t).' ('.$e->getMessage().')',
                'data' => ['error' => 'missing_or_invalid_table', 'temp_table' => $t],
            ];
        }

        $items = [];
        foreach ($rows as $r) {
            $r = self::dbRowToObject($r);
            $pd = $r->post_date ? date('d M H:i', strtotime($r->post_date)).' IST' : '';
            $items[] = [
                'news_id' => (int) $r->post_id,
                'news_type' => (string) $r->news_type,
                'commodity' => (string) $r->sub_label,
                'sub_commodity' => '',
                'title' => (string) $r->post_title,
                'thumbnail_image' => (string) $r->thumb,
                'published_date' => $pd,
                'read_time' => '3 min read',
                'detail_link' => (string) $r->href,
                'access_level' => (string) $r->access_level,
            ];
        }

        $lastPage = max(1, (int) ceil($total / $perPage));
        $industryLabelMap = [
            'all' => 'All Industries',
            'auto' => 'Auto',
            'construction' => 'Construction',
            'mining' => 'Mining',
            'infrastructure' => 'Infrastructure',
        ];

        return [
            'status' => true,
            'message' => 'News list fetched successfully',
            'data' => [
                'filters' => [
                    'industries' => [
                        'selected' => isset($industryLabelMap[$selectedIndustry]) ? $industryLabelMap[$selectedIndustry] : 'All Industries',
                        'options' => [
                            ['code' => 'all', 'name' => 'All Industries'],
                            ['code' => 'auto', 'name' => 'Auto'],
                            ['code' => 'construction', 'name' => 'Construction'],
                            ['code' => 'mining', 'name' => 'Mining'],
                            ['code' => 'infrastructure', 'name' => 'Infrastructure'],
                        ],
                    ],
                    'category' => [
                        'selected' => $selectedCategory,
                        'options' => [
                            ['key' => 'intel', 'label' => 'Intel', 'description' => 'Market intelligence'],
                            ['key' => 'DOM', 'label' => 'Analysis', 'description' => 'Expert analysis'],
                            ['key' => 'IMP', 'label' => 'Featured', 'description' => 'Editor picks'],
                            ['key' => 'EXP', 'label' => 'Insights', 'description' => 'Deep dives'],
                        ],
                    ],
                    'region' => [
                        'selected' => $selectedRegion,
                        'options' => [
                            ['key' => 'ALL', 'label' => 'All Regions'],
                            ['key' => 'INDIA', 'label' => 'India', 'description' => 'Domestic markets'],
                            ['key' => 'INTL', 'label' => 'International', 'description' => 'Import & global'],
                            ['key' => 'CHINA', 'label' => 'China'],
                        ],
                    ],
                    'Language' => [
                        'selected' => $selectedLanguage,
                        'options' => [
                            ['key' => 'hindi', 'label' => 'Hindi', 'description' => 'हिन्दी'],
                            ['key' => 'Bengali', 'label' => 'Bengali', 'description' => 'বাংলা'],
                        ],
                    ],
                ],
                'market' => self::displayMarketLabel($request, $access['app_type'], $cid),
                'items' => $items,
                'meta' => [
                    'access' => [
                        'level' => $access['access_level'],
                        'memid' => $access['memid'],
                        'app_type' => $access['app_type'],
                    ],
                    'pagination' => [
                        'current_page' => $page,
                        'per_page' => $perPage,
                        'total' => $total,
                        'last_page' => $lastPage,
                    ],
                ],
                'temp_table' => self::TABLE_TEMP_NEWS,
                'import_sources' => [
                    'tbl_news_post',
                    'tbl_region_commodity_master',
                ],
            ],
        ];
    }

    /**
     * Report list for a route commodity id (staging `tbl_temp_v2026_reports`).
     * Staging rows do not drive industries / region / Language filters — those stay UI-only like news.
     * `category` (intel, DOM, IMP, EXP) filters on synthetic `ui_category` for sandbox variety.
     *
     * @param int $commodityId from `/commodities/{id}/reports`
     *
     * @return array{status:bool,message:string,data:array}
     */
    public static function commoditiesReports(Request $request, $commodityId)
    {
        $access = self::resolveAccess($request);
        list($page, $perPage, $offset) = self::paginateParams($request);
        $q = trim((string) $request->query('q', $request->input('q', '')));
        $cid = (int) $commodityId;

        $selectedIndustry = strtolower((string) $request->query('industries', $request->input('industries', 'all')));
        $selectedCategory = (string) $request->query('category', $request->input('category', 'ALL'));
        $selectedRegion = (string) $request->query('region', $request->input('region', 'ALL'));
        $selectedLanguage = (string) $request->query('Language', $request->input('Language', 'ALL'));

        $conn = DB::connection('mysql');
        $t = self::TABLE_TEMP_REPORTS;

        try {
            $where = ['1=1'];
            $bind = [];
            if (!$access['authenticated']) {
                $where[] = 't.`access_level` = ?';
                $bind[] = 'guest_ok';
            }
            $where[] = '(t.`commodity_id` = ? OR t.`sub_commodity_id` = ? OR t.`commodity_id` IN (SELECT `id` FROM `tbl_region_commodity_master` WHERE `itemType` = \'commodity\' AND `parentID` = ?))';
            $bind[] = $cid;
            $bind[] = $cid;
            $bind[] = $cid;
            if ($access['app_type'] === 'CM') {
                $where[] = 't.`commodity_id` = 401';
            }
            if ($selectedCategory !== '' && strtoupper($selectedCategory) !== 'ALL') {
                $where[] = 't.`ui_category` = ?';
                $bind[] = strtolower($selectedCategory);
            }
            if ($q !== '') {
                $where[] = 't.`title_search` LIKE ?';
                $norm = function_exists('mb_strtolower') ? mb_strtolower($q, 'UTF-8') : strtolower($q);
                $bind[] = '%'.str_replace(['%', '_'], ['\\%', '\\_'], $norm).'%';
            }

            $whereSql = implode(' AND ', $where);
            $countRow = self::dbRowToObject($conn->selectOne('SELECT COUNT(*) AS c FROM `'.$t.'` t WHERE '.$whereSql, $bind));
            $total = (int) $countRow->c;

            $listBind = $bind;
            $listBind[] = (int) $perPage;
            $listBind[] = (int) $offset;
            $rows = $conn->select(
                'SELECT t.* FROM `'.$t.'` t WHERE '.$whereSql.' ORDER BY t.`report_sort` DESC LIMIT ? OFFSET ?',
                $listBind
            );
        } catch (Exception $e) {
            return [
                'status' => false,
                'message' => self::missingTableMessage($t).' ('.$e->getMessage().')',
                'data' => ['error' => 'missing_or_invalid_table', 'temp_table' => $t],
            ];
        }

        $items = [];
        foreach ($rows as $r) {
            $r = self::dbRowToObject($r);
            $items[] = [
                'commodity' => (string) $r->commodity_label,
                'sub_commodity' => (string) $r->sub_commodity_label,
                'report_id' => (int) $r->report_id,
                'title' => (string) $r->title,
                'detail_link' => (string) $r->detail_href,
                'date' => (string) $r->date_display,
                'features' => [
                    'ai_analysis_enabled' => true,
                    'ai_label' => 'AI Analysis & Summarization',
                ],
                'actions' => [
                    'archives' => [
                        'label' => 'Archives',
                        'icon' => 'eye_icon',
                    ],
                    'share' => [
                        'label' => 'Share',
                        'icon' => 'share_nodes_icon',
                        'url' => 'https://www.bigmint.co/share/report/'.(int) $r->report_id,
                    ],
                    'save' => [
                        'label' => 'Save',
                        'icon' => 'bookmark_icon',
                        'is_saved' => false,
                    ],
                ],
            ];
        }

        $lastPage = max(1, (int) ceil($total / $perPage));
        $industryLabelMap = [
            'all' => 'All Industries',
            'auto' => 'Auto',
            'construction' => 'Construction',
            'mining' => 'Mining',
            'infrastructure' => 'Infrastructure',
        ];

        return [
            'status' => true,
            'message' => 'Report list fetched successfully',
            'data' => [
                'filters' => [
                    'industries' => [
                        'selected' => isset($industryLabelMap[$selectedIndustry]) ? $industryLabelMap[$selectedIndustry] : 'All Industries',
                        'options' => [
                            ['code' => 'all', 'name' => 'All Industries'],
                            ['code' => 'auto', 'name' => 'Auto'],
                            ['code' => 'construction', 'name' => 'Construction'],
                            ['code' => 'mining', 'name' => 'Mining'],
                            ['code' => 'infrastructure', 'name' => 'Infrastructure'],
                        ],
                    ],
                    'category' => [
                        'selected' => $selectedCategory,
                        'options' => [
                            ['key' => 'intel', 'label' => 'Intel', 'description' => 'Market intelligence'],
                            ['key' => 'DOM', 'label' => 'Analysis', 'description' => 'Expert analysis'],
                            ['key' => 'IMP', 'label' => 'Featured', 'description' => 'Editor picks'],
                            ['key' => 'EXP', 'label' => 'Insights', 'description' => 'Deep dives'],
                        ],
                    ],
                    'region' => [
                        'selected' => $selectedRegion,
                        'options' => [
                            ['key' => 'ALL', 'label' => 'All Regions'],
                            ['key' => 'INDIA', 'label' => 'India', 'description' => 'Domestic markets'],
                            ['key' => 'INTL', 'label' => 'International', 'description' => 'Import & global'],
                            ['key' => 'CHINA', 'label' => 'China'],
                        ],
                    ],
                    'Language' => [
                        'selected' => $selectedLanguage,
                        'options' => [
                            ['key' => 'hindi', 'label' => 'Hindi', 'description' => 'हिन्दी'],
                            ['key' => 'Bengali', 'label' => 'Bengali', 'description' => 'বাংলা'],
                        ],
                    ],
                ],
                'market' => self::displayMarketLabel($request, $access['app_type'], $cid),
                'items' => $items,
                'meta' => [
                    'access' => [
                        'level' => $access['access_level'],
                        'memid' => $access['memid'],
                        'app_type' => $access['app_type'],
                    ],
                    'pagination' => [
                        'current_page' => $page,
                        'per_page' => $perPage,
                        'total' => $total,
                        'last_page' => $lastPage,
                    ],
                ],
                'temp_table' => self::TABLE_TEMP_REPORTS,
                'import_sources' => [
                    'tbl_steelreport',
                    'tbl_steelreports_category',
                    'tbl_region_commodity_master',
                ],
            ],
        ];
    }

    /**
     * @return array{status:bool,message:string,data:array}
     */
    public static function tendersRecent(Request $request)
    {
        $listing = self::tendersListing($request, null);
        if (empty($listing['status'])) {
            return [
                'status' => false,
                'message' => $listing['message'],
                'data' => [],
                'temp_table' => self::TABLE_TEMP_TENDERS,
            ];
        }

        return [
            'status' => true,
            'message' => $listing['message'],
            'data' => $listing['data']['items'],
            'temp_table' => self::TABLE_TEMP_TENDERS,
            'import_sources' => ['tbl_tenders', 'tbl_tenders_region'],
            'meta' => $listing['data']['meta'],
        ];
    }

    /**
     * @return array{success:bool,message:string,data:array}
     */
    public static function commoditiesTenders(Request $request, $commodityId, $filterType)
    {
        $listing = self::tendersListing($request, (int) $commodityId, $filterType);
        if (empty($listing['status'])) {
            return [
                'success' => false,
                'message' => $listing['message'],
                'data' => isset($listing['data']) ? $listing['data'] : [],
            ];
        }

        $items = isset($listing['data']['items']) ? $listing['data']['items'] : [];
        $meta = isset($listing['data']['meta']) ? $listing['data']['meta'] : [];
        $total = isset($meta['pagination']['total']) ? $meta['pagination']['total'] : count($items);
        $activeFilter = $filterType ? $filterType : 'ALL';
        $conn = DB::connection('mysql');
        $cmdtyLabel = self::commodityDisplayName($conn, (int) $commodityId);
        if ($cmdtyLabel === '') {
            $cmdtyLabel = (string) $commodityId;
        }
        $appType = isset($meta['access']['app_type']) ? $meta['access']['app_type'] : 'SM';

        return [
            'success' => true,
            'message' => $listing['message'],
            'data' => [
                'temp_table' => self::TABLE_TEMP_TENDERS,
                'import_sources' => ['tbl_tenders', 'tbl_tenders_region'],
                'meta' => $meta,
                'filter_type' => [
                    'active_value' => $activeFilter,
                    'options' => ['ALL', 'Buy', 'Sell', 'Services'],
                ],
                'market' => self::displayMarketLabel($request, $appType, (int) $commodityId),
                'commodity' => [
                    'id' => (string) $commodityId,
                    'label' => $cmdtyLabel,
                    'total_count' => $total,
                    'icon' => 'layers_icon',
                    'is_expanded' => true,
                    'sub_commodity' => [
                        [
                            'id' => self::TABLE_TEMP_TENDERS,
                            'Sub_commodity' => 'All',
                            'count' => count($items),
                            'items' => $items,
                        ],
                    ],
                ],
            ],
        ];
    }

    /**
     * @param int|null $commodityId null = recent (no commodity filter on read)
     *
     * @return array{status:bool,message:string,data:array}
     */
    private static function tendersListing(Request $request, $commodityId = null, $filterType = null)
    {
        $access = self::resolveAccess($request);
        list($page, $perPage, $offset) = self::paginateParams($request);
        $q = trim((string) $request->query('q', $request->input('q', '')));
        $filterType = $filterType === null ? $request->query('filter_type', 'ALL') : $filterType;
        $selectedMarket = self::selectedMarketToken($request);

        $conn = DB::connection('mysql');
        $t = self::TABLE_TEMP_TENDERS;

        try {
            $whereRead = ['1=1'];
            $bindRead = [];
            if ($commodityId !== null && (int) $commodityId > 0) {
                $cid = (int) $commodityId;
                $whereRead[] = '(t.`commodity_id` = ? OR t.`sub_commodity_id` = ? OR IFNULL(t.`sub_sub_commodity_id`, 0) = ?)';
                $bindRead[] = $cid;
                $bindRead[] = $cid;
                $bindRead[] = $cid;
            }
            if ($access['app_type'] === 'CM') {
                $whereRead[] = 't.`commodity_id` = 401';
            }
            if ($selectedMarket !== '') {
                $sub = '(SELECT `id` FROM `tbl_region_commodity_master` WHERE LOWER(`itemType`) = \'commodity\' AND FIND_IN_SET(?, IFNULL(`f_market`, \'\')))';
                $whereRead[] = '(t.`commodity_id` IN '.$sub.' OR t.`sub_commodity_id` IN '.$sub.' OR IFNULL(t.`sub_sub_commodity_id`, 0) IN '.$sub.')';
                $bindRead[] = $selectedMarket;
                $bindRead[] = $selectedMarket;
                $bindRead[] = $selectedMarket;
            }
            if (!$access['authenticated']) {
                $whereRead[] = 't.`access_level` = ?';
                $bindRead[] = 'guest_ok';
            }
            if ($filterType && strtoupper((string) $filterType) !== 'ALL') {
                $whereRead[] = 't.`buy_sell` = ?';
                $bindRead[] = (string) $filterType;
            }
            if ($q !== '') {
                $whereRead[] = 't.`title_search` LIKE ?';
                $norm = function_exists('mb_strtolower') ? mb_strtolower($q, 'UTF-8') : strtolower($q);
                $bindRead[] = '%'.str_replace(['%', '_'], ['\\%', '\\_'], $norm).'%';
            }

            $whereReadSql = implode(' AND ', $whereRead);
            $countRow = self::dbRowToObject($conn->selectOne('SELECT COUNT(*) AS c FROM `'.$t.'` t WHERE '.$whereReadSql, $bindRead));
            $total = (int) $countRow->c;

            $listBind = $bindRead;
            $listBind[] = (int) $perPage;
            $listBind[] = (int) $offset;
            $rows = $conn->select(
                'SELECT t.* FROM `'.$t.'` t WHERE '.$whereReadSql.' ORDER BY t.`due_sort` ASC LIMIT ? OFFSET ?',
                $listBind
            );
        } catch (Exception $e) {
            return [
                'status' => false,
                'message' => self::missingTableMessage($t).' ('.$e->getMessage().')',
                'data' => ['error' => 'missing_or_invalid_table', 'temp_table' => $t],
            ];
        }

        $items = [];
        foreach ($rows as $r) {
            $r = self::dbRowToObject($r);
            $tag = strtoupper(trim((string) $r->buy_sell));
            if ($tag === '') {
                $tag = 'TENDER';
            }
            $items[] = [
                'tender_id' => (int) $r->tender_id,
                'title' => (string) $r->title,
                'type_tag' => $tag,
                'closes_in' => '',
                'quantity' => trim((string) $r->qty_label),
                'location' => (string) $r->region_label,
                'auction_type' => (string) $r->offer_type !== '' ? (string) $r->offer_type : 'Tender',
                'base_price' => (string) $r->floor_price !== '' ? (string) $r->floor_price : '',
                'time_elapsed_percentage' => 0,
                'closing_date' => $r->due_sort ? date('d M', strtotime($r->due_sort)) : '',
                'redirect_link' => 'https://example.com/tenders/'.(int) $r->tender_id,
                'access_level' => (string) $r->access_level,
            ];
        }

        $lastPage = max(1, (int) ceil($total / $perPage));

        $meta = [
            'access' => [
                'level' => $access['access_level'],
                'memid' => $access['memid'],
                'app_type' => $access['app_type'],
            ],
            'pagination' => [
                'current_page' => $page,
                'per_page' => $perPage,
                'total' => $total,
                'last_page' => $lastPage,
            ],
            'applied_filters' => [
                'commodity_id' => $commodityId,
                'filter_type' => $filterType,
                'q' => $q,
                'selected_market' => $selectedMarket !== '' ? $selectedMarket : null,
                'app_type' => $access['app_type'],
                'v2019_coal_scope' => $access['app_type'] === 'CM',
            ],
            'temp_table' => self::TABLE_TEMP_TENDERS,
            'import_sources' => ['tbl_tenders', 'tbl_tenders_region'],
        ];

        return [
            'status' => true,
            'message' => 'Tenders fetched successfully',
            'data' => [
                'meta' => $meta,
                'items' => $items,
            ],
        ];
    }
}
