

-- Games table
CREATE TABLE IF NOT EXISTS tb_game (
    id INT AUTO_INCREMENT PRIMARY KEY,
    game_code VARCHAR(100) UNIQUE NOT NULL,
    game_name VARCHAR(255) NOT NULL,
    game_provider VARCHAR(100),
    game_type VARCHAR(50),
    game_image TEXT,
    game_status TINYINT DEFAULT 1,
    INDEX idx_game_code (game_code),
    INDEX idx_provider (game_provider)
);

-- Transactions table
CREATE TABLE IF NOT EXISTS tb_transaction (
    id INT AUTO_INCREMENT PRIMARY KEY,
    trxid VARCHAR(100) UNIQUE NOT NULL,
    intUser VARCHAR(50),
    extUser VARCHAR(100),
    type ENUM('deposit', 'withdraw') NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    before_agent_balance DECIMAL(15,2),
    after_agent_balance DECIMAL(15,2),
    agent_code VARCHAR(50) NOT NULL,
    created DATETIME NOT NULL,
    INDEX idx_trxid (trxid),
    INDEX idx_agent_code (agent_code),
    INDEX idx_created (created)
);

-- Transfer table (agent to agent transfers)
CREATE TABLE IF NOT EXISTS tb_transfer (
    id INT AUTO_INCREMENT PRIMARY KEY,
    trxid VARCHAR(100) UNIQUE NOT NULL,
    to_agent VARCHAR(50) NOT NULL,
    from_agent VARCHAR(50) NOT NULL,
    action VARCHAR(20),
    amount DECIMAL(15,2) NOT NULL,
    before_agent_balance DECIMAL(15,2),
    after_agent_balance DECIMAL(15,2),
    note TEXT,
    created DATETIME NOT NULL,
    INDEX idx_to_agent (to_agent),
    INDEX idx_from_agent (from_agent)
);

-- Balance topup table
CREATE TABLE IF NOT EXISTS tb_balance (
    id INT AUTO_INCREMENT PRIMARY KEY,
    trxid VARCHAR(100) UNIQUE NOT NULL,
    agent_code VARCHAR(50) NOT NULL,
    balance DECIMAL(15,2) NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    created DATETIME NOT NULL,
    date_created DATE NOT NULL,
    metode VARCHAR(100),
    status ENUM('Pending', 'Success', 'Cancel') DEFAULT 'Pending',
    whatsapp VARCHAR(20),
    checkout TEXT,
    INDEX idx_agent_code (agent_code),
    INDEX idx_status (status)
);

-- Game history/betting table
CREATE TABLE IF NOT EXISTS tb_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    intUser VARCHAR(50) NOT NULL,
    extUser VARCHAR(100) NOT NULL,
    bet_money DECIMAL(15,2) DEFAULT 0,
    win_money DECIMAL(15,2) DEFAULT 0,
    provider_code VARCHAR(100),
    game_code VARCHAR(100),
    type VARCHAR(50),
    txn_id VARCHAR(100),
    txn_type VARCHAR(20),
    user_start_balance DECIMAL(15,2),
    user_end_balance DECIMAL(15,2),
    created_at DATETIME NOT NULL,
    agent_code VARCHAR(50),
    INDEX idx_intUser (intUser),
    INDEX idx_extUser (extUser),
    INDEX idx_agent_code (agent_code),
    INDEX idx_created_at (created_at)
);

-- Game providers table
CREATE TABLE IF NOT EXISTS tb_provider (
    id INT AUTO_INCREMENT PRIMARY KEY,
    provider_code VARCHAR(100) UNIQUE NOT NULL,
    provider_name VARCHAR(255) NOT NULL,
    provider_type VARCHAR(50),
    provider_image TEXT,
    provider_status TINYINT DEFAULT 1,
    INDEX idx_provider_code (provider_code)
);

-- Game play sessions table
CREATE TABLE IF NOT EXISTS tb_play (
    id INT AUTO_INCREMENT PRIMARY KEY,
    extUser VARCHAR(100),
    intUser VARCHAR(50),
    provider VARCHAR(100),
    gamecode VARCHAR(100),
    tc VARCHAR(100) UNIQUE,
    gamestart DATETIME,
    url TEXT,
    title VARCHAR(255),
    INDEX idx_tc (tc)
);

-- Payment channels table
CREATE TABLE IF NOT EXISTS tb_channel (
    id INT AUTO_INCREMENT PRIMARY KEY,
    service VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    fee VARCHAR(20),
    status ENUM('ON', 'OFF') DEFAULT 'ON'
);

-- Insert sample game providers
INSERT INTO tb_provider (provider_code, provider_name, provider_type, provider_status) VALUES
('PRAGMATIC', 'Pragmatic Play', 'slot', 1),
('PGSOFT', 'PG Soft', 'slot', 1),
('MICROGAMING', 'Microgaming', 'slot', 1),
('EVOLUTION', 'Evolution Gaming', 'live', 1)
ON DUPLICATE KEY UPDATE provider_code = provider_code;

-- Insert sample games
INSERT INTO tb_game (game_code, game_name, game_provider, game_type, game_status) VALUES
('vs20olympgate', 'Gates of Olympus', 'PRAGMATIC', 'slot', 1),
('vs25wolf', 'Wolf Gold', 'PRAGMATIC', 'slot', 1),
('vs20fruitparty', 'Fruit Party', 'PRAGMATIC', 'slot', 1),
('mahjong-ways', 'Mahjong Ways', 'PGSOFT', 'slot', 1),
('fortune-ox', 'Fortune Ox', 'PGSOFT', 'slot', 1)
ON DUPLICATE KEY UPDATE game_code = game_code;