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

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 = 'ok'
    sub_table = db['Subscribe']
    time_stamp = datetime.fromtimestamp(time.time()).strftime("%Y-%m-%d %H:%M:%S")
    pk = sub_table.insert({'email':req.email,'timestamp':time_stamp})
    return {'msg':code}

@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['time_stamp'].strftime('%Y-%m-%d %H時 %M分')

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

    df = df[['serial id','e-mail','登記時間']]
    save_sheet(df,'email_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, index=True, sheet=tabname, start=startpos, replace=True)