import json
import re
import ssl
import urllib.parse
import urllib.request
from datetime import datetime, timezone

import certifi
import pandas as pd
from IPython.display import Markdown, display

UA = "edmonton-tax-viz/school-coverage (open-data quality check)"
SSL_CTX = ssl.create_default_context(cafile=certifi.where())

RUN_AT = datetime.now(timezone.utc)

# ⚠️ THIS REPORT IS A SNAPSHOT, and the published page has to say so on its own
# face — it is the artifact that gets handed to someone, usually without the
# index page that would otherwise date it. Two of these four carried NO date at
# all until 2026-08-29.
#
# FIRST_MEASURED is when the finding was made and does NOT change on re-run.
# _STAMPED_AT is when this page was last re-executed against live data. Both are
# shown: "first found on" and "still true today" are different claims and a
# reader needs each. Imports are aliased and local to this block so it can be
# pasted into any of these notebooks unchanged.
import datetime as _dt

from IPython.display import Markdown as _Md, display as _disp

FIRST_MEASURED = "2026-08-29"
_STAMPED_AT = _dt.datetime.now(_dt.timezone.utc)

_disp(_Md(
    f"**Snapshot.** Finding first measured **{FIRST_MEASURED}**; this page "
    f"re-executed against live data **{_STAMPED_AT:%Y-%m-%d}** (UTC). "
    f"Nothing re-runs these on a schedule — re-execute before citing a figure."))

CHECKS: list[tuple[bool, str]] = []


def check(ok: bool, claim: str) -> None:
    CHECKS.append((bool(ok), claim))
    print(f"  [{'PASS' if ok else 'FAIL'}] {claim}")


def _read(url: str, timeout: int = 300) -> bytes:
    req = urllib.request.Request(url, headers={"User-Agent": UA})
    with urllib.request.urlopen(req, context=SSL_CTX, timeout=timeout) as r:
        return r.read()


def soda(dataset: str, params: dict) -> list[dict]:
    """One Socrata query. ⚠️ urlencode is required — a raw space in $select
    (as in `count(*) as n`) raises InvalidURL before the request is made."""
    return json.loads(_read(
        f"https://data.edmonton.ca/resource/{dataset}.json?"
        + urllib.parse.urlencode(params)))


def catalog(query: str, limit: int = 400) -> list[dict]:
    """Search the PUBLIC Socrata catalogue for this domain — the same index the
    portal's own search box uses, so a negative here is a negative for anyone
    looking for the data by hand."""
    url = ("https://api.us.socrata.com/api/catalog/v1?"
           + urllib.parse.urlencode({"domains": "data.edmonton.ca",
                                     "q": query, "limit": limit}))
    return json.loads(_read(url))["results"]


print(f"run at: {RUN_AT:%Y-%m-%d %H:%M} UTC")

run at: 2026-10-03 17:02 UTC

PUBLISHED = {
    "996c-239n": "EPSB School Locations (Edmonton Public)",
    "gfxq-u8uu": "Edmonton Catholic Schools (Current)",
}

rows = []
for ds, label in PUBLISHED.items():
    n = int(soda(ds, {"$select": "count(1)"})[0]["count_1"])
    rows.append({"dataset": ds, "name": label, "rows": n})

published = pd.DataFrame(rows)
display(published)

total_published = int(published["rows"].sum())
print(f"\ntotal published school points: {total_published}")

total published school points: 322

FALSIFYING = [
    "private school",
    "charter school",
    "independent school",
    "francophone",
    "Centre-Nord",          # the francophone authority serving Edmonton
]

# A hit only counts if the dataset is plausibly a school POINT SET for one of
# the missing operators — the loose match returns playgrounds and survey
# results otherwise, and counting those as hits would flatter the portal.
MISSING_TERMS = ("private", "charter", "independent", "francophone",
                 "centre-nord", "centre nord")
SCHOOL_TERMS = ("school", "ecole", "école")

found = []
search_rows = []
for q in FALSIFYING:
    res = catalog(q)
    relevant = [r for r in res
                if any(t in r["resource"]["name"].lower() for t in MISSING_TERMS)
                and any(t in r["resource"]["name"].lower() for t in SCHOOL_TERMS)]
    found.extend(relevant)
    search_rows.append({
        "search": q,
        "results": len(res),
        "naming a missing operator AND a school": len(relevant),
        "example result": res[0]["resource"]["name"][:52] if res else "—",
    })

display(pd.DataFrame(search_rows))

broad = catalog("school")


def operator_of(name: str) -> str:
    n = name.lower()
    if any(t in n for t in MISSING_TERMS):
        return "a MISSING operator"
    if "catholic" in n:
        return "Edmonton Catholic (ECSD)"
    if "public school" in n or "epsb" in n:
        return "Edmonton Public (EPSB)"
    return "not a school-operator dataset"


