-- ============================================================================
-- Tabela: open_material_params
-- Opis:   Parametry referencyjne dla surowców typu OPEN (dowolny dostawca).
--         Przechowuje rekomendowaną cenę zakupu, wielkość opakowania oraz
--         opis opakowania. Lista sugerowanych dostawców przechowywana jest
--         w powiązanej tabeli open_material_suggested_suppliers.
-- ============================================================================

CREATE TABLE IF NOT EXISTS `open_material_params` (

    `id`                         INT UNSIGNED     NOT NULL AUTO_INCREMENT,

    -- Relacja do surowca (jeden rekord na surowiec)
    `material_id`                INT UNSIGNED     NOT NULL
        COMMENT 'FK do materials – surowiec typu OPEN',

    -- Rekomendowana cena zakupu
    `recommended_purchase_price` DECIMAL(10, 2)   NULL     DEFAULT NULL
        COMMENT 'Rekomendowana cena zakupu brutto za opakowanie (waluta systemowa)',

    -- Wielkość opakowania w jednostce bazowej surowca (g / ml / szt)
    `package_size`               DECIMAL(12, 4)   NULL     DEFAULT NULL
        COMMENT 'Wielkość opakowania w jednostce bazowej surowca (g, ml, szt)',

    -- Opis tekstowy opakowania (np. "1x szynka konserwowa, 1000g")
    `package_description`        VARCHAR(255)     NULL     DEFAULT NULL
        COMMENT 'Opis opakowania – pomocniczy tekst dla franczyzobiorcy',

    -- Audyt
    `created_at`                 TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP
        COMMENT 'Data utworzenia rekordu',
    `updated_at`                 TIMESTAMP        NULL     DEFAULT NULL
        ON UPDATE CURRENT_TIMESTAMP
        COMMENT 'Data ostatniej aktualizacji',
    `created_by`                 INT UNSIGNED     NULL     DEFAULT NULL
        COMMENT 'ID użytkownika, który utworzył rekord',
    `updated_by`                 INT UNSIGNED     NULL     DEFAULT NULL
        COMMENT 'ID użytkownika, który zaktualizował rekord',

    -- ── Klucze ──────────────────────────────────────────────────
    PRIMARY KEY (`id`),

    -- Jeden rekord parametrów na surowiec
    UNIQUE KEY `uk_open_material_params_material` (`material_id`),

    -- Indeks wspierający wyszukiwanie po cenie (np. filtrowanie po zakresie)
    KEY `idx_omp_price` (`recommended_purchase_price`),

    -- ── Klucze obce ─────────────────────────────────────────────
    CONSTRAINT `fk_omp_material`
        FOREIGN KEY (`material_id`)
        REFERENCES `materials` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE

) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci
  COMMENT = 'Parametry referencyjne surowców OPEN: cena, wielkość i opis opakowania';


-- ============================================================================
-- Tabela: open_material_suggested_suppliers
-- Opis:   Lista sugerowanych dostawców dla surowca OPEN.
--         Relacja N:M między open_material_params a material_suppliers,
--         zrealizowana jako osobna tabela (jeden surowiec → wielu dostawców).
-- ============================================================================

