import pandas as pd
from fastapi import FastAPI, File, UploadFile
from fastapi.middleware.cors import CORSMiddleware
import dataset
from functools import reduce

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


@app.post("/write/")
async def writecsv(file: UploadFile = File(...)):
    # read csv
    repls = (' Stats ', '_'), (' at ', '_'), ('-', '_'), \
            (' ', ''), ('.csv', '')
    filename = file.filename
    filename = reduce(lambda a, kv: a.replace(*kv), repls, filename)

    data = file.file
    df = pd.read_csv(data, sep=",",
                     skiprows=0, na_values='NULL')

    # db connect
    db = dataset.connect(
        'mysql://choozmo:pAssw0rd@db.ptt.cx: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
    i = 0

    rows = lines[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()