#!/usr/bin/env python3

import csv
from collections import defaultdict

def prepare_categorization_review():
    """Create review files for Keith to properly categorize expenses"""
    
    # Read the complete expenses
    all_expenses = []
    with open('COMPLETE_EXPENSES_WITH_INTERNET_2025-2026.csv', 'r', encoding='utf-8') as f:
        reader = csv.DictReader(f)
        all_expenses = list(reader)
    
    print(f"Loaded {len(all_expenses)} total expense records")
    
    # Separate expenses by current business allocation
    review_needed = []
    already_categorized = []
    
    for expense in all_expenses:
        if expense['business'] == 'Review Needed':
            review_needed.append(expense)
        else:
            already_categorized.append(expense)
    
    print(f"Review needed: {len(review_needed)} records (${sum(float(e['amount']) for e in review_needed):,.2f})")
    print(f"Already categorized: {len(already_categorized)} records")
    
    # Group review_needed by vendor for easier categorization
    vendor_groups = defaultdict(list)
    for expense in review_needed:
        vendor = expense['vendor'].strip()
        vendor_groups[vendor].append(expense)
    
    # Create review file sorted by total amount per vendor (biggest first)
    vendor_totals = []
    for vendor, expenses in vendor_groups.items():
        total_amount = sum(float(e['amount']) for e in expenses)
        count = len(expenses)
        vendor_totals.append({
            'vendor': vendor,
            'count': count,
            'total_amount': total_amount,
            'expenses': expenses
        })
    
    # Sort by total amount (descending)
    vendor_totals.sort(key=lambda x: x['total_amount'], reverse=True)
    
    # Create the main review file
    with open('EXPENSES_FOR_KEITH_REVIEW.csv', 'w', newline='', encoding='utf-8') as f:
        fieldnames = ['vendor', 'transaction_count', 'total_amount', 'sample_date', 'sample_amount', 'sample_description', 'suggested_business', 'keith_decision']
        writer = csv.DictWriter(f, fieldnames=fieldnames)
        writer.writeheader()
        
        for vendor_data in vendor_totals:
            expenses = vendor_data['expenses']
            sample = expenses[0]  # Use first transaction as sample
            
            # Try to suggest a business based on vendor name patterns
            suggested_business = suggest_business_allocation(vendor_data['vendor'])
            
            writer.writerow({
                'vendor': vendor_data['vendor'],
                'transaction_count': vendor_data['count'],
                'total_amount': f"{vendor_data['total_amount']:.2f}",
                'sample_date': sample['date'],
                'sample_amount': sample['amount'],
                'sample_description': sample.get('category', ''),
                'suggested_business': suggested_business,
                'keith_decision': ''  # Empty for Keith to fill
            })
    
    print(f"Created EXPENSES_FOR_KEITH_REVIEW.csv with {len(vendor_totals)} unique vendors")
    
    # Create a detailed breakdown file
    with open('DETAILED_EXPENSES_FOR_REVIEW.csv', 'w', newline='', encoding='utf-8') as f:
        fieldnames = ['date', 'amount', 'vendor', 'category', 'cardholder', 'source', 'keith_business_allocation']
        writer = csv.DictWriter(f, fieldnames=fieldnames)
        writer.writeheader()
        
        for expense in sorted(review_needed, key=lambda x: (x['vendor'], x['date'])):
            writer.writerow({
                'date': expense['date'],
                'amount': expense['amount'],
                'vendor': expense['vendor'],
                'category': expense.get('category', ''),
                'cardholder': expense.get('cardholder', ''),
                'source': expense['source'],
                'keith_business_allocation': ''  # Empty for Keith to fill
            })
    
    print(f"Created DETAILED_EXPENSES_FOR_REVIEW.csv with all {len(review_needed)} individual records")
    
    # Create examples file showing how similar vendors were already categorized
    create_categorization_examples(already_categorized)
    
    # Create summary stats
    create_review_summary(vendor_totals, already_categorized)

