import requests
import sqlite3
import json
import time
import datetime
from weather import all_city


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()
    special_time = [0, 8, 16, 17, 15]
    while True:
        now = datetime.datetime.now()
        hour = int(now.hour)
        print(hour, "--------------")
        if hour in special_time:
            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:
                    if pid == 620500000000:
                        print(pid, '=================================')
                        cursor1.execute('select city from district where id = ?', (pid,))
                        te = cursor1.fetchone()
                        city = te[0] 
                        district = temp
                        print(district, city, "-------%%%%%%%%%%%-------")
                    else:
                        print(pid)
                        cursor1.execute('select city from district where id = ?', (pid,))
                        te = cursor1.fetchone()
                        city = te[0] 
                        district = temp
                        print(district, city, "--------------")
                province_id = (str(row[0]))[:2]
                province = province_dict.get(province_id)
                
                if district == '唐河县' or city == "郑州市" or city == "市辖区":
                    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)))
                        print(city, district, province, "---------------------")
                    if hour in special_time:
                        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),))
                            print(city, district, province, "+++++++++++++++++++++")
                    conn1.commit()
            conn1.close()
            print("start sleep...")
            time.sleep(3600)
            


if __name__ == "__main__":
    main()