-- Commio (thinQ) CDR local cache — avoids hitting the live Commio API
-- on every billing request; scheduled sync + one-time backfill populate this.
CREATE TABLE `CommioCdr` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `commioCallId` VARCHAR(191) NOT NULL,
    `fromDid` VARCHAR(191) NOT NULL,
    `toDid` VARCHAR(191) NOT NULL,
    `cdrTime` DATETIME(3) NOT NULL,
    `retail` DOUBLE NULL,
    `billable` INTEGER NULL,
    `totalRetail` DOUBLE NOT NULL,
    `sipCode` VARCHAR(191) NULL,
    `sipReason` VARCHAR(191) NULL,
    `raw` JSON NOT NULL,
    `ingestedAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `CommioCdr_commioCallId_key`(`commioCallId`),
    INDEX `CommioCdr_toDid_cdrTime_idx`(`toDid`, `cdrTime`),
    INDEX `CommioCdr_fromDid_idx`(`fromDid`),
    INDEX `CommioCdr_cdrTime_idx`(`cdrTime`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;