#!/usr/bin/env python3

import csv

def create_final_categorization_file():
    """Create comprehensive file with Keith's decisions applied"""
    
    # Keith's categorization decisions so far
    keith_decisions = {
        'Edwards Jet Center': 'Space Monkey Partners',
        'Verizon': 'Space Monkey Partners', 
        'Storybrand Don Miller': 'Atomic Elevator',
        'Department of Education Educ': 'Personal',
        'Hubspot Inc': 'Atomic Elevator',
        'Amazon': 'Space Monkey Partners',
        'West Clinic': 'Personal',
        'Divers Den Investments': 'Personal',
        'Venetian Palazzo Room': 'Space Monkey Partners',
        'Beartooth Market': 'Space Monkey Partners',
        'The Spoke Shop': 'Space Monkey Partners',
        'Res': 'Space Monkey Partners',
        'Candrug': 'Personal',
        'SSBTRUSTOPS P/R Contr': 'Space Monkey Partners',
        'The Pilot Shop': 'Space Monkey Partners',
        'Fathom Video': 'Atomic Elevator',
        'Ace Hardware': 'Atomic Elevator',
        'Nespresso': 'Space Monkey Partners',
        'The Local West End': 'Atomic Elevator',
        'Yellowstone Wildlife': 'Personal',
        'Exxon Mobil': 'Space Monkey Partners',
    }
    
    # Read the original review file
    vendors_data = []
    with open('EXPENSES_FOR_KEITH_REVIEW.csv', 'r', encoding='utf-8') as f:
        reader = csv.DictReader(f)
        vendors_data = list(reader)
    
    print(f"Loaded {len(vendors_data)} vendors from review file")
    
    # Apply Keith's decisions and create final file
    categorized_count = 0
    remaining_count = 0
    categorized_amount = 0
    remaining_amount = 0
    
    for vendor in vendors_data:
        vendor_name = vendor['vendor'].strip()
        amount = float(vendor['total_amount'])
        
        # Apply Keith's decision if we have one
        if vendor_name in keith_decisions:
            vendor['keith_decision'] = keith_decisions[vendor_name]
            categorized_count += 1
            categorized_amount += amount
        else:
            vendor['keith_decision'] = ''  # Empty for Keith to fill
            remaining_count += 1
            remaining_amount += amount
    
    # Sort by total amount (highest first) to prioritize Keith's time
    vendors_data.sort(key=lambda x: float(x['total_amount']), reverse=True)
    
    # Create final categorization file
    output_file = 'KEITH_FINAL_EXPENSE_CATEGORIZATION.csv'
    with open(output_file, 'w', newline='', encoding='utf-8') as f:
        fieldnames = [
            'vendor',
            'total_amount', 
            'transaction_count',
            'sample_date',
            'sample_amount',
            'sample_description',
            'keith_decision'
        ]
        writer = csv.DictWriter(f, fieldnames=fieldnames)
        writer.writeheader()
        
        for vendor in vendors_data:
            writer.writerow({
                'vendor': vendor['vendor'],
                'total_amount': vendor['total_amount'],
                'transaction_count': vendor['transaction_count'],
                'sample_date': vendor['sample_date'],
                'sample_amount': vendor['sample_amount'],
                'sample_description': vendor['sample_description'],
                'keith_decision': vendor['keith_decision']
            })
    
    print(f"Created {output_file}")
    print(f"\nProgress Summary:")
    print(f"Categorized: {categorized_count} vendors (${categorized_amount:,.2f})")
    print(f"Remaining: {remaining_count} vendors (${remaining_amount:,.2f})")
    
    # Show next 20 vendors Keith needs to categorize
    print(f"\nNext 20 vendors Keith needs to categorize:")
    count = 0
    for vendor in vendors_data:
        if not vendor['keith_decision']:
            count += 1
            if count <= 20:
                print(f"{count:2d}. {vendor['vendor']:<35} ${float(vendor['total_amount']):>8,.2f} ({vendor['transaction_count']} transactions)")
            else:
                break
    
    print(f"\nFILE READY FOR DOWNLOAD: {output_file}")
    print(f"Instructions: Fill in 'keith_decision' column with:")
    print(f"  - Personal")
    print(f"  - Space Monkey Partners") 
    print(f"  - Atomic Elevator")

def main():
    create_final_categorization_file()

if __name__ == "__main__":
    main()