-- ============================================================================
-- Migration: Add DELETE Policies for All Tables
-- Date: 2026-02-09
-- Purpose: Implement RLS DELETE policies for Growth Engine CRM
-- 
-- Context from C6 Audit Analysis:
-- - Growth Engine is B2B CRM - leads don't have direct access
-- - All authenticated users are internal team members (email whitelist)
-- - Need team-level DELETE policies + GDPR deletion workflow
-- - Soft deletion preferred for audit trail and GDPR compliance
-- ============================================================================

-- ============================================================================
-- SECTION 1: Add soft deletion columns where missing
-- GDPR requires audit trail - soft delete allows data recovery and compliance
-- ============================================================================

-- Add deleted_at columns for soft deletion support on tables that need it
DO $$
BEGIN
    -- Leads table - critical for GDPR, must have soft delete
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'leads' AND column_name = 'deleted_at') THEN
        ALTER TABLE leads ADD COLUMN deleted_at TIMESTAMPTZ DEFAULT NULL;
        ALTER TABLE leads ADD COLUMN deleted_by UUID DEFAULT NULL REFERENCES auth.users(id);
        CREATE INDEX idx_leads_deleted_at ON leads(deleted_at) WHERE deleted_at IS NULL;
    END IF;

    -- Communications - soft delete for compliance
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'communications' AND column_name = 'deleted_at') THEN
        ALTER TABLE communications ADD COLUMN deleted_at TIMESTAMPTZ DEFAULT NULL;
        ALTER TABLE communications ADD COLUMN deleted_by UUID DEFAULT NULL;
    END IF;

    -- SMS Messages - soft delete for compliance
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'sms_messages' AND column_name = 'deleted_at') THEN
        ALTER TABLE sms_messages ADD COLUMN deleted_at TIMESTAMPTZ DEFAULT NULL;
        ALTER TABLE sms_messages ADD COLUMN deleted_by UUID DEFAULT NULL;
    END IF;

    -- Sequences - soft delete
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'sequences' AND column_name = 'deleted_at') THEN
        ALTER TABLE sequences ADD COLUMN deleted_at TIMESTAMPTZ DEFAULT NULL;
        ALTER TABLE sequences ADD COLUMN deleted_by UUID DEFAULT NULL;
    END IF;

    -- Callbacks - soft delete
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'callbacks' AND column_name = 'deleted_at') THEN
        ALTER TABLE callbacks ADD COLUMN deleted_at TIMESTAMPTZ DEFAULT NULL;
        ALTER TABLE callbacks ADD COLUMN deleted_by UUID DEFAULT NULL;
    END IF;
END $$;

-- ============================================================================
-- SECTION 2: Helper function for checking admin role
-- Centralized admin check - currently all authenticated users are team members
-- Future: could check a roles table or user metadata
-- ============================================================================

CREATE OR REPLACE FUNCTION is_team_member()
RETURNS BOOLEAN AS $$
BEGIN
    -- All authenticated users in Growth Engine are team members
    -- (protected by email whitelist in auth middleware)
    RETURN auth.uid() IS NOT NULL;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE OR REPLACE FUNCTION is_admin_user()
RETURNS BOOLEAN AS $$
DECLARE
    user_email TEXT;
BEGIN
    -- Get current user's email
    SELECT email INTO user_email FROM auth.users WHERE id = auth.uid();
    
    -- Admin emails - should match your admin list
    -- TODO: Move to a roles table for better management
    RETURN user_email IN (
        'michael@rateright.com.au',
        'admin@rateright.com.au'
    );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- ============================================================================
-- SECTION 3: DELETE Policies for Core CRM Tables
-- Pattern: Authenticated team members can delete, with soft delete preferred
-- ============================================================================

-- -----------------------------------------------------------------------------
-- LEADS - Core entity, GDPR-sensitive
-- Policy: Team members can soft-delete; hard delete admin-only
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS "Team members can soft delete leads" ON leads;
CREATE POLICY "Team members can soft delete leads"
ON leads FOR UPDATE
TO authenticated
USING (is_team_member() AND deleted_at IS NULL)
WITH CHECK (
    is_team_member() 
    AND deleted_at IS NOT NULL  -- Only allow setting deleted_at
    AND deleted_by = auth.uid() -- Must record who deleted
);

