/**
 * Created by qiujun on 2018/4/13.
 */
const mySqlCli = global.yoho.utils.mysqlCli;
const TABLE_SHOES_ANSWER = 'act_shoes_answers';
const TABLE_SHOES_FRIENDS = 'act_shoes_friends';
const TABLE_USER = 'user';

class ShoesModel extends global.yoho.BaseModel {
    constructor(ctx) {
        super(ctx);
    }


    /**
     * 查询两个用户是不是好友关系
     * @param unionId
     * @param friendUnionId
     * @returns {Promise.<*>}
     */
    async checkFriends(unionId, friendUnionId) {
        let strSql = `SELECT * FROM ${TABLE_SHOES_FRIENDS}
                        WHERE union_id = :unionId
                        AND friend_union_id = :friendUnionId;`;

        return mySqlCli.query(strSql, {
            unionId,
            friendUnionId
        });
    }

    /**
     * 给两个用户添加好友关系
     * @param actId
     * @param unionId
     * @param friendUnionId
     * @returns {Promise.<void>}
     */
    async addFriends(actId, unionId, friendUnionId) {
        let strSql_1 = `SELECT * FROM ${TABLE_USER} WHERE union_id = :unionId;`;
        let strSql_2 = `SELECT * FROM ${TABLE_USER} WHERE union_id = :friendUnionId;`;
        let strInsert_u = `INSERT INTO ${TABLE_SHOES_FRIENDS} (user_id, friend_id, union_id, friend_union_id, act_id)
                        VALUES (:userId, :friendId, :unionId, :friendUnionId, :actId);`;
        let strInsert_f = `INSERT INTO ${TABLE_SHOES_FRIENDS} (user_id, friend_id, union_id, friend_union_id, act_id)
                        VALUES (:friendId, :userId, :friendUnionId, :unionId, :actId);`;

        let userInfo = await mySqlCli.query(strSql_1, {unionId});
        let friendInfo = await mySqlCli.query(strSql_2, {friendUnionId});
        let userId = 0;
        let friendId = 0;

        if (userInfo.length > 0) {
            userId = userInfo[0].id;
        }
        if (friendInfo.length > 0) {
            friendId = friendInfo[0].id;
        }

        if (userId && friendId) {
            // 插两条好友数据，互相为好友
            let ufId = await mySqlCli.insert(strInsert_u, {userId, friendId, unionId, friendUnionId, actId});
            let fuId = await mySqlCli.insert(strInsert_f, {friendId, userId, friendUnionId, unionId, actId});

            return Promise.resolve({
                act_id: actId,
                ufId: ufId,
                fuId: fuId,
                user_id: userId,
                friend_id: friendId,
                union_id: unionId,
                friend_union_id: friendUnionId
            });
        } else {
            return Promise.reject(
                {
                    code: 203,
                    data: {userId: userId, friendId: friendId},
                    message: '用户查询失败'
                });
        }
    }

    /**
     * 通过unionId获取用户id
     * @param unionId
     * @returns {Promise.<*>}
     */
    getUserId(unionId) {
        let strSql = `SELECT id FROM ${TABLE_USER} WHERE union_id = :unionId;`;

        return mySqlCli.query(strSql, {unionId});
    }

    /**
     * 查询用户的游戏记录
     * @param actId
     * @param unionId
     * @returns {Promise.<*>}
     */
    async checkUserPlayed(actId, unionId) {
        let strUser = `SELECT id, user_phone FROM ${TABLE_USER} WHERE union_id = :unionId`;
        let strPlayed = `SELECT user_id, question_id, user_select_answer FROM ${TABLE_SHOES_ANSWER}
                         WHERE user_id = :userId AND act_id = :actId;`;


        let userInfo = await mySqlCli.query(strUser, {unionId});
        let userId = 0;
        let userPhone = 0;

        if (userInfo && userInfo.length > 0) {
            userId = userInfo[0].id;
            let phone = userInfo[0].user_phone;

            if (parseInt(phone, 10) !== 0) {
                userPhone = 1;
            }
        } else {
            return Promise.reject({
                code: 205,
                message: '没有该用户'
            });
        }

        let playedInfo = await mySqlCli.query(strPlayed, {userId, actId});

        if (playedInfo && playedInfo.length > 0) {
            return Promise.resolve(Object.assign({}, {data: playedInfo}, {phone: userPhone}));
        } else {
            return Promise.reject({
                code: 204,
                message: '没有查询到用户游戏记录'
            });
        }
    }

