-- RLS Policies for Customer Data Isolation
-- This file creates Row Level Security policies to ensure users can only access
-- data for customer IDs they are linked to via the customer_user table

-- ============================================================================
-- PHASE 1: Enable RLS on Core Tables
-- ============================================================================

-- Enable RLS on tables with direct customer_id field
ALTER TABLE task ENABLE ROW LEVEL SECURITY;
ALTER TABLE promos_linked_users ENABLE ROW LEVEL SECURITY;
ALTER TABLE kaching_linked_users ENABLE ROW LEVEL SECURITY;
ALTER TABLE survey_store_link ENABLE ROW LEVEL SECURITY;
ALTER TABLE store_customer_link ENABLE ROW LEVEL SECURITY;

-- Enable RLS on tables linked via junction tables
ALTER TABLE store ENABLE ROW LEVEL SECURITY;
ALTER TABLE promos ENABLE ROW LEVEL SECURITY;
ALTER TABLE kaching ENABLE ROW LEVEL SECURITY;
ALTER TABLE survey_responses ENABLE ROW LEVEL SECURITY;
ALTER TABLE survey ENABLE ROW LEVEL SECURITY;

-- ============================================================================
-- TO DISABLE RLS (if needed):
-- ============================================================================
-- ALTER TABLE task DISABLE ROW LEVEL SECURITY;
-- ALTER TABLE promos_linked_users DISABLE ROW LEVEL SECURITY;
-- ALTER TABLE kaching_linked_users DISABLE ROW LEVEL SECURITY;
-- ALTER TABLE survey_store_link DISABLE ROW LEVEL SECURITY;
-- ALTER TABLE store_customer_link DISABLE ROW LEVEL SECURITY;
-- ALTER TABLE store DISABLE ROW LEVEL SECURITY;
-- ALTER TABLE promos DISABLE ROW LEVEL SECURITY;
-- ALTER TABLE kaching DISABLE ROW LEVEL SECURITY;
-- ALTER TABLE survey_responses DISABLE ROW LEVEL SECURITY;
-- ALTER TABLE survey DISABLE ROW LEVEL SECURITY;

-- ============================================================================
-- PHASE 2: Create RLS Policies for Tables with Direct customer_id
-- ============================================================================

-- Task table policies
-- Users can only see tasks for customers they are linked to
CREATE POLICY "Users can view tasks for their linked customers"
ON task FOR SELECT
USING (
  customer_id IN (
    SELECT customer_id 
    FROM customer_user 
    WHERE user_id = auth.uid()
  )
);

-- Users can only insert tasks for customers they are linked to
CREATE POLICY "Users can insert tasks for their linked customers"
ON task FOR INSERT
WITH CHECK (
  customer_id IN (
    SELECT customer_id 
    FROM customer_user 
    WHERE user_id = auth.uid()
  )
);

-- Users can only update tasks for customers they are linked to
CREATE POLICY "Users can update tasks for their linked customers"
ON task FOR UPDATE
USING (
  customer_id IN (
    SELECT customer_id 
    FROM customer_user 
    WHERE user_id = auth.uid()
  )
)
WITH CHECK (
  customer_id IN (
    SELECT customer_id 
    FROM customer_user 
    WHERE user_id = auth.uid()
  )
);

-- Users can only delete tasks for customers they are linked to
CREATE POLICY "Users can delete tasks for their linked customers"
ON task FOR DELETE
USING (
  customer_id IN (
    SELECT customer_id 
    FROM customer_user 
    WHERE user_id = auth.uid()
  )
);

-- Promos linked users table policies
CREATE POLICY "Users can view promos_linked_users for their linked customers"
ON promos_linked_users FOR SELECT
USING (
  customer_id IN (
    SELECT customer_id 
    FROM customer_user WHERE user_id = auth.uid()
  )
);

CREATE POLICY "Users can insert promos_linked_users for their linked customers"
ON promos_linked_users FOR INSERT
WITH CHECK (
  customer_id IN (
    SELECT customer_id 
    FROM customer_user WHERE user_id = auth.uid()
  )
);

CREATE POLICY "Users can update promos_linked_users for their linked customers"
ON promos_linked_users FOR UPDATE
USING (
  customer_id IN (
    SELECT customer_id 
    FROM customer_user WHERE user_id = auth.uid()
  )
)
WITH CHECK (
  customer_id IN (
    SELECT customer_id 
    FROM customer_user WHERE user_id = auth.uid()
  )
);

