-- c77_rbac--1.0--1.1.sql: Upgrade script from version 1.0 to 1.1
-- Adds bulk operations, removal functions, admin sync, and enhanced error handling
\echo Upgrading c77_rbac from 1.0 to 1.1...

-- Add timestamps to existing tables
ALTER TABLE public.c77_rbac_subject_roles 
ADD COLUMN IF NOT EXISTS created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE public.c77_rbac_role_features 
ADD COLUMN IF NOT EXISTS created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

-- Add new performance indexes
CREATE INDEX IF NOT EXISTS idx_c77_rbac_subjects_external_id_hash 
ON public.c77_rbac_subjects USING hash(external_id);

CREATE INDEX IF NOT EXISTS idx_c77_rbac_subject_roles_composite 
ON public.c77_rbac_subject_roles(subject_id, scope_type, scope_id);

-- Replace existing functions with enhanced versions
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;

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;

-- Add new bulk assignment function
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;

-- Add removal functions
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;

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;

-- Add admin sync functions
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;

-- Add optimized performance function
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;

-- Update the original function to use optimized version
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;

-- Add utility functions
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;

-- Enhance the apply_policy function with 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;

-- Create helpful views
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;

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 permissions for new functions
GRANT EXECUTE ON FUNCTION
    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_can_access_optimized(TEXT, TEXT, TEXT, TEXT),
    public.c77_rbac_get_user_roles(TEXT),
    public.c77_rbac_get_role_features(TEXT)
    TO PUBLIC;

-- Grant permissions for new views
GRANT SELECT ON 
    public.c77_rbac_user_permissions,
    public.c77_rbac_summary
    TO PUBLIC;

\echo c77_rbac successfully upgraded to version 1.1