#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2022/7/19 5:30 PM
# @Author  : lvhongmeng


import pymysql
import json
import requests
import datetime

# file upload to fileserver
# curl -F builds/pingcap/ee/collect_metadb.py=@collect_metadb.py http://fileserver.pingcap.net/upload
# job : https://cd.pingcap.net/view/CD-Refactor/job/send_result_to_group/

metadb_group_url = "https://open.feishu.cn/open-apis/bot/v2/hook/36a7a091-2dea-4a0d-bf98-63b3617938ed"


def message():
    pymysql.install_as_MySQLdb()

    userName = ""
    password = ""
    dbHost = ""
    dbPort = ""
    dbName = ""

    try:
        conn = pymysql.connect(host=dbHost, user=userName, passwd=password,
                               db=dbName, port=dbPort)
        cur = conn.cursor(pymysql.cursors.DictCursor)

        sql = " SELECT " \
              "TABLE_NAMES, " \
              "USERNAME, " \
              "num " \
              "from " \
              " (SELECT " \
              "TABLE_NAMES, " \
              "GROUP_CONCAT(SAMPLE_USER SEPARATOR ',') as USERNAME, " \
              "count(1) num " \
              "from " \
              "(select " \
              "TABLE_NAMES, " \
              "SAMPLE_USER " \
              "from CLUSTER_STATEMENTS_SUMMARY_HISTORY  " \
              "where SUMMARY_BEGIN_TIME> date_sub(curdate(),interval 7 day) " \
              "and SUMMARY_BEGIN_TIME< date_sub(curdate(),interval -1 day) " \
              "and TABLE_NAMES like 'nightly_ms%' " \
              "group by TABLE_NAMES,SAMPLE_USER) t1 " \
              "group by TABLE_NAMES) t2 " \
              "where num > 1 " \
              "order by num DESC "
        print(sql)
        cur.execute(sql)
        records = cur.fetchall()
        record_list = list(records)
        result = "ID\tTable\tUser\n"
        index = 1
        for line in record_list:
            r = "%d\t%s\t[%s]\n" % (index, str(line.get('TABLE_NAMES')), str(line.get('USERNAME')))
            result = result + r
            index = index + 1

        conn.commit()
        return result
    except Exception as e:
        conn.rollback()
        print("Error!")
        print(e)
    finally:
        cur.close()
        conn.close()


def create_feishu_post_data(msg):
    fields = []
    notify_summary = "**📋 Message：**\n"

    notify_summary += msg

    fields.append({
        "is_short": True,
        "text": {
            "content": notify_summary,
            "tag": "lark_md"
        }
    })

    lark_post_data = {
        "msg_type": "interactive",
        "card": {
            "config": {
                "wide_screen_mode": True
            },
            "elements": [
                {
                    "fields": fields,
                    "tag": "div"
                },
                {
                    "tag": "hr"
                }
            ],
            "header": {
                "title": {
                    "content": "【" + str(datetime.date.today()) + "】 nightly metadb progress",
                    "tag": "plain_text"
                }
            }
        }
    }

    return lark_post_data


def send(webhook, msg):
    headers = {
        "Content-Type": "application/json"
    }
    print(msg)
    r = requests.post(webhook, headers=headers, json=msg)

    print(r.text)


if __name__ == '__main__':
    msg = message()
    lark_msg_body = create_feishu_post_data(msg)
    send(metadb_group_url, lark_msg_body)