const express = require('express')
const app = express()
const router = express.Router()
const fs = require("fs"); 
const path = require('path');
const port = 8091
const exec = require('child_process').execFile;
const progressStream = require('progress-stream');
const cookieParser = require('cookie-parser')
const crypto = require('crypto');
const multer  = require('multer');
const moment = require('moment');

var request = require('sync-request');

const execSql = require('./mysql-tool.js');
var nodemailer = require("nodemailer");

app.use(express.static('public'))

app.use(express.json()) // for parsing application/json
app.use(express.urlencoded({ extended: true })) // for parsing application/x-www-form-urlencoded

app.use(cookieParser())

//------------------------------Sqlite-------queryMysql--------------------------

let runSql = async function (sql,param){

    await execSql(sql,param)
    //db.close()
}
let selectOneSql =async function (sql,param){

    let row = await execSql(sql,param)
    //db.close()
	return row[0];
}
let selectListSql =async function (sql,param){
	
    let rows = await execSql(sql,param)
    //db.close()
	return rows;
}



//------------------------------返回结果对象封装--------result-------------------------
let result = function(success,message,data){
	let code = success ? 200 : 9999;
	var result = {
		success:success,
		code:code,
		message:message,
		data:data
	};
	return result;
};
//------------------------------------定义session--------------------session使用完要清除 目前未实现--------------------------------------
let session = {
	map:{},
	set:function(key,value){
		this.map[key] = value;
	},
	get:function(key){
		return this.map[key];
	}
};
//---------------------------------------公共方法sql--------------------------------------------------

let commonFun = {
	
	
	writeFile:function(str){
		fs.appendFile('info.log', str,  function(err) {
		});
	},
	dateFormat:function(date,format) {
		var format_arr = {
			"yyyy": date.getFullYear(),
			"mm": date.getMonth() + 1 >= 10 ? date.getMonth() + 1 : "0" + (date.getMonth() + 1),
			"dd": date.getDate() < 10 ? "0"+date.getDate():date.getDate(),
			"HH": date.getHours()<10 ? "0"+date.getHours():date.getHours(),
			"MM": date.getMinutes()<10?"0"+date.getMinutes():date.getMinutes(),
			"ss": date.getSeconds()<10?"0"+date.getSeconds():date.getSeconds(),
		};
		for (var key in format_arr) {
			format = format.replace(key, format_arr[key]);
		}
		return format;
	},
	
	
};

//-----------------------------------请求处理器--------------------------------------------------
app.all('/*', function (req, res, next) {

	next() // pass control to the next handler
})

///---------------------------------邮箱---------------------------------------//////

