CREATE TABLE IF NOT EXISTS page_listing_sections (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL UNIQUE,
  sort_order INTEGER NOT NULL DEFAULT 0,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO page_listing_sections (name, sort_order)
SELECT DISTINCT
  trim(listing_section) AS name,
  dense_rank() OVER (ORDER BY trim(listing_section)) * 10 AS sort_order
FROM pages
WHERE trim(COALESCE(listing_section, '')) <> ''
ON CONFLICT (name) DO NOTHING;

CREATE INDEX IF NOT EXISTS page_listing_sections_sort_order_name_idx
  ON page_listing_sections (sort_order ASC, name ASC);