import time
from datetime import datetime
from fastapi import FastAPI, Form, Request
from fastapi.templating import Jinja2Templates
from fastapi.middleware.cors import CORSMiddleware
from pydantic import BaseModel
from oauth2client.service_account import ServiceAccountCredentials
import os, dataset, json, gspread
from gspread_pandas import Spread, Client
import pandas as pd

app = FastAPI()
directory=os.path.dirname(os.getcwd())
templates = Jinja2Templates(directory)

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

@app.get("/")
async def read_root(request: Request):
    return templates.TemplateResponse("index.html",{"request": request})

@app.post("/submitform")
async def formentry(name: str = Form(...), phone: str = Form(...), loc: str = Form(...), course_name: str = Form(...), bookdate: str = Form(...), booktime: str = Form(...)):
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/violetbeauty?charset=utf8mb4')
    request_table = db['booking_info']
    time_stamp = datetime.fromtimestamp(time.time())
    time_stamp = time_stamp.strftime("%Y-%m-%d %H:%M:%S")

    print(time_stamp)
    
    request_table.insert({"name": name, "phone": phone, "location": loc, "course_name":course_name, "book_date": bookdate, "book_time": booktime, "time_stamp": time_stamp})

    return 0 # pls make booking success page
    # return templates.TemplateResponse("(bookingsuccess.html)",{"request": request})

@app.get("/getbooking_data")
async def getdata():
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/violetbeauty?charset=utf8mb4')
    statement = 'SELECT * FROM booking_info ORDER BY time_stamp DESC'
    df = pd.DataFrame()
    for row in db.query(statement):
        date_format = "%Y-%M-%d %H:%M:%S"
   
        fdate = row['time_stamp'].strftime('%Y-%m-%d')
        
        df = df.append({'填單時間':row['time_stamp'],'姓名':row['name'],'電話':str('\'' + row['phone']),'預約體驗店家':row['location'],'選擇課程':row['course_name'],'預約日期':row['book_date'],'預約時間':row['book_time']}, ignore_index=True)
    
    save_sheet(df,'紫晴預約資料','預約資料')

    return '資料下載完成'

def save_sheet(df,filename,tabname,startpos='A1'):

    scope = ['https://spreadsheets.google.com/feeds',
            'https://www.googleapis.com/auth/drive']

    credentials = ServiceAccountCredentials.from_json_keyfile_name('violet-beauty-booking-data-8ef5f0d9a747.json', scope)

    gc = gspread.authorize(credentials)
    spread = Spread(filename,creds=credentials)
    spread.df_to_sheet(df, index=False, sheet=tabname, start=startpos, replace=False)

    time_stamp = datetime.fromtimestamp(time.time())
    time_stamp = time_stamp.strftime("%Y-%m-%d %H:%M:%S")
    
    spread.sheet.update('I1', '最後更新:' + time_stamp)