-- c77_rbac--1.1.sql: Enhanced PostgreSQL extension for role-based access control (RBAC)
-- Requires PostgreSQL 14 or later
-- Production-ready version with bulk operations, removal functions, and enhanced error handling
\echo Use "CREATE EXTENSION c77_rbac" to load this file. \quit

-- Tables (unchanged from 1.0)
CREATE TABLE public.c77_rbac_subjects (
    subject_id BIGSERIAL PRIMARY KEY,
    external_id TEXT UNIQUE NOT NULL
);

CREATE TABLE public.c77_rbac_roles (
    role_id BIGSERIAL PRIMARY KEY,
    name TEXT UNIQUE NOT NULL
);

CREATE TABLE public.c77_rbac_features (
    feature_id BIGSERIAL PRIMARY KEY,
    name TEXT UNIQUE NOT NULL
);

CREATE TABLE public.c77_rbac_subject_roles (
    subject_id BIGINT REFERENCES public.c77_rbac_subjects(subject_id) ON DELETE CASCADE,
    role_id BIGINT REFERENCES public.c77_rbac_roles(role_id) ON DELETE CASCADE,
    scope_type TEXT NOT NULL,
    scope_id TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (subject_id, role_id, scope_type, scope_id)
);

CREATE TABLE public.c77_rbac_role_features (
    role_id BIGINT REFERENCES public.c77_rbac_roles(role_id) ON DELETE CASCADE,
    feature_id BIGINT REFERENCES public.c77_rbac_features(feature_id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (role_id, feature_id)
);

-- Enhanced indexes for better performance
CREATE INDEX idx_c77_rbac_subjects_external_id ON public.c77_rbac_subjects(external_id);
CREATE INDEX idx_c77_rbac_subjects_external_id_hash ON public.c77_rbac_subjects USING hash(external_id);
CREATE INDEX idx_c77_rbac_roles_name ON public.c77_rbac_roles(name);
CREATE INDEX idx_c77_rbac_features_name ON public.c77_rbac_features(name);
CREATE INDEX idx_c77_rbac_subject_roles_subject_id ON public.c77_rbac_subject_roles(subject_id);
CREATE INDEX idx_c77_rbac_subject_roles_role_id ON public.c77_rbac_subject_roles(role_id);
CREATE INDEX idx_c77_rbac_subject_roles_scope ON public.c77_rbac_subject_roles(scope_type, scope_id);
CREATE INDEX idx_c77_rbac_subject_roles_composite ON public.c77_rbac_subject_roles(subject_id, scope_type, scope_id);

-- Enhanced c77_rbac_assign_subject with better error handling
CREATE OR REPLACE FUNCTION public.c77_rbac_assign_subject(
    p_external_id TEXT,
    p_role_name TEXT,
    p_scope_type TEXT,
    p_scope_id TEXT
) RETURNS VOID AS $$
DECLARE
    v_subject_id BIGINT;
    v_role_id BIGINT;
BEGIN
    -- Comprehensive input validation with helpful error messages
    IF p_external_id IS NULL OR trim(p_external_id) = '' THEN
        RAISE EXCEPTION 'external_id cannot be NULL or empty'
            USING HINT = 'Provide a valid user identifier',
                  ERRCODE = 'invalid_parameter_value';
    END IF;

    IF p_role_name IS NULL OR trim(p_role_name) = '' THEN
        RAISE EXCEPTION 'role_name cannot be NULL or empty'
            USING HINT = 'Provide a valid role name like admin, manager, editor',
                  ERRCODE = 'invalid_parameter_value';
    END IF;

    IF p_scope_type IS NULL OR trim(p_scope_type) = '' THEN
        RAISE EXCEPTION 'scope_type cannot be NULL or empty'
            USING HINT = 'Use global, department, region, or custom scope type',
                  ERRCODE = 'invalid_parameter_value';
    END IF;

    IF p_scope_id IS NULL OR trim(p_scope_id) = '' THEN
        RAISE EXCEPTION 'scope_id cannot be NULL or empty'
            USING HINT = 'Use "all" for global scope or specific identifier for scoped access',
                  ERRCODE = 'invalid_parameter_value';
    END IF;

    -- Validate scope_type against common patterns (warning, not error)
    IF p_scope_type NOT IN ('global', 'department', 'region', 'court', 'program', 'project', 'team', 'customer', 'tenant') THEN
        RAISE NOTICE 'scope_type "%" is not in standard list. Proceeding anyway.', p_scope_type;
    END IF;

    -- Insert or get subject
    INSERT INTO public.c77_rbac_subjects (external_id)
    VALUES (trim(p_external_id))
    ON CONFLICT (external_id) DO NOTHING
    RETURNING subject_id INTO v_subject_id;

    IF v_subject_id IS NULL THEN
        SELECT subject_id INTO v_subject_id
        FROM public.c77_rbac_subjects
        WHERE external_id = trim(p_external_id);
    END IF;

    -- Insert or get role
    INSERT INTO public.c77_rbac_roles (name)
    VALUES (trim(p_role_name))
    ON CONFLICT (name) DO NOTHING
    RETURNING role_id INTO v_role_id;

    IF v_role_id IS NULL THEN
        SELECT role_id INTO v_role_id
        FROM public.c77_rbac_roles
        WHERE name = trim(p_role_name);
    END IF;

    -- Assign role to subject with scope
    INSERT INTO public.c77_rbac_subject_roles (subject_id, role_id, scope_type, scope_id)
    VALUES (v_subject_id, v_role_id, trim(p_scope_type), trim(p_scope_id))
    ON CONFLICT (subject_id, role_id, scope_type, scope_id) DO NOTHING;

    RAISE NOTICE 'Assigned role "%" to subject "%" with scope "%/%"', 
        p_role_name, p_external_id, p_scope_type, p_scope_id;

EXCEPTION 
    WHEN unique_violation THEN
        RAISE EXCEPTION 'Role assignment already exists'
            USING HINT = 'This user already has this role with this scope';
    WHEN OTHERS THEN
        RAISE EXCEPTION 'Failed to assign role: %', SQLERRM
            USING HINT = 'Check that inputs are valid and role exists';
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Enhanced c77_rbac_grant_feature with better error handling
CREATE OR REPLACE FUNCTION public.c77_rbac_grant_feature(
    p_role_name TEXT,
    p_feature_name TEXT
) RETURNS VOID AS $$
DECLARE
    v_role_id BIGINT;
    v_feature_id BIGINT;
BEGIN
    -- Input validation
    IF p_role_name IS NULL OR trim(p_role_name) = '' THEN
        RAISE EXCEPTION 'role_name cannot be NULL or empty'
            USING HINT = 'Provide a valid role name',
                  ERRCODE = 'invalid_parameter_value';
    END IF;

    IF p_feature_name IS NULL OR trim(p_feature_name) = '' THEN
        RAISE EXCEPTION 'feature_name cannot be NULL or empty'
            USING HINT = 'Provide a valid feature/permission name',
                  ERRCODE = 'invalid_parameter_value';
    END IF;

    -- Insert or get role
    INSERT INTO public.c77_rbac_roles (name)
    VALUES (trim(p_role_name))
    ON CONFLICT (name) DO NOTHING
    RETURNING role_id INTO v_role_id;

    IF v_role_id IS NULL THEN
        SELECT role_id INTO v_role_id
        FROM public.c77_rbac_roles
        WHERE name = trim(p_role_name);
    END IF;

    -- Insert or get feature
    INSERT INTO public.c77_rbac_features (name)
    VALUES (trim(p_feature_name))
    ON CONFLICT (name) DO NOTHING
    RETURNING feature_id INTO v_feature_id;

    IF v_feature_id IS NULL THEN
        SELECT feature_id INTO v_feature_id
        FROM public.c77_rbac_features
        WHERE name = trim(p_feature_name);
    END IF;

    -- Grant feature to role
    INSERT INTO public.c77_rbac_role_features (role_id, feature_id)
    VALUES (v_role_id, v_feature_id)
    ON CONFLICT (role_id, feature_id) DO NOTHING;

    RAISE NOTICE 'Granted feature "%" to role "%"', p_feature_name, p_role_name;

EXCEPTION WHEN OTHERS THEN
    RAISE EXCEPTION 'Failed to grant feature: %', SQLERRM
        USING HINT = 'Check that role and feature names are valid';
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- NEW: Bulk assignment function for performance
CREATE OR REPLACE FUNCTION public.c77_rbac_bulk_assign_subjects(
    p_external_ids TEXT[],
    p_role_name TEXT,
    p_scope_type TEXT,
    p_scope_id TEXT
) RETURNS TABLE(external_id TEXT, success BOOLEAN, error_message TEXT) AS $$
DECLARE
    v_external_id TEXT;
    v_success_count INTEGER := 0;
    v_error_count INTEGER := 0;
BEGIN
    -- Validate inputs
    IF p_external_ids IS NULL OR array_length(p_external_ids, 1) IS NULL THEN
        RAISE EXCEPTION 'external_ids array cannot be NULL or empty';
    END IF;

    IF p_role_name IS NULL OR trim(p_role_name) = '' THEN
        RAISE EXCEPTION 'role_name cannot be NULL or empty';
    END IF;

    -- Process each external_id
    FOREACH v_external_id IN ARRAY p_external_ids LOOP
        BEGIN
            PERFORM public.c77_rbac_assign_subject(v_external_id, p_role_name, p_scope_type, p_scope_id);
            external_id := v_external_id;
            success := true;
            error_message := NULL;
            v_success_count := v_success_count + 1;
            RETURN NEXT;
        EXCEPTION WHEN OTHERS THEN
            external_id := v_external_id;
            success := false;
            error_message := SQLERRM;
            v_error_count := v_error_count + 1;
            RETURN NEXT;
        END;
    END LOOP;

    RAISE NOTICE 'Bulk assignment complete: % successful, % failed', v_success_count, v_error_count;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- NEW: Remove role assignment function
CREATE OR REPLACE FUNCTION public.c77_rbac_revoke_subject_role(
    p_external_id TEXT,
    p_role_name TEXT,
    p_scope_type TEXT,
    p_scope_id TEXT
) RETURNS BOOLEAN AS $$
DECLARE
    v_removed BOOLEAN := false;
    v_rows_affected INTEGER;
BEGIN
    -- Input validation
    IF p_external_id IS NULL OR trim(p_external_id) = '' THEN
        RAISE EXCEPTION 'external_id cannot be NULL or empty';
    END IF;

    IF p_role_name IS NULL OR trim(p_role_name) = '' THEN
        RAISE EXCEPTION 'role_name cannot be NULL or empty';
    END IF;

    -- Remove the role assignment
    DELETE FROM public.c77_rbac_subject_roles sr
    WHERE sr.subject_id = (
        SELECT subject_id FROM public.c77_rbac_subjects 
        WHERE external_id = trim(p_external_id)
    )
    AND sr.role_id = (
        SELECT role_id FROM public.c77_rbac_roles 
        WHERE name = trim(p_role_name)
    )
    AND sr.scope_type = trim(p_scope_type)
    AND sr.scope_id = trim(p_scope_id);
    
    GET DIAGNOSTICS v_rows_affected = ROW_COUNT;
    v_removed := (v_rows_affected > 0);

    IF v_removed THEN
        RAISE NOTICE 'Revoked role "%" from subject "%" with scope "%/%"', 
            p_role_name, p_external_id, p_scope_type, p_scope_id;
    ELSE
        RAISE NOTICE 'No role assignment found to revoke for subject "%" role "%" scope "%/%"',
            p_external_id, p_role_name, p_scope_type, p_scope_id;
    END IF;

    RETURN v_removed;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- NEW: Remove feature from role function
CREATE OR REPLACE FUNCTION public.c77_rbac_revoke_feature(
    p_role_name TEXT,
    p_feature_name TEXT
) RETURNS BOOLEAN AS $$
DECLARE
    v_removed BOOLEAN := false;
    v_rows_affected INTEGER;
BEGIN
    -- Input validation
    IF p_role_name IS NULL OR trim(p_role_name) = '' THEN
        RAISE EXCEPTION 'role_name cannot be NULL or empty';
    END IF;

    IF p_feature_name IS NULL OR trim(p_feature_name) = '' THEN
        RAISE EXCEPTION 'feature_name cannot be NULL or empty';
    END IF;

    -- Remove the feature from role
    DELETE FROM public.c77_rbac_role_features rf
    WHERE rf.role_id = (
        SELECT role_id FROM public.c77_rbac_roles 
        WHERE name = trim(p_role_name)
    )
    AND rf.feature_id = (
        SELECT feature_id FROM public.c77_rbac_features 
        WHERE name = trim(p_feature_name)
    );
    
    GET DIAGNOSTICS v_rows_affected = ROW_COUNT;
    v_removed := (v_rows_affected > 0);

    IF v_removed THEN
        RAISE NOTICE 'Revoked feature "%" from role "%"', p_feature_name, p_role_name;
    ELSE
        RAISE NOTICE 'Feature "%" was not granted to role "%"', p_feature_name, p_role_name;
    END IF;

    RETURN v_removed;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- NEW: Admin sync functions (referenced in docs but missing)
CREATE OR REPLACE FUNCTION public.c77_rbac_sync_admin_features()
RETURNS INTEGER AS $$
DECLARE
    v_feature_record RECORD;
    v_features_synced INTEGER := 0;
BEGIN
    -- Grant all existing features to 'admin' role
    FOR v_feature_record IN 
        SELECT name FROM public.c77_rbac_features
    LOOP
        -- Use the existing grant function to ensure consistency
        PERFORM public.c77_rbac_grant_feature('admin', v_feature_record.name);
        v_features_synced := v_features_synced + 1;
    END LOOP;

    RAISE NOTICE 'Synced % features to admin role', v_features_synced;
    RETURN v_features_synced;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE OR REPLACE FUNCTION public.c77_rbac_sync_global_admin_features()
RETURNS INTEGER AS $$
DECLARE
    v_role_record RECORD;
    v_feature_record RECORD;
    v_assignments_made INTEGER := 0;
BEGIN
    -- Find all roles that have global/all scope assignments
    FOR v_role_record IN 
        SELECT DISTINCT r.name
        FROM public.c77_rbac_roles r
        JOIN public.c77_rbac_subject_roles sr ON r.role_id = sr.role_id
        WHERE sr.scope_type = 'global' AND sr.scope_id = 'all'
    LOOP
        -- Grant all features to this role
        FOR v_feature_record IN 
            SELECT name FROM public.c77_rbac_features
        LOOP
            PERFORM public.c77_rbac_grant_feature(v_role_record.name, v_feature_record.name);
            v_assignments_made := v_assignments_made + 1;
        END LOOP;
    END LOOP;

    RAISE NOTICE 'Synced features to % global admin roles', v_assignments_made;
    RETURN v_assignments_made;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Enhanced c77_rbac_can_access with performance optimizations
CREATE OR REPLACE FUNCTION public.c77_rbac_can_access_optimized(
    p_feature_name TEXT,
    p_external_id TEXT,
    p_scope_type TEXT,
    p_scope_id TEXT
) RETURNS BOOLEAN AS $$
BEGIN
    -- Fast path for NULL external_id
    IF p_external_id IS NULL THEN
        RETURN FALSE;
    END IF;

    -- Use optimized query with better indexes
    RETURN EXISTS (
        SELECT 1 
        FROM public.c77_rbac_subjects s
        JOIN public.c77_rbac_subject_roles sr ON s.subject_id = sr.subject_id
        JOIN public.c77_rbac_role_features rf ON sr.role_id = rf.role_id
        JOIN public.c77_rbac_features f ON rf.feature_id = f.feature_id
        WHERE s.external_id = p_external_id
        AND f.name = p_feature_name
        AND (
            (sr.scope_type = 'global' AND sr.scope_id = 'all') OR
            (sr.scope_type = p_scope_type AND sr.scope_id = p_scope_id)
        )
    );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER STABLE;

-- Keep the original function for backward compatibility
CREATE OR REPLACE FUNCTION public.c77_rbac_can_access(
    p_feature_name TEXT,
    p_external_id TEXT,
    p_scope_type TEXT,
    p_scope_id TEXT
) RETURNS BOOLEAN AS $$
BEGIN
    -- Use the optimized version
    RETURN public.c77_rbac_can_access_optimized(p_feature_name, p_external_id, p_scope_type, p_scope_id);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER STABLE;

-- Enhanced apply_policy function (unchanged core logic, better error handling)
CREATE OR REPLACE FUNCTION public.c77_rbac_apply_policy(
    p_table_name TEXT,
    p_feature_name TEXT,
    p_scope_type TEXT,
    p_scope_column TEXT
) RETURNS VOID AS $$
DECLARE
    v_schema_name TEXT;
    v_table_name TEXT;
    v_policy_exists BOOLEAN;
    v_table_exists BOOLEAN;
BEGIN
    -- Input validation
    IF p_table_name IS NULL OR trim(p_table_name) = '' THEN
        RAISE EXCEPTION 'table_name cannot be NULL or empty';
    END IF;

    IF p_feature_name IS NULL OR trim(p_feature_name) = '' THEN
        RAISE EXCEPTION 'feature_name cannot be NULL or empty';
    END IF;

    -- Split schema and table name
    IF position('.' IN p_table_name) > 0 THEN
        v_schema_name := split_part(p_table_name, '.', 1);
        v_table_name := split_part(p_table_name, '.', 2);
    ELSE
        v_schema_name := 'public';
        v_table_name := p_table_name;
    END IF;

    -- Check if table exists
    SELECT EXISTS (
        SELECT 1 FROM information_schema.tables
        WHERE table_schema = v_schema_name
        AND table_name = v_table_name
    ) INTO v_table_exists;

    IF NOT v_table_exists THEN
        RAISE EXCEPTION 'Table %.% does not exist', v_schema_name, v_table_name
            USING HINT = 'Check table name and schema';
    END IF;

    -- Check if column exists
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_schema = v_schema_name
        AND table_name = v_table_name
        AND column_name = p_scope_column
    ) THEN
        RAISE EXCEPTION 'Column % does not exist in table %.%', p_scope_column, v_schema_name, v_table_name
            USING HINT = 'Check column name spelling';
    END IF;

    -- Check if policy exists
    SELECT EXISTS (
        SELECT 1 FROM pg_policies
        WHERE schemaname = v_schema_name
          AND tablename = v_table_name
          AND policyname = 'c77_rbac_policy'
    ) INTO v_policy_exists;

    -- Drop existing policy if it exists
    IF v_policy_exists THEN
        EXECUTE format('DROP POLICY c77_rbac_policy ON %I.%I', v_schema_name, v_table_name);
        RAISE NOTICE 'Dropped existing policy on %.%', v_schema_name, v_table_name;
    END IF;

    -- Create the new policy
    EXECUTE format(
            'CREATE POLICY c77_rbac_policy ON %I.%I FOR ALL TO PUBLIC USING (
                public.c77_rbac_can_access(
                    %L,
                    current_setting(''c77_rbac.external_id'', true),
                    %L,
                    %I::text
                )
            )',
            v_schema_name, v_table_name, p_feature_name, p_scope_type, p_scope_column
            );

    -- Enable and force RLS
    EXECUTE format('ALTER TABLE %I.%I ENABLE ROW LEVEL SECURITY', v_schema_name, v_table_name);
    EXECUTE format('ALTER TABLE %I.%I FORCE ROW LEVEL SECURITY', v_schema_name, v_table_name);

    RAISE NOTICE 'Applied RLS policy to %.% requiring feature "%" with scope "%/%"', 
        v_schema_name, v_table_name, p_feature_name, p_scope_type, p_scope_column;

