# Kingborough Council — Planning Notices (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 this from filename: da_kingborough
URL   = "https://www.kingborough.tas.gov.au/development/planning-notices/"

DB.ensure_table!(TABLE)

# Optional columns for extras used on this site
begin
  DB.client.query("ALTER TABLE `#{DB.client.escape(TABLE)}` ADD COLUMN IF NOT EXISTS document_url TEXT NULL")
  DB.client.query("ALTER TABLE `#{DB.client.escape(TABLE)}` ADD COLUMN IF NOT EXISTS on_notice_to DATE NULL")
  DB.client.query("ALTER TABLE `#{DB.client.escape(TABLE)}` ADD COLUMN IF NOT EXISTS on_notice_to_raw VARCHAR(50) NULL")
rescue => e
  warn "Optional column add skipped: #{e.class} #{e.message}"
end

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

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

rows = doc.css("#list 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.length < 5

  council_reference    = tds[0].text.strip
  address              = tds[1].text.strip
  date_received_raw    = tds[2].text.strip
  on_notice_to_raw     = tds[3].text.strip
  description          = tds[4].text.strip

  # All links in the row (often PDFs). Join if there are multiple.
  links = row.css("td a").map { |a| abs_url(URL, a["href"]) }.reject(&:empty?)
  document_url = links.join(", ")

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

  # Need a reference and an address to store a row
  next if council_reference.empty? || address.empty?

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

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

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

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