/**
 * 文章model
 * @author: leo <qi.li@yoho.cn>
 * @date: 28/06/2017
 */

const _ = require('lodash');
const mysqlCli = global.yoho.utils.mysqlCli;

const TABLE_USER = 'user';
const TABLE_ACT_ARTICLE = 'act_article';
const TABLE_ACT_ARTICLE_IMG = 'act_article_img';
const TABLE_ACT_ARTICLE_GOOD = 'act_article_good';

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

    /**
     * 获取文章列表
     * @returns {*}
     */
    articleList({actId, pageNo, pageSize, orderBy, order}) {
        const orderMapping = {
            goodCount: 'good_count',
            createTime: 'create_time'
        };

        let limitSql;
        let orderSql;
        let whereSql;

        whereSql = 'WHERE AA.act_id = :actId';
        orderSql = `ORDER BY AA.${orderMapping[orderBy]} ${order}`;
        limitSql = 'LIMIT :start, :page';

        return mysqlCli.query(
            `SELECT 
                AA.id, 
                AA.good_count, 
                AAI.img_url, 
                AA.content, 
                AA.create_time,
                USR.user_name 
            FROM ${TABLE_ACT_ARTICLE} AA
            INNER JOIN ${TABLE_ACT_ARTICLE_IMG} AAI
            INNER JOIN ${TABLE_USER} USR  
            ON AA.id = AAI.article_id AND AA.user_id = USR.id
            ${whereSql} ${orderSql} ${limitSql};`, {
                actId,
                start: (pageNo - 1) * pageSize,
                page: _.parseInt(pageSize)
            }
        );
    }

    /**
     * 获取文章无用户
     * @returns {*}
     */
    articleListWithoutUser({actId, pageNo, pageSize, orderBy, order, dateTime, limitDate}) {
        const orderMapping = {
            goodCount: 'good_count',
            createTime: 'create_time'
        };

        const date = dateTime || '';
        const limit_date = limitDate || '';

        let limitSql;
        let orderSql;
        let whereSql;

        whereSql = 'WHERE AA.act_id = :actId';
        if (date !== '') {
            whereSql += ' AND to_days(AA.create_time) = to_days(\'' + date + '\')';
        }
        if (limit_date !== '') {
            whereSql += ' AND to_days(AA.create_time) <= to_days(\'' + limit_date + '\')';
        }
        orderSql = `ORDER BY AA.${orderMapping[orderBy]} ${order}`;
        limitSql = 'LIMIT :start, :page';

        return mysqlCli.query(
            `SELECT 
                AA.id, 
                AA.good_count, 
                AAI.img_url, 
                AA.content, 
                AA.create_time,
                AA.user_name 
            FROM ${TABLE_ACT_ARTICLE} AA
            INNER JOIN ${TABLE_ACT_ARTICLE_IMG} AAI
            ON AA.id = AAI.article_id 
            ${whereSql} ${orderSql} ${limitSql};`, {
                actId,
                start: (pageNo - 1) * pageSize,
                page: _.parseInt(pageSize)
            }
        );
    }

    /**
     * 获取无图但有用户的文章列表，(用于获取用户得分列表)
     * @param actId
     * @param pageNo
     * @param pageSize
     * @param orderBy
     * @param order
     * @returns {*}
     */
    articleListWithUser({actId, pageNo, pageSize, orderBy, order}) {
        const orderMapping = {
            goodCount: 'good_count',
            createTime: 'create_time'
        };

        let limitSql;
        let orderSql;
        let whereSql;

        whereSql = 'WHERE AA.act_id = :actId';
        orderSql = `ORDER BY AA.${orderMapping[orderBy]} ${order}`;
        limitSql = 'LIMIT :start, :page';

        let strSql = `SELECT @row:=@row+1 as rank, UA.* 
                        FROM(
                            SELECT AA.id, AA.good_count, AA.user_id, U.user_avatar, U.user_name 
                            FROM ${TABLE_ACT_ARTICLE} AA 
                            INNER JOIN ${TABLE_USER} U 
                            ON AA.user_id = U.id 
                            ${whereSql} ${orderSql} ${limitSql}
                            ) AS UA,
                      (select @row:=0) as record;`;

        return mysqlCli.query(strSql, {
            actId,
            start: (pageNo - 1) * pageSize,
            page: _.parseInt(pageSize)
        });
    }

    /**
     * 获取文章列表无图
     * @returns {*}
     */
    articleListWithOutImg({actId, pageNo, pageSize, orderBy, order}) {
        const orderMapping = {
            goodCount: 'good_count',
            createTime: 'create_time',
            id: 'id'
        };

        let limitSql;
        let orderSql;
        let whereSql;

        whereSql = `WHERE act_id = ${actId}`;
        orderSql = `ORDER BY ${orderMapping[orderBy]} ${order}`;
        limitSql = `LIMIT ${(pageNo - 1) * pageSize}, ${pageSize}`;
        return mysqlCli.query(
            `SELECT 
                id, 
                good_count, 
                content, 
                create_time
            FROM ${TABLE_ACT_ARTICLE}
            ${whereSql} ${orderSql} ${limitSql};`
        );
    }

    /**
     * 获取文章总数
     * @returns {*}
     */
    allArticlesNum(actId) {
        return mysqlCli.query(
            `SELECT COUNT(*) AS total FROM ${TABLE_ACT_ARTICLE} WHERE act_id = :actId;`, {
                actId
            }
        ).then(res => {
            return res[0].total;
        });
    }

    /**
     * 获取文章总数及投票总数
     * @returns {*}
     */
    allArticlesNumVote(actId) {
        return mysqlCli.query(
            `SELECT COUNT(*) AS total, SUM(good_count) AS total_count FROM 
            ${TABLE_ACT_ARTICLE} WHERE act_id = :actId;`, {
                actId
            }
        ).then(res => {
            return {total: res[0].total, total_count: res[0].total_count};
        });
    }

    /**
     * 发表文章
     * @param actId 活动ID
     * @param content 文章内容
     * @param imgUrl 图片链接
     * @returns {*}
     */
    createArticle({actId, content, imgUrl}) {
        const session = this.ctx.req.session;
        const userId = _.get(session, 'user.id');

        return mysqlCli.insert(
            `insert into ${TABLE_ACT_ARTICLE} (act_id, user_id, content) 
                values (:actId, :userId, :content);`,
            {
                actId,
                userId,
                content,
                imgUrl
            }
        );
    }

    /**
     * 发布无图片的文章(主要用于保存用户得分)
     * @param actId
     * @param userId
     * @param score
     * @returns {*}
     */
    createArticleWithoutImg(actId, userId, score) {
        let strSql = `insert into ${TABLE_ACT_ARTICLE} (act_id, user_id, good_count)
                values (:actId, :userId, :score);`;

        return mysqlCli.insert(strSql, {
            actId,
            userId,
            score
        });
    }

    /**
     * 通过actId与userId查找文章
     * @param actId
     * @param userId
     * @returns {*}
     */
    queryArticleByUserId(actId, userId) {
        let strSql = `select * from ${TABLE_ACT_ARTICLE} where 
                    act_id = :actId 
                    and user_id = :userId;`;

        return mysqlCli.query(strSql, {
            actId,
            userId
        });
    }

    /**
     * 通过articleId更新用户的goodCount
     * @param articleId
     * @param score
     * @returns {*}
     */
    updateArticleById(articleId, score) {
        let strSql = `update ${TABLE_ACT_ARTICLE} set 
                        good_count = :score where
                        id = :articleId;`;

        return mysqlCli.update(strSql, {
            score,
            articleId
        });
    }

    /**
     * 文章图片插入
     * @param articleId
     * @param imgUrl
     * @returns {*}
     */
    insertArticleImg(articleId, imgUrl) {
        return mysqlCli.insert(
            `insert into ${TABLE_ACT_ARTICLE_IMG} (article_id, img_url) values (:articleId, :imgUrl);`,
            {
                imgUrl,
                articleId
            }
        );
    }

    /**
     * 获取单个文章详情
     * @param articleId
     * @returns {*}
     */
    getSingleArticle(actId, articleId) {
        return mysqlCli.query(`SELECT AA.id AS id,
            AA.content AS content,
            AA.good_count AS good_count,
            AA.user_id as user_id,
            U.user_name as user_name,
            AA.create_time AS create_time,
            AAI.img_url 
            FROM act_article AS AA 
            LEFT JOIN act_article_img  AS AAI ON AA.id = AAI.article_id
            LEFT JOIN user AS U ON AA.user_id = U.id 
            WHERE AA.act_id = :actId 
            AND AA.id = :articleId;`,
            {
                actId,
                articleId
            }
        );
    }

    /**
     * 通过编号或者姓名获取单个文章详情
     * @param codeName
     * @returns {*}
     */
    getArticleByCodeName(actId, codeName) {
        return mysqlCli.query(`SELECT AA.id AS id,
            AA.content AS content,
            AA.good_count AS good_count,
            AA.create_time AS create_time,
            AA.user_name AS user_name,
            AAI.img_url 
            FROM act_article AS AA 
            LEFT JOIN act_article_img  AS AAI ON AA.id = AAI.article_id
            WHERE AA.act_id = :actId 
            AND (AA.content = :codeName 
            OR AA.user_name = :codeName);`,
            {
                actId,
                codeName
            }
        );
    }

    /**
     * 通过文章ID获取单个文章排名
     * @param articleId
     * @param actId
     * @returns {*}
     */
    getRankById(actId, articleId) {
        return mysqlCli.query(`select rownum from 
            (SELECT (@rowNo := @rowNo + 1) AS rownum, id, good_count FROM 
            (SELECT * from act_article WHERE act_id = :actId order by good_count desc) A, 
            (SELECT @rowNo := 0) B) C  WHERE C.id = :articleId;`,
            {
                actId,
                articleId
            }
        );
    }

    /**
     * 获取单个文章详情
     * @param articleId
     * @param actId
     * @returns {*}
     */
    getArticleById(actId, articleId) {
        return mysqlCli.query(`SELECT AA.id AS id,
            AA.content AS content,
            AA.good_count AS good_count,
            AA.user_name as user_name,
            AA.create_time AS create_time,
            AAI.img_url 
            FROM act_article AS AA 
            LEFT JOIN act_article_img  AS AAI ON AA.id = AAI.article_id
            WHERE AA.act_id = :actId 
            AND AA.id = :articleId;`,
            {
                actId,
                articleId
            }
        );
    }

    /**
     * 获取当前点赞用户点赞的文章的投票限制状态(repeat字段表示该文章是否可以重复投票，vote_limit字段表示该文章)
     * @param actId
     * @param articleId
     * @returns {*}
     */
    getActLimit(actId) {
        let sqlStr = `SELECT repeat_limit, vote_limit FROM activity WHERE 
                        id = ${actId}`;

        return mysqlCli.query(sqlStr);
    }

    /**
     * 获取当前点赞用户点赞时的IP,对应到当天(如果repeat为false,那么代表当前文章不能重复投票)
     * @param actId 活动id
     * @param articleId 文章id
     * @param repeat 是否对于该文章可以重复投票
     * @returns {*}
     */
    getArticleIp(actId, articleId, ip) {
        let sqlStr = `SELECT COUNT(*) as vote_count 
                    FROM ${TABLE_ACT_ARTICLE_GOOD} WHERE act_id = :actId 
                    AND ip = :ip
                    AND article_id = :articleId 
                    AND to_days(create_time) = to_days(now())`;

        return mysqlCli.query(sqlStr, {
            actId,
            ip,
            articleId
        });
    }

    /**
     * 获取当前点赞用户的ip今天已经投票的次数
     * @param actId
     * @param ip
     * @returns {*}
     */
    getIpCount(actId, ip) {
        let sqlStr = `SELECT count(*) AS vote_count
                    FROM ${TABLE_ACT_ARTICLE_GOOD} WHERE act_id = :actId 
                    AND ip = :ip 
                    AND to_days(create_time) = to_days(now())`;

        return mysqlCli.query(sqlStr, {
            actId,
            ip
        });
    }

    /**
     * 文章点赞
     * @param actId
     * @param articleId
     * @returns {*}
     */
    likeArticle(actId, articleId) {
        return mysqlCli.update(
            `UPDATE ${TABLE_ACT_ARTICLE} AA
            SET good_count = good_count + 1
            WHERE AA.act_id = :actId 
            AND AA.id = :articleId;`,
            {
                actId,
                articleId
            }
        );
    }

    /**
     * 插入点赞详情
     * @param actId
     * @param articleId
     * @returns {*}
     */
    insertLikeDetail(actId, articleId) {
        const userId = _.get(this.ctx.req.session, 'user.id', 0);
        const ip = this.header['X-Forwarded-For'] || this.ctx.req.ip || this.ctx.req.connection.remoteAddress;

        return mysqlCli.insert(
            `INSERT INTO ${TABLE_ACT_ARTICLE_GOOD} (act_id, article_id, user_id, ip)
            VALUES (:actId, :articleId, :userId, :ip);`,
            {
                ip,
                userId,
                actId,
                articleId
            }
        );
    }

}

module.exports = ArticleModel;