import warnings
import time
import utils
import db  
from db_utils import get_db_connection 
import utils
import streamlit as st
import pandas as pd
from db_utils import get_db_connection
from utils import is_username_unique
from datetime import datetime, date
import test
from test import calculate_interest_yield
from test import calc_interest
from modals import call_auction_modal
from modals import bank_offer_modal
from mailer import send_auction_notification
from db import execute_bank_offer

def login_page():
    st.subheader("Login to your Account")
    username = st.text_input("Username")
    password = st.text_input("Password", type='password')
    
    if st.button("Login"):
        conn = get_db_connection()
        cursor = conn.cursor()
        # Querying tbUsers with your new column names
        cursor.execute("SELECT UserID, Type, Name FROM tbUsers WHERE UserName=? AND Password=?", (username, password))
        user = cursor.fetchone()
        conn.close()

        if user:
            st.session_state.logged_in = True
            st.session_state.user_id = user[0]   # UserID
            st.session_state.user_type = user[1] # Type
            st.session_state.user_name = user[2] # Name
            st.rerun()
        else:
            st.error("Invalid Username or Password")

def signup_client():
    st.subheader("Register Client Account")
    
    col1, col2 = st.columns(2)
    with col1:
        u = st.text_input("Username", key="cl_u")
        p = st.text_input("Password", type='password', key="cl_p")
        n = st.text_input("Client Name / Company Name", key="cl_n")
        nn = st.text_input("Nick Name / Alias", key="cl_nn")

    with col2:
        e = st.text_input("Email", key="cl_e")
        tel = st.text_input("Telephone", key="cl_tel")
        acc = st.text_input("Account Number (IBAN)", key="cl_acc")
        country = st.text_input("Country", value="SK", key="cl_country")

    ad1 = st.text_input("Address Line 1 (Street)", key="cl_ad1")
    ad2 = st.text_input("Address Line 2 (City/ZIP)", key="cl_ad2")

    if st.button("Register Client Account", width='stretch'):
        if not u:
            st.error("Please enter a username.")
        elif not utils.is_username_unique(u):
            st.error(f"⚠️ Username '{u}' is already taken. Please choose another.")
        else:
            conn = get_db_connection()
            cursor = conn.cursor()
            try:
                sql = """
                INSERT INTO tbUsers (UserName, [Password], [Name], NickName, Ad1, Ad2, Country, Email, Tel, AccountNumber, [Type]) 
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'Client')
                """
                cursor.execute(sql, (u, p, n, nn, ad1, ad2, country, e, tel, acc))
                conn.commit()
                st.success("Account created! Please login.")
                st.session_state.auth_mode = "Login"
                st.rerun()
            except Exception as e:
                st.error(f"Database Error: {e}")
            finally:
                conn.close()

def signup_bank():
    st.subheader("Register Bank Account")
    
    col1, col2 = st.columns(2)
    with col1:
        u = st.text_input("Username", key="bk_u")
        p = st.text_input("Password", type='password', key="bk_p")
        n = st.text_input("Client Name / Bank Name", key="bk_n")
        
    with col2:
        e = st.text_input("Email", key="bk_e")
        tel = st.text_input("Telephone", key="bk_tel")
        country = st.text_input("Country", value="SK", key="bk_country")
        #tax = st.number_input("Bank Tax Rate (%)", value=19.0, step=0.1, key="bk_tax")

    ad1 = st.text_input("Address Line 1 (Street)", key="bk_ad1")
    ad2 = st.text_input("Address Line 2 (City/ZIP)", key="bk_ad2")

    if st.button("Register Bank Account", width='stretch'):
        if not u:
            st.error("Please enter a username.")
        elif not test.is_username_unique(u):
            st.error(f"⚠️ Username '{u}' is already taken. Please choose another.")
        else:
            conn = get_db_connection()
            cursor = conn.cursor()
            try:
                tax = 0            
                sql = """
                INSERT INTO tbUsers (UserName, [Password], [Name], Ad1, Ad2, Country, Email, Tel, [Type], BWithTax) 
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'Bank', ?)
                """
                cursor.execute(sql, (u, p, n, ad1, ad2, country, e, tel, tax))
                conn.commit()
                st.success("Bank Registered! Please login.")
                st.session_state.auth_mode = "Login"
                st.rerun()
            except Exception as e:
                st.error(f"Error: {e}")
            finally:
                conn.close()
                
