import csv
import pandas as pd

# Read all data files
mapping = pd.read_csv('account_mapping_income_statement.csv')
mar_2025 = pd.read_csv('march_2025_trial_balance.csv')
feb_2025 = pd.read_csv('feb_2025_actuals.csv')
mar_2024 = pd.read_csv('mar_2024_actuals.csv')
budget = pd.read_csv('mar_2025_budget.csv')

# Merge mapping with actuals
mar_2025_mapped = mar_2025.merge(mapping, on='account_no')
feb_2025_mapped = feb_2025.merge(mapping, on='account_no')
mar_2024_mapped = mar_2024.merge(mapping, on='account_no')
budget_mapped = budget.merge(mapping, on='account_no')

# Define section order
section_order = ['REVENUE', 'COST_OF_REVENUE', 'OPERATING_EXPENSES', 'OTHER_INCOME_EXPENSE', 'INCOME_TAX']

# Build income statement
results = []

# Process each section
for section in section_order:
    section_data_mar = mar_2025_mapped[mar_2025_mapped['section'] == section]
    section_data_feb = feb_2025_mapped[feb_2025_mapped['section'] == section]
    section_data_ly = mar_2024_mapped[mar_2024_mapped['section'] == section]
    section_data_bud = budget_mapped[budget_mapped['section'] == section]
    
    if len(section_data_mar) > 0:
        # Section header
        results.append({
            'line_item': section.replace('_', ' ').title(),
            'type': 'header'
        })
        
        # Group by line_item
        for line_item in section_data_mar.sort_values('sort_order')['line_item'].unique():
            mar_val = section_data_mar[section_data_mar['line_item'] == line_item]['amount_usd'].sum()
            feb_val = section_data_feb[section_data_feb['line_item'] == line_item]['amount_usd'].sum()
            ly_val = section_data_ly[section_data_ly['line_item'] == line_item]['amount_usd'].sum()
            bud_val = section_data_bud[section_data_bud['line_item'] == line_item]['amount_usd'].sum()
            
            # Calculate variances
            var_mom = mar_val - feb_val
            var_yoy = mar_val - ly_val
            var_bud = mar_val - bud_val
            
            var_mom_pct = (var_mom / feb_val * 100) if feb_val != 0 else 0
            var_yoy_pct = (var_yoy / ly_val * 100) if ly_val != 0 else 0
            var_bud_pct = (var_bud / bud_val * 100) if bud_val != 0 else 0
            
            results.append({
                'line_item': '  ' + line_item,
                'mar_2025': mar_val,
                'feb_2025': feb_val,
                'var_mom': var_mom,
                'var_mom_pct': var_mom_pct,
                'mar_2024': ly_val,
                'var_yoy': var_yoy,
                'var_yoy_pct': var_yoy_pct,
                'budget': bud_val,
                'var_budget': var_bud,
                'var_budget_pct': var_bud_pct,
                'type': 'detail'
            })
        
        # Section total
        mar_total = section_data_mar['amount_usd'].sum()
        feb_total = section_data_feb['amount_usd'].sum()
        ly_total = section_data_ly['amount_usd'].sum()
        bud_total = section_data_bud['amount_usd'].sum()
        
        var_mom_total = mar_total - feb_total
        var_yoy_total = mar_total - ly_total
        var_bud_total = mar_total - bud_total
        
        var_mom_pct_total = (var_mom_total / feb_total * 100) if feb_total != 0 else 0
        var_yoy_pct_total = (var_yoy_total / ly_total * 100) if ly_total != 0 else 0
        var_bud_pct_total = (var_bud_total / bud_total * 100) if bud_total != 0 else 0
        
        results.append({
            'line_item': 'Total ' + section.replace('_', ' ').title(),
            'mar_2025': mar_total,
            'feb_2025': feb_total,
            'var_mom': var_mom_total,
            'var_mom_pct': var_mom_pct_total,
            'mar_2024': ly_total,
            'var_yoy': var_yoy_total,
            'var_yoy_pct': var_yoy_pct_total,
            'budget': bud_total,
            'var_budget': var_bud_total,
            'var_budget_pct': var_bud_pct_total,
            'type': 'subtotal'
        })
        
        results.append({'line_item': '', 'type': 'blank'})

