from typing import Optional
from dataset.util import ResultIter
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
import dataset
import json
import re
from pytube import extract

from opencc import OpenCC

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

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


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


@app.get("/test")
def test():
    db = dataset.connect(
        'mysql://hhh7796hhh:lYmWsu^ujcA1@hhh-v57.cmab1ctkglka.ap-northeast-2.rds.amazonaws.com:3306/xoops?charset=utf8', {'pool_recycle': 3600})

    jData = json.load(open('data.json', encoding='utf8'))
    aa = ""
    cc = db.connections
    cursor = db.query("select * from site_setup;")
    for c in cursor:
        aa = c['id']
    return {aa}


@app.get("/movexoopstostage")
def movexoopstostage(designerid: str = "0", caseid: str = "0"):
    db = dataset.connect(
        'mysql://hhh7796hhh:lYmWsu^ujcA1@hhh-v57.cmab1ctkglka.ap-northeast-2.rds.amazonaws.com:3306/xoops?charset=utf8', {'pool_recycle': 3600})

    cursor = db.query(
        "replace INTO stage._hdesigner SELECT * FROM xoops._hdesigner WHERE hdesigner_id IN ('" + designerid+"');")
    cursor = db.query(
        "replace INTO stage._hcase SELECT * FROM xoops._hcase WHERE hcase_id IN ('"+caseid.replace(',', "','")+"');")
    cursor = db.query(
        "replace INTO stage._hcase_img SELECT * FROM xoops._hcase_img WHERE hcase_id IN ('"+caseid.replace(',', "','")+"');")
    return {"success"}


