# George Town Council — Development Applications (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_georgetown
URL   = "https://georgetown.tas.gov.au/development-applications/"

DB.ensure_table!(TABLE)

# Optional: keep the document link with each row
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, fallback = "")
  node ? node.text.strip : fallback
end

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

# Try to pull a council reference from common places
REF_RX = %r{(DA|APP|APPLICATION|PLA)\s*([0-9]{4})\s*[/\-]?\s*([A-Za-z0-9\-_.]{2,})}i
def extract_reference(str)
  s = str.to_s
  if (m = s.match(REF_RX))
    return "DA #{m[2]} / #{m[3]}"
  end
  # Compact like DA202500123
  if (m = s.match(/\bDA(20\d{2})(\d{3,})\b/i))
    return "DA #{m[1]} / #{m[2]}"
  end
  nil
end

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

# Most items on this page are shown as “cards” with a small details table inside
cards = doc.css(".card, .entry-content .wp-block-group, .entry-content .content-block, .entry-content .notice, .entry-content")
items = []

cards.each do |card|
  table = card.at_css("table")
  next unless table

  rows = table.css("tr")
  kv = {}

  rows.each do |tr|
    cells = tr.css("th, td")
    next if cells.empty?
    # Expect two-column label/value rows; be defensive about order
    key = cells[0]&.text&.strip.to_s
    val = cells[1]&.text&.strip.to_s
    if cells.size >= 2 && !key.empty?
      kv[key] = val
    end
  end

  next if kv.empty?

  # Pull useful fields by fuzzy key match
  find = ->(needle_regex) {
    pair = kv.find { |k, _| k =~ needle_regex }
    pair ? pair[1] : ""
  }

  application_id     = find.call(/^(Application\s*(ID|No|Number)|Ref)/i)
  address            = find.call(/(Address|Property)/i)
  proposal           = find.call(/(Proposal|Description)/i)
  app_date_raw       = find.call(/(Application\s*Date|Date\s*Lodged|Date\s*Received)/i)
  closing_date_raw   = find.call(/(On\s*Notice\s*(to|until)|Closing\s*Date|Closes)/i)

  # Document link if present in the table or surrounding block
  link = table.at_css("a[href$='.pdf'], a[href*='.pdf?']") || card.at_css("a[href$='.pdf'], a[href*='.pdf?']")
  document_url = link ? abs_url(URL, link["href"]) : ""

  # Council reference priority: Application ID, then text refs, then file name
  council_reference =
    application_id.to_s.strip
  council_reference = extract_reference(proposal) if council_reference.to_s.empty?
  council_reference ||= extract_reference(File.basename(document_url)) || extract_reference(address) || ""

  # Pick a date to store: prefer application date, else closing/on-notice
  date_received     = Util.parse_aus_date(app_date_raw)
  date_received_raw = app_date_raw.to_s.strip
  if date_received.nil? && !closing_date_raw.to_s.strip.empty?
    date_received = Util.parse_aus_date(closing_date_raw)
    date_received_raw = closing_date_raw
  end

  # Minimal required fields
  address = address.to_s.strip
  next if address.empty? || council_reference.empty?

  items << {
    description: proposal.to_s.strip,
    date_received: date_received,
    date_received_raw: date_received_raw,
    address: address,
    council_reference: council_reference,
    document_url: document_url
  }
end

puts "Found #{items.length} items for #{TABLE}"

items.each do |row|
  DB.upsert(TABLE, {
    description: row[:description],
    date_received: row[:date_received],
    date_received_raw: row[:date_received_raw],
    address: row[:address],
    council_reference: row[:council_reference],
    applicant: "",
    owner: ""
  })
  
  enrich_after_upsert!(
    table: TABLE,
    council_reference: council_reference,
    address: address
  )

  # keep the PDF link if the column exists
  begin
    upd = DB.client.prepare("UPDATE `#{DB.client.escape(TABLE)}` SET document_url = ? WHERE council_reference = ? AND address = ?")
    upd.execute(row[:document_url], row[:council_reference], row[:address])
  rescue Mysql2::Error => e
    warn "[georgetown] db update skipped for #{row[:council_reference]}: #{e.message}"
  end

  puts "Upserted #{row[:council_reference]} -> #{row[:address]}"
end

puts "Done #{TABLE}."