# Derwent Valley Council — Development Applications being advertised
# Primary list: https://www.derwentvalley.tas.gov.au/home/card-listing/development-applications
# Fallback list (Public Notice posts): https://www.derwentvalley.tas.gov.au/home/latest-news?f.News+category%7CnewsCategory=Public+Notice

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 -> da_derwentvalley
LIST_URL     = "https://www.derwentvalley.tas.gov.au/home/card-listing/development-applications"
NEWS_URL     = "https://www.derwentvalley.tas.gov.au/home/latest-news?f.News+category%7CnewsCategory=Public+Notice"

DB.ensure_table!(TABLE)

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(80) NULL")
  DB.client.query("ALTER TABLE `#{DB.client.escape(TABLE)}` ADD COLUMN IF NOT EXISTS title_reference TEXT 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

# Common reference forms: "DA 2025/097"
REF_RX = %r{\bDA\s*(20\d{2})\s*/\s*([A-Za-z0-9\-_.]+)}i
def extract_ref(s)
  t = s.to_s
  if (m = t.match(REF_RX))
    return "DA #{m[1]} / #{m[2]}"
  end
  nil
end

def extract_date_token(s)
  text = s.to_s
  return $1 if text =~ /(\b\d{1,2}\/\d{1,2}\/\d{2,4}\b)/
  return $1 if text =~ /(\b\d{1,2}\s+[A-Za-z]{3,}\s+\d{4}\b)/
  return $1 if text =~ /(\b[A-Za-z]{3,}\s+\d{1,2},?\s+\d{4}\b)/
  ""
end

def extract_on_notice_raw(text)
  s = text.to_s.gsub(/\s+/, " ")
  # Look for wording like "Submissions must be received by ...", "close on ...", "on notice until ..."
  if s =~ /(submissions?|representations?)\s+(must\s+be\s+)?(received|made|close|closing)\s+(by|on)\s*[:\-]?\s*([A-Za-z0-9\/ ,]+)/i
    d = extract_date_token($5)
    return d unless d.empty?
  end
  if s =~ /\bon\s*notice\s*(until|to)\s*[:\-]?\s*([A-Za-z0-9\/ ,]+)/i
    d = extract_date_token($2)
    return d unless d.empty?
  end
  extract_date_token(s)
end

def parse_detail(url)
  html = Http.get(url)
  doc  = Nokogiri::HTML(html)

  title = doc.at_css("h1, .entry-title")&.text&.strip.to_s
  body_text = doc.at_css("main")&.text.to_s
  body_text = doc.text.to_s if body_text.strip.empty?

  council_reference = extract_ref(title) || extract_ref(body_text)
  # Address often sits in the title after " - "
  address = if title.include?(" - ")
    title.split(" - ", 2)[1].to_s.strip
  else
    # Fallback: first line with a number and street
    line = body_text.split(/\n/).find { |l| l =~ /\d{1,4}\s+\S+/ }
    line.to_s.strip
  end
  address = title if address.to_s.strip.empty?

  pdf_a = doc.at_css("a[href$='.pdf'], a[href*='.pdf?']")
  pdf   = pdf_a ? abs_url(url, pdf_a["href"].to_s) : ""

  on_raw = extract_on_notice_raw(body_text)
  on_dt  = Util.parse_aus_date(on_raw)

  return nil if council_reference.to_s.strip.empty? || address.to_s.strip.empty?

  {
    council_reference: council_reference,
    address: address,
    description: "Development Application",
    date_received_raw: on_raw,
    date_received: on_dt,
    document_url: pdf,
    title_reference: title
  }
end

def detail_links_from_list(list_url)
  html = Http.get(list_url)
  doc  = Nokogiri::HTML(html)
  # Cards or list items link to detail posts
  links = doc.css("a").map { |a|
    href = a["href"].to_s
    next if href.empty? || href.start_with?("#")
    abs_url(list_url, href)
  }.compact.uniq

  # Keep obvious news or notice items
  links.select { |u|
    u.include?("/home/latest-news/") || u.include?("/news/") || u =~ /application-for-planning-approval/i
  }
end

def detail_links_from_news(news_url)
  html = Http.get(news_url)
  doc  = Nokogiri::HTML(html)
  doc.css("a").map { |a|
    href = a["href"].to_s
    next if href.empty? || href.start_with?("#")
    u = abs_url(news_url, href)
    u if u =~ /application-for-planning-approval/i
  }.compact.uniq
end

links = []
begin
  links = detail_links_from_list(LIST_URL)
rescue => e
  warn "List fetch failed, will try news listing: #{e.class} #{e.message}"
end

if links.empty?
  begin
    links = detail_links_from_news(NEWS_URL)
  rescue => e
    warn "News fetch failed: #{e.class} #{e.message}"
  end
end

links.uniq!

puts "Found #{links.length} candidate link(s) for #{TABLE}"

saved = 0

links.each do |u|
  begin
    item = parse_detail(u)
  rescue => e
    warn "Skip #{u}: #{e.class} #{e.message}"
    next
  end
  next unless item

  DB.upsert(TABLE, {
    description: item[:description],
    date_received: item[:date_received],
    date_received_raw: item[:date_received_raw],
    address: item[:address],
    council_reference: item[: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 = ?, on_notice_to = ?, on_notice_to_raw = ?, title_reference = ? WHERE council_reference = ? AND address = ?")
    upd.execute(item[:document_url], item[:date_received], item[:date_received_raw], item[:title_reference], item[:council_reference], item[:address])
  rescue => e
    warn "Extras update skipped for #{item[:council_reference]}: #{e.class} #{e.message}"
  end

  puts "Upserted #{item[:council_reference]} -> #{item[:address]}"
  saved += 1
end

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