-- Migration 00002: Row Level Security (RLS) Policies and Helper Functions

-- 1. Helper Functions
CREATE OR REPLACE FUNCTION public.is_household_member(_household_id UUID)
RETURNS BOOLEAN
LANGUAGE sql
SECURITY DEFINER
SET search_path = public
STABLE
AS $$
  SELECT EXISTS (
    SELECT 1 
    FROM public.household_members
    WHERE household_id = _household_id
      AND user_id = auth.uid()
  );
$$;

CREATE OR REPLACE FUNCTION public.has_household_role(_household_id UUID, _allowed_roles public.household_role[])
RETURNS BOOLEAN
LANGUAGE sql
SECURITY DEFINER
SET search_path = public
STABLE
AS $$
  SELECT EXISTS (
    SELECT 1 
    FROM public.household_members
    WHERE household_id = _household_id
      AND user_id = auth.uid()
      AND role = ANY(_allowed_roles)
  );
$$;

-- Enable RLS on all tables
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.households ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.household_members ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.categories ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.locations ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.units ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.products ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.inventory_batches ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.stock_movements ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.shopping_lists ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.shopping_list_items ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.purchases ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.purchase_items ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.price_observations ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.budgets ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.inventory_sessions ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.inventory_session_items ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.dietary_preferences ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.recipes ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.meal_plans ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.ai_settings ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.ai_request_logs ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.ai_pending_actions ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.rate_limit_events ENABLE ROW LEVEL SECURITY;

-- 2. Policies for profiles
CREATE POLICY "Users can view all profiles in their households"
    ON public.profiles FOR SELECT
    USING (
        id = auth.uid() OR
        EXISTS (
            SELECT 1 FROM public.household_members m1
            JOIN public.household_members m2 ON m1.household_id = m2.household_id
            WHERE m1.user_id = auth.uid() AND m2.user_id = public.profiles.id
        )
    );

CREATE POLICY "Users can update their own profile"
    ON public.profiles FOR UPDATE
    USING (id = auth.uid())
    WITH CHECK (id = auth.uid());

CREATE POLICY "Users can insert their own profile"
    ON public.profiles FOR INSERT
    WITH CHECK (id = auth.uid());

-- 3. Policies for households
CREATE POLICY "Members can view their households"
    ON public.households FOR SELECT
    USING (public.is_household_member(id));

CREATE POLICY "Authenticated users can create households"
    ON public.households FOR INSERT
    WITH CHECK (owner_id = auth.uid());

CREATE POLICY "OWNER and ADMIN can update household"
    ON public.households FOR UPDATE
    USING (public.has_household_role(id, ARRAY['OWNER', 'ADMIN']::public.household_role[]));

CREATE POLICY "Only OWNER can delete household"
    ON public.households FOR DELETE
    USING (owner_id = auth.uid());

-- 4. Policies for household_members
CREATE POLICY "Members can view household members"
    ON public.household_members FOR SELECT
    USING (public.is_household_member(household_id));

CREATE POLICY "OWNER and ADMIN can add household members"
    ON public.household_members FOR INSERT
    WITH CHECK (public.has_household_role(household_id, ARRAY['OWNER', 'ADMIN']::public.household_role[]));

CREATE POLICY "OWNER and ADMIN can update member roles"
    ON public.household_members FOR UPDATE
    USING (public.has_household_role(household_id, ARRAY['OWNER', 'ADMIN']::public.household_role[]));

CREATE POLICY "OWNER and ADMIN can remove members"
    ON public.household_members FOR DELETE
    USING (public.has_household_role(household_id, ARRAY['OWNER', 'ADMIN']::public.household_role[]) OR user_id = auth.uid());

-- Helper Macro macro-like pattern for tables scoped by household_id
-- Categories, Locations, Units, Products, Inventory Batches, Stock Movements, Shopping Lists, Purchases, Price Observations, Budgets, Inventory Sessions, Dietary Preferences, Recipes, Meal Plans, AI Settings, AI Pending Actions

-- Categories
CREATE POLICY "Members can view categories" ON public.categories FOR SELECT USING (public.is_household_member(household_id));
CREATE POLICY "MEMBER, ADMIN, OWNER can insert categories" ON public.categories FOR INSERT WITH CHECK (public.has_household_role(household_id, ARRAY['MEMBER', 'ADMIN', 'OWNER']::public.household_role[]));
CREATE POLICY "MEMBER, ADMIN, OWNER can update categories" ON public.categories FOR UPDATE USING (public.has_household_role(household_id, ARRAY['MEMBER', 'ADMIN', 'OWNER']::public.household_role[]));
CREATE POLICY "ADMIN and OWNER can delete categories" ON public.categories FOR DELETE USING (public.has_household_role(household_id, ARRAY['ADMIN', 'OWNER']::public.household_role[]));

