require('dotenv').config();
const mysql = require('mysql');
class db {
    constructor() {
        this.con = mysql.createConnection({
            host: process.env.DB_HOST,
            user: process.env.DB_USER,
            password: process.env.DB_PASS,
            database: process.env.DB_NAME,
            port: process.env.DB_PORT || 3306,
            charset: 'utf8mb4', 
        });
        this.connectDB();
        this.pool = mysql.createPool({
            connectionLimit: 10,
            host: process.env.DB_HOST,
            user: process.env.DB_USER,
            password: process.env.DB_PASS,
            database: process.env.DB_NAME,
            port: process.env.DB_PORT || 3306,
            waitForConnections: true,
            charset: 'utf8mb4', 
        });
    }

    connectDB() {
        this.con.connect(function (err) {
            if (err) console.log(err);
            else console.log('DB Connection Established SANTMS');
        });
    }

    disconnectDB() {
        this.con.end();
    }


    runSql(sql, event) {
        return new Promise((resolve, reject) => {
            try {
                this.pool.getConnection((err, pconn) => {
                    if (err) {
                        this.con.query(sql, (err, result) => {

                            if (err) {
                                reject({ status: false, data: null, err: err });
                            }
                            else {
                                resolve({ status: true, data: result });
                            }
                        });
                    } else {
                        pconn.query(sql, (err, result, fields) => {
                            pconn.release();
                            if (err) {
                                reject({ status: false, data: null, err: err })
                            } else {
                                resolve({ status: true, data: result });
                            }
                        });
                    }
                });
            } catch (err) {
                reject({ status: false, data: null, err: err });
            }
        });
    }

    runSqlRow(sql, event) {
        return new Promise((resolve, reject) => {
            try {
                this.pool.getConnection((err, pconn) => {
                    if (err) {
                        this.con.query(sql, (err, result) => {
                            if (err) {
                                reject({ status: false, data: null, err: err });
                            }
                            else {
                                var val = (result.length > 0) ? result[0] : '';
                                resolve({ status: true, data: val });
                            }
                        });
                    } else {
                        pconn.query(sql, (err, result, fields) => {
                            pconn.release();
                            if (err) {
                                reject({ status: false, data: null, err: err });
                            } else {
                                var val = (result.length > 0) ? result[0] : '';
                                resolve({ status: true, data: val });
                            }
                        });
                    }
                });
            } catch (err) {
                reject({ status: false, data: null, err: err });
            }
        });
    }

    insertData(table, data) {
        var keys = Object.keys(data);
        var columns = [];
        var valuesAr = [];
        for (var i = 0; i < keys.length; i++) {
            let key = keys[i];
            if (data[key] != null) {
                columns.push(key);
                valuesAr.push("'" + data[key] + "'");
            }
        }
        var values = valuesAr.join(',');
        var sql = "INSERT INTO " + table + " (" + columns + ") VALUES (" + values + ")";
        // console.log("sql",sql)
        return new Promise((resolve, reject) => {
            try {
                // this.pool.getConnection((err, pconn) => {
                    // if (err) {
                        this.con.query(sql, function (err, result) {
                            if (err) {
                                reject({ status: false, insertId: 0, err: err.stack });
                            }
                            else {
                                resolve({ status: true, insertId: result.insertId });
                            }
                        });
                    // } else {
                    //     pconn.query(sql, (err, result, fields) => {
                    //         pconn.release();
                    //         if (err) {
                    //             reject({ status: false, insertId: 0, err: err.stack });
                    //         } else {
                    //             resolve({ status: true, insertId: result.insertId });
                    //         }
                    //     });
                    // }
                // });
            } catch (err) {
                reject({ status: false, insertId: 0, err: err.stack });
            }
        });
    }

    // insertData(table, data) {
    //     var keys = Object.keys(data);
    //     var columns = [];
    //     var placeholders = [];
    //     var values = [];
    //     for (var i = 0; i < keys.length; i++) {
    //         let key = keys[i];
    //         if (data[key] != null) {
    //             columns.push(key);
    //             placeholders.push('?');
    //             values.push(data[key]);
    //         }
    //     }
    //     var columnsStr = columns.join(',');
    //     var placeholdersStr = placeholders.join(',');
    //     var sql = "INSERT INTO " + table + " (" + columnsStr + ") VALUES (" + placeholdersStr + ")";
    //     return new Promise((resolve, reject) => {
    //         try {
    //             this.pool.getConnection((err, pconn) => {
    //                 if (err) {
    //                     reject({ status: false, insertId: 0, err: err.stack });
    //                 } else {
    //                     pconn.query(sql, values, (err, result, fields) => {
    //                         pconn.release();
    //                         if (err) {
    //                             reject({ status: false, insertId: 0, err: err.stack });
    //                         } else {
    //                             resolve({ status: true, insertId: result.insertId });
    //                         }
    //                     });
    //                 }
    //             });
    //         } catch (err) {
    //             reject({ status: false, insertId: 0, err: err.stack });
    //         }
    //     });
    // }
    

