-- ========================================================================
-- 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
SET FOREIGN_KEY_CHECKS = 0;


-- ========================================================================
-- 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 7: Create User for Organization 2
-- ========================================================================

-- Password: Police123 (hashed with bcrypt)
-- Hash generated using bcrypt with 10 rounds: $2a$10$sampleHashForPolice123Password

INSERT IGNORE INTO `users` (`name`, `login_id`, `rank_id`, `service_no`, `unit_id`, `order_id`, `organization_id`, `department_id`, `role_id`, `designation_id`, `phone`, `mobile`, `alternative_mobile`, `email`, `avatar`, `status`, `last_seen`, `password_hash`, `parent_id`, `is_active`, `created_at`, `updated_at`) VALUES
	('Police Super Admin', 'Police_sadmin', 18, 'BA20001', 11, 1, 2, 80, 7, 127, '+880211100001', '+8801711100001', '+8801811100001', 'Police_admin@Police.mil.bd', NULL, 'offline', NULL, '$2a$10$wSP7ffhG.pqpil3rC9UkMehi/7unt3FNjAqIWadpa19AzQpONmNtG', NULL, 1, '2025-10-25 00:00:00', '2025-10-25 00:00:00'),
	('Police Admin User', 'Police_admin', 19, 'BA20002', 11, 2, 2, 81, 8, 129, '+880211100002', '+8801711100002', '+8801811100002', 'admin@Police.mil.bd', NULL, 'offline', NULL, '$2a$10$kMUlyMUgmH.i7hWMsa7KzeCYGIzs.uSgzZOVbVE3PipWOfh0lueyO', NULL, 1, '2025-10-25 00:00:00', '2025-10-25 00:00:00'),
	('Police Regular User', 'Police_user', 20, 'BA20003', 12, 3, 2, 82, 9, 130, '+880211100003', '+8801711100003', '+8801811100003', 'user@Police.mil.bd', NULL, 'offline', NULL, '$2a$10$IRwspfCD3SYWvfJ.YSgYPeecZj4vEfmyV7ud2hLqoo5rCWtY.xrVy', NULL, 1, '2025-10-25 00:00:00', '2025-10-25 00:00:00');

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

SET FOREIGN_KEY_CHECKS = 1;

-- ========================================================================
-- 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