thrilltrack-explorer/supabase/migrations/20251017133149_e67ae2ec-ce21-474b-a8b8-10f9730b0e8e.sql

-- JSONB Elimination Phase 1 (CORRECTED)
-- Creates relational tables to replace JSONB columns

-- ============================================================================
-- 1. COASTER_STATS (Already created successfully)
-- ============================================================================
-- Table created in previous migration attempt

-- ============================================================================
-- 2. TECHNICAL_SPECIFICATIONS (Already created successfully)
-- ============================================================================
-- Table created in previous migration attempt

-- ============================================================================
-- 3. LIST_ITEMS (CORRECTED - Remove visibility reference)
-- ============================================================================
CREATE TABLE IF NOT EXISTS public.list_items (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  list_id UUID NOT NULL REFERENCES public.user_top_lists(id) ON DELETE CASCADE,
  entity_type TEXT NOT NULL CHECK (entity_type IN ('park', 'ride', 'coaster')),
  entity_id UUID NOT NULL,
  position INTEGER NOT NULL,
  notes TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(list_id, position),
  UNIQUE(list_id, entity_id)
);

CREATE INDEX IF NOT EXISTS idx_list_items_list ON public.list_items(list_id);
CREATE INDEX IF NOT EXISTS idx_list_items_entity ON public.list_items(entity_id, entity_type);

ALTER TABLE public.list_items ENABLE ROW LEVEL SECURITY;

-- RLS: Users can view list items if they own the list OR the list is findable by others
CREATE POLICY "Users view own list items"
  ON public.list_items FOR SELECT
  USING (EXISTS (
    SELECT 1 FROM public.user_top_lists utl
    WHERE utl.id = list_items.list_id
    AND utl.user_id = auth.uid()
  ));

-- RLS: Users can manage their own list items
CREATE POLICY "Users manage own list items"
  ON public.list_items FOR ALL
  USING (EXISTS (
    SELECT 1 FROM public.user_top_lists utl
    WHERE utl.id = list_items.list_id AND utl.user_id = auth.uid()
  ));