-- CreateTable
CREATE TABLE `nav_items` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `label` VARCHAR(120) NOT NULL,
    `kind` ENUM('DROPDOWN', 'LINK') NOT NULL,
    `linkType` ENUM('NONE', 'ARTICLE', 'LAKE', 'NBS', 'EXTERNAL', 'SECTION') NOT NULL DEFAULT 'NONE',
    `slug` VARCHAR(140) NULL,
    `position` INTEGER NOT NULL DEFAULT 0,
    `is_published` BOOLEAN NOT NULL DEFAULT false,
    `parent_id` INTEGER NULL,
    `section_id` INTEGER NULL,
    `article_id` INTEGER NULL,
    `lake_id` INTEGER NULL,
    `nbs_id` INTEGER NULL,
    `external_url` VARCHAR(2048) NULL,
    `created_by` INTEGER NOT NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,

    UNIQUE INDEX `nav_items_slug_key`(`slug`),
    INDEX `nav_items_parent_id_position_idx`(`parent_id`, `position`),
    INDEX `nav_items_is_published_idx`(`is_published`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- AddForeignKey
ALTER TABLE `nav_items` ADD CONSTRAINT `nav_items_parent_id_fkey` FOREIGN KEY (`parent_id`) REFERENCES `nav_items`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `nav_items` ADD CONSTRAINT `nav_items_section_id_fkey` FOREIGN KEY (`section_id`) REFERENCES `nav_items`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `nav_items` ADD CONSTRAINT `nav_items_article_id_fkey` FOREIGN KEY (`article_id`) REFERENCES `articles`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `nav_items` ADD CONSTRAINT `nav_items_lake_id_fkey` FOREIGN KEY (`lake_id`) REFERENCES `lakes`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `nav_items` ADD CONSTRAINT `nav_items_nbs_id_fkey` FOREIGN KEY (`nbs_id`) REFERENCES `nature_based_solutions`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `nav_items` ADD CONSTRAINT `nav_items_created_by_fkey` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;