-- ============================================================================================
-- PRODUCTION MIGRATION — Project Live/Staging URLs
-- ============================================================================================
-- Adds optional Live and Staging deployment URLs to dim_projects (surfaced in the Project
-- detail Overview tab). Idempotent — safe to re-run.
-- Deploy: mysql -u <user> -p `stacie_Aggie_v1.0` < db/add_project_live_staging_urls.sql
-- ============================================================================================

USE `stacie_Aggie_v1.0`;

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_projects' AND COLUMN_NAME = 'live_url'
);
SET @sql = IF(@col_exists = 0, 'ALTER TABLE dim_projects ADD COLUMN live_url VARCHAR(500) NULL', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_projects' AND COLUMN_NAME = 'staging_url'
);
SET @sql = IF(@col_exists = 0, 'ALTER TABLE dim_projects ADD COLUMN staging_url VARCHAR(500) NULL', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;