import os
from datetime import datetime

import pymysql

CE_ENDPOINT = os.environ["CE_ENDPOINT"]
CE_DB_HOST = os.environ["CE_DB_HOST"]
CE_DB_PORT = 4000
CE_DB_USER = os.environ["CE_DB_USER"]
CE_DB_PASSWD = os.environ["CE_DB_PASSWD"]
CE_DB_NAME = "diagnose-rule-stats"


def lambda_handler(event, context):
    if context.get_remaining_time_in_millis() < 60000:
        raise ValueError("the lambda function's timeout property should be configured at least 60s")

    _task_id = event["task_id"]
    _args = event["kwargs"]
    _start = _args["start_ts"]
    _end = _args["stop_ts"]
    try:
        _rule_id = _args["rule"]
    except KeyError:
        _rule_id = None

    try:
        stats_records = read_stats_records(_start, _end, _rule_id)
        feedback_records = read_feedback_records(_start, _end, _rule_id)
        if len(stats_records) + len(feedback_records) > 10000:
            return {
                "code": 413,
                "id": _task_id,
                "error": "too many records requested",
                "stats_len": len(stats_records),
                "feedback_len": len(feedback_records),
            }
        return {
            "code": 200,
            "id": _task_id,
            "stats": stats_records,
            "feedback": feedback_records,
        }
    except Exception as e:
        return {
            "code": 500,
            "id": _task_id,
            "error": e,
        }


def query_db(sql):
    conn = pymysql.connect(
        host=CE_DB_HOST,
        port=CE_DB_PORT,
        user=CE_DB_USER,
        password=CE_DB_PASSWD,
        database=CE_DB_NAME,
        charset="utf8mb4",
        cursorclass=pymysql.cursors.DictCursor,
    )

    records = []
    with conn:
        with conn.cursor() as cursor:
            cursor.execute(sql)
            records = cursor.fetchall()

        # connection is not autocommit by default. So you must commit to save
        # your changes.
        conn.commit()

    return records


def read_stats_records(start, end, rule=None):
    _start = datetime.fromtimestamp(start).strftime("%Y-%m-%d %H:%M:%S")
    _stop = datetime.fromtimestamp(end).strftime("%Y-%m-%d %H:%M:%S")
    tr = """FROM `rule_trigger_details`
            WHERE ts > '{}' AND ts < '{}' """.format(
        _start,
        _stop,
    )
    if rule and rule > 0:
        sql = """SELECT rule_id, SUM(alert_cnt) AS cnt {} AND rule_id = '{}'
                GROUP BY rule_id""".format(
            tr, rule
        )
    else:
        sql = """SELECT rule_id, SUM(alert_cnt) AS cnt {}
                GROUP BY rule_id""".format(
            tr
        )

    return [
        {
            "rule_id": _line["rule_id"],
            "cnt": int(_line["cnt"]),
        }
        for _line in query_db(sql)
    ]


def read_feedback_records(start, end, rule=None):
    _start = datetime.fromtimestamp(start).strftime("%Y-%m-%d %H:%M:%S")
    _stop = datetime.fromtimestamp(end).strftime("%Y-%m-%d %H:%M:%S")
    tr = """FROM `rule_feedback_details`
            WHERE click_ts > '{}' AND click_ts < '{}' """.format(
        _start,
        _stop,
    )

    _is_detail = rule and rule > 0
    if _is_detail:
        sql = """SELECT rule_id, user, agree, message {} AND rule_id = '{}' """.format(tr, rule)
        return query_db(sql)

    sql = """SELECT rule_id, agree, COUNT(*) AS cnt {}
            GROUP BY rule_id, agree""".format(
        tr
    )

    _raw = query_db(sql)
    result = {}
    for _line in _raw:
        _id = _line["rule_id"]
        try:
            record = result[_id]
        except KeyError:
            record = {"rule_id": _id, "agree": 0, "disagree": 0}
        if _line["agree"] == 0:
            record["disagree"] += _line["cnt"]
        if _line["agree"] == 1:
            record["agree"] += _line["cnt"]
        result[_id] = record

    return list(result.values())