-- =====================================================
-- COMPLETE AUTHENTICATION SYSTEM WITH ROLE SEPARATION
-- =====================================================

-- Create authentication settings table
CREATE TABLE IF NOT EXISTS `authentication_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `oauth_google_enabled` tinyint(1) DEFAULT 0,
  `oauth_facebook_enabled` tinyint(1) DEFAULT 0,
  `oauth_twitter_enabled` tinyint(1) DEFAULT 0,
  `oauth_github_enabled` tinyint(1) DEFAULT 0,
  `magic_links_enabled` tinyint(1) DEFAULT 0,
  `two_factor_enabled` tinyint(1) DEFAULT 0,
  `phone_otp_enabled` tinyint(1) DEFAULT 0,
  `webauthn_enabled` tinyint(1) DEFAULT 0,
  `qr_login_enabled` tinyint(1) DEFAULT 0,
  `biometric_enabled` tinyint(1) DEFAULT 0,
  `sso_enabled` tinyint(1) DEFAULT 0,
  `passwordless_enabled` tinyint(1) DEFAULT 0,
  `google_client_id` varchar(255) DEFAULT NULL,
  `google_client_secret` varchar(255) DEFAULT NULL,
  `facebook_app_id` varchar(255) DEFAULT NULL,
  `facebook_app_secret` varchar(255) DEFAULT NULL,
  `twitter_api_key` varchar(255) DEFAULT NULL,
  `twitter_api_secret` varchar(255) DEFAULT NULL,
  `github_client_id` varchar(255) DEFAULT NULL,
  `github_client_secret` varchar(255) DEFAULT NULL,
  `recaptcha_site_key` varchar(255) DEFAULT NULL,
  `recaptcha_secret_key` varchar(255) DEFAULT NULL,
  `session_timeout` int(11) DEFAULT 1440,
  `max_login_attempts` int(11) DEFAULT 5,
  `lockout_duration` int(11) DEFAULT 15,
  `require_email_verification` tinyint(1) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Insert default authentication settings
INSERT IGNORE INTO `authentication_settings` (`id`, `oauth_google_enabled`, `oauth_facebook_enabled`, `oauth_twitter_enabled`, `magic_links_enabled`, `two_factor_enabled`) 
VALUES (1, 0, 0, 0, 0, 0);

-- Update admins table structure
ALTER TABLE `admins` 
ADD COLUMN IF NOT EXISTS `username` varchar(50) DEFAULT NULL AFTER `email`,
ADD COLUMN IF NOT EXISTS `password` varchar(255) DEFAULT NULL AFTER `username`,
ADD COLUMN IF NOT EXISTS `status` enum('active','inactive','suspended') DEFAULT 'active' AFTER `permissions`,
ADD COLUMN IF NOT EXISTS `last_login_at` timestamp NULL DEFAULT NULL AFTER `status`,
ADD COLUMN IF NOT EXISTS `last_login_ip` varchar(45) DEFAULT NULL AFTER `last_login_at`,
ADD COLUMN IF NOT EXISTS `failed_login_attempts` int(11) DEFAULT 0 AFTER `last_login_ip`,
ADD COLUMN IF NOT EXISTS `locked_until` timestamp NULL DEFAULT NULL AFTER `failed_login_attempts`,
ADD COLUMN IF NOT EXISTS `two_factor_enabled` tinyint(1) DEFAULT 0 AFTER `locked_until`,
ADD COLUMN IF NOT EXISTS `two_factor_secret` varchar(255) DEFAULT NULL AFTER `two_factor_enabled`,
ADD COLUMN IF NOT EXISTS `backup_codes` text DEFAULT NULL AFTER `two_factor_secret`,
ADD COLUMN IF NOT EXISTS `created_by` int(11) DEFAULT NULL AFTER `backup_codes`;

-- Update users table for role-based authentication with 9-tier hierarchy
ALTER TABLE `users` 
ADD COLUMN IF NOT EXISTS `user_type` enum('guest','user','vip','creator','studio','partner','moderator','admin','super_admin') DEFAULT 'user' AFTER `role`,
ADD COLUMN IF NOT EXISTS `tier_level` int(11) DEFAULT 1 AFTER `user_type`,
ADD COLUMN IF NOT EXISTS `failed_login_attempts` int(11) DEFAULT 0 AFTER `tier_level`,
ADD COLUMN IF NOT EXISTS `locked_until` timestamp NULL DEFAULT NULL AFTER `failed_login_attempts`,
ADD COLUMN IF NOT EXISTS `trusted_devices` text DEFAULT NULL AFTER `locked_until`,
ADD COLUMN IF NOT EXISTS `require_device_verification` tinyint(1) DEFAULT 0 AFTER `trusted_devices`,
ADD COLUMN IF NOT EXISTS `webauthn_enabled` tinyint(1) DEFAULT 0 AFTER `require_device_verification`,
ADD COLUMN IF NOT EXISTS `preferred_login_method` enum('password','oauth','magic_link','webauthn','biometric') DEFAULT 'password' AFTER `webauthn_enabled`,
ADD COLUMN IF NOT EXISTS `backup_codes` text DEFAULT NULL AFTER `preferred_login_method`,
ADD COLUMN IF NOT EXISTS `two_factor_confirmed_at` timestamp NULL DEFAULT NULL AFTER `backup_codes`,
ADD COLUMN IF NOT EXISTS `phone_verified` tinyint(1) DEFAULT 0 AFTER `two_factor_confirmed_at`,
ADD COLUMN IF NOT EXISTS `phone_verified_at` timestamp NULL DEFAULT NULL AFTER `phone_verified`,
ADD COLUMN IF NOT EXISTS `skip_mfa_on_trusted` tinyint(1) DEFAULT 0 AFTER `phone_verified_at`,
ADD COLUMN IF NOT EXISTS `trusted_device_duration` int(11) DEFAULT 30 AFTER `skip_mfa_on_trusted`,
ADD COLUMN IF NOT EXISTS `passkey_enabled` tinyint(1) DEFAULT 0 AFTER `trusted_device_duration`,
ADD COLUMN IF NOT EXISTS `prefer_passkey_login` tinyint(1) DEFAULT 0 AFTER `passkey_enabled`,
ADD COLUMN IF NOT EXISTS `passkey_backup_enabled` tinyint(1) DEFAULT 0 AFTER `prefer_passkey_login`;

-- Create user roles table for detailed role management
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 default user roles with 9-tier 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 login sessions table
CREATE TABLE IF NOT EXISTS `login_sessions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `user_type` enum('user','admin') DEFAULT 'user',
  `session_id` varchar(255) NOT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `device_type` varchar(50) DEFAULT NULL,
  `device_name` varchar(100) DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  `is_trusted` tinyint(1) DEFAULT 0,
  `login_method` enum('password','oauth','magic_link','webauthn','biometric') DEFAULT 'password',
  `oauth_provider` varchar(50) DEFAULT NULL,
  `two_factor_verified` tinyint(1) DEFAULT 0,
  `last_activity` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `expires_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `session_id` (`session_id`),
  KEY `user_type` (`user_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Create OAuth providers table
