<?php
date_default_timezone_set('Asia/Kolkata');
include('/home/steelmin/public_html/inc/config_pdo.php');

function insertVisitedUrlData($externalData) {
    global $conn; // Use the globally included PDO connection

    // 1. Prepare the data for insertion
    $_data = [
        'memid' => $externalData['memid'] ?? '',  // Extract 'memid' from external data
        'f_url' => $externalData['f_url'] ?? '',  // Extract 'f_url' from external data
        'f_date' => $externalData['f_date'] ?? date('Y-m-d'),  // Extract 'f_date' or use current date
        'f_time' => $externalData['f_time'] ?? date('H:i:s'),  // Extract 'f_time' or use current time
        'isNewWebsite' => $externalData['isNewWebsite'] ?? 'N',  // Extract 'isNewWebsite' or default to 'N'
        'ipaddress' => $externalData['ipaddress'] ?? $_SERVER['REMOTE_ADDR'],  // Extract 'ipaddress' or use current IP
        'site_name' => $externalData['site_name'] ?? 'DefaultSite',  // Extract 'site_name' or use default
        'f_useragent' => $externalData['f_useragent'] ?? '',  // Extract 'f_useragent' from external data
        'server_variable' => $externalData['server_variable'] ?? '',  // Extract 'server_variable' from external data
        'request_variable' => $externalData['request_variable'] ?? ''  // Extract 'request_variable' from external data
    ];

    // 2. Insert the data into the table using PDO
    $stmt = $conn->prepare("INSERT INTO tbl_urls_visited_count (memid, f_url, f_date, f_time, isNewWebsite, ipaddress, site_name, f_useragent, server_variable, request_variable) VALUES (:memid, :f_url, :f_date, :f_time, :isNewWebsite, :ipaddress, :site_name, :f_useragent, :server_variable, :request_variable)");

    // Bind parameters
    $stmt->bindParam(':memid', $_data['memid'], PDO::PARAM_STR);
    $stmt->bindParam(':f_url', $_data['f_url'], PDO::PARAM_STR);
    $stmt->bindParam(':f_date', $_data['f_date'], PDO::PARAM_STR);
    $stmt->bindParam(':f_time', $_data['f_time'], PDO::PARAM_STR);
    $stmt->bindParam(':isNewWebsite', $_data['isNewWebsite'], PDO::PARAM_STR);
    $stmt->bindParam(':ipaddress', $_data['ipaddress'], PDO::PARAM_STR);
    $stmt->bindParam(':site_name', $_data['site_name'], PDO::PARAM_STR);
    $stmt->bindParam(':f_useragent', $_data['f_useragent'], PDO::PARAM_STR);
    $stmt->bindParam(':server_variable', $_data['server_variable'], PDO::PARAM_STR);
    $stmt->bindParam(':request_variable', $_data['request_variable'], PDO::PARAM_STR);

    // Execute the query
    $stmt->execute();

    // 3. Get the last inserted ID
    $insertedId = $conn->lastInsertId();

    // 4. Return the inserted ID as a JSON response
    return json_encode(['inserted_id' => $insertedId]);
}



function getLoggedinUserData($memid) {
    global $conn; // Use the globally included PDO connection

    // Prepare the SQL query to fetch the data
    $stmt = $conn->prepare("SELECT * FROM tbl_loggedin_users WHERE mem_id = :mem_id LIMIT 1");

    // Bind the memid parameter
    $stmt->bindParam(':mem_id', $memid, PDO::PARAM_STR);

    // Execute the query
    $stmt->execute();

    // Fetch the first record (if found)
    $userData = $stmt->fetch(PDO::FETCH_ASSOC);

    if ($userData) {
        // Return the user data as JSON if found
        return json_encode($userData);
    } else {
        // Return an empty array if no data is found
        return json_encode([]);
    }
}

// Handling the request if values are passed via URL parameters or a POST request
if (isset($_REQUEST['fetchUser']) && $_REQUEST['fetchUser'] == 'y' && isset($_REQUEST['memid']) && $_REQUEST['memid'] != '') {
    $memid = $_REQUEST['memid'];  // mem_id passed from the request

    header('Content-Type: application/json');
    echo getLoggedinUserData($memid);
}



if (isset($_REQUEST['updateLoggedinUser']) && $_REQUEST['updateLoggedinUser'] == 'y' && isset($_REQUEST['memid']) && $_REQUEST['memid'] != '') {
      $memid = $_REQUEST['memid'];  // mem_id passed from the request
      if (!empty($memid)) {
        // Prepare the SQL query to update the log_time
        $stmt = $conn->prepare("UPDATE tbl_loggedin_users SET log_time = :log_time WHERE mem_id = :mem_id");

        // Bind the parameters
        $stmt->bindParam(':log_time', date('Y-m-d H:i:s'), PDO::PARAM_STR);
        $stmt->bindParam(':mem_id', $memid, PDO::PARAM_STR);
        $stmt->execute();       
    }
}




// Handling the request, no need for sourceUrl to be mandatory
if (isset($_REQUEST['insertData']) && $_REQUEST['insertData'] == 'y') {
    // Fetch the data sent to the server (usually from POST)
    $externalData = json_decode(file_get_contents('php://input'), true);

    // Ensure external data exists and process the insertion
    if ($externalData) {
        header('Content-Type: application/json');
        echo insertVisitedUrlData($externalData);
    } else {
        echo json_encode(['error' => 'No data received']);
    }
}



