from fastapi import FastAPI, File, UploadFile
from fastapi.middleware.cors import CORSMiddleware
import dataset
import zipfile
import pandas as pd
import glob



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


@app.post("/upload/")
async def upload( title:str, file: UploadFile = File(...)):

    file = file.file
    target = title
    with zipfile.ZipFile(file, "r") as zip_ref:
        zip_ref.extractall(target)
    await write(target, title)


@app.post("/write")
async def write(dir, title):

    path = dir
    all_files = glob.glob(path + "/*.csv")

    li = []

    for filename in all_files:
        df = pd.read_csv(filename, index_col=None, header=0)
        li.append(df)

    # pre-clean data
    frame = pd.concat(li, axis=0, ignore_index=True)
    data_name = "./"+dir+"/"+title+".csv"
    data = frame.to_csv(data_name)
    await todb(data_name, title)
    print("data", data_name)


@app.post("/todb")
async def todb(file, title):
    # read csv
    filename = title
    data = file
    df = pd.read_csv(data, sep=",",
                     skiprows=0, na_values='NULL')

    # db connect
    db = dataset.connect(
        'mysql://root:pAssw0rd@localhost:3306/keywordweb?charset=utf8mb4'
    )

    sql = "CREATE TABLE IF NOT EXISTS " \
          + filename + \
          "(ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, " \
          "keyword VARCHAR(100), " \
          "currency VARCHAR(100), avg_monthly_searches VARCHAR(100), " \
          "three_month_change VARCHAR(100), " \
          "yearly_change VARCHAR(100), competition VARCHAR(100), " \
          "competition_indexed VARCHAR(100), " \
          "top_page_bid_low_range VARCHAR(100), " \
          "top_page_bid_hi_range VARCHAR(100)" \
          ");"

    db.query(sql)

    # write to db
    table = db[filename]
    lines = df.shape


    rows = lines[0]
    i = 0
    for i in range(rows):
        row1 = df.iloc[i]
        k = row1[0]
        c = row1[1]
        a = row1[2]
        t = row1[3]
        y = row1[4]
        c1 = row1[5]
        c2 = row1[6]
        t1 = row1[7]
        t2 = row1[8]

        dbdata = dict(keyword=k, currency=c, avg_monthly_searches=a,
                      three_month_change=t, yearly_change=y, competition=c1,
                      competition_indexed=c2, top_page_bid_low_range=t1,
                      top_page_bid_hi_range=t2)
        table.insert(dbdata)

    db.close()