CREATE DATABASE IF NOT EXISTS panelgame CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE panelgame;

CREATE TABLE IF NOT EXISTS users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(190) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin','reseller','user') NOT NULL DEFAULT 'reseller',
    balance_cents BIGINT NOT NULL DEFAULT 0,
    saldo BIGINT NOT NULL DEFAULT 0,
    active TINYINT(1) NOT NULL DEFAULT 0,
    register_hash VARCHAR(64) NULL,
    login_token CHAR(64) NULL,
    created_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS auth_tokens (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    token CHAR(64) NOT NULL UNIQUE,
    expires_at DATETIME NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX(user_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS register_hashes (
    hash_code VARCHAR(64) PRIMARY KEY,
    balance_cents BIGINT NOT NULL DEFAULT 0,
    role ENUM('reseller','user') NOT NULL DEFAULT 'reseller',
    max_uses INT NOT NULL DEFAULT 1,
    used_count INT NOT NULL DEFAULT 0,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS categories (
    id VARCHAR(60) PRIMARY KEY,
    name VARCHAR(140) NOT NULL,
    logo VARCHAR(255) NULL,
    banner VARCHAR(255) NULL,
    version VARCHAR(60) NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS services (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category_id VARCHAR(60) NOT NULL,
    service_name VARCHAR(160) NOT NULL,
    duration INT NOT NULL,
    price_cents BIGINT NOT NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX(category_id),
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS game_keys (
    license_key VARCHAR(30) PRIMARY KEY,
    service_id BIGINT UNSIGNED NULL,
    category_id VARCHAR(60) NOT NULL,
    category_name VARCHAR(140) NOT NULL,
    service_name VARCHAR(160) NOT NULL,
    duration_days INT NOT NULL,
    price_cents BIGINT NOT NULL,
    created_by BIGINT UNSIGNED NOT NULL,
    created_by_role VARCHAR(20) NOT NULL DEFAULT 'reseller',
    activated_at BIGINT NOT NULL DEFAULT 0,
    expired_at BIGINT NOT NULL DEFAULT 0,
    used_by VARCHAR(190) NOT NULL DEFAULT '',
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at BIGINT NOT NULL,
    INDEX(created_by), INDEX(category_id), INDEX(service_id)
);

CREATE TABLE IF NOT EXISTS transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    type VARCHAR(40) NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    admin_id BIGINT UNSIGNED NULL,
    category_id VARCHAR(60) NULL,
    service_id BIGINT UNSIGNED NULL,
    amount_cents BIGINT NOT NULL,
    balance_before BIGINT NOT NULL,
    balance_after BIGINT NOT NULL,
    license_key VARCHAR(30) NULL,
    created_at BIGINT NOT NULL,
    INDEX(user_id), INDEX(admin_id), INDEX(service_id)
);


-- Default login accounts. Import ulang file ini, atau jalankan fix_default_logins.sql jika tabel sudah ada.
-- Admin login: admin@gmail.com / admin123
-- Reseller login: reseller@gmail.com / reseller123
INSERT INTO users (email, password_hash, role, balance_cents, saldo, active, register_hash, login_token)
VALUES
('admin@gmail.com', '$2y$12$RMxWiKoUAcEkgnf/O04ezu9zrkThTP3c1hUHIicUQ7whOCGSsqMrm', 'admin', 100000, 100000, 1, 'ADMIN-SEED', NULL),
('reseller@gmail.com', '$2y$12$LdnvQCaPYC6HFAPLOc/45.YMjgTKks4HTdd17CQgqf.1gja8fUDTC', 'reseller', 50000, 50000, 1, 'RESELLER-SEED', NULL)
ON DUPLICATE KEY UPDATE
password_hash=VALUES(password_hash),
role=VALUES(role),
balance_cents=VALUES(balance_cents),
saldo=VALUES(saldo),
active=1,
register_hash=VALUES(register_hash);

INSERT INTO register_hashes (hash_code, balance_cents, role, max_uses, used_count, active)
VALUES
('RESELLER100', 10000, 'reseller', 100, 0, 1),
('USER10', 1000, 'user', 100, 0, 1)
ON DUPLICATE KEY UPDATE
balance_cents=VALUES(balance_cents),
role=VALUES(role),
max_uses=VALUES(max_uses),
active=1;

INSERT INTO categories (id, name, logo, banner, version, active) VALUES
('codm_ios', 'CODM GARENA IOS', '', '', '1.0', 1),
('mlbb', 'Mobile Legends', '', '', '1.0', 1)
ON DUPLICATE KEY UPDATE name=VALUES(name), logo=VALUES(logo), banner=VALUES(banner), version=VALUES(version), active=VALUES(active);

INSERT INTO services (category_id, service_name, duration, price_cents, active) VALUES
('codm_ios', 'CODM GR IOS 3 DAYS', 3, 17000, 1),
('codm_ios', 'CODM GR IOS 7 DAYS', 7, 25500, 1),
('codm_ios', 'CODM GR IOS 30 DAYS', 30, 42500, 1),
('mlbb', 'MLBB 7 DAYS', 7, 1200, 1);