-- Migration: Enhance User Groups with Hierarchy and Roles
-- Purpose: Add organizational structure support to user groups
-- Date: 2025-12-04

-- 1. Add parent_id column to user_groups table for hierarchy
ALTER TABLE user_groups 
ADD COLUMN parent_id INT UNSIGNED NULL DEFAULT NULL 
AFTER id,
ADD INDEX idx_parent_id (parent_id);

-- 2. Add role column to user_group_members table
ALTER TABLE user_group_members 
ADD COLUMN role ENUM('manager', 'leader', 'member', 'observer') 
DEFAULT 'member' 
AFTER user_id,
ADD INDEX idx_role (role),
ADD INDEX idx_group_role (group_id, role);

-- 3. Update type column to support new types while keeping existing values
ALTER TABLE user_groups 
MODIFY COLUMN type ENUM('system', 'department', 'project', 'location', 'custom', 'division', 'team', 'squad') 
DEFAULT 'custom';

-- 4. Add display_order column for sorting children within same parent
ALTER TABLE user_groups 
ADD COLUMN display_order INT DEFAULT 0 
AFTER is_active,
ADD INDEX idx_display_order (display_order);

-- 5. Sample hierarchical data (optional - comment out if not needed)
-- Create sample org structure: Company > Divisions > Departments > Teams

-- Top level - Company
INSERT INTO user_groups (name, slug, description, type, parent_id, is_active, created_by, display_order) 
VALUES 
('All Company', 'all-company', 'Entire organization', 'system', NULL, 1, 1, 1)
ON DUPLICATE KEY UPDATE name=name;

SET @company_id = LAST_INSERT_ID();

-- Divisions
INSERT INTO user_groups (name, slug, description, type, parent_id, is_active, created_by, display_order) 
VALUES 
('Sales Division', 'sales-division', 'Sales and marketing teams', 'division', @company_id, 1, 1, 1),
('Operations Division', 'operations-division', 'Operations and delivery', 'division', @company_id, 1, 1, 2),
('Technology Division', 'technology-division', 'Engineering and IT', 'division', @company_id, 1, 1, 3)
ON DUPLICATE KEY UPDATE name=name;

-- Get division IDs
SET @sales_div_id = (SELECT id FROM user_groups WHERE slug = 'sales-division' LIMIT 1);
SET @ops_div_id = (SELECT id FROM user_groups WHERE slug = 'operations-division' LIMIT 1);
SET @tech_div_id = (SELECT id FROM user_groups WHERE slug = 'technology-division' LIMIT 1);

-- Departments under Sales Division
INSERT INTO user_groups (name, slug, description, type, parent_id, is_active, created_by, display_order) 
VALUES 
('Sales Department', 'sales-department', 'Direct sales team', 'department', @sales_div_id, 1, 1, 1),
('Marketing Department', 'marketing-department', 'Marketing and campaigns', 'department', @sales_div_id, 1, 1, 2)
ON DUPLICATE KEY UPDATE name=name;

-- Departments under Operations Division
INSERT INTO user_groups (name, slug, description, type, parent_id, is_active, created_by, display_order) 
VALUES 
('Manufacturing Department', 'manufacturing-department', 'Production teams', 'department', @ops_div_id, 1, 1, 1),
('Logistics Department', 'logistics-department', 'Supply chain and delivery', 'department', @ops_div_id, 1, 1, 2)
ON DUPLICATE KEY UPDATE name=name;

-- Departments under Technology Division
INSERT INTO user_groups (name, slug, description, type, parent_id, is_active, created_by, display_order) 
VALUES 
('Engineering Department', 'engineering-department', 'Software development', 'department', @tech_div_id, 1, 1, 1),
('IT Department', 'it-department', 'IT support and infrastructure', 'department', @tech_div_id, 1, 1, 2)
ON DUPLICATE KEY UPDATE name=name;

-- Get department IDs
SET @sales_dept_id = (SELECT id FROM user_groups WHERE slug = 'sales-department' LIMIT 1);
SET @eng_dept_id = (SELECT id FROM user_groups WHERE slug = 'engineering-department' LIMIT 1);

-- Teams under Sales Department
INSERT INTO user_groups (name, slug, description, type, parent_id, is_active, created_by, display_order) 
VALUES 
('Enterprise Sales Team', 'enterprise-sales-team', 'Enterprise account executives', 'team', @sales_dept_id, 1, 1, 1),
('SMB Sales Team', 'smb-sales-team', 'Small business sales', 'team', @sales_dept_id, 1, 1, 2)
ON DUPLICATE KEY UPDATE name=name;

-- Teams under Engineering Department
INSERT INTO user_groups (name, slug, description, type, parent_id, is_active, created_by, display_order) 
VALUES 
('Backend Team', 'backend-team', 'Backend developers', 'team', @eng_dept_id, 1, 1, 1),
('Frontend Team', 'frontend-team', 'Frontend developers', 'team', @eng_dept_id, 1, 1, 2),
('DevOps Squad', 'devops-squad', 'Infrastructure and deployment', 'squad', @eng_dept_id, 1, 1, 3)
ON DUPLICATE KEY UPDATE name=name;

-- Migration complete
-- Run this migration using: mysql -u root -p m1_erp < database/migrations/030_enhance_user_groups_hierarchy.sql