--source ../include/have_columnstore.inc
--disable_warnings
DROP DATABASE IF EXISTS mcol_5932;
--enable_warnings

CREATE DATABASE mcol_5932;
USE mcol_5932;

CREATE TABLE `fact_de_odvcan_11600` (
    `REGODV_ID` INT(10) UNSIGNED DEFAULT NULL,
    `HSTOPH_ID` INT(11) DEFAULT NULL,
    `ATC4PH_ID` INT(11) DEFAULT NULL,
    `PZN_ID` INT(10) UNSIGNED DEFAULT NULL,
    `WERTART_ID` SMALLINT(5) UNSIGNED DEFAULT NULL,
    `NFC3_ID` SMALLINT(5) UNSIGNED DEFAULT NULL,
    `SUB_ID` SMALLINT(5) UNSIGNED DEFAULT NULL,
    `VAL_25_101` INT(11) DEFAULT NULL,
    `VAL_25_102` INT(11) DEFAULT NULL,
    `VAL_25_430` DECIMAL(12, 3) DEFAULT NULL,
    `VAL_25_103` INT(11) DEFAULT NULL,
    `VAL_25_104` INT(11) DEFAULT NULL,
    `VAL_25_105` INT(11) DEFAULT NULL,
    `ZE` DECIMAL(10, 3) DEFAULT NULL,
    `ZE_CI` DECIMAL(13, 4) DEFAULT NULL,
    `DDD` DECIMAL(10, 3) DEFAULT NULL,
    `VM` DECIMAL(25, 17) DEFAULT NULL
) ENGINE=Columnstore DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

INSERT INTO `fact_de_odvcan_11600` (
    `REGODV_ID`, `HSTOPH_ID`, `ATC4PH_ID`, `PZN_ID`, `WERTART_ID`, `NFC3_ID`, `SUB_ID`,
    `VAL_25_101`, `VAL_25_102`, `VAL_25_430`, `VAL_25_103`, `VAL_25_104`, `VAL_25_105`,
    `ZE`, `ZE_CI`, `DDD`, `VM`
) VALUES
(105904, 9873, 12345, 54321, 1, 178, 26037, 50, 60, 100.500, 70, 80, 90, 1.2, 3.4, 5.6, 0.1),
(100302, 12770, 12346, 54322, 1, 178, 26037, 55, 65, 200.750, 75, 85, 95, 2.3, 4.5, 6.7, 0.1),
(597802, 929, 12347, 54323, 1, 178, 26037, 60, 70, 300.250, 80, 90, 100, 3.4, 5.6, 7.8, 0.1),
(105907, 4665, 12348, 54324, 1, 178, 26037, 65, 75, 400.600, 85, 95, 105, 4.5, 6.7, 8.9, 0.1),
(106000, 9873, 12349, 54325, 1, 178, 26037, 70, 80, 500.900, 90, 100, 110, 5.6, 7.8, 9.1, 0.1),
(106001, 12770, 12350, 54326, 1, 178, 26037, 75, 85, 600.300, 95, 105, 115, 6.7, 8.9, 10.2, 0.1),
(106002, 4665, 12351, 54327, 1, 178, 26037, 80, 90, 700.150, 100, 110, 120, 7.8, 9.1, 11.3, 0.1),
(106003, 929, 12352, 54328, 1, 178, 26037, 85, 95, 800.750, 105, 115, 125, 8.9, 10.2, 12.4, 0.1);

CREATE ALGORITHM = UNDEFINED
DEFINER = `root`@`localhost`
SQL SECURITY DEFINER
VIEW `v_fact_de_odvcan_11600` AS
SELECT
    `REGODV_ID`, `ATC4PH_ID`, `HSTOPH_ID`, `PZN_ID`, `VAL_25_430`,
    `VAL_25_430` * `VAL_25_101` / 100 AS `VAL_25_5430`,
    `VAL_25_430` * `VAL_25_102` / 100 AS `VAL_25_5431`,
    `VAL_25_430` * `ZE` AS `VAL_25_5432`,
    `VAL_25_430` * `DDD` AS `VAL_25_5433`,
    `VAL_25_430` * `VAL_25_104` / 100 AS `VAL_25_5434`,
    `VAL_25_430` * `VAL_25_105` / 100 AS `VAL_25_5435`,
    `VAL_25_430` * `VM` AS `VAL_25_5436`,
    `VAL_25_430` * `VAL_25_103` / 100 AS `VAL_25_5437`,
    `VAL_25_430` * `ZE_CI` AS `VAL_25_5790`,
    `VAL_25_430` - `VAL_25_430` + 1 AS `VAL_25_5999`,
    `WERTART_ID`, `NFC3_ID`, `SUB_ID`
