-- CreateTable
CREATE TABLE `NbUsers` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `hubId` INTEGER NULL DEFAULT 0,
    `fullName` VARCHAR(191) NOT NULL,
    `userName` VARCHAR(191) NOT NULL,
    `userType` ENUM('USER', 'ADMIN', 'HUB') NOT NULL,
    `password` VARCHAR(191) NOT NULL,
    `contactNumber` VARCHAR(191) NOT NULL,
    `fullAddress` VARCHAR(191) NOT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    UNIQUE INDEX `NbUsers_userName_key`(`userName`),
    INDEX `NbUsers_fullName_contactNumber_idx`(`fullName`, `contactNumber`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `NbTransactions` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `userId` INTEGER NOT NULL,
    `bankId` VARCHAR(191) NOT NULL,
    `terminalId` VARCHAR(191) NOT NULL,
    `receiptId` VARCHAR(191) NOT NULL,
    `merchantId` VARCHAR(191) NOT NULL,
    `refId` VARCHAR(191) NULL,
    `prn` VARCHAR(191) NULL,
    `remarks` VARCHAR(191) NULL,
    `cardNumber` VARCHAR(191) NOT NULL,
    `responseCode` VARCHAR(191) NULL,
    `responseMessage` VARCHAR(191) NULL,
    `transactionResponse` JSON NULL,
    `paymentSource` VARCHAR(191) NOT NULL DEFAULT 'CARD',
    `status` ENUM('SUCCESS', 'FAILED', 'PENDING', 'TERMINATED') NOT NULL,
    `isDeleted` BOOLEAN NOT NULL DEFAULT false,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `otherDetails` JSON NULL,

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

-- CreateTable
CREATE TABLE `NbPackages` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `transactionId` INTEGER NOT NULL,
    `amount` VARCHAR(191) NOT NULL,
    `trackingId` VARCHAR(191) NOT NULL,

    INDEX `NbPackages_transactionId_idx`(`transactionId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `NbBanks` (
    `bankId` VARCHAR(191) NOT NULL,
    `address` VARCHAR(191) NOT NULL,
    `bankCode` VARCHAR(191) NOT NULL,
    `bankName` VARCHAR(191) NOT NULL,

    UNIQUE INDEX `NbBanks_bankId_key`(`bankId`),
    INDEX `NbBanks_bankId_idx`(`bankId`),
    PRIMARY KEY (`bankId`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- AddForeignKey
ALTER TABLE `NbUsers` ADD CONSTRAINT `NbUsers_hubId_fkey` FOREIGN KEY (`hubId`) REFERENCES `NbUsers`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `NbTransactions` ADD CONSTRAINT `NbTransactions_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `NbUsers`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `NbTransactions` ADD CONSTRAINT `NbTransactions_bankId_fkey` FOREIGN KEY (`bankId`) REFERENCES `NbBanks`(`bankId`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `NbPackages` ADD CONSTRAINT `NbPackages_transactionId_fkey` FOREIGN KEY (`transactionId`) REFERENCES `NbTransactions`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
