pre.sql

update bom
set bomSourceType = JSON_ARRAY(bomSourceType);

alter table bom
modify bomSourceType json;

alter table contractManufacturing rename column processLinkedContract to productionWorkflowContract;

alter table batch
add column `reservedForEntityLineItemId` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL;

update smeassist.batch
set smeassist.batch.reservedForEntity = null;

update smeassist.batch
set smeassist.batch.reservedForEntityId = null;

alter table internalTransferSourceMapping
add column subSourceId varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL;

alter table internalTransferSourceMapping rename column subType to subSourceType;

alter table `contractManufacturingJob`
add column `sourceId` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL;

alter table `contractManufacturingJob`
add column `sourceType` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL;

delete
from bomMapping;

alter table smeassist.bomMapping
drop column isOutSourced;

update bom
set bomType = 'INDIVIDUAL';
CREATE TABLE `productionWorkflow` (
    `id` char(20) NOT NULL,
    `createdBy` char(20) NOT NULL,
    `dateCreated` bigint NOT NULL,
    `isDeleted` bit(1) NOT NULL,
    `lastModified` bigint NOT NULL,
    `lastModifiedBy` char(20) NOT NULL,
    `organisationId` char(20) NOT NULL,
    `actualStartDate` bigint DEFAULT NULL,
    `bomId` varchar(255) NOT NULL,
    `closureRemarks` varchar(255) DEFAULT NULL,
    `completedDate` bigint DEFAULT NULL,
    `contactId` varchar(255) DEFAULT NULL,
    `contactType` varchar(255) DEFAULT NULL,
    `expectedCompletionDate` bigint DEFAULT NULL,
    `expectedStartDate` bigint DEFAULT NULL,
    `inventoryId` varchar(255) NOT NULL,
    `orderNumber` varchar(255) DEFAULT NULL,
    `primaryProductId` varchar(255) DEFAULT NULL,
    `primaryProductName` varchar(255) DEFAULT NULL,
    `primaryProductionQuantity` decimal(19, 2) DEFAULT NULL,
    `remarks` varchar(255) DEFAULT NULL,
    `sourceId` varchar(255) DEFAULT NULL,
    `sourceType` varchar(255) DEFAULT NULL,
    `status` varchar(255) DEFAULT NULL,
    PRIMARY KEY (`id`)
)
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8mb4
    COLLATE = utf8mb4_unicode_ci;

CREATE TABLE `productionWorkflowItem` (
    `id` char(20) NOT NULL,
    `createdBy` char(20) NOT NULL,
    `dateCreated` bigint NOT NULL,
    `isDeleted` bit(1) NOT NULL,
    `lastModified` bigint NOT NULL,
    `lastModifiedBy` char(20) NOT NULL,
    `organisationId` char(20) NOT NULL,
    `bomId` varchar(255) NOT NULL,
    `level` int NOT NULL,
    `productId` varchar(255) NOT NULL,
    `productionWorkflowId` varchar(255) NOT NULL,
    `sourceItemId` varchar(255) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `index_productId`(`productId`),
    KEY `index_dateCreated`(`dateCreated`),
    KEY `index_lastModified`(`lastModified`)
)
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8mb4
    COLLATE = utf8mb4_unicode_ci;

CREATE TABLE `productionWorkflowItemSplit` (
    `id` char(20) NOT NULL,
    `createdBy` char(20) NOT NULL,
    `dateCreated` bigint NOT NULL,
    `isDeleted` bit(1) NOT NULL,
    `lastModified` bigint NOT NULL,
    `lastModifiedBy` char(20) NOT NULL,
    `organisationId` char(20) NOT NULL,
    `bomId` varchar(255) NOT NULL,
    `entityId` varchar(255) NOT NULL,
    `entityInventoryId` varchar(255) NOT NULL,
    `entityLineItemId` varchar(255) NOT NULL,
    `entityType` varchar(255) NOT NULL,
    `entityZoneId` varchar(255) NOT NULL,
    `productId` varchar(255) NOT NULL,
    `productionWorkflowId` varchar(255) NOT NULL,
    `productionWorkflowItemId` varchar(255) NOT NULL,
    `quantity` decimal(19, 2) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `index_productId`(`productId`),
    KEY `index_entityType_entityId_productId`(`entityType`, `entityId`, `productId`),
    KEY `index_dateCreated`(`dateCreated`),
    KEY `index_lastModified`(`lastModified`)
)
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8mb4
    COLLATE = utf8mb4_unicode_ci;

CREATE TABLE `productionWorkflowTask` (
    `id` char(20) NOT NULL,
    `createdBy` char(20) NOT NULL,
    `dateCreated` bigint NOT NULL,
    `isDeleted` bit(1) NOT NULL,
    `lastModified` bigint NOT NULL,
    `lastModifiedBy` char(20) NOT NULL,
    `organisationId` char(20) NOT NULL,
    `itemSplitId` varchar(255) NOT NULL,
    `productionWorkflowId` varchar(255) NOT NULL,
    `quantity` decimal(19, 2) NOT NULL,
    `status` int NOT NULL,
    PRIMARY KEY (`id`),
    KEY `index_itemSplitId`(`itemSplitId`),
    KEY `index_productionWorkflowId`(`productionWorkflowId`),
    KEY `index_dateCreated`(`dateCreated`),
    KEY `index_lastModified`(`lastModified`)
)
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8mb4
    COLLATE = utf8mb4_unicode_ci;

CREATE TABLE `productionWorkflowTaskItem` (
    `id` char(20) NOT NULL,
    `createdBy` char(20) NOT NULL,
    `dateCreated` bigint NOT NULL,
    `isDeleted` bit(1) NOT NULL,
    `lastModified` bigint NOT NULL,
    `lastModifiedBy` char(20) NOT NULL,
    `organisationId` char(20) NOT NULL,
    `itemSplitId` varchar(255) NOT NULL,
    `productionWorkflowId` varchar(255) NOT NULL,
    `quantity` decimal(19, 2) NOT NULL,
    `taskId` varchar(255) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `index_itemSplitId`(`itemSplitId`)
)
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8mb4
    COLLATE = utf8mb4_unicode_ci;

drop table productionOrderMapping;