-- ============================================================
-- B2B Client Attributes — migracja
-- Data: 2026-04-21
-- ============================================================
-- UWAGA: tabela b2b_client_company_department NIE jest modyfikowana.
-- Pozostaje nienaruszona do czasu przepięcia starego kodu.
-- Nowy słownik działów to osobna tabela: b2b_client_department.
-- ============================================================

-- 1. Nowy słownik działów firmowych (per-brand) — OBOK starej tabeli
--    Wzorzec spójny z b2b_client_type / b2b_client_sector / b2b_client_interest
CREATE TABLE IF NOT EXISTS `b2b_client_department` (
    `id`       INT(11)      NOT NULL AUTO_INCREMENT,
    `id_brand` INT(11)      NOT NULL,
    `name`     VARCHAR(255) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_brand` (`id_brand`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 2. Tabela aliasów dla nowego słownika działów (tłumaczenia)
CREATE TABLE IF NOT EXISTS `b2b_client_department_alias` (
    `id`                    INT(11)      NOT NULL AUTO_INCREMENT,
    `id_b2b_client_department` INT(11)  NOT NULL,
    `lang_code`             VARCHAR(5)   NOT NULL,
    `alias_type`            VARCHAR(50)  NOT NULL DEFAULT 'default',
    `alias`                 VARCHAR(255) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_dept_alias` (`id_b2b_client_department`, `lang_code`, `alias_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 3. Rozszerzenie client_shop_profiles o pola skalarne (type, sector) — jedno per klient/sklep
ALTER TABLE `client_shop_profiles`
    ADD COLUMN `id_b2b_client_type`       INT(11) NULL DEFAULT NULL AFTER `score`,
    ADD COLUMN `id_b2b_client_sector`     INT(11) NULL DEFAULT NULL AFTER `id_b2b_client_type`;

-- 4. Tabela junction: klient × sklep × interest (wiele zainteresowań)
CREATE TABLE IF NOT EXISTS `client_shop_interests` (
    `id`          INT(11)   NOT NULL AUTO_INCREMENT,
    `client_id`   INT(11)   NOT NULL,
    `shop_id`     INT(11)   NOT NULL,
    `id_interest` INT(11)   NOT NULL,
    `created_at`  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_csi` (`client_id`, `shop_id`, `id_interest`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 5. Tabela junction: klient × sklep × department (nowy słownik — wiele działów)
CREATE TABLE IF NOT EXISTS `client_shop_departments` (
    `id`            INT(11)   NOT NULL AUTO_INCREMENT,
    `client_id`     INT(11)   NOT NULL,
    `shop_id`       INT(11)   NOT NULL,
    `id_department` INT(11)   NOT NULL,  -- FK do b2b_client_department.id
    `created_at`    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_csd` (`client_id`, `shop_id`, `id_department`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