# Calculate key metrics
revenue_mar = mar_2025_mapped[mar_2025_mapped['section'] == 'REVENUE']['amount_usd'].sum()
revenue_feb = feb_2025_mapped[feb_2025_mapped['section'] == 'REVENUE']['amount_usd'].sum()
revenue_ly = mar_2024_mapped[mar_2024_mapped['section'] == 'REVENUE']['amount_usd'].sum()
revenue_bud = budget_mapped[budget_mapped['section'] == 'REVENUE']['amount_usd'].sum()

cogs_mar = mar_2025_mapped[mar_2025_mapped['section'] == 'COST_OF_REVENUE']['amount_usd'].sum()
cogs_feb = feb_2025_mapped[feb_2025_mapped['section'] == 'COST_OF_REVENUE']['amount_usd'].sum()
cogs_ly = mar_2024_mapped[mar_2024_mapped['section'] == 'COST_OF_REVENUE']['amount_usd'].sum()
cogs_bud = budget_mapped[budget_mapped['section'] == 'COST_OF_REVENUE']['amount_usd'].sum()

gp_mar = revenue_mar - cogs_mar
gp_feb = revenue_feb - cogs_feb
gp_ly = revenue_ly - cogs_ly
gp_bud = revenue_bud - cogs_bud

results.append({
    'line_item': 'GROSS PROFIT',
    'mar_2025': gp_mar,
    'feb_2025': gp_feb,
    'var_mom': gp_mar - gp_feb,
    'var_mom_pct': ((gp_mar - gp_feb) / gp_feb * 100) if gp_feb != 0 else 0,
    'mar_2024': gp_ly,
    'var_yoy': gp_mar - gp_ly,
    'var_yoy_pct': ((gp_mar - gp_ly) / gp_ly * 100) if gp_ly != 0 else 0,
    'budget': gp_bud,
    'var_budget': gp_mar - gp_bud,
    'var_budget_pct': ((gp_mar - gp_bud) / gp_bud * 100) if gp_bud != 0 else 0,
    'type': 'key_metric'
})

results.append({
    'line_item': 'Gross Margin %',
    'mar_2025': (gp_mar / revenue_mar * 100) if revenue_mar != 0 else 0,
    'feb_2025': (gp_feb / revenue_feb * 100) if revenue_feb != 0 else 0,
    'mar_2024': (gp_ly / revenue_ly * 100) if revenue_ly != 0 else 0,
    'budget': (gp_bud / revenue_bud * 100) if revenue_bud != 0 else 0,
    'type': 'percentage'
})

results.append({'line_item': '', 'type': 'blank'})

opex_mar = mar_2025_mapped[mar_2025_mapped['section'] == 'OPERATING_EXPENSES']['amount_usd'].sum()
opex_feb = feb_2025_mapped[feb_2025_mapped['section'] == 'OPERATING_EXPENSES']['amount_usd'].sum()
opex_ly = mar_2024_mapped[mar_2024_mapped['section'] == 'OPERATING_EXPENSES']['amount_usd'].sum()
opex_bud = budget_mapped[budget_mapped['section'] == 'OPERATING_EXPENSES']['amount_usd'].sum()

ebit_mar = gp_mar - opex_mar
ebit_feb = gp_feb - opex_feb
ebit_ly = gp_ly - opex_ly
ebit_bud = gp_bud - opex_bud

results.append({
    'line_item': 'OPERATING INCOME (EBIT)',
    'mar_2025': ebit_mar,
    'feb_2025': ebit_feb,
    'var_mom': ebit_mar - ebit_feb,
    'var_mom_pct': ((ebit_mar - ebit_feb) / ebit_feb * 100) if ebit_feb != 0 else 0,
    'mar_2024': ebit_ly,
    'var_yoy': ebit_mar - ebit_ly,
    'var_yoy_pct': ((ebit_mar - ebit_ly) / ebit_ly * 100) if ebit_ly != 0 else 0,
    'budget': ebit_bud,
    'var_budget': ebit_mar - ebit_bud,
    'var_budget_pct': ((ebit_mar - ebit_bud) / ebit_bud * 100) if ebit_bud != 0 else 0,
    'type': 'key_metric'
})

