-- Setup for the "reachability needs recursion" exercise (DDIA 2e Ch. 3).
-- A location hierarchy encoded as an adjacency list: each place references the
-- place it is `within`. The depths vary on purpose, so that no *fixed* number
-- of joins can answer "is X within Y?" for every X.
--
-- Run once against a fresh Postgres 16:
--   docker exec ddia-ch3 psql -U postgres -d study -f /tmp/recursion_setup.sql
-- Then open an interactive session and type the queries by hand.

DROP TABLE IF EXISTS locations;

CREATE TABLE locations (
  id        int PRIMARY KEY,
  name      text NOT NULL,
  type      text NOT NULL,
  within_id int REFERENCES locations(id)   -- self-reference: the parent place
);

-- Hops below 'North America':
--   United States = 1, Idaho / California = 2,
--   Ada County / Los Angeles = 3, Boise = 4.
INSERT INTO locations (id, name, type, within_id) VALUES
  (1,  'North America', 'continent', NULL),
  (2,  'United States', 'country',   1),
  (3,  'Idaho',         'state',     2),
  (4,  'Ada County',    'county',    3),
  (5,  'Boise',         'city',      4),
  (6,  'California',     'state',     2),
  (7,  'Los Angeles',   'city',      6),
  (8,  'Europe',        'continent', NULL),
  (9,  'England',       'country',   8),
  (10, 'London',        'city',      9);
