"""Owner-reviewed source reservations. Internal costs never become customer prices."""
import json,sqlite3
from decimal import Decimal,ROUND_HALF_UP
from fastapi import Request,HTTPException
from .store import one,many,uid,now
from . import field_models as m
from .models import Line,Quote
from .documents import InvoiceInput,snapshot,number,event,new_invoice,quote_row,check_version,stage_rows

TABLES={'quote':'quotes','invoice':'invoices'}
def fail(message,status=409):raise HTTPException(status,message)
def required(row):
    if not row:fail('Source or document not found in this job.',404)
    return row

def source(db,job_id,kind,sid):
    if kind=='field':
        row=required(one(db,'SELECT * FROM field_sessions WHERE id=? AND job_id=?',(sid,job_id)))
        if row['state']!='converted':fail('Review and record this field work as labour first.')
        entries=many(db,'SELECT t.* FROM time_entries t JOIN field_time_links l ON l.time_id=t.id WHERE l.session_id=? AND l.active=1 AND t.archived=0 ORDER BY t.work_date,t.resource_id',(sid,))
        if not entries:fail('This field record has no active labour entries.')
        minutes=sum(t['duration_minutes'] for t in entries);quantity=format((Decimal(minutes)/60).quantize(Decimal('.001'),rounding=ROUND_HALF_UP).normalize(),'f');unit='person-hours'
        internal=sum(t['cost_pence'] for t in entries);description='Reviewed field labour';context=' + '.join(p['name'] for p in json.loads(row['resources']))+'; '+', '.join(sorted({t['work_date'] for t in entries}))+'; '+str(minutes)+' recorded person-minutes';snapshot_value={'record':row,'time_entries':entries}
    elif kind=='time':
        row=required(one(db,'SELECT * FROM time_entries WHERE id=? AND job_id=?',(sid,job_id)))
        if row['archived'] or one(db,'SELECT time_id FROM field_time_links WHERE time_id=?',(sid,)):fail('Generated field labour is selected once through its field record, not through each time entry.')
        quantity=format((Decimal(row['duration_minutes'])/60).quantize(Decimal('.001'),rounding=ROUND_HALF_UP).normalize(),'f');unit='person-hours';internal=row['cost_pence'];description='Recorded labour on '+row['work_date'];context=json.loads(row['resource_snapshot'])['name']+'; '+str(row['duration_minutes'])+' recorded minutes';snapshot_value=row
    elif kind=='material':
        row=required(one(db,'SELECT * FROM job_materials WHERE id=? AND job_id=?',(sid,job_id)))
        if row['archived'] or row['status']!='received':fail('Only active received materials can be selected for a draft.')
        quantity=row['quantity'];unit=row['unit'];internal=row['total_pence'];description=row['name'];context='Received '+(row['received_date'] or 'date not entered');snapshot_value=row
    else:fail('Unknown source kind.',422)
    return {'source_kind':kind,'source_id':sid,'source_version':row['version'],'description':description,'private_context':context,'quantity':quantity,'unit':unit,'internal_cost_pence':internal,'unit_pence':None,'snapshot':snapshot_value}

def claimed(db,kind,doc_id):return many(db,"SELECT * FROM document_sources WHERE kind=? AND document_id=? AND state IN ('reserved','issued') ORDER BY line_index",(kind,doc_id))
def log(db,row,action,actor):db.execute('INSERT INTO source_history(claim_id,action,actor_id,snapshot,created_at) VALUES(?,?,?,?,?)',(row['id'],action,actor,json.dumps(row),now()))
def release(db,kind,doc_id,actor,action='released'):
    for row in claimed(db,kind,doc_id):
        db.execute("UPDATE document_sources SET state='released',updated_at=? WHERE id=?",(now(),row['id']));log(db,one(db,'SELECT * FROM document_sources WHERE id=?',(row['id'],)),action,actor)

def unchanged(db,kind,doc_id):
    for row in claimed(db,kind,doc_id):
        current=source(db,row['job_id'],row['source_kind'],row['source_id'])
        if current['source_version']!=row['source_version'] or current['snapshot']!=json.loads(row['source_snapshot']):fail('A selected work or material source changed. Review the draft sources and customer prices again before sharing or issuing.')

def guard_edit(db,kind,doc_id):
    if claimed(db,kind,doc_id):fail('This draft has reserved work sources. Use Review source draft in Field work to edit lines, add manual lines or release sources.')

