-- ============================================
-- Enterprise HRM ERP System - ALL FEATURES SETUP
-- Complete Migration File - Start to Latest
-- Run this file to set up ALL new features
-- ============================================
-- 
-- Features Included:
-- 1. Multi-Branch System (Sites/Branches)
-- 2. Branch-wise User Assignment
-- 3. Branch-wise Role Permissions
-- 4. User-Site Relationship (Many-to-Many)
-- ============================================

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

-- ============================================
-- FEATURE 1: Multi-Branch System
-- ============================================
-- Allows admin to create and manage multiple branches
-- Each branch can have separate users, data, and permissions

CREATE TABLE IF NOT EXISTS `sites` (
  `site_id` int(11) NOT NULL AUTO_INCREMENT,
  `site_code` varchar(50) NOT NULL,
  `site_name` varchar(255) NOT NULL,
  `site_address` text,
  `site_phone` varchar(50),
  `site_email` varchar(255),
  `site_status` enum('active','inactive') DEFAULT 'active',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`site_id`),
  UNIQUE KEY `site_code` (`site_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- FEATURE 2: User-Site Relationship (Many-to-Many)
-- ============================================
-- Allows users to be assigned to multiple branches
-- Supports primary branch selection

CREATE TABLE IF NOT EXISTS `user_sites` (
  `user_site_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `site_id` int(11) NOT NULL,
  `is_primary` tinyint(1) DEFAULT 0,
  `assigned_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `assigned_by` int(11),
  PRIMARY KEY (`user_site_id`),
  UNIQUE KEY `unique_user_site` (`user_id`,`site_id`),
  KEY `fk_user_site_user` (`user_id`),
  KEY `fk_user_site_site` (`site_id`),
  KEY `fk_user_site_assigned_by` (`assigned_by`),
  CONSTRAINT `fk_user_site_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_user_site_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_user_site_assigned_by` FOREIGN KEY (`assigned_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- FEATURE 3: Branch-wise Role Permissions
-- ============================================
-- Allows different permissions for same role in different branches
-- Overrides default role permissions for specific branches

CREATE TABLE IF NOT EXISTS `site_role_permissions` (
  `site_role_permission_id` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL,
  `role_id` int(11) NOT NULL,
  `permission_id` int(11) NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_by` int(11),
  PRIMARY KEY (`site_role_permission_id`),
  UNIQUE KEY `unique_site_role_permission` (`site_id`,`role_id`,`permission_id`),
  KEY `fk_site_role_permission_site` (`site_id`),
  KEY `fk_site_role_permission_role` (`role_id`),
  KEY `fk_site_role_permission_permission` (`permission_id`),
  KEY `fk_site_role_permission_created_by` (`created_by`),
  CONSTRAINT `fk_site_role_permission_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_site_role_permission_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`role_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_site_role_permission_permission` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`permission_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_site_role_permission_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- FEATURE 4: Ensure Users table has site_id
-- ============================================
-- Add site_id column to users table if it doesn't exist

-- Check if site_id column exists in users table
SET @column_exists = (
    SELECT COUNT(*) 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'users' 
    AND COLUMN_NAME = 'site_id'
);

-- Add site_id column if it doesn't exist
SET @sql = IF(@column_exists = 0,
    'ALTER TABLE `users` 
     ADD COLUMN `site_id` int(11) NOT NULL DEFAULT 1 AFTER `user_id`,
     ADD KEY `fk_user_site` (`site_id`),
     ADD CONSTRAINT `fk_user_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT',
    'SELECT "Column site_id already exists in users table" AS message'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ============================================
-- COMMIT TRANSACTION
-- ============================================

COMMIT;

-- ============================================
-- MIGRATION SUMMARY
-- ============================================
-- 
-- ✅ Tables Created/Updated:
--    1. sites - Branch/Site management
--    2. user_sites - User-Branch relationship
--    3. site_role_permissions - Branch-wise permissions
--    4. users.site_id - Added if missing
--
-- ✅ Features Enabled:
--    • Multi-branch system
--    • Branch-wise user assignment
--    • Branch-wise login with selection
--    • Branch-wise permissions
--    • Branch switcher in header
--    • Role management system
--    • Permission setup
--    • Stock Group menu
--
-- ✅ Next Steps:
--    1. Create your first branch: /admin/sites_create.php
--    2. Assign users to branches: /admin/users.php
--    3. Set branch permissions: /admin/branch_permissions.php
--
-- ============================================

