from asyncio.log import logger
from datetime import datetime
from enum import Enum
import pandas as pd
import pyodbc
from models.aapolfps import aapolfps
from models.tisdb import TISDB

water_column_mapping = {    
    'VS': 'vs',
    'Amount': 'celkem',
    'WaterMeterStatusDate': 'datumOdectu', 
    'WaterMeterPreviousStatus': 'stav1',
    'WaterMeterStatus' : 'stav2', 
    'Consumption' : 'spotreba', 
    'WaterRate' : 'sazba', 
    'WaterFeeAmount' : 'vodne',         
    'SewerRate' : 'sazbaStocne', 
    'SewerFeeAmount' : 'stocne',     
    'WaterFeeAmountWithoutVAT' : 'vodneBezDPH',
    'WaterFeeAmountVAT' : 'vodneDPH',
    'SewerFeeAmountWithoutVAT' : 'stocneBezDPH',
    'SewerFeeAmountVAT' : 'stocneDPH',
    'AmountWithoutVAT' : 'celkemBezDPH',
    'AmountVAT' : 'celkemDPH'
}

water_VAT_column_mapping = {
    'WaterFixedFeeAmount' : 'pronajem',
    'WaterFixedAmountWithoutVAT' : 'pronajemBezDPH',
    'WaterFixedAmountVAT' :'pronajemDPH'
}


sewer_VAT_column_mapping = {
    'SewerFixedFeeAmount' : 'pronajem',
    'SewerFixedAmountWithoutVAT' : 'pronajemBezDPH',
    'SewerFixedAmountVAT' : 'pronajemDPH'
}

water_column_naming = {    
    'VS': 'VS',
    'Amount': 'Částka celkem',
    'WaterMeterStatusDate': 'Datum odečtu', 
    'WaterMeterPreviousStatus': 'Předhozí stav',
    'WaterMeterStatus' : 'Aktuální stav', 
    'Consumption' : 'Spotřeba', 
    'WaterRate' : 'Sazba pro vodné', 
    'WaterFeeAmount' : 'Vodné', 
    'WaterFixedFeeAmount' : 'Vodné pevná', 
    'SewerRate' : 'Sazba stočné', 
    'SewerFeeAmount' : 'Stočné', 
    'SewerFixedFeeAmount' : 'Pevná stočné',
    'WaterFeeAmountWithoutVAT' : 'Vodne Bez DPH',
    'WaterFeeAmountVAT' : 'Vodne DPH',
    'SewerFeeAmountWithoutVAT' : 'Stocne Bez DPH',
    'SewerFeeAmountVAT' : 'Stocne DPH',
    'AmountWithoutVAT' : 'Celkem Bez DPH',
    'AmountVAT' : 'Celkem DPH'
}

class WaterOrSewer(Enum):
    WATER = 1
    SEWER = 2