    async getUserPlayedInfo(actId, unionId) {
        let strUser = `SELECT id FROM ${TABLE_USER} WHERE union_id = :unionId`;
        let strPlayed = `SELECT user_id, question_id, user_select_answer FROM ${TABLE_SHOES_ANSWER}
                         WHERE user_id = :userId AND act_id = :actId;`;


        let userInfo = await mySqlCli.query(strUser, {unionId});
        let userId = 0;

        if (userInfo && userInfo.length > 0) {
            userId = userInfo[0].id;
        } else {
            return {
                code: 205,
                message: '没有该用户'
            };
        }

        let playedInfo = await mySqlCli.query(strPlayed, {userId, actId});

        if (playedInfo && playedInfo.length > 0) {
            return {
                code: 204,
                data: playedInfo,
                message: '查询到用户游戏记录'
            };
        } else {
            return {
                code: 200,
                data: [],
                message: '没有查询到用户游戏记录'
            };
        }
    }

    /**
     * 保存用户答题信息(多条)
     * @param actId
     * @param userId
     * @param questionId
     * @param userSelectAnswer
     * @param isCorrect
     * @returns {*}
     */
    saveUserResult(actId, userId, list, index, result) {
        let questionId = list[index].questionId;
        let userSelectAnswer = list[index].userSelectOption;
        let isCorrect = list[index].isCorrect;
        let that = this;


        let strSql = `INSERT INTO ${TABLE_SHOES_ANSWER} (act_id, user_id, question_id, user_select_answer, is_correct)
                        VALUES (:actId, :userId, :questionId, :userSelectAnswer, :isCorrect);`;



        if (index < list.length) {

            mySqlCli.insert(strSql, {
                actId,
                userId,
                questionId,
                userSelectAnswer,
                isCorrect
            }).then(ret => {
                index += 1;
                result.push(ret);
                that.saveUserResult(actId, userId, list, index, result);
            });
        } else {
            return Promise.resolve(result);
        }
    }

    /**
     * 插入用户数据(单条)
     * @param actId
     * @param userId
     * @param questionId
     * @param userSelectAnswer
     * @param isCorrect
     * @param playTime
     * @param clickTimes
     * @returns {Promise.<void>}
     */
    saveResult(actId, userId, questionId, userSelectAnswer, isCorrect, playTime, clickTimes) {
        let strSql = `INSERT INTO ${TABLE_SHOES_ANSWER} (act_id, user_id, question_id, user_select_answer, is_correct, play_time, click_times)
                        VALUES (:actId, :userId, :questionId, :userSelectAnswer, :isCorrect, :playTime, :clickTimes);`;

        return mySqlCli.insert(strSql, {
            actId,
            userId,
            questionId,
            userSelectAnswer,
            isCorrect,
            playTime,
            clickTimes
        });
    }

    /**
     * 获取用户得分
     * @param unionId
     * @param actId
     * @param perScore
     * @returns {Promise.<*>}
     */
    async getUserScore(unionId, actId, perScore) {
        let strSql = `SELECT * FROM ${TABLE_USER} WHERE union_id = :unionId;`;
        let strScore = `SELECT COUNT(*) as count FROM ${TABLE_SHOES_ANSWER} WHERE user_id = :userId
                        AND act_id = :actId AND is_correct=1;`;
        let userInfo = await mySqlCli.query(strSql, {
            unionId
        });

        if (userInfo.length > 0) {
            let userInfoObj = userInfo[0];
            let userId = userInfoObj.id;
            let userAvatar = userInfoObj.user_avatar;
            let userName = userInfoObj.user_name;
            let scoreInfo = await mySqlCli.query(strScore, {
                userId,
                actId
            });

            let score = scoreInfo[0].count * perScore;

            return Promise.resolve({
                userid: userId,
                unionid: unionId,
                nickname: userName,
                headimgurl: userAvatar,
                score: score
            });

        } else {
            return Promise.reject({
                code: 203,
                message: '用户不存在'
            });
        }
    }

