CREATE TABLE IF NOT EXISTS users (tg_id BIGINT PRIMARY KEY,username VARCHAR(255) NOT NULL DEFAULT '',first_name VARCHAR(255) NOT NULL DEFAULT '',last_name VARCHAR(255) NOT NULL DEFAULT '',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS settings (`key` VARCHAR(190) PRIMARY KEY,value TEXT NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS wallets (user_id BIGINT PRIMARY KEY,irt_balance BIGINT NOT NULL DEFAULT 0,crypto_balance DECIMAL(36,12) NOT NULL DEFAULT 0,FOREIGN KEY(user_id) REFERENCES users(tg_id) ON DELETE CASCADE) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS wallet_ledger (id BIGINT AUTO_INCREMENT PRIMARY KEY,user_id BIGINT NOT NULL,asset VARCHAR(20) NOT NULL,amount DECIMAL(36,12) NOT NULL,reason VARCHAR(120) NOT NULL,ref VARCHAR(190) NOT NULL DEFAULT '',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,INDEX(user_id)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS user_states (user_id BIGINT PRIMARY KEY,state VARCHAR(190) NOT NULL,payload JSON NULL,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS deposits (id BIGINT AUTO_INCREMENT PRIMARY KEY,user_id BIGINT NOT NULL,type ENUM('irt','crypto') NOT NULL,amount DECIMAL(36,12) NOT NULL,file_id TEXT NOT NULL,status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',admin_note TEXT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,approved_at TIMESTAMP NULL,INDEX(status),INDEX(user_id)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS virtual_numbers (id BIGINT AUTO_INCREMENT PRIMARY KEY,country VARCHAR(100) NOT NULL,service VARCHAR(100) NOT NULL DEFAULT 'Telegram',phone VARCHAR(80) NOT NULL UNIQUE,price BIGINT NOT NULL DEFAULT 0,status ENUM('available','reserved','sold','disabled') NOT NULL DEFAULT 'available',otp_code VARCHAR(100) NULL,sold_to BIGINT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,INDEX(country,status)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS number_orders (id BIGINT AUTO_INCREMENT PRIMARY KEY,user_id BIGINT NOT NULL,number_id BIGINT NOT NULL,status ENUM('waiting_code','delivered','cancelled') NOT NULL DEFAULT 'waiting_code',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(number_id) REFERENCES virtual_numbers(id),INDEX(user_id,status)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS rates (asset VARCHAR(20) PRIMARY KEY,buy_price DECIMAL(24,8) NOT NULL DEFAULT 0,sell_price DECIMAL(24,8) NOT NULL DEFAULT 0,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB;
INSERT INTO rates(asset,buy_price,sell_price) VALUES ('TON',0,0),('TRX',0,0),('USDT',0,0) ON DUPLICATE KEY UPDATE asset=VALUES(asset);
CREATE TABLE IF NOT EXISTS orders (id BIGINT AUTO_INCREMENT PRIMARY KEY,track_code VARCHAR(40) NOT NULL UNIQUE,user_id BIGINT NOT NULL,category VARCHAR(60) NOT NULL,title VARCHAR(190) NOT NULL,details TEXT NULL,payment_type VARCHAR(20) NOT NULL DEFAULT 'irt',amount DECIMAL(36,12) NOT NULL DEFAULT 0,status VARCHAR(40) NOT NULL DEFAULT 'pending',admin_note TEXT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,INDEX(user_id,status),INDEX(track_code)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;