# Luchtmeetnet stale and null readings: the ingest_air.py path (flag_bad_readings, to_db_rows)
# replayed on the unit-test fixture of tests/test_data_quality.py. Generated on 1 October 2026
# with a short script that imports ingest_air.py as the container does; no database, S3 or network.
# Why a replay: by 12:30 UTC on 1 October the real NL10240 feed had produced no null and no frozen
# value (52 NO2 rows in RDS, 0 flagged, 0 null; no DATA_QUALITY_ERROR in logs/air.log), so the
# behaviour is proven by the tests (91+ pass) and by this trace, not yet by production data.

## 1. Input page, newest first as the API returns it (None = JSON null)
component  value                 timestamp
      NO2   12.0 2026-10-01T05:00:00+00:00
      NO2    NaN 2026-10-01T04:00:00+00:00
      NO2   30.0 2026-10-01T03:00:00+00:00
      NO2   30.0 2026-10-01T02:00:00+00:00
      NO2   30.0 2026-10-01T01:00:00+00:00
      NO2   25.0 2026-10-01T00:00:00+00:00

## 2. JSON log lines written by flag_bad_readings (STALE_RUN = 3; ts = when this file was made)
{"ts": "2026-10-01T12:23:11+00:00", "level": "WARNING", "event": "DATA_QUALITY_ERROR", "source": "Luchtmeetnet", "station_id": "NL10240", "field": "NO2", "reason": "stale_or_null", "value": 30.0, "timestamp": "2026-10-01T01:00:00+00:00"}
{"ts": "2026-10-01T12:23:11+00:00", "level": "WARNING", "event": "DATA_QUALITY_ERROR", "source": "Luchtmeetnet", "station_id": "NL10240", "field": "NO2", "reason": "stale_or_null", "value": 30.0, "timestamp": "2026-10-01T02:00:00+00:00"}
{"ts": "2026-10-01T12:23:11+00:00", "level": "WARNING", "event": "DATA_QUALITY_ERROR", "source": "Luchtmeetnet", "station_id": "NL10240", "field": "NO2", "reason": "stale_or_null", "value": 30.0, "timestamp": "2026-10-01T03:00:00+00:00"}
{"ts": "2026-10-01T12:23:11+00:00", "level": "WARNING", "event": "DATA_QUALITY_ERROR", "source": "Luchtmeetnet", "station_id": "NL10240", "field": "NO2", "reason": "stale_or_null", "value": null, "timestamp": "2026-10-01T04:00:00+00:00"}

## 3. sensor_readings rows from to_db_rows: (station_id, timestamp, component, value, is_flagged)
## The null hour is stored as a real SQL NULL (None, not NaN) with is_flagged = True; the three
## frozen hours are kept with their value and is_flagged = True; nothing is dropped.
('NL10240', datetime.datetime(2026, 10, 1, 0, 0, tzinfo=datetime.timezone.utc), 'NO2', 25.0, False)
('NL10240', datetime.datetime(2026, 10, 1, 1, 0, tzinfo=datetime.timezone.utc), 'NO2', 30.0, True)
('NL10240', datetime.datetime(2026, 10, 1, 2, 0, tzinfo=datetime.timezone.utc), 'NO2', 30.0, True)
('NL10240', datetime.datetime(2026, 10, 1, 3, 0, tzinfo=datetime.timezone.utc), 'NO2', 30.0, True)
('NL10240', datetime.datetime(2026, 10, 1, 4, 0, tzinfo=datetime.timezone.utc), 'NO2', None, True)
('NL10240', datetime.datetime(2026, 10, 1, 5, 0, tzinfo=datetime.timezone.utc), 'NO2', 12.0, False)
## bad_data_count returned for this run: 4 (recorded in ingestion_runs, summed by /health)

## 4. Why the flag must be sticky across runs (UPSERT_SQL)
## Each hourly run re-fetches the newest 50 hours. Replaying three consecutive pages with hours
## 0, 1, 2 frozen at 30.0: once hour 0 has left the page, hours 1 and 2 are a run of two and
## look fine on their own. flag_bad_readings alone would therefore unflag them one run later.
## page hours 0..49: flags of the frozen hours on this page alone = [True, True, True]
## page hours 1..50: flags of the frozen hours on this page alone = [False, False]
## page hours 2..51: flags of the frozen hours on this page alone = [False]
## After the three runs through the sticky upsert (an unchanged value keeps its flag, a revised
## value gets the fresh flag), the frozen hours in the table are:
##   2026-09-20T00:00:00+00:00  (30.0, True)
##   2026-09-20T01:00:00+00:00  (30.0, True)
##   2026-09-20T02:00:00+00:00  (30.0, True)

## UPSERT_SQL as deployed:
    INSERT INTO sensor_readings (station_id, timestamp, component, value, is_flagged)
    VALUES %s
    ON CONFLICT (station_id, timestamp, component)
    DO UPDATE SET
        value = EXCLUDED.value,
        is_flagged = CASE
            WHEN sensor_readings.value IS DISTINCT FROM EXCLUDED.value THEN EXCLUDED.is_flagged
            ELSE sensor_readings.is_flagged OR EXCLUDED.is_flagged
        END