-- ============================================
-- RLS Policy Performance Fixes Migration
-- Run this in Supabase SQL Editor
-- ============================================
-- Fixes two types of issues:
-- 1. auth_rls_initplan: Ensures auth.uid() is wrapped in (SELECT ...)
-- 2. multiple_permissive_policies: Consolidates overlapping policies
-- ============================================

-- ============================================
-- Fix 1: user_subscriptions
-- Issues: Multiple permissive policies for INSERT and SELECT
-- ============================================

-- Drop existing policies
DROP POLICY IF EXISTS "user_subscriptions_select_policy" ON user_subscriptions;
DROP POLICY IF EXISTS "user_subscriptions_admin_policy" ON user_subscriptions;
DROP POLICY IF EXISTS "user_subscriptions_insert_self" ON user_subscriptions;

-- Consolidated SELECT policy: user can see own OR admin can see all
CREATE POLICY "user_subscriptions_select_policy" ON user_subscriptions
FOR SELECT USING (
  (SELECT auth.uid()) = user_id OR 
  EXISTS (SELECT 1 FROM admin_users WHERE admin_users.user_id = (SELECT auth.uid()))
);

-- Consolidated INSERT policy: user can insert own OR admin can insert any
CREATE POLICY "user_subscriptions_insert_policy" ON user_subscriptions
FOR INSERT WITH CHECK (
  (SELECT auth.uid()) = user_id OR 
  EXISTS (SELECT 1 FROM admin_users WHERE admin_users.user_id = (SELECT auth.uid()))
);

-- Admin UPDATE/DELETE policy
CREATE POLICY "user_subscriptions_admin_modify_policy" ON user_subscriptions
FOR UPDATE USING (
  EXISTS (SELECT 1 FROM admin_users WHERE admin_users.user_id = (SELECT auth.uid()))
);

CREATE POLICY "user_subscriptions_admin_delete_policy" ON user_subscriptions
FOR DELETE USING (
  EXISTS (SELECT 1 FROM admin_users WHERE admin_users.user_id = (SELECT auth.uid()))
);

-- ============================================
-- Fix 2: bottle_splits
-- Issues: bottle_splits_host_policy (ALL) and bottle_splits_public_view (SELECT) overlap
-- ============================================

DROP POLICY IF EXISTS "bottle_splits_host_policy" ON bottle_splits;
DROP POLICY IF EXISTS "bottle_splits_public_view" ON bottle_splits;

-- Consolidated SELECT: host can see all own, everyone can see active
CREATE POLICY "bottle_splits_select_policy" ON bottle_splits
FOR SELECT USING (
  (SELECT auth.uid()) = host_id OR is_active = true
);

-- Host-only for INSERT/UPDATE/DELETE
CREATE POLICY "bottle_splits_host_insert_policy" ON bottle_splits
FOR INSERT WITH CHECK ((SELECT auth.uid()) = host_id);

CREATE POLICY "bottle_splits_host_update_policy" ON bottle_splits
FOR UPDATE USING ((SELECT auth.uid()) = host_id);

CREATE POLICY "bottle_splits_host_delete_policy" ON bottle_splits
FOR DELETE USING ((SELECT auth.uid()) = host_id);

-- ============================================
-- Fix 3: bottles
-- Issues: bottles_owner_policy (ALL) and bottles_session_select_policy (SELECT) overlap
-- Strategy: Just keep owner policy because the session_select_policy doesn't exist in schema
-- ============================================

DROP POLICY IF EXISTS "bottles_owner_policy" ON bottles;
DROP POLICY IF EXISTS "bottles_session_select_policy" ON bottles;

-- Owner-only policy for all operations (simple and performant)
CREATE POLICY "bottles_policy" ON bottles
FOR ALL USING ((SELECT auth.uid()) = user_id);

-- ============================================
-- Fix 4: buddy_invites
-- Issues: buddy_invites_creator_policy (ALL) and buddy_invites_redeem_policy (SELECT) overlap
-- ============================================

DROP POLICY IF EXISTS "buddy_invites_creator_policy" ON buddy_invites;
DROP POLICY IF EXISTS "buddy_invites_redeem_policy" ON buddy_invites;

-- Consolidated SELECT: creator can see own, anyone can see non-expired (for redemption)
CREATE POLICY "buddy_invites_select_policy" ON buddy_invites
FOR SELECT USING (
  (SELECT auth.uid()) = creator_id OR expires_at > now()
);

-- Creator-only for INSERT/UPDATE/DELETE
CREATE POLICY "buddy_invites_creator_insert_policy" ON buddy_invites
FOR INSERT WITH CHECK ((SELECT auth.uid()) = creator_id);

CREATE POLICY "buddy_invites_creator_update_policy" ON buddy_invites
FOR UPDATE USING ((SELECT auth.uid()) = creator_id);

CREATE POLICY "buddy_invites_creator_delete_policy" ON buddy_invites
FOR DELETE USING ((SELECT auth.uid()) = creator_id);

-- ============================================
-- Fix 5: global_products
-- Issues: "Enable Admin Insert/Update" (ALL) and "Enable Read Access for all users" (SELECT) overlap
-- ============================================

DROP POLICY IF EXISTS "Enable Admin Insert/Update" ON global_products;
DROP POLICY IF EXISTS "Enable Read Access for all users" ON global_products;

-- Everyone can SELECT
CREATE POLICY "global_products_select_policy" ON global_products
FOR SELECT USING (true);

-- Admin-only for INSERT/UPDATE/DELETE
CREATE POLICY "global_products_admin_insert_policy" ON global_products
FOR INSERT WITH CHECK (
  EXISTS (SELECT 1 FROM admin_users WHERE admin_users.user_id = (SELECT auth.uid()))
);

