-- Roadmap extensions: multi-categories, attachments, comments, and missing columns

START TRANSACTION;

-- Ensure base columns exist on roadmap
ALTER TABLE `roadmap`
  ADD COLUMN IF NOT EXISTS `done` TINYINT(1) NOT NULL DEFAULT 0 AFTER `description`,
  ADD COLUMN IF NOT EXISTS `due_date` DATE NULL AFTER `category`,
  ADD COLUMN IF NOT EXISTS `sort_order` INT NULL DEFAULT 0 AFTER `due_date`,
  ADD COLUMN IF NOT EXISTS `color` VARCHAR(16) NULL AFTER `sort_order`,
  ADD COLUMN IF NOT EXISTS `admin_id` INT NULL AFTER `color`,
  ADD COLUMN IF NOT EXISTS `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

-- Categories master table
CREATE TABLE IF NOT EXISTS `roadmap_categories` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL UNIQUE,
  `color` VARCHAR(16) NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Many-to-many between roadmap and categories
CREATE TABLE IF NOT EXISTS `roadmap_item_categories` (
  `roadmap_id` INT NOT NULL,
  `category_id` INT NOT NULL,
  PRIMARY KEY (`roadmap_id`, `category_id`),
  KEY `idx_category_id` (`category_id`),
  CONSTRAINT `fk_ric_roadmap` FOREIGN KEY (`roadmap_id`) REFERENCES `roadmap`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ric_category` FOREIGN KEY (`category_id`) REFERENCES `roadmap_categories`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Attachments for roadmap items
CREATE TABLE IF NOT EXISTS `roadmap_attachments` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `roadmap_id` INT NOT NULL,
  `path` VARCHAR(255) NOT NULL,
  `filename` VARCHAR(255) NOT NULL,
  `uploaded_by` INT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_ra_roadmap_id` (`roadmap_id`),
  CONSTRAINT `fk_ra_roadmap` FOREIGN KEY (`roadmap_id`) REFERENCES `roadmap`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Comments from Prüfer/Admins
CREATE TABLE IF NOT EXISTS `roadmap_comments` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `roadmap_id` INT NOT NULL,
  `user_id` INT NULL,
  `text` TEXT NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_rc_roadmap_id` (`roadmap_id`),
  CONSTRAINT `fk_rc_roadmap` FOREIGN KEY (`roadmap_id`) REFERENCES `roadmap`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

COMMIT;
