"""Checked SQLite snapshots and restores for this project's local service.

The SQLite online backup API includes committed WAL transactions. A snapshot is a
new directory containing store.sqlite and a checksum manifest. It is not encrypted
or signed. Never restore over a running store; restoration always creates a new
folder. Only this project's SQLite database is covered, not an external receiver.
"""
import argparse, hashlib, json, os, shutil, sqlite3, time
from pathlib import Path

ROOT=Path(__file__).resolve().parent
PROJECT=json.loads((ROOT/'delivery.json').read_text())['id']
TABLES={
 'supplier-catalog-imports':{'suppliers','items','batches','audit'},
 'webhook-delivery-queue':{'endpoints','events','jobs','attempts','audit'},
 'usage-reconciliation':{'events','entries','periods','posting_batches','postings','totals','closes','audit'}
}[PROJECT]

def checksum(path):
    h=hashlib.sha256()
    with Path(path).open('rb') as stream:
        for block in iter(lambda:stream.read(1048576),b''):h.update(block)
    return h.hexdigest()

def connect_readonly(path):
    path=Path(path).resolve(strict=True)
    if not path.is_file():raise ValueError('Database must be a regular file')
    return sqlite3.connect(path.as_uri()+'?mode=ro',uri=True,timeout=5)

def inspect_database(path):
    with connect_readonly(path) as db:
        check=[r[0] for r in db.execute('PRAGMA integrity_check')]
        foreign=[list(r) for r in db.execute('PRAGMA foreign_key_check')]
        tables={r[0] for r in db.execute("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'")}
        if not TABLES.issubset(tables):raise ValueError('Database does not contain this project schema')
        counts={t:db.execute('SELECT COUNT(*) FROM "'+t.replace('"','""')+'"').fetchone()[0] for t in sorted(tables)}
        if check!=['ok'] or foreign:raise ValueError('Database integrity or foreign keys failed')
        return {'integrity':'ok','foreign_key_errors':0,'table_counts':counts,'user_version':db.execute('PRAGMA user_version').fetchone()[0]}

def backup(source,destination):
    destination=Path(destination);destination.mkdir(mode=0o700,parents=False,exist_ok=False)
    started=time.monotonic()
    try:
        target=destination/'store.sqlite'
        def progress(status,remaining,total):
            if time.monotonic()-started>30:raise TimeoutError('Snapshot exceeded 30 seconds; retry during a quieter interval')
        with connect_readonly(source) as origin, sqlite3.connect(target) as copied:
            origin.backup(copied,pages=256,progress=progress,sleep=.01)
        os.chmod(target,0o600)
        facts=inspect_database(target)
        manifest={'format':1,'project':PROJECT,'file':'store.sqlite','sha256':checksum(target),'bytes':target.stat().st_size,**facts}
        with (destination/'manifest.json').open('x') as out:json.dump(manifest,out,indent=2);out.write('\n')
        os.chmod(destination/'manifest.json',0o600)
        return {**manifest,'elapsed_seconds':round(time.monotonic()-started,6)}
    except BaseException:
        shutil.rmtree(destination);raise

def restore(snapshot,destination):
    snapshot=Path(snapshot)
    if snapshot.is_symlink() or (snapshot/'manifest.json').is_symlink() or (snapshot/'store.sqlite').is_symlink():raise ValueError('Snapshot symlinks are not accepted')
    manifest=json.loads((snapshot/'manifest.json').read_text())
    if manifest.get('format')!=1 or manifest.get('project')!=PROJECT or manifest.get('file')!='store.sqlite':raise ValueError('Snapshot manifest does not match this project')
    source=snapshot/'store.sqlite'
    if checksum(source)!=manifest.get('sha256') or source.stat().st_size!=manifest.get('bytes'):raise ValueError('Snapshot checksum or length mismatch')
    facts=inspect_database(source)
    if facts['table_counts']!=manifest.get('table_counts'):raise ValueError('Snapshot row counts differ from manifest')
    destination=Path(destination);destination.mkdir(mode=0o700,parents=False,exist_ok=False)
    try:
        target=destination/'store.sqlite'
        with source.open('rb') as src,target.open('xb') as out:
            shutil.copyfileobj(src,out);out.flush();os.fsync(out.fileno())
        os.chmod(target,0o600)
        if checksum(target)!=manifest['sha256']:raise ValueError('Restored bytes differ')
        return {'database':str(target),**inspect_database(target)}
    except BaseException:
        shutil.rmtree(destination);raise

if __name__=='__main__':
    parser=argparse.ArgumentParser(description=__doc__)
    subs=parser.add_subparsers(dest='operation',required=True)
    p=subs.add_parser('inspect');p.add_argument('--db',required=True)
    p=subs.add_parser('backup');p.add_argument('--db',required=True);p.add_argument('--out',required=True)
    p=subs.add_parser('restore');p.add_argument('--snapshot',required=True);p.add_argument('--out',required=True)
    args=parser.parse_args();os.umask(0o077)
    if args.operation=='inspect':result=inspect_database(args.db)
    elif args.operation=='backup':result=backup(args.db,args.out)
    else:result=restore(args.snapshot,args.out)
    print(json.dumps(result,indent=2))