tas_councils/scrapers/brighton.rb

# Brighton Council — Advertised Development Applications (site page, not PlanBuild)
require "date"
require "nokogiri"
require "cgi"
require "fileutils"
require_relative "../lib/http"
require_relative "../lib/db"
require_relative "../lib/util"
require_relative "../lib/geocode"
require_relative "../lib/enrich"

TABLE = ENV.fetch("TABLE_NAME") # run_all.sh sets this from filename: da_brighton
URL   = "https://www.brighton.tas.gov.au/planning/advertised-development-applications/"

DOWNLOAD_ATTACHMENTS = ENV["DOWNLOAD_ATTACHMENTS"] == "1"
DOWNLOAD_DIR         = ENV["DOWNLOAD_DIR"] || "/app/downloads"

DB.ensure_table!(TABLE)
ensure_extra_columns!(TABLE)   

# --- helpers ---------------------------------------------------------------
# DA/APP refs like “DA2025-130”, “DA 2024/174”, etc → “DA YYYY / NNN…”
REF_RX = %r{
\b(?:DA|APP|APPLICATION)\s*
(20\d{2})\s*[/\-]?\s*
    ([A-Za-z0-9\-_.]{2,})\b
}ix

def extract_ref_from(str)
    s = CGI.unescape(str.to_s)
    if (m = s.match(REF_RX))
        return "DA #{m[1]} / #{m[2]}"
    end
    # Compact like DA20250123
    if (m = s.match(/\bDA(20\d{2})(\d{3,})\b/i))
        return "DA #{m[1]} / #{m[2]}"
    end
    nil
end

def abs_url(base, href)
    return "" if href.to_s.strip.empty?
    URI.join(base, href).to_s
rescue URI::InvalidURIError
    href.to_s
end

def safe_name(s) = s.to_s.gsub(/[^\w\-.]+/, "_")

    def strip_ordinals(s)
        s.to_s.gsub(/\b(\d{1,2})(st|nd|rd|th)\b/i, '\1')
    end

    def parse_close_date(s)
        Util.parse_aus_date(strip_ordinals(s.to_s))
    end

    def download_pdf(url, council_reference)
        return nil unless DOWNLOAD_ATTACHMENTS && !url.to_s.strip.empty?
        folder = File.join(DOWNLOAD_DIR, "brighton", safe_name(council_reference))
        FileUtils.mkdir_p(folder)
        begin
            res  = Http.get_response(url) rescue Http.get(url)
            body = res.respond_to?(:body) ? res.body : res.to_s
            fname = safe_name(File.basename(URI.parse(url).path))
            fname += ".pdf" unless fname.downcase.end_with?(".pdf")
            path  = File.join(folder, fname)
            File.binwrite(path, body)
            puts "Saved PDF #{path}"
            "/downloads/brighton/#{safe_name(council_reference)}/#{fname}"
        rescue => e
            warn "PDF download failed for #{url}: #{e.class} #{e.message}"
            nil
        end
    end

    # --- scrape ---------------------------------------------------------------

    html = Http.get(URL)
    doc  = Nokogiri::HTML(html)

    # Find the advertised table by headers
    table = doc.css("table").find do |t|
        heads = t.css("thead th").map { |th| th.text.strip.downcase }
        heads.any? &&
        heads.any? { |h| h.include?("description") } &&
        heads.any? { |h| h.include?("address") } &&
        heads.any? { |h| h.include?("closing") || h.include?("closing date") } &&
        heads.any? { |h| h.include?("pdf") }
    end

    unless table
        puts "No advertised table found on #{URL}"
        exit 0
    end

    # Column indexes by header text (fallback to 0..3 if needed)
    heads = table.css("thead th").map { |th| th.text.strip.downcase }
    i_desc = heads.index { |h| h.include?("description") } || 0
    i_addr = heads.index { |h| h.include?("address") }     || 1
    i_close= heads.index { |h| h.include?("closing") }     || 2
    i_pdf  = heads.index { |h| h.include?("pdf") }         || 3

    rows = table.css("tbody tr")
    puts "Found #{rows.length} row(s) for #{TABLE}"

    saved = 0
    today = Date.today

    rows.each do |tr|
        tds = tr.css("td")
        next if tds.empty?

        description = tds[i_desc]&.text&.strip.to_s
        address     = tds[i_addr]&.text&.strip.to_s
        close_raw   = tds[i_close]&.text&.strip.to_s
        on_notice_to   = parse_close_date(close_raw)
		date_received = on_notice_to && (on_notice_to - 14)

        # one or more PDF links
        pdf_links = tds[i_pdf]&.css('a[href]')&.map { |a| abs_url(URL, a["href"].to_s) } || []
        document_url = pdf_links.first.to_s

        # reference from link text or file name
        ref_texts = tds[i_pdf]&.css('a')&.map { |a| a.text.to_s } || []
        council_reference =
            ref_texts.map { |tx| extract_ref_from(tx) }.compact.first ||
            pdf_links.map { |u| extract_ref_from(File.basename(u)) }.compact.first

        # minimal keys
        next if address.empty? || council_reference.nil?

        DB.upsert(TABLE, {
            description: description.empty? ? "Development Application" : description,
            date_received: date_received,
            date_received_raw: today.strftime("%Y-%m-%d"),
            on_notice_to: on_notice_to,       # store close date in DATE column
            on_notice_to_raw: strip_ordinals(close_raw),
            address: address,
            council_reference: council_reference,
            applicant: "",
            owner: ""
            })

        enrich_after_upsert!(
            table: TABLE,
            council_reference: council_reference,
            address: address
            )

        # remote URL
        begin
            upd = DB.client.prepare("UPDATE `#{DB.client.escape(TABLE)}` SET document_url = ? WHERE council_reference = ? AND address = ?")
            upd.execute(document_url, council_reference, address)
        rescue => e
            warn "document_url update skipped for #{council_reference}: #{e.class} #{e.message}"
        end

        # local copy
        local_doc_url = download_pdf(document_url, council_reference)
        if local_doc_url
            begin
                upd2 = DB.client.prepare("UPDATE `#{DB.client.escape(TABLE)}` SET local_document_url = ? WHERE council_reference = ? AND address = ?")
                upd2.execute(local_doc_url, council_reference, address)
            rescue => e
                warn "local_document_url update skipped for #{council_reference}: #{e.class} #{e.message}"
            end
        end

        puts "Upserted #{council_reference} -> #{address} (doc: #{document_url.empty? ? 0 : 1}, saved: #{local_doc_url ? 1 : 0})"
        saved += 1
    end

    puts "Done #{TABLE}. Saved #{saved} item(s)."