-- Hard delete only for admins (GDPR "right to erasure" requests)
DROP POLICY IF EXISTS "Admins can hard delete leads" ON leads;
CREATE POLICY "Admins can hard delete leads"
ON leads FOR DELETE
TO authenticated
USING (is_admin_user());

COMMENT ON POLICY "Team members can soft delete leads" ON leads IS 
'GDPR-compliant soft deletion. Team members mark leads as deleted but data retained for audit. Use UPDATE to set deleted_at.';

COMMENT ON POLICY "Admins can hard delete leads" ON leads IS 
'Hard delete for GDPR "right to erasure" requests only. Admin-restricted.';

-- -----------------------------------------------------------------------------
-- COMMUNICATIONS - Call/SMS records linked to leads
-- Policy: Team members can delete their own; admins can delete any
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS "Team members can delete communications" ON communications;
CREATE POLICY "Team members can delete communications"
ON communications FOR DELETE
TO authenticated
USING (
    is_team_member()
    AND (
        user_id = auth.uid()  -- Own communications
        OR is_admin_user()     -- Or admin
    )
);

COMMENT ON POLICY "Team members can delete communications" ON communications IS 
'Users can delete their own communication records. Admins can delete any.';

-- -----------------------------------------------------------------------------
-- SMS_MESSAGES - SMS history
-- Policy: Team members can delete; prefer soft delete via update
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS "Team members can delete sms_messages" ON sms_messages;
CREATE POLICY "Team members can delete sms_messages"
ON sms_messages FOR DELETE
TO authenticated
USING (
    is_team_member()
    AND (
        sent_by = auth.uid()  -- Own messages
        OR is_admin_user()     -- Or admin
    )
);

COMMENT ON POLICY "Team members can delete sms_messages" ON sms_messages IS 
'Users can delete their own SMS records. Admins can delete any.';

-- -----------------------------------------------------------------------------
-- CALL_QUALITY_SCORES - AI scoring data
-- Policy: Admins only - quality data should be preserved
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS "Admins can delete call_quality_scores" ON call_quality_scores;
CREATE POLICY "Admins can delete call_quality_scores"
ON call_quality_scores FOR DELETE
TO authenticated
USING (is_admin_user());

COMMENT ON POLICY "Admins can delete call_quality_scores" ON call_quality_scores IS 
'Admin-only deletion. Quality scores preserved for analytics and coaching.';

-- -----------------------------------------------------------------------------
-- QUALITY_AUDITS - Audit records
-- Policy: Admin only - audit trail must be preserved
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS "Admins can delete quality_audits" ON quality_audits;
CREATE POLICY "Admins can delete quality_audits"
ON quality_audits FOR DELETE
TO authenticated
USING (is_admin_user());

COMMENT ON POLICY "Admins can delete quality_audits" ON quality_audits IS 
'Admin-only. Audit records are compliance-critical.';

-- -----------------------------------------------------------------------------
-- REP_WEEKLY_SNAPSHOTS - Performance snapshots
-- Policy: Admin only
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS "Admins can delete rep_weekly_snapshots" ON rep_weekly_snapshots;
CREATE POLICY "Admins can delete rep_weekly_snapshots"
ON rep_weekly_snapshots FOR DELETE
TO authenticated
USING (is_admin_user());

-- -----------------------------------------------------------------------------
-- AI_SUGGESTIONS - AI-generated suggestions
-- Policy: Team members can delete
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS "Team members can delete ai_suggestions" ON ai_suggestions;
CREATE POLICY "Team members can delete ai_suggestions"
ON ai_suggestions FOR DELETE
TO authenticated
USING (is_team_member());

COMMENT ON POLICY "Team members can delete ai_suggestions" ON ai_suggestions IS 
'Team members can clean up outdated AI suggestions.';

-- -----------------------------------------------------------------------------
-- PROMPT_VERSIONS - Prompt management
-- Policy: Admin only - prompts are system configuration
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS "Admins can delete prompt_versions" ON prompt_versions;
CREATE POLICY "Admins can delete prompt_versions"
ON prompt_versions FOR DELETE
TO authenticated
USING (is_admin_user());