class aapolvod:
    def __init__(self, tisdb: TISDB):
        self.tisdb = tisdb        
        self.sewer_column_not_insert = ["vs", "sazba", "WaterOrSewer", "WaterFixedAmountWithoutVAT", "WaterFixedAmountVAT", "WaterFixedFeeAmount"]
        self.water_column_not_insert = ["vs", "sazbaStocne", "pevnaStocne", "WaterOrSewer", "SewerFixedAmountWithoutVAT", "SewerFixedAmountVAT", "SewerFixedFeeAmount"]        
        self.aapolfps = aapolfps(tisdb)

    def get_id_by_vs(self, vs: str):
        try:            
            query = "SELECT idaasezfps FROM aasezfps WHERE vs = ?"
            self.tisdb.cursor.execute(query, vs)
            result = self.tisdb.cursor.fetchall()
            
            if result:
                # Pokud bylo něco nalezeno, vrátíme první hodnotu z prvního řádku
                return result[0][0]
            else:
                return None
        except pyodbc.Error as e:
            print(f"Chyba při provádění dotazu: {str(e)}")
            return None    
    
    def insert_water(self, df : pd.DataFrame, datvzn: datetime, datspl : datetime, kodktp_spotreba: int, kodktp_pevna: int, waterOrSewer : WaterOrSewer, ideksezcph : int):    
        table_name = 'aapolvod'
        if waterOrSewer == WaterOrSewer.WATER:
            not_insert = self.water_column_not_insert
        else:
            not_insert = self.sewer_column_not_insert

        #columns_to_remove = [col for col in df.columns if col.startswith('--')]
        #df = df.drop(columns=columns_to_remove)


        idaasezktp_spotreba = self.aapolfps.get_idaasezktp(int(kodktp_spotreba))
        print(idaasezktp_spotreba)
        idaasezktp_pevna = self.aapolfps.get_idaasezktp(int(kodktp_pevna))
        print(idaasezktp_pevna)
        print(ideksezcph)
        dph = self.aapolfps.get_dph(int(ideksezcph))
        print(dph)
                
        for index, row in df.iterrows():
            vs = row["VS"]
            idaasezfps = self.get_id_by_vs(vs)
            if idaasezfps is not None:
                data_to_insert_osv = row.to_dict()

                if row["WaterFeeAmount"] > 0:
                    idaapolfps_vodne = self.aapolfps.insert_by_params(idaasezfps, vs, datvzn, datspl, row["WaterFeeAmount"], idaasezktp_spotreba, commit=True, ideksezcph=ideksezcph, cbdph = row["WaterFeeAmountWithoutVAT"], cdph = row["WaterFeeAmountVAT"], dph = dph)
                    data_to_insert_osv["idaapolfps"] = idaapolfps_vodne
                    

                if row["SewerFeeAmount"] > 0:
                    idaapolfps_stocne = self.aapolfps.insert_by_params(idaasezfps, vs, datvzn, datspl, row["SewerFeeAmount"], idaasezktp_spotreba, commit=True, ideksezcph=ideksezcph, cbdph = row["SewerFeeAmountWithoutVAT"], cdph = row["SewerFeeAmountVAT"], dph = dph)
                    data_to_insert_osv["idaapolfps_stocne"] = idaapolfps_stocne


                if row["WaterFixedFeeAmount"] > 0:
                    idaapolfps_pronajem = self.aapolfps.insert_by_params(idaasezfps, vs, datvzn, datspl, row["WaterFixedFeeAmount"], idaasezktp_pevna, commit=True, ideksezcph=ideksezcph, cbdph = row["WaterFixedAmountWithoutVAT"], cdph = row["WaterFixedAmountVAT"], dph = dph)
                    data_to_insert_osv["idaapolfps_pronajem"] = idaapolfps_pronajem
              

                if row["SewerFixedFeeAmount"] > 0:
                    idaapolfps_pronajem = self.aapolfps.insert_by_params(idaasezfps, vs, datvzn, datspl, row["SewerFixedFeeAmount"], idaasezktp_pevna, commit=True, ideksezcph=ideksezcph, cbdph = row["SewerFixedAmountWithoutVAT"], cdph = row["SewerFixedAmountVAT"], dph = dph)
                    data_to_insert_osv["idaapolfps_pronajem"] = idaapolfps_pronajem

                data_to_insert_osv["ideksezcph"] = ideksezcph
                data_to_insert_osv["idaasezfps"] = idaasezfps
                data_to_insert_osv["akt"] = 10                     

                if row["WaterOrSewer"] == 91:
                    water_column_mapping_joined = {**water_column_mapping, **water_VAT_column_mapping}
                else:
                    water_column_mapping_joined = {**water_column_mapping, **sewer_VAT_column_mapping}


                insert_query = self.tisdb.get_insert_query(table_name, data_to_insert_osv, mappings=water_column_mapping_joined, not_insert=not_insert)
                logger.debug(insert_query)
                self.tisdb.cursor_exec(insert_query, True)                        
            else:     
                logger.warning(f"Nelze najit VS {vs}")                       
                    
        
        #last_inserted_id : int = cursor.fetchone()[0]
        #print(last_inserted_id)