DROP TABLE IF EXISTS `table_json`;

CREATE TABLE `table_json` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `value` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`value`)),
  `virtcomplete` tinyint(4) GENERATED ALWAYS AS (if(convert(json_unquote(json_extract(`value`,'$.complete')) using utf8mb4) = 'true',1,0)) VIRTUAL,
  `virtcurrentTimeMillis` bigint(20) GENERATED ALWAYS AS (nullif(json_unquote(json_extract(`value`,'$.currentTimeMillis')),'null')) VIRTUAL,
  `virtexecutionDate` varchar(15) GENERATED ALWAYS AS (nullif(json_unquote(json_extract(`value`,'$.executionDate')),'null')) VIRTUAL,
  `virtexecutionNumber` int(11) GENERATED ALWAYS AS (nullif(json_unquote(json_extract(`value`,'$.executionNumber')),'null')) VIRTUAL,
  `virtplanetName` varchar(50) GENERATED ALWAYS AS (nullif(json_unquote(json_extract(`value`,'$.planetName')),'null')) VIRTUAL,
  `virtplanetUniqueId` varchar(30) GENERATED ALWAYS AS (nullif(json_unquote(json_extract(`value`,'$.planetUniqueId')),'null')) VIRTUAL,
  `virtremove` tinyint(4) GENERATED ALWAYS AS (if(convert(json_unquote(json_extract(`value`,'$.remove')) using utf8mb4) = 'true',1,0)) VIRTUAL,
  `virtresult` varchar(20) GENERATED ALWAYS AS (nullif(json_unquote(json_extract(`value`,'$.result')),'null')) VIRTUAL,
  `virtrobotExecutionId` varchar(250) GENERATED ALWAYS AS (nullif(json_unquote(json_extract(`value`,'$.robotExecutionId')),'null')) VIRTUAL,
  `virtrobotName` varchar(50) GENERATED ALWAYS AS (nullif(json_unquote(json_extract(`value`,'$.robotName')),'null')) VIRTUAL,
  `virtrobotUniqueId` varchar(30) GENERATED ALWAYS AS (nullif(json_unquote(json_extract(`value`,'$.robotUniqueId')),'null')) VIRTUAL,
  `virtstartTimeMillis` bigint(20) GENERATED ALWAYS AS (nullif(json_unquote(json_extract(`value`,'$.startTimeMillis')),'null')) VIRTUAL,
  `virtendTimeMillis` bigint(20) GENERATED ALWAYS AS (nullif(json_unquote(json_extract(`value`,'$.endTimeMillis')),'null')) VIRTUAL,
  `virtcreationTS` bigint(20) GENERATED ALWAYS AS (nullif(json_unquote(json_extract(`value`,'$.creationTS')),'null')) VIRTUAL,
  PRIMARY KEY (`id`),
  KEY `ix_currentTimeMillis_startTimeMillis` (`virtcurrentTimeMillis`,`virtstartTimeMillis`),
  KEY `ix_executionDate` (`virtexecutionDate`),
  KEY `ix_executionNumber` (`virtexecutionNumber`),
  KEY `ix_planetName` (`virtplanetName`),
  KEY `ix_planetUniqueId` (`virtplanetUniqueId`),
  KEY `ix_remove` (`virtremove`),
  KEY `ix_result_complete` (`virtresult`,`virtcomplete`),
  KEY `ix_robotExecutionId` (`virtrobotExecutionId`),
  KEY `ix_robotName` (`virtrobotName`),
  KEY `ix_robotName_creationTS` (`virtrobotName`,`virtcreationTS`),
  KEY `ix_robotName_executionNumber` (`virtrobotName`,`virtexecutionNumber`),
  KEY `ix_robotUniqueId` (`virtrobotUniqueId`),
  KEY `ix_startTimeMillis` (`virtstartTimeMillis`),
  KEY `ix_endTimeMillis` (`virtendTimeMillis`),
  KEY `ix_robotName_startTimeMillis` (`virtrobotName`,`virtstartTimeMillis`),
  KEY `ix_creationTS` (`virtcreationTS`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO table_json (value) VALUES ('{
 "complete": true,
 "auditTimestamp":"2025-11-06 20:11:06",
 "currentTimeMillis": 1678886400000,
 "executionDate": "2025-11-03",
 "executionNumber": 101,
 "planetName": "Mars",
 "planetUniqueId": "P-MAR-001",
 "remove": false,
 "result": "OK",
 "robotExecutionId": "EXEC-A7B8C9D0",
 "robotName": "ExplorerBot",
 "robotUniqueId": "RB-EXP-10",
 "startTimeMillis": 1678886300000,
 "endTimeMillis": 1678886400000,
 "creationTS": 1678886200000
}');
