-- Run on live MySQL if product delete/update fails with P2021 for history tables.
-- Safe to re-run (IF NOT EXISTS).

CREATE TABLE IF NOT EXISTS `ProductPriceHistory` (
  `id` VARCHAR(191) NOT NULL,
  `tenantId` VARCHAR(191) NOT NULL,
  `productId` VARCHAR(191) NOT NULL,
  `sku` VARCHAR(191) NOT NULL,
  `field` ENUM('sale', 'cost') NOT NULL,
  `kind` ENUM('set', 'increased', 'decreased') NOT NULL,
  `previousPrice` DECIMAL(12, 2) NULL,
  `price` DECIMAL(12, 2) NOT NULL,
  `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  INDEX `ProductPriceHistory_tenantId_productId_createdAt_idx` (`tenantId`, `productId`, `createdAt`),
  CONSTRAINT `ProductPriceHistory_tenantId_fkey`
    FOREIGN KEY (`tenantId`) REFERENCES `Tenant`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `ProductPriceHistory_productId_fkey`
    FOREIGN KEY (`productId`) REFERENCES `Product`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ProductStockValueHistory` (
  `id` VARCHAR(191) NOT NULL,
  `tenantId` VARCHAR(191) NOT NULL,
  `productId` VARCHAR(191) NOT NULL,
  `sku` VARCHAR(191) NOT NULL,
  `reason` ENUM('set', 'price', 'quantity', 'both') NOT NULL,
  `previousPrice` DECIMAL(12, 2) NULL,
  `price` DECIMAL(12, 2) NOT NULL,
  `previousStock` INT NULL,
  `stock` INT NOT NULL,
  `previousValue` DECIMAL(18, 2) NULL,
  `value` DECIMAL(18, 2) NOT NULL,
  `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  INDEX `ProductStockValueHistory_tenantId_productId_createdAt_idx` (`tenantId`, `productId`, `createdAt`),
  CONSTRAINT `ProductStockValueHistory_tenantId_fkey`
    FOREIGN KEY (`tenantId`) REFERENCES `Tenant`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `ProductStockValueHistory_productId_fkey`
    FOREIGN KEY (`productId`) REFERENCES `Product`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;