-- db.sql
-- Database buat provider game sendiri (pengganti rest.cxfcore.sbs)

CREATE TABLE IF NOT EXISTS admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL
);

CREATE TABLE IF NOT EXISTS sites (
    id INT AUTO_INCREMENT PRIMARY KEY,
    domain VARCHAR(255) NOT NULL,
    secret_key VARCHAR(64) NOT NULL UNIQUE,
    status ENUM('aktif', 'nonaktif') DEFAULT 'aktif',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS players (
    id INT AUTO_INCREMENT PRIMARY KEY,
    site_id INT NOT NULL,
    extplayer VARCHAR(100) NOT NULL,
    balance DECIMAL(15,2) DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_player (site_id, extplayer),
    FOREIGN KEY (site_id) REFERENCES sites(id)
);

CREATE TABLE IF NOT EXISTS transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    player_id INT NOT NULL,
    type ENUM('deposit', 'withdraw', 'bet', 'win') NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    balance_after DECIMAL(15,2) NOT NULL,
    ref_id VARCHAR(100) DEFAULT NULL,
    note VARCHAR(255) DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (player_id) REFERENCES players(id)
);

CREATE TABLE IF NOT EXISTS games (
    id INT AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    thumbnail VARCHAR(255) DEFAULT NULL,
    rtp_target DECIMAL(5,2) DEFAULT 96.00,
    min_bet INT DEFAULT 1000,
    max_bet INT DEFAULT 500000,
    status ENUM('aktif', 'nonaktif') DEFAULT 'aktif'
);

CREATE TABLE IF NOT EXISTS game_sessions (
    token VARCHAR(64) PRIMARY KEY,
    player_id INT NOT NULL,
    game_id INT NOT NULL,
    site_id INT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME NOT NULL,
    FOREIGN KEY (player_id) REFERENCES players(id),
    FOREIGN KEY (game_id) REFERENCES games(id)
);

-- Fitur "setwin"/"callwin" -- override menang/kalah per player, dipake bot admin.
-- Mode ini TERUS AKTIF sampai diganti manual (sama kayak command setwin yang udah ada).
CREATE TABLE IF NOT EXISTS win_override (
    player_id INT PRIMARY KEY,
    mode ENUM('win', 'lose', 'normal') DEFAULT 'normal',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (player_id) REFERENCES players(id)
);

-- 5 game awal, nama & tema ORIGINAL (bukan niru provider asli manapun)
INSERT INTO games (slug, name, thumbnail, rtp_target, min_bet, max_bet) VALUES
('golden-pharaoh', 'Golden Pharaoh', 'golden-pharaoh.jpg', 96.00, 1000, 500000),
('lucky-dragon', 'Lucky Dragon Fortune', 'lucky-dragon.jpg', 96.00, 1000, 500000),
('wild-safari', 'Wild Safari Riches', 'wild-safari.jpg', 96.00, 1000, 500000),
('candy-burst', 'Candy Burst Mania', 'candy-burst.jpg', 96.00, 1000, 500000),
('pirate-treasure', 'Pirate Treasure Quest', 'pirate-treasure.jpg', 96.00, 1000, 500000);