#!/usr/bin/env python3
"""Restore ETFLayers CSV or Parquet exports into a standalone SQLite copy."""

import argparse
import csv
import hashlib
import io
import json
import re
import sqlite3
from contextlib import closing
from datetime import date, datetime, timezone
from decimal import Decimal
from pathlib import Path
from zipfile import BadZipFile, ZipFile

METHOD = "nport-common-equity-v1"
ARCHIVE_METHODS = {"sec-audited-schedule-v1", "sec-nq-schedule-v1", "sec-mixed-holdings-schedule-v1"}
SCALE = 10**10
TABLES = {
    "funds": ("id ticker name issuer category subcategory geography capitalization management exchange status series_id class_id", ("id",)),
    "filings": ("id report_date filed_date public_at source_url accession source_sha256 version matched_nav_pct unmapped_nav_pct excluded_reported_nav_pct withdrawn sequence", ("version",)),
    "positions": ("version security_id name title ticker industry country weight_pct", ("version", "security_id")),
    "industries": ("security_id sic industry source_url", ("security_id",)),
}
CSV_PROTECTED_PREFIXES = ("=", "+", "-", "@", "\t", "\r")
CSV_ESCAPED_PREFIXES = CSV_PROTECTED_PREFIXES + ("'", "\n")
CSV_ENCODING = "escaped-null-v1"
CSV_NULL = r"\N"
REQUIRED_TEXT = {
    "funds": ("id", "ticker", "name", "category", "status"),
    "filings": ("id", "report_date", "filed_date", "accession", "source_url", "source_sha256", "version"),
    "positions": ("version", "security_id", "name", "title"),
    "industries": ("security_id", "sic", "industry", "source_url"),
}


def encode_csv(value):
    if value is None:
        return CSV_NULL
    if isinstance(value, str):
        if value.startswith("\\"):
            return "\\" + value
        if value.startswith(CSV_ESCAPED_PREFIXES):
            return "'" + value
    return value


def decode_csv(value, encoding):
    if encoding == CSV_ENCODING:
        if value == CSV_NULL:
            return None
        if value.startswith("\\\\") or (value.startswith("'") and value[1:].startswith(CSV_ESCAPED_PREFIXES)):
            return value[1:]
        if value.startswith(("\\", "'") + CSV_PROTECTED_PREFIXES + ("\n",)):
            raise ValueError("Invalid escaped CSV cell.")
    elif value.startswith("'") and value[1:].startswith(CSV_PROTECTED_PREFIXES):
        return value[1:]
    return value


def sha256(source):
    digest = hashlib.sha256()
    for block in iter(lambda: source.read(1024 * 1024), b""):
        digest.update(block)
    return digest.hexdigest()


def history_digest(observations):
    payload = json.dumps(observations, sort_keys=True, separators=(",", ":"), allow_nan=False)
    return hashlib.sha256(payload.encode()).hexdigest()


def history_response(observations):
    methods = sorted({row.get("methodology", METHOD) for row in observations})
    if not methods or methods == [METHOD]:
        return {"items": observations, "methodology": METHOD}
    return {
        "items": [row | {"methodology": row.get("methodology", METHOD)} for row in observations],
        "methodology": methods[0] if len(methods) == 1 else "mixed",
        "methodologies": methods,
    }


