-- Lake rich-text fields hold editor HTML (images, tables, attachments):
-- widen TEXT (64 KB) to MEDIUMTEXT (16 MB). Widening keeps every existing value.
ALTER TABLE `lakes`
    MODIFY `description` MEDIUMTEXT NULL,
    MODIFY `environmental_stressors` MEDIUMTEXT NULL,
    MODIFY `ecological_status` MEDIUMTEXT NULL,
    MODIFY `socio_economic_context` MEDIUMTEXT NULL,
    MODIFY `historical_background` MEDIUMTEXT NULL,
    MODIFY `nbs_implemented` MEDIUMTEXT NULL,
    MODIFY `monitoring_activities` MEDIUMTEXT NULL,
    MODIFY `key_findings` MEDIUMTEXT NULL,
    MODIFY `challenges_opportunities` MEDIUMTEXT NULL;

-- Managers create and edit any lake, like Organizations (write:lakes).
-- Idempotent: skips an existing row, and does nothing if the permission is missing.
INSERT INTO `role_permissions` (`role`, `permission_id`)
SELECT 'MANAGER', p.`id`
FROM `permissions` p
WHERE p.`name` = 'write:lakes'
  AND NOT EXISTS (
      SELECT 1 FROM `role_permissions` rp
      WHERE rp.`role` = 'MANAGER' AND rp.`permission_id` = p.`id`
  );