-- Slice 6 · Per-project-type starter checklists
-- Instances-per-project (templates live in PHP for now — the ProjectStarter model owns them).

CREATE TABLE IF NOT EXISTS project_starter_tasks (
    c0d                 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    project_id          INT NOT NULL,
    task_key            VARCHAR(60) NOT NULL,
    task_label          VARCHAR(200) NOT NULL,
    task_description    VARCHAR(500) NOT NULL DEFAULT '',
    owner_slot          VARCHAR(40) NOT NULL DEFAULT '',
    owner_user_id       INT NOT NULL DEFAULT 0,
    owner_user_name     VARCHAR(120) NOT NULL DEFAULT '',
    sequence            INT NOT NULL DEFAULT 0,
    due_offset_days     INT NOT NULL DEFAULT 0,
    due_date            DATE NULL,
    target_url_template VARCHAR(255) NOT NULL DEFAULT '',
    status              ENUM('OPEN','DONE','SKIPPED') NOT NULL DEFAULT 'OPEN',
    completed_by        INT NOT NULL DEFAULT 0,
    completed_by_name   VARCHAR(120) NOT NULL DEFAULT '',
    completed_on        TIMESTAMP NULL,
    notes               TEXT NULL,
    created_by          INT NOT NULL DEFAULT 0,
    created_on          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    edited_by           INT NOT NULL DEFAULT 0,
    edited_on           TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted             TINYINT(1) NOT NULL DEFAULT 0,
    UNIQUE KEY uq_proj_key (project_id, task_key),
    KEY ix_owner_open (owner_user_id, status),
    KEY ix_project_seq (project_id, sequence)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
