-- ==============================================================================
-- JAZPLATFORM: MODERATION ACCESS & INSERT POLICIES
-- Migration: 20260829000001_moderation_and_insert_policies.sql
-- ==============================================================================

-- 1. Ensure sort_order column on property_images
ALTER TABLE public.property_images ADD COLUMN IF NOT EXISTS sort_order INT DEFAULT 0;

-- 2. Updatable view for property_amenity_map
CREATE OR REPLACE VIEW public.property_amenity_map AS 
SELECT property_id, amenity_id FROM public.property_amenity_mappings;

-- 3. INSERT & UPDATE POLICIES
DO $$ BEGIN
  CREATE POLICY "Enable insert for properties" ON public.properties FOR INSERT WITH CHECK (true);
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
  CREATE POLICY "Enable update for properties" ON public.properties FOR UPDATE USING (true);
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
  CREATE POLICY "Enable insert for property_images" ON public.property_images FOR INSERT WITH CHECK (true);
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
  CREATE POLICY "Enable update for property_images" ON public.property_images FOR UPDATE USING (true);
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
  CREATE POLICY "Enable insert for property_documents" ON public.property_documents FOR INSERT WITH CHECK (true);
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
  CREATE POLICY "Enable insert for property_amenity_mappings" ON public.property_amenity_mappings FOR INSERT WITH CHECK (true);
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
  CREATE POLICY "Enable insert for users" ON public.users FOR INSERT WITH CHECK (true);
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

-- 4. STAFF & MODERATION VISIBILITY POLICIES
DO $$ BEGIN
  CREATE POLICY "Staff view all properties" ON public.properties FOR SELECT USING (true);
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
  CREATE POLICY "Staff view all property images" ON public.property_images FOR SELECT USING (true);
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
  CREATE POLICY "Staff view all property documents" ON public.property_documents FOR SELECT USING (true);
EXCEPTION WHEN duplicate_object THEN NULL; END $$;