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

-- Reconstructible DWD/KPI tables must never contain platform/default tenants.
DELETE FROM `dwd_material_readiness`
WHERE `tenant_id` IS NULL OR `tenant_id` IN (0,1,1300000000001);
DELETE FROM `dwd_material_shortage`
WHERE `tenant_id` IS NULL OR `tenant_id` IN (0,1,1300000000001);
DELETE FROM `dwd_order_schedule_trans`
WHERE `tenant_id` IS NULL OR `tenant_id` IN (0,1,1300000000001);
DELETE FROM `dwd_supplier_delivery`
WHERE `tenant_id` IS NULL OR `tenant_id` IN (0,1,1300000000001);
DELETE FROM `dwd_supplier_risk`
WHERE `tenant_id` IS NULL OR `tenant_id` IN (0,1,1300000000001);
DELETE FROM `dwd_supply_demand`
WHERE `tenant_id` IS NULL OR `tenant_id` IN (0,1,1300000000001);
DELETE FROM `ado_s9_kpi_value_l1_day`
WHERE `tenant_id` IS NULL OR `tenant_id` IN (0,1,1300000000001);
DELETE FROM `ado_s9_kpi_value_l2_day`
WHERE `tenant_id` IS NULL OR `tenant_id` IN (0,1,1300000000001);
DELETE FROM `ado_s9_kpi_value_l3_day`
WHERE `tenant_id` IS NULL OR `tenant_id` IN (0,1,1300000000001);
DELETE FROM `ado_s9_kpi_value_l4_day`
WHERE `tenant_id` IS NULL OR `tenant_id` IN (0,1,1300000000001);

SET @guard_sql := IF(
  (SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
   WHERE CONSTRAINT_SCHEMA=DATABASE() AND TABLE_NAME='dwd_material_readiness'
     AND CONSTRAINT_NAME='ck_wp5_valid_tenant_01')=0,
  'ALTER TABLE `dwd_material_readiness` ADD CONSTRAINT `ck_wp5_valid_tenant_01` CHECK (`tenant_id` IS NOT NULL AND `tenant_id` NOT IN (0,1,1300000000001))',
  'SELECT 1');
PREPARE guard_stmt FROM @guard_sql;
EXECUTE guard_stmt;
DEALLOCATE PREPARE guard_stmt;

SET @guard_sql := IF(
  (SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
   WHERE CONSTRAINT_SCHEMA=DATABASE() AND TABLE_NAME='dwd_material_shortage'
     AND CONSTRAINT_NAME='ck_wp5_valid_tenant_02')=0,
  'ALTER TABLE `dwd_material_shortage` ADD CONSTRAINT `ck_wp5_valid_tenant_02` CHECK (`tenant_id` IS NOT NULL AND `tenant_id` NOT IN (0,1,1300000000001))',
  'SELECT 1');
PREPARE guard_stmt FROM @guard_sql;
EXECUTE guard_stmt;
DEALLOCATE PREPARE guard_stmt;

SET @guard_sql := IF(
  (SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
   WHERE CONSTRAINT_SCHEMA=DATABASE() AND TABLE_NAME='dwd_order_schedule_trans'
     AND CONSTRAINT_NAME='ck_wp5_valid_tenant_03')=0,
  'ALTER TABLE `dwd_order_schedule_trans` ADD CONSTRAINT `ck_wp5_valid_tenant_03` CHECK (`tenant_id` IS NOT NULL AND `tenant_id` NOT IN (0,1,1300000000001))',
  'SELECT 1');
PREPARE guard_stmt FROM @guard_sql;
EXECUTE guard_stmt;
DEALLOCATE PREPARE guard_stmt;

SET @guard_sql := IF(
  (SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
   WHERE CONSTRAINT_SCHEMA=DATABASE() AND TABLE_NAME='dwd_supplier_delivery'
     AND CONSTRAINT_NAME='ck_wp5_valid_tenant_04')=0,
  'ALTER TABLE `dwd_supplier_delivery` ADD CONSTRAINT `ck_wp5_valid_tenant_04` CHECK (`tenant_id` IS NOT NULL AND `tenant_id` NOT IN (0,1,1300000000001))',
  'SELECT 1');
