import csv
import re
from pathlib import Path

# Read the CSV data embedded in the input.md
input_file = Path("/tmp/cw/v4/runs/haiku/input.md")
content = input_file.read_text()

# Extract CSV section
csv_start = content.find("```csv") + 6
csv_end = content.find("```", csv_start)
csv_data = content[csv_start:csv_end].strip()

# Parse CSV
rows = list(csv.DictReader(csv_data.strip().split('\n')))

# Track exceptions
exceptions = []
cleaned_rows = []

# Process each row
for row in rows:
    source_row = row['source_row']
    cleaned_row = dict(row)  # Start with original
    
    # Track original values
    original_person = row['person']
    original_cost_center = row['cost_center']
    original_amount = row['amount']
    original_date = row['date']
    original_currency = row['currency']
    
    # Initialize normalized fields
    person_normalized = None
    cost_center_normalized = None
    amount_normalized = None
    amount_sign_normalized = None
    date_normalized = None
    currency_normalized = None
    
    # Rule 1: Trim person name whitespace
    if original_person and original_person.strip() != original_person:
        person_normalized = original_person.strip()
        exceptions.append({
            'source_row': source_row,
            'expense_id': row['expense_id'],
            'issue': 'Person name has leading/trailing whitespace',
            'original_value': repr(original_person),
            'treatment': f'Trimmed to: {person_normalized}',
            'decision_needed': 'None - safe trim applied'
        })
    else:
        person_normalized = original_person
    
    # Rule 2: Uppercase cost center codes
    if original_cost_center and original_cost_center != original_cost_center.upper():
        cost_center_normalized = original_cost_center.upper()
        exceptions.append({
            'source_row': source_row,
            'expense_id': row['expense_id'],
            'issue': 'Cost center code not uppercase',
            'original_value': original_cost_center,
            'treatment': f'Uppercased to: {cost_center_normalized}',
            'decision_needed': 'None - safe transform applied'
        })
    else:
        cost_center_normalized = original_cost_center
    
    # Rule 3: Handle dates
    if original_date:
        if re.match(r'^\d{4}-\d{2}-\d{2}$', original_date):
            date_normalized = original_date
        elif re.match(r'^\d{2}/\d{2}/\d{4}$', original_date):
            exceptions.append({
                'source_row': source_row,
                'expense_id': row['expense_id'],
                'issue': 'Date format MM/DD/YYYY or DD/MM/YYYY ambiguous; locale unknown',
                'original_value': original_date,
                'treatment': 'Left unchanged in original_date field',
                'decision_needed': 'Confirm date locale and convert to YYYY-MM-DD'
            })
            date_normalized = None
        else:
            date_normalized = original_date
    
    # Rule 4: Handle currency
    if original_currency == '':
        exceptions.append({
            'source_row': source_row,
            'expense_id': row['expense_id'],
            'issue': 'Currency field is empty',
            'original_value': '(empty)',
            'treatment': 'Left unchanged; amount not normalized',
            'decision_needed': 'Confirm currency for amount ' + original_amount
        })
        currency_normalized = None
    else:
        currency_normalized = original_currency
    
    # Rule 5: Handle amounts
    if original_amount:
        # Check for refund in parentheses
        if original_amount.startswith('(') and original_amount.endswith(')'):
            # This is a refund - convert to negative
            inner = original_amount[1:-1]  # Remove parentheses
            try:
                amount_value = float(inner.replace(',', ''))
                amount_normalized = -amount_value
                amount_sign_normalized = f"-{inner}"
                exceptions.append({
                    'source_row': source_row,
                    'expense_id': row['expense_id'],
                    'issue': 'Amount shown in parentheses (refund)',
                    'original_value': original_amount,
                    'treatment': f'Normalized to: {amount_normalized}',
                    'decision_needed': 'None - refund indicator converted to negative sign'
                })
            except ValueError:
                exceptions.append({
                    'source_row': source_row,
                    'expense_id': row['expense_id'],
                    'issue': 'Amount in parentheses but value not numeric',
                    'original_value': original_amount,
                    'treatment': 'Left unchanged',
                    'decision_needed': 'Clarify amount value'
                })
        # Check for USD with comma thousands separator
        elif currency_normalized == 'USD' and ',' in original_amount and '.' in original_amount:
            # Standard US format: 1,250.00
            try:
                amount_normalized = float(original_amount.replace(',', ''))
                amount_sign_normalized = original_amount
            except ValueError:
                exceptions.append({
                    'source_row': source_row,
                    'expense_id': row['expense_id'],
                    'issue': 'USD amount with comma separator cannot be parsed',
                    'original_value': original_amount,
                    'treatment': 'Left unchanged',
                    'decision_needed': 'Verify amount format'
                })
        # Check for European format (comma decimal, dot thousands)
        elif currency_normalized == 'EUR' and ',' in original_amount and '.' in original_amount:
            # Likely European: 1.234,50
            exceptions.append({
                'source_row': source_row,
                'expense_id': row['expense_id'],
                'issue': 'Amount locale unknown (could be European 1.234,50 format)',
                'original_value': original_amount,
                'treatment': 'Left unchanged in original_amount field',
                'decision_needed': 'Confirm amount locale and convert to standard format'
            })
        # Check for simple numeric values
        elif re.match(r'^[\d.]+$', original_amount):
            try:
                amount_normalized = float(original_amount)
                amount_sign_normalized = original_amount
            except ValueError:
                pass
        # Non-numeric values (TBC, etc.)
        elif original_amount == 'TBC':
            exceptions.append({
                'source_row': source_row,
                'expense_id': row['expense_id'],
                'issue': 'Amount is TBC (To Be Confirmed) - awaiting invoice',
                'original_value': original_amount,
                'treatment': 'Left unchanged',
                'decision_needed': 'Provide final amount when invoice received'
            })
        else:
            exceptions.append({
                'source_row': source_row,
                'expense_id': row['expense_id'],
                'issue': 'Amount value cannot be parsed',
                'original_value': original_amount,
                'treatment': 'Left unchanged',
                'decision_needed': 'Clarify amount value'
            })
    
    # Add normalized fields to cleaned row
    cleaned_row['person_normalized'] = person_normalized
    cleaned_row['cost_center_normalized'] = cost_center_normalized
    cleaned_row['date_normalized'] = date_normalized if date_normalized else ''
    cleaned_row['currency_normalized'] = currency_normalized if currency_normalized else ''
    cleaned_row['amount_normalized'] = str(amount_normalized) if amount_normalized is not None else ''
    
    cleaned_rows.append(cleaned_row)

