import itertools
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'

connstr = '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"):

    with dataset.connect(connstr) as db:
        jData = json.load(open(hhhMBPath+'/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
                                where display_datetime < now()
                                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'] == 5:
                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 adtype""")

                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'] == 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 where display_datetime < now() 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 where display_datetime < now() 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(hhhMBPath+'/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(hhhPCPath+'/json/' + filename, 'w', encoding='utf-8') as f:
            json.dump(jData, f, ensure_ascii=False, indent=4)

        return jData

# @app.get("/gencase")


def gencase(id: str = "12257", sort: str = "new", page: str = "1"):

    with dataset.connect(connstr) as db:
        jData = json.load(open(hhhMBPath+'/json/cases.json', encoding='utf8'))

        records = db.query("""SELECT *, c.style cstyle, c.style2 cstyle2 FROM _hcase c
    LEFT JOIN _hdesigner d ON c.hdesigner_id = d.hdesigner_id
    WHERE c.hcase_id = '""" + id + """'  AND c.onoff='1' AND d.onoff='1' and c.sdate < now() """)

        # print(jData)
        for x in jData:
            tmpCaseDetail = []
            icount = 0
            for c in records:
                icount += 1
                #tmpCaseDetail.append({"CaseDetailImg": c["cimg"]})
                #x["CaseDetail"] = tmpCaseDetail

                if c != None:
                    x["designerid"] = str(c["hdesigner_id"])
                    x["CaseId"] = str(c["hcase_id"])
                    x["Casetitle"] = c["caption"]
                    x["CaseTeamName"] = c["name"]
                    x["CaseCompany"] = c["title"]
                    x["CaseCompanyAddress"] = c["address"]
                    x["CaseCompanyTel"] = c["phone"]
                    x["CaseCompanyEmail"] = c["mail"]
                    x["CaseCompanyWeb"] = c["website"]
                    x["CaseDate"] = str(c["sdate"])
                    x["CaseViews"] = c["viewed"]
                    x["CaseCoverImg"] = c["cover"]
                    x["CaseImgAmount"] = icount
                    x["CaseStyle"] = c["cstyle"]
                    x["CaseHouse"] = c["layout"]
                    x["CaseSize"] = c["area"]
                    x["CaseProject"] = ""
                    x["CaseDataMember"] = c["member"]
                    x["CaseDataSize"] = c["area"]
                    x["CaseDataStyle"] = c["cstyle"] + c["cstyle2"]
                    x["CaseDataType"] = c["type"]
                    x["CaseDataSituation"] = c["condition"]
                    x["CaseDataImgProvide"] = c["provider"]
                    x["CaseDataSpace"] = c["layout"]
                    x["CaseDataMaterial"] = c["materials"]
                    x["ContactFreeTel"] = c["service_phone"]
                    x["ContactDesignerImg"] = c["img_path"]
                    x["CasePageLink"] = ""
                    x["CasePageprev"] = ""
                    #x["CaseTag"]= []

                    # 相同設計師的個案
                    sql = """SELECT * FROM _hcase c
            WHERE hdesigner_id = '""" + x["designerid"] + """' and hcase_id <> '""" + x["CaseId"] + """' and sdate < now()  AND c.onoff='1'
            ORDER BY """ + ("sdate" if sort == 'new' else 'viewed') + """ DESC
            LIMIT """ + str((int(page) - 1)*12) + """,12
            """
                    cases = db.query(sql)

                    tmpOtherCases = []
                    for other in cases:
                        tmpOtherCase = {}
                        tmpOtherCase["designerid"] = str(other["hdesigner_id"])
                        tmpOtherCase["casesid"] = str(other["hcase_id"])
                        tmpOtherCase["Views"] = other["viewed"]
                        tmpOtherCase["ProfileImg"] = other["cover"]
                        tmpTags = []
                        for tag in other["tag"].split(','):
                            tmpTags.append({"Tag": tag, "TagLink": ""})
                        tmpOtherCase["ProfileTag"] = tmpTags
                        tmpOtherCases.append(tmpOtherCase)

                    x["DesignerProfile"] = tmpOtherCases

                    # 相同風格的個案
                    sql = """SELECT * FROM _hcase c
            WHERE style = '""" + x["CaseStyle"] + """' and hcase_id <> '""" + x["CaseId"] + """' and sdate < now()  AND c.onoff='1'
            ORDER BY """ + ("sdate" if sort == 'new' else 'viewed') + """ DESC
            LIMIT """ + str((int(page) - 1)*12) + """,12
            """
                    cases = db.query(sql)

                    tmpOtherCases = []
                    for other in cases:
                        tmpOtherCase = {}
                        tmpOtherCase["designerid"] = str(other["hdesigner_id"])
                        tmpOtherCase["casesid"] = str(other["hcase_id"])
                        tmpOtherCase["Views"] = other["viewed"]
                        tmpOtherCase["ProfileImg"] = other["cover"]
                        tmpTags = []
                        for tag in other["tag"].split(','):
                            tmpTags.append({"Tag": tag, "TagLink": ""})
                        tmpOtherCase["ProfileTag"] = tmpTags
                        tmpOtherCases.append(tmpOtherCase)

                    x["StyleProfile"] = tmpOtherCases

                    # 相同風格的RANDOM 10筆
                    # cases = db.query("""SELECT * FROM _hcase c
            # WHERE style = '""" + x["CaseStyle"] + """' and hcase_id <> '""" + x["CaseId"] + """' and sdate < now()  AND c.onoff='1'
            # ORDER BY RAND()
            # LIMIT 10
            # """)
                    """ tmpOtherCases = []
                    for other in cases:
                        tmpOtherCase = {}
                        tmpOtherCase["designerid"] = str(other["hdesigner_id"])
                        tmpOtherCase["casesid"] = str(other["hcase_id"])
                        tmpOtherCase["PortfoliolImg"] = other["cover"]
                        tmpOtherCase["PortfoliolLink"] = ""
                        tmpOtherCase["PortfoliolImgAlt"] = other["caption"]
                        tmpOtherCases.append(tmpOtherCase)
                    x["OtherStylePortfolio"]= tmpOtherCases """

        # print(x)

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

        return jData


def gendesigner(id: str = "14151", sort: str = "new", page: str = "1"):
    with dataset.connect(connstr) as db:
        jData = json.load(open(hhhMBPath+'/json/designers.json', encoding='utf8'))

        records = db.query("""SELECT * FROM _hdesigner d
    WHERE d.hdesigner_id = '""" + id + """' AND d.onoff='1' """)

        # print(jData)
        for x in jData:
            tmpCaseDetail = []
            icount = 0
            for c in records:
                icount += 1
                # tmpCaseDetail.append({"CaseDetailImg":c["cimg"]})

            if page == "1":
                x["id"] = c["hdesigner_id"]
                x["BannerImg"] = c["background"]
                x["CompanyName"] = c["title"]
                x["DesignerName"] = c["name"]
                x["Designerimg"] = c["img_path"]
                x["Description"] = c["seo"]
                x["Approve"] = c["position"]
                x["FB_link"] = c["fbpageurl"]
                x["Basics"] = [
                    {"title": "免費專線：",
                        "link": c["service_phone"], "data": c["service_phone"]},
                    {"title": "諮詢專線：", "link": c["phone"], "data": c["phone"]},
                    {"title": "諮詢專線：", "link": c["phone"], "data": c["phone"]},
                    {"title": "公司傳真：", "link": c["fax"], "data": c["fax"]},
                    {"title": "公司地址：", "link": c["address"], "data": c["address"]},
                    {"title": "電子信箱：", "link": c["mail"], "data": c["mail"]},
                    {"title": "公司網址：", "link": c["website"], "data": c["website"]}
                ]
                x["FreeCall"] = c["service_phone"]
                x["ConsoleCall_1"] = c["phone"]
                x["ConsoleCall_2"] = c["phone"]
                x["Fax"] = c["fax"]
                x["Address"] = c["address"]
                x["Email"] = c["mail"]
                x["Web"] = c["website"]
                
                x["Branches"] = []
                branches = db.query("""SELECT * FROM designer_branch br 
    WHERE br.designer_id = '""" + id + """' """)
                
                for branch in branches:
                    tmpobj = {
                        "title": branch["title"],
                        "address": branch["address"],
                        "tel": branch["tel"],
                        "fax": branch["fax"]
                    }
                    x["Branches"].append(tmpobj)

                x["Budget"] = c["budget"]
                x["Square"] = c["area"]
                x["SpecialCase"] = c["special"]
                x["Charge"] = c["charge"]
                x["Pay"] = c["payment"]
                x["WorkLoc"] = c["region"]
                x["WorkType"] = c["type"]
                x["WorkStyle"] = c["style"]
                x["WorkBudget"] = c["budget"]
                x["Terms"] = [
                    {"title": "接案預算：", "data": c["budget"]},
                    {"title": "接案坪數：", "data": c["area"]},
                    {"title": "特殊接案：", "data": c["special"]},
                    {"title": "收費方式：", "data": c["charge"]},
                    {"title": "付費方式：", "data": c["payment"]},
                    {"title": "接案區域：", "data": c["region"]},
                    {"title": "接案類型：", "data": c["type"]},
                    {"title": "接案風格：", "data": c["style"]}
                ]
                x["scMedia"] = [
                    {"name": "Facebook", "img": "https://hhh.com.tw/assets/images/rv_web/fb.svg",
                        "link": c["fbpageurl"]},
                    {"name": "Line", "img": "https://hhh.com.tw/assets/images/rv_web/line.svg",
                        "link": c["line_link"]},
                    {"name": "Wechat", "img": "https://hhh.com.tw/assets/images/rv_web/wechat.svg",
                        "link": c["fbpageurl"]},
                    {"name": "email", "img": "https://hhh.com.tw/assets/images/rv_web/share.svg",
                        "link": c["mail"]},
                    {"name": "Like", "img": "https://hhh.com.tw/assets/images/rv_web/like-o.svg", "link": ""}
                ]
                x["Content"] = [
                    {
                        "Title": "設計師作品",
                        "mb_title": "作品",
                        "Tabtag": "intro",
                        "Display_mb": "true",
                        "isActive": "true",
                        "Carddata": []
                    },
                    {
                        "Title": "設計師影音",
                        "mb_title": "影音",
                        "Tabtag": "video",
                        "Display_mb": "true",
                        "isActive": "true",
                        "Carddata": []
                    },
                    {
                        "Title": "設計師專欄",
                        "mb_title": "專欄",
                        "Tabtag": "columns",
                        "Display_mb": "true",
                        "isActive": "true",
                        "Carddata": []
                    },
                    {
                        "Title": "VR360",
                        "mb_title": "",
                        "Tabtag": "vr360",
                        "Display_mb": "false",
                        "isActive": "true",
                        "Carddata": []
                    },
                    {
                        "Title": "設計師公司簡介",
                        "mb_title": "公司簡介",
                        "Tabtag": "company",
                        "Display_mb": "true",
                        "isActive": "true",
                        "Carddata": [],
                        "info": [
                            {"title": "設計理念", "data": c["idea"]},
                            {"title": "公司統編", "data": c["taxid"]},
                            {"title": "相關經歷", "data": c["career"]},
                            {"title": "專業證照", "data": c["license"]},
                            {"title": "獲獎紀錄", "data": c["awards"]}
                        ]
                    }
                ]

            # 設計師作品
            x["Content"][0]["Carddata"] = []
            cases = db.query("""SELECT tag,cover,caption,hcase_id,viewed,sdate FROM _hcase c
    WHERE c.hdesigner_id = '""" + id + """' AND c.onoff='1' 
    ORDER BY """ + ("sdate" if sort == 'new' else 'viewed') + """ DESC
    LIMIT """ + str((int(page) - 1)*12) + """,12
    """)

            for case in cases:
                tmpobj = {
                    "url": "https://hhh.com.tw/cases/detail/d/"+str(case["hcase_id"]),
                    "imgURL": case["cover"],
                    "title": case["caption"],
                    "tag": [{"name": tag, "link": ""} for tag in case["tag"].split(',')],
                    "views": case["viewed"],
                    "dateSort": str(case["sdate"])
                }
                x["Content"][0]["Carddata"].append(tmpobj)

            # 設計師影音
            # https://i.ytimg.com/vi/y6VmaLC7O9Y/hqdefault.jpg
            x["Content"][1]["Carddata"] = []
            videos = db.query("""SELECT tag_vpattern,iframe,title,hvideo_id,viewed,display_datetime FROM _hvideo v
    WHERE v.hdesigner_id = '""" + id + """' AND display_datetime < NOW() 
    ORDER BY """ + ("display_datetime" if sort == 'new' else 'viewed') + """ DESC
    LIMIT """ + str((int(page) - 1)*12) + """,12
    """)

            for video in videos:
                tmpobj = {
                    "url": "https://hhh.com.tw/video-post.php?id="+str(video['hvideo_id']),
                    "imgURL": "https://i.ytimg.com/vi/" + extract.video_id(str(video['iframe']))+"/hqdefault.jpg",
                    "title": video["title"],
                    "tag": [{"name": tag, "link": ""} for tag in video["tag_vpattern"].split(',')],
                    "views": video["viewed"],
                    "dateSort": str(video["display_datetime"])
                }
                x["Content"][1]["Carddata"].append(tmpobj)

            # 設計師專欄
            x["Content"][2]["Carddata"] = []
            columns = db.query("""SELECT tag,clogo,ctitle,hcolumn_id,viewed,sdate FROM _hcolumn c
    WHERE onoff=1 AND (hdesigner_ids LIKE '""" + id + """,%' OR hdesigner_ids LIKE '%,""" + id + """,%' OR hdesigner_ids LIKE '%,""" + id + """' OR hdesigner_ids = '""" + id + """') 
    ORDER BY """ + ("sdate" if sort == 'new' else 'viewed') + """ DESC
    LIMIT """ + str((int(page) - 1)*12) + """,12
    """)

            for column in columns:
                tmpobj = {
                    "url": "https://hhh.com.tw/video-post.php?id="+str(video['hvideo_id']),
                    "imgURL": column['clogo'],
                    "title": column["ctitle"],
                    "tag": [] if not column["tag"] else [{"name": tag, "link": ""} for tag in column["tag"].split(',')],
                    "views": column["viewed"],
                    "dateSort": str(column["sdate"])
                }
                x["Content"][2]["Carddata"].append(tmpobj)

            # VR360
            x["Content"][3]["Carddata"] = []
            cases = db.query("""SELECT tag,cover,caption,hcase_id,viewed,sdate FROM _hcase c
    WHERE c.hdesigner_id = '""" + id + """' and istaging != '' AND c.onoff='1' 
    ORDER BY """ + ("sdate" if sort == 'new' else 'viewed') + """ DESC
    LIMIT """ + str((int(page) - 1)*12) + """,12
    """)

            for case in cases:
                tmpobj = {
                    "url": "https://hhh.com.tw/cases/detail/d/"+str(case["hcase_id"]),
                    "imgURL": case["cover"],
                    "title": case["caption"],
                    "tag": [{"name": tag, "link": ""} for tag in case["tag"].split(',')],
                    "views": case["viewed"],
                    "dateSort": str(case["sdate"])
                }
                x["Content"][3]["Carddata"].append(tmpobj)

            # 設計公司簡介
            #x["Content"][4]["Carddata"] = []
            #cases = db.query("")

            """ for case in cases:
                tmpobj = {
                    "imgURL":case["cover"],
                    "title":case["caption"],
                    "tag":[{"name": tag, "link": ""} for tag in case["tag"].split(',')],
                    "views":case["viewed"],
                    "dateSort":str(case["sdate"])
                    }
                x["Content"][4]["Carddata"].append(tmpobj) """

        # print(x)

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

        return jData


def gencolumn(id: str = "6392", sort: str = "new", page: str = "1"):
    with dataset.connect(connstr) as db:
        jData = json.load(open(hhhMBPath+'/json/Columns.json', encoding='utf8'))

        records = db.query("""SELECT * FROM _hcolumn c
    WHERE c.hcolumn_id = '""" + id + """'  AND c.onoff='1' """)

        # print(id)
        for x in jData:
            icount = 0
            c = None
            for c in records:
                icount += 1
                if c != None:
                    x["Columnsid"] = str(c["hcolumn_id"])
                    x["Columnstitle"] = c["ctitle"]
                    x["ColumnsCoverImg"] = c["clogo"]
                    x["ColumnsDate"] = str(c["sdate"])
                    x["ColumnsViews"] = str(c["viewed"])
                    # 只有第一頁時有內容，以免資料過大
                    if page == "1":
                        x["ColumnsContent"] = c["page_content"]
                    tmpTags = []
                    for tag in c["ctag"].split(','):
                        tmpTags.append({"Tag": tag})
                    x["ColumnsTag"] = tmpTags

                    # 相同類別的最新12筆
                    sql = """SELECT * FROM _hcolumn c
            WHERE (c.ctype like '%""" + str(c["ctype"]) + """%' and c.ctype_sub like '%""" + str(c["ctype_sub"]) + """%') and hcolumn_id <> '""" + str(c["hcolumn_id"]) + """' and sdate < now()  AND c.onoff='1'
            ORDER BY """ + ("sdate" if sort == 'new' else 'viewed') + """ DESC
            LIMIT """ + str((int(page) - 1)*12) + """,12
            """
                    ctypes = db.query(sql)
                    tmpOtherCols = []
                    for other in ctypes:
                        tmpOtherCol = {}
                        tmpOtherCol["Columnsid"] = str(other["hcolumn_id"])
                        tmpOtherCol["ColumnsCoverImg"] = other["clogo"]
                        tmpOtherCol["Views"] = other["viewed"]
                        tmpOtherCol["Columnstitle"] = other["ctitle"]
                        tmpTags = []
                        for tag in other["ctag"].split(','):
                            tmpTags.append({"Tag": tag})
                        tmpOtherCol["ColumnsTag"] = tmpTags
                        tmpOtherCols.append(tmpOtherCol)
                    x["OtherColumns"] = tmpOtherCols

        # print(x)

        # print(jData)
        if not os.path.exists(hhhMBPath):
            os.mkdir(hhhMBPath)
        with open(hhhMBPath+'/json/Columns-' + id + '.json', '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)

with dataset.connect(connstr) as db:

    genjson()
    """ gencase("14151")
    gendesigner("788")
    gencolumn("6392") """
    """ gencolumn("6349")
    gencolumn("6476")
    gencolumn("6392") """

    """ gencase("421")
    gencase("12257")
    gencase("14305") """

    """ gendesigner("312") """

    cases = db.query("""SELECT DISTINCT hcase_id FROM _hcase c
    WHERE c.onoff='1' """)

    """ for sc in cases:
        gencase(str(sc['hcase_id'])) """

    columns = db.query("""SELECT DISTINCT hcolumn_id FROM _hcolumn c
    WHERE c.onoff='1' and sdate < now() """)

    """ for sc in columns:
        gencolumn(str(sc['hcolumn_id'])) """

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