import requests
import re
import pandas as pd
import asyncio
import pyodide_http
from pyodide.http import pyfetch, open_url
from urllib.parse import quote
from pyscript import document, window
from requests.exceptions import HTTPError
from datetime import datetime, timezone, timedelta

pyodide_http.patch_all()


def pcs_df(deposits):

    url =  open_url('https://4412874.fs1.hubspotusercontent-na1.net/hubfs/4412874/DepositCalculator/deposits_client_db_prod_data_06272025.csv')
    df = pd.read_csv(url)
    
    deposit_types = list(pd.unique(df['Deposit Type']))
    deposit_code = list(pd.unique(df['Deposit Product']))

    deposit_dict = dict(zip(deposit_code, deposit_types))

    user_choice = deposits

    if user_choice in deposit_dict:
        selected_value = deposit_dict[user_choice]       
        pcs_df = df[df['Deposit Type'] == selected_value]
    
    return pcs_df

def printPDF(event):

    printPDF = document.getElementById("printPDF")
    printPDF.style.display = "none"

    title_section = document.getElementById("title_section")
    title_section.style.display = "block"

    window.print()

    printPDF = document.getElementById("printPDF")
    printPDF.style.display = "block"

    title_section = document.getElementById("title_section")
    title_section.style.display = "none"

def clear(event):

    document.getElementById('InstDepositName').value = ''
    document.getElementById('AssetYield').value = ''
    document.getElementById('AssetInc').value = ''
    document.getElementById('InstCapRatio').value = ''
    document.getElementById('AnnlGrowth').value = ''
    document.getElementById('Balance').value = ''
    document.getElementById('AnnlRate').value = ''
    document.getElementById('Term').value = ''
    

def clear_all(event):

    document.getElementById('InstDepositName').value = ''
    document.getElementById('AssetYield').value = ''
    document.getElementById('AssetInc').value = ''
    document.getElementById('InstCapRatio').value = ''
    document.getElementById('AnnlGrowth').value = ''
    document.getElementById('Balance').value = ''
    document.getElementById('AnnlRate').value = ''
    document.getElementById('Term').value = ''


    emailPrompt = document.querySelector("#emailPrompt")
    OpsCost = document.querySelector("#OpsCost")
    AssetRev = document.querySelector("#AssetRev")
    CalcPrompt = document.querySelector("#CalcPrompt")
    BaseROA = document.querySelector("#BaseROA")
    WAL = document.querySelector("#WAL")
    IntExp = document.querySelector("#IntExp")
    IntExpPer = document.querySelector("#IntExpPer")
    NII = document.querySelector("#NII")
    NIIPer = document.querySelector("#NIIPer")
    TotRev = document.querySelector("#TotRev")
    TotRevPer = document.querySelector("#TotRevPer")
    COF = document.querySelector("#COF")
    COFPer = document.querySelector("#COFPer")
    Trx = document.querySelector("#Trx")
    TrxPer = document.querySelector("#TrxPer")
    Support = document.querySelector("#Support")
    SupportPer = document.querySelector("#SupportPer")
    Mktg = document.querySelector("#Mktg")
    MktgPer = document.querySelector("#MktgPer")
    Ovhead = document.querySelector("#Ovhead")
    OvheadPer = document.querySelector("#OvheadPer")
    TotExp = document.querySelector("#TotExp")
    TotExpPer = document.querySelector("#TotExpPer")
    #NetInc = document.querySelector("#NetInc")
    #NetIncPer = document.querySelector("#NetIncPer")
    CapROA = document.querySelector("#CapROA")
    #ROADpRate = document.querySelector("#ROADpRate")
    NetOpsCost = document.querySelector("#NetOpsCost")
    XROA = document.querySelector("#XROA")
    MaxProdRate = document.querySelector("#MaxProdRate")
    finalTable = document.getElementById("finalTable")
    finalTable.style.display = "none"
    finalTable2 = document.getElementById("finalTable2")
    finalTable2.style.display = "none"
    finalTable3 = document.getElementById("finalTable3")
    finalTable3.style.display = "none"
    finalTable4 = document.getElementById("finalTable4")
    finalTable4.style.display = "none"
    printPDF = document.getElementById("printPDF")
    printPDF.style.display = "none"
    AssetRev2 = document.querySelector("#AssetRev2")
    TotExpPer2 = document.querySelector("#TotExpPer2")
    TotDepRev = document.querySelector("#TotDepRev")
    TotOpExp = document.getElementById("TotOpExp")
    NetInc2 = document.querySelector("#NetInc2")
    DepRateAdj = document.querySelector("#DepRateAdj")
    ALM = document.querySelector("#ALM")

    emailPrompt.innerText = " "
    OpsCost.innerText = " "
    AssetRev.innerText = " "
    CalcPrompt.innerText = " "
    BaseROA.innerText = " "
    WAL.innerText = " "
    IntExp.innerText = " "
    IntExpPer.innerText = " "
    NII.innerText = " "
    NIIPer.innerText = " "
    TotRev.innerText = " "
    TotRevPer.innerText = " "
    COF.innerText = " "
    COFPer.innerText = " "
    Trx.innerText = " "
    TrxPer.innerText = " "
    Support.innerText = " "
    SupportPer.innerText = " "
    Mktg.innerText = " "
    MktgPer.innerText = " "
    Ovhead.innerText = " "
    OvheadPer.innerText = " "
    TotExp.innerText = " "
    TotExpPer.innerText = " "
    #NetInc.innerText = " "
    #NetIncPer.innerText = " "
    CapROA.innerText = " "
    #ROADpRate.innerText = " "
    NetOpsCost.innerText = " "
    XROA.innerText = " "
    MaxProdRate.innerText = " "
    AssetRev2.innerText = " "
    TotExpPer2.innerText = " "
    TotDepRev.innerText = " "
    TotOpExp.innerText = " "
    NetInc2.innerText = " "
    DepRateAdj.innerText = " "
    ALM.innerText = " "


