import gspread
from oauth2client.service_account import ServiceAccountCredentials
import pymysql


def get_client():
    keyfile = 'path_to_keyfile.json'
    scope = ['https://spreadsheets.google.com/feeds',
             'https://www.googleapis.com/auth/drive']
    creds = ServiceAccountCredentials.from_json_keyfile_name(keyfile, scope)
    gc = gspread.authorize(creds)
    return gc


def update_cells_by_chunk(spreadsheet,
                          worksheet,
                          cells,
                          chunk_size=5000):

    ws = get_or_create_worksheet(spreadsheet, worksheet)

    cell_count = len(cells)
    print('Updating {} cells...'.format(cell_count))

    chunks = [cells[i:(i + chunk_size)]
              for i in range(0, len(cells), chunk_size)]

    for c in chunks:
        ws.update_cells(c)
        first_cell = (c[0].row, c[0].col)
        last_cell = (c[-1].row, c[-1].col)
        print('> updated {} to {}'.format(first_cell, last_cell))


def clear_by_chunks(spreadsheet, worksheet, chunk_size=5000):
    print('Clearing worksheet values...')
    ws = get_or_create_worksheet(spreadsheet, worksheet)
    cells = ws.range(create_range(ws.row_count,
                                  ws.col_count))
    for cell in cells:
        cell.value = ''
    update_cells_by_chunk(spreadsheet,
                          worksheet,
                          cells,
                          chunk_size=chunk_size)


def get_or_create_worksheet(spreadsheet, worksheet):
    client = get_client()
    ss = client.open(spreadsheet)
    for ws in ss.worksheets():
        if ws.title == worksheet:
            return ws

    print('Worksheet {} does not exist, creating...'.format(worksheet))
    return ss.add_worksheet(worksheet, 2500, 26)


def update_cell_values(spreadsheet, worksheet, headers, results):
    ws = get_or_create_worksheet(spreadsheet, worksheet)

    # Create table like object, then flatten list
    cell_values = list(results)
    cell_values.insert(0, headers)
    cell_values = [y for x in cell_values for y in x]

    # If the worksheet needs to be resized
    if len(headers) > ws.col_count or len(cell_values) > ws.row_count:
        print('Worksheet size insufficient, resizing...')
        ws.resize(rows=len(results) + 1, cols=len(headers) + 1)

    cells = ws.range(create_range(len(results) + 1, len(headers)))
    if len(cells) != len(cell_values):
        print('Aborted: cell to value mismatch')
        print('> cells:', len(cells))
        print('> values:', len(cell_values))
        return

    print('Updating cell values...')
    for index, cell in enumerate(cells):
        cell.value = default_value(cell_values[index])

    print('Syncing with worksheet...')
    update_cells_by_chunk(spreadsheet, worksheet, cells)


def create_range(rows, cols):
    start = gspread.utils.rowcol_to_a1(1, 1)
    end = gspread.utils.rowcol_to_a1(rows, cols)
    return start + ':' + end


def run(query):
    '''
    specific for pymysql, returns header, rows values
     '''

     # NOT SAFE FOR PRODUCTION
    cred = {
        'user': 'USERNAME',
        'password': 'PASSWORD'
        'database': 'DATABASE',
        'host': 'HOST'
        'charset': 'utf8'
    }

    try:
        conn = pymysql.connect(**cred)
        with conn.cursor() as cursor:
            cursor.execute(query)
            headers = [i[0] for i in cursor.description]
            results = cursor.fetchall()

            print('Executed DB query, results = {} rows, {} cols'.format(
                len(results),
                len(headers)
            ))
            return headers, results
    finally:
        conn.close()


def default_value(value):
    if not value:
        return ''

    return value


def update_worksheet_with_query(spreadsheet, worksheet, query, clear=True):
    # Grab the data from the database
    headers, results = run(query)

    # Clear the old results
    if clear:
        clear_by_chunks(spreadsheet, worksheet)

    # Create the cells and insert the new results
    update_cell_values(spreadsheet, worksheet, headers, results)