-- ============================================================
-- Migracja 016: Etap 12 - Pakiet 3 (P0)
-- ContentOps: workflow redakcyjny szkic -> review SEO -> publikacja
-- ============================================================


-- categories.content_workflow
SET @has_categories_workflow := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'categories'
      AND COLUMN_NAME = 'content_workflow'
);

SET @sql := IF(
    @has_categories_workflow = 0,
    "ALTER TABLE `categories`
        ADD COLUMN `content_workflow` ENUM('draft','seo_review','published')
        NOT NULL DEFAULT 'draft'
        AFTER `show_on_home`",
    'DO 0'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @has_idx_categories_workflow := (
    SELECT COUNT(*)
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'categories'
      AND INDEX_NAME = 'idx_categories_content_workflow'
);

SET @sql := IF(
    @has_idx_categories_workflow = 0,
    "ALTER TABLE `categories`
        ADD INDEX `idx_categories_content_workflow` (`content_workflow`, `is_visible`)",
    'DO 0'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- products.content_workflow
SET @has_products_workflow := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'products'
      AND COLUMN_NAME = 'content_workflow'
);

SET @sql := IF(
    @has_products_workflow = 0,
    "ALTER TABLE `products`
        ADD COLUMN `content_workflow` ENUM('draft','seo_review','published')
        NOT NULL DEFAULT 'draft'
        AFTER `status`",
    'DO 0'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @has_idx_products_workflow := (
    SELECT COUNT(*)
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'products'
      AND INDEX_NAME = 'idx_products_content_workflow'
);

SET @sql := IF(
    @has_idx_products_workflow = 0,
    "ALTER TABLE `products`
        ADD INDEX `idx_products_content_workflow` (`content_workflow`, `status`)",
    'DO 0'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- services.content_workflow
SET @has_services_workflow := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'services'
      AND COLUMN_NAME = 'content_workflow'
);

SET @sql := IF(
    @has_services_workflow = 0,
    "ALTER TABLE `services`
        ADD COLUMN `content_workflow` ENUM('draft','seo_review','published')
        NOT NULL DEFAULT 'draft'
        AFTER `is_published`",
    'DO 0'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @has_idx_services_workflow := (
    SELECT COUNT(*)
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'services'
      AND INDEX_NAME = 'idx_services_content_workflow'
);

SET @sql := IF(
    @has_idx_services_workflow = 0,
    "ALTER TABLE `services`
        ADD INDEX `idx_services_content_workflow` (`content_workflow`, `is_published`)",
    'DO 0'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- realizations.content_workflow
SET @has_realizations_workflow := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'realizations'
      AND COLUMN_NAME = 'content_workflow'
);

SET @sql := IF(
    @has_realizations_workflow = 0,
    "ALTER TABLE `realizations`
        ADD COLUMN `content_workflow` ENUM('draft','seo_review','published')
        NOT NULL DEFAULT 'draft'
        AFTER `is_published`",
    'DO 0'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @has_idx_realizations_workflow := (
    SELECT COUNT(*)
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'realizations'
      AND INDEX_NAME = 'idx_realizations_content_workflow'
);

SET @sql := IF(
    @has_idx_realizations_workflow = 0,
    "ALTER TABLE `realizations`
        ADD INDEX `idx_realizations_content_workflow` (`content_workflow`, `is_published`)",
    'DO 0'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Backfill zgodny z aktualnym stanem publikacji.
UPDATE `categories`
SET `content_workflow` = CASE
    WHEN `content_workflow` = 'seo_review' THEN 'seo_review'
    WHEN `is_visible` = 1 THEN 'published'
    ELSE 'draft'
END;

UPDATE `products`
SET `content_workflow` = CASE
    WHEN `content_workflow` = 'seo_review' THEN 'seo_review'
    WHEN `status` = 'published' THEN 'published'
    ELSE 'draft'
END;

UPDATE `services`
SET `content_workflow` = CASE
    WHEN `content_workflow` = 'seo_review' THEN 'seo_review'
    WHEN `is_published` = 1 THEN 'published'
    ELSE 'draft'
END;

UPDATE `realizations`
SET `content_workflow` = CASE
    WHEN `content_workflow` = 'seo_review' THEN 'seo_review'
    WHEN `is_published` = 1 THEN 'published'
    ELSE 'draft'
END;