##### CALCULATIONS SECTION
    
def calcAssetRev(AssetYield, AssetInc, deposits):

    df = pcs_df(deposits)
    # OpsCost = df.loc[df['Sub-Name'] == 'Asset Operational Costs', 'Client Avg.'].values[0]
    AssetRev = AssetYield + AssetInc


    result = document.querySelector("#AssetRev")
    result.setAttribute("style", "color:black")
    result.innerText = f'{(AssetRev*100):.2f} %'

    return AssetRev

def calcBaseROA(InstCapRatio, AnnlGrowth):

    BaseROA = (InstCapRatio) * (AnnlGrowth)
    result = document.querySelector("#BaseROA")
    result.setAttribute("style", "color:black")
    result.innerText = f'{(BaseROA*100):.2f} %'

    return BaseROA

def calcWAL(Decay, Term, AnnlRate, deposits):

    if deposits == "Dp7" or deposits == "Dp8": ## CD's
        
        WAL = Term/12

    else:
        WAL = (((((1 - (1 - (1 - (1 - (Decay+ .0000001)) ** (1 / 12))) ** Term) / (1 - (1 - (Decay + .0000001)) ** (1 / 12)))) + 
                ((((1 / (1 + (AnnlRate / 1200)))) * (((1 / (1 + (AnnlRate/ 1200)))) ** Term - 
                                                    (1 - (1 - (1 - (Decay + .0000001)) ** (1 / 12))) ** Term) / 
                                                    ((1 - (1 - (1 - (Decay + .0000001)) ** (1 / 12))) - ((1 / (1 + (AnnlRate/ 1200)))))
                                                    ))) / ((1 - (((1 / (1 + (AnnlRate / 1200)))) ** Term))))/13
        

    result = document.querySelector("#WAL")
    result.setAttribute("style", "color:black")
    result.innerText = f"{WAL:.2f}"
    
    return WAL