results.append({
    'line_item': 'Operating Margin %',
    'mar_2025': (ebit_mar / revenue_mar * 100) if revenue_mar != 0 else 0,
    'feb_2025': (ebit_feb / revenue_feb * 100) if revenue_feb != 0 else 0,
    'mar_2024': (ebit_ly / revenue_ly * 100) if revenue_ly != 0 else 0,
    'budget': (ebit_bud / revenue_bud * 100) if revenue_bud != 0 else 0,
    'type': 'percentage'
})

results.append({'line_item': '', 'type': 'blank'})

# Calculate net other income/expense
other_income_mar = mar_2025_mapped[(mar_2025_mapped['section'] == 'OTHER_INCOME_EXPENSE') & (mar_2025_mapped['account_class'] == 'other_income')]['amount_usd'].sum()
other_expense_mar = mar_2025_mapped[(mar_2025_mapped['section'] == 'OTHER_INCOME_EXPENSE') & (mar_2025_mapped['account_class'] == 'other_expense')]['amount_usd'].sum()
net_other_mar = other_income_mar - other_expense_mar

other_income_feb = feb_2025_mapped[(feb_2025_mapped['section'] == 'OTHER_INCOME_EXPENSE') & (feb_2025_mapped['account_class'] == 'other_income')]['amount_usd'].sum()
other_expense_feb = feb_2025_mapped[(feb_2025_mapped['section'] == 'OTHER_INCOME_EXPENSE') & (feb_2025_mapped['account_class'] == 'other_expense')]['amount_usd'].sum()
net_other_feb = other_income_feb - other_expense_feb

other_income_ly = mar_2024_mapped[(mar_2024_mapped['section'] == 'OTHER_INCOME_EXPENSE') & (mar_2024_mapped['account_class'] == 'other_income')]['amount_usd'].sum()
other_expense_ly = mar_2024_mapped[(mar_2024_mapped['section'] == 'OTHER_INCOME_EXPENSE') & (mar_2024_mapped['account_class'] == 'other_expense')]['amount_usd'].sum()
net_other_ly = other_income_ly - other_expense_ly

other_income_bud = budget_mapped[(budget_mapped['section'] == 'OTHER_INCOME_EXPENSE') & (budget_mapped['account_class'] == 'other_income')]['amount_usd'].sum()
other_expense_bud = budget_mapped[(budget_mapped['section'] == 'OTHER_INCOME_EXPENSE') & (budget_mapped['account_class'] == 'other_expense')]['amount_usd'].sum()
net_other_bud = other_income_bud - other_expense_bud

ebt_mar = ebit_mar + net_other_mar
ebt_feb = ebit_feb + net_other_feb
ebt_ly = ebit_ly + net_other_ly
ebt_bud = ebit_bud + net_other_bud

results.append({
    'line_item': 'INCOME BEFORE TAX',
    'mar_2025': ebt_mar,
    'feb_2025': ebt_feb,
    'var_mom': ebt_mar - ebt_feb,
    'var_mom_pct': ((ebt_mar - ebt_feb) / ebt_feb * 100) if ebt_feb != 0 else 0,
    'mar_2024': ebt_ly,
    'var_yoy': ebt_mar - ebt_ly,
    'var_yoy_pct': ((ebt_mar - ebt_ly) / ebt_ly * 100) if ebt_ly != 0 else 0,
    'budget': ebt_bud,
    'var_budget': ebt_mar - ebt_bud,
    'var_budget_pct': ((ebt_mar - ebt_bud) / ebt_bud * 100) if ebt_bud != 0 else 0,
    'type': 'key_metric'
})

results.append({'line_item': '', 'type': 'blank'})

tax_mar = mar_2025_mapped[mar_2025_mapped['section'] == 'INCOME_TAX']['amount_usd'].sum()
tax_feb = feb_2025_mapped[feb_2025_mapped['section'] == 'INCOME_TAX']['amount_usd'].sum()
tax_ly = mar_2024_mapped[mar_2024_mapped['section'] == 'INCOME_TAX']['amount_usd'].sum()
tax_bud = budget_mapped[budget_mapped['section'] == 'INCOME_TAX']['amount_usd'].sum()

net_income_mar = ebt_mar - tax_mar
net_income_feb = ebt_feb - tax_feb
net_income_ly = ebt_ly - tax_ly
net_income_bud = ebt_bud - tax_bud

