-- AlterTable
ALTER TABLE `poi_files` ADD COLUMN `removal_reason` TEXT NULL,
    ADD COLUMN `removed` BOOLEAN NOT NULL DEFAULT false,
    ADD COLUMN `removed_at` DATETIME(3) NULL,
    ADD COLUMN `removed_by` INTEGER NULL;

-- AlterTable
ALTER TABLE `points_of_interest` ADD COLUMN `original_observation` TEXT NULL,
    ADD COLUMN `original_title` VARCHAR(255) NULL;

-- CreateTable
CREATE TABLE `user_lake_assignments` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `user_id` INTEGER NOT NULL,
    `lake_id` INTEGER NOT NULL,
    `assigned_by` INTEGER NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,

    INDEX `user_lake_assignments_user_id_idx`(`user_id`),
    INDEX `user_lake_assignments_lake_id_idx`(`lake_id`),
    UNIQUE INDEX `user_lake_assignments_user_id_lake_id_key`(`user_id`, `lake_id`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `moderation_logs` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `action` ENUM('POI_APPROVED', 'POI_REJECTED', 'POI_CONTENT_EDITED', 'POI_IMAGE_REMOVED', 'LAKE_UPDATED', 'LAKE_MODERATOR_ASSIGNED', 'LAKE_MODERATOR_UNASSIGNED') NOT NULL,
    `reason` TEXT NULL,
    `details` JSON NULL,
    `moderator_id` INTEGER NOT NULL,
    `target_user_id` INTEGER NULL,
    `poi_id` INTEGER NULL,
    `lake_id` INTEGER NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    INDEX `moderation_logs_moderator_id_idx`(`moderator_id`),
    INDEX `moderation_logs_target_user_id_idx`(`target_user_id`),
    INDEX `moderation_logs_poi_id_idx`(`poi_id`),
    INDEX `moderation_logs_lake_id_idx`(`lake_id`),
    INDEX `moderation_logs_action_idx`(`action`),
    INDEX `moderation_logs_created_at_idx`(`created_at`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateIndex
CREATE INDEX `poi_files_removed_idx` ON `poi_files`(`removed`);

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

-- AddForeignKey
ALTER TABLE `user_lake_assignments` ADD CONSTRAINT `user_lake_assignments_lake_id_fkey` FOREIGN KEY (`lake_id`) REFERENCES `lakes`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `user_lake_assignments` ADD CONSTRAINT `user_lake_assignments_assigned_by_fkey` FOREIGN KEY (`assigned_by`) REFERENCES `users`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `moderation_logs` ADD CONSTRAINT `moderation_logs_moderator_id_fkey` FOREIGN KEY (`moderator_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `moderation_logs` ADD CONSTRAINT `moderation_logs_target_user_id_fkey` FOREIGN KEY (`target_user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `moderation_logs` ADD CONSTRAINT `moderation_logs_poi_id_fkey` FOREIGN KEY (`poi_id`) REFERENCES `points_of_interest`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

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

-- AddForeignKey
ALTER TABLE `poi_files` ADD CONSTRAINT `poi_files_removed_by_fkey` FOREIGN KEY (`removed_by`) REFERENCES `users`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;