-- ============================================
-- Restore Public/Buddy Bottle Read Access
-- ============================================
-- This script fixes the issue where non-owners cannot see bottle data 
-- for public splits or shared tastings.
-- ============================================

-- Drop the overly restrictive performance-fix policy
DROP POLICY IF EXISTS "bottles_policy" ON bottles;

-- 1. Unified SELECT policy: owner OR active split viewer OR session participant
CREATE POLICY "bottles_select_policy" ON bottles
FOR SELECT USING (
  -- Owner access
  (SELECT auth.uid()) = user_id OR 
  
  -- Public split access (anyone can see bottle info if the split is active)
  EXISTS (
    SELECT 1 FROM bottle_splits 
    WHERE bottle_id = bottles.id AND is_active = true
  ) OR
  
  -- Participant access (user is already part of this split)
  EXISTS (
    SELECT 1 FROM split_participants sp
    JOIN bottle_splits bs ON bs.id = sp.split_id
    WHERE bs.bottle_id = bottles.id AND sp.user_id = (SELECT auth.uid())
  ) OR

  -- Buddy/Session access (user is a buddy in a session involving this bottle)
  id IN (
    SELECT t.bottle_id 
    FROM tastings t
    JOIN tasting_sessions ts ON ts.id = t.session_id
    JOIN session_participants sp ON sp.session_id = ts.id
    JOIN buddies b ON b.id = sp.buddy_id
    WHERE b.buddy_profile_id = (SELECT auth.uid())
  )
);

-- 2. Owner-only for modifications (No change needed from security perspective)
CREATE POLICY "bottles_insert_policy" ON bottles
FOR INSERT WITH CHECK ((SELECT auth.uid()) = user_id);

CREATE POLICY "bottles_update_policy" ON bottles
FOR UPDATE USING ((SELECT auth.uid()) = user_id);

CREATE POLICY "bottles_delete_policy" ON bottles
FOR DELETE USING ((SELECT auth.uid()) = user_id);

-- ============================================
-- Verification query
-- ============================================
-- SELECT * FROM pg_policies WHERE tablename = 'bottles';