-- BattleMania Advanced - Complete MySQL Database Schema
-- Generated from existing Laravel migrations
-- Date: 2025-01-19


-- Drop existing tables if they exist

-- Create sessions table
CREATE TABLE `sessions` (
    `id` varchar(255) NOT NULL,
    `user_id` bigint unsigned DEFAULT NULL,
    `ip_address` varchar(45) DEFAULT NULL,
    `user_agent` text,
    `payload` longtext NOT NULL,
    `last_activity` int NOT NULL,
    PRIMARY KEY (`id`),
    KEY `sessions_user_id_index` (`user_id`),
    KEY `sessions_last_activity_index` (`last_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create games table
CREATE TABLE `games` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    `slug` varchar(255) NOT NULL,
    `platform` enum('mobile','pc','console') NOT NULL,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `games_slug_unique` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create levels table
CREATE TABLE `levels` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    `min_xp` int NOT NULL,
    `min_matches` int NOT NULL DEFAULT '0',
    `min_agreement_rate` decimal(3,2) NOT NULL DEFAULT '0.00',
    `requires_kyc` tinyint(1) NOT NULL DEFAULT '0',
    `can_create_challenges` tinyint(1) NOT NULL DEFAULT '1',
    `can_host_free_tournaments` tinyint(1) NOT NULL DEFAULT '0',
    `can_host_paid_tournaments` tinyint(1) NOT NULL DEFAULT '0',
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create badges table
CREATE TABLE `badges` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    `description` text NOT NULL,
    `icon` varchar(255) DEFAULT NULL,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create user_badges table
CREATE TABLE `user_badges` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `user_id` bigint unsigned NOT NULL,
    `badge_id` bigint unsigned NOT NULL,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `user_badges_user_id_badge_id_unique` (`user_id`,`badge_id`),
    KEY `user_badges_badge_id_foreign` (`badge_id`),
    CONSTRAINT `user_badges_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `user_badges_badge_id_foreign` FOREIGN KEY (`badge_id`) REFERENCES `badges` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create challenges table
CREATE TABLE `challenges` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `creator_id` bigint unsigned NOT NULL,
    `accepter_id` bigint unsigned DEFAULT NULL,
    `game_id` bigint unsigned NOT NULL,
    `wager_amount` decimal(10,2) NOT NULL,
    `best_of` int NOT NULL DEFAULT '1',
    `rules_text` text,
    `status` enum('open','accepted','completed','disputed') NOT NULL DEFAULT 'open',
    `result` enum('creator_win','accepter_win','none') NOT NULL DEFAULT 'none',
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `challenges_creator_id_foreign` (`creator_id`),
    KEY `challenges_accepter_id_foreign` (`accepter_id`),
    KEY `challenges_game_id_foreign` (`game_id`),
    CONSTRAINT `challenges_creator_id_foreign` FOREIGN KEY (`creator_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `challenges_accepter_id_foreign` FOREIGN KEY (`accepter_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `challenges_game_id_foreign` FOREIGN KEY (`game_id`) REFERENCES `games` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add MySQL-optimized indexes for leaderboard performance
ALTER TABLE `challenges` ADD INDEX `idx_creator_result_status` (`creator_id`, `result`, `status`);
ALTER TABLE `challenges` ADD INDEX `idx_accepter_result_status` (`accepter_id`, `result`, `status`);
ALTER TABLE `challenges` ADD INDEX `idx_creator_result_status_date` (`creator_id`, `result`, `status`, `updated_at`);
ALTER TABLE `challenges` ADD INDEX `idx_accepter_result_status_date` (`accepter_id`, `result`, `status`, `updated_at`);

-- Create match_proofs table
CREATE TABLE `match_proofs` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `challenge_id` bigint unsigned NOT NULL,
    `user_id` bigint unsigned NOT NULL,
    `file_url` varchar(255) NOT NULL,
    `notes` text,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `match_proofs_challenge_id_foreign` (`challenge_id`),
    KEY `match_proofs_user_id_foreign` (`user_id`),
    CONSTRAINT `match_proofs_challenge_id_foreign` FOREIGN KEY (`challenge_id`) REFERENCES `challenges` (`id`) ON DELETE CASCADE,
    CONSTRAINT `match_proofs_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create disputes table
CREATE TABLE `disputes` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `challenge_id` bigint unsigned NOT NULL,
    `opened_by` bigint unsigned NOT NULL,
    `reason` text NOT NULL,
    `status` enum('open','resolved') NOT NULL DEFAULT 'open',
    `resolution` text,
    `admin_id` bigint unsigned DEFAULT NULL,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `disputes_challenge_id_foreign` (`challenge_id`),
    KEY `disputes_opened_by_foreign` (`opened_by`),
    KEY `disputes_admin_id_foreign` (`admin_id`),
    CONSTRAINT `disputes_challenge_id_foreign` FOREIGN KEY (`challenge_id`) REFERENCES `challenges` (`id`) ON DELETE CASCADE,
    CONSTRAINT `disputes_opened_by_foreign` FOREIGN KEY (`opened_by`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `disputes_admin_id_foreign` FOREIGN KEY (`admin_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create tournaments table
CREATE TABLE `tournaments` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `host_id` bigint unsigned NOT NULL,
    `game_id` bigint unsigned NOT NULL,
    `type` enum('knockout','round_robin') NOT NULL,
    `title` varchar(255) NOT NULL,
    `description` text,
    `entry_fee` decimal(10,2) NOT NULL DEFAULT '0.00',
    `prize_pool` decimal(10,2) NOT NULL DEFAULT '0.00',
    `livestream_link` varchar(255) DEFAULT NULL,
    `status` enum('upcoming','ongoing','completed') NOT NULL DEFAULT 'upcoming',
    `bracket_json` json DEFAULT NULL,
    `starts_at` timestamp NULL DEFAULT NULL,
    `ends_at` timestamp NULL DEFAULT NULL,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `tournaments_host_id_foreign` (`host_id`),
    KEY `tournaments_game_id_foreign` (`game_id`),
    CONSTRAINT `tournaments_host_id_foreign` FOREIGN KEY (`host_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `tournaments_game_id_foreign` FOREIGN KEY (`game_id`) REFERENCES `games` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create tournament_matches table
CREATE TABLE `tournament_matches` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `tournament_id` bigint unsigned NOT NULL,
    `round` int NOT NULL,
    `player_a_id` bigint unsigned NOT NULL,
    `player_b_id` bigint unsigned NOT NULL,
    `result` enum('player_a_win','player_b_win','none') NOT NULL DEFAULT 'none',
    `dispute_id` bigint unsigned DEFAULT NULL,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `tournament_matches_tournament_id_foreign` (`tournament_id`),
    KEY `tournament_matches_player_a_id_foreign` (`player_a_id`),
    KEY `tournament_matches_player_b_id_foreign` (`player_b_id`),
    KEY `tournament_matches_dispute_id_foreign` (`dispute_id`),
    CONSTRAINT `tournament_matches_tournament_id_foreign` FOREIGN KEY (`tournament_id`) REFERENCES `tournaments` (`id`) ON DELETE CASCADE,
    CONSTRAINT `tournament_matches_player_a_id_foreign` FOREIGN KEY (`player_a_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `tournament_matches_player_b_id_foreign` FOREIGN KEY (`player_b_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `tournament_matches_dispute_id_foreign` FOREIGN KEY (`dispute_id`) REFERENCES `disputes` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create wallet_transactions table
CREATE TABLE `wallet_transactions` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `user_id` bigint unsigned NOT NULL,
    `type` enum('deposit','withdraw','wager','prize','fee','refund') NOT NULL,
    `amount` decimal(10,2) NOT NULL,
    `status` enum('pending','completed','failed','cancelled') NOT NULL DEFAULT 'pending',
    `reference` varchar(255) DEFAULT NULL,
    `provider` varchar(255) DEFAULT NULL,
    `description` text,
    `metadata` json DEFAULT NULL,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `wallet_transactions_user_id_foreign` (`user_id`),
    CONSTRAINT `wallet_transactions_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add MySQL-optimized indexes for winnings leaderboard performance
ALTER TABLE `wallet_transactions` ADD INDEX `idx_user_type_status` (`user_id`, `type`, `status`);
ALTER TABLE `wallet_transactions` ADD INDEX `idx_user_type_status_date` (`user_id`, `type`, `status`, `created_at`);

-- Create notifications table
CREATE TABLE `notifications` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `user_id` bigint unsigned NOT NULL,
    `type` varchar(255) NOT NULL,
    `title` varchar(255) NOT NULL,
    `body` text NOT NULL,
    `data` json DEFAULT NULL,
    `read_at` timestamp NULL DEFAULT NULL,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `notifications_user_id_read_at_index` (`user_id`,`read_at`),
    CONSTRAINT `notifications_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create cache table
CREATE TABLE `cache` (
    `key` varchar(255) NOT NULL,
    `value` mediumtext NOT NULL,
    `expiration` int NOT NULL,
    PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create notification_providers table
CREATE TABLE `notification_providers` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    `type` enum('email','sms') NOT NULL,
    `display_name` varchar(255) NOT NULL,
    `configuration` json NOT NULL,
    `is_active` tinyint(1) NOT NULL DEFAULT '0',
    `is_default` tinyint(1) NOT NULL DEFAULT '0',
    `priority` int NOT NULL DEFAULT '1',
    `test_results` json DEFAULT NULL,
    `last_tested_at` timestamp NULL DEFAULT NULL,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `notification_providers_name_type_unique` (`name`,`type`),
    KEY `notification_providers_type_is_active_priority_index` (`type`,`is_active`,`priority`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create notification_settings table
CREATE TABLE `notification_settings` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `key` varchar(255) NOT NULL,
    `value` text,
    `description` text,
    `type` enum('string','boolean','integer','json') NOT NULL DEFAULT 'string',
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `notification_settings_key_unique` (`key`),
    KEY `notification_settings_key_index` (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create popup_notifications table
CREATE TABLE `popup_notifications` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `title` varchar(255) NOT NULL,
    `message` text NOT NULL,
    `type` enum('info','warning','success','announcement') NOT NULL DEFAULT 'info',
    `is_active` tinyint(1) NOT NULL DEFAULT '1',
    `start_date` timestamp NULL DEFAULT NULL,
    `end_date` timestamp NULL DEFAULT NULL,
    `target_users` json DEFAULT NULL,
    `created_by` bigint unsigned NOT NULL,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `popup_notifications_is_active_start_date_end_date_index` (`is_active`,`start_date`,`end_date`),
    KEY `popup_notifications_created_by_foreign` (`created_by`),
    CONSTRAINT `popup_notifications_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create posts table
CREATE TABLE `posts` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `title` varchar(255) NOT NULL,
    `slug` varchar(255) NOT NULL,
    `content` text NOT NULL,
    `excerpt` text,
    `type` enum('blog','news','highlight','tournament','update') NOT NULL,
    `status` enum('draft','published','archived') NOT NULL DEFAULT 'draft',
    `featured_image` varchar(255) DEFAULT NULL,
    `metadata` json DEFAULT NULL,
    `author_id` bigint unsigned NOT NULL,
    `published_at` timestamp NULL DEFAULT NULL,
    `views` int NOT NULL DEFAULT '0',
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `posts_slug_unique` (`slug`),
    KEY `posts_type_status_index` (`type`,`status`),
    KEY `posts_published_at_status_index` (`published_at`,`status`),
    KEY `posts_author_id_foreign` (`author_id`),
    CONSTRAINT `posts_author_id_foreign` FOREIGN KEY (`author_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create user_popup_notifications table
CREATE TABLE `user_popup_notifications` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `user_id` bigint unsigned NOT NULL,
    `popup_notification_id` bigint unsigned NOT NULL,
    `seen_at` timestamp NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `user_popup_notifications_user_id_popup_notification_id_unique` (`user_id`,`popup_notification_id`),
    KEY `user_popup_notifications_seen_at_index` (`seen_at`),
    KEY `user_popup_notifications_popup_notification_id_foreign` (`popup_notification_id`),
    CONSTRAINT `user_popup_notifications_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `user_popup_notifications_popup_notification_id_foreign` FOREIGN KEY (`popup_notification_id`) REFERENCES `popup_notifications` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create platform_settings table
CREATE TABLE `platform_settings` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `key` varchar(255) NOT NULL,
    `value` text,
    `type` varchar(255) NOT NULL DEFAULT 'string',
    `group` varchar(255) NOT NULL DEFAULT 'general',
    `description` text,
    `is_encrypted` tinyint(1) NOT NULL DEFAULT '0',
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `platform_settings_key_unique` (`key`),
    KEY `platform_settings_group_key_index` (`group`,`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create migrations table
CREATE TABLE `migrations` (
    `id` int unsigned NOT NULL AUTO_INCREMENT,
    `migration` varchar(255) NOT NULL,
    `batch` int NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Insert sample data
INSERT INTO `games` (`id`, `name`, `slug`, `platform`, `created_at`, `updated_at`) VALUES
(1, 'FIFA 24', 'fifa-24', 'console', NOW(), NOW()),
(2, 'eFootball', 'efootball', 'mobile', NOW(), NOW()),
(3, 'Fortnite', 'fortnite', 'pc', NOW(), NOW()),
(4, 'Call of Duty: Mobile', 'cod-mobile', 'mobile', NOW(), NOW()),
(5, 'PUBG Mobile', 'pubg-mobile', 'mobile', NOW(), NOW());

INSERT INTO `levels` (`id`, `name`, `min_xp`, `min_matches`, `min_agreement_rate`, `requires_kyc`, `can_create_challenges`, `can_host_free_tournaments`, `can_host_paid_tournaments`, `created_at`, `updated_at`) VALUES
(1, 'Rookie', 0, 0, 0.00, 0, 1, 0, 0, NOW(), NOW()),
(2, 'Amateur', 100, 5, 0.70, 0, 1, 1, 0, NOW(), NOW()),
(3, 'Pro', 500, 20, 0.80, 0, 1, 1, 1, NOW(), NOW()),
(4, 'Elite', 1500, 50, 0.90, 1, 1, 1, 1, NOW(), NOW()),
(5, 'Legend', 5000, 100, 0.95, 1, 1, 1, 1, NOW(), NOW());

INSERT INTO `badges` (`id`, `name`, `description`, `icon`, `created_at`, `updated_at`) VALUES
(1, 'First Win', 'Win your first match', '🏆', NOW(), NOW()),
(2, 'Tournament Champion', 'Win a tournament', '👑', NOW(), NOW()),
(3, 'Verified Player', 'Complete KYC verification', '✅', NOW(), NOW()),
(4, 'Fair Player', 'Maintain 90%+ agreement rate', '🤝', NOW(), NOW()),
(5, 'Active Player', 'Play 50+ matches', '🎮', NOW(), NOW());