from enum import Enum
from typing import Optional
from pydantic import BaseModel
from fastapi import FastAPI, Query
import dataset,json


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


class Tag(BaseModel):
    id: int
    name: str
	
@app.get("/tags")
async def get_tags():
    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 result
	
@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


@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 id,name from course_table where id IN'+course_id_string    
    for row in db.query(statement):
        courses.append({'id':row['id'],'name':row['name']})
    return courses