-- -----------------------------------------------------------------------------
-- SMS_TEMPLATES - SMS template library
-- Policy: Team members can delete templates they created
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS "Team members can delete sms_templates" ON sms_templates;
CREATE POLICY "Team members can delete sms_templates"
ON sms_templates FOR DELETE
TO authenticated
USING (
    is_team_member()
    AND (
        created_by = auth.uid()
        OR is_admin_user()
    )
);

-- -----------------------------------------------------------------------------
-- SEQUENCES - SMS automation sequences
-- Policy: Creator or admin can delete
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS "Creators can delete sequences" ON sequences;
CREATE POLICY "Creators can delete sequences"
ON sequences FOR DELETE
TO authenticated
USING (
    is_team_member()
    AND (
        created_by = auth.uid()
        OR is_admin_user()
    )
);

-- -----------------------------------------------------------------------------
-- SEQUENCE_ENROLLMENTS - Lead enrollments in sequences
-- Policy: Team members can unenroll leads
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS "Team members can delete sequence_enrollments" ON sequence_enrollments;
CREATE POLICY "Team members can delete sequence_enrollments"
ON sequence_enrollments FOR DELETE
TO authenticated
USING (is_team_member());

COMMENT ON POLICY "Team members can delete sequence_enrollments" ON sequence_enrollments IS 
'Team members can unenroll leads from sequences.';

-- -----------------------------------------------------------------------------
-- SEQUENCE_STEPS - Steps within sequences
-- Policy: Sequence creator or admin
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS "Creators can delete sequence_steps" ON sequence_steps;
CREATE POLICY "Creators can delete sequence_steps"
ON sequence_steps FOR DELETE
TO authenticated
USING (
    is_team_member()
    AND EXISTS (
        SELECT 1 FROM sequences s 
        WHERE s.id = sequence_steps.sequence_id 
        AND (s.created_by = auth.uid() OR is_admin_user())
    )
);

-- -----------------------------------------------------------------------------
-- CALLBACKS - Scheduled callbacks
-- Policy: Team members can delete their own callbacks
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS "Team members can delete callbacks" ON callbacks;
CREATE POLICY "Team members can delete callbacks"
ON callbacks FOR DELETE
TO authenticated
USING (
    is_team_member()
    AND (
        scheduled_by = auth.uid()
        OR assigned_to = auth.uid()
        OR is_admin_user()
    )
);

-- ============================================================================
-- SECTION 4: DELETE Policies for Analytics/Metrics Tables
-- Pattern: Admin-only to preserve data integrity
-- ============================================================================

-- -----------------------------------------------------------------------------
-- USAGE_EVENTS - Tracking events
-- Policy: Admin only
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS "Admins can delete usage_events" ON usage_events;
CREATE POLICY "Admins can delete usage_events"
ON usage_events FOR DELETE
TO authenticated
USING (is_admin_user());

-- -----------------------------------------------------------------------------
-- DAILY_METRICS - Aggregated daily metrics
-- Policy: Admin only
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS "Admins can delete daily_metrics" ON daily_metrics;
CREATE POLICY "Admins can delete daily_metrics"
ON daily_metrics FOR DELETE
TO authenticated
USING (is_admin_user());

-- -----------------------------------------------------------------------------
-- PLATFORM_ACTIVITY - Platform sync data
-- Policy: Admin only
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS "Admins can delete platform_activity" ON platform_activity;
CREATE POLICY "Admins can delete platform_activity"
ON platform_activity FOR DELETE
TO authenticated
USING (is_admin_user());

-- ============================================================================
-- SECTION 5: DELETE Policies for Gamification Tables
-- Pattern: Team members own their data; admins can delete any
-- ============================================================================

-- -----------------------------------------------------------------------------
-- ACTIVATION_STATES - User activation progress
-- Policy: Own data or admin
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS "Users can delete own activation_states" ON activation_states;
CREATE POLICY "Users can delete own activation_states"
ON activation_states FOR DELETE
TO authenticated
USING (
    user_id = auth.uid()
    OR is_admin_user()
);

