###########################################################################
#
# Copyright (c) 2021-2024 Diality Inc. - All Rights Reserved.
#
# THIS CODE MAY NOT BE COPIED OR REPRODUCED IN ANY FORM, IN PART OR IN
# WHOLE, WITHOUT THE EXPLICIT PERMISSION OF THE COPYRIGHT OWNER.
#
# @file    excel_ops.py
#
# @author  (last)      Dara Navaei
# @date    (last)      22-Feb-2022
# @author  (original)  Dara Navaei
# @date    (original)  21-Feb-2021
#
############################################################################

import os
import math
import datetime
from openpyxl.styles import PatternFill, Font, Alignment, Protection
from openpyxl.utils import get_column_letter
from openpyxl import Workbook, load_workbook

COLUMN_WIDTH_TOLERANCE = 3.0
COLUMN_MAX_WIDTH = 50.0
ROW_DEFAULT_HEIGHT = 15.0
GREEN = PatternFill(start_color='00FF00', end_color='00FF00', fill_type='solid')
YELLOW = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
BLUE = PatternFill(start_color='00CCFF', end_color='00CCFF', fill_type='solid')
RED = PatternFill(start_color='FF0000', end_color='FF0000', fill_type='solid')
NO_COLOR = PatternFill(fill_type='none')


def get_an_excel_workbook():
    """
   This function returns an excel workbook object

    @return excel workbook object
    """
    return Workbook()


def setup_excel_worksheet(workbook_obj, title, index=None, protection=False):
    """
    Creates the worksheets in the created excel file with the name of each software_project_name as the name of
    the worksheet.

    @param workbook_obj: Excel workbook object
    @param title: Title of the created worksheet
    @param index: Index of the worksheet. If a sheet needs to be at a certain place (default none)
    @param protection: Flag to indicate whether the worksheet is write protected or not (default False)

    @return none
    """
    # Assume the new sheet does not exist
    is_sheet_name_available = False

    # Loop through the list of the sheet names in the excel
    for sheet_name in workbook_obj.sheetnames:
        # If the requested title exists in the excel document, set as True
        # and break out of the loop.
        if title == sheet_name:
            is_sheet_name_available = True
            break
    # If the requested sheet does not exist, create it
    if is_sheet_name_available is False:
        # Create a tab and name the tab with the name of the projects dictionary
        workbook_obj.create_sheet(title=title, index=index)

    # Check if the created worksheet must be write protected and if yes, protect it
    if protection:
        workbook_obj[title].protection.sheet = True

    # If the first tab is Sheet or Sheet1, remove it
    # The other tabs must be created first before removing the default tab
    if workbook_obj.sheetnames[0] == 'Sheet' or workbook_obj.sheetnames[0] == 'Sheet1':
        sheet = workbook_obj.get_sheet_by_name(workbook_obj.sheetnames[0])
        workbook_obj.remove_sheet(sheet)


