-- OSM no-hunting land in the US and Canada → geo.osm_exclusions, replacing the jurisdictions this file covers.
-- Inputs (filled by tools/geodata/land-status/osm-exclusions, or by OsmExclusionImportTests):
--   staging.osm_exclusions_raw  GDAL OSM driver multipolygons (osm_id, osm_way_id, name, leisure, landuse, amenity,
--                               tourism, geom)
--   staging.osm_exclusions_run  one row: version, source_url, jurisdictions (the states/provinces the file fully
--                               covers; null = all)
-- Parks, playgrounds, golf courses, cemeteries, schools, sports grounds, campgrounds and built-up land: hunting is
-- usually not allowed there, and the government land layers miss most of them (municipal parks, park land on Crown
-- parcels). Big parks (over 2,000 ha) are left to the official protected-area layers (CPCAD, PAD-US), which carry
-- their real rules; OSM's huge "park" polygons include hunted land (state forest preserves, regional parks).
-- ODbL rows stay in their own table (CLAUDE.md) and never enter geo.land_status.

begin;

create temporary table exclusions_norm on commit drop as
with o as (
  select *, st_multi(st_collectionextract(st_makevalid(geom), 3)) as valid_geom
  from staging.osm_exclusions_raw
),
k as (
  select o.*,
    case
      when leisure = 'playground' then 'playground'
      when leisure in ('golf_course', 'miniature_golf') then 'golf_course'
      when landuse = 'cemetery' or amenity = 'grave_yard' then 'cemetery'
      when amenity in ('school', 'college', 'university', 'kindergarten') then 'school'
      when leisure in ('pitch', 'sports_centre', 'stadium', 'track', 'recreation_ground') or landuse = 'recreation_ground' then 'sports'
      when tourism in ('camp_site', 'caravan_site') then 'campground'
      when leisure in ('park', 'garden', 'dog_park') or tourism in ('zoo', 'theme_park') then 'park'
      when landuse in ('residential', 'commercial', 'retail') or amenity = 'hospital' then 'built_up'
    end as kind
  from o
  where not st_isempty(o.valid_geom)
)
select
  case when osm_id is not null then 'r' || osm_id else 'w' || osm_way_id end as osm_id,
  kind,
  nullif(trim(name), '') as name,
  (
    select j.code from geo.jurisdictions j
    where j.parent_code in ('US', 'CA') and j.geom is not null and st_intersects(j.geom, st_pointonsurface(k.valid_geom))
    limit 1
  ) as jurisdiction_code,
  valid_geom as geom
from k
where kind is not null
  and not (kind = 'park' and st_area(valid_geom::geography) > 2e7);

delete from exclusions_norm n
using staging.osm_exclusions_run r
where n.jurisdiction_code is null or (r.jurisdictions is not null and not n.jurisdiction_code = any(r.jurisdictions));

delete from geo.osm_exclusions e
using staging.osm_exclusions_run r
where r.jurisdictions is null or e.jurisdiction_code = any(r.jurisdictions);

insert into geo.osm_exclusions (osm_id, kind, name, jurisdiction_code, geom)
select osm_id, kind, name, jurisdiction_code, geom from exclusions_norm;

insert into geo.dataset_imports (id, dataset_key, version, source_url, license, attribution, feature_count, checksum, scope, imported_at)
select
  encode(set_bit(set_bit(overlay(uuid_send(gen_random_uuid()) placing
    substring(int8send(floor(extract(epoch from clock_timestamp()) * 1000)::bigint) from 3) from 1 for 6), 52, 1), 53, 1), 'hex')::uuid,
  'osm-exclusions',
  r.version,
  r.source_url,
  'ODbL 1.0',
  '© OpenStreetMap contributors',
  (select count(*) from exclusions_norm),
  (select md5(string_agg(osm_id || ':' || md5(st_asbinary(geom)), ',' order by osm_id)) from exclusions_norm),
  coalesce(array_to_string(r.jurisdictions, ' '), 'US CA'),
  now()
from staging.osm_exclusions_run r;

commit;