-- MySQL dump 10.13  Distrib 8.0.45, for Linux (x86_64)
--
-- Host: localhost    Database: ludodragon_db
-- ------------------------------------------------------
-- Server version	8.0.45-0ubuntu0.24.04.1

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `countries`
--

DROP TABLE IF EXISTS `countries`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `countries` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `country_code` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `country_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `currency_code` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `currency_symbol` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_gateway` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `supported_payment_gateways_json` json DEFAULT NULL,
  `deposit_rules_json` json DEFAULT NULL,
  `withdrawal_rules_json` json DEFAULT NULL,
  `default_language` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `supported_languages_json` json DEFAULT NULL,
  `matchmaking_region` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_country_code` (`country_code`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `daily_login_claims`
--

DROP TABLE IF EXISTS `daily_login_claims`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `daily_login_claims` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `claim_date` date NOT NULL,
  `streak_day` int NOT NULL,
  `reward_day_number` int NOT NULL,
  `cash_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `dragon_coins` bigint unsigned NOT NULL DEFAULT '0',
  `wallet_type` enum('play_balance','withdrawable_balance') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'play_balance',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_daily_login_claim_user_date` (`user_id`,`claim_date`),
  KEY `idx_daily_login_claim_user` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=46 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `daily_login_reward_days`
--

DROP TABLE IF EXISTS `daily_login_reward_days`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `daily_login_reward_days` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `day_number` int NOT NULL,
  `title` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `cash_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `dragon_coins` bigint unsigned NOT NULL DEFAULT '0',
  `wallet_type` enum('play_balance','withdrawable_balance') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'play_balance',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_daily_login_day` (`day_number`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `daily_login_rewards`
--

DROP TABLE IF EXISTS `daily_login_rewards`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `daily_login_rewards` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `reward_day` int NOT NULL,
  `coin_amount` bigint NOT NULL,
  `claim_date` date NOT NULL,
  `streak_count` int NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_user_claim_date` (`user_id`,`claim_date`),
  KEY `idx_user_id` (`user_id`),
  CONSTRAINT `fk_daily_login_rewards_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `deposit_offer_claims`
--

DROP TABLE IF EXISTS `deposit_offer_claims`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `deposit_offer_claims` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `offer_id` bigint unsigned NOT NULL,
  `user_id` bigint unsigned NOT NULL,
  `deposit_request_id` bigint unsigned NOT NULL,
  `deposit_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `bonus_cash_awarded` decimal(18,2) NOT NULL DEFAULT '0.00',
  `bonus_dragon_coins_awarded` bigint unsigned NOT NULL DEFAULT '0',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_offer_claim_deposit` (`deposit_request_id`),
  KEY `idx_offer_claim_user` (`user_id`),
  KEY `idx_offer_claim_offer` (`offer_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `deposit_offers`
--