function getEligibleData($memid) {
    
    
    global $conn; // Use the globally included PDO connection

    // Prepare the SQL query
    $stmt = $conn->prepare("SELECT * FROM tbl_special_table WHERE memid = :memid");
    
    // Bind the memid parameter
    $stmt->bindParam(':memid', $memid, PDO::PARAM_INT);
    
    // Execute the query
    $stmt->execute();
    
    // Fetch the number of rows
    $rowCount = $stmt->rowCount();
    
    if ($rowCount > 0) {
        // Fetch all data if rows are found
        $excel_data = $stmt->fetchAll(PDO::FETCH_ASSOC);
        return json_encode($excel_data); // Return data as JSON
    }
    
    return json_encode([]); // Return an empty JSON array if no rows are found
}

if(isset($_REQUEST['EligibleData']) && ( $_REQUEST['EligibleData'] =='y') && ($_REQUEST['memid'] !='')){
//$memid = 115566; // Replace with the actual memid value

$memid = $_REQUEST['memid'];
header('Content-Type: application/json');
echo getEligibleData($memid);
}





function getCategoryTitles($memid) {
    global $conn; // Use the globally included PDO connection

    // Subquery 1
    $subquery1 = "
        SELECT c1.commodityID, 
               (SELECT itemName FROM tbl_region_commodity_master mst WHERE mst.id = c1.commodityID AND itemType = 'commodity') AS cattitle
        FROM tbl_steelreport AS s1
        JOIN tbl_steelreports_category AS c1 ON s1.steelreports_category_id = c1.id
        JOIN tbl_reg_logs AS r1 ON FIND_IN_SET(r1.selected_products, c1.AccessibleWithPackage)
        WHERE r1.memid = :memid
        AND r1.f_product IN ('MA', 'W', 'C')
        AND r1.status = 'Active'
        AND r1.inv_no <> ''
        AND c1.isMonthlyOrCumulative IN ('c', 'm')
        AND (
            (c1.isMonthlyOrCumulative = 'm' AND r1.startdate <= s1.from_month_year AND r1.enddate >= s1.from_month_year) OR
            (c1.isMonthlyOrCumulative = 'c' AND r1.enddate >= s1.to_month_year AND r1.startdate <= s1.to_month_year)
        )
    ";

    // Subquery 2
    $subquery2 = "
        SELECT c2.commodityID, 
               (SELECT itemName FROM tbl_region_commodity_master mst WHERE mst.id = c2.commodityID AND itemType = 'commodity') AS cattitle
        FROM tbl_steelreport AS s2
        JOIN tbl_steelreports_category AS c2 ON s2.steelreports_category_id = c2.id
        JOIN tbl_reg_logs AS r2 ON r2.selected_products = c2.id
        WHERE r2.memid = :memid
        AND r2.f_product = 'CD'
        AND r2.cd_inv_no <> ''
        AND r2.status IN ('Active', 'Expired')
        AND c2.isMonthlyOrCumulative IN ('c', 'm')
        AND (
            (c2.isMonthlyOrCumulative = 'm' AND r2.startdate <= s2.from_month_year AND r2.enddate >= s2.from_month_year) OR
            (c2.isMonthlyOrCumulative = 'c' AND r2.enddate >= s2.to_month_year AND r2.startdate <= s2.to_month_year)
        )
    ";

    // Combine subqueries with UNION and fetch distinct results
    $sql = "
        SELECT DISTINCT * FROM ({$subquery1}) AS subquery1
        UNION
        SELECT DISTINCT * FROM ({$subquery2}) AS subquery2
    ";

    // Prepare the final query
    $stmt = $conn->prepare($sql);

    // Bind the memid parameter for both subqueries
    $stmt->bindParam(':memid', $memid, PDO::PARAM_INT);

    // Execute the query
    $stmt->execute();

    // Fetch all results
    $categoryTitles = $stmt->fetchAll(PDO::FETCH_ASSOC);

     return json_encode($categoryTitles);
}

if(isset($_REQUEST['categoryTitles']) && ( $_REQUEST['categoryTitles'] =='y') && ($_REQUEST['memid'] !='')){
    
$memid = $_REQUEST['memid'];
header('Content-Type: application/json');
echo getCategoryTitles($memid);

}



function getFirstToDateAsJson(array $params) {
    global $conn; // Use the globally included PDO connection

    // Extract parameters from the array
    $datatypeName = $params['datatypeName'] ?? null;
    $region = $params['region'] ?? null;
    $commodity = $params['commodity'] ?? null;

    // Prepare the SQL query
    $sql = "
        SELECT toDate 
        FROM tbl_stats_left_toolbox_landing_bm 
        WHERE datatypeName = :datatypeName
        AND region = :region
        AND commodity = :commodity
        LIMIT 1
    ";

    // Prepare the statement
    $stmt = $conn->prepare($sql);

    // Bind the parameters
    $stmt->bindParam(':datatypeName', $datatypeName, PDO::PARAM_STR);
    $stmt->bindParam(':region', $region, PDO::PARAM_STR);
    $stmt->bindParam(':commodity', $commodity, PDO::PARAM_STR);

    // Execute the query
    $stmt->execute();

    // Fetch the first row
    $data = $stmt->fetch(PDO::FETCH_ASSOC);

    // Return the result as JSON
    return json_encode($data ? $data : []);
}