def admin_page():
    st.header("System Administration")
    tab1, tab2 = st.tabs(["👥 All Users", "📊 All Auctions"])
    conn = get_db_connection()

    with tab1:
        st.subheader("Client & Bank Registry")
        query_users = "SELECT UserID, UserName, [Name], [Type], Email, Country, BWithTax FROM tbUsers"
        try:
            df_users = pd.read_sql(query_users, conn)
            
            # Note: Avoid shortening names to 10 chars if you plan to save them back, 
            # otherwise you will overwrite the full names in your DB with truncated versions!
            
            edited_df_users = st.data_editor(
                df_users, 
                key="editor_users", 
                hide_index=True, 
                disabled=["UserID"] # Keep Primary Key read-only
            )

            if st.button("Save User Changes"):
                # Logic to update SQL (see below)
                st.success("User updates saved!")
                
        except Exception as e:
            st.error(f"Error: {e}")

    with tab2:
        st.subheader("Full Auction History")
        query_auctions = "SELECT * FROM tbAuctions" # Simpler for editing
        try:
            df_auctions = pd.read_sql(query_auctions, conn)
            
            edited_df_auctions = st.data_editor(
                df_auctions,
                key="editor_auctions",
                hide_index=True,
                column_config={
                    "Amount": st.column_config.NumberColumn(format="%,.2f"),
                    "Rate": st.column_config.NumberColumn(format="%.3f%%")
                }
            )
            
            if st.button("Save Auction Changes"):
                # Logic to update SQL
                st.info("Updates would be processed here.")
                
        except Exception as e:
            st.error(f"Error: {e}")
            
    conn.close()

def about_page():
    st.write("### About this Platform")
    st.write("This is a financial auction system designed for secure bidding between banks and clients.")

def clAuctions(client_name):
    st.subheader("Deposits Close to Maturity")
    
    conn = get_db_connection()
    # Updated Query: Removed GrossYield, Added NetYield
    query = """
    SELECT 
        a.AuctionID, u2.Name as BankName, a.Amount, a.Rate, 
        a.MaturityDate, a.Currency, a.IDBank, a.IDClient, u2.Email as BankEmail, a.Basis, a.NetYield, a.BWithTax
    FROM tbAuctions AS a
    INNER JOIN tbUsers AS u1 ON a.IDClient = u1.UserID
    INNER JOIN tbUsers AS u2 ON a.IDBank = u2.UserID
    WHERE u1.Name = ? 
      AND a.Status = 'Active'
    ORDER BY a.MaturityDate ASC
    """
    df = pd.read_sql(query, conn, params=[client_name])
    conn.close()

    if df.empty:
        st.info("No active deposits found.")
        return

    # Styling for Maturity
    def highlight_maturity(row):
        days_left = (pd.to_datetime(row['MaturityDate']).date() - date.today()).days
        if days_left <= 2: return ['background-color: #ffcccc; color: black'] * len(row)
        if days_left <= 5: return ['background-color: #fff4cc; color: black'] * len(row)
        return [''] * len(row)

    # Updated Column Configuration: Showing Net Yield
    event = st.dataframe(
        df.style.apply(highlight_maturity, axis=1),
        column_config={
            "AuctionID": None, 
            "IDBank": None, 
            "IDClient": None, 
            "BankEmail": None, 
            "Basis": None,
            "Amount": st.column_config.NumberColumn("Amount", format="%,.2f"),
            "NetYield": st.column_config.NumberColumn("Net Yield", format="%,.2f")
        },
        hide_index=True, 
        on_select="rerun", 
        selection_mode="single-row", 
        key="cl_auc_table"
    )

    if event.selection.rows:
        # Get the row and convert to dict immediately
        row_data = df.iloc[event.selection.rows[0]].to_dict() 
    
        # Calculate rollover amount: Principal + Net Yield
        principal = float(row_data.get('Amount') or 0)
        net_yield = float(row_data.get('NetYield') or 0)
        row_data['Amount'] = principal + net_yield      
    
        # Pass the dictionary to the modal
        call_auction_modal(row_data, db.execute_call_insert)
        
