-- CreateTable
CREATE TABLE `company` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `uuid` VARCHAR(191) NOT NULL,
    `externalCompanyId` INTEGER NULL,
    `companyName` VARCHAR(191) NOT NULL,
    `contactNumber` VARCHAR(191) NULL,
    `address` VARCHAR(500) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `company_uuid_key`(`uuid`),
    UNIQUE INDEX `company_externalCompanyId_key`(`externalCompanyId`),
    INDEX `company_externalCompanyId_idx`(`externalCompanyId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `role` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `uuid` VARCHAR(191) NOT NULL,
    `name` VARCHAR(191) NOT NULL,
    `displayName` VARCHAR(191) NOT NULL,
    `isSystem` BOOLEAN NOT NULL DEFAULT true,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `role_uuid_key`(`uuid`),
    UNIQUE INDEX `role_name_key`(`name`),
    INDEX `role_name_idx`(`name`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `user` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `uuid` VARCHAR(191) NOT NULL,
    `externalUserId` INTEGER NULL,
    `fullName` VARCHAR(191) NOT NULL,
    `email` VARCHAR(191) NOT NULL,
    `contactNumber` VARCHAR(191) NULL,
    `passwordHash` VARCHAR(191) NULL,
    `userType` VARCHAR(191) NOT NULL DEFAULT 'LOCAL',
    `isActive` BOOLEAN NOT NULL DEFAULT true,
    `companyId` INTEGER NULL,
    `roleId` INTEGER NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `user_uuid_key`(`uuid`),
    UNIQUE INDEX `user_externalUserId_key`(`externalUserId`),
    UNIQUE INDEX `user_email_key`(`email`),
    INDEX `user_externalUserId_idx`(`externalUserId`),
    INDEX `user_email_idx`(`email`),
    INDEX `user_companyId_idx`(`companyId`),
    INDEX `user_roleId_idx`(`roleId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `refreshtoken` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `token` VARCHAR(512) NOT NULL,
    `userId` INTEGER NOT NULL,
    `expiresAt` DATETIME(3) NOT NULL,
    `isRevoked` BOOLEAN NOT NULL DEFAULT false,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    UNIQUE INDEX `refreshtoken_token_key`(`token`),
    INDEX `refreshtoken_userId_idx`(`userId`),
    INDEX `refreshtoken_token_idx`(`token`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `billpayment` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `uuid` VARCHAR(191) NOT NULL,
    `billingAccountId` INTEGER NOT NULL,
    `amount` DOUBLE NOT NULL,
    `paymentMethod` VARCHAR(191) NOT NULL,
    `status` VARCHAR(191) NOT NULL DEFAULT 'PENDING',
    `orderId` VARCHAR(191) NOT NULL,
    `orderReference` VARCHAR(191) NULL,
    `errorCode` VARCHAR(191) NULL,
    `failureReason` VARCHAR(500) NULL,
    `paidUserId` INTEGER NOT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    UNIQUE INDEX `billpayment_uuid_key`(`uuid`),
    UNIQUE INDEX `billpayment_orderId_key`(`orderId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `billaccount` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `uuid` VARCHAR(191) NOT NULL,
    `accountNumber` VARCHAR(191) NOT NULL,
    `accountName` VARCHAR(191) NOT NULL,
    `outstandingBalance` DOUBLE NOT NULL,
    `smsCostTransactional` DOUBLE NULL,
    `smsCostPromotional` DOUBLE NULL,
    `accountStatus` VARCHAR(191) NOT NULL DEFAULT 'INACTIVE',
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,
    `userId` INTEGER NOT NULL,

    UNIQUE INDEX `billaccount_uuid_key`(`uuid`),
    UNIQUE INDEX `billaccount_accountNumber_key`(`accountNumber`),
    INDEX `billaccount_userId_idx`(`userId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `lowbalancealert` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `uuid` VARCHAR(191) NOT NULL,
    `billingAccountId` INTEGER NOT NULL,
    `userId` INTEGER NOT NULL,
    `thresholdLevel` INTEGER NOT NULL DEFAULT 500,
    `isActive` BOOLEAN NOT NULL DEFAULT true,
    `balanceAtAlert` DOUBLE NOT NULL,
    `sentTo` VARCHAR(191) NOT NULL,
    `maskUsed` VARCHAR(191) NOT NULL,
    `serverRef` INTEGER NULL,
    `status` VARCHAR(191) NOT NULL DEFAULT 'SENT',
    `failureReason` VARCHAR(500) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    UNIQUE INDEX `lowbalancealert_uuid_key`(`uuid`),
    UNIQUE INDEX `lowbalancealert_serverRef_key`(`serverRef`),
    INDEX `lowbalancealert_billingAccountId_thresholdLevel_isActive_idx`(`billingAccountId`, `thresholdLevel`, `isActive`),
    INDEX `lowbalancealert_billingAccountId_idx`(`billingAccountId`),
    INDEX `lowbalancealert_userId_idx`(`userId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `department` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `uuid` VARCHAR(191) NOT NULL,
    `userId` INTEGER NOT NULL,
    `name` VARCHAR(191) NOT NULL,
    `is_enabled` BOOLEAN NOT NULL DEFAULT false,
    `callBackUrl` VARCHAR(191) NULL,
    `Signature` VARCHAR(191) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,
    `deletedAt` DATETIME(3) NULL,

    UNIQUE INDEX `department_uuid_key`(`uuid`),
    INDEX `department_userId_idx`(`userId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `sms_template` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `uuid` VARCHAR(191) NOT NULL,
    `deptId` INTEGER NOT NULL,
    `name` VARCHAR(191) NOT NULL,
    `text_content` VARCHAR(900) NOT NULL,
    `smsType` VARCHAR(191) NOT NULL DEFAULT 'STANDARD',
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,
    `deletedAt` DATETIME(3) NULL,

    UNIQUE INDEX `sms_template_uuid_key`(`uuid`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `sendermask` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `uuid` VARCHAR(191) NOT NULL,
    `maskName` VARCHAR(191) NOT NULL,
    `purpose` VARCHAR(255) NOT NULL,
    `is_default` BOOLEAN NOT NULL DEFAULT false,
    `status` VARCHAR(191) NOT NULL DEFAULT 'PENDING',
    `maskType` VARCHAR(191) NULL,
    `isExternal` BOOLEAN NOT NULL DEFAULT false,
    `emailSent` BOOLEAN NOT NULL DEFAULT false,
    `emailSentAt` DATETIME(3) NULL,
    `createdByUserId` INTEGER NOT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `sendermask_uuid_key`(`uuid`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `departmentsendermask` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `uuid` VARCHAR(191) NOT NULL,
    `senderMaskId` INTEGER NOT NULL,
    `deptId` INTEGER NOT NULL,
    `assignedAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `deletedAt` DATETIME(3) NULL,

    UNIQUE INDEX `departmentsendermask_uuid_key`(`uuid`),
    INDEX `departmentsendermask_deptId_idx`(`deptId`),
    INDEX `departmentsendermask_senderMaskId_idx`(`senderMaskId`),
    UNIQUE INDEX `departmentsendermask_senderMaskId_deptId_key`(`senderMaskId`, `deptId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `campaign` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `uuid` VARCHAR(191) NOT NULL,
    `name` VARCHAR(191) NOT NULL,
    `status` VARCHAR(191) NOT NULL DEFAULT 'SCHEDULED',
    `server_ref` INTEGER NULL,
    `smsValidityPeriod` INTEGER NULL,
    `customMessage` VARCHAR(900) NULL,
    `scheduledDate` DATETIME(3) NOT NULL,
    `notificationNumber` VARCHAR(191) NULL,
    `createdByUserId` INTEGER NOT NULL,
    `billingAccountId` INTEGER NOT NULL,
    `senderMaskId` INTEGER NOT NULL,
    `mobileNumberListId` BIGINT NULL,
    `customNumberList` JSON NULL,
    `customBlacklist` JSON NULL,
    `templateId` INTEGER NULL,
    `smsType` VARCHAR(191) NOT NULL DEFAULT 'STANDARD',
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,
    `deletedAt` DATETIME(3) NULL,

    UNIQUE INDEX `campaign_uuid_key`(`uuid`),
    UNIQUE INDEX `campaign_server_ref_key`(`server_ref`),
    INDEX `campaign_createdByUserId_idx`(`createdByUserId`),
    INDEX `campaign_status_idx`(`status`),
    INDEX `campaign_scheduledDate_idx`(`scheduledDate`),
    INDEX `campaign_billingAccountId_idx`(`billingAccountId`),
    INDEX `campaign_senderMaskId_idx`(`senderMaskId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `mobilenumberlist` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `uuid` VARCHAR(191) NOT NULL,
    `listName` VARCHAR(191) NOT NULL,
    `listType` VARCHAR(191) NOT NULL DEFAULT 'REGULAR',
    `createdByUserId` INTEGER NOT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,
    `deletedAt` DATETIME(3) NULL,

    UNIQUE INDEX `mobilenumberlist_uuid_key`(`uuid`),
    INDEX `mobilenumberlist_createdByUserId_idx`(`createdByUserId`),
    INDEX `mobilenumberlist_listType_idx`(`listType`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `mobilenumber` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `uuid` VARCHAR(191) NOT NULL,
    `phoneNumber` VARCHAR(191) NOT NULL,
    `type` VARCHAR(191) NOT NULL,
    `is_blocked` BOOLEAN NOT NULL DEFAULT false,
    `numberListId` BIGINT NULL,
    `deletedAt` DATETIME(3) NULL,

    UNIQUE INDEX `mobilenumber_uuid_key`(`uuid`),
    INDEX `mobilenumber_phoneNumber_idx`(`phoneNumber`),
    INDEX `mobilenumber_is_blocked_idx`(`is_blocked`),
    INDEX `mobilenumber_numberListId_idx`(`numberListId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `sms_report` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `uuid` VARCHAR(191) NOT NULL,
    `server_ref` INTEGER NOT NULL,
    `deliveryType` INTEGER NOT NULL,
    `number` VARCHAR(191) NOT NULL,
    `maskName` VARCHAR(191) NOT NULL,
    `messagePart` INTEGER NOT NULL,
    `chargedAmount` DOUBLE NOT NULL DEFAULT 0,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `sms_report_uuid_key`(`uuid`),
    INDEX `sms_report_server_ref_idx`(`server_ref`),
    INDEX `sms_report_deliveryType_idx`(`deliveryType`),
    UNIQUE INDEX `sms_report_server_ref_number_messagePart_key`(`server_ref`, `number`, `messagePart`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `auditlog` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `entityName` VARCHAR(191) NOT NULL,
    `entityId` VARCHAR(191) NOT NULL,
    `action` VARCHAR(191) NOT NULL,
    `oldValue` JSON NULL,
    `newValue` JSON NULL,
    `changedFields` JSON NULL,
    `performedByUserId` INTEGER NOT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    INDEX `auditlog_entityName_entityId_idx`(`entityName`, `entityId`),
    INDEX `auditlog_performedByUserId_idx`(`performedByUserId`),
    INDEX `auditlog_createdAt_idx`(`createdAt`),
    INDEX `auditlog_action_idx`(`action`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- AddForeignKey
ALTER TABLE `user` ADD CONSTRAINT `user_companyId_fkey` FOREIGN KEY (`companyId`) REFERENCES `company`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `user` ADD CONSTRAINT `user_roleId_fkey` FOREIGN KEY (`roleId`) REFERENCES `role`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `refreshtoken` ADD CONSTRAINT `refreshtoken_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `user`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `billpayment` ADD CONSTRAINT `billpayment_billingAccountId_fkey` FOREIGN KEY (`billingAccountId`) REFERENCES `billaccount`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `billaccount` ADD CONSTRAINT `billaccount_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `user`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `lowbalancealert` ADD CONSTRAINT `lowbalancealert_billingAccountId_fkey` FOREIGN KEY (`billingAccountId`) REFERENCES `billaccount`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `sms_template` ADD CONSTRAINT `sms_template_deptId_fkey` FOREIGN KEY (`deptId`) REFERENCES `department`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `departmentsendermask` ADD CONSTRAINT `departmentsendermask_senderMaskId_fkey` FOREIGN KEY (`senderMaskId`) REFERENCES `sendermask`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `departmentsendermask` ADD CONSTRAINT `departmentsendermask_deptId_fkey` FOREIGN KEY (`deptId`) REFERENCES `department`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `campaign` ADD CONSTRAINT `campaign_billingAccountId_fkey` FOREIGN KEY (`billingAccountId`) REFERENCES `billaccount`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `campaign` ADD CONSTRAINT `campaign_senderMaskId_fkey` FOREIGN KEY (`senderMaskId`) REFERENCES `sendermask`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `campaign` ADD CONSTRAINT `campaign_mobileNumberListId_fkey` FOREIGN KEY (`mobileNumberListId`) REFERENCES `mobilenumberlist`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `campaign` ADD CONSTRAINT `campaign_templateId_fkey` FOREIGN KEY (`templateId`) REFERENCES `sms_template`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `mobilenumber` ADD CONSTRAINT `mobilenumber_numberListId_fkey` FOREIGN KEY (`numberListId`) REFERENCES `mobilenumberlist`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `sms_report` ADD CONSTRAINT `sms_report_server_ref_fkey` FOREIGN KEY (`server_ref`) REFERENCES `campaign`(`server_ref`) ON DELETE RESTRICT ON UPDATE CASCADE;