-- pSEO article data: per-city padel cost and market analysis.
-- One row per city — consumed by the city-cost-de.md.jinja template to generate
-- ~600 city-level articles about padel investment costs.
--
-- Calculator override columns use camelCase to match the DEFAULTS keys in
-- planner/calculator.py, so they are auto-applied as calc pre-fills.

MODEL (
  name serving.pseo_city_costs_de,
  kind FULL,
  cron '@daily',
  grain city_key
);

SELECT
  -- Composite natural key: country_slug + city_slug ensures uniqueness across countries
  -- (city_slug alone is not unique — 'valencia' exists in ES, VE, etc.)
  c.country_slug || '-' || c.city_slug  AS city_key,
  -- City identity
  c.city_slug,
  c.city_name,
  c.country_code,
  c.country_name_en,
  c.country_slug,
  -- Market metrics
  c.population,
  c.padel_venue_count,
  c.venues_per_100k,
  c.market_score,
  lop.opportunity_score,
  c.data_confidence,
  -- Pricing (from Playtomic, NULL when no coverage)
  c.median_hourly_rate,
  c.median_peak_rate,
  c.median_offpeak_rate,
  c.median_occupancy_rate,
  c.median_daily_revenue_per_venue,
  COALESCE(c.price_currency, p.price_currency, 'EUR') AS price_currency,
  -- Calculator override columns — camelCase keys match planner DEFAULTS auto-mapping.
  -- utilTarget: planner stores 0.0–1.0, calculator expects 0–100.
  p.rate_peak                       AS "ratePeak",
  p.rate_off_peak                   AS "rateOffPeak",
  p.avg_utilisation_pct * 100       AS "utilTarget",
  FLOOR(p.courts_typical)           AS "dblCourts",
  -- 'country' drives currency formatting in the calculator
  c.country_code                    AS "country",
  CURRENT_DATE                      AS refreshed_date
FROM serving.city_market_profile c
LEFT JOIN serving.planner_defaults p
  ON c.country_code = p.country_code
  AND c.city_slug = p.city_slug
LEFT JOIN serving.location_opportunity_profile lop
  ON c.country_code = lop.country_code
  AND c.geoname_id = lop.geoname_id
-- Only cities with actual padel presence and at least some rate data
WHERE c.padel_venue_count > 0
  AND (p.rate_peak IS NOT NULL OR c.median_peak_rate IS NOT NULL)
