-- =====================================================
-- COMPLETE 9-TIER AUTHENTICATION SYSTEM MIGRATION
-- =====================================================

-- First, ensure the admins table exists with proper structure
CREATE TABLE IF NOT EXISTS `admins` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `username` varchar(100) NOT NULL,
  `password` varchar(255) NOT NULL,
  `role` enum('super_admin','admin','moderator','partner','studio','creator','vip','user','guest') NOT NULL DEFAULT 'user',
  `permissions` longtext DEFAULT NULL,
  `avatar` varchar(255) DEFAULT NULL,
  `status` enum('active','inactive','suspended') DEFAULT 'active',
  `last_login_at` timestamp NULL DEFAULT NULL,
  `last_login_ip` varchar(45) DEFAULT NULL,
  `two_factor_enabled` tinyint(1) DEFAULT 0,
  `two_factor_secret` varchar(255) DEFAULT NULL,
  `backup_codes` longtext DEFAULT NULL,
  `failed_login_attempts` int(11) DEFAULT 0,
  `locked_until` timestamp NULL DEFAULT NULL,
  `created_by` bigint(20) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `admins_email_unique` (`email`),
  UNIQUE KEY `admins_username_unique` (`username`),
  KEY `idx_admins_role` (`role`),
  KEY `idx_admins_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Migrate existing admin users from users table to admins table
INSERT IGNORE INTO `admins` (
  `name`, `email`, `username`, `password`, `role`, `permissions`, `avatar`, `status`, 
  `last_login_at`, `last_login_ip`, `created_at`, `updated_at`
)
SELECT 
  COALESCE(NULLIF(`name`, ''), NULLIF(`username`, ''), 'Admin User') as name,
  `email`,
  COALESCE(NULLIF(`username`, ''), SUBSTRING(`email`, 1, LOCATE('@', `email`) - 1)) as username,
  `password`,
  CASE 
    WHEN `role` = 'admin' THEN 'admin'
    WHEN `permission` = 'full_access' THEN 'super_admin'
    WHEN `role` = 'super_admin' THEN 'super_admin'
    WHEN `role` = 'moderator' THEN 'moderator'
    WHEN `role` = 'partner' OR `user_type` = 'partner' OR `is_partner` = 1 THEN 'partner'
    WHEN `role` = 'studio' OR `role` = 'business' OR `user_type` = 'studio' OR `user_type` = 'business' OR `is_business` = 1 OR `is_studio` = 1 THEN 'studio'
    WHEN `role` = 'creator' OR `user_type` = 'creator' OR `is_creator` = 1 THEN 'creator'
    WHEN `role` = 'vip' OR `user_type` = 'vip' OR `is_vip` = 1 THEN 'vip'
    ELSE 'user'
  END as role,
  CASE 
    WHEN `permission` = 'full_access' OR `role` = 'super_admin' THEN '["all"]'
    WHEN `role` = 'admin' THEN '["users.view","users.edit","users.delete","content.view","content.edit","content.delete","reports.view","reports.manage","settings.view","settings.edit"]'
    WHEN `role` = 'moderator' THEN '["content.view","content.moderate","reports.view","reports.manage","users.view"]'
    WHEN `role` = 'partner' THEN '["partner.view","partner.manage","creators.manage","revenue.view","analytics.view","content.create"]'
    WHEN `role` = 'studio' THEN '["studio.view","studio.manage","analytics.view","payments.view","content.create","creators.manage"]'
    WHEN `role` = 'creator' THEN '["content.create","content.edit","analytics.view","earnings.view"]'
    WHEN `role` = 'vip' THEN '["content.view","content.premium","support.priority","features.enhanced"]'
    ELSE '["content.view"]'
  END as permissions,
  `avatar`,
  CASE 
    WHEN `status` = 'active' THEN 'active'
    WHEN `status` = 'suspended' THEN 'suspended'
    ELSE 'inactive'
  END as status,
  `last_login_at`,
  `last_login_ip`,
  `date` as created_at,
  NOW() as updated_at
FROM `users` 
WHERE (
  `role` IN ('admin', 'super_admin', 'moderator', 'partner', 'studio', 'business') 
  OR `is_admin` = 1 
  OR `permission` IN ('full_access', 'limited_access') 
  OR `is_business` = 1 
  OR `is_studio` = 1 
  OR `is_partner` = 1
)
AND `email` IS NOT NULL AND `email` != ''
AND `email` NOT IN (SELECT `email` FROM `admins`);

-- Update users table to remove admin roles and clean up
UPDATE `users` SET 
  `role` = CASE 
    WHEN `user_type` = 'creator' THEN 'creator'
    WHEN `user_type` = 'model' THEN 'creator'
    WHEN `is_creator` = 1 THEN 'creator'
    WHEN `user_type` = 'vip' OR `is_vip` = 1 THEN 'vip'
    WHEN `user_type` = 'studio' OR `role` = 'studio' OR `is_business` = 1 THEN 'studio'
    ELSE 'user'
  END,
  `is_admin` = 0,
  `permission` = 'none'
WHERE `role` IN ('admin', 'super_admin', 'moderator') 
   OR `is_admin` = 1 
   OR `permission` IN ('full_access', 'limited_access');

-- Create default super admin if none exists
INSERT IGNORE INTO `admins` (
  `name`, `email`, `username`, `password`, `role`, `permissions`, `status`, `created_at`, `updated_at`
) VALUES (
  'Super Administrator',
  'admin@hotx2c.com',
  'superadmin',
  '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
  'super_admin',
  '["all"]',
  'active',
  NOW(),
  NOW()
);

-- Create test admin accounts with known passwords
INSERT IGNORE INTO `admins` (
  `name`, `email`, `username`, `password`, `role`, `permissions`, `status`, `created_at`, `updated_at`
) VALUES 
(
  'Test Admin',
  'test@hotx2c.com',
  'testadmin',
  '$2y$12$DrWT57/Ul4.gYkYKU2CM3.Ux.BkZjxbDBXqvbpmgfVFhqKqvQvQGu',
  'admin',
  '["users.view","users.edit","content.view","content.edit","reports.view","settings.view"]',
  'active',
  NOW(),
  NOW()
),
(
  'Test Moderator',
  'moderator@hotx2c.com',
  'testmod',
  '$2y$12$mV95Dl/Z4dc0U.FK4DPikOmEh36APJAUZzARpGeLJ37hqKqvQvQGu',
  'moderator',
  '["content.view","content.moderate","reports.view","users.view"]',
  'active',
  NOW(),
  NOW()
),
(
  'Test Partner',
  'partner@hotx2c.com',
  'testpartner',
  '$2y$12$mV95Dl/Z4dc0U.FK4DPikOmEh36APJAUZzARpGeLJ37hqKqvQvQGu',
  'partner',
  '["partner.view","partner.manage","creators.manage","revenue.view","analytics.view"]',
  'active',
  NOW(),
  NOW()
);

-- Create test regular users with proper 9-tier roles
INSERT IGNORE INTO `users` (
  `name`, `email`, `username`, `password`, `role`, `user_type`, `status`, `is_admin`, 
  `permission`, `failed_login_attempts`, `date`, `ip`
) VALUES 
(
  'Test User',
  'user@hotx2c.com',
  'testuser',
  '$2y$12$DrWT57/Ul4.gYkYKU2CM3.Ux.BkZjxbDBXqvbpmgfVFhqKqvQvQGu',
  'user',
  'user',
  'active',
  0,
  'none',
  0,
  NOW(),
  '127.0.0.1'
),
(
  'Test VIP',
  'vip@hotx2c.com',
  'testvip',
  '$2y$12$DrWT57/Ul4.gYkYKU2CM3.Ux.BkZjxbDBXqvbpmgfVFhqKqvQvQGu',
  'vip',
  'vip',
  'active',
  0,
  'none',
  0,
  NOW(),
  '127.0.0.1'
),
(
  'Test Creator',
  'creator@hotx2c.com',
  'testcreator',
  '$2y$12$mV95Dl/Z4dc0U.FK4DPikOmEh36APJAUZzARpGeLJ37hqKqvQvQGu',
  'creator',
  'creator',
  'active',
  0,
  'none',
  0,
  NOW(),
  '127.0.0.1'
),
(
  'Test Studio',
  'studio@hotx2c.com',
  'teststudio',
  '$2y$12$mV95Dl/Z4dc0U.FK4DPikOmEh36APJAUZzARpGeLJ37hqKqvQvQGu',
  'studio',
  'studio',
  'active',
  0,
  'none',
  0,
  NOW(),
  '127.0.0.1'
);

-- Update users table with 9-tier user_type enum
ALTER TABLE `users` 
MODIFY COLUMN `user_type` enum('guest','user','vip','creator','studio','partner','moderator','admin','super_admin') DEFAULT 'user';

-- Update users table with 9-tier role enum
ALTER TABLE `users` 
MODIFY COLUMN `role` enum('guest','user','vip','creator','studio','partner','moderator','admin','super_admin') DEFAULT 'user';

-- Add additional columns for enhanced user management
ALTER TABLE `users` 
ADD COLUMN IF NOT EXISTS `is_partner` tinyint(1) DEFAULT 0 AFTER `is_business`,
ADD COLUMN IF NOT EXISTS `is_vip` tinyint(1) DEFAULT 0 AFTER `is_partner`,
ADD COLUMN IF NOT EXISTS `is_studio` tinyint(1) DEFAULT 0 AFTER `is_vip`,
ADD COLUMN IF NOT EXISTS `tier_level` int(11) DEFAULT 2 AFTER `user_type`;

-- Update tier levels based on 9-tier system
UPDATE `users` SET `tier_level` = 9 WHERE `user_type` = 'super_admin' OR `role` = 'super_admin';
UPDATE `users` SET `tier_level` = 8 WHERE `user_type` = 'admin' OR `role` = 'admin';
UPDATE `users` SET `tier_level` = 7 WHERE `user_type` = 'moderator' OR `role` = 'moderator';
UPDATE `users` SET `tier_level` = 6 WHERE `user_type` = 'partner' OR `role` = 'partner' OR `is_partner` = 1;
UPDATE `users` SET `tier_level` = 5 WHERE `user_type` = 'studio' OR `role` = 'studio' OR `is_business` = 1 OR `is_studio` = 1;
UPDATE `users` SET `tier_level` = 4 WHERE `user_type` = 'creator' OR `role` = 'creator' OR `is_creator` = 1;
UPDATE `users` SET `tier_level` = 3 WHERE `user_type` = 'vip' OR `role` = 'vip' OR `is_vip` = 1;
UPDATE `users` SET `tier_level` = 2 WHERE `tier_level` IS NULL OR `tier_level` = 0;

-- Ensure all required tables exist for authentication system
CREATE TABLE IF NOT EXISTS `admin_sessions` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `admin_id` bigint(20) UNSIGNED NOT NULL,
  `session_id` varchar(255) NOT NULL,
  `ip_address` varchar(45) NOT NULL,
  `user_agent` text NOT NULL,
  `last_activity` timestamp NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_admin_sessions_admin_id` (`admin_id`),
  KEY `idx_admin_sessions_session_id` (`session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `login_sessions` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `admin_id` bigint(20) UNSIGNED DEFAULT NULL,
  `session_id` varchar(255) NOT NULL,
  `ip_address` varchar(45) NOT NULL,
  `user_agent` text NOT NULL,
  `login_method` enum('password','social','magic_link','phone_otp','webauthn','qr_code') DEFAULT 'password',
  `is_admin` tinyint(1) DEFAULT 0,
  `logged_in_at` timestamp NULL DEFAULT NULL,
  `logged_out_at` timestamp NULL DEFAULT NULL,
  `last_activity` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_login_sessions_user_id` (`user_id`),
  KEY `idx_login_sessions_admin_id` (`admin_id`),
  KEY `idx_login_sessions_session_id` (`session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `security_logs` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `admin_id` bigint(20) UNSIGNED DEFAULT NULL,
  `event_type` varchar(50) NOT NULL,
  `event_category` enum('authentication','authorization','security','system') DEFAULT 'authentication',
  `severity` enum('info','warning','error','critical') DEFAULT 'info',
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `request_url` varchar(500) DEFAULT NULL,
  `request_method` varchar(10) DEFAULT NULL,
  `additional_data` longtext DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_security_logs_user_id` (`user_id`),
  KEY `idx_security_logs_admin_id` (`admin_id`),
  KEY `idx_security_logs_event_type` (`event_type`),
  KEY `idx_security_logs_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create user roles table with 9-tier hierarchy
CREATE TABLE IF NOT EXISTS `user_roles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `display_name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `level` int(11) DEFAULT 1,
  `permissions` text DEFAULT NULL,
  `is_admin_role` tinyint(1) DEFAULT 0,
  `can_access_admin_panel` tinyint(1) DEFAULT 0,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Insert 9-tier role hierarchy
INSERT IGNORE INTO `user_roles` (`name`, `display_name`, `description`, `level`, `is_admin_role`, `can_access_admin_panel`) VALUES
('guest', 'Guest', 'Unauthenticated visitor with limited access', 1, 0, 0),
('user', 'User', 'Regular user/fan who can subscribe to creators', 2, 0, 0),
('vip', 'VIP', 'Premium subscriber with enhanced features and priority support', 3, 0, 0),
('creator', 'Creator', 'Content creator/model who can publish and earn', 4, 0, 0),
('studio', 'Studio', 'Studio account with analytics, payments, and multi-creator management', 5, 0, 0),
('partner', 'Partner', 'Agency partnership with multi-creator management capabilities', 6, 1, 1),
('moderator', 'Moderator', 'Content moderator with review and oversight permissions', 7, 1, 1),
('admin', 'Administrator', 'Platform administrator with management access', 8, 1, 1),
('super_admin', 'Super Administrator', 'System-level administrator with full control', 9, 1, 1);

-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS `idx_users_user_type` ON `users` (`user_type`);
CREATE INDEX IF NOT EXISTS `idx_users_tier_level` ON `users` (`tier_level`);
CREATE INDEX IF NOT EXISTS `idx_users_role` ON `users` (`role`);
CREATE INDEX IF NOT EXISTS `idx_users_is_partner` ON `users` (`is_partner`);
CREATE INDEX IF NOT EXISTS `idx_users_is_vip` ON `users` (`is_vip`);
CREATE INDEX IF NOT EXISTS `idx_users_is_studio` ON `users` (`is_studio`);

-- =====================================================
-- 9-TIER AUTHENTICATION SYSTEM MIGRATION COMPLETE
-- =====================================================

-- Summary of 9-Tier Hierarchy:
-- Level 9: super_admin - System control
-- Level 8: admin - Platform management  
-- Level 7: moderator - Content oversight
-- Level 6: partner - Agency partnerships, multi-creator management
-- Level 5: studio - Studio accounts, analytics, payments
-- Level 4: creator - Content creators/models
-- Level 3: vip - Premium subscribers, enhanced features
-- Level 2: user - Regular users/fans
-- Level 1: guest - Unauthenticated visitors