if (isset($_REQUEST['getfirstdate']) && $_REQUEST['getfirstdate'] === 'y' && !empty($_REQUEST['params'])) {

    // Decode the JSON string back to an array
    $params = json_decode($_REQUEST['params'], true);

    header('Content-Type: application/json');
    echo getFirstToDateAsJson($params);
}


function getPriceStaticDataAsJson() {
    global $conn; // Use the globally included PDO connection

    // Prepare the SQL query
    $sql = "
        SELECT 
            m.itemName as commodity, 
            m1.itemName as subcommodity, 
            m2.itemName as region, 
            ps.commodityID, 
            ps.subCommodityID, 
            ps.regionID, 
            m.f_order_by,
            CASE f_cnf_fob
                WHEN 'f' THEN 'Regional'
                WHEN 'cnf' THEN 'Import'
                WHEN 'fob' THEN 'Export'
            END as f_cnf_fob
        FROM `tbl_price_static` AS ps
        LEFT OUTER JOIN `tbl_region_commodity_master` AS m 
            ON ps.commodityID = m.ID AND m.itemType = 'commodity'
        LEFT OUTER JOIN `tbl_region_commodity_master` AS m1 
            ON ps.subCommodityID = m1.ID AND m1.itemType = 'commodity'
        LEFT OUTER JOIN `tbl_region_commodity_master` AS m2 
            ON ps.regionID = m2.ID AND m2.itemType = 'region'
        WHERE 
            ps.commodityID <> 475 
            AND ps.commodityID > 0 
            AND ps.subCommodityID > 0 
            AND ps.regionID > 0
        GROUP BY 
            ps.commodityID, 
            ps.subCommodityID, 
            ps.regionID, 
            f_cnf_fob, 
            continue_or_discontinue
        ORDER BY 
            m.f_order_by, 
            m1.f_order_by, 
            m2.f_order_by
    ";

    // Execute the query
    $stmt = $conn->query($sql);

    // Fetch all results
    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Return the results as JSON
    return json_encode($results);
}

if (isset($_REQUEST['getpricestats']) && $_REQUEST['getpricestats'] === 'y') {
    
header('Content-Type: application/json');
echo getPriceStaticDataAsJson();

}



function getTenderDataAsJson($tenderTable, $filterCond) {
    global $conn; // Use the globally included PDO connection

    // Define the subqueries
    $subquery1 = "
        SELECT 'c1' AS collectionID,
               1 AS tagID,
               'Tenders Expiring In Next 5 Days' AS title,
               'https://www.steelmint.com/images/tender_collection/1.jpg' AS imageUrl,
               COUNT(id) AS tenderCount
        FROM $tenderTable
        WHERE due_date <= NOW() + INTERVAL 5 DAY
          AND due_date >= NOW()
          $filterCond
    ";

    $subquery2 = "
        SELECT 'c2' AS collectionID,
               2 AS tagID,
               'Export Tenders' AS title,
               'https://www.steelmint.com/images/tender_collection/2.jpg' AS imageUrl,
               COUNT(id) AS tenderCount
        FROM $tenderTable
        WHERE STR_TO_DATE(due_date, '%Y-%m-%d') >= CURDATE()
          AND company_name IN (
              'SAIL',
              'RINL',
              'KIOCL',
              'SESA GOA',
              'TATA Steel',
              'Tata International Ltd.'
          )
          $filterCond
    ";

    $subquery3 = "
        SELECT 'c3' AS collectionID,
               3 AS tagID,
               'High Volume Tenders' AS title,
               'https://www.steelmint.com/images/tender_collection/3.jpg' AS imageUrl,
               COUNT(id) AS tenderCount
        FROM $tenderTable
        WHERE due_date >= NOW()
          AND (
              (qty_unit = 'MT' AND qty >= 10000)
              OR (qty_unit <> 'MT')
          )
          $filterCond
    ";

    // Combine the subqueries with UNION
    $sql = "
        SELECT * FROM ($subquery1) AS t1
        UNION ALL
        SELECT * FROM ($subquery2) AS t2
        UNION ALL
        SELECT * FROM ($subquery3) AS t3
    ";

    // Execute the query
    $stmt = $conn->query($sql);

    // Fetch all results
    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Return the results as JSON
    return json_encode($results);
}

if(isset($_REQUEST['gettendersdata']) && ($_REQUEST['gettendersdata'] === 'y')) {

$tenderTable = 'tbl_tenders'; 
$filterCond = base64_decode($_REQUEST['filterCond']); 
header('Content-Type: application/json');
echo getTenderDataAsJson($tenderTable, $filterCond);

}