def clAuctionsConfirm(client_id):
    st.subheader("Deposits to Confirm")
    conn = get_db_connection()
    query = """
    SELECT 
        a.AuctionID, uB.Name as BankName, a.Amount, a.Currency, 
        a.TradeDate, a.ValueDate, a.Tenor, a.MaturityDate, 
        a.OfferRate, a.OfferTime, a.Basis, a.GrossYield, a.WithTax, a.NetYield,
        uB.Email as BankEmail
    FROM tbAuctions AS a
    INNER JOIN tbUsers AS uB ON a.IDBank = uB.UserID
    WHERE a.IDClient = ? AND a.Status = 'Offer'
    """
    df = pd.read_sql(query, conn, params=[client_id])
    conn.close()

    if df.empty:
        st.info("No pending bank offers to confirm.")
        return

    event = st.dataframe(
        df,
        # This list defines the exact order from left to right
        column_order=[
            "BankName", 
            "Amount", 
            "Currency", 
            "OfferRate", 
            "NetYield", 
            "MaturityDate"
            "ValueDate", 
            "TradeDate", 
            "Tenor", 
        ],
        column_config={
            "BankName": st.column_config.TextColumn("Financial Institution"),
            "OfferRate": st.column_config.NumberColumn("Rate", format="%.2f%%"),
            "Amount": st.column_config.NumberColumn("Amount", format="%,.2f"),
            "NetYield": st.column_config.NumberColumn("Net Interest", format="%,.2f"),
            "MaturityDate": st.column_config.DateColumn("Maturity", format="DD-MMM-YYYY"),
            "TradeDate": st.column_config.DateColumn("Trade Date", format="DD-MMM-YYYY"),
            "ValueDate": st.column_config.DateColumn("Value Date", format="DD-MMM-YYYY"),
            "Tenor": st.column_config.TextColumn("Tenor"),
            "Currency": st.column_config.TextColumn("Currency"),
        },
        hide_index=True,
        on_select="rerun",
        selection_mode="single-row",
        key="client_confirm_table"
    )

    if event.selection.rows:
        row_data = df.iloc[event.selection.rows[0]].to_dict()
        # Ensure we pass the handler function here
        from modals import client_decision_modal
        client_decision_modal(row_data, execute_client_decision)

def execute_client_decision(row_data, new_status):
    """
    Handles the Client's decision to either Accept (Active) or Reject (Rejected) an offer.
    """
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # 1. Setup Timestamps and Rates
        now_str = datetime.now().strftime('%m/%d/%y %H:%M')
        offer_rate = float(row_data.get('OfferRate', 0))
        final_rate = offer_rate if new_status == "Active" else 0
        
        # 2. Update Database
        sql = """
        UPDATE tbAuctions 
        SET [Status] = ?, [ConfirmTime] = ?, [ConfirmRate] = ?, [Rate] = ?
        WHERE [AuctionID] = ?
        """
        
        cursor.execute(sql, (
            new_status, 
            now_str, 
            final_rate, 
            final_rate if new_status == "Active" else offer_rate,
            int(row_data['AuctionID'])
        ))
        
        conn.commit()
        conn.close()

        # 3. Email Notification Section
        try:
            from mailer import send_auction_notification
            
            # Prepare rich data payload for the bank
            mail_payload = {
                "BankName": row_data.get('BankName'),
                "ClientName": row_data.get('ClientName', 'The Client'),
                "Amount": row_data.get('Amount'),
                "Currency": row_data.get('Currency'),
                "Tenor": row_data.get('Tenor', 'N/A'),
                "CallRate": offer_rate,
                "TradeDate": row_data.get('TradeDate', 'N/A'),
                "Maturity": row_data.get('MaturityDate', 'N/A'),
                "RecipientEmail": row_data.get('BankEmail'),
                "ConfirmTime": now_str
            }
            
            if mail_payload["RecipientEmail"]:
                send_auction_notification(new_status, mail_payload)
                
        except Exception as mail_err:
            st.warning(f"Database updated, but email notification failed: {mail_err}")

        return True

    except Exception as e:
        if 'conn' in locals() and conn: conn.close()
        st.error(f"Error processing client decision: {e}")
        return False
    
