-- Example SQL for comprehensive permission management

-- 1. Create role-based permission tables (extend your schema)
CREATE TABLE IF NOT EXISTS user_roles (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id VARCHAR(255) NOT NULL,
    role_name VARCHAR(100) NOT NULL,
    app_id VARCHAR(255) REFERENCES applications(app_id) ON DELETE CASCADE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255) NOT NULL,
    UNIQUE(user_id, role_name, app_id)
);

CREATE TABLE IF NOT EXISTS role_permissions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    role_name VARCHAR(100) NOT NULL,
    permission_id UUID NOT NULL REFERENCES available_permissions(id) ON DELETE CASCADE,
    app_id VARCHAR(255) REFERENCES applications(app_id) ON DELETE CASCADE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255) NOT NULL,
    UNIQUE(role_name, permission_id, app_id)
);

-- 2. Define standard roles
INSERT INTO role_permissions (role_name, permission_id, app_id, created_by)
SELECT 'admin', id, NULL, 'system' 
FROM available_permissions 
WHERE scope LIKE 'internal.%';

INSERT INTO role_permissions (role_name, permission_id, app_id, created_by)
SELECT 'app_manager', id, NULL, 'system'
FROM available_permissions 
WHERE scope LIKE 'app.%' OR scope LIKE 'token.%';

INSERT INTO role_permissions (role_name, permission_id, app_id, created_by)
SELECT 'read_only', id, NULL, 'system'
FROM available_permissions 
WHERE scope LIKE '%.read';

-- 3. Assign users to roles
INSERT INTO user_roles (user_id, role_name, created_by) VALUES
('admin@example.com', 'admin', 'system'),
('test@example.com', 'app_manager', 'system'),
('limited@example.com', 'read_only', 'system');

-- 4. Query user permissions via roles
SELECT DISTINCT ur.user_id, ap.scope, ap.name
FROM user_roles ur
JOIN role_permissions rp ON ur.role_name = rp.role_name
JOIN available_permissions ap ON rp.permission_id = ap.id
WHERE ur.user_id = 'admin@example.com';