import sqlite3
import pandas as pd
import test
from datetime import datetime
from mailer import send_auction_notification
from utils import normalize_data
from test import calc_interest  # Import the correct function
from db_utils import get_db_connection
import streamlit as st

def clean_data_format(df):
    """
    Cleans dataframe columns. 
    Dates are handled via simple string splitting to avoid .dt errors.
    """
    for col in df.columns:
        # --- IF YOU EVER NEED THE OLD METHOD BACK ---
        # try:
        #     # Old problematic logic
        #     df[col] = pd.to_datetime(df[col], errors='ignore').dt.strftime('%Y-%m-%d')
        # except Exception as e:
        #     print(f"Migration Error: {e}")
        
        # --- NEW SAFE METHOD ---
        if 'Date' in col:
            # Ensures it's a string and removes time component if present
            df[col] = df[col].astype(str).str.split(' ').str[0]
            
    return df

def init_db():
    conn = get_db_connection()
    cursor = conn.cursor()
    # Updated Schema to include lifecycle columns
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS tbAuctions (
        AuctionID INTEGER PRIMARY KEY AUTOINCREMENT,
        MaID INTEGER, -- Parent ID for rollovers
        IDClient INTEGER,
        IDBank INTEGER,
        TradeDate TEXT,
        ValueDate TEXT,
        Tenor TEXT,
        MaturityDate TEXT,
        Amount REAL,
        Currency TEXT,
        Rate REAL,
        Basis TEXT,
        GrossYield REAL,
        WithTax REAL,
        NetYield REAL,
        Status TEXT,
        CallTime TEXT,
        CallRate REAL,
        OfferTime TEXT,
        OfferRate REAL,
        ConfirmTime TEXT,
        ConfirmRate REAL
    )
    """)
    conn.commit()
    conn.close()

def execute_call_insert(original_data, amount, rate, t_date, t_time, new_maturity, tenor_str):
    """
    1. Updates original maturing deposit status to 'Called'
    2. Calculates Yields using calc_interest
    3. Inserts a new deposit request with status 'Call'
    4. Sends notification to the bank
    """
    try:        
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # Clean the input data
        original_data = normalize_data(original_data)
        
        # --- PREPARE DATA ---
        original_maturity = original_data.get('MaturityDate')
        basis_str = original_data.get('Basis', 'Act/365')
        tax_str = original_data.get('BWithTax')
        
        # Calculate precise tenor from dates
        if isinstance(t_date, str): t_date = datetime.strptime(t_date, '%Y-%m-%d').date()
        if isinstance(new_maturity, str): new_maturity = datetime.strptime(new_maturity, '%Y-%m-%d').date()
        days_diff = (new_maturity - t_date).days
        
        # --- UPDATED: Use calc_interest ---
        # Note: Added 0 for tax_percent as per your existing structure
        gross, tax, net = calc_interest(
            amount, rate, days_diff, basis_str, tax_str
        )
        
        # Prepare strings for SQLite
        db_mat = new_maturity.strftime('%Y-%m-%d')
        call_ts = datetime.combine(t_date, t_time).strftime('%Y-%m-%d %H:%M')
        
        # --- 1. UPDATE ORIGINAL RECORD ---
        cursor.execute(
            "UPDATE tbAuctions SET Status = 'Called' WHERE AuctionID = ?",
            (int(original_data['AuctionID']),)
        )

        # --- 2. INSERT NEW 'CALL' RECORD ---
        sql = """
        INSERT INTO tbAuctions (
            MaID, IDClient, IDBank, TradeDate, ValueDate, Tenor, 
            MaturityDate, Amount, Currency, Rate, Basis, Status, 
            CallTime, CallRate, GrossYield, WithTax, NetYield, BWithTax
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'Call', ?, ?, ?, ?, ?,?)
        """
        
        values = (
            int(original_data['AuctionID']),
            int(original_data['IDClient']),
            int(original_data['IDBank']),
            original_maturity,       # TradeDate (Continuity)
            original_maturity,       # ValueDate (Continuity)
            str(tenor_str),
            db_mat,
            float(amount),
            str(original_data['Currency']),
            float(rate),
            str(basis_str),
            call_ts,
            float(rate),
            float(gross),
            float(tax),
            float(net),
            float(tax_str)
        )
        
        cursor.execute(sql, values)
        conn.commit()
        conn.close()

        # --- 3. SEND EMAIL NOTIFICATION ---
        mail_payload = {
            "ClientName": original_data.get('ClientName', 'Client'),
            "BankName": original_data['BankName'],
            "Amount": amount,
            "Currency": original_data['Currency'],
            "RecipientEmail": original_data['BankEmail'],
            "CallRate": rate,
            # ADD THESE MISSING FIELDS:
            "CallTime": call_ts, 
            "TradeDate": db_mat,     # Or your desired date variable
            "Maturity": db_mat,      # Matching the 'Maturity Date' expected in mailer
            "Tenor": tenor_str
        }

        #print(f"DEBUG: Sending Email Payload: {mail_payload}")
        send_auction_notification("Call", mail_payload)
        
        return True

    except Exception as e:
        print(f"❌ Database Error in execute_call_insert: {e}")
        return False

def execute_bank_offer(row_data, offer_rate, gross, net, status):
    # 1. Convert to standard Python dict and fix NumPy types (int64/float64)
    from utils import normalize_data
    row_data = normalize_data(row_data)
    
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # 2. Update the database
        sql = """
            UPDATE tbAuctions 
            SET Status = ?, OfferRate = ?, GrossYield = ?, NetYield = ?, OfferTime = ?
            WHERE AuctionID = ?
        """
        now = datetime.now().strftime('%Y-%m-%d %H:%M')
        cursor.execute(sql, (status, offer_rate, gross, net, now, row_data['AuctionID']))
        
        # 3. FIX: Get Client Email (since it was missing in your debug output)
        cursor.execute("SELECT Email FROM tbUsers WHERE UserID = ?", (row_data['IDClient'],))
        client_email_row = cursor.fetchone()
        client_email = client_email_row[0] if client_email_row else None

        conn.commit()
        conn.close()

        # 4. Prepare the email notification
        mail_payload = {
            "ClientName": row_data.get('ClientName'),
            "BankName": st.session_state.get('user_name', 'Bank'), # Get current logged in bank name
            "Amount": row_data.get('Amount'),
            "Currency": row_data.get('Currency'),
            "RecipientEmail": client_email, 
            "OfferRate": offer_rate,
            "Maturity": row_data.get('MaturityDate')
        }
        
        send_auction_notification(status, mail_payload)
        return True

    except Exception as e:
        st.error(f"❌ Error in execute_bank_offer: {e}")
        return False

def get_banks_for_currency(client_id, currency):
    """
    Finds all banks that have an account associated 
    with the client and the requested currency.
    """
    client_id = int(client_id)  # ← force plain int, not numpy int64
    currency = str(currency)    # ← force plain string

    conn = get_db_connection()
    conn.row_factory = sqlite3.Row 
    cursor = conn.cursor()
    
    # query updated to match dap.db schema:
    # tbUsers: UserID, Name, Email, Type [cite: 20, 21]
    # tbAccounts: IDClient, Currency, BankID 
    query = """
        SELECT DISTINCT u.UserID AS IDBank, u.Name AS BankName, u.Email AS BankEmail
        FROM tbUsers u
        JOIN tbAccounts a ON u.UserID = a.IDBank
        WHERE a.IDClient = ? AND a.Currency = ? AND u.Type = 'Bank'
    """
    
    cursor.execute(query, (client_id, currency))
    banks = [dict(row) for row in cursor.fetchall()]
    conn.close()
    return banks