-- JKEX external_id is a business/order id and can be shared by several detail rows.
-- Keep it in DailyData.ID_Field, and use JKEX's numeric record id as the source-row identity.

SET @dailydata_has_source_id := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'DailyData'
      AND COLUMN_NAME = 'JKEXSourceID'
);
SET @dailydata_sql := IF(
    @dailydata_has_source_id = 0,
    'ALTER TABLE `DailyData` ADD COLUMN `JKEXSourceID` BIGINT UNSIGNED NULL COMMENT ''JKEX source record id; external_id remains in ID_Field'' AFTER `ID_Field`',
    'SELECT 1'
);
PREPARE dailydata_stmt FROM @dailydata_sql;
EXECUTE dailydata_stmt;
DEALLOCATE PREPARE dailydata_stmt;

SET @dailydata_has_old_unique := (
    SELECT COUNT(*)
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'DailyData'
      AND INDEX_NAME = 'unique_website_tabletype_date_time_datatype_description'
);
SET @dailydata_sql := IF(
    @dailydata_has_old_unique > 0,
    'ALTER TABLE `DailyData` DROP INDEX `unique_website_tabletype_date_time_datatype_description`',
    'SELECT 1'
);
PREPARE dailydata_stmt FROM @dailydata_sql;
EXECUTE dailydata_stmt;
DEALLOCATE PREPARE dailydata_stmt;

SET @dailydata_has_legacy_index := (
    SELECT COUNT(*)
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'DailyData'
      AND INDEX_NAME = 'idx_dailydata_legacy_identity'
);
SET @dailydata_sql := IF(
    @dailydata_has_legacy_index = 0,
    'ALTER TABLE `DailyData` ADD INDEX `idx_dailydata_legacy_identity` (`WebsiteID`, `TableType`, `Date`, `TIME`, `DataType`, `DESCRIPTION`(255))',
    'SELECT 1'
);
PREPARE dailydata_stmt FROM @dailydata_sql;
EXECUTE dailydata_stmt;
DEALLOCATE PREPARE dailydata_stmt;

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