import os
from typing import Optional
from dataset.util import ResultIter
from datetime import tzinfo
import datetime
#import mysql.connector
#from mysql.connector import Error
import dataset
import json
from pytube import extract


hhhMBPath = '../hhh-home-mb'
hhhPCPath = '../hhh-home-pc'

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


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

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

    records = 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:
            records = db.query("""SELECT adlogo lo,adlogo_mobile mlo, adhref lk, adlogo_mobile_webp lomwebp, adlogo_webp dwebp 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']), 'Dwebp': str(c['dwebp'])}
                x["data"].append(a)
            # print(x["data"])

        # 主要輪播區-首八大
        if x['id'] == 1:
            records = db.query("""SELECT adlogo lo,adlogo_mobile mlo, adhref lk, adlogo_mobile_webp lomwebp, adlogo_webp dwebp 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']), 'Dwebp': str(c['dwebp'])}
                x["data"].append(a)
            # print(x["data"])

        #tab區塊-最夯設計, 影音實錄, 專欄文章
        if x['id'] == 2:
            x["data"] = []
            records = db.query("""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 = db.query("""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 4""")
            a = {'tab': '影音實錄', 'data': []}
            cnt = 0
            for c in records:
                if cnt == 0:
                    cnt += 1
                    continue
                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 = db.query("""SELECT ctitle TT,clogo IMG, CONCAT('https://hhh.com.tw/columns/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 = db.query("""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 = db.query("""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 = db.query(
                "SELECT max_row from outer_site_set WHERE title='來選好貨'")

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

            records = db.query("""(SELECT theme_type, mapping_id, IFNULL(ifnull(ifnull(_hcase.caption,_hcolumn.ctitle),_hproduct.name),_hvideo.title) COLLATE utf8_general_ci caption  , IFNULL(ifnull(_hcase.cover,_hcolumn.clogo),_hproduct.cover) COLLATE utf8_general_ci J, iframe , IFNULL(ifnull(ifnull(_hcase.short_desc,_hcolumn.cdesc),_hproduct.descr),_hvideo.`desc`) COLLATE utf8_general_ci 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)
UNION
(SELECT 'product', id, `name`, cover, NULL ,descr ,CONCAT('https://hhh.com.tw/product-post.php?id=',id) FROM _hproduct WHERE onoff='1' ORDER BY id DESC LIMIT """ + str(maxrow) + """)
                                LIMIT """ + str(maxrow))

            x["data"] = []
            for c in records:
                print(c)
                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 = db.query(
                "SELECT max_row from outer_site_set WHERE title='本週推薦'")

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

            records = db.query("""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 = db.query(
                "SELECT max_row from outer_site_set WHERE title='粉絲推薦'")

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

            records = db.query("""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 = 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 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 = db.query(
                "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(hhhMBPath):
        os.mkdir(hhhMBPath)
    with open('../json/' + filename, 'w', encoding='utf-8') as f:
        json.dump(jData, f, ensure_ascii=False, indent=4)

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

    return jData


genjson("realtime.json")
cmd="""curl -X POST "https://api.cloudflare.com/client/v4/zones/8cb58022ad5743cfd9f088d5e3a261eb/purge_cache" -H "X-Auth-Email: web.dept.hhh@gmail.com"  -H "X-Auth-Key:63e51d9a1638d66afcbfc3320aabec52304de" -H "Content-Type: application/json" --data '{"files":["https://m.hhh.com.tw/hhh-home-mb/json/realtime.json"]}'"""
os.system(cmd)

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