CREATE POLICY "Users can delete promos_linked_users for their linked customers"
ON promos_linked_users FOR DELETE
USING (
  customer_id IN (
    SELECT customer_id 
    FROM customer_user WHERE user_id = auth.uid()
  )
);

-- Kaching linked users table policies
CREATE POLICY "Users can view kaching_linked_users for their linked customers"
ON kaching_linked_users FOR SELECT
USING (
  customer_id IN (
    SELECT customer_id 
    FROM customer_user WHERE user_id = auth.uid()
  )
);

CREATE POLICY "Users can insert kaching_linked_users for their linked customers"
ON kaching_linked_users FOR INSERT
WITH CHECK (
  customer_id IN (
    SELECT customer_id 
    FROM customer_user WHERE user_id = auth.uid()
  )
);

CREATE POLICY "Users can update kaching_linked_users for their linked customers"
ON kaching_linked_users FOR UPDATE
USING (
  customer_id IN (
    SELECT customer_id 
    FROM customer_user WHERE user_id = auth.uid()
  )
)
WITH CHECK (
  customer_id IN (
    SELECT customer_id 
    FROM customer_user WHERE user_id = auth.uid()
  )
);

CREATE POLICY "Users can delete kaching_linked_users for their linked customers"
ON kaching_linked_users FOR DELETE
USING (
  customer_id IN (
    SELECT customer_id 
    FROM customer_user WHERE user_id = auth.uid()
  )
);

-- Survey store link table policies
CREATE POLICY "Users can view survey_store_link for their linked customers"
ON survey_store_link FOR SELECT
USING (
  customer_id IN (
    SELECT customer_id 
    FROM customer_user WHERE user_id = auth.uid()
  )
);

CREATE POLICY "Users can insert survey_store_link for their linked customers"
ON survey_store_link FOR INSERT
WITH CHECK (
  customer_id IN (
    SELECT customer_id 
    FROM customer_user WHERE user_id = auth.uid()
  )
);

CREATE POLICY "Users can update survey_store_link for their linked customers"
ON survey_store_link FOR UPDATE
USING (
  customer_id IN (
    SELECT customer_id 
    FROM customer_user WHERE user_id = auth.uid()
  )
)
WITH CHECK (
  customer_id IN (
    SELECT customer_id 
    FROM customer_user WHERE user_id = auth.uid()
  )
);

CREATE POLICY "Users can delete survey_store_link for their linked customers"
ON survey_store_link FOR DELETE
USING (
  customer_id IN (
    SELECT customer_id 
    FROM customer_user WHERE user_id = auth.uid()
  )
);

-- Store customer link table policies
CREATE POLICY "Users can view store_customer_link for their linked customers"
ON store_customer_link FOR SELECT
USING (
  customer_id IN (
    SELECT customer_id 
    FROM customer_user WHERE user_id = auth.uid()
  )
);

CREATE POLICY "Users can insert store_customer_link for their linked customers"
ON store_customer_link FOR INSERT
WITH CHECK (
  customer_id IN (
    SELECT customer_id 
    FROM customer_user WHERE user_id = auth.uid()
  )
);

CREATE POLICY "Users can update store_customer_link for their linked customers"
ON store_customer_link FOR UPDATE
USING (
  customer_id IN (
    SELECT customer_id 
    FROM customer_user WHERE user_id = auth.uid()
  )
)
WITH CHECK (
  customer_id IN (
    SELECT customer_id 
    FROM customer_user WHERE user_id = auth.uid()
  )
);

CREATE POLICY "Users can delete store_customer_link for their linked customers"
ON store_customer_link FOR DELETE
USING (
  customer_id IN (
    SELECT customer_id 
    FROM customer_user WHERE user_id = auth.uid()
  )
);

-- ============================================================================
-- PHASE 3: Create RLS Policies for Tables Linked via Junction Tables
-- ============================================================================

-- Store table policies (linked via store_customer_link)
CREATE POLICY "Users can view stores for their linked customers"
ON store FOR SELECT
USING (
  EXISTS (
    SELECT 1
    FROM store_customer_link scl
    WHERE scl.store_code = store.store_code
    AND scl.customer_id IN (
      SELECT customer_id 
      FROM customer_user WHERE user_id = auth.uid()
    )
  )
);

