-- Stable identity and provenance for JKEX Outside details.
-- Outside virtual rows use string ids, so the numeric JKEXSourceID alone is insufficient.

SET @sql := IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'DailyData' AND COLUMN_NAME = 'JKEXSourceKey') = 0,
    'ALTER TABLE `DailyData` ADD COLUMN `JKEXSourceKey` VARCHAR(191) NULL COMMENT ''JKEX stable source key'' AFTER `JKEXSourceID`',
    'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'DailyData' AND COLUMN_NAME = 'JKEXSourceCompany') = 0,
    'ALTER TABLE `DailyData` ADD COLUMN `JKEXSourceCompany` VARCHAR(100) NULL AFTER `JKEXSourceKey`',
    'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'DailyData' AND COLUMN_NAME = 'JKEXDetailType') = 0,
    'ALTER TABLE `DailyData` ADD COLUMN `JKEXDetailType` VARCHAR(32) NULL AFTER `JKEXSourceCompany`',
    'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    (SELECT COUNT(*) FROM information_schema.STATISTICS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'DailyData' AND INDEX_NAME = 'uniq_dailydata_jkex_source_key') = 0,
    'ALTER TABLE `DailyData` ADD UNIQUE INDEX `uniq_dailydata_jkex_source_key` (`WebsiteID`, `DataType`, `JKEXSourceKey`)',
    'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