def insert(db,kind,doc_id,job_id,cid,source_value,index,line,actor):
    if one(db,"SELECT id FROM document_sources WHERE source_kind=? AND source_id=? AND state IN ('reserved','issued')",(source_value['source_kind'],source_value['source_id'])):fail('This work or material is already reserved or included in another document. Resolve that document before selecting it again.')
    rid=uid();db.execute("INSERT INTO document_sources VALUES(?,?,?,?,?,?,?,?,?,?,?,'reserved',?,?)",(rid,kind,doc_id,job_id,cid,source_value['source_kind'],source_value['source_id'],source_value['source_version'],json.dumps(source_value['snapshot']),index,json.dumps(line),now(),now()));log(db,one(db,'SELECT * FROM document_sources WHERE id=?',(rid,)),'reserved',actor)

def transfer(db,oldkind,oldid,newkind,newid,actor,*,check=False):
    rows=claimed(db,oldkind,oldid)
    if not rows and oldkind=='quote':
        rows=many(db,"SELECT s.* FROM document_sources s JOIN source_history h ON h.id=(SELECT MAX(h2.id) FROM source_history h2 WHERE h2.claim_id=s.id) WHERE s.kind=? AND s.document_id=? AND h.action='transferred' ORDER BY h.id",(oldkind,oldid))
        latest={}
        for row in rows:latest[(row['source_kind'],row['source_id'])]=row
        old=required(one(db,'SELECT snapshot FROM quotes WHERE id=?',(oldid,)));items=json.loads(old['snapshot'])['items']
        rows=[r for r in latest.values() if r['line_index']<len(items) and json.loads(r['line_snapshot'])==items[r['line_index']]]
    if check:
        for row in rows:
            value=source(db,row['job_id'],row['source_kind'],row['source_id'])
            if value['source_version']!=row['source_version'] or value['snapshot']!=json.loads(row['source_snapshot']):fail('The quote source changed. Revise and review its sources before drafting an invoice.')
    release(db,oldkind,oldid,actor,'transferred')
    new=required(one(db,'SELECT * FROM '+TABLES[newkind]+' WHERE id=?',(newid,)))
    for row in rows:
        insert(db,newkind,newid,new['job_id'],new['customer_id'],{'source_kind':row['source_kind'],'source_id':row['source_id'],'source_version':row['source_version'],'snapshot':json.loads(row['source_snapshot'])},row['line_index'],json.loads(row['line_snapshot']),actor)

def issued(db,doc_id,actor):
    unchanged(db,'invoice',doc_id)
    for row in claimed(db,'invoice',doc_id):
        db.execute("UPDATE document_sources SET state='issued',updated_at=? WHERE id=?",(now(),row['id']));log(db,one(db,'SELECT * FROM document_sources WHERE id=?',(row['id'],)),'issued',actor)

def document_projection(db,kind,doc_id):
    result=[]
    for row in claimed(db,kind,doc_id):
        try:value=source(db,row['job_id'],row['source_kind'],row['source_id']);changed=value['source_version']!=row['source_version'] or value['snapshot']!=json.loads(row['source_snapshot'])
        except HTTPException:changed=True
        result.append({'source_kind':row['source_kind'],'source_id':row['source_id'],'source_version':row['source_version'],'line_index':row['line_index'],'state':row['state'],'source_changed':changed})
    return result

