/**
 * 搜索功能。
 */
"use strict";

let imRepo = require('./database/im.db.js');

/**
 * 搜索与医生签约过的患者，条件：患者姓名。
 */
module.exports.searchPatients = function (userId, userRole, keyword, handler) {
    let sql = "SELECT p.code, p.name, p.birthday, p.sex, p.photo " +
        "FROM wlyy.wlyy_sign_family f, wlyy.wlyy_patient p " +
        "WHERE f.patient = p.code and f.`status`=1  AND p.name LIKE ? ";

    if(userRole == 2){
        // 全科医生
        sql += " AND(f.doctor = ? or f.doctor_health = ?)"
    } else if(userRole == 3){
        // 健康管理师
        sql += " AND(f.doctor = ? or f.doctor_health = ?)"
    }

    imRepo.execQuery({
        "sql": sql,
        "args": ["%" + keyword + "%", userId,userId],
        "handler": handler
    });
};

/**
 * 搜索与患者的私信。
 */
module.exports.searchPatientPM = function (userId, keyword, handler) {
    //var sql = "SELECT p.code, p.name, p.birthday, p.sex, p.photo, m.msg_id, m.content " +
    //    "FROM (SELECT CASE WHEN msg.from_uid = ? THEN msg.to_uid ELSE msg.from_uid END peer_id, msg.msg_id, msg.content " +
    //    "      FROM msg_p2p msg " +
    //    "      WHERE (msg.from_uid = ? OR msg.to_uid = ?) AND msg.content LIKE ? AND type = 1) m, wlyy.wlyy_patient p " +
    //    "WHERE m.peer_id = p.code ORDER BY p.code";
    var sql ="SELECT p1.code, p1. name, p1.birthday, p1.sex, p1.photo, count(1) AS amount, max(p.content) as content,max(p.msg_id) as msg_id, p1. CODE AS chat,'1' as type FROM msg_p2p p, wlyy.wlyy_patient p1 WHERE (( p.to_uid = p1.`code` AND p.from_uid = ? ) OR ( p.from_uid = p1.`code` AND p.to_uid = ? )) AND p.content LIKE ? AND p.type IN (1, 2, 3, 5, 6) GROUP BY p1. CODE, p1. NAME, p1.birthday, p1.sex, p1.photo UNION ALL SELECT t. CODE as code, t. NAME as name, p1.birthday, p1.sex, p1.photo, count(1) AS amount, max(g.content) as content,max(g.msg_id) as msg_id, t. CODE AS chat,'2' as type FROM wlyy.wlyy_patient p1, wlyy.wlyy_talk_group_member p, wlyy.wlyy_talk_group t, msg_group g WHERE (p.member_code = p1.`code`) AND t.`code` = p.group_code AND g.to_gid = t.`code` AND g.content LIKE ? AND t.`code` IN ( SELECT mem.group_code FROM wlyy.wlyy_talk_group_member mem WHERE member_code = ? ) GROUP BY p1. CODE, p1. NAME, p1.birthday, p1.sex, p1.photo, t. CODE"

    imRepo.execQuery({
        "sql": sql,
        "args": [userId, userId, "%" + keyword + "%", "%" + keyword + "%", userId],
        "handler": handler
    });
};
/**
 * 患者聊天记录列表
 * @param userId
 * @param patientId
 * @param keyword
 * @param handler
 */