def calcCOF(CalcdWAL, Balance, deposits, term):

    if deposits == "Dp6": ## MMkt

        CalcdWAL = 0

    df = treasury_data_df
    filtered = df[df['months'] < term]

    if not filtered.empty:
        selected_row = filtered.loc[filtered['months'].idxmax()]
    else:
        selected_row = None  # or handle the "no match" case

    if deposits == "Dp7" or deposits == "Dp8":

        COFPer = selected_row.loc['yield_percent']
        COFPer = COFPer/100
        COF = Balance * COFPer

    else:
        
        prime = 2.62
        COFPer = prime/100
        COF = Balance * COFPer


    result = document.querySelector("#COF")
    result.setAttribute("style", "color:black")
    result.innerText = f"$ {int(COF)}"
    per_result = document.querySelector("#COFPer")
    per_result.setAttribute("style", "color:black")
    per_result.innerText = f"{(COFPer*100):.2f} %"

    return COF

def calcNII(deposits, Balance):

    df = pcs_df(deposits)
    NII = df.loc[df['Sub-Name'] == 'Non-Interest Income', 'Client Avg.'].values[0]
    NIIPer = NII/Balance


    result = document.querySelector("#NII")
    result.setAttribute("style", "color:black")
    result.innerText = f"$ {int(NII)}"
    per_result = document.querySelector("#NIIPer")
    per_result.setAttribute("style", "color:black")
    per_result.innerText = f"{(NIIPer*100):.2f} %"

    return NII

def calcTotRev(CalcdCOF, CalcdNII, Balance):

    COF = CalcdCOF
    NII = CalcdNII

    TotRev = NII + COF
    TotRevPer = TotRev/Balance

    result = document.querySelector("#TotRev")
    result.setAttribute("style", "color:black")
    result.innerText = f"$ {int(TotRev)}"
    per_result = document.querySelector("#TotRevPer")
    per_result.setAttribute("style", "color:black")
    per_result.innerText = f"{(TotRevPer*100):.2f} %"

    return TotRev, TotRevPer

def calcIntExp(Balance, AnnlRate):

    IntExp = Balance * AnnlRate
    IntExpPer = IntExp/Balance

    result = document.querySelector("#IntExp")
    result.setAttribute("style", "color:black")
    result.innerText = f"$ {int(IntExp)}"
    per_result = document.querySelector("#IntExpPer")
    per_result.setAttribute("style", "color:black")
    per_result.innerText = f"{(IntExpPer*100):.2f} %"

    return IntExp, IntExpPer

def calcPCSdata(deposits, Balance, CalcdWAL):

    df = pcs_df(deposits)
    Trx = df.loc[df['Sub-Name'] == 'Transactions', 'Client Avg.'].values[0]
    Support = df.loc[df['Sub-Name'] == 'Support', 'Client Avg.'].values[0]
    Mktg = df.loc[df['Sub-Name'] == 'Marketing', 'Client Avg.'].values[0]
    Ovhead = df.loc[df['Sub-Name'] == 'Overhead', 'Client Avg.'].values[0]

    TrxResult = document.querySelector("#Trx")
    TrxResult.setAttribute("style", "color:black")
    TrxResult.innerText = f"$ {int(Trx)}"
    SupportResult = document.querySelector("#Support")
    SupportResult.setAttribute("style", "color:black")
    SupportResult.innerText = f"$ {int(Support)}"
    MktgResult = document.querySelector("#Mktg")
    MktgResult.setAttribute("style", "color:black")
    MktgResult.innerText = f"$ {int(Mktg)}"
    OvheadResult = document.querySelector("#Ovhead")
    OvheadResult.setAttribute("style", "color:black")
    OvheadResult.innerText = f"$ {int(Ovhead)}"

    TrxPer = Trx/Balance
    SupportPer = Support/Balance
    MktgPer = Mktg/Balance
    OvheadPer = Ovhead/Balance

    Trx_per = document.querySelector("#TrxPer")
    Trx_per.setAttribute("style", "color:black")
    Trx_per.innerText = f"{(TrxPer*100):.2f} %"
    Support_per = document.querySelector("#SupportPer")
    Support_per.setAttribute("style", "color:black")
    Support_per.innerText = f"{(SupportPer*100):.2f} %"
    Mktg_per = document.querySelector("#MktgPer")
    Mktg_per.setAttribute("style", "color:black")
    Mktg_per.innerText = f"{(MktgPer*100):.2f} %"
    Ovhead_per = document.querySelector("#OvheadPer")
    Ovhead_per.setAttribute("style", "color:black")
    Ovhead_per.innerText = f"{(OvheadPer*100):.2f} %"

    return Trx, Support, Mktg, Ovhead

