-- AlterTable
ALTER TABLE `properties` ADD COLUMN `cadastralKey` VARCHAR(191) NULL;

-- AlterTable
ALTER TABLE `property_services` MODIFY `kind` ENUM('WATER', 'ELECTRIC', 'GAS', 'CABLE', 'PROPERTY_TAX', 'FEDERAL_ZONE', 'ALARM', 'TELEPHONE', 'OTHER') NOT NULL;

-- CreateTable
CREATE TABLE `statement_batches` (
    `id` VARCHAR(191) NOT NULL,
    `serviceKind` ENUM('WATER', 'ELECTRIC', 'GAS', 'CABLE', 'PROPERTY_TAX', 'FEDERAL_ZONE', 'ALARM', 'TELEPHONE', 'OTHER') NOT NULL,
    `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 `statement_batches_status_createdAt_idx`(`status`, `createdAt`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `statement_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,
    `extractedAccountRef` VARCHAR(191) NULL,
    `extractedAmount` DECIMAL(12, 2) NULL,
    `extractedPeriod` VARCHAR(191) NULL,
    `extractedDueDate` DATETIME(3) NULL,
    `extractedCadastralKey` VARCHAR(191) NULL,
    `matchedPropertyServiceId` VARCHAR(191) NULL,
    `matchedCustomerId` VARCHAR(191) NULL,
    `matchNote` VARCHAR(191) NULL,
    `reviewedById` VARCHAR(191) NULL,
    `reviewedAt` DATETIME(3) NULL,
    `postedTransactionId` VARCHAR(191) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    UNIQUE INDEX `statement_documents_postedTransactionId_key`(`postedTransactionId`),
    INDEX `statement_documents_status_idx`(`status`),
    INDEX `statement_documents_matchedCustomerId_idx`(`matchedCustomerId`),
    UNIQUE INDEX `statement_documents_batchId_pageNumber_key`(`batchId`, `pageNumber`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateIndex
CREATE INDEX `properties_cadastralKey_idx` ON `properties`(`cadastralKey`);

-- CreateIndex
CREATE INDEX `property_services_kind_accountNumber_idx` ON `property_services`(`kind`, `accountNumber`);

-- CreateIndex
CREATE INDEX `property_services_kind_meterNumber_idx` ON `property_services`(`kind`, `meterNumber`);

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

-- AddForeignKey
ALTER TABLE `statement_documents` ADD CONSTRAINT `statement_documents_batchId_fkey` FOREIGN KEY (`batchId`) REFERENCES `statement_batches`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `statement_documents` ADD CONSTRAINT `statement_documents_matchedPropertyServiceId_fkey` FOREIGN KEY (`matchedPropertyServiceId`) REFERENCES `property_services`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

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

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

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

