const mysql = require('mysql')
const pool = mysql.createPool({
    host     :  '地址',
    port     :  '端口',
    user     :  'root',
    password :  '密码',
    database :  '数据库'
})

let query = function( sql, values ) {
    return new Promise(( resolve, reject ) => {
        pool.getConnection(function(err, connection) {
            if (err) {
                reject( err )
            } else {
                connection.query(sql, values, ( err, rows) => {
                    if ( err ) {
                        reject( err )
                    } else {
                        resolve( rows )
                    }
                    connection.release()
                })
            }
        })
    })
}

let queryByConn = function (conn,sql,values) {
    return new Promise(( resolve, reject ) => {
        conn.query(sql, values, ( err, rows) => {
            if ( err ) {
                reject( err )
            } else {
                resolve( rows )
            }
        })
    })
}

let exeTrans = (arg) => {//执行事务
    return new Promise(( resolve, reject ) => {
        pool.getConnection((err, connection) => {
            if (err) {
                return reject(err);
            } else {
                connection.beginTransaction(async (err) => {
                    let res = 'success';
                    if (err) return Promise.reject(err);
                    try {
                        if (typeof arg == 'function') {//需要执行具体事务的
                            res = await arg(connection);//传递一个结果出来
                        } else {
                            for (var i = 0; i < arg.length; i++) {
                                var obj = arg[i];
                                await queryByConn(connection, obj.sql, obj.values);
                            }
                        }
                    }catch (e) {
                        console.log('******************exception*********************')
                        console.log(e)
                        connection.rollback();
                        connection.release();
                        return reject(err);
                    }
                    connection.commit();
                    connection.release();
                    return resolve(res);
                })
            }
        })
    })
}

let constInsert = (tblName,obj) => {//构建insert
    let [props,values,ques] = [[],[],[]];
    for(let p in obj){
        props.push(p);
        values.push(obj[p]);
        ques.push('?');
    }
    let sql = `insert into ${tblName} (${props.join(',')}) values(${ques.join(',')})`;
    return query(sql,values);
}

let constUpsert = (tblName,obj) => {//构建Upsert
    let [props,values,ques,updateProps] = [[],[],[],[]];
    for(let p in obj){
        props.push(p);
        updateProps.push(`${p}=?`);
        values.push(obj[p]);
        ques.push('?');
    }
    let sql = `insert into ${tblName} (${props.join(',')}) values(${ques.join(',')}) ON DUPLICATE KEY UPDATE ${updateProps.join(',')}`;
    return query(sql,[...values,...values]);
}

let getInsertStmt = (tblName,obj) => {//获取sql,values
    let [props,values,ques] = [[],[],[]];
    for(let p in obj){
        props.push(p);
        values.push(obj[p]);
        ques.push('?');
    }
    let sql = `insert into ${tblName} (${props.join(',')}) values(${ques.join(',')})`;
    return {sql,values};
}

let constUpdate = (tblName,obj) => {//构建udpate
    let [props,values] = [[],[],[]];
    let id = obj.id;
    delete obj.id;
    for(let p in obj){
        props.push(`${p}=?`);
        values.push(obj[p]);
    }
    let sql = `update ${tblName} set ${props.join(',')} where id = '${id}'`;
    return query(sql,values);
}

let getUpdateStmt = (tblName,obj) => {//获取sql,values
    let [props,values] = [[],[],[]];
    let id = obj.id;
    delete obj.id;
    for(let p in obj){
        props.push(`${p}=?`);
        values.push(obj[p]);
    }
    let sql = `update ${tblName} set ${props.join(',')} where id = '${id}'`;
    return {sql,values};
}

let getUpsertStmt = (tblName,obj) => {//构建Upsert
    let [props,values,ques,updateProps] = [[],[],[],[]];
    for(let p in obj){
        props.push(p);
        updateProps.push(`${p}=?`);
        values.push(obj[p]);
        ques.push('?');
    }
    let sql = `insert into ${tblName} (${props.join(',')}) values(${ques.join(',')}) ON DUPLICATE KEY UPDATE ${updateProps.join(',')}`;
    return {sql,values:[...values,...values]}
}

let paging = (prefix,qryObj,pageSize = 10,pageNo = 1,order) => {//分页查询
    let {where,values} = constructWhere(qryObj);
    let limit = `limit ${(pageNo - 1) * pageSize},${pageSize}`;
    let sql = `${prefix} ${where} ${order ? 'order by ' + order : ''} ${limit} `;
    console.log('*************sql***************')
    console.log(sql);
    console.log(values);
    return query(sql,values);
}

let constructWhere = (qryObj) => {
    let [props,values] = [[],[]];
    for(let p in qryObj){
        if(!qryObj[p]) continue;//值是空的代表不查询，跳过
        if(p.substr(0,4) == 'amb_'){//处理like的情况
            let filedName = p.substr(4,p.length -4);
            props.push(`${filedName} like ? and`);
            values.push(`%${qryObj[p]}%`);
        }else if(p.substr(0,5) == 'null_'){//处理null的情况
            let filedName = p.substr(5,p.length -5);
            if(qryObj[p] == '1'){
                props.push(`(${filedName} is not null and ${filedName} <> '') and`);
            }else{
                props.push(`(${filedName} is null or ${filedName} = '') and`);
            }
        }else if(p.substr(0,4) == 'lte_'){//小于或等于
            let filedName = p.substr(4,p.length -4);
            props.push(`${filedName} <= ? and`);
            values.push(qryObj[p]);
        }else if(p.substr(0,4) == 'gte_'){//大于或等于
            let filedName = p.substr(4,p.length -4);
            props.push(`${filedName} >= ? and`);
            values.push(qryObj[p]);
        }else if(p.substr(0,3) == 'in_'){//indexOf
            let filedName = p.substr(3,p.length -3);
            props.push(`FIND_IN_SET(?,${filedName}) > 0 and`);
            values.push(qryObj[p]);
        }else if(p.substr(0,4) == 'loc_'){//包含字符串
            let filedName = p.substr(4,p.length -4);
            props.push(`LOCATE(?,${filedName}) > 0 and`);
            values.push(qryObj[p]);
        }else if(p.substr(0,9) == 'JSON-CON_'){//JSON_CONTAINS(students->'$[*].id', '1', '$')
            let filedNamesStr = p.substr(9,p.length -9);
            let [tableField,jsonField] = filedNamesStr.split('_');
            props.push(`JSON_CONTAINS(${tableField}->'$[*].${jsonField}', ?, '$') and`);
            values.push(qryObj[p]);
        }else if(p.substr(0,3) == 'or_'){// in(2,3)
            let filedName = p.substr(3,p.length -3);
            props.push(`${filedName} in (${qryObj[p]}) and`);
            // values.push(qryObj[p]);
        }else{//等值的情况
            props.push(`${p}=? and`);
            values.push(qryObj[p]);
        }
    }
    console.log('************props************')
    console.log(props)
    let propsStr = props.join(' ');
    let where = props.length > 0 ? `where ${propsStr.substr(0,propsStr.length - 3)}` : '';//去掉and
    return {where,values} ;
}

module.exports = {pool, query ,constInsert,constUpdate,paging,exeTrans,queryByConn,constructWhere,getInsertStmt,getUpdateStmt,getUpsertStmt,constUpsert}