function getCommodityDataCD($continue_or_discontinue) {
    global $conn; // Use the global database connection

    try {
        // SQL query with dynamic $continue_or_discontinue
        $sql = "SELECT m.itemName as commodity, 
                       m1.itemName as subcommodity, 
                       m2.itemName as region, 
                       m2.parentID as regionparentID, 
                       ps.commodityID, 
                       ps.subCommodityID, 
                       ps.regionID, 
                       m.f_order_by,
                       CASE 
                           WHEN f_cnf_fob='f' AND continue_or_discontinue=2 THEN 'Futures'
                           WHEN f_cnf_fob='f' THEN 'Regional'
                           WHEN f_cnf_fob='cnf' THEN 'Import'
                           WHEN f_cnf_fob='fob' THEN 'Export'
                       END as f_cnf_fob
                FROM tbl_price_static AS ps
                LEFT OUTER JOIN tbl_region_commodity_master AS m 
                    ON ps.commodityID = m.ID 
                    AND m.itemType = 'commodity'
                LEFT OUTER JOIN tbl_region_commodity_master AS m1 
                    ON ps.subCommodityID = m1.ID 
                    AND m1.itemType = 'commodity'
                LEFT OUTER JOIN tbl_region_commodity_master AS m2 
                    ON ps.regionID = m2.ID 
                    AND m2.itemType = 'region'
                WHERE ps.commodityID > 0 
                  AND ps.subCommodityID > 0 
                  AND ps.regionID > 0
                  AND ps.showOnlyInSteelmint = 1 
                  AND ps.continue_or_discontinue = :continue_or_discontinue
                GROUP BY ps.commodityID, 
                         ps.subCommodityID, 
                         ps.regionID, 
                         ps.f_cnf_fob, 
                         ps.continue_or_discontinue
                ORDER BY m.f_order_by, 
                         m1.f_order_by, 
                         m2.f_order_by";

        // Prepare and execute query with parameter binding
        $stmt = $conn->prepare($sql);
        $stmt->bindParam(':continue_or_discontinue', $continue_or_discontinue, PDO::PARAM_INT);
        $stmt->execute();
        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

        // Return results as JSON
        return json_encode($results);

    } catch (PDOException $e) {
        // Handle error
        return json_encode(['error' => $e->getMessage()]);
    }
}

if (isset($_REQUEST['continue_or_discontinue']) && $_REQUEST['cd'] === 'y') {
    
// Retrieve continue_or_discontinue from request or set default
$continue_or_discontinue = isset($_REQUEST['continue_or_discontinue']) ? $_REQUEST['continue_or_discontinue'] : 1;

// Output JSON data
header('Content-Type: application/json');
echo getCommodityDataCD($continue_or_discontinue);

}




function getFobData($continue_or_discontinue, $cond = '') {
    global $conn; // Use the global database connection

    try {
        // SQL query with dynamic $continue_or_discontinue and optional $cond
        $sql = "SELECT DISTINCT f_cnf_fob, 
                       CASE 
                           WHEN f_cnf_fob='f' AND continue_or_discontinue=2 THEN 'Futures'
                           WHEN f_cnf_fob='f' THEN 'Regional'
                           WHEN f_cnf_fob='cnf' THEN 'Import'
                           WHEN f_cnf_fob='fob' THEN 'Export'
                       END as f_cnf_fob_text
                FROM tbl_price_static AS ps
                LEFT OUTER JOIN tbl_state_city AS sc 
                    ON ps.cityID = sc.id 
                LEFT OUTER JOIN tbl_region_commodity_master AS m1 
                    ON sc.country_id = m1.ID 
                    AND m1.itemType = 'region'
                LEFT OUTER JOIN tbl_region_commodity_master AS m2 
                    ON ps.regionID = m2.ID 
                    AND m2.itemType = 'region'
                WHERE ps.regionID > 0 
                  AND ps.showOnlyInSteelmint = 1 
                  AND ps.continue_or_discontinue = :continue_or_discontinue
                  $cond
                GROUP BY f_cnf_fob, continue_or_discontinue
                ORDER BY continue_or_discontinue, f_cnf_fob";

        // Prepare and execute query with parameter binding
        $stmt = $conn->prepare($sql);
        $stmt->bindParam(':continue_or_discontinue', $continue_or_discontinue, PDO::PARAM_INT);
        $stmt->execute();
        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

        // Return results as JSON
        return json_encode($results);

    } catch (PDOException $e) {
        // Handle error
        return json_encode(['error' => $e->getMessage()]);
    }
}

if (isset($_REQUEST['continue_or_discontinue']) && $_REQUEST['getfobdata'] === 'y') {
    
    // Retrieve continue_or_discontinue from request or set default
    $continue_or_discontinue = $_REQUEST['continue_or_discontinue'];

    // Retrieve optional condition from request or set default
    $cond = isset($_REQUEST['cond']) ? urldecode($_REQUEST['cond']) : '';

    // Output JSON data
    header('Content-Type: application/json');
    echo getFobData($continue_or_discontinue, $cond);

}



function getRegionData($continue_or_discontinue, $cond = '') {
    // Declare the global $pdo variable
    global $conn;

    // SQL query with placeholders for the continue_or_discontinue and cond
    $sql = "SELECT DISTINCT m2.itemName as region, ps.regionID, m1.itemName as subRegion, sc.country_id as subRegionID, m2.f_order_by, m1.parentID
            FROM `tbl_price_static` AS ps
            LEFT OUTER JOIN `tbl_state_city` AS sc ON ps.cityID = sc.id 
            LEFT OUTER JOIN `tbl_region_commodity_master` AS m1 ON sc.country_id = m1.ID AND m1.itemType = 'region'
            LEFT OUTER JOIN `tbl_region_commodity_master` AS m2 ON ps.regionID = m2.ID AND m2.itemType = 'region'
            WHERE ps.regionID > 0 AND ps.showOnlyInSteelmint = 1 AND ps.$continue_or_discontinue 
            $cond
            GROUP BY ps.cityID
            ORDER BY m2.f_order_by";

    // Prepare and execute the query using PDO
    $stmt = $conn->prepare($sql);
    $stmt->execute();

    // Fetch all results into an associative array
    $data = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Return the data as a JSON-encoded string
    return json_encode($data);
}

