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

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)
);

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);
