-- Create and use database
CREATE DATABASE IF NOT EXISTS mtplayyyy CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci;
USE mtplayyyy;

-- Users table
CREATE TABLE IF NOT EXISTS users (
    id int(11) NOT NULL AUTO_INCREMENT,
    id_user varchar(50) NOT NULL DEFAULT '0',
    phone varchar(20) NOT NULL DEFAULT '0',
    token varchar(100) NOT NULL DEFAULT '0',
    name_user varchar(50) NOT NULL DEFAULT '0',
    password varchar(300) NOT NULL DEFAULT '0',
    plain_password varchar(250) DEFAULT NULL,
    money float NOT NULL DEFAULT 0,
    total_money float NOT NULL DEFAULT 0,
    bonus_money float NOT NULL DEFAULT 0,
    roses_f1 int(11) NOT NULL DEFAULT 0,
    roses_f int(11) NOT NULL DEFAULT 0,
    roses_today int(11) NOT NULL DEFAULT 0,
    level int(11) NOT NULL DEFAULT 0,
    rank int(11) NOT NULL DEFAULT 0,
    code varchar(30) NOT NULL DEFAULT '0',
    invite varchar(30) NOT NULL DEFAULT '0',
    ctv varchar(50) NOT NULL DEFAULT '0',
    veri int(11) NOT NULL DEFAULT 0,
    otp varchar(10) NOT NULL DEFAULT '0',
    ip_address varchar(50) NOT NULL DEFAULT '0',
    status int(11) NOT NULL DEFAULT 0,
    today datetime NOT NULL DEFAULT current_timestamp(),
    time varchar(50) NOT NULL DEFAULT '0',
    time_otp varchar(50) NOT NULL DEFAULT '0',
    user_level int(11) DEFAULT 0,
    avatar varchar(50) DEFAULT '1-a6662edb.png',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

-- Admin table
CREATE TABLE IF NOT EXISTS admin (
    id int(11) NOT NULL AUTO_INCREMENT,
    wingo1 text NOT NULL,
    wingo3 text NOT NULL,
    wingo5 text NOT NULL,
    wingo10 text NOT NULL,
    k5d text NOT NULL,
    k5d3 text NOT NULL,
    k5d5 text,
    k5d10 text NOT NULL,
    k3d text NOT NULL,
    k3d3 text NOT NULL,
    k3d5 text NOT NULL,
    k3d10 text NOT NULL,
    win_rate int(11) NOT NULL DEFAULT 0,
    telegram varchar(100) NOT NULL DEFAULT '0',
    cskh varchar(100) NOT NULL DEFAULT '0',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- Insert default admin row
INSERT IGNORE INTO admin (id, wingo1, wingo3, wingo5, wingo10, k5d, k5d3, k5d5, k5d10, k3d, k3d3, k3d5, k3d10, win_rate, telegram, cskh)
VALUES (1, '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', '-1', 80, 'https://t.me/Olivia_XDR', 'https://t.me/Olivia_XDR');

-- Wingo table
CREATE TABLE IF NOT EXISTS wingo (
    id int(11) NOT NULL AUTO_INCREMENT,
    period varchar(30) NOT NULL DEFAULT '0',
    game varchar(20) NOT NULL DEFAULT '0',
    amount float NOT NULL DEFAULT 0,
    status int(11) NOT NULL DEFAULT 0,
    time varchar(50) NOT NULL DEFAULT '0',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- 5D table
CREATE TABLE IF NOT EXISTS `5d` (
    id int(11) NOT NULL AUTO_INCREMENT,
    period varchar(30) NOT NULL DEFAULT '0',
    result varchar(30) NOT NULL DEFAULT '0',
    game int(11) NOT NULL DEFAULT 0,
    status int(11) NOT NULL DEFAULT 0,
    time varchar(50) NOT NULL DEFAULT '0',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- K3 table
CREATE TABLE IF NOT EXISTS k3 (
    id int(11) NOT NULL AUTO_INCREMENT,
    period varchar(30) NOT NULL DEFAULT '0',
    result varchar(30) NOT NULL DEFAULT '0',
    game int(11) NOT NULL DEFAULT 0,
    status int(11) NOT NULL DEFAULT 0,
    time varchar(50) NOT NULL DEFAULT '0',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- Level table
CREATE TABLE IF NOT EXISTS level (
    id int(11) NOT NULL AUTO_INCREMENT,
    level int(11) NOT NULL DEFAULT 0,
    f1 float NOT NULL DEFAULT 0,
    f2 float NOT NULL DEFAULT 0,
    f3 float NOT NULL DEFAULT 0,
    f4 float NOT NULL DEFAULT 0,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

INSERT IGNORE INTO level VALUES (1,0,0.6,0.18,0.054,0.0162);
INSERT IGNORE INTO level VALUES (2,1,0.7,0.21,0.063,0.0189);
INSERT IGNORE INTO level VALUES (3,2,0.75,0.225,0.0675,0.0203);
INSERT IGNORE INTO level VALUES (4,3,0.8,0.24,0.072,0.0216);
INSERT IGNORE INTO level VALUES (5,4,0.85,0.255,0.0765,0.023);
INSERT IGNORE INTO level VALUES (6,5,0.9,0.27,0.081,0.0243);
INSERT IGNORE INTO level VALUES (7,6,1,0.3,0.09,0.027);

-- Bank recharge table
CREATE TABLE IF NOT EXISTS bank_recharge (
    id int(11) NOT NULL AUTO_INCREMENT,
    name_bank varchar(100) NOT NULL DEFAULT '0',
    name_user varchar(100) NOT NULL DEFAULT '0',
    stk varchar(50) NOT NULL DEFAULT '0',
    type varchar(20) NOT NULL DEFAULT 'bank',
    time varchar(50) NOT NULL DEFAULT '0',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- Recharge table
CREATE TABLE IF NOT EXISTS recharge (
    id int(11) NOT NULL AUTO_INCREMENT,
    id_order varchar(100) NOT NULL DEFAULT '0',
    phone varchar(20) NOT NULL DEFAULT '0',
    money float NOT NULL DEFAULT 0,
    status int(11) NOT NULL DEFAULT 0,
    today datetime NOT NULL DEFAULT current_timestamp(),
    time varchar(50) NOT NULL DEFAULT '0',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- Withdraw table
CREATE TABLE IF NOT EXISTS withdraw (
    id int(11) NOT NULL AUTO_INCREMENT,
    id_order varchar(100) NOT NULL DEFAULT '0',
    phone varchar(20) NOT NULL DEFAULT '0',
    money float NOT NULL DEFAULT 0,
    status int(11) NOT NULL DEFAULT 0,
    name_bank varchar(100) NOT NULL DEFAULT '0',
    name_user varchar(100) NOT NULL DEFAULT '0',
    stk varchar(50) NOT NULL DEFAULT '0',
    today datetime NOT NULL DEFAULT current_timestamp(),
    time varchar(50) NOT NULL DEFAULT '0',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- Wingo bet table
CREATE TABLE IF NOT EXISTS wingo_bet (
    id int(11) NOT NULL AUTO_INCREMENT,
    phone varchar(20) NOT NULL DEFAULT '0',
    game varchar(20) NOT NULL DEFAULT '0',
    period varchar(30) NOT NULL DEFAULT '0',
    bet varchar(20) NOT NULL DEFAULT '0',
    money float NOT NULL DEFAULT 0,
    status int(11) NOT NULL DEFAULT 0,
    today datetime NOT NULL DEFAULT current_timestamp(),
    time varchar(50) NOT NULL DEFAULT '0',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- 5D bet table
CREATE TABLE IF NOT EXISTS `5d_bet` (
    id int(11) NOT NULL AUTO_INCREMENT,
    phone varchar(20) NOT NULL DEFAULT '0',
    game int(11) NOT NULL DEFAULT 0,
    period varchar(30) NOT NULL DEFAULT '0',
    bet varchar(20) NOT NULL DEFAULT '0',
    money float NOT NULL DEFAULT 0,
    status int(11) NOT NULL DEFAULT 0,
    today datetime NOT NULL DEFAULT current_timestamp(),
    time varchar(50) NOT NULL DEFAULT '0',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- K3 bet table
CREATE TABLE IF NOT EXISTS k3_bet (
    id int(11) NOT NULL AUTO_INCREMENT,
    phone varchar(20) NOT NULL DEFAULT '0',
    game int(11) NOT NULL DEFAULT 0,
    period varchar(30) NOT NULL DEFAULT '0',
    bet varchar(20) NOT NULL DEFAULT '0',
    money float NOT NULL DEFAULT 0,
    status int(11) NOT NULL DEFAULT 0,
    today datetime NOT NULL DEFAULT current_timestamp(),
    time varchar(50) NOT NULL DEFAULT '0',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- TRX Wingo table
CREATE TABLE IF NOT EXISTS trx_wingo (
    id int(11) NOT NULL AUTO_INCREMENT,
    period varchar(30) NOT NULL DEFAULT '0',
    result varchar(10) NOT NULL DEFAULT '0',
    status int(11) NOT NULL DEFAULT 0,
    time varchar(50) NOT NULL DEFAULT '0',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- TRX Wingo bet table
CREATE TABLE IF NOT EXISTS trx_wingo_bet (
    id int(11) NOT NULL AUTO_INCREMENT,
    phone varchar(20) NOT NULL DEFAULT '0',
    period varchar(30) NOT NULL DEFAULT '0',
    bet varchar(20) NOT NULL DEFAULT '0',
    money float NOT NULL DEFAULT 0,
    status int(11) NOT NULL DEFAULT 0,
    today datetime NOT NULL DEFAULT current_timestamp(),
    time varchar(50) NOT NULL DEFAULT '0',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- Transactions table
CREATE TABLE IF NOT EXISTS transactions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    phone VARCHAR(15) NOT NULL,
    amount INT NOT NULL,
    transactionTypeId VARCHAR(80) NOT NULL,
    transferTypeId VARCHAR(80) NOT NULL,
    details VARCHAR(256) NOT NULL,
    status INT NOT NULL,
    time VARCHAR(30) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- Claimed rewards table
CREATE TABLE IF NOT EXISTS claimed_rewards (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    phone VARCHAR(15) NOT NULL,
    reward_id INT NOT NULL,
    type VARCHAR(20) NOT NULL,
    amount INT NOT NULL,
    status INT NOT NULL,
    time VARCHAR(30) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- Attendance bonus table
CREATE TABLE IF NOT EXISTS attendance_bonus (
    id int(11) NOT NULL AUTO_INCREMENT,
    phone varchar(20) NOT NULL DEFAULT '0',
    day int(11) NOT NULL DEFAULT 0,
    status int(11) NOT NULL DEFAULT 0,
    time varchar(50) NOT NULL DEFAULT '0',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- Invite table
CREATE TABLE IF NOT EXISTS invite (
    id int(11) NOT NULL AUTO_INCREMENT,
    phone varchar(20) NOT NULL DEFAULT '0',
    invite varchar(30) NOT NULL DEFAULT '0',
    time varchar(50) NOT NULL DEFAULT '0',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- Seed wingo game data
DELETE FROM wingo;
INSERT INTO wingo (period, game, amount, status, time) VALUES ('2022070110000','wingo10',6,1,UNIX_TIMESTAMP()*1000);
INSERT INTO wingo (period, game, amount, status, time) VALUES ('2022070110001','wingo10',0,0,UNIX_TIMESTAMP()*1000);
INSERT INTO wingo (period, game, amount, status, time) VALUES ('2022070110000','wingo5',6,1,UNIX_TIMESTAMP()*1000);
INSERT INTO wingo (period, game, amount, status, time) VALUES ('2022070110001','wingo5',0,0,UNIX_TIMESTAMP()*1000);
INSERT INTO wingo (period, game, amount, status, time) VALUES ('2022070110000','wingo3',6,1,UNIX_TIMESTAMP()*1000);
INSERT INTO wingo (period, game, amount, status, time) VALUES ('2022070110001','wingo3',0,0,UNIX_TIMESTAMP()*1000);
INSERT INTO wingo (period, game, amount, status, time) VALUES ('2022070110000','wingo',6,1,UNIX_TIMESTAMP()*1000);
INSERT INTO wingo (period, game, amount, status, time) VALUES ('2022070110001','wingo',0,0,UNIX_TIMESTAMP()*1000);

-- Seed 5D game data
DELETE FROM `5d`;
INSERT INTO `5d` (period, result, game, status, time) VALUES ('2022070110000','23521',10,1,UNIX_TIMESTAMP()*1000);
INSERT INTO `5d` (period, result, game, status, time) VALUES ('2022070110001','0',10,0,UNIX_TIMESTAMP()*1000);
INSERT INTO `5d` (period, result, game, status, time) VALUES ('2022070110000','23521',5,1,UNIX_TIMESTAMP()*1000);
INSERT INTO `5d` (period, result, game, status, time) VALUES ('2022070110001','0',5,0,UNIX_TIMESTAMP()*1000);
INSERT INTO `5d` (period, result, game, status, time) VALUES ('2022070110000','23521',3,1,UNIX_TIMESTAMP()*1000);
INSERT INTO `5d` (period, result, game, status, time) VALUES ('2022070110001','0',3,0,UNIX_TIMESTAMP()*1000);
INSERT INTO `5d` (period, result, game, status, time) VALUES ('2022070110000','23521',1,1,UNIX_TIMESTAMP()*1000);
INSERT INTO `5d` (period, result, game, status, time) VALUES ('2022070110001','0',1,0,UNIX_TIMESTAMP()*1000);

-- Seed K3 game data
DELETE FROM k3;
INSERT INTO k3 (period, result, game, status, time) VALUES ('2022070110000','235',10,1,UNIX_TIMESTAMP()*1000);
INSERT INTO k3 (period, result, game, status, time) VALUES ('2022070110001','0',10,0,UNIX_TIMESTAMP()*1000);
INSERT INTO k3 (period, result, game, status, time) VALUES ('2022070110000','235',5,1,UNIX_TIMESTAMP()*1000);
INSERT INTO k3 (period, result, game, status, time) VALUES ('2022070110001','0',5,0,UNIX_TIMESTAMP()*1000);
INSERT INTO k3 (period, result, game, status, time) VALUES ('2022070110000','235',3,1,UNIX_TIMESTAMP()*1000);
INSERT INTO k3 (period, result, game, status, time) VALUES ('2022070110001','0',3,0,UNIX_TIMESTAMP()*1000);
INSERT INTO k3 (period, result, game, status, time) VALUES ('2022070110000','235',1,1,UNIX_TIMESTAMP()*1000);
INSERT INTO k3 (period, result, game, status, time) VALUES ('2022070110001','0',1,0,UNIX_TIMESTAMP()*1000);

SELECT 'Database setup complete!' AS status;