def clAnalytics(client_name):
    import matplotlib.pyplot as plt
    import numpy as np
    from matplotlib.ticker import StrMethodFormatter

    st.subheader("Client Deposit Analytics")

    conn = get_db_connection()

    query = """
    SELECT
        a.AuctionID,
        uB.Name as BankName,
        a.Amount,
        a.GrossYield,
        a.WithTax,
        a.NetYield,
        a.MaturityDate,
        a.Currency,
        a.Basis,
        a.Status
    FROM (tbAuctions AS a
    LEFT JOIN tbUsers AS uB ON a.IDBank = uB.UserID)
    LEFT JOIN tbUsers AS uC ON a.IDClient = uC.UserID
    WHERE uC.Name = ?
    ORDER BY a.MaturityDate ASC
    """

    try:
        df = pd.read_sql(query, conn, params=[client_name])
    finally:
        conn.close()

    if df.empty:
        st.info("No deposits found.")
        return

    df["Amount"] = pd.to_numeric(df["Amount"], errors='coerce').fillna(0)
    df["NetYield"] = pd.to_numeric(df["NetYield"], errors='coerce').fillna(0)
    df["MaturityDate"] = pd.to_datetime(df["MaturityDate"])
    df["Cashflow"] = df["Amount"] + df["NetYield"]

    # -------------------------
    # TABLE
    # -------------------------

    df_display = df.copy()
    df_display["MaturityDate"] = df_display["MaturityDate"].dt.date

    st.dataframe(
        df_display,
        column_config={
            "AuctionID": None,
            "BankName": "Financial Institution",
            "Amount": st.column_config.NumberColumn("Amount", format="%,.2f"),
            "GrossYield": st.column_config.NumberColumn("Gross Yield", format="%,.2f"),
            "WithTax": st.column_config.NumberColumn("Tax", format="%,.2f"),
            "NetYield": st.column_config.NumberColumn("Net Yield", format="%,.2f"),
            "Cashflow": st.column_config.NumberColumn("Cashflow", format="%,.2f"),
            "MaturityDate": st.column_config.DateColumn("Maturity", format="DD-MMM-YYYY"),
            "Currency": "Currency",
            "Basis": "Basis",
            "Status": "Status"
        },
        hide_index=True,
        width='stretch'
    )

    st.divider()

    # -------------------------
    # STACKED CASHFLOW CHART
    # -------------------------

    st.subheader("Future Deposit Cashflows")

    banks = df["BankName"].unique()
    dates = sorted(df["MaturityDate"].unique())

    x = np.arange(len(dates))
    bottom = np.zeros(len(dates))

    fig, ax = plt.subplots()

    max_total = 0

    for bank in banks:
        bank_data = df[df["BankName"] == bank]

        values = []
        for d in dates:
            match = bank_data[bank_data["MaturityDate"] == d]
            if not match.empty:
                values.append(match["Cashflow"].values[0])
            else:
                values.append(0)

        ax.bar(x, values, bottom=bottom, label=bank)

        bottom += np.array(values)
        max_total = max(max_total, bottom.max())

    ax.set_xticks(x)
    ax.set_xticklabels([d.strftime("%d-%b-%Y") for d in dates])

    ax.set_xlabel("Maturity Date")
    ax.set_ylabel("Cashflow (Amount + Net Yield)")
    ax.set_title("Future Deposit Cashflows")

    # Disable scientific notation
    ax.yaxis.set_major_formatter(StrMethodFormatter('{x:,.0f}'))

    # Add 20% headroom
    ax.set_ylim(0, max_total * 1.2)

    ax.legend(title="Bank")

    st.pyplot(fig)