def calcTotExp(Balance, Trx, Support, Mktg, Ovhead, CalcdIntExp):

    IntExp, IntExpPer = CalcdIntExp
    TotExp = Support + Mktg + Trx + Ovhead + IntExp
    TotExpPer = TotExp / Balance

    result = document.querySelector("#TotExp")
    result.setAttribute("style", "color:black")
    result.innerText = f"$ {int(TotExp)}"  

    per_result = document.querySelector("#TotExpPer")
    per_result.setAttribute("style", "color:black")
    per_result.innerText = f"{(TotExpPer*100):.2f} %"

    return TotExp, TotExpPer

#def calcProdCost(NII, Trx, Support, Mktg, Ovhead, Balance):
    
    ProdCost = NII + Trx + Support + Mktg + Ovhead

    ProdCostPer = (NII/Balance) + (Trx/Balance) + (Support/Balance) + (Mktg/Balance) + (Ovhead/Balance)

    result = document.querySelector("#ProdCost")
    result.setAttribute("style", "color:black")
    result.innerText = f"$ {'{:,.0f}'.format(round(ProdCost, 0))}"  

    per_result = document.querySelector("#ProdCostPer")
    per_result.setAttribute("style", "color:black")
    per_result.innerText = f"{(ProdCostPer*100):.2f} %"
    
    return ProdCostPer

def calcNetInc(CalcdTotRev, CalcdTotExp, CalcdIntExp):
    
    IntExp, IntExpPer = CalcdIntExp
    TotRev, TotRevPer = CalcdTotRev
    TotExp, TotExpPer = CalcdTotExp

    #NetInc = TotRev - TotExp
    #NetIncPer = TotRevPer - TotExpPer

    #result = document.querySelector("#NetInc")
    #result.setAttribute("style", "color:black")
    #result.innerText = f"$ {int(NetInc)}"  

    #per_result = document.querySelector("#NetIncPer")
    #per_result.setAttribute("style", "color:black")
    #per_result.innerText = f"{(NetIncPer*100):.2f} %"

    #### DP Rate

    TotDepRevPer = TotRevPer

    Dp_per_result = document.querySelector("#TotDepRev")
    Dp_per_result.setAttribute("style", "color:#5F5F5F")
    Dp_per_result.innerText = f"{(TotDepRevPer*100):.2f} %"

    NetInc2 = TotDepRevPer - (TotExpPer - IntExpPer)

    NetInc_result = document.querySelector("#NetInc2")
    NetInc_result.setAttribute("style", "color:#4472C4; font-size: 25px")
    NetInc_result.innerText = f"{(NetInc2*100):.2f} %"

    #####
    
    return NetInc2, TotDepRevPer

def calcDPRate(CalcdBaseROA, CalcdNetInc, AnnlRate):

    CapROA = CalcdBaseROA

    CapROA_result = document.querySelector("#CapROA")
    CapROA_result.setAttribute("style", "color:#4472C4; font-size: 25px")
    CapROA_result.innerText = f"{(CapROA*100):.2f} %"

    NetInc, TotDepRev = CalcdNetInc

    DepRateAdj = NetInc - CapROA #### discount_rate for Present Value

    DepRateAdj_result = document.querySelector("#DepRateAdj")
    DepRateAdj_result.setAttribute("style", "color:#4472C4; font-size: 25px")
    DepRateAdj_result.innerText = f"{(DepRateAdj*100):.2f} %"

    #ROADpRate = AnnlRate + DepRateAdj

    #ROADpRate_result = document.querySelector("#ROADpRate")
    #ROADpRate_result.setAttribute("style", "color:#4472C4; font-size: 25px")
    #ROADpRate_result.innerText = f"{(ROADpRate*100):.2f} %"

    return DepRateAdj

