import copy

from openpyxl import Workbook
from django.http import HttpResponse
from openpyxl.writer.excel import save_virtual_workbook
from openpyxl.utils import get_column_letter
from BaseModels.mailSender import techSendMail
from openpyxl.styles import PatternFill, Font, Alignment
from openpyxl.styles.borders import Border, Side
from openpyxl.styles.numbers import BUILTIN_FORMATS
from colorsys import rgb_to_hls


def pairwise(iterable):
    a = iter(iterable)
    return zip(a, a)


options_params_splitter = '<|>'


def set_col_options(ws, row, col, rows_len, options):
    if type(options) == str:
        options = dict(item.split("=")[::1] for item in options.split('&'))

    cols_set = 1
    if options:

        exists_group_option_for_column = False
        for key in options.keys():
            if key.startswith('g_col_'):
                exists_group_option_for_column = True
                break

        if 'cols_merge' in options and options['cols_merge']:
            cols_set = int(options['cols_merge'])
            if cols_set > 1:
                ws.merge_cells(start_row=row, start_column=col, end_row=row, end_column=col + cols_set - 1)

        if exists_group_option_for_column:
            g_col_back_color = None
            if 'g_col_back_color' in options and options['g_col_back_color']:
                g_col_back_color = options['g_col_back_color']
            g_col_num_w_sep = None
            if 'g_col_num_w_sep' in options and options['g_col_num_w_sep']:
                g_col_num_w_sep = options['g_col_num_w_sep']

            cur_col = col
            while cur_col < col + cols_set:
                cur_row = row
                while cur_row < rows_len:
                    if g_col_back_color:
                        ws.cell(row=cur_row, column=cur_col).fill = PatternFill('solid', fgColor=g_col_back_color)
                    if g_col_num_w_sep:
                        ws.cell(row=cur_row, column=cur_col).number_format = '#,##0.00'
                    cur_row += 1
                cur_col += 1

        if 'col_show_total' in options and options['col_show_total']:
            ws.cell(row=rows_len, column=col).font = Font(bold=True)
            ws.cell(row=rows_len, column=col).value = "=SUM({0}{1}:{0}{2})".format(
                get_column_letter(col),
                row + 1,
                rows_len - 1
            )
            ws.cell(row=rows_len, column=col).number_format = '#,##0.00'

        if 'back_color' in options and options['back_color']:
            ws.cell(row=row, column=col).fill = PatternFill('solid', fgColor=options['back_color'])

        if 'bold' in options and options['bold']:
            ws.cell(row=row, column=col).font = Font(bold=True)

        if 'col_bold' in options and options['col_bold']:
            cur_col = col
            while cur_col < col + cols_set:
                cur_row = row
                while cur_row < rows_len:
                    ws.cell(row=cur_row, column=cur_col).font = Font(bold=True)
                    cur_row += 1
                cur_col += 1

    return cols_set


def add_table_in_workbook(work_sheet, data, convert_minus_to_null=False, headers_rows_count=0):
    thin_border = Border(left=Side(style='thin'),
                         right=Side(style='thin'),
                         top=Side(style='thin'),
                         bottom=Side(style='thin'))

    r = 1

    for row in data:

        try:

            c = 1
            cols = row
            if type(data) == dict:
                cols = row.values()
            for val in cols:
                options = None

                inc_c = 1

                work_sheet.cell(row=r, column=c).border = thin_border

                # получаем опции
                if type(val) == str:
                    val_w_options = val.split(options_params_splitter)
                    if len(val_w_options) > 1:
                        val = val_w_options[0]
                        # применяем опции
                        inc_c = set_col_options(work_sheet, row=r, col=c, rows_len=len(data) + 1,
                                                options=val_w_options[1])
                elif type(val) == dict:
                    inc_c = set_col_options(work_sheet, row=r, col=c, rows_len=len(data) + 1, options=val)
                    val = val['val']

                # если стоит опция "минусовые значения преобразовывать в нулевые"
                if convert_minus_to_null:
                    try:
                        if val < 0:
                            val = 0
                    except:
                        pass

                try:
                    work_sheet.cell(row=r, column=c).value = val
                except:
                    work_sheet.cell(row=r, column=c).value = str(val)

                c += inc_c

        except Exception as e:
            msg = f'add_table_in_workbook in row {str(r)} ERROR = {str(e)}'
            print(msg)

        r += 1

    try:

        dims = {}
        row_c = 0
        for row in work_sheet.rows:
            # не подгоняем данные под надписи в хэдере
            if row_c < headers_rows_count:
                row_c += 1
                continue

            for cell in row:
                if cell.value:
                    dims[cell.column] = max((dims.get(cell.column, 0), len(str(cell.value))))

            row_c += 1

        for col, value in dims.items():
            if value > 150:
                value = 150
            if value < 3:
                value = 3
            work_sheet.column_dimensions[get_column_letter(col)].width = value

    except Exception as e:
        msg = f'add_table_in_workbook in sets width ERROR = {str(e)}'
        print(msg)

    return


def get_xls_file_by_data_list(data, convert_minus_to_null=False):
    try:

        wb = Workbook()
        ws = wb.active

        if type(data) == list and len(data) and type(data[0]) == dict:

            i = 0
            for page in data:

                title = None
                if 'title' in page:
                    title = page['title']

                # если первая страница - она уже создана, просто переименовываем
                if i == 0:
                    if title:
                        ws.title = title
                else:
                    ws = wb.create_sheet(title)

                headers_rows_count = 0
                if 'headers_rows_count' in page:
                    headers_rows_count = page['headers_rows_count']

                add_table_in_workbook(ws, page['table'], convert_minus_to_null, headers_rows_count)

                i += 1
        else:
            add_table_in_workbook(ws, data, convert_minus_to_null)

        xls_file = save_virtual_workbook(wb)

        return xls_file

    except Exception as e:
        msg = str(e)
        print(msg)
        return msg