CREATE POLICY "global_products_admin_update_policy" ON global_products
FOR UPDATE USING (
  EXISTS (SELECT 1 FROM admin_users WHERE admin_users.user_id = (SELECT auth.uid()))
);

CREATE POLICY "global_products_admin_delete_policy" ON global_products
FOR DELETE USING (
  EXISTS (SELECT 1 FROM admin_users WHERE admin_users.user_id = (SELECT auth.uid()))
);

-- ============================================
-- Fix 6: session_participants
-- Issues: session_participants_manage_policy and session_participants_read_policy overlap
-- ============================================

DROP POLICY IF EXISTS "session_participants_owner_policy" ON session_participants;
DROP POLICY IF EXISTS "session_participants_manage_policy" ON session_participants;
DROP POLICY IF EXISTS "session_participants_read_policy" ON session_participants;

-- Single unified policy: owner can do all
CREATE POLICY "session_participants_policy" ON session_participants
FOR ALL USING ((SELECT auth.uid()) = user_id);

-- ============================================
-- Fix 7: split_participants
-- Issues: Multiple overlapping policies for different actions
-- ============================================

DROP POLICY IF EXISTS "split_participants_own_policy" ON split_participants;
DROP POLICY IF EXISTS "split_participants_host_policy" ON split_participants;
DROP POLICY IF EXISTS "split_participants_public_view" ON split_participants;

-- Consolidated SELECT: own participation OR host of split OR public active split
CREATE POLICY "split_participants_select_policy" ON split_participants
FOR SELECT USING (
  (SELECT auth.uid()) = user_id OR 
  split_id IN (SELECT id FROM bottle_splits WHERE host_id = (SELECT auth.uid())) OR
  split_id IN (SELECT id FROM bottle_splits WHERE is_active = true)
);

-- INSERT: own participation OR host of split
CREATE POLICY "split_participants_insert_policy" ON split_participants
FOR INSERT WITH CHECK (
  (SELECT auth.uid()) = user_id OR 
  split_id IN (SELECT id FROM bottle_splits WHERE host_id = (SELECT auth.uid()))
);

-- UPDATE: own participation OR host of split
CREATE POLICY "split_participants_update_policy" ON split_participants
FOR UPDATE USING (
  (SELECT auth.uid()) = user_id OR 
  split_id IN (SELECT id FROM bottle_splits WHERE host_id = (SELECT auth.uid()))
);

-- DELETE: own participation OR host of split
CREATE POLICY "split_participants_delete_policy" ON split_participants
FOR DELETE USING (
  (SELECT auth.uid()) = user_id OR 
  split_id IN (SELECT id FROM bottle_splits WHERE host_id = (SELECT auth.uid()))
);

-- ============================================
-- Fix 8: subscription_plans
-- Issues: subscription_plans_admin_policy (ALL) and subscription_plans_select_policy (SELECT) overlap
-- ============================================

DROP POLICY IF EXISTS "subscription_plans_select_policy" ON subscription_plans;
DROP POLICY IF EXISTS "subscription_plans_admin_policy" ON subscription_plans;

-- Consolidated SELECT: active plans OR admin can see all
CREATE POLICY "subscription_plans_select_policy" ON subscription_plans
FOR SELECT USING (
  is_active = true OR 
  EXISTS (SELECT 1 FROM admin_users WHERE admin_users.user_id = (SELECT auth.uid()))
);

-- Admin-only for INSERT/UPDATE/DELETE
CREATE POLICY "subscription_plans_admin_insert_policy" ON subscription_plans
FOR INSERT WITH CHECK (
  EXISTS (SELECT 1 FROM admin_users WHERE admin_users.user_id = (SELECT auth.uid()))
);

CREATE POLICY "subscription_plans_admin_update_policy" ON subscription_plans
FOR UPDATE USING (
  EXISTS (SELECT 1 FROM admin_users WHERE admin_users.user_id = (SELECT auth.uid()))
);

CREATE POLICY "subscription_plans_admin_delete_policy" ON subscription_plans
FOR DELETE USING (
  EXISTS (SELECT 1 FROM admin_users WHERE admin_users.user_id = (SELECT auth.uid()))
);

-- ============================================
-- Fix 9: tasting_sessions
-- Issues: sessions_access_policy (ALL) and sessions_modification_policy overlap
-- Strategy: Keep it simple - owner-only for modifications, no complex buddy joins
-- ============================================

DROP POLICY IF EXISTS "sessions_access_policy" ON tasting_sessions;
DROP POLICY IF EXISTS "sessions_modification_policy" ON tasting_sessions;

-- Owner-only policy for all operations
CREATE POLICY "tasting_sessions_policy" ON tasting_sessions
FOR ALL USING ((SELECT auth.uid()) = user_id);

-- ============================================
-- Fix 10: tastings
-- Issues: tastings_modify_policy (ALL) and tastings_select_policy (SELECT) overlap
-- Strategy: Keep it simple - owner-only for all operations
-- ============================================

DROP POLICY IF EXISTS "tastings_select_policy" ON tastings;
DROP POLICY IF EXISTS "tastings_modify_policy" ON tastings;

-- Owner-only policy for all operations
CREATE POLICY "tastings_policy" ON tastings
FOR ALL USING ((SELECT auth.uid()) = user_id);

-- ============================================
-- Verification Query (run after migration)
-- ============================================
-- SELECT tablename, policyname, roles, cmd 
-- FROM pg_policies 
-- WHERE schemaname = 'public'
-- ORDER BY tablename, cmd, roles;