import requests
import sqlite3
import json
import os
import datetime
from weather import all_city
import logging

# 配置日志级别和格式
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')

# 创建一个文件处理器，指定日志文件名和写入模式
os.mkdir("/data/weather/") if not os.path.exists("/data/weather/") else None
file_handler = logging.FileHandler('/data/weather/app.log')
file_handler.setLevel(logging.INFO)

# 创建一个格式化程序，用于定义日志消息的显示方式
formatter = logging.Formatter('%(asctime)s - %(levelname)s - %(message)s')
file_handler.setFormatter(formatter)

# 将文件处理器添加到根日志记录器中
logging.getLogger('').addHandler(file_handler)

app_id = "69334222"
app_secret = "2u4bHXHD"

province_dict = {
"11": '北京市',
"12": '天津市',
"13": '河北省',
"14": '山西省',
"15": '内蒙古自治区',
"21": '辽宁省',
"22": '吉林省',
"23": '黑龙江省',
"31": '上海市',
"32": '江苏省',
"33": '浙江省',
"34": '安徽省',
"35": '福建省',
"36": '江西省',
"37": '山东省',
"41": '河南省',
"42": '湖北省',
"43": '湖南省',
"44": '广东省',
"45": '广西壮族自治区',
"46": '海南省',
"50": '重庆市',
"51": '四川省',
"52": '贵州省',
"53": '云南省',
"54": '西藏自治区',
"61": '陕西省',
"62": '甘肃省',
"63": '青海省',
"64": '宁夏回族自治区',
"65": '新疆维吾尔自治区'
}

db = "/data/weather/weather.db"
def init_data():
    # 导入全部的城市
    conn = sqlite3.connect(db)
    cursor = conn.cursor()


    cursor.execute(
        """
        CREATE TABLE IF NOT EXISTS `district` (
        `id` bigint(20) NOT NULL,
        `city` varchar(50) NOT NULL DEFAULT '',
        `pid` bigint(20) NOT NULL
        );
        """
    )
    cursor.execute(
        """
        CREATE TABLE IF NOT EXISTS `day_data` (
        `id` INTEGER PRIMARY KEY,
        `province` varchar(50) NOT NULL,
        `city` varchar(50) NOT NULL DEFAULT '',
        `district` varchar(50) NOT NULL DEFAULT '',
        `content` TEXT NULL DEFAULT ''
        );
        """
    )

    cursor.execute(
        """
        CREATE TABLE IF NOT EXISTS `server_day_data` (
        `id` INTEGER PRIMARY KEY,
        `province` varchar(50) NOT NULL,
        `city` varchar(50) NOT NULL DEFAULT '',
        `district` varchar(50) NOT NULL DEFAULT '',
        `content` TEXT NULL DEFAULT ''
        );
        """
    )

    sql = """
        select * from district where id > 65
    """
    cursor.execute(sql)
    results = cursor.fetchall()
    if not results:
        print("填充原始数据")
        for k in all_city:
            sql = """
            INSERT INTO `district` (`id`, `city`, `pid`) VALUES (?, ?, ?)
            """
            cursor.execute(sql, (k[0], k[1], k[2], ))
        conn.commit()
    conn.close()

def main():
    init_data()
    logging.warning("up...")
    conn1 = sqlite3.connect(db)
    cursor1 = conn1.cursor()
    sql = """
        select * from district where id > 65
    """
    cursor1.execute(sql)
    results = cursor1.fetchall()
    # 更新数据
    for i, row in enumerate(results):
        temp = row[1]
        id = row[0]
        pid = row[2]
        if len(str(pid)) == 2:
            city = temp 
            district = ""
        else:
            cursor1.execute('select city from district where id = ?', (pid,))
            te = cursor1.fetchone()
            city = te[0] 
            district = temp
        province_id = (str(row[0]))[:2]
        province = province_dict.get(province_id)
        try:
            today_url = f"https://v0.yiketianqi.com/api?unescape=1&version=v62&appid={app_id}&appsecret={app_secret}&adcode={id}"
            today_response = requests.get(today_url)
            today_data = json.loads(today_response.text)
            if "errcode" not in today_data.keys():
                today_sql = """
                    INSERT OR REPLACE INTO `day_data` (`id`,`province`, `city`, `district`, content) VALUES (?, ?, ?, ?, ?)
                """
                cursor1.execute(today_sql, (i, province, city, district,str(today_data)))
            server_day_url = f"https://v0.yiketianqi.com/api?unescape=1&version=v91&appid={app_id}&appsecret={app_secret}&adcode={id}"
            server_day_response = requests.get(server_day_url)
            server_day_data = json.loads(server_day_response.text)
            if "errcode" not in server_day_data.keys():
                server_sql = f"""
                    INSERT OR REPLACE INTO `server_day_data` (`id`,`province`, `city`, `district`, `content`) VALUES (?, ?, ?, ?, ?)
                """
                cursor1.execute(server_sql, (i, province, city, district,str(server_day_data),))
            conn1.commit()
        except Exception as e:
            print(e)
    conn1.close()
    logging.warning("over...")


if __name__ == "__main__":
    main()