    /**
     * 获取用户得分及排名
     * @param unionId
     * @param actId
     * @param perScore
     * @returns {Promise.<*>}
     */
    async getUserRankScore(unionId, actId, perScore) {
        let strUser = `SELECT * FROM ${TABLE_USER} WHERE union_id = :unionId;`;
        let strScore = `SELECT C.* FROM (SELECT (@rowNo := @rowNo + 1) AS rownum, A.* FROM
                        (SELECT SUM(is_correct) as count, 
                        SUM(play_time) as playTime, 
                        SUM(click_times) as clickTimes, 
                        AVG(UNIX_TIMESTAMP(create_time)) as create_time, 
                        user_id from act_shoes_answers WHERE act_id = :actId 
                        group by user_id order by count desc, clickTimes asc, playTime asc, create_time asc) A,
                        (SELECT @rowNo := 0) B) C WHERE C.user_id = :userId;`;

        let userInfo = await mySqlCli.query(strUser, {
            unionId
        });



        if (userInfo.length > 0) {
            let userInfoObj = userInfo[0];
            let userId = userInfoObj.id;
            let userAvatar = userInfoObj.user_avatar;
            let userName = decodeURIComponent(userInfoObj.user_name);
            let scoreInfo = await mySqlCli.query(strScore, {
                userId,
                actId
            });

            if (scoreInfo && scoreInfo.length > 0) {
                let score = scoreInfo[0].count * perScore;
                let rank = scoreInfo[0].rownum;
                let playTime = scoreInfo[0].playTime;
                let clickTimes = scoreInfo[0].clickTimes;
                let createTime = parseInt((new Date()).getTime() / 1000, 10) - parseInt(scoreInfo[0].create_time, 10);

                return {
                    code: 200,
                    userid: userId,
                    unionid: unionId,
                    nickname: userName,
                    headimgurl: userAvatar,
                    score: score,
                    rank: rank,
                    playTime: playTime,
                    clickTimes: clickTimes,
                    createTime: createTime
                };
            } else {
                return {
                    code: 201,
                    unionId: unionId,
                    message: '用户无得分'
                };
            }
        } else {
            return {
                code: 203,
                message: '用户不存在'
            };
        }
    }

    /**
     * 获取好友得分
     * @param userId
     * @param actId
     * @param perScore
     * @param userScore
     * @returns {Promise.<*>}
     */
    async getFriendsScoreList(userId, actId, perScore, userScore) {
        let strSql = `SELECT
                      U.id,
                      U.union_id,
                      U.user_name,
                      U.user_avatar,
                      SF.user_id,
                      SF.friend_id
                      FROM ${TABLE_USER} U
                      INNER JOIN
                      ${TABLE_SHOES_FRIENDS} SF
                      ON U.id = SF.friend_id WHERE SF.user_id = :userId
                      ORDER BY SF.create_time DESC;`;

        let friendsInfo = await mySqlCli.query(strSql, {userId}); // 查询到所有好友信息
        let friendsIds = [];
        let friendList = [];
        let record = {
            winNum: 0,
            dogfallNum: 0,
            failNum: 0
        };

        for (let i = 0; i < friendsInfo.length; i++) { // 把好友id放入数组
            friendsIds.push(friendsInfo[i].id);
        }
        if (friendsIds.length === 0) { // 如果好友数为 0，则直接返回空数组
            return Promise.resolve({
                record,
                friendList
            });
        }

        let friendIds = friendsIds.join(',');
        let strScore = `SELECT user_id, SUM(is_correct) as count FROM ${TABLE_SHOES_ANSWER}
                        WHERE user_id in (${friendIds}) AND act_id = :actId group by user_id;`;
        let scoreInfo = await mySqlCli.query(strScore, {
            actId,
        });

        if (scoreInfo && scoreInfo.length > 0) { // 给friendList插入处理后的结果(得分及信息)
            for (let i = 0; i < scoreInfo.length; i++) {
                let friendId = scoreInfo[i].user_id;
                let friendScore = scoreInfo[i].count * perScore;

                if (friendScore < userScore) { // 处理输赢数量
                    record.winNum += 1;
                } else if (friendScore === userScore) {
                    record.dogfallNum += 1;
                } else {
                    record.failNum += 1;
                }
                for (let j = 0; j < friendsInfo.length; j++) {
                    if (friendId === friendsInfo[j].id) {
                        let friend_obj = {};

                        friend_obj.score = friendScore;
                        friend_obj.unionid = friendsInfo[j].union_id;
                        friend_obj.nickname = friendsInfo[j].user_name;
                        friend_obj.headimgurl = friendsInfo[j].user_avatar;

                        friendList.push(friend_obj);
                        break;
                    }
                }
            }
        }

        return Promise.resolve({
            record,
            friendList
        });

    }

