from enum import Enum
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
from fastapi.staticfiles import StaticFiles
import time
from datetime import datetime
from PIL import Image
import io
from typing import List
import threading
import pandas as pd
from gspread_pandas import Spread, Client
from oauth2client.service_account import ServiceAccountCredentials
import gspread
from first import first
app = FastAPI()
origins = [
    "http://172.105.205.52",
    "http://172.105.205.52:8000",
]

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

serverADDR = 'welife.asia:8002/'
cover_img_dir = 'static/cover_img/'
app.mount("/static/cover_img", StaticFiles(directory="static/cover_img"), name="static/cover_img")



##########TAG CRUD################TAG CRUD##################TAG CRUD###########TAG CRUD##################
class Tag(BaseModel):
    id: int
    name: str

@app.get("/tags")
async def get_tags():
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/openTalk?charset=utf8mb4')
    statement = 'SELECT id,name FROM tag'
    tags = []
    for row in db.query(statement):
        tags.append({'id':row['id'],'name':row['name']})
    return tags

@app.post("/create_tag")
async def create_tags(tag:Tag):
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/openTalk?charset=utf8mb4')
    tag_table = db['tag']
    pk = tag_table.insert({'name':tag.name})
    tag.id = pk
    return tag
	
@app.get("/del_tag/{tag_id}")
async def delete_tags(tag_id:int):
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/openTalk?charset=utf8mb4')
    sqls = 'DELETE FROM tag WHERE id = '+str(tag_id)
    db.query(sqls)
    sqls = 'DELETE FROM course_tag WHERE tag_id = '+str(tag_id)
    db.query(sqls)
    return 'success'
	
@app.post("/update_tag/")
async def update_tags(tag_id,tag:Tag):
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/openTalk?charset=utf8mb4')
    sqls = 'UPDATE tag\
    SET name="'+tag.name+'"\
    WHERE id='+tag_id+';'
    db.query(sqls)
    tag.id=tag_id
    return tag
    
@app.get("/tags/{tag_id}")
async def get_tags(tag_id):
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/openTalk?charset=utf8mb4')
    statement = 'SELECT id,name FROM tag where id = '+tag_id
    for row in db.query(statement):
        result = {'id':row['id'],'name':row['name']}
    return result
	
	
	
###########COURSE CRUD###########COURSE CRUD###########COURSE CRUD###########COURSE CRUD###########
class Course(BaseModel):
    id: int
    title: str
    profile: str
    url: str #course website location url
    cover_img: str #img url form
    teacher_name: str
    teacher_img: str #teacher user image url form
    price: int
    price_discount: int
    hours: float
    units: int

class Email_tag(BaseModel):
    email:str
    tags:str


    

@app.post("/get_courses_by_cid")
async def get_courses(cids:List[int]):
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/openTalk?charset=utf8mb4')
    cid_str = '('
    for t in cids:
        cid_str = cid_str + str(t) +','
    cid_str = cid_str[:-1]+')'

    courses = []
    statement = 'SELECT * FROM course WHERE id IN'+cid_str

    for row in db.query(statement):
        courses.append({'id':row['id'],'title':row['title'],'url':row['url']
        ,'teacher_name':['teacher_name'],'price':row['price'],'price_discount':row['price_discount'],'profile':row['profile']
        ,'cover_img':'https://'+serverADDR + row['cover_img'],'hours':row['hours'],'units':row['units'],'clicks':row['clicks']})
    return courses


@app.post("/get_courses")
async def get_courses(tags:List[int]):
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/openTalk?charset=utf8mb4')
    tag_str = '('
    for t in tags:
        tag_str = tag_str + str(t) +','
    tag_str = tag_str[:-1]+')'


    courses = []
    statement = 'SELECT * FROM course_tag '\
    'INNER JOIN course on course_tag.course_id=course.id '\
    'INNER JOIN tag on course_tag.tag_id = tag.id '\
    'WHERE tag.id IN'+tag_str+' ORDER BY clicks DESC'

    for row in db.query(statement):
        courses.append({'id':row['course_id'],'title':row['title'],'url':row['url']
        ,'teacher_name':['teacher_name'],'price':row['price'],'price_discount':row['price_discount'],'profile':row['profile']
        ,'cover_img':'https://'+serverADDR + row['cover_img'],'hours':row['hours'],'units':row['units'],'clicks':row['clicks']})
    return courses

@app.post("/create_courses")
async def create_courses(tags: list,course:Course):
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/openTalk?charset=utf8mb4')
    #add to course table
    course_table = db['course']
    pk = course_table.insert({'title':course.title,'profile':course.profile,'url':course.url,'cover_img':course.cover_img,'teacher_name':course.teacher_name,
    'price':course.price,'price_discount':course.price_discount,'hours':course.hours,'units':course.units})
    course.id = pk
    course_tag_table = db['course_tag']
    for tag in tags:
        course_tag_table.insert({'course_id':course.id,'tag_id':tag})
    
    return {'msg':'新增成功'}
        
@app.get("/del_course/{course_id}")
async def delete_course(course_id:int):
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/openTalk?charset=utf8mb4')
    sqls = 'DELETE FROM course WHERE id = '+str(course_id)
    db.query(sqls)
    sqls = 'DELETE FROM course_tag WHERE course_id = '+str(course_id)
    db.query(sqls)
    return {'msg':'新增成功'}

@app.post("/update_course")
async def update_course(cid:int,tags:List[int]):
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/openTalk?charset=utf8mb4')
    sqls = 'DELETE FROM course_tag WHERE course_id = '+str(cid)
    db.query(sqls)
    course_tag_table = db['course_tag']
    for tag in tags:
        course_tag_table.insert({'course_id':cid,'tag_id':tag})
    return {'msg':'ok'}

@app.get("/course_click/{course_id}")
async def delete_course(course_id:int):
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/openTalk?charset=utf8mb4')

    sqls = 'UPDATE course\
    SET clicks=clicks+1\
    WHERE id='+str(course_id)+';'
    db.query(sqls)
    return {'msg':'ok'}

@app.post("/upload_cover_img/")
async def create_upload_file(file: UploadFile = File(...)):
    img_name = str(time.time()).replace('.','')

    contents = await file.read()
    image = Image.open(io.BytesIO(contents))
    image= image.convert("RGB")
    image.save(cover_img_dir+img_name+'.jpg')
    return {"msg": serverADDR+cover_img_dir+img_name+'.jpg'}

@app.post("/email_interest")
async def email_interest(email_tag:Email_tag):
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/openTalk?charset=utf8mb4')
    email_table = db['interest_email']
    
    for tag in email_tag.tags.split(','):
        email_table.insert({'email':email_tag.email,'interest_tag':tag})
    x = threading.Thread(target=to_sheet)
    x.start()
    return {'msg':'ok'}

    
def to_sheet():
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/openTalk?charset=utf8mb4')
    statement = 'SELECT DISTINCT email FROM interest_email '
    
    df = pd.DataFrame()
    for row in db.query(statement):
        tag_names = ''
        for row_tag in db.query('SELECT interest_tag FROM interest_email WHERE email="'+row['email']+'"'):
            tag_names += first(db.query('SELECT * FROM tag where id ='+str(row_tag['interest_tag'])))['name']
            tag_names += ', '
        df = df.append({'email':row['email'],'種類':tag_names}, ignore_index=True)
    save_sheet(df,'interest_email','index')
    
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('spread2.json', scope)

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