--
-- Tring to repro https://bugs.mysql.com/bug.php?id=112694
--

CREATE TABLE `table_systems` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `type` varchar(64) NOT NULL,
  `subtype` varchar(255) DEFAULT NULL,
  `TPField` json DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_type` (`type`),
  KEY `idx_type_subtype` (`type`,`subtype`),
  KEY `idx_tpfields` ((cast(`TPField` as char(255) array)))
) ENGINE=InnoDB AUTO_INCREMENT=1000000 DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC;

--
-- Seed data
--

DELIMITER $$

CREATE PROCEDURE SeedData()
BEGIN
    -- Temporary variable to hold the subtype
    DECLARE subtypeValue VARCHAR(255);
    -- Temporary variable to hold the TPField JSON.
    DECLARE tpFieldValue JSON;
    -- Counter for the loop.
    DECLARE i INT DEFAULT 1;
    -- Predefined set of subtype values.
    DECLARE subtypeOptions TEXT DEFAULT 'sub1,sub2,sub3,sub4,sub5,sub6,sub7,sub8,sub9,sub10,sub11,sub12,sub13,sub14,sub15,sub16,sub17,sub18,sub19,sub20,sub21,sub22,sub23,sub24,sub25,sub26,sub27,sub28,sub29,sub30,sub31,sub32,sub33,sub34,sub35,sub36,sub37,sub38,sub39,sub40,sub41,sub42,sub43,sub44,sub45,sub46,sub47,sub48,sub49,sub50,sub51,sub52,sub53,sub54,sub55,sub56,sub57,sub58,sub59,sub60,sub61,sub62,sub63,sub64,sub65,sub66,sub67,sub68,sub69,sub70,sub71,sub72,sub73,sub74,sub75,sub76,sub77,sub78,sub79,sub80,sub81,sub82,sub83,sub84,sub85,sub86,sub87,sub88,sub89,sub90,sub91,sub92,sub93,sub94,sub95,sub96,sub97,sub98,sub99,sub100'; -- Add your 100 subtype values here separated by commas.
    -- Temporary variable to hold random index for subtype selection.
    DECLARE randomIndex INT;
    -- Temporary variable for the alphanumeric string generation loop.
    DECLARE j INT;
    -- Temporary variable to hold a single alphanumeric string.
    DECLARE randomString VARCHAR(255);

    -- Start the loop.
    WHILE i <= 100000 DO
        -- Choose a random subtype from the predefined set.
        SET randomIndex = FLOOR(1 + RAND() * 100); -- Generating a random index between 1 and 100.
        SET subtypeValue = SUBSTRING_INDEX(SUBSTRING_INDEX(subtypeOptions, ',', randomIndex), ',', -1);
        
        -- Reset TPField JSON value.
        SET tpFieldValue = JSON_ARRAY();
        -- Generate a random length for the TPField JSON array between 2 and 10.
        SET j = FLOOR(2 + RAND() * 9);
        WHILE j > 0 DO
            -- Generate a random alphanumeric string of a random length between 5 and 10 characters.
            SET randomString = ELT(FLOOR(1 + RAND() * 36), 'A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
                                                                 '0','1','2','3','4','5','6','7','8','9');
            SET randomString = CONCAT(randomString, LPAD(FLOOR(RAND() * 100000), 4, '0'));
            
            -- Add the alphanumeric string to the TPField JSON array.
            SET tpFieldValue = JSON_ARRAY_APPEND(tpFieldValue, '$', randomString);
            SET j = j - 1;
        END WHILE;
        
        -- Insert the row into the table. Use 'cloud' for the first few rows and 'hardware' for the rest.
        IF i < 1 THEN
            INSERT INTO table_systems (`type`, `subtype`, `TPField`) VALUES ('cloud', subtypeValue, tpFieldValue);
        ELSE
            INSERT INTO table_systems (`type`, `subtype`, `TPField`) VALUES ('hardware', subtypeValue, tpFieldValue);
        END IF;
        
        -- Increment the counter.
        SET i = i + 1;
    END WHILE;
END$$

DELIMITER ;

CALL SeedData();

--
-- Try querying one of these records.
--

SELECT * FROM table_systems WHERE type = 'hardware' AND subtype = 'sub17' AND  JSON_LENGTH(TPField) = 7  AND  JSON_CONTAINS(TPField, '[\"X3718\",\"64142\",\"M5246\",\"U2397\",\"S8326\",\"W6626\",\"P9704\"]')