def calcProdRate(AssetRev, CalcdTotExp, CalcdIntExp, BaseROA):

    # Updated 4/24/2024 from ABC PSC Cost Summary
    OpsCost = 3.12/100

    IntExp, IntExpPer = CalcdIntExp
    TotExp, TotExpPer = CalcdTotExp
    TotExpExclIntExp = TotExpPer - IntExpPer
    NetOpsCost = AssetRev - OpsCost - TotExpExclIntExp
    MaxProdRate = NetOpsCost - BaseROA


    #### DP Rate
    TotOpExpPer = TotExpExclIntExp

    per_TotOpExp = document.querySelector("#TotOpExp")
    per_TotOpExp.setAttribute("style", "color:#5F5F5F")
    per_TotOpExp.innerText = f"{(TotOpExpPer*100):.2f} %"
    #####

    per_NetOpsCost = document.querySelector("#NetOpsCost")
    per_NetOpsCost.setAttribute("style", "color:#3C7D22; font-size: 25px")
    per_NetOpsCost.innerText = f"{(NetOpsCost*100):.2f} %"

    ops_result = document.querySelector("#OpsCost")
    ops_result.setAttribute("style", "color:#5F5F5F")
    ops_result.innerText = f'{(OpsCost*100):.2f} %'

    result = document.querySelector("#AssetRev2")
    result.setAttribute("style", "color:#5F5F5F")
    result.innerText = f'{(AssetRev*100):.2f} %'

    per_result = document.querySelector("#TotExpPer2")
    per_result.setAttribute("style", "color:#5F5F5F")
    per_result.innerText = f"{(TotExpExclIntExp*100):.2f} %"

    per_XROA = document.querySelector("#XROA")
    per_XROA.setAttribute("style", "color:#3C7D22; font-size: 25px")
    per_XROA.innerText = f"{(BaseROA*100):.2f} %"

    per_MaxProdRate = document.querySelector("#MaxProdRate")
    per_MaxProdRate.setAttribute("style", "color:#3C7D22; font-size: 25px")
    per_MaxProdRate.innerText = f"{(MaxProdRate*100):.2f} %"

    return MaxProdRate


def calcALM(calcdDPRate, calcdProdRate):

    ALM = calcdProdRate - calcdDPRate
    ALM = abs(ALM)

    per_ALM = document.querySelector("#ALM")
    per_ALM.setAttribute("style", "color:black; font-size: 25px")
    per_ALM.innerText = f"{(ALM*100):.2f} %"

    return ALM

