AiDOPWarehouse/server/Admin.NET.Web.Entry/UpdateScripts/1.0.350.verify.sql

SELECT CASE WHEN
    (
      SELECT COUNT(*)
      FROM information_schema.TABLES t
      WHERE t.TABLE_SCHEMA=DATABASE()
        AND t.TABLE_NAME IN (
          'mdp_stg_s4_iqc','mdp_stg_s4_return','mdp_stg_s4_shipment','mdp_stg_s4_shortage',
          'mdp_std_delivery_schedule','mdp_std_delivery_result','mdp_std_material_readiness','mdp_std_process_outsource_order',
          'dwd_supply_demand','dwd_supplier_delivery','dwd_supplier_risk','dwd_process_outsource_delivery',
          'dwd_material_readiness','dwd_material_shortage','dwd_qc_trans'
        )
        AND NOT EXISTS (
          SELECT 1 FROM information_schema.COLUMNS c
          WHERE c.TABLE_SCHEMA=t.TABLE_SCHEMA AND c.TABLE_NAME=t.TABLE_NAME AND c.COLUMN_NAME='factory_id'
        )
    ) = 0
AND (
      SELECT COUNT(*)
      FROM information_schema.TABLES t
      WHERE t.TABLE_SCHEMA=DATABASE() AND t.TABLE_NAME LIKE 'mdp_stg_%'
        AND (
          NOT EXISTS (SELECT 1 FROM information_schema.COLUMNS c WHERE c.TABLE_SCHEMA=t.TABLE_SCHEMA AND c.TABLE_NAME=t.TABLE_NAME AND c.COLUMN_NAME='create_time')
          OR NOT EXISTS (SELECT 1 FROM information_schema.COLUMNS c WHERE c.TABLE_SCHEMA=t.TABLE_SCHEMA AND c.TABLE_NAME=t.TABLE_NAME AND c.COLUMN_NAME='update_time')
        )
    ) = 0
AND (
      SELECT COUNT(*)
      FROM (
        SELECT s.TABLE_NAME,s.INDEX_NAME
        FROM information_schema.STATISTICS s
        WHERE s.TABLE_SCHEMA=DATABASE()
          AND s.NON_UNIQUE=0
          AND s.INDEX_NAME<>'PRIMARY'
          AND s.TABLE_NAME IN (
            'mdp_stg_s4_iqc','mdp_stg_s4_return','mdp_stg_s4_shipment','mdp_stg_s4_shortage',
            'mdp_std_delivery_schedule','mdp_std_delivery_result','mdp_std_material_readiness','mdp_std_process_outsource_order',
            'dwd_supply_demand','dwd_supplier_delivery','dwd_supplier_risk','dwd_process_outsource_delivery',
            'dwd_material_readiness','dwd_qc_trans'
          )
        GROUP BY s.TABLE_NAME,s.INDEX_NAME
        HAVING SUM(s.COLUMN_NAME='factory_id')=0
      ) missing_factory_unique
    ) = 0
AND (SELECT COUNT(*) FROM `dwd_supplier_delivery` WHERE `factory_id`<=0) = 0
AND (SELECT COUNT(*) FROM `dwd_material_readiness` WHERE `factory_id`<=0) = 0
AND (
      SELECT COUNT(*) FROM `aidop_migration_derived_cleanup_log`
      WHERE `migration_version`='1.0.350'
        AND `table_name`='dwd_process_outsource_delivery'
        AND `estimated_rows`>=90000000
    ) = 1
THEN 1 ELSE 0 END AS ok;