-- CreateTable
CREATE TABLE `nature_based_solutions` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(255) NOT NULL,
    `subtitle` VARCHAR(255) NULL,
    `content` TEXT NOT NULL,
    `user_id` INTEGER NOT NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,

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

-- CreateTable
CREATE TABLE `nbs_images` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(255) NULL,
    `credit` VARCHAR(255) NULL,
    `year` VARCHAR(4) NULL,
    `disclaimer` TEXT NULL,
    `upload_order` INTEGER NOT NULL DEFAULT 0,
    `nbs_id` INTEGER NOT NULL,
    `file_id` INTEGER NOT NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,

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

-- CreateTable
CREATE TABLE `nbs_files` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `upload_order` INTEGER NOT NULL DEFAULT 0,
    `nbs_id` INTEGER NOT NULL,
    `file_id` INTEGER NOT NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,

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

-- CreateTable
CREATE TABLE `_LakeToNatureBasedSolution` (
    `A` INTEGER NOT NULL,
    `B` INTEGER NOT NULL,

    UNIQUE INDEX `_LakeToNatureBasedSolution_AB_unique`(`A`, `B`),
    INDEX `_LakeToNatureBasedSolution_B_index`(`B`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- AddForeignKey
ALTER TABLE `nature_based_solutions` ADD CONSTRAINT `nature_based_solutions_user_id_fkey` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `nbs_images` ADD CONSTRAINT `nbs_images_nbs_id_fkey` FOREIGN KEY (`nbs_id`) REFERENCES `nature_based_solutions`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `nbs_images` ADD CONSTRAINT `nbs_images_file_id_fkey` FOREIGN KEY (`file_id`) REFERENCES `files`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `nbs_files` ADD CONSTRAINT `nbs_files_nbs_id_fkey` FOREIGN KEY (`nbs_id`) REFERENCES `nature_based_solutions`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `nbs_files` ADD CONSTRAINT `nbs_files_file_id_fkey` FOREIGN KEY (`file_id`) REFERENCES `files`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `_LakeToNatureBasedSolution` ADD CONSTRAINT `_LakeToNatureBasedSolution_A_fkey` FOREIGN KEY (`A`) REFERENCES `lakes`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `_LakeToNatureBasedSolution` ADD CONSTRAINT `_LakeToNatureBasedSolution_B_fkey` FOREIGN KEY (`B`) REFERENCES `nature_based_solutions`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
