-- OSM protected areas → geo.osm_public_lands, replacing the countries in this extract (roadmap 3.2b).
-- Inputs (filled by tools/geodata/public-land/osm, or by OsmImportTests):
--   staging.osm_protected  GDAL OSM driver multipolygons (osm_id, osm_way_id, name, name_en, boundary, leisure,
--                          protect_class, protection_title, designation, operator, ownership, access, hunting, geom)
--   staging.ne_countries   Natural Earth countries (iso_a2_eh, geom)
--   staging.osm_run        one row: version, source_url, countries (the ones the file fully covers; null = all)
-- ODbL rows stay in their own table (CLAUDE.md). Countries with their own public-land source (PAD-US, CPCAD) are
-- skipped, so a region never mixes OSM and non-OSM public-land polygons (docs/07 §5).

begin;

create temporary table osm_norm on commit drop as
with o as (
  select *, st_multi(st_collectionextract(st_makevalid(geom), 3)) as valid_geom
  from staging.osm_protected
),
located as (
  select o.*, (
    select n.iso_a2_eh from staging.ne_countries n
    where n.iso_a2_eh ~ '^[A-Z]{2}$' and st_intersects(n.geom, st_pointonsurface(o.valid_geom))
    limit 1
  ) as country
  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 source_id,
  nullif(trim(name), '') as name,
  nullif(trim(operator), '') as manager_name,
  case ownership
    when 'national' then 'federal'
    when 'state' then 'state'
    when 'municipal' then 'local'
    when 'private' then 'private'
    else 'unknown'
  end as manager_type,
  coalesce(
    nullif(trim(protection_title), ''),
    nullif(trim(designation), ''),
    case when boundary = 'national_park' then 'National park' when leisure = 'nature_reserve' then 'Nature reserve' else 'Protected area' end
  ) as designation,
  case
    when access in ('yes', 'permissive', 'designated') then 'open'
    when access in ('no', 'private') then 'closed'
    else 'unknown'
  end as public_access,
  case hunting when 'yes' then 'allowed' when 'no' then 'prohibited' else 'unknown' end as hunting,
  -- One generic colour for protected areas outside North America (docs/03 §5); the designation says what it is.
  'protected' as palette,
  country as country_code,
  jsonb_strip_nulls(jsonb_build_object(
    'name_en', nullif(trim(name_en), ''),
    'protect_class', nullif(trim(protect_class), ''),
    'osm', case when osm_id is not null then 'relation/' || osm_id else 'way/' || osm_way_id end
  )) as attributes,
  valid_geom as geom
from located
cross join staging.osm_run r
where country is not null
  and country not in ('US', 'CA', 'PR', 'VI', 'GU', 'AS', 'MP', 'UM')
  and (r.countries is null or country = any(r.countries));

delete from geo.osm_public_lands p
using staging.osm_run r
where p.source = 'OSM' and (r.countries is null or p.country_code = any(r.countries));

insert into geo.osm_public_lands
  (source, source_id, name, manager_name, manager_type, designation, public_access, hunting, palette, country_code, admin1, attributes, geom)
select 'OSM', source_id, name, manager_name, manager_type, designation, public_access, hunting, palette, country_code, null, attributes, geom
from osm_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-protected',
  r.version,
  r.source_url,
  'ODbL 1.0',
  '© OpenStreetMap contributors',
  (select count(*) from osm_norm),
  (select md5(string_agg(source_id || ':' || md5(st_asbinary(geom)), ',' order by source_id)) from osm_norm),
  coalesce(array_to_string(r.countries, ' '), 'ALL'),
  now()
from staging.osm_run r;

update geo.layers l
set source_version = r.version, updated_at = now()
from staging.osm_run r
where l.key = 'public-lands-osm';

commit;