-- Run prisma:preflight:pilot-plant and quiesce affected writers before deployment.
-- Keep original migration and business history intact. Backfill before NOT NULL.
ALTER TABLE `Asset`
  ADD COLUMN `nameScopeId` VARCHAR(30) NULL,
  ADD COLUMN `photoKey` VARCHAR(255) NULL,
  ADD COLUMN `version` INTEGER UNSIGNED NOT NULL DEFAULT 0;

UPDATE `Asset` SET `nameScopeId` = CASE
  WHEN `level` = 'EQUIPMENT' THEN `unitId`
  WHEN `level` = 'SUB_ITEM' THEN `parentId`
END;

ALTER TABLE `Asset` MODIFY `nameScopeId` VARCHAR(30) NOT NULL;
CREATE UNIQUE INDEX `Asset_nameScopeId_name_key` ON `Asset` (`nameScopeId`, `name`);

ALTER TABLE `PlantUnit`
  ADD COLUMN `photoKey` VARCHAR(255) NULL,
  ADD COLUMN `version` INTEGER UNSIGNED NOT NULL DEFAULT 0;

ALTER TABLE `DamageReport`
  ADD COLUMN `reviewedById` VARCHAR(30) NULL,
  ADD COLUMN `reviewedAt` DATETIME(3) NULL,
  ADD COLUMN `reviewNote` VARCHAR(1000) NULL,
  ADD COLUMN `acceptanceInputHash` CHAR(64) NULL,
  MODIFY `status` ENUM('OPEN','ACCEPTED','IN_REPAIR','RESOLVED','DISMISSED') NOT NULL DEFAULT 'OPEN';

CREATE INDEX `DamageReport_reviewedById_idx` ON `DamageReport` (`reviewedById`);
CREATE INDEX `DamageReport_status_reportedAt_id_idx` ON `DamageReport` (`status`, `reportedAt`, `id`);
ALTER TABLE `DamageReport` ADD CONSTRAINT `DamageReport_reviewedById_fkey`
  FOREIGN KEY (`reviewedById`) REFERENCES `User` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- Create the unique index before removing the old FK-supporting nonunique index.
CREATE UNIQUE INDEX `WorkOrder_damageReportId_key` ON `WorkOrder` (`damageReportId`);
DROP INDEX `WorkOrder_damageReportId_idx` ON `WorkOrder`;