def write_to_excel(workbook_obj, project, row, column, data, name='Calibri', font=11, bold=False, color=None,
                   merge=None, horizontal='left', freeze=False, max_col_len=None, protect_cell=False):
    """
    This function writes data at the specified row and column in an excel file (object).

    @param workbook_obj: Excel workbook object
    @param project: Name of the sheet (i.e HD-DEN-4308)
    @param row: Current row number
    @param column: Current column number
    @param data: Data to be written at the cell
    @param name: Font type (default Calibri)
    @param font: Font size (default 11)
    @param bold: Bold or un-bold (default un-bold)
    @param color: Color of the cell (default no color)
    @param merge: Merge cells. Cells must be provided with A1:A4 format (default none)
    @param horizontal: Horizontal alignment (default left)
    @param freeze: Freeze top row (default false)
    @param max_col_len: maximum length of a column (default none, means it is not restricted)
    @param protect_cell: flag to indicate whether to write protect cell or not (default False)

    @return: None
    """
    row_height = 0
    # Get the number of an alphabetic column (i.e A -> 1)
    column_letter = get_column_letter(column)
    cell_name = column_letter + str(row)

    # Set the active worksheet to the provided worksheet
    active_sheet = workbook_obj[project]
    # Set the provided data into the specified row and column and set the bold, color and horizontal alignment
    active_sheet.cell(row=row, column=column).value = data
    active_sheet.cell(row=row, column=column).font = Font(size=font, bold=bold, name=name)
    # Wrapping text is not needed unless the length of the data is more than maximum column
    # length
    active_sheet[cell_name].alignment = Alignment(vertical='center', horizontal=horizontal, wrap_text=False)
    # Get the width of the current column
    column_width = active_sheet.column_dimensions[column_letter].width
    # When the column width is on the default, openpyxl reports None. If the width is reported as None,
    # it will be set to 0 for math comparison
    column_width = 0 if column_width is None else column_width
    # If the length is not provided, use the default maximum length
    max_len = COLUMN_MAX_WIDTH if max_col_len is None else max_col_len
    # Remove all the end of the line artifacts
    length_of_data = len(str(data).rstrip())

    # If the length of data was greater than the maximum length, calculate the number of
    # rows is needed with the
    # default height
    if length_of_data > max_len:
        # Since the length is greater than maximum, enable wrap text
        active_sheet[cell_name].alignment = Alignment(vertical='center', horizontal=horizontal, wrap_text=True)
        # Calculate what the row height should be when the cell is extended
        row_height = math.ceil(length_of_data / max_len) * ROW_DEFAULT_HEIGHT
        active_sheet.column_dimensions[column_letter].width = max_len
    # If the length of the data provided is already less than the length of cell,
    # do nothing
    elif column_width < length_of_data:
        active_sheet.column_dimensions[column_letter].width = length_of_data + COLUMN_WIDTH_TOLERANCE
        row_height = ROW_DEFAULT_HEIGHT

    # If the current row height is not defined or the row height is less than the calculated new height, set it
    if active_sheet.row_dimensions[row].height is None or active_sheet.row_dimensions[row].height < row_height:
        active_sheet.row_dimensions[row].height = row_height

    # If color has been defined, set the color of the cell
    if color is not None:
        active_sheet[cell_name].fill = color

    # If merge has been requested
    if merge is not None:
        # The format of merge for this function is A1:C1
        active_sheet.merge_cells(str(merge))

    if freeze:
        # To freeze row 1, make the cell is not row 1, that's why A2 was chosen
        active_sheet.freeze_panes = 'A2'

    # Enforce the cell protection whether it is a False or True
    active_sheet.cell(row, column).protection = Protection(locked=protect_cell)


def load_excel_report(path):
    """
   This function returns an object of a currently existing excel workbook

    @return loaded excel workbook object
    """
    return load_workbook(path)


def get_cell_value(workbook_obj, project, row, col):
    """
   This function returns the value of a written excel cell

    @return excel workbook object
    """
    # Set the active worksheet to the provided worksheet
    active_sheet = workbook_obj[project]
    # Get the cell object
    cell_obj = active_sheet.cell(row=row, column=col)
    # Convert it to the actual value
    return cell_obj.value


def merge_cells(workbook_obj, project, start_row, start_col, end_row, end_col):
    """
    This function merges the specified cells.

    @param workbook_obj: Excel workbook object
    @param project: Name of the sheet (i.e HD-DEN-4308)
    @param start_row: Row number at the beginning of merge
    @param start_col: Column number at the beginning of merge
    @param end_row: Row number at the end of merge
    @param end_col: Column number at the end of merge

    @return: None
    """
    # Go to the define sheet
    active_sheet = workbook_obj[project]
    # Convert the cell numbers to text and number (i.e row=1 and col=1 -> A1) for the start and end cells
    merge_start_cell = get_column_letter(start_col) + str(start_row)
    merge_end_cell = get_column_letter(end_col) + str(end_row)
    # The format of merge for this function is A1:C1
    active_sheet.merge_cells(str(merge_start_cell) + ':' + str(merge_end_cell))


def save_report(excel_workbook, save_dir, record_name, stack_name=None):
    """
    This function overrides the save function in the Base class. The function saves the excel file.

    @param excel_workbook: Excel workbook object
    @param save_dir: Saving directory
    @param record_name: Type of record being saved (i.e. calibration, software configuration)
    @param stack_name: Name of the software stack name (i.e. HD) default none

    @returns none
    """
    # Get the current date
    current_date = str(datetime.datetime.now().date())
    # Some of the records might want to add the name of the stack. For instance, software configurations might want to
    # mention that this is an HD or DG software configuration report.
    if stack_name is not None:
        address = current_date + '-' + str(stack_name) + '-' + str(record_name).upper() + '-Record.xlsx'
    else:
        address = current_date + '-' + str(record_name).upper() + '-Record.xlsx'
    # Create the save path by using the path, date and current code review excel count out of total number of them
    path = os.path.join(save_dir, address)
    excel_workbook.save(filename=path)