-- HYBRID ENHANCED AUTHENTICATION SYSTEM
-- For Laravel Backend + Node.js API + React Frontend + Mobile App
-- Run this in phpMyAdmin or any SQL client

-- Magic Links Table
CREATE TABLE IF NOT EXISTS `magic_links` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL,
  `token` varchar(255) NOT NULL,
  `expires_at` timestamp NOT NULL,
  `used_at` timestamp NULL DEFAULT NULL,
  `ip_address` varchar(45) NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `token` (`token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Auth Tokens Table (for API and Mobile)
CREATE TABLE IF NOT EXISTS `auth_tokens` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `token` varchar(255) NOT NULL,
  `type` enum('qr_login','api','mobile_app','pin','jwt','refresh') NOT NULL,
  `expires_at` timestamp NOT NULL,
  `used_at` timestamp NULL DEFAULT NULL,
  `device_info` json NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `token` (`token`),
  INDEX `user_type` (`user_id`, `type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Phone Verifications Table
CREATE TABLE IF NOT EXISTS `phone_verifications` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `phone` varchar(20) NOT NULL,
  `code` varchar(10) NOT NULL,
  `provider` enum('twilio','aws_sns') NOT NULL,
  `attempts` int(11) DEFAULT 0,
  `expires_at` timestamp NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Modern Passkeys Table (WebAuthn for Web/Mobile)
CREATE TABLE IF NOT EXISTS `passkeys` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `credential_id` varchar(255) NOT NULL,
  `public_key` text NOT NULL,
  `device_name` varchar(100) NOT NULL,
  `device_type` enum('mobile','tablet','desktop','security_key') NOT NULL,
  `authenticator_type` enum('face_id','touch_id','fingerprint','windows_hello','yubikey') NOT NULL,
  `counter` bigint(20) DEFAULT 0,
  `last_used_at` timestamp NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `credential_id` (`credential_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Trusted Devices Table
CREATE TABLE IF NOT EXISTS `trusted_devices` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `device_name` varchar(100) NOT NULL,
  `device_fingerprint` varchar(255) NOT NULL,
  `device_type` enum('mobile','tablet','desktop') NOT NULL,
  `browser` varchar(50) NULL,
  `ip_address` varchar(45) NOT NULL,
  `location` varchar(100) NULL,
  `last_used_at` timestamp NULL,
  `expires_at` timestamp NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `device_fingerprint` (`device_fingerprint`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Social Accounts Table
CREATE TABLE IF NOT EXISTS `social_accounts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `provider` varchar(50) NOT NULL,
  `provider_id` varchar(100) NOT NULL,
  `provider_email` varchar(255) NULL,
  `provider_name` varchar(255) NULL,
  `avatar` varchar(500) NULL,
  `access_token` text NULL,
  `refresh_token` text NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `provider_account` (`provider`, `provider_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Referral System Tables
CREATE TABLE IF NOT EXISTS `referrals` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `referrer_id` bigint(20) unsigned NOT NULL,
  `referred_id` bigint(20) unsigned NOT NULL,
  `referral_code` varchar(50) NOT NULL,
  `status` enum('pending','completed','cancelled') DEFAULT 'pending',
  `commission_earned` decimal(10,2) DEFAULT 0.00,
  `commission_paid` tinyint(1) DEFAULT 0,
  `conversion_date` timestamp NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `referral_code` (`referral_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Activity Logs Table
CREATE TABLE IF NOT EXISTS `activity_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `action` varchar(100) NOT NULL,
  `details` text NULL,
  `ip_address` varchar(45) NOT NULL,
  `user_agent` text NULL,
  `platform` enum('web','mobile','api') DEFAULT 'web',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `user_action` (`user_id`, `action`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Social Media Posts Table
CREATE TABLE IF NOT EXISTS `social_media_posts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `platform` enum('instagram','twitter','facebook','tiktok','snapchat') NOT NULL,
  `post_id` varchar(255) NULL,
  `content` text NOT NULL,
  `media_url` varchar(500) NULL,
  `status` enum('pending','posted','failed') DEFAULT 'pending',
  `posted_at` timestamp NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Cross-Platform Posts Table
CREATE TABLE IF NOT EXISTS `cross_platform_posts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `original_post_id` bigint(20) unsigned NULL,
  `content` text NOT NULL,
  `media_urls` json NULL,
  `platforms` json NOT NULL,
  `scheduled_at` timestamp NULL,
  `status` enum('draft','scheduled','posting','completed','failed') DEFAULT 'draft',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Login Rate Limiting Table
CREATE TABLE IF NOT EXISTS `login_attempts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `ip_address` varchar(45) NOT NULL,
  `email` varchar(255) NULL,
  `attempts` int(11) DEFAULT 1,
  `blocked_until` timestamp NULL,
  `platform` enum('web','mobile','api') DEFAULT 'web',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `ip_email` (`ip_address`, `email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- OAuth States Table
CREATE TABLE IF NOT EXISTS `oauth_states` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `state` varchar(255) NOT NULL,
  `provider` varchar(50) NOT NULL,
  `user_id` bigint(20) unsigned NULL,
  `platform` enum('web','mobile') DEFAULT 'web',
  `expires_at` timestamp NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `state` (`state`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Regional Restrictions Table
CREATE TABLE IF NOT EXISTS `regional_restrictions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `country_code` varchar(2) NOT NULL,
  `country_name` varchar(100) NOT NULL,
  `allow_social_login` tinyint(1) DEFAULT 1,
  `allow_biometric` tinyint(1) DEFAULT 1,
  `allow_phone_otp` tinyint(1) DEFAULT 1,
  `data_retention_days` int(11) DEFAULT 365,
  `require_age_verification` tinyint(1) DEFAULT 0,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `country_code` (`country_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Add columns to existing users table
ALTER TABLE `users` 
ADD COLUMN IF NOT EXISTS `first_name` varchar(100) NULL,
ADD COLUMN IF NOT EXISTS `last_name` varchar(100) NULL,
ADD COLUMN IF NOT EXISTS `pin_code` varchar(255) NULL,
ADD COLUMN IF NOT EXISTS `temp_password` varchar(255) NULL,
ADD COLUMN IF NOT EXISTS `temp_password_expires` timestamp NULL,
ADD COLUMN IF NOT EXISTS `mfa_enabled` tinyint(1) DEFAULT 0,
ADD COLUMN IF NOT EXISTS `mfa_method` enum('email','phone','both') DEFAULT 'email',
ADD COLUMN IF NOT EXISTS `phone_verified_at` timestamp NULL,
ADD COLUMN IF NOT EXISTS `login_attempts` int(11) DEFAULT 0,
ADD COLUMN IF NOT EXISTS `locked_until` timestamp NULL,
ADD COLUMN IF NOT EXISTS `preferred_login_method` enum('password','pin','passkey','social') DEFAULT 'password',
ADD COLUMN IF NOT EXISTS `last_login_method` varchar(50) NULL,
ADD COLUMN IF NOT EXISTS `google_id` varchar(100) NULL,
ADD COLUMN IF NOT EXISTS `facebook_id` varchar(100) NULL,
ADD COLUMN IF NOT EXISTS `referral_code` varchar(50) NULL UNIQUE,
ADD COLUMN IF NOT EXISTS `referral_earnings` decimal(10,2) DEFAULT 0.00,
ADD COLUMN IF NOT EXISTS `total_referrals` int(11) DEFAULT 0,
ADD COLUMN IF NOT EXISTS `instagram_url` varchar(255) NULL,
ADD COLUMN IF NOT EXISTS `twitter_url` varchar(255) NULL,
ADD COLUMN IF NOT EXISTS `tiktok_url` varchar(255) NULL,
ADD COLUMN IF NOT EXISTS `youtube_url` varchar(255) NULL,
ADD COLUMN IF NOT EXISTS `snapchat_url` varchar(255) NULL,
ADD COLUMN IF NOT EXISTS `onlyfans_url` varchar(255) NULL,
ADD COLUMN IF NOT EXISTS `api_token` varchar(255) NULL,
ADD COLUMN IF NOT EXISTS `mobile_device_id` varchar(255) NULL;

-- Admin Settings Updates (All 50+ Controls)
ALTER TABLE `admin_settings`
ADD COLUMN IF NOT EXISTS `enable_social_login` enum('on','off') DEFAULT 'on',
ADD COLUMN IF NOT EXISTS `enable_magic_links` enum('on','off') DEFAULT 'on',
ADD COLUMN IF NOT EXISTS `enable_phone_otp` enum('on','off') DEFAULT 'on',
ADD COLUMN IF NOT EXISTS `enable_pin_login` enum('on','off') DEFAULT 'on',
ADD COLUMN IF NOT EXISTS `enable_passkeys` enum('on','off') DEFAULT 'on',
ADD COLUMN IF NOT EXISTS `google_oauth_client_id` varchar(200) NULL,
ADD COLUMN IF NOT EXISTS `facebook_app_id` varchar(200) NULL,
ADD COLUMN IF NOT EXISTS `facebook` varchar(255) NULL,
ADD COLUMN IF NOT EXISTS `twitter` varchar(255) NULL,
ADD COLUMN IF NOT EXISTS `instagram` varchar(255) NULL,
ADD COLUMN IF NOT EXISTS `tiktok` varchar(255) NULL,
ADD COLUMN IF NOT EXISTS `youtube` varchar(255) NULL,
ADD COLUMN IF NOT EXISTS `enable_activity_logs` enum('on','off') DEFAULT 'on',
ADD COLUMN IF NOT EXISTS `session_timeout` int(11) DEFAULT 1440,
ADD COLUMN IF NOT EXISTS `max_login_attempts` int(11) DEFAULT 5,
ADD COLUMN IF NOT EXISTS `lockout_duration` int(11) DEFAULT 15,
ADD COLUMN IF NOT EXISTS `instagram_access_token` varchar(500) NULL,
ADD COLUMN IF NOT EXISTS `twitter_api_key` varchar(200) NULL,
ADD COLUMN IF NOT EXISTS `twitter_api_secret` varchar(200) NULL,
ADD COLUMN IF NOT EXISTS `facebook_page_token` varchar(500) NULL,
ADD COLUMN IF NOT EXISTS `enable_qr_codes` enum('on','off') DEFAULT 'on',
ADD COLUMN IF NOT EXISTS `enable_social_sharing` enum('on','off') DEFAULT 'on',
ADD COLUMN IF NOT EXISTS `enable_gdpr_compliance` enum('on','off') DEFAULT 'off',
ADD COLUMN IF NOT EXISTS `enable_regional_restrictions` enum('on','off') DEFAULT 'off',
ADD COLUMN IF NOT EXISTS `data_retention_days` int(11) DEFAULT 365,
ADD COLUMN IF NOT EXISTS `require_cookie_consent` enum('on','off') DEFAULT 'off',
ADD COLUMN IF NOT EXISTS `enable_right_to_be_forgotten` enum('on','off') DEFAULT 'off',
ADD COLUMN IF NOT EXISTS `enable_auto_moderation` enum('on','off') DEFAULT 'off',
ADD COLUMN IF NOT EXISTS `enable_manual_approval` enum('on','off') DEFAULT 'off',
ADD COLUMN IF NOT EXISTS `enable_nsfw_detection` enum('on','off') DEFAULT 'on',
ADD COLUMN IF NOT EXISTS `enable_spam_filter` enum('on','off') DEFAULT 'on',
ADD COLUMN IF NOT EXISTS `enable_payments` enum('on','off') DEFAULT 'on',
ADD COLUMN IF NOT EXISTS `enable_stripe` enum('on','off') DEFAULT 'on',
ADD COLUMN IF NOT EXISTS `enable_paypal` enum('on','off') DEFAULT 'on',
ADD COLUMN IF NOT EXISTS `enable_crypto_payments` enum('on','off') DEFAULT 'off',
ADD COLUMN IF NOT EXISTS `enable_subscriptions` enum('on','off') DEFAULT 'on',
ADD COLUMN IF NOT EXISTS `enable_cdn` enum('on','off') DEFAULT 'on',
ADD COLUMN IF NOT EXISTS `enable_caching` enum('on','off') DEFAULT 'on',
ADD COLUMN IF NOT EXISTS `enable_queue_processing` enum('on','off') DEFAULT 'on',
ADD COLUMN IF NOT EXISTS `enable_realtime_notifications` enum('on','off') DEFAULT 'on',
ADD COLUMN IF NOT EXISTS `enable_live_battles` enum('on','off') DEFAULT 'on',
ADD COLUMN IF NOT EXISTS `enable_ai_integration` enum('on','off') DEFAULT 'off',
ADD COLUMN IF NOT EXISTS `enable_vault_system` enum('on','off') DEFAULT 'on',
ADD COLUMN IF NOT EXISTS `enable_media_processing` enum('on','off') DEFAULT 'on',
ADD COLUMN IF NOT EXISTS `enable_api_access` enum('on','off') DEFAULT 'on',
ADD COLUMN IF NOT EXISTS `api_rate_limit` int(11) DEFAULT 1000,
ADD COLUMN IF NOT EXISTS `enable_webhooks` enum('on','off') DEFAULT 'on',
ADD COLUMN IF NOT EXISTS `enable_third_party_integrations` enum('on','off') DEFAULT 'on',
ADD COLUMN IF NOT EXISTS `enable_mobile_app` enum('on','off') DEFAULT 'on',
ADD COLUMN IF NOT EXISTS `mobile_api_version` varchar(10) DEFAULT 'v1',
ADD COLUMN IF NOT EXISTS `jwt_secret` varchar(255) NULL,
ADD COLUMN IF NOT EXISTS `jwt_expiry` int(11) DEFAULT 3600;

-- Test User with all features
INSERT IGNORE INTO `users` (
    `name`, `first_name`, `last_name`, `username`, `email`, `password`, 
    `date`, `status`, `verified_id`, `referral_code`, `created_at`, `updated_at`
) VALUES (
    'Test User', 'Test', 'User', 'testuser', 'testuser@example.com',
    '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
    NOW(), 'active', 'yes', 'TEST2024', NOW(), NOW()
);