#!/usr/bin/env python3
# SPDX-License-Identifier: MIT
"""Independent read-only native-ledger verifier, date/milestone selector and export."""
import argparse
from datetime import datetime, timezone, time as daytime
from decimal import Decimal, getcontext
import hashlib
import html
import json
from pathlib import Path
import sqlite3

getcontext().prec = 50
D = Decimal


def canonical(value):
    return json.dumps(value, sort_keys=True, separators=(',', ':'), allow_nan=False)


def open_readonly(path):
    db = sqlite3.connect(Path(path).resolve().as_uri() + '?mode=ro', uri=True, timeout=0.2)
    db.execute('PRAGMA query_only=ON')
    db.execute('BEGIN')
    return db


def verify(db):
    previous = '0' * 64
    count, postings, lesson_count = 0, 0, 0
    balances = {}
    parent_lesson = None
    receipt_ids = set()
    last_book = None
    last_peak = None
    last_ratchet = None
    tables = {r[0] for r in db.execute("SELECT name FROM sqlite_master WHERE type='table'")}
    for event_id,at,kind,body,parent,digest in db.execute('SELECT id,at,kind,body,parent,hash FROM events ORDER BY id'):
        if parent != previous or hashlib.sha256(canonical([at,kind,body,parent]).encode()).hexdigest() != digest:
            raise ValueError('event_hash_chain_mismatch')
        item = json.loads(body)
        lines = item.get('journal', [])
        if 'journal_lines' in tables:
            physical = [{'account':a,'debit':d,'credit':c,'memo':m} for a,d,c,m in db.execute(
                'SELECT account,debit,credit,memo FROM journal_lines WHERE event_id=? ORDER BY seq',(event_id,))]
            if physical != lines:
                raise ValueError('physical_journal_mismatch')
        debit = credit = D('0')
        for line in lines:
            d,c = D(line['debit']),D(line['credit'])
            if not d.is_finite() or not c.is_finite() or min(d,c)<0 or (d and c):
                raise ValueError('invalid_posting')
            debit += d; credit += c
            balances[line['account']] = balances.get(line['account'],D('0')) + d-c
            postings += 1
        if debit != credit:
            raise ValueError('unbalanced_event')
        if 'snapshot' in item:
            last_peak, last_ratchet = item.get('peak'),item.get('milestone_floor')
        accounting = item.get('accounting')
        if accounting:
            if D(accounting['debit_total']) != debit or D(accounting['credit_total']) != credit:
                raise ValueError('declared_posting_total_mismatch')
            book = accounting['book_after']
            last_book = book
            for account,expected in [('Assets:Cash',D(book['cash'])),('Assets:Inventory',D(book['cost'])),
                ('Assets:ValuationAdjustment',D(book['value'])-D(book['cost']))]:
                if balances.get(account,D('0')) != expected:
                    raise ValueError('book_to_trial_balance_mismatch')
        if kind == 'LESSON':
            physical = db.execute('SELECT body FROM lessons WHERE event_id=?',(event_id,)).fetchone()
            if not physical or json.loads(physical[0]) != item:
                raise ValueError('physical_lesson_mismatch')
            if 'lesson_hash' in item:
                without = {k:v for k,v in item.items() if k!='lesson_hash'}
                if item.get('parent_lesson_hash') != parent_lesson or hashlib.sha256(canonical(without).encode()).hexdigest() != item['lesson_hash']:
                    raise ValueError('lesson_ancestry_mismatch')
                parent_lesson = item['lesson_hash']
            lesson_count += 1
        if kind == 'EXECUTION_RECEIPT':
            receipt_ids.add(item['broker_id'])
            row = db.execute('SELECT body FROM receipts WHERE broker_id=?',(item['broker_id'],)).fetchone()
            if not row or any(item.get(k)!=v for k,v in json.loads(row[0]).items()):
                raise ValueError('physical_execution_receipt_mismatch')
        previous = digest
        count += 1
    if db.execute('SELECT count(*) FROM lessons').fetchone()[0] != lesson_count:
        raise ValueError('orphan_lesson_rows')
    if db.execute('SELECT count(*) FROM receipts').fetchone()[0] != len(receipt_ids):
        raise ValueError('orphan_execution_receipt_rows')
    state = {k:json.loads(v) for k,v in db.execute('SELECT k,v FROM state')}
    if last_book is not None and state.get('book') != last_book:
        raise ValueError('durable_book_state_mismatch')
    for key,expected in [('peak',last_peak),('ratchet',last_ratchet)]:
        if expected is not None and D(state.get(key,'0')) != D(expected):
            raise ValueError('durable_risk_state_mismatch')
    if db.execute('PRAGMA foreign_key_check').fetchone() is not None:
        raise ValueError('orphan_posting_rows')
    if db.execute('PRAGMA quick_check').fetchone()[0] != 'ok':
        raise ValueError('sqlite_integrity_failure')
    return {'verified':True,'events':count,'postings':postings,'lessons':lesson_count,'tip_hash':previous,
        'trial_balance':{k:format(v,'f') for k,v in balances.items()},
        'scope':'Internal management ledger; no external anchor or regulatory certification'}


