-- LP System V2 migration 0004_m4_statements: M4: automatic monthly statement runs and deliveries (A8, BR-40).
-- GENERATED by scripts/generate-migration.ts from prisma schema snapshots. Do not edit by hand.
-- Every table states ENGINE, charset and collation explicitly; nothing relies on server or database defaults.
-- Every statement can be re-run safely (IF NOT EXISTS / INSERT IGNORE) so an interrupted migration always resumes.
-- CreateTable
CREATE TABLE IF NOT EXISTS `StatementRun` (
    `id` VARCHAR(191) NOT NULL,
    `period` VARCHAR(7) NOT NULL,
    `status` VARCHAR(16) NOT NULL DEFAULT 'RUNNING',
    `startedAt` DATETIME(3) NOT NULL,
    `completedAt` DATETIME(3) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    UNIQUE INDEX `StatementRun_period_key`(`period`),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- CreateTable
CREATE TABLE IF NOT EXISTS `StatementDelivery` (
    `id` VARCHAR(191) NOT NULL,
    `runId` VARCHAR(191) NULL,
    `customerId` VARCHAR(191) NOT NULL,
    `period` VARCHAR(7) NOT NULL,
    `kind` VARCHAR(16) NOT NULL,
    `dedupeKey` VARCHAR(191) NULL,
    `status` VARCHAR(16) NOT NULL,
    `reason` TEXT NULL,
    `statementId` VARCHAR(191) NULL,
    `emailLogId` VARCHAR(191) NULL,
    `actorId` VARCHAR(191) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `StatementDelivery_dedupeKey_key`(`dedupeKey`),
    INDEX `StatementDelivery_runId_status_idx`(`runId`, `status`),
    INDEX `StatementDelivery_customerId_period_idx`(`customerId`, `period`),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;

-- AddForeignKey
ALTER TABLE `StatementDelivery` ADD CONSTRAINT `StatementDelivery_runId_fkey` FOREIGN KEY IF NOT EXISTS (`runId`) REFERENCES `StatementRun`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `StatementDelivery` ADD CONSTRAINT `StatementDelivery_customerId_fkey` FOREIGN KEY IF NOT EXISTS (`customerId`) REFERENCES `Customer`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `StatementDelivery` ADD CONSTRAINT `StatementDelivery_statementId_fkey` FOREIGN KEY IF NOT EXISTS (`statementId`) REFERENCES `StatementOfAccount`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

INSERT INTO `SystemMeta` (`key`, `value`, `updatedAt`) VALUES ('schema_version', '4', CURRENT_TIMESTAMP(3)) ON DUPLICATE KEY UPDATE `value` = '4', `updatedAt` = CURRENT_TIMESTAMP(3);