-- -----------------------------------------------------------------------------
-- ACHIEVEMENTS - User achievements
-- Policy: Admin only - achievements are earned
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS "Admins can delete achievements" ON achievements;
CREATE POLICY "Admins can delete achievements"
ON achievements FOR DELETE
TO authenticated
USING (is_admin_user());

-- -----------------------------------------------------------------------------
-- USER_XP - XP totals
-- Policy: Admin only
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS "Admins can delete user_xp" ON user_xp;
CREATE POLICY "Admins can delete user_xp"
ON user_xp FOR DELETE
TO authenticated
USING (is_admin_user());

-- -----------------------------------------------------------------------------
-- XP_HISTORY - XP transaction history
-- Policy: Admin only
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS "Admins can delete xp_history" ON xp_history;
CREATE POLICY "Admins can delete xp_history"
ON xp_history FOR DELETE
TO authenticated
USING (is_admin_user());

-- -----------------------------------------------------------------------------
-- CHALLENGES - Gamification challenges
-- Policy: Creator or admin
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS "Creators can delete challenges" ON challenges;
CREATE POLICY "Creators can delete challenges"
ON challenges FOR DELETE
TO authenticated
USING (
    created_by = auth.uid()
    OR is_admin_user()
);

-- -----------------------------------------------------------------------------
-- DUEL_CHALLENGES - 1v1 challenges
-- Policy: Participants or admin
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS "Participants can delete duel_challenges" ON duel_challenges;
CREATE POLICY "Participants can delete duel_challenges"
ON duel_challenges FOR DELETE
TO authenticated
USING (
    challenger_id = auth.uid()
    OR challenged_id = auth.uid()
    OR is_admin_user()
);

-- -----------------------------------------------------------------------------
-- LEADERBOARD_SNAPSHOTS - Historical leaderboard
-- Policy: Admin only
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS "Admins can delete leaderboard_snapshots" ON leaderboard_snapshots;
CREATE POLICY "Admins can delete leaderboard_snapshots"
ON leaderboard_snapshots FOR DELETE
TO authenticated
USING (is_admin_user());

-- ============================================================================
-- SECTION 6: DELETE Policies for Coaching/Intelligence Tables
-- ============================================================================

-- -----------------------------------------------------------------------------
-- COACHING_ALERTS - AI coaching alerts
-- Policy: Team members can dismiss/delete their alerts
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS "Users can delete own coaching_alerts" ON coaching_alerts;
CREATE POLICY "Users can delete own coaching_alerts"
ON coaching_alerts FOR DELETE
TO authenticated
USING (
    user_id = auth.uid()
    OR is_admin_user()
);

-- -----------------------------------------------------------------------------
-- OBJECTION_REBUTTALS - Sales rebuttal library
-- Policy: Creator or admin
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS "Creators can delete objection_rebuttals" ON objection_rebuttals;
CREATE POLICY "Creators can delete objection_rebuttals"
ON objection_rebuttals FOR DELETE
TO authenticated
USING (
    is_team_member()
    AND (
        created_by = auth.uid()
        OR is_admin_user()
    )
);

-- -----------------------------------------------------------------------------
-- DEAL_INTELLIGENCE - Deal insights
-- Policy: Team members (deal owner) or admin
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS "Team members can delete deal_intelligence" ON deal_intelligence;
CREATE POLICY "Team members can delete deal_intelligence"
ON deal_intelligence FOR DELETE
TO authenticated
USING (is_team_member());

-- -----------------------------------------------------------------------------
-- PLAYBOOK_ENTRIES - Sales playbook
-- Policy: Creator or admin
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS "Creators can delete playbook_entries" ON playbook_entries;
CREATE POLICY "Creators can delete playbook_entries"
ON playbook_entries FOR DELETE
TO authenticated
USING (
    created_by = auth.uid()
    OR is_admin_user()
);

-- -----------------------------------------------------------------------------
-- VOICEMAILS - Voicemail recordings
-- Policy: Assigned user or admin
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS "Users can delete own voicemails" ON voicemails;
CREATE POLICY "Users can delete own voicemails"
ON voicemails FOR DELETE
TO authenticated
USING (
    user_id = auth.uid()
    OR is_admin_user()
);

-- ============================================================================
-- SECTION 7: DELETE Policies for System/Config Tables
-- Pattern: Admin-only for system configuration
-- ============================================================================