CREATE TABLE IF NOT EXISTS `oauth_providers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `provider` varchar(50) NOT NULL,
  `provider_id` varchar(255) NOT NULL,
  `provider_email` varchar(255) DEFAULT NULL,
  `provider_name` varchar(255) DEFAULT NULL,
  `provider_avatar` varchar(500) DEFAULT NULL,
  `access_token` text DEFAULT NULL,
  `refresh_token` text DEFAULT NULL,
  `expires_at` timestamp NULL 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 `provider_user` (`provider`, `provider_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Create magic links table
CREATE TABLE IF NOT EXISTS `magic_links` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL,
  `token` varchar(255) NOT NULL,
  `user_type` enum('user','admin') DEFAULT 'user',
  `expires_at` timestamp NOT NULL,
  `used_at` timestamp NULL DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `token` (`token`),
  KEY `email` (`email`),
  KEY `expires_at` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Create two factor authentication table
CREATE TABLE IF NOT EXISTS `two_factor_auth` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `user_type` enum('user','admin') DEFAULT 'user',
  `method` enum('app','sms','email') DEFAULT 'app',
  `secret` varchar(255) DEFAULT NULL,
  `phone_number` varchar(20) DEFAULT NULL,
  `backup_codes` text DEFAULT NULL,
  `enabled_at` timestamp NULL DEFAULT NULL,
  `last_used_at` timestamp NULL 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 `user_method` (`user_id`, `user_type`, `method`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Create WebAuthn credentials table
CREATE TABLE IF NOT EXISTS `webauthn_credentials` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `user_type` enum('user','admin') DEFAULT 'user',
  `credential_id` varchar(255) NOT NULL,
  `public_key` text NOT NULL,
  `counter` int(11) DEFAULT 0,
  `device_name` varchar(100) DEFAULT NULL,
  `device_type` varchar(50) DEFAULT NULL,
  `last_used_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `credential_id` (`credential_id`),
  KEY `user_id` (`user_id`, `user_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Create login attempts table
CREATE TABLE IF NOT EXISTS `login_attempts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(255) DEFAULT NULL,
  `username` varchar(50) DEFAULT NULL,
  `ip_address` varchar(45) NOT NULL,
  `user_agent` text DEFAULT NULL,
  `success` tinyint(1) DEFAULT 0,
  `failure_reason` varchar(255) DEFAULT NULL,
  `user_type` enum('user','admin') DEFAULT 'user',
  `attempted_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `ip_address` (`ip_address`),
  KEY `email` (`email`),
  KEY `attempted_at` (`attempted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Create trusted devices table
CREATE TABLE IF NOT EXISTS `trusted_devices` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `user_type` enum('user','admin') DEFAULT 'user',
  `device_token` varchar(255) NOT NULL,
  `device_name` varchar(100) DEFAULT NULL,
  `device_type` varchar(50) DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  `last_used_at` timestamp NULL DEFAULT NULL,
  `expires_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `device_token` (`device_token`),
  KEY `user_id` (`user_id`, `user_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Create password reset tokens table
CREATE TABLE IF NOT EXISTS `password_reset_tokens` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL,
  `token` varchar(255) NOT NULL,
  `user_type` enum('user','admin') DEFAULT 'user',
  `expires_at` timestamp NOT NULL,
  `used_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `token` (`token`),
  KEY `email` (`email`),
  KEY `expires_at` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Update existing admin record with proper authentication fields
UPDATE `admins` SET 
  `username` = 'superadmin',
  `password` = '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
  `role` = 'super_admin',
  `permissions` = '["all"]',
  `status` = 'active'
WHERE `id` = 1 AND `username` IS NULL;

-- Update existing users to have proper user types based on their current roles with 9-tier system
UPDATE `users` SET `user_type` = 'super_admin', `tier_level` = 9 WHERE `role` = 'admin' AND `permission` = 'full_access';
UPDATE `users` SET `user_type` = 'admin', `tier_level` = 8 WHERE `role` = 'admin' AND `permission` != 'full_access';
UPDATE `users` SET `user_type` = 'moderator', `tier_level` = 7 WHERE `role` = 'moderator';
UPDATE `users` SET `user_type` = 'partner', `tier_level` = 6 WHERE `is_partner` = 1 OR `role` = 'partner';
UPDATE `users` SET `user_type` = 'studio', `tier_level` = 5 WHERE `is_business` = 1 OR `role` = 'business' OR `role` = 'studio';
UPDATE `users` SET `user_type` = 'creator', `tier_level` = 4 WHERE `is_creator` = 1 OR `role` = 'creator';
UPDATE `users` SET `user_type` = 'vip', `tier_level` = 3 WHERE `is_vip` = 1 OR `role` = 'vip';
UPDATE `users` SET `user_type` = 'user', `tier_level` = 2 WHERE `user_type` IS NULL OR `user_type` = '' OR `user_type` = 'fan';

-- Create admin permissions table
CREATE TABLE IF NOT EXISTS `admin_permissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `display_name` varchar(150) NOT NULL,
  `description` text DEFAULT NULL,
  `category` varchar(50) DEFAULT 'general',
  `is_system_permission` tinyint(1) DEFAULT 0,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Insert default admin permissions
INSERT IGNORE INTO `admin_permissions` (`name`, `display_name`, `description`, `category`, `is_system_permission`) VALUES
('users_management', 'User Management', 'Manage users, creators, and accounts', 'users', 1),
('content_moderation', 'Content Moderation', 'Review and moderate content', 'content', 1),
('financial_management', 'Financial Management', 'Handle payments, withdrawals, and transactions', 'finance', 1),
('system_settings', 'System Settings', 'Configure system-wide settings', 'system', 1),
('analytics_access', 'Analytics Access', 'View analytics and reports', 'analytics', 0),
('support_management', 'Support Management', 'Handle customer support tickets', 'support', 0),
('marketing_tools', 'Marketing Tools', 'Access marketing and promotion tools', 'marketing', 0),
('api_management', 'API Management', 'Manage API keys and integrations', 'system', 0);

-- Create role permissions junction table
CREATE TABLE IF NOT EXISTS `role_permissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `role_id` int(11) NOT NULL,
  `permission_id` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `role_permission` (`role_id`, `permission_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Create security logs table
CREATE TABLE IF NOT EXISTS `security_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `user_type` enum('user','admin') DEFAULT 'user',
  `action` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `metadata` text DEFAULT NULL,
  `severity` enum('low','medium','high','critical') DEFAULT 'low',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`, `user_type`),
  KEY `action` (`action`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Create email verification tokens table
CREATE TABLE IF NOT EXISTS `email_verification_tokens` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `user_type` enum('user','admin') DEFAULT 'user',
  `email` varchar(255) NOT NULL,
  `token` varchar(255) NOT NULL,
  `expires_at` timestamp NOT NULL,
  `verified_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `token` (`token`),
  KEY `user_id` (`user_id`, `user_type`),
  KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 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_status` ON `users` (`status`);
CREATE INDEX IF NOT EXISTS `idx_users_email_verified` ON `users` (`email_verified_at`);
CREATE INDEX IF NOT EXISTS `idx_admins_status` ON `admins` (`status`);
CREATE INDEX IF NOT EXISTS `idx_admins_role` ON `admins` (`role`);

-- =====================================================
-- AUTHENTICATION SYSTEM SETUP COMPLETE
-- =====================================================