# -*- coding: utf-8 -*-
import sys
sys.path.append("/var/local/mode2")
from functions.mwDB import mwDB


def getSettingKeywords(domain=False, return_id=False):
    """
    @param  bool return_id
    @return list ret (return_id=False)
            dict ret (return_id=True)
    """
    print('getSettingKeywords() in')
    Db = mwDB(domain)
    query = 'SELECT id, keyword1, keyword2, keyword3, keyword4 FROM web_search_keyword ORDER BY id DESC'
    res = Db.fetchone(query)
    Db.close()
    try:
        if res:
            list_all = list(filter(lambda s: s != '', list(res)))
            if len(list_all) > 1:  # [id, 'keyword']
                search_keyword_id = list_all[0]
                print('search_keyword_id:')
                print(search_keyword_id)
                setting_keywords = []
                for s in list_all[1:]:
                    setting_keywords.append(s)
                setting_keywords.sort()
                print('setting_keywords:')
                print(setting_keywords)
                if return_id:
                    ret = dict(id=search_keyword_id,
                               keywords=setting_keywords)  # idも返す
                else:
                    ret = setting_keywords
        print('getSettingKeywords() out')
        if ret:
            return ret
        else:
            return None

    except Exception as e:
        print('getSettingKeywords() error')
        print(str(e), exc_info=True)
        print('getSettingKeywords() out')
        return None


def getNewsFromDB(search_keywords=None, search_keyword_id=None, news_num=8, domain=False):
    """
    @param  list search_keywords
    @param  int  search_keyword_id
    @param  int  news_num
    @return dict ret
    """
    print('getNewsFromDB() in')

    try:
        Db = mwDB(domain)
        query = "SELECT url, title, search_keyword FROM social_news "\
                "WHERE search_keyword_id = (SELECT MAX(id) FROM web_search_keyword) "\
                "ORDER BY article_id;"
        print(query)
        res = Db.fetch(query)
        print(res)
        Db.close()

        ret = {}
        if len(res) == 0:  # データが存在しない場合
            ret['all_keywords'] = dict(news=None)
            print(ret)
            return ret

        news_res_dict = {}
        if search_keywords:
            skeys_num = len(search_keywords)
            for keyword in search_keywords:
                results = []
                # [results.append(dict(url=i[0], title=i[1])) for i in res if i[2].encode(
                #     'utf-8') == keyword]  # search_keyword from db is unicode
                for i in res:
                    if i[2] == keyword:
                        results.append(dict(url=i[0], title=i[1]))
                print(results)
                if len(results):
                    # 表示するデータ数のみ取得.
                    res_by_keyword = dict(news=results[:news_num])
                else:
                    res_by_keyword = dict(news=None)
                news_res_dict[keyword] = res_by_keyword

            if skeys_num == 1:
                ret['all_keywords'] = news_res_dict[search_keywords[0]]
                return ret

            elif skeys_num >= 2:
                top_news_summary = []
                exist_data_keys = []
                for keyword in search_keywords:
                    if news_res_dict[keyword]['news']:
                        if news_res_dict[keyword]['news'][0]['url'] != 'No Data':
                            exist_data_keys.append(keyword)
                exist_data_key_num = len(exist_data_keys)
                if exist_data_key_num > 1:
                    if exist_data_key_num == 2:
                        tmp_news_num = 4

                    elif exist_data_key_num == 3:
                        tmp_news_num = 3

                    elif exist_data_key_num == 4:
                        tmp_news_num = 2

                    exist_data_keys.sort()
                    for keyword in exist_data_keys:
                        # [top_news_summary.append(
                        #     nw) for nw in news_res_dict[keyword]['news'][:tmp_news_num] if nw['url'] != 'No Data']
                        for nw in news_res_dict[keyword]['news'][:tmp_news_num]:
                            if nw['url'] != 'No Data':
                                top_news_summary.append(nw)

                    if len(top_news_summary) == 0:
                        top_news_summary.append(
                            dict(url='No Data', title='No Data'))
                        news_res_dict['all_keywords'] = dict(
                            news=top_news_summary)
                    else:
                        news_res_dict['all_keywords'] = dict(
                            news=top_news_summary[:news_num])

                elif exist_data_key_num == 1:
                    top_news_summary = news_res_dict[exist_data_keys[0]
                                                     ]['news'][:news_num]
                    news_res_dict['all_keywords'] = dict(news=top_news_summary)

                else:
                    news_res_dict['all_keywords'] = dict(news=None)
        else:
            results = []
            [results.append(dict(url=i[0], title=i[1])) for i in res]
            news_res_dict['all_keywords'] = dict(news=results)

        print('getNewsFromDB() out')
        ret = news_res_dict
        print(ret)
        return ret

    except Exception as e:
        print('getNewsFromDB() error')
        print(str(e), exc_info=True)
        news_res_dict = {}
        news_res_dict['all_keywords'] = dict(news=None)

        print('getNewsFromDB() out')
        ret = news_res_dict
        return ret