def clSetup(client_id):
    st.title("Client Setup")
    conn = get_db_connection()
    
    # --- TAB 1: BANK ACCOUNTS ---
    st.subheader("Bank Accounts Management")
    acc_query = "SELECT AccountID, Bank, Currency, IBAN FROM tbAccounts WHERE IDClient = ?"
    df_acc = pd.read_sql(acc_query, conn, params=(client_id,))
    
    if not df_acc.empty:
        st.dataframe(df_acc.drop(columns=['AccountID']), width='stretch', hide_index=True)
    
    with st.expander("New Account"):
        with st.form("add_account_form"):
            banks_df = pd.read_sql("SELECT UserID, Name FROM tbUsers WHERE Type = 'Bank'", conn)
            selected_bank_name = st.selectbox("Assign to Bank", options=banks_df['Name'].tolist())
            col1, col2 = st.columns(2)
            acc_iban = col1.text_input("IBAN Number")
            acc_currency = col1.selectbox("Account Currency", ["EUR", "USD"])
            acc_tax = col2.number_input("Default Tax Rate (%)", value=19.0)
            acc_basis = col2.selectbox("Default Basis", ["Act/365", "Act/Act"])
            
            if st.form_submit_button("Save Account"):
                bank_id = banks_df[banks_df['Name'] == selected_bank_name]['UserID'].values[0]
                cursor = conn.cursor()
                cursor.execute(
                    "INSERT INTO tbAccounts (IDClient, IDBank, Bank, Currency, IBAN, Basis, Tax) VALUES (?, ?, ?, ?, ?, ?, ?)",
                    (client_id, int(bank_id), selected_bank_name, acc_currency, acc_iban, acc_basis, float(acc_tax))
                )
                conn.commit()
                st.success("✅ Account Added")
                st.rerun()

    st.write("---")

    # --- TAB 2: MANUAL DEPOSIT ENTRY ---
    st.subheader("Manual Deposit Entry")
    
    # Form Reset Logic
    if "form_id" not in st.session_state:
        st.session_state.form_id = 0

    # Fetch accounts INCLUDING the correct IDBank (UserID)
    accounts_df = pd.read_sql("SELECT AccountID, IDBank, Bank, Currency, Basis, Tax FROM tbAccounts WHERE IDClient = ?", conn, params=(client_id,))
    
    if not accounts_df.empty:
        display_options = [f"{row['Bank']} ({row['Currency']})" for _, row in accounts_df.iterrows()]
        selected_label = st.selectbox("Select Account", options=display_options)
        selected_acc = accounts_df.iloc[display_options.index(selected_label)]
        
        # Use dynamic key to force clear form on success
        with st.form(key=f"manual_deposit_form_{st.session_state.form_id}"):
            col_a, col_b = st.columns(2)
            m_amount = col_a.number_input("Principal Amount", min_value=0.0, format="%.2f")
            m_rate = col_a.number_input("Rate (%)", min_value=0.0, format="%.4f")
            m_value_date = col_a.date_input("Value Date", datetime.now())
            m_maturity = col_a.date_input("Maturity Date")
            
            col_b.text_input("Currency", value=selected_acc['Currency'], disabled=True)
            col_b.text_input("Basis", value=selected_acc['Basis'], disabled=True)
            col_b.text_input("Tax Rate (%)", value=f"{selected_acc['Tax']:.2f}", disabled=True)
            
            if st.form_submit_button("Save Deposit"):
                tenor_days = (m_maturity - m_value_date).days
                gross, tax_amt, net = test.calc_interest(m_amount, m_rate, tenor_days, selected_acc['Basis'], selected_acc['Tax'])
                
                if tenor_days < 0:
                    st.error("Maturity Date cannot be before Value Date.")
                else:
                    cursor = conn.cursor()
                    sql = """INSERT INTO tbAuctions (IDClient, IDBank, TradeDate, ValueDate, Tenor, MaturityDate, Amount, Currency, Rate, Basis, GrossYield, WithTax, NetYield, BWithTax, Status) 
                             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,'Active')"""
                    cursor.execute(sql, (client_id, int(selected_acc['IDBank']), m_value_date.strftime('%Y-%m-%d'), m_value_date.strftime('%Y-%m-%d'), f"{tenor_days}D", m_maturity.strftime('%Y-%m-%d'), float(m_amount), selected_acc['Currency'], float(m_rate), selected_acc['Basis'], gross, tax_amt, net, selected_acc['Tax'] ))
                    conn.commit()
                    
                    st.success(f"✅ Saved! Net Yield: {net:,.2f}")
                    st.session_state.form_id += 1 # Forces form to clear
                    time.sleep(2)
                    st.rerun()
    conn.close()

