-- 1. Setup Functions for Context Retrieval
CREATE OR REPLACE FUNCTION current_account_id() RETURNS UUID AS $$
    SELECT current_setting('app.current_account_id', true)::UUID;
$$ LANGUAGE SQL STABLE;

CREATE OR REPLACE FUNCTION is_admin_bypass() RETURNS BOOLEAN AS $$
    SELECT current_setting('app.bypass_rls', true) = 'true';
$$ LANGUAGE SQL STABLE;

-- 2. Helper to Recreate Policies with Direct account_id Check
-- This ensures high performance by avoiding joins in the security policy
CREATE OR REPLACE FUNCTION setup_rls_policy(tbl_name TEXT) RETURNS VOID AS $$
BEGIN
    EXECUTE 'ALTER TABLE ' || tbl_name || ' ENABLE ROW LEVEL SECURITY';
    EXECUTE 'ALTER TABLE ' || tbl_name || ' FORCE ROW LEVEL SECURITY';
    EXECUTE 'DROP POLICY IF EXISTS ' || tbl_name || '_isolation_policy ON ' || tbl_name;
    EXECUTE 'CREATE POLICY ' || tbl_name || '_isolation_policy ON ' || tbl_name || 
            ' FOR ALL TO PUBLIC USING (is_admin_bypass() OR account_id = current_account_id())';
END;
$$ LANGUAGE plpgsql;

-- 3. Apply RLS to all Tenant-Specific Tables
SELECT setup_rls_policy('websites');
SELECT setup_rls_policy('ai_audience_fit_scores');
SELECT setup_rls_policy('pages');
SELECT setup_rls_policy('prompts');
SELECT setup_rls_policy('ai_visibility_scores');
SELECT setup_rls_policy('citations');
SELECT setup_rls_policy('visibility_gaps');
SELECT setup_rls_policy('notifications');
SELECT setup_rls_policy('prompt_ai_responses');
SELECT setup_rls_policy('prompt_responses');
SELECT setup_rls_policy('tracked_prompts');
SELECT setup_rls_policy('traffic_events');
SELECT setup_rls_policy('icp_inputs');
SELECT setup_rls_policy('icp_results');
SELECT setup_rls_policy('ai_visibility_runs');
