// Customizable storage module for any mode of storage
// Update the functions here to change the storage medium

// Import modules
const { DebugBuilder } = require("./utilities/debugBuilder");
const log = new DebugBuilder("server", "libStorage");
const { RSSSourceRecord, RSSPostRecord } = require("./utilities/recordHelper");

// Storage Specific Modules
// MySQL
const mysql = require("mysql");

const rssFeedsTable = process.env.DB_RSS_FEEDS_TABLE;
const rssPostsTable = process.env.DB_RSS_POSTS_TABLE;
const accountsTable = process.env.DB_ACCOUNTS_TABLE;
const transactionsTable = process.env.DB_TRANSACTIONS_TABLE;
const pricingTable = process.env.DB_PRICING_TABLE;

var Connection = mysql.createPool({
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASS,
    database: process.env.DB_NAME,
    connectionLimit: 10
});

// Helper Functions
/**
 * Function to run and handle SQL errors
 * @param {string} sqlQuery The SQL query string
 * @param {*} connection The SQL connection to be used to query
 * @param {function} callback The callback function to be called with an error or the results
 * @param {number} _retry Set by error retry, increments the number a query has been retried to increase wait time and track a specific query
 */
function runSQL(sqlQuery, connection, callback = (err, rows) => {
    log.ERROR(err);
    throw err;
}, _retry = 0) {
    // Start the MySQL Connection
    if (!connection) connection = Connection;
    connection.query(sqlQuery, (err, rows) => {
        if (err) {
            if (err.code === "EHOSTUNREACH") {
                // DB Connection is unavailable
                let retryTimeout;
                switch(_retry){
                    case 0:
                        retryTimeout = 30000;
                        break;
                    case retry < 15:
                        retryTimeout = 30000 + retry * 15000;
                        break;
                    default:                        
                        log.ERROR("Retried Database 15 times over, please check connection status and restart the app", sqlQuery, err);
                        return callback(err, undefined);                        
                }
                log.WARN(`Database connection is unavailable, waiting ${ retryTimeout / 1000 } seconds...`);
                _retry += 1
                // Wait for the retry timeout before trying the query again
                setTimeout(runSQL(sqlQuery, connection, callback, _retry));
            }
            else return callback(err, undefined);        
        } 
        log.VERBOSE(`SQL result for query '${sqlQuery}':`, rows);        
        return callback(undefined, rows);
    })
}

/**
 * Return a formatted date time string from now for MySQL
 * 
 * @returns Date string for now formatted for MySQL
 */
function returnMysqlTime(){
    return new Date().toISOString().slice(0, 19).replace('T', ' ');
}

class Storage {
    constructor(_dbTable, _connection) {       
        this.dbTable = _dbTable;
	    this.connection = _connection;
        this.validKeys = [];
        
        var sqlQuery = `SHOW COLUMNS FROM ${this.dbTable};`; 

        runSQL(sqlQuery, this.connection,  (err, rows) => {
            if (err) return log.ERROR("Error getting column names: ", err);
            if (rows){
                for (const validKey of rows){
                    this.validKeys.push(validKey.Field);
                }
                log.VERBOSE(`Database rows for '${this.dbTable}': `, rows);
                log.DEBUG(`Keys for '${this.dbTable}': `, this.validKeys);
            }            
        })
    }

    /**
     * Wrapper to delete an entry using the storage method configured
     * @param {} entryID The ID of the entry to be deleted
     * @param {function} callback The callback function to be called with the record when deleted     
     */
    destroy(entryID, callback) {
        if (!entryID) return callback(Error("No entry ID given"), undefined);

        this.getRecordBy('id', entryID, (err, entryRecord) => {
            this.removeEntry(entryRecord.id, (err, results) => {
                if (err) return callback(err, undefined);
                return callback(undefined, results);
            });
        });        
    }

    /**
     * Get a record by a specified key
     * @param {*} key The key to search for
     * @param {*} keyValue The value of the key to search for
     * @param {*} callback The callback function
     */
    getRecordBy(key, keyValue, callback) {                
        if (!this.validKeys.includes(key)) return callback(new Error("Given key not valid", key), undefined);

        const sqlQuery = `SELECT * FROM ${this.dbTable} WHERE ${key} = "${keyValue}"`;

        runSQL(sqlQuery, this.connection,  (err, rows) => {
            if (err) return callback(err, undefined);
            if (rows[0]?.[key]) return callback(undefined, rows[0]);
            else return callback(undefined, false);
        })
    }

