-- ============================================================
-- Migracja: Notatki konsultanta
-- Data: 2026-04-13
-- ============================================================

-- Słownik typów notatek (zarządzany przez admin)
CREATE TABLE IF NOT EXISTS consultant_note_type (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code        VARCHAR(64)  NOT NULL UNIQUE,
    name        VARCHAR(128) NOT NULL,
    description TEXT         NULL,
    is_active   TINYINT(1)   NOT NULL DEFAULT 1,
    sort_order  SMALLINT     NOT NULL DEFAULT 0,
    created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Notatki konsultanta o sklepie (główny "problem/zgłoszenie")
CREATE TABLE IF NOT EXISTS consultant_note (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    shop_id         INT UNSIGNED  NOT NULL,
    consultant_id   INT UNSIGNED  NOT NULL,
    membership_id   INT UNSIGNED  NULL,
    note_type_id    INT UNSIGNED  NULL,
    content         TEXT          NOT NULL,
    deleted_at      DATETIME      NULL DEFAULT NULL,
    created_at      DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_shop_id       (shop_id),
    INDEX idx_consultant_id (consultant_id),
    INDEX idx_deleted_at    (deleted_at),
    CONSTRAINT fk_cn_shop FOREIGN KEY (shop_id)
        REFERENCES franchisee_shop(id) ON DELETE RESTRICT,
    CONSTRAINT fk_cn_type FOREIGN KEY (note_type_id)
        REFERENCES consultant_note_type(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Komentarze do notatek (follow-upy konsultanta)
CREATE TABLE IF NOT EXISTS consultant_note_comment (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    note_id         INT UNSIGNED  NOT NULL,
    consultant_id   INT UNSIGNED  NOT NULL,
    content         TEXT          NOT NULL,
    deleted_at      DATETIME      NULL DEFAULT NULL,
    created_at      DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_note_id       (note_id),
    INDEX idx_deleted_at    (deleted_at),
    CONSTRAINT fk_cnc_note FOREIGN KEY (note_id)
        REFERENCES consultant_note(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Zdjęcia: reużywamy istniejącej tabeli file_attachment
-- related_type = 'consultant_note_comment'
-- related_id   = consultant_note_comment.id
-- Max 4 zdjęcia per komentarz — wymuszane przez API service

-- Przykładowe dane słownika
INSERT IGNORE INTO consultant_note_type (code, name, description, is_active, sort_order) VALUES
    ('VISIT',    'Wizyta',             'Wizyta konsultanta w sklepie',    1, 10),
    ('AUDIT',    'Audyt',              'Formalny audyt placówki',          1, 20),
    ('COACHING', 'Coaching',           'Sesja coachingowa z personelem',   1, 30),
    ('ISSUE',    'Problem/Zgłoszenie', 'Zgłoszony problem lub incydent',   1, 40),
    ('OTHER',    'Inne',               'Pozostałe notatki',                1, 99);

