-- Manual order of NbS inside their theme. Display order: page-pinned first,
-- then position, then id.
ALTER TABLE `articles` ADD COLUMN `position` INTEGER NOT NULL DEFAULT 0;

-- Backfill from the order the NbS page shows today (pinned first, newest pin
-- first, then newest first), so nothing moves on deploy. The window function
-- keeps the derived table materialized, which MySQL needs to update `articles`.
UPDATE `articles` a
JOIN (
    SELECT `id`,
           ROW_NUMBER() OVER (
               PARTITION BY `theme_id`
               ORDER BY `pinned_at` IS NULL, `pinned_at` DESC, `created_at` DESC, `id` DESC
           ) - 1 AS `pos`
    FROM `articles`
    WHERE `is_deleted` = false
) ranked ON ranked.`id` = a.`id`
SET a.`position` = ranked.`pos`;

-- CreateIndex
CREATE INDEX `articles_theme_id_position_idx` ON `articles`(`theme_id`, `position`);

-- Managers delete any NbS (delete:articles), like Admins.
-- 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` = 'delete:articles'
  AND NOT EXISTS (
      SELECT 1 FROM `role_permissions` rp
      WHERE rp.`role` = 'MANAGER' AND rp.`permission_id` = p.`id`
  );