-- Stop API writers and back up before applying. MySQL DDL is not transactional.
-- Abort on malformed legacy hierarchy before changing persistent tables.
CREATE TEMPORARY TABLE pilot_split_guard (violations BIGINT NOT NULL CHECK (violations = 0));
INSERT INTO pilot_split_guard SELECT COUNT(*) FROM Asset a
LEFT JOIN PlantUnit c ON c.id=a.unitId LEFT JOIN Asset p ON p.id=a.parentId
WHERE c.id IS NULL OR (a.level='EQUIPMENT' AND a.parentId IS NOT NULL)
OR (a.level='SUB_ITEM' AND (p.id IS NULL OR p.level<>'EQUIPMENT' OR p.unitId<>a.unitId));
CREATE TABLE `category` (
    `id` VARCHAR(30) NOT NULL,
    `code` VARCHAR(50) NOT NULL,
    `name` VARCHAR(150) NOT NULL,
    `photoKey` VARCHAR(255) NULL,
    `version` INTEGER UNSIGNED NOT NULL DEFAULT 0,
    `archivedAt` DATETIME(3) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `category_code_key`(`code`),
    UNIQUE INDEX `category_name_key`(`name`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE `equipment` (
    `id` VARCHAR(30) NOT NULL,
    `code` VARCHAR(80) NOT NULL,
    `name` VARCHAR(191) NOT NULL,
    `version` INTEGER UNSIGNED NOT NULL DEFAULT 0,
    `photoKey` VARCHAR(255) NULL,
    `manufacturer` VARCHAR(150) NULL,
    `serialNumber` VARCHAR(150) NULL,
    `photoUrl` VARCHAR(2048) NULL,
    `commissionedAt` DATE NULL,
    `archivedAt` DATETIME(3) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,
    `categoryId` VARCHAR(30) NOT NULL,

    UNIQUE INDEX `equipment_code_key`(`code`),
    INDEX `equipment_categoryId_archivedAt_idx`(`categoryId`, `archivedAt`),
    UNIQUE INDEX `equipment_categoryId_name_key`(`categoryId`, `name`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE `items` (
    `id` VARCHAR(30) NOT NULL,
    `code` VARCHAR(80) NOT NULL,
    `name` VARCHAR(191) NOT NULL,
    `version` INTEGER UNSIGNED NOT NULL DEFAULT 0,
    `photoKey` VARCHAR(255) NULL,
    `manufacturer` VARCHAR(150) NULL,
    `serialNumber` VARCHAR(150) NULL,
    `photoUrl` VARCHAR(2048) NULL,
    `commissionedAt` DATE NULL,
    `archivedAt` DATETIME(3) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,
    `equipmentId` VARCHAR(30) NOT NULL,

    UNIQUE INDEX `items_code_key`(`code`),
    INDEX `items_equipmentId_archivedAt_idx`(`equipmentId`, `archivedAt`),
    UNIQUE INDEX `items_equipmentId_name_key`(`equipmentId`, `name`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE `operating_log` (
    `id` VARCHAR(30) NOT NULL,
    `equipmentId` VARCHAR(30) NULL,
    `itemId` VARCHAR(30) NULL,
    `periodStart` DATETIME(3) NOT NULL,
    `periodEnd` DATETIME(3) NOT NULL,
    `operatingMinutes` INTEGER UNSIGNED NOT NULL,

    UNIQUE INDEX `operating_log_equipmentId_periodStart_key`(`equipmentId`, `periodStart`),
    UNIQUE INDEX `operating_log_itemId_periodStart_key`(`itemId`, `periodStart`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE `part_assignment` (
    `equipmentId` VARCHAR(30) NULL,
    `itemId` VARCHAR(30) NULL,
    `partId` VARCHAR(30) NOT NULL,
    `requiredQuantity` INTEGER UNSIGNED NOT NULL DEFAULT 1,
    `id` VARCHAR(30) NOT NULL,

    INDEX `part_assignment_partId_idx`(`partId`),
    UNIQUE INDEX `part_assignment_equipmentId_partId_key`(`equipmentId`, `partId`),
    UNIQUE INDEX `part_assignment_itemId_partId_key`(`itemId`, `partId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
INSERT INTO category (id,code,name,photoKey,version,archivedAt,createdAt,updatedAt) SELECT id,code,name,photoKey,version,archivedAt,createdAt,updatedAt FROM PlantUnit;
INSERT INTO equipment (id,code,name,version,photoKey,manufacturer,serialNumber,photoUrl,commissionedAt,archivedAt,createdAt,updatedAt,categoryId) SELECT id,code,name,version,photoKey,manufacturer,serialNumber,photoUrl,commissionedAt,archivedAt,createdAt,updatedAt,unitId FROM Asset WHERE level='EQUIPMENT';
INSERT INTO items (id,code,name,version,photoKey,manufacturer,serialNumber,photoUrl,commissionedAt,archivedAt,createdAt,updatedAt,equipmentId) SELECT id,code,name,version,photoKey,manufacturer,serialNumber,photoUrl,commissionedAt,archivedAt,createdAt,updatedAt,parentId FROM Asset WHERE level='SUB_ITEM';
INSERT INTO operating_log (id,equipmentId,itemId,periodStart,periodEnd,operatingMinutes)
SELECT l.id,CASE WHEN a.level='EQUIPMENT' THEN a.id END,CASE WHEN a.level='SUB_ITEM' THEN a.id END,l.periodStart,l.periodEnd,l.operatingMinutes FROM AssetOperatingLog l JOIN Asset a ON a.id=l.assetId;
INSERT INTO part_assignment (id,equipmentId,itemId,partId,requiredQuantity)
SELECT LEFT(SHA2(CONCAT(p.assetId,':',p.partId),256),30),CASE WHEN a.level='EQUIPMENT' THEN a.id END,CASE WHEN a.level='SUB_ITEM' THEN a.id END,p.partId,p.requiredQuantity FROM AssetPart p JOIN Asset a ON a.id=p.assetId;
INSERT INTO pilot_split_guard SELECT ABS((SELECT COUNT(*) FROM Asset)-(SELECT COUNT(*) FROM equipment)-(SELECT COUNT(*) FROM items));
INSERT INTO pilot_split_guard SELECT ABS((SELECT COUNT(*) FROM PlantUnit)-(SELECT COUNT(*) FROM category));
INSERT INTO pilot_split_guard SELECT ABS((SELECT COUNT(*) FROM AssetPart)-(SELECT COUNT(*) FROM part_assignment));
INSERT INTO pilot_split_guard SELECT ABS((SELECT COUNT(*) FROM AssetOperatingLog)-(SELECT COUNT(*) FROM operating_log));
ALTER TABLE CalibrationRecord ADD COLUMN equipmentId VARCHAR(30) NULL, ADD COLUMN itemId VARCHAR(30) NULL;
UPDATE CalibrationRecord d JOIN Asset a ON a.id=d.assetId SET d.equipmentId=CASE WHEN a.level='EQUIPMENT' THEN a.id END,d.itemId=CASE WHEN a.level='SUB_ITEM' THEN a.id END;
INSERT INTO pilot_split_guard SELECT COUNT(*) FROM CalibrationRecord WHERE assetId IS NOT NULL AND COALESCE(itemId,equipmentId,'')<>assetId;
ALTER TABLE DamageReport ADD COLUMN equipmentId VARCHAR(30) NULL, ADD COLUMN itemId VARCHAR(30) NULL;
UPDATE DamageReport d JOIN Asset a ON a.id=d.assetId SET d.equipmentId=CASE WHEN a.level='EQUIPMENT' THEN a.id END,d.itemId=CASE WHEN a.level='SUB_ITEM' THEN a.id END;
INSERT INTO pilot_split_guard SELECT COUNT(*) FROM DamageReport WHERE assetId IS NOT NULL AND COALESCE(itemId,equipmentId,'')<>assetId;
ALTER TABLE FailureEvent ADD COLUMN equipmentId VARCHAR(30) NULL, ADD COLUMN itemId VARCHAR(30) NULL;
UPDATE FailureEvent d JOIN Asset a ON a.id=d.assetId SET d.equipmentId=CASE WHEN a.level='EQUIPMENT' THEN a.id END,d.itemId=CASE WHEN a.level='SUB_ITEM' THEN a.id END;
INSERT INTO pilot_split_guard SELECT COUNT(*) FROM FailureEvent WHERE assetId IS NOT NULL AND COALESCE(itemId,equipmentId,'')<>assetId;
ALTER TABLE MaintenancePlan ADD COLUMN equipmentId VARCHAR(30) NULL, ADD COLUMN itemId VARCHAR(30) NULL;
UPDATE MaintenancePlan d JOIN Asset a ON a.id=d.assetId SET d.equipmentId=CASE WHEN a.level='EQUIPMENT' THEN a.id END,d.itemId=CASE WHEN a.level='SUB_ITEM' THEN a.id END;
INSERT INTO pilot_split_guard SELECT COUNT(*) FROM MaintenancePlan WHERE assetId IS NOT NULL AND COALESCE(itemId,equipmentId,'')<>assetId;
ALTER TABLE Notification ADD COLUMN equipmentId VARCHAR(30) NULL, ADD COLUMN itemId VARCHAR(30) NULL;
UPDATE Notification d JOIN Asset a ON a.id=d.assetId SET d.equipmentId=CASE WHEN a.level='EQUIPMENT' THEN a.id END,d.itemId=CASE WHEN a.level='SUB_ITEM' THEN a.id END;
INSERT INTO pilot_split_guard SELECT COUNT(*) FROM Notification WHERE assetId IS NOT NULL AND COALESCE(itemId,equipmentId,'')<>assetId;
ALTER TABLE WorkOrder ADD COLUMN equipmentId VARCHAR(30) NULL, ADD COLUMN itemId VARCHAR(30) NULL;
UPDATE WorkOrder d JOIN Asset a ON a.id=d.assetId SET d.equipmentId=CASE WHEN a.level='EQUIPMENT' THEN a.id END,d.itemId=CASE WHEN a.level='SUB_ITEM' THEN a.id END;
INSERT INTO pilot_split_guard SELECT COUNT(*) FROM WorkOrder WHERE assetId IS NOT NULL AND COALESCE(itemId,equipmentId,'')<>assetId;
ALTER TABLE `Asset` DROP FOREIGN KEY `Asset_unitId_fkey`;
ALTER TABLE `Asset` DROP FOREIGN KEY `Asset_parentId_fkey`;
ALTER TABLE `AssetOperatingLog` DROP FOREIGN KEY `AssetOperatingLog_assetId_fkey`;
ALTER TABLE `AssetPart` DROP FOREIGN KEY `AssetPart_assetId_fkey`;
ALTER TABLE `AssetPart` DROP FOREIGN KEY `AssetPart_partId_fkey`;
ALTER TABLE `CalibrationRecord` DROP FOREIGN KEY `CalibrationRecord_assetId_fkey`;
ALTER TABLE `DamageReport` DROP FOREIGN KEY `DamageReport_assetId_fkey`;
ALTER TABLE `FailureEvent` DROP FOREIGN KEY `FailureEvent_assetId_fkey`;
ALTER TABLE `MaintenancePlan` DROP FOREIGN KEY `MaintenancePlan_assetId_fkey`;
ALTER TABLE `Notification` DROP FOREIGN KEY `Notification_assetId_fkey`;
ALTER TABLE `WorkOrder` DROP FOREIGN KEY `WorkOrder_assetId_fkey`;
DROP INDEX `CalibrationRecord_assetId_calibratedAt_idx` ON `CalibrationRecord`;
DROP INDEX `DamageReport_assetId_status_reportedAt_idx` ON `DamageReport`;
DROP INDEX `FailureEvent_assetId_failedAt_idx` ON `FailureEvent`;
DROP INDEX `MaintenancePlan_assetId_idx` ON `MaintenancePlan`;
DROP INDEX `Notification_assetId_idx` ON `Notification`;
DROP INDEX `WorkOrder_assetId_createdAt_idx` ON `WorkOrder`;
ALTER TABLE CalibrationRecord DROP COLUMN assetId;
ALTER TABLE DamageReport DROP COLUMN assetId;
ALTER TABLE FailureEvent DROP COLUMN assetId;
ALTER TABLE MaintenancePlan DROP COLUMN assetId;
ALTER TABLE Notification DROP COLUMN assetId;
ALTER TABLE WorkOrder DROP COLUMN assetId;
CREATE INDEX `CalibrationRecord_equipmentId_calibratedAt_idx` ON `CalibrationRecord`(`equipmentId`, `calibratedAt`);
CREATE INDEX `CalibrationRecord_itemId_calibratedAt_idx` ON `CalibrationRecord`(`itemId`, `calibratedAt`);
CREATE INDEX `DamageReport_equipmentId_status_reportedAt_idx` ON `DamageReport`(`equipmentId`, `status`, `reportedAt`);
CREATE INDEX `DamageReport_itemId_status_reportedAt_idx` ON `DamageReport`(`itemId`, `status`, `reportedAt`);
CREATE INDEX `FailureEvent_equipmentId_failedAt_idx` ON `FailureEvent`(`equipmentId`, `failedAt`);
CREATE INDEX `FailureEvent_itemId_failedAt_idx` ON `FailureEvent`(`itemId`, `failedAt`);
CREATE INDEX `MaintenancePlan_equipmentId_idx` ON `MaintenancePlan`(`equipmentId`);
CREATE INDEX `MaintenancePlan_itemId_idx` ON `MaintenancePlan`(`itemId`);
CREATE INDEX `Notification_equipmentId_idx` ON `Notification`(`equipmentId`);
CREATE INDEX `Notification_itemId_idx` ON `Notification`(`itemId`);
CREATE INDEX `WorkOrder_equipmentId_createdAt_idx` ON `WorkOrder`(`equipmentId`, `createdAt`);
CREATE INDEX `WorkOrder_itemId_createdAt_idx` ON `WorkOrder`(`itemId`, `createdAt`);
ALTER TABLE `CalibrationRecord` ADD CONSTRAINT `CalibrationRecord_equipmentId_fkey` FOREIGN KEY (`equipmentId`) REFERENCES `equipment`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `CalibrationRecord` ADD CONSTRAINT `CalibrationRecord_itemId_fkey` FOREIGN KEY (`itemId`) REFERENCES `items`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `DamageReport` ADD CONSTRAINT `DamageReport_equipmentId_fkey` FOREIGN KEY (`equipmentId`) REFERENCES `equipment`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `DamageReport` ADD CONSTRAINT `DamageReport_itemId_fkey` FOREIGN KEY (`itemId`) REFERENCES `items`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `equipment` ADD CONSTRAINT `equipment_categoryId_fkey` FOREIGN KEY (`categoryId`) REFERENCES `category`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `FailureEvent` ADD CONSTRAINT `FailureEvent_equipmentId_fkey` FOREIGN KEY (`equipmentId`) REFERENCES `equipment`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `FailureEvent` ADD CONSTRAINT `FailureEvent_itemId_fkey` FOREIGN KEY (`itemId`) REFERENCES `items`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `items` ADD CONSTRAINT `items_equipmentId_fkey` FOREIGN KEY (`equipmentId`) REFERENCES `equipment`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `MaintenancePlan` ADD CONSTRAINT `MaintenancePlan_equipmentId_fkey` FOREIGN KEY (`equipmentId`) REFERENCES `equipment`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `MaintenancePlan` ADD CONSTRAINT `MaintenancePlan_itemId_fkey` FOREIGN KEY (`itemId`) REFERENCES `items`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `Notification` ADD CONSTRAINT `Notification_equipmentId_fkey` FOREIGN KEY (`equipmentId`) REFERENCES `equipment`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `Notification` ADD CONSTRAINT `Notification_itemId_fkey` FOREIGN KEY (`itemId`) REFERENCES `items`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `operating_log` ADD CONSTRAINT `operating_log_equipmentId_fkey` FOREIGN KEY (`equipmentId`) REFERENCES `equipment`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `operating_log` ADD CONSTRAINT `operating_log_itemId_fkey` FOREIGN KEY (`itemId`) REFERENCES `items`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `part_assignment` ADD CONSTRAINT `part_assignment_equipmentId_fkey` FOREIGN KEY (`equipmentId`) REFERENCES `equipment`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `part_assignment` ADD CONSTRAINT `part_assignment_itemId_fkey` FOREIGN KEY (`itemId`) REFERENCES `items`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `part_assignment` ADD CONSTRAINT `part_assignment_partId_fkey` FOREIGN KEY (`partId`) REFERENCES `Part`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `WorkOrder` ADD CONSTRAINT `WorkOrder_equipmentId_fkey` FOREIGN KEY (`equipmentId`) REFERENCES `equipment`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `WorkOrder` ADD CONSTRAINT `WorkOrder_itemId_fkey` FOREIGN KEY (`itemId`) REFERENCES `items`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
-- Remove old tables only after copies and new foreign keys succeed.
DROP TABLE AssetPart;
DROP TABLE AssetOperatingLog;
DROP TABLE Asset;
DROP TABLE PlantUnit;
DROP TEMPORARY TABLE pilot_split_guard;