-- CreateTable
CREATE TABLE `policy_ocr_batches` (
    `id` VARCHAR(191) NOT NULL,
    `provider` VARCHAR(191) NOT NULL DEFAULT 'GMX',
    `status` ENUM('UPLOADED', 'PROCESSING', 'READY_FOR_REVIEW', 'COMPLETED', 'FAILED') NOT NULL DEFAULT 'UPLOADED',
    `uploadedById` VARCHAR(191) NOT NULL,
    `label` VARCHAR(191) NULL,
    `fileCount` INTEGER NOT NULL DEFAULT 0,
    `error` TEXT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `completedAt` DATETIME(3) NULL,

    INDEX `policy_ocr_batches_status_createdAt_idx`(`status`, `createdAt`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `policy_ocr_documents` (
    `id` VARCHAR(191) NOT NULL,
    `batchId` VARCHAR(191) NOT NULL,
    `pageNumber` INTEGER NOT NULL,
    `storageKey` VARCHAR(191) NOT NULL,
    `status` ENUM('PENDING_OCR', 'OCR_FAILED', 'NEEDS_REVIEW', 'MATCHED', 'CONFIRMED', 'POSTED', 'REJECTED') NOT NULL DEFAULT 'PENDING_OCR',
    `ocrRawText` TEXT NULL,
    `ocrConfidence` DECIMAL(4, 3) NULL,
    `provider` VARCHAR(191) NULL,
    `extractedPolicyNumber` VARCHAR(191) NULL,
    `extractedInsuredName` VARCHAR(191) NULL,
    `extractedAdditionalInsured` VARCHAR(191) NULL,
    `extractedAgentName` VARCHAR(191) NULL,
    `extractedLegalAddress` TEXT NULL,
    `extractedZip` VARCHAR(191) NULL,
    `extractedPolicyFrom` DATETIME(3) NULL,
    `extractedPolicyTo` DATETIME(3) NULL,
    `extractedPolicyDate` DATETIME(3) NULL,
    `extractedCurrency` VARCHAR(191) NULL,
    `extractedNetPremium` DECIMAL(12, 2) NULL,
    `extractedPolicyFee` DECIMAL(12, 2) NULL,
    `extractedBrokerFee` DECIMAL(12, 2) NULL,
    `extractedTotal` DECIMAL(12, 2) NULL,
    `extractedCoveragesJson` JSON NULL,
    `extractedPremiumPayment` VARCHAR(191) NULL,
    `matchedPolicyId` VARCHAR(191) NULL,
    `matchedCustomerId` VARCHAR(191) NULL,
    `matchCandidates` JSON NULL,
    `matchNote` VARCHAR(191) NULL,
    `reviewedById` VARCHAR(191) NULL,
    `reviewedAt` DATETIME(3) NULL,
    `createdPolicyId` VARCHAR(191) NULL,
    `postedTransactionId` VARCHAR(191) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    UNIQUE INDEX `policy_ocr_documents_createdPolicyId_key`(`createdPolicyId`),
    UNIQUE INDEX `policy_ocr_documents_postedTransactionId_key`(`postedTransactionId`),
    INDEX `policy_ocr_documents_status_idx`(`status`),
    INDEX `policy_ocr_documents_matchedCustomerId_idx`(`matchedCustomerId`),
    UNIQUE INDEX `policy_ocr_documents_batchId_pageNumber_key`(`batchId`, `pageNumber`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- AddForeignKey
ALTER TABLE `policy_ocr_batches` ADD CONSTRAINT `policy_ocr_batches_uploadedById_fkey` FOREIGN KEY (`uploadedById`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `policy_ocr_documents` ADD CONSTRAINT `policy_ocr_documents_batchId_fkey` FOREIGN KEY (`batchId`) REFERENCES `policy_ocr_batches`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `policy_ocr_documents` ADD CONSTRAINT `policy_ocr_documents_matchedPolicyId_fkey` FOREIGN KEY (`matchedPolicyId`) REFERENCES `policies`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `policy_ocr_documents` ADD CONSTRAINT `policy_ocr_documents_matchedCustomerId_fkey` FOREIGN KEY (`matchedCustomerId`) REFERENCES `customers`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `policy_ocr_documents` ADD CONSTRAINT `policy_ocr_documents_reviewedById_fkey` FOREIGN KEY (`reviewedById`) REFERENCES `users`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `policy_ocr_documents` ADD CONSTRAINT `policy_ocr_documents_createdPolicyId_fkey` FOREIGN KEY (`createdPolicyId`) REFERENCES `policies`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `policy_ocr_documents` ADD CONSTRAINT `policy_ocr_documents_postedTransactionId_fkey` FOREIGN KEY (`postedTransactionId`) REFERENCES `transactions`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;