#!/usr/bin/env python3
"""Offline deterministic expansion; stdout only, no database/network operation."""
import hashlib
import json
import math
import struct
import subprocess
from pathlib import Path

R = Path(__file__).resolve().parent
BASE = R.parent
SQL = '''WITH "I" AS (SELECT CAST(? AS DOUBLE) AS "X" FROM DUAL),
"W" AS (SELECT "X", CAST(FLOOR("X") AS DOUBLE) AS "WHOLE" FROM "I"),
"S" AS (SELECT "X", "WHOLE", CAST(("X"-"WHOLE")*CAST(100 AS DOUBLE) AS DOUBLE) AS "SCALED" FROM "W"),
"L" AS (SELECT "X", "WHOLE", "SCALED", CAST(FLOOR("SCALED") AS DOUBLE) AS "LOWER_I" FROM "S"),
"R" AS (SELECT "X", "WHOLE", "SCALED", "LOWER_I",
 CASE WHEN "SCALED"-"LOWER_I"<CAST(0.5 AS DOUBLE) THEN "LOWER_I"
 WHEN "SCALED"-"LOWER_I">CAST(0.5 AS DOUBLE) THEN "LOWER_I"+CAST(1 AS DOUBLE)
 WHEN MOD("LOWER_I",CAST(2 AS DOUBLE))=CAST(0 AS DOUBLE) THEN "LOWER_I"
 ELSE "LOWER_I"+CAST(1 AS DOUBLE) END AS "ROUNDED_I" FROM "L"),
"Q" AS (SELECT "X", "WHOLE", "SCALED", "LOWER_I", "ROUNDED_I",
 CAST("WHOLE"+"ROUNDED_I"/CAST(100 AS DOUBLE) AS DOUBLE) AS "QUANTIZED",
 CAST(CAST(1000000 AS DOUBLE)-CAST(1 AS DOUBLE)/CAST(100 AS DOUBLE) AS DOUBLE) AS "CAP" FROM "R"),
"C" AS (SELECT "X", "WHOLE", "SCALED", "LOWER_I", "ROUNDED_I", "QUANTIZED", "CAP",
 CASE WHEN "QUANTIZED"< -"CAP" THEN -"CAP" WHEN "QUANTIZED">"CAP" THEN "CAP" ELSE "QUANTIZED" END AS "CLIPPED" FROM "Q")
SELECT "X", "WHOLE", "SCALED", "LOWER_I", "ROUNDED_I", "QUANTIZED", "CAP", "CLIPPED",
 CASE WHEN "X" IS NULL THEN 1 ELSE 0 END AS "INPUT_IS_NULL",
 CASE WHEN "SCALED"-"LOWER_I"<CAST(0.5 AS DOUBLE) THEN -1 WHEN "SCALED"-"LOWER_I">CAST(0.5 AS DOUBLE) THEN 1 WHEN "X" IS NULL THEN NULL ELSE 0 END AS "HALF_SIDE",
 CASE WHEN "QUANTIZED"< -"CAP" OR "QUANTIZED">"CAP" THEN 1 WHEN "X" IS NULL THEN NULL ELSE 0 END AS "WAS_CLIPPED",
 CAST("CLIPPED" AS REAL) AS "STORED_REAL", CAST(CAST("CLIPPED" AS REAL) AS DOUBLE) AS "STORED_DOUBLE" FROM "C"'''


def sha(p):
    return hashlib.sha256(p.read_bytes()).hexdigest()


def typed(x):
    if x is None:
        return {"is_null": True, "php_type": "NULL", "native_double_hex": None, "float17g": None}
    return {"is_null": False, "php_type": "double", "native_double_hex": struct.pack("<d", x).hex(),
            "float17g": format(x, ".17g"), "byte_order": "little"}


def model(lex):
    if lex is None:
        return {"stored": typed(None), "physical_getter": typed(None), "clipped": False}
    x = float(lex)
    w = float(math.floor(x))
    s = (x-w)*100.0
    lower = float(math.floor(s))
    f = s-lower
    ri = lower if f < 0.5 or (f == 0.5 and int(lower) % 2 == 0) else lower+1.0
    q = w+ri/100.0
    cap = 1000000.0-1.0/100.0
    clipped = -cap if q < -cap else cap if q > cap else q
    stored = struct.unpack("<f", struct.pack("<f", clipped))[0]
    return {"parse64": typed(x), "whole": typed(w), "scaled": typed(s), "rounded_i": typed(ri),
            "quantized": typed(q), "clipped": q != clipped, "stored": typed(stored),
            "physical_getter": typed(float(format(stored, ".2f")))}