-- Locations
CREATE POLICY "Members can view locations" ON public.locations FOR SELECT USING (public.is_household_member(household_id));
CREATE POLICY "MEMBER, ADMIN, OWNER can insert locations" ON public.locations FOR INSERT WITH CHECK (public.has_household_role(household_id, ARRAY['MEMBER', 'ADMIN', 'OWNER']::public.household_role[]));
CREATE POLICY "MEMBER, ADMIN, OWNER can update locations" ON public.locations FOR UPDATE USING (public.has_household_role(household_id, ARRAY['MEMBER', 'ADMIN', 'OWNER']::public.household_role[]));
CREATE POLICY "ADMIN and OWNER can delete locations" ON public.locations FOR DELETE USING (public.has_household_role(household_id, ARRAY['ADMIN', 'OWNER']::public.household_role[]));

-- Units
CREATE POLICY "Members can view units" ON public.units FOR SELECT USING (household_id IS NULL OR public.is_household_member(household_id));
CREATE POLICY "MEMBER, ADMIN, OWNER can insert units" ON public.units FOR INSERT WITH CHECK (household_id IS NOT NULL AND public.has_household_role(household_id, ARRAY['MEMBER', 'ADMIN', 'OWNER']::public.household_role[]));
CREATE POLICY "MEMBER, ADMIN, OWNER can update units" ON public.units FOR UPDATE USING (household_id IS NOT NULL AND public.has_household_role(household_id, ARRAY['MEMBER', 'ADMIN', 'OWNER']::public.household_role[]));

-- Products
CREATE POLICY "Members can view products" ON public.products FOR SELECT USING (public.is_household_member(household_id));
CREATE POLICY "MEMBER, ADMIN, OWNER can insert products" ON public.products FOR INSERT WITH CHECK (public.has_household_role(household_id, ARRAY['MEMBER', 'ADMIN', 'OWNER']::public.household_role[]));
CREATE POLICY "MEMBER, ADMIN, OWNER can update products" ON public.products FOR UPDATE USING (public.has_household_role(household_id, ARRAY['MEMBER', 'ADMIN', 'OWNER']::public.household_role[]));
CREATE POLICY "ADMIN and OWNER can delete products" ON public.products FOR DELETE USING (public.has_household_role(household_id, ARRAY['ADMIN', 'OWNER']::public.household_role[]));

-- Inventory Batches
CREATE POLICY "Members can view inventory batches" ON public.inventory_batches FOR SELECT USING (public.is_household_member(household_id));
CREATE POLICY "MEMBER, ADMIN, OWNER can modify inventory batches" ON public.inventory_batches FOR ALL USING (public.has_household_role(household_id, ARRAY['MEMBER', 'ADMIN', 'OWNER']::public.household_role[]));

-- Stock Movements
CREATE POLICY "Members can view stock movements" ON public.stock_movements FOR SELECT USING (public.is_household_member(household_id));
CREATE POLICY "MEMBER, ADMIN, OWNER can insert stock movements" ON public.stock_movements FOR INSERT WITH CHECK (public.has_household_role(household_id, ARRAY['MEMBER', 'ADMIN', 'OWNER']::public.household_role[]));

-- Shopping Lists
CREATE POLICY "Members can view shopping lists" ON public.shopping_lists FOR SELECT USING (public.is_household_member(household_id));
CREATE POLICY "MEMBER, ADMIN, OWNER can modify shopping lists" ON public.shopping_lists FOR ALL USING (public.has_household_role(household_id, ARRAY['MEMBER', 'ADMIN', 'OWNER']::public.household_role[]));

-- Shopping List Items
CREATE POLICY "Members can view shopping list items" ON public.shopping_list_items FOR SELECT USING (
    EXISTS (SELECT 1 FROM public.shopping_lists WHERE id = shopping_list_id AND public.is_household_member(household_id))
);
CREATE POLICY "MEMBER, ADMIN, OWNER can modify shopping list items" ON public.shopping_list_items FOR ALL USING (
    EXISTS (SELECT 1 FROM public.shopping_lists WHERE id = shopping_list_id AND public.has_household_role(household_id, ARRAY['MEMBER', 'ADMIN', 'OWNER']::public.household_role[]))
);

-- Purchases & Purchase Items
CREATE POLICY "Members can view purchases" ON public.purchases FOR SELECT USING (public.is_household_member(household_id));
CREATE POLICY "MEMBER, ADMIN, OWNER can modify purchases" ON public.purchases FOR ALL USING (public.has_household_role(household_id, ARRAY['MEMBER', 'ADMIN', 'OWNER']::public.household_role[]));