CREATE POLICY "Users can insert stores for their linked customers"
ON store FOR INSERT
WITH CHECK (
  EXISTS (
    SELECT 1
    FROM store_customer_link scl
    WHERE scl.store_code = store.store_code
    AND scl.customer_id IN (
      SELECT customer_id 
      FROM customer_user WHERE user_id = auth.uid()
    )
  )
);

CREATE POLICY "Users can update stores for their linked customers"
ON store FOR UPDATE
USING (
  EXISTS (
    SELECT 1
    FROM store_customer_link scl
    WHERE scl.store_code = store.store_code
    AND scl.customer_id IN (
      SELECT customer_id 
      FROM customer_user WHERE user_id = auth.uid()
    )
  )
)
WITH CHECK (
  EXISTS (
    SELECT 1
    FROM store_customer_link scl
    WHERE scl.store_code = store.store_code
    AND scl.customer_id IN (
      SELECT customer_id 
      FROM customer_user WHERE user_id = auth.uid()
    )
  )
);

CREATE POLICY "Users can delete stores for their linked customers"
ON store FOR DELETE
USING (
  EXISTS (
    SELECT 1
    FROM store_customer_link scl
    WHERE scl.store_code = store.store_code
    AND scl.customer_id IN (
      SELECT customer_id 
      FROM customer_user WHERE user_id = auth.uid()
    )
  )
);

-- Promos table policies (linked via promos_linked_users)
CREATE POLICY "Users can view promos for their linked customers"
ON promos FOR SELECT
USING (
  EXISTS (
    SELECT 1
    FROM promos_linked_users plu
    WHERE plu.promo_id = promos.id
    AND plu.customer_id IN (
      SELECT customer_id 
      FROM customer_user WHERE user_id = auth.uid()
    )
  )
);

CREATE POLICY "Users can insert promos for their linked customers"
ON promos FOR INSERT
WITH CHECK (
  EXISTS (
    SELECT 1
    FROM promos_linked_users plu
    WHERE plu.promo_id = promos.id
    AND plu.customer_id IN (
      SELECT customer_id 
      FROM customer_user WHERE user_id = auth.uid()
    )
  )
);

CREATE POLICY "Users can update promos for their linked customers"
ON promos FOR UPDATE
USING (
  EXISTS (
    SELECT 1
    FROM promos_linked_users plu
    WHERE plu.promo_id = promos.id
    AND plu.customer_id IN (
      SELECT customer_id 
      FROM customer_user WHERE user_id = auth.uid()
    )
  )
)
WITH CHECK (
  EXISTS (
    SELECT 1
    FROM promos_linked_users plu
    WHERE plu.promo_id = promos.id
    AND plu.customer_id IN (
      SELECT customer_id 
      FROM customer_user WHERE user_id = auth.uid()
    )
  )
);

CREATE POLICY "Users can delete promos for their linked customers"
ON promos FOR DELETE
USING (
  EXISTS (
    SELECT 1
    FROM promos_linked_users plu
    WHERE plu.promo_id = promos.id
    AND plu.customer_id IN (
      SELECT customer_id 
      FROM customer_user WHERE user_id = auth.uid()
    )
  )
);

-- Kaching table policies (linked via kaching_linked_users)
CREATE POLICY "Users can view kaching for their linked customers"
ON kaching FOR SELECT
USING (
  EXISTS (
    SELECT 1
    FROM kaching_linked_users klu
    WHERE klu.kaching_id = kaching.id
    AND klu.customer_id IN (
      SELECT customer_id 
      FROM customer_user WHERE user_id = auth.uid()
    )
  )
);

CREATE POLICY "Users can insert kaching for their linked customers"
ON kaching FOR INSERT
WITH CHECK (
  EXISTS (
    SELECT 1
    FROM kaching_linked_users klu
    WHERE klu.kaching_id = kaching.id
    AND klu.customer_id IN (
      SELECT customer_id 
      FROM customer_user WHERE user_id = auth.uid()
    )
  )
);

CREATE POLICY "Users can update kaching for their linked customers"
ON kaching FOR UPDATE
USING (
  EXISTS (
    SELECT 1
    FROM kaching_linked_users klu
    WHERE klu.kaching_id = kaching.id
    AND klu.customer_id IN (
      SELECT customer_id 
      FROM customer_user WHERE user_id = auth.uid()
    )
  )
)
WITH CHECK (
  EXISTS (
    SELECT 1
    FROM kaching_linked_users klu
    WHERE klu.kaching_id = kaching.id
    AND klu.customer_id IN (
      SELECT customer_id 
      FROM customer_user WHERE user_id = auth.uid()
    )
  )
);

