#!/usr/bin/env python3
"""Extract a small, attributed Ausgrid subset from the pinned preserved archive.

Usage: python3 research/battery-quote-check/real-data/extract.py /path/to/archive.zip
No network, dependencies, inferred DST conversion, netting or missing-data repair.
"""
from pathlib import Path
import csv
import datetime as dt
from decimal import Decimal
import hashlib
import io
import json
import sys
import zipfile

ARCHIVE_SHA256 = "5a766f52b6c8b3b72730380f4422e478934bc94640a4b089dd0e0e3c055c5d82"
MEMBER = "Solar home 2012-2013.csv"
NOTES = "Ausgrid solar home electricity data notes (Aug 2014).pdf"
CUSTOMER = "12"
HERE = Path(__file__).resolve().parent


def sha(data):
    return hashlib.sha256(data).hexdigest()


def main():
    if len(sys.argv) != 2:
        raise SystemExit(__doc__)
    archive = Path(sys.argv[1]).read_bytes()
    if sha(archive) != ARCHIVE_SHA256:
        raise SystemExit("Archive hash differs; review provenance before accepting a new source.")
    with zipfile.ZipFile(io.BytesIO(archive)) as z:
        data = z.read(MEMBER)
        notes = z.read(NOTES)
    rows = csv.reader(io.StringIO(data.decode("utf-8-sig")))
    next(rows)  # Ausgrid notice; preserved via provenance instead of CSV payload.
    header = next(rows)
    expected_times = [f"{(i // 2) % 24}:{'30' if i % 2 else '00'}" for i in range(1, 49)]
    if header != ["Customer", "Generator Capacity", "Postcode", "Consumption Category", "date", *expected_times, "Row Quality"]:
        raise SystemExit("Source schema changed.")
    selected = {}
    for row in rows:
        if row[0] != CUSTOMER:
            continue
        if len(row) != 54 or row[-1] != "" or row[3] not in ("GC", "GG"):
            raise SystemExit("Unexpected channel, non-actual quality, or malformed selected source row.")
        date = dt.datetime.strptime(row[4], "%d/%m/%Y").date()
        key = (date, row[3])
        if key in selected:
            raise SystemExit("Duplicate source date/channel.")
        values = row[5:53]
        if any(not Decimal(v).is_finite() or Decimal(v) < 0 for v in values):
            raise SystemExit("Invalid or negative energy value.")
        selected[key] = values
    dates = [dt.date(2012, 7, 1) + dt.timedelta(days=i) for i in range(365)]
    if set(selected) != {(d, c) for d in dates for c in ("GC", "GG")}:
        raise SystemExit("Expected precisely one GC and GG row for every date of the financial year.")
    buf = io.StringIO(newline="")
    writer = csv.writer(buf, lineterminator="\n")
    writer.writerow(["source_date", "source_channel", *expected_times])
    for date in dates:
        for channel in ("GC", "GG"):
            writer.writerow([date.isoformat(), channel, *selected[(date, channel)]])
    subset = buf.getvalue().encode()
    filename = "ausgrid-household-12-2012-2013.csv"
    totals = {c: str(sum((Decimal(v) for (d, k), vs in selected.items() if k == c for v in vs), Decimal(0))) for c in ("GC", "GG")}
    manifest = {
        "title": "Ausgrid household 12, 2012-2013 measured gross-channel research subset",
        "creator": "Ausgrid", "archiveCustodian": "Pierre Haessig",
        "license": "CC BY 3.0 Australia", "licenseUrl": "https://creativecommons.org/licenses/by/3.0/au/",
        "primaryMetadata": "https://data.gov.au/data/en/dataset/nsw-solar-home-electricty-data",
        "originalArchiveUrl": "https://www.ausgrid.com.au/-/media/Documents/Data-to-share/Solar-home-electricity-data/Solar-home-half-hour-data---1-July-2012-to-30-June-2013.zip",
        "originalArchiveStatus": "HTTP 404 on retrieval; preserved copy used",
        "retrievedArchiveUrl": "https://pierreh.eu/downloads/Ausgrid_solar_home_data.zip",
        "mirrorProvenanceUrl": "https://github.com/pierre-haessig/ausgrid-solar-data",
        "retrievedDate": "2026-09-21", "archiveSha256": ARCHIVE_SHA256,
        "sourceMember": MEMBER, "sourceMemberSha256": sha(data),
        "notesMember": NOTES, "notesSha256": sha(notes),
        "sourceCustomerPseudonym": CUSTOMER,
        "subsetFile": filename, "subsetSha256": sha(subset), "subsetBytes": len(subset),
        "firstSourceDate": "2012-07-01", "lastSourceDate": "2013-06-30",
        "days": 365, "dailyChannelRows": 730, "pairedIntervals": 17520,
        "intervalMinutes": 30, "unit": "kWh", "channelTotalsKwh": totals,
        "channels": {"GC": "Measured general consumption; excludes controlled load and solar generation", "GG": "Measured gross solar generation; separate from household loads"},
        "quality": "All selected source rows have blank Row Quality (actual, per Ausgrid notes). No CL rows exist for this pseudonym/year.",
        "sourceClock": "Ausgrid August 2014 notes specify EST and summer EDT but provide 48 columns/day without transition-day resolution. Historic absolute instants are unresolved.",
        "intervalConvention": "Source column 0:30 ends first interval; final 0:00 ends next midnight.",
        "benchmarkClock": "Artificial consecutive 30-minute UTC test slots beginning 2012-07-01T00:00:00Z; source date/column order only, not claimed historic instants.",
        "changes": ["Selected only public de-identified customer 12 for one financial year", "Removed postcode, generator capacity, customer ID and empty quality columns", "Converted source date labels to ISO dates; sorted by source date then GC/GG", "Preserved all source energy decimal strings without netting, scaling, interpolation or rounding"],
        "notEstablished": ["Actual net-metered import/export", "Historical DST instant mapping", "Representative household", "Current bill or savings", "Real installer quote comparison"],
    }
    (HERE / filename).write_bytes(subset)
    (HERE / "manifest.json").write_text(json.dumps(manifest, indent=2) + "\n")
    print(json.dumps({"file": filename, "bytes": len(subset), "sha256": sha(subset), "channelTotalsKwh": totals}, indent=2))


if __name__ == "__main__":
    main()