-- ==========================================================================
--  AdriSegon / Montante VIP
--  Mise a jour MySQL : score live dans l'espace MONTANTE
--
--  Version phpMyAdmin/o2switch SANS PROCEDURE et SANS DELIMITER.
--  Elle peut etre relancee : chaque colonne est ajoutee seulement si absente.
-- ==========================================================================

SET NAMES utf8mb4;

-- live_home_score
SET @sql = (
  SELECT IF(
    EXISTS (
      SELECT 1
      FROM information_schema.COLUMNS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'pronostics'
        AND COLUMN_NAME = 'live_home_score'
    ),
    'SELECT ''live_home_score existe deja'' AS info',
    'ALTER TABLE `pronostics` ADD COLUMN `live_home_score` INT UNSIGNED DEFAULT NULL AFTER `result_checked_at`'
  )
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- live_away_score
SET @sql = (
  SELECT IF(
    EXISTS (
      SELECT 1
      FROM information_schema.COLUMNS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'pronostics'
        AND COLUMN_NAME = 'live_away_score'
    ),
    'SELECT ''live_away_score existe deja'' AS info',
    'ALTER TABLE `pronostics` ADD COLUMN `live_away_score` INT UNSIGNED DEFAULT NULL AFTER `live_home_score`'
  )
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- live_score_completed
SET @sql = (
  SELECT IF(
    EXISTS (
      SELECT 1
      FROM information_schema.COLUMNS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'pronostics'
        AND COLUMN_NAME = 'live_score_completed'
    ),
    'SELECT ''live_score_completed existe deja'' AS info',
    'ALTER TABLE `pronostics` ADD COLUMN `live_score_completed` TINYINT(1) NOT NULL DEFAULT 0 AFTER `live_away_score`'
  )
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- live_score_last_update
SET @sql = (
  SELECT IF(
    EXISTS (
      SELECT 1
      FROM information_schema.COLUMNS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'pronostics'
        AND COLUMN_NAME = 'live_score_last_update'
    ),
    'SELECT ''live_score_last_update existe deja'' AS info',
    'ALTER TABLE `pronostics` ADD COLUMN `live_score_last_update` DATETIME DEFAULT NULL AFTER `live_score_completed`'
  )
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- live_score_checked_at
SET @sql = (
  SELECT IF(
    EXISTS (
      SELECT 1
      FROM information_schema.COLUMNS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'pronostics'
        AND COLUMN_NAME = 'live_score_checked_at'
    ),
    'SELECT ''live_score_checked_at existe deja'' AS info',
    'ALTER TABLE `pronostics` ADD COLUMN `live_score_checked_at` DATETIME DEFAULT NULL AFTER `live_score_last_update`'
  )
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Verification : le resultat doit afficher 5 lignes.
SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'pronostics'
  AND COLUMN_NAME IN (
    'live_home_score',
    'live_away_score',
    'live_score_completed',
    'live_score_last_update',
    'live_score_checked_at'
  )
ORDER BY FIELD(
  COLUMN_NAME,
  'live_home_score',
  'live_away_score',
  'live_score_completed',
  'live_score_last_update',
  'live_score_checked_at'
);