-- Moderator feedback on an observation, read by the submitter on
-- /dashboard/my-observations. Cascades with its observation: feedback is
-- meaningless without it, and the moderation log keeps the audit trail
-- independently (its poi_id is ON DELETE SET NULL).

-- AlterTable
ALTER TABLE `moderation_logs` MODIFY `action` ENUM('POI_APPROVED', 'POI_REJECTED', 'POI_CONTENT_EDITED', 'POI_IMAGE_REMOVED', 'POI_FEEDBACK_SENT', 'LAKE_UPDATED', 'LAKE_MODERATOR_ASSIGNED', 'LAKE_MODERATOR_UNASSIGNED') NOT NULL;

-- CreateTable
CREATE TABLE `poi_feedback` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `message` TEXT NOT NULL,
    `read_at` DATETIME(3) NULL,
    `poi_id` INTEGER NOT NULL,
    `author_id` INTEGER NOT NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    INDEX `poi_feedback_poi_id_idx`(`poi_id`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- AddForeignKey
ALTER TABLE `poi_feedback` ADD CONSTRAINT `poi_feedback_poi_id_fkey` FOREIGN KEY (`poi_id`) REFERENCES `points_of_interest`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `poi_feedback` ADD CONSTRAINT `poi_feedback_author_id_fkey` FOREIGN KEY (`author_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;