-- ===========================================================================
-- Migration: Create of_tag and of_tag_assignment
-- Date: 2026-05-15
-- Context: Global tagging system for OperationalFramework entities
--          (checklist, task, training, procedure, competency)
-- ===========================================================================
-- of_tag           — definicja tagów (globalne, współdzielone między typami encji)
-- of_tag_assignment — pivot: przypisanie tagu do konkretnej encji per typ
-- ===========================================================================

CREATE TABLE IF NOT EXISTS `of_tag` (
    `id`         INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    `name`       VARCHAR(100)  NOT NULL               COMMENT 'Wyświetlana nazwa tagu — unikalna',
    `color`      VARCHAR(7)    NOT NULL DEFAULT '#6366f1' COMMENT 'Kolor tagu w formacie hex #RRGGBB',
    `created_at` DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_of_tag_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===========================================================================

CREATE TABLE IF NOT EXISTS `of_tag_assignment` (
    `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `tag_id`      INT UNSIGNED NOT NULL               COMMENT 'FK → of_tag.id',
    `entity_type` ENUM('checklist','task','training','procedure','competency')
                               NOT NULL               COMMENT 'Typ encji, do której przypisano tag',
    `entity_id`   INT UNSIGNED NOT NULL               COMMENT 'ID encji danego typu',
    `created_at`  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_tag_entity`    (`tag_id`, `entity_type`, `entity_id`),
    KEY           `idx_entity`    (`entity_type`, `entity_id`),
    CONSTRAINT `fk_ota_tag`
        FOREIGN KEY (`tag_id`) REFERENCES `of_tag` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
