002_basic_rls.sql

-- ============================================
-- ENABLE RLS on all tables
-- ============================================
ALTER TABLE organizations ENABLE ROW LEVEL SECURITY;
ALTER TABLE organization_members ENABLE ROW LEVEL SECURITY;
ALTER TABLE events ENABLE ROW LEVEL SECURITY;
ALTER TABLE generated_assets ENABLE ROW LEVEL SECURITY;
ALTER TABLE flyers ENABLE ROW LEVEL SECURITY;

-- ============================================
-- RLS POLICIES: ORGANIZATIONS
-- ============================================

-- Users can view organizations they're members of
CREATE POLICY "Users can view their organizations"
ON organizations FOR SELECT
USING (
  id IN (
    SELECT org_id FROM organization_members 
    WHERE user_id = auth.uid()
  )
);

-- Users can create organizations (they become owner automatically via trigger)
CREATE POLICY "Users can create organizations"
ON organizations FOR INSERT
WITH CHECK (created_by = auth.uid());

-- Only owners can update organizations
CREATE POLICY "Owners can update organizations"
ON organizations FOR UPDATE
USING (
  id IN (
    SELECT org_id FROM organization_members 
    WHERE user_id = auth.uid() AND role = 'owner'
  )
);

-- Only owners can delete organizations
CREATE POLICY "Owners can delete organizations"
ON organizations FOR DELETE
USING (
  id IN (
    SELECT org_id FROM organization_members 
    WHERE user_id = auth.uid() AND role = 'owner'
  )
);

-- ============================================
-- RLS POLICIES: ORGANIZATION MEMBERS
-- ============================================

-- Users can view members of their organizations
CREATE POLICY "Users can view org members"
ON organization_members FOR SELECT
USING (
  org_id IN (
    SELECT org_id FROM organization_members 
    WHERE user_id = auth.uid()
  )
);

-- Owners and admins can add members
CREATE POLICY "Admins can add members"
ON organization_members FOR INSERT
WITH CHECK (
  org_id IN (
    SELECT org_id FROM organization_members 
    WHERE user_id = auth.uid() AND role IN ('owner', 'admin')
  )
);

-- Owners and admins can remove members
CREATE POLICY "Admins can remove members"
ON organization_members FOR DELETE
USING (
  org_id IN (
    SELECT org_id FROM organization_members 
    WHERE user_id = auth.uid() AND role IN ('owner', 'admin')
  )
);

-- ============================================
-- RLS POLICIES: EVENTS
-- ============================================

-- Users can view events from their organizations
CREATE POLICY "Users can view org events"
ON events FOR SELECT
USING (
  org_id IN (
    SELECT org_id FROM organization_members 
    WHERE user_id = auth.uid()
  )
);

-- Users can create events in their organizations
CREATE POLICY "Users can create org events"
ON events FOR INSERT
WITH CHECK (
  org_id IN (
    SELECT org_id FROM organization_members 
    WHERE user_id = auth.uid()
  )
  AND created_by = auth.uid()
);

-- Users can update events in their organizations
CREATE POLICY "Users can update org events"
ON events FOR UPDATE
USING (
  org_id IN (
    SELECT org_id FROM organization_members 
    WHERE user_id = auth.uid()
  )
);

-- Users can delete events they created
CREATE POLICY "Users can delete their events"
ON events FOR DELETE
USING (
  created_by = auth.uid()
  OR org_id IN (
    SELECT org_id FROM organization_members 
    WHERE user_id = auth.uid() AND role IN ('owner', 'admin')
  )
);

-- ============================================
-- RLS POLICIES: GENERATED ASSETS
-- ============================================

-- Users can view assets for events in their orgs
CREATE POLICY "Users can view org event assets"
ON generated_assets FOR SELECT
USING (
  event_id IN (
    SELECT id FROM events
    WHERE org_id IN (
      SELECT org_id FROM organization_members 
      WHERE user_id = auth.uid()
    )
  )
);

-- Users can create assets for events in their orgs
CREATE POLICY "Users can create org event assets"
ON generated_assets FOR INSERT
WITH CHECK (
  event_id IN (
    SELECT id FROM events
    WHERE org_id IN (
      SELECT org_id FROM organization_members 
      WHERE user_id = auth.uid()
    )
  )
);

-- Users can delete assets for events in their orgs
CREATE POLICY "Users can delete org event assets"
ON generated_assets FOR DELETE
USING (
  event_id IN (
    SELECT id FROM events
    WHERE org_id IN (
      SELECT org_id FROM organization_members 
      WHERE user_id = auth.uid()
    )
  )
);

-- ============================================
-- RLS POLICIES: FLYERS
-- ============================================

-- Users can view flyers for events in their orgs
CREATE POLICY "Users can view org event flyers"
ON flyers FOR SELECT
USING (
  event_id IN (
    SELECT id FROM events
    WHERE org_id IN (
      SELECT org_id FROM organization_members 
      WHERE user_id = auth.uid()
    )
  )
);

-- Users can create flyers for events in their orgs
CREATE POLICY "Users can create org event flyers"
ON flyers FOR INSERT
WITH CHECK (
  event_id IN (
    SELECT id FROM events
    WHERE org_id IN (
      SELECT org_id FROM organization_members 
      WHERE user_id = auth.uid()
    )
  )
);

-- Users can delete flyers for events in their orgs
CREATE POLICY "Users can delete org event flyers"
ON flyers FOR DELETE
USING (
  event_id IN (
    SELECT id FROM events
    WHERE org_id IN (
      SELECT org_id FROM organization_members 
      WHERE user_id = auth.uid()
    )
  )
);