-- Migration: Prüferboard Tabellen

START TRANSACTION;

-- Haupttabelle
CREATE TABLE IF NOT EXISTS `prueferboard` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `description` TEXT NULL,
  `priority` TINYINT NOT NULL DEFAULT 2, -- 1=hoch,2=mittel,3=niedrig
  `done` TINYINT(1) NOT NULL DEFAULT 0,
  `due_date` DATE NULL,
  `sort_order` INT NULL DEFAULT 0,
  `color` VARCHAR(16) NULL,
  `admin_id` INT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Kategorien-Stammtabelle
CREATE TABLE IF NOT EXISTS `prueferboard_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;

-- Zuordnung Eintrag <-> Kategorien
CREATE TABLE IF NOT EXISTS `prueferboard_item_categories` (
  `prueferboard_id` INT NOT NULL,
  `category_id` INT NOT NULL,
  PRIMARY KEY (`prueferboard_id`, `category_id`),
  KEY `idx_pib_category_id` (`category_id`),
  CONSTRAINT `fk_pib_prueferboard` FOREIGN KEY (`prueferboard_id`) REFERENCES `prueferboard`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_pib_category` FOREIGN KEY (`category_id`) REFERENCES `prueferboard_categories`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Anhänge
CREATE TABLE IF NOT EXISTS `prueferboard_attachments` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `prueferboard_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_pa_prueferboard_id` (`prueferboard_id`),
  CONSTRAINT `fk_pa_prueferboard` FOREIGN KEY (`prueferboard_id`) REFERENCES `prueferboard`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Kommentare (Admins/Prüfer)
CREATE TABLE IF NOT EXISTS `prueferboard_comments` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `prueferboard_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_pc_prueferboard_id` (`prueferboard_id`),
  CONSTRAINT `fk_pc_prueferboard` FOREIGN KEY (`prueferboard_id`) REFERENCES `prueferboard`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

COMMIT;
