# -*- coding: utf-8 -*-
"""
/***************************************************************************
 Sewercalc
                                 A QGIS plugin
 Plugin para dimensionamento de redes coletoras de esgoto 
 Generated by Plugin Builder: http://g-sherman.github.io/Qgis-Plugin-Builder/
                              -------------------
        begin                : 2025-02-07
        git sha              : $Format:%H$
        copyright            : (C) 2025 by Wanderilo  Lima
        email                : Wanderilo Lima
 ***************************************************************************/

/***************************************************************************
 *                                                                         *
 *   This program is free software; you can redistribute it and/or modify  *
 *   it under the terms of the GNU General Public License as published by  *
 *   the Free Software Foundation; either version 2 of the License, or     *
 *   (at your option) any later version.                                   *
 *                                                                         *
 ***************************************************************************/
"""


import sys
import os


plugin_dir = os.path.dirname(__file__)  
sys.path.insert(0, plugin_dir)         


from openpyxl import load_workbook
from openpyxl.styles import Border, Side, NamedStyle, Alignment


import json
from datetime import datetime
from .Sewercalc_config_cx_txt import carregar_configuracoes

def salvar_planilha(tabela_rede, layer_rede):
    configuracoes = carregar_configuracoes()
    
    pop_inicial = float(configuracoes.get("Edit_Pini", 0))
    pop_final = float(configuracoes.get("Edit_Pfim", 0))
    cons_per_capita = float(configuracoes.get("Edit_consumo", 0))
    k1 = float(configuracoes.get("Edit_k1", 0))
    k2 = float(configuracoes.get("Edit_k2", 0))
    coef_retorno = float(configuracoes.get("Edit_Retorno", 2))
    tx_inf = float(configuracoes.get("Edit_tx_infiltra", 0))
    R_min = float(configuracoes.get("Edit_R_min", 2))
    tensao = float(configuracoes.get("Edit_Tensao", 2))
    manning = float(configuracoes.get("Edit_manning", 3))
    L = float(configuracoes.get("L", 2))
    LT= float(configuracoes.get("LT", 3))
    vazao_ini= float(configuracoes.get("vazao_ini", 4))
    vazao_fim= float(configuracoes.get("vazao_fim", 4))
    
    caminho_completo = layer_rede.source() 
    pasta_salvar = os.path.dirname(caminho_completo) 
    
    caminho_modelo = os.path.join(os.path.dirname(__file__), 'modelo.xlsx')
    data_hora = datetime.now().strftime('%Y-%m-%d_%H-%M-%S')
    caminho_salvar = os.path.join(pasta_salvar, f'Memorial de cálculo_{data_hora}.xlsx')

    workbook = load_workbook(caminho_modelo)

    if 'REDE' in workbook.sheetnames:
        sheet = workbook['REDE'] 
    else:
        sheet = workbook.create_sheet('REDE')

    texto = NamedStyle(name="texto")
    centralizado = Alignment(horizontal='center')
    esquerda = Alignment(horizontal='left')
    numerico = NamedStyle(name="numerico")
    numerico.number_format = '0.00'
    border_bottom = Border(bottom=Side(style='thin'))

    def aplicar_estilo(cell, value=None, style=None, alignment=None, number_format=None):
        if value is not None:
            cell.value = value
        if style is not None:
            cell.style = style
        if alignment is not None:
            cell.alignment = alignment
        if number_format is not None:
            cell.number_format = number_format
            
    # PARAMETROS-----------------------------------------------------------------------------------------------------     
    colunas_parametro = {
    3: [
        {'value': round(pop_inicial, 0), 'style': numerico, 'alignment': centralizado, 'number_format': '0'},
        {'value': round(pop_final, 0), 'style': numerico, 'alignment': centralizado, 'number_format': '0'}
    ],
    5: [
        {'value': round(cons_per_capita, 0), 'style': numerico, 'alignment': centralizado, 'number_format': '0 " l/dia"'},
        {'value': round(coef_retorno, 2), 'style': numerico, 'alignment': centralizado, 'number_format': '0.00'}
    ],
    7: [
        {'value': round(tx_inf, 3), 'style': numerico, 'alignment': centralizado, 'number_format': '0.000 " l/s.km'},
        {'value': round(R_min, 2), 'style': numerico, 'alignment': centralizado, 'number_format': '0.00 " m"'}
    ],
    9: [
        {'value': round(k1, 2), 'style': numerico, 'alignment': centralizado, 'number_format': '0.00'},
        {'value': round(k2, 2), 'style': numerico, 'alignment': centralizado, 'number_format': '0.00'}
    ],
    11: [
        {'value': round(vazao_ini, 4), 'style': numerico, 'alignment': centralizado, 'number_format': '0.0000 " l/s"'},
        {'value': round(vazao_fim, 4), 'style': numerico, 'alignment': centralizado, 'number_format': '0.0000 " l/s"'}
    ],
    13: [
        {'value': round(L, 2), 'style': numerico, 'alignment': centralizado, 'number_format': '0.00 " m"'},
        {'value': round(LT, 2), 'style': numerico, 'alignment': centralizado, 'number_format': '0.00 " m"'}
    ],
    15: [
        {'value': round(tensao, 2), 'style': numerico, 'alignment': centralizado, 'number_format': '0.00 " Pa"'},
        {'value': round(manning, 3), 'style': numerico, 'alignment': centralizado, 'number_format': '0.000'}
    ]
}