    /**
     * Get all records stored 
     * @param {function} callback 
     */
    getAllRecords(callback) {
        log.INFO("Getting all records from: ", this.dbTable);
        const sqlQuery = `SELECT * FROM ${this.dbTable}`

        let records = [];

        runSQL(sqlQuery, this.connection, (err, rows) => {
            if (err) return callback(err, undefined);
            for (const row of rows) {                
                if (this.dbTable == rssFeedsTable){
                    records.push(new RSSSourceRecord(row.id, row.title, row.link, row.category, row.guild_id, row.channel_id));
                }
                else {                    
                    records.push(rows);
                }
            }
            log.VERBOSE("All records:", records);
            return callback(undefined, records);
        });
    }

    /**
     * Gets all unique rows in the given key
     * @param {*} key 
     * @param {*} callback 
     */
    getUniqueByKey(key, callback){
        log.INFO("Getting all unique values in column: ", key);
        const sqlQuery = `SELECT DISTINCT ${key} FROM ${this.dbTable}`

        let records = [];

        runSQL(sqlQuery, this.connection, (err, rows) => {
            if (err) return callback(err, undefined);
            for (const row of rows) {                
                if (this.dbTable == rssFeedsTable){
                    records.push(new RSSSourceRecord(row.id, row.title, row.link, row.category, row.guild_id, row.channel_id));
                }
                else {
                    records.push(rows);
                }
            }
            log.VERBOSE("All records:", records);
            return callback(undefined, records);
        });
    }

    closeConnection() {
        try {
            this.connection.end();
        }
        catch (err) {
            log.ERROR("Error closing connection :", this.connection, err);
            throw err;
        }
    }
}

exports.UserStorage = class UserStorage extends Storage {
    constructor(connection = undefined) {
        super(accountsTable, connection);
    }

    /**
     * Save a new account to the database
     * @param {*} _discordAccountId The Discord ID the the user
     * @param {*} callback The callback to be sent
     * @callback Error|Array|*
     */
    saveAccount(_discordAccountId, callback){
        const sqlQuery  = `INSERT INTO ${this.dbTable} (discord_account_id, balance) VALUES ("${_discordAccountId}", ${0});`;

        log.DEBUG(`Adding new entry with SQL query: '${sqlQuery}'`)

        runSQL(sqlQuery, this.connection, (err, rows) => {
            if (err) return callback(err, undefined);
            if (rows?.affectedRows > 0) return callback(undefined, rows);
            return callback(undefined, undefined);
        })        
    }
    
    /**
     * Check or return the balance of a given account ID
     * 
     * @param {*} _tokensToBeUsed The amount of tokens to be used, set to 0 to return the balance
     * @param {*} _account_id The account ID to check or return the balance of
     * @param {*} callback 
     */
    checkBalance(_tokensToBeUsed, _account_id, callback) {
        if (!_account_id) return callback(new Error("Account not specified when checking account balance"), undefined);
        log.DEBUG("Tokens to verify against balance", _tokensToBeUsed, _account_id);
        if (!_tokensToBeUsed && !_tokensToBeUsed >= 0) return callback(new Error("Specified tokens are invalid when checking account balance"), undefined);
        this.getRecordBy('account_id', _account_id, (err, record) => {
            if (err) return callback(err, undefined);
            
            // Check to see if the account has a balance greater than what was given
            if(_tokensToBeUsed > 0){
                if (record?.balance && record.balance > _tokensToBeUsed) return callback(undefined, true);
                else return callback(undefined, false);
            }

            return callback(undefined, record.balance)
        })
    }
    
    /**
     * Update a user's account Balance
     * 
     * @param {string} _updateType The type of update to make to the account [ withdraw | deposit ]
     * @param {number} _updateAmount The amount to update the account
     * @param {number} _account_id The ID of the discord account to update
     * @param {function} callback The callback function to call with the results
     * @returns Result from the SQL query or false
     */
    updateBalance(_updateType, _updateAmount, _discord_account_id, callback){
        var sqlQuery = "";
        switch(_updateType){
            case "withdraw":
                // Code here to withdraw funds
                sqlQuery = `UPDATE ${this.dbTable} SET balance=balance-${_updateAmount} WHERE discord_account_id = ${_discord_account_id};`;
                break;
            case "deposit":
                // Code here to withdraw funds
                sqlQuery = `UPDATE ${this.dbTable} SET balance=balance+${_updateAmount} WHERE discord_account_id = ${_discord_account_id};`;
                break;
            default:
                log.ERROR('Update type not valid: ', _updateType);
                return callback(new Error("Update type not valid"));
        }
        
        if(!sqlQuery) return callback(new Error("SQL Query empty"), undefined);

        log.DEBUG("Updating Balance with SQL Query: ", sqlQuery);

        runSQL(sqlQuery, this.connection, (err, rows) => {
            if (err) return callback(err, undefined);
            if (!rows?.affectedRows > 0) return callback(new Error("Error updating Balance", rows), undefined);
            return callback(undefined, rows);
        })  
    }
}

