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

@author: lyin
"""

from __future__ import print_function

import contextlib
import os
import tempfile

from apiclient import discovery
import boto3
import botocore.exceptions
import httplib2
import oauth2client
from oauth2client import client
from oauth2client import tools
import pandas as pd

S3_BUCKET = os.environ.get('DATALYTICS_S3_BUCKET', 'dev-cucumbers').strip('/')

CLIENT_SECRET_FILE = 'credentials/google/client_secret.json'
TOKEN_FILE = 'credentials/google/token.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'


@contextlib.contextmanager
def s3_storage_file(s3_path, upload_back=True):
    """Context manager to work with copy of file from s3 as local temp file.

    File from s3 is copied from s3 to local temp file on context enter.
    If upload_back is True and context is exited without error, file is
    uploaded back to s3. In any case, local temp file is removed after
    context exit.

    Args:
        s3_path (str): Path to file on s3.
        upload_back (bool): Indicates if file should be uploaded back after
            context exit.
    Yields:
        str: Path to local temp file.
    """
    s3_client = boto3.client('s3')
    fd, path = tempfile.mkstemp()
    try:
        s3_client.download_file(S3_BUCKET, s3_path, path)
    except botocore.exceptions.ClientError:
        # This exception may occur if file doesn't exist and it's safe to
        # ignore it. But it probably may be caused by other reasons.
        # Unfortunately, ClientError doesn't provide good way to see what
        #  exactly caused it.
        pass
    try:
        yield path
    except Exception:
        raise
    else:
        if upload_back:
            s3_client.upload_file(path, S3_BUCKET, s3_path)
    finally:
        os.close(fd)
        os.remove(path)


def get_credentials():
    """Get valid user credentials from storage.

    If nothing has been stored, or if the stored credentials are invalid,
    the OAuth2 flow is completed to obtain the new credentials.

    Returns:
        Credentials, the obtained credential.
    """
    with s3_storage_file(TOKEN_FILE) as token_file:
        storage = oauth2client.file.Storage(token_file)
        credentials = storage.get()

        if credentials is None or credentials.invalid:
            with s3_storage_file(
                    CLIENT_SECRET_FILE, upload_back=False) as client_secret:
                flow = client.flow_from_clientsecrets(client_secret, SCOPES)
                flow.user_agent = APPLICATION_NAME
                credentials = tools.run_flow(
                    flow, storage, flags=tools.argparser.parse_args())
    return credentials


def get_sheet_ids(s_id):
    """Return 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=s_id).execute()
    sheets = sheet_metadata.get('sheets', '')

    return [sheet.get(
        'properties', {}).get('title', 'Sheet1') for sheet in sheets]


def sheet_2_df(
        s_id, sheet, t_range, dtype=None,
        index=None, dateTimeRenderOption=None):
    """Get 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=t_range)

    # Submit the arguments and retrieve results from Sheet
    result = service.spreadsheets().values().get(
        spreadsheetId=s_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, s_id, sheet, t_range):
    """Update 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=t_range)

    valueRange = {
        'range': rangeName,
        'majorDimension': 'ROWS',
        'values': values
    }

    result = service.spreadsheets().values().update(
        spreadsheetId=s_id,
        range=rangeName,
        valueInputOption='RAW',
        body=valueRange
    ).execute()

    return result