# Roles and Permissions Structure

## Overview
The `roles` and `permissions` tables are **GLOBAL** tables shared across all organizations. They do NOT have `organization_id` columns.

## Why Global?

### 1. **Standardization**
- All organizations use the same set of roles (Super_admin, Admin, User, etc.)
- Consistent permission structure across the entire system
- Easier to maintain and update system-wide permissions

### 2. **Simplified Management**
- One place to manage all roles and permissions
- Changes to permissions apply system-wide
- No duplication of role/permission data

### 3. **Security**
- Centralized permission definitions
- Reduces risk of permission inconsistencies between organizations
- Easier to audit and review security settings

## Database Structure

### Global Tables (No organization_id)
```sql
-- roles: System-wide user roles
CREATE TABLE `roles` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `description` text,
  `level` int NOT NULL DEFAULT '0',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
);

-- permissions: System-wide permissions
CREATE TABLE `permissions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `description` text,
  `resource` varchar(100) NOT NULL,
  `action` varchar(50) NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_permission` (`resource`,`action`)
);

-- role_permissions: Maps permissions to roles (global)
CREATE TABLE `role_permissions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `role_id` int NOT NULL,
  `permission_id` int NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_role_permission` (`role_id`,`permission_id`)
);
```

### Organization-Specific Tables (With organization_id)
```sql
-- users: Belong to organizations and have global roles
CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `organization_id` int NOT NULL,  -- Organization-specific
  `role_id` int NOT NULL,           -- References global role
  `name` varchar(255) NOT NULL,
  -- ... other fields
  CONSTRAINT `users_ibfk_5` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`),
  CONSTRAINT `users_ibfk_7` FOREIGN KEY (`organization_id`) REFERENCES `organizations` (`id`)
);

-- user_permissions: Optional custom permissions per user
CREATE TABLE `user_permissions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `permission_id` int NOT NULL,  -- References global permission
  `granted_by` int DEFAULT NULL
);
```

## How It Works

### 1. **Role Assignment**
```
Organization 1 (Bangladesh Navy)
  └── User A (role_id = 1, "Super_admin") → Uses global role definition

Organization 2 (Bangladesh Army)
  └── User B (role_id = 1, "Super_admin") → Uses same global role definition
```

### 2. **Permission Checking**
```sql
-- Get user's permissions from their role
SELECT p.* 
FROM users u
JOIN roles r ON u.role_id = r.id
JOIN role_permissions rp ON r.id = rp.role_id
JOIN permissions p ON rp.permission_id = p.id
WHERE u.id = ? AND u.organization_id = ?;

-- Get user's custom permissions
SELECT p.*
FROM user_permissions up
JOIN permissions p ON up.permission_id = p.id
WHERE up.user_id = ?;
```

### 3. **Data Isolation**
Even though roles and permissions are global, **data access is still isolated** by organization:
- Users belong to specific organizations via `organization_id`
- All data tables (units, departments, messages, etc.) have `organization_id`
- Queries always filter by `organization_id` to ensure data isolation

## Standard Roles

| Role ID | Name | Level | Description |
|---------|------|-------|-------------|
| 1 | Super_admin | 100 | Full system access across all organizations |
| 2 | Admin | 50 | Organization administrator with elevated privileges |
| 3 | User | 10 | Regular user with standard access |

## Permission Categories

### User Management
- user.create, user.read, user.update, user.delete
- user.manage_roles

### Organizational Structure
- unit.*, department.*, designation.*, rank.*

### Communication
- message.*, conversation.*, notification.*

### System
- feedback.*, audit_log.*, system_settings.*, dashboard.*

## Benefits of Global Roles/Permissions

### 1. **Consistency**
- Same permission structure across all organizations
- Predictable behavior for system administrators
- Easier documentation and training

### 2. **Maintainability**
- Single source of truth for permissions
- Update permissions in one place
- No need to sync changes across organizations

### 3. **Flexibility**
- Organizations can still have different user hierarchies
- Custom permissions can be added per user if needed
- Organization-specific data remains isolated

### 4. **Scalability**
- Adding new organizations doesn't require duplicating roles/permissions
- Faster onboarding of new organizations
- Reduced database size and complexity

## Example: Multi-Organization Usage

```sql
-- Organization 1: Bangladesh Navy
INSERT INTO organizations VALUES (1, 'Bangladesh Navy', 'BN', ...);
INSERT INTO users VALUES (1, 1, 'Admiral Name', 1, 1, ...);  -- org_id=1, role_id=1

-- Organization 2: Bangladesh Army
INSERT INTO organizations VALUES (2, 'Bangladesh Army', 'BA', ...);
INSERT INTO users VALUES (2, 2, 'General Name', 1, 2, ...);  -- org_id=2, role_id=1

-- Both users have role_id=1 (Super_admin) but belong to different organizations
-- They have the same permissions but can only access their organization's data
```

## When to Use Custom User Permissions

The `user_permissions` table allows granting individual permissions to users outside their role:

```sql
-- Grant a specific user additional permission
INSERT INTO user_permissions (user_id, permission_id, granted_by)
VALUES (123, 45, 1);  -- Grant permission_id 45 to user 123
```

Use cases:
- Temporary elevated access
- Special project permissions
- User-specific capabilities
- Override role limitations

## Migration from Organization-Specific Roles

If you previously had organization-specific roles, you can migrate by:

1. **Remove organization_id from roles/permissions tables**
```sql
ALTER TABLE roles DROP COLUMN organization_id;
ALTER TABLE permissions DROP COLUMN organization_id;
```

2. **Update role/permission assignments**
```sql
-- Consolidate duplicate roles across organizations
-- Map organization-specific role IDs to global role IDs
```

3. **Update application queries**
```sql
-- Remove organization_id filter from role/permission queries
-- Keep organization_id filter on all data access queries
```

## Security Considerations

1. **Data Isolation**: Even with global roles, maintain strict `organization_id` filtering
2. **Super Admin Access**: Super_admin can potentially access all organizations
3. **Permission Enforcement**: Always check both role permissions and user permissions
4. **Audit Logging**: Log all permission changes and role assignments with `organization_id`

## Conclusion

Global roles and permissions provide:
- ✅ Consistent permission structure
- ✅ Easier maintenance
- ✅ Better scalability
- ✅ Reduced complexity
- ✅ Data isolation through organization_id on data tables

The key is: **Roles and permissions are global, but data access is organization-specific.**

---
**Last Updated**: October 20, 2025
**Database Version**: 2.0 (Multi-Organization with Global Roles)