-- ===========================================================================
-- Cases (Zgłoszenia z routingiem przez obszary konsultantów) – SQL DDL
-- Charset: utf8mb4_unicode_ci  |  Engine: InnoDB
-- ===========================================================================
-- NOTA: Moduł `cases` jest budowany równolegle do istniejącego mechanizmu
-- `demand` (patrz: documentation/demands_ddl.sql) i nie modyfikuje go.
--
-- Kluczowe pola:
--   - area_key        → obszar odpowiedzialności konsultanta (routing tematu)
--   - id_meeting      → opcjonalne powiązanie ze spotkaniem, na którym
--                        użytkownik chciałby omówić zgłoszenie.
--                        ON DELETE SET NULL: gdy administrator usunie spotkanie,
--                        case pozostaje w systemie, a powiązanie jest zerowane —
--                        re-przyporządkowanie do innego spotkania będzie obsłużone
--                        w kolejnej iteracji (admin/panel).
-- ===========================================================================

-- ---------------------------------------------------------------------------
-- 1. case_request – główna tabela zgłoszeń modułu Cases
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `case_request` (
    `id`                  INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    `ref`                 VARCHAR(32)   NOT NULL COMMENT 'Unique reference, e.g. CASE-20260429-AB12C',
    `area_key`            VARCHAR(64)   NOT NULL COMMENT 'consultant_area_templates.area_key (routing topic)',
    `status`              ENUM('NEW','IN_PROGRESS','RESOLVED','CLOSED','CANCELLED')
                          NOT NULL DEFAULT 'NEW',
    `title`               VARCHAR(255)  NOT NULL,
    `description`         TEXT          NOT NULL,
    `context`             TEXT          NULL COMMENT 'Optional context / motivation',
    `meta_json`           TEXT          NULL,
    `shop_id`             INT UNSIGNED  NULL,
    `created_by_user_id`  INT UNSIGNED  NOT NULL COMMENT 'auth_user.id of the franchisee creator',

    -- Sugerowany konsultant (snapshot przy tworzeniu — może być pusty
    -- gdy obszar nie ma jeszcze przypisanego konsultanta)
    `suggested_membership_id` INT       NULL COMMENT 'user_membership.id konsultanta z obszaru area_key',

    -- Spotkanie wybrane przez użytkownika (opcjonalnie)
    `id_meeting`          INT           NULL COMMENT 'franchise_meeting.id — proponowane spotkanie do omówienia',

    `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_case_request_ref` (`ref`),
    KEY `idx_case_request_status`     (`status`),
    KEY `idx_case_request_area`       (`area_key`),
    KEY `idx_case_request_shop`       (`shop_id`),
    KEY `idx_case_request_created_by` (`created_by_user_id`),
    KEY `idx_case_request_meeting`    (`id_meeting`),
    KEY `idx_case_request_created_at` (`created_at`),

    -- ON DELETE SET NULL — usunięcie spotkania nie usuwa case'a;
    -- powiązanie ze spotkaniem zostaje wyzerowane do późniejszego przypisania
    -- przez administratora.
    CONSTRAINT `fk_case_request_meeting`
        FOREIGN KEY (`id_meeting`) REFERENCES `franchise_meeting` (`id`)
        ON DELETE SET NULL
        ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- NOTE: Załączniki obsługujemy przez istniejącą tabelę `file_attachment`
-- z related_type = 'case_request', related_id = case_request.id
-- (analogicznie do mechanizmu `demand`).
-- ---------------------------------------------------------------------------

