-- ========================================================================
-- MIGRATION SCRIPT: Add Organization 2 with Unit, Department, and User
-- ========================================================================
-- This script creates a new organization (organization_id = 2) along with
-- the required data: unit, department, designation, role, rank, and user.
--
-- Run this script to populate data for a second organization.
--
-- Date: October 25, 2025
-- Version: 1.0
-- ========================================================================

USE cms_db;

-- Disable foreign key checks temporarily


-- ========================================================================
-- STEP 2: Create Rank for Organization 2
-- ========================================================================

INSERT IGNORE INTO `ranks` (`name`, `organization_id`, `level`, `description`, `is_active`, `created_at`, `updated_at`) VALUES
	('Colonel', 2, 5, 'Senior Police Officer', 1, '2025-10-25 00:00:00', '2025-10-25 00:00:00'),
	('Lieutenant Colonel', 2, 6, 'Police Field Officer', 1, '2025-10-25 00:00:00', '2025-10-25 00:00:00'),
	('Major', 2, 7, 'Police Officer', 1, '2025-10-25 00:00:00', '2025-10-25 00:00:00'),
	('Captain', 2, 8, 'Police Junior Officer', 1, '2025-10-25 00:00:00', '2025-10-25 00:00:00');

-- ========================================================================
-- STEP 3: Create Role for Organization 2
-- ========================================================================

INSERT IGNORE INTO `roles` (`name`, `organization_id`, `description`, `level`, `is_active`, `created_at`, `updated_at`) VALUES
	('Police Super Admin', 2, 'Super Administrator for Bangladesh Police with full system access', 100, 1, '2025-10-25 00:00:00', '2025-10-25 00:00:00'),
	('Police Admin', 2, 'Administrator for Bangladesh Police with elevated privileges', 50, 1, '2025-10-25 00:00:00', '2025-10-25 00:00:00'),
	('Police User', 2, 'Regular user for Bangladesh Police with standard access', 10, 1, '2025-10-25 00:00:00', '2025-10-25 00:00:00');

-- ========================================================================
-- STEP 4: Create Unit for Organization 2
-- ========================================================================

INSERT IGNORE INTO `units` (`name`, `code`, `description`, `organization_id`, `parent_id`, `order_id`, `is_active`, `created_by`, `created_at`, `updated_at`) VALUES
	('Police Headquarters', 'AHQ', 'Bangladesh Police Headquarters', 2, NULL, 1, 1, NULL, '2025-10-25 00:00:00', '2025-10-25 00:00:00'),
	('Dhaka Cantonment', 'DC', 'Dhaka Cantonment Area', 2, 11, 2, 1, NULL, '2025-10-25 00:00:00', '2025-10-25 00:00:00');

-- ========================================================================
-- STEP 5: Create Department for Organization 2
-- ========================================================================

INSERT IGNORE INTO `departments` (`name`, `organization_id`, `unit_id`, `parent_id`, `order_id`, `description`, `is_active`, `created_at`, `updated_at`) VALUES
	('Police Operations', 2, 11, NULL, 1, 'Operations Department', 1, '2025-10-25 00:00:00', '2025-10-25 00:00:00'),
	('Police Administration', 2, 11, NULL, 2, 'Administration Department', 1, '2025-10-25 00:00:00', '2025-10-25 00:00:00'),
	('Police Logistics', 2, 12, NULL, 3, 'Logistics Department', 1, '2025-10-25 00:00:00', '2025-10-25 00:00:00');

-- ========================================================================
-- STEP 6: Create Designations for Organization 2
-- ========================================================================

INSERT IGNORE INTO `designations` (`name`, `organization_id`, `department_id`, `parent_id`, `order_id`, `description`, `is_active`, `created_at`, `updated_at`) VALUES
	('Chief of Police Staff', 2, 80, NULL, 1, 'Police Chief', 1, '2025-10-25 00:00:00', '2025-10-25 00:00:00'),
	('Director Operations', 2, 80, 127, 2, 'Operations Director', 1, '2025-10-25 00:00:00', '2025-10-25 00:00:00'),
	('Admin Officer', 2, 81, NULL, 1, 'Administration Officer', 1, '2025-10-25 00:00:00', '2025-10-25 00:00:00'),
	('Logistics Officer', 2, 82, NULL, 1, 'Logistics Officer', 1, '2025-10-25 00:00:00', '2025-10-25 00:00:00');



-- ========================================================================
-- STEP 8: Re-enable foreign key checks
-- ========================================================================


-- ========================================================================
-- VERIFICATION QUERIES
-- ========================================================================
-- Run these queries to verify the migration was successful:
--
-- 1. Check organization:
--    SELECT * FROM organizations WHERE id = 2;
--
-- 2. Check units:
--    SELECT * FROM units WHERE organization_id = 2;
--
-- 3. Check departments:
--    SELECT * FROM departments WHERE organization_id = 2;
--
-- 4. Check designations:
--    SELECT * FROM designations WHERE organization_id = 2;
--
-- 5. Check ranks:
--    SELECT * FROM ranks WHERE organization_id = 2;
--
-- 6. Check roles:
--    SELECT * FROM roles WHERE organization_id = 2;
--
-- 7. Check users:
--    SELECT * FROM users WHERE organization_id = 2;
--
-- 8. Full user details with joins:
--    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;
--
-- ========================================================================

-- Migration completed successfully!
-- 
-- Summary of created data for organization_id = 2 (Bangladesh Police):
-- - 1 Organization: Bangladesh Police (ID: 2)
-- - 4 Ranks: Colonel, Lieutenant Colonel, Major, Captain (IDs: 18-21)
-- - 3 Roles: Police Super Admin, Police Admin, Police User (IDs: 7-9)
-- - 2 Units: Police Headquarters, Dhaka Cantonment (IDs: 11-12)
-- - 3 Departments: Police Operations, Police Administration, Police Logistics (IDs: 80-82)
-- - 4 Designations: Chief of Police Staff, Director Operations, Admin Officer, Logistics Officer (IDs: 127-130)
-- - 3 Users: Police Super Admin, Police Admin User, Police Regular User (IDs: 200-202)
--
-- Login Credentials:
-- - Police_admin@Police.mil.bd / admin123 (or password hash used)
-- - admin@Police.mil.bd / admin123
-- - user@Police.mil.bd / admin123

