import sys
import os

sys.path.append(os.path.dirname(sys.path[0]))  # 上级目录
import datetime
from utils.secure_db_client import get_remote_engine

import requests


"""删除 10天cookie"""


def send_mg(site, report_date, type):
    url = 'http://47.112.96.71:8082/selection/sendMessage'
    data = {
        'account': 'pengyanbing',
        'title': site + '站点 搜索词',
        'content': report_date + ' 搜索词 ' + type + '页面解析错误。请检查'
    }
    requests.post(url=url, data=data, timeout=15)


def init_db(site_name):
    engine = get_remote_engine(
        site_name=site_name,  # -> database "selection"
        db_type="mysql",  # -> 服务端 alias "mysql"
    )

    select_all_cookie = f"SELECT id FROM {site_name}_cookies"
    df_cookie_all_lsit = engine.read_sql(select_all_cookie)
    if not df_cookie_all_lsit.empty:
        cookie_all_lsit = list(df_cookie_all_lsit.id)
        print(f"{site_name} all cookie 总数量：", len(cookie_all_lsit))

    day = ((datetime.datetime.now()) + datetime.timedelta(days=-9)).strftime("%Y-%m-%d")
    select_sql = f"SELECT id FROM {site_name}_cookies WHERE updated_time < '{day}'"
    print(select_sql)
    df_id = engine.read_sql(select_sql)
    if not df_id.empty:
        cookie_lsit = list(df_id.id)
        print("删除数量 10 天前的 cookie ：",len(cookie_lsit))
        delect_sql = f"DELETE FROM {site_name}_cookies WHERE updated_time < '{day}'"
        print(delect_sql)
        with engine.begin() as conn:
            conn.execute(delect_sql)

if __name__ == '__main__':
    site_list = ['us','de','uk','fr','es','it']
    # site_list = ['us']
    for site in site_list:
        spider_us = init_db(site_name=site)
