from fastapi import APIRouter
from supabase import create_client, Client
from dotenv import load_dotenv
import os
from datetime import datetime
from random import choice
from openai import OpenAI

client = OpenAI()

load_dotenv()

# supaspace 連線
url: str =  os.environ.get('SUPABASE_URL')
key: str = os.environ.get('SUPABASE_KEY')

supabase: Client = create_client(url, key)

dbRouter = APIRouter()



@dbRouter.get("/click")
def add_click_time():

    try:

        response = supabase.table('click_time').select("*").execute()

        click_time  = response.data[0]['click_time'] + 1
    
        data, count = supabase.table('click_time') \
            .update({'click_time':click_time,'update_time':str(datetime.now())})\
            .eq('id', 1)\
            .execute()
        
        return {"state":"success","click_time" : click_time}

    except Exception as e:
        
        return {"state":str(e)}
    

@dbRouter.get("/find_brand")
def find_brand(keyword:str = None,language :str = "ch"):

    if keyword is None :
        return {"state":"fail","message" : "no keyword"}

    try :
        data, count = supabase.table('101_brand')\
        .select('*')\
        .eq("language", language)\
        .like('tags', f'%{keyword}%')\
        .execute()

        result = []

        for shop in data[1] :


            json = {
                "type" : shop["type"],
                "info" : shop
            }

            result.append(json)


        return {"state":"success","data" : result}

    except Exception as e:
        return {"state":"fail","message" :str(e)}
    
@dbRouter.get("/arviews")
def arviews(start:str,end:str):
    try :
        data, count = supabase.table('101_arviews')\
        .select('*')\
        .eq('start_loc', start) \
        .like('tour_place', f'%{end}%') \
        .execute()

        result :str
        words :str

        if len(data[1]) != 0:
            result = data[1][0]["url"]
            words = data[1][0]["words"]
        else :
            result = "no this route"


        return {"state":"success","url" : result,"words" : words}

    except Exception as e:
        return {"state":"fail","message" :str(e)}

@dbRouter.get("/static_tickets")
async def static_tickets(is_Chinese : int = None):
    try:
        data =None


        if is_Chinese :
            data, count = supabase.table('101_ticket')\
            .select('*')\
            .in_('id', [1,3,6,7])\
            .execute()
        else :
            data, count = supabase.table('101_ticket')\
            .select('*')\
            .in_('id', [182,183,180])\
            .execute()

        result = []
        
        for shop in data[1] :

            json = {
                "type" : shop["type"],
                "info" : shop
            }

            result.append(json)

        return {"state":"success","result" : result}

    except Exception as e:
        return {"state":"fail","message" :str(e)}

@dbRouter.get("/ad/{type}")
def read_root(type:str,language :str = "ch"):

    keyword1 :str 
    keyword2 :str

    if type == "美食伴手禮":
        keyword1 = "餐飲"
        keyword2 = "伴手禮" 
    else :
        keyword1 = "住宿"
        keyword2 = "伴手禮" 

    data, count = supabase.table('101_brand')\
        .select('*')\
        .eq("floor","館外")\
        .eq("language", language)\
        .or_(f"tags.ilike.%{keyword1}%,tags.ilike.%{keyword2}%")\
        .execute()
    
    result = data[1]

    # 從結果中隨機選擇一筆資料
    random_row = choice(result)

    #print(random_row)
    
    return {"data": random_row}