CREATE POLICY "Members can view purchase items" ON public.purchase_items FOR SELECT USING (
    EXISTS (SELECT 1 FROM public.purchases WHERE id = purchase_id AND public.is_household_member(household_id))
);
CREATE POLICY "MEMBER, ADMIN, OWNER can modify purchase items" ON public.purchase_items FOR ALL USING (
    EXISTS (SELECT 1 FROM public.purchases WHERE id = purchase_id AND public.has_household_role(household_id, ARRAY['MEMBER', 'ADMIN', 'OWNER']::public.household_role[]))
);

-- Price Observations
CREATE POLICY "Members can view price observations" ON public.price_observations FOR SELECT USING (public.is_household_member(household_id));
CREATE POLICY "MEMBER, ADMIN, OWNER can modify price observations" ON public.price_observations FOR ALL USING (public.has_household_role(household_id, ARRAY['MEMBER', 'ADMIN', 'OWNER']::public.household_role[]));

-- Budgets
CREATE POLICY "Members can view budgets" ON public.budgets FOR SELECT USING (public.is_household_member(household_id));
CREATE POLICY "ADMIN and OWNER can modify budgets" ON public.budgets FOR ALL USING (public.has_household_role(household_id, ARRAY['ADMIN', 'OWNER']::public.household_role[]));

-- Inventory Sessions & Session Items
CREATE POLICY "Members can view inventory sessions" ON public.inventory_sessions FOR SELECT USING (public.is_household_member(household_id));
CREATE POLICY "MEMBER, ADMIN, OWNER can modify inventory sessions" ON public.inventory_sessions FOR ALL USING (public.has_household_role(household_id, ARRAY['MEMBER', 'ADMIN', 'OWNER']::public.household_role[]));

CREATE POLICY "Members can view inventory session items" ON public.inventory_session_items FOR SELECT USING (
    EXISTS (SELECT 1 FROM public.inventory_sessions WHERE id = session_id AND public.is_household_member(household_id))
);
CREATE POLICY "MEMBER, ADMIN, OWNER can modify inventory session items" ON public.inventory_session_items FOR ALL USING (
    EXISTS (SELECT 1 FROM public.inventory_sessions WHERE id = session_id AND public.has_household_role(household_id, ARRAY['MEMBER', 'ADMIN', 'OWNER']::public.household_role[]))
);

-- Dietary Preferences, Recipes, Meal Plans, AI Settings, AI Pending Actions
CREATE POLICY "Members can view dietary preferences" ON public.dietary_preferences FOR SELECT USING (public.is_household_member(household_id));
CREATE POLICY "MEMBER, ADMIN, OWNER can update dietary preferences" ON public.dietary_preferences FOR ALL USING (public.has_household_role(household_id, ARRAY['MEMBER', 'ADMIN', 'OWNER']::public.household_role[]));

CREATE POLICY "Members can view recipes" ON public.recipes FOR SELECT USING (public.is_household_member(household_id));
CREATE POLICY "MEMBER, ADMIN, OWNER can modify recipes" ON public.recipes FOR ALL USING (public.has_household_role(household_id, ARRAY['MEMBER', 'ADMIN', 'OWNER']::public.household_role[]));

CREATE POLICY "Members can view meal plans" ON public.meal_plans FOR SELECT USING (public.is_household_member(household_id));
CREATE POLICY "MEMBER, ADMIN, OWNER can modify meal plans" ON public.meal_plans FOR ALL USING (public.has_household_role(household_id, ARRAY['MEMBER', 'ADMIN', 'OWNER']::public.household_role[]));

CREATE POLICY "Members can view AI settings" ON public.ai_settings FOR SELECT USING (public.is_household_member(household_id));
CREATE POLICY "ADMIN and OWNER can modify AI settings" ON public.ai_settings FOR ALL USING (public.has_household_role(household_id, ARRAY['ADMIN', 'OWNER']::public.household_role[]));

CREATE POLICY "Members can view AI logs" ON public.ai_request_logs FOR SELECT USING (public.is_household_member(household_id));
CREATE POLICY "System can insert AI logs" ON public.ai_request_logs FOR INSERT WITH CHECK (public.is_household_member(household_id));

CREATE POLICY "Members can view pending actions" ON public.ai_pending_actions FOR SELECT USING (public.is_household_member(household_id));
CREATE POLICY "MEMBER, ADMIN, OWNER can modify pending actions" ON public.ai_pending_actions FOR ALL USING (public.has_household_role(household_id, ARRAY['MEMBER', 'ADMIN', 'OWNER']::public.household_role[]));

CREATE POLICY "Users can manage their rate limit events" ON public.rate_limit_events FOR ALL USING (user_id = auth.uid());