-- Migracja: atrybuty opisowe klientów B2B
-- Data: 2026-04-21
--
-- 1. Dodanie kolumn FK do client_shop_profiles (type + sector per sklep)
-- 2. Tabela client_shop_interests (zainteresowania klienta per sklep)
-- 3. Tabela client_shop_departments (działy firmy klienta per sklep)
-- 4. Słownik globalny b2b_client_department (per brand, zarządzany przez admina)
--    Uwaga: b2b_client_type, b2b_client_sector, b2b_client_interest
--    są tworzone przez dumpy SQL z katalogu .github/examples/21042026/

-- ── 1. Kolumny FK w client_shop_profiles ─────────────────────────────────────
ALTER TABLE client_shop_profiles
    ADD COLUMN id_b2b_client_type   INT UNSIGNED NULL DEFAULT NULL
        COMMENT 'FK → b2b_client_type.id (słownik globalny per brand)',
    ADD COLUMN id_b2b_client_sector INT UNSIGNED NULL DEFAULT NULL
        COMMENT 'FK → b2b_client_sector.id (słownik globalny per brand)';

-- ── 2. Zainteresowania produktowe klienta per sklep ───────────────────────────
CREATE TABLE IF NOT EXISTS client_shop_interests (
    id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    client_id   INT UNSIGNED NOT NULL COMMENT 'id klienta',
    shop_id     INT UNSIGNED NOT NULL COMMENT 'id sklepu',
    id_interest INT UNSIGNED NOT NULL COMMENT 'FK → b2b_client_interest.id',
    PRIMARY KEY (id),
    UNIQUE KEY uq_client_shop_interest (client_id, shop_id, id_interest),
    KEY idx_shop_id (shop_id),
    KEY idx_client_id (client_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 3. Działy firmy klienta per sklep ─────────────────────────────────────────
CREATE TABLE IF NOT EXISTS client_shop_departments (
    id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    client_id     INT UNSIGNED NOT NULL COMMENT 'id klienta',
    shop_id       INT UNSIGNED NOT NULL COMMENT 'id sklepu',
    id_department INT UNSIGNED NOT NULL COMMENT 'FK → b2b_client_department.id',
    PRIMARY KEY (id),
    UNIQUE KEY uq_client_shop_dept (client_id, shop_id, id_department),
    KEY idx_shop_id (shop_id),
    KEY idx_client_id (client_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 4. Słownik globalny działów (per brand) ────────────────────────────────────
-- Zarządzany przez admina (CRUD w B2BDictionaryCrudController).
-- Różni się od b2b_client_company_department, która przechowuje
-- działy przypisane do konkretnego klienta (stara tabela, bez id_brand).
CREATE TABLE IF NOT EXISTS b2b_client_department (
    id_brand INT(11) NOT NULL,
    id       INT(11) NOT NULL AUTO_INCREMENT,
    name     VARCHAR(255) NOT NULL,
    PRIMARY KEY (id),
    KEY idx_brand (id_brand)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ── 5. Tabele słownikowe (type, sector, interest) ─────────────────────────────
-- Jeśli jeszcze nie istnieją — tworzone przez SQL dumpy z .github/examples/21042026/
CREATE TABLE IF NOT EXISTS b2b_client_type (
    id_brand INT(11)      NOT NULL,
    id       INT(11)      NOT NULL AUTO_INCREMENT,
    name     VARCHAR(255) NOT NULL,
    PRIMARY KEY (id),
    KEY idx_brand (id_brand)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS b2b_client_sector (
    id_brand INT(11)      NOT NULL,
    id       INT(11)      NOT NULL AUTO_INCREMENT,
    name     VARCHAR(255) NOT NULL,
    PRIMARY KEY (id),
    KEY idx_brand (id_brand)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS b2b_client_interest (
    id_brand INT(11)      NOT NULL,
    id       INT(11)      NOT NULL AUTO_INCREMENT,
    name     VARCHAR(255) NOT NULL,
    PRIMARY KEY (id),
    KEY idx_brand (id_brand)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

