#!/usr/bin/env python3
"""Local JSON-to-SQLite projection experiment, Python 3.9+.

Run without arguments. All inputs are fictional. Only a temporary SQLite file
is written. No NetSuite, SuiteScript, HTTP or ERP operation is executed.
The profile, aliases, value types and size budgets below are local policies,
not a NetSuite response schema or native validation specification.
"""
from pathlib import Path
from tempfile import TemporaryDirectory
import json
import re
import sqlite3


PROFILE = "alias-v1"
MAX_TEXT = 16_384
MAX_ROWS = 100
MAX_ID = 2**63 - 1
SOURCE_KEYS = {"sourceid", "erpref"}


class ProjectionError(ValueError):
    def __init__(self, code):
        super().__init__(code)
        self.code = code


def object_from_pairs(pairs):
    result = {}
    folded = set()
    for key, value in pairs:
        if not re.fullmatch(r"[A-Za-z_][A-Za-z0-9_]*", key):
            raise ProjectionError("alias_format")
        if key in result:
            raise ProjectionError("duplicate_json_key")
        if key.lower() in folded:
            raise ProjectionError("case_collision")
        folded.add(key.lower())
        result[key] = value
    return result


def reject_constant(_value):
    raise ProjectionError("non_json_number")


def decode_rows(text):
    if not isinstance(text, str):
        raise ProjectionError("payload_type")
    if len(text) > MAX_TEXT:
        raise ProjectionError("text_budget")
    try:
        rows = json.loads(text, object_pairs_hook=object_from_pairs,
                          parse_constant=reject_constant)
    except json.JSONDecodeError as error:
        raise ProjectionError("invalid_json") from error
    if not isinstance(rows, list):
        raise ProjectionError("root_type")
    if len(rows) > MAX_ROWS:
        raise ProjectionError("row_budget")
    return rows


def project_row(row):
    if not isinstance(row, dict):
        raise ProjectionError("row_type")
    missing = SOURCE_KEYS - row.keys()
    if missing:
        if any(key.lower() in missing for key in row):
            raise ProjectionError("alias_case")
        raise ProjectionError("alias_missing")
    if row.keys() - SOURCE_KEYS:
        raise ProjectionError("alias_extra")
    source_id, reference = row["sourceid"], row["erpref"]
    if source_id is None or reference is None:
        raise ProjectionError("value_null")
    if type(source_id) is not int:
        raise ProjectionError("id_type")
    if not 1 <= source_id <= MAX_ID:
        raise ProjectionError("id_range")
    if not isinstance(reference, str):
        raise ProjectionError("reference_type")
    if not reference.strip():
        raise ProjectionError("reference_empty")
    # The source names are explicit; the business value is not case-folded.
    return {"sourceId": source_id, "erpReference": reference}


def connect(path):
    db = sqlite3.connect(str(path), isolation_level=None)
    db.execute("PRAGMA foreign_keys=ON")
    return db


def initialize(path):
    db = connect(path)
    try:
        db.executescript("""
            CREATE TABLE batches (
                batch_id TEXT PRIMARY KEY,
                profile TEXT NOT NULL
            );
            CREATE TABLE projection (
                batch_id TEXT NOT NULL REFERENCES batches(batch_id),
                source_id INTEGER NOT NULL,
                erp_reference TEXT NOT NULL,
                PRIMARY KEY(batch_id, source_id)
            );
        """)
    finally:
        db.close()


