-- ============================================================
-- Migracja 007: Usługi - zdjęcia procesu i powiązane produkty
-- Tartak Dana CMS - Etap 4
-- Kompatybilność: MySQL 8.0
-- ============================================================

SET NAMES utf8mb4;

-- -----------------------------------------------------------
-- Relacje usługa -> produkt (many-to-many)
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `service_products` (
    `service_id` INT UNSIGNED NOT NULL,
    `product_id` INT UNSIGNED NOT NULL,
    `sort_order` INT NOT NULL DEFAULT 0,
    PRIMARY KEY (`service_id`, `product_id`),
    INDEX `idx_service_products_sort` (`service_id`, `sort_order`, `product_id`),
    CONSTRAINT `fk_service_products_service` FOREIGN KEY (`service_id`)
        REFERENCES `services`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_service_products_product` FOREIGN KEY (`product_id`)
        REFERENCES `products`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- Media: dodanie typu encji `service`
-- -----------------------------------------------------------
SET @media_entity_type := (
    SELECT COLUMN_TYPE
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'media'
      AND COLUMN_NAME = 'entity_type'
    LIMIT 1
);

SET @needs_service_enum := IF(
    @media_entity_type IS NOT NULL AND LOCATE("'service'", @media_entity_type) = 0,
    1,
    0
);

SET @sql := IF(
    @needs_service_enum = 1,
    "ALTER TABLE `media` MODIFY `entity_type` ENUM('product','realization','category','page','service') NOT NULL",
    'DO 0'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