def build():
    old = json.loads(subprocess.check_output(["python3", str(BASE/"analyze-evidence.py")]))
    half = json.loads(subprocess.check_output(["python3", str(BASE/"half-tie-execution-20261011/analyze-evidence.py")]))
    inventory = {}
    for p in old["lanes"]["B"]["records"]:
        if not (p["case_id"].startswith("S-") and p["case_id"].endswith("INSERT")):
            continue
        lex = p["original_lexeme"]
        g = {"is_null": lex is None, "php_type": p["native_type"], "native_double_hex": p["native_double_hex"],
             "float17g": p["native_float17g"], "byte_order": "little" if lex is not None else None}
        evidence = {"kind": "old_five_column_decimal30_projection_roundtrip_not_physical_DB_bits",
                    "case_id": p["case_id"], "column": p["column"], "server_decimal30_original": p["server_decimal30"],
                    "stored_promoted_double": typed(float(p["server_decimal30"])) if lex is not None else typed(None),
                    "physical_getter": g}
        if lex in inventory:
            assert inventory[lex]["physical_getter"] == g
            inventory[lex]["source_observations"].append({"case_id": p["case_id"], "column": p["column"]})
        else:
            inventory[lex] = {"original_lexeme": lex, "physical_getter": g, "preferred_stored_oracle": evidence,
                              "source_observations": [{"case_id": p["case_id"], "column": p["column"]}]}
    for p in half["lanes"]["B"]["points"][:24]:
        lex = p["original_lexeme"]
        t = p["reads"]["computed_point"]["typed_rows"][0]["power"]
        g = p["reads"]["native_point"]["typed_rows"][0]["power"]
        def strip(t):
            return {"is_null": False, **{k: t[k] for k in ["php_type", "native_double_hex", "float17g", "byte_order"]}}
        evidence = {"kind": "half_tie_actual_computed_native_double_bits", "case_id": p["case_id"],
                    "column": "MD", "stored_promoted_double": strip(t), "physical_getter": strip(g)}
        if lex not in inventory:
            inventory[lex] = {"original_lexeme": lex, "physical_getter": strip(g), "source_observations": []}
        else:
            assert inventory[lex]["physical_getter"] == strip(g), lex
        inventory[lex]["source_observations"].append({"case_id": p["case_id"], "column": "MD"})
        inventory[lex]["preferred_stored_oracle"] = evidence
    assert len(inventory) == 43
    oracles = list(inventory.values())
    cases = []
    for i, oracle in enumerate(oracles, 1):
        lex = oracle["original_lexeme"]
        m = model(lex)
        exp = oracle["preferred_stored_oracle"]["stored_promoted_double"]
        assert m["stored"]["native_double_hex"] == exp["native_double_hex"], lex
        assert m["physical_getter"]["native_double_hex"] == oracle["physical_getter"]["native_double_hex"], lex
        oracle["offline_model_matches_preferred_bits"] = True
        cases.append({"case_id": "S-L%02d" % i, "template": "literal_rint", "oracle_index": i-1,
                      "parameters": [{"position": 1, "original_value": lex, "runtime_value": lex,
                                      "php_type": "NULL" if lex is None else "string",
                                      "pdo_type_code": 0 if lex is None else 2,
                                      "pdo_type": "PDO::PARAM_NULL" if lex is None else "PDO::PARAM_STR",
                                      "conversion": "identity; no pre-round/no float conversion"}]})
    sentinels = ["0.125", "0.005000000000000001", "12.344999999999999", "999999.995"]
    for i, lex in enumerate(sentinels, 1):
        cases.append({"case_id": "OS-L%02d" % i, "template": "literal_rint", "oracle_index": next(j for j,o in enumerate(oracles) if o["original_lexeme"]==lex),
                      "parameters": [{"position": 1, "original_value": lex, "runtime_value": lex, "php_type": "string",
                                      "pdo_type_code": 1, "pdo_type": "PDO::PARAM_INT",
                                      "conversion": "identity original string, do not truncate just because INT bind"}]})
    return {"status": "PROPOSED_PENDING_ROOT_REVIEW_NOT_EXECUTED", "scope": "C-only literal SELECT; no natural-table references, no new vectors or B database calls",
            "source_pin": {"MySQL": "9bb243a3fa252257468bbcd98f668bfe15bed48c", "field_cc_lines": "4028-4062,4290-4334",
                           "source_algorithm": "double parse -> floor/fraction*100 -> rint-nearest-even -> /100 + floor -> clip -> float32",
                           "FENV": "nearest-even inferred from exact ties and source; actual FENV not independently measured",
                           "DM_optimizer_and_literal_parse": "unproven; proposed intermediate observations localize differences, CASE is not a repair"},
            "baseline_sha256": {"original_plan": sha(BASE/"probe-plan.json"), "old_execution_seal": sha(BASE/"execution-seal.json"),
                                "B_old_raw": sha(BASE/"private-evidence/recovered-execute-B.jsonl"),
                                "half_plan": sha(BASE/"primary-source-derivation-20261011/half-tie-probe-proposal.json"),
                                "half_seal": sha(BASE/"half-tie-execution-20261011/seal.json"),
                                "B_half_raw": sha(BASE/"half-tie-execution-20261011/private-evidence/B-run.jsonl"),
                                "signed_zero_verification": sha(BASE/"half-tie-execution-20261011/signed-zero-verification.json")},
            "connection": {"lane": "C", "host": "127.0.0.1", "port": 5237, "schema": "ZHCT_APP", "original_worker_pid": 112175,
                           "cmdline_root_identity_required": "/tmp/zhct-dm-poc-20260928.fcpiiN/store", "isolated_config_require": "application/database.php",
                           "env_whitelist": ["POC_HOST","POC_PORT","POC_SCHEMA","POC_USER","POC_PASSWORD","POC_DIALECT","POC_EXTERNAL_STUBS","POC_REAL_QUEUE","PATH","LANG","LC_ALL","LC_CTYPE","TZ","NLS_LANG","DM_CHARSET","DM_SVC_PATH","DM_HOME","LD_LIBRARY_PATH"],
                           "secrets": "never print/persist; drop all other environment keys", "persistent": False,
                           "sqlmode_charset_driver_changes": "forbidden"},
            "budget": {"C_SELECT_executions": 49, "C_prepare_only_attempts": 1, "C_total_diagnostic_attempts": 50,
                       "ceiling": 64, "identity_executions": 2, "literal_executions": 47, "DDL": 0, "DML": 0, "natural": 0,
                       "HTTP": 0, "B_SQL": 0, "max_rows_identity_version": 16, "exact_rows_identity_schema_and_literals": 1, "max_serialized_bytes_each": 65536,
                       "retry": 0, "begin_commit_rollback": 0},
            "templates": {"identity_schema": "SELECT SYS_CONTEXT('USERENV','CURRENT_SCHEMA') AS SCHEMA_NAME FROM DUAL", "identity_version": "SELECT * FROM V$VERSION", "literal_rint": SQL},
            "sequence": ["identity_schema once; exact ZHCT_APP", "identity_version once; archive actual unchanged server identity", "prepare-only literal_rint once", "all cases in listed order; single existing session, no retries"],
            "cases": cases, "oracles": oracles,
            "guards": ["exact SQL template hashes and ordered parameters only; WITH is not a general allowlist", "no catalog, user table or schema writes",
                       "stop on unknown SQL/prepare/type/row/bytes failure; persist original errorInfo before stopping; no rewritten SQL or rebind",
                       "persist typed original params, native value/type/float17g/native_double_hex/byte_order and column metadata",
                       "C warning status unknown; do not fabricate zero warning rows", "signed-zero oracle exclusively original raw strings; null remains null",
                       "evaluate stored-double vs getter contracts separately; no aggregate or alias-based round2",
                       "source model is offline assumption not actual C evidence; old decimal30 roundtrip oracle labelled, not DB raw bits"],
            "after_result": "Root reviews exact matches and remaining differences before any business narrow fix. No further probe/table/HTTP is implicitly authorized."}


if __name__ == "__main__":
    print(json.dumps(build(), ensure_ascii=False, indent=2))