# -*- coding: utf-8 -*-
"""
Created on Mon Jun 20 17:42:01 2016

@author: lyin
"""

from __future__ import print_function
import httplib2
import os
import json


from apiclient import discovery
import oauth2client
from oauth2client import client
from oauth2client import tools
import pandas as pd

# Oauth2 Credentials to be downloaded from Google Console as client_secrets.json

# For the API
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
API_VERSION = 'v4'
API_SERVICE_NAME = 'sheets'
APPLICATION_NAME = 'Holy Sheet'
DISCOVERYURL = ('https://sheets.googleapis.com/$discovery/rest?'
                'version=v4')

def create_client_secrets():
    '''
    Turns Env variables into client_secrets.json
    '''
    try:
        client_id = os.environ.get('GDOC_CLIENT_ID')
        client_secret = os.environ.get('GDOC_CLIENT_SECRET')
    except:
        raise("PLEASE CONFIGURE ENV VARIABLES FOR GDOC")

    client_secrets = {
        "installed": {
            "client_id": client_id,
            "project_id": APPLICATION_NAME, 
            "auth_uri": "https://accounts.google.com/o/oauth2/auth",
            "token_uri": "https://accounts.google.com/o/oauth2/token",
            "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
            "client_secret": client_secret,
            "redirect_uris":
                ["urn:ietf:wg:oauth:2.0:oob",
                 "http://localhost"]
        }
    }

    with open(CLIENT_SECRET_FILE, 'w') as outfile:
        json.dump(client_secrets, outfile)

def get_credentials():
    '''
    Gets access token from environment variables.
    
    '''

    try:
        CLIENT_ID = os.environ.get('GDOC_CLIENT_ID')
        CLIENT_SECRET = os.environ.get('GDOC_CLIENT_SECRET')
        ACCESS_TOKEN = os.environ.get('GDOC_ACCESS_TOKEN')
        REFRESH_TOKEN = os.environ.get('GDOC_REFRESH_TOKEN')
    except:
        print("configure GDOC environment variables in .env")
        return

    data = json.dumps({
        "revoke_uri": "https://accounts.google.com/o/oauth2/revoke", 
        "refresh_token": REFRESH_TOKEN, 
        "client_secret": CLIENT_SECRET, 
        "_module": "oauth2client.client", 
        "user_agent": APPLICATION_NAME, 
        "_class": "OAuth2Credentials", 
        "token_expiry": "2017-02-25T00:05:53Z", 
        "access_token": ACCESS_TOKEN,
        "token_uri": "https://accounts.google.com/o/oauth2/token", 
        "token_info_uri": "https://www.googleapis.com/oauth2/v3/tokeninfo", 
        "id_token": '', 
        "token_response": {
            "access_token": ACCESS_TOKEN, 
            "expires_in": 3599, "token_type": "Bearer"}, 
        "client_id": CLIENT_ID, 
        "invalid": False, 
        "scopes": SCOPES
    })

    credentials = oauth2client.client.Credentials.new_from_json(data)
    
    return credentials

def get_sheet_ids(id):
    '''
    Returns a list of sheet names (tabs) in a spreadsheet id.
    '''
    # Get Authenticated.
    credentials = get_credentials()
    http = credentials.authorize(httplib2.Http())
    
    # Connect to the API.
    service = discovery.build(
        API_SERVICE_NAME, 
        API_VERSION, 
        http=http,
        discoveryServiceUrl=DISCOVERYURL
    )
    
    sheet_metadata = service.spreadsheets().get(spreadsheetId=id).execute()
    sheets = sheet_metadata.get('sheets', '')
    
    return [sheet.get("properties", {}).get("title", "Sheet1") for sheet in sheets]
    

def sheet_2_df(id,sheet,range,dtype=None,index=None,dateTimeRenderOption=None):
    '''
    Gets a sheet to a pandas dataframe given 
    an id (string) of a google sheet, 
    sheet (string) name and 
    the A1 range (string ie A1:E).
    
    Note that the first row in the range will be the columns of the dataframe.
    
    try it on this sample spreadsheet:
    https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
    
    with
    
    sheet_2_df(
        id=1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms,
        sheet='Class Data',
        range='A1:F'
    )
    '''
    # Get Authenticated.
    credentials = get_credentials()
    http = credentials.authorize(httplib2.Http())
    
    # Connect to the API.
    service = discovery.build(
        API_SERVICE_NAME, 
        API_VERSION, 
        http=http,
        discoveryServiceUrl=DISCOVERYURL
    )
    
    # Construct arguments
    rangeName = '{SHEET}!{RANGE}'.format(SHEET=sheet,RANGE=range)
    
    # Submit the arguments and retrieve results from Sheet
    result = service.spreadsheets().values().get(
        spreadsheetId=id, 
        range=rangeName,
        dateTimeRenderOption=dateTimeRenderOption
    ).execute()

    values = result.get('values', [])

    if not values:
        df = pd.DataFrame()
    
    else:
        df = pd.DataFrame(
            data=[row for row in values[1:] if row],
            columns=values[0],
            dtype=dtype,
            index=index
        )
    return df

def df_2_sheet(df,id,sheet,range, valueInputOption='RAW'):
    '''
    Updates a sheet of a sheetid with 'values' (list of list)
    '''
    
    values = df.values.tolist()    
    
    credentials = get_credentials()
    http = credentials.authorize(httplib2.Http())
    
    # Connect to the API.
    service = discovery.build(
        API_SERVICE_NAME, 
        API_VERSION, 
        http=http,
        discoveryServiceUrl=DISCOVERYURL
    )
    
    # Construct arguments
    rangeName = '{SHEET}!{RANGE}'.format(SHEET=sheet,RANGE=range)
    
    valueRange = {
        "range":rangeName,
        "majorDimension":"ROWS",
        "values":values
    }
    
    result = service.spreadsheets().values().update(
        spreadsheetId=id,
        range=rangeName,
        valueInputOption=valueInputOption,
        body= valueRange      
    ).execute()
    
    return result