FROM `fact_de_odvcan_11600`;

CREATE TABLE IF NOT EXISTS `group_f38275cd_db16_4e71_9306_c20af09d4c8b` (
    `key_id` BIGINT,
    `eg_id` VARCHAR(40)
) ENGINE=Columnstore;

INSERT INTO `group_f38275cd_db16_4e71_9306_c20af09d4c8b`
VALUES(105904, '$1'),
(100302, '$1'),
(597802, '$1'),
(106000, '$1'),
(106001, '$1'),
(105907, '$1'),
(106002, '$1'),
(106003, '$1');

CREATE TABLE IF NOT EXISTS `group_6b1d15de_368d_4239_a5df_09a2ca386b97` (
    `key_id` BIGINT,
    `eg_id` VARCHAR(40)
) ENGINE=Columnstore;

INSERT INTO `group_6b1d15de_368d_4239_a5df_09a2ca386b97`
VALUES(105904, '$1'),
(100302, '$1'),
(105907, '$1'),
(106000, '$1'),
(106001, '$1'),
(597802, '$1'),
(106002, '$1'),
(106003, '$1');

CREATE TABLE IF NOT EXISTS `group_7d5e323d_69ab_413e_817b_2fde051ec00c` (
    `key_id` BIGINT,
    `eg_id` VARCHAR(40)
) ENGINE=Columnstore;

INSERT INTO `group_7d5e323d_69ab_413e_817b_2fde051ec00c`
VALUES(100302, '$1'),
(105502, '$1'),
(105904, '$1'),
(106000, '$1'),
(106001, '$1'),
(105907, '$1'),
(597802, '$1'),
(106003, '$1');

SELECT
    CASE WHEN v.`SUB_ID` IN (26037) THEN '$1' ELSE 'REST' END AS `group1`,
    CASE WHEN v.`SUB_ID` IN (26037) THEN '$1' ELSE 'REST' END AS `group2`,
    v.`ATC4PH_ID`, v.`HSTOPH_ID`, v.`PZN_ID`,
    COALESCE(g0.`eg_id`, 'REST') AS `group3`,
    COALESCE(g1.`eg_id`, 'REST') AS `group4`,
    COALESCE(g2.`eg_id`, 'REST') AS `group5`,
 SUM(`VAL_25_430`) AS `VALUE_2025_01_M_430_1_1`,
 SUM(`VAL_25_5432`) AS `VALUE_2025_01_M_5432_1_2`,
 SUM(`VAL_25_5430`) AS `VALUE_2025_01_M_5430_1_3`
FROM `v_fact_de_odvcan_11600` AS v
JOIN `group_f38275cd_db16_4e71_9306_c20af09d4c8b` AS g0 ON g0.`key_id` = v.`REGODV_ID`
JOIN `group_6b1d15de_368d_4239_a5df_09a2ca386b97` AS g1 ON g1.`key_id` = v.`REGODV_ID`
JOIN `group_7d5e323d_69ab_413e_817b_2fde051ec00c` AS g2 ON g2.`key_id` = v.`REGODV_ID`
WHERE v.`WERTART_ID` IN (1)
    AND v.`NFC3_ID` IN (178)
    AND v.`SUB_ID` IN (26037)
    AND v.`HSTOPH_ID` IN (9873, 12770, 929, 4665)
    GROUP BY `group1`, `group2`, `ATC4PH_ID`, `HSTOPH_ID`, `PZN_ID`, `group3`, `group4`, `group5`
ORDER BY ATC4PH_ID
LIMIT 1000000;

DROP TABLE IF EXISTS `group_f38275cd_db16_4e71_9306_c20af09d4c8b`;
DROP TABLE IF EXISTS `group_6b1d15de_368d_4239_a5df_09a2ca386b97`;
DROP TABLE IF EXISTS `group_7d5e323d_69ab_413e_817b_2fde051ec00c`;

DROP DATABASE mcol_5932;