# colunas na linha 10
    for col, configs in colunas_parametro.items():
        config = configs[0] 
        cell = sheet.cell(row=10, column=col)
        aplicar_estilo(
            cell,
            value=config.get('value'),
            style=config.get('style'),
            alignment=config.get('alignment'),
            number_format=config.get('number_format')
        )

    # colunas na linha 11
    for col, configs in colunas_parametro.items():
        config = configs[1]  
        cell = sheet.cell(row=11, column=col)
        aplicar_estilo(
            cell,
            value=config.get('value'),
            style=config.get('style'),
            alignment=config.get('alignment'),
            number_format=config.get('number_format')
        )

    

        
    #INFORMAÇÔES DA REDE ---------------------------------------------------------------------------------------
    colunas = {
        2: {'value': lambda v: v['NOME'], 'style': texto, 'alignment': centralizado},
        3: {'value': lambda v: v['PV_MONT'], 'style': texto, 'alignment': centralizado},
        4: {'value': lambda v: v['PV_JUS'], 'style': texto, 'alignment': centralizado},
        5: {'value': lambda v: round(v['L'], 2), 'style': numerico, 'alignment': centralizado, 'number_format': '0.00'},
        6: {'value': lambda v: round(v['CTM'], 3), 'style': numerico, 'alignment': centralizado, 'number_format': '0.000'},
        7: {'value': lambda v: round(v['CTJ'], 3), 'style': numerico, 'alignment': centralizado, 'number_format': '0.000'},
        8: {'value': lambda v: round(v['CCM'], 3), 'style': numerico, 'alignment': centralizado, 'number_format': '0.000'},
        9: {'value': lambda v: round(v['CCJ'], 3), 'style': numerico, 'alignment': centralizado, 'number_format': '0.000'},
        10: {'value': lambda v: round(v['PFM'], 3), 'style': numerico, 'alignment': centralizado, 'number_format': '0.000'},
        11: {'value': lambda v: round(v['PFJ'], 3), 'style': numerico, 'alignment': centralizado, 'number_format': '0.000'},
        12: {'value': lambda v: v['DN'] * 1000, 'style': numerico, 'alignment': centralizado, 'number_format': '0'},
        13: {'value': lambda v: round(v['i'], 6), 'style': numerico, 'alignment': centralizado, 'number_format': '0.000000'},
        14: {'value': lambda v: round(v['Q_ACM_INI'], 4), 'style': numerico, 'alignment': centralizado, 'number_format': '0.0000'},
        15: {'value': lambda v: round(v['Q_ACM_FIM'], 4), 'style': numerico, 'alignment': centralizado, 'number_format': '0.0000'},
        16: {'value': lambda v: round(v['VI'], 4), 'style': numerico, 'alignment': centralizado, 'number_format': '0.0000'},
        17: {'value': lambda v: round(v['VF'], 4), 'style': numerico, 'alignment': centralizado, 'number_format': '0.0000'},
        18: {'value': lambda v: round(v['VC'], 4), 'style': numerico, 'alignment': centralizado, 'number_format': '0.0000'},
        19: {'value': lambda v: round(v['T'], 2), 'style': numerico, 'alignment': centralizado, 'number_format': '0.00'},
        20: {'value': lambda v: v['Y_INI'] * 1000, 'style': numerico, 'alignment': centralizado, 'number_format': '0.0'},
        21: {'value': lambda v: v['Y_FIM'] * 1000, 'style': numerico, 'alignment': centralizado, 'number_format': '0.0'},
        22: {'value': lambda v: Degrau, 'style': texto, 'alignment': esquerda}
    }
            
    linha = 15
    for trecho, values in tabela_rede.items():
        if round(values['DG'], 3) > 0.84:
            Degrau = f" TQ - {round(values['DG'], 2)}"
        else:
            Degrau = f" DG - {round(values['DG'], 2)}"

        # Aplicar os valores e estilos às células--------------------------------------------
        for col, config in colunas.items(): 
            cell = sheet.cell(row=linha, column=col)
            aplicar_estilo(cell, **{k: v(values) if callable(v) else v for k, v in config.items()})

        # Aplicar borda inferior -----------------------------------------
        for col in range(2, 23): 
            sheet.cell(row=linha, column=col).border = border_bottom
            
        linha += 1


    borda_fina = Side(border_style='thin', color='000000')
    borda_media = Side(border_style='medium', color='000000')

    for row in range(10, 11 + 1):  # linhas 10 e 11
        for col in range(2, 15 + 1):  # colunas B a O
            cell = sheet.cell(row=row, column=col)

            # Define bordas externas (contorno) como 'medium'
            top = borda_media if row == 10 else borda_fina
            bottom = borda_media if row == 11 else borda_fina
            left = borda_media if col == 2 else borda_fina
            right = borda_media if col == 15 else borda_fina

            cell.border = Border(top=top, bottom=bottom, left=left, right=right)

    
    area_impressao = f'B1:V{linha + 1}'
    sheet.print_area = area_impressao
    
    workbook.save(caminho_salvar)
    print(pasta_salvar)
    
    return pasta_salvar