import pandas as pd
from sqlalchemy import create_engine
import sys
import os
import base64
from io import BytesIO
from openpyxl.styles import Border, Side, Alignment, NamedStyle
from openpyxl import load_workbook

# Database connection class - simplified for portal use
import json

class DatabaseConnection:
    def __init__(self, config_file=None):
        if config_file and os.path.exists(config_file):
            with open(config_file, 'r') as f:
                config = json.load(f)
            self.connection_string = f"mysql+pymysql://{config['username']}:{config['password']}@{config['host']}/{config['database']}"
        else:
            raise ValueError(
                'Database config_file is required. Pass a JSON config path (e.g. db_config.json) with '
                'username, password, host, and database. No default connection string is used for security.'
            )

    def get_engine(self):
        return create_engine(self.connection_string)

try:
    print('DEBUG: Starting downloadLoans_stream.py')
    shortname = sys.argv[1]
    shortdate = sys.argv[2]
    config_file = sys.argv[3] if len(sys.argv) > 3 else None
except:
    shortname = input('Enter shortname: ')
    shortdate = input('Enter shortdate: ')
    config_file = None

# Allow config path from env so callers don't need to pass credentials on command line
if not config_file and os.environ.get('DB_CONFIG_PATH') and os.path.exists(os.environ['DB_CONFIG_PATH']):
    config_file = os.environ['DB_CONFIG_PATH']

try:
    print(f'DEBUG: Connecting to DB for shortname={shortname}, shortdate={shortdate}')
    # Use proper database connection
    db = DatabaseConnection(config_file)
    engine = db.get_engine()
    
    print('DEBUG: Reading loanscurrent')
    loanscurrent = pd.read_sql(shortname + 'loanscurrent' + shortdate, engine)
    print(f'DEBUG: loanscurrent shape: {loanscurrent.shape}')
    
    print('DEBUG: Reading customfactors')
    customfactors = pd.read_sql(shortname + 'reserves' + shortdate, engine)
    print(f'DEBUG: customfactors shape: {customfactors.shape}')
    customfactors = customfactors[['callreportcode', 'loantype', 'qualadj', 'customfactors']]
    
    loanscurrent = pd.merge(loanscurrent, customfactors, how='left', left_on='CallReportCode', right_on='callreportcode')

    # Load pool_names mapping for this shortname
    pool_names_query = f"SELECT poolCode, newName FROM pool_names WHERE shortname = '{shortname}'"
    print(f'DEBUG: pool_names_query: {pool_names_query}')
    pool_names_df = pd.read_sql(pool_names_query, engine)
    pool_names_map = dict(zip(pool_names_df['poolCode'].astype(str), pool_names_df['newName']))

    def get_loan_type_display(row):
        callreportcode = str(row['CallReportCode'])
        if callreportcode in pool_names_map:
            return pool_names_map[callreportcode]
        else:
            return row['LoanType']

    # Apply the loan type mapping
    loanscurrent['Loan Type'] = loanscurrent.apply(get_loan_type_display, axis=1)
    
    # Clean and format the data
    loanscurrent['Current Balance'] = pd.to_numeric(loanscurrent['CurrentBalance'], errors='coerce')
    loanscurrent['Avg Loss Rate'] = pd.to_numeric(loanscurrent['avglossrate'], errors='coerce')
    loanscurrent['Remaining Life'] = pd.to_numeric(loanscurrent['RemainingLife'], errors='coerce')
    loanscurrent['Lifetime Loss'] = pd.to_numeric(loanscurrent['lifetimeloss'], errors='coerce')
    loanscurrent['Fwd Looking Adj'] = pd.to_numeric(loanscurrent['qualadj'], errors='coerce')
    loanscurrent['Custom Qual Factors'] = pd.to_numeric(loanscurrent['customfactors'], errors='coerce')
    loanscurrent['Total Loss Rate'] = pd.to_numeric(loanscurrent['totalrate'], errors='coerce')
    loanscurrent['Total Reserve'] = pd.to_numeric(loanscurrent['totalreserve'], errors='coerce')

    # Handle branch number for specific banks
    if shortname == 'vintagebankks':
        loanscurrent['BranchNumber'] = loanscurrent['BranchNumber'].astype(str).str.replace('\\.0', '', regex=True)
        loanscurrent = loanscurrent[['Acct#', 'Loan Type', 'Current Balance', 'Avg Loss Rate', 'Remaining Life', 'Lifetime Loss', 'Fwd Looking Adj', 'Custom Qual Factors', 'Total Loss Rate', 'Total Reserve', 'BranchNumber']]
    else:
        loanscurrent = loanscurrent[['Acct#', 'Loan Type', 'Current Balance', 'Avg Loss Rate', 'Remaining Life', 'Lifetime Loss', 'Fwd Looking Adj', 'Custom Qual Factors', 'Total Loss Rate', 'Total Reserve']]

    # Create Excel file in memory
    print('DEBUG: Creating Excel file in memory...')
    
    # Create a new workbook with basic structure
    from openpyxl import Workbook
    wb = Workbook()
    ws = wb.active
    ws.title = "LoanDownload"
    
    # Set headers
    headers = ['Account#', 'Loan Type', 'Current Balance', 'Avg Loss Rate', 'Remaining Life', 'Lifetime Loss', 'Fwd Looking Adj', 'Custom Qual Factors', 'Total Loss Rate', 'Total Reserve']
    if shortname == 'vintagebankks':
        headers.append('Branch #')
    
    for col, header in enumerate(headers, 1):
        ws.cell(row=1, column=col, value=header)
    
    # Add data
    print('DEBUG: Writing data to Excel...')
    loanscurrent['Acct#'] = loanscurrent['Acct#'].astype(str).str.replace('\\.0', '', regex=True)
    
    for idx, row in loanscurrent.iterrows():
        excel_row = idx + 2  # Start from row 2 (after header)
        ws.cell(row=excel_row, column=1, value=str(row['Acct#']))
        ws.cell(row=excel_row, column=2, value=row['Loan Type'])
        ws.cell(row=excel_row, column=3, value=row['Current Balance'])
        ws.cell(row=excel_row, column=4, value=row['Avg Loss Rate'])
        ws.cell(row=excel_row, column=5, value=row['Remaining Life'])
        ws.cell(row=excel_row, column=6, value=row['Lifetime Loss'])
        ws.cell(row=excel_row, column=7, value=row['Fwd Looking Adj'])
        ws.cell(row=excel_row, column=8, value=row['Custom Qual Factors'])
        ws.cell(row=excel_row, column=9, value=row['Total Loss Rate'])
        ws.cell(row=excel_row, column=10, value=row['Total Reserve'])
        
        if shortname == 'vintagebankks':
            ws.cell(row=excel_row, column=11, value=str(row['BranchNumber']))

    # Save to memory buffer
    print('DEBUG: Saving to memory buffer...')
    buffer = BytesIO()
    wb.save(buffer)
    buffer.seek(0)
    
    # Output base64 encoded Excel file
    excel_data = buffer.getvalue()
    base64_data = base64.b64encode(excel_data).decode('utf-8')
    
    print('EXCEL_DATA_START')
    print(base64_data)
    print('EXCEL_DATA_END')
    print(f'FILENAME:{shortname}_LoanDownload_{shortdate}.xlsx')

except Exception as e:
    print('ERROR')
    print(f'DEBUG: Exception occurred: {e}')
    import traceback
    traceback.print_exc()
    sys.exit(1)