-- Approvals queue and rapport requests. Idempotent; run once against the application database.
CREATE TABLE IF NOT EXISTS `ApprovalRequest` (
  `id` VARCHAR(191) NOT NULL,
  `siteId` VARCHAR(191) NOT NULL,
  `entryId` VARCHAR(191) NOT NULL,
  `exhibitorId` VARCHAR(191) NULL,
  `status` VARCHAR(20) NOT NULL DEFAULT 'PENDING',
  `comment` VARCHAR(1000) NULL,
  `submittedById` VARCHAR(191) NOT NULL,
  `submittedAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `decidedById` VARCHAR(191) NULL,
  `decidedAt` DATETIME(3) NULL,
  PRIMARY KEY (`id`),
  INDEX `ApprovalRequest_siteId_status_submittedAt_idx` (`siteId`, `status`, `submittedAt`),
  INDEX `ApprovalRequest_entryId_idx` (`entryId`),
  CONSTRAINT `ApprovalRequest_siteId_fkey` FOREIGN KEY (`siteId`) REFERENCES `Site`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `ApprovalRequest_entryId_fkey` FOREIGN KEY (`entryId`) REFERENCES `ContentEntry`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `RapportRequest` (
  `id` VARCHAR(191) NOT NULL,
  `siteId` VARCHAR(191) NOT NULL,
  `targetEntryId` VARCHAR(191) NULL,
  `targetName` VARCHAR(200) NOT NULL,
  `name` VARCHAR(120) NOT NULL,
  `email` VARCHAR(254) NOT NULL,
  `company` VARCHAR(160) NULL,
  `message` TEXT NOT NULL,
  `status` VARCHAR(20) NOT NULL DEFAULT 'PENDING',
  `ipHash` VARCHAR(64) NULL,
  `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `decidedAt` DATETIME(3) NULL,
  `decidedById` VARCHAR(191) NULL,
  PRIMARY KEY (`id`),
  INDEX `RapportRequest_siteId_status_createdAt_idx` (`siteId`, `status`, `createdAt`),
  INDEX `RapportRequest_targetEntryId_fkey` (`targetEntryId`),
  CONSTRAINT `RapportRequest_siteId_fkey` FOREIGN KEY (`siteId`) REFERENCES `Site`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `RapportRequest_targetEntryId_fkey` FOREIGN KEY (`targetEntryId`) REFERENCES `ContentEntry`(`id`) ON DELETE SET NULL ON UPDATE CASCADE
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;