#!/usr/bin/env python3
"""
Recompute every figure in "Four traps in the Sarasota County commercial property
record, counted" from the county's own public bulk file.

    https://glvtl.com/field-study/sarasota-commercial-record/

By Chris Klebl, Sterling Digital Partners. Free to reuse with attribution.

Standard library only. No install step.

    python3 count_sarasota.py                 # download the current roll, then count
    python3 count_sarasota.py path/to.zip     # count a roll you already have

The published figures came from the file dated 2026-09-18 10:00 GMT. The roll is
revised continuously, so a later file will NOT reproduce them exactly. That is
expected. What should reproduce is the method: same definitions, same columns,
same arithmetic. If a number moves, the date moved with it.

Source: Sarasota County Property Appraiser, Download Data.
https://www.sarasotapropertyappraiser.gov/downloads/download-data/
"""

import csv, io, sys, zipfile, statistics, collections, urllib.request

URL = "https://www.sarasotapropertyappraiser.gov/downloads/SCPA_Parcels_Sales_CSV.zip"
PARCELS = "Parcel_Sales_CSV/Sarasota.csv"
SALES = "Parcel_Sales_CSV/ParcelSales.csv"

# DOR use codes 1000-4999 are commercial and industrial.
CRE_LO, CRE_HI = 1000, 4999
# Qualification codes the appraiser treats as an open-market sale.
QUALIFIED = {"0", "01", "02", "03", "04", "05", "06", "07", "6", "7"}
SALES_FROM_YEAR = 2024

csv.field_size_limit(1 << 24)


def money(s):
    s = (s or "").replace(",", "").replace("$", "").strip()
    try:
        return float(s)
    except ValueError:
        return None


def usecode(s):
    s = (s or "").strip()
    return int(s) if s.isdigit() and len(s) == 4 else None


def rows(zf, member):
    """Stream one CSV member without unpacking the archive to disk."""
    with zf.open(member) as fh:
        yield from csv.DictReader(io.TextIOWrapper(fh, encoding="utf-8", errors="replace"))


def fetch(path=None):
    if path:
        return zipfile.ZipFile(path)
    sys.stderr.write("downloading %s ...\n" % URL)
    req = urllib.request.Request(URL, headers={"User-Agent": "count_sarasota/1.0"})
    with urllib.request.urlopen(req, timeout=300) as r:
        info = r.headers.get("Last-Modified", "not stated")
        blob = r.read()
    sys.stderr.write("source file Last-Modified: %s\n\n" % info)
    return zipfile.ZipFile(io.BytesIO(blob))


