-- ================================================================================
-- DEPLOYMENT SCRIPT: GLOBAL BET CYCLE TRACKING
-- ================================================================================
-- Purpose: Migrate from per-user bet cycle tracking to global cycle tracking
-- 
-- CRITICAL CONTEXT:
-- Match results are generated ONCE per week and shared by ALL users.
-- Therefore, cycle tracking must be global (one row per league) rather than 
-- per-user to ensure consistent loss/win phases across the entire platform.
--
-- What this does:
-- 1. Drops old per-user tracking tables (champions_bet_cycles, etc.)
-- 2. Creates new global tracking tables (champions_bet_cycle, etc.)
-- 3. Initializes each league with a single global cycle row
-- 4. Adds week_history JSON field to track which weeks triggered anti-abuse
--
-- EXECUTE THIS ON LIVE SERVER AFTER CODE DEPLOYMENT
-- ================================================================================

-- Disable foreign key checks temporarily
SET FOREIGN_KEY_CHECKS = 0;

-- ================================================================================
-- STEP 1: DROP OLD PER-USER TRACKING TABLES
-- ================================================================================

DROP TABLE IF EXISTS `champions_bet_cycles`;
DROP TABLE IF EXISTS `premier_bet_cycles`;
DROP TABLE IF EXISTS `liga_bet_cycles`;

-- ================================================================================
-- STEP 2: CREATE CHAMPIONS GLOBAL BET CYCLE TABLE
-- ================================================================================

CREATE TABLE `champions_bet_cycle` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `consecutive_rounds` int(11) NOT NULL DEFAULT 0,
  `cycle_phase` enum('loss','win') NOT NULL DEFAULT 'loss',
  `week_history` json DEFAULT NULL COMMENT 'Track which weeks triggered anti-abuse: [{season, week, phase, enforced, timestamp}]',
  `cycle_started_at` timestamp NULL DEFAULT NULL,
  `last_triggered_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Initialize with single global row
INSERT INTO `champions_bet_cycle` (`id`, `consecutive_rounds`, `cycle_phase`, `week_history`, `cycle_started_at`, `last_triggered_at`, `created_at`, `updated_at`)
VALUES (1, 0, 'loss', '[]', NOW(), NULL, NOW(), NOW());

-- ================================================================================
-- STEP 3: CREATE PREMIER GLOBAL BET CYCLE TABLE
-- ================================================================================

CREATE TABLE `premier_bet_cycle` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `consecutive_rounds` int(11) NOT NULL DEFAULT 0,
  `cycle_phase` enum('loss','win') NOT NULL DEFAULT 'loss',
  `week_history` json DEFAULT NULL COMMENT 'Track which weeks triggered anti-abuse: [{season, week, phase, enforced, timestamp}]',
  `cycle_started_at` timestamp NULL DEFAULT NULL,
  `last_triggered_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Initialize with single global row
INSERT INTO `premier_bet_cycle` (`id`, `consecutive_rounds`, `cycle_phase`, `week_history`, `cycle_started_at`, `last_triggered_at`, `created_at`, `updated_at`)
VALUES (1, 0, 'loss', '[]', NOW(), NULL, NOW(), NOW());

-- ================================================================================
-- STEP 4: CREATE LIGA GLOBAL BET CYCLE TABLE
-- ================================================================================

CREATE TABLE `liga_bet_cycle` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `consecutive_rounds` int(11) NOT NULL DEFAULT 0,
  `cycle_phase` enum('loss','win') NOT NULL DEFAULT 'loss',
  `week_history` json DEFAULT NULL COMMENT 'Track which weeks triggered anti-abuse: [{season, week, phase, enforced, timestamp}]',
  `cycle_started_at` timestamp NULL DEFAULT NULL,
  `last_triggered_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Initialize with single global row
INSERT INTO `liga_bet_cycle` (`id`, `consecutive_rounds`, `cycle_phase`, `week_history`, `cycle_started_at`, `last_triggered_at`, `created_at`, `updated_at`)
VALUES (1, 0, 'loss', '[]', NOW(), NULL, NOW(), NOW());

-- Re-enable foreign key checks
SET FOREIGN_KEY_CHECKS = 1;

-- ================================================================================
-- VERIFICATION QUERIES (run these to confirm deployment)
-- ================================================================================

-- Should return exactly 1 for each:
-- SELECT COUNT(*) FROM champions_bet_cycle;
-- SELECT COUNT(*) FROM premier_bet_cycle;
-- SELECT COUNT(*) FROM liga_bet_cycle;

-- View initial state:
-- SELECT * FROM champions_bet_cycle;
-- SELECT * FROM premier_bet_cycle;
-- SELECT * FROM liga_bet_cycle;

-- ================================================================================
-- ROLLBACK INSTRUCTIONS (IN CASE OF EMERGENCY)
-- ================================================================================

-- If you need to rollback to old per-user tables:
-- 
-- SET FOREIGN_KEY_CHECKS = 0;
-- DROP TABLE IF EXISTS champions_bet_cycle;
-- DROP TABLE IF EXISTS premier_bet_cycle;
-- DROP TABLE IF EXISTS liga_bet_cycle;
-- 
-- CREATE TABLE `champions_bet_cycles` (
--   `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
--   `user_id` bigint(20) UNSIGNED NOT NULL,
--   `consecutive_rounds` int(11) NOT NULL DEFAULT 0,
--   `cycle_phase` enum('loss','win') NOT NULL DEFAULT 'loss',
--   `last_bet_at` timestamp NULL DEFAULT NULL,
--   `cycle_started_at` timestamp NULL DEFAULT NULL,
--   `created_at` timestamp NULL DEFAULT NULL,
--   `updated_at` timestamp NULL DEFAULT NULL,
--   PRIMARY KEY (`id`),
--   UNIQUE KEY `champions_bet_cycles_user_id_unique` (`user_id`)
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- 
-- -- Repeat for premier_bet_cycles and liga_bet_cycles
-- SET FOREIGN_KEY_CHECKS = 1;
-- 
-- WARNING: Rollback requires re-deploying old code version!

-- ================================================================================
-- POST-DEPLOYMENT CHECKLIST
-- ================================================================================
-- [ ] Verify tables created: SHOW TABLES LIKE '%bet_cycle%';
-- [ ] Verify 1 row each: SELECT COUNT(*) FROM champions_bet_cycle;
-- [ ] Check logs after first single-bettor week for "GLOBAL cycle" messages
-- [ ] Monitor week_history JSON updates in subsequent weeks
-- ================================================================================