def suggest_business_allocation(vendor_name):
    """Suggest business allocation based on vendor name patterns"""
    vendor_lower = vendor_name.lower()
    
    # Technology/Software patterns - likely Atomic Elevator
    tech_keywords = ['apple', 'google', 'microsoft', 'zoom', 'slack', 'github', 'aws', 'adobe', 'canva', 'software', 'tech', 'app', 'api', 'cloud', 'hosting']
    if any(keyword in vendor_lower for keyword in tech_keywords):
        return 'Atomic Elevator'
    
    # Marketing/Advertising patterns - likely Space Monkey Partners  
    marketing_keywords = ['facebook', 'meta', 'google ads', 'linkedin', 'twitter', 'social', 'marketing', 'advertising', 'campaign', 'media']
    if any(keyword in vendor_lower for keyword in marketing_keywords):
        return 'Space Monkey Partners'
    
    # Personal patterns
    personal_keywords = ['restaurant', 'grocery', 'gas station', 'personal', 'medical', 'pharmacy', 'retail']
    if any(keyword in vendor_lower for keyword in personal_keywords):
        return 'Personal'
    
    # Shared/Office patterns
    shared_keywords = ['office', 'utilities', 'internet', 'phone', 'travel', 'hotel', 'flight', 'uber', 'lyft']
    if any(keyword in vendor_lower for keyword in shared_keywords):
        return 'Shared'
    
    return 'Unknown - Keith Review'

def create_categorization_examples(already_categorized):
    """Show examples of how vendors were already categorized"""
    
    business_examples = defaultdict(list)
    for expense in already_categorized:
        if expense['business'] != 'Review Needed':
            business_examples[expense['business']].append(expense['vendor'])
    
    with open('CATEGORIZATION_EXAMPLES.txt', 'w', encoding='utf-8') as f:
        f.write("EXAMPLES OF HOW VENDORS ARE ALREADY CATEGORIZED\n")
        f.write("=" * 50 + "\n\n")
        
        for business, vendors in business_examples.items():
            unique_vendors = list(set(vendors))[:10]  # Show up to 10 unique examples
            f.write(f"{business}:\n")
            for vendor in unique_vendors:
                f.write(f"  - {vendor}\n")
            f.write("\n")

def create_review_summary(vendor_totals, already_categorized):
    """Create summary statistics"""
    
    with open('REVIEW_SUMMARY.txt', 'w', encoding='utf-8') as f:
        f.write("EXPENSE CATEGORIZATION REVIEW SUMMARY\n")
        f.write("=" * 40 + "\n\n")
        
        total_review_amount = sum(vt['total_amount'] for vt in vendor_totals)
        f.write(f"NEEDS REVIEW:\n")
        f.write(f"  Unique Vendors: {len(vendor_totals)}\n")
        f.write(f"  Total Amount: ${total_review_amount:,.2f}\n\n")
        
        f.write(f"TOP 20 VENDORS BY AMOUNT:\n")
        for i, vt in enumerate(vendor_totals[:20], 1):
            f.write(f"  {i:2d}. {vt['vendor']:<30} ${vt['total_amount']:>8,.2f} ({vt['count']} transactions)\n")
        
        f.write(f"\nALREADY CATEGORIZED:\n")
        business_totals = defaultdict(float)
        for expense in already_categorized:
            business_totals[expense['business']] += float(expense['amount'])
        
        for business, total in sorted(business_totals.items(), key=lambda x: x[1], reverse=True):
            f.write(f"  {business}: ${total:,.2f}\n")

def main():
    prepare_categorization_review()
    
    print("\nFiles created for Keith's review:")
    print("1. EXPENSES_FOR_KEITH_REVIEW.csv - Main review file (vendors grouped)")
    print("2. DETAILED_EXPENSES_FOR_REVIEW.csv - All individual transactions") 
    print("3. CATEGORIZATION_EXAMPLES.txt - Examples of existing categorizations")
    print("4. REVIEW_SUMMARY.txt - Summary statistics")
    print("\nRecommendation: Start with EXPENSES_FOR_KEITH_REVIEW.csv")

if __name__ == "__main__":
    main()