EXCEPTION WHEN OTHERS THEN
    RAISE EXCEPTION 'Failed to apply policy to %.%: %', v_schema_name, v_table_name, SQLERRM
        USING HINT = 'Check table exists and you have sufficient privileges';
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- NEW: Utility functions for easier management
CREATE OR REPLACE FUNCTION public.c77_rbac_get_user_roles(p_external_id TEXT)
RETURNS TABLE(role_name TEXT, scope_type TEXT, scope_id TEXT, assigned_at TIMESTAMP) AS $$
BEGIN
    RETURN QUERY
    SELECT r.name, sr.scope_type, sr.scope_id, sr.created_at
    FROM public.c77_rbac_subjects s
    JOIN public.c77_rbac_subject_roles sr ON s.subject_id = sr.subject_id
    JOIN public.c77_rbac_roles r ON sr.role_id = r.role_id
    WHERE s.external_id = p_external_id
    ORDER BY sr.created_at DESC;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER STABLE;

CREATE OR REPLACE FUNCTION public.c77_rbac_get_role_features(p_role_name TEXT)
RETURNS TABLE(feature_name TEXT, granted_at TIMESTAMP) AS $$
BEGIN
    RETURN QUERY
    SELECT f.name, rf.created_at
    FROM public.c77_rbac_roles r
    JOIN public.c77_rbac_role_features rf ON r.role_id = rf.role_id
    JOIN public.c77_rbac_features f ON rf.feature_id = f.feature_id
    WHERE r.name = p_role_name
    ORDER BY f.name;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER STABLE;

