/** freq.js
 * 
 * Google Cloud Functions impementation of `linkeysearch:javascript/freq`
 * this script extracts products' order-frequency 
 * -----------------------------------------------------------------------------
 * 
 * Contents:
 * #1 Requirements
 * #2 Personalized constants and parametres
 * #3 Supporting functions
 * #4 Output functions
 * #5 Entry-point function main()
 */

// #1
// REQUIREMENTS
////////////////////////////////////////////////////////////////////////////////


var mysql = require('mysql');
const fs = require('fs');
const os = require('os');
const {Storage} = require('@google-cloud/storage');     // import Google Cloud client library




// #2
// SETUP PERSONALIZED CONSTANTS AND PARAMETRES
////////////////////////////////////////////////////////////////////////////////


// google-storage parametres
// --- -- -- - - -
const projectId = 'pythia-251711';
// const keyFilename = '../auth/pythia-251711-047e3d5e6608.json';


// temp file of frequencies (json)
const temp_file = os.tmpdir() + '/freq.json';


// db connection parametres
// (keep the one that suits your environment; comment out the other)
// -----------------------------------------------------------------------------


// while on Google Cloud Functions
var con = mysql.createConnection({   // ** check
    socketPath: "/cloudsql/pythia-251711:europe-west4:pythia-db-eu",
    user: "pythia_services",
    password: process.env.DB_PASSWORD,
    database: "pythia_db"
});

// MAIN OUTPUT OF THE SCRIPT
// arrays that need to be constructed and filled with data
// -----------------------------------------------------------------------------

/** array of product frequencies
 * array of obj: { id: , fq: }
 * @var id (int): product's eys code
 * @var fq (int): (order) frerquency 
 */
var freq_ = [];



// #3
// SUPPORTING FUNCTIONS
////////////////////////////////////////////////////////////////////////////////



/** extract frequencies
 * 
 * @param obj (array): array of products
 */
function extract_freq(obj) {

    obj.forEach( rec => {
        var description = rec.product_description;
        var fq  = rec.FREQuency;
        var pid = rec.eys_code;      

        freq_.push({
            fq: fq,
            id: pid
        });
    });
}


// #4
// OUTPUT FUNCTIONS
////////////////////////////////////////////////////////////////////////////////

// Save Local
// -----------------------------------------------------------------------------
async function save_local(jsonArray, fileName) {
    let fileStr = JSON.stringify(jsonArray);    // convert json to string
    
    // write string to file
    fs.writeFileSync(fileName, fileStr, 'utf8', (err) => {
        if (err) {
            console.log("An error occured while writing keywords.json");
            return console.log(err);
        }
        console.log("JSON file", fileName, "has been saved.");

    });

    return true;
}


// Save to Google Cloud Storage
// -----------------------------------------------------------------------------
async function upload_file( bucketName, srcFilePath, trgFilePath ) {
    // Creates a client
    const storage = new Storage();

    try {
        await storage.bucket(bucketName).upload(srcFilePath, {
            destination: trgFilePath, 
            gzip: true,     // serve compressed
            metadata: {     // cache for 8 hours
                cacheControl: 'public, max-age=60'      // production set: 28800
            }
        });
        console.log(`${srcFilePath} uploaded to ${bucketName}`);
    }
    catch(err) {
        console.error('ERROR:', err);
    }
}


// #5
// MAIN function (exposed function to run the whole proccess)
////////////////////////////////////////////////////////////////////////////////


/** main()
 * 
 * nodejs's exported function
 * used as entry-point function (in case of Google Cloud function)
 */

exports.main = () => {   // google cloud function entry point

    console.log('temporary file:', temp_file);

    // connect;
    // get records to proccess;
    // call main proccess function; 
    // save dictionary;
    // end script;
    // -----------------------------------------------------------------------------

    // SQL to get needed data 
    var sql = "SELECT count(pl.eys_code) as FREQuency,\
            pl.product_id as product_id,\
            pb.brand_name,\
            pl.barcode, pl.skl_code, pl.eys_code,\
            IF( pd.description IS NOT NULL , pd.description , pl.product_description ) AS product_description,\
            pl.bpcs_code,\
            pd.image_path\
        FROM product_list as pl\
        LEFT JOIN delivery_orders_products AS dop ON dop.product_id = pl.eys_code\
        LEFT JOIN delivery_orders AS do ON dop.order_id = do.id\
        LEFT JOIN product_list AS replacement ON dop.replacement_for = replacement.eys_code\
        LEFT JOIN product_details AS pd ON pl.eys_code = pd.eys_code\
        LEFT JOIN product_brands pb ON pl.brand_id = pb.id\
        WHERE pl.active = 1 AND pl.sap_code IS NOT NULL AND pl.product_category_sap_4 NOT LIKE '72%'\
        GROUP BY pl.product_id\
        ORDER BY FREQuency DESC\
        LIMIT 50000";
    
    console.log('Frequencies SQL:', sql)

    // query sql
    con.query(sql, function (err, result) {
        if (err) throw err;
        console.log('Records from database received!')

        extract_freq(result);    // proccess
        console.log('Keywords proccesed!')

        save_local(freq_, temp_file)        // save frequencies
        .then( () => {
            upload_file('pythia-files', temp_file, 'uploads/json/freq.json')
            .then( () => {
                console.log('Results saved! Exiting...');
            });
        });
            
    });
}