"""Read and Validate Adjustment File."""

import pandas as pd
import openpyxl
from openpyxl.styles import Alignment, PatternFill
from openpyxl.utils.cell import get_column_letter
import json

from adjustment.helpers import get_and_set_valid_account_ids
from adjustment.helpers import get_and_set_valid_contract_ids
from adjustment.helpers import get_and_set_statement_periods
from adjustment.helpers import get_and_set_upcs
from adjustment.helpers import get_and_set_contract_product_terms
from adjustment.helpers import get_and_set_contract_label_terms
from adjustment.validate.validation import Validation

FILE_BATCH_SIZE = 5000

class AdjustmentFile:
    def __init__(self, file):
        self.__file = file
        self.__excel_data = dict()
        self.__account_ids = set()
        self.__contract_ids = set()
        self.__statement_years = set()
        self.__upcs = set()
        self.__is_file_valid = True

    def __add_error_column_to_excel(self, new_column_num: int):
        """open excel file using openpyxl file and add "Error" column to existing file."""
        wb = openpyxl.load_workbook(self.__file)
        sheet = wb.active 
        new_column = new_column_num
        sheet.cell(
            row=1,
            column=new_column,
            value='Errors'
        ).fill = PatternFill(patternType='solid', fgColor='FF0000')

        sheet.column_dimensions[get_column_letter(new_column)].width = 40

        return wb, sheet

    def __write_error_column(self, sheet, new_column_num: int, errors: dict):
        """write errors to new column "Error."""
        if errors:
            print('Writing validation errors to excel sheet')
            for row_num in list(errors.keys()):
                sheet.cell(
                    row=row_num,
                    column=new_column_num,
                    value=json.dumps(errors[row_num])
                ).alignment = Alignment(wrap_text=True)

                sheet.row_dimensions[row_num].height = 50

        return True

    def _validate_adjustment(self):
        """get the required data from database and the validate the file."""
        errors = dict()
        get_and_set_valid_account_ids(self.__account_ids)
        get_and_set_valid_contract_ids(self.__contract_ids)
        get_and_set_statement_periods(self.__statement_years)
        get_and_set_upcs(self.__upcs)
        get_and_set_contract_product_terms(self.__contract_ids)
        get_and_set_contract_label_terms(self.__contract_ids)

        for index, adjustment_row in self.__excel_data.items():
            adjustment_validation = Validation(adjustment_row)
            validation_errors = adjustment_validation.validate_adjustment()
            if validation_errors:
                errors.update({index: validation_errors})
                self.__is_file_valid = False 

        return errors

    def read_and_validate_file(self):
        """Read and validate file.
        
        - The function reads the file data in batches and validates it.
        - It will add the "Error" column to existing excel sheet
          in case if there are any validation errors.
        """
        print("Reading excel sheet")
        BATCH_SIZE = FILE_BATCH_SIZE
        df = pd.read_excel(self.__file, na_filter=False)
        new_column_num = len(df.columns) + 1
        wb, sheet = self.__add_error_column_to_excel(new_column_num)

        for index, row in df.iterrows():
            if row[0]:
                self.__account_ids.add(str(row[0]))
            if row[1]:
                self.__contract_ids.add(str(row[1]))
            if row[5]:
                self.__statement_years.add(str(row[5]))
            if row[7]:
                self.__statement_years.add(str(row[7]))
            if row[2]:
                self.__upcs.add(str(row[2]))

            row_num = index + 2
            self.__excel_data.update({ row_num: row })

            if index and index % BATCH_SIZE == 0:
                print(f'Validating adjustments rows from {abs(row_num-BATCH_SIZE)} to {row_num}')
                validation_errors = self._validate_adjustment()

                self.__write_error_column(sheet, new_column_num, validation_errors)
                self.__excel_data = dict()
                self.__account_ids = set()
                self.__contract_ids = set()
                self.__statement_years = set()

        if len(self.__excel_data.keys()) <= BATCH_SIZE:
            print(f'Validating adjustments rows from {abs(row_num-len(self.__excel_data.keys()))} to {row_num}')
            validation_errors = self._validate_adjustment()

            self.__write_error_column(sheet, new_column_num, validation_errors)  

        if not self.__is_file_valid:
            wb.save(self.__file)
            wb.close()
            raise Exception("Validation errors has been added to the file.")
        
        wb.close()
        print("File validated successfully.")

        return True