AiDOPWarehouse/server/Admin.NET.Web.Entry/UpdateScripts/1.0.364.sql

-- WP-SD1: improvement plan trusted-scope guards.
DELETE FROM `ado_smart_ops_improvement_plan`
WHERE `TenantId` IS NULL OR `TenantId` IN (0,1,1300000000001);

SET @uk_sql := IF(
  (SELECT COUNT(*) FROM information_schema.STATISTICS
   WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='ado_smart_ops_improvement_plan'
     AND INDEX_NAME='uk_smart_ops_improve_tenant_plan')=0,
  'ALTER TABLE `ado_smart_ops_improvement_plan` ADD UNIQUE INDEX `uk_smart_ops_improve_tenant_plan` (`TenantId`,`PlanNo`)',
  'SELECT 1');
PREPARE uk_stmt FROM @uk_sql;
EXECUTE uk_stmt;
DEALLOCATE PREPARE uk_stmt;

SET @q_sql := IF(
  (SELECT COUNT(*) FROM information_schema.STATISTICS
   WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='ado_smart_ops_improvement_plan'
     AND INDEX_NAME='idx_smart_ops_improve_query')=0,
  'ALTER TABLE `ado_smart_ops_improvement_plan` ADD INDEX `idx_smart_ops_improve_query` (`TenantId`,`FactoryId`,`ModuleCode`,`Status`)',
  'SELECT 1');
PREPARE q_stmt FROM @q_sql;
EXECUTE q_stmt;
DEALLOCATE PREPARE q_stmt;

SET @o_sql := IF(
  (SELECT COUNT(*) FROM information_schema.STATISTICS
   WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='ado_smart_ops_improvement_plan'
     AND INDEX_NAME='idx_smart_ops_improve_owner')=0,
  'ALTER TABLE `ado_smart_ops_improvement_plan` ADD INDEX `idx_smart_ops_improve_owner` (`TenantId`,`OwnerUserId`)',
  'SELECT 1');
PREPARE o_stmt FROM @o_sql;
EXECUTE o_stmt;
DEALLOCATE PREPARE o_stmt;

SET @d_sql := IF(
  (SELECT COUNT(*) FROM information_schema.STATISTICS
   WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='ado_smart_ops_improvement_plan'
     AND INDEX_NAME='idx_smart_ops_improve_due')=0,
  'ALTER TABLE `ado_smart_ops_improvement_plan` ADD INDEX `idx_smart_ops_improve_due` (`TenantId`,`DueDate`)',
  'SELECT 1');
PREPARE d_stmt FROM @d_sql;
EXECUTE d_stmt;
DEALLOCATE PREPARE d_stmt;

SET @tenant_sql := IF(
  (SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
   WHERE CONSTRAINT_SCHEMA=DATABASE() AND TABLE_NAME='ado_smart_ops_improvement_plan'
     AND CONSTRAINT_NAME='ck_improve_valid_tenant')=0,
  'ALTER TABLE `ado_smart_ops_improvement_plan` ADD CONSTRAINT `ck_improve_valid_tenant` CHECK (`TenantId` IS NOT NULL AND `TenantId` NOT IN (0,1,1300000000001))',
  'SELECT 1');
PREPARE tenant_stmt FROM @tenant_sql;
EXECUTE tenant_stmt;
DEALLOCATE PREPARE tenant_stmt;

SET @factory_sql := IF(
  (SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
   WHERE CONSTRAINT_SCHEMA=DATABASE() AND TABLE_NAME='ado_smart_ops_improvement_plan'
     AND CONSTRAINT_NAME='ck_improve_valid_factory')=0,
  'ALTER TABLE `ado_smart_ops_improvement_plan` ADD CONSTRAINT `ck_improve_valid_factory` CHECK (`FactoryId` > 0 AND `FactoryId` <> `TenantId`)',
  'SELECT 1');
PREPARE factory_stmt FROM @factory_sql;
EXECUTE factory_stmt;
DEALLOCATE PREPARE factory_stmt;