[Script] Catalogue Search Fix for ALL retros [Script]

[Bop]

If you are searching a furniture and clicking an item but it shows a wall item type. This fix is for you!

First, run this query

UPDATE catalog_items
SET offer_id = CASE
    WHEN item_ids LIKE '%;%' AND SUBSTRING_INDEX(item_ids, ';', 1) REGEXP '^[0-9]+$' THEN CAST(SUBSTRING_INDEX(item_ids, ';', 1) AS UNSIGNED)
    WHEN item_ids LIKE '%:%' AND SUBSTRING_INDEX(item_ids, ':', 1) REGEXP '^[0-9]+$' THEN CAST(SUBSTRING_INDEX(item_ids, ':', 1) AS UNSIGNED)
    WHEN item_ids REGEXP '^[0-9]+$' THEN CAST(item_ids AS UNSIGNED)
    ELSE 0  -- or another appropriate default value
END;

This will match all id's in the offer_item column to the item_ids column

Second, after running this query, you will need to install NodeJS, if it's already than no need to install it again

You must be registered for see links

Third step, place this file/script inside gamedata folder where your FurnitureData.json is located and edit the database credentials to match your current database credentials

RECOMMENDED: Make a backup of your FurnitureData.json

const fs = require('fs');
const mysql = require('mysql2');
const { performance } = require('perf_hooks');

// Database connection configuration
const dbConfig = {
    host: 'localhost',
    user: 'root',
    password: 'PASSWORD',
    database: 'DBNAME'
};

const connection = mysql.createConnection(dbConfig);

function updateOfferIdsForSection(furniTypes, callback) {
    let completed = 0;
    furniTypes.forEach((furniture, index) => {
        const query = 'SELECT offer_id FROM catalog_items WHERE catalog_name = ?';
        connection.query(query, [furniture.classname], (error, results) => {
            if (error) {
                console.error('Error querying database:', error);
                return callback(error);
            }
            if (results.length > 0) {
                furniture.offerid = results[0].offer_id;
                console.log(`Updated offerid for ${furniture.classname} to ${results[0].offer_id}`);
            } else {
                console.log(`No matching record found for ${furniture.classname}`);
            }
            completed++;
            if (completed === furniTypes.length) {
                callback();
            }
        });
    });
}

const start = performance.now();

fs.readFile('FurnitureData.json', 'utf8', (err, data) => {
    if (err) {
        console.error('Error reading file:', err);
        return;
    }

    const furnitureData = JSON.parse(data);

    updateOfferIdsForSection(furnitureData.wallitemtypes.furnitype, (error) => {
        if (error) {
            console.error('Error updating wall item types:', error);
            return;
        }

        updateOfferIdsForSection(furnitureData.roomitemtypes.furnitype, (error) => {
            if (error) {
                console.error('Error updating room item types:', error);
                return;
            }

            const updatedJSONData = JSON.stringify(furnitureData, null, 2);
            fs.writeFile('FurnitureData.json', updatedJSONData, (err) => {
                if (err) {
                    console.error('Error writing file:', err);
                    return;
                }
                console.log('FurnitureData.json updated successfully');

                connection.end((err) => {
                    if (err) {
                        console.error('Error closing connection:', err);
                        return;
                    }
                    const end = performance.now();
                    console.log(`Script took ${(end - start) / 1000} seconds to finish`);
                });
            });
        });
    });
});

Final Step:
Use your favorite terminal
Run "npm i" or "npm install" to install the dependencies/packages
Run "npm SCRIPTFILENAME.js"