# Organization Schema Migration Guide

## Overview

The database schema has been updated to support **multi-organization functionality**. A new `organizations` table has been created, and `organization_id` foreign keys have been added to all relevant tables.

## Changes Summary

### 1. New Table: `organizations`

**Purpose**: Root table for managing multiple organizations

**Schema**:
```sql
CREATE TABLE organizations (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  code VARCHAR(50) NOT NULL UNIQUE,
  type VARCHAR(100),
  description TEXT,
  address TEXT,
  phone VARCHAR(20),
  email VARCHAR(255),
  website VARCHAR(255),
  logo VARCHAR(500),
  is_active TINYINT(1) DEFAULT 1,
  created_by INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
```

**Default Data**:
- ID: 1
- Name: Bangladesh Navy
- Code: BN
- Type: Military

### 2. Tables Modified with `organization_id`

The following tables now include `organization_id INT NOT NULL DEFAULT 1` with foreign key constraints:

| Table | Purpose | Foreign Key |
|-------|---------|-------------|
| `units` | Organizational units | → organizations(id) |
| `departments` | Departments within units | → organizations(id) |
| `designations` | Job designations | → organizations(id) |
| `ranks` | Military ranks | → organizations(id) |
| `roles` | User roles | → organizations(id) |
| `users` | System users (already had it) | → organizations(id) |
| `conversations` | Messaging conversations | → organizations(id) |
| `notifications` | User notifications | → organizations(id) |
| `feedback` | User feedback | → organizations(id) |
| `audit_logs` | Activity logs | → organizations(id) |

### 3. Indexes Added

All tables with `organization_id` now have an index:
```sql
KEY `idx_organization_id` (`organization_id`)
```

## Migration Steps

### Step 1: Backup Current Database

```bash
# Create backup before migration
mysqldump -u root -p cms_db > cms_db_backup_before_org_migration.sql
```

### Step 2: Drop and Recreate Database

```bash
# Drop existing database
mysql -u root -p -e "DROP DATABASE IF EXISTS cms_db;"

# Create fresh database from new schema
mysql -u root -p < database_schema.sql
```

### Step 3: Restore User Data (if needed)

If you need to preserve existing user data, extract and reimport:

```sql
-- Extract users from backup
SELECT * FROM users INTO OUTFILE 'users_backup.csv';

-- Import into new schema (organization_id will default to 1)
LOAD DATA INFILE 'users_backup.csv' INTO TABLE users;
```

## Organizational Hierarchy

```
organizations (root)
  ├── units
  │    └── departments
  │         └── users (assigned to departments)
  ├── ranks (available ranks within org)
  ├── roles (available roles within org)
  │    └── users (assigned to roles)
  ├── conversations (org-specific messaging)
  ├── notifications (org-specific alerts)
  ├── feedback (org-specific feedback)
  └── audit_logs (org-specific activity tracking)
```

## Application Updates Needed

### Backend Changes Required

1. **Models**: Update all models to include organization_id filtering
   ```javascript
   // Example: Update Contact.findAll()
   static async findAll(options = {}) {
     const { organization_id = 1, ...otherOptions } = options;
     // Include organization_id in WHERE clause
   }
   ```

2. **Controllers**: Add organization context to queries
   ```javascript
   // Get organization_id from authenticated user
   const organizationId = req.user.organization_id || 1;
   ```

3. **Middleware**: Add organization validation
   ```javascript
   // Ensure users can only access their organization's data
   const organizationMiddleware = (req, res, next) => {
     req.organizationId = req.user.organization_id;
     next();
   };
   ```

### Frontend Changes Required

1. **API Calls**: Include organization context in requests
2. **Admin Interface**: Add organization selector for super admins
3. **Filters**: Add organization filter to list views

## Benefits

1. **Multi-Tenancy**: Support multiple military organizations in one database
2. **Data Isolation**: Each organization's data is logically separated
3. **Scalability**: Easy to add new organizations without schema changes
4. **Security**: Organization-level access control
5. **Flexibility**: Organizations can have their own ranks, roles, units

## Default Behavior

- All existing data belongs to organization_id = 1 (Bangladesh Navy)
- New records will automatically use organization_id = 1 if not specified
- No code changes required for single-organization deployments

## Example: Adding a New Organization

```sql
-- Add a new organization
INSERT INTO organizations (name, code, type, description) VALUES
  ('Bangladesh Army', 'BA', 'Military', 'Army Forces of Bangladesh');

-- Add army-specific ranks
INSERT INTO ranks (name, organization_id, level, description) VALUES
  ('Lieutenant General', 2, 1, 'Three-star general'),
  ('Major General', 2, 2, 'Two-star general'),
  ('Brigadier General', 2, 3, 'One-star general');

-- Add army-specific units
INSERT INTO units (name, code, organization_id) VALUES
  ('Army Headquarters', 'AHQ', 2);
```

## Rollback Plan

If issues occur:

```bash
# Restore from backup
mysql -u root -p -e "DROP DATABASE IF EXISTS cms_db;"
mysql -u root -p -e "CREATE DATABASE cms_db;"
mysql -u root -p cms_db < cms_db_backup_before_org_migration.sql
```

## Testing Checklist

- [ ] Database creation successful
- [ ] All foreign keys created properly
- [ ] Existing data preserved with organization_id = 1
- [ ] User login works correctly
- [ ] Contact directory displays data
- [ ] Dashboard stats load properly
- [ ] Messaging system works
- [ ] Audit logs capture organization context

## Notes

- The `users` table already had `organization_id` - no changes needed there
- All INSERT statements updated to include organization_id = 1
- All 126 designation records updated successfully
- All constraint names follow pattern: `tablename_ibfk_X`

## Support

For issues or questions about this migration:
1. Check the SQL error logs
2. Verify foreign key constraints
3. Ensure all DEFAULT values are correct
4. Test with a fresh database first

---
**Migration Date**: October 20, 2025  
**Schema Version**: 2.0 (Multi-Organization Support)  
**Default Organization**: Bangladesh Navy (ID: 1)

