from pathlib import Path
import webbrowser

import gspread

def _view(self: gspread.worksheet.Worksheet):
    """Open this Google Sheet in the default web browser"""
    webbrowser.open(self.url)

def _protect(self):
    body = {
        "requests": [{
            "addProtectedRange": {"protectedRange": {
                "range": {"sheetId": self._properties['sheetId'],},
                "warningOnly": False,
                "description": f"LOCKED by: {self.email}",
                "editors": {
                        "domainUsersCanEdit": False,
                        "users": [self.email]
        },}}}]}
    try:
        return self.spreadsheet.batch_update(body)
    except gspread.exceptions.APIError as api:
        print(f"'{self._properties['title']}' is already protected")
        return api

def _unprotect(self):
    range_id = self.spreadsheet.list_protected_ranges(self.id).pop()
    range_id = range_id['protectedRangeId']
    body = {
        "requests":
            [{"deleteProtectedRange": {"protectedRangeId": range_id}}]
    }
    try:
        return self.spreadsheet.batch_update(body)
    except gspread.exceptions.APIError as api:
        print(f"'{self._properties['title']}' is already protected")
        return api

def _df(self):
    """
    Return a Pandas Dataframe from the specified Google Sheet object.

    Add attribute:
      pd.DataFrame.sheet : gspread.worksheet.Worksheet

    Add method:
        df.to_gspread()
    """
    global pd  # Just-in-time import, so as not to 'force' Pandas requirement
    import pandas as pd  
    df = pd.DataFrame(self.get_all_records(numericise_ignore=['all']))
    df.sheet = self
    setattr(pd.DataFrame, "to_gspread", _to_gspread)
    return df

def _to_gspread(self: pd.DataFrame, *args, **kwargs):
    """
    Save (ie. clear and update) the Pandas DataFrame to its parent Google Sheet
    (gspread.worksheet.Worksheet object) specified in self.sheet
    """
    sheet = self.sheet
    sheet.clear()
    sheet.update([self.columns.values.tolist()] + self.values.tolist())
    print(f"Saved: {sheet.spreadsheet.title}\n{sheet.url}")

def Worksheet(key_or_title=None, sheet_name="", sheet_index=0, account_path=""):
    """
    Return a Google Worksheet object (gspread.worksheet.Worksheet) from the
    specified workbook and worksheet, or the first worksheet if not specified.

    sheet_index uses normal Python counting ie. starts at 0.

    Added helper methods and shortcuts:

      gspread.worksheet.Worksheet.protect()
      gspread.worksheet.Worksheet.unprotect()
      gspread.worksheet.Worksheet.view()
      gspread.worksheet.Worksheet.df
      pandas.DataFrame.to_gspread()

      .account_path = account_path
      .email = client.auth._service_account_email

    """
    if key_or_title is None and "WORKBOOK_ID" in globals():
        key_or_title = WORKBOOK_ID
    if account_path == "" and "GOOGLE_SERVICE_ACCOUNT_JSON_PATH" in globals():
        account_path = GOOGLE_SERVICE_ACCOUNT_JSON_PATH
    try:
        account_path = account_path.resolve()
        client = gspread.service_account(filename=account_path)
    except FileNotFoundError:
        print(f"FileNotFoundError:\n{account_path}")
        return
    try:
        book = client.open(key_or_title)
    except gspread.exceptions.SpreadsheetNotFound:
        try:
            book = client.open_by_key(key_or_title)
        except gspread.exceptions.APIError:
            print(f"gspread.exceptions.APIError:\n  '{key_or_title}' not recognised as a key or title.")
            return
    try:
        if sheet_name:
            sheet = book.worksheet(sheet_name)
        else:
            sheet = book.worksheets()[sheet_index]
    except (gspread.exceptions.WorksheetNotFound, IndexError, NameError):
        key = f"'{sheet_name}'" if sheet_name else f"index {sheet_index}"
        print(f"gspread.exceptions.WorksheetNotFound:\n  sheet {key} not found.")
        sheet = None
    # Add helper methods and shortcuts
    setattr(gspread.worksheet.Worksheet, "view", _view)
    setattr(gspread.worksheet.Worksheet, "protect", _protect)
    setattr(gspread.worksheet.Worksheet, "unprotect", _unprotect)
    setattr(gspread.worksheet.Worksheet, 'df', property(_df))
    sheet.account_path = account_path
    sheet.email = client.auth._service_account_email
    return sheet