-- ========================================================================
-- MIGRATION SCRIPT: Add Multi-Organization Support
-- ========================================================================
-- This script migrates an existing cms_db database to support multiple
-- organizations without losing any existing data.
--
-- IMPORTANT: Backup your database before running this script!
--   mysqldump -u root -p cms_db > backup_before_migration.sql
--
-- Date: October 20, 2025
-- Version: 2.0
-- ========================================================================

USE cms_db;

-- Disable foreign key checks temporarily for migration
SET FOREIGN_KEY_CHECKS = 0;

-- ========================================================================
-- STEP 1: Create organizations table
-- ========================================================================

CREATE TABLE IF NOT EXISTS `organizations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `code` varchar(50) NOT NULL,
  `type` varchar(100) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `address` text DEFAULT NULL,
  `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_by` int(11) DEFAULT NULL,
  `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`),
  KEY `idx_name` (`name`),
  KEY `idx_code` (`code`),
  KEY `idx_is_active` (`is_active`),
  KEY `idx_created_by` (`created_by`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Insert default organization
INSERT IGNORE INTO `organizations` (`id`, `name`, `code`, `type`, `description`, `address`, `phone`, `email`, `website`, `logo`, `is_active`, `created_by`, `created_at`, `updated_at`) VALUES
	(1, 'Bangladesh Navy', 'BN', 'Military', 'Naval Forces of Bangladesh', 'Naval Headquarters, Dhaka', '+880-2-9853871', 'info@navy.mil.bd', 'https://www.navy.mil.bd', NULL, 1, NULL, '2025-10-20 00:00:00', '2025-10-20 00:00:00');

-- ========================================================================
-- STEP 2: Add organization_id columns to existing tables
-- ========================================================================

-- Add to units table
ALTER TABLE `units` 
  ADD COLUMN IF NOT EXISTS `organization_id` int(11) NOT NULL DEFAULT 1 AFTER `description`,
  ADD INDEX IF NOT EXISTS `idx_organization_id` (`organization_id`);

-- Add to departments table
ALTER TABLE `departments` 
  ADD COLUMN IF NOT EXISTS `organization_id` int(11) NOT NULL DEFAULT 1 AFTER `name`,
  ADD INDEX IF NOT EXISTS `idx_organization_id` (`organization_id`);

-- Add to designations table
ALTER TABLE `designations` 
  ADD COLUMN IF NOT EXISTS `organization_id` int(11) NOT NULL DEFAULT 1 AFTER `name`,
  ADD INDEX IF NOT EXISTS `idx_organization_id` (`organization_id`);

-- Add to ranks table
ALTER TABLE `ranks` 
  ADD COLUMN IF NOT EXISTS `organization_id` int(11) NOT NULL DEFAULT 1 AFTER `name`,
  ADD INDEX IF NOT EXISTS `idx_organization_id` (`organization_id`);

-- Add to roles table
ALTER TABLE `roles` 
  ADD COLUMN IF NOT EXISTS `organization_id` int(11) NOT NULL DEFAULT 1 AFTER `name`,
  ADD INDEX IF NOT EXISTS `idx_organization_id` (`organization_id`);

-- Add to conversations table
ALTER TABLE `conversations` 
  ADD COLUMN IF NOT EXISTS `organization_id` int(11) NOT NULL DEFAULT 1 AFTER `name`,
  ADD INDEX IF NOT EXISTS `idx_organization_id` (`organization_id`);

-- Add to notifications table
ALTER TABLE `notifications` 
  ADD COLUMN IF NOT EXISTS `organization_id` int(11) NOT NULL DEFAULT 1 AFTER `user_id`,
  ADD INDEX IF NOT EXISTS `idx_organization_id` (`organization_id`);

-- Add to feedback table
-- First, drop existing check constraint if it exists
SET @constraint_exists = (
  SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS 
  WHERE CONSTRAINT_SCHEMA = 'cms_db' 
    AND TABLE_NAME = 'feedback' 
    AND CONSTRAINT_NAME = 'feedback_chk_1'
    AND CONSTRAINT_TYPE = 'CHECK'
);

SET @sql_drop = IF(@constraint_exists > 0, 
  'ALTER TABLE feedback DROP CONSTRAINT feedback_chk_1',
  'SELECT ''No existing check constraint to drop'' AS message'
);
PREPARE stmt FROM @sql_drop;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Now add organization_id column
ALTER TABLE `feedback` 
  ADD COLUMN IF NOT EXISTS `organization_id` int(11) NOT NULL DEFAULT 1 AFTER `phone`,
  ADD INDEX IF NOT EXISTS `idx_organization_id` (`organization_id`);

-- Add to audit_logs table
ALTER TABLE `audit_logs` 
  ADD COLUMN IF NOT EXISTS `organization_id` int(11) NOT NULL DEFAULT 1 AFTER `user_id`,
  ADD INDEX IF NOT EXISTS `idx_organization_id` (`organization_id`);

-- ========================================================================
-- STEP 3: Add foreign key constraints
-- ========================================================================

-- Note: Use IF NOT EXISTS logic by checking if constraint already exists

-- Units foreign key
SET @constraint_exists = (
  SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS 
  WHERE CONSTRAINT_SCHEMA = 'cms_db' 
    AND TABLE_NAME = 'units' 
    AND CONSTRAINT_NAME = 'units_ibfk_3'
);

SET @sql = IF(@constraint_exists = 0, 
  'ALTER TABLE units ADD CONSTRAINT units_ibfk_3 FOREIGN KEY (organization_id) REFERENCES organizations(id)',
  'SELECT ''Constraint units_ibfk_3 already exists'' AS message'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Departments foreign key
SET @constraint_exists = (
  SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS 
  WHERE CONSTRAINT_SCHEMA = 'cms_db' 
    AND TABLE_NAME = 'departments' 
    AND CONSTRAINT_NAME = 'departments_ibfk_3'
);

SET @sql = IF(@constraint_exists = 0, 
  'ALTER TABLE departments ADD CONSTRAINT departments_ibfk_3 FOREIGN KEY (organization_id) REFERENCES organizations(id)',
  'SELECT ''Constraint departments_ibfk_3 already exists'' AS message'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Designations foreign key
SET @constraint_exists = (
  SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS 
  WHERE CONSTRAINT_SCHEMA = 'cms_db' 
    AND TABLE_NAME = 'designations' 
    AND CONSTRAINT_NAME = 'designations_ibfk_3'
);

SET @sql = IF(@constraint_exists = 0, 
  'ALTER TABLE designations ADD CONSTRAINT designations_ibfk_3 FOREIGN KEY (organization_id) REFERENCES organizations(id)',
  'SELECT ''Constraint designations_ibfk_3 already exists'' AS message'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Ranks foreign key
SET @constraint_exists = (
  SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS 
  WHERE CONSTRAINT_SCHEMA = 'cms_db' 
    AND TABLE_NAME = 'ranks' 
    AND CONSTRAINT_NAME = 'ranks_ibfk_1'
);

SET @sql = IF(@constraint_exists = 0, 
  'ALTER TABLE ranks ADD CONSTRAINT ranks_ibfk_1 FOREIGN KEY (organization_id) REFERENCES organizations(id)',
  'SELECT ''Constraint ranks_ibfk_1 already exists'' AS message'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Roles foreign key
SET @constraint_exists = (
  SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS 
  WHERE CONSTRAINT_SCHEMA = 'cms_db' 
    AND TABLE_NAME = 'roles' 
    AND CONSTRAINT_NAME = 'roles_ibfk_1'
);

SET @sql = IF(@constraint_exists = 0, 
  'ALTER TABLE roles ADD CONSTRAINT roles_ibfk_1 FOREIGN KEY (organization_id) REFERENCES organizations(id)',
  'SELECT ''Constraint roles_ibfk_1 already exists'' AS message'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Conversations foreign key
SET @constraint_exists = (
  SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS 
  WHERE CONSTRAINT_SCHEMA = 'cms_db' 
    AND TABLE_NAME = 'conversations' 
    AND CONSTRAINT_NAME = 'conversations_ibfk_2'
);

SET @sql = IF(@constraint_exists = 0, 
  'ALTER TABLE conversations ADD CONSTRAINT conversations_ibfk_2 FOREIGN KEY (organization_id) REFERENCES organizations(id)',
  'SELECT ''Constraint conversations_ibfk_2 already exists'' AS message'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Notifications foreign key
SET @constraint_exists = (
  SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS 
  WHERE CONSTRAINT_SCHEMA = 'cms_db' 
    AND TABLE_NAME = 'notifications' 
    AND CONSTRAINT_NAME = 'notifications_ibfk_2'
);

SET @sql = IF(@constraint_exists = 0, 
  'ALTER TABLE notifications ADD CONSTRAINT notifications_ibfk_2 FOREIGN KEY (organization_id) REFERENCES organizations(id)',
  'SELECT ''Constraint notifications_ibfk_2 already exists'' AS message'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Feedback foreign key
SET @constraint_exists = (
  SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS 
  WHERE CONSTRAINT_SCHEMA = 'cms_db' 
    AND TABLE_NAME = 'feedback' 
    AND CONSTRAINT_NAME = 'feedback_ibfk_2'
);

SET @sql = IF(@constraint_exists = 0, 
  'ALTER TABLE feedback ADD CONSTRAINT feedback_ibfk_2 FOREIGN KEY (organization_id) REFERENCES organizations(id)',
  'SELECT ''Constraint feedback_ibfk_2 already exists'' AS message'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Audit logs foreign key
SET @constraint_exists = (
  SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS 
  WHERE CONSTRAINT_SCHEMA = 'cms_db' 
    AND TABLE_NAME = 'audit_logs' 
    AND CONSTRAINT_NAME = 'audit_logs_ibfk_2'
);

SET @sql = IF(@constraint_exists = 0, 
  'ALTER TABLE audit_logs ADD CONSTRAINT audit_logs_ibfk_2 FOREIGN KEY (organization_id) REFERENCES organizations(id)',
  'SELECT ''Constraint audit_logs_ibfk_2 already exists'' AS message'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ========================================================================
-- STEP 4: Verify migration
-- ========================================================================

-- Check organization count
SELECT 'Organizations:' AS Info, COUNT(*) AS Count FROM organizations;

-- Check data distribution
SELECT 
  'Data Distribution' AS Info,
  (SELECT COUNT(*) FROM units WHERE organization_id = 1) AS units,
  (SELECT COUNT(*) FROM departments WHERE organization_id = 1) AS departments,
  (SELECT COUNT(*) FROM designations WHERE organization_id = 1) AS designations,
  (SELECT COUNT(*) FROM users WHERE organization_id = 1) AS users;

-- List all foreign keys to organizations
SELECT 
  TABLE_NAME,
  CONSTRAINT_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'cms_db'
  AND REFERENCED_TABLE_NAME = 'organizations'
ORDER BY TABLE_NAME;

-- Re-enable foreign key checks
SET FOREIGN_KEY_CHECKS = 1;

-- ========================================================================
-- Migration Complete
-- ========================================================================

SELECT 'Migration completed successfully!' AS Status,
       NOW() AS Timestamp;