-- Database: dokan_hub
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+06:00";

-- ১. অ্যাডমিন টেবিল
CREATE TABLE `admins` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(50) NOT NULL UNIQUE,
  `password` VARCHAR(255) NOT NULL,
  `full_name` VARCHAR(100) NOT NULL,
  `role` ENUM('super_admin', 'moderator') DEFAULT 'super_admin',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ২. ইউজার টেবিল
CREATE TABLE `users` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `phone` VARCHAR(20) NOT NULL UNIQUE,
  `email` VARCHAR(100) NULL,
  `password` VARCHAR(255) NOT NULL,
  `api_token` VARCHAR(64) DEFAULT NULL,
  `status` ENUM('active', 'banned') DEFAULT 'active',
  `referral_code` VARCHAR(20) NOT NULL UNIQUE,
  `referred_by` VARCHAR(20) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ৩. ওয়ালেট টেবিল
CREATE TABLE `wallets` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL UNIQUE,
  `deposit_balance` DECIMAL(10,2) DEFAULT 0.00,
  `winning_balance` DECIMAL(10,2) DEFAULT 0.00,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ৪. গেমস টেবিল
CREATE TABLE `games` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  `slug` VARCHAR(50) NOT NULL UNIQUE,
  `icon` VARCHAR(255) DEFAULT NULL,
  `banner` VARCHAR(255) DEFAULT NULL,
  `status` ENUM('active', 'inactive') DEFAULT 'active',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ৫. টুর্নামেন্ট টেবিল
CREATE TABLE `tournaments` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `game_id` INT(11) NOT NULL,
  `title` VARCHAR(150) NOT NULL,
  `match_type` ENUM('free', 'paid') NOT NULL DEFAULT 'paid',
  `match_category` ENUM('solo', 'duo', 'squad') NOT NULL DEFAULT 'solo',
  `map` VARCHAR(50) DEFAULT 'Bermuda',
  `version` VARCHAR(50) DEFAULT 'TPP',
  `entry_fee` DECIMAL(10,2) DEFAULT 0.00,
  `prize_pool` DECIMAL(10,2) DEFAULT 0.00,
  `per_kill` DECIMAL(10,2) DEFAULT 0.00,
  `match_time` DATETIME NOT NULL,
  `total_slots` INT(11) NOT NULL DEFAULT 48,
  `filled_slots` INT(11) NOT NULL DEFAULT 0,
  `room_id` VARCHAR(50) DEFAULT NULL,
  `room_password` VARCHAR(50) DEFAULT NULL,
  `room_status` ENUM('hidden', 'visible') DEFAULT 'hidden',
  `status` ENUM('upcoming', 'ongoing', 'completed', 'canceled') DEFAULT 'upcoming',
  `rules` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`game_id`) REFERENCES `games`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ৬. টুর্নামেন্ট পার্টিসিপেন্টস টেবিল
CREATE TABLE `tournament_participants` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `tournament_id` INT(11) NOT NULL,
  `user_id` INT(11) NOT NULL,
  `in_game_name` VARCHAR(100) NOT NULL,
  `in_game_id` VARCHAR(50) DEFAULT NULL,
  `slot_number` INT(11) NOT NULL,
  `team_name` VARCHAR(50) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_participant` (`tournament_id`, `user_id`),
  UNIQUE KEY `unique_slot` (`tournament_id`, `slot_number`),
  FOREIGN KEY (`tournament_id`) REFERENCES `tournaments`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ৭. ট্রানজ্যাকশন হিস্ট্রি
CREATE TABLE `transactions` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  `type` ENUM('credit', 'debit') NOT NULL,
  `category` ENUM('deposit', 'withdrawal', 'match_join', 'match_win', 'referral', 'refund') NOT NULL,
  `amount` DECIMAL(10,2) NOT NULL,
  `balance_after` DECIMAL(10,2) NOT NULL,
  `reference_id` VARCHAR(100) DEFAULT NULL,
  `description` VARCHAR(255) DEFAULT NULL,
  `status` ENUM('pending', 'completed', 'failed') DEFAULT 'completed',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ৮. ডিপোজিট রিকোয়েস্ট টেবিল
CREATE TABLE `deposit_requests` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  `method` ENUM('bkash', 'nagad', 'rocket') NOT NULL,
  `sender_number` VARCHAR(20) NOT NULL,
  `transaction_id` VARCHAR(100) NOT NULL UNIQUE,
  `amount` DECIMAL(10,2) NOT NULL,
  `status` ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
  `admin_note` VARCHAR(255) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ৯. উইথড্র রিকোয়েস্ট টেবিল
CREATE TABLE `withdrawal_requests` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  `method` ENUM('bkash', 'nagad', 'rocket') NOT NULL,
  `receiver_number` VARCHAR(20) NOT NULL,
  `amount` DECIMAL(10,2) NOT NULL,
  `status` ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
  `admin_note` VARCHAR(255) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ১০. ম্যাচ রেজাল্ট ও উইনার টেবিল
CREATE TABLE `match_results` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `tournament_id` INT(11) NOT NULL,
  `user_id` INT(11) NOT NULL,
  `rank` INT(11) NOT NULL,
  `kills` INT(11) DEFAULT 0,
  `winning_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`tournament_id`) REFERENCES `tournaments`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ১১. নোটিফিকেশন টেবিল
CREATE TABLE `notifications` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) DEFAULT NULL, -- NULL হলে সবার জন্য
  `title` VARCHAR(150) NOT NULL,
  `message` TEXT NOT NULL,
  `is_read` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ১২. রেফারেল টেবিল
CREATE TABLE `referrals` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `referrer_id` INT(11) NOT NULL,
  `referee_id` INT(11) NOT NULL,
  `bonus_amount` DECIMAL(10,2) DEFAULT 0.00,
  `status` ENUM('pending', 'completed') DEFAULT 'completed',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`referrer_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`referee_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ১৩. অ্যাপ ও পেমেন্ট সেটিংস
CREATE TABLE `settings` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `setting_key` VARCHAR(50) NOT NULL UNIQUE,
  `setting_value` TEXT DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ১৪. অ্যাডমিন অ্যাকটিভিটি লগ
CREATE TABLE `admin_logs` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `admin_id` INT(11) NOT NULL,
  `action` VARCHAR(100) NOT NULL,
  `details` TEXT DEFAULT NULL,
  `ip_address` VARCHAR(50) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ১৫. টিম টেবিল (Duo/Squad এর জন্য)
CREATE TABLE `teams` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `tournament_id` INT(11) NOT NULL,
  `team_name` VARCHAR(100) NOT NULL,
  `leader_id` INT(11) NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- প্রাথমিক ডাটা (Default Data)
INSERT INTO `admins` (`username`, `password`, `full_name`, `role`) VALUES
('admin', '$2y$10$w8T0Uv5X3t5YyE3i1Cq33O.t6C4Fz7R2S1I0e9B3a8K7J2G1l7gG.', 'Super Admin', 'super_admin');
-- অ্যাডমিন ডিফল্ট পাসওয়ার্ড: admin123

INSERT INTO `games` (`name`, `slug`, `icon`, `status`) VALUES
('Free Fire', 'free-fire', 'freefire_icon.png', 'active'),
('Ludo King', 'ludo', 'ludo_icon.png', 'active');

INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES
('min_deposit', '50'),
('min_withdrawal', '100'),
('bkash_number', '01700000000'),
('nagad_number', '01800000000'),
('rocket_number', '01900000000'),
('referral_bonus', '10'),
('app_notice', 'Welcome to Dokan Hub Tournament App! Join matches and win prizes!');

COMMIT;