-- -----------------------------------------------------------------------------
-- TAGS - Tag definitions
-- Policy: Admin only
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS "Admins can delete tags" ON tags;
CREATE POLICY "Admins can delete tags"
ON tags FOR DELETE
TO authenticated
USING (is_admin_user());

-- -----------------------------------------------------------------------------
-- LEAD_TAGS - Lead-tag associations
-- Policy: Team members can untag leads
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS "Team members can delete lead_tags" ON lead_tags;
CREATE POLICY "Team members can delete lead_tags"
ON lead_tags FOR DELETE
TO authenticated
USING (is_team_member());

-- -----------------------------------------------------------------------------
-- COMPETITORS - Competitor tracking
-- Policy: Admin only
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS "Admins can delete competitors" ON competitors;
CREATE POLICY "Admins can delete competitors"
ON competitors FOR DELETE
TO authenticated
USING (is_admin_user());

-- -----------------------------------------------------------------------------
-- OUTBOUND_WEBHOOKS - Webhook configurations
-- Policy: Admin only - system configuration
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS "Admins can delete outbound_webhooks" ON outbound_webhooks;
CREATE POLICY "Admins can delete outbound_webhooks"
ON outbound_webhooks FOR DELETE
TO authenticated
USING (is_admin_user());

-- -----------------------------------------------------------------------------
-- WEBHOOK_DELIVERIES - Webhook delivery logs
-- Policy: Admin only
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS "Admins can delete webhook_deliveries" ON webhook_deliveries;
CREATE POLICY "Admins can delete webhook_deliveries"
ON webhook_deliveries FOR DELETE
TO authenticated
USING (is_admin_user());

-- -----------------------------------------------------------------------------
-- ENRICHMENT_CONFIG - API enrichment settings
-- Policy: Admin only
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS "Admins can delete enrichment_config" ON enrichment_config;
CREATE POLICY "Admins can delete enrichment_config"
ON enrichment_config FOR DELETE
TO authenticated
USING (is_admin_user());

-- -----------------------------------------------------------------------------
-- ENRICHMENT_CACHE - Cached enrichment data
-- Policy: Admin only (cache management)
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS "Admins can delete enrichment_cache" ON enrichment_cache;
CREATE POLICY "Admins can delete enrichment_cache"
ON enrichment_cache FOR DELETE
TO authenticated
USING (is_admin_user());

-- -----------------------------------------------------------------------------
-- ENRICHMENT_USAGE - API usage tracking
-- Policy: Admin only
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS "Admins can delete enrichment_usage" ON enrichment_usage;
CREATE POLICY "Admins can delete enrichment_usage"
ON enrichment_usage FOR DELETE
TO authenticated
USING (is_admin_user());

-- ============================================================================
-- SECTION 8: DELETE Policies for Job/Report Tables
-- Pattern: Admin-only to preserve system integrity
-- ============================================================================

-- -----------------------------------------------------------------------------
-- JOB_LOCKS - Background job locking
-- Policy: Admin only (system table)
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS "Admins can delete job_locks" ON job_locks;
CREATE POLICY "Admins can delete job_locks"
ON job_locks FOR DELETE
TO authenticated
USING (is_admin_user());

-- -----------------------------------------------------------------------------
-- FRICTION_PATTERNS - Detected friction points
-- Policy: Admin only
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS "Admins can delete friction_patterns" ON friction_patterns;
CREATE POLICY "Admins can delete friction_patterns"
ON friction_patterns FOR DELETE
TO authenticated
USING (is_admin_user());

-- -----------------------------------------------------------------------------
-- EXCELLENCE_REPORTS - Weekly excellence reports
-- Policy: Admin only
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS "Admins can delete excellence_reports" ON excellence_reports;
CREATE POLICY "Admins can delete excellence_reports"
ON excellence_reports FOR DELETE
TO authenticated
USING (is_admin_user());

-- -----------------------------------------------------------------------------
-- IMPACT_MEASUREMENTS - Feature impact data
-- Policy: Admin only
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS "Admins can delete impact_measurements" ON impact_measurements;
CREATE POLICY "Admins can delete impact_measurements"
ON impact_measurements FOR DELETE
TO authenticated
USING (is_admin_user());

