-- Padel court locations from OpenStreetMap via Overpass API.
-- Reads landing zone JSON directly, unnests elements, filters to nodes with
-- valid coordinates, deduplicates on osm_id, and approximates country from bbox.
--
-- Source: data/landing/overpass/{year}/{month}/courts.json.gz

MODEL (
  name staging.stg_padel_courts,
  kind FULL,
  cron '@daily',
  grain osm_id
);

WITH parsed AS (
  SELECT
    elem ->> 'type'                     AS osm_type,
    (elem ->> 'id')::BIGINT             AS osm_id,
    TRY_CAST(elem ->> 'lat' AS DOUBLE)  AS lat,
    TRY_CAST(elem ->> 'lon' AS DOUBLE)  AS lon,
    elem -> 'tags' ->> 'name'           AS name,
    elem -> 'tags' ->> 'addr:country'   AS country_code,
    elem -> 'tags' ->> 'addr:city'      AS city_tag,
    elem -> 'tags' ->> 'addr:postcode'  AS postcode,
    elem -> 'tags' ->> 'operator'       AS operator_name,
    elem -> 'tags' ->> 'opening_hours'  AS opening_hours,
    elem -> 'tags' ->> 'fee'            AS fee,
    filename                            AS source_file,
    CURRENT_DATE                        AS extracted_date
  FROM (
    SELECT UNNEST(elements) AS elem, filename
    FROM read_json(
      @LANDING_DIR || '/overpass/*/*/courts.json.gz',
      format = 'auto',
      filename = true
    )
  )
  WHERE (elem ->> 'type') IS NOT NULL
),
deduped AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY osm_id ORDER BY extracted_date DESC) AS rn
  FROM parsed
  WHERE osm_type = 'node'
    AND lat IS NOT NULL AND lon IS NOT NULL
    AND lat BETWEEN -90  AND 90
    AND lon BETWEEN -180 AND 180
),
with_country AS (
  SELECT
    osm_id, lat, lon,
    COALESCE(NULLIF(TRIM(UPPER(country_code)), ''), CASE
      WHEN lat BETWEEN 47.27 AND 55.06 AND lon BETWEEN  5.87 AND 15.04 THEN 'DE'
      WHEN lat BETWEEN 35.95 AND 43.79 AND lon BETWEEN -9.39 AND  4.33 THEN 'ES'
      WHEN lat BETWEEN 49.90 AND 60.85 AND lon BETWEEN -8.62 AND  1.77 THEN 'GB'
      WHEN lat BETWEEN 41.36 AND 51.09 AND lon BETWEEN -5.14 AND  9.56 THEN 'FR'
      WHEN lat BETWEEN 45.46 AND 47.80 AND lon BETWEEN  5.96 AND 10.49 THEN 'CH'
      WHEN lat BETWEEN 46.37 AND 49.02 AND lon BETWEEN  9.53 AND 17.16 THEN 'AT'
      WHEN lat BETWEEN 36.35 AND 47.09 AND lon BETWEEN  6.62 AND 18.51 THEN 'IT'
      WHEN lat BETWEEN 37.00 AND 42.15 AND lon BETWEEN -9.50 AND -6.19 THEN 'PT'
      ELSE NULL
    END)                      AS country_code,
    NULLIF(TRIM(name), '')    AS name,
    NULLIF(TRIM(city_tag), '') AS city,
    postcode, operator_name, opening_hours, fee, extracted_date
  FROM deduped
  WHERE rn = 1
)
SELECT
  osm_id,
  'osm'          AS source,
  lat, lon, country_code, name, city, postcode, operator_name, opening_hours,
  CASE LOWER(fee) WHEN 'yes' THEN TRUE WHEN 'no' THEN FALSE ELSE NULL END AS is_paid,
  extracted_date
FROM with_country