if (isset($_REQUEST['continue_or_discontinue']) && $_REQUEST['getregiondata'] === 'y') {
    // Retrieve continue_or_discontinue from request or set default
    $continue_or_discontinue = $_REQUEST['continue_or_discontinue'];

    // Retrieve optional condition from request or set default
    $cond = isset($_REQUEST['cond']) ? urldecode($_REQUEST['cond']) : '';

    // Output JSON data
    header('Content-Type: application/json');
    echo getRegionData($continue_or_discontinue, $cond);
}






function getLatestDate() {
    // Declare the global $pdo variable
    global $conn;

    // SQL query to retrieve the latest date
    $sql = "SELECT f_date
            FROM `tbl_steelprices`
            WHERE `relation_id` IN (1503, 1502, 1501, 1500, 1125, 1124, 1123, 1122, 1121, 1120, 1119, 1118, 1117, 1116, 1114, 1113, 1112, 1111, 1110, 1109)
            AND f_date = last_updated_date
            AND f_date <> '0000-00-00'
            ORDER BY f_date DESC
            LIMIT 1";

    // Prepare and execute the query using PDO
    $stmt = $conn->prepare($sql);
    $stmt->execute();

    // Fetch the result
    $data = $stmt->fetch(PDO::FETCH_ASSOC);

    // Return the data as a JSON-encoded string
    return json_encode($data);
}

if (isset($_REQUEST['getlatestdate']) && $_REQUEST['getlatestdate'] === 'y') {
    // Output JSON data
    header('Content-Type: application/json');
    echo getLatestDate();
}





function getFDate($asst_id) {
    // Declare the global $conn variable
    global $conn;

    // SQL query to retrieve the f_date based on the provided asst_id
    $qry = "SELECT f_date
            FROM tbl_data_collection_new
            WHERE pid = :asst_id
            AND f_date <= :current_date
            AND outlier <> ''
            AND p_flag = 'A'
            AND price_type <> 'OTHERS'
            ORDER BY f_date DESC
            LIMIT 1";

    // Prepare the SQL statement
    $stmt = $conn->prepare($qry);

    // Bind the parameters
    $currentDate = date('Y-m-d');
    $stmt->bindParam(':asst_id', $asst_id, PDO::PARAM_INT);
    $stmt->bindParam(':current_date', $currentDate, PDO::PARAM_STR);

    // Execute the statement
    $stmt->execute();

    // Fetch the result
    $row = $stmt->fetch(PDO::FETCH_ASSOC);

    // Return the f_date as a JSON-encoded string
    return json_encode($row);
}

if (isset($_REQUEST['asst_id']) && $_REQUEST['getfdate'] === 'y') {

    // Retrieve and sanitize the asset ID from the request
    $asst_id = filter_var($_REQUEST['asst_id'], FILTER_SANITIZE_NUMBER_INT);

    // Output JSON data
    header('Content-Type: application/json');
    echo getFDate($asst_id);
}






function getMarketData($itemID) {
    // Declare the global $conn variable
    global $conn;

    // SQL query to retrieve the f_market based on the provided itemID
    $sqlQry = "
        SELECT f_market
        FROM `tbl_op_commodity_master` as r
        WHERE id = :itemID";

    // Prepare the SQL statement
    $stmt = $conn->prepare($sqlQry);

    // Bind the itemID parameter
    $stmt->bindParam(':itemID', $itemID, PDO::PARAM_INT);

    // Execute the statement
    $stmt->execute();

    // Fetch the result
    $row = $stmt->fetch(PDO::FETCH_ASSOC);

    // Return the market data as a JSON-encoded string
    return json_encode($row);
}


if (isset($_REQUEST['itemID']) && $_REQUEST['getmarketdata'] === 'y') {

    // Retrieve the item ID from the request
    $itemID = filter_var($_REQUEST['itemID'], FILTER_SANITIZE_NUMBER_INT);

    // Output JSON data
    header('Content-Type: application/json');
    echo getMarketData($itemID);
}






function getCommodityData($tbl, $where_condition) {
    // Declare the global $conn variable
    global $conn;

    // SQL query to retrieve commodityID and subCommodityID based on the provided table and condition
    $sqlQry = "
        SELECT r.commodityID,
               r.subCommodityID
        FROM `$tbl` as r
        WHERE $where_condition";

    // Prepare the SQL statement
    $stmt = $conn->prepare($sqlQry);

    // Execute the statement
    $stmt->execute();

    // Fetch the results as an array
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Return the results as a JSON-encoded string
    return json_encode($rows);
}


if (isset($_REQUEST['tbl']) && isset($_REQUEST['where_condition']) && $_REQUEST['getcommoditydata'] === 'y') {

    // Sanitize the inputs
    $tbl = filter_var($_REQUEST['tbl'], FILTER_SANITIZE_STRING);
    $where_condition = filter_var($_REQUEST['where_condition'], FILTER_SANITIZE_STRING);

    // Output JSON data
    header('Content-Type: application/json');
    echo getCommodityData($tbl, $where_condition);
}





function getMarketData2($comm_arr) {
    global $conn;
    
    // Prepare the SQL query with placeholders
    $qry = "SELECT GROUP_CONCAT(f_market) as market
            FROM tbl_region_commodity_master
            WHERE id IN ($comm_arr) AND itemtype='commodity'";
    
    // Execute the query using PDO
    $stmt = $conn->prepare($qry);
    $stmt->execute();

    // Fetch the result
    $resultData = $stmt->fetch(PDO::FETCH_ASSOC);

    // Return the result as a JSON string
    return json_encode($resultData);
}

