-- ============================================================================
-- Tabela: supplier_catalog_product_mappings
-- Opis: Mapowanie produktów katalogowych dostawców do surowców franczyzobiorców
-- ============================================================================

CREATE TABLE IF NOT EXISTS `supplier_catalog_product_mappings` (
    -- Klucz główny
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,

    -- Relacje
    `catalog_product_id` INT UNSIGNED NOT NULL COMMENT 'FK do supplier_catalog_products',
    `material_id` INT UNSIGNED NOT NULL COMMENT 'FK do materials (surowce franczyzobiorcy)',
    `supplier_id` INT UNSIGNED NOT NULL COMMENT 'FK do material_suppliers (denormalizacja dla wydajności)',

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

    -- Opcjonalne pola dla rozszerzonej funkcjonalności
    `conversion_factor` DECIMAL(10,4) DEFAULT 1.0000 COMMENT 'Współczynnik konwersji jednostek (jeśli potrzebny)',
    `notes` TEXT NULL COMMENT 'Opcjonalne notatki o mapowaniu',
    `is_active` TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'Czy mapowanie jest aktywne (soft delete)',

    -- Indeksy
    PRIMARY KEY (`id`),

    -- Unikalne mapowanie: jeden produkt katalogowy może być zmapowany tylko do jednego surowca
    UNIQUE KEY `uk_catalog_product_mapping` (`catalog_product_id`),

    -- Indeksy dla wydajności zapytań
    KEY `idx_material_id` (`material_id`),
    KEY `idx_supplier_id` (`supplier_id`),
    KEY `idx_active` (`is_active`),
    KEY `idx_supplier_material` (`supplier_id`, `material_id`),
    KEY `idx_created_at` (`created_at`),

    -- Klucze obce (Foreign Keys)
    CONSTRAINT `fk_mapping_catalog_product`
        FOREIGN KEY (`catalog_product_id`)
        REFERENCES `supplier_catalog_products` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

    CONSTRAINT `fk_mapping_material`
        FOREIGN KEY (`material_id`)
        REFERENCES `materials` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

    CONSTRAINT `fk_mapping_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='Mapowania produktów katalogowych dostawców do surowców';

-- ============================================================================
-- Przykładowe zapytania / Queries
-- ============================================================================

-- 1. Pobranie wszystkich mapowań dla dostawcy z dodatkowymi informacjami
-- (używane w kontrolerze: getBySupplier)
/*
SELECT
    m.id,
    m.catalog_product_id,
    m.material_id,
    m.supplier_id,
    m.created_at,
    m.updated_at,
    cp.sku as catalog_product_sku,
    cp.name as catalog_product_name,
    mat.name as material_name,
    mat.unit_name
FROM supplier_catalog_product_mappings m
INNER JOIN supplier_catalog_products cp ON m.catalog_product_id = cp.id
INNER JOIN materials mat ON m.material_id = mat.id
WHERE m.supplier_id = ?
  AND m.is_active = 1
ORDER BY cp.name;
*/

-- 2. Pobranie mapowania dla konkretnego produktu katalogowego
-- (używane w kontrolerze: getByCatalogProductId)
/*
SELECT
    m.*,
    cp.sku as catalog_product_sku,
    cp.name as catalog_product_name,
    mat.name as material_name
FROM supplier_catalog_product_mappings m
LEFT JOIN supplier_catalog_products cp ON m.catalog_product_id = cp.id
LEFT JOIN materials mat ON m.material_id = mat.id
WHERE m.catalog_product_id = ?
  AND m.is_active = 1;
*/

-- 3. Produkty katalogowe bez mapowania
-- (używane w kontrolerze: getUnmappedBySupplier)
/*
SELECT
    cp.id,
    cp.sku,
    cp.name,
    cp.package_size,
    cp.package_unit,
    cp.is_active
FROM supplier_catalog_products cp
LEFT JOIN supplier_catalog_product_mappings m
    ON cp.id = m.catalog_product_id AND m.is_active = 1
WHERE cp.supplier_id = ?
  AND m.id IS NULL
  AND cp.is_active = 1
ORDER BY cp.name;
*/

-- 4. Sprawdzenie czy materiał jest już użyty w mapowaniach danego dostawcy
/*
SELECT COUNT(*) as count
FROM supplier_catalog_product_mappings
WHERE supplier_id = ?
  AND material_id = ?
  AND is_active = 1;
*/

-- 5. Historia zmian mapowań (audit trail) - opcjonalne
-- Jeśli potrzebujesz historii zmian, można dodać tabelę audit:
/*
CREATE TABLE IF NOT EXISTS `supplier_catalog_product_mappings_audit` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `mapping_id` INT UNSIGNED NOT NULL,
    `action` ENUM('CREATE', 'UPDATE', 'DELETE') NOT NULL,
    `old_material_id` INT UNSIGNED NULL,
    `new_material_id` INT UNSIGNED NULL,
    `changed_by` INT UNSIGNED NULL,
    `changed_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `change_reason` VARCHAR(500) NULL,
    PRIMARY KEY (`id`),
    KEY `idx_mapping_id` (`mapping_id`),
    KEY `idx_changed_at` (`changed_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
*/

-- ============================================================================
-- Indeksy dodatkowe (opcjonalne, w zależności od potrzeb)
-- ============================================================================

-- Jeśli często wykonujesz zapytania typu "znajdź wszystkie mapowania dla materiału"
-- ALTER TABLE supplier_catalog_product_mappings
-- ADD KEY `idx_material_active` (`material_id`, `is_active`);

-- Jeśli często sprawdzasz mapowania według daty utworzenia
-- ALTER TABLE supplier_catalog_product_mappings
-- ADD KEY `idx_supplier_created` (`supplier_id`, `created_at`);

-- ============================================================================
-- Triggery (opcjonalne) - automatyczna denormalizacja supplier_id
-- ============================================================================

DELIMITER $$

CREATE TRIGGER `trg_before_insert_mapping`
BEFORE INSERT ON `supplier_catalog_product_mappings`
FOR EACH ROW
BEGIN
    -- Automatycznie pobierz supplier_id z catalog_product jeśli nie podano
    IF NEW.supplier_id IS NULL THEN
        SELECT supplier_id INTO NEW.supplier_id
        FROM supplier_catalog_products
        WHERE id = NEW.catalog_product_id;
    END IF;
END$$

DELIMITER ;

-- ============================================================================
-- Dane testowe (przykładowe)
-- ============================================================================

/*
-- Przykładowe mapowanie
INSERT INTO supplier_catalog_product_mappings
    (catalog_product_id, material_id, supplier_id, created_by)
VALUES
    (1, 45, 1, 1),  -- SKU: 1007503 "Abricot - 12⌀" → Morela suszona
    (2, 46, 1, 1),  -- SKU: 1002120 "Abricot - 22⌀" → Morela suszona duża
    (3, 47, 1, 1);  -- SKU: 1003221 "Abricot & Cerise - 22⌀" → Mix morela-wiśnia
*/

-- ============================================================================
-- Statystyki i raporty
-- ============================================================================

-- Statystyki mapowań dla dostawcy
/*
SELECT
    s.id as supplier_id,
    s.name as supplier_name,
    COUNT(DISTINCT cp.id) as total_products,
    COUNT(DISTINCT m.id) as mapped_products,
    COUNT(DISTINCT cp.id) - COUNT(DISTINCT m.id) as unmapped_products,
    ROUND(COUNT(DISTINCT m.id) * 100.0 / COUNT(DISTINCT cp.id), 2) as mapping_percentage
FROM material_suppliers s
LEFT JOIN supplier_catalog_products cp ON s.id = cp.supplier_id AND cp.is_active = 1
LEFT JOIN supplier_catalog_product_mappings m ON cp.id = m.catalog_product_id AND m.is_active = 1
WHERE s.id = ?
GROUP BY s.id, s.name;
*/

