import typing
import necrobot.exception
from necrobot.gsheet.makerequest import make_request
from necrobot.gsheet.sheetcell import SheetCell
from necrobot.gsheet.sheetrange import SheetRange
from necrobot.gsheet.spreadsheets import Spreadsheets


class WorksheetIndexData(object):
    """
    Stores the index of various columns on a GSheet.
    Call initialize() to read these indicies automatically from searching the sheet.
    """

    def __init__(self, gsheet_id: str, columns: typing.List[str]):
        # Sheet info
        self.gsheet_id = gsheet_id
        self.wks_name = None
        self.wks_id = None
        self._sheet_size = None

        # Array bounds
        self.min_column = None
        self.max_column = None
        self.header_row = None
        self.footer_row = None

        # Column indicies
        self._col_names = columns
        self._col_indicies = dict()

    def __getattr__(self, item):
        return self.getcol(item)

    @property
    def col_indicies(self):
        return self._col_indicies

    @property
    def num_columns(self):
        return len(self._col_indicies)

    def getcol(self, colname) -> int or None:
        colname = self._convert_colname(colname)
        if colname in self._col_indicies:
            return self._col_indicies[colname] - self.min_column
        else:
            return None

    def reset(self, columns: typing.List[str]) -> None:
        """Sets new column names and wipes all data."""
        self.wks_name = None
        self.wks_id = None
        self._sheet_size = None

        # Array bounds
        self.min_column = None
        self.max_column = None
        self.header_row = None
        self.footer_row = None

        # Column indicies
        self._col_names = columns
        self._col_indicies = dict()

    async def initialize(self, wks_name: str, wks_id: str) -> None:
        """Read the GSheet and store the indicies of columns
        
        Parameters
        ----------
        wks_name: str
            The name of the worksheet to initialize from.
        wks_id: int
            The ID of the worksheet to initialize from.
        
        Raises
        ------
        googleapiclient.error.Error
            Uncaught Google API error.
        AlreadyInitializedException
            If we've already called initialize() on this object
        NotFoundException
            If cannot find a worksheet with name wks_name
        """
        if self.wks_id is not None:
            raise necrobot.exception.AlreadyInitializedExecption(
                'Worksheet already initialized <wks_name = {]> <wks_id = {}>'.format(self.wks_name, self.wks_id)
            )

        async with Spreadsheets() as spreadsheets:
            # Find the size of the worksheet
            request = spreadsheets.get(spreadsheetId=self.gsheet_id)
            sheet_data = await make_request(request)
            for sheet in sheet_data['sheets']:
                props = sheet['properties']
                if wks_name is not None and props['title'] == wks_name:
                    self.wks_name = wks_name
                    self.wks_id = props['sheetId']
                    self._sheet_size = (int(props['gridProperties']['rowCount']),
                                        int(props['gridProperties']['columnCount']),)
                    break
                elif wks_id is not None and props['sheetId'] == wks_id:
                    self.wks_name = props['title']
                    self.wks_id = wks_id
                    self._sheet_size = (int(props['gridProperties']['rowCount']),
                                        int(props['gridProperties']['columnCount']),)
                    break

            if self.wks_id is None:
                self.wks_id = 0
                # Old code, we're doing hacky stuff for S8 tho
                # raise necrobot.exception.NotFoundException(
                #     "No worksheet with name {wks_name} on GSheet {gsheetid}".format(
                #         wks_name=wks_name,
                #         gsheetid=self.gsheet_id
                #     )
                # )

            await self._refresh(spreadsheets)

    @property
    def valid(self):
        return self.header_row is not None and self.min_column is not None

    @property
    def bottom_idx(self):
        return self.footer_row - self.header_row - 2

    @property
    def full_range(self):
        return SheetRange(
            ul_cell=(self.header_row + 1, self.min_column,),
            lr_cell=(self.footer_row - 1, self.max_column,),
            wks_name=self.wks_name
        )

    @property
    def full_range_extend_right(self):
        return SheetRange(
            ul_cell=(self.header_row + 1, self.min_column,),
            lr_cell=(self.footer_row - 1, self._sheet_size[1],),
            wks_name=self.wks_name
        )

    def get_range(self, left, right, top, bottom) -> SheetRange:
        return SheetRange(
            ul_cell=(self.header_row + top + 1, self.min_column + left,),
            lr_cell=(self.header_row + bottom + 1, self.min_column + right,),
            wks_name=self.wks_name
        )

    def get_range_for_column(self, col_idx):
        return self.get_range(left=col_idx, right=col_idx, top=0, bottom=self.footer_row - 1)

    async def get_values(self, spreadsheets, extend_right=False):
        range_to_get = self.full_range_extend_right if extend_right else self.full_range
        request = spreadsheets.values().get(
            spreadsheetId=self.gsheet_id,
            range=range_to_get,
            majorDimension='ROWS'
        )
        return await make_request(request)

    async def refresh_all(self):
        """Refresh all data"""
        async with Spreadsheets() as spreadsheets:
            # Find the size of the worksheet
            request = spreadsheets.get(spreadsheetId=self.gsheet_id)
            sheet_data = await make_request(request)
            for sheet in sheet_data['sheets']:
                props = sheet['properties']
                if props['sheetId'] == self.wks_id:
                    self.wks_name = props['title']
                    self._sheet_size = (int(props['gridProperties']['rowCount']),
                                        int(props['gridProperties']['columnCount']),)
                    break

            await self._refresh(spreadsheets)

    async def refresh_footer(self):
        """Refresh the self.footer_row property from the GSheet"""
        async with Spreadsheets() as spreadsheets:
            # Find the header row and the column indicies
            row_query_min = 1
            row_query_max = 10
            while self.footer_row is None and row_query_min <= self._sheet_size[0]:
                # Get the cells
                range_to_get = SheetRange(
                    ul_cell=(row_query_min, 1,),
                    lr_cell=(row_query_max, self._sheet_size[1],),
                    wks_name=self.wks_name
                )
                request = spreadsheets.values().get(
                    spreadsheetId=self.gsheet_id,
                    range=range_to_get,
                    majorDimension='ROWS'
                )
                value_range = await make_request(request)

                # Check if the cells we got are completely empty
                if 'values' not in value_range:
                    if self.header_row is not None:
                        self.footer_row = row_query_min

                # If there are values in the cells, find header and footers
                else:
                    values = value_range['values']
                    # If we got fewer than the requested number of rows, we've found the footer
                    if len(values) < row_query_max - row_query_min + 1:
                        self.footer_row = row_query_min + len(values)

                # Prepare for next loop
                row_query_min = row_query_max + 1
                row_query_max = min(2*row_query_max, self._sheet_size[0])

    async def update_cell(self, row: int, col: int, value: str, raw_input: bool = True) -> bool:
        """Update a single cell.

        Parameters
        ----------
        row: int
            The row index (begins at 0).
        col: int
            The column index (begins at 0).
        value: str
            The cell value.
        raw_input: bool
            If False, GSheets will auto-format the input.

        Returns
        -------
        bool
            True if the update was successful.
        """
        if not self.valid:
            raise RuntimeError('Trying to update a cell on an invalid MatchupSheet.')

        row += self.header_row + 1
        col += self.min_column
        range_str = str(SheetCell(row, col, wks_name=self.wks_name))
        value_input_option = 'RAW' if raw_input else 'USER_ENTERED'
        value_range_body = {'values': [[value]]}
        async with Spreadsheets() as spreadsheets:
            request = spreadsheets.values().update(
                spreadsheetId=self.gsheet_id,
                range=range_str,
                valueInputOption=value_input_option,
                body=value_range_body
            )
            response = await make_request(request)
            return response is not None

    async def update_cells(self, sheet_range: SheetRange, values: list, raw_input=True) -> bool:
        """Update all cells in a range.

        Parameters
        ----------
        sheet_range: SheetRange
            The range to update.
        values: list[list[str]]
            An array of values; one of the inner lists is a row, so values[i][j] is the ith row, jth column value.
        raw_input
            If False, GSheets will auto-format the input.

        Returns
        -------
        bool
            True if the update was successful.
        """
        range_str = str(sheet_range)
        value_input_option = 'RAW' if raw_input else 'USER_ENTERED'
        value_range_body = {'values': values}
        async with Spreadsheets() as spreadsheets:
            request = spreadsheets.values().update(
                spreadsheetId=self.gsheet_id,
                range=range_str,
                valueInputOption=value_input_option,
                body=value_range_body
            )
            response = await make_request(request)
            return response is not None

    async def _refresh(self, spreadsheets):
        """Find the array bounds and the column indicies"""
        # Find the header row and the column indicies
        row_query_min = 1
        row_query_max = 10
        col_vals = []
        while self.footer_row is None and row_query_min <= self._sheet_size[0]:
            # Get the cells
            range_to_get = SheetRange(
                ul_cell=(row_query_min, 1,),
                lr_cell=(row_query_max, self._sheet_size[1],),
                wks_name=self.wks_name
            )
            request = spreadsheets.values().get(
                spreadsheetId=self.gsheet_id,
                range=range_to_get,
                majorDimension='ROWS'
            )
            value_range = await make_request(request)

            # Check if the cells we got are completely empty
            if 'values' not in value_range:
                if self.header_row is not None:
                    self.footer_row = row_query_min

            # If there are values in the cells, find header and footers
            else:
                values = value_range['values']
                for row, row_values in enumerate(values):
                    row += 1
                    if self.header_row is None:
                        for col, cell_value in enumerate(row_values):
                            col += 1
                            if self._make_index(cell_value, col):
                                self.header_row = row
                                col_vals.append(col)

                # If we got fewer than the requested number of rows, we've found the footer
                if len(values) < row_query_max - row_query_min + 1:
                    self.footer_row = row_query_min + len(values)

            # Prepare for next loop
            row_query_min = row_query_max + 1
            row_query_max = min(2 * row_query_max, self._sheet_size[0])

        if col_vals:
            self.min_column = min(self.min_column, min(col_vals)) if self.min_column is not None else min(col_vals)
            self.max_column = max(self.max_column, max(col_vals)) if self.max_column is not None else max(col_vals)

    def _make_index(self, cell_value: str, col: int) -> bool:
        for colname in self._col_names:
            if colname.lower() in cell_value.lower():
                self._col_indicies[self._convert_colname(colname)] = col
                return True
        return False

    @staticmethod
    def _convert_colname(c):
        return c.replace(' ', '_')