if (isset($_REQUEST['comm_arr']) && $_REQUEST['getmarketdata2'] === 'y') {

    // Retrieve the commodity array from the request
    $comm_arr = $_REQUEST['comm_arr'];

    // Output JSON data
    header('Content-Type: application/json');
    echo getMarketData2($comm_arr);
}





function getCommodityID($subCommodityID, $where) {
    // Declare the global $pdo variable
    global $conn;

    // Sanitize input parameters to prevent SQL injection
    $subCommodityID = $conn->quote($subCommodityID);
    $where = $conn->quote($where);

    // SQL query
    $qry = "SELECT 
                CASE 
                    WHEN parentID = 0 THEN id 
                    ELSE parentID 
                END AS cId
            FROM tbl_region_commodity_master 
            WHERE id IN ($subCommodityID) $where 
            LIMIT 1";

    // Prepare and execute the query using PDO
    $stmt = $conn->prepare($qry);
    if (!$stmt->execute()) {
        // Handle query execution error
        die(json_encode(['error' => 'Query failed: ' . implode(' ', $stmt->errorInfo())]));
    }

    // Fetch the result
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    if ($row) {
        $commodityID = $row['cId'];
        $result = ['commodityID' => $commodityID];
    } else {
        $result = ['commodityID' => null];
    }

    // Return result as JSON
    return json_encode($result);
}

if (isset($_REQUEST['comm_arr']) && $_REQUEST['getcommodityid'] === 'y') {
    // Retrieve parameters from request
    $comm_arr = $_REQUEST['comm_arr'];
    $where = isset($_GET['where']) ? $_GET['where'] : '';

    // Output JSON data
    header('Content-Type: application/json');
    echo getCommodityID($comm_arr, $where);
}





function getCategoriesData() {
    global $conn;

    // Define the SQL query
    $sql = "SELECT DISTINCT(`category`) as name, `item` as sub_category, 1 as `prices`, 0 as `news`, 0 as `tenders` 
            FROM `tbl_prices_index_master` 
            WHERE `category` <> '' 
            AND `category` <> 'index' 
            AND `tabs` <> '' 
            AND `page` = 'mobileapp' 
            AND view_all_spinner_ordering <> 0
            UNION
            SELECT DISTINCT(`category`) as name, `sub_category`, 0 as `prices`, 1 as `news`, 0 as `tenders` 
            FROM `tbl_news_post` 
            WHERE `category` <> '' 
            AND `category` <> 'index'
            UNION
            SELECT DISTINCT(`category`) as name, `sub_category`, 0 as `prices`, 0 as `news`, 1 as `tenders` 
            FROM `tbl_tenders` 
            WHERE `category` <> '' 
            AND `category` <> 'index'
            ORDER BY CASE name
                WHEN 'Raw Material' THEN 1
                WHEN 'Iron Ore' THEN 2
                WHEN 'Coal' THEN 3
                WHEN 'Ferrous Scrap' THEN 4
                WHEN 'Scrap' THEN 5
                WHEN 'Ship Breaking' THEN 6
                WHEN 'Semi Finish' THEN 7
                WHEN 'Semi Finished' THEN 8
                WHEN 'Finish Long' THEN 9
                WHEN 'Finish Flat' THEN 10
                WHEN 'Ferro Alloy' THEN 11
                WHEN 'Logistics' THEN 12
                ELSE 99
            END";

    // Prepare and execute the query
    $stmt = $conn->prepare($sql);
    $stmt->execute();

    // Fetch the results
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Return the results as a JSON-encoded array
    return json_encode($rows);
}

// Check if the request parameter matches the expected value
if (isset($_REQUEST['getcategoriesdata']) && $_REQUEST['getcategoriesdata'] === 'y') {
    header('Content-Type: application/json');
    echo getCategoriesData();
}



function getUserPortfolio($user_id, $type, $appType) {
    global $conn;

    // Define the SQL query
    $sql = "SELECT * 
            FROM `tbl_user_portfolio`
            WHERE `user_id` = :user_id
            AND `type` = :type
            AND `appType` = :appType";

    // Prepare the query
    $stmt = $conn->prepare($sql);

    // Bind the parameters
    $stmt->bindParam(':user_id', $user_id, PDO::PARAM_INT);
    $stmt->bindParam(':type', $type, PDO::PARAM_STR);
    $stmt->bindParam(':appType', $appType, PDO::PARAM_STR);

    // Execute the query
    $stmt->execute();

    // Fetch the results
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Return the results as a JSON-encoded array
    return json_encode($rows);
}

// Check if the request contains the necessary parameters and execute the function
if (isset($_REQUEST['user_id']) && isset($_REQUEST['type']) && isset($_REQUEST['appType']) && $_REQUEST['getportfolio'] === 'y') {
    
    $user_id = isset($_REQUEST['user_id']) ? base64_decode($_REQUEST['user_id']) : null;
    $type = isset($_REQUEST['type']) ? base64_decode($_REQUEST['type']) : null;
    $appType = isset($_REQUEST['appType']) ? base64_decode($_REQUEST['appType']) : null;

    header('Content-Type: application/json');
    echo getUserPortfolio($user_id, $type, $appType);
}



