-- 20260406_multi_tenancy.sql
-- Upgrades existing tables to track mult-tenant ownership safely.

-- 1. Ensure user_id references auth.users in existing tables (members_list and templates)
-- Using DO block to safely add if they were missed during manual deployment
DO $$ 
BEGIN
  BEGIN
    ALTER TABLE members_list ADD COLUMN user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE;
  EXCEPTION 
    WHEN duplicate_column THEN RAISE NOTICE 'column user_id already exists in members_list';
  END;

  BEGIN
    ALTER TABLE templates ADD COLUMN user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE;
  EXCEPTION 
    WHEN duplicate_column THEN RAISE NOTICE 'column user_id already exists in templates';
  END;
END $$;

-- 2. Add user_id to email_sends isolated tracking table
DO $$
BEGIN
  BEGIN
    ALTER TABLE email_sends ADD COLUMN user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE;
  EXCEPTION
    WHEN duplicate_column THEN RAISE NOTICE 'column user_id already exists in email_sends';
  END;
END $$;

-- 3. Update Policy for email_sends
ALTER TABLE email_sends ENABLE ROW LEVEL SECURITY;

-- Remove old overly permissive policies if they exist to prevent cross-tenant exposure
DROP POLICY IF EXISTS "Allow anyone to select from email_sends" ON email_sends;
DROP POLICY IF EXISTS "Allow anyone to update email_sends" ON email_sends;
DROP POLICY IF EXISTS "Allow anyone to insert into email_sends" ON email_sends;

-- Require authenticated user isolation 
CREATE POLICY "Users view own sends" ON email_sends FOR SELECT TO authenticated USING (auth.uid() = user_id);
CREATE POLICY "Users insert own sends" ON email_sends FOR INSERT TO authenticated WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users update own sends" ON email_sends FOR UPDATE TO authenticated USING (auth.uid() = user_id);


-- 4. Secure email_opens and email_clicks
-- We leave INSERT permissive (or rely on edge function service role) because these are hit by public webhooks
DROP POLICY IF EXISTS "Allow anyone to select from email_opens" ON email_opens;
DROP POLICY IF EXISTS "Allow anyone to select from email_clicks" ON email_clicks;

-- Secure READ operations using the exact email_sends relational join
CREATE POLICY "Users view own opens" ON email_opens
FOR SELECT TO authenticated
USING (
  EXISTS (
    SELECT 1 FROM email_sends s
    WHERE s.id = email_opens.email_send_id
    AND s.user_id = auth.uid()
  )
);

CREATE POLICY "Users view own clicks" ON email_clicks
FOR SELECT TO authenticated
USING (
  EXISTS (
    SELECT 1 FROM email_sends s
    WHERE s.id = email_clicks.email_send_id
    AND s.user_id = auth.uid()
  )
);

-- 5. Force RLS Enablement globally to ensure safety
ALTER TABLE members_list ENABLE ROW LEVEL SECURITY;
ALTER TABLE templates ENABLE ROW LEVEL SECURITY;

-- If members_list or templates lost their policies, redeclare them strictly
DROP POLICY IF EXISTS "Users can fully manage their contacts" ON members_list;
CREATE POLICY "Users can fully manage their contacts" ON members_list FOR ALL USING (auth.uid() = user_id);

DROP POLICY IF EXISTS "Users can fully manage their templates" ON templates;
CREATE POLICY "Users can fully manage their templates" ON templates FOR ALL USING (auth.uid() = user_id);
