#!/usr/bin/env python3
"""Offline, no DB/HTTP; expand explicit synthetic fixture templates for review."""
import hashlib
import json
from pathlib import Path

HERE = Path(__file__).resolve().parent


def expand(lane):
    raw_path = HERE / ("private/preflight-stage2-B.json" if lane == "B" else "private-evidence/C/preflight-stage2-C.json")
    raw = json.loads(raw_path.read_text())
    template = json.loads((HERE / "fixture-values-template.json").read_text())
    tenants = {"store": 2147001111, "terminal": 2147001112}
    users = {int(row["store_id"]): row for row in raw["active_users"]}
    operators = {int(row["store_id"]): row for row in raw["marked_operator"]}
    assert set(users) == set(tenants.values()) == set(operators)
    plans = []

    def add(table, values, ref):
        quote = '"' if lane == "C" else '`'
        cols = list(values)
        actual_cols = {row["column_name"].lower() for row in raw["schema"][table]["columns"]}
        expected = set(cols) | {"id"}
        if table == "staff" and lane == "C":
            assert expected - actual_cols == {"img"} and not actual_cols - expected
        else:
            assert expected == actual_cols, (table, expected ^ actual_cols)
        assert raw["schema"][table]["FK"] == raw["schema"][table]["triggers"] == 0
        sql = 'INSERT INTO ' + quote + ('YOSHOP_' + table.upper() if lane == "C" else 'yoshop_' + table) + quote
        sql += ' (' + ','.join(quote + (col.upper() if lane == "C" else col) + quote for col in cols) + ') VALUES (' + ','.join('?' for _ in cols) + ')'
        assert sql.count('?') == len(values)
        plans.append({"table": table, "generated_reference": ref, "sql": sql,
                      "bindings": [{"position": i + 1, "value": value, "pdo_type": 2, "transform": "string cast immediately before bind, resolved owned ID only"}
                                   for i, value in enumerate(values.values())], "values": values})

    for module, tenant in tenants.items():
        values = dict(template["staff"])
        user = users[tenant]
        assert user["nick_name"] == "CDXLF11F86A_" + module and int(user["is_delete"]) == 0
        values.update(name="CDXRR11A8F4_" + module, img="/cdx/CDXRR11A8F4_" + module + ".png",
                      user_id=int(user["user_id"]), store_id=tenant)
        add("staff", values, "@staff_" + module)

    operator_id = int(operators[tenants["store"]]["store_user_id"])
    assert int(operators[tenants["store"]]["is_super"]) == 1
    for survey_type in template["expansion"]["survey_types"]:
        values = dict(template["nutrition_meals"])
        values.update(user_id=operator_id, type=survey_type,
                      other_text="CDXRR11A8F4_SURVEY_" + str(survey_type))
        add("nutrition_meals", values, "@master_" + str(survey_type))
    for answer in template["expansion"]["answers"]:
        assert any(int(row["enum_type"]) == answer["type"] and int(row["enum_key"]) == answer["reference_key"]
                   for row in raw["enum_dictionary"])
        values = dict(template["nutrition_lack"])
        values.update(user_id=operator_id, master_id="@master_" + str(answer["master_type"]),
                      reference_id=answer["reference_key"], type=answer["type"], v1=answer["v1"], v2=answer["v2"],
                      recommend="CDXRR11A8F4_RECOMMEND_" + str(answer["type"]))
        add("nutrition_lack", values, "@answer_" + str(len(plans)))
    values = dict(template["nurtrition"])
    values["user_id"] = operator_id
    add("nurtrition", values, "@nurtrition_store")
    assert len(plans) == 13
    return {"lane": lane, "source_preflight_sha256": hashlib.sha256(raw_path.read_bytes()).hexdigest(),
            "requires_root_img_schema_alignment": lane == "C", "INSERT": 13, "UPDATE": 0, "DELETE": 0, "DDL": 0,
            "plans": plans}


if __name__ == "__main__":
    for lane in ["B", "C"]:
        result = expand(lane)
        target = HERE / ("fixture-proposal-" + lane + ".json")
        target.write_text(json.dumps(result, ensure_ascii=False, indent=2) + "\n")
        print(json.dumps({"lane": lane, "INSERT": result["INSERT"], "path": target.name,
                          "sha256": hashlib.sha256(target.read_bytes()).hexdigest()}))