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
app = FastAPI()
origins = [
    "http://172.105.205.52",
    "http://172.105.205.52:8000",
]

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


db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/openTalk?charset=utf8mb4')

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

@app.get("/tags")
async def get_tags():
    course_table = db['course_table']
    print(course_table.columns)
    statement = 'SELECT id,name FROM tag_table'
    tags = []
    for row in db.query(statement):
        tags.append({'id':row['id'],'name':row['name']})
    return tags

@app.post("/tags")
async def create_tags(tag:Tag):
    tag_table = db['tag_table']
    pk = tag_table.insert({'name':tag.name})
    tag.id = pk
    return tag
	
@app.delete("/tags/{tag_id}")
async def delete_tags(tag_id:int):
    sqls = 'DELETE FROM tag_table 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.put("/tags/{tag_id}")
async def update_tags(tag_id,tag:Tag):
    sqls = 'UPDATE tag_table\
    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):
    statement = 'SELECT id,name FROM tag_table 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
    url: str #course website location url
    img: str #img url form
    teacher_name: str
    teacher_img: str #teacher user image url form
    price: int
    hours: int
    units: int
    
@app.get("/courses")
async def get_courses(tag_string):
    
    courses = []
    statement = 'SELECT course_id from course_tag where tag_id IN'+tag_string

    course_id_string = '('
    for row in db.query(statement):
        course_id_string = course_id_string + str(row['course_id'])+","
    course_id_string=course_id_string[:-1]
    course_id_string = course_id_string+')'

    statement = 'SELECT * from course_table where id IN'+course_id_string    
    for row in db.query(statement):
        courses.append({'id':row['id'],'title':row['title'],'url':row['url'],'img':row['img']
        ,'teacher_name':['teacher_name'],'teacher_img':row['teacher_img'],'price':row['price']
        ,'hours':row['hours'],'units':row['units']})
    return courses

@app.post("/courses")
async def create_courses(Tag_IDs: list,course:Course):
    course_table = db['course_table']
    course_tag_table = db['course_tag']
    pkey = course_table.insert({'title':course.title,'url':course.url,'img':course.img
    ,'teacher_name':course.teacher_name, 'teacher_img': course.teacher_img,'units':course.units
    ,'price':course.price,'hours':course.hours})
    sqls = 'INSERT INTO course_tag (course_id, tag_id)VALUES'
    for tid in Tag_IDs:
        sqls = sqls + '('+str(pkey)+','+str(tid)+'),'
    sqls = sqls[:-1]
    db.query(sqls)
    return course
        
@app.post("/course_cover_change/{course_id}")
async def course_cover_change(course_id, uploaded_file: UploadFile = File(...)):
    file_location = f"course_cover_img/{course_id}"
    with open(file_location, "wb+") as file_object:
        file_object.write(uploaded_file.file.read())
    sqls = 'UPDATE course_table\
    SET img="'+file_location+'"\
    WHERE id='+course_id+';'
    db.query(sqls)
    return '{"file_loc":'+file_location+'}'

@app.post("/teacher_img_change/{course_id}")
async def teacher_img_change(course_id, uploaded_file: UploadFile = File(...)):
    file_location = f"teacher_img/{course_id}"
    with open(file_location, "wb+") as file_object:
        file_object.write(uploaded_file.file.read())
    sqls = 'UPDATE course_table\
    SET teacher_img="'+file_location+'"\
    WHERE id='+course_id+';'
    db.query(sqls)
    return '{"file_loc":'+file_location+'}'
    
@app.delete("/courses/{course_id}")
async def delete_course(course_id:int):
    sqls = 'DELETE FROM course_table WHERE id = '+str(course_id)
    db.query(sqls)
    sqls = 'DELETE FROM course_tag WHERE course_id = '+str(course_id)
    db.query(sqls)
    return "success"
    
@app.put("/courses/{course_id}")
async def update_course(course_id,course:Course):
    sqls = 'UPDATE course_table\
    SET title="'+course.title+'", url="'+course.url+'", hours='+str(course.hours)+',\
    teacher_name="'+course.teacher_name+'", price='+str(course.price)+', units='+str(course.units)+'\
    WHERE id='+str(course_id)+';'
    db.query(sqls)
    course.id=course_id
    return course