def getRelatedWordsFromDB(search_keywords, interest_term="today 1-m", domain=False):
    """
    @param  liset  search_keywords
    @param  string interest_term
    @return dict   ret
    """
    print('getRelatedWordsFromDB() in')
    Db = mwDB(domain)
    try:
        # get each related
        ret = {}
        all_relateds = {}
        skey_num = len(search_keywords)
        for keyword in search_keywords:
            query = "SELECT related_word, count FROM social_related_words "\
                    "WHERE search_keyword_id = (SELECT MAX(id) FROM web_search_keyword) "\
                    "and interest_term = %s and search_keyword = %s ORDER BY count DESC LIMIT 5;"
            res = Db.fetch(query, [interest_term, keyword])
            if len(res) == 0:
                all_relateds[keyword] = [
                    dict(word=None, value=None, keyword=keyword)]
            else:
                tmp_data = []
                for r in res:
                    tmp_data.append(
                        dict(word=r[0], value=r[1], keyword=keyword))
                all_relateds[keyword] = tmp_data
        Db.close()
        if skey_num == 1:
            ret['all_keywords'] = all_relateds[search_keywords[0]]
            return ret

        elif skey_num > 1:
            top_related_summary = []
            all_relateds_num = 0
            exist_data_keys = [
                keyword for keyword in search_keywords if all_relateds[keyword][0]['word']]
            exist_data_key_num = len(exist_data_keys)
            if exist_data_key_num > 1:
                if exist_data_key_num == 2:
                    tmp_related_num = 4
                elif exist_data_key_num == 3:
                    tmp_related_num = 3
                elif exist_data_key_num == 4:
                    tmp_related_num = 2
                exist_data_keys.sort()
                for keyword in exist_data_keys:
                    [top_related_summary.append(
                        rw) for rw in all_relateds[keyword][:tmp_related_num]]
                all_relateds['all_keywords'] = top_related_summary[:8]

            elif exist_data_key_num == 1:
                all_relateds['all_keywords'] = all_relateds[exist_data_keys[0]]

            else:
                all_relateds['all_keywords'] = [
                    dict(word=None, value=None, keyword=search_keywords)]

            if all_relateds == None:
                all_relateds['all_keywords'] = [
                    dict(word=None, value=None, keyword=search_keywords)]
            print('getRelatedWordsFromDB() out')
            ret = all_relateds
            print(ret)
            return ret

        if len(ret.keys()) == 0:
            print('getRelatedWordsFromDB() out')
            ret['all_keywords'] = [
                dict(word=None, value=None, keyword=search_keywords)]
            return ret

    except Exception as e:
        print('getRelatedWords() error')
        print(str(e), exc_info=True)
        print('getRelatedWordsFromDB() out')
        ret['all_keywords'] = [
            dict(word=None, value=None, keyword=search_keywords)]
        return ret


