-- =============================================================
-- Moduł Checklist – DDL
-- Wersja: iteracja 1 (MVP)
-- Data: 2026-04-16
-- =============================================================
-- Migracja (jeśli tabela checklist_task już istnieje):
-- ALTER TABLE `checklist_task` ADD COLUMN `note` TEXT NULL AFTER `sort_order`;
-- =============================================================

-- -------------------------------------------------------------
-- Tabela: checklist
-- Globalny szablon listy kontrolnej (nie przypisany do sklepu)
-- -------------------------------------------------------------
CREATE TABLE `checklist` (
    `id`             INT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `name`           VARCHAR(255)     NOT NULL,
    `description`    TEXT             NULL,
    `workstation_id` INT UNSIGNED     NULL,
    `execution_time` TIME             NULL,
    `created_at`     TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`     TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_checklist_workstation`
        FOREIGN KEY (`workstation_id`)
        REFERENCES `shop_workstation` (`id`)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -------------------------------------------------------------
-- Tabela: checklist_task
-- Pivot M:N – powiązanie checklisty z zadaniami (todo_task)
-- -------------------------------------------------------------
CREATE TABLE `checklist_task` (
    `id`           INT UNSIGNED      NOT NULL AUTO_INCREMENT,
    `checklist_id` INT UNSIGNED      NOT NULL,
    `task_id`      INT UNSIGNED      NOT NULL,
    `sort_order`   SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    `note`         TEXT              NULL,
    `created_at`   TIMESTAMP         NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_checklist_task` (`checklist_id`, `task_id`),
    CONSTRAINT `fk_ct_checklist`
        FOREIGN KEY (`checklist_id`)
        REFERENCES `checklist` (`id`)
        ON DELETE CASCADE,
    CONSTRAINT `fk_ct_task`
        FOREIGN KEY (`task_id`)
        REFERENCES `todo_task` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

