# 重新跑出来 Last 3 days, active clusters (tikv > 3) trending
import MySQLdb
from datetime import datetime, timedelta

from settings import (
    DB_HOST, DB_PORT, DB_USER, DB_PASSWD, DB_NAME,
    INTERNAL_IP_LIST, BASE_DIR
)


"""
create table `spark_last_3_days_active_clusters_tikv_gte_3` (
    `id` int(11) NOT NULL AUTO_INCREMENT Primary key,
    `date` date NOT NULL,
    `num` int(11) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

create table `spark_last_3_days_active_clusters_tikv_gte_3_tiflash_gte_1` (
    `id` int(11) NOT NULL AUTO_INCREMENT Primary key,
    `date` date NOT NULL,
    `num` int(11) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
"""


def main():
    target_time = datetime.strptime('2020-11-20', '%Y-%m-%d')
    end_at = datetime.now()
    while target_time <= end_at:
        run_task(target_time)
        target_time += timedelta(days=1)


def run_task(target_time):
    last_3_day = target_time - timedelta(days=3)
    last_30_day = target_time - timedelta(days=30)

    target_time_str = target_time.strftime('%Y-%m-%d')
    last_3_day_str = last_3_day.strftime('%Y-%m-%d')
    last_30_day_str = last_30_day.strftime('%Y-%m-%d')

    db = MySQLdb.connect(
        host=DB_HOST,
        port=DB_PORT,
        user=DB_USER,
        passwd=DB_PASSWD,
        db=DB_NAME
    )
    cursor = db.cursor()

    cursor.execute("""
        SELECT count(DISTINCT(tracking_id))
        FROM tidb_clusters
        WHERE
            first_report_at <= %s
            and tracking_id in (
                SELECT tracking_id
                FROM tidb_logs
                WHERE
                   report_time >= %s and
                   report_time <= %s and
                   tikv_node_num >= 3
            )
        """, [last_30_day_str, last_3_day_str, target_time_str])
    count = cursor.fetchall()[0][0]
    cursor.execute("""
        INSERT INTO spark_last_3_days_active_clusters_tikv_gte_3 (`num`, `date`)
        VALUES (%s, %s)
        """, [count, target_time_str])
    cursor.execute('commit;')

    cursor.execute("""
        SELECT count(DISTINCT(tracking_id))
        FROM tidb_clusters
        WHERE
            first_report_at <= %s
            and tracking_id in (
                SELECT tracking_id
                FROM tidb_logs
                WHERE
                   report_time >= %s and
                   report_time <= %s and
                   tikv_node_num >= 3 and
                   tiflash_node_num >= 1
            )
        """, [last_30_day_str, last_3_day_str, target_time_str])
    count = cursor.fetchall()[0][0]
    cursor.execute("""
        INSERT INTO spark_last_3_days_active_clusters_tikv_gte_3_tiflash_gte_1 (`num`, `date`)
        VALUES (%s, %s)
        """, [count, target_time_str])
    cursor.execute('commit;')