#!/usr/bin/env python3
"""Verify local AIBD fixture controls. Executes no SQL and makes no network calls."""
from __future__ import annotations
import csv
from collections import defaultdict
from decimal import Decimal
from pathlib import Path

ROOT = Path(__file__).resolve().parent
def rows(name):
    with (ROOT / name).open(newline="", encoding="utf-8") as handle:
        return list(csv.DictReader(handle))

assumptions = {r["field"]: r for r in rows("assumption-register-AIBD-20260831.csv")}
false_fields = {
    "execution_performed", "warehouse_connected", "dry_run_performed", "customer_data",
    "sla_claimed", "second_run_performed", "independent_validation_performed",
    "product_task_claimed", "product_cancel_claimed", "product_rerun_claimed",
    "product_download_claimed", "product_sql_inspection_claimed",
}
for field in false_fields:
    assert assumptions[field]["value"].lower() == "false"
    assert assumptions[field]["status"] == "held"

assert assumptions["assumed_source_logical_bytes"]["value"] == "200000000000"
assert assumptions["assumed_bounded_logical_bytes"]["value"] == "15000000000"
assert assumptions["assumed_selected_column_logical_bytes"]["value"] == "6000000000"

alloc = rows("column-logical-bytes-AIBD-20260831.csv")
assert sum(int(r["assumed_bounded_logical_bytes"]) for r in alloc) == 15_000_000_000
assert sum(int(r["assumed_projected_selected_logical_bytes"]) for r in alloc) == 6_000_000_000
assert all(r["status"] == "assumption_not_observation" for r in alloc)

held = rows("held-observation-fields-AIBD-20260831.csv")
required_held = {
    "engine_plan", "dry_run_estimated_bytes", "processed_bytes", "scanned_bytes",
    "billed_bytes", "slots_or_dpus", "actual_rate_or_pricing", "actual_cost",
    "runtime", "job_ids", "output",
}
assert {r["field"] for r in held} == required_held
assert all(r["value"] == "" and r["status"] == "held" for r in held)

sql = " ".join((ROOT / "query-plan-fixture-AIBD-20260831.sql").read_text(encoding="utf-8").upper().split())
for fragment in (
    "SELECT CHANNEL, REVENUE, VARIABLE_COST", "ORDER_DATE >= DATE '2026-06-01'",
    "ORDER_DATE < DATE '2026-08-30'", "IS_INTERNAL = FALSE",
    "SUM(REVENUE - VARIABLE_COST)", "GROUP BY CHANNEL", "ROW_NUMBER() OVER",
    "CONTRIBUTION_RANK <= 5",
):
    assert fragment in sql, f"missing SQL control: {fragment}"
assert "SELECT *" not in sql

totals = defaultdict(Decimal)
for row in rows("static-control-input-AIBD-20260831.csv"):
    if "2026-06-01" <= row["order_date"] < "2026-08-30" and row["is_internal"].lower() == "false":
        totals[row["channel"]] += Decimal(row["revenue"]) - Decimal(row["variable_cost"])
calculated = sorted(totals.items(), key=lambda x: (-x[1], x[0]))[:5]
expected = rows("expected-control-output-AIBD-20260831.csv")
assert len(expected) == 5
for rank, ((channel, amount), row) in enumerate(zip(calculated, expected), 1):
    assert row["channel"] == channel
    assert Decimal(row["contribution"]) == amount
    assert int(row["contribution_rank"]) == rank
    assert row["result_status"] == "expected_static_control_not_observed"

source = (ROOT / "external-source-check-AIBD-20260831.md").read_text(encoding="utf-8").lower()
for phrase in ("not a third-party audit", "did not execute", "dateaccessed", "limited claims"):
    assert phrase in source
print("PASS: AIBD static assumptions, allocations, SQL controls, held fields, and expected arithmetic agree; no SQL or network action occurred.")