PREPARE guard_stmt FROM @guard_sql;
EXECUTE guard_stmt;
DEALLOCATE PREPARE guard_stmt;

SET @guard_sql := IF(
  (SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
   WHERE CONSTRAINT_SCHEMA=DATABASE() AND TABLE_NAME='dwd_supplier_risk'
     AND CONSTRAINT_NAME='ck_wp5_valid_tenant_05')=0,
  'ALTER TABLE `dwd_supplier_risk` ADD CONSTRAINT `ck_wp5_valid_tenant_05` CHECK (`tenant_id` IS NOT NULL AND `tenant_id` NOT IN (0,1,1300000000001))',
  'SELECT 1');
PREPARE guard_stmt FROM @guard_sql;
EXECUTE guard_stmt;
DEALLOCATE PREPARE guard_stmt;

SET @guard_sql := IF(
  (SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
   WHERE CONSTRAINT_SCHEMA=DATABASE() AND TABLE_NAME='dwd_supply_demand'
     AND CONSTRAINT_NAME='ck_wp5_valid_tenant_06')=0,
  'ALTER TABLE `dwd_supply_demand` ADD CONSTRAINT `ck_wp5_valid_tenant_06` CHECK (`tenant_id` IS NOT NULL AND `tenant_id` NOT IN (0,1,1300000000001))',
  'SELECT 1');
PREPARE guard_stmt FROM @guard_sql;
EXECUTE guard_stmt;
DEALLOCATE PREPARE guard_stmt;

SET @guard_sql := IF(
  (SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
   WHERE CONSTRAINT_SCHEMA=DATABASE() AND TABLE_NAME='ado_s9_kpi_value_l1_day'
     AND CONSTRAINT_NAME='ck_wp5_valid_tenant_07')=0,
  'ALTER TABLE `ado_s9_kpi_value_l1_day` ADD CONSTRAINT `ck_wp5_valid_tenant_07` CHECK (`tenant_id` IS NOT NULL AND `tenant_id` NOT IN (0,1,1300000000001))',
  'SELECT 1');
PREPARE guard_stmt FROM @guard_sql;
EXECUTE guard_stmt;
DEALLOCATE PREPARE guard_stmt;

SET @guard_sql := IF(
  (SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
   WHERE CONSTRAINT_SCHEMA=DATABASE() AND TABLE_NAME='ado_s9_kpi_value_l2_day'
     AND CONSTRAINT_NAME='ck_wp5_valid_tenant_08')=0,
  'ALTER TABLE `ado_s9_kpi_value_l2_day` ADD CONSTRAINT `ck_wp5_valid_tenant_08` CHECK (`tenant_id` IS NOT NULL AND `tenant_id` NOT IN (0,1,1300000000001))',
  'SELECT 1');
PREPARE guard_stmt FROM @guard_sql;
EXECUTE guard_stmt;
DEALLOCATE PREPARE guard_stmt;

SET @guard_sql := IF(
  (SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
   WHERE CONSTRAINT_SCHEMA=DATABASE() AND TABLE_NAME='ado_s9_kpi_value_l3_day'
     AND CONSTRAINT_NAME='ck_wp5_valid_tenant_09')=0,
  'ALTER TABLE `ado_s9_kpi_value_l3_day` ADD CONSTRAINT `ck_wp5_valid_tenant_09` CHECK (`tenant_id` IS NOT NULL AND `tenant_id` NOT IN (0,1,1300000000001))',
  'SELECT 1');
PREPARE guard_stmt FROM @guard_sql;
EXECUTE guard_stmt;
DEALLOCATE PREPARE guard_stmt;

SET @guard_sql := IF(
  (SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
   WHERE CONSTRAINT_SCHEMA=DATABASE() AND TABLE_NAME='ado_s9_kpi_value_l4_day'
     AND CONSTRAINT_NAME='ck_wp5_valid_tenant_10')=0,
  'ALTER TABLE `ado_s9_kpi_value_l4_day` ADD CONSTRAINT `ck_wp5_valid_tenant_10` CHECK (`tenant_id` IS NOT NULL AND `tenant_id` NOT IN (0,1,1300000000001))',
  'SELECT 1');
PREPARE guard_stmt FROM @guard_sql;
EXECUTE guard_stmt;
DEALLOCATE PREPARE guard_stmt;