def snapshot(path, event_id=None, date=None, milestone=None, after=None, limit=256):
    db = open_readonly(path)
    try:
        args=[]; where=[]
        if event_id is not None:
            where.append('id<=?'); args.append(event_id)
        if date:
            parsed=datetime.fromisoformat(date.replace('Z','+00:00'))
            if len(date)==10: parsed=datetime.combine(parsed.date(),daytime.max,tzinfo=timezone.utc)
            if parsed.tzinfo is None: raise ValueError('timezone_required')
            where.append('at<=?'); args.append(parsed.astimezone(timezone.utc).isoformat())
        clause=(' WHERE '+' AND '.join(where)) if where else ''
        latest=db.execute('SELECT coalesce(max(id),0) FROM events').fetchone()[0]
        eligible=db.execute('SELECT coalesce(max(id),0) FROM events'+clause,args).fetchone()[0]
        end=eligible
        if milestone is not None:
            threshold=D(str(milestone))
            if not threshold.is_finite() or threshold<=0: raise ValueError('positive_milestone_required')
            end=0
            for eid,body in db.execute('SELECT id,body FROM events'+clause+' ORDER BY id',args):
                candidate=json.loads(body)
                if 'snapshot' in candidate and D(candidate.get('peak','0'))>=threshold:
                    end=eid
                    break
        rows=db.execute('SELECT id,at,kind,body FROM events WHERE id<=? ORDER BY id DESC',(end,))
        selected=None
        chart=[]
        for eid,at,kind,body in rows:
            item=json.loads(body)
            if 'snapshot' not in item: continue
            if selected is None: selected={'id':eid,'at':at,'kind':kind,**item}
            chart.append({'id':eid,'at':at,'equity':item['snapshot']['equity'],'floor':item['floor']})
            if len(chart)>=1000: break
        events=[{'id':eid,'at':at,'kind':kind,'body':json.loads(body)} for eid,at,kind,body in db.execute(
            'SELECT id,at,kind,body FROM events WHERE id<=? ORDER BY id DESC LIMIT ?',(end,limit))]
        lessons=[{'id':eid,'at':at,**json.loads(body)} for eid,at,body in db.execute(
            "SELECT id,at,body FROM events WHERE kind='LESSON' AND id<=? ORDER BY id DESC LIMIT 200",(end,))]
        stream=[]
        if after is not None:
            stream=[{'id':eid,'at':at,'kind':kind,'body':json.loads(body)} for eid,at,kind,body in db.execute(
                'SELECT id,at,kind,body FROM events WHERE id>? ORDER BY id LIMIT ?',(after,limit))]
        halt=db.execute("SELECT at,kind,body FROM events WHERE kind IN ('HALT','OPERATOR_REARM') AND id<=? ORDER BY id DESC LIMIT 1",(end,)).fetchone()
        halt_value=({'at':halt[0],'reason':json.loads(halt[2])['reason']} if halt and halt[1]=='HALT' else None)
        return {'latest_id':latest,'selected':selected,'chart':list(reversed(chart)),
            'events':list(reversed(events)),'lessons':lessons,'stream':stream,
            'halt':halt_value,
            'limits':{'events':limit,'lessons':200,'chart':1000},'database':'native-live.sqlite3'}
    finally: db.close()


def diagnostic(path, output, metadata):
    db=open_readonly(path)
    try: proof=verify(db)
    finally: db.close()
    data=snapshot(path)
    report={'verification':proof,'metadata':metadata,'snapshot':data['selected']}
    text='<!doctype html><meta charset="utf-8"><title>AlphaBot independent diagnostic</title>'
    text+='<meta http-equiv="Content-Security-Policy" content="default-src \'none\'">'
    text+='<h1>AlphaBot pre-entry diagnostic</h1><pre>'+html.escape(json.dumps(report,indent=2))+'</pre>'
    # Exclusive create: diagnostics can never overwrite source or the live database.
    with Path(output).open('x') as stream: stream.write(text)
    return proof


def main():
    parser=argparse.ArgumentParser(description=__doc__)
    parser.add_argument('--project',required=True,type=Path)
    parser.add_argument('--verify',action='store_true')
    parser.add_argument('--date')
    parser.add_argument('--milestone',type=Decimal)
    parser.add_argument('--event-id',type=int)
    parser.add_argument('--format',choices=('json','html'),default='json')
    parser.add_argument('--output',type=Path)
    args=parser.parse_args()
    path=args.project/'native-live.sqlite3'
    if args.verify:
        db=open_readonly(path)
        try: result=verify(db)
        finally: db.close()
    else:
        result=snapshot(path,args.event_id,args.date,args.milestone)
    content=json.dumps(result,indent=2)
    if args.format=='html':
        content='<!doctype html><meta charset="utf-8"><title>AlphaBot audit snapshot</title><pre>'+html.escape(content)+'</pre>'
    if args.output:
        with args.output.open('x') as stream: stream.write(content+'\n')
    else: print(content)


if __name__=='__main__': main()