DROP TABLE IF EXISTS `deposit_offers`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `deposit_offers` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `country_code` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `currency_code` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `min_deposit` decimal(18,2) NOT NULL DEFAULT '0.00',
  `max_deposit` decimal(18,2) DEFAULT NULL,
  `bonus_cash_percent` decimal(5,2) NOT NULL DEFAULT '0.00',
  `bonus_cash_fixed` decimal(18,2) NOT NULL DEFAULT '0.00',
  `bonus_cash_wallet_type` enum('play_balance','withdrawable_balance') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'play_balance',
  `bonus_dragon_coins` bigint unsigned NOT NULL DEFAULT '0',
  `first_deposit_only` tinyint(1) NOT NULL DEFAULT '0',
  `max_claims_per_user` int DEFAULT NULL,
  `start_at` datetime DEFAULT NULL,
  `end_at` datetime DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `priority` int NOT NULL DEFAULT '0',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_deposit_offers_active` (`is_active`),
  KEY `idx_deposit_offers_country` (`country_code`),
  KEY `idx_deposit_offers_priority` (`priority`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `deposit_requests`
--

DROP TABLE IF EXISTS `deposit_requests`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `deposit_requests` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `request_ref` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_id` bigint unsigned NOT NULL,
  `provider` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'modempay',
  `payout_method` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'mobile_money',
  `amount` decimal(18,2) NOT NULL,
  `currency` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'GMD',
  `network` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `account_number` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `account_name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` enum('pending','success','failed','cancelled') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
  `provider_payment_intent_id` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `provider_payment_link` text COLLATE utf8mb4_unicode_ci,
  `provider_reference` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `provider_status` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `provider_raw_response` longtext COLLATE utf8mb4_unicode_ci,
  `paid_at` datetime DEFAULT NULL,
  `failed_at` datetime DEFAULT NULL,
  `cancelled_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_deposit_request_ref` (`request_ref`),
  KEY `idx_deposit_user_id` (`user_id`),
  KEY `idx_deposit_status` (`status`),
  KEY `idx_deposit_provider_intent` (`provider_payment_intent_id`),
  KEY `idx_deposit_provider_reference` (`provider_reference`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `device_change_requests`
--

DROP TABLE IF EXISTS `device_change_requests`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `device_change_requests` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `device_id` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
  `linked_email` varchar(190) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `requested_email` varchar(190) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` enum('pending','approved','rejected') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
  `admin_note` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `game_entry_fees`
--

DROP TABLE IF EXISTS `game_entry_fees`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `game_entry_fees` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `game_mode_id` bigint unsigned NOT NULL,
  `title` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `country_code` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `entry_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `entry_currency_type` enum('cash','dragon_coins') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `cash_currency_code` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reward_pool_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `reward_pool_currency_code` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `sort_order` int NOT NULL DEFAULT '0',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_game_stakes_mode` (`game_mode_id`),
  KEY `idx_game_stakes_country` (`country_code`),
  KEY `idx_game_stakes_active` (`is_active`),
  KEY `idx_game_stakes_sort` (`sort_order`),
  CONSTRAINT `fk_game_stakes_country` FOREIGN KEY (`country_code`) REFERENCES `countries` (`country_code`) ON DELETE SET NULL,
  CONSTRAINT `fk_game_stakes_mode` FOREIGN KEY (`game_mode_id`) REFERENCES `game_modes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `game_modes`
--

DROP TABLE IF EXISTS `game_modes`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `game_modes` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `mode_key` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `title` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `max_players` int NOT NULL,
  `entry_wallet_type` enum('play_balance','dragon_coins') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `reward_wallet_type` enum('play_balance','withdrawable_balance') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_country_locked` tinyint(1) NOT NULL DEFAULT '1',
  `match_duration_seconds` int NOT NULL,
  `turn_duration_seconds` int NOT NULL,
  `max_skips` int NOT NULL DEFAULT '3',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_game_modes_mode_key` (`mode_key`),
  KEY `idx_game_modes_active` (`is_active`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `game_prize_rules`
--

DROP TABLE IF EXISTS `game_prize_rules`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `game_prize_rules` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `entry_fee_id` bigint unsigned NOT NULL,
  `platform_fee_percent` decimal(5,2) NOT NULL DEFAULT '0.00',
  `winner_count` int NOT NULL DEFAULT '1',
  `first_prize_percent` decimal(5,2) NOT NULL DEFAULT '100.00',
  `second_prize_percent` decimal(5,2) DEFAULT NULL,
  `third_prize_percent` decimal(5,2) DEFAULT NULL,
  `tie_rule_type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'score_then_position',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_game_prize_rules_entry_fee_id` (`entry_fee_id`),
  KEY `idx_game_prize_rules_active` (`is_active`),
  CONSTRAINT `fk_game_prize_rules_entry_fee` FOREIGN KEY (`entry_fee_id`) REFERENCES `game_entry_fees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `game_rule_displays`
--

DROP TABLE IF EXISTS `game_rule_displays`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `game_rule_displays` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `entry_fee_id` bigint unsigned NOT NULL,
  `title` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `rules_json` json NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_game_rule_displays_entry_fee_id` (`entry_fee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `global_combat_entries`
--

DROP TABLE IF EXISTS `global_combat_entries`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `global_combat_entries` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `event_id` bigint unsigned NOT NULL,
  `user_id` bigint unsigned NOT NULL,
  `entry_no` int NOT NULL DEFAULT '1',
  `entry_ref` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `entry_dragon_coins` bigint unsigned NOT NULL DEFAULT '0',
  `allowed_moves_snapshot` int NOT NULL DEFAULT '30',
  `active_run_id` bigint unsigned DEFAULT NULL,
  `final_score` bigint DEFAULT NULL,
  `best_score_snapshot` bigint DEFAULT NULL,
  `status` enum('joined','started','finished','submitted','disqualified','cancelled') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'joined',
  `started_at` datetime DEFAULT NULL,
  `finished_at` datetime DEFAULT NULL,
  `submitted_at` datetime DEFAULT NULL,
  `disqualified_at` datetime DEFAULT NULL,
  `cancelled_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_global_combat_entries_entry_ref` (`entry_ref`),
  UNIQUE KEY `uq_global_combat_entries_event_user_entry_no` (`event_id`,`user_id`,`entry_no`),
  KEY `idx_global_combat_entries_event_id` (`event_id`),
  KEY `idx_global_combat_entries_user_id` (`user_id`),
  KEY `idx_global_combat_entries_status` (`status`),
  KEY `idx_global_combat_entries_active_run_id` (`active_run_id`),
  CONSTRAINT `fk_global_combat_entries_active_run` FOREIGN KEY (`active_run_id`) REFERENCES `global_combat_runs` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_global_combat_entries_event` FOREIGN KEY (`event_id`) REFERENCES `global_combat_events` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_global_combat_entries_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `global_combat_events`
--

DROP TABLE IF EXISTS `global_combat_events`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `global_combat_events` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `event_ref` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `title` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `entry_dragon_coins` bigint unsigned NOT NULL DEFAULT '0',
  `allowed_moves` int NOT NULL DEFAULT '30',
  `prize_pool_cash` decimal(18,2) NOT NULL DEFAULT '0.00',
  `prize_currency_code` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'USD',
  `reward_wallet_type` enum('withdrawable_balance','play_balance') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'withdrawable_balance',
  `max_players` int DEFAULT NULL,
  `max_entries_per_user` int NOT NULL DEFAULT '0',
  `leaderboard_size` int NOT NULL DEFAULT '100',
  `leaderboard_rule` enum('highest_score','latest_score') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'highest_score',
  `tie_break_rule` enum('earlier_submission','later_submission') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'earlier_submission',
  `status` enum('draft','upcoming','live','ended','settled','cancelled') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'draft',
  `start_at` datetime DEFAULT NULL,
  `end_at` datetime DEFAULT NULL,
  `settled_at` datetime DEFAULT NULL,
  `cancelled_at` datetime DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_global_combat_events_event_ref` (`event_ref`),
  KEY `idx_global_combat_events_status` (`status`),
  KEY `idx_global_combat_events_is_active` (`is_active`),
  KEY `idx_global_combat_events_start_at` (`start_at`),
  KEY `idx_global_combat_events_end_at` (`end_at`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `global_combat_move_logs`
--

DROP TABLE IF EXISTS `global_combat_move_logs`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `global_combat_move_logs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `run_id` bigint unsigned NOT NULL,
  `move_no` int NOT NULL,
  `client_action_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `player_action_json` json NOT NULL,
  `server_result_json` json NOT NULL,
  `score_before` bigint NOT NULL DEFAULT '0',
  `score_delta` bigint NOT NULL DEFAULT '0',
  `score_after` bigint NOT NULL DEFAULT '0',
  `moves_remaining_before` int NOT NULL,
  `moves_remaining_after` int NOT NULL,
  `state_version_before` bigint unsigned NOT NULL,
  `state_version_after` bigint unsigned NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_global_combat_move_logs_run_move_no` (`run_id`,`move_no`),
  UNIQUE KEY `uq_global_combat_move_logs_client_action` (`run_id`,`client_action_id`),
  KEY `idx_global_combat_move_logs_run_id` (`run_id`),
  CONSTRAINT `fk_global_combat_move_logs_run` FOREIGN KEY (`run_id`) REFERENCES `global_combat_runs` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `global_combat_payouts`
--

DROP TABLE IF EXISTS `global_combat_payouts`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `global_combat_payouts` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `event_id` bigint unsigned NOT NULL,
  `entry_id` bigint unsigned NOT NULL,
  `score_id` bigint unsigned NOT NULL,
  `user_id` bigint unsigned NOT NULL,
  `final_rank` int NOT NULL,
  `cash_reward` decimal(18,2) NOT NULL DEFAULT '0.00',
  `dragon_coin_reward` bigint unsigned NOT NULL DEFAULT '0',
  `wallet_transaction_cash_ref` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `wallet_transaction_coin_ref` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` enum('pending','paid','failed','cancelled') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
  `paid_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_global_combat_payouts_score_id` (`score_id`),
  UNIQUE KEY `uq_global_combat_payouts_event_user` (`event_id`,`user_id`),
  KEY `idx_global_combat_payouts_event_id` (`event_id`),
  KEY `idx_global_combat_payouts_user_id` (`user_id`),
  KEY `idx_global_combat_payouts_status` (`status`),
  KEY `fk_global_combat_payouts_entry` (`entry_id`),
  CONSTRAINT `fk_global_combat_payouts_entry` FOREIGN KEY (`entry_id`) REFERENCES `global_combat_entries` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_global_combat_payouts_event` FOREIGN KEY (`event_id`) REFERENCES `global_combat_events` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_global_combat_payouts_score` FOREIGN KEY (`score_id`) REFERENCES `global_combat_scores` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_global_combat_payouts_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `global_combat_reward_rules`
--

DROP TABLE IF EXISTS `global_combat_reward_rules`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `global_combat_reward_rules` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `event_id` bigint unsigned NOT NULL,
  `rank_from` int NOT NULL,
  `rank_to` int NOT NULL,
  `cash_reward` decimal(18,2) NOT NULL DEFAULT '0.00',
  `dragon_coin_reward` bigint unsigned NOT NULL DEFAULT '0',
  `title` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_global_combat_reward_rules_event_id` (`event_id`),
  KEY `idx_global_combat_reward_rules_is_active` (`is_active`),
  CONSTRAINT `fk_global_combat_reward_rules_event` FOREIGN KEY (`event_id`) REFERENCES `global_combat_events` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `global_combat_runs`
--

DROP TABLE IF EXISTS `global_combat_runs`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `global_combat_runs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `run_ref` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `event_id` bigint unsigned NOT NULL,
  `entry_id` bigint unsigned NOT NULL,
  `user_id` bigint unsigned NOT NULL,
  `status` enum('active','finished','cancelled','expired','invalidated') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'active',
  `server_seed` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `rng_nonce` bigint unsigned NOT NULL DEFAULT '0',
  `moves_allowed` int NOT NULL DEFAULT '30',
  `moves_used` int NOT NULL DEFAULT '0',
  `moves_remaining` int NOT NULL DEFAULT '30',
  `current_score` bigint NOT NULL DEFAULT '0',
  `state_version` bigint unsigned NOT NULL DEFAULT '1',
  `state_json` json DEFAULT NULL,
  `started_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `finished_at` datetime DEFAULT NULL,
  `expires_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_global_combat_runs_run_ref` (`run_ref`),
  UNIQUE KEY `uq_global_combat_runs_entry_id` (`entry_id`),
  KEY `idx_global_combat_runs_event_id` (`event_id`),
  KEY `idx_global_combat_runs_user_id` (`user_id`),
  KEY `idx_global_combat_runs_status` (`status`),
  CONSTRAINT `fk_global_combat_runs_entry` FOREIGN KEY (`entry_id`) REFERENCES `global_combat_entries` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_global_combat_runs_event` FOREIGN KEY (`event_id`) REFERENCES `global_combat_events` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_global_combat_runs_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `global_combat_scores`
--

DROP TABLE IF EXISTS `global_combat_scores`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `global_combat_scores` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `event_id` bigint unsigned NOT NULL,
  `entry_id` bigint unsigned NOT NULL,
  `run_id` bigint unsigned DEFAULT NULL,
  `user_id` bigint unsigned NOT NULL,
  `total_score` bigint NOT NULL DEFAULT '0',
  `moves_used` int NOT NULL DEFAULT '0',
  `points_breakdown_json` json DEFAULT NULL,
  `game_summary_json` json DEFAULT NULL,
  `rank_position` int DEFAULT NULL,
  `is_rewarded` tinyint(1) NOT NULL DEFAULT '0',
  `is_disqualified` tinyint(1) NOT NULL DEFAULT '0',
  `score_status` enum('pending','accepted','flagged','rejected') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'accepted',
  `submitted_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_global_combat_scores_entry_id` (`entry_id`),
  KEY `idx_global_combat_scores_event_id` (`event_id`),
  KEY `idx_global_combat_scores_user_id` (`user_id`),
  KEY `idx_global_combat_scores_rank_position` (`rank_position`),
  KEY `idx_global_combat_scores_total_score` (`total_score`),
  KEY `idx_global_combat_scores_status` (`score_status`),
  KEY `idx_global_combat_scores_event_user_score` (`event_id`,`user_id`,`total_score`,`submitted_at`),
  KEY `idx_global_combat_scores_run_id` (`run_id`),
  CONSTRAINT `fk_global_combat_scores_entry` FOREIGN KEY (`entry_id`) REFERENCES `global_combat_entries` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_global_combat_scores_event` FOREIGN KEY (`event_id`) REFERENCES `global_combat_events` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_global_combat_scores_run` FOREIGN KEY (`run_id`) REFERENCES `global_combat_runs` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_global_combat_scores_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `google_device_bindings`
--

DROP TABLE IF EXISTS `google_device_bindings`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `google_device_bindings` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `device_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `google_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_id` bigint unsigned DEFAULT NULL,
  `player_name` varchar(120) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(190) COLLATE utf8mb4_unicode_ci 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 `uq_device_id` (`device_id`),
  UNIQUE KEY `uq_google_id` (`google_id`),
  KEY `idx_user_id` (`user_id`),
  CONSTRAINT `fk_google_device_bindings_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `match_action_logs`
--

DROP TABLE IF EXISTS `match_action_logs`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `match_action_logs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `match_id` bigint unsigned NOT NULL,
  `user_id` bigint unsigned NOT NULL,
  `turn_no` int NOT NULL,
  `action_type` enum('roll','move','skip','timeout','quit','system') COLLATE utf8mb4_unicode_ci NOT NULL,
  `client_action_id` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `request_json` json DEFAULT NULL,
  `result_json` json DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_match_action_logs_client_action` (`match_id`,`client_action_id`),
  KEY `idx_match_action_logs_match` (`match_id`,`turn_no`),
  KEY `idx_match_action_logs_user` (`user_id`),
  CONSTRAINT `fk_match_action_logs_match` FOREIGN KEY (`match_id`) REFERENCES `matches` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_match_action_logs_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=482 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `match_players`
--

DROP TABLE IF EXISTS `match_players`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `match_players` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `match_id` bigint unsigned NOT NULL,
  `user_id` bigint unsigned NOT NULL,
  `seat_no` int NOT NULL,
  `join_status` enum('joined','active','disconnected','reconnected','quit','left','finished') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'joined',
  `finish_rank` int DEFAULT NULL,
  `is_winner` tinyint(1) NOT NULL DEFAULT '0',
  `turns_taken` int NOT NULL DEFAULT '0',
  `moves_made` int NOT NULL DEFAULT '0',
  `skips_used` int NOT NULL DEFAULT '0',
  `disconnected_at` datetime DEFAULT NULL,
  `reconnected_at` datetime DEFAULT NULL,
  `final_score` int DEFAULT NULL,
  `refund_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `prize_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_match_players_match_user` (`match_id`,`user_id`),
  UNIQUE KEY `uq_match_players_match_seat` (`match_id`,`seat_no`),
  KEY `idx_match_players_user` (`user_id`),
  KEY `idx_match_players_status` (`join_status`),
  KEY `idx_match_players_match_seat` (`match_id`,`seat_no`),
  CONSTRAINT `fk_match_players_match` FOREIGN KEY (`match_id`) REFERENCES `matches` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_match_players_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=253 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `match_players_bak_20260317`
--

DROP TABLE IF EXISTS `match_players_bak_20260317`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `match_players_bak_20260317` (
  `id` bigint unsigned NOT NULL DEFAULT '0',
  `match_id` bigint unsigned NOT NULL,
  `user_id` bigint unsigned NOT NULL,
  `seat_no` int NOT NULL,
  `join_status` enum('joined','active','disconnected','reconnected','quit','left','finished') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'joined',
  `finish_rank` int DEFAULT NULL,
  `is_winner` tinyint(1) NOT NULL DEFAULT '0',
  `turns_taken` int NOT NULL DEFAULT '0',
  `moves_made` int NOT NULL DEFAULT '0',
  `skips_used` int NOT NULL DEFAULT '0',
  `disconnected_at` datetime DEFAULT NULL,
  `reconnected_at` datetime DEFAULT NULL,
  `final_score` int DEFAULT NULL,
  `refund_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `prize_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `match_results`
--

DROP TABLE IF EXISTS `match_results`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `match_results` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `match_id` bigint unsigned NOT NULL,
  `result_type` enum('normal','tie','refund','cancelled') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'normal',
  `total_entry_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `total_refund_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `platform_fee_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `total_prize_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `settlement_status` enum('pending','completed','failed') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
  `result_json` json DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_match_results_match` (`match_id`),
  CONSTRAINT `fk_match_results_match` FOREIGN KEY (`match_id`) REFERENCES `matches` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `match_runtime_state`
--

DROP TABLE IF EXISTS `match_runtime_state`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `match_runtime_state` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `match_id` bigint unsigned NOT NULL,
  `phase` enum('countdown','roll','move','ended','live') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'countdown',
  `current_turn_no` int NOT NULL DEFAULT '1',
  `current_turn_seat_no` int DEFAULT '1',
  `current_turn_user_id` bigint unsigned DEFAULT NULL,
  `turn_started_at` datetime DEFAULT NULL,
  `turn_ends_at` datetime DEFAULT NULL,
  `state_version` bigint unsigned NOT NULL DEFAULT '1',
  `state_json` json DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_match_runtime_state_match_id` (`match_id`),
  KEY `idx_match_runtime_state_turn_user` (`current_turn_user_id`),
  CONSTRAINT `fk_match_runtime_state_match` FOREIGN KEY (`match_id`) REFERENCES `matches` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_match_runtime_state_user` FOREIGN KEY (`current_turn_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `match_state`
--

DROP TABLE IF EXISTS `match_state`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `match_state` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `match_id` bigint unsigned NOT NULL,
  `phase` enum('waiting','roll','move','finished') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'waiting',
  `current_turn_seat_no` int DEFAULT NULL,
  `turn_started_at` datetime DEFAULT NULL,
  `turn_expires_at` datetime DEFAULT NULL,
  `dice_value` int DEFAULT NULL,
  `selected_token_seat_no` int DEFAULT NULL,
  `selected_token_index` int DEFAULT NULL,
  `winner_seat_no` int DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_match_state_match` (`match_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `match_templates`
--

DROP TABLE IF EXISTS `match_templates`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `match_templates` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `match_type` enum('cash','global_combat') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'cash',
  `mode` enum('duel','arena') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'duel',
  `entry_fee` decimal(18,2) NOT NULL DEFAULT '0.00',
  `entry_coin` decimal(18,2) NOT NULL DEFAULT '0.00',
  `entry_wallet_type` enum('play_balance','withdrawable_balance','dragon_coins') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'play_balance',
  `reward_wallet_type` enum('play_balance','withdrawable_balance') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'withdrawable_balance',
  `max_players` int NOT NULL DEFAULT '2',
  `min_players_to_start` int NOT NULL DEFAULT '2',
  `winner_count` int NOT NULL DEFAULT '1',
  `match_duration_seconds` int NOT NULL DEFAULT '300',
  `turn_duration_seconds` int NOT NULL DEFAULT '7',
  `max_skips` int NOT NULL DEFAULT '3',
  `require_equal_turns` tinyint(1) NOT NULL DEFAULT '1',
  `allow_zero_move_refund` tinyint(1) NOT NULL DEFAULT '1',
  `no_refund_after_first_move` tinyint(1) NOT NULL DEFAULT '1',
  `disconnect_grace_seconds` int NOT NULL DEFAULT '30',
  `country_code` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `platform_fee_percent` decimal(5,2) NOT NULL DEFAULT '0.00',
  `prize_split_json` json DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `sort_order` int NOT NULL DEFAULT '0',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_active` (`is_active`),
  KEY `idx_country` (`country_code`),
  KEY `idx_mode` (`mode`),
  KEY `idx_match_templates_mode_country_active` (`mode`,`country_code`,`is_active`),
  KEY `idx_match_templates_match_type_active` (`match_type`,`is_active`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `match_templates_bak_20260317`
--

DROP TABLE IF EXISTS `match_templates_bak_20260317`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `match_templates_bak_20260317` (
  `id` bigint unsigned NOT NULL DEFAULT '0',
  `title` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `match_type` enum('cash','global_combat') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'cash',
  `mode` enum('duel','arena') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'duel',
  `entry_fee` decimal(18,2) NOT NULL DEFAULT '0.00',
  `entry_coin` decimal(18,2) NOT NULL DEFAULT '0.00',
  `max_players` int NOT NULL DEFAULT '2',
  `country_code` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `platform_fee_percent` decimal(5,2) NOT NULL DEFAULT '0.00',
  `prize_split_json` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `sort_order` int NOT NULL DEFAULT '0',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `match_turn_logs`
--

DROP TABLE IF EXISTS `match_turn_logs`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `match_turn_logs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `match_id` bigint unsigned NOT NULL,
  `user_id` bigint unsigned NOT NULL,
  `turn_no` int NOT NULL,
  `action_no` int NOT NULL DEFAULT '1',
  `action_type` enum('turn_start','roll','move','skip','timeout','quit','disconnect','reconnect','turn_end','match_end') COLLATE utf8mb4_unicode_ci NOT NULL,
  `dice_value` tinyint unsigned DEFAULT NULL,
  `move_json` json DEFAULT NULL,
  `state_version_before` bigint unsigned DEFAULT NULL,
  `state_version_after` bigint unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_match_turn_logs_turn_action` (`match_id`,`turn_no`,`action_no`),
  KEY `idx_match_turn_logs_match` (`match_id`,`created_at`),
  KEY `idx_match_turn_logs_user` (`user_id`),
  CONSTRAINT `fk_match_turn_logs_match` FOREIGN KEY (`match_id`) REFERENCES `matches` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_match_turn_logs_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `match_wallet_entries`
--

DROP TABLE IF EXISTS `match_wallet_entries`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `match_wallet_entries` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `match_id` bigint unsigned NOT NULL,
  `user_id` bigint unsigned NOT NULL,
  `wallet_type` enum('play_balance','withdrawable_balance','dragon_coins') COLLATE utf8mb4_unicode_ci NOT NULL,
  `amount` decimal(18,2) NOT NULL,
  `status` enum('charged','refunded','settled') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'charged',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_match_user` (`match_id`,`user_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=251 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `matches`
--

DROP TABLE IF EXISTS `matches`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `matches` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `match_ref` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `game_mode_id` bigint unsigned NOT NULL,
  `entry_fee_id` bigint unsigned NOT NULL,
  `match_status` enum('waiting','starting','live','ended','cancelled') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'waiting',
  `country_code` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `room_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `max_players` int NOT NULL,
  `current_players` int NOT NULL DEFAULT '0',
  `entry_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `entry_currency_type` enum('cash','dragon_coins') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `cash_currency_code` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `started_at` datetime DEFAULT NULL,
  `ended_at` datetime DEFAULT NULL,
  `total_duration_seconds` int NOT NULL,
  `turn_duration_seconds` int NOT NULL,
  `winner_count` int NOT NULL DEFAULT '1',
  `platform_fee_percent` decimal(5,2) NOT NULL DEFAULT '0.00',
  `prize_pool_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `reward_currency_type` enum('cash','dragon_coins') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'cash',
  `reward_currency_code` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `settlement_status` enum('pending','processing','settled','failed') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
  `settled_at` datetime DEFAULT NULL,
  `authoritative_result_json` longtext COLLATE utf8mb4_unicode_ci,
  `edgegap_request_id` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `edgegap_status` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `edgegap_ip` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `edgegap_port` int DEFAULT NULL,
  `edgegap_last_checked_at` datetime DEFAULT NULL,
  `edgegap_error` text COLLATE utf8mb4_unicode_ci,
  `edgegap_server_secret` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `settlement_error` text COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_matches_match_ref` (`match_ref`),
  UNIQUE KEY `uq_matches_room_code` (`room_code`),
  KEY `idx_matches_status` (`match_status`),
  KEY `idx_matches_mode` (`game_mode_id`),
  KEY `idx_matches_stake` (`entry_fee_id`),
  KEY `idx_matches_country` (`country_code`),
  KEY `idx_matches_created_at` (`created_at`),
  KEY `idx_matches_edgegap_request_id` (`edgegap_request_id`),
  KEY `idx_matches_edgegap_status` (`edgegap_status`),
  CONSTRAINT `fk_matches_country` FOREIGN KEY (`country_code`) REFERENCES `countries` (`country_code`) ON DELETE SET NULL,
  CONSTRAINT `fk_matches_entry_fee` FOREIGN KEY (`entry_fee_id`) REFERENCES `game_entry_fees` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_matches_mode` FOREIGN KEY (`game_mode_id`) REFERENCES `game_modes` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=128 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `matches_bak_20260317`
--

DROP TABLE IF EXISTS `matches_bak_20260317`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `matches_bak_20260317` (
  `id` bigint unsigned NOT NULL DEFAULT '0',
  `match_ref` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `game_mode_id` bigint unsigned NOT NULL,
  `entry_fee_id` bigint unsigned NOT NULL,
  `match_status` enum('waiting','starting','live','ended','cancelled') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'waiting',
  `country_code` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `room_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `max_players` int NOT NULL,
  `current_players` int NOT NULL DEFAULT '0',
  `entry_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `entry_currency_type` enum('cash','dragon_coins') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `cash_currency_code` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `started_at` datetime DEFAULT NULL,
  `ended_at` datetime DEFAULT NULL,
  `total_duration_seconds` int NOT NULL,
  `turn_duration_seconds` int NOT NULL,
  `winner_count` int NOT NULL DEFAULT '1',
  `platform_fee_percent` decimal(5,2) NOT NULL DEFAULT '0.00',
  `prize_pool_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `reward_currency_type` enum('cash','dragon_coins') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'cash',
  `reward_currency_code` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `matchmaking_queue`
--

DROP TABLE IF EXISTS `matchmaking_queue`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `matchmaking_queue` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `game_mode_id` bigint unsigned NOT NULL,
  `entry_fee_id` bigint unsigned NOT NULL,
  `country_code` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` enum('searching','matched','cancelled','expired') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'searching',
  `network_session_id` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `matched_match_id` bigint unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_queue_user` (`user_id`),
  KEY `idx_queue_mode_stake_country_status` (`game_mode_id`,`entry_fee_id`,`country_code`,`status`),
  KEY `idx_queue_status_created` (`status`,`created_at`),
  KEY `idx_queue_match` (`matched_match_id`),
  KEY `fk_queue_country` (`country_code`),
  KEY `fk_queue_entry_fee` (`entry_fee_id`),
  KEY `idx_matchmaking_queue_user_status` (`user_id`,`status`),
  KEY `idx_matchmaking_queue_match` (`matched_match_id`),
  CONSTRAINT `fk_matchmaking_queue_match` FOREIGN KEY (`matched_match_id`) REFERENCES `matches` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_queue_country` FOREIGN KEY (`country_code`) REFERENCES `countries` (`country_code`) ON DELETE SET NULL,
  CONSTRAINT `fk_queue_entry_fee` FOREIGN KEY (`entry_fee_id`) REFERENCES `game_entry_fees` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_queue_match` FOREIGN KEY (`matched_match_id`) REFERENCES `matches` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_queue_mode` FOREIGN KEY (`game_mode_id`) REFERENCES `game_modes` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_queue_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=299 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `matchmaking_queue_bak_20260317`
--

DROP TABLE IF EXISTS `matchmaking_queue_bak_20260317`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `matchmaking_queue_bak_20260317` (
  `id` bigint unsigned NOT NULL DEFAULT '0',
  `user_id` bigint unsigned NOT NULL,
  `game_mode_id` bigint unsigned NOT NULL,
  `entry_fee_id` bigint unsigned NOT NULL,
  `country_code` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` enum('searching','matched','cancelled','expired') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'searching',
  `network_session_id` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `matched_match_id` bigint unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `player_profiles`
--

DROP TABLE IF EXISTS `player_profiles`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `player_profiles` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `phone_number` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `avatar_type` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT 'preset',
  `avatar_value` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `joined_date` datetime DEFAULT CURRENT_TIMESTAMP,
  `games_played` int NOT NULL DEFAULT '0',
  `wins` int NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `last_daily_login_claim_date` date DEFAULT NULL,
  `current_login_streak` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_user_id` (`user_id`),
  KEY `idx_phone_number` (`phone_number`),
  CONSTRAINT `fk_player_profiles_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `reward_settings`
--

DROP TABLE IF EXISTS `reward_settings`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `reward_settings` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `title` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `cash_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `dragon_coins` bigint unsigned NOT NULL DEFAULT '0',
  `wallet_type` enum('play_balance','withdrawable_balance') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'play_balance',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_reward_setting_key` (`setting_key`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `transaction_history`
--

DROP TABLE IF EXISTS `transaction_history`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `transaction_history` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `transaction_type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `balance_type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `direction` enum('credit','debit') COLLATE utf8mb4_unicode_ci NOT NULL,
  `amount` decimal(18,2) DEFAULT NULL,
  `currency_code` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `coin_amount` bigint DEFAULT NULL,
  `status` enum('pending','completed','failed','reversed') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'completed',
  `title` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `related_match_id` bigint unsigned DEFAULT NULL,
  `reference_id` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `country_code` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `mode_type` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_transaction_type` (`transaction_type`),
  KEY `idx_balance_type` (`balance_type`),
  KEY `idx_status` (`status`),
  KEY `idx_country_code` (`country_code`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_reference_id` (`reference_id`),
  CONSTRAINT `fk_transaction_history_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `user_daily_login_state`
--

DROP TABLE IF EXISTS `user_daily_login_state`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `user_daily_login_state` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `last_claim_date` date DEFAULT NULL,
  `streak_day` int NOT NULL DEFAULT '0',
  `total_claims` int NOT NULL DEFAULT '0',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_user_daily_login_state_user` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `user_payout_accounts`
--

DROP TABLE IF EXISTS `user_payout_accounts`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `user_payout_accounts` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `provider` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `payout_method` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `country_code` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `network` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `account_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `account_number` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_default` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_user_provider_method_country_default` (`user_id`,`provider`,`payout_method`,`country_code`,`is_default`),
  KEY `idx_user_payout_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `user_sessions`
--

DROP TABLE IF EXISTS `user_sessions`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `user_sessions` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `token_hash` char(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `device_id` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `platform` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `ip_address` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_agent` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `issued_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `expires_at` datetime NOT NULL,
  `last_used_at` datetime DEFAULT NULL,
  `revoked_at` datetime DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_user_sessions_token_hash` (`token_hash`),
  KEY `idx_user_sessions_user_id` (`user_id`),
  KEY `idx_user_sessions_expires_at` (`expires_at`),
  KEY `idx_user_sessions_is_active` (`is_active`),
  CONSTRAINT `fk_user_sessions_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=224 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `users`
--

DROP TABLE IF EXISTS `users`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `users` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `player_id` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `auth_provider` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'google',
  `google_id` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `firebase_uid` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `name` varchar(120) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(190) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `country_code` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `language_code` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_country_locked` tinyint(1) NOT NULL DEFAULT '0',
  `referral_code` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `referred_by_user_id` bigint unsigned DEFAULT NULL,
  `profile_completed` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `last_login_at` timestamp NULL DEFAULT NULL,
  `play_balance` decimal(18,2) NOT NULL DEFAULT '0.00',
  `withdrawable_balance` decimal(18,2) NOT NULL DEFAULT '0.00',
  `dragon_coins` decimal(18,2) NOT NULL DEFAULT '0.00',
  `first_deposit_bonus_given` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_player_id` (`player_id`),
  UNIQUE KEY `uq_google_id` (`google_id`),
  UNIQUE KEY `uq_firebase_uid` (`firebase_uid`),
  UNIQUE KEY `uq_referral_code` (`referral_code`),
  KEY `idx_country_code` (`country_code`),
  KEY `idx_referred_by_user_id` (`referred_by_user_id`),
  CONSTRAINT `fk_users_country` FOREIGN KEY (`country_code`) REFERENCES `countries` (`country_code`) ON DELETE SET NULL,
  CONSTRAINT `fk_users_referred_by` FOREIGN KEY (`referred_by_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `wallet_transactions`
--

DROP TABLE IF EXISTS `wallet_transactions`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `wallet_transactions` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `txn_ref` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_id` bigint unsigned NOT NULL,
  `wallet_type` enum('play_balance','withdrawable_balance','dragon_coins') COLLATE utf8mb4_unicode_ci NOT NULL,
  `entry_side` enum('credit','debit') COLLATE utf8mb4_unicode_ci NOT NULL,
  `amount` decimal(18,2) NOT NULL,
  `transaction_type` enum('deposit','deposit_bonus','match_entry','match_refund','match_win','global_combat_entry','global_combat_reward','withdraw_request','withdraw_reject_refund','withdraw_paid','referral_bonus','daily_login_reward','participation_reward','admin_adjustment') COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` enum('success','pending','failed','reversed') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'success',
  `related_type` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `related_id` bigint unsigned DEFAULT NULL,
  `description` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `balance_before` decimal(18,2) NOT NULL DEFAULT '0.00',
  `balance_after` decimal(18,2) NOT NULL DEFAULT '0.00',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `txn_ref` (`txn_ref`),
  KEY `idx_user_created` (`user_id`,`created_at`),
  KEY `idx_user_wallet` (`user_id`,`wallet_type`),
  KEY `idx_related` (`related_type`,`related_id`)
) ENGINE=InnoDB AUTO_INCREMENT=492 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `wallets`
--

DROP TABLE IF EXISTS `wallets`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `wallets` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `play_balance` decimal(18,2) NOT NULL DEFAULT '0.00',
  `withdrawable_balance` decimal(18,2) NOT NULL DEFAULT '0.00',
  `dragon_coins` bigint NOT NULL DEFAULT '0',
  `currency_code` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `has_received_first_deposit_bonus` tinyint(1) NOT NULL DEFAULT '0',
  `first_deposit_at` datetime 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 `uq_wallet_user_id` (`user_id`),
  CONSTRAINT `fk_wallets_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `withdrawal_requests`
--

DROP TABLE IF EXISTS `withdrawal_requests`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `withdrawal_requests` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `request_ref` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_id` bigint unsigned NOT NULL,
  `amount` decimal(18,2) NOT NULL,
  `payout_method` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `account_name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `account_number` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `bank_name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `note` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` enum('pending','approved','paid','rejected','cancelled') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
  `reviewed_by` bigint unsigned DEFAULT NULL,
  `reviewed_at` datetime DEFAULT NULL,
  `paid_at` datetime DEFAULT NULL,
  `reject_reason` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_withdrawal_request_ref` (`request_ref`),
  KEY `idx_withdrawal_user_id` (`user_id`),
  KEY `idx_withdrawal_status` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `withdrawals`
--

DROP TABLE IF EXISTS `withdrawals`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `withdrawals` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `amount` decimal(18,2) NOT NULL,
  `status` enum('pending','approved','rejected','paid') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
  `method` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `account_name` varchar(120) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `account_number` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `bank_name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `admin_note` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `requested_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `reviewed_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_status` (`user_id`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2026-03-31 16:31:47
