-- Categories: groups with child categories, codes, labels and library assignment (ShowOff-style Categories page).
-- Run once on the database, then `pnpm prisma generate` and restart the dev server. Safe to run again.

ALTER TABLE `Category`
  ADD COLUMN IF NOT EXISTS `isGroup` BOOLEAN NOT NULL DEFAULT false,
  ADD COLUMN IF NOT EXISTS `parentId` VARCHAR(191) NULL,
  ADD COLUMN IF NOT EXISTS `code` VARCHAR(40) NULL,
  ADD COLUMN IF NOT EXISTS `selectable` BOOLEAN NOT NULL DEFAULT true,
  ADD COLUMN IF NOT EXISTS `sortOrder` INT NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS `labels` JSON NULL,
  ADD COLUMN IF NOT EXISTS `scopes` JSON NULL;

CREATE INDEX IF NOT EXISTS `Category_parentId_idx` ON `Category`(`parentId`);

-- Self reference (skipped when it already exists).
SET @fk := (SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = DATABASE() AND TABLE_NAME = 'Category' AND CONSTRAINT_NAME = 'Category_parentId_fkey');
SET @sql := IF(@fk = 0, 'ALTER TABLE `Category` ADD CONSTRAINT `Category_parentId_fkey` FOREIGN KEY (`parentId`) REFERENCES `Category`(`id`) ON DELETE CASCADE ON UPDATE CASCADE', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Existing per-library categories become the children of one group per website and library.
INSERT INTO `Category` (`id`, `siteId`, `scope`, `name`, `slug`, `isGroup`, `scopes`)
SELECT UUID(), c.`siteId`, '_group', CONCAT(UPPER(LEFT(c.`scope`, 1)), SUBSTRING(c.`scope`, 2), ' categories'), CONCAT('group-', c.`scope`), true, JSON_ARRAY(c.`scope`)
FROM `Category` c
WHERE c.`isGroup` = false AND c.`parentId` IS NULL AND c.`scope` NOT IN ('_group', '_tree')
  AND NOT EXISTS (SELECT 1 FROM (SELECT `siteId`, `slug` FROM `Category` WHERE `isGroup` = true) g WHERE g.`siteId` = c.`siteId` AND g.`slug` = CONCAT('group-', c.`scope`))
GROUP BY c.`siteId`, c.`scope`;

UPDATE `Category` c
JOIN (SELECT `id`, `siteId`, `slug` FROM `Category` WHERE `isGroup` = true) g ON g.`siteId` = c.`siteId` AND g.`slug` = CONCAT('group-', c.`scope`)
SET c.`parentId` = g.`id`
WHERE c.`isGroup` = false AND c.`parentId` IS NULL AND c.`scope` NOT IN ('_group', '_tree');