eduepos-group/docs/inspect-schema-optimized.sql

/* Store inspection and assessment schema. */
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `bu_rectification`;
DROP TABLE IF EXISTS `bu_inspect_task_item`;
DROP TABLE IF EXISTS `bu_inspect_task`;
DROP TABLE IF EXISTS `bu_inspect_plan_store`;
DROP TABLE IF EXISTS `bu_inspect_plan_indicator`;
DROP TABLE IF EXISTS `bu_inspect_plan`;
DROP TABLE IF EXISTS `bu_inspect_indicator`;
DROP TABLE IF EXISTS `bu_assess_result`;

CREATE TABLE `bu_inspect_indicator` (
  `INDICATOR_ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Indicator ID',
  `INDICATOR_CODE` varchar(64) NOT NULL COMMENT 'Indicator code',
  `INDICATOR_NAME` varchar(100) NOT NULL COMMENT 'Indicator name',
  `INDICATOR_TYPE` tinyint(4) NOT NULL DEFAULT '2' COMMENT '1 automatic, 2 manual',
  `DATA_SOURCE` varchar(64) DEFAULT NULL COMMENT 'Automatic data source',
  `MAX_SCORE` decimal(10,2) NOT NULL DEFAULT '100.00' COMMENT 'Default maximum score',
  `PASS_SCORE` decimal(10,2) NOT NULL DEFAULT '60.00' COMMENT 'Default passing score',
  `WEIGHT` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT 'Weight percentage',
  `SCORE_RULE` text COMMENT 'Scoring rule JSON',
  `SORT` int(11) NOT NULL DEFAULT '0' COMMENT 'Display order',
  `STATUS` tinyint(4) NOT NULL DEFAULT '1' COMMENT '1 enabled, 0 disabled',
  `RMK` varchar(500) DEFAULT NULL COMMENT 'Remark',
  `CREATE_DATE` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATE_DATE` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`INDICATOR_ID`),
  UNIQUE KEY `UK_INSPECT_INDICATOR_CODE` (`INDICATOR_CODE`),
  KEY `IDX_INSPECT_INDICATOR_TYPE` (`INDICATOR_TYPE`),
  KEY `IDX_INSPECT_INDICATOR_STATUS_SORT` (`STATUS`,`SORT`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Inspection indicators';

CREATE TABLE `bu_inspect_plan` (
  `PLAN_ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Inspection plan ID',
  `PLAN_NAME` varchar(150) NOT NULL COMMENT 'Plan name',
  `PLAN_TYPE` tinyint(4) NOT NULL DEFAULT '1' COMMENT '1 regular, 2 special, 3 temporary',
  `CYCLE_TYPE` tinyint(4) NOT NULL DEFAULT '2' COMMENT '1 daily, 2 weekly, 3 monthly, 4 once',
  `START_DATE` date NOT NULL COMMENT 'Plan start date',
  `END_DATE` date NOT NULL COMMENT 'Plan end date',
  `NEXT_EXECUTE_DATE` date DEFAULT NULL COMMENT 'Next execution date',
  `INSPECTOR_ID` bigint(20) DEFAULT NULL COMMENT 'Default inspector ID',
  `INSPECTOR_NAME` varchar(100) DEFAULT NULL COMMENT 'Default inspector name snapshot',
  `AUTO_COLLECT_FLAG` tinyint(4) NOT NULL DEFAULT '1' COMMENT '1 enable automatic collection, 0 disable',
  `STORE_COUNT` int(11) NOT NULL DEFAULT '0' COMMENT 'Associated store count snapshot',
  `STATUS` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0 draft, 1 published, 2 paused, 3 finished, 4 deleted',
  `PUBLISH_DATE` datetime DEFAULT NULL,
  `FINISH_DATE` datetime DEFAULT NULL,
  `RMK` varchar(500) DEFAULT NULL,
  `CREATE_DATE` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATE_DATE` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`PLAN_ID`),
  KEY `IDX_INSPECT_PLAN_STATUS` (`STATUS`),
  KEY `IDX_INSPECT_PLAN_CYCLE` (`CYCLE_TYPE`,`NEXT_EXECUTE_DATE`),
  KEY `IDX_INSPECT_PLAN_DATE` (`START_DATE`,`END_DATE`),
  KEY `IDX_INSPECT_PLAN_INSPECTOR` (`INSPECTOR_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Inspection plans';

CREATE TABLE `bu_inspect_plan_indicator` (
  `PLAN_INDICATOR_ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Plan-indicator relation ID',
  `PLAN_ID` bigint(20) NOT NULL COMMENT 'Inspection plan ID',
  `INDICATOR_ID` bigint(20) NOT NULL COMMENT 'Indicator ID',
  `INDICATOR_NAME` varchar(100) NOT NULL COMMENT 'Indicator name snapshot',
  `INDICATOR_TYPE` tinyint(4) NOT NULL COMMENT 'Indicator type snapshot',
  `MAX_SCORE` decimal(10,2) NOT NULL DEFAULT '100.00' COMMENT 'Plan-specific maximum score',
  `PASS_SCORE` decimal(10,2) NOT NULL DEFAULT '60.00' COMMENT 'Plan-specific passing score',
  `WEIGHT` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT 'Weight percentage',
  `SCORE_RULE` text COMMENT 'Plan-specific scoring rule snapshot',
  `SORT` int(11) NOT NULL DEFAULT '0',
  `STATUS` tinyint(4) NOT NULL DEFAULT '1' COMMENT '1 valid, 0 invalid',
  `CREATE_DATE` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATE_DATE` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`PLAN_INDICATOR_ID`),
  UNIQUE KEY `UK_INSPECT_PLAN_INDICATOR` (`PLAN_ID`,`INDICATOR_ID`),
  KEY `IDX_INSPECT_PLAN_INDICATOR_INDICATOR` (`INDICATOR_ID`),
  KEY `IDX_INSPECT_PLAN_INDICATOR_SORT` (`PLAN_ID`,`STATUS`,`SORT`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Inspection plan indicators';

CREATE TABLE `bu_inspect_plan_store` (
  `PLAN_STORE_ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Plan-store relation ID',
  `PLAN_ID` bigint(20) NOT NULL,
  `STORE_ID` bigint(20) NOT NULL,
  `STORE_NAME` varchar(150) DEFAULT NULL COMMENT 'Store name snapshot',
  `STORE_BELONGTO` bigint(20) DEFAULT NULL,
  `PROVINCE` bigint(20) DEFAULT NULL,
  `CITY` bigint(20) DEFAULT NULL,
  `STATUS` tinyint(4) NOT NULL DEFAULT '1' COMMENT '1 valid, 0 deleted',
  `CREATE_DATE` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`PLAN_STORE_ID`),
  UNIQUE KEY `UK_INSPECT_PLAN_STORE` (`PLAN_ID`,`STORE_ID`),
  KEY `IDX_INSPECT_PLAN_STORE_STORE` (`STORE_ID`),
  KEY `IDX_INSPECT_PLAN_STORE_AREA` (`STORE_BELONGTO`,`PROVINCE`,`CITY`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Inspection plan stores';

CREATE TABLE `bu_inspect_task` (
  `TASK_ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Inspection task ID',
  `PLAN_ID` bigint(20) NOT NULL,
  `PLAN_NAME` varchar(150) DEFAULT NULL COMMENT 'Plan name snapshot',
  `STORE_ID` bigint(20) NOT NULL,
  `STORE_NAME` varchar(150) DEFAULT NULL COMMENT 'Store name snapshot',
  `TASK_DATE` date NOT NULL COMMENT 'Task date',
  `INSPECTOR_ID` bigint(20) DEFAULT NULL COMMENT 'Actual inspector ID',
  `INSPECTOR_NAME` varchar(100) DEFAULT NULL COMMENT 'Actual inspector name snapshot',
  `TOTAL_SCORE` decimal(10,2) DEFAULT NULL COMMENT 'Total score',
  `MAX_SCORE` decimal(10,2) DEFAULT NULL COMMENT 'Total maximum score',
  `PASS_SCORE` decimal(10,2) DEFAULT NULL COMMENT 'Total passing score',
  `PASS_FLAG` tinyint(4) DEFAULT NULL COMMENT '1 passed, 0 failed',
  `UNPASS_ITEM_COUNT` int(11) NOT NULL DEFAULT '0' COMMENT 'Failed item count',
  `START_DATE` datetime DEFAULT NULL COMMENT 'Execution start time',
  `SUBMIT_DATE` datetime DEFAULT NULL COMMENT 'Submission time',
  `CANCEL_DATE` datetime DEFAULT NULL COMMENT 'Cancellation time',
  `STATUS` tinyint(4) NOT NULL DEFAULT '1' COMMENT '1 pending, 2 executing, 3 finished, 4 cancelled',
  `RMK` varchar(500) DEFAULT NULL COMMENT 'Task remark',
  `CREATE_DATE` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATE_DATE` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`TASK_ID`),
  UNIQUE KEY `UK_INSPECT_TASK_PLAN_STORE_DATE` (`PLAN_ID`,`STORE_ID`,`TASK_DATE`),
  KEY `IDX_INSPECT_TASK_STORE_DATE` (`STORE_ID`,`TASK_DATE`),
  KEY `IDX_INSPECT_TASK_STATUS_DATE` (`STATUS`,`TASK_DATE`),
  KEY `IDX_INSPECT_TASK_INSPECTOR` (`INSPECTOR_ID`,`STATUS`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Inspection tasks';

CREATE TABLE `bu_inspect_task_item` (
  `TASK_ITEM_ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Task score item ID',
  `TASK_ID` bigint(20) NOT NULL,
  `PLAN_ID` bigint(20) NOT NULL,
  `STORE_ID` bigint(20) NOT NULL,
  `PLAN_INDICATOR_ID` bigint(20) NOT NULL DEFAULT '0' COMMENT '0 means plan-level score',
  `INDICATOR_ID` bigint(20) DEFAULT NULL COMMENT 'Indicator ID; NULL for plan-level score',
  `SCORE_LEVEL` tinyint(4) NOT NULL DEFAULT '2' COMMENT '1 plan level, 2 indicator level',
  `INDICATOR_CODE` varchar(64) DEFAULT NULL COMMENT 'Indicator code snapshot',
  `INDICATOR_NAME` varchar(100) DEFAULT NULL COMMENT 'Indicator name snapshot',
  `INDICATOR_TYPE` tinyint(4) DEFAULT NULL,
  `DATA_SOURCE` varchar(64) DEFAULT NULL,
  `WEIGHT` decimal(10,2) NOT NULL DEFAULT '0.00',
  `MAX_SCORE` decimal(10,2) NOT NULL DEFAULT '100.00',
  `PASS_SCORE` decimal(10,2) NOT NULL DEFAULT '60.00',
  `RAW_VALUE` decimal(12,4) DEFAULT NULL COMMENT 'Automatic collection raw value',
  `RAW_VALUE_TEXT` varchar(100) DEFAULT NULL COMMENT 'Raw value display text',
  `SCORE` decimal(10,2) DEFAULT NULL,
  `WEIGHTED_SCORE` decimal(10,2) DEFAULT NULL,
  `PASS_FLAG` tinyint(4) DEFAULT NULL COMMENT '1 passed, 0 failed',
  `PICTURE_URLS` varchar(2000) DEFAULT NULL,
  `RMK` varchar(1000) DEFAULT NULL,
  `SORT` int(11) NOT NULL DEFAULT '0',
  `STATUS` tinyint(4) NOT NULL DEFAULT '1',
  `CREATE_DATE` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATE_DATE` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`TASK_ITEM_ID`),
  UNIQUE KEY `UK_INSPECT_TASK_ITEM` (`TASK_ID`,`PLAN_INDICATOR_ID`),
  KEY `IDX_INSPECT_TASK_ITEM_STORE` (`STORE_ID`),
  KEY `IDX_INSPECT_TASK_ITEM_INDICATOR` (`INDICATOR_ID`),
  KEY `IDX_INSPECT_TASK_ITEM_PASS` (`PASS_FLAG`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Inspection task score items';

CREATE TABLE `bu_rectification` (
  `RECT_ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `TASK_ID` bigint(20) NOT NULL,
  `TASK_ITEM_ID` bigint(20) NOT NULL,
  `PLAN_ID` bigint(20) DEFAULT NULL,
  `STORE_ID` bigint(20) NOT NULL,
  `STORE_NAME` varchar(150) DEFAULT NULL,
  `INDICATOR_ID` bigint(20) DEFAULT NULL COMMENT 'NULL for plan-level rectification',
  `INDICATOR_NAME` varchar(100) DEFAULT NULL COMMENT 'Indicator name snapshot',
  `RECT_ROUND` int(11) NOT NULL DEFAULT '1',
  `PARENT_RECT_ID` bigint(20) DEFAULT NULL,
  `INSPECT_SCORE` decimal(10,2) DEFAULT NULL,
  `PASS_SCORE` decimal(10,2) DEFAULT NULL,
  `NOTICE_CONTENT` varchar(1000) DEFAULT NULL,
  `DEADLINE_DATE` datetime NOT NULL,
  `START_DATE` datetime DEFAULT NULL,
  `FEEDBACK_CONTENT` varchar(1000) DEFAULT NULL,
  `FEEDBACK_PICTURES` varchar(2000) DEFAULT NULL,
  `FEEDBACK_DATE` datetime DEFAULT NULL,
  `RECHECK_SCORE` decimal(10,2) DEFAULT NULL,
  `RECHECK_RESULT` tinyint(4) DEFAULT NULL,
  `RECHECK_PICTURES` varchar(2000) DEFAULT NULL,
  `RECHECK_RMK` varchar(1000) DEFAULT NULL,
  `RECHECK_USER_ID` bigint(20) DEFAULT NULL,
  `RECHECK_USER_NAME` varchar(100) DEFAULT NULL,
  `RECHECK_DATE` datetime DEFAULT NULL,
  `URGE_COUNT` int(11) NOT NULL DEFAULT '0',
  `LAST_URGE_DATE` datetime DEFAULT NULL,
  `STATUS` tinyint(4) NOT NULL DEFAULT '1',
  `CREATE_DATE` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATE_DATE` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`RECT_ID`),
  KEY `IDX_RECT_TASK_ITEM` (`TASK_ITEM_ID`),
  KEY `IDX_RECT_STORE_STATUS` (`STORE_ID`,`STATUS`),
  KEY `IDX_RECT_INDICATOR` (`INDICATOR_ID`),
  KEY `IDX_RECT_DEADLINE` (`STATUS`,`DEADLINE_DATE`),
  KEY `IDX_RECT_PARENT` (`PARENT_RECT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Inspection rectification records';

CREATE TABLE `bu_assess_result` (
  `ASSESS_RESULT_ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `STORE_ID` bigint(20) NOT NULL,
  `STORE_NAME` varchar(150) DEFAULT NULL,
  `STORE_BELONGTO` bigint(20) DEFAULT NULL,
  `PROVINCE` bigint(20) DEFAULT NULL,
  `CITY` bigint(20) DEFAULT NULL,
  `PERIOD_TYPE` tinyint(4) NOT NULL COMMENT '1 weekly, 2 monthly',
  `PERIOD_CODE` varchar(20) NOT NULL,
  `PERIOD_START_DATE` date NOT NULL,
  `PERIOD_END_DATE` date NOT NULL,
  `INSPECT_SCORE` decimal(10,2) NOT NULL DEFAULT '0.00',
  `AUTO_SCORE` decimal(10,2) NOT NULL DEFAULT '0.00',
  `TOTAL_SCORE` decimal(10,2) NOT NULL DEFAULT '0.00',
  `ASSESS_LEVEL` tinyint(4) NOT NULL DEFAULT '4',
  `RANK_NO` int(11) DEFAULT NULL,
  `TASK_COUNT` int(11) NOT NULL DEFAULT '0',
  `FINISH_TASK_COUNT` int(11) NOT NULL DEFAULT '0',
  `RECT_COUNT` int(11) NOT NULL DEFAULT '0',
  `OVERTIME_RECT_COUNT` int(11) NOT NULL DEFAULT '0',
  `SUMMARY_DATE` datetime NOT NULL,
  `STATUS` tinyint(4) NOT NULL DEFAULT '1',
  `CREATE_DATE` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATE_DATE` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ASSESS_RESULT_ID`),
  UNIQUE KEY `UK_ASSESS_STORE_PERIOD` (`STORE_ID`,`PERIOD_TYPE`,`PERIOD_CODE`),
  KEY `IDX_ASSESS_PERIOD_RANK` (`PERIOD_TYPE`,`PERIOD_CODE`,`RANK_NO`),
  KEY `IDX_ASSESS_AREA` (`STORE_BELONGTO`,`PROVINCE`,`CITY`),
  KEY `IDX_ASSESS_LEVEL` (`ASSESS_LEVEL`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Store assessment results';

SET FOREIGN_KEY_CHECKS = 1;