-- ============================================
-- JACKPOT SYSTEM - SQL MIGRATION SCRIPT
-- Run this on your live server
-- ============================================

-- 1. CREATE JACKPOT TABLE
-- ============================================

CREATE TABLE IF NOT EXISTS `jackpot` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `current_amount` DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
    `assigned_bet_id` BIGINT UNSIGNED NULL,
    `assigned_game_type` VARCHAR(50) NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `assigned_at` TIMESTAMP NULL,
    `paid_out` TINYINT(1) NOT NULL DEFAULT 0,
    `paid_out_at` TIMESTAMP NULL,
    `bet_count` INT NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    INDEX `jackpot_is_active_assigned_bet_id_index` (`is_active`, `assigned_bet_id`),
    INDEX `jackpot_bet_count_index` (`bet_count`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert initial jackpot entry
INSERT INTO `jackpot` (`current_amount`, `is_active`, `bet_count`, `created_at`, `updated_at`)
VALUES (0.00, 1, 0, NOW(), NOW());


-- 2. ADD JACKPOT_WON COLUMN TO BET TABLES
-- ============================================
-- Note: Only Premier, Liga, and Champions participate in jackpot
-- Keno, Mixed, Aviator, and other games are excluded

-- Add to premier_bets
ALTER TABLE `premier_bets` 
ADD COLUMN `jackpot_won` DECIMAL(15, 2) NOT NULL DEFAULT 0.00 AFTER `payout`;

-- Add to liga_bets
ALTER TABLE `liga_bets` 
ADD COLUMN `jackpot_won` DECIMAL(15, 2) NOT NULL DEFAULT 0.00 AFTER `payout`;

-- Add to champions_bets
ALTER TABLE `champions_bets` 
ADD COLUMN `jackpot_won` DECIMAL(15, 2) NOT NULL DEFAULT 0.00 AFTER `payout`;


-- 3. VERIFY INSTALLATION
-- ============================================

-- Check jackpot table structure
DESCRIBE `jackpot`;

-- Check jackpot initial entry
SELECT * FROM `jackpot`;

-- Verify columns were added to bet tables
SHOW COLUMNS FROM `premier_bets` LIKE 'jackpot_won';
SHOW COLUMNS FROM `liga_bets` LIKE 'jackpot_won';
SHOW COLUMNS FROM `champions_bets` LIKE 'jackpot_won';


-- ============================================
-- ROLLBACK SCRIPT (if needed)
-- ============================================

-- Uncomment and run these commands to rollback changes

-- DROP TABLE IF EXISTS `jackpot`;
-- ALTER TABLE `premier_bets` DROP COLUMN `jackpot_won`;
-- ALTER TABLE `liga_bets` DROP COLUMN `jackpot_won`;
-- ALTER TABLE `champions_bets` DROP COLUMN `jackpot_won`;