def about_client_page():

    st.title("About the Deposit Auction Platform")

    st.markdown("""
    ## Overview

    The **Deposit Auction Platform** is a financial marketplace that connects **clients with banks** 
    to obtain competitive interest rates on short-term deposits.

    Instead of negotiating rates manually, clients can **initiate an auction process** where banks 
    compete by offering interest rates for the deposit.

    This process ensures:
    - transparent pricing
    - competitive interest rates
    - efficient communication between clients and banks
    """)

    st.divider()

    st.header("Client Role in the Auction Process")

    st.markdown("""
    Clients manage their deposits and initiate the auction process when a deposit is close to maturity.

    The platform follows a **three-step process**:

    ### 1 - Call (Client)
    When a deposit approaches maturity, the client can **call a new rate**.

    The client specifies:
    - deposit **amount**
    - **requested rate**
    - **tenor**
    - **new maturity date**

    The platform then sends the request to the selected bank.

    ### 2 - Offer (Bank)
    The bank reviews the request and responds with an **offer rate**.

    The bank calculates:
    - Gross Yield
    - Tax
    - Net Yield

    The offer is then sent back to the client through the platform.

    ### 3 - Confirm (Client)
    The client reviews the bank's offer and decides whether to **confirm the deposit**.

    Once confirmed:
    - the deposit becomes **active**
    - all financial calculations are stored
    - the auction cycle is completed
    """)

    st.divider()

    st.header("Platform Pages for Clients")

    st.markdown("""
    ### Auctions

    The **Auctions page** shows deposits that are **approaching maturity**.

    From this page clients can:
    - view upcoming maturities
    - call new deposit rates
    - initiate new auctions with banks

    ### Analytics

    The **Analytics page** provides an overview of the client's deposit portfolio.

    Clients can view:
    - all deposits with their status
    - gross yield, tax, and net yield
    - future cashflows from deposits
    - maturity schedule visualized in charts

    This helps clients plan **future liquidity and income from deposits**.
    """)

    st.divider()

    st.header("Security and Transparency")

    st.markdown("""
    All transactions and auction steps are recorded in the system.

    The platform ensures:
    - clear audit trail of all actions
    - transparent communication between banks and clients
    - accurate calculation of yields and taxes
    """)

    st.info("The platform is designed to simplify deposit negotiations and provide better transparency for both clients and banks.")