def mount_billing_sources(app,store,session,once):
    @app.get('/api/jobs/{job_id}/suggestions')
    def suggestions(job_id:str,request:Request):
        s=session(request)
        with store.tenant(s['business_id']) as db:
            job=required(one(db,'SELECT id,customer_id,service FROM jobs WHERE id=?',(job_id,)));values=[]
            ids=[('field',r['id']) for r in many(db,"SELECT id FROM field_sessions WHERE job_id=? AND state='converted'",(job_id,))]+[('time',r['id']) for r in many(db,'SELECT id FROM time_entries WHERE job_id=? AND archived=0 AND id NOT IN (SELECT time_id FROM field_time_links)',(job_id,))]+[('material',r['id']) for r in many(db,"SELECT id FROM job_materials WHERE job_id=? AND archived=0 AND status='received'",(job_id,))]
            for kind,sid in ids:
                value=source(db,job_id,kind,sid);value.pop('snapshot');claim=one(db,"SELECT kind,document_id,state,source_version FROM document_sources WHERE source_kind=? AND source_id=? AND state IN ('reserved','issued')",(kind,sid));value['claim']=claim;values.append(value)
            drafts=[{'kind':kind,**r} for kind,table in TABLES.items() for r in many(db,"SELECT id,reference,version FROM "+table+" WHERE job_id=? AND status='draft'",(job_id,))]
            return {'job':job,'sources':values,'drafts':drafts,'basis':'Full-source reservations. Labour is person-hours rounded to three decimals from reviewed minutes. Customer unit prices must be entered explicitly; internal costs are separate.'}

    @app.get('/api/jobs/{job_id}/suggestions/history')
    def history(job_id:str,request:Request):
        s=session(request)
        with store.tenant(s['business_id']) as db:
            required(one(db,'SELECT id FROM jobs WHERE id=?',(job_id,)));return [{**r,'snapshot':json.loads(r['snapshot'])} for r in many(db,'SELECT h.* FROM source_history h JOIN document_sources s ON s.id=h.claim_id WHERE s.job_id=? ORDER BY h.id DESC',(job_id,))]

    @app.post('/api/jobs/{job_id}/suggestions/draft')
    def save(job_id:str,payload:m.DraftReview,request:Request):
        s=session(request,csrf=True)
        try:
            with store.tenant(s['business_id']) as db:
                def action():
                    job=required(one(db,'SELECT * FROM jobs WHERE id=?',(job_id,)));original=None;old=None
                    if payload.document_id:
                        old=required(one(db,'SELECT * FROM '+TABLES[payload.kind]+' WHERE id=? AND job_id=? AND customer_id=?',(payload.document_id,job_id,job['customer_id'])));check_version(old,payload.version)
                        if old['status']!='draft':fail('Only a private draft may be changed through source review.')
                        original=json.loads(old['snapshot'])
                    lines=[Line(**line.model_dump(include={'description','quantity','unit_pence'})) for line in payload.lines]
                    data={'items':lines,'vat_percent':payload.vat_percent,'notes':payload.notes}
                    if original:
                        for key in ('document_date','valid_until','due_date'):
                            if key in original:data[key]=original[key]
                    contract=(Quote if payload.kind=='quote' else InvoiceInput)(**data);content=snapshot(db,job_id,contract,original);selected=[]
                    for n,line in enumerate(payload.lines):
                        if line.source_id:
                            value=source(db,job_id,line.source_kind,line.source_id)
                            if value['source_version']!=line.source_version:fail('A source changed after preview. Reload and review before creating the draft.')
                            selected.append((n,value,lines[n].model_dump()))
                    if old:
                        if payload.kind=='invoice' and stage_rows(db,old['id']) and sum(x['amount_pence'] for x in stage_rows(db,old['id']))!=content['total_pence']:fail('Clear or update the draft payment schedule before changing its total.')
                        release(db,payload.kind,old['id'],s['subject'],'draft.review_replaced');did=old['id'];db.execute('UPDATE '+TABLES[payload.kind]+' SET snapshot=?,total_pence=?,version=version+1 WHERE id=?',(json.dumps(content),content['total_pence'],did));reference=old['reference']
                    elif payload.kind=='invoice':
                        made=new_invoice(db,job_id,job['customer_id'],content,s['subject']);did=made['id'];reference=made['reference']
                    else:
                        did=uid();reference=number(db,'quote');db.execute("INSERT INTO quotes(id,reference,job_id,customer_id,status,snapshot,total_pence,created_at) VALUES(?,?,?,?,'draft',?,?,?)",(did,reference,job_id,job['customer_id'],json.dumps(content),content['total_pence'],now()));db.execute('INSERT INTO quote_links VALUES(?,?,NULL,1,1,NULL,NULL)',(did,did))
                    for index,value,line in selected:insert(db,payload.kind,did,job_id,job['customer_id'],value,index,line,s['subject'])
                    current=one(db,'SELECT * FROM '+TABLES[payload.kind]+' WHERE id=?',(did,));event(db,payload.kind,did,'sources.reviewed',old,current,'Customer quantities/prices explicitly reviewed; no issuing or sharing.',s['subject']);return {'id':did,'kind':payload.kind,'reference':reference,'version':current['version']}
                return once(db,request,payload.model_dump(),action)
        except sqlite3.IntegrityError:fail('A source was reserved concurrently. Refresh and review the existing document.')