#!/usr/bin/env python3
"""Local budget worksheet. No network, vendor access, purchase or quote validation."""
import csv
import sys
from decimal import Decimal, InvalidOperation

FIELDS = ["case_id", "plan", "seats", "rate_basis", "seat_rate_usd_month", "boost2_seats", "boost5_seats", "boost_fee_usd_month_total", "migration_usd", "parallel_tools_usd_year", "internal_hours", "hourly_rate_usd", "source_ref"]
OUTPUT = ["case_id", "budget_basis", "annual_base_usd", "annual_boost_usd", "monthly_pooled_credits", "first_year_cash_usd", "internal_effort_usd", "first_year_with_effort_usd", "missing_inputs"]

def number(row, key, integer=False):
    value = row[key].strip()
    if not value:
        return None
    try:
        n = Decimal(value)
    except InvalidOperation:
        raise ValueError(f"{row['case_id']}: {key} must be a number or blank")
    if not n.is_finite() or n < 0 or (integer and n != n.to_integral_value()):
        raise ValueError(f"{row['case_id']}: invalid {key}")
    return int(n) if integer else n

def money(n):
    return "unknown" if n is None else str(n.quantize(Decimal("0.01")))

def calculate(row):
    if row['plan'] not in ('Core', 'Professional'):
        raise ValueError('plan must be Core or Professional')
    if row['rate_basis'] not in ('public_starting', 'user_quote', 'illustrative', 'unknown'):
        raise ValueError('invalid rate_basis')
    seats = number(row, 'seats', True)
    b2 = number(row, 'boost2_seats', True)
    b5 = number(row, 'boost5_seats', True)
    if seats is None or seats < 1 or b2 is None or b5 is None or b2 + b5 > seats:
        raise ValueError('positive paid seats and non-overlapping Boost seat counts required')
    rate = number(row, 'seat_rate_usd_month')
    if row['rate_basis'] == 'public_starting' and (row['plan'] != 'Core' or rate != Decimal('149')):
        raise ValueError('2026-10-29 public starting reference is Core149 only; use a sourced quote for other rates')
    if row['rate_basis'] == 'unknown' and rate is not None:
        raise ValueError('unknown basis must leave seat rate blank')
    if row['rate_basis'] != 'unknown' and (rate is None or not row['source_ref'].strip()):
        raise ValueError('a known rate requires its basis and source_ref')
    if row['plan'] == 'Core' and (b2 or b5):
        raise ValueError('this worksheet models Boosts only on Professional')
    boost = number(row, 'boost_fee_usd_month_total')
    if b2 + b5 == 0:
        if boost not in (None, Decimal('0')):
            raise ValueError('no Boost selected, so leave its fee blank or0')
        boost = Decimal('0')
    migration = number(row, 'migration_usd')
    overlap = number(row, 'parallel_tools_usd_year')
    hours = number(row, 'internal_hours')
    hourly = number(row, 'hourly_rate_usd')
    base = None if rate is None else rate * seats * 12
    annual_boost = None if boost is None else boost * 12
    cash = None if any(x is None for x in (base, annual_boost, migration, overlap)) else base + annual_boost + migration + overlap
    effort = None if hours is None or hourly is None else hours * hourly
    total = None if cash is None or effort is None else cash + effort
    missing = [name for name, value in [('seat_rate_usd_month', rate), ('boost_fee_usd_month_total', boost), ('migration_usd', migration), ('parallel_tools_usd_year', overlap), ('internal_hours', hours), ('hourly_rate_usd', hourly)] if value is None]
    pool = str(seats * 2500 + b2 * 2500 + b5 * 10000) if row['plan'] == 'Professional' else 'not_modelled'
    basis = {'public_starting': 'public_floor_plus_user_inputs', 'user_quote': 'user_supplied_quote_not_verified', 'illustrative': 'hypothetical_not_a_loxo_offer', 'unknown': 'incomplete_no_subscription_quote'}[row['rate_basis']]
    return dict(zip(OUTPUT, [row['case_id'], basis, money(base), money(annual_boost), pool, money(cash), money(effort), money(total), '|'.join(missing)]))

def main():
    if len(sys.argv) != 2:
        raise ValueError('Usage: python3 loxo-budget.py INPUT.csv')
    with open(sys.argv[1], newline='', encoding='utf-8') as stream:
        reader = csv.DictReader(stream)
        if reader.fieldnames != FIELDS:
            raise ValueError('CSV header must exactly match the supplied template')
        rows = list(reader)
    results = []
    seen = set()
    for row in rows:
        if None in row or any(value is None for value in row.values()):
            raise ValueError('every row must have exactly the header column count')
        case = row['case_id'].strip()
        if not case or case in seen:
            raise ValueError('case_id must be present and unique')
        seen.add(case)
        results.append(calculate(row))
    writer = csv.DictWriter(sys.stdout, fieldnames=OUTPUT, lineterminator='\n')
    writer.writeheader()
    writer.writerows(results)

if __name__ == '__main__':
    try:
        main()
    except (ValueError, OSError) as error:
        print(str(error), file=sys.stderr)
        sys.exit(2)