-- CreateTable
CREATE TABLE `ProductImage` (
    `id` VARCHAR(30) NOT NULL,
    `businessId` VARCHAR(30) NOT NULL,
    `productId` VARCHAR(30) NOT NULL,
    `url` VARCHAR(500) NOT NULL,
    `sortOrder` INTEGER NOT NULL DEFAULT 0,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    INDEX `ProductImage_productId_sortOrder_idx`(`productId`, `sortOrder`),
    INDEX `ProductImage_businessId_productId_idx`(`businessId`, `productId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- AddForeignKey
ALTER TABLE `ProductImage` ADD CONSTRAINT `ProductImage_productId_fkey` FOREIGN KEY (`productId`) REFERENCES `Product`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- Backfill cover images into gallery when present
INSERT INTO `ProductImage` (`id`, `businessId`, `productId`, `url`, `sortOrder`, `createdAt`)
SELECT LEFT(REPLACE(UUID(), '-', ''), 30), `businessId`, `id`, `imageUrl`, 0, NOW(3)
FROM `Product`
WHERE `imageUrl` IS NOT NULL AND `imageUrl` <> '';