    insertDataBatch(table, data) {
        var columns = [];
        var values = [];
        for (var i = 0; i < data.length; i++) {
            if (i == 0) {
                columns = Object.keys(data[i]).join(',');
            }
            let val_arr = Object.values(data[i]).join(',')
            values.push('(' + val_arr + ')');
        }
        var value_str = values.join(',');
        var sql = "INSERT INTO " + table + " (" + columns + ") VALUES " + value_str;
        return new Promise((resolve, reject) => {
            try {
                // this.pool.getConnection((err, pconn) => {
                //     if (err) {
                        this.con.query(sql, function (err, result) {
                            if (err) {
                                reject({ status: false, insertId: 0, err: err.stack });
                            }
                            else {
                                resolve({ status: true, insertId: result.insertId });
                            }
                        });
                //     } else {
                //         pconn.query(sql, (err, result, fields) => {
                //             pconn.release();
                //             if (err) {
                //                 reject({ status: false, insertId: 0, err: err.stack });
                //             } else {
                //                 resolve({ status: true, insertId: result.insertId });
                //             }
                //         });
                //     }
                // });
            } catch (err) {
                reject({ status: false, insertId: 0, err: err.stack });
            }
        });
    }

    updateData(table, data, whereObj) {
        var valuesAr = [];
        Object.keys(data).forEach(function (key) {
            if (data[key] == 'null') {
                data[key] = null;
            }
            if (data[key] != null) {
                valuesAr.push(key + " = '" + data[key] + "'");
            } else {
                valuesAr.push(key + " = " + data[key]);
            }
        });
        var values = valuesAr.join(',');
        var where = (whereObj != null) ? this.getWhere(whereObj) : '';
        var sql = "UPDATE " + table + " SET " + values + " " + where;
        return new Promise((resolve, reject) => {
            try {
                // this.pool.getConnection((err, pconn) => {
                //     if (err) {
                        this.con.query(sql, function (err, result) {
                            if (err) {
                                reject({ status: false, affectedRows: 0, err: err.stack });
                            }
                            else {
                                resolve({ status: true, affectedRows: result.affectedRows });
                            }
                        });
                    // } else {
                    //     pconn.query(sql, (err, result, fields) => {
                    //         pconn.release();
                    //         if (err) {
                    //             reject({ status: false, affectedRows: 0, err: err.stack });
                    //         } else {
                    //             resolve({ status: true, affectedRows: result.affectedRows });
                    //         }
                    //     });
                    // }
                // });

            } catch (err) {
                reject({ status: false, affectedRows: 0, err: err.stack });
            }
        });
    }

    getWhere(obj) {
        var where = " WHERE 1=1";
        var oprtr = (obj['operator'] != undefined) ? obj['operator'] : {};
        var oprtrKey = ' = '
        Object.keys(obj).forEach(function (key) {
            if (key != "pageno" && obj[key] != '') {
                if (key != 'operator') {
                    if (oprtr[key] != undefined && oprtr[key] == 'LIKE') {
                        var val = (obj[key] != null) ? "'%" + obj[key] + "%'" : null;
                        oprtrKey = ' like ';
                    } else if (oprtr[key] != undefined && oprtr[key] == 'IN') {
                        var val = (obj[key] != null) ? "(" + obj[key] + ")" : null;
                        oprtrKey = ' IN ';
                    } else if (oprtr[key] != undefined && oprtr[key] == 'IS') {
                        var val = (obj[key] != null) ? obj[key] : null;
                        oprtrKey = ' IS ';
                    } else if (oprtr[key] != undefined && oprtr[key].includes('NOT_FIND_IN_SET') == true) {
                        var val = (obj[key] != null) ? obj[key] : null;
                        oprtrKey = ' FIND_IN_SET(' + obj[key] + ', ' + key + ')=0';
                    } else if (oprtr[key] != undefined) {
                        var val = (obj[key] != null) ? "'" + obj[key] + "'" : null;
                        oprtrKey = ' ' + oprtr[key] + ' ';
                    } else {
                        var val = (obj[key] != null) ? "'" + obj[key] + "'" : null;
                    }
                    where += " AND " + key + oprtrKey + val;
                }
            }

        });
        return where;
    }

}
module.exports = new db();