import os
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 json
from pytube import extract

app = FastAPI()

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

hhhHomePath = '../hhh-home-mb'


def ExecuteQuery(isql):
    connection = mysql.connector.connect(
        host='hhh-v57.cmab1ctkglka.ap-northeast-2.rds.amazonaws.com',
        database='xoops',
        user='hhh7796hhh',
        password='lYmWsu^ujcA1'
    )
    cursor = connection.cursor(dictionary=True)
    cursor.execute(isql)
    if cursor.rowcount == -1:
        return cursor.fetchall()
    else:
        connection.commit()
        return cursor.rowcount


def ExecuteCmd(isql):
    connection = mysql.connector.connect(
        host='hhh-v57.cmab1ctkglka.ap-northeast-2.rds.amazonaws.com',
        database='xoops',
        user='hhh7796hhh',
        password='lYmWsu^ujcA1'
    )
    cursor = connection.cursor(dictionary=True)
    cursor.execute(isql)
    print(cursor.rowcount)
    connection.commit()
    return None


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

records = ExecuteQuery("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:
        records = ExecuteQuery("""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 records:
            a = {'imgUrl': c['mlo'], 'link': str(
                c['lk']), 'DimgUrl': c['lo'], 'webp': str(c['lomwebp'])}
            x["data"].append(a)
        # print(x["data"])

    # 主要輪播區-首八大
    if x['id'] == 1:
        records = ExecuteQuery("""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 records:
            a = {'imgUrl': c['mlo'], 'link': str(
                c['lk']), 'DimgUrl': c['lo'], 'webp': str(c['lomwebp'])}
            x["data"].append(a)
        # print(x["data"])

    #tab區塊-最夯設計, 影音實錄, 專欄文章
    if x['id'] == 2:
        x["data"] = []
        records = ExecuteQuery("""SELECT caption TT ,cover IMG, CONCAT('https://hhh.com.tw/cases/detail/',hcase_id,'/') LK, short_desc txt
                        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 records:
            ad = {'imgUrl': c['IMG'], 'link': c['LK'],
                    'title': c['TT'], 'description': c['txt']}
            a['data'].append(ad)
        x["data"].append(a)

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

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

    # 主題企劃區
    if x['id'] == 3:
        records = ExecuteQuery("""SELECT logo lo, CONCAT('https://hhh.com.tw/topic/detail/',htopic_id,'/') lk, `desc`, title FROM _htopic
WHERE onoff = '1'
ORDER BY htopic_id DESC limit 3""")

        x["data"] = []
        for c in records:
            a = {'imgUrl': c['lo'], 'link': str(
                c['lk']), 'video': 'false', 'description': c['desc'], 'title': c['title']}
            x["data"].append(a)
        # print(x["data"])

    # 編輯精選
    if x['id'] == 4:
        records = ExecuteQuery("""SELECT hcolumn_id, ctitle, clogo,cdesc
                            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 records:
            a = {'imgUrl': c['clogo'], 'link': "https://hhh.com.tw/columns/detail/" + str(
                c['hcolumn_id']) + "/", 'title': c['ctitle'], 'video': 'false', 'description': c['cdesc']}
            x["data"].append(a)
        # print(x["data"])

    # 來選好物區
    if x['id'] == 6:

        records = ExecuteQuery(
            "SELECT max_row from outer_site_set WHERE title='來選好貨'")

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

        records = ExecuteQuery("""SELECT theme_type, mapping_id, IFNULL(ifnull(ifnull(_hcase.caption,_hcolumn.ctitle),_hproduct.name),_hvideo.title) caption , IFNULL(ifnull(_hcase.cover,_hcolumn.clogo),_hproduct.cover) J, iframe , IFNULL(ifnull(ifnull(_hcase.short_desc,_hcolumn.cdesc),_hproduct.descr),_hvideo.`desc`) short_desc
, (case when theme_type='case' then CONCAT('https://hhh.com.tw/cases/detail/d/',mapping_id) when theme_type='column' then CONCAT('https://hhh.com.tw/columns/detail/',mapping_id) when theme_type='product' then CONCAT('https://hhh.com.tw/product-post.php?id=',mapping_id) when theme_type='video' then CONCAT('https://hhh.com.tw/video-post.php?id=',mapping_id) ELSE '' END) url
-- SELECT *
FROM homepage_set
left join _hcase ON _hcase.hcase_id=homepage_set.mapping_id AND theme_type='case'-- AND _hcase.onoff = '1'
LEFT JOIN _hproduct ON mapping_id = _hproduct.id AND theme_type='product'-- AND _hproduct.onoff = '1'
LEFT JOIN _hcolumn ON mapping_id = _hcolumn.hcolumn_id AND theme_type='column'-- AND _hcolumn.onoff = '1'
LEFT JOIN _hvideo ON mapping_id = _hvideo.hvideo_id AND theme_type='video'
WHERE homepage_set.onoff='Y'
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 outer_set, inner_sort
                            LIMIT """ + str(maxrow))

        x["data"] = []
        for c in records:
            if c['iframe'] is None:
                if isinstance(c['J'], bytearray) or isinstance(c['J'], bytes):
                    c['J'] = c['J'].decode('utf8')
                if isinstance(c['caption'], bytearray) or isinstance(c['caption'], bytes):
                    c['caption'] = c['caption'].decode('utf8')
                if isinstance(c['short_desc'], bytearray) or isinstance(c['short_desc'], bytes):
                    c['short_desc'] = c['short_desc'].decode('utf8')    
                a = {'imgUrl': c['J'], 'link': c['url'], 'title': c['caption'],
                        'description': c['short_desc'], 'video': 'false'}
            else:
                tid = extract.video_id(str(c['iframe']))
                timg = "https://img.youtube.com/vi/" + tid+"/hqdefault.jpg"
                ccaption = ""
                cdescription = ""
                if isinstance(c['caption'], bytearray):
                    ccaption = str(c['caption'].decode('utf8'))
                else:
                    ccaption = str(c['caption'])
                if c['short_desc'] is not None:
                    if isinstance(c['short_desc'], bytes):
                        cdescription = str(c['short_desc'].decode('utf8'))
                    else:
                        cdescription = str(c['short_desc'])
                a = {'imgUrl': timg, 'link': c['url'], 'title': ccaption,
                        'description': cdescription, 'video': tid}
            x["data"].append(a)
        # print(x["data"])

    # 本週推薦
    if x['id'] == 7:
        records = ExecuteQuery(
            "SELECT max_row from outer_site_set WHERE title='本週推薦'")

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

        records = ExecuteQuery("""SELECT theme_type, mapping_id, IFNULL(ifnull(ifnull(_hcase.caption,_hcolumn.ctitle),_hproduct.name),_hvideo.title) caption , IFNULL(ifnull(_hcase.cover,_hcolumn.clogo),_hproduct.cover) J, iframe , IFNULL(ifnull(ifnull(_hcase.short_desc,_hcolumn.cdesc),_hproduct.descr),_hvideo.`desc`) short_desc
, (case when theme_type='case' then CONCAT('https://hhh.com.tw/cases/detail/d/',mapping_id) when theme_type='column' then CONCAT('https://hhh.com.tw/columns/detail/',mapping_id) when theme_type='product' then CONCAT('https://hhh.com.tw/product-post.php?id=',mapping_id) when theme_type='video' then CONCAT('https://hhh.com.tw/video-post.php?id=',mapping_id) ELSE '' END) url
-- SELECT *
FROM homepage_set
left join _hcase ON _hcase.hcase_id=homepage_set.mapping_id AND theme_type='case'-- AND _hcase.onoff = '1'
LEFT JOIN _hproduct ON mapping_id = _hproduct.id AND theme_type='product'-- AND _hproduct.onoff = '1'
LEFT JOIN _hcolumn ON mapping_id = _hcolumn.hcolumn_id AND theme_type='column'-- AND _hcolumn.onoff = '1'
LEFT JOIN _hvideo ON mapping_id = _hvideo.hvideo_id AND theme_type='video'
WHERE homepage_set.onoff='Y'
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 outer_set, inner_sort
                        LIMIT """ + str(maxrow))

        x["data"] = []
        for c in records:
            if c['iframe'] is None:
                if isinstance(c['J'], bytearray) or isinstance(c['J'], bytes):
                    c['J'] = c['J'].decode('utf8')
                if isinstance(c['caption'], bytearray) or isinstance(c['caption'], bytes):
                    c['caption'] = c['caption'].decode('utf8')
                if isinstance(c['short_desc'], bytearray) or isinstance(c['short_desc'], bytes):
                    c['short_desc'] = c['short_desc'].decode('utf8')    
                a = {'imgUrl': c['J'], 'link': c['url'], 'title': c['caption'],
                        'description': c['short_desc'], 'video': 'false'}
            else:
                tid = extract.video_id(str(c['iframe']))
                timg = "https://img.youtube.com/vi/" + tid+"/hqdefault.jpg"
                ccaption = ""
                cdescription = ""
                if isinstance(c['caption'], bytearray):
                    ccaption = str(c['caption'].decode('utf8'))
                else:
                    ccaption = str(c['caption'])
                if c['short_desc'] is not None:
                    if isinstance(c['short_desc'], bytes):
                        cdescription = str(c['short_desc'].decode('utf8'))
                    else:
                        cdescription = str(c['short_desc'])
                a = {'imgUrl': timg, 'link': c['url'], 'title': ccaption,
                        'description': cdescription, 'video': tid}
            x["data"].append(a)
        # print(x["data"])

    # 粉絲推薦
    if x['id'] == 8:

        records = ExecuteQuery(
            "SELECT max_row from outer_site_set WHERE title='粉絲推薦'")

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

        records = ExecuteQuery("""SELECT theme_type, mapping_id, IFNULL(ifnull(ifnull(_hcase.caption,_hcolumn.ctitle),_hproduct.name),_hvideo.title) caption , IFNULL(ifnull(_hcase.cover,_hcolumn.clogo),_hproduct.cover) J, iframe , IFNULL(ifnull(ifnull(_hcase.short_desc,_hcolumn.cdesc),_hproduct.descr),_hvideo.`desc`) short_desc
, (case when theme_type='case' then CONCAT('https://hhh.com.tw/cases/detail/d/',mapping_id) when theme_type='column' then CONCAT('https://hhh.com.tw/columns/detail/',mapping_id) when theme_type='product' then CONCAT('https://hhh.com.tw/product-post.php?id=',mapping_id) when theme_type='video' then CONCAT('https://hhh.com.tw/video-post.php?id=',mapping_id) ELSE '' END) url
-- SELECT *
FROM homepage_set
left join _hcase ON _hcase.hcase_id=homepage_set.mapping_id AND theme_type='case'-- AND _hcase.onoff = '1'
LEFT JOIN _hproduct ON mapping_id = _hproduct.id AND theme_type='product'-- AND _hproduct.onoff = '1'
LEFT JOIN _hcolumn ON mapping_id = _hcolumn.hcolumn_id AND theme_type='column'-- AND _hcolumn.onoff = '1'
LEFT JOIN _hvideo ON mapping_id = _hvideo.hvideo_id AND theme_type='video'
WHERE homepage_set.onoff='Y'
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 outer_set, inner_sort
                        LIMIT """ + str(maxrow))

        x["data"] = []
        for c in records:
            if c['iframe'] is None:
                if isinstance(c['J'], bytearray) or isinstance(c['J'], bytes):
                    c['J'] = c['J'].decode('utf8')
                if isinstance(c['caption'], bytearray) or isinstance(c['caption'], bytes):
                    c['caption'] = c['caption'].decode('utf8')
                if isinstance(c['short_desc'], bytearray) or isinstance(c['short_desc'], bytes):
                    c['short_desc'] = c['short_desc'].decode('utf8')    
                a = {'imgUrl': c['J'], 'link': c['url'], 'title': c['caption'],
                        'description': c['short_desc'], 'video': 'false'}
            else:
                tid = extract.video_id(str(c['iframe']))
                timg = "https://img.youtube.com/vi/" + tid+"/hqdefault.jpg"
                ccaption = ""
                cdescription = ""
                if isinstance(c['caption'], bytearray):
                    ccaption = str(c['caption'].decode('utf8'))
                else:
                    ccaption = str(c['caption'])
                if c['short_desc'] is not None:
                    if isinstance(c['short_desc'], bytes):
                        cdescription = str(c['short_desc'].decode('utf8'))
                    else:
                        cdescription = str(c['short_desc'])
                a = {'imgUrl': timg, 'link': c['url'], 'title': ccaption,
                        'description': cdescription, 'video': tid}
            x["data"].append(a)
        # print(x["data"])

    if x['id'] == 9:
        records = ExecuteQuery(
            "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 records:
            x['title'] = ""
            if isinstance(c['T'], bytearray):
                x['title'] = str(c['T'].decode('utf8'))
            else:
                x['title'] = str(c['T'])
            x['yt'] = extract.video_id(str(c['Y']))
            # print(id)

    if x['id'] == 10:
        records = ExecuteQuery(
            "SELECT all_search_tag ast FROM site_setup")

        x["data"] = []
        for c in records:
            x["data"] = c['ast'].split(',')
            # print(id)


# print(jData)
""" if not os.path.exists(hhhHomePath):
    os.mkdir(hhhHomePath)
with open(hhhHomePath+'/json/' + filename, 'w', encoding='utf-8') as f:
    json.dump(jData, f, ensure_ascii=False, indent=4) """

print(jData)


""" if __name__ == "__main__":
    uvicorn.run(app, host="0.0.0.0", port=8000) """