# coding: UTF-8
# import re
import codecs
# import datetime
import os
import sys
import traceback
sys.path.append('/var/local/mode2/')
import pymysql.cursors

MAILANALYSIS_SETTINGS_PATH = '/var/local/mode2/mode2_mailanalysis_settings'

def get_settings(setting_path, domain=False):
    # DB接続に必要なID,PASS設定を取得
    if domain:
        domain_str = "_"+domain
    else:
        domain_str = ""

    try:
        setting_file_path = f'{setting_path}{domain_str}'
        if not os.path.isfile(setting_file_path):
            return False
        else:
            settings = {}
            settings_file = codecs.open(setting_file_path, 'r', 'utf-8')
            settings_data = settings_file.readlines()
            settings_file.close()
            if len(settings_data) > 0:
                for t in settings_data:
                    t = t.split("=")
                    k = t[0]
                    v = t[1].replace("\n", "")
                    settings[str(k)] = v
        return settings

    except Exception as e:
        # print e
        return False


def get_keywords(settings):
    keywords = []

    try:
        connection = pymysql.connect(host="localhost",
                                     user=settings['mysql_user'],
                                     password=settings['mysql_password'],
                                     db=settings['mysql_db'],
                                     charset='utf8',
                                     cursorclass=pymysql.cursors.DictCursor,
                                     )
        with connection.cursor() as cursor:
            sql = "SELECT * FROM mail_keyword_settings"
            cursor.execute(sql)
            res = cursor.fetchall()
            for r in res:
                keywords.append(r)
        connection.close()

        return keywords

    except Exception as e:
        # print e
        raise e


if __name__ == "__main__":

    try:
        print("start mailanalysis_batch")
        settings_list = []
        target_path = f'/var/local/mode2/'
        for x in os.listdir(target_path):
            if x.startswith('mode2_mailanalysis_settings'):
                if x == 'mode2_mailanalysis_settings':
                    settings_list.append(False)
                else:
                    domain = x.replace("mode2_mailanalysis_settings_", "")
                    settings_list.append(domain)

        for domain in settings_list:
            try:
                settings = get_settings(MAILANALYSIS_SETTINGS_PATH, domain)
                if not settings:
                    pass

                connection = pymysql.connect(host="localhost",
                                             user=settings['mysql_user'],
                                             password=settings['mysql_password'],
                                             db=settings['mysql_db'],
                                             charset='utf8',
                                             sql_mode='',
                                             cursorclass=pymysql.cursors.DictCursor,
                                             )
                if not connection:
                    pass

                keywords = get_keywords(settings)
                for k in keywords:
                    try:
                        with connection.cursor() as cursor:
                            sql = "select date_format(a.updated, '%%Y-%%m-%%d') as dt, "\
                                "sum(tf) as tf, count(tf) as mail_count from "\
                                "mail_message_keyword AS a WHERE keyword='%s' and category_id=%s "\
                                "and a.updated > date_add(current_date, interval -2 day) "\
                                "and DATE_FORMAT(a.updated,'%%Y-%%m-%%d') <= date_add(current_date, interval -1 day) "\
                                "group by DATE_FORMAT(a.updated, '%%Y-%%m-%%d') order by DATE_FORMAT(a.updated, '%%Y-%%m-%%d');" % (
                                    k['keyword'], k['category_id'])

                            cursor.execute(sql)
                            insert_data = cursor.fetchall()

                            if len(insert_data) > 0:
                                # get latest keyword list
                                latest_keywords = get_keywords(settings)
                                latest_keywords_list = []
                                for l in latest_keywords:
                                    latest_keywords_list.append(
                                        l['category_id'])

                                if k['category_id'] in latest_keywords_list:

                                    for i in insert_data:
                                        sql = "INSERT INTO mail_count_summary (date, keyword, category_id, mail_count, word_count, grain) VALUES ('%s', '%s', '%s', '%s', '%s', 'D') " \
                                              "ON DUPLICATE KEY UPDATE date='%s', keyword='%s', category_id='%s', mail_count='%s', word_count='%s', grain='D';" % \
                                              (i['dt'], k['keyword'], k['category_id'], i['mail_count'], i['tf'],
                                               i['dt'], k['keyword'], k['category_id'], i['mail_count'], i['tf'])
                                        # print sql
                                        r = cursor.execute(sql)

                            # 古いデータの削除
                            sql = "DELETE FROM mail_message_keyword WHERE updated < date_add(current_date, interval -62 day);"
                            r = cursor.execute(sql)
                            sql = "DELETE FROM mail_mail_info WHERE updated < date_add(current_date, interval -62 day);"
                            r = cursor.execute(sql)

                    except Exception as e:
                        raise e

                connection.close()
            except Exception as e:
                raise e
        print("end mailanalysis_batch")
    except Exception as e:
        print(traceback(e))