def getOvertimeFromDB(search_keywords=None, interest_term="today 1-m", domain=False):
    """
    @param  list search_keywords
    @param  list interest_term
    @return dict ret             e.g. {'all_keywords':{'data':{[]}, 'label':{}},
                                       'key1':{'data':{[]}, 'label':{}},
                                       'keyN':{'data':{[]}, 'label':{}}}
    """

    Db = mwDB(domain)
    ret = {}
    if len(search_keywords) > 1:
        # for some keywords
        query = "SELECT CONCAT(CONCAT('date',':',date(day)),',', "\
            "GROUP_CONCAT(CONCAT(keyword,':',count))) results "\
            "FROM social_trend_overtime WHERE search_keyword_id = "\
            "(SELECT MAX(id) FROM web_search_keyword) "\
            "and interest_term = %s and search_type = 'compare' "\
            "GROUP BY day;"
        tmp_res = Db.fetch(query, [interest_term])
        if len(tmp_res) == 0:
            ret['all_keywords'] = dict(data=None, label=None)
            Db.close()
            return ret
        # ret = (('date:2017-08-10,keyword1:50'),('date2:2017-08-11,key2:60'),,,())
        tmp_ot_data = [dict([i.split(':') for i in j[0].split(',')])
                       for j in tmp_res]
        tmp_ot_labels = tmp_ot_data[0].keys()
        ret['all_keywords'] = dict(data=tmp_ot_data, label=tmp_ot_labels)
        for skey in search_keywords:
            print(skey)
            query = "SELECT CONCAT(CONCAT('date',':',date(day)),',', "\
                "GROUP_CONCAT(CONCAT(keyword,':',count))) results "\
                "FROM social_trend_overtime WHERE search_keyword_id = "\
                "(SELECT MAX(id) FROM web_search_keyword) "\
                "and interest_term = %s and search_type = 'single' and keyword = %s "\
                "GROUP BY day;"
            try:
                tmp_res = Db.fetch(query, [interest_term, skey])
                tmp_ot_data = [dict([i.split(':')
                                     for i in j[0].split(',')]) for j in tmp_res]
                tmp_ot_labels = tmp_ot_data[0].keys()
                ret[skey] = dict(data=tmp_ot_data, label=tmp_ot_labels)
            except Exception as e:
                print('getOvertimeFromDB() error')
                print(e, exc_info=True)

    elif len(search_keywords) == 1:
        query = "SELECT CONCAT(CONCAT('date',':',date(day)),',', "\
            "GROUP_CONCAT(CONCAT(keyword,':',count))) results "\
            "FROM social_trend_overtime WHERE search_keyword_id = "\
            "(SELECT MAX(id) FROM web_search_keyword) "\
            "and interest_term = %s and search_type = 'single' and keyword = %s "\
            "GROUP BY day;"
        tmp_res = Db.fetch(query, [interest_term, search_keywords[0]])
        # tmp_res; e.g (('date:2017-08-10,keyword1:50'),('date2:2017-08-11,key2:60'),,,())
        if len(tmp_res) == 0:
            ret['all_keywords'] = dict(data=None, label=None)
            print(ret)
            Db.close()
            return ret
        try:
            tmp_ot_data = [dict([i.split(':')
                                 for i in j[0].split(',')]) for j in tmp_res]
            tmp_ot_labels = tmp_ot_data[0].keys()
            ret['all_keywords'] = dict(data=tmp_ot_data, label=tmp_ot_labels)
        except Exception as e:
            print('getOvertimeFromDB() error')
            print(str(e), exc_info=True)
            ret['all_keywords'] = dict(data=None, label=None)

    Db.close()

    data = []
    if len(ret) == 0:
        print('getOvertimeFromDB() out')
        ret['all_keywords'] = dict(data=None, label=None)
        return ret

    print('getOvertimeFromDB() out')
    print(ret)
    return ret


def getIPFromDB(period_type, period_from, period_to, domain=False):
    Db = mwDB(domain)
    print('############### getIPFromDB ################')
    print('period_from : ' + period_from)
    print('period_to : ' + period_to)
    q_base = 'SELECT ip_address, cast(sum(access_count) as signed) as access_count FROM web_access_ip'
    q_groupby = ' GROUP BY ip_address'
    q_period_type = 'period_type = %s'
    q_period_range = 'access_date >= %s AND access_date <= %s'
    q_orderby = ' ORDER BY access_count DESC LIMIT 10'
    query = q_base + ' WHERE ' + q_period_type + \
        ' AND ' + q_period_range + q_groupby + q_orderby

    print('query : ' + query)
    dbdata = Db.fetch(query, [period_type, period_from, period_to])
    Db.close()

    ipdata = []
    for i in dbdata:
        ipdata.append({'ADDR': i[0], 'VALUE': i[1]})

    ret = ipdata
    print(ret)

    return ret


def getWebSetting(setting_name, domain=False):
    Db = mwDB(domain)
    print('############### getWebSetting ################')
    query = 'SELECT setting_value FROM web_setting WHERE setting_name = %s'
    ret = Db.fetch(query, [setting_name])
    Db.close()
    print(ret)
    return ret