broad_df = pd.DataFrame([
    {"name": r["resource"]["name"], "operator": operator_of(r["resource"]["name"])}
    for r in broad
])
display(broad_df["operator"].value_counts().rename("datasets").to_frame())

missing_hits = broad_df[broad_df["operator"] == "a MISSING operator"]
print(f'datasets naming a missing operator: {len(missing_hits)}')
if len(missing_hits):
    display(missing_hits)

datasets naming a missing operator: 0

roll_cols = list(soda("q7d6-ambg", {"$limit": 1})[0].keys())
roll_n = int(soda("q7d6-ambg", {"$select": "count(1)"})[0]["count_1"])

print(f"q7d6-ambg — {roll_n:,} rows, {len(roll_cols)} columns:")
for c in roll_cols:
    print("   ", c)

USE_FIELDS = ("use", "occupancy", "building", "description", "type", "purpose")
# ⚠️ Matched on WORD BOUNDARIES, not as substrings. A plain `in` test reports
# `house_number` as a land-use field (it contains "use") and the invariant below
# then fails for a reason that has nothing to do with the City's data — which is
# exactly what happened on the first run of this notebook.
land_use_cols = [c for c in roll_cols
                 if any(re.search(rf"(^|_){t}(_|$)", c.lower()) for t in USE_FIELDS)]
print(f"\ncolumns describing land use or occupancy: {land_use_cols or 'NONE'}")

q7d6-ambg — 439,696 rows, 15 columns:
    account_number
    neighbourhood_id
    neighbourhood
    ward
    assessed_value
    tax_class
    garage
    mill_class_1
    tax_class_pct_1
    latitude
    longitude
    point_location
    :@computed_region_ecxu_fw7u
    :@computed_region_ueb5_7sqn
    :@computed_region_7ccj_gre3

columns describing land use or occupancy: NONE

# Without this, a catalogue that answers 200 with no results passes the next
# check: nothing searched reads the same as nothing found. It is a floor on
# the five together, not on each — "charter school" and "Centre-Nord" return
# nothing at all on a healthy catalogue (measured 2026-09-23: 15/0/6/1/0).
check(sum(r["results"] for r in search_rows) > 0,
      "the falsifying searches returned at least one result between them — "
      "the catalogue answered")
check(len(found) == 0,
      "no falsifying search returned a point set for a private, charter or "
      "francophone school")
check(len(missing_hits) == 0,
      "no dataset in the broad 'school' search names a missing operator")
check(total_published > 0 and len(published) == 2,
      f"exactly two school point sets are published, carrying "
      f"{total_published} schools between them")
check(not land_use_cols,
      "the current roll carries no land-use or occupancy field, so schools "
      "cannot be identified from it")

passed = sum(1 for ok, _ in CHECKS if ok)
display(Markdown(f"### {passed} of {len(CHECKS)} invariants passed"))
for ok, claim in CHECKS:
    display(Markdown(f"- {'✅' if ok else '❌'} {claim}"))
display(Markdown(f"_Checked against `data.edmonton.ca` on "
                 f"{RUN_AT:%Y-%m-%d} (UTC)._"))
if passed != len(CHECKS):
    raise AssertionError(f"{len(CHECKS) - passed} invariant(s) failed — see above")

  [PASS] the falsifying searches returned at least one result between them — the catalogue answered
  [PASS] no falsifying search returned a point set for a private, charter or francophone school
  [PASS] no dataset in the broad 'school' search names a missing operator
  [PASS] exactly two school point sets are published, carrying 322 schools between them
  [PASS] the current roll carries no land-use or occupancy field, so schools cannot be identified from it

Edmonton's open data covers two school authorities, not all of them¶

The one methodological point worth reading even if nothing else¶

Reproducing¶

1. What Edmonton publishes¶

2. Searching for what is missing¶

Every school dataset on the portal, classified¶

3. Why a consumer cannot work around it¶

4. The consequence, and its direction¶

5. Invariants¶

5 of 5 invariants passed¶

	dataset	name	rows
0	996c-239n	EPSB School Locations (Edmonton Public)	225
1	gfxq-u8uu	Edmonton Catholic Schools (Current)	97

	search	results	example result
0	private school	15	Map View: Playgrounds
1	charter school	0	—
2	independent school	6	Developer Arterial Projects - Deprecated
3	francophone	1	2016 Census - Population by School Residency (...
4	Centre-Nord	0	—

	datasets
operator
not a school-operator dataset	360
Edmonton Public (EPSB)	24
Edmonton Catholic (ECSD)	16