-- CreateTable
CREATE TABLE `Users` (
    `user_id` VARCHAR(191) NOT NULL,
    `firstname` VARCHAR(191) NOT NULL,
    `lastname` VARCHAR(191) NOT NULL,
    `othername` VARCHAR(191) NULL,
    `email` VARCHAR(191) NOT NULL,
    `phoneNumber` VARCHAR(191) NOT NULL,
    `alternatePhoneNumber` VARCHAR(191) NOT NULL,
    `sex` ENUM('MALE', 'FEMALE', 'OTHERS') NOT NULL,
    `streetNumber` VARCHAR(191) NOT NULL,
    `streetName` VARCHAR(191) NOT NULL,
    `verification_status` ENUM('PENDING', 'VERIFIED', 'UNVERIFIED', 'REJECTED') NOT NULL DEFAULT 'UNVERIFIED',
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,
    `passport` VARCHAR(191) NOT NULL,
    `user_type_id` VARCHAR(191) NOT NULL,

    UNIQUE INDEX `Users_email_key`(`email`),
    PRIMARY KEY (`user_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `Publications` (
    `publication_id` VARCHAR(191) NOT NULL,
    `link` VARCHAR(191) NOT NULL,
    `details` VARCHAR(191) NOT NULL,
    `user_publication_id` VARCHAR(191) NOT NULL,

    PRIMARY KEY (`publication_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `Educational_Qualifications` (
    `edu_qualification_id` VARCHAR(191) NOT NULL,
    `name` VARCHAR(191) NOT NULL,
    `eud_qualification_image` VARCHAR(191) NOT NULL,
    `edu_qualification_user_id` VARCHAR(191) NOT NULL,

    PRIMARY KEY (`edu_qualification_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `Professional_Qualifications` (
    `pro_qualification_id` VARCHAR(191) NOT NULL,
    `name` VARCHAR(191) NOT NULL,
    `pro_qualification_image` VARCHAR(191) NOT NULL,
    `pro_qualification_user_id` VARCHAR(191) NOT NULL,

    PRIMARY KEY (`pro_qualification_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `Work_Experience` (
    `work_experience_id` VARCHAR(191) NOT NULL,
    `startDate` DATETIME(3) NOT NULL,
    `endDate` DATETIME(3) NOT NULL,
    `workAddress` VARCHAR(191) NOT NULL,
    `jobTitle` VARCHAR(191) NOT NULL,
    `work_experience_user_id` VARCHAR(191) NOT NULL,

    PRIMARY KEY (`work_experience_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `Lincenses` (
    `lincense_id` VARCHAR(191) NOT NULL,
    `name` VARCHAR(191) NOT NULL,
    `image` VARCHAR(191) NULL,
    `lincense_number` VARCHAR(191) NOT NULL,
    `expiryDate` DATETIME(3) NOT NULL,
    `user_id` VARCHAR(191) NOT NULL,

    PRIMARY KEY (`lincense_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `Lincense_Juridiction` (
    `lincense_jurisdiction_id` VARCHAR(191) NOT NULL,
    `lincense_id` VARCHAR(191) NOT NULL,
    `country_id` VARCHAR(191) NOT NULL,

    PRIMARY KEY (`lincense_jurisdiction_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `JobRecommendations` (
    `job_recommendation_id` VARCHAR(191) NOT NULL,
    `recommender` VARCHAR(191) NOT NULL,
    `recommendation_statement` VARCHAR(191) NULL,
    `recommendation_letter` VARCHAR(191) NULL,
    `user_id` VARCHAR(191) NOT NULL,

    PRIMARY KEY (`job_recommendation_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `Interests` (
    `interns_interest_id` VARCHAR(191) NOT NULL,
    `name` VARCHAR(191) NOT NULL,

    PRIMARY KEY (`interns_interest_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `Intern_Interests` (
    `intern_interest_id` VARCHAR(191) NOT NULL,
    `interest_id` VARCHAR(191) NOT NULL,
    `user_id` VARCHAR(191) NOT NULL,

    PRIMARY KEY (`intern_interest_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `Installer_Types` (
    `installer_type_id` VARCHAR(191) NOT NULL,
    `name` VARCHAR(191) NOT NULL,

    PRIMARY KEY (`installer_type_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `User_Installer_Types` (
    `user_installer_type_id` VARCHAR(191) NOT NULL,
    `user_id` VARCHAR(191) NOT NULL,
    `interests_id` VARCHAR(191) NOT NULL,

    PRIMARY KEY (`user_installer_type_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `User_Building_Details` (
    `user_building_details_id` VARCHAR(191) NOT NULL,
    `postalCode` VARCHAR(191) NOT NULL,
    `streetAddress` VARCHAR(191) NOT NULL,
    `city` VARCHAR(191) NOT NULL,
    `user_id` VARCHAR(191) NOT NULL,
    `building_type_id` VARCHAR(191) NOT NULL,
    `building_sub_type_id` VARCHAR(191) NOT NULL,
    `user_commodity_id` VARCHAR(191) NOT NULL,
    `user_building_utility_id` VARCHAR(191) NOT NULL,

    PRIMARY KEY (`user_building_details_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `User_Building_Commodities` (
    `user_building_commodities_id` VARCHAR(191) NOT NULL,
    `commodity_id` VARCHAR(191) NOT NULL,
    `user_building_details_id` VARCHAR(191) NOT NULL,

    PRIMARY KEY (`user_building_commodities_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `Building_Types` (
    `building_type_id` VARCHAR(191) NOT NULL,
    `name` VARCHAR(191) NOT NULL,

    PRIMARY KEY (`building_type_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `Building_Sub_Types` (
    `building_sub_type_id` VARCHAR(191) NOT NULL,
    `name` VARCHAR(191) NOT NULL,

    PRIMARY KEY (`building_sub_type_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `User_Building_Utility` (
    `user_building_utility_id` VARCHAR(191) NOT NULL,
    `utility_name` VARCHAR(191) NOT NULL,
    `streetAddress` VARCHAR(191) NOT NULL,
    `accountNumber` VARCHAR(191) NOT NULL,
    `unit` VARCHAR(191) NOT NULL,
    `city` VARCHAR(191) NOT NULL,
    `postalCode` VARCHAR(191) NOT NULL,
    `numberOfOccupants` VARCHAR(191) NOT NULL,
    `utility_id` VARCHAR(191) NOT NULL,
    `commodity_id` VARCHAR(191) NOT NULL,

    PRIMARY KEY (`user_building_utility_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `Utility_Companies` (
    `utility_company_id` VARCHAR(191) NOT NULL,
    `utility_company_name` VARCHAR(191) NOT NULL,
    `country_id` VARCHAR(191) NOT NULL,
    `commodity_id` VARCHAR(191) NOT NULL,

    PRIMARY KEY (`utility_company_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `Country` (
    `country_id` VARCHAR(191) NOT NULL,
    `name` VARCHAR(191) NOT NULL,

    PRIMARY KEY (`country_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `States` (
    `state_id` VARCHAR(191) NOT NULL,
    `name` VARCHAR(191) NOT NULL,
    `country_id` VARCHAR(191) NOT NULL,

    PRIMARY KEY (`state_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `Commodity` (
    `commodity_id` VARCHAR(191) NOT NULL,
    `name` VARCHAR(191) NOT NULL,

    PRIMARY KEY (`commodity_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `User_Types` (
    `id` VARCHAR(191) NOT NULL,
    `name` VARCHAR(191) NOT NULL,

    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- AddForeignKey
ALTER TABLE `Users` ADD CONSTRAINT `Users_user_type_id_fkey` FOREIGN KEY (`user_type_id`) REFERENCES `User_Types`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Publications` ADD CONSTRAINT `Publications_user_publication_id_fkey` FOREIGN KEY (`user_publication_id`) REFERENCES `Users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Educational_Qualifications` ADD CONSTRAINT `Educational_Qualifications_edu_qualification_user_id_fkey` FOREIGN KEY (`edu_qualification_user_id`) REFERENCES `Users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Professional_Qualifications` ADD CONSTRAINT `Professional_Qualifications_pro_qualification_user_id_fkey` FOREIGN KEY (`pro_qualification_user_id`) REFERENCES `Users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Work_Experience` ADD CONSTRAINT `Work_Experience_work_experience_user_id_fkey` FOREIGN KEY (`work_experience_user_id`) REFERENCES `Users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Lincenses` ADD CONSTRAINT `Lincenses_user_id_fkey` FOREIGN KEY (`user_id`) REFERENCES `Users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Lincense_Juridiction` ADD CONSTRAINT `Lincense_Juridiction_lincense_id_fkey` FOREIGN KEY (`lincense_id`) REFERENCES `Lincenses`(`lincense_id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Lincense_Juridiction` ADD CONSTRAINT `Lincense_Juridiction_country_id_fkey` FOREIGN KEY (`country_id`) REFERENCES `Country`(`country_id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `JobRecommendations` ADD CONSTRAINT `JobRecommendations_user_id_fkey` FOREIGN KEY (`user_id`) REFERENCES `Users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Intern_Interests` ADD CONSTRAINT `Intern_Interests_interest_id_fkey` FOREIGN KEY (`interest_id`) REFERENCES `Interests`(`interns_interest_id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Intern_Interests` ADD CONSTRAINT `Intern_Interests_user_id_fkey` FOREIGN KEY (`user_id`) REFERENCES `Users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `User_Installer_Types` ADD CONSTRAINT `User_Installer_Types_user_id_fkey` FOREIGN KEY (`user_id`) REFERENCES `Users`(`user_id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `User_Installer_Types` ADD CONSTRAINT `User_Installer_Types_interests_id_fkey` FOREIGN KEY (`interests_id`) REFERENCES `Installer_Types`(`installer_type_id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `User_Building_Details` ADD CONSTRAINT `User_Building_Details_user_id_fkey` FOREIGN KEY (`user_id`) REFERENCES `Users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `User_Building_Details` ADD CONSTRAINT `User_Building_Details_building_type_id_fkey` FOREIGN KEY (`building_type_id`) REFERENCES `Building_Types`(`building_type_id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `User_Building_Details` ADD CONSTRAINT `User_Building_Details_building_sub_type_id_fkey` FOREIGN KEY (`building_sub_type_id`) REFERENCES `Building_Sub_Types`(`building_sub_type_id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `User_Building_Details` ADD CONSTRAINT `User_Building_Details_user_building_utility_id_fkey` FOREIGN KEY (`user_building_utility_id`) REFERENCES `User_Building_Utility`(`user_building_utility_id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `User_Building_Commodities` ADD CONSTRAINT `User_Building_Commodities_commodity_id_fkey` FOREIGN KEY (`commodity_id`) REFERENCES `Commodity`(`commodity_id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `User_Building_Commodities` ADD CONSTRAINT `User_Building_Commodities_user_building_details_id_fkey` FOREIGN KEY (`user_building_details_id`) REFERENCES `User_Building_Details`(`user_building_details_id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `User_Building_Utility` ADD CONSTRAINT `User_Building_Utility_utility_id_fkey` FOREIGN KEY (`utility_id`) REFERENCES `Utility_Companies`(`utility_company_id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `User_Building_Utility` ADD CONSTRAINT `User_Building_Utility_commodity_id_fkey` FOREIGN KEY (`commodity_id`) REFERENCES `Commodity`(`commodity_id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Utility_Companies` ADD CONSTRAINT `Utility_Companies_country_id_fkey` FOREIGN KEY (`country_id`) REFERENCES `Country`(`country_id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `Utility_Companies` ADD CONSTRAINT `Utility_Companies_commodity_id_fkey` FOREIGN KEY (`commodity_id`) REFERENCES `Commodity`(`commodity_id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `States` ADD CONSTRAINT `States_country_id_fkey` FOREIGN KEY (`country_id`) REFERENCES `Country`(`country_id`) ON DELETE RESTRICT ON UPDATE CASCADE;