def bkAuctionsConfirm(bank_id):
    st.subheader("Incoming Rate Calls")
    
    conn = get_db_connection()

    # Joining with tbUsers to get Client Name and Bank's BWithTax
    query = """
    SELECT 
        a.AuctionID, uC.Name as ClientName, a.Amount, a.Currency, 
        a.TradeDate, a.ValueDate, a.Tenor, a.MaturityDate, 
        a.CallRate, a.CallTime, a.Basis, uB.BWithTax, a.IDClient
    FROM (tbAuctions AS a
    LEFT JOIN tbUsers AS uB ON a.IDBank = uB.UserID)
    LEFT JOIN tbUsers AS uC ON a.IDClient = uC.UserID
    WHERE a.IDBank = ? AND a.Status = 'Call'
    """
    
    try:
         df = pd.read_sql(query, conn, params=[bank_id])
    finally:
        conn.close()

    if df.empty:
        st.info("No active 'Call' requests found for your bank.")
        return

    # Table configuration
    event = st.dataframe(
    df,
    column_config={
        # HIDDEN COLUMNS
        "AuctionID": None,
        "IDClient": None,
        "BWithTax": None,
        "Basis": None,
        "CallTime": None, # Hiding time here as requested
        
        # FORMATTED COLUMNS
        "ClientName": "Client",
        "Amount": st.column_config.NumberColumn("Amount", format="%,.2f"),
        "CallRate": st.column_config.NumberColumn("Call Rate", format="%.2f%%"),
        
        # DATE ONLY COLUMNS (No Time)
        "TradeDate": st.column_config.DateColumn("Trade Date", format="DD-MMM-YYYY"),
        "ValueDate": st.column_config.DateColumn("Value Date", format="DD-MMM-YYYY"),
        "MaturityDate": st.column_config.DateColumn("Maturity", format="DD-MMM-YYYY"),
        
        "Tenor": "Tenor",
        "Currency": "Ccy"
    },
    hide_index=True,
    on_select="rerun",
    selection_mode="single-row",
    key="bank_confirm_table",
    width='stretch'
    )

    if event.selection.rows:
        row_data = df.iloc[event.selection.rows[0]]
        bank_offer_modal(row_data, execute_bank_offer)
    
def bkAnalytics(bank_id):

    import matplotlib.pyplot as plt
    import numpy as np
    from matplotlib.ticker import StrMethodFormatter

    st.subheader("Bank Deposit Analytics")

    conn = get_db_connection()

    query = """
    SELECT
        a.AuctionID,
        uC.Name as ClientName,
        a.Amount,
        a.GrossYield,
        a.WithTax,
        a.NetYield,
        a.MaturityDate,
        a.Currency,
        a.Basis,
        a.Status
    FROM (tbAuctions AS a
    LEFT JOIN tbUsers AS uC ON a.IDClient = uC.UserID)
    WHERE a.IDBank = ?
    ORDER BY a.MaturityDate ASC
    """

    try:
        df = pd.read_sql(query, conn, params=[bank_id])
    finally:
        conn.close()

    if df.empty:
        st.info("No deposits found.")
        return

    df["MaturityDate"] = pd.to_datetime(df["MaturityDate"])
    df["Cashflow"] = df["Amount"] + df["NetYield"]

    # -------------------------
    # TABLE
    # -------------------------

    df_display = df.copy()
    df_display["MaturityDate"] = df_display["MaturityDate"].dt.date

    st.dataframe(
        df_display,
        column_config={
            "AuctionID": None,
            "ClientName": "Client",
            "Amount": st.column_config.NumberColumn("Amount", format="%,.2f"),
            "GrossYield": st.column_config.NumberColumn("Gross Yield", format="%,.2f"),
            "WithTax": st.column_config.NumberColumn("Tax", format="%,.2f"),
            "NetYield": st.column_config.NumberColumn("Net Yield", format="%,.2f"),
            "Cashflow": st.column_config.NumberColumn("Cashflow", format="%,.2f"),
            "MaturityDate": st.column_config.DateColumn("Maturity", format="DD-MMM-YYYY"),
            "Currency": "Currency",
            "Basis": "Basis",
            "Status": "Status"
        },
        hide_index=True,
        width='stretch'
    )

    st.divider()

    # -------------------------
    # STACKED CASHFLOW CHART
    # -------------------------

    st.subheader("Future Funding Cashflows")

    clients = df["ClientName"].unique()
    dates = sorted(df["MaturityDate"].unique())

    x = np.arange(len(dates))
    bottom = np.zeros(len(dates))

    fig, ax = plt.subplots()

    max_total = 0

    for client in clients:
        client_data = df[df["ClientName"] == client]

        values = []
        for d in dates:
            match = client_data[client_data["MaturityDate"] == d]
            if not match.empty:
                values.append(match["Cashflow"].values[0])
            else:
                values.append(0)

        ax.bar(x, values, bottom=bottom, label=client)

        bottom += np.array(values)
        max_total = max(max_total, bottom.max())

    ax.set_xticks(x)
    ax.set_xticklabels([d.strftime("%d-%b-%Y") for d in dates])

    ax.set_xlabel("Maturity Date")
    ax.set_ylabel("Cashflow (Amount + Net Yield)")
    ax.set_title("Future Deposit Maturities")

    ax.yaxis.set_major_formatter(StrMethodFormatter('{x:,.0f}'))

    ax.set_ylim(0, max_total * 1.2)

    ax.legend(title="Client")

    st.pyplot(fig)