-- Enhanced cleanup functions (keep existing ones, add new)
CREATE OR REPLACE FUNCTION public.c77_rbac_remove_all_policies()
RETURNS void AS $$
DECLARE
    policy_record RECORD;
    table_count INTEGER := 0;
BEGIN
    -- Find and remove all c77_rbac policies
    FOR policy_record IN
        SELECT schemaname, tablename, policyname
        FROM pg_policies
        WHERE policyname = 'c77_rbac_policy'
        LOOP
            EXECUTE format('DROP POLICY IF EXISTS %I ON %I.%I',
                           policy_record.policyname,
                           policy_record.schemaname,
                           policy_record.tablename);

            -- Optionally disable RLS on the table
            EXECUTE format('ALTER TABLE %I.%I DISABLE ROW LEVEL SECURITY',
                           policy_record.schemaname,
                           policy_record.tablename);

            table_count := table_count + 1;
            RAISE NOTICE 'Removed policy from %.%', policy_record.schemaname, policy_record.tablename;
        END LOOP;

    RAISE NOTICE 'Removed policies from % tables', table_count;
    RAISE NOTICE 'You can now run: DROP EXTENSION c77_rbac CASCADE;';
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE OR REPLACE FUNCTION public.c77_rbac_cleanup_for_removal(
    p_remove_data BOOLEAN DEFAULT false
)
RETURNS void AS $$
BEGIN
    -- First remove all policies
    PERFORM public.c77_rbac_remove_all_policies();

    -- Optionally clear all RBAC data
    IF p_remove_data THEN
        -- Clear in correct order due to foreign keys
        DELETE FROM public.c77_rbac_subject_roles;
        DELETE FROM public.c77_rbac_role_features;
        DELETE FROM public.c77_rbac_subjects;
        DELETE FROM public.c77_rbac_roles;
        DELETE FROM public.c77_rbac_features;

        RAISE NOTICE 'Cleared all RBAC data';
    END IF;

    RAISE NOTICE 'Cleanup complete. You can now run: DROP EXTENSION c77_rbac CASCADE;';
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE OR REPLACE FUNCTION public.c77_rbac_show_dependencies()
RETURNS TABLE(
    dependency_type TEXT,
    schema_name TEXT,
    object_name TEXT,
    details TEXT
) AS $$
BEGIN
    RETURN QUERY
        SELECT * FROM (
                          -- Find policies
                          SELECT
                              'POLICY'::TEXT as dependency_type,
                              schemaname::TEXT as schema_name,
                              tablename::TEXT as object_name,
                              policyname::TEXT as details
                          FROM pg_policies
                          WHERE policyname = 'c77_rbac_policy'

                          UNION ALL

                          -- Find tables with RLS enabled
                          SELECT
                              'RLS_ENABLED'::TEXT as dependency_type,
                              schemaname::TEXT as schema_name,
                              tablename::TEXT as object_name,
                              'Row Level Security is enabled'::TEXT as details
                          FROM pg_tables
                          WHERE rowsecurity = true
                            AND (schemaname, tablename) IN (
                              SELECT schemaname, tablename
                              FROM pg_policies
                              WHERE policyname = 'c77_rbac_policy'
                          )

                          UNION ALL

                          -- Find stored procedures that might use c77_rbac functions
                          SELECT DISTINCT
                              'FUNCTION'::TEXT as dependency_type,
                              n.nspname::TEXT as schema_name,
                              p.proname::TEXT as object_name,
                              'May reference c77_rbac functions'::TEXT as details
                          FROM pg_proc p
                                   JOIN pg_namespace n ON p.pronamespace = n.oid
                          WHERE p.prosrc LIKE '%c77_rbac%'
                            AND n.nspname NOT IN ('pg_catalog', 'information_schema')
                            AND p.proname NOT LIKE 'c77_rbac%'
                      ) AS dependencies
        ORDER BY dependency_type, schema_name, object_name;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Grant permissions for all functions
