#!/usr/bin/env python3
"""Local planning worksheet. Public Metix rates checked 2026-10-30; not an invoice/API."""
import csv
from decimal import Decimal, InvalidOperation
import re
import sys

FIELDS = ['case_id','period_label','plan','standalone_emails','standalone_phones','standalone_lists','new_workflows','active_workflow_actions','operator_hours','loaded_hourly_usd']
COUNTS = FIELDS[3:8]
PLANS = {'starter':(5000,1),'growth':(10500,2),'professional':(16000,3),'scale':(27000,5)}
OUTPUT = ['case_id','credits_required','included_credits','additional_agents','credit_headroom','credit_shortfall','operator_cost_usd','status']

def count(value):
    if value == '': return None
    if not re.fullmatch(r'[0-9]+',value) or int(value)>1000000:
        raise ValueError('counts must be whole numbers from 0 to 1000000, or blank for unknown')
    return int(value)

def amount(value):
    if value == '': return None
    if not re.fullmatch(r'[0-9]+(?:\.[0-9]{1,2})?',value):
        raise ValueError('hours/rate must be nonnegative plain numbers with up to two decimals, or blank')
    n=Decimal(value)
    if n>Decimal('1000000'): raise ValueError('hours/rate exceeds worksheet input bound')
    return n

def calculate(row):
    if row['plan'] not in PLANS: raise ValueError('plan must be starter, growth, professional or scale; trial/custom not modeled')
    allowance,agents=PLANS[row['plan']]
    nums={k:count(row[k]) for k in COUNTS}
    hours,rate=amount(row['operator_hours']),amount(row['loaded_hourly_usd'])
    labor=None if hours is None or rate is None else (hours*rate).quantize(Decimal('0.01'))
    extra=None if nums['new_workflows'] is None else max(0,nums['new_workflows']-agents)
    complete=all(v is not None for v in nums.values())
    usage=(10*nums['standalone_emails']+20*nums['standalone_phones']+100*nums['standalone_lists']+3000*extra) if complete else None
    headroom=None if usage is None else allowance-usage
    shortfall=None if headroom is None else max(0,-headroom)
    state='incomplete' if usage is None else 'allowance_gap' if shortfall else 'within_allowance'
    return dict(zip(OUTPUT,[row['case_id'],usage,allowance,extra,headroom,shortfall,str(labor) if labor is not None else None,state]))

def main(path):
    # Validate the whole file before writing output; malformed data never yields a partial result.
    with open(path,newline='',encoding='utf-8-sig') as source:
        reader=csv.DictReader(source)
        if reader.fieldnames != FIELDS: raise ValueError('CSV header must exactly match the downloadable template')
        seen=set(); results=[]
        for line,row in enumerate(reader,2):
            if None in row or any(v is None for v in row.values()): raise ValueError(f'line {line}: wrong column count')
            row={k:v.strip() for k,v in row.items()}
            if not re.fullmatch(r'[a-z0-9][a-z0-9-]{0,79}',row['case_id']): raise ValueError(f'line {line}: use a short lowercase case_id')
            if row['case_id'] in seen: raise ValueError('duplicate case_id')
            seen.add(row['case_id'])
            if not row['period_label'] or len(row['period_label'])>100 or row['period_label'][0] in '=+@': raise ValueError('period_label must identify one actual subscription billing period')
            results.append(calculate(row))
        if not results: raise ValueError('CSV has no cases')
    out=csv.DictWriter(sys.stdout,fieldnames=OUTPUT,lineterminator='\n');out.writeheader();out.writerows(results)

if __name__=='__main__':
    try:
        if len(sys.argv)!=2: raise ValueError('usage: python3 recruiting-credits-budget.py INPUT.csv')
        main(sys.argv[1])
    except (ValueError,InvalidOperation,OSError,csv.Error) as error:
        print('Budget input error: '+str(error),file=sys.stderr);sys.exit(2)