355 lines
14 KiB
SQL
355 lines
14 KiB
SQL
-- CreateTable
|
|
CREATE TABLE `User` (
|
|
`id` INTEGER NOT NULL AUTO_INCREMENT,
|
|
`email` VARCHAR(191) NOT NULL,
|
|
`password` VARCHAR(191) NOT NULL,
|
|
`firstName` VARCHAR(191) NOT NULL,
|
|
`lastName` VARCHAR(191) NOT NULL,
|
|
`isActive` BOOLEAN NOT NULL DEFAULT true,
|
|
`customerId` INTEGER NULL,
|
|
`createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
`updatedAt` DATETIME(3) NOT NULL,
|
|
|
|
UNIQUE INDEX `User_email_key`(`email`),
|
|
UNIQUE INDEX `User_customerId_key`(`customerId`),
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `Role` (
|
|
`id` INTEGER NOT NULL AUTO_INCREMENT,
|
|
`name` VARCHAR(191) NOT NULL,
|
|
`description` VARCHAR(191) NULL,
|
|
`createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
`updatedAt` DATETIME(3) NOT NULL,
|
|
|
|
UNIQUE INDEX `Role_name_key`(`name`),
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `Permission` (
|
|
`id` INTEGER NOT NULL AUTO_INCREMENT,
|
|
`resource` VARCHAR(191) NOT NULL,
|
|
`action` VARCHAR(191) NOT NULL,
|
|
|
|
UNIQUE INDEX `Permission_resource_action_key`(`resource`, `action`),
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `RolePermission` (
|
|
`roleId` INTEGER NOT NULL,
|
|
`permissionId` INTEGER NOT NULL,
|
|
|
|
PRIMARY KEY (`roleId`, `permissionId`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `UserRole` (
|
|
`userId` INTEGER NOT NULL,
|
|
`roleId` INTEGER NOT NULL,
|
|
|
|
PRIMARY KEY (`userId`, `roleId`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `Customer` (
|
|
`id` INTEGER NOT NULL AUTO_INCREMENT,
|
|
`customerNumber` VARCHAR(191) NOT NULL,
|
|
`type` ENUM('PRIVATE', 'BUSINESS') NOT NULL DEFAULT 'PRIVATE',
|
|
`salutation` VARCHAR(191) NULL,
|
|
`firstName` VARCHAR(191) NOT NULL,
|
|
`lastName` VARCHAR(191) NOT NULL,
|
|
`companyName` VARCHAR(191) NULL,
|
|
`email` VARCHAR(191) NULL,
|
|
`phone` VARCHAR(191) NULL,
|
|
`mobile` VARCHAR(191) NULL,
|
|
`taxNumber` VARCHAR(191) NULL,
|
|
`businessRegistration` TEXT NULL,
|
|
`commercialRegister` VARCHAR(191) NULL,
|
|
`notes` TEXT NULL,
|
|
`createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
`updatedAt` DATETIME(3) NOT NULL,
|
|
|
|
UNIQUE INDEX `Customer_customerNumber_key`(`customerNumber`),
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `Address` (
|
|
`id` INTEGER NOT NULL AUTO_INCREMENT,
|
|
`customerId` INTEGER NOT NULL,
|
|
`type` ENUM('DELIVERY_RESIDENCE', 'BILLING') NOT NULL DEFAULT 'DELIVERY_RESIDENCE',
|
|
`street` VARCHAR(191) NOT NULL,
|
|
`houseNumber` VARCHAR(191) NOT NULL,
|
|
`postalCode` VARCHAR(191) NOT NULL,
|
|
`city` VARCHAR(191) NOT NULL,
|
|
`country` VARCHAR(191) NOT NULL DEFAULT 'Deutschland',
|
|
`isDefault` BOOLEAN NOT NULL DEFAULT false,
|
|
`createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
`updatedAt` DATETIME(3) NOT NULL,
|
|
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `BankCard` (
|
|
`id` INTEGER NOT NULL AUTO_INCREMENT,
|
|
`customerId` INTEGER NOT NULL,
|
|
`accountHolder` VARCHAR(191) NOT NULL,
|
|
`iban` VARCHAR(191) NOT NULL,
|
|
`bic` VARCHAR(191) NULL,
|
|
`bankName` VARCHAR(191) NULL,
|
|
`expiryDate` DATETIME(3) NULL,
|
|
`isActive` BOOLEAN NOT NULL DEFAULT true,
|
|
`createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
`updatedAt` DATETIME(3) NOT NULL,
|
|
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `IdentityDocument` (
|
|
`id` INTEGER NOT NULL AUTO_INCREMENT,
|
|
`customerId` INTEGER NOT NULL,
|
|
`type` ENUM('ID_CARD', 'PASSPORT', 'DRIVERS_LICENSE', 'OTHER') NOT NULL DEFAULT 'ID_CARD',
|
|
`documentNumber` VARCHAR(191) NOT NULL,
|
|
`issuingAuthority` VARCHAR(191) NULL,
|
|
`issueDate` DATETIME(3) NULL,
|
|
`expiryDate` DATETIME(3) NULL,
|
|
`isActive` BOOLEAN NOT NULL DEFAULT true,
|
|
`createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
`updatedAt` DATETIME(3) NOT NULL,
|
|
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `Meter` (
|
|
`id` INTEGER NOT NULL AUTO_INCREMENT,
|
|
`customerId` INTEGER NOT NULL,
|
|
`meterNumber` VARCHAR(191) NOT NULL,
|
|
`type` ENUM('ELECTRICITY', 'GAS') NOT NULL,
|
|
`location` VARCHAR(191) NULL,
|
|
`isActive` BOOLEAN NOT NULL DEFAULT true,
|
|
`createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
`updatedAt` DATETIME(3) NOT NULL,
|
|
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `MeterReading` (
|
|
`id` INTEGER NOT NULL AUTO_INCREMENT,
|
|
`meterId` INTEGER NOT NULL,
|
|
`readingDate` DATETIME(3) NOT NULL,
|
|
`value` DOUBLE NOT NULL,
|
|
`unit` VARCHAR(191) NOT NULL DEFAULT 'kWh',
|
|
`notes` VARCHAR(191) NULL,
|
|
`createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `SalesPlatform` (
|
|
`id` INTEGER NOT NULL AUTO_INCREMENT,
|
|
`name` VARCHAR(191) NOT NULL,
|
|
`contactInfo` TEXT NULL,
|
|
`isActive` BOOLEAN NOT NULL DEFAULT true,
|
|
`createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
`updatedAt` DATETIME(3) NOT NULL,
|
|
|
|
UNIQUE INDEX `SalesPlatform_name_key`(`name`),
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `Contract` (
|
|
`id` INTEGER NOT NULL AUTO_INCREMENT,
|
|
`contractNumber` VARCHAR(191) NOT NULL,
|
|
`customerId` INTEGER NOT NULL,
|
|
`type` ENUM('ELECTRICITY', 'GAS', 'DSL', 'FIBER', 'MOBILE', 'TV', 'CAR_INSURANCE') NOT NULL,
|
|
`status` ENUM('DRAFT', 'PENDING', 'ACTIVE', 'CANCELLED', 'EXPIRED') NOT NULL DEFAULT 'DRAFT',
|
|
`addressId` INTEGER NULL,
|
|
`bankCardId` INTEGER NULL,
|
|
`identityDocumentId` INTEGER NULL,
|
|
`salesPlatformId` INTEGER NULL,
|
|
`previousContractId` INTEGER NULL,
|
|
`providerName` VARCHAR(191) NULL,
|
|
`tariffName` VARCHAR(191) NULL,
|
|
`customerNumberAtProvider` VARCHAR(191) NULL,
|
|
`startDate` DATETIME(3) NULL,
|
|
`endDate` DATETIME(3) NULL,
|
|
`cancellationPeriod` INTEGER NULL,
|
|
`commission` DOUBLE NULL,
|
|
`portalUsername` VARCHAR(191) NULL,
|
|
`portalPasswordEncrypted` VARCHAR(191) NULL,
|
|
`notes` TEXT NULL,
|
|
`createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
`updatedAt` DATETIME(3) NOT NULL,
|
|
|
|
UNIQUE INDEX `Contract_contractNumber_key`(`contractNumber`),
|
|
UNIQUE INDEX `Contract_previousContractId_key`(`previousContractId`),
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `EnergyContractDetails` (
|
|
`id` INTEGER NOT NULL AUTO_INCREMENT,
|
|
`contractId` INTEGER NOT NULL,
|
|
`meterId` INTEGER NULL,
|
|
`annualConsumption` DOUBLE NULL,
|
|
`basePrice` DOUBLE NULL,
|
|
`unitPrice` DOUBLE NULL,
|
|
`bonus` DOUBLE NULL,
|
|
`previousProviderName` VARCHAR(191) NULL,
|
|
`previousCustomerNumber` VARCHAR(191) NULL,
|
|
|
|
UNIQUE INDEX `EnergyContractDetails_contractId_key`(`contractId`),
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `InternetContractDetails` (
|
|
`id` INTEGER NOT NULL AUTO_INCREMENT,
|
|
`contractId` INTEGER NOT NULL,
|
|
`downloadSpeed` INTEGER NULL,
|
|
`uploadSpeed` INTEGER NULL,
|
|
`routerModel` VARCHAR(191) NULL,
|
|
`routerSerialNumber` VARCHAR(191) NULL,
|
|
`installationDate` DATETIME(3) NULL,
|
|
|
|
UNIQUE INDEX `InternetContractDetails_contractId_key`(`contractId`),
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `PhoneNumber` (
|
|
`id` INTEGER NOT NULL AUTO_INCREMENT,
|
|
`internetContractDetailsId` INTEGER NOT NULL,
|
|
`phoneNumber` VARCHAR(191) NOT NULL,
|
|
`isMain` BOOLEAN NOT NULL DEFAULT false,
|
|
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `MobileContractDetails` (
|
|
`id` INTEGER NOT NULL AUTO_INCREMENT,
|
|
`contractId` INTEGER NOT NULL,
|
|
`phoneNumber` VARCHAR(191) NULL,
|
|
`simCardNumber` VARCHAR(191) NULL,
|
|
`dataVolume` DOUBLE NULL,
|
|
`includedMinutes` INTEGER NULL,
|
|
`includedSMS` INTEGER NULL,
|
|
`deviceModel` VARCHAR(191) NULL,
|
|
`deviceImei` VARCHAR(191) NULL,
|
|
|
|
UNIQUE INDEX `MobileContractDetails_contractId_key`(`contractId`),
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `TvContractDetails` (
|
|
`id` INTEGER NOT NULL AUTO_INCREMENT,
|
|
`contractId` INTEGER NOT NULL,
|
|
`receiverModel` VARCHAR(191) NULL,
|
|
`smartcardNumber` VARCHAR(191) NULL,
|
|
`package` VARCHAR(191) NULL,
|
|
|
|
UNIQUE INDEX `TvContractDetails_contractId_key`(`contractId`),
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `CarInsuranceDetails` (
|
|
`id` INTEGER NOT NULL AUTO_INCREMENT,
|
|
`contractId` INTEGER NOT NULL,
|
|
`licensePlate` VARCHAR(191) NULL,
|
|
`hsn` VARCHAR(191) NULL,
|
|
`tsn` VARCHAR(191) NULL,
|
|
`vin` VARCHAR(191) NULL,
|
|
`vehicleType` VARCHAR(191) NULL,
|
|
`firstRegistration` DATETIME(3) NULL,
|
|
`noClaimsClass` VARCHAR(191) NULL,
|
|
`insuranceType` ENUM('LIABILITY', 'PARTIAL', 'FULL') NOT NULL DEFAULT 'LIABILITY',
|
|
`deductiblePartial` DOUBLE NULL,
|
|
`deductibleFull` DOUBLE NULL,
|
|
`policyNumber` VARCHAR(191) NULL,
|
|
`previousInsurer` VARCHAR(191) NULL,
|
|
|
|
UNIQUE INDEX `CarInsuranceDetails_contractId_key`(`contractId`),
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `User` ADD CONSTRAINT `User_customerId_fkey` FOREIGN KEY (`customerId`) REFERENCES `Customer`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `RolePermission` ADD CONSTRAINT `RolePermission_roleId_fkey` FOREIGN KEY (`roleId`) REFERENCES `Role`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `RolePermission` ADD CONSTRAINT `RolePermission_permissionId_fkey` FOREIGN KEY (`permissionId`) REFERENCES `Permission`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `UserRole` ADD CONSTRAINT `UserRole_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `User`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `UserRole` ADD CONSTRAINT `UserRole_roleId_fkey` FOREIGN KEY (`roleId`) REFERENCES `Role`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `Address` ADD CONSTRAINT `Address_customerId_fkey` FOREIGN KEY (`customerId`) REFERENCES `Customer`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `BankCard` ADD CONSTRAINT `BankCard_customerId_fkey` FOREIGN KEY (`customerId`) REFERENCES `Customer`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `IdentityDocument` ADD CONSTRAINT `IdentityDocument_customerId_fkey` FOREIGN KEY (`customerId`) REFERENCES `Customer`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `Meter` ADD CONSTRAINT `Meter_customerId_fkey` FOREIGN KEY (`customerId`) REFERENCES `Customer`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `MeterReading` ADD CONSTRAINT `MeterReading_meterId_fkey` FOREIGN KEY (`meterId`) REFERENCES `Meter`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `Contract` ADD CONSTRAINT `Contract_customerId_fkey` FOREIGN KEY (`customerId`) REFERENCES `Customer`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `Contract` ADD CONSTRAINT `Contract_addressId_fkey` FOREIGN KEY (`addressId`) REFERENCES `Address`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `Contract` ADD CONSTRAINT `Contract_bankCardId_fkey` FOREIGN KEY (`bankCardId`) REFERENCES `BankCard`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `Contract` ADD CONSTRAINT `Contract_identityDocumentId_fkey` FOREIGN KEY (`identityDocumentId`) REFERENCES `IdentityDocument`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `Contract` ADD CONSTRAINT `Contract_salesPlatformId_fkey` FOREIGN KEY (`salesPlatformId`) REFERENCES `SalesPlatform`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `Contract` ADD CONSTRAINT `Contract_previousContractId_fkey` FOREIGN KEY (`previousContractId`) REFERENCES `Contract`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `EnergyContractDetails` ADD CONSTRAINT `EnergyContractDetails_contractId_fkey` FOREIGN KEY (`contractId`) REFERENCES `Contract`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `EnergyContractDetails` ADD CONSTRAINT `EnergyContractDetails_meterId_fkey` FOREIGN KEY (`meterId`) REFERENCES `Meter`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `InternetContractDetails` ADD CONSTRAINT `InternetContractDetails_contractId_fkey` FOREIGN KEY (`contractId`) REFERENCES `Contract`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `PhoneNumber` ADD CONSTRAINT `PhoneNumber_internetContractDetailsId_fkey` FOREIGN KEY (`internetContractDetailsId`) REFERENCES `InternetContractDetails`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `MobileContractDetails` ADD CONSTRAINT `MobileContractDetails_contractId_fkey` FOREIGN KEY (`contractId`) REFERENCES `Contract`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `TvContractDetails` ADD CONSTRAINT `TvContractDetails_contractId_fkey` FOREIGN KEY (`contractId`) REFERENCES `Contract`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `CarInsuranceDetails` ADD CONSTRAINT `CarInsuranceDetails_contractId_fkey` FOREIGN KEY (`contractId`) REFERENCES `Contract`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
|