results.append({
    'line_item': 'NET INCOME',
    'mar_2025': net_income_mar,
    'feb_2025': net_income_feb,
    'var_mom': net_income_mar - net_income_feb,
    'var_mom_pct': ((net_income_mar - net_income_feb) / net_income_feb * 100) if net_income_feb != 0 else 0,
    'mar_2024': net_income_ly,
    'var_yoy': net_income_mar - net_income_ly,
    'var_yoy_pct': ((net_income_mar - net_income_ly) / net_income_ly * 100) if net_income_ly != 0 else 0,
    'budget': net_income_bud,
    'var_budget': net_income_mar - net_income_bud,
    'var_budget_pct': ((net_income_mar - net_income_bud) / net_income_bud * 100) if net_income_bud != 0 else 0,
    'type': 'key_metric'
})

results.append({
    'line_item': 'Net Margin %',
    'mar_2025': (net_income_mar / revenue_mar * 100) if revenue_mar != 0 else 0,
    'feb_2025': (net_income_feb / revenue_feb * 100) if revenue_feb != 0 else 0,
    'mar_2024': (net_income_ly / revenue_ly * 100) if revenue_ly != 0 else 0,
    'budget': (net_income_bud / revenue_bud * 100) if revenue_bud != 0 else 0,
    'type': 'percentage'
})

# Format output
def format_currency(val):
    if pd.isna(val):
        return ''
    return f"${val:,.0f}"

def format_percent(val):
    if pd.isna(val):
        return ''
    return f"{val:.1f}%"

# Create formatted output
with open('Board_Income_Statement_March_2025.csv', 'w', newline='') as f:
    writer = csv.writer(f)
    
    # Header
    writer.writerow(['INCOME STATEMENT - MARCH 2025'])
    writer.writerow(['(in USD)'])
    writer.writerow([])
    writer.writerow([
        '',
        'Mar 2025',
        'Feb 2025',
        'Var $',
        'Var %',
        'Mar 2024',
        'YoY Var $',
        'YoY Var %',
        'Budget',
        'Var to Budget $',
        'Var to Budget %'
    ])
    
    for row in results:
        row_type = row.get('type')
        
        if row_type == 'header':
            writer.writerow([row['line_item']])
        elif row_type == 'blank':
            writer.writerow([])
        elif row_type == 'percentage':
            writer.writerow([
                row['line_item'],
                format_percent(row.get('mar_2025', '')),
                format_percent(row.get('feb_2025', '')),
                '',
                '',
                format_percent(row.get('mar_2024', '')),
                '',
                '',
                format_percent(row.get('budget', '')),
                '',
                ''
            ])
        else:
            writer.writerow([
                row['line_item'],
                format_currency(row.get('mar_2025', '')),
                format_currency(row.get('feb_2025', '')),
                format_currency(row.get('var_mom', '')),
                format_percent(row.get('var_mom_pct', '')),
                format_currency(row.get('mar_2024', '')),
                format_currency(row.get('var_yoy', '')),
                format_percent(row.get('var_yoy_pct', '')),
                format_currency(row.get('budget', '')),
                format_currency(row.get('var_budget', '')),
                format_percent(row.get('var_budget_pct', ''))
            ])

print("✓ Board-ready income statement generated: Board_Income_Statement_March_2025.csv")
print(f"\n📊 Key Metrics Summary:")
print(f"Revenue: {format_currency(revenue_mar)} (vs Budget: {format_percent((revenue_mar - revenue_bud) / revenue_bud * 100)})")
print(f"Gross Profit: {format_currency(gp_mar)} | Margin: {format_percent(gp_mar / revenue_mar * 100)}")
print(f"Operating Income: {format_currency(ebit_mar)} | Margin: {format_percent(ebit_mar / revenue_mar * 100)}")
print(f"Net Income: {format_currency(net_income_mar)} | Margin: {format_percent(net_income_mar / revenue_mar * 100)}")
print(f"\n⚠️  Key Variances:")
print(f"• Revenue vs Budget: {format_currency(revenue_mar - revenue_bud)} ({format_percent((revenue_mar - revenue_bud) / revenue_bud * 100)})")
print(f"• Operating Income vs Budget: {format_currency(ebit_mar - ebit_bud)} ({format_percent((ebit_mar - ebit_bud) / ebit_bud * 100)})")
print(f"• Net Income vs Budget: {format_currency(net_income_mar - net_income_bud)} ({format_percent((net_income_mar - net_income_bud) / net_income_bud * 100)})")