@app.get("/genjson")
def genjson(filename: str = "index.json"):

    jData = json.load(open('data.json', encoding='utf8'))

    db = dataset.connect(
        'mysql://hhh7796hhh:lYmWsu^ujcA1@hhh-v57.cmab1ctkglka.ap-northeast-2.rds.amazonaws.com:3306/xoops?charset=utf8')
    db.begin()
    cursor = db.query(
        "SELECT * FROM _had where (now() between start_time and end_time or ( start_time is null and end_time is null) or ( start_time = '0000-00-00 00:00:00' and end_time = '0000-00-00 00:00:00')) and onoff='1' and adtype like '首八大%' ")

    for x in jData:

        # 頂部輪播區-新刊頭
        if x['id'] == 0:
            cursor = db.query("""SELECT adlogo lo,adlogo_mobile mlo, adhref lk, adlogo_mobile_webp lomwebp FROM _had
                        WHERE adtype LIKE '新刊頭%'
                        AND onoff='1'
                        AND(NOW() BETWEEN start_time AND end_time OR(start_time='0000-00-00 00:00:00' and end_time='0000-00-00 00:00:00') or (start_time is null and end_time is NULL))
                        ORDER BY cast(SUBSTR(adtype,4) AS DECIMAL)""")

            x["data"] = []
            for c in cursor:
                a = {'imgUrl': c['mlo'], 'link': str(
                    c['lk']), 'DimgUrl': c['lo'], 'webp': str(c['lomwebp'])}
                x["data"].append(a)
            # print(x["data"])
            cursor.close()

        # 主要輪播區-首八大
        if x['id'] == 1:
            cursor = db.query("""SELECT adlogo lo,adlogo_mobile mlo, adhref lk, adlogo_mobile_webp lomwebp FROM _had
                        WHERE adtype LIKE '首八大%'
                        AND onoff='1'
                        AND(NOW() BETWEEN start_time AND end_time OR(start_time='0000-00-00 00:00:00' and end_time='0000-00-00 00:00:00') or (start_time is null and end_time is NULL))
                        ORDER BY cast(SUBSTR(adtype,4) AS DECIMAL)""")

            x["data"] = []
            for c in cursor:
                a = {'imgUrl': c['mlo'], 'link': str(
                    c['lk']), 'DimgUrl': c['lo'], 'webp': str(c['lomwebp'])}
                x["data"].append(a)
            # print(x["data"])
            cursor.close()

        #tab區塊-最夯設計, 影音實錄, 專欄文章
        if x['id'] == 2:
            x["data"] = []
            cursor = db.query("""SELECT caption TT ,cover IMG, CONCAT('https://www.hhh.com.tw/cases/detail/',hcase_id,'/') LK
                            from _hcase
                            left join _hdesigner ON _hcase.hdesigner_id=_hdesigner.hdesigner_id
                            WHERE
                            _hcase.onoff='1' AND _hdesigner.onoff='1'
                            AND(NOW() > sdate)
                            ORDER BY hcase_id DESC
                            LIMIT 3""")
            a = {'tab': '最夯設計', 'data': []}
            for c in cursor:
                ad = {'imgUrl': c['IMG'], 'link': c['LK'],
                      'description': c['TT']}
                a['data'].append(ad)
            x["data"].append(a)
            cursor.close()

            cursor = db.query("""SELECT title TT,iframe IMG , CONCAT('https://www.hhh.com.tw/video-post.php?id=',hvideo_id) LK
                            from _hvideo
                            ORDER BY hvideo_id DESC
                            LIMIT 3""")
            a = {'tab': '影音實錄', 'data': []}
            for c in cursor:
                tid = extract.video_id(c['IMG'])
                timg = "https://img.youtube.com/vi/" + tid+"/hqdefault.jpg"
                ad = {'imgUrl': timg, 'link': c['LK'],
                      'description': c['TT']}
                a['data'].append(ad)
            x["data"].append(a)
            cursor.close()

            cursor = db.query("""SELECT ctitle TT,clogo IMG, CONCAT('https://www.hhh.com.tw/column/detail/',hcolumn_id,'/') LK
                            from _hcolumn
                            WHERE onoff='1'
                            AND NOW() > sdate
                            ORDER BY hcolumn_id DESC
                            LIMIT 3""")
            a = {'tab': '專欄文章', 'data': []}
            for c in cursor:
                ad = {'imgUrl': c['IMG'], 'link': c['LK'],
                      'description': c['TT']}
                a['data'].append(ad)
            x["data"].append(a)
            cursor.close()
            # print(x["data"])

        # 主題企劃區
        if x['id'] == 3:
            cursor = db.query("""SELECT CONCAT('https://www.hhh.com.tw/',theme_type,'/',mapping_id,'/detail/') lk, ifnull(_hcase.caption,_hcolumn.ctitle) tt, ifnull(_hcase.cover,_hcolumn.clogo) lo
                        FROM homepage_set
                        left join _hcase ON _hcase.hcase_id=homepage_set.mapping_id AND theme_type='case'
                        left join _hcolumn ON _hcolumn.hcolumn_id=homepage_set.mapping_id AND theme_type='column'
                        WHERE outer_set=1
                        AND homepage_set.onoff='Y'
                        AND(NOW() BETWEEN homepage_set.start_time AND homepage_set.end_time OR(homepage_set.start_time='0000-00-00 00:00:00' and homepage_set.end_time='0000-00-00 00:00:00') or (homepage_set.start_time is null and homepage_set.end_time is NULL))
                        ORDER BY inner_sort""")

            x["data"] = []
            for c in cursor:
                a = {'imgUrl': c['lo'], 'link': str(c['lk'])}
                x["data"].append(a)
            cursor.close()
            # print(x["data"])

        # 編輯精選
        if x['id'] == 4:
            cursor = db.query("""SELECT hcolumn_id, ctitle, clogo
                                FROM homepage_set
                                LEFT JOIN _hcolumn ON mapping_id = hcolumn_id
                                WHERE outer_set=8
                                AND homepage_set.onoff='Y'
                                AND(NOW() BETWEEN homepage_set.start_time AND homepage_set.end_time OR(homepage_set.start_time='0000-00-00 00:00:00' and homepage_set.end_time='0000-00-00 00:00:00') or (homepage_set.start_time is null and homepage_set.end_time is NULL))
                                ORDER BY inner_sort""")

            x["data"] = []
            for c in cursor:
                a = {'imgUrl': c['clogo'], 'link': "https://www.hhh.com.tw/columns/detail/" + str(
                    c['hcolumn_id']) + "/", 'description': c['ctitle']}
                x["data"].append(a)
            cursor.close()
            # print(x["data"])

        # 來選好物區
        if x['id'] == 6:
            
            db = dataset.connect(
                    'mysql://hhh7796hhh:lYmWsu^ujcA1@hhh-v57.cmab1ctkglka.ap-northeast-2.rds.amazonaws.com:3306/xoops?charset=utf8')
                  
            cursor = db.query(
                "SELECT max_row from outer_site_set WHERE title='來選好貨'")
            
            maxrow = 1
            for c in cursor:
                maxrow = c['max_row']

            db = dataset.connect(
                    'mysql://hhh7796hhh:lYmWsu^ujcA1@hhh-v57.cmab1ctkglka.ap-northeast-2.rds.amazonaws.com:3306/xoops?charset=utf8')

            cursor = db.query("""SELECT id, cover, name
                                FROM _hproduct
                                where onoff ='1'
                                ORDER BY id desc
                                LIMIT """ + str(maxrow))

            x["data"] = []
            for fk in cursor:
                a = {'imgUrl': fk['cover'], 'link': "https://www.hhh.com.tw/product-post.php?id=" + str(
                    fk['id']) + "/", 'description': fk['name']}
                x["data"].append(a)
            cursor.close()
            # print(x["data"])

        # 本週推薦
        if x['id'] == 7:
            cursor = db.query(
                "SELECT max_row from outer_site_set WHERE title='本週推薦'")

            maxrow = 1
            for c in cursor:
                maxrow = c['max_row']

            db = dataset.connect(
                    'mysql://hhh7796hhh:lYmWsu^ujcA1@hhh-v57.cmab1ctkglka.ap-northeast-2.rds.amazonaws.com:3306/xoops?charset=utf8')

            cursor = db.query("""SELECT homepage_set.inner_sort, homepage_set.outer_set, _hcase_img.name as J, _hcase_img.name, _hcase.caption, _hcase.hcase_id, _hcase.hdesigner_id, _hdesigner.name, theme_type 
                            FROM homepage_set
                            left join _hcase ON _hcase.hcase_id=homepage_set.mapping_id
                            LEFT JOIN _hcase_img ON _hcase.hcase_id=_hcase_img.hcase_id
                            LEFT JOIN _hdesigner ON _hcase.hdesigner_id=_hdesigner.hdesigner_id
                            WHERE homepage_set.onoff='Y'
                            AND _hcase.onoff='1'
                            AND is_cover='1'
                            AND outer_set = (SELECT oss_id from outer_site_set WHERE title='粉絲推薦')
                            AND(NOW() BETWEEN homepage_set.start_time AND homepage_set.end_time OR(homepage_set.start_time='0000-00-00 00:00:00' and homepage_set.end_time='0000-00-00 00:00:00') or (homepage_set.start_time is null and homepage_set.end_time is NULL))
                            ORDER BY inner_sort
                            LIMIT """ + str(maxrow))

            x["data"] = []
            for fk in cursor:
                a = {'imgUrl': fk['J'], 'link': "https://www.hhh.com.tw/cases/detail/" + str(
                    fk['hcase_id']) + "/", 'description': fk['caption'], "video": "false"}
                x["data"].append(a)
            cursor.close()
            # print(x["data"])

        # 粉絲推薦
        if x['id'] == 8:
            
            db = dataset.connect(
                    'mysql://hhh7796hhh:lYmWsu^ujcA1@hhh-v57.cmab1ctkglka.ap-northeast-2.rds.amazonaws.com:3306/xoops?charset=utf8')

            cursor = db.query(
                "SELECT max_row from outer_site_set WHERE title='粉絲推薦'")

            maxrow = 1
            for c in cursor:
                maxrow = c['max_row']

            db = dataset.connect(
                    'mysql://hhh7796hhh:lYmWsu^ujcA1@hhh-v57.cmab1ctkglka.ap-northeast-2.rds.amazonaws.com:3306/xoops?charset=utf8')

            cursor = db.query("""SELECT homepage_set.inner_sort, homepage_set.outer_set, _hcase_img.name as J, _hcase_img.name, _hcase.caption, _hcase.hcase_id, _hcase.hdesigner_id, _hdesigner.name, theme_type 
                            FROM homepage_set
                            left join _hcase ON _hcase.hcase_id=homepage_set.mapping_id
                            LEFT JOIN _hcase_img ON _hcase.hcase_id=_hcase_img.hcase_id
                            LEFT JOIN _hdesigner ON _hcase.hdesigner_id=_hdesigner.hdesigner_id
                            WHERE homepage_set.onoff='Y'
                            AND _hcase.onoff='1'
                            AND is_cover='1'
                            AND outer_set = (SELECT oss_id from outer_site_set WHERE title='粉絲推薦')
                            AND(NOW() BETWEEN homepage_set.start_time AND homepage_set.end_time OR(homepage_set.start_time='0000-00-00 00:00:00' and homepage_set.end_time='0000-00-00 00:00:00') or (homepage_set.start_time is null and homepage_set.end_time is NULL))
                            ORDER BY inner_sort
                            LIMIT """ + str(maxrow))

            x["data"] = []
            for fk in cursor:
                a = {'imgUrl': fk['J'], 'link': "https://www.hhh.com.tw/cases/detail/" + str(
                    fk['hcase_id']) + "/", 'description': fk['caption'], "video": "false"}
                x["data"].append(a)
            cursor.close()
            # print(x["data"])

        if x['id'] == 9:
            cursor = db.query(
                "SELECT id, (case when youtube_title = '' OR youtube_title IS NULL then (SELECT title FROM _hvideo ORDER BY hvideo_id DESC LIMIT 1) ELSE youtube_title END) T, (case when youtube_id = '' OR youtube_id IS NULL then (SELECT iframe FROM _hvideo ORDER BY hvideo_id DESC LIMIT 1) ELSE youtube_id end) Y FROM site_setup")

            for c in cursor:
                x['title'] = c['T']
                x['yt'] = extract.video_id(c['Y'])
                # print(id)
            cursor.close()
            
    # print(jData)
    db.close()
    with open(filename, 'w', encoding='utf-8') as f:
        json.dump(jData, f, ensure_ascii=False, indent=4)

    return jData