def calcPresentValue(rate, term_months, CPR, DPRate, MaxProdRate, balance, deposits):

    ## AnnlRate, Term, Decay, DPRate, Balance, deposits
    rate = round(rate, 4)
    discount_rate = round(DPRate, 4) ## CD's
    product_rate = round(MaxProdRate, 4) ## NMD's

    if deposits in ("Dp7", "Dp8"):
        """
        Calculate the discounted gain or loss on a bank CD with monthly interest payments.
        
        Args:
            principal (float): The initial investment in the CD.
            annual_interest_rate (float): The annual interest rate (as a decimal).
            term_months (int): The duration of the CD in months.
            discount_rate (float): The annual discount rate (as a decimal).
        
        Returns:
            dict: A dictionary with the total discounted interest, discounted final balance, and gain or loss.
        """

        # Convert annual rates to monthly
        monthly_interest_rate = rate / 12
        monthly_discount_rate = discount_rate / 12

        # Generate cash flows
        monthly_interest = balance * monthly_interest_rate
        cash_flows = [monthly_interest] * term_months  # Monthly interest payments
        cash_flows[-1] += balance  # Add principal repayment in the last month

        # Discount cash flows
        discounted_cash_flows = [
            cf / ((1 + monthly_discount_rate) ** i) for i, cf in enumerate(cash_flows, start=1)
        ]

        # Calculate discounted totals
        total_discounted_cash_flows = sum(discounted_cash_flows)
        gain_loss = total_discounted_cash_flows - balance

        ########  

        PresentValue = document.querySelector("#PresentValue")
        PresentValue.setAttribute("style", "color:#000000; font-size: 22px")
        PresentValue.innerText = f"$ {(total_discounted_cash_flows):,.0f}"

        if round(gain_loss, 0) == 0:
            GainLoss = document.querySelector("#GainLoss")
            GainLoss.setAttribute("style", "color:#000000; font-size: 22px")
            GainLoss.innerHTML = f"$ {abs(gain_loss):,.0f}</span>" 

        elif discount_rate > rate:
            GainLoss = document.querySelector("#GainLoss")
            GainLoss.setAttribute("style", "color:#000000; font-size: 22px")
            GainLoss.innerHTML = f"<span style='color: green;'>$ {abs(gain_loss):,.0f}</span>"
           
        else:
            GainLoss = document.querySelector("#GainLoss")
            GainLoss.setAttribute("style", "color:#000000; font-size: 22px")
            GainLoss.innerHTML = f"<span style='color: red;'>$ ({abs(gain_loss):,.0f})</span>"



    elif deposits not in ("Dp7", "Dp8"):
        """
        Calculate the value of an Interest-Only (IO) strip.

        Parameters:
        - io_rate: The interest rate on the IO strip (as a decimal, e.g., 0.02 for 2%)
        - cpr: Constant Prepayment Rate (as a decimal, e.g., 0.10 for 10%)
        - periods: Number of periods (months)
        - principal_balance: Initial principal balance

        Returns:
        - The value of the IO strip.
        """
        """
        pv_io = 0.0
        smm = 1 - (1 - CPR) ** (1 / 12)
        
        for t in range(1, term_months + 1):
            # Calculate the remaining principal balance after prepayment
            remaining_principal = balance * (1 - smm) ** t
            
            # Interest payment for the period
            interest_payment = remaining_principal * rate / 12
            
            # Discount the interest payment back to present value
            pv_io += interest_payment / (1 + product_rate / 12) ** t

        io_price = (balance + pv_io) / balance *100
        """
        rate = product_rate - rate

        pv_io = 0.0
        smm = 1 - (1 - CPR) ** (1 / 12)
        
        for t in range(1, term_months + 1):
            # Calculate the remaining principal balance after prepayment
            remaining_principal = balance * (1 - smm) ** t
            
            # Interest payment for the period
            interest_payment = remaining_principal * rate / 12
            
            # Discount the interest payment back to present value
            pv_io += interest_payment / (1 + product_rate / 12) ** t

            io_price = (balance + pv_io) / balance *100
        

        PresentValue = document.querySelector("#PresentValue")
        PresentValue.setAttribute("style", "color:#000000; font-size: 22px")
        PresentValue.innerText = f"{io_price:.2f} %"

        if round(pv_io, 0) == 0:
            GainLoss = document.querySelector("#GainLoss")
            GainLoss.setAttribute("style", "color:#000000; font-size: 22px")
            GainLoss.innerHTML = f"$ {abs(pv_io):,.0f}</span>"

        elif rate > 0:
            GainLoss = document.querySelector("#GainLoss")
            GainLoss.setAttribute("style", "color:#000000; font-size: 22px")
            GainLoss.innerHTML = f"<span style='color: green;'>$ {abs(pv_io):,.0f}</span>"

        else:
            GainLoss = document.querySelector("#GainLoss")
            GainLoss.setAttribute("style", "color:#000000; font-size: 22px")
            GainLoss.innerHTML = f"<span style='color: red;'>$ ({abs(pv_io):,.0f})</span>"


##### CALCULATIONS SECTION


######################### TREASURY DATA SECTION


async def invoke_treasury_data_lambda():

    treasury_data_url = 'https://inczvecoy2xv76eolih6xp4s340ribbg.lambda-url.ap-southeast-2.on.aws/'

    while True:
        try:
            # Make a GET request to the Lambda function URL
            response = requests.get(treasury_data_url)
            response.raise_for_status()  # Raises an HTTPError for bad responses (4xx and 5xx)
            
            if response.status_code == 200:
            # Try to parse JSON
                try:
                    response_json = response.json()
                    print('Lambda Response Status:', response.status_code)
                    data = response_json
                    break
                
                except ValueError:
                    print('Lambda Response Status:', response.status_code)
                    data = response.text
                    break

            await asyncio.sleep(5)
        
        except HTTPError:
            pass

    return data


