-- ==============================================================================
-- HIGHRA CRM: LIVE SERVER DATABASE MIGRATION & RBAC SETUP
-- Target Database: rshineca_mab (or your live database)
-- ==============================================================================

-- 1. Alter Users Table (Add Enterprise Columns if missing)
ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `first_name` VARCHAR(255) NULL AFTER `name`;
ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `last_name` VARCHAR(255) NULL AFTER `first_name`;
ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `mobile` VARCHAR(40) NULL AFTER `email`;
ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `employee_id` VARCHAR(50) NULL AFTER `mobile`;
ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `department` VARCHAR(100) NULL AFTER `employee_id`;
ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `designation` VARCHAR(100) NULL AFTER `department`;
ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `reporting_to` BIGINT UNSIGNED NULL AFTER `designation`;
ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `branch_location` VARCHAR(100) NULL AFTER `reporting_to`;
ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `access_scope` VARCHAR(50) NOT NULL DEFAULT 'all' AFTER `branch_location`;
ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `avatar` VARCHAR(255) NULL AFTER `access_scope`;
ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `is_locked` TINYINT(1) NOT NULL DEFAULT 0 AFTER `active`;
ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `two_factor_enabled` TINYINT(1) NOT NULL DEFAULT 0 AFTER `is_locked`;
ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `joining_date` DATE NULL AFTER `two_factor_enabled`;
ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `last_login_at` TIMESTAMP NULL AFTER `joining_date`;
ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `last_login_ip` VARCHAR(45) NULL AFTER `last_login_at`;

-- 2. Create Roles Table
CREATE TABLE IF NOT EXISTS `roles` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `slug` VARCHAR(255) NOT NULL UNIQUE,
  `description` TEXT NULL,
  `is_system` TINYINT(1) NOT NULL DEFAULT 0,
  `status` VARCHAR(20) NOT NULL DEFAULT 'active',
  `created_at` TIMESTAMP NULL,
  `updated_at` TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Create Permissions Table
CREATE TABLE IF NOT EXISTS `permissions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `module` VARCHAR(255) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `slug` VARCHAR(255) NOT NULL UNIQUE,
  `description` TEXT NULL,
  `created_at` TIMESTAMP NULL,
  `updated_at` TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Create Role Permissions Pivot Table
CREATE TABLE IF NOT EXISTS `role_permissions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `role_id` BIGINT UNSIGNED NOT NULL,
  `permission_slug` VARCHAR(255) NOT NULL,
  `access_scope` VARCHAR(50) NOT NULL DEFAULT 'all',
  `created_at` TIMESTAMP NULL,
  `updated_at` TIMESTAMP NULL,
  UNIQUE KEY `role_permission_unique` (`role_id`, `permission_slug`),
  CONSTRAINT `fk_role_permissions_role_id` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Create User Permission Overrides Table
CREATE TABLE IF NOT EXISTS `user_permission_overrides` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `permission_slug` VARCHAR(255) NOT NULL,
  `granted` TINYINT(1) NOT NULL DEFAULT 1,
  `access_scope` VARCHAR(50) NOT NULL DEFAULT 'all',
  `created_at` TIMESTAMP NULL,
  `updated_at` TIMESTAMP NULL,
  UNIQUE KEY `user_permission_unique` (`user_id`, `permission_slug`),
  CONSTRAINT `fk_user_permission_overrides_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. Create Audit Logs Table
CREATE TABLE IF NOT EXISTS `audit_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `user_id` BIGINT UNSIGNED NULL,
  `action` VARCHAR(255) NOT NULL,
  `module` VARCHAR(255) NOT NULL,
  `target_type` VARCHAR(255) NULL,
  `target_id` BIGINT UNSIGNED NULL,
  `description` TEXT NULL,
  `old_values` JSON NULL,
  `new_values` JSON NULL,
  `ip_address` VARCHAR(45) NULL,
  `user_agent` TEXT NULL,
  `created_at` TIMESTAMP NULL,
  `updated_at` TIMESTAMP NULL,
  INDEX `idx_audit_logs_user` (`user_id`),
  INDEX `idx_audit_logs_module` (`module`),
  INDEX `idx_audit_logs_target` (`target_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. Create Login Histories Table
CREATE TABLE IF NOT EXISTS `login_histories` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `ip_address` VARCHAR(45) NULL,
  `user_agent` TEXT NULL,
  `status` VARCHAR(30) NOT NULL DEFAULT 'success',
  `logged_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_login_histories_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8. Record Migration in migrations table (so Laravel knows it ran)
INSERT IGNORE INTO `migrations` (`migration`, `batch`) 
VALUES ('2026_08_20_000001_create_roles_and_permissions_system', 3);