-- Add todo categories tables
CREATE TABLE `todo_category` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `description` TEXT,
    `color` VARCHAR(7) DEFAULT '#3B82F6',
    `isActive` BOOLEAN DEFAULT TRUE,
    `parentId` INT NULL,
    `sortOrder` INT DEFAULT 0,
    `visibleToRoles` JSON,
    `createdAt` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updatedAt` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `createdBy` INT,
    INDEX `idx_category_parent` (`parentId`),
    INDEX `idx_category_active` (`isActive`),
    INDEX `idx_category_sort` (`sortOrder`),
    CONSTRAINT `fk_category_parent` FOREIGN KEY (`parentId`) REFERENCES `todo_category`(`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_category_created_by` FOREIGN KEY (`createdBy`) REFERENCES `user`(`id`) ON DELETE SET NULL
);

-- Add category reference to todo table
ALTER TABLE `todo` ADD COLUMN `categoryId` INT NULL AFTER `createdBy`;
ALTER TABLE `todo` ADD INDEX `idx_todo_category` (`categoryId`);
ALTER TABLE `todo` ADD CONSTRAINT `fk_todo_category` FOREIGN KEY (`categoryId`) REFERENCES `todo_category`(`id`) ON DELETE SET NULL;

-- Insert default categories
INSERT INTO `todo_category` (`name`, `description`, `color`, `parentId`, `sortOrder`, `visibleToRoles`, `createdBy`) VALUES
('Veriagent', 'Oppgaver for Veriagenter', '#10B981', NULL, 1, '["VERIAGENT", "MODERATOR", "DEVELOPERS", "ADMIN", "SUPER_ADMIN"]', 1),
('Moderator', 'Oppgaver for Moderatorer', '#8B5CF6', NULL, 2, '["MODERATOR", "DEVELOPERS", "ADMIN", "SUPER_ADMIN"]', 1),
('Utvikler', 'Oppgaver for Utviklere', '#06B6D4', NULL, 3, '["DEVELOPERS", "ADMIN", "SUPER_ADMIN"]', 1),
('Admin', 'Administrative oppgaver', '#F59E0B', NULL, 4, '["ADMIN", "SUPER_ADMIN"]', 1),
('Lederskapet', 'Lederoppgaver', '#EF4444', NULL, 5, '["SUPER_ADMIN"]', 1);
