# Glamorgan Spring Bay Council — Public Notices scraper (site page, not PlanBuild)

require "nokogiri"
require_relative "../lib/http"
require_relative "../lib/db"
require_relative "../lib/util"
require_relative "../lib/enrich"

TABLE = ENV.fetch("TABLE_NAME")  # run_all.sh sets from filename: da_glamorgan
URL   = "https://gsbc.tas.gov.au/services-facilities/public-notices/"

DB.ensure_table!(TABLE)

# Optional column to store the PDF or document link
begin
  DB.client.query("ALTER TABLE `#{DB.client.escape(TABLE)}` ADD COLUMN IF NOT EXISTS document_url VARCHAR(1024) NULL")
rescue => e
  warn "document_url add skipped: #{e.class} #{e.message}"
end

def text_or(node, default = "")
  node ? node.text.strip : default
end

# Try to extract a reference from visible text or file names
REF_RX = %r{(DA|PLA|APP|APPLICATION)\s*([0-9]{4})\s*/\s*([A-Za-z0-9\-_.]+)}i
def extract_reference(str)
  s = str.to_s
  if (m = s.match(REF_RX))
    return "#{m[1].upcase} #{m[2]} / #{m[3]}"
  end
  nil
end

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

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

# Find a table that looks like the notices list
tables = doc.css("table")
table  = tables.find do |t|
  headers = t.css("thead th").map { |th| th.text.strip.downcase }
  headers.empty? ? t.css("tbody tr:first-child td").size >= 4 : headers.size >= 4
end

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

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

saved = 0

rows.each_with_index do |row, idx|
  tds = row.css("td")
  next if tds.empty?

  # Expected columns on this page:
  # 0: Description, 1: Address, 2: Application date, 3: On notice to, 4: Document link
  description        = text_or(tds[0])
  address            = text_or(tds[1])
  application_raw    = text_or(tds[2])
  on_notice_to_raw   = text_or(tds[3])
  link_el            = tds[4]&.at_css("a")
  document_url 	     = link_el ? safe_abs(URL, link_el["href"]) : ""

  date_received      = Util.parse_aus_date(application_raw)
  on_notice_to       = Util.parse_aus_date(on_notice_to_raw)

  # Reference: try text, then file name, then a stable fallback
  council_reference  = extract_reference(description) ||
                       extract_reference(address) ||
                       extract_reference(File.basename(document_url)) ||
                       begin
                         date_key = date_received || on_notice_to
                         base = [date_key&.strftime("%Y%m%d"), address.gsub(/\s+/, " ")[0,40]].compact.join(" ")
                         base.empty? ? "GSBC-ROW-#{idx+1}" : "GSBC #{base}"
                       end

  next if address.empty? || council_reference.empty?

  DB.upsert(TABLE, {
    description: description,
    date_received: date_received,
    date_received_raw: application_raw,
    address: address,
    council_reference: council_reference,
    applicant: "",
    owner: ""
  })
  
  enrich_after_upsert!(
    table: TABLE,
    council_reference: council_reference,
    address: address
  )

  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 Mysql2::Error => e
    warn "[glamorgan] db update skipped for #{council_reference}: #{e.message}"
  end

  puts "Upserted #{council_reference} -> #{address}"
  saved += 1
end

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