    /**
     * 新版获取好友得分带使用时间，点击次数排名
     * @param actId
     * @param perScore
     * @returns {Promise.<Array>}
     */
    async getAllUserScoreList(actId, perScore) {
        let strScore = `SELECT C.* FROM
                        (SELECT (@rowNo := @rowNo + 1) AS rownum,
                        A.* FROM
                        (SELECT
                        U.union_id,
                        U.user_name,
                        U.user_avatar,
                        U.user_phone,
                        SUM(S.is_correct) as count,
                        SUM(S.play_time) as playTime,
                        SUM(S.click_times) as clickTimes,
                        AVG(UNIX_TIMESTAMP(S.create_time)) as create_time,
                        S.user_id as user_id
                        FROM ${TABLE_USER} AS U INNER JOIN
                        ${TABLE_SHOES_ANSWER} AS S
                        ON U.id = S.user_id
                        WHERE S.act_id=:actId
                        group by S.user_id
                        order by count desc, clickTimes asc, playTime asc, create_time asc) A, (SELECT @rowNo := 0) B) C 
                        limit 0, 100`;

        let scoreInfo = await mySqlCli.query(strScore, {
            actId
        });

        let friendList = [];

        if (scoreInfo && scoreInfo.length > 0) {
            for (let i = 0; i < scoreInfo.length; i++) {
                let friendObj = {};

                friendObj.score = scoreInfo[i].count * perScore;
                friendObj.rank = scoreInfo[i].rownum;
                friendObj.userId = scoreInfo[i].user_id;
                friendObj.headimgurl = scoreInfo[i].user_avatar;
                friendObj.nickname = decodeURIComponent(scoreInfo[i].user_name);
                friendObj.playTime = scoreInfo[i].playTime;
                friendObj.clickTimes = scoreInfo[i].clickTimes;
                friendObj.createTime = parseInt((new Date()).getTime() / 1000, 10) - parseInt(scoreInfo[i].create_time, 10);

                // friendObj.unionid = scoreInfo[i].union_id.split('').reverse().join(''); // unionId反过来排列后再输出

                friendList.push(friendObj);
            }

            return Promise.resolve(friendList);
        }
    }

    /**
     * 获取排名列表的表格list，用于后台展示
     * @param actId
     * @param perScore
     * @param limit
     * @returns {Promise.<Array>}
     */
    async getUserPhoneList(actId, perScore, limit) {
        let strScore = `SELECT C.* FROM
                        (SELECT (@rowNo := @rowNo + 1) AS rownum,
                        A.* FROM
                        (SELECT
                        U.union_id,
                        U.user_name,
                        U.user_avatar,
                        U.user_phone,
                        SUM(S.is_correct) as count,
                        SUM(S.play_time) as playTime,
                        SUM(S.click_times) as clickTimes,
                        AVG(UNIX_TIMESTAMP(S.create_time)) as create_time,
                        S.user_id as user_id
                        FROM ${TABLE_USER} AS U INNER JOIN
                        ${TABLE_SHOES_ANSWER} AS S
                        ON U.id = S.user_id
                        WHERE S.act_id=:actId
                        group by S.user_id
                        order by count desc, clickTimes asc, playTime asc, create_time asc) A, (SELECT @rowNo := 0) B) C 
                        limit 0, ${limit}`;


        let scoreInfo = await mySqlCli.query(strScore, {
            actId
        });

        let friendList = [];

        if (scoreInfo && scoreInfo.length > 0) {
            for (let i = 0; i < scoreInfo.length; i++) {
                let friendObj = {};

                friendObj.score = scoreInfo[i].count * perScore;
                friendObj.rank = scoreInfo[i].rownum;
                friendObj.userId = scoreInfo[i].user_id;
                friendObj.userPhone = scoreInfo[i].user_phone;
                friendObj.headimgurl = scoreInfo[i].user_avatar;
                friendObj.nickname = decodeURIComponent(scoreInfo[i].user_name);
                friendObj.playTime = scoreInfo[i].playTime;
                friendObj.clickTimes = scoreInfo[i].clickTimes;
                friendObj.createTime = new Date(scoreInfo[i].create_time * 1000).toLocaleString();

                // friendObj.unionid = scoreInfo[i].union_id.split('').reverse().join(''); // unionId反过来排列后再输出

                friendList.push(friendObj);
            }

            return Promise.resolve(friendList);
        }
    }

    deleteUserPlayInfo(actId, userIds) {
        let strSQL = `DELETE FROM ${TABLE_SHOES_ANSWER} 
                      WHERE act_id = :actId 
                      AND user_id in (:userIds)`;

        return mySqlCli.delete(strSQL, {actId, userIds}).then(res => {
            return {
                userId: userIds,
                actId: actId,
                data: res || 0
            };
        });
    }

}

module.exports = ShoesModel;