def stage(path, batch_id, text, profile=PROFILE):
    if profile != PROFILE:
        raise ProjectionError("profile_version")
    if not isinstance(batch_id, str) or not re.fullmatch(r"[a-z][a-z0-9-]{0,32}", batch_id):
        raise ProjectionError("batch_identity")
    rows = decode_rows(text)
    db = connect(path)
    output = []
    try:
        db.execute("BEGIN")
        db.execute("INSERT INTO batches VALUES(?,?)", (batch_id, profile))
        for row in rows:
            mapped = project_row(row)
            db.execute("INSERT INTO projection VALUES(?,?,?)",
                       (batch_id, mapped["sourceId"], mapped["erpReference"]))
            output.append(mapped)
        db.commit()
        return output
    except sqlite3.IntegrityError as error:
        db.rollback()
        raise ProjectionError("storage_constraint") from error
    except BaseException:
        db.rollback()
        raise
    finally:
        db.close()


def snapshot(path):
    db = connect(path)
    try:
        return {
            "batches": db.execute("SELECT * FROM batches ORDER BY batch_id").fetchall(),
            "rows": db.execute("SELECT * FROM projection ORDER BY batch_id,source_id").fetchall(),
        }
    finally:
        db.close()


def main():
    checks = []

    def check(name, condition):
        if not condition:
            raise AssertionError(name)
        checks.append(name)

    def fails(name, code, operation):
        try:
            operation()
        except ProjectionError as error:
            check(name, error.code == code)
        else:
            raise AssertionError(name + ": expected rejection")

    good = [{"sourceid": 12345, "erpref": "Cli-Ab-007"},
            {"sourceid": 12346, "erpref": "CLI-AB-007"}]
    text = json.dumps(good)
    check("camel_case_lookup_loses_present_id", good[0].get("sourceId") is None
          and good[0]["sourceid"] == 12345)
    check("lowercasing_values_would_merge_fictional_references",
          good[0]["erpref"] != good[1]["erpref"]
          and good[0]["erpref"].lower() == good[1]["erpref"].lower())
    expected = [{"sourceId": 12345, "erpReference": "Cli-Ab-007"},
                {"sourceId": 12346, "erpReference": "CLI-AB-007"}]
    before_row = dict(good[0])
    check("explicit_projection_keeps_value_and_target_names", project_row(good[0]) == expected[0])
    check("projection_does_not_rename_source_object", good[0] == before_row)

    with TemporaryDirectory() as directory:
        path = Path(directory) / "projection.sqlite"
        initialize(path)
        check("validated_batch_returns_both_projections", stage(path, "safe", text) == expected)
        baseline = snapshot(path)
        check("reopened_database_keeps_both_exact_references", baseline == {
            "batches": [("safe", PROFILE)],
            "rows": [("safe", 12345, "Cli-Ab-007"), ("safe", 12346, "CLI-AB-007")],
        })

        cases = [
            ("wrong_alias_case", '[{"sourceId":1,"erpref":"X"}]', "alias_case"),
            ("missing_source_alias", '[{"erpref":"X"}]', "alias_missing"),
            ("different_alias_spelling", '[{"source_id":1,"erpref":"X"}]', "alias_missing"),
            ("missing_reference_alias", '[{"sourceid":1}]', "alias_missing"),
            ("null_id_is_not_missing_alias", '[{"sourceid":null,"erpref":"X"}]', "value_null"),
            ("null_reference_is_not_missing_alias", '[{"sourceid":1,"erpref":null}]', "value_null"),
            ("boolean_id", '[{"sourceid":true,"erpref":"X"}]', "id_type"),
            ("text_id", '[{"sourceid":"1","erpref":"X"}]', "id_type"),
            ("float_id", '[{"sourceid":1.0,"erpref":"X"}]', "id_type"),
            ("negative_id", '[{"sourceid":-1,"erpref":"X"}]', "id_range"),
            ("zero_id", '[{"sourceid":0,"erpref":"X"}]', "id_range"),
            ("sqlite_integer_boundary", '[{"sourceid":9223372036854775808,"erpref":"X"}]', "id_range"),
            ("numeric_reference", '[{"sourceid":1,"erpref":12}]', "reference_type"),
            ("nested_reference", '[{"sourceid":1,"erpref":{"value":"X"}}]', "reference_type"),
            ("empty_reference", '[{"sourceid":1,"erpref":""}]', "reference_empty"),
            ("blank_reference", '[{"sourceid":1,"erpref":"  "}]', "reference_empty"),
            ("extra_alias_strict_local_profile", '[{"sourceid":1,"erpref":"X","unused":2}]', "alias_extra"),
            ("row_is_not_object", '[12]', "row_type"),
            ("root_is_not_array", '{"sourceid":1,"erpref":"X"}', "root_type"),
            ("malformed_json", '[', "invalid_json"),
            ("exact_duplicate_json_key", '[{"sourceid":1,"sourceid":2,"erpref":"X"}]', "duplicate_json_key"),
            ("case_collision_before_mapping", '[{"sourceid":1,"sourceId":2,"erpref":"X"}]', "case_collision"),
            ("escaped_duplicate_json_key", r'[{"sourceid":1,"\u0073ourceid":2,"erpref":"X"}]', "duplicate_json_key"),
            ("non_ascii_alias_local_policy", '[{"sourceid":1,"référence":"X"}]', "alias_format"),
            ("nan_is_not_json", '[{"sourceid":NaN,"erpref":"X"}]', "non_json_number"),
            ("non_text_payload", None, "payload_type"),
            ("text_budget_local_policy", " " * (MAX_TEXT + 1), "text_budget"),
            ("row_budget_local_policy", json.dumps([good[0]] * (MAX_ROWS + 1)), "row_budget"),
        ]
        for number, (name, payload, code) in enumerate(cases):
            fails(name, code, lambda payload=payload, number=number:
                  stage(path, "invalid-" + str(number), payload))
        check("all_invalid_inputs_leave_previous_inventory_unchanged", snapshot(path) == baseline)

        late = json.dumps([good[0], {"sourceId": 12347, "erpref": "Late"}])
        fails("late_alias_error_is_classified", "alias_case", lambda: stage(path, "late", late))
        check("late_alias_error_rolls_back_batch_and_first_row", snapshot(path) == baseline)
        duplicated = json.dumps([good[0], {"sourceid": 12345, "erpref": "Other"}])
        fails("duplicate_local_identity_is_rejected", "storage_constraint",
              lambda: stage(path, "duplicate", duplicated))
        check("duplicate_identity_rolls_back_the_whole_local_batch", snapshot(path) == baseline)
        fails("existing_batch_is_not_silently_replaced", "storage_constraint",
              lambda: stage(path, "safe", text))
        fails("unknown_profile_is_not_silently_accepted", "profile_version",
              lambda: stage(path, "version", text, "alias-v2"))
        fails("empty_batch_identity_is_rejected", "batch_identity",
              lambda: stage(path, "", text))
        check("retry_and_profile_failures_preserve_previous_projection", snapshot(path) == baseline)
        check("explicit_empty_batch_returns_empty_projection", stage(path, "empty", "[]") == [])
        after_empty = snapshot(path)
        check("empty_batch_has_a_profile_without_inventing_a_record",
              after_empty["rows"] == baseline["rows"]
              and after_empty["batches"] == [("empty", PROFILE), ("safe", PROFILE)])
        unusual = '[{"sourceid":12347,"erpref":"  Réf-Ab-0007  "}]'
        check("nonempty_reference_is_preserved_without_trim_or_case_change",
              stage(path, "unusual", unusual)[0]["erpReference"] == "  Réf-Ab-0007  ")

    print(json.dumps({
        "checks": len(checks), "check_names": checks,
        "valid_projection_rows": 2, "late_error_committed_rows": 0,
        "business_reference_case_preserved": True,
        "duplicate_json_keys_rejected_before_dict_loss": True,
        "http_calls": 0, "native_nquery_executions": 0, "erp_writes": 0,
        "temporary_sqlite_writes": True,
    }, ensure_ascii=False, indent=2))


if __name__ == "__main__":
    main()