import YoConfig
from typing import Optional
from fastapi import FastAPI
from fastapi.middleware.cors import CORSMiddleware

from pytrends.request import TrendReq
from datetime import tzinfo
import datetime
import mysql.connector
from mysql.connector import Error

from opencc import OpenCC

app = FastAPI()
cc = OpenCC('s2t')

origins = [
    "*"
]
app.add_middleware(
    CORSMiddleware,
    allow_origins=origins,
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"],
)

connection = mysql.connector.connect(
    host=YoConfig['db']['choozmo_new']['MYSQL_HOST'],
    database=YoConfig['db']['choozmo_new']['MYSQL_DB'],
    user=YoConfig['db']['choozmo_new']['MYSQL_USER'],
    password=YoConfig['db']['choozmo_new']['MYSQL_PASSWORD']
)
cursor = connection.cursor(buffered=True)

@app.get("/")
def read_root():
    return {"Hello": "World"}


@app.get("/items/{item_id}")
def read_item(item_id: int, q: Optional[str] = None):
    return {"item_id": item_id, "q": q}


@app.get("/ts_top")
def get_ts_tops(td: Optional[str] = str(datetime.date.today()), tc: Optional[int] = 3):
    if not connection.is_connected():
        connection.connect()
        cursor = connection.cursor(buffered=True)
    sql = "(SELECT * FROM trending_searches " + ((" where ts_date between '" + td + "' and '" + td + \
        "'+ INTERVAL 1 day ") if td != str(datetime.date.today()) else "") + \
        " ORDER BY ts_date DESC LIMIT 20) ORDER BY ts_date LIMIT " + str(tc)
    cursor.execute(sql)
    records = cursor.fetchall()
    return records


@app.get("/related_queries/{Keyword}")
def get_related_queries(Keyword: str, u: Optional[str] = '', fd: Optional[str] = '2020-01-01', td: Optional[str] = str(datetime.date.today())):
    save_userKw(Keyword, u)
    sql = 'select * from related_queries where rq_kword = \'' + Keyword + '\' '
    cursor.execute(sql)
    # print(str(cursor.rowcount))
    if not cursor.rowcount > 0:
        pytrends = TrendReq(hl='zh-TW', tz=1200, geo='TW')
        kw_list = []
        kw_list.append(Keyword)
        pytrends.build_payload(kw_list, cat=0, timeframe=fd +
                               ' '+td, geo='TW', gprop='')
        KWORD = kw_list[0]
        rqdata = pytrends.related_queries()

        try:
            if not rqdata[KWORD]['rising'] is None:
                for item in rqdata[KWORD]['rising'].values.tolist():
                    sql = 'insert into related_queries(rq_kword,rq_relatedword,rq_count,rq_type) select \'' + \
                        KWORD+'\',\'' + item[0] + '\',\'' + \
                        str(item[1]) + '\',\'rising\' '
                    sql = cc.convert(sql)
                    cursor.execute(sql)
            if not rqdata[KWORD]['top'] is None:
                for item in rqdata[KWORD]['top'].values.tolist():
                    sql = 'insert into related_queries(rq_kword,rq_relatedword,rq_count,rq_type) select \'' + \
                        KWORD+'\',\'' + item[0] + '\',\'' + \
                        str(item[1]) + '\',\'top\' '
                    sql = cc.convert(sql)
                    cursor.execute(sql)
        except:
            print(sql)
        connection.commit()
    else:
        records = cursor.fetchall()
        return records
    sql = 'select * from related_queries where rq_kword = \'' + Keyword + '\' '
    cursor.execute(sql)
    records = cursor.fetchall()
    return records


