"""Strict non-executable validation of format 8 portal/document history."""
import json
import math
import re
from datetime import datetime
from .sales_outputs import Settings,snapshot_hash


def validate(db,require,*,sanitised=False):
    def rows(sql,args=()):
        cursor=db.execute(sql,args);names=[d[0] for d in cursor.description]
        return [dict(zip(names,r)) for r in cursor]
    def stamp(value):
        try:return isinstance(value,str) and datetime.fromisoformat(value).tzinfo is not None
        except (ValueError,TypeError):return False
    def hid(v):return isinstance(v,str) and re.fullmatch(r'[a-f0-9]{32}',v) is not None
    customers={r['id']:r for r in rows('SELECT * FROM customers')}
    accounts={r['customer_id']:r for r in rows('SELECT * FROM portal_accounts')}
    require(len(rows('SELECT * FROM portal_migrations'))==1,'Portal identity migration marker is missing.')
    states=('pending','enabled','legacy','revoked')
    for cid,a in accounts.items():
        require(cid in customers and hid(a['portal_id']) and a['state'] in states and type(a['version']) is int and a['version']>=1 and stamp(a['created_at']) and stamp(a['updated_at']),'Invalid exact-customer portal account.')
        require(isinstance(a['delivery_email'],str) and len(a['delivery_email'])<=200,'Invalid captured portal address.')
        require(a['password']=='' if sanitised or a['state']!='enabled' else isinstance(a['password'],str) and re.fullmatch(r'[a-f0-9]{32}:[a-f0-9]{128}',a['password']) is not None,'Unexpected or unsafe portal credential.')
        if not sanitised and a['state']=='enabled':require(bool(customers[cid]['portal_hash']),'Enabled portal has no session grant.')
        if a['state'] in ('pending','revoked'):require(customers[cid]['portal_hash'] is None,'Revoked or pending portal has a grant.')
    tokens=rows('SELECT * FROM portal_tokens')
    if sanitised:require(not tokens,'Recovery archive contains portal setup or recovery tokens.')
    for t in tokens:
        require(hid(t['id']) and t['customer_id'] in accounts and t['kind'] in ('setup','reset','legacy') and re.fullmatch(r'[a-f0-9]{64}',t['token_hash']) is not None,'Invalid hashed portal token.')
        require(type(t['account_version']) is int and 1<=t['account_version']<=accounts[t['customer_id']]['version'] and isinstance(t['expires'],(int,float)) and math.isfinite(t['expires']) and stamp(t['created_at']) and (t['used_at'] is None or stamp(t['used_at'])),'Invalid portal token lifetime or version.')
    actions={'revoked','legacy.code_issued','legacy.code_used','setup.captured','setup.completed','reset.captured','reset.completed','recovery.revoked'}
    histories=rows('SELECT * FROM portal_events ORDER BY id');latest={}
    safe_keys={'customer_id','portal_id','delivery_email','state','version','created_at','updated_at'}
    for h in histories:
        require(h['customer_id'] in customers and h['action'] in actions and stamp(h['created_at']) and isinstance(h['detail'],str) and len(h['detail'])<=1000,'Invalid portal history.')
        value=json.loads(h['snapshot'])
        require(isinstance(value,dict) and (not value or set(value)==safe_keys),'Portal history contains unsupported fields or secrets.')
        if value:
            require(value['customer_id']==h['customer_id'] and hid(value['portal_id']) and value['state'] in states and type(value['version']) is int and value['version']>=1,'Portal history belongs to another customer.')
            latest[h['customer_id']]=value
    for cid,a in accounts.items():
        require(latest.get(cid)=={k:v for k,v in a.items() if k!='password'},'Portal account does not match its last saved history.')
    saved=rows('SELECT * FROM sales_settings');require(len(saved)==1 and saved[0]['id']==1,'Document settings are missing.')
    cfg=saved[0];Settings.model_validate_json(cfg['content']);require(type(cfg['version']) is int and cfg['version']>=1,'Invalid settings version.')
    history=rows('SELECT * FROM sales_settings_history ORDER BY version')
    require([r['version'] for r in history]==list(range(2,cfg['version']+1)),'Document settings history is incomplete.')
    for h in history:
        Settings.model_validate_json(h['content']);require(stamp(h['created_at']),'Invalid settings timestamp.')
    if history:require(json.loads(history[-1]['content'])==json.loads(cfg['content']),'Settings do not match their history.')
    elif cfg['version']==1:require(json.loads(cfg['content'])==Settings().model_dump(),'Initial settings differ without history.')
    responses=rows('SELECT * FROM quote_responses')
    response_keys={'reference','quote_version','snapshot','snapshot_hash','actor','mode','action_label','owner_wording','confirmed','typed_name','acknowledgement','qualification'}
    for r in responses:
        q=rows('SELECT * FROM quotes WHERE id=?',(r['quote_id'],));require(len(q)==1,'Quote response has no quote.');q=q[0];e=json.loads(r['evidence']);content=json.loads(q['snapshot'])
        from .customer_review import historical_owner
        require(set(e)==response_keys and e['snapshot']==content and e['snapshot_hash']==snapshot_hash(content) and e['reference']==q['reference'] and historical_owner(db,q['job_id'],r['customer_id']),'Quote response snapshot or customer is inconsistent.')
        require(r['action'] in ('accepted','declined') and type(e['quote_version']) is int and 1<=e['quote_version']<q['version'] and stamp(r['created_at']),'Invalid quote response transition.')
        require(e['mode'] in ('customer','owner-recorded') and type(e['confirmed']) is bool and e['confirmed']==(e['mode']=='customer'),'Invalid quote acknowledgement mode.')
        require(e['action_label']==('Accept this demo quote' if r['action']=='accepted' else 'Decline this demo quote') and e['owner_wording']==content['business'].get('document_settings',{}).get('quote_acknowledgement_text',''),'Quote response wording is inconsistent.')
        require(isinstance(e['typed_name'],str) and len(e['typed_name'])<=200 and isinstance(e['acknowledgement'],str) and len(e['acknowledgement'])<=1000 and e['qualification']=='Local demonstration record. A typed name is not a certified signature.','Invalid quote response evidence.')
        if e['mode']=='customer':require(e['actor']=='portal:'+r['customer_id'],'Quote response actor belongs to another customer.')
        else:require(r['action']=='accepted' and not e['typed_name'] and not e['acknowledgement'],'Owner response invents customer acknowledgement.')
        events=rows("SELECT * FROM document_events WHERE kind='quote' AND document_id=? AND action=? AND actor=?",(q['id'],r['action'],e['actor']))
        require(any(json.loads(h['before_value'])['version']==e['quote_version'] and json.loads(h['after_value'])['status']==r['action'] for h in events),'Quote response has no matching transition history.')
    requests=rows('SELECT * FROM payment_request_snapshots')
    for r in requests:
        invoices=rows('SELECT * FROM invoices WHERE id=?',(r['invoice_id'],));stages=rows('SELECT * FROM payment_stages WHERE id=?',(r['stage_id'],))
        require(len(invoices)==len(stages)==1,'Payment request has no exact invoice or stage.');inv=invoices[0];stage=stages[0];v=json.loads(r['snapshot'])
        require(set(v)=={'invoice_id','reference','document','document_hash','stage','requested_remaining_pence','recorded_at','origin','qualification'},'Unsupported payment request fields.')
        require(stage['invoice_id']==inv['id']==v['invoice_id'] and inv['issued_at'] and v['document']==json.loads(inv['snapshot']) and v['document_hash']==snapshot_hash(v['document']) and v['reference']==inv['reference'],'Payment request document or ownership is inconsistent.')
        require(v['stage']=={k:stage[k] for k in ('id','position','label','amount_pence','due_date','requested_at')} and bool(stage['requested_at']),'Payment request changed its original stage.')
        require(type(v['requested_remaining_pence']) is int and 0<v['requested_remaining_pence']<=stage['amount_pence'] and v['recorded_at']==r['created_at'] and stamp(r['created_at']) and v['origin'] in ('first explicit local request','owner-reviewed legacy request'),'Invalid payment request value or provenance.')
        require(v['qualification']=='Part of the existing invoice. Not another invoice, receipt or revenue entry.','Invalid payment request purpose.')
        events=rows("SELECT after_value,created_at FROM document_events WHERE kind='invoice' AND document_id=? AND action='stage.requested'",(inv['id'],))
        require(any(json.loads(e['after_value']).get('stage_id')==stage['id'] and json.loads(e['after_value']).get('remaining_pence')==v['requested_remaining_pence'] and e['created_at']>=v['recorded_at'] for e in events),'Payment request amount has no matching explicit request history.')
    return {'accounts':len(accounts),'history':len(histories),'responses':len(responses),'payment_requests':len(requests),'settings_version':cfg['version']}


def sanitise(db):
    db.execute("UPDATE portal_accounts SET password=''")
    db.execute('DELETE FROM portal_tokens')


def restored(store):
    from .portal_access import event
    from .store import now
    with store.registry() as registry:bids=[r[0] for r in registry.execute('SELECT id FROM businesses')]
    for bid in bids:
        with store.tenant(bid) as db:
            db.execute('BEGIN IMMEDIATE');sanitise(db)
            ids=[r[0] for r in db.execute('SELECT customer_id FROM portal_accounts')]
            db.execute("UPDATE portal_accounts SET state='revoked',version=version+1,updated_at=?",(now(),))
            for cid in ids:event(db,cid,'recovery.revoked','recovery','Customer passwords, setup codes and sessions remain disabled. Owner must invite again.')