-- SoftAPI Proxy - Production Database Schema
-- MySQL 5.7+ / MariaDB

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

-- 1. Clients
CREATE TABLE IF NOT EXISTS clients (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL DEFAULT '',
    api_key VARCHAR(64) NOT NULL,
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    allowed_ips TEXT DEFAULT NULL COMMENT 'JSON array of IPs',
    allowed_domains TEXT DEFAULT NULL COMMENT 'JSON array of domains',
    callback_url TEXT DEFAULT NULL,
    return_url TEXT DEFAULT NULL,
    balance DECIMAL(15,2) NOT NULL DEFAULT 0,
    commission_percent DECIMAL(5,2) NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_api_key (api_key),
    INDEX idx_status (status),
    INDEX idx_created (created_at)
) ENGINE=InnoDB;

-- 2. Users (per client)
CREATE TABLE IF NOT EXISTS users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    client_id INT UNSIGNED NOT NULL,
    user_id VARCHAR(128) NOT NULL COMMENT 'User ID from client system',
    balance DECIMAL(15,2) NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_client_user (client_id, user_id),
    INDEX idx_client (client_id),
    INDEX idx_user_id (user_id),
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- 3. Game Sessions
CREATE TABLE IF NOT EXISTS game_sessions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    client_id INT UNSIGNED NOT NULL,
    user_id VARCHAR(128) NOT NULL,
    game_uid VARCHAR(128) NOT NULL,
    session_id VARCHAR(128) DEFAULT NULL,
    callback_url TEXT DEFAULT NULL,
    return_url TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_game_uid (game_uid),
    INDEX idx_client (client_id),
    INDEX idx_user (user_id),
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- 4. Transactions
CREATE TABLE IF NOT EXISTS transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    client_id INT UNSIGNED NOT NULL,
    user_id VARCHAR(128) NOT NULL,
    game_uid VARCHAR(128) NOT NULL,
    game_round VARCHAR(128) NOT NULL,
    bet_amount DECIMAL(15,4) NOT NULL DEFAULT 0,
    win_amount DECIMAL(15,4) NOT NULL DEFAULT 0,
    before_balance DECIMAL(15,2) NOT NULL DEFAULT 0,
    after_balance DECIMAL(15,2) NOT NULL DEFAULT 0,
    commission_amount DECIMAL(15,4) NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_game_round (game_round),
    INDEX idx_client (client_id),
    INDEX idx_user (user_id),
    INDEX idx_game_uid (game_uid),
    INDEX idx_created (created_at),
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- 5. API Logs
CREATE TABLE IF NOT EXISTS api_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    client_id INT UNSIGNED DEFAULT NULL,
    endpoint VARCHAR(128) NOT NULL,
    request_payload JSON DEFAULT NULL,
    response_payload JSON DEFAULT NULL,
    ip VARCHAR(45) DEFAULT NULL,
    status INT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_client (client_id),
    INDEX idx_endpoint (endpoint),
    INDEX idx_created (created_at),
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- 6. Admins
CREATE TABLE IF NOT EXISTS admins (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(64) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    last_login_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_username (username),
    INDEX idx_status (status)
) ENGINE=InnoDB;

-- 7. Callback Retry Queue (for failed forwards)
CREATE TABLE IF NOT EXISTS callback_retries (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    game_round VARCHAR(128) NOT NULL,
    client_id INT UNSIGNED NOT NULL,
    payload JSON NOT NULL,
    callback_url TEXT NOT NULL,
    attempts INT NOT NULL DEFAULT 0,
    max_attempts INT NOT NULL DEFAULT 5,
    last_error TEXT DEFAULT NULL,
    next_retry_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_next_retry (next_retry_at),
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;
