"""Read-only enterprise exception prioritization; no invented efficiency scores."""
import argparse
from datetime import datetime, timezone
from decimal import Decimal
import json
from pathlib import Path
import sqlite3


def due(value):
    d=datetime.fromisoformat(value.replace('Z','+00:00'))
    if d.tzinfo is None or d.utcoffset() is None:raise ValueError('Deadline needs a timezone offset')
    return d.astimezone(timezone.utc)


class PortfolioPerformanceRanker:
    def __init__(self,db_filepath='/Users/bpa/ForesixaQuant/qzotic_pure_sandbox.db'):
        path=Path(db_filepath).absolute()
        if not path.is_file() or path.is_symlink():raise ValueError('Existing regular database required')
        self.conn=sqlite3.connect(path.resolve().as_uri()+'?mode=ro',uri=True,timeout=5);self.conn.row_factory=sqlite3.Row

    def execute_portfolio_ranking_audit(self,current_time=None):
        current=current_time or datetime.now(timezone.utc)
        if current.tzinfo is None or current.utcoffset() is None:raise ValueError('Current time must be timezone-aware')
        try:
            self.conn.execute('BEGIN')
            cols={r[1]:r[2].upper() for r in self.conn.execute('PRAGMA table_info(enterprise_portfolio)')}
            if cols.get('allocated_budget_cents')!='INTEGER':raise ValueError('Current integer-cent portfolio schema required')
            rows=self.conn.execute('SELECT project_id,project_name,industry_vertical,lifecycle_state,supervisor_deadline,allocated_budget_cents FROM enterprise_portfolio').fetchall()
            units=[];excluded=[]
            for r in rows:
                if r['project_name'].upper().startswith('TEST_'):
                    excluded.append({'project_id':r['project_id'],'name':r['project_name'],'reason':'TEST_ name excluded from business totals; original database record preserved'});continue
                amount=r['allocated_budget_cents']
                if type(amount) is not int or not 0<=amount<=900000000000000000:raise ValueError('Invalid integer budget')
                item=dict(r);item['budget_usd']=format(Decimal(amount)/100,'.2f');item['overdue_seconds']=None
                try:
                    target=due(r['supervisor_deadline']);delta=(current-target).total_seconds()
                    item['deadline_status']='OVERDUE' if delta>0 else 'NOT_YET_DUE'
                    item['overdue_seconds']=max(0,int(delta))
                except (ValueError,TypeError,AttributeError):item['deadline_status']='NEEDS_DEADLINE_REVIEW'
                fix=self.conn.execute('SELECT execution_status,execution_evidence_log,migration_audit_notes FROM remediation_lifecycle WHERE associated_project=? ORDER BY remediation_id DESC LIMIT 1',(r['project_name'],)).fetchone()
                item['remediation']=dict(fix) if fix else None
                item['evidence_status']='NOT_RECORDED'
                if fix:
                    item['evidence_status']='RECORDED_NOT_INDEPENDENTLY_VERIFIED' if fix['execution_evidence_log'] else 'MISSING_EXECUTION_EVIDENCE'
                if item['deadline_status']=='NEEDS_DEADLINE_REVIEW':priority=0
                elif fix and fix['execution_status'] in ('UNKNOWN','FAILED'):priority=1
                elif r['lifecycle_state']=='ACTIVE' and item['deadline_status']=='OVERDUE':priority=2
                else:priority=3
                item['review_priority']=priority;units.append(item)
            units.sort(key=lambda u:(u['review_priority'], -(u['overdue_seconds'] or 0),u['project_name']))
            report={'scope':'Enterprise exception review order, not trading win-rate or measured efficiency','portfolio_summary':units,'excluded_test_records':excluded,'business_units':len(units),'active_units':sum(u['lifecycle_state']=='ACTIVE' for u in units),'budget_total_usd':format(Decimal(sum(u['allocated_budget_cents'] for u in units))/100,'.2f'),'notice':'Priority is an explicit review rule, not an effectiveness score. Budget allocations are not revenue/assets. Historical status and text are not independent outcome verification.'}
            self.conn.rollback();return report
        except BaseException:
            self.conn.rollback();raise

    def close(self):self.conn.close()


if __name__=='__main__':
    p=argparse.ArgumentParser();p.add_argument('--db',default='/Users/bpa/ForesixaQuant/qzotic_pure_sandbox.db');args=p.parse_args()
    ranker=None
    try:
        ranker=PortfolioPerformanceRanker(args.db);print(json.dumps(ranker.execute_portfolio_ranking_audit(),indent=2))
    except (sqlite3.Error,ValueError) as exc:
        print(json.dumps({'status':'FAIL','reason':str(exc)}));raise SystemExit(2)
    finally:
        if ranker:ranker.close()