@app.get("/related_topics/{Keyword}")
def get_related_topics(Keyword: str, u: Optional[str] = '', fd: Optional[str] = '2020-01-01', td: Optional[str] = str(datetime.date.today())):
    save_userKw(Keyword, u)
    sql = 'select * from related_topics where rt_kword = \'' + Keyword + '\' '
    cursor.execute(sql)
    # print(str(cursor.rowcount))
    if not cursor.rowcount > 0:
        pytrends = TrendReq(hl='zh-TW', tz=1200, geo='TW')
        kw_list = []
        kw_list.append(Keyword)
        pytrends.build_payload(kw_list, cat=0, timeframe=fd +
                               ' '+td, geo='TW', gprop='')
        KWORD = kw_list[0]
        rtdata = pytrends.related_topics()
        try:
            if not rtdata[KWORD]['rising'] is None:
                for item in rtdata[KWORD]['rising'].values.tolist():
                    sql = 'insert into related_topics(rt_kword,rt_type,rt_value,rt_formattedValue,rt_link,rt_topic_mid,rt_topic_title,rt_topic_type) select \'' + KWORD+'\',\'rising\',\'' + str(
                        item[0]) + '\',\'' + item[1] + '\',\'' + item[2] + '\',\'' + item[3] + '\',\'' + item[4] + '\',\'' + item[5] + '\' '
                    sql = cc.convert(sql)
                    cursor.execute(sql)
            if not rtdata[KWORD]['top'] is None:
                for item in rtdata[KWORD]['top'].values.tolist():
                    sql = 'insert into related_topics(rt_kword,rt_type,rt_value,rt_hasData,rt_link,rt_topic_mid,rt_topic_title,rt_topic_type) select \'' + KWORD + \
                        '\',\'top\',\'' + item[1] + '\',\'' + str(item[2]) + '\',\'' + item[3] + '\',\'' + \
                        item[4] + '\',\'' + item[5] + '\',\'' + \
                        item[6] + '\' '
                    sql = cc.convert(sql)
                    cursor.execute(sql)
        except:
            print(sql)
        connection.commit()
    else:
        records = cursor.fetchall()
        return records
    sql = 'select * from related_topics where rt_kword = \'' + Keyword + '\' '
    cursor.execute(sql)
    records = cursor.fetchall()
    return records


def save_userKw(Keyword: str, Username: str = ''):
    if not connection.is_connected():
        connection.connect()
        cursor = connection.cursor(buffered=True)
    sql = 'insert into user_searches(us_username,us_search_word) select \'' + \
        Keyword + '\',\'' + Username + '\' '
    cursor.execute(sql)
    return


@app.get("/related_queries1/{Keyword}")
def get_related_queries(Keyword: str, u: Optional[str] = '', fd: Optional[str] = '2020-01-01', td: Optional[str] = str(datetime.date.today())):
    save_userKw(Keyword, u)
    pytrends = TrendReq(hl='zh-TW', tz=1200, geo='TW')
    kw_list = []
    kw_list.append(Keyword)
    pytrends.build_payload(kw_list, cat=0, timeframe=fd +
                           ' '+td, geo='TW', gprop='')
    KWORD = kw_list[0]
    rqdata = pytrends.related_queries()

    if not rqdata[KWORD]['rising'] is None:
        return rqdata[KWORD]['rising'].values.tolist()
    if not rqdata[KWORD]['top'] is None:
        return rqdata[KWORD]['top'].values.tolist()
    return


@app.get("/related_topics1/{Keyword}")
def get_related_topics(Keyword: str, u: Optional[str] = '', fd: Optional[str] = '2020-01-01', td: Optional[str] = str(datetime.date.today())):
    save_userKw(Keyword, u)
    pytrends = TrendReq(hl='zh-TW', tz=1200, geo='TW')
    kw_list = []
    kw_list.append(Keyword)
    pytrends.build_payload(kw_list, cat=0, timeframe=fd +
                           ' '+td, geo='TW', gprop='')
    KWORD = kw_list[0]
    rtdata = pytrends.related_topics()

    if not rtdata[KWORD]['rising'] is None:
        return rtdata[KWORD]['rising'].values.tolist()
    if not rtdata[KWORD]['top'] is None:
        return rtdata[KWORD]['top'].values.tolist()
    return