# Organization 2 Migration Guide

## Overview

This migration script adds a complete second organization (Bangladesh Army) to the CMS database with all required supporting data.

## What Gets Created

### 1. Organization (ID: 2)
- **Name**: Bangladesh Army
- **Code**: BA
- **Type**: Military
- **Contact**: info@army.mil.bd

### 2. Ranks (IDs: 18-21)
- Colonel (Level 5)
- Lieutenant Colonel (Level 6)
- Major (Level 7)
- Captain (Level 8)

### 3. Roles (IDs: 7-9)
- Army Super Admin (Level 100)
- Army Admin (Level 50)
- Army User (Level 10)

### 4. Units (IDs: 11-12)
- **Army Headquarters (AHQ)** - Main headquarters
- **Dhaka Cantonment (DC)** - Sub-unit under AHQ

### 5. Departments (IDs: 80-82)
- **Army Operations** - Under Army Headquarters
- **Army Administration** - Under Army Headquarters
- **Army Logistics** - Under Dhaka Cantonment

### 6. Designations (IDs: 127-130)
- Chief of Army Staff (Operations Department)
- Director Operations (Operations Department, under Chief)
- Admin Officer (Administration Department)
- Logistics Officer (Logistics Department)

### 7. Users (IDs: 200-202)
Three sample users with different roles:

| ID  | Name              | Login        | Email                    | Role             | Unit               | Department         |
|-----|-------------------|--------------|--------------------------|------------------|--------------------|-------------------|
| 200 | Army Super Admin  | army_sadmin  | army_admin@army.mil.bd  | Army Super Admin | Army Headquarters  | Army Operations   |
| 201 | Army Admin User   | army_admin   | admin@army.mil.bd       | Army Admin       | Army Headquarters  | Army Administration |
| 202 | Army Regular User | army_user    | user@army.mil.bd        | Army User        | Dhaka Cantonment   | Army Logistics    |

**Default Password**: `admin123` (for all users)

## How to Run the Migration

### Option 1: Using MySQL Command Line

```bash
cd backend/database
mysql -u root -p cms_db < migration_add_org2_data.sql
```

### Option 2: Using MySQL Workbench

1. Open MySQL Workbench
2. Connect to your database
3. Open the file: `backend/database/migration_add_org2_data.sql`
4. Execute the entire script

### Option 3: Using Command Prompt (Windows)

```cmd
cd backend\database
mysql -u root -p cms_db < migration_add_org2_data.sql
```

## Verification

After running the migration, verify the data was created successfully:

### Check Organization

```sql
SELECT * FROM organizations WHERE id = 2;
```

### Check All Related Data

```sql
-- Check units
SELECT * FROM units WHERE organization_id = 2;

-- Check departments
SELECT * FROM departments WHERE organization_id = 2;

-- Check users
SELECT * FROM users WHERE organization_id = 2;
```

### Full User Details with Relationships

```sql
SELECT 
    u.id, 
    u.name, 
    u.login_id, 
    u.email, 
    u.service_no,
    o.name as organization_name,
    un.name as unit_name,
    d.name as department_name,
    des.name as designation_name,
    r.name as rank_name,
    ro.name as role_name
FROM users u
LEFT JOIN organizations o ON u.organization_id = o.id
LEFT JOIN units un ON u.unit_id = un.id
LEFT JOIN departments d ON u.department_id = d.id
LEFT JOIN designations des ON u.designation_id = des.id
LEFT JOIN ranks r ON u.rank_id = r.id
LEFT JOIN roles ro ON u.role_id = ro.id
WHERE u.organization_id = 2;
```

### Expected Result

You should see 3 users with complete organizational hierarchy:

```
+-----+-------------------+-------------+------------------------+-----------+------------------+-------------------+---------------------+----------------------+-----------+------------------+
| id  | name              | login_id    | email                  | service_no| organization_name| unit_name         | department_name     | designation_name     | rank_name | role_name        |
+-----+-------------------+-------------+------------------------+-----------+------------------+-------------------+---------------------+----------------------+-----------+------------------+
| 200 | Army Super Admin  | army_sadmin | army_admin@army.mil.bd | BA20001   | Bangladesh Army  | Army Headquarters | Army Operations     | Chief of Army Staff  | Colonel   | Army Super Admin |
| 201 | Army Admin User   | army_admin  | admin@army.mil.bd      | BA20002   | Bangladesh Army  | Army Headquarters | Army Administration | Admin Officer        | Lt Colonel| Army Admin       |
| 202 | Army Regular User | army_user   | user@army.mil.bd       | BA20003   | Bangladesh Army  | Dhaka Cantonment  | Army Logistics      | Logistics Officer    | Major     | Army User        |
+-----+-------------------+-------------+------------------------+-----------+------------------+-------------------+---------------------+----------------------+-----------+------------------+
```

## Testing Login

You can test logging in with any of the created users:

```bash
# Test using the debug auth page or your application's login
Email: army_admin@army.mil.bd
Password: admin123
```

## Data Structure

The migration follows this hierarchy:

```
Bangladesh Army (Organization ID: 2)
│
├── Army Headquarters (Unit ID: 11)
│   ├── Army Operations (Department ID: 80)
│   │   ├── Chief of Army Staff (Designation ID: 127)
│   │   │   └── Army Super Admin (User ID: 200)
│   │   └── Director Operations (Designation ID: 128)
│   │
│   └── Army Administration (Department ID: 81)
│       └── Admin Officer (Designation ID: 129)
│           └── Army Admin User (User ID: 201)
│
└── Dhaka Cantonment (Unit ID: 12) [child of Army Headquarters]
    └── Army Logistics (Department ID: 82)
        └── Logistics Officer (Designation ID: 130)
            └── Army Regular User (User ID: 202)
```

## Rollback

If you need to remove this data:

```sql
-- Delete in reverse order due to foreign key constraints
DELETE FROM users WHERE organization_id = 2;
DELETE FROM designations WHERE organization_id = 2;
DELETE FROM departments WHERE organization_id = 2;
DELETE FROM units WHERE organization_id = 2;
DELETE FROM roles WHERE organization_id = 2;
DELETE FROM ranks WHERE organization_id = 2;
DELETE FROM organizations WHERE id = 2;
```

## Notes

- All IDs are explicitly set to avoid conflicts with existing data
- The migration uses `INSERT IGNORE` to prevent errors if data already exists
- Password hashes use bcrypt with 10 rounds (same as existing users)
- Foreign key checks are temporarily disabled during migration
- All timestamps are set to October 25, 2025

## Customization

To modify this migration for your needs:

1. **Change Organization**: Edit the organization INSERT statement (line 22)
2. **Add More Units**: Add additional unit INSERT statements (lines 49-50)
3. **Add More Departments**: Add additional department INSERT statements (lines 57-59)
4. **Add More Users**: Add additional user INSERT statements (lines 79-81)

## Troubleshooting

### Error: Duplicate entry

**Cause**: The data already exists in the database.

**Solution**: The script uses `INSERT IGNORE` which should skip duplicates. If you want to update existing data, use `REPLACE INTO` instead of `INSERT IGNORE`.

### Error: Foreign key constraint fails

**Cause**: Referenced data (organization, unit, department, etc.) doesn't exist.

**Solution**: Ensure you run the entire script from top to bottom. The script is ordered to create dependencies first.

### Error: Cannot add or update a child row

**Cause**: The organization with ID 2 doesn't exist when trying to add related data.

**Solution**: Make sure the organizations table INSERT (Step 1) executes successfully first.

## Support

For issues or questions:
- Check the verification queries above
- Review the main database schema: `database_schema.sql`
- Refer to the organization migration guide: `ORGANIZATION_SCHEMA_MIGRATION_GUIDE.md`