def setDf(data):
    
    df = pd.DataFrame(data)
    df = df.rename(columns=lambda name: name.replace(' Month', 'm').replace(' Yr', 'y').replace(' Mo', 'm').lower())
    df['date'] = pd.to_datetime(df['date'])

    today_utc = (datetime.now(timezone.utc) - timedelta(days=2)).date()
    filtered_df = df[df['date'].dt.date == today_utc]

    # Melt to long format including the 7y point
    df_rates = filtered_df.melt(
        id_vars='date',
        value_vars=['1m', '3m', '6m', '1y', '2y', '5y', '7y', '10y', '30y'],
        var_name='maturity',
        value_name='yield_percent'
    )

    # Add WAL mapping
    wal_mapping = {
        '1m': 0.08,
        '3m': 0.25,
        '6m': 0.5,
        '1y': 1,
        '2y': 2,
        '5y': 5,
        '7y': 7,
        '10y': 10,
        '30y': 30
    }
    df_rates['WAL'] = df_rates['maturity'].map(wal_mapping)

    # Define and apply order of maturities
    maturity_order = ['1m', '3m', '6m', '1y', '2y', '5y', '7y', '10y', '30y']
    df_rates['maturity'] = pd.Categorical(df_rates['maturity'], categories=maturity_order, ordered=True)
    df_rates = df_rates.sort_values('maturity')
    df_rates['months'] = df_rates['maturity'].apply(
        lambda x: int(x[:-1]) if x.endswith('m') else int(x[:-1]) * 12
    ).astype(int)


    df_rates['yield_percent'] = df_rates['yield_percent'].astype(float)

    return df_rates



######################### TREASURY DATA SECTION


def email_submit(event):

    email_address = document.getElementById("email").value
    pattern = r'^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$'
    emailPrompt = document.querySelector("#emailPrompt")
    CalcPrompt = document.querySelector("#CalcPrompt")

    if re.match(pattern, email_address) is not None:
        email = True
        document.getElementById('calculate').removeAttribute("disabled")
        displayText = " "
        emailPrompt.innerText = displayText
        CalcPrompt.innerText = displayText

        event.target.style.backgroundColor = '#009900'

    else:
        displayText = "Please enter a valid email address"
        emailPrompt.setAttribute("style", "color:red")
        emailPrompt.innerText = displayText
        email = False
        document.getElementById('calculate').setAttribute("disabled", True)

    global confirm
    confirm = True

    return email

def emailCheck(email):
    
    pattern = r'^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$'
    if re.match(pattern, email) is not None:

        emailPrompt = document.querySelector("#emailPrompt")
        emailPrompt.innerText = " "
        email = True

    else:
        emailPrompt = document.querySelector("#emailPrompt")
        displayText = "Please enter a valid email address"
        emailPrompt.setAttribute("style", "color:red")
        emailPrompt.innerText = displayText

        CalcPrompt = document.querySelector("#CalcPrompt")
        displayText = "Kindly confirm that all fields have been filled in correctly or are valid. (email)"
        CalcPrompt.setAttribute("style", "color:red")
        CalcPrompt.innerText = displayText

        email = False

    return email