// 参数：发件人，收件人，主题，正文（支持html格式）
function sendMail(from, aliasName, tos, subject, msg)
{
    const smtpTransport = nodemailer.createTransport({
    host: 'smtp.qq.com',
    secureConnection: true, // use SSL
    secure: true,
    port: 465,
    auth: {
        user: from,
        pass: '13508901457wjs',
    }
    });

    smtpTransport.sendMail({
        //from    : '标题别名 <foobar@latelee.org>',
        from    : aliasName + ' ' + '<' + from + '>',
        //'li@latelee.org, latelee@163.com',//收件人邮箱，多个邮箱地址间用英文逗号隔开
        to      : tos,
        subject : subject,//邮件主题
        //text    : msg,
        html    : msg
    }, function(err, res) {
        if (err)
        {
            console.log('error: ', err);
        }
    });
}
///---------------------------------邮箱---------------------------------------//////
function nl2br(str, isXhtml) {
    var breakTag = (isXhtml || typeof isXhtml === 'undefined') ? '<br />' : '<br>';
    var str = (str + '').replace(/&/g, "&amp;").replace(/</g, "&lt;").replace(/>/g, "&gt;").replace(/"/g, "&quot;").replace(/'/g, "&apos;");
    return (str + '').replace(/([^>\r\n]?)(\r\n|\n\r|\r|\n)/g, '$1' + breakTag + '$2');
}
function getClientIP(req) {
	let ip = req.headers['x-forwarded-for'] || // 判断是否有反向代理 IP
			 req.connection.remoteAddress || // 判断 connection 的远程 IP
			 req.socket.remoteAddress || // 判断后端的 socket 的 IP
			 req.connection.socket.remoteAddress;
	console.log('ip='+ip);
    return ip;
}

//-----------------------------------cxy请求处理器--------------------------------------------------
app.post('/cxy/addTool', async(req, res) => {
	let param = req.body;
	let toolID = param.toolID;
	let imgSrc = param.imgSrc;
	let h3 = param.h3;
	let target = param.target;
	let id = moment(Date.now()).format('YYYYMMDDHHmmss');
	
	let selectSql = "select IFNULL(max(sort),0) as sort from item t where toolID=?";
	let sqlParems = [toolID];
	const data = await selectOneSql(selectSql,sqlParems);
	let sort = data.sort + 1;
	let createTime = new Date();
	let insertSql = "insert into item (id,toolID,imgSrc,h3,target,sort,createTime) values (?,?,?,?,?,?,?)";
	let sqlParams = [id,toolID,imgSrc,h3,target,sort,createTime];
	
	await runSql(insertSql,sqlParams);

	let obj = result(true,'addTool','success');
	res.send(obj);
})

app.get('/cxy/loadTool', async(req, res) => {
	let id = req.query.id;
	let selectSql = "select * from tool order by sort ";
	let sqlParams = [];
	if(id != null && id != ""){
		selectSql += " where id=?"
		sqlParams = [id];
	}
	const data = await selectListSql(selectSql,sqlParams);

	let obj = result(true,'loadTool',data);
	res.send(obj);
})

app.get('/cxy/visitNumber', async(req, res) => {
	let id = req.query.id;
	let selectSql = "select count(*) count from visitLog ";
	let sqlParams = [];
	const data = await selectOneSql(selectSql,sqlParams);

	let obj = result(true,'visitNumber',data);
	res.send(obj);
})

app.post('/cxy/visitLog', async(req, res) => {
	let addr = getClientIP(req);
	let remoteIP = req.body.remoteIP;
	let remoteAddr = req.body.remoteAddr;
	if(addr != null && addr != ''){
		addr = addr.replace(/\:/g,'').replace(/f/g,'')
	}
	
	let selectSql = "select * from visitLog where ipAddr = ? and createTime between ? and ? ";
	let startDate = commonFun.dateFormat(new Date(),'yyyy-mm-dd') + ' 00:00:00';
	let endDate = commonFun.dateFormat(new Date(),'yyyy-mm-dd HH:MM:ss');
	let Params = [addr,startDate,endDate];
	const data = await selectListSql(selectSql,Params);

	let insertSql = "insert into visitLog (visitID,ipAddr,remoteIP,remoteAddr,createTime) values (?,?,?,?,?)";
	let id = moment(Date.now()).format('YYYYMMDDHHmmss');
	let sqlParams = [id,addr,remoteIP,remoteAddr,new Date()];
	await execSql(insertSql,sqlParams);
	
	let obj = result(true,'visitLog','succ');
	res.send(obj);
})

app.get('/cxy/loadGrid', async(req, res) => {
	let id = req.query.id;
	let selectSql = "select t.id,t.toolID,t.h3,t.imgSrc,t.target,(select name from tool where id=t.toolID) as toolName,t.sort from item t  ";
	let sqlParams = [];
	if(id != null && id != ""){
		selectSql += " where t.toolID=?"
		sqlParams = [id];
	}
	selectSql += " order by t.toolID asc,t.sort asc ";
	const data = await selectListSql(selectSql,sqlParams);

	let obj = result(true,'loadGrid',data);
	res.send(obj);
})

app.get('/cxy/loadWYmusic', async(req, res) => {
	let id = req.query.id;
	let selectSql = "SELECT musicID,id FROM wymusic ORDER BY RAND() LIMIT 1  ";
	let sqlParams = [];
	
	if(id != null){
		id = parseInt(id) + 1;
		selectSql = "SELECT musicID,id FROM wymusic where id = ?  ";
		sqlParams = [id];
	}
	
	
	let data = await selectOneSql(selectSql,sqlParams);
	if(data == null){
		id = 1;
		selectSql = "SELECT musicID,id FROM wymusic where id = ?  ";
		sqlParams = [id];
		data = await selectOneSql(selectSql,sqlParams);
	}
	let obj = result(true,'loadWYmusic',data);
	res.send(obj);
})

app.get('/cxy/getAction', async(req, res) => {
	let action = req.query.action;
	let obj = result(true,'getAction',null);
	if(action == 'newAdd'){
		obj = result(true,'getAction',action);
	}
	res.send(obj);
})

app.post('/cxy/advise', async(req, res) => {
	let addr = getClientIP(req);
	if(addr != null && addr != ''){
		addr = addr.replace(/\:/g,'').replace(/f/g,'')
	}
	var param = req.body;
	let advise = param.advise;
	let from = '915778187@qq.com';
	let subject = '工具建议';
	let aliasName = '';
	let tos = 'wangjisheng88@126.com';
	let msg = advise;

	sendMail(from, aliasName, tos, subject, msg);
	
	let insertSql = "insert into advise (adviseID,ipAddr,advise,createTime) values (?,?,?,?)";
	let id = moment(Date.now()).format('YYYYMMDDHHmmss');
	let sqlParams = [id,addr,advise,new Date()];
	await execSql(insertSql,sqlParams);
	
	let obj = result(true,'loadGrid','succ');
	res.send(obj);
})
//*******************android app*********************************
app.get('/cxy/getMusicList', async(req, res) => {
        let pageNum = req.query.pageNum;
        let musicType = req.query.musicType;
        console.log(req.query);
        let limit = req.query.limit;
        if(limit == null || limit == undefined || limit == ''){
            limit = 10;
        }
        limit = parseInt(limit);
        pageNum = (parseInt(pageNum) - 1) * limit;
		let sqlParams = [pageNum,limit];
		let mType = parseInt(musicType);
		
		
        let selectSql = "SELECT * FROM android_music where musicID > 0";
        if(mType > 0){
			selectSql= selectSql+" and LOCATE(?, musicType) > 0 ";
			sqlParams = [musicType, pageNum,limit];
		}
		selectSql = selectSql + "  order by musicid desc  limit ? , ? ";
		
        let data = await selectListSql(selectSql,sqlParams);
        res.send(data);
})

//模糊查询
app.get('/cxy/getMusicListByKey', async(req, res) => {
	let keyword = req.query.keyword;
	let selectSql = "SELECT * FROM android_music where LOCATE('.mp3', musicUrl) > 0 and LOCATE(?, musicName) > 0 ";
	let sqlParams = [keyword];
	let data = await selectListSql(selectSql,sqlParams);
	res.send(data);
})
//查询最新的app版本信息
app.get('/cxy/getAppVersionInfo', async(req, res) => {

	let selectSql = "SELECT * FROM android_music where musicID=117  limit 1 " ;
	let sqlParams = [];
	let data = await selectListSql(selectSql,sqlParams);
	res.send(data);
})
//*******************android app*********************************

//*******************微信小程序接口*********************************

app.get('/wx/getUser', async(req, res) => {
	let openid = req.query.openid;
	let selectSql = "SELECT * FROM wx_user where openid = ? ";
	let sqlParams = [openid];
	let data = await selectOneSql(selectSql,sqlParams);
	res.send(data);
})

app.post('/wx/login', async(req, res) => {
	let param = req.body;
	let code = param.code;
	let nickName = param.nickName;
	let avatarUrl = param.avatarUrl;
	
	const appID = "wx07e274c41c0bca41";
	const AppSecret = "e9d9b69d7fe2b7ef8ecfe248fb23f95f";
	let wx_url = "https://api.weixin.qq.com/sns/jscode2session?appid="+appID+"&secret="+AppSecret+"&js_code="+code+"&grant_type=anthorization_code";
	
	let body = request('GET',wx_url).getBody().toString();
	let obj = JSON.parse(body);
	console.log(obj.openid);
	let openid = obj.openid;
	let selectSql = "SELECT id,nickName,avatarUrl,level FROM wx_user where openid = ? ";
	let sqlParams = [openid];
	let data = await selectOneSql(selectSql,sqlParams);
	console.log(data);
	if(data != null && data != undefined){
			res.send(data);
	}else{
			let insertSql = "insert into wx_user (id,openid,nickName,avatarUrl,level,createTime) values (null,?,?,?,?,?) ";
			sqlParams = [openid,nickName,avatarUrl,1,new Date()];
			console.log(openid+'='+nickName+'='+avatarUrl);
			await execSql(insertSql,sqlParams);
			res.send({level:1});
	}
	
})
app.post('/wx/updateUser', async(req, res) => {
	let param = req.body;
	let code = param.code;
	let level = param.level;

	const appID = "wx07e274c41c0bca41";
	const AppSecret = "e9d9b69d7fe2b7ef8ecfe248fb23f95f";
	let wx_url = "https://api.weixin.qq.com/sns/jscode2session?appid="+appID+"&secret="+AppSecret+"&js_code="+code+"&grant_type=anthorization_code";

	let body1 = request('GET',wx_url).getBody().toString();
	let obj = JSON.parse(body1);
	console.log(obj.openid);
	let openid = obj.openid;

	let updateSql = "update wx_user set level=?,updateTime=?  where openid=? ";
	let sqlParams = [level,new Date(),openid];
	await execSql(updateSql,sqlParams);
	let re = result(true,'wx_updateU','succ');
	res.send(re);

})

app.get('/wx/getTopList', async(req, res) => {
	
	let selectSql = "SELECT id,nickName,avatarUrl,level FROM wx_user order by level limit 5 ";
	let sqlParams = [];
	let data = await selectListSql(selectSql,sqlParams);
	res.send(data);
})
app.get('/wx/getLevel', async(req, res) => {
	let level = req.query.level;

	let selectSql = "SELECT * FROM wx_level where level = ? ";
	let sqlParams = [level];
	let data2 = await selectOneSql(selectSql,sqlParams);
	
	res.send(data2);
})
app.get('/wx/getLevelLength', async(req, res) => {
	
	let selectSql = "SELECT max(level) as levelLength FROM wx_level ";
	let sqlParams = [];
	let data = await selectOneSql(selectSql,sqlParams);
	
	res.send(data);
})
//*******************微信小程序接口*********************************

var server = app.listen(port, () => {
	var host = server.address().address
	var port = server.address().port
	console.log("应用实例，访问地址为 http://%s:%s", host, port)
})