exports.TransactionStorage = class TransactionStorage extends Storage {
    constructor(connection = undefined) {
        super(transactionsTable, connection);
    }
    
    createTransaction(transaction, callback){
        var sqlQuery  = `INSERT INTO ${this.dbTable} (transaction_id, account_id, discord_tokens_used, provider_tokens_used, provider_id, order_date) VALUES ("${transaction.transaction_id}", "${transaction.account_id}", "${transaction.discord_tokens_used}", "${transaction.provider_tokens_used}", "${transaction.provider_id}", "${returnMysqlTime()}");`;

        log.DEBUG(`Adding new entry with SQL query: '${sqlQuery}'`)

        runSQL(sqlQuery, this.connection, (err, rows) => {
            if (err) return callback(err, undefined);
            if (rows?.affectedRows > 0) return callback(undefined, rows);
            return callback(undefined, undefined);
        })        
    }            
}

exports.FeedStorage = class FeedStorage extends Storage {
    constructor(connection = undefined) {
        super(rssFeedsTable, connection);
    }

    /**
     *  Wrapper to save a new entry using the storage method configured
     * @param {Array} toBeSaved Entry or Entries to be added 
     * @param {function} callback The callback function to be called with the record when saved     
     */
    create(toBeSaved, callback) {      
        log.DEBUG("To be saved:", toBeSaved);
        log.DEBUG("to be saved length:", toBeSaved.length);
        // If the request was for the Feeds Table
        if (!toBeSaved[0].fields?.title) return callback(Error("No title given"), undefined);
        let newRecords = []
        for (var entry of toBeSaved) {
            entry = entry.fields; 
            log.DEBUG("Entry:", entry);
            this.returnRecord(undefined, entry.title, entry.link, entry.category, entry.guild_id, entry.channel_id, (err, record) => {
                if (err) return callback(err, undefined);                
                newRecords.push(record);
                if (toBeSaved.length === 1) {
                    log.DEBUG("One record to callback with:", record);
                    return callback(undefined, record);
                }
            }, false) // Do not update the if it exists
        }    
        if (!toBeSaved.length === 1) {
            return callback(undefined, newRecords);
        }                
    }    

    /**
     * Check to see if an entry exists in the storage method configured
     * @param {*} title The title of the entry to check if it exists
     * @returns {true|false|*}
     */
    checkForTitle(title, callback) {
        if (!title) return callback(new Error("No title given when checking for title"), undefined)        
        
        this.getRecordBy("title", title, callback);
    }

    /**
     * Save the given entry to the storage medium
     * @param {Object} entryObject The entry object to be saved
     * @param {function} callback The callback to be called with either an error or undefined if successful
     */
    saveEntry(entryObject, callback) {
        log.DEBUG("Saving entry:", entryObject);
        if (!entryObject?.title || !entryObject?.link || !entryObject?.category) {
            return callback(new Error("Entry object malformed, check the object before saving it"), undefined)
        }
    
        const sqlQuery  = `INSERT INTO ${this.dbTable} (title, link, category, guild_id, channel_id) VALUES ("${entryObject.title}", "${entryObject.link}", "${entryObject.category}", "${entryObject.guild_id}", "${entryObject.channel_id}");`;

        log.DEBUG(`Adding new entry with SQL query: '${sqlQuery}'`)

        runSQL(sqlQuery, this.connection, (err, rows) => {
            if (err) return callback(err, undefined);
            return callback(undefined, rows);
        })
    }

    /**
     * Save the given entry to the storage medium
     * @param {Object} entryObject The entry object to be saved
     * @param {function} callback The callback to be called with either an error or undefined if successful
     */
    updateEntry(entryObject, callback) {
        let queryParams = [];
        if (!entryObject.title) return callback(new Error("No title given before updating"), undefined);
        queryParams.push(`title = "${entryObject.title}"`);
        if (!entryObject.link) return callback(new Error("No link given before updating"), undefined);
        queryParams.push(`link = "${entryObject.link}"`);
        if (entryObject.category) queryParams.push(`category = "${entryObject.category}"`);
        if (entryObject.guild_id) queryParams.push(`guild_id = "${entryObject.guild_id}"`);
        if (entryObject.channel_id) queryParams.push(`channel_id = "${entryObject.channel_id}"`);

        let sqlQuery = `UPDATE ${this.dbTable} SET`; 
        
        let i = 0;
        for (const param of queryParams) {
            if (i === queryParams.length-1) {
                sqlQuery = `${sqlQuery} ${param}`
                i += 1;
            }
            else {
                sqlQuery = `${sqlQuery} ${param},`
                i += 1;
            }
        }

        sqlQuery = `${sqlQuery} WHERE title = "${entryObject.title}";`

        log.DEBUG(`Updating entry with SQL query: '${sqlQuery}'`)

        runSQL(sqlQuery, this.connection, (err, rows) => {
            if (err) return callback(err, undefined);
            return callback(undefined, rows);
        })
    }

    /**
     * Delete the given entry from the storage medium
     * @param {*} id The title of the entry to be deleted
     * @param {function} callback The callback to be called with either an error or undefined if successful
     */
    removeEntry(id, callback) {
        if (!id) {
            return callback(new Error("No entry id given before deleting"), undefined)
        }
    
        const sqlQuery  = `DELETE FROM ${this.dbTable} WHERE id = "${id}";`;

        runSQL(sqlQuery, this.connection, (err, rows) => {
            if (err) return callback(err, undefined);
            return callback(undefined, rows[0]);
        })
    }

    /**
     * Returns a record class for the given information, if there's no ID, it will create it
     * @param {*} _id The ID / line number of the record in the storage medium (OPT)
     * @param {*} _title The title of the record
     * @param {*} _link The link to the RSS feed
     * @param {*} _category The category of the record 
     * @param {*} callback Callback function to return an error or the record
     */
    returnRecord(_id, _title, _link, _category, _guild_id, _channel_id, callback, updateEnabled = true) {
        log.DEBUG(`Return record for these values: ID: '${_id}', Title: '${_title}', Category: '${_category}', Link: '${_link}', Guild: '${_guild_id}', Channel:'${_channel_id}', Update Enabled: `, updateEnabled)
        if (!_link && !_title && !_guild_id && !_channel_id) return callback(new Error("No link or title given when creating a record"), undefined);
        let entryObject = {
            "title": _title, 
            "link": _link,
            "guild_id": _guild_id,
            "channel_id": _channel_id
        }
        if (_category) entryObject.category = _category;
        
        if (_id) {        
            entryObject.id = _id;
            if (!updateEnabled) return callback(undefined, undefined);            
            
            this.updateEntry(entryObject, (err, rows) => {
                if (err) return callback(err, undefined);
                this.getRecordBy('id', entryObject.id, (err, record) => {
                    if (err) return callback(err, undefined);
                    return callback(undefined, new RSSSourceRecord(record.id, record.title, record.link, record.category, record.guild_id, record.channel_id));
                })
            })                                                     
        }
        else {                        
            this.checkForTitle(_title, (err, titleExists) => {
                if (!titleExists) {  
                    log.DEBUG("Entry doesn't exist, making one now", entryObject);
                    this.saveEntry(entryObject, (err, rows) => {
                        if (err) return callback(err, undefined);
                        this.getRecordBy("title", entryObject.title, (err, record) => {
                            if (err) return callback(err, undefined);
                            return callback(undefined, new RSSSourceRecord(record.id, record.title, record.link, record.category, record.guild_id, record.channel_id));
                        })                        
                    });
                }
                else{
                    if (!updateEnabled) return callback(undefined, undefined);  
                    
                    this.updateEntry(entryObject, (err, rows) => {
                        if (err) return callback(err, undefined);
                        this.getRecordBy('title', entryObject.title, (err, record) => {
                            if (err) return callback(err, undefined);
                            return callback(undefined, new RSSSourceRecord(record.id, record.title, record.link, record.category, record.guild_id, record.channel_id));
                        })
                    })                                      
                }
            }) 
        }   
    }    
}

exports.PostStorage = class PostStorage extends Storage {
    constructor(connection = undefined) {
        super(rssPostsTable, connection);
    }    

    savePost(_postObject, callback){
        const tempCreationDate = returnMysqlTime();
        log.DEBUG("Saving Post Object:", _postObject);
        if (!_postObject?.postId || !_postObject?.link) {            
            return callback(new Error("Post object malformed, check the object before saving it", _postObject), undefined)
        }

        if (_postObject.link.length > 250) _postObject.link = _postObject.link.substring(0, 250);
    
        const sqlQuery  = `INSERT INTO ${this.dbTable} (post_guid, post_link, post_sent_date) VALUES ("${_postObject.postId}","${_postObject.link}","${tempCreationDate}");`;

        log.DEBUG(`Adding new post with SQL query: '${sqlQuery}'`)

        runSQL(sqlQuery, this.connection, (err, rows) => {
            if (err) return callback(err, undefined);
            return callback(undefined, rows);
        })
    }
}