-- =========================================================
-- KHILADIZONE FF - MYSQL DATABASE SCHEMA (phpMyAdmin Ready)
-- Database: khiladiz_khiladizone
-- =========================================================

SET FOREIGN_KEY_CHECKS = 0;

-- 1. Users Table
CREATE TABLE IF NOT EXISTS `users` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `mongo_id` VARCHAR(24) DEFAULT NULL,
  `name` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `username` VARCHAR(255) NOT NULL,
  `password` VARCHAR(255) DEFAULT NULL,
  `google_id` VARCHAR(255) DEFAULT NULL,
  `fcm_token` VARCHAR(512) DEFAULT NULL,
  `wallet_notifications_enabled` TINYINT(1) NOT NULL DEFAULT 1,
  `kyc_status` ENUM('PENDING', 'VERIFIED', 'REJECTED') NOT NULL DEFAULT 'PENDING',
  `wallet_deposit` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `wallet_winnings` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `wallet_bonus` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `wallet_referral` DECIMAL(12,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 `uk_users_email` (`email`),
  UNIQUE KEY `uk_users_username` (`username`),
  KEY `idx_users_mongo_id` (`mongo_id`),
  KEY `idx_users_google_id` (`google_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Orders Table (Razorpay Orders)
CREATE TABLE IF NOT EXISTS `orders` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `mongo_id` VARCHAR(24) DEFAULT NULL,
  `order_id` VARCHAR(255) NOT NULL,
  `user_id` BIGINT UNSIGNED DEFAULT NULL,
  `amount` DECIMAL(12,2) NOT NULL,
  `amount_in_paise` BIGINT NOT NULL,
  `currency` VARCHAR(10) NOT NULL DEFAULT 'INR',
  `receipt` VARCHAR(255) DEFAULT NULL,
  `status` ENUM('created', 'paid', 'failed') NOT NULL DEFAULT 'created',
  `payment_id` VARCHAR(255) DEFAULT NULL,
  `signature` TEXT 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 `uk_orders_order_id` (`order_id`),
  KEY `idx_orders_user_id` (`user_id`),
  KEY `idx_orders_payment_id` (`payment_id`),
  CONSTRAINT `fk_orders_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Transactions Table (Wallet Ledger)
CREATE TABLE IF NOT EXISTS `transactions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `mongo_id` VARCHAR(24) DEFAULT NULL,
  `user_id` BIGINT UNSIGNED DEFAULT NULL,
  `user_email` VARCHAR(255) DEFAULT NULL,
  `order_id` VARCHAR(255) DEFAULT NULL,
  `payment_id` VARCHAR(255) DEFAULT NULL,
  `type` ENUM('DEPOSIT', 'WITHDRAWAL', 'BONUS', 'WINNING', 'ENTRY_FEE') NOT NULL,
  `amount` DECIMAL(12,2) NOT NULL,
  `status` ENUM('SUCCESS', 'PENDING', 'FAILED') NOT NULL DEFAULT 'SUCCESS',
  `description` TEXT DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_transactions_payment_id` (`payment_id`),
  KEY `idx_transactions_user_id` (`user_id`),
  KEY `idx_transactions_order_id` (`order_id`),
  CONSTRAINT `fk_transactions_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Matches Table (Tournament Matches)
CREATE TABLE IF NOT EXISTS `matches` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `mongo_id` VARCHAR(24) DEFAULT NULL,
  `match_code` VARCHAR(50) DEFAULT NULL,
  `title` VARCHAR(255) NOT NULL,
  `game_category` VARCHAR(100) NOT NULL,
  `match_type` VARCHAR(50) NOT NULL DEFAULT 'SOLO',
  `entry_fee` INT NOT NULL DEFAULT 5,
  `prize_pool` INT NOT NULL DEFAULT 200,
  `per_kill` INT NOT NULL DEFAULT 0,
  `max_players` INT NOT NULL DEFAULT 100,
  `joined_players` INT NOT NULL DEFAULT 0,
  `map_name` VARCHAR(100) NOT NULL DEFAULT 'Bermuda',
  `status` ENUM('UPCOMING', 'LIVE', 'COMPLETED') NOT NULL DEFAULT 'UPCOMING',
  `start_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_matches_category` (`game_category`),
  KEY `idx_matches_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Webhook Events Table (Razorpay Webhook Log & Deduplication)
CREATE TABLE IF NOT EXISTS `webhook_events` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `mongo_id` VARCHAR(24) DEFAULT NULL,
  `event_id` VARCHAR(255) NOT NULL,
  `event_type` VARCHAR(100) NOT NULL,
  `payment_id` VARCHAR(255) DEFAULT NULL,
  `order_id` VARCHAR(255) DEFAULT NULL,
  `payload` LONGTEXT DEFAULT NULL,
  `processed_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_webhook_event_id` (`event_id`),
  KEY `idx_webhook_payment_id` (`payment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;