def read_manifest(bundle):
    manifest = json.loads(bundle.read("manifest.json"))
    if not isinstance(manifest, dict):
        raise ValueError("Invalid archive manifest.")
    fmt = manifest["format"]
    if type(manifest["schema_version"]) is not int or manifest["schema_version"] != 1 or manifest["methodology"] != METHOD or fmt not in ("csv", "parquet"):
        raise ValueError("Unsupported archive format or methodology.")
    encoding = manifest.get("csv_encoding")
    if encoding is not None and (encoding != CSV_ENCODING or fmt != "csv"):
        raise ValueError("Unsupported CSV encoding.")
    fund_ids = manifest["fund_ids"]
    if not isinstance(fund_ids, list) or not all(isinstance(item, str) and item for item in fund_ids) or len(set(fund_ids)) != len(fund_ids):
        raise ValueError("Invalid fund scope.")
    expected_industry_scope = "fund_history" if fund_ids else "catalog"
    if manifest.get("industry_scope", expected_industry_scope) != expected_industry_scope:
        raise ValueError("Invalid industry scope.")
    industry_version = manifest["industry_version"]
    if industry_version is not None and (not isinstance(industry_version, str) or not re.fullmatch(r"[0-9a-f]{64}", industry_version)):
        raise ValueError("Invalid industry revision.")
    files = {f"{table}.{fmt}" for table in TABLES}
    if set(manifest["files"]) != files or sorted(bundle.namelist()) != sorted(files | {"manifest.json"}):
        raise ValueError("The archive must contain exactly the four declared tables.")
    through, after = manifest["through_sequence"], manifest["after_sequence"]
    if type(through) is not int or not 0 <= through <= 2**63 - 1 or (after is not None and (type(after) is not int or not 0 <= after <= through)):
        raise ValueError("Invalid sequence range.")
    if "catalog_lineage" in manifest:
        lineage = manifest["catalog_lineage"]
        if (not isinstance(lineage, dict) or not isinstance(lineage.get("through"), str)
                or not re.fullmatch(r"[0-9a-f]{64}", lineage["through"])
                or (after is None and lineage.get("from") is not None)
                or (after is not None and (not isinstance(lineage.get("from"), str)
                    or not re.fullmatch(r"[0-9a-f]{64}", lineage["from"])))):
            raise ValueError("Invalid catalog lineage.")
    for table, (columns, _) in TABLES.items():
        if manifest["fields"][table] != columns.split():
            raise ValueError(f"Unexpected schema for {table}.")
        name = f"{table}.{fmt}"
        expected = manifest["files"][name]
        with bundle.open(name) as member:
            if bundle.getinfo(name).file_size != expected["bytes"] or sha256(member) != expected["sha256"]:
                raise ValueError(f"Checksum or byte count mismatch for {name}.")
    return manifest


def rows(bundle, table, fmt, fields, encoding=None):
    with bundle.open(f"{table}.{fmt}") as member:
        if fmt == "csv":
            reader = csv.DictReader(io.TextIOWrapper(member, encoding="utf-8", newline=""))
            if reader.fieldnames != fields:
                raise ValueError(f"Unexpected CSV headings in {table}.")
            for record in reader:
                if None in record or any(value is None for value in record.values()):
                    raise ValueError(f"Malformed CSV row in {table}.")
                yield {field: decode_csv(value, encoding) for field, value in record.items()}
        else:
            try:
                from pyarrow.parquet import ParquetFile
            except ImportError as exc:
                raise ValueError("Install pyarrow to restore Parquet, or request CSV.") from exc
            parquet = ParquetFile(member)
            expected_types = {
                "sequence": "int64", "withdrawn": "bool",
                **{field: "double" for field in fields if field.endswith("_pct")},
            }
            if parquet.schema_arrow.names != fields or any(
                str(field.type) != expected_types.get(field.name, "string") for field in parquet.schema_arrow
            ):
                raise ValueError(f"Unexpected Parquet schema in {table}.")
            for batch in parquet.iter_batches(batch_size=10_000):
                yield from batch.to_pylist()