module.exports.searchPatientPMList = function (userId,keyword,groupId,type, handler) {
    //var temp ="SELECT p1. CODE, p1. NAME, p1.birthday, p1.sex, p1.photo, p1. CODE AS chat FROM msg_p2p p, wlyy.wlyy_patient p1 WHERE (( p.to_uid = p1.`code` AND p.from_uid = ? ) OR ( p.from_uid = p1.`code` AND p.to_uid = ? )) AND p.content LIKE ? AND p.type IN (1, 2, 3, 5, 6) GROUP BY p1. CODE, p1. NAME, p1.birthday, p1.sex, p1.photo UNION ALL SELECT p1. CODE, p1. NAME, p1.birthday, p1.sex, p1.photo, t. CODE AS chat FROM wlyy.wlyy_patient p1, wlyy.wlyy_talk_group_member p, wlyy.wlyy_talk_group t, msg_group g WHERE (p.member_code = p1.`code`) AND t.`code` = p.group_code AND g.to_gid = t.`code` AND g.content LIKE ? AND t.`code` IN ( SELECT mem.group_code FROM wlyy.wlyy_talk_group_member mem WHERE member_code = ? ) GROUP BY p1. CODE, p1. NAME, p1.birthday, p1.sex, p1.photo, t. CODE"
    //var  sql = "select temp.code,temp.name,temp.birthday,temp.sex,temp.photo,temp.chat from ("+temp+") temp where temp.code = ?";
    if(type==1){//如果是单对单的时候
        var sql="SELECT p1. CODE as code, p1. NAME as name, p1.birthday, p1.sex, p1.photo, p.content,p.msg_id, p1. CODE AS chat " +
            "FROM msg_p2p p, wlyy.wlyy_patient p1 WHERE " +
            "(( p.to_uid = ? AND p.from_uid = ? ) OR ( p.from_uid = ? AND p.to_uid = ? )) and (p.to_uid=p1.`code` or p.from_uid =p1.code)" +
            "AND p.content LIKE ? AND p.type IN (1, 2, 3, 5, 6) order by p.msg_id desc;"

        imRepo.execQuery({
            "sql": sql,
            "args": [userId,groupId,userId,groupId,"%" + keyword + "%"],
            "handler": handler
        });
    }
    if(type==2){//如果是多对多的时候g
        var sql="SELECT t. CODE as code, t. NAME as name, p1.birthday, p1.sex, p1.photo, g.content,g.msg_id,t.CODE AS chat FROM wlyy.wlyy_patient p1, wlyy.wlyy_talk_group_member p, wlyy.wlyy_talk_group t, msg_group g WHERE (p.member_code = p1.`code`) AND t.`code` = p.group_code AND g.to_gid = t.`code` AND g.content LIKE ? AND t.`code`  = ? order by g.msg_id desc"

        imRepo.execQuery({
            "sql": sql,
            "args": ["%" + keyword + "%",groupId],
            "handler": handler
        });
    }
}

/**
 * 搜索聊天过的患者(仅限群组聊天)
 */

module.exports.searchGroupPatients = function(userId,keyword,handler){
    var sql ="SELECT g.`code`, g.`name`, (( SELECT group_concat(temp.member_name) FROM wlyy.wlyy_talk_group_member temp WHERE temp.group_code = g. CODE and  t.type=4 AND temp.member_name LIKE ? )) AS con " +
        "FROM msg_group w, wlyy.wlyy_talk_group g, wlyy.wlyy_talk_group_member t " +
        "WHERE w.to_gid = g.`code` and  t.type=4 " +
        "AND t.group_code = g. CODE " +
        "AND w.to_gid IN ( SELECT w1.group_code FROM wlyy.wlyy_talk_group_member w1 WHERE w1.member_code = ? ) " +
        "AND ( g.`name` LIKE ? OR t.member_name LIKE ?) " +
        "GROUP BY g. NAME, g.`code` order by w.msg_id desc";
    imRepo.execQuery({
        "sql": sql,
        "args": ["%" + keyword + "%",userId,  "%" + keyword + "%", "%" + keyword + "%"],
        "handler": handler
    });
}



/**
 * 搜索聊天过的医生及行政团队内成员，条件：医生姓名。
 */
module.exports.searchDoctors = function (userId, keyword, handler) {
    let sql = "SELECT DISTINCT d.code, d.name, d.photo, d.hospital " +
        "FROM msg_statistic ms, wlyy.wlyy_doctor d " +
        "WHERE d.code = ? AND ms.from_gid IS NULL AND (ms.peer_uid = d.code OR ms.uid = d.code) AND d.name like ?" +
        "UNION " +
        "SELECT d.code, d.name, d.photo, d.hospital " +
        "FROM wlyy.wlyy_admin_team_member m1, wlyy.wlyy_admin_team_member m2, wlyy.wlyy_doctor d " +
        "WHERE m1.doctor_code = ? AND m1.team_id = m2.team_id AND m2.doctor_code = d.code AND d.name like ?";

    imRepo.execQuery({
        "sql": sql,
        "args": [userId, "%" + keyword + "%", userId, "%" + keyword + "%"],
        "handler": handler
    });
};

/**
 * 搜索聊天过的医生(仅限点对点)
 */
