CREATE TABLE `customerinfotype` (
    `id` int unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(50) NOT NULL DEFAULT '',
    `active` tinyint(1) NOT NULL DEFAULT 1,
    `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `createdById` int unsigned NOT NULL DEFAULT 10,
    `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `updatedById` int unsigned NOT NULL DEFAULT 10,
    PRIMARY KEY (`id`),
    INDEX `createdById` (`createdById`) USING BTREE,
    INDEX `updatedById` (`updatedById`) USING BTREE,
    INDEX `active` (`active`) USING BTREE,
    INDEX `name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `customerinfo` (
    `id` int unsigned NOT NULL AUTO_INCREMENT,
    `customerId` int unsigned NOT NULL,
    `typeId` int unsigned NOT NULL,
    `value` varchar(255) NOT NULL DEFAULT '',
    `active` tinyint(1) NOT NULL DEFAULT 1,
    `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `createdById` int unsigned NOT NULL DEFAULT 10,
    `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `updatedById` int unsigned NOT NULL DEFAULT 10,
    PRIMARY KEY (`id`),
    INDEX `customerId` (`customerId`) USING BTREE,
    INDEX `typeId` (`typeId`) USING BTREE,
    INDEX `createdById` (`createdById`) USING BTREE,
    INDEX `updatedById` (`updatedById`) USING BTREE,
    INDEX `active` (`active`) USING BTREE,
    INDEX `value` (`value`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO customerinfotype (name, active, created, createdById, updated, updatedById)
VALUES ('Yearly Target Reached', 1, UTC_TIMESTAMP, 10, UTC_TIMESTAMP, 10);

update systemsettings set value = REPLACE(value, '}', ', "yearlyTarget" : ""}'),
                          description = 'Conversion / Redemption Ratio ({"conversion" : 1, "redemption" : 10, "limit" : 50000, "yearlyTarget" : 100000}. Conversion: 1 means $1 = 1 point. Redemption: 10 means 10 points = $1'
where type = 'credit_points_conversion_ratio';