def main(path=None):
    zf = fetch(path)

    total = live = 0
    cre_accounts = set()
    values = []                                    # (just, assessed, taxable)
    situs_jurisdiction = collections.Counter()     # situs city SARASOTA -> municipality
    situs_jurisdiction_cre = collections.Counter()
    directions = collections.defaultdict(set)      # (number, street, city) -> {N,S,E,W,""}
    parcels_at_key = collections.defaultdict(set)

    for r in rows(zf, PARCELS):
        total += 1
        if not (r["Status"] or "").startswith("OPEN"):
            continue
        live += 1

        code = usecode(r["STCD"])
        is_cre = code is not None and CRE_LO <= code <= CRE_HI
        city = (r["LOCCITY"] or "").strip().upper()
        muni = (r["Municipality"] or "").strip()

        if is_cre:
            cre_accounts.add(r["ACCOUNT"])
            j, a, t = money(r["JUST"]), money(r["ASSD"]), money(r["TXBL"])
            if j and j > 0 and a is not None and t is not None:
                values.append((j, a, t))

        if city == "SARASOTA":
            situs_jurisdiction[muni] += 1
            if is_cre:
                situs_jurisdiction_cre[muni] += 1

        num, street = (r["LOCN"] or "").strip(), (r["LOCS"] or "").strip().upper()
        if num and street:
            key = (num, street, city)
            directions[key].add((r["LOCD"] or "").strip().upper())
            parcels_at_key[key].add(r["ACCOUNT"])

    sales = collections.Counter()
    by_code = collections.Counter()
    nominal = sold = 0
    for r in rows(zf, SALES):
        if r["Account"] not in cre_accounts:
            continue
        year = None
        for tok in (r["SaleDate"] or "").replace("-", "/").split("/"):
            if len(tok) == 4 and tok.isdigit():
                year = int(tok)
        if year is None or year < SALES_FROM_YEAR:
            continue
        sold += 1
        q = (r["QualCode"] or "").strip()
        by_code[q] += 1
        sales["qualified" if q in QUALIFIED else "not qualified"] += 1
        price = money(r["SalePrice"])
        if price is not None and price <= 100:
            nominal += 1

    # ---------- report ----------
    pct = lambda n, d: "%.1f%%" % (100.0 * n / d) if d else "n/a"
    line = lambda k, v: print("%-46s %s" % (k, v))

    print("=" * 74)
    print("SARASOTA COUNTY COMMERCIAL PROPERTY RECORD - COUNTS")
    print("=" * 74)
    line("rows in Sarasota.csv", f"{total:,}")
    line("live parcels (Status begins OPEN)", f"{live:,}")
    line("commercial / industrial (DOR 1000-4999)", f"{len(cre_accounts):,}")

    print("\n-- Finding 01: sales qualification, %d onward --" % SALES_FROM_YEAR)
    line("recorded sales on commercial parcels", f"{sold:,}")
    for k in ("qualified", "not qualified"):
        line("  %s" % k, f"{sales[k]:,}  ({pct(sales[k], sold)})")
    line("  recorded at $100 or less", f"{nominal:,}  ({pct(nominal, sold)})")
    print("  top codes:")
    for code, n in by_code.most_common(8):
        print("    %-6s %6d  %s" % (code, n, pct(n, sold)))

    print("\n-- Finding 02: 'SARASOTA' situs city by jurisdiction --")
    tot_s = sum(situs_jurisdiction.values())
    line("live parcels with SARASOTA situs city", f"{tot_s:,}")
    for k, n in situs_jurisdiction.most_common():
        line("  %s" % (k or "(blank)"), f"{n:,}  ({pct(n, tot_s)})")
    tot_c = sum(situs_jurisdiction_cre.values())
    line("commercial subset", f"{tot_c:,}")
    for k, n in situs_jurisdiction_cre.most_common():
        line("  %s" % (k or "(blank)"), f"{n:,}  ({pct(n, tot_c)})")

    print("\n-- Finding 03: assessed against just value (commercial) --")
    n = len(values)
    line("parcels with JUST > 0 and ASSD/TXBL present", f"{n:,}")
    line("median ASSD / JUST", "%.1f%%" % (100 * statistics.median(a / j for j, a, t in values)))
    line("median TXBL / JUST", "%.1f%%" % (100 * statistics.median(t / j for j, a, t in values)))
    for thr in (0.99, 0.95, 0.90, 0.75, 0.50):
        c = sum(1 for j, a, t in values if a / j < thr)
        line("  assessed below %d%% of just" % (thr * 100), f"{c:,}  ({pct(c, n)})")
    tj = sum(j for j, a, t in values)
    ta = sum(a for j, a, t in values)
    line("total just value", f"${tj:,.0f}")
    line("total assessed value", f"${ta:,.0f}")
    line("aggregate gap", f"${tj - ta:,.0f}  ({pct(tj - ta, tj)})")

    print("\n-- Finding 04: directional address collisions --")
    multi = {k: v for k, v in directions.items() if len([d for d in v if d]) > 1}
    line("distinct (number, street, city) keys", f"{len(directions):,}")
    line("keys with 2+ non-blank directions", f"{len(multi):,}")
    line("parcels behind them", f"{sum(len(parcels_at_key[k]) for k in multi):,}")
    print("\nAggregate counts only. No owner, mailing, grantor/grantee or legal-description")
    print("field is read or printed by this script.")


if __name__ == "__main__":
    main(sys.argv[1] if len(sys.argv) > 1 else None)