def calculate(event):

    CalcPrompt = document.querySelector("#CalcPrompt")
    CalcPrompt.innerText = " "

    email = document.getElementById("email").value
    
    if emailCheck(email) == True and confirm == True:
        try:
            InstDepositName = document.getElementById('InstDepositName').value
            deposits = document.querySelector("#deposits").value
            AssetYield = float(document.querySelector("#AssetYield").value)/100
            AssetInc = float(document.querySelector("#AssetInc").value)/100
            InstCapRatio = float(document.querySelector("#InstCapRatio").value)/100
            AnnlGrowth = float(document.querySelector("#AnnlGrowth").value)/100
            Balance = float(document.querySelector("#Balance").value)
            AnnlRate = float(document.querySelector("#AnnlRate").value)/100

            if AnnlRate == 0:
                AnnlRate = 0.00000001

            Term = int(document.querySelector("#Term").value)

            if Term == 0:
                Term = 1

            Decay = 0

            AssetRev = calcAssetRev(AssetYield, AssetInc, deposits)
            BaseROA = calcBaseROA(InstCapRatio, AnnlGrowth)
            WAL = calcWAL(Decay, Term, AnnlRate, deposits)
            NII = calcNII(deposits, Balance)
            COF = calcCOF(WAL, Balance, deposits, Term)
            TotRev = calcTotRev(COF, NII, Balance)
            IntExp = calcIntExp(Balance, AnnlRate)
            Trx, Support, Mktg, Ovhead = calcPCSdata(deposits, Balance, WAL)
            TotExp = calcTotExp(Balance, Trx, Support, Mktg, Ovhead, IntExp)
            # ProdCost = calcProdCost(NII, Trx, Support, Mktg, Ovhead, Balance)
            NetInc = calcNetInc(TotRev, TotExp, IntExp)
            DPRate = calcDPRate(BaseROA, NetInc, AnnlRate)
            MaxProdRate = calcProdRate(AssetRev, TotExp, IntExp, BaseROA)
            ALM = calcALM(DPRate, MaxProdRate)
            PresentValue = calcPresentValue(AnnlRate, Term, Decay, DPRate, MaxProdRate,Balance, deposits)

            finalTable = document.getElementById("finalTable")
            finalTable.style.display = "table"
            finalTable2 = document.getElementById("finalTable2")
            finalTable2.style.display = "table"
            finalTable3 = document.getElementById("finalTable3")
            finalTable3.style.display = "table"
            finalTable4 = document.getElementById("finalTable4")
            finalTable4.style.display = "table"
            printPDF = document.getElementById("printPDF")
            printPDF.style.display = "block"

        except ValueError or AttributeError:
            CalcPrompt = document.querySelector("#CalcPrompt")
            displayText = "Kindly confirm that all fields have been filled in correctly or are valid."
            CalcPrompt.setAttribute("style", "color:red")
            CalcPrompt.innerText = displayText


    elif confirm == False:

        CalcPrompt = document.querySelector("#CalcPrompt")
        displayText = "Kindly confirm above that you agree with the following terms and conditions."
        CalcPrompt.setAttribute("style", "color:red")
        CalcPrompt.innerText = displayText

    
async def load_url():

    ## Treasury Data Section
    treasury_data = await invoke_treasury_data_lambda()

    global treasury_data_df
    treasury_data_df = setDf(treasury_data)


async def main():
    emailPrompt = document.querySelector("#emailPrompt")
    CalcPrompt = document.querySelector("#CalcPrompt")

    # Start the backend loading process asynchronously
    loading_url_task = asyncio.create_task(load_url())

    # Allow UI to be interactive while loading files
    while not loading_url_task.done():
        await asyncio.sleep(5)  # Yield control to the event loop to keep UI responsive

    # Once loading is complete, update the UI
    CalcPrompt.innerText = " "
    emailPrompt.innerText = " "
    document.getElementById('calculate').removeAttribute("disabled")  # Enable the button

# Initial values
treasury_data_df = []
confirm = False

# Function to start the main coroutine after the window has loaded
def on_load():
    # Display initial loading prompt
    emailPrompt = document.querySelector("#emailPrompt")
    displayText = "Gathering real-time market data, this takes about 10-20 seconds."
    emailPrompt.setAttribute("style", "color:red; font-weight:bold")
    emailPrompt.innerText = displayText

    CalcPrompt = document.querySelector("#CalcPrompt")
    CalcPrompt.setAttribute("style", "color:gray")
    CalcPrompt.innerText = "Files are loading, Please wait"

    asyncio.create_task(main())  # Ensure main is run as a coroutine

# Trigger the process when the window has fully loaded
window.addEventListener('load', on_load())