-- ============================================================================
-- SECTION 9: DELETE Policies for Frontend/Notification Tables
-- ============================================================================

-- -----------------------------------------------------------------------------
-- WEB_VITALS - Frontend performance metrics
-- Policy: Admin only (analytics data)
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS "Admins can delete web_vitals" ON web_vitals;
CREATE POLICY "Admins can delete web_vitals"
ON web_vitals FOR DELETE
TO authenticated
USING (is_admin_user());

-- -----------------------------------------------------------------------------
-- FRONTEND_ERRORS - Frontend error logs
-- Policy: Admin only
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS "Admins can delete frontend_errors" ON frontend_errors;
CREATE POLICY "Admins can delete frontend_errors"
ON frontend_errors FOR DELETE
TO authenticated
USING (is_admin_user());

-- -----------------------------------------------------------------------------
-- PUSH_SUBSCRIPTIONS - Push notification subscriptions
-- Policy: Users can delete their own subscriptions
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS "Users can delete own push_subscriptions" ON push_subscriptions;
CREATE POLICY "Users can delete own push_subscriptions"
ON push_subscriptions FOR DELETE
TO authenticated
USING (
    user_id = auth.uid()
    OR is_admin_user()
);

-- -----------------------------------------------------------------------------
-- NOTIFICATION_PREFERENCES - User notification settings
-- Policy: Users can delete their own preferences
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS "Users can delete own notification_preferences" ON notification_preferences;
CREATE POLICY "Users can delete own notification_preferences"
ON notification_preferences FOR DELETE
TO authenticated
USING (
    user_id = auth.uid()
    OR is_admin_user()
);

-- -----------------------------------------------------------------------------
-- USERS - User records (if exists as separate table)
-- Policy: Admin only - user management
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS "Admins can delete users" ON users;
CREATE POLICY "Admins can delete users"
ON users FOR DELETE
TO authenticated
USING (is_admin_user());

-- ============================================================================
-- SECTION 10: GDPR Soft Delete Function
-- Provides a standardized way to soft-delete records with audit trail
-- ============================================================================

CREATE OR REPLACE FUNCTION soft_delete_lead(lead_id UUID)
RETURNS BOOLEAN AS $$
DECLARE
    success BOOLEAN;
BEGIN
    -- Check authorization
    IF NOT is_team_member() THEN
        RAISE EXCEPTION 'Unauthorized: Must be team member';
    END IF;

    -- Perform soft delete
    UPDATE leads 
    SET 
        deleted_at = NOW(),
        deleted_by = auth.uid()
    WHERE id = lead_id 
    AND deleted_at IS NULL;

    GET DIAGNOSTICS success = ROW_COUNT > 0;
    
    -- Also soft-delete related communications
    UPDATE communications 
    SET 
        deleted_at = NOW(),
        deleted_by = auth.uid()
    WHERE lead_id = lead_id 
    AND deleted_at IS NULL;
    
    -- Soft-delete related SMS messages
    UPDATE sms_messages 
    SET 
        deleted_at = NOW(),
        deleted_by = auth.uid()
    WHERE lead_id = lead_id 
    AND deleted_at IS NULL;
    
    RETURN success;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

COMMENT ON FUNCTION soft_delete_lead IS 
'GDPR-compliant soft deletion of a lead and all related data. 
Records deleted_at timestamp and user who performed deletion.
Use for standard deletion requests.';

-- ============================================================================
-- SECTION 11: GDPR Hard Delete Function (Right to Erasure)
-- For verified GDPR Article 17 requests only
-- ============================================================================

CREATE OR REPLACE FUNCTION gdpr_hard_delete_lead(lead_id UUID)
RETURNS BOOLEAN AS $$
DECLARE
    lead_email TEXT;
