CREATE TABLE `ScannerSession` (
  `id` VARCHAR(191) NOT NULL,
  `userId` VARCHAR(191) NOT NULL,
  `categoryId` VARCHAR(191) NOT NULL,
  `folderPath` VARCHAR(191) NOT NULL,
  `customFields` TEXT NOT NULL,
  `status` ENUM('ACTIVE', 'PROCESSING', 'COMPLETED', 'FAILED') NOT NULL DEFAULT 'ACTIVE',
  `startedAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `finishedAt` DATETIME(3) NULL,
  `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt` DATETIME(3) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `ScannerSession_userId_status_idx`(`userId`, `status`),
  INDEX `ScannerSession_folderPath_status_idx`(`folderPath`, `status`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE `ScannerSessionFile` (
  `id` VARCHAR(191) NOT NULL,
  `sessionId` VARCHAR(191) NOT NULL,
  `sharepointItemId` VARCHAR(191) NOT NULL,
  `fileName` VARCHAR(191) NOT NULL,
  `mimeType` VARCHAR(191) NOT NULL,
  `stagedFileName` VARCHAR(191) NOT NULL,
  `extractedData` JSON NULL,
  `status` ENUM('EXTRACTED', 'FAILED', 'SAVED') NOT NULL DEFAULT 'EXTRACTED',
  `errorMessage` TEXT NULL,
  `remoteDeletedAt` DATETIME(3) NULL,
  `documentId` VARCHAR(191) NULL,
  `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt` DATETIME(3) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `ScannerSessionFile_sessionId_sharepointItemId_key`(`sessionId`, `sharepointItemId`),
  INDEX `ScannerSessionFile_sessionId_status_idx`(`sessionId`, `status`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE `ScannerSessionEvent` (
  `id` VARCHAR(191) NOT NULL,
  `sessionId` VARCHAR(191) NOT NULL,
  `userId` VARCHAR(191) NULL,
  `type` VARCHAR(191) NOT NULL,
  `message` TEXT NOT NULL,
  `context` JSON NULL,
  `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  INDEX `ScannerSessionEvent_sessionId_createdAt_idx`(`sessionId`, `createdAt`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE `ScannerSession` ADD CONSTRAINT `ScannerSession_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `User`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `ScannerSession` ADD CONSTRAINT `ScannerSession_categoryId_fkey` FOREIGN KEY (`categoryId`) REFERENCES `Category`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `ScannerSessionFile` ADD CONSTRAINT `ScannerSessionFile_sessionId_fkey` FOREIGN KEY (`sessionId`) REFERENCES `ScannerSession`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `ScannerSessionEvent` ADD CONSTRAINT `ScannerSessionEvent_sessionId_fkey` FOREIGN KEY (`sessionId`) REFERENCES `ScannerSession`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;