# critwire Learn: /learn/labs/e4.py
# Run it after the lab loader (/learn/data/postgis/learn_curriculum.sql), on the same database, in the environment
# /learn/labs/requirements.txt pins (the setup page, /learn/setup/, gives the uv commands):
#   python e4.py
# It connects with libpq's PG* variables (PGHOST, PGPORT, PGDATABASE, PGUSER, PGPASSWORD) and reads the lab data from
# LEARN_BASE (default https://critwire.com). Each block runs once, in page order, after py-connect and the boot setup;
# an untested block is commented out under its reason.

# %% py-connect
# q: py-connect
# status: ran
import psycopg


def connect():
    """A connection to the lab database: plain table names find learn_curriculum's tables, then PostGIS in public."""
    return psycopg.connect(options="-c search_path=learn_curriculum,public")

# %% e4-derived
# q: e4-derived
# status: ran
# after: py-connect
import json

DOWNLOAD_DOI = "10.15468/dl.fbpk9w"  # the GBIF download the records came from

with connect() as conn:
    counts = dict(conn.execute("""
        SELECT CAST(o.dataset_key AS text), count(*)
        FROM occurrences AS o JOIN species AS s ON s.id = o.species_id
        WHERE s.scientific_name IN ('Sarcophilus harrisii', 'Panthera onca')
        GROUP BY o.dataset_key ORDER BY o.dataset_key""").fetchall())

# The body of POST https://api.gbif.org/v1/derivedDataset: what GBIF needs to mint a DOI that credits each dataset.
body = {
    "title": "Tasmanian devil and jaguar records used in my analysis",
    "description": "A subset of GBIF download 10.15468/dl.fbpk9w: open records of two species.",
    "originalDownloadDOI": DOWNLOAD_DOI,
    "sourceUrl": "https://example.org/where-my-subset-is-published",
    "relatedDatasets": counts,
}
print(f"relatedDatasets: {len(counts)} datasets, {sum(counts.values())} records")
for key, n in sorted(counts.items(), key=lambda kv: (-kv[1], kv[0]))[:3]:
    print(f"  {key}: {n}")
print(f"body: {len(json.dumps(body))} bytes, ready to POST with your GBIF account")

# %% e4-ex
# q: e4-ex
# status: ran
# after: py-connect
# The row-level reject checks: each one's name, and the condition a breaking row meets.
REJECTS = [
    ("coordinates out of range", "longitude NOT BETWEEN -180 AND 180 OR latitude NOT BETWEEN -90 AND 90"),
    ("coordinates exactly 0, 0", "longitude = 0 AND latitude = 0"),
    ("coordinate uncertainty negative, NaN or infinite",
     "NOT (coordinate_uncertainty_m >= 0 AND coordinate_uncertainty_m < 'Infinity')"),
    ("year after the download (2026)", "year > 2026"),
]


def failures(cur):
    """Each reject check the staged rows break: its name, how many rows, the lowest key."""
    found = []
    for name, condition in REJECTS:
        n, first = cur.execute(f"SELECT count(*), min(id) FROM staged WHERE {condition}").fetchone()
        if n:
            found.append((name, n, first))
    return found


def report(label, cur):
    rows = cur.execute("SELECT count(*) FROM staged").fetchone()[0]
    found = failures(cur)
    print(f"{label} ({rows:,} rows): {'refused' if found else 'every reject check 0: load'}")
    for name, n, first in found:
        print(f"  {name}: {n} (first {first})")


with connect() as conn, conn.cursor() as cur:
    # CREATE TABLE AS copies no CHECK, as a staging table wouldn't have the target's
    cur.execute("""CREATE TEMP TABLE staged AS
                   SELECT o.id, o.longitude, o.latitude, o.coordinate_uncertainty_m, o.year
                   FROM occurrences AS o JOIN species AS s ON s.id = o.species_id
                   WHERE s.scientific_name = 'Sarcophilus harrisii'""")
    report("the devil's records", cur)
    cur.execute("""INSERT INTO staged VALUES (9000000001, 0, 0, NULL, 2019),
                                             (9000000002, 146.8, -41.4, 'NaN', 2031)""")
    report("with two made-up records", cur)
    conn.rollback()