# Multi-Organization Support - Database Changes

## Overview
The database schema has been updated to support multi-organization/multi-tenancy functionality. All tables now include an `organization_id` column to enable complete data isolation between different organizations.

## Changes Made

### 1. New Table: `organizations`
A new top-level table has been created to manage organizations:

```sql
CREATE TABLE `organizations` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `code` varchar(50) NOT NULL,
  `description` text,
  `address` text,
  `phone` varchar(20) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `website` varchar(255) DEFAULT NULL,
  `logo` varchar(500) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`),
  UNIQUE KEY `name` (`name`),
  KEY `idx_name` (`name`),
  KEY `idx_code` (`code`),
  KEY `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
```

### 2. Tables Modified with `organization_id`
All the following tables have been updated to include `organization_id`:

#### Core Tables
- `users` - Added organization_id with foreign key constraint
- `roles` - Added organization_id with foreign key constraint
- `permissions` - Added organization_id with foreign key constraint
- `role_permissions` - Added organization_id with foreign key constraint
- `user_permissions` - Added organization_id with foreign key constraint

#### Organizational Structure Tables
- `units` - Added organization_id with foreign key constraint
- `departments` - Added organization_id with foreign key constraint
- `designations` - Added organization_id with foreign key constraint
- `ranks` - Added organization_id with foreign key constraint

#### Communication & Messaging Tables
- `conversations` - Added organization_id with foreign key constraint
- `conversation_participants` - Added organization_id with foreign key constraint
- `messages` - Added organization_id with foreign key constraint
- `notifications` - Added organization_id with foreign key constraint

#### System Tables
- `audit_logs` - Added organization_id with foreign key constraint
- `feedback` - Added organization_id with foreign key constraint
- `system_settings` - Added organization_id with foreign key constraint
- `user_preferences` - Added organization_id with foreign key constraint
- `user_sessions` - Added organization_id with foreign key constraint

### 3. Unique Constraints Updated
Several unique constraints have been updated to be organization-specific:

- `users.org_service_no` - (organization_id, service_no)
- `users.org_email` - (organization_id, email)
- `units.org_code` - (organization_id, code)
- `departments.org_name` - (organization_id, name)
- `roles.org_name` - (organization_id, name)
- `ranks.org_name` - (organization_id, name)
- `permissions.unique_permission` - (organization_id, resource, action)
- `designations.org_name_department_parent` - (organization_id, name, department_id, parent_id)
- `system_settings.org_setting_key` - (organization_id, setting_key)

### 4. Stored Procedures Updated

#### CreateUser
Updated to include `organization_id` parameter:
```sql
CREATE PROCEDURE `CreateUser`(
    IN p_organization_id INT,
    IN p_name VARCHAR(255),
    IN p_rank_id INT,
    -- ... other parameters
)
```

#### CreateDirectConversation
Updated to include `organization_id` parameter:
```sql
CREATE PROCEDURE `CreateDirectConversation`(
    IN p_organization_id INT,
    IN p_user1_id INT,
    IN p_user2_id INT
)
```

### 5. Sample Data Added
A sample organization has been added:
```sql
INSERT INTO `organizations` VALUES
(1, 'Bangladesh Navy', 'BN', 'Bangladesh Navy - Naval Force of Bangladesh', 
'Naval Headquarters, Dhaka Cantonment, Dhaka-1206, Bangladesh', 
'+88-02-8754041', 'info@navy.mil.bd', 'https://www.navy.mil.bd');
```

All existing sample data (roles, permissions, units, departments, ranks, designations, users) has been updated to reference `organization_id = 1`.

## Database Hierarchy

The updated organizational hierarchy is now:

```
Organizations (top-level)
  └── Units
      └── Departments
          └── Designations
              └── Users
                  └── Users (unlimited nesting via parent_id)
```

## Foreign Key Relationships

All tables with `organization_id` have foreign key constraints:
```sql
CONSTRAINT table_name_ibfk_N FOREIGN KEY (`organization_id`) 
  REFERENCES `organizations` (`id`) ON DELETE CASCADE
```

This ensures:
- Data integrity across organizations
- Automatic cleanup when an organization is deleted
- Prevention of orphaned records

## Migration Notes

### For Existing Databases
If you have an existing database, you will need to:
1. Create the `organizations` table
2. Add a default organization
3. Add `organization_id` column to all tables
4. Populate `organization_id` with the default organization ID
5. Update foreign key constraints
6. Update unique constraints
7. Update stored procedures

### For New Installations
Simply run the updated `database_schema.sql` file to create the complete multi-organization structure.

## Application Code Updates Required

The following areas of your application code will need updates:

1. **Authentication & Session Management**
   - Store organization_id in user session
   - Filter all queries by organization_id

2. **API Endpoints**
   - Add organization_id to all data queries
   - Validate user belongs to the organization they're accessing

3. **Data Creation**
   - Include organization_id when creating new records
   - Ensure organization_id matches current user's organization

4. **Data Retrieval**
   - Add WHERE organization_id = ? to all queries
   - Prevent cross-organization data access

5. **Stored Procedure Calls**
   - Update calls to CreateUser and CreateDirectConversation
   - Pass organization_id parameter

## Security Considerations

1. **Data Isolation**: Always filter queries by organization_id
2. **Authorization**: Verify user's organization before any operation
3. **Cross-Organization Access**: Implement strict checks to prevent unauthorized access
4. **Organization Switching**: If allowing users to belong to multiple organizations, implement proper context switching

## Benefits

1. **Multi-Tenancy**: Support multiple independent organizations in a single database
2. **Data Isolation**: Complete separation of data between organizations
3. **Scalability**: Easy to add new organizations without schema changes
4. **Security**: Foreign key constraints ensure data integrity
5. **Flexibility**: Each organization can have its own hierarchical structure

## Example Queries

### Getting users for a specific organization:
```sql
SELECT * FROM users WHERE organization_id = 1;
```

### Creating a new user:
```sql
CALL CreateUser(1, 'John Doe', 1, 'SN123', 1, 1, 3, 1, '+123456', ...);
```

### Creating a conversation:
```sql
CALL CreateDirectConversation(1, 123, 456);
```

## Future Enhancements

Consider implementing:
1. Organization-level settings and configurations
2. Organization-level branding and customization
3. Cross-organization reporting (for super admins)
4. Organization billing and subscription management
5. Organization-level user limits and quotas

## Support

For questions or issues related to these changes, please refer to the main project documentation or contact the development team.

---
**Date**: October 20, 2025
**Version**: 1.0
**Author**: Database Schema Update