from enum import Enum
import time
from datetime import datetime
from typing import Optional
from pydantic import BaseModel
from fastapi import FastAPI, Query, UploadFile, File
import dataset,json
from fastapi import FastAPI
from fastapi.middleware.cors import CORSMiddleware
import pandas as pd
from gspread_pandas import Spread, Client
from oauth2client.service_account import ServiceAccountCredentials
import gspread
app = FastAPI()

origins = [
    "https://hhh.com.tw"
    "http://172.105.205.52",
    "http://172.105.205.52:8001",
    "http://172.104.93.163",
]

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




class client_info(BaseModel):
    name:str
    organization:str
    position:str
    phone:str
    email:str
    cert_last5:str
    where_learn:str
    why_sign:str
    line_id:str
    


@app.post("/add_client_info")
async def add_client_info(ci:client_info):
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/hhh?charset=utf8mb4')
    
    statement = 'SELECT * FROM client_info WHERE email="'+ci.email+'"'
    email_cnt = 0
    for row in db.query(statement):
        email_cnt = email_cnt + 1
    
    statement = 'SELECT * FROM client_info WHERE phone="'+ci.phone+'"'
    phone_cnt = 0
    for row in db.query(statement):
        phone_cnt = phone_cnt + 1
    
    return_code = 0
    #return code 0=good, 1=email duplication, 2=phone duplication, 3= both duplicate
    if email_cnt>0 and phone_cnt==0:
        return_code = 1
    if email_cnt==0 and phone_cnt>0:
        return_code = 2
    if email_cnt>0 and phone_cnt>0:
        return_code = 3
    
    if True:
        return_code=0
    if return_code ==0 :
        request_table = db['client_info']
        time_stamp = datetime.fromtimestamp(time.time())
        time_stamp = time_stamp.strftime("%Y-%m-%d %H:%M:%S")
 
        pk = request_table.insert({'name':ci.name,'organization':ci.organization,'position':ci.position,'phone':ci.phone,'email':ci.email
            ,'cert_last5':ci.cert_last5,'where_learn':ci.where_learn,'why_sign':ci.why_sign,'line_id':ci.line_id,'time_stamp':time_stamp})
        make_ci_df()
        return return_code
    else :
        return return_code

    
    return return_code
    
    
def make_ci_df():
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/hhh?charset=utf8mb4')

    statement = 'SELECT * FROM client_info ORDER BY time_stamp DESC'
    #2021-05-23 15:57:43
    df = pd.DataFrame()
    for row in db.query(statement):
        date_format = "%Y-%M-%d %H:%M:%S"
   
        #fdate = datetime.strptime(row['ts_date'],date_format)
        fdate = row['time_stamp'].strftime('%Y-%m-%d %H時 %M分')
        
        df = df.append({'serial id':row['id'],'姓名':row['name'],'公司':row['organization']
        ,'職稱':row['position'],'電話':row['phone'],"e-mail":row['email'],'已匯款，後五碼':row['cert_last5']
        ,'如何知道這個課程':row['where_learn'],'爲什麼想報名':row['why_sign'],'Line_ID':row['line_id'],'報名日期':fdate}, ignore_index=True)
    
    cols = list(df.columns.values)
    print(cols)
    df = df[['報名日期','姓名','serial id','公司'
        ,'職稱','電話',"e-mail",'已匯款，後五碼'
        ,'如何知道這個課程','爲什麼想報名','Line_ID']]
    cols = list(df.columns.values)
    print(cols)
    save_sheet(df,'client_info','result')
    
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('c:\\keys\\spread2.json', scope)
    credentials = ServiceAccountCredentials.from_json_keyfile_name('spread2.json', scope)

    gc = gspread.authorize(credentials)
    spread = Spread(filename,creds=credentials)

    spread.df_to_sheet(df, index=True, sheet=tabname, start=startpos, replace=True)