BEGIN
    -- Only admins can perform hard deletes
    IF NOT is_admin_user() THEN
        RAISE EXCEPTION 'Unauthorized: GDPR hard delete requires admin access';
    END IF;

    -- Get lead email for audit log
    SELECT email INTO lead_email FROM leads WHERE id = lead_id;
    
    -- Log the deletion (to a separate audit table if exists)
    -- INSERT INTO gdpr_deletion_log (lead_id, lead_email, deleted_by, deleted_at)
    -- VALUES (lead_id, lead_email, auth.uid(), NOW());
    
    -- Delete in correct order (foreign key constraints)
    DELETE FROM lead_tags WHERE lead_id = lead_id;
    DELETE FROM sequence_enrollments WHERE lead_id = lead_id;
    DELETE FROM callbacks WHERE lead_id = lead_id;
    DELETE FROM sms_messages WHERE lead_id = lead_id;
    DELETE FROM communications WHERE lead_id = lead_id;
    DELETE FROM deal_intelligence WHERE lead_id = lead_id;
    DELETE FROM leads WHERE id = lead_id;
    
    RETURN TRUE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

COMMENT ON FUNCTION gdpr_hard_delete_lead IS 
'Complete erasure of lead data per GDPR Article 17 "Right to Erasure".
Admin-only. Use only for verified GDPR deletion requests.
Deletes lead and ALL related records permanently.';

-- ============================================================================
-- SECTION 12: View for excluding soft-deleted records
-- Use these views in application queries instead of direct table access
-- ============================================================================

CREATE OR REPLACE VIEW active_leads AS
SELECT * FROM leads WHERE deleted_at IS NULL;

CREATE OR REPLACE VIEW active_communications AS
SELECT * FROM communications WHERE deleted_at IS NULL;

CREATE OR REPLACE VIEW active_sms_messages AS
SELECT * FROM sms_messages WHERE deleted_at IS NULL;

COMMENT ON VIEW active_leads IS 'Leads excluding soft-deleted records. Use this view for normal queries.';

-- ============================================================================
-- Migration complete
-- ============================================================================

-- Summary of policies created:
-- 
-- CORE CRM (5 tables):
--   - leads: Soft delete for team, hard delete for admin
--   - communications: Owner or admin
--   - sms_messages: Owner or admin
--   - call_quality_scores: Admin only
--   - quality_audits: Admin only
--
-- SEQUENCES (4 tables):
--   - sequences: Creator or admin
--   - sequence_enrollments: Team members
--   - sequence_steps: Sequence creator or admin
--   - callbacks: Scheduler/assignee or admin
--
-- ANALYTICS (4 tables):
--   - usage_events: Admin only
--   - daily_metrics: Admin only
--   - platform_activity: Admin only
--   - rep_weekly_snapshots: Admin only
--
-- GAMIFICATION (6 tables):
--   - activation_states: Owner or admin
--   - achievements: Admin only
--   - user_xp: Admin only
--   - xp_history: Admin only
--   - challenges: Creator or admin
--   - duel_challenges: Participants or admin
--   - leaderboard_snapshots: Admin only
--
-- COACHING/INTELLIGENCE (5 tables):
--   - coaching_alerts: Owner or admin
--   - objection_rebuttals: Creator or admin
--   - deal_intelligence: Team members
--   - playbook_entries: Creator or admin
--   - voicemails: Owner or admin
--   - ai_suggestions: Team members
--
-- SYSTEM/CONFIG (10 tables):
--   - tags: Admin only
--   - lead_tags: Team members
--   - competitors: Admin only
--   - outbound_webhooks: Admin only
--   - webhook_deliveries: Admin only
--   - enrichment_config: Admin only
--   - enrichment_cache: Admin only
--   - enrichment_usage: Admin only
--   - prompt_versions: Admin only
--   - sms_templates: Creator or admin
--
-- JOBS/REPORTS (4 tables):
--   - job_locks: Admin only
--   - friction_patterns: Admin only
--   - excellence_reports: Admin only
--   - impact_measurements: Admin only
--
-- FRONTEND/NOTIFICATIONS (5 tables):
--   - web_vitals: Admin only
--   - frontend_errors: Admin only
--   - push_subscriptions: Owner or admin
--   - notification_preferences: Owner or admin
--   - users: Admin only
--
-- GDPR Functions:
--   - soft_delete_lead(): Team members can soft delete
--   - gdpr_hard_delete_lead(): Admin-only complete erasure
--
-- Views:
--   - active_leads: Excludes soft-deleted
--   - active_communications: Excludes soft-deleted
--   - active_sms_messages: Excludes soft-deleted
