#!/usr/bin/env python3

import csv
from datetime import datetime

def add_internet_expenses_to_analysis():
    """Add processed internet expenses to the main financial analysis - FIXED VERSION"""
    
    # Read the existing complete expenses
    existing_expenses = []
    try:
        with open('CLEAN_COMPLETE_EXPENSES_2025-2026.csv', 'r', encoding='utf-8') as f:
            reader = csv.DictReader(f)
            existing_expenses = list(reader)
        print(f"Loaded {len(existing_expenses)} existing expense records")
        
        # Show structure of existing data
        if existing_expenses:
            print(f"Existing data columns: {list(existing_expenses[0].keys())}")
        
    except FileNotFoundError:
        print("Main expense file not found, will create new one")
    
    # Read the internet expenses
    internet_expenses = []
    with open('processed_internet_expenses_fixed.csv', 'r', encoding='utf-8') as f:
        reader = csv.DictReader(f)
        internet_expenses = list(reader)
    
    print(f"Loaded {len(internet_expenses)} internet expense records")
    if internet_expenses:
        print(f"Internet data columns: {list(internet_expenses[0].keys())}")
    
    # Convert internet expenses to match the main format (lowercase columns)
    formatted_internet_expenses = []
    for expense in internet_expenses:
        formatted_expense = {
            'date': expense['Date'],
            'amount': float(expense['Amount']),
            'vendor': expense['Vendor'],
            'cardholder': 'Keith Lauver',  # Default cardholder
            'category': 'Internet/Utilities',  # More specific category
            'business': expense['Category'],  # This contains 'Atomic Elevator'
            'source': f"{expense['Source']} (Reimbursed)"
        }
        formatted_internet_expenses.append(formatted_expense)
    
    # Combine all expenses
    all_expenses = existing_expenses + formatted_internet_expenses
    
    # Sort by date
    all_expenses.sort(key=lambda x: x['date'])
    
    # Write updated file
    output_file = 'COMPLETE_EXPENSES_WITH_INTERNET_2025-2026.csv'
    if all_expenses:
        fieldnames = ['date', 'amount', 'vendor', 'cardholder', 'category', 'business', 'source']
        with open(output_file, 'w', newline='', encoding='utf-8') as f:
            writer = csv.DictWriter(f, fieldnames=fieldnames)
            writer.writeheader()
            writer.writerows(all_expenses)
    
    print(f"Combined {len(all_expenses)} total expense records in {output_file}")
    
    # Summary analysis
    internet_total = sum(float(e['amount']) for e in formatted_internet_expenses)
    existing_total = sum(float(e['amount']) for e in existing_expenses)
    combined_total = internet_total + existing_total
    
    print(f"\nExpense Analysis Update:")
    print(f"Previous Total: ${existing_total:,.2f}")
    print(f"Internet Expenses: ${internet_total:,.2f}")
    print(f"New Combined Total: ${combined_total:,.2f}")
    
    # Breakdown by business
    business_totals = {}
    for expense in all_expenses:
        business = expense.get('business', 'Uncategorized')
        amount = float(expense['amount'])
        business_totals[business] = business_totals.get(business, 0) + amount
    
    print(f"\nUpdated Business Allocation:")
    for business, total in sorted(business_totals.items(), key=lambda x: x[1], reverse=True):
        print(f"  {business}: ${total:,.2f}")
    
    # Show internet expense details
    print(f"\nInternet Expenses Added:")
    for expense in formatted_internet_expenses:
        print(f"  {expense['date']}: ${expense['amount']:,.2f} - {expense['vendor']} ({expense['source']})")
    
    return all_expenses

def main():
    add_internet_expenses_to_analysis()

if __name__ == "__main__":
    main()