-- TRAIL DPS 4.2 - LOT 11.4.1
-- Base cible : base tracking OVH, ex. synoluidpstrack.
-- Objet : ajouter les colonnes de rattachement commercial SMS sur sms_logs.
-- Script idempotent compatible MySQL/MariaDB/phpMyAdmin.

SET @trail_dps_db := DATABASE();

SET @has_sms_logs := (
    SELECT COUNT(*)
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = @trail_dps_db
      AND TABLE_NAME = 'sms_logs'
);

SET @sql := (
    SELECT IF(@has_sms_logs = 0,
        'SELECT ''Table sms_logs absente - colonne license_id ignoree'' AS info',
        IF(COUNT(*) = 0,
            'ALTER TABLE `sms_logs` ADD COLUMN `license_id` INT UNSIGNED NULL AFTER `id`',
            'SELECT ''sms_logs.license_id deja presente'' AS info'
        )
    )
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @trail_dps_db
      AND TABLE_NAME = 'sms_logs'
      AND COLUMN_NAME = 'license_id'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := (
    SELECT IF(@has_sms_logs = 0,
        'SELECT ''Table sms_logs absente - colonne license_key ignoree'' AS info',
        IF(COUNT(*) = 0,
            'ALTER TABLE `sms_logs` ADD COLUMN `license_key` VARCHAR(128) NULL AFTER `license_id`',
            'SELECT ''sms_logs.license_key deja presente'' AS info'
        )
    )
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @trail_dps_db
      AND TABLE_NAME = 'sms_logs'
      AND COLUMN_NAME = 'license_key'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := (
    SELECT IF(@has_sms_logs = 0,
        'SELECT ''Table sms_logs absente - colonne place_instance_id ignoree'' AS info',
        IF(COUNT(*) = 0,
            'ALTER TABLE `sms_logs` ADD COLUMN `place_instance_id` VARCHAR(128) NULL AFTER `license_key`',
            'SELECT ''sms_logs.place_instance_id deja presente'' AS info'
        )
    )
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @trail_dps_db
      AND TABLE_NAME = 'sms_logs'
      AND COLUMN_NAME = 'place_instance_id'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := (
    SELECT IF(@has_sms_logs = 0,
        'SELECT ''Table sms_logs absente - colonne scope ignoree'' AS info',
        IF(COUNT(*) = 0,
            'ALTER TABLE `sms_logs` ADD COLUMN `scope` VARCHAR(64) NULL AFTER `status`',
            'SELECT ''sms_logs.scope deja presente'' AS info'
        )
    )
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @trail_dps_db
      AND TABLE_NAME = 'sms_logs'
      AND COLUMN_NAME = 'scope'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := (
    SELECT IF(@has_sms_logs = 0,
        'SELECT ''Table sms_logs absente - colonne related_share_id ignoree'' AS info',
        IF(COUNT(*) = 0,
            'ALTER TABLE `sms_logs` ADD COLUMN `related_share_id` BIGINT UNSIGNED NULL AFTER `scope`',
            'SELECT ''sms_logs.related_share_id deja presente'' AS info'
        )
    )
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @trail_dps_db
      AND TABLE_NAME = 'sms_logs'
      AND COLUMN_NAME = 'related_share_id'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := (
    SELECT IF(@has_sms_logs = 0,
        'SELECT ''Table sms_logs absente - colonne billable ignoree'' AS info',
        IF(COUNT(*) = 0,
            'ALTER TABLE `sms_logs` ADD COLUMN `billable` TINYINT(1) NOT NULL DEFAULT 1 AFTER `related_share_id`',
            'SELECT ''sms_logs.billable deja presente'' AS info'
        )
    )
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @trail_dps_db
      AND TABLE_NAME = 'sms_logs'
      AND COLUMN_NAME = 'billable'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Index facturation / reporting SMS.
SET @sql := (
    SELECT IF(@has_sms_logs = 0,
        'SELECT ''Table sms_logs absente - idx_sms_logs_license_time ignore'' AS info',
        IF(COUNT(*) = 0,
            'CREATE INDEX `idx_sms_logs_license_time` ON `sms_logs` (`license_id`, `created_at`)',
            'SELECT ''idx_sms_logs_license_time deja present'' AS info'
        )
    )
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = @trail_dps_db
      AND TABLE_NAME = 'sms_logs'
      AND INDEX_NAME = 'idx_sms_logs_license_time'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := (
    SELECT IF(@has_sms_logs = 0,
        'SELECT ''Table sms_logs absente - idx_sms_logs_license_key_time ignore'' AS info',
        IF(COUNT(*) = 0,
            'CREATE INDEX `idx_sms_logs_license_key_time` ON `sms_logs` (`license_key`, `created_at`)',
            'SELECT ''idx_sms_logs_license_key_time deja present'' AS info'
        )
    )
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = @trail_dps_db
      AND TABLE_NAME = 'sms_logs'
      AND INDEX_NAME = 'idx_sms_logs_license_key_time'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := (
    SELECT IF(@has_sms_logs = 0,
        'SELECT ''Table sms_logs absente - idx_sms_logs_place_time ignore'' AS info',
        IF(COUNT(*) = 0,
            'CREATE INDEX `idx_sms_logs_place_time` ON `sms_logs` (`place_instance_id`, `created_at`)',
            'SELECT ''idx_sms_logs_place_time deja present'' AS info'
        )
    )
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = @trail_dps_db
      AND TABLE_NAME = 'sms_logs'
      AND INDEX_NAME = 'idx_sms_logs_place_time'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := (
    SELECT IF(@has_sms_logs = 0,
        'SELECT ''Table sms_logs absente - idx_sms_logs_scope_time ignore'' AS info',
        IF(COUNT(*) = 0,
            'CREATE INDEX `idx_sms_logs_scope_time` ON `sms_logs` (`scope`, `created_at`)',
            'SELECT ''idx_sms_logs_scope_time deja present'' AS info'
        )
    )
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = @trail_dps_db
      AND TABLE_NAME = 'sms_logs'
      AND INDEX_NAME = 'idx_sms_logs_scope_time'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Fin LOT 11.4.1.