GRANT EXECUTE ON FUNCTION
    public.c77_rbac_assign_subject(TEXT, TEXT, TEXT, TEXT),
    public.c77_rbac_grant_feature(TEXT, TEXT),
    public.c77_rbac_can_access(TEXT, TEXT, TEXT, TEXT),
    public.c77_rbac_can_access_optimized(TEXT, TEXT, TEXT, TEXT),
    public.c77_rbac_apply_policy(TEXT, TEXT, TEXT, TEXT),
    public.c77_rbac_bulk_assign_subjects(TEXT[], TEXT, TEXT, TEXT),
    public.c77_rbac_revoke_subject_role(TEXT, TEXT, TEXT, TEXT),
    public.c77_rbac_revoke_feature(TEXT, TEXT),
    public.c77_rbac_sync_admin_features(),
    public.c77_rbac_sync_global_admin_features(),
    public.c77_rbac_get_user_roles(TEXT),
    public.c77_rbac_get_role_features(TEXT),
    public.c77_rbac_remove_all_policies(),
    public.c77_rbac_cleanup_for_removal(BOOLEAN),
    public.c77_rbac_show_dependencies()
    TO PUBLIC;

-- Grant table permissions (read-only, modifications through functions only)
GRANT SELECT ON
    public.c77_rbac_subjects,
    public.c77_rbac_roles,
    public.c77_rbac_features,
    public.c77_rbac_subject_roles,
    public.c77_rbac_role_features
    TO PUBLIC;