CREATE POLICY "Users can delete kaching for their linked customers"
ON kaching FOR DELETE
USING (
  EXISTS (
    SELECT 1
    FROM kaching_linked_users klu
    WHERE klu.kaching_id = kaching.id
    AND klu.customer_id IN (
      SELECT customer_id 
      FROM customer_user WHERE user_id = auth.uid()
    )
  )
);

-- Survey responses table policies (linked via survey_store_link)
CREATE POLICY "Users can view survey_responses for their linked customers"
ON survey_responses FOR SELECT
USING (
  EXISTS (
    SELECT 1
    FROM survey_store_link ssl
    WHERE ssl.survey_id = survey_responses.survey_id
    AND ssl.store_code = survey_responses.store_code
    AND ssl.customer_id IN (
      SELECT customer_id 
      FROM customer_user WHERE user_id = auth.uid()
    )
  )
);

CREATE POLICY "Users can insert survey_responses for their linked customers"
ON survey_responses FOR INSERT
WITH CHECK (
  EXISTS (
    SELECT 1
    FROM survey_store_link ssl
    WHERE ssl.survey_id = survey_responses.survey_id
    AND ssl.store_code = survey_responses.store_code
    AND ssl.customer_id IN (
      SELECT customer_id 
      FROM customer_user WHERE user_id = auth.uid()
    )
  )
);

CREATE POLICY "Users can update survey_responses for their linked customers"
ON survey_responses FOR UPDATE
USING (
  EXISTS (
    SELECT 1
    FROM survey_store_link ssl
    WHERE ssl.survey_id = survey_responses.survey_id
    AND ssl.store_code = survey_responses.store_code
    AND ssl.customer_id IN (
      SELECT customer_id 
      FROM customer_user WHERE user_id = auth.uid()
    )
  )
)
WITH CHECK (
  EXISTS (
    SELECT 1
    FROM survey_store_link ssl
    WHERE ssl.survey_id = survey_responses.survey_id
    AND ssl.store_code = survey_responses.store_code
    AND ssl.customer_id IN (
      SELECT customer_id 
      FROM customer_user WHERE user_id = auth.uid()
    )
  )
);

CREATE POLICY "Users can delete survey_responses for their linked customers"
ON survey_responses FOR DELETE
USING (
  EXISTS (
    SELECT 1
    FROM survey_store_link ssl
    WHERE ssl.survey_id = survey_responses.survey_id
    AND ssl.store_code = survey_responses.store_code
    AND ssl.customer_id IN (
      SELECT customer_id 
      FROM customer_user WHERE user_id = auth.uid()
    )
  )
);

-- Survey table policies (linked via survey_store_link)
CREATE POLICY "Users can view survey for their linked customers"
ON survey FOR SELECT
USING (
  EXISTS (
    SELECT 1
    FROM survey_store_link ssl
    WHERE ssl.survey_id = survey.id
    AND ssl.customer_id IN (
      SELECT customer_id 
      FROM customer_user WHERE user_id = auth.uid()
    )
  )
);

CREATE POLICY "Users can insert survey for their linked customers"
ON survey FOR INSERT
WITH CHECK (
  EXISTS (
    SELECT 1
    FROM survey_store_link ssl
    WHERE ssl.survey_id = survey.id
    AND ssl.customer_id IN (
      SELECT customer_id 
      FROM customer_user WHERE user_id = auth.uid()
    )
  )
);

CREATE POLICY "Users can update survey for their linked customers"
ON survey FOR UPDATE
USING (
  EXISTS (
    SELECT 1
    FROM survey_store_link ssl
    WHERE ssl.survey_id = survey.id
    AND ssl.customer_id IN (
      SELECT customer_id 
      FROM customer_user WHERE user_id = auth.uid()
    )
  )
)
WITH CHECK (
  EXISTS (
    SELECT 1
    FROM survey_store_link ssl
    WHERE ssl.survey_id = survey.id
    AND ssl.customer_id IN (
      SELECT customer_id 
      FROM customer_user WHERE user_id = auth.uid()
    )
  )
);

CREATE POLICY "Users can delete survey for their linked customers"
ON survey FOR DELETE
USING (
  EXISTS (
    SELECT 1
    FROM survey_store_link ssl
    WHERE ssl.survey_id = survey.id
    AND ssl.customer_id IN (
      SELECT customer_id 
      FROM customer_user WHERE user_id = auth.uid()
    )
  )
);