module.exports.searchP2Pdoctors = function(userId,keyword,handler){
    var sql ="SELECT d. NAME as name , d. CODE as code , d.hospital_name, d.job_name, d.sex, d.photo " +
             "FROM msg_p2p p, wlyy.wlyy_doctor d " +
             "WHERE (( p.from_uid = d. CODE AND p.to_uid = ? ) OR ( p.to_uid = d. CODE AND p.from_uid = ? )) " +
             "AND d.`name` LIKE ? " +
             "GROUP BY d. NAME, d. CODE, d.hospital_name, d.job_name, d.sex, d.photo order by p.msg_id desc";
    imRepo.execQuery({
        "sql": sql,
        "args": [userId, userId, "%" + keyword + "%"],
        "handler": handler
    });
}


/**
 * 搜索聊天过的内容
 */
module.exports.searchDoctorsContent = function(userId,keyword,handler){
    var sql ="SELECT wt. CODE as code, wt.`name`, count(mg.content) AS amount, max(mg.content) AS content,max(mg.msg_id) as msg_id , 2 AS type,wt.type as group_type,'' as photo " +
             "FROM msg_group mg, wlyy.wlyy_talk_group wt " +
             "WHERE mg.to_gid = wt.`code` " +
             "AND mg.content LIKE ? " +
             "AND wt.`code` IN ( SELECT me.group_code FROM wlyy.wlyy_talk_group_member me WHERE me.member_code = ? ) " +
             "AND wt.`code` NOT IN (SELECT me.group_code FROM wlyy.wlyy_talk_group_member me WHERE me.group_code = wt.code and  me.STATUS = 1  AND me.TYPE =4) "+
             "GROUP BY wt. CODE, wt.`name`,wt.type " +
             "UNION ALL " +
             "SELECT d. CODE as code, d. NAME as name, sum(1) AS amount, max(mp.content) AS content,max(mp.msg_id) as msg_id, 1 AS type ,0 as group_type,d.photo as photo " +
             "FROM msg_p2p mp, wlyy.wlyy_doctor d " +
             "WHERE (( mp.from_uid = d. CODE AND mp.to_uid = ? ) OR ( mp.to_uid = d. CODE AND mp.from_uid = ? )) " +
             "AND mp.content LIKE ?  GROUP BY d. NAME, d. CODE";
    imRepo.execQuery({
        "sql": sql,
        "args": ["%" + keyword + "%",userId,  userId,userId, "%" + keyword + "%"],
        "handler": handler
    });
}


/**
 * 搜索聊天过的内容（群组详情,P2P详情）type = 1 p2p type = 2 群组
 */
module.exports.searchDoctorsContentDetail = function(userId,keyword,groupcode,type,handler){
    if(type==2){
        var groupsql ="SELECT wt. CODE as code, wt.`name`,mg.content,mg.msg_id,wt.type as group_type " +
            "FROM msg_group mg, wlyy.wlyy_talk_group wt " +
            "WHERE mg.to_gid = wt.`code` " +
            "AND mg.content LIKE ? " +
            "AND wt.`code` =? ";
        imRepo.execQuery({
            "sql": groupsql,
            "args": ["%" + keyword + "%",groupcode],
            "handler": handler
        });
    }
    if(type==1){
         var p2psql  =  "SELECT d. CODE as code, d. NAME as name,d.photo, mp.content,mp.msg_id,0 as group_type " +
            "FROM msg_p2p mp, wlyy.wlyy_doctor d " +
            "WHERE (( mp.from_uid = ? AND mp.to_uid = ? and mp.from_uid = d.`code`) OR ( mp.to_uid = ? AND mp.from_uid = ? and mp.to_uid = d.`code` )) " +
            "AND mp.content LIKE ? ;";

            imRepo.execQuery({
                "sql": p2psql,
                "args": [groupcode,userId,groupcode,userId,"%" + keyword + "%"],
                "handler": handler
            });
        }
    }



/**
 * 搜索聊天过的医生(仅限群组聊天)
 */
