-- Upgrade Patch for existing sbclub_v440finalwithCache.sql
-- Adds: featured/popular columns for gamexa_lists, sortOrder for gamexa_providers
-- Adds: SoftAPI session/round logging tables
-- Generated: 2026-02-07 08:51:18 UTC

SET NAMES utf8mb4;
SET foreign_key_checks = 0;

-- 1) Providers sort order
ALTER TABLE `gamexa_providers`
  ADD COLUMN IF NOT EXISTS `sortOrder` INT NOT NULL DEFAULT 0 AFTER `gameCount`;

-- 2) Games flags for home page
ALTER TABLE `gamexa_lists`
  ADD COLUMN IF NOT EXISTS `isFeatured` TINYINT(1) NOT NULL DEFAULT 0 AFTER `status`,
  ADD COLUMN IF NOT EXISTS `isPopular`  TINYINT(1) NOT NULL DEFAULT 0 AFTER `isFeatured`,
  ADD COLUMN IF NOT EXISTS `sortOrder`  INT NOT NULL DEFAULT 0 AFTER `isPopular`;

-- Helpful indexes
ALTER TABLE `gamexa_lists`
  ADD INDEX IF NOT EXISTS `idx_gamexa_lists_status` (`status`),
  ADD INDEX IF NOT EXISTS `idx_gamexa_lists_featured` (`isFeatured`),
  ADD INDEX IF NOT EXISTS `idx_gamexa_lists_popular` (`isPopular`),
  ADD INDEX IF NOT EXISTS `idx_gamexa_lists_provider` (`providerId`);

-- 3) SoftAPI logs (separate from your existing bet tables to avoid conflicts)
CREATE TABLE IF NOT EXISTS `softapi_sessions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `game_id` BIGINT UNSIGNED NULL,
  `session_uid` VARCHAR(128) NOT NULL UNIQUE,
  `launch_url` TEXT NULL,
  `status` ENUM('created','launched','closed') NOT NULL DEFAULT 'created',
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_softapi_sessions_user` (`user_id`),
  KEY `idx_softapi_sessions_game` (`game_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `softapi_rounds` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `session_uid` VARCHAR(128) NOT NULL,
  `game_round` VARCHAR(191) NOT NULL,
  `member_account` VARCHAR(64) NOT NULL,
  `bet_amount` DECIMAL(16,2) NOT NULL DEFAULT 0.00,
  `win_amount` DECIMAL(16,2) NOT NULL DEFAULT 0.00,
  `raw_payload` LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL CHECK (json_valid(`raw_payload`)),
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_softapi_round` (`session_uid`,`game_round`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET foreign_key_checks = 1;