"""Ordered exact-job corrections. Returning to an earlier owner is a new event."""
import json
from .store import digest

# Recovery connections may use tuples; avoid changing their row factory.
def many(db,sql,args=()):
    cursor=db.execute(sql,args);keys=[d[0] for d in cursor.description]
    return [dict(zip(keys,row)) for row in cursor.fetchall()]

def one(db,sql,args=()):
    rows=many(db,sql,args)
    return rows[0] if rows else None

LEDGER_SQL = """CREATE TABLE customer_transfer_jobs(job_id TEXT NOT NULL REFERENCES jobs(id),
 transfer_id TEXT NOT NULL REFERENCES customer_transfers(id), from_customer_id TEXT NOT NULL REFERENCES customers(id),
 to_customer_id TEXT NOT NULL REFERENCES customers(id), before_version INTEGER NOT NULL, after_version INTEGER NOT NULL,
 sequence INTEGER NOT NULL, previous_transfer_id TEXT REFERENCES customer_transfers(id),
 PRIMARY KEY(job_id,sequence), UNIQUE(job_id,transfer_id), UNIQUE(job_id,after_version))"""


def legacy(db):
    return [r[1] for r in db.execute('PRAGMA table_info(customer_transfer_jobs)')]==['job_id','transfer_id','from_customer_id','to_customer_id','before_version','after_version']


def chain(db, jid):
    cols=[r[1] for r in db.execute('PRAGMA table_info(customer_transfer_jobs)')]
    if not cols:return []
    old=legacy(db)
    rows=many(db,'SELECT * FROM customer_transfer_jobs WHERE job_id=? ORDER BY '+('rowid' if old else 'sequence'),(jid,))
    if old:rows=[{**r,'sequence':1,'previous_transfer_id':None} for r in rows]
    job=one(db,'SELECT customer_id,version FROM jobs WHERE id=?',(jid,))
    previous=None
    for n,r in enumerate(rows,1):
        if r['sequence']!=n or r['previous_transfer_id']!=(previous['transfer_id'] if previous else None) or r['from_customer_id']==r['to_customer_id'] or type(r['before_version']) is not int or r['before_version']<1 or r['after_version']!=r['before_version']+1:
            raise ValueError('Customer ownership chain is incomplete or out of order.')
        if previous and (r['from_customer_id']!=previous['to_customer_id'] or r['before_version']<previous['after_version']):raise ValueError('Customer ownership chain branches or rewinds.')
        t=one(db,'SELECT * FROM customer_transfers WHERE id=?',(r['transfer_id'],))
        if not t or t['source_id']!=r['from_customer_id'] or t['target_id']!=r['to_customer_id']:raise ValueError('Ownership move has no matching review.')
        plan=json.loads(t['plan'])
        raw=json.dumps({k:v for k,v in plan.items() if k!='preview_hash'},sort_keys=True,ensure_ascii=False,separators=(',',':'))
        before=[j for j in plan['jobs'] if j['id']==jid]
        if digest(raw)!=t['preview_hash'] or plan['preview_hash']!=t['preview_hash'] or len(before)!=1 or before[0]['version']!=r['before_version'] or before[0]['customer_id']!=r['from_customer_id']:raise ValueError('Ownership move differs from its saved preview.')
        previous=r
    if rows and (not job or job['customer_id']!=rows[-1]['to_customer_id'] or job['version']<rows[-1]['after_version']):raise ValueError('Current customer differs from the last ownership move.')
    return rows


def epoch(db,jid):
    rows=chain(db,jid)
    return rows[-1]['transfer_id'] if rows else None


def retained_association(db,jid,table,rid):
    return any(rid in json.loads(one(db,'SELECT plan FROM customer_transfers WHERE id=?',(m['transfer_id'],))['plan'])['associations'][jid].get(table,[]) for m in chain(db,jid))


def history(db,jid):
    moves=chain(db,jid);j=one(db,'SELECT customer_id FROM jobs WHERE id=?',(jid,))
    first=one(db,'SELECT snapshot FROM job_workflow_history WHERE job_id=? ORDER BY version LIMIT 1',(jid,))
    baseline=json.loads(first['snapshot'])['job']['customer_id'] if first else j['customer_id']
    result=[]
    for m in moves:
        t=one(db,'SELECT * FROM customer_transfers WHERE id=?',(m['transfer_id'],));plan=json.loads(t['plan'])
        result.append({**m,'reason':t['reason'],'created_at':t['created_at'],'actor':t['actor'],'source_name':plan['source']['name'],'target_name':plan['target']['name']})
    return {'job_id':jid,'observed_original_customer_id':baseline,'current_customer_id':j['customer_id'],'moves':result}


def migrate(db):
    """Validate the exact old ledger before atomically adding sequence evidence."""
    from .customer_recovery_v12 import validate
    from pathlib import Path
    pinned=json.loads((Path(__file__).resolve().parents[1]/'docs/recovery-schema-v12.json').read_text())
    expected=next(r[3] for r in pinned['tenant'] if r[1]=='customer_transfer_jobs')
    actual=db.execute("SELECT sql FROM sqlite_schema WHERE name='customer_transfer_jobs'").fetchone()[0]
    if actual!=expected:raise ValueError('Unsupported customer transfer ledger schema.')
    def require(ok,message):
        if not ok:raise ValueError(message)
    db.execute('SAVEPOINT customer_move_upgrade')
    try:
        validate(db,require)
        saved=many(db,'SELECT * FROM customer_transfer_jobs ORDER BY job_id')
        db.execute('DROP TABLE customer_transfer_jobs');db.execute(LEDGER_SQL)
        for r in saved:db.execute('INSERT INTO customer_transfer_jobs VALUES(?,?,?,?,?,?,1,NULL)',tuple(r.values()))
        from .customer_recovery_v13 import validate as current
        current(db,require)
        db.execute('RELEASE customer_move_upgrade')
    except BaseException:
        db.execute('ROLLBACK TO customer_move_upgrade');db.execute('RELEASE customer_move_upgrade');raise