#!/usr/bin/env python3
"""Rank developers by fit to a Zenith-sized microgrid, not by how big they are.

The only WA microgrids Zenith has published are the ruler:
  Eglinton (Cedar Woods): about 1,300 homes, 4.4 MW solar, 5 MW capacity.
  Ocean Reef (DevelopmentWA): about 1,000 homes, 4 MVA connection.
  Peel (DevelopmentWA): industrial precinct, 2.2 MW at the start.

Thomas's ceiling for one contract: under 10 MW of connection, no more than
10 MW of solar and batteries, about 5 MW of load, about 4 MW of contract
maximum demand. The Western Power planning lump is the next size up:
about 1,600 homes, 75 hectares, or 15 MVA.
"""
import re
import sqlite3

DB = "/Users/hattr/Documents/wa-land-developers/developers.sqlite"

# Homes that match the two residential deals, and the lump just above them.
WINDOW = (400, 2000)       # in or next to Eglinton / Ocean Reef
CEILING = (2000, 3500)     # one connection is tight
# 1,600 homes == 75 ha, from the planning lump. Used only when homes are absent.
HA_PER_HOME = 75 / 1600


def numbers(note):
    note = note or ""
    def grab(pat):
        m = re.search(pat, note)
        return float(m.group(1)) if m else None
    homes = grab(r"dwellings=(\d+)")
    ha = grab(r"hectares=(\d+)")
    mw = grab(r"connection_mw=([0-9.]+)")
    solar = grab(r"solar_mw=([0-9.]+)")
    if homes == 0:
        homes = None
    if ha == 0:
        ha = None
    return homes, ha, mw, solar


def homes_equiv(homes, ha):
    vals = []
    if homes:
        vals.append(homes)
    if ha:
        vals.append(ha / HA_PER_HOME)
    return max(vals) if vals else None


# One building, even a big one, is not the green-title estate microgrid
# Zenith has actually built.
SINGLE_BUILDING = {
    "apartment developer",
    "community housing developer",
    "student housing developer",
    "infill developer",
}


ESTATE_KINDS = {
    "land developer",
    "government land agency",
    "industrial land developer",
    "industrial and mixed-use developer",
    "airport land developer",
    "government housing developer",
    "land-lease community developer",
    "lifestyle village developer",
    "house-and-land developer",
    "precinct proponent",
    "land and mixed-use developer",
    "retirement housing developer",
    "community foundation land partner",
    "government regional development commission",
}


def classify(tier, kind, region, note, sources, proven):
    homes, ha, mw, solar = numbers(note)
    equiv = homes_equiv(homes, ha)
    kind = kind or ""

    if proven:
        band, label = 1, "Already bought a microgrid like this"
    elif tier == "C":
        band, label = 8, "Builder register only"
    elif region == "horizon":
        band, label = 8, "Outside the SWIS"
    elif tier == "B" and equiv is None:
        band, label = 8, "One occupier, no estate"
    elif equiv is not None and equiv < 100:
        band, label = 7, "Too small for a precinct microgrid"
    elif kind in SINGLE_BUILDING and not ha:
        if homes and homes >= 200:
            band, label = 6, "One large building, not a land estate"
        else:
            band, label = 6, "One site, size unknown"
    elif (mw and 2 <= mw <= 10) or (equiv and WINDOW[0] <= equiv <= WINDOW[1]) or (ha and 15 <= ha <= 75):
        band, label = 2, "About the size of Eglinton or Ocean Reef"
    elif equiv and CEILING[0] < equiv <= CEILING[1]:
        band, label = 3, "A bit big for one connection"
    elif (equiv and equiv > CEILING[1]) or (ha and ha > 75) or (mw and mw > 10):
        band, label = 4, "Right kind of company, too big for one contract"
    elif tier == "A" and region == "swis" and kind in ESTATE_KINDS:
        band, label = 5, "Land developer, size not on the page"
    elif tier == "A" and region == "swis":
        band, label = 6, "One site, size unknown"
    else:
        band, label = 8, "Not this business"

    # Closeness to 1,150 homes (between the two built residential deals).
    if equiv:
        closeness = max(0, 40 - abs(equiv - 1150) / 80)
    elif mw:
        closeness = max(0, 40 - abs(mw - 5) * 8)
    else:
        closeness = 0
    score = {1: 1000, 2: 800, 3: 600, 4: 400, 5: 300, 6: 150, 7: 50, 8: 0}[band]
    score += closeness + min(sources, 8)

    bits = []
    if proven:
        bits.append("Named on a Zenith microgrid page.")
    if homes:
        bits.append(f"About {int(homes):,} dwellings on the source page.")
    if ha:
        bits.append(f"About {int(ha):,} hectares on the source page.")
    if mw:
        bits.append(f"Listed connection about {mw:g} MW.")
    if solar:
        bits.append(f"Listed solar about {solar:g} MW.")
    if not bits:
        bits.append("No homes, hectares, or megawatts on the source page.")
    bits.append(label + ".")
    return band, label, round(score, 1), " ".join(bits)


def main():
    con = sqlite3.connect(DB)
    con.execute("DROP TABLE IF EXISTS zenith_fit")
    con.execute("""
        CREATE TABLE zenith_fit (
            developer_id INTEGER PRIMARY KEY,
            band INTEGER NOT NULL,
            band_label TEXT NOT NULL,
            score REAL NOT NULL,
            reason TEXT NOT NULL
        )
    """)
    rows = con.execute("""
        SELECT d.id, d.tier, d.entity_kind, d.region_tag, d.scale_note, d.trading_name,
               (SELECT COUNT(DISTINCT url) FROM source_urls s WHERE s.developer_id = d.id)
        FROM developers d
        WHERE d.duplicate_of IS NULL
    """).fetchall()
    proven_ids = {r[0] for r in con.execute("""
        SELECT DISTINCT developer_id FROM source_urls WHERE url LIKE '%zenithenergy.com.au%'
    """)}
    for did, tier, kind, region, note, name, sources in rows:
        band, label, score, reason = classify(tier, kind, region, note, sources or 0, did in proven_ids)
        con.execute(
            "INSERT INTO zenith_fit VALUES (?,?,?,?,?)",
            (did, band, label, score, reason),
        )
    con.commit()
    print("rows", con.execute("select count(*) from zenith_fit").fetchone()[0])
    print("\nBANDS")
    for r in con.execute("""
        SELECT band, band_label, COUNT(*)
        FROM zenith_fit GROUP BY band, band_label ORDER BY band
    """):
        print(f"  {r[0]}  {r[2]:5}  {r[1]}")
    print("\nTOP OF EACH USEFUL BAND")
    for band in (1, 2, 3, 4):
        print(f"\n== band {band} ==")
        for name, reason, score in con.execute("""
            SELECT d.trading_name, f.reason, f.score
            FROM zenith_fit f JOIN developers d ON d.id = f.developer_id
            WHERE f.band = ? ORDER BY f.score DESC LIMIT 12
        """, (band,)):
            print(f"  {score:6.0f}  {name[:40]:40}  {reason[:110]}")

if __name__ == "__main__":
    main()