-- v6 fixes
-- 1) Make dashboard/audience counts able to ignore soft-deleted rows where these tables support soft delete.
-- 2) No destructive data changes.

SET @db := DATABASE();

SET @col_exists := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='customers' AND COLUMN_NAME='datetime_deleted');
SET @sql := IF(@col_exists=0,'ALTER TABLE customers ADD COLUMN datetime_deleted DATETIME NULL AFTER date_created','SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx_exists := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='customers' AND INDEX_NAME='idx_customers_account_deleted');
SET @sql := IF(@idx_exists=0,'CREATE INDEX idx_customers_account_deleted ON customers (account_id, datetime_deleted)','SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='items' AND COLUMN_NAME='datetime_deleted');
SET @sql := IF(@col_exists=0,'ALTER TABLE items ADD COLUMN datetime_deleted DATETIME NULL','SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx_exists := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='items' AND INDEX_NAME='idx_items_account_deleted');
SET @sql := IF(@idx_exists=0,'CREATE INDEX idx_items_account_deleted ON items (account_id, datetime_deleted)','SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='reminder' AND COLUMN_NAME='datetime_deleted');
SET @sql := IF(@col_exists=0,'ALTER TABLE reminder ADD COLUMN datetime_deleted DATETIME NULL','SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx_exists := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='reminder' AND INDEX_NAME='idx_reminder_account_deleted');
SET @sql := IF(@idx_exists=0,'CREATE INDEX idx_reminder_account_deleted ON reminder (account_id, datetime_deleted)','SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
