CREATE TABLE IF NOT EXISTS `ado_smart_ops_improvement_action` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `tenant_id` BIGINT NOT NULL,
  `factory_id` BIGINT NOT NULL,
  `plan_id` BIGINT NOT NULL,
  `sequence_no` INT NOT NULL,
  `content` VARCHAR(500) NOT NULL,
  `owner_user_id` BIGINT NULL,
  `owner_name` VARCHAR(100) NULL,
  `due_date` DATETIME NULL,
  `status` VARCHAR(32) NOT NULL,
  `completed_at` DATETIME NULL,
  `proof_remark` TEXT NULL,
  `version` INT NOT NULL DEFAULT 1,
  `create_user_id` BIGINT NULL,
  `update_user_id` BIGINT NULL,
  `create_time` DATETIME NOT NULL,
  `update_time` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_improve_action_seq` (`tenant_id`,`plan_id`,`sequence_no`),
  KEY `idx_improve_action_owner` (`tenant_id`,`owner_user_id`,`status`),
  KEY `idx_improve_action_due` (`tenant_id`,`due_date`),
  CONSTRAINT `ck_improve_action_tenant` CHECK (`tenant_id` IS NOT NULL AND `tenant_id` NOT IN (0,1,1300000000001)),
  CONSTRAINT `ck_improve_action_factory` CHECK (`factory_id` > 0 AND `factory_id` <> `tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `ado_smart_ops_improvement_action_log` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `tenant_id` BIGINT NOT NULL,
  `action_id` BIGINT NOT NULL,
  `plan_id` BIGINT NOT NULL,
  `operation` VARCHAR(32) NOT NULL,
  `before_json` TEXT NULL,
  `after_json` TEXT NULL,
  `operator_user_id` BIGINT NULL,
  `remark` VARCHAR(500) NULL,
  `create_time` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_improve_action_log` (`tenant_id`,`action_id`,`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `ado_smart_ops_improvement_action_notice` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `tenant_id` BIGINT NOT NULL,
  `action_id` BIGINT NOT NULL,
  `notice_type` VARCHAR(32) NOT NULL,
  `notice_date` DATE NOT NULL,
  `notice_id` BIGINT NULL,
  `create_time` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_improve_action_notice` (`tenant_id`,`action_id`,`notice_type`,`notice_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
