-- map.vapp.vn — OpenStreetMap Derivative Database: method of alteration (ODbL 1.0 §4.6(b))
--
-- Operator: ERPFit (CÔNG TY TNHH ERPFIT), map.vapp.vn
-- Original database: OpenStreetMap, © OpenStreetMap contributors, ODbL 1.0
--   https://www.openstreetmap.org/copyright · https://opendatacommons.org/licenses/odbl/1-0/
-- Extract: Geofabrik "vietnam-latest.osm.pbf" (https://download.geofabrik.de/asia/vietnam.html),
--   loaded with: osm2pgsql --create --hstore --multi-geometry  (default style → planet_osm_point/line/polygon)
--
-- Scope: the table vapp.geocode and the Wikidata rows whose coordinate is taken from it. These are
-- used publicly by the geocode, autocomplete and reverse-geocode endpoints. The Derivative Database
-- is offered under ODbL 1.0. The search index has two separate parts: one holds only this Derivative
-- Database (with those Wikidata rows); the other holds the non-OSM sources (Overture Maps Places,
-- Foursquare Open Places, store locators). The non-OSM part is not altered with OSM data, keeps its
-- own licences (see https://map.vapp.vn/attribution), and only search results from the two parts are
-- combined, as in the OSMF Geocoding Guideline.
--
-- Applying the steps below, in order, to the extract above reproduces every difference between the
-- OSM data and our Derivative Database. Requests for a full copy: support@erpfit.com.
--
-- Steps:
--   1. data/maps/geocode.sql — builds vapp.geocode from the osm2pgsql tables (field selection, centroids, class importance)
--   2. data/maps/sovereignty-cleanup.sql — removes PRC-framed CJK-named features in the Biển Đông box (Law 27/2018)
--   3. data/maps/maps-41b-coord-fix.sql — moves one Wikidata row onto an OSM place coordinate
--   4. data/maps/prominence.sql — adds an urban prominence boost (our own city_center list = additional Contents) and snaps Wikidata admin rows to OSM place points

-- ════ Step 1: data/maps/geocode.sql ════

-- MAPS-03 (first cut) — self-hosted geocoder over our OWN OSM PostGIS data.
-- Frugal + sovereign: no external API (Goong/Google), no Pelias/Elasticsearch. A single
-- materialized corpus of named features + pg_trgm fuzzy match. Feeds the demo search box and,
-- later, the vapp-maps gateway (MAPS-05). Unblocks nothing from dulieu — uses OSM only.

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS unaccent;

-- Materialized corpus: named points + polygons + roads, each reduced to a centroid,
-- with a normalized (unaccent+lower) name and an importance score for ranking.
DROP TABLE IF EXISTS vapp.geocode;
CREATE TABLE vapp.geocode AS
WITH src AS (
  -- POINTS: places, amenities, shops, etc.
  SELECT name,
         COALESCE(place, amenity, shop, tourism, 'poi') AS kind,
         CASE place WHEN 'state' THEN 0.92 WHEN 'province' THEN 0.92 WHEN 'region' THEN 0.85
              WHEN 'city' THEN 0.95 WHEN 'town' THEN 0.8 WHEN 'suburb' THEN 0.7
              WHEN 'village' THEN 0.6 WHEN 'neighbourhood' THEN 0.5 ELSE NULL END AS place_imp,
         way
  FROM planet_osm_point WHERE name IS NOT NULL
  UNION ALL
  -- POLYGONS: admin areas, buildings, landuse, water bodies (centroid).
  SELECT name,
         COALESCE(CASE WHEN boundary='administrative' THEN 'admin' END,
                  amenity, leisure, "natural", building, 'area') AS kind,
         CASE WHEN boundary='administrative' THEN 0.75 ELSE NULL END AS place_imp,
         ST_Centroid(way)
  FROM planet_osm_polygon WHERE name IS NOT NULL
  UNION ALL
  -- ROADS/LINES: streets (centroid of the line).
  SELECT name, COALESCE(highway, waterway, 'way') AS kind, NULL::float8,
         ST_Centroid(way)
  FROM planet_osm_line WHERE name IS NOT NULL AND highway IS NOT NULL
)
SELECT
  row_number() OVER () AS id,
  name,
  lower(unaccent(name)) AS name_norm,
  kind,
  COALESCE(place_imp,
    CASE kind
      WHEN 'motorway' THEN 0.55 WHEN 'trunk' THEN 0.5 WHEN 'primary' THEN 0.45
      WHEN 'secondary' THEN 0.4 WHEN 'admin' THEN 0.7 ELSE 0.3 END) AS importance,
  ST_Y(ST_Transform(way,4326))::double precision AS lat,
  ST_X(ST_Transform(way,4326))::double precision AS lng,
  way
FROM src;

CREATE INDEX geocode_name_trgm ON vapp.geocode USING gin (name_norm gin_trgm_ops);
CREATE INDEX geocode_way_idx ON vapp.geocode USING gist (way);
-- Prefix type-ahead index (MAPS-13c): btree text_pattern_ops → tight range scan for `LIKE 'q%'`.
CREATE INDEX geocode_name_prefix ON vapp.geocode (name_norm text_pattern_ops);
ANALYZE vapp.geocode;

-- Geocode: fuzzy name search over OSM base (vapp.geocode) UNION Overture Places (vapp.poi_overture,
-- MAPS-18 — 1.3M permissive VN business POIs, CDLA-Permissive-2.0). Ranked by trigram similarity ×
-- (importance | Overture confidence). Tables stay provenance-separate; unified only at query time.
-- PERF: the 1.3M-row Overture branch is gated to selective queries (len>=5) so short/common terms
-- stay OSM-fast; plpgsql makes the length test a One-Time Filter that skips the big GIN scan.
-- (pg_trgm fuzzy over 1.7M rows is at its ceiling for short terms → MAPS-13 Tantivy is the real fix.)
CREATE OR REPLACE FUNCTION vapp.geocode(q text, lim int DEFAULT 8)
RETURNS TABLE(name text, kind text, lat double precision, lng double precision, score real)
AS $$
DECLARE qn text := lower(unaccent(q));
BEGIN
  RETURN QUERY
  WITH hits AS (
    SELECT g.name, g.kind, g.lat, g.lng, (similarity(g.name_norm, qn)*(0.6+g.importance))::real AS score
    FROM vapp.geocode g WHERE g.name_norm % qn
    UNION ALL
    SELECT p.name, p.category, p.lat, p.lng, (similarity(p.name_norm, qn)*(0.6+p.confidence))::real
    FROM vapp.poi_overture p WHERE char_length(qn) >= 5 AND p.name_norm % qn
    UNION ALL
    -- Wikidata prominence layer (MAPS-22, CC0): only 14k rows, no perf gate. importance is
    -- sitelink-derived (up to 2.0) so famous landmarks/cities outrank same-named rural features.
    SELECT w.name, w.kind, w.lat, w.lng, (similarity(w.name_norm, qn)*(0.6+w.importance))::real
    FROM vapp.poi_wikidata w WHERE w.name_norm % qn
  )
  SELECT h.name, h.kind, h.lat, h.lng, h.score FROM hits h ORDER BY h.score DESC LIMIT lim;
END $$ LANGUAGE plpgsql STABLE;

-- Reverse geocode: nearest named feature (KNN on the point index) to a lon/lat.
CREATE OR REPLACE FUNCTION vapp.reverse_geocode(in_lng double precision, in_lat double precision)
RETURNS TABLE(name text, kind text, lat double precision, lng double precision, dist_m double precision)
AS $$
  WITH p AS (SELECT ST_Transform(ST_SetSRID(ST_MakePoint(in_lng,in_lat),4326),3857) g)
  SELECT g.name, g.kind, g.lat, g.lng, ST_Distance(g.way, p.g) AS dist_m
  FROM vapp.geocode g, p
  ORDER BY g.way <-> p.g
  LIMIT 1;
$$ LANGUAGE sql STABLE;

-- Autocomplete: fast PREFIX type-ahead (accent-insensitive), importance-ranked.
-- Powers the search box live dropdown + T1 industries (store-locator, address entry).
-- MAPS-13c: prefix-ONLY (index-backed) — dropped the fuzzy `% qn` OR that pulled ~51K rows +
-- 33K index-recheck (was 230ms → 8.8ms). Fuzzy/typo recall stays in vapp.geocode() (submit/Enter).
-- Overture branch gated to len>=4 (prefix 'ph%' is too broad on 1.3M rows).
CREATE OR REPLACE FUNCTION vapp.autocomplete(q text, lim int DEFAULT 6)
RETURNS TABLE(name text, kind text, lat double precision, lng double precision, score real)
AS $$
DECLARE qn text := lower(unaccent(q));
BEGIN
  RETURN QUERY
  WITH hits AS (
    SELECT g.name, g.kind, g.lat, g.lng, (0.6+g.importance)::real AS score
    FROM vapp.geocode g WHERE g.name_norm LIKE qn || '%'
    UNION ALL
    SELECT p.name, p.category, p.lat, p.lng, (0.6+p.confidence)::real
    FROM vapp.poi_overture p WHERE char_length(qn) >= 4 AND p.name_norm LIKE qn || '%'
    UNION ALL
    SELECT w.name, w.kind, w.lat, w.lng, (0.6+w.importance)::real
    FROM vapp.poi_wikidata w WHERE w.name_norm LIKE qn || '%'
  )
  SELECT h.name, h.kind, h.lat, h.lng, h.score FROM hits h ORDER BY h.score DESC LIMIT lim;
END $$ LANGUAGE plpgsql STABLE;

-- ════ Step 2: data/maps/sovereignty-cleanup.sql ════

-- MAPS-28.2 sovereignty cleanup — remove PRC-framed features from geocode corpus
-- Chinese-named underwater features + PRC administrative claims (Sansha City / Nansha
-- District / Hainan Province) in the Biển Đông / Hoàng Sa / Trường Sa zone.
-- These leak from OSM data and violate Law 27/2018 sovereignty compliance.
--
-- Run after any geocode corpus rebuild (osm2pgsql re-import, Overture re-import).
-- psql -h localhost -p 5434 -U postgres -d vapp_maps -f data/maps/sovereignty-cleanup.sql

BEGIN;

-- CJK-named features in the South China Sea sovereignty zone (109-118E, 6-18N)
DELETE FROM vapp.geocode
WHERE name ~ '[一-龥㐀-鿿豈-﫿]'
  AND lng BETWEEN 109 AND 118
  AND lat BETWEEN 6 AND 18;

COMMIT;

SELECT count(*) AS remaining_cjk_in_scs FROM vapp.geocode
WHERE name ~ '[一-龥㐀-鿿豈-﫿]'
  AND lng BETWEEN 109 AND 118
  AND lat BETWEEN 6 AND 18;

-- ════ Step 3: data/maps/maps-41b-coord-fix.sql ════

-- MAPS-41b — coordinate corrections for two flagged geocode losses, from Wikidata P625 (CC0, legal).
--
-- 1. Bà Nà Hills: the only corpus rows were Overture, and the top-scoring one (kind 'tours',
--    imp 0.847) sits at 108.2517 — ~28 km E of the actual resort, on the coast. Wikidata Q5004763
--    P625 = 15.99555556, 107.99416667 (the Sun World Bà Nà Hills resort, Trường Sơn range). We ADD
--    a prominence-carrying Wikidata row so the correct coordinate wins (landmarks keep their precise
--    P625 — the admin-centroid snap in prominence.sql only touches admin kinds).
-- 2. Tây Ninh: the top Wikidata row is the PROVINCE entity Q36608, whose P625 is the boundary
--    centroid (11.3333, 106.1667) — 7.7 km NE of downtown, in farmland. A province name should
--    resolve to its tỉnh lỵ downtown (as every rival does). We move Q36608's representative point to
--    the OSM city point already in our corpus (thành phố Tây Ninh, 11.313655, 106.096697; ODbL,
--    already ingested), matching the intent of prominence.sql's admin-centroid → OSM-downtown snap.
--
-- Idempotent. Run BEFORE prominence.sql (which recomputes importance = imp_base + urban_boost from
-- the corrected coords) and then rebuild the Tantivy index (`vapp-maps build-index`).

-- 1. Bà Nà Hills (Q5004763, 9 sitelinks → importance 0.6 + (ln(10)-0.7)*0.36 = 1.177) --------------
DELETE FROM vapp.poi_wikidata WHERE qid = 'Q5004763';
INSERT INTO vapp.poi_wikidata (qid, name, name_norm, kind, sitelinks, importance, imp_base, lat, lng, way)
VALUES ('Q5004763', 'Bà Nà Hills', 'ba na hills', 'landmark', 9, 1.177, 1.177,
        15.99555556, 107.99416667,
        ST_Transform(ST_SetSRID(ST_MakePoint(107.99416667, 15.99555556), 4326), 3857));

-- 2. Tây Ninh province representative point → downtown (thành phố Tây Ninh) -------------------------
UPDATE vapp.poi_wikidata
SET lat = 11.313655, lng = 106.096697,
    way = ST_Transform(ST_SetSRID(ST_MakePoint(106.096697, 11.313655), 4326), 3857)
WHERE qid = 'Q36608';

SELECT 'maps-41b coord fix applied' AS status,
       (SELECT round(lat::numeric,5)||','||round(lng::numeric,5) FROM vapp.poi_wikidata WHERE qid='Q5004763') AS ba_na,
       (SELECT round(lat::numeric,5)||','||round(lng::numeric,5) FROM vapp.poi_wikidata WHERE qid='Q36608') AS tay_ninh;

-- ════ Step 4: data/maps/prominence.sql ════

-- MAPS-22b — Urban-density prominence boost for the OSM geocode corpus.
--
-- Problem: vapp.geocode.importance is derived purely from OSM feature CLASS (road type / place
-- type), so every "Đường Nguyễn Huệ" trunk road scores 0.5 whether it is the world-famous HCMC
-- pedestrian street or a rural road in Lào Cai. Wikidata (MAPS-22a) fixes NAMED landmarks, but the
-- hundreds of thousands of ordinary streets need a universal prominence signal.
--
-- Signal: proximity to a major VN population center. VN population/search demand is heavily
-- concentrated in a handful of metros; a feature in a dense urban core is far more likely to be the
-- intended result. boost = max over cities of  weight · exp(-dist_km / scale). This is exactly the
-- kind of population/centrality prior Nominatim/Google fold into ranking.
--
-- Idempotent: snapshots the class-based importance into imp_base once, then recomputes
-- importance = imp_base + urban_boost every run (never compounds). Run AFTER geocode.sql.

-- Curated major-city centers (lng, lat, weight). Weight ≈ population/search prominence.
DROP TABLE IF EXISTS vapp.city_center;
CREATE TABLE vapp.city_center(name text, lng double precision, lat double precision, weight double precision);
INSERT INTO vapp.city_center(name,lng,lat,weight) VALUES
  ('Hồ Chí Minh',    106.700, 10.776, 0.70),
  ('Hà Nội',         105.850, 21.028, 0.70),
  ('Đà Nẵng',        108.221, 16.054, 0.38),
  ('Hải Phòng',      106.688, 20.844, 0.30),
  ('Cần Thơ',        105.784, 10.035, 0.30),
  ('Biên Hòa',       106.824, 10.957, 0.26),
  ('Nha Trang',      109.190, 12.239, 0.26),
  ('Huế',            107.585, 16.463, 0.24),
  ('Vũng Tàu',       107.084, 10.346, 0.22),
  ('Buôn Ma Thuột',  108.050, 12.667, 0.20),
  ('Quy Nhơn',       109.220, 13.782, 0.20),
  ('Vinh',           105.692, 18.679, 0.20),
  ('Hạ Long',        107.079, 20.951, 0.20),
  ('Thủ Dầu Một',    106.652, 10.980, 0.20),
  ('Thái Nguyên',    105.848, 21.594, 0.16),
  ('Nam Định',       106.168, 20.428, 0.16),
  ('Đà Lạt',         108.442, 11.940, 0.18),
  ('Rạch Giá',       105.081, 10.012, 0.15),
  ('Long Xuyên',     105.436, 10.386, 0.15),
  ('Mỹ Tho',         106.360, 10.360, 0.15);

-- Snapshot the class-based importance ONCE so re-running never compounds the boost.
ALTER TABLE vapp.geocode ADD COLUMN IF NOT EXISTS imp_base double precision;
UPDATE vapp.geocode SET imp_base = importance WHERE imp_base IS NULL;

-- Recompute importance = imp_base + urban_boost.  scale = 11 km (urban-core radius); the boost is
-- effectively 0 by ~40 km out, so rural features keep their class importance unchanged.
WITH boost AS (
  SELECT g.id,
         COALESCE(MAX(c.weight * exp(-(ST_DistanceSphere(ST_MakePoint(g.lng,g.lat),
                                                         ST_MakePoint(c.lng,c.lat))/1000.0) / 11.0)), 0) AS b
  FROM vapp.geocode g
  LEFT JOIN vapp.city_center c
    ON ST_DistanceSphere(ST_MakePoint(g.lng,g.lat), ST_MakePoint(c.lng,c.lat)) < 60000  -- 60 km cutoff
  GROUP BY g.id
)
UPDATE vapp.geocode g SET importance = g.imp_base + boost.b
FROM boost WHERE boost.id = g.id;

ANALYZE vapp.geocode;

-- Coordinate snap: Wikidata sets P625 for administrative units (city/province/district/ward) to
-- the geometric CENTROID of a sprawling boundary, which can land 30+ km from downtown (e.g. the
-- "Cần Thơ" municipality entity sits 33 km from Ninh Kiều). OSM place=* points, by contrast, are
-- placed at the town center. So for admin/place-kind Wikidata rows, adopt the nearest same-named OSM
-- place point (best of both: Wikidata prominence + OSM's accurate downtown coordinate). Landmarks
-- (museums, markets, bridges…) keep their precise Wikidata coordinates untouched.
WITH snap AS (
  SELECT DISTINCT ON (w.qid) w.qid, g.lat AS glat, g.lng AS glng, g.way AS gway
  FROM vapp.poi_wikidata w
  JOIN vapp.geocode g
    ON g.name_norm = w.name_norm
   AND g.kind IN ('city','town','suburb','village','neighbourhood','admin')
   AND ST_DistanceSphere(ST_MakePoint(w.lng,w.lat), ST_MakePoint(g.lng,g.lat)) < 35000
  WHERE w.kind IN ('city','town','district','ward','commune')
  ORDER BY w.qid, ST_DistanceSphere(ST_MakePoint(w.lng,w.lat), ST_MakePoint(g.lng,g.lat))
)
UPDATE vapp.poi_wikidata w
SET lat = snap.glat, lng = snap.glng, way = snap.gway
FROM snap WHERE snap.qid = w.qid;

-- Apply the SAME urban prominence to the Wikidata layer so location-in-a-metro is rewarded
-- consistently across sources (a "Lê Lợi" ward in a remote province stays flat; a same-named
-- feature in HCMC/Hà Nội rises). Cheap: only ~14k rows.
ALTER TABLE vapp.poi_wikidata ADD COLUMN IF NOT EXISTS imp_base double precision;
UPDATE vapp.poi_wikidata SET imp_base = importance WHERE imp_base IS NULL;
WITH boost AS (
  SELECT w.qid,
         COALESCE(MAX(c.weight * exp(-(ST_DistanceSphere(ST_MakePoint(w.lng,w.lat),
                                                         ST_MakePoint(c.lng,c.lat))/1000.0) / 11.0)), 0) AS b
  FROM vapp.poi_wikidata w
  LEFT JOIN vapp.city_center c
    ON ST_DistanceSphere(ST_MakePoint(w.lng,w.lat), ST_MakePoint(c.lng,c.lat)) < 60000
  GROUP BY w.qid
)
UPDATE vapp.poi_wikidata w SET importance = w.imp_base + boost.b
FROM boost WHERE boost.qid = w.qid;
ANALYZE vapp.poi_wikidata;

SELECT 'urban boost applied' AS status,
       (SELECT count(*) FILTER (WHERE importance > imp_base + 0.05) FROM vapp.geocode) AS geocode_boosted,
       (SELECT round(max(importance)::numeric,3) FROM vapp.geocode) AS geocode_max,
       (SELECT round(max(importance)::numeric,3) FROM vapp.poi_wikidata) AS wikidata_max;