from openpyxl import load_workbook
from openpyxl.styles import Border, Side, Font, colors
from openpyxl.styles.borders import BORDER_THIN


# file_name = input('file name: ')
file_name = 'TEMPLATE.xlsx'
untouched_wb = load_workbook(file_name)
untouched_ws = untouched_wb.active

all_rows = untouched_ws.rows
f_column = untouched_ws['F']
i_column = untouched_ws['I']
j_column = untouched_ws['J']
ft_bold = Font(bold=True)
thin_allborder = Border(bottom=Side(border_style=BORDER_THIN, color='00000000'),
                        top=Side(border_style=BORDER_THIN, color='00000000'))
ft_red = Font(color=colors.RED)
ignore_indexes = []
offset = 0


def reddify(index):
    f_column[index].font = ft_red
    i_column[index].font = ft_red
    j_column[index].font = ft_red


def insert_total(start_index, stop_index, ignore_list):
    total = 0
    for index_here in range(start_index, stop_index+1):
        if index_here in ignore_list:
            continue
        total += j_column[index_here].value
    return total


for index, each_name in enumerate(f_column):
    if each_name.value and each_name.value.lower() != 'name':
        present_value = each_name.value
        prev_value = f_column[index - 1].value
        next_value = f_column[index + 1].value
        if present_value != prev_value and present_value != next_value:
            if present_value and i_column[index].value and i_column[index].value.lower() != 'subcontractors':
             reddify(index)
            continue

        if i_column[index].value.lower() != 'subcontractors':
            ignore_indexes.append(index)
            # determine start
        if present_value != prev_value or not prev_value:
            start = index
        # determine end
        if prev_value and next_value != present_value:
            end = index

            total_amount = insert_total(start, end, ignore_indexes)

            total_index = end + 2 + offset
            untouched_ws.insert_rows(total_index)
            untouched_ws[f'J{total_index}'] = total_amount
            untouched_ws[f'J{total_index}'].font = ft_bold
            untouched_ws[f'J{total_index}'].border = thin_allborder

            for indexey in ignore_indexes:
                reddify(indexey)
            offset += 1
            ignore_indexes = []



untouched_wb.save(f'new_{file_name}')
print(f'>>SAVED NEW FILE!!')