-- =====================================================
-- ADD STABLE POS REFERENCE ID - LIVE DATABASE
-- =====================================================
-- Run this on your live MySQL database
-- Safe to run multiple times (won't break if already exists)
-- =====================================================

-- 1. Add pos_ref_id column to fixtures table (if not exists)
-- =====================================================
SET @col_exists = (
    SELECT COUNT(*) 
    FROM information_schema.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'fixtures' 
    AND COLUMN_NAME = 'pos_ref_id'
);

SET @sql = IF(@col_exists = 0,
    'ALTER TABLE fixtures ADD COLUMN pos_ref_id INT UNSIGNED NULL DEFAULT NULL AFTER id',
    'SELECT "Column pos_ref_id already exists" AS message'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 2. Add unique index on pos_ref_id (if not exists)
-- =====================================================
SET @index_exists = (
    SELECT COUNT(*) 
    FROM information_schema.STATISTICS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'fixtures' 
    AND INDEX_NAME = 'idx_pos_ref_id'
);

SET @sql = IF(@index_exists = 0,
    'ALTER TABLE fixtures ADD UNIQUE INDEX idx_pos_ref_id (pos_ref_id)',
    'SELECT "Index idx_pos_ref_id already exists" AS message'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 3. Create sequence table (optional - kept for compatibility)
-- =====================================================
-- Note: With recycling approach, we don't strictly need this,
-- but keeping it for backward compatibility
CREATE TABLE IF NOT EXISTS pos_ref_id_sequence (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    last_pos_ref_id INT UNSIGNED NOT NULL DEFAULT 0,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    note VARCHAR(255) DEFAULT 'Used for tracking only - recycling active'
);

-- 4. Initialize sequence table (optional)
-- =====================================================
INSERT INTO pos_ref_id_sequence (id, last_pos_ref_id, note) 
VALUES (1, 0, 'Numbers are recycled from finished fixtures')
ON DUPLICATE KEY UPDATE note = 'Numbers are recycled from finished fixtures';

-- 5. Show results
-- =====================================================
SELECT 
    'Migration Complete' AS status,
    (SELECT COUNT(*) FROM information_schema.COLUMNS 
     WHERE TABLE_SCHEMA = DATABASE() 
     AND TABLE_NAME = 'fixtures' 
     AND COLUMN_NAME = 'pos_ref_id') AS column_exists,
    (SELECT COUNT(*) FROM information_schema.STATISTICS 
     WHERE TABLE_SCHEMA = DATABASE() 
     AND TABLE_NAME = 'fixtures' 
     AND INDEX_NAME = 'idx_pos_ref_id') AS index_exists,
    (SELECT COUNT(*) FROM information_schema.TABLES 
     WHERE TABLE_SCHEMA = DATABASE() 
     AND TABLE_NAME = 'pos_ref_id_sequence') AS sequence_table_exists;

-- 6. Show current state
-- =====================================================
SELECT 
    COUNT(*) as total_fixtures,
    COUNT(pos_ref_id) as fixtures_with_pos_ref_id,
    COUNT(*) - COUNT(pos_ref_id) as fixtures_without_pos_ref_id
FROM fixtures
WHERE date >= NOW()
  AND status IN ('NS', 'scheduled', 'TBD');

-- =====================================================
-- DONE! Next step: Run initialize_pos_ref_ids.php
-- =====================================================
