import os
import gspread
from oauth2client.service_account import ServiceAccountCredentials

from app.settings import app_cfg as cfg

def fetch_spreadsheet():
  """Open the Google Spreadsheet, which contains the individual worksheets"""
  scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
  path = os.path.dirname(os.path.abspath(__file__))
  credentials = ServiceAccountCredentials.from_json_keyfile_name(cfg.GOOGLE_ACCOUNT_CREDS_PATH, scope)
  docid = "1denb7TjYsN9igHyvYah7fQ0daABW32Z30lwV7QrDJQc"
  client = gspread.authorize(credentials)
  spreadsheet = client.open_by_key(docid)
  return spreadsheet

def fetch_worksheet(name):
  """Get a reference to a particular "worksheet" from the Google Spreadsheet"""
  spreadsheet = fetch_spreadsheet()
  return spreadsheet.worksheet(name)

def fetch_google_sheet(name):
  """Get all the values from a particular worksheet as a list of lists.
  Returns:
  :keys - the first row of the document
  :lines - a list of lists with the rest of the rows"""
  rows = fetch_worksheet(name).get_all_values()
  keys = rows[0]
  lines = rows[1:]
  return keys, lines

def fetch_google_sheet_objects(name):
  """Get all the values from a worksheet as a list of dictionaries"""
  keys, rows = fetch_google_sheet(name)
  recs = []
  for row in rows:
    rec = {}
    for index, key in enumerate(keys):
      rec[key] = row[index]
    recs.append(rec)
  return recs

def fetch_google_lookup(name, item_key='key'):
  """Get all the values from a worksheet as a dictionary of dictionaries.
  Specify which field you want to use as the dictionary key."""
  keys, rows = fetch_google_sheet(name)
  lookup = {}
  for row in rows:
    rec = {}
    for index, key in enumerate(keys):
      rec[key] = row[index]
    lookup[rec[item_key]] = rec
  return lookup

def fetch_verified_paper_lookup():
  """Fetch a lookup keyed by dataset, where each dataset points to a hash of valid or invalid papers..."""
  keys, rows = fetch_google_sheet('verifications')
  verified_lookup = {}
  for row in rows:
    rec = {}
    for index, key in enumerate(keys):
      rec[key] = row[index]
    if rec['dataset'] not in verified_lookup:
      verified_lookup[rec['dataset']] = {}
    if str(rec['uses_dataset']) == '1':
      verified_lookup[rec['dataset']][rec['paper_id']] = rec
  return verified_lookup

def update_or_append_worksheet(name, form):
  """Update a row if it exists, create it if it doesn't"""
  worksheet = fetch_worksheet(name)
  keys = worksheet.row_values(1)
  row = [ form[key] if key in form else '' for key in keys ]
  try:
    cell = worksheet.find(form['paper_id'])
  except:
    cell = None
  if cell:
    for i, item in enumerate(row):
      worksheet.update_cell(cell.row, i+1, item)
  else:
    worksheet.append_row(row)