from app import *
from apps.data import get_data

import pickle
import os.path
from googleapiclient.discovery import build
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request

def google_sheet_api_check(SCOPES):
    token_file='data/google_sheet/token.pickle'
    credentials_file='data/google_sheet/credentials.json'
    creds = None
    # The file token.pickle stores the user's access and refresh tokens, and is
    # created automatically when the authorization flow completes for the first
    # time.
    if os.path.exists(token_file):
        with open(token_file, 'rb') as token:
            creds = pickle.load(token)
    # If there are no (valid) credentials available, let the user log in.
    if not creds or not creds.valid:
        if creds and creds.expired and creds.refresh_token:
            creds.refresh(Request())
        else:
            flow = InstalledAppFlow.from_client_secrets_file(
                credentials_file, SCOPES)
            creds = flow.run_local_server(port=0)
        # Save the credentials for the next run
        with open(token_file, 'wb') as token:
            pickle.dump(creds, token)
    return creds

def google_sheet_to_dataframe(SCOPES,SPREADSHEET_ID,RANGE_NAME):
    creds=google_sheet_api_check(SCOPES)
    service = build('sheets', 'v4', credentials=creds)

    # Call the Sheets API
    sheet = service.spreadsheets()
    result = sheet.values().get(spreadsheetId=SPREADSHEET_ID,
                                range=RANGE_NAME).execute()
    values = result.get('values', [])

    if not values:
        print('No data found.')
    
    else:
        data = result.get('values')
        df = pd.DataFrame(data)
    return df

def download_data(saved_csv):
    SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly']

    google_sheet_id = '1qIDhtfoMWANpRSsW8TVeNwWT6kkBr2OcgYUhuiQXJPE'
    sheet_name = 'Current Marking'

    df=google_sheet_to_dataframe(SCOPES,google_sheet_id,sheet_name)
    column_names=df.iloc[0,:].to_list()
    df=df.iloc[1:,:]
    df.columns=column_names
    df.to_csv(saved_csv,index=False)

def preprocess_billing(input_csv, output_csv):
    # read bill sheet
    df=pd.read_csv(input_csv)
    df=df.loc[:,['Date', 'Marking', 'Company Name']]
    df.rename(columns={'Company Name':'Name'}, inplace=True)
    df['Date']=pd.to_datetime(df['Date'], errors='coerce').fillna(method='pad')
    # remove na in marking
    df=df.where(df['Marking'].str.contains("/")).dropna(subset=['Marking'])
    # remove space
    replace={" +":""}
    df.replace(replace,regex=True,inplace=True)
    # extract marking
    df['Marking']=df['Marking'].str.extract("(CIEF/\w+)", expand=False)
    df.drop_duplicates(inplace=True)
    df.to_csv(output_csv, index=False)

# layout
layout = html.Div([
    dcc.Interval(
        id='input-interval',
        # 1 hour
        interval=60*60*1000, # in milliseconds
        n_intervals=0)
    ])


# live update
@app.callback(
        Output('data', 'data'),
        Input('input-interval', 'n_intervals'),
        State('data', 'data'))
def update_output(n_interval, data):
    try:
        # download data from google sheet to local file
        download_data('data/billing0.csv')
        # preprocess data and save to local file
        preprocess_billing('data/billing0.csv', 'data/billing1.csv')
        data=get_data()
        print('reloaded')
        return(data)
    except Exception as e:
        print('There was an error processing data.')
        print(e)
        raise(PreventUpdate)