DELIMITER $$

USE `rl_test`$$

CREATE OR REPLACE DEFINER=CURRENT_USER PROCEDURE `test`(IN param1 INT)
BEGIN

    CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `rl_test_managed_subcategories` AS with ms1 as (select `rl_test`.`rl_test_preferred_vendors`.`subcategory` AS `subcategory`,`rl_test`.`rl_test_preferred_vendors`.`start_date` AS `start_date`,`rl_test`.`rl_test_preferred_vendors`.`end_date` AS `end_date` from `rl_test`.`rl_test_preferred_vendors` group by 1,2,3), ms2 as (select `ms1`.`subcategory` AS `subcategory`,count(0) AS `num_records` from (select `rl_test`.`rl_test_preferred_vendors`.`subcategory` AS `subcategory`,`rl_test`.`rl_test_preferred_vendors`.`start_date` AS `start_date`,`rl_test`.`rl_test_preferred_vendors`.`end_date` AS `end_date` from `rl_test`.`rl_test_preferred_vendors` group by 1,2,3) `ms1` group by 1)select `ms1`.`subcategory` AS `subcategory`,`ms1`.`start_date` AS `start_date`,`ms1`.`end_date` AS `end_date`,1 AS `is_managed` from (`ms1` join `ms2` on(`ms1`.`subcategory` = `ms2`.`subcategory`)) where `ms2`.`num_records` > 1 union all select `ms1`.`subcategory` AS `subcategory`,`ms1`.`start_date` AS `start_date`,`ms1`.`end_date` AS `end_date`,1 AS `is_managed` from (((select `rl_test`.`rl_test_preferred_vendors`.`subcategory` AS `subcategory`,`rl_test`.`rl_test_preferred_vendors`.`start_date` AS `start_date`,`rl_test`.`rl_test_preferred_vendors`.`end_date` AS `end_date` from `rl_test`.`rl_test_preferred_vendors` group by 1,2,3)) `ms1` join (select `ms1`.`subcategory` AS `subcategory`,count(0) AS `num_records` from (select `rl_test`.`rl_test_preferred_vendors`.`subcategory` AS `subcategory`,`rl_test`.`rl_test_preferred_vendors`.`start_date` AS `start_date`,`rl_test`.`rl_test_preferred_vendors`.`end_date` AS `end_date` from `rl_test`.`rl_test_preferred_vendors` group by 1,2,3) `ms1` group by 1) `ms2` on(`ms1`.`subcategory` = `ms2`.`subcategory`)) where `ms2`.`num_records` = 1;

    set group_concat_max_len = 1000000;
    set @base_table = "client1spenddata_20191003";
    set @client_schema = "rl_test";
    set @spend_data_table = "rl_test1spenddata";

    SET @create_table_columns = (select GROUP_CONCAT(concat(COLUMN_NAME," ",COLUMN_TYPE, " Default ",COLUMN_DEFAULT))
    from information_schema.columns
    where table_schema = @client_schema
    and table_name = @base_table)
    ;

    SET @create_table = (select concat("CREATE TABLE ",@client_schema,".",@spend_data_table," (
    `year` int unsigned DEFAULT NULL,
    `month` int unsigned DEFAULT NULL,
    `is_preferred` tinyint(3) unsigned DEFAULT 0,
    `is_managed` tinyint(3) unsigned DEFAULT 0,
    `impactable` tinyint(4) DEFAULT 1,
    `include_in_savings` tinyint(4) DEFAULT 1,
    `lost_savings_rate` int(11) DEFAULT NULL,
    `enable_lost_savings` tinyint(4) DEFAULT 1,
    `default_savings_rate` int(11) DEFAULT NULL,
    `managed_spend_target_isg` int(11) DEFAULT NULL,
    `managed_spend_target_company` int(11) DEFAULT NULL,
    `enable_managed_spend_isg` tinyint(4) DEFAULT 1,
    `compliance_target_isg` int(11) DEFAULT NULL,
    `compliance_target_company` int(11) DEFAULT NULL,
    `enable_compliance_target_isg` tinyint(4) DEFAULT 1,
    ",@create_table_columns,"
    ) ENGINE=Columnstore DEFAULT CHARSET=utf8;"));

    set @droping = (select concat("drop table if exists ",@client_schema,".",@spend_data_table,";"));
    PREPARE drop_stmt FROM @droping;
    EXECUTE drop_stmt;
    DEALLOCATE PREPARE drop_stmt;

    PREPARE create_stmt FROM @create_table;
    EXECUTE create_stmt;
    DEALLOCATE PREPARE create_stmt;

    set @base_columns = (select GROUP_CONCAT(concat("sd.",COLUMN_NAME))
    from information_schema.columns
    where table_schema = @client_schema
    and table_name = @base_table);

    set @spend_data_columns = (
    select GROUP_CONCAT(COLUMN_NAME)
    from information_schema.columns
    where table_schema = @client_schema
    and table_name = @base_table
    );

    set @insert_stmt = (select concat("insert into ",@client_schema,".",@spend_data_table,"( ",@spend_data_columns,", `year`, `month`, is_preferred, is_managed,impactable,include_in_savings,lost_savings_rate,enable_lost_savings,default_savings_rate,managed_spend_target_isg,managed_spend_target_company,enable_managed_spend_isg,compliance_target_isg,compliance_target_company,enable_compliance_target_isg)
    select ",@base_columns,",year(date) as `sd.year`, month(date) as `sd.month`, pv.is_preferred, ms.is_managed,
    cs.impactable,
    cs.include_in_savings,
    cs.lost_savings_rate,
    cs.enable_lost_savings,
    cs.default_savings_rate,
    cs.managed_spend_target_isg,
    cs.managed_spend_target_company,
    cs.enable_managed_spend_isg,
    cs.compliance_target_isg,
    cs.compliance_target_company,
    cs.enable_compliance_target_isg
    from ",@client_schema,".",@base_table," sd
    left outer join ",@client_schema,".",@client_schema,"_preferred_vendors pv
    on sd.normalized_vendor_name = pv.normalized_vendor_name and
    sd.subcategory = pv.subcategory and
    sd.date between pv.start_date and pv.end_date
    left outer join ",@client_schema,".",@client_schema,"_managed_subcategories ms
    on sd.subcategory = ms.subcategory
    and sd.date between ms.start_date and ms.end_date
    left outer join ",@client_schema,".",@client_schema,"_company_settings cs
    on sd.subcategory = cs.subcategory
    "
    ));

    select @insert_stmt;

    PREPARE insert_stmts FROM @insert_stmt;
    EXECUTE insert_stmts;
    DEALLOCATE PREPARE insert_stmts;

END$$

DELIMITER ;

drop view rl_test_managed_subcategories;
call test(1);