function getPortfolioData($memid, $commodity, $region) {
    // Declare the global $pdo variable
    global $conn;

    // SQL query with placeholders
    $qry = "SELECT id, commodity, region, dataType 
            FROM portfolio 
            WHERE excel_card = :excel_card 
            AND memid = :memid 
            AND commodity = :commodity 
            AND region = :region";

    // Prepare the query
    $stmt = $conn->prepare($qry);

    // Set the value for excel_card
    $excel_card = 'y';
    
    // Bind the parameters
    $stmt->bindParam(':excel_card', $excel_card);
    $stmt->bindParam(':memid', $memid);
    $stmt->bindParam(':commodity', $commodity);
    $stmt->bindParam(':region', $region);

    

    // Execute the query
    $stmt->execute();

    // Fetch all results into an associative array
    $data = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Return the data as a JSON-encoded string
    return json_encode($data);
}

// Example usage (assuming you get memid, commodity, and region from a request)
if (isset($_REQUEST['get_portfolio_data']) && $_REQUEST['get_portfolio_data'] === 'y') {
    $memid = $_REQUEST['memid'];
    $commodity = $_REQUEST['commodity'];
    $region = $_REQUEST['region'];

    // Output JSON data
    header('Content-Type: application/json');
    echo getPortfolioData($memid, $commodity, $region);
}



function getPortfolioDataByMemid($memid) {
    // Declare the global $conn variable
    global $conn;

    // SQL query with placeholders
    $qry = "SELECT id, commodity, region, dataType 
            FROM portfolio 
            WHERE excel_card = :excel_card 
            AND memid = :memid";

    // Prepare the query
    $stmt = $conn->prepare($qry);
    
    // Set the value for excel_card
    $excel_card = 'y';
    
    // Bind the parameters
    $stmt->bindParam(':excel_card', $excel_card);
    $stmt->bindParam(':memid', $memid);

    

    // Execute the query
    $stmt->execute();

    // Fetch all results into an associative array
    $data = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Return the data as a JSON-encoded string
    return json_encode($data);
}

// Example usage (assuming you get memid from a request)
if (isset($_REQUEST['get_portfolio_data_by_memid']) && $_REQUEST['get_portfolio_data_by_memid'] === 'y') {
    $memid = $_REQUEST['memid'];

    // Output JSON data
    header('Content-Type: application/json');
    echo getPortfolioDataByMemid($memid);
}




function getBulkShipmentData($commodity, $region) {
    // Declare the global $conn variable
    global $conn;

    // SQL query with placeholders
    $qry = "SELECT excel_file 
            FROM tbl_bulk_shipment 
            WHERE f_commodity = :commodity 
            AND f_region = :region";

    // Prepare the query
    $stmt = $conn->prepare($qry);

    // Bind the parameters
    $stmt->bindParam(':commodity', $commodity);
    $stmt->bindParam(':region', $region);

    // Execute the query
    $stmt->execute();

    // Fetch all results into an associative array
    $data = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Return the data as a JSON-encoded string
    return json_encode($data);
}

// Example usage (assuming you get commodity and region from a request)
if (isset($_REQUEST['get_bulk_shipment_data']) && $_REQUEST['get_bulk_shipment_data'] === 'y') {
    $commodity = $_REQUEST['commodity'];
    $region = $_REQUEST['region'];

    // Output JSON data
    header('Content-Type: application/json');
    echo getBulkShipmentData($commodity, $region);
}



// This code should be on the API server

function getGroupCommodityData($comm_arr) {
    global $conn;

    $comm_arr = filter_var($comm_arr, FILTER_SANITIZE_STRING);

    $sqlQry = "
        SELECT 
            GROUP_CONCAT(f_market) as market
        FROM 
            tbl_region_commodity_master
        WHERE 
            id in ($comm_arr)  
            AND itemtype='commodity'";

    $stmt = $conn->prepare($sqlQry);
    $stmt->execute();
    $row = $stmt->fetch(PDO::FETCH_ASSOC);

    return json_encode($row);
}

if (isset($_REQUEST['comm_arr']) && $_REQUEST['getgroupcommoditydata'] === 'y') {
    $comm_arr = $_REQUEST['comm_arr'];

    header('Content-Type: application/json');
    echo getGroupCommodityData($comm_arr);
}





