CREATE TABLE users(
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 telegram_id BIGINT NOT NULL UNIQUE,
 username VARCHAR(255) NULL,
 first_name VARCHAR(255) NOT NULL,
 last_name VARCHAR(255) NULL,
 balance BIGINT NOT NULL DEFAULT 0,
 signup_reward_given TINYINT(1) NOT NULL DEFAULT 0,
 referred_by BIGINT UNSIGNED NULL,
 blocked TINYINT(1) NOT NULL DEFAULT 0,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX(username),INDEX(referred_by)
)ENGINE=InnoDB;

CREATE TABLE games(
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 stake BIGINT UNSIGNED NOT NULL,
 user_move ENUM('rock','paper','scissors') NOT NULL,
 opponent_move ENUM('rock','paper','scissors') NOT NULL,
 result ENUM('win','loss','draw') NOT NULL,
 balance_change BIGINT NOT NULL,
 balance_before BIGINT NOT NULL,
 balance_after BIGINT NOT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX(user_id,created_at),
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
)ENGINE=InnoDB;

CREATE TABLE game_sessions(
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 chat_id BIGINT NOT NULL,
 message_id BIGINT NOT NULL,
 stake BIGINT UNSIGNED NOT NULL,
 token VARCHAR(64) NOT NULL UNIQUE,
 user_move VARCHAR(20) NULL,
 opponent_move VARCHAR(20) NULL,
 result VARCHAR(20) NULL,
 balance_change BIGINT NULL,
 status ENUM('pending','processing','done','expired') NOT NULL DEFAULT 'pending',
 expires_at DATETIME NOT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX(user_id,status),
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
)ENGINE=InnoDB;

CREATE TABLE callback_events(
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 callback_id VARCHAR(255) NOT NULL UNIQUE,
 user_id BIGINT UNSIGNED NULL,
 data VARCHAR(500) NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)ENGINE=InnoDB;

CREATE TABLE transactions(
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 type VARCHAR(60) NOT NULL,
 amount BIGINT NOT NULL,
 description VARCHAR(500) NOT NULL,
 balance_before BIGINT NOT NULL,
 balance_after BIGINT NOT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX(user_id,created_at),
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
)ENGINE=InnoDB;

CREATE TABLE referrals(
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 referrer_id BIGINT UNSIGNED NOT NULL,
 referred_id BIGINT UNSIGNED NOT NULL UNIQUE,
 reward BIGINT NOT NULL DEFAULT 0,
 status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(referrer_id) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY(referred_id) REFERENCES users(id) ON DELETE CASCADE
)ENGINE=InnoDB;

CREATE TABLE required_chats(
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 chat_id VARCHAR(100) NOT NULL UNIQUE,
 name VARCHAR(255) NOT NULL,
 join_link VARCHAR(500) NOT NULL,
 enabled TINYINT(1) NOT NULL DEFAULT 1,
 sort_order INT NOT NULL DEFAULT 0
)ENGINE=InnoDB;

CREATE TABLE withdrawals(
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 amount BIGINT UNSIGNED NOT NULL,
 status ENUM('pending','approved','rejected','paid') NOT NULL DEFAULT 'pending',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
)ENGINE=InnoDB;

CREATE TABLE settings(`key` VARCHAR(100) PRIMARY KEY,`value` TEXT NOT NULL)ENGINE=InnoDB;
CREATE TABLE admins(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,telegram_id BIGINT NOT NULL UNIQUE,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)ENGINE=InnoDB;
CREATE TABLE logs(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,level VARCHAR(20) NOT NULL,message TEXT NOT NULL,context TEXT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)ENGINE=InnoDB;

INSERT INTO settings(`key`,`value`) VALUES
('signup_reward','1000'),
('signup_reward_enabled','1'),
('referral_reward','500'),
('referral_enabled','1'),
('game_amounts','[100,500,1000,5000]'),
('game_enabled','1'),
('maintenance','0'),
('admin_password','CHANGE_ADMIN_PASSWORD')
ON DUPLICATE KEY UPDATE value=VALUES(value);