"""Python 3 stdlib typed import and Python/SQLite parity reference."""
import argparse
import csv
import json
import sqlite3
from pathlib import Path

TABLE_FILES = {"projects": "projects.csv", "observations": "observations.csv", "sources": "sources.csv", "coverage": "coverage.csv"}
QUERY_FIELDS = ("observation_id", "project_key", "metal", "country_code", "name_en", "metric", "value", "unit", "period_start", "period_end", "year", "effective_date", "basis", "ownership_basis", "entity_scope", "source_keys")


def unescape(value):
    if value.startswith("'") and (value[1:].startswith("'") or value[1:].lstrip().startswith(("=", "+", "-", "@"))):
        return value[1:]
    return value


def convert(value, kind):
    if value == "":
        return None
    if kind == "integer":
        return int(value)
    if kind == "number":
        return float(value) if any(c in value.lower() for c in (".", "e")) else int(value)
    if kind == "boolean":
        if value not in {"true", "false"}:
            raise ValueError("Invalid boolean: " + value)
        return value == "true"
    if kind == "array[string]":
        parsed = json.loads(value)
        if not isinstance(parsed, list) or not all(isinstance(x, str) for x in parsed):
            raise ValueError("Invalid array[string]")
        return parsed
    return unescape(value)


def load_tables(folder):
    folder = Path(folder)
    with (folder / "data-dictionary.csv").open(encoding="utf-8-sig", newline="") as handle:
        dictionary = list(csv.DictReader(handle))
    types = {(row["table"], row["field"]): row["type"] for row in dictionary}
    tables = {}
    for table, filename in TABLE_FILES.items():
        with (folder / filename).open(encoding="utf-8-sig", newline="") as handle:
            tables[table] = [{field: convert(value, types[table, field]) for field, value in row.items()} for row in csv.DictReader(handle)]
    return tables, types


def python_query(tables):
    projects = {p["project_key"]: p for p in tables["projects"]}
    result = []
    for o in tables["observations"]:
        p = projects[o["project_key"]]
        if p["country_code"] in {"CN", "KR"} and p["metal"] in {"lead", "zinc"} and p["layer"] in {"processing", "capacity", "smelter", "refinery"} and o["metric"] in {"production", "capacity"}:
            result.append({f: p[f] if f in {"project_key", "metal", "country_code", "name_en"} else o[f] for f in QUERY_FIELDS})
    return sorted(result, key=lambda r: (r["project_key"], r["metric"], r["observation_id"]))


def sqlite_query(tables, types, query_path):
    with sqlite3.connect(":memory:") as db:
        db.row_factory = sqlite3.Row
        for table, rows in tables.items():
            fields = [f for t, f in types if t == table]
            # NUMERIC preserves integer quantities above 2**53; REAL rounds them.
            sql_types = {"number": "NUMERIC", "integer": "INTEGER", "boolean": "INTEGER"}
            columns = ",".join('"' + f.replace('"', '""') + '" ' + sql_types.get(types[table, f], "TEXT") for f in fields)
            db.execute('CREATE TABLE "' + table + '" (' + columns + ')')
            values = [[json.dumps(row[f], ensure_ascii=False, separators=(",", ":")) if isinstance(row[f], list) else row[f] for f in fields] for row in rows]
            db.executemany('INSERT INTO "' + table + '" VALUES (' + ','.join('?' for _ in fields) + ')', values)
        result = [dict(row) for row in db.execute(Path(query_path).read_text(encoding="utf-8"))]
        for row in result:
            row["source_keys"] = json.loads(row["source_keys"])
        return result


def run(folder):
    tables, types = load_tables(folder)
    py = python_query(tables)
    sql = sqlite_query(tables, types, Path(__file__).with_suffix(".sql"))
    if py != sql:
        raise ValueError("Python/SQL parity failure")
    return {"python_sql_parity": True, "project_rows": len(tables["projects"]), "observation_rows": len(tables["observations"]), "query_rows": len(py), "query": py}


if __name__ == "__main__":
    parser = argparse.ArgumentParser(description=__doc__)
    parser.add_argument("snapshot_dir", nargs="?", default=".")
    args = parser.parse_args()
    print(json.dumps(run(args.snapshot_dir), ensure_ascii=False, indent=2))