#!/usr/bin/env python3
"""Fill ABN and legal_name for tier A and B from the public ABN Lookup. No guessed emails."""
import re
import sqlite3
import sys
from pathlib import Path
from urllib.parse import quote

from bs4 import BeautifulSoup

sys.path.insert(0, str(Path(__file__).parent))
from fetch import get

DB = "/Users/hattr/Documents/wa-land-developers/developers.sqlite"
RET = "2026-10-19"


def norm(s):
    s = (s or "").lower()
    s = s.replace("&", " and ")
    s = re.sub(r"[^a-z0-9 ]", " ", s)
    s = re.sub(r"\b(proprietary|pty|ltd|limited|the|atf)\b", " ", s)
    s = re.sub(r"\s+", " ", s).strip()
    return s


STOP = {"staff", "cafe", "air", "partitioning", "finance", "employee", "social", "club", "nomin"}


def parse_results(html):
    soup = BeautifulSoup(html, "lxml")
    table = soup.find("table")
    rows = []
    if not table:
        return rows
    for tr in table.find_all("tr"):
        cells = [c.get_text(" ", strip=True) for c in tr.find_all("td")]
        if len(cells) < 4:
            continue
        abn_m = re.search(r"(\d{2} \d{3} \d{3} \d{3})", cells[0])
        if not abn_m or "Active" not in cells[0]:
            continue
        loc = cells[3]
        state_m = re.search(r"\b(WA|NSW|VIC|QLD|SA|TAS|NT|ACT)\b", loc)
        rows.append({
            "abn": re.sub(r"\s+", "", abn_m.group(1)),
            "name": re.sub(r"\s+", " ", cells[1]).strip(),
            "state": state_m.group(1) if state_m else "",
            "type": cells[2],
        })
    return rows


def choose(query, rows):
    q = norm(query)
    q_tokens = [t for t in q.split() if len(t) > 2 or re.search(r"\d", t)]
    if not q_tokens:
        return None
    scored = []
    for r in rows:
        n = norm(r["name"])
        n_tokens = n.split()
        if not n_tokens:
            continue
        if any(t not in n_tokens for t in q_tokens):
            continue
        extra = [t for t in n_tokens if t not in q_tokens and t not in {"trustee", "for", "and", "unit", "trust", "discretionary", "group"}]
        # a project code or banned word means this is a different entity
        if any(re.search(r"\d", t) for t in extra):
            continue
        if any(t in STOP for t in extra):
            continue
        if len(extra) > 1:
            continue
        score = 100 - 10 * len(extra)
        if n == q:
            score += 30
        if r["state"] == "WA":
            score += 12
        if r["type"] == "Entity Name":
            score += 3
        scored.append((score, r))
    if not scored:
        return None
    scored.sort(key=lambda x: -x[0])
    return scored[0][1]


ALT = {
    "DevelopmentWA": "Western Australian Land Authority",
    "Lendlease": "Lendlease Corporation Limited",
    "Hesperia": "Hesperia Pty Ltd",
    "Department of Communities": "Department of Communities",
    "Wolfdene": "Wolfdene Pty Ltd",
    "Mirvac": "Mirvac Limited",
    "Stockland": "Stockland Corporation Limited",
}


def main():
    con = sqlite3.connect(DB)
    con.execute("UPDATE developers SET abn=NULL, legal_name=NULL, duplicate_of=NULL WHERE tier IN ('A','B')")
    con.execute("DELETE FROM source_urls WHERE url LIKE '%abr.business.gov.au%'")
    con.commit()
    names = [r[0] for r in con.execute(
        "SELECT DISTINCT trading_name FROM developers WHERE tier IN ('A','B') AND duplicate_of IS NULL AND (abn IS NULL OR abn='') ORDER BY trading_name"
    )]
    print("to look up", len(names), flush=True)
    for name in names:
        if " and " in name.lower():
            print("SKIP combined", name, flush=True)
            continue
        queries = []
        if name in ALT:
            queries.append(ALT[name])
        if name not in queries:
            queries.append(name)
        pick = None
        final = None
        content = b""
        for q in queries:
            url = "https://abr.business.gov.au/Search/ResultsActive?SearchText=" + quote(q)
            try:
                status, final, content, cached = get(url, timeout=40)
            except Exception as e:
                print("ERR", name, e, flush=True)
                continue
            if status != 200:
                print("HTTP", status, name, flush=True)
                continue
            pick = choose(q, parse_results(content))
            if pick:
                break
        if not pick:
            print("NO MATCH", name, flush=True)
            continue
        view = "https://abr.business.gov.au/ABN/View?abn=" + pick["abn"]
        con.execute(
            "UPDATE developers SET abn=?, legal_name=? WHERE tier IN ('A','B') AND trading_name=? AND abn IS NULL",
            (pick["abn"], pick["name"], name),
        )
        ids = [r[0] for r in con.execute(
            "SELECT id FROM developers WHERE tier IN ('A','B') AND trading_name=?", (name,)
        )]
        for i in ids:
            con.execute(
                "INSERT INTO source_urls (developer_id, url, retrieved_at) VALUES (?,?,?)",
                (i, final, RET),
            )
            con.execute(
                "INSERT INTO source_urls (developer_id, url, retrieved_at) VALUES (?,?,?)",
                (i, view, RET),
            )
        con.commit()
        print(f"OK {name} -> {pick['abn']} {pick['name']} {pick['state']}", flush=True)
    # dedupe A/B on ABN, keep lowest id
    groups = {}
    for i, abn, tier in con.execute("SELECT id, abn, tier FROM developers WHERE abn IS NOT NULL AND abn!='' AND tier IN ('A','B')"):
        groups.setdefault(abn, []).append(i)
    for abn, ids in groups.items():
        if len(ids) < 2:
            continue
        ids = sorted(ids)
        keep = ids[0]
        for other in ids[1:]:
            con.execute("UPDATE developers SET duplicate_of=? WHERE id=? AND duplicate_of IS NULL", (keep, other))
            con.execute("UPDATE source_urls SET developer_id=? WHERE developer_id=?", (keep, other))
        print("DEDUP", abn, "keep", keep, "others", ids[1:], flush=True)
    con.commit()
    print("done", flush=True)

if __name__ == "__main__":
    main()