CREATE TABLE IF NOT EXISTS `open_material_suggested_suppliers` (

    `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,

    -- Relacja do rekordu parametrów
    `params_id`   INT UNSIGNED NOT NULL
        COMMENT 'FK do open_material_params',

    -- Relacja do dostawcy
    `supplier_id` INT UNSIGNED NOT NULL
        COMMENT 'FK do material_suppliers',

    -- Kolejność wyświetlania (opcjonalna)
    `sort_order`  SMALLINT UNSIGNED NOT NULL DEFAULT 0
        COMMENT 'Kolejność wyświetlania sugerowanych dostawców',

    -- Audyt
    `created_at`  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
        COMMENT 'Data dodania dostawcy do sugestii',

    -- ── Klucze ──────────────────────────────────────────────────
    PRIMARY KEY (`id`),

    -- Ten sam dostawca nie może być sugerowany dwukrotnie dla tego samego surowca
    UNIQUE KEY `uk_omss_params_supplier` (`params_id`, `supplier_id`),

    KEY `idx_omss_params`   (`params_id`),
    KEY `idx_omss_supplier` (`supplier_id`),

    -- ── Klucze obce ─────────────────────────────────────────────
    CONSTRAINT `fk_omss_params`
        FOREIGN KEY (`params_id`)
        REFERENCES `open_material_params` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

    CONSTRAINT `fk_omss_supplier`
        FOREIGN KEY (`supplier_id`)
        REFERENCES `material_suppliers` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE

) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci
  COMMENT = 'Sugerowani dostawcy dla surowców OPEN – relacja params ↔ supplier';


-- ============================================================================
-- Query: pobierz parametry wszystkich surowców OPEN
-- Użycie: GET /materials/open-params
--
-- Zwraca jeden wiersz na surowiec OPEN.
-- Sugerowani dostawcy agregowani jako JSON array w kolumnie suggested_suppliers.
-- Wymaga: MySQL 5.7.22+ lub MariaDB 10.2.3+ (JSON_ARRAYAGG + JSON_OBJECT)
-- ============================================================================

SELECT
    omp.id,
    omp.material_id,
    m.name                              AS material_name,
    mc.name                             AS category_name,
    u.symbol                            AS unit_name,
    omp.recommended_purchase_price,
    omp.package_size,
    omp.package_description,
    omp.created_at,
    omp.updated_at,
    (
        SELECT JSON_ARRAYAGG(
            JSON_OBJECT(
                'id',   omss.supplier_id,
                'name', ms.name
            )
            ORDER BY omss.sort_order, omss.id
        )
        FROM  `open_material_suggested_suppliers` omss
        JOIN  `material_suppliers`                ms  ON ms.id = omss.supplier_id
        WHERE omss.params_id = omp.id
    )                                   AS suggested_suppliers
FROM       `open_material_params`   omp
JOIN       `materials`              m   ON m.id  = omp.material_id
LEFT JOIN  `material_categories`    mc  ON mc.id = m.id_category
LEFT JOIN  `units`                  u   ON u.id  = m.id_unit
ORDER BY
    mc.name, m.name;


-- ============================================================================
-- Query: pobierz parametry jednego surowca OPEN
-- Użycie: GET /materials/{id}/open-params
-- ============================================================================

SELECT
    omp.id,
    omp.material_id,
    m.name                              AS material_name,
    mc.name                             AS category_name,
    u.symbol                            AS unit_name,
    omp.recommended_purchase_price,
    omp.package_size,
    omp.package_description,
    omp.created_at,
    omp.updated_at,
    (
        SELECT JSON_ARRAYAGG(
            JSON_OBJECT(
                'id',   omss.supplier_id,
                'name', ms.name
            )
            ORDER BY omss.sort_order, omss.id
        )
        FROM  `open_material_suggested_suppliers` omss
        JOIN  `material_suppliers`                ms  ON ms.id = omss.supplier_id
        WHERE omss.params_id = omp.id
    )                                   AS suggested_suppliers
FROM       `open_material_params`   omp
JOIN       `materials`              m   ON m.id  = omp.material_id
LEFT JOIN  `material_categories`    mc  ON mc.id = m.id_category
LEFT JOIN  `units`                  u   ON u.id  = m.id_unit
WHERE omp.material_id = :material_id;


-- ============================================================================
-- Query: upsert parametrów surowca OPEN
-- Użycie: POST /materials/{id}/open-params
-- ============================================================================

INSERT INTO `open_material_params`
    (material_id, recommended_purchase_price, package_size, package_description, created_by)
VALUES
    (:material_id, :recommended_purchase_price, :package_size, :package_description, :user_id)
ON DUPLICATE KEY UPDATE
    recommended_purchase_price = VALUES(recommended_purchase_price),
    package_size               = VALUES(package_size),
    package_description        = VALUES(package_description),
    updated_by                 = VALUES(created_by),
    updated_at                 = CURRENT_TIMESTAMP;


-- ============================================================================
-- Query: synchronizacja sugerowanych dostawców
-- Użycie: po upsert params — usuń stare, wstaw nowe
--
-- Krok 1: usuń wszystkich sugerowanych dostawców dla danego rekordu
-- ============================================================================

DELETE FROM `open_material_suggested_suppliers`
WHERE params_id = (
    SELECT id FROM `open_material_params` WHERE material_id = :material_id
);

-- Krok 2: wstaw nowych (wykonaj dla każdego dostawcy z listy)

INSERT INTO `open_material_suggested_suppliers`
    (params_id, supplier_id, sort_order)
VALUES
    (
        (SELECT id FROM `open_material_params` WHERE material_id = :material_id),
        :supplier_id,
        :sort_order   -- 0, 1, 2 ... kolejność z frontendu
    );


-- ============================================================================
-- Query: usuń parametry surowca OPEN
-- Użycie: DELETE /materials/{id}/open-params
-- CASCADE usuwa automatycznie open_material_suggested_suppliers
-- ============================================================================

DELETE FROM `open_material_params`
WHERE material_id = :material_id;