function getAdjacentPosts($curritemid) {
    // Declare the global $conn variable
    global $conn;

    // Query for the next post
    $nextStmt = $conn->prepare("
        SELECT href 
        FROM tbl_news_post 
        WHERE post_id > :curritemid 
        AND for_smgroup_company LIKE '%steelmint%' 
        ORDER BY post_id ASC 
        LIMIT 0,1
    ");
    $nextStmt->bindParam(':curritemid', $curritemid, PDO::PARAM_INT);
    $nextStmt->execute();
    $nextRes = $nextStmt->fetch(PDO::FETCH_ASSOC);

    // Query for the previous post
    $prevStmt = $conn->prepare("
        SELECT href 
        FROM tbl_news_post 
        WHERE post_id < :curritemid 
        AND for_smgroup_company LIKE '%steelmint%' 
        ORDER BY post_id DESC 
        LIMIT 0,1
    ");
    $prevStmt->bindParam(':curritemid', $curritemid, PDO::PARAM_INT);
    $prevStmt->execute();
    $prevRes = $prevStmt->fetch(PDO::FETCH_ASSOC);

    // Extract the href values or set to null if not found
    $next_post = $nextRes['href'] ?? null;
    $previous_post = $prevRes['href'] ?? null;

    // Return the results as a JSON-encoded string
    return json_encode([
        'next_post' => $next_post,
        'previous_post' => $previous_post,
    ]);
}


if (isset($_REQUEST['curritemid']) && $_REQUEST['getadjacentposts'] === 'y') {
    $curritemid = $_REQUEST['curritemid'];

    header('Content-Type: application/json');
    echo getAdjacentPosts($curritemid);
}



function getCommodityDataAsJson(array $params) {
    global $conn; // Use the globally included PDO connection

    // Extract parameters from the array
    $subCommodityID = $params['subCommodityID'] ?? null;
    $where = $params['where'] ?? '';

    if (empty($subCommodityID)) {
        return json_encode(['error' => 'subCommodityID is required']);
    }

    // Prepare the SQL query
    $sql = "
        SELECT 
            CASE WHEN parentID = 0 THEN id ELSE parentID END AS cId
        FROM tbl_region_commodity_master 
        WHERE id IN ($subCommodityID) $where
        LIMIT 1
    ";

    // Prepare the statement
    $stmt = $conn->prepare($sql);

    // Execute the query
    $stmt->execute();

    // Fetch the first row
    $data = $stmt->fetch(PDO::FETCH_ASSOC);

    // Return the result as JSON
    return json_encode($data ? $data : []);
}


if (isset($_REQUEST['getcommoditydataasjson']) && $_REQUEST['getcommoditydataasjson'] === 'y' && !empty($_REQUEST['params'])) {

    // Decode the JSON string back to an array
    $params = json_decode($_REQUEST['params'], true);

    header('Content-Type: application/json');
    echo getCommodityDataAsJson($params);
}


function getLogTypeForIframe($memidForIframe) {
    global $conn; // Use the globally included PDO connection

    // Initialize the log type
    $logtype = "price_pre";

    // Check if memidForIframe is set and not empty
    if (isset($memidForIframe) && $memidForIframe != '') {
        
        // Prepare the SQL query to fetch the record
        $query = "SELECT * FROM `tbl_updated_reg_logs` WHERE `status` = 'Active' AND `services` = 'W' AND `memid` = :memid";

        // Prepare and execute the statement
        $stmt = $conn->prepare($query);
        $stmt->bindParam(':memid', $memidForIframe, PDO::PARAM_INT);
        $stmt->execute();

        // Check if any rows were returned
        if ($stmt->rowCount() > 0) {
            $logtype = "price_post";  // Log type when a record is found
        }
    }

    // Return the log type
    return $logtype;
}

// Example usage: Call the function only if memidForIframe is passed
if (isset($_REQUEST['memidForIframe']) && $_REQUEST['memidForIframe'] != '') {
    $logtype = getLogTypeForIframe($_REQUEST['memidForIframe']);
} else {
    $logtype = 'price_pre';  // Default logtype
}

// Handling the request, no need for sourceUrl to be mandatory
if (isset($_REQUEST['insert_rm_saved_sections']) && $_REQUEST['insert_rm_saved_sections'] == 'y') {
    // Fetch the data sent to the server (usually from POST)
    $inputData = json_decode(file_get_contents('php://input'), true);
    header('Content-Type: application/json');
    $_data = [
        'section' => $inputData['section'] ?? '',
        'rm_setting_id' => $inputData['rm_setting_id'] ?? '', 
        'updated_at' => $inputData['updated_at'] ?? date('Y-m-d H:i:s')       
    ];

    // 2. Insert the data into the table using PDO
    $stmt = $conn->prepare("INSERT INTO rm_saved_sections (rm_setting_id, section, updated_at) VALUES (:rm_setting_id, :section, :updated_at)");

    // Bind parameters
    $stmt->bindParam(':rm_setting_id', $_data['rm_setting_id'], PDO::PARAM_STR);
    $stmt->bindParam(':section', $_data['section'], PDO::PARAM_STR);
    $stmt->bindParam(':updated_at', $_data['updated_at'], PDO::PARAM_STR);
   
    // Execute the query
    $stmt->execute();

    // 3. Get the last inserted ID
    $insertedId = $conn->lastInsertId();   
    
}

// Handling the request, no need for sourceUrl to be mandatory
if (isset($_REQUEST['insert_rm_save_settings']) && $_REQUEST['insert_rm_save_settings'] == 'y') {
    // Fetch the data sent to the server (usually from POST)
    $inputData = json_decode(file_get_contents('php://input'), true);
    header('Content-Type: application/json');
    $_data = [       
        'rm_name' => $inputData['rm_name'] ?? '', 
        'rm_user' => $inputData['rm_user'] ?? '',
        'date_added' => $inputData['date_added'] ?? date('Y-m-d H:i:s')       
    ];

    // 2. Insert the data into the table using PDO
    $stmt = $conn->prepare("INSERT INTO rm_save_settings (rm_name, rm_user, date_added) VALUES (:rm_name, :rm_user, :date_added)");

    // Bind parameters
    $stmt->bindParam(':rm_name', $_data['rm_name'], PDO::PARAM_STR);
    $stmt->bindParam(':rm_user', $_data['rm_user'], PDO::PARAM_STR);
    $stmt->bindParam(':date_added', $_data['date_added'], PDO::PARAM_STR);
   
    // Execute the query
    $stmt->execute();

    // 3. Get the last inserted ID
    $insertedId = $conn->lastInsertId();   
    echo json_encode(['inserted_id' => $insertedId]);
    
}


?>