module.exports.searchGroupDoctors = function(userId,keyword,handler){
    var sql ="SELECT g.`code`, g.`name`,'2' as group_type, (( SELECT group_concat(temp.member_name) FROM wlyy.wlyy_talk_group_member temp WHERE temp.group_code = g. CODE and  t.type<4 AND temp.member_name LIKE ? )) AS con " +
            "FROM msg_group w, wlyy.wlyy_talk_group g, wlyy.wlyy_talk_group_member t " +
            "WHERE w.to_gid = g.`code` and  t.type<4 " +
            "AND t.group_code = g. CODE " +
            "AND w.to_gid IN ( SELECT w1.group_code FROM wlyy.wlyy_talk_group_member w1 WHERE w1.member_code = ? ) " +
            "AND W.to_gid NOT IN (SELECT w1.group_code FROM wlyy.wlyy_talk_group_member w1 WHERE w1.group_code = g.code and  w1. STATUS = 1  AND w1.TYPE =4) "+
            "AND ( g.`name` LIKE ? OR t.member_name LIKE ?) " +
            "GROUP BY g. NAME, g.`code` " +
            " UNION ALL" +//下面是行政团队信息
            " SELECT g.id CODE, g. NAME NAME, '1' AS group_type, " +
            "( SELECT group_concat(temp. NAME) FROM wlyy.wlyy_admin_team_member m1, wlyy.wlyy_doctor temp WHERE temp. CODE = m1.doctor_code AND m1.team_id = g.id AND temp.`name` LIKE ? ) AS con " +
            " FROM wlyy.wlyy_admin_team g, wlyy.wlyy_admin_team_member m, wlyy.wlyy_doctor d, msg_statistic ms " +
            " WHERE d. CODE = m.doctor_code AND m.team_id = g.id AND g.id = ms.from_gid AND ms.uid = ? AND ms.last_content_type IN (1, 2, 3, 5, 6) AND (d. NAME LIKE ? OR g. NAME LIKE ?) GROUP BY g.id, g. NAME";
    imRepo.execQuery({
        "sql": sql,
        "args": ["%" + keyword + "%",userId,  "%" + keyword + "%", "%" + keyword + "%", "%" + keyword + "%",userId, "%" + keyword + "%", "%" + keyword + "%"],
        "handler": handler
    });
}

/**
 * 搜索医生消息内容。
 */
module.exports.searchDoctorMessages = function (userId, keyword, handler) {
    let sql = "SELECT d.code, d.name, d.photo, m.msg_id, m.content " +
        "FROM (SELECT CASE WHEN msg.from_uid = ? THEN msg.to_uid ELSE msg.from_uid END peer_id, msg.msg_id, msg.content " +
        "      FROM msg_p2p msg " +
        "      WHERE (msg.from_uid = ? OR msg.to_uid = ?) AND msg.content LIKE ? AND type = 1) m, wlyy.wlyy_doctor d " +
        "WHERE m.peer_id = d.code ORDER BY d.name ASC, m.msg_id DESC";

    imRepo.execQuery({
        "sql": sql,
        "args": [userId, userId, userId, "%" + keyword + "%"],
        "handler": handler
    });
};

/**
 * 搜索医生的讨论组，使用名称。
 */
module.exports.searchGroups = function (userId, keyword, handler) {
    let sql = "SELECT g.code, g.name, m.member_code, m.member_name " +
        "FROM wlyy.wlyy_talk_group g left join wlyy.wlyy_talk_group_member m on g.code = m.group_code " +
        "WHERE g.name like ? OR m.member_name LIKE ? AND m.member_code = ?";

    imRepo.execQuery({
        "sql": sql,
        "args": ["%" + keyword + "%", "%" + keyword + "%", userId],
        "handler": handler
    });
};

/**
 * 搜索医生的讨论组消息，使用消息内容。
 */
module.exports.searchGroupMessages = function (userId, keyword, handler) {
    let sql = "SELECT g.code, g.name, gm.msg_id, gm.content FROM msg_group gm, " +
        "   (SELECT t.id code, t.name name " +
        "    FROM wlyy.wlyy_admin_team t, wlyy.wlyy_admin_team_member m " +
        "    WHERE m.doctor_code = ? AND t.id = m.team_id " +
        "    UNION " +
        "    SELECT m.group_code code, m.group_name name " +
        "    FROM wlyy.wlyy_talk_group_member m " +
        "    WHERE m.member_code = ?) g " +
        "WHERE g.code = gm.to_gid AND gm.type = 1 AND gm.content LIKE ? ORDER BY g.name ASC, gm.msg_id DESC";

    imRepo.execQuery({
        "sql": sql,
        "args": [userId, userId, "%" + keyword + "%"],
        "handler": handler
    });
};