# Check for exact duplicates (after normalized trim)
for i in range(len(cleaned_rows)):
    for j in range(i + 1, len(cleaned_rows)):
        row_i = cleaned_rows[i]
        row_j = cleaned_rows[j]
        
        # Check if all key fields match (except source_row)
        if (row_i['expense_id'] == row_j['expense_id'] and
            row_i['date'] == row_j['date'] and
            row_i['person_normalized'] == row_j['person_normalized'] and
            row_i['cost_center_normalized'] == row_j['cost_center_normalized'] and
            row_i['currency_normalized'] == row_j['currency_normalized'] and
            row_i['amount'] == row_j['amount'] and
            row_i['category'] == row_j['category'] and
            row_i['note'] == row_j['note']):
            
            exceptions.append({
                'source_row': row_j['source_row'],
                'expense_id': row_j['expense_id'],
                'issue': f"Exact duplicate of source_row {row_i['source_row']}",
                'original_value': f"All fields match row {row_i['source_row']}",
                'treatment': 'Retained both records; duplicate flagged for review',
                'decision_needed': 'Determine if one should be removed or if both are needed'
            })

# Check for conflicting records (same ID, different amounts)
id_amounts = {}
for row in cleaned_rows:
    exp_id = row['expense_id']
    amount = row['amount']
    if exp_id not in id_amounts:
        id_amounts[exp_id] = []
    id_amounts[exp_id].append((row['source_row'], amount))

for exp_id, records in id_amounts.items():
    amounts = set(r[1] for r in records)
    if len(amounts) > 1:
        # Conflicting amounts for same ID
        for source_row, amount in records:
            found = False
            for exc in exceptions:
                if exc['source_row'] == source_row and 'conflicting' in exc['issue'].lower():
                    found = True
                    break
            if not found:
                exceptions.append({
                    'source_row': source_row,
                    'expense_id': exp_id,
                    'issue': f"Expense ID {exp_id} has conflicting amounts: {', '.join(sorted(amounts))}",
                    'original_value': amount,
                    'treatment': 'Retained both records with different amounts',
                    'decision_needed': 'Reconcile conflicting amounts and determine correct value'
                })

# Write cleaned.csv
with open('/tmp/cw/v4/runs/haiku/cleaned.csv', 'w', newline='') as f:
    fieldnames = ['source_row', 'expense_id', 'date', 'person', 'cost_center', 'currency', 'amount', 
                  'person_normalized', 'cost_center_normalized', 'date_normalized', 'currency_normalized', 
                  'amount_normalized', 'category', 'note']
    writer = csv.DictWriter(f, fieldnames=fieldnames)
    writer.writeheader()
    for row in cleaned_rows:
        out_row = {k: row.get(k, '') for k in fieldnames}
        writer.writerow(out_row)

# Write exceptions.csv
with open('/tmp/cw/v4/runs/haiku/exceptions.csv', 'w', newline='') as f:
    fieldnames = ['source_row', 'expense_id', 'issue', 'original_value', 'treatment', 'decision_needed']
    writer = csv.DictWriter(f, fieldnames=fieldnames)
    writer.writeheader()
    for exc in exceptions:
        writer.writerow(exc)

print("Files created successfully")
print(f"Cleaned rows: {len(cleaned_rows)}")
print(f"Exception records: {len(exceptions)}")