from enum import Enum
import time
from datetime import datetime
from typing import Optional
from pydantic import BaseModel
from fastapi import FastAPI, Query, UploadFile, File
from fastapi import FastAPI
from fastapi.middleware.cors import CORSMiddleware
import json
import urllib.request
import dataset,json
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:8001",
]

app.add_middleware(
    CORSMiddleware,
    # allow_origins=origins,
    allow_origins=["*"],
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"],
)
db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/NFTBoard?charset=utf8mb4')
class subreq(BaseModel):
    email:str

@app.get("/showdata/{limit}")
async def showdata(limit):
    url = 'https://api.coinmarketcap.com/data-api/v3/nft/collections?start=0&limit=' + limit
    r = urllib.request.urlopen(url)
    collections = json.loads(r.read())['data']['collections']
    
    reqs = []

    for req in collections:
        logo = 'https://s3.coinmarketcap.com/generated/nft/collections/' + req['slug'] + '.png';
        last_7_days = 'https://s3.coinmarketcap.com/generated/sparklines/nft/collection/web/30d/' + req['slug'] +'.png';

        reqs.append({'rank':req['rank'],'slug':req['slug'],'logo':logo,'website':req['website'],'name':req['name'],'assets':req['transferUniqueAssetsAT'],'collection_net_worth':req['netWorth'],'volume_7d':req['volume7d'],'sales_7d':req['sales7d'],'volume_all_time':req['volumeAT'],'sales_all_time':req['salesAT'],'last_7_days':last_7_days})
    return reqs

@app.post("/add_sub")
async def add_subclient_info(req:subreq):
    code = 1
    sub_table = db['Subscribe']
    time_stamp = datetime.fromtimestamp(time.time()).strftime("%Y-%m-%d %H:%M:%S")

    statement='SELECT COUNT(1) FROM Subscribe WHERE email = "'+req.email+'"'
    if first(db.query(statement))['COUNT(1)']==0:
        pk = sub_table.insert({'email':req.email,'timestamp':time_stamp})
        make_ci_df()
        code = 0
    return {'msg':code}#0 means succss added, 1 means email is duplicated

@app.get("/get_sub_list")
async def add_subclient_info():
    statement = 'SELECT * FROM Subscribe'
    result = []
    for row in db.query(statement):
        result += [{'id':row['id'],'email':row['email'],'timeStamp':row['timestamp']}]
    return result


def make_ci_df():
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/NFTBoard?charset=utf8mb4')
    statement = 'SELECT * FROM Subscribe ORDER BY timestamp DESC'
    #2021-05-23 15:57:43
    df = pd.DataFrame()
    for row in db.query(statement):
        date_format = "%Y-%M-%d %H:%M:%S"
        fdate = row['timestamp'].strftime('%Y-%m-%d %H時 %M分')

        df = df.append({'serial id':row['id'],'e-mail':row['email'],'登記時間':fdate}, ignore_index=True)

    df = df[['serial id','e-mail','登記時間']]
    save_sheet(df,'subscribe_rec','Sheet1')

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, sheet=tabname, start=startpos, replace=True)