const query = require('../db/db-query');

module.exports = async (pageInfo) => {
    let { skip, limit, order, searchQueries, table } = pageInfo;
    try {
        //判斷request table，並給予相對應參數
        let tableStatus = table == 'user' ? 'USER_STATUS' : 'MEETING_STATUS';
        let tableOrder = table == 'user' ? 'USER_ID' : 'MEETING_NUMBER';

        //where
        let sqlWhere = ` WHERE ${tableStatus} = '1'`;
        //用於會議資料查詢條件
        if (searchQueries) sqlWhere += setSearchQuery(searchQueries);

        //Select order
        let sqlSelect = `SELECT * FROM ${table} `;
        let sqlOrder = ` ORDER BY ${tableOrder} ${order}`;

        //limit for pagination
        let sqlLimit = ` LIMIT ${skip} ,${limit}`;

        //Combine query
        let sql = sqlSelect + sqlWhere + sqlOrder + sqlLimit;
        let pageData = await query(sql);

        //Table rows count
        let sqlCount = `SELECT COUNT(*) FROM ${table}`;
        let dataCount = await query(sqlCount + sqlWhere);
        dataCount = dataCount[0]['COUNT(*)'];

        //換算資料頁數
        let totalPages = Math.ceil(parseInt(dataCount) / parseInt(limit));
        return {
            totalPages, //頁數
            pageData, //資料
        };
    } catch (err) {
        return err;
    }
};

//設置查詢條件query
const setSearchQuery = (searchQueries) => {
    let sqlForm = '';
    //判斷是否為標籤
    const isHashTag = (keyword) => {
        if (keyword.includes('#')) {
            const keywordWithoutTag = keyword.replace('#', '');
            return {
                isTag: true,
                keyword: keywordWithoutTag,
            };
        } else {
            return {
                isTag: false,
                keyword: keyword,
            };
        }
    };

    //處理多組關鍵字
    const mutipleKeyWord = (keywords) => {
        keywords.forEach((keyWord) => {
            sqlForm += isHashTag(keyWord).isTag
                ? ` AND MEETING_TAGS LIKE'%${isHashTag(keyWord).keyword}%'`
                : ` AND MEETING_TEXT LIKE'%${keyWord}%'`;
        });
    };

    //時間
    if (searchQueries.startTime && searchQueries.endTime)
        sqlForm += ` AND MEETING_DATE BETWEEN '${searchQueries.startTime}' AND '${searchQueries.endTime}'`;

    //關鍵字
    if (searchQueries.keyWord) mutipleKeyWord(searchQueries.keyWord);

    //主席
    if (searchQueries.charperson) sqlForm += ` AND MEETING_CHARPERSON LIKE'%${searchQueries.charperson}%'`;

    //紀錄
    if (searchQueries.minute) sqlForm += ` AND MEETING_MINUTE LIKE'%${searchQueries.minute}%'`;

    return sqlForm;
};