def value_for(field, value):
    if field.endswith("_pct"):
        # Store integer units so summing the archive uses the same precision as the API.
        try:
            numeric = Decimal(str(value))
            if numeric.is_finite() and 0 <= numeric <= Decimal((2**63 - 1) // SCALE):
                return round(numeric * SCALE)
        except ArithmeticError:
            pass
        raise ValueError(f"Invalid percentage in {field}.")
    if field == "withdrawn":
        if value in (True, "True", "true", "1"):
            return 1
        if value in (False, "False", "false", "0"):
            return 0
        raise ValueError("Invalid withdrawn flag.")
    if field == "sequence":
        if type(value) is int or (isinstance(value, str) and re.fullmatch(r"0|[1-9][0-9]*", value)):
            return int(value)
        raise ValueError("Invalid filing sequence.")
    return value


def validated_rows(bundle, table, manifest, fields, incoming_funds, incoming_filings):
    scope = set(manifest["fund_ids"])
    encoding = manifest.get("csv_encoding")
    fmt = manifest["format"]
    lower_bound = manifest["after_sequence"] or 0
    text_fields = REQUIRED_TEXT[table]
    numeric = [(index, field) for index, field in enumerate(fields) if
               field.endswith("_pct") or field in ("sequence", "withdrawn")]
    for row in rows(bundle, table, fmt, fields, encoding):
        if any(not isinstance(row[field], str) for field in text_fields):
            raise ValueError(f"Missing required text in {table}.")
        if table == "funds":
            fund_id = row["id"]
            if not fund_id or (scope and fund_id not in scope) or fund_id in incoming_funds:
                raise ValueError("Unexpected, out-of-scope or duplicate fund.")
            if row["status"] in ("unsupported", "unmapped") or fund_id.startswith("demo-"):
                raise ValueError("Archive contains an unsupported fund.")
            incoming_funds.add(fund_id)
        elif table == "filings":
            fund_id = row["id"]
            if fund_id not in incoming_funds:
                raise ValueError("Filing belongs to a fund absent from this archive.")
            sequence = value_for("sequence", row["sequence"])
            if not lower_bound < sequence <= manifest["through_sequence"]:
                raise ValueError("Filing sequence is outside the manifest range.")
            digest = row["source_sha256"]
            identity = f'{fund_id}:{row["accession"]}:{digest}'
            version = hashlib.sha256(identity.encode()).hexdigest()[:32]
            if row["version"] != version or not re.fullmatch(r"[0-9a-f]{64}", digest) or not row["accession"]:
                raise ValueError("Invalid filing version or source hash.")
            if any(date.fromisoformat(row[field]).isoformat() != row[field] for field in ("report_date", "filed_date")):
                raise ValueError("Invalid filing date.")
            if fmt == "csv" and not encoding and row["public_at"] == "":
                row["public_at"] = None
            if row["public_at"] is not None:
                public = datetime.fromisoformat(row["public_at"].replace("Z", "+00:00"))
                if public.tzinfo is None or not row["public_at"].endswith("Z"):
                    raise ValueError("Invalid filing timestamp.")
            if version in incoming_filings:
                raise ValueError("Duplicate filing version in this archive.")
            incoming_filings[version] = value_for("withdrawn", row["withdrawn"])
        elif table == "positions":
            if incoming_filings.get(row["version"]) != 0 or not row["security_id"]:
                raise ValueError("Position has no active parent filing in this archive.")
        values = [row[field] for field in fields]
        for index, field in numeric:
            values[index] = value_for(field, values[index])
        if table == "positions" and values[-1] <= 0:
            raise ValueError("Position must have a positive weight.")
        yield tuple(values)


def apply_history(db, manifest):
    if "archive_history" not in manifest:
        return
    snapshot = manifest["archive_history"]
    if not isinstance(snapshot, dict):
        raise ValueError("Invalid archive history snapshot.")
    revision, digest, observations = (snapshot.get(key) for key in ("revision", "sha256", "observations"))
    if type(revision) is not int or not 0 < revision <= 2**63 - 1 or not isinstance(observations, list):
        raise ValueError("Invalid archive history revision.")
    if digest != history_digest(observations):
        raise ValueError("Archive history checksum mismatch.")

    scope = set(manifest["fund_ids"])
    known_funds = {row[0] for row in db.execute("SELECT id FROM funds")}
    records = []
    for row in observations:
        if not isinstance(row, dict):
            raise ValueError("Invalid archive history observation.")
        day, sources = row["report_date"], row["sources"]
        if (row["archive_summary"] is not True or row["methodology"] not in ARCHIVE_METHODS
                or date.fromisoformat(day).isoformat() != day or not re.fullmatch(r"[0-9a-f]{32}", row["data_version"])
                or type(row["common_count"]) is not int or row["common_count"] < 0
                or not isinstance(sources, list) or len(sources) != 2 or not all(isinstance(source, dict) for source in sources)):
            raise ValueError("Invalid archive history observation.")
        value_for("overlap_pct", row["overlap_pct"])
        fund_ids = {source["id"] for source in sources}
        if len(fund_ids) != 2 or not fund_ids <= known_funds or (scope and not fund_ids <= scope):
            raise ValueError("Archive history references an absent or out-of-scope fund.")
        for source in sources:
            if (source["report_date"] != day or date.fromisoformat(source["filed_date"]).isoformat() != source["filed_date"]
                    or not re.fullmatch(r"\d{4}-\d\d-\d\dT\d\d:\d\d:\d\dZ", source["public_at"])
                    or not source["source_url"].startswith("https://www.sec.gov/Archives/edgar/data/")
                    or not re.fullmatch(r"[0-9a-f]{64}", source["source_sha256"])):
                raise ValueError("Invalid archive history source.")
            datetime.fromisoformat(source["public_at"].replace("Z", "+00:00"))
        records.append((*sorted(fund_ids), day, max(source["public_at"] for source in sources), json.dumps(row)))

    db.execute("""CREATE TABLE IF NOT EXISTS archive_history_state (
        id INTEGER PRIMARY KEY, revision INTEGER NOT NULL, catalog_sequence INTEGER NOT NULL, sha256 TEXT NOT NULL)""")
    db.execute("""CREATE TABLE IF NOT EXISTS archive_history (
        fund_a TEXT NOT NULL, fund_b TEXT NOT NULL, report_date TEXT NOT NULL, public_at TEXT NOT NULL,
        observation TEXT NOT NULL, PRIMARY KEY(fund_a,fund_b,report_date))""")
    through = manifest["through_sequence"]
    current = db.execute("SELECT revision,catalog_sequence,sha256 FROM archive_history_state WHERE id=1").fetchone()
    if current:
        prior_revision, prior_sequence, prior_digest = current
        if revision < prior_revision:
            return
        if revision == prior_revision and through == prior_sequence and digest != prior_digest:
            raise ValueError("Conflicting archive history at the same revision and checkpoint.")
    db.execute("DELETE FROM archive_history")
    try:
        db.executemany("INSERT INTO archive_history VALUES (?,?,?,?,?)", records)
    except sqlite3.IntegrityError as exc:
        raise ValueError("Duplicate archive history observation.") from exc
    db.execute("INSERT OR REPLACE INTO archive_history_state VALUES (1,?,?,?)", (revision, through, digest))


def corrected_filings(db, field_names, records, corrected):
    changes = {"filed_date", "public_at", "source_url", "sequence"}
    version = field_names.index("version")
    sequence = field_names.index("sequence")
    for row in records:
        earlier = db.execute("SELECT * FROM filings WHERE version=?", (row[version],)).fetchone()
        if earlier:
            if row[sequence] <= earlier[sequence] or any(
                before != after for key, before, after in zip(field_names, earlier, row, strict=True) if key not in changes
            ):
                raise ValueError("Correction conflicts with the restored filing version.")
            corrected[row[version]] = 0
        yield row


def new_positions(db, records, corrected):
    seen = set()
    for row in records:
        version, security_id = row[:2]
        if version not in corrected:
            yield row
            continue
        key = version, security_id
        earlier = db.execute("SELECT * FROM positions WHERE version=? AND security_id=?", key).fetchone()
        if earlier != row or key in seen:
            raise ValueError("Correction changes positions for a restored filing version.")
        seen.add(key)
        corrected[version] += 1
    for version, count in corrected.items():
        if db.execute("SELECT count(*) FROM positions WHERE version=?", (version,)).fetchone()[0] != count:
            raise ValueError("Correction omits positions for a restored filing version.")


def apply_archive(database, archive, expected_sha256=None):
    with Path(archive).open("rb") as source:
        digest = sha256(source)
    if expected_sha256 and digest != expected_sha256.casefold():
        raise ValueError("Archive SHA-256 does not match the download receipt.")
    with ZipFile(archive) as bundle, closing(sqlite3.connect(database)) as db, db:
        manifest = read_manifest(bundle)
        after, through, fmt = (manifest[key] for key in ("after_sequence", "through_sequence", "format"))
        db.execute("BEGIN IMMEDIATE")
        tables = {row[0] for row in db.execute("SELECT name FROM sqlite_master WHERE type='table'")}
        if tables and "archive_state" not in tables:
            raise ValueError("Use a fresh database or one created by this restore tool.")
        db.execute("""CREATE TABLE IF NOT EXISTS archive_state (
            id INTEGER PRIMARY KEY, fund_ids TEXT, sequence INTEGER, industry_version TEXT)""")
        db.execute("CREATE TABLE IF NOT EXISTS archive_receipts (sha256 TEXT PRIMARY KEY, sequence INTEGER NOT NULL)")
        prior = db.execute("SELECT fund_ids,sequence FROM archive_state WHERE id=1").fetchone()
        scope = json.dumps(sorted(manifest["fund_ids"]))
        applied = db.execute("SELECT 1 FROM archive_receipts WHERE sha256=?", (digest,)).fetchone()
        if applied:
            return {"status": "already_applied", "through_sequence": prior[1]}
        if (prior is None and after is not None) or (prior is not None and prior != (scope, after)):
            raise ValueError("Apply a full archive to a fresh database and continue with contiguous updates for the same funds.")
        db.execute("CREATE TABLE IF NOT EXISTS archive_lineage (id INTEGER PRIMARY KEY, head TEXT NOT NULL)")
        current_lineage = db.execute("SELECT head FROM archive_lineage WHERE id=1").fetchone()
        lineage = manifest.get("catalog_lineage")
        if current_lineage:
            if lineage is None:
                raise ValueError("This database requires increments with catalog lineage.")
            if lineage["from"] != current_lineage[0]:
                raise ValueError("Increment belongs to a different catalog history.")

        incoming_funds, incoming_filings = set(), {}
        corrected = {}
        for table, (columns, keys) in TABLES.items():
            fields = columns.split()
            definitions = [
                f"{field} {'INTEGER' if field.endswith('_pct') or field in ('sequence','withdrawn') else 'TEXT'}"
                + (" NOT NULL" if field in keys else "") for field in fields
            ]
            key_list = ", ".join(keys)
            db.execute(f"CREATE TABLE IF NOT EXISTS {table} ({', '.join(definitions)}, PRIMARY KEY ({key_list}))")
            if table == "industries":
                db.execute("DELETE FROM industries")
            updates = ", ".join(f"{name}=excluded.{name}" for name in fields if name not in keys)
            parameters = ", ".join("?" for _ in fields)
            statement = f"INSERT INTO {table} ({', '.join(fields)}) VALUES ({parameters})"
            records = validated_rows(bundle, table, manifest, fields, incoming_funds, incoming_filings)
            if table == "funds":
                statement += f" ON CONFLICT ({key_list}) DO UPDATE SET {updates}"
            elif table == "filings" and prior:
                records = corrected_filings(db, fields, records, corrected)
                statement += """ ON CONFLICT(version) DO UPDATE SET filed_date=excluded.filed_date,
                    public_at=excluded.public_at, source_url=excluded.source_url, sequence=excluded.sequence"""
            elif table == "positions" and corrected:
                records = new_positions(db, records, corrected)
            try:
                db.executemany(statement, records)
            except sqlite3.IntegrityError as exc:
                raise ValueError(f"Duplicate or conflicting record in {table}.") from exc
        if manifest.get("industry_scope") == "fund_history" and db.execute("""
            SELECT security_id FROM industries EXCEPT
            SELECT p.security_id FROM positions p JOIN industries i ON i.security_id=p.security_id LIMIT 1""").fetchone():
            raise ValueError("Industry registry contains an out-of-scope security.")
        apply_history(db, manifest)
        if lineage:
            db.execute("INSERT OR REPLACE INTO archive_lineage VALUES (1,?)", (lineage["through"],))
        db.execute("CREATE INDEX IF NOT EXISTS filings_by_fund_date ON filings (id,report_date,filed_date,accession,sequence)")
        db.execute("INSERT OR REPLACE INTO archive_state VALUES (1,?,?,?)", (scope, through, manifest["industry_version"]))
        db.execute("INSERT INTO archive_receipts VALUES (?,?)", (digest, through))
        return {"status": "applied", "through_sequence": through}


def time_cutoff(known_at):
    if known_at:
        if len(known_at) == 10:
            known_at = date.fromisoformat(known_at).isoformat() + "T23:59:59Z"
        cutoff = datetime.fromisoformat(known_at.replace("Z", "+00:00"))
        if cutoff.tzinfo is None:
            raise ValueError("Provide a timezone with --known-at.")
        return cutoff.astimezone(timezone.utc).isoformat(timespec="seconds").replace("+00:00", "Z")
    return None


def fund_timelines(db, left, right, as_of, known_at):
    funds = []
    timelines = []
    for symbol in (left, right):
        fund = db.execute("SELECT id,ticker FROM funds WHERE ticker=? COLLATE NOCASE OR id=?", (symbol, symbol)).fetchone()
        if not fund:
            raise ValueError(f"Fund {symbol} is absent from this archive.")
        funds.append(fund)
        by_day = {}
        for filing in db.execute("""SELECT * FROM filings
            WHERE id=? AND (? IS NULL OR report_date<=?) AND (? IS NULL OR public_at<=?)
            ORDER BY report_date DESC,filed_date DESC,accession DESC,sequence DESC""",
            (fund["id"], as_of, as_of, known_at, known_at)):
            by_day.setdefault(filing["report_date"], filing)
        timelines.append({day: filing for day, filing in by_day.items() if not filing["withdrawn"]})
    if funds[0]["id"] == funds[1]["id"]:
        raise ValueError("Provide two distinct funds.")
    return funds, timelines


def position_overlap(db, day, filings, industry_version):
    versions = [filing["version"] for filing in filings]
    count, weight = db.execute("""
        SELECT count(*),coalesce(sum(min(a.weight_pct,b.weight_pct)),0)
        FROM positions a JOIN positions b USING (security_id) WHERE a.version=? AND b.version=?
    """, versions).fetchone()
    version_input = METHOD + ":" + ":".join(sorted(version + industry_version for version in versions))
    return {"report_date": day, "common_count": count, "overlap_pct": weight / SCALE,
            "methodology": METHOD, "data_version": hashlib.sha256(version_input.encode()).hexdigest()[:32]}


def archived_pair(db, funds, as_of, known_at):
    if not db.execute("SELECT 1 FROM sqlite_master WHERE type='table' AND name='archive_history'").fetchone():
        return {}
    ids = [fund["id"] for fund in funds]
    result = {}
    for (payload,) in db.execute("""SELECT observation FROM archive_history
        WHERE fund_a=? AND fund_b=? AND (? IS NULL OR report_date<=?) AND (? IS NULL OR public_at<=?)""",
        (*sorted(ids), as_of, as_of, known_at, known_at)):
        observation = json.loads(payload)
        sources = {source["id"]: source for source in observation["sources"]}
        result[observation["report_date"]] = observation | {"sources": [sources[id_] for id_ in ids]}
    return result


def compare(database, left, right, report_date=None, known_at=None):
    if report_date:
        report_date = date.fromisoformat(report_date).isoformat()
    known_at = time_cutoff(known_at)
    with closing(sqlite3.connect(f"{Path(database).resolve().as_uri()}?mode=ro", uri=True)) as db:
        db.row_factory = sqlite3.Row
        funds, (left_filing, right_filing) = fund_timelines(db, left, right, report_date, known_at)
        common = left_filing.keys() & right_filing.keys()
        if report_date and report_date not in common:
            if observation := archived_pair(db, funds, report_date, known_at).get(report_date):
                return observation
            common = set()
        if not common:
            raise ValueError("Provide two distinct funds with a shared report date.")
        day = max(common)
        industry_version = db.execute("SELECT industry_version FROM archive_state WHERE id=1").fetchone()[0] or ""
        return position_overlap(db, day, (left_filing[day], right_filing[day]), industry_version)


def history(database, left, right, as_of=None, known_at=None):
    if as_of:
        as_of = date.fromisoformat(as_of).isoformat()
    known_at = time_cutoff(known_at)
    with closing(sqlite3.connect(f"{Path(database).resolve().as_uri()}?mode=ro", uri=True)) as db:
        db.row_factory = sqlite3.Row
        funds, (left_filing, right_filing) = fund_timelines(db, left, right, as_of, known_at)
        industry_version = db.execute("SELECT industry_version FROM archive_state WHERE id=1").fetchone()[0] or ""
        combined = archived_pair(db, funds, as_of, known_at)
        for day in left_filing.keys() & right_filing.keys():
            filings = (left_filing[day], right_filing[day])
            sources = [{key: filing[key] / SCALE if key.endswith("_pct") else filing[key]
                        for key in filing.keys() if key not in ("withdrawn", "sequence")} | {"ticker": fund["ticker"]}
                       for filing, fund in zip(filings, funds, strict=True)]
            observation = position_overlap(db, day, filings, industry_version)
            observation.pop("methodology")
            combined[day] = observation | {"sources": sources}
        return history_response([combined[day] for day in sorted(combined)])


def main():
    parser = argparse.ArgumentParser(description=__doc__)
    commands = parser.add_subparsers(dest="command", required=True)
    apply = commands.add_parser("apply", help="Apply a full ZIP or one contiguous update.")
    apply.add_argument("database", type=Path)
    apply.add_argument("archive", type=Path)
    apply.add_argument("--sha256", help="Expected ZIP SHA-256 from the authenticated export status.")
    inspect = commands.add_parser("compare", help="Reproduce overlap from the restored archive.")
    inspect.add_argument("database", type=Path)
    inspect.add_argument("left")
    inspect.add_argument("right")
    inspect.add_argument("--date")
    inspect.add_argument("--known-at")
    timeline = commands.add_parser("history", help="Reproduce dated observations from the restored archive.")
    timeline.add_argument("database", type=Path)
    timeline.add_argument("left")
    timeline.add_argument("right")
    timeline.add_argument("--as-of")
    timeline.add_argument("--known-at")
    args = parser.parse_args()
    try:
        if args.command == "apply":
            result = apply_archive(args.database, args.archive, args.sha256)
        elif args.command == "history":
            result = history(args.database, args.left, args.right, args.as_of, args.known_at)
        else:
            result = compare(args.database, args.left, args.right, args.date, args.known_at)
    except (BadZipFile, OSError, TypeError, ValueError, KeyError, sqlite3.Error) as exc:
        parser.exit(1, f"{exc}\n")
    print(json.dumps(result, indent=2))


if __name__ == "__main__":
    main()
