-- ============================================================
-- Migracja 015: Etap 11 - Pakiet 2 (P0)
-- Taksonomia realizacji + indeksy pod filtrowanie
-- ============================================================


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

SET @sql := IF(
    @has_realization_type = 0,
    "ALTER TABLE `realizations`
        ADD COLUMN `realization_type` ENUM('elewacje','tarasy','dachy','ogrody')
        NOT NULL DEFAULT 'elewacje'
        AFTER `location_text`",
    'DO 0'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

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

SET @sql := IF(
    @has_idx_type_pub = 0,
    "ALTER TABLE `realizations`
        ADD INDEX `idx_realizations_type_published` (`realization_type`, `is_published`, `created_at`)",
    'DO 0'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Backfill typow dla rekordow historycznych.
-- Priorytet reguly:
-- 1) tarasy
-- 2) dachy
-- 3) ogrody
-- 4) elewacje (fallback)
UPDATE `realizations`
SET `realization_type` = CASE
    WHEN LOWER(CONCAT_WS(' ', `slug`, `title`, `excerpt`, `location_text`)) REGEXP 'taras|pomost|deck' THEN 'tarasy'
    WHEN LOWER(CONCAT_WS(' ', `slug`, `title`, `excerpt`, `location_text`)) REGEXP 'dach|podbit|wiezb|lata|kontrlat|okap' THEN 'dachy'
    WHEN LOWER(CONCAT_WS(' ', `slug`, `title`, `excerpt`, `location_text`)) REGEXP 'ogrod|altan|plac-zabaw|buda|toalet|drewut|wiat' THEN 'ogrody'
    ELSE 'elewacje'
END;

