CREATE DATABASE IF NOT EXISTS ludo_master;
USE ludo_master;

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  uid VARCHAR(50) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL DEFAULT 'Rahul',
  email VARCHAR(100) NOT NULL DEFAULT 'mdrahul123134@gmail.com',
  phone VARCHAR(20) NOT NULL DEFAULT '8801405416822',
  referral_code VARCHAR(50) NOT NULL DEFAULT 'C6480645',
  balance DECIMAL(12,2) NOT NULL DEFAULT 1550.00,
  winning_balance DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  matches_played INT NOT NULL DEFAULT 0,
  referrals_count INT NOT NULL DEFAULT 4,
  total_earnings DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO users (uid, name, email, phone, referral_code, balance, winning_balance, matches_played, referrals_count, total_earnings)
VALUES ('5281', 'Rahul', 'mdrahul123134@gmail.com', '8801405416822', 'C6480645', 1550.00, 0.00, 0, 4, 0.00)
ON DUPLICATE KEY UPDATE uid=uid;

CREATE TABLE IF NOT EXISTS matches (
  id VARCHAR(50) PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  time VARCHAR(100) NOT NULL DEFAULT 'No Fixed Time',
  game_name VARCHAR(100) NOT NULL DEFAULT 'Ludo King',
  category ENUM('SPECIAL', 'REGULAR', 'AUTOMATIC') NOT NULL DEFAULT 'REGULAR',
  winning_prize DECIMAL(12,2) NOT NULL DEFAULT 0,
  entry_fee DECIMAL(12,2) NOT NULL DEFAULT 0,
  players_joined INT NOT NULL DEFAULT 0,
  total_players_needed INT NOT NULL DEFAULT 2,
  status ENUM('ACTIVE', 'FULL', 'CLOSED', 'COMPLETED') NOT NULL DEFAULT 'ACTIVE',
  room_code VARCHAR(50) DEFAULT NULL,
  winner_name VARCHAR(100) DEFAULT NULL,
  user_joined_name VARCHAR(100) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO matches (id, title, time, game_name, category, winning_prize, entry_fee, players_joined, total_players_needed, status) VALUES
('special-1', 'Special Match ~~1', 'No Fixed Time', 'Ludo King', 'SPECIAL', 4000, 2200, 0, 2, 'ACTIVE'),
('special-3', 'Special Match ~~3', 'No Fixed Time', 'Ludo King', 'SPECIAL', 2000, 1100, 1, 2, 'ACTIVE'),
('special-5', 'Special Match ~~5', 'No Fixed Time', 'Ludo King', 'SPECIAL', 1000, 550, 2, 2, 'CLOSED'),
('special-6', 'Special Match ~~6', '11:30 PM', 'Ludo King', 'SPECIAL', 540, 300, 0, 2, 'ACTIVE'),
('regular-1', 'Ludo Star Pro ~~1', 'No Fixed Time', 'Ludo King', 'REGULAR', 180, 100, 1, 2, 'ACTIVE'),
('regular-2', 'Ludo Star Pro ~~2', '09:00 PM', 'Ludo King', 'REGULAR', 90, 50, 0, 2, 'ACTIVE'),
('auto-1', 'Ludo King Auto ~~1', 'Instant Match', 'Ludo King', 'AUTOMATIC', 900, 500, 0, 2, 'ACTIVE')
ON DUPLICATE KEY UPDATE id=id;

CREATE TABLE IF NOT EXISTS transactions (
  id VARCHAR(100) PRIMARY KEY,
  type ENUM('DEPOSIT', 'WITHDRAW', 'ENTRY_FEE', 'WINNINGS', 'GIFT_SENT', 'GIFT_RECEIVED') NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  date VARCHAR(100) NOT NULL,
  status ENUM('PENDING', 'SUCCESS', 'FAILED') NOT NULL DEFAULT 'PENDING',
  description TEXT,
  payment_channel VARCHAR(50) DEFAULT NULL,
  account_no VARCHAR(50) DEFAULT NULL,
  transaction_id VARCHAR(100) DEFAULT NULL,
  user_uid VARCHAR(50) NOT NULL DEFAULT '5281',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS chat_messages (
  id VARCHAR(100) PRIMARY KEY,
  sender ENUM('user', 'bot') NOT NULL DEFAULT 'user',
  text TEXT NOT NULL,
  timestamp VARCHAR(20) NOT NULL,
  user_uid VARCHAR(50) NOT NULL DEFAULT '5281',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO chat_messages (id, sender, text, timestamp) VALUES
('msg-start-1', 'bot', 'নমস্কার! Ludo Master লাইভ কাস্টমার সাপোর্টে আপনাকে স্বাগতম। আপনার যেকোনো সমস্যায় (ডিপোজিট, উইথড্র, বা ম্যাচ জয়েনিং) এখানে সরাসরি প্রশ্ন করতে পারেন। আমি আপনাকে বাংলায় সহায়তা করব। 🎲', '12:00')
ON DUPLICATE KEY UPDATE id=id;

CREATE TABLE IF NOT EXISTS leaderboard (
  id INT AUTO_INCREMENT PRIMARY KEY,
  rank_no INT NOT NULL DEFAULT 0,
  name VARCHAR(100) NOT NULL,
  matches_played INT NOT NULL DEFAULT 0,
  earnings DECIMAL(12,2) NOT NULL DEFAULT 0,
  badge VARCHAR(100) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO leaderboard (rank_no, name, matches_played, earnings, badge) VALUES
(1, 'Rabiul Islam', 2566, 603680, 'Grandmaster'),
(2, 'মো: সুমন শেখ', 707, 145000, NULL),
(3, 'Mahmuda', 1834, 120500, NULL),
(4, '★★MD:AL,AMIN★★', 651, 85300, NULL),
(5, '☄️✨☠️WON SAKIB☠️✨☄️', 723, 82100, NULL),
(6, 'SOHEL.RANA', 1396, 78900, NULL),
(7, 'kishor islam', 2845, 75420, NULL),
(8, 'ovi', 1459, 62000, NULL),
(9, 'নিরব', 672, 51200, NULL),
(10, 'prio raihan', 1182, 49500, NULL),
(11, 'Rubel king', 2438, 46100, NULL),
(12, 'saidul', 820, 38200, NULL)
ON DUPLICATE KEY UPDATE id=id;