-- Explicitly revoke direct modification access
REVOKE INSERT, UPDATE, DELETE ON
    public.c77_rbac_subjects,
    public.c77_rbac_roles,
    public.c77_rbac_features,
    public.c77_rbac_subject_roles,
    public.c77_rbac_role_features
    FROM PUBLIC;

-- Create view for easier role management and reporting
CREATE OR REPLACE VIEW public.c77_rbac_user_permissions AS
SELECT 
    s.external_id,
    r.name as role_name,
    f.name as feature_name,
    sr.scope_type,
    sr.scope_id,
    sr.created_at as role_assigned_at,
    rf.created_at as feature_granted_at
FROM public.c77_rbac_subjects s
JOIN public.c77_rbac_subject_roles sr ON s.subject_id = sr.subject_id
JOIN public.c77_rbac_roles r ON sr.role_id = r.role_id
JOIN public.c77_rbac_role_features rf ON r.role_id = rf.role_id
JOIN public.c77_rbac_features f ON rf.feature_id = f.feature_id
ORDER BY s.external_id, sr.scope_type, sr.scope_id, f.name;

GRANT SELECT ON public.c77_rbac_user_permissions TO PUBLIC;

-- Create summary view for administrators
CREATE OR REPLACE VIEW public.c77_rbac_summary AS
SELECT 
    'Subjects' as object_type,
    count(*)::text as count,
    'Total users in RBAC system' as description
FROM public.c77_rbac_subjects
UNION ALL
SELECT 
    'Roles' as object_type,
    count(*)::text as count,
    'Total roles defined' as description
FROM public.c77_rbac_roles
UNION ALL
SELECT 
    'Features' as object_type,
    count(*)::text as count,
    'Total features/permissions defined' as description
FROM public.c77_rbac_features
UNION ALL
SELECT 
    'Role Assignments' as object_type,
    count(*)::text as count,
    'Total role assignments to users' as description
FROM public.c77_rbac_subject_roles
UNION ALL
SELECT 
    'Feature Grants' as object_type,
    count(*)::text as count,
    'Total features granted to roles' as description
FROM public.c77_rbac_role_features
UNION ALL
SELECT 
    'Active Policies' as object_type,
    count(*)::text as count,
    'Tables with c77_rbac policies applied' as description
FROM pg_policies 
WHERE policyname = 'c77_rbac_policy';

GRANT SELECT ON public.c77_rbac_summary TO PUBLIC;