-- Extensions + search/geo indexes
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS unaccent;
--> statement-breakpoint
ALTER TABLE facilities ADD COLUMN IF NOT EXISTS search tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('simple'::regconfig, coalesce(name,'')), 'A') ||
    setweight(to_tsvector('simple'::regconfig, coalesce(city,'') || ' ' || coalesce(region_name,'') || ' ' || coalesce(address,'')), 'B') ||
    setweight(to_tsvector('simple'::regconfig, coalesce(description,'')), 'C')
  ) STORED;
--> statement-breakpoint
CREATE INDEX IF NOT EXISTS facilities_search_idx ON facilities USING gin (search);
--> statement-breakpoint
CREATE INDEX IF NOT EXISTS facilities_name_trgm_idx ON facilities USING gin (name gin_trgm_ops);
--> statement-breakpoint
CREATE INDEX IF NOT EXISTS facilities_city_trgm_idx ON facilities USING gin (city gin_trgm_ops);
--> statement-breakpoint
CREATE INDEX IF NOT EXISTS operators_name_trgm_idx ON operators USING gin (name gin_trgm_ops);
--> statement-breakpoint
CREATE INDEX IF NOT EXISTS metros_name_trgm_idx ON metros USING gin (name gin_trgm_ops);
--> statement-breakpoint
CREATE INDEX IF NOT EXISTS projects_name_trgm_idx ON projects USING gin (name gin_trgm_ops);
--> statement-breakpoint
CREATE INDEX IF NOT EXISTS facilities_latlng_idx ON facilities (lat, lng) WHERE lat IS NOT NULL;
--> statement-breakpoint
CREATE INDEX IF NOT EXISTS facilities_live_idx ON facilities (status, country_iso2) WHERE merged_into IS NULL;