def about_bank_page():

    st.title("About the Deposit Auction Platform – Bank Perspective")

    st.markdown("""
    ## Overview

    The **Deposit Auction Platform** is a secure financial marketplace connecting **banks with clients**.  
    Banks can receive auction requests from clients for deposits approaching maturity and compete by offering rates.

    The platform ensures:
    - transparent client-bank communication
    - efficient auction management
    - automated calculation of yields, taxes, and net returns
    """)

    st.divider()

    st.header("Bank Role in the Auction Process")

    st.markdown("""
    Banks interact with clients through a structured **three-step process**:

    ### 1 - Call (Client Initiates)
    - Clients submit a **rate call** when a deposit is near maturity.
    - The call includes:
      - deposit **amount**
      - requested **rate**
      - **tenor**
      - new **maturity date**
    - The bank receives this call in real-time on the platform.

    ### 2 - Offer (Bank Responds)
    - The bank reviews the call and provides an **offer rate**.
    - The system calculates:
      - **Gross Yield**
      - **Tax Amount**
      - **Net Yield**
    - The offer is sent back to the client.

    ### 3 - Confirm (Client Confirms)
    - The client decides whether to accept the bank's offer.
    - Upon confirmation:
      - the deposit status changes to **Active**
      - all yield calculations are finalized and stored
      - the auction cycle is completed
    """)

    st.divider()

    st.header("Bank Analytics & Dashboard")

    st.markdown("""
    The **Bank Analytics page** provides a comprehensive overview of the bank’s deposits:

    - **Incoming Calls**: View all client rate calls awaiting your response.
    - **Offers Made**: Track your submitted offers and corresponding yields.
    - **Deposit Portfolio**: Review active deposits with gross/net yields and maturities.
    - **Cashflow Visualization**: See a stacked chart of upcoming cashflows by client.
    
    This helps banks:
    - monitor upcoming maturities
    - manage liquidity and interest income
    - assess exposure to individual clients
    """)

    st.divider()

    st.header("Security and Transparency")

    st.markdown("""
    The platform ensures that all bank actions are recorded and auditable:

    - each **call and offer** is logged
    - calculation of yields and taxes is **automated and transparent**
    - secure communication between clients and banks is guaranteed

    Banks can therefore operate confidently and efficiently within the system.
    """)

    st.info("The platform is designed to simplify deposit auctions and provide clear insights into client interactions and cashflow management.")