-- ------------------------------------------------
-- Changes to support Unused tcodes role blacklist
-- Created 20140319
-- ------------------------------------------------

ALTER TABLE `um_role`
  ADD COLUMN `dash_ignore_tcodes` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '0 = include role\'s tcodes in \"Unused tcodes\" and \"Allocated tcode\"\n1 = don\'t include'  AFTER `license_category_id`, 
  ADD INDEX `sys_cli_dash_ignore` (`system` ASC, `client` ASC, `dash_ignore_tcodes` ASC);

ALTER TABLE `um_role_tcode`
  ADD COLUMN `dash_ignore_tcodes` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '0 = include role\'s tcodes in \"Unused tcodes\" and \"Allocated tcode\"\n1 = don\'t include'  AFTER `client`, 
  ADD INDEX `tc_del_ignore` (`tcode_id` ASC, `um_delete` ASC, `dash_ignore_tcodes` ASC);

ALTER TABLE `um_dash_unused_tcodes_history`
  ADD COLUMN `count_allocated_tcodes` INT(10) NOT NULL AFTER `count_unused_tcodes`;

ALTER TABLE `um_dash_unused_tcodes_history` 
  CHANGE COLUMN `count_unused_tcodes` `count_unused_tcodes` INT(10) NOT NULL DEFAULT 0 , 
  CHANGE COLUMN `count_allocated_tcodes` `count_allocated_tcodes` INT(10) NOT NULL DEFAULT 0;

INSERT INTO um_dash_unused_tcodes_history (um_dash_unused_tcodes_history.system, client, date, count_allocated_tcodes)
  SELECT um_info.system, client, date(timestamp) date, substring_index(group_concat(n_tcodes ORDER BY timestamp DESC), ',', 1) count_allocated_tcodes
  FROM um_info
  GROUP BY um_info.system, client, date
ON DUPLICATE KEY UPDATE count_allocated_tcodes=VALUES(count_allocated_tcodes);
