-- ------------------------------------------ --
-- APM-1627: APM restrictions: when a tcode
-- restriction is deleted, its values are
-- not shown when "Show deleted restrictions"
-- is selected. RE-MAKE with new GUI.
-- ------------------------------------------ --
CREATE TABLE IF NOT EXISTS `tc_restrict_temp` (
  `id` int NOT NULL AUTO_INCREMENT,
  `system` char(3) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `client` char(3) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `project` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,

  `class` varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `object` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `field` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `low` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `high` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  
  `comment` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,

  `audit_user` varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `audit_system` varchar(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `audit_client` varchar(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `audit_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
SELECT	DISTINCT(tc.id) AS `id`, tc.system AS `system`, tc.client AS `client`, `project`,
		`class`, `object`, `field`, 
        `low`, `high`,
		tc.comment AS `comment`, tc.status AS `status`,
		tc.audit_user AS `audit_user`, tc.audit_system AS `audit_system`, tc.audit_client AS `audit_client`,
		tc.audit_timestamp AS `audit_timestamp`
FROM `tc_restrict` AS tc
LEFT JOIN `tc_restrict_values` AS tc_val ON 
	tc_val.restrict_id = tc.id AND 
	tc_val.status = tc.status
WHERE tc.status IN ('A', 'I')
GROUP BY tc.id
ORDER BY tc.status, tc.audit_timestamp DESC;

CREATE TABLE IF NOT EXISTS `tc_restrict_applied_temp` (
  `issue_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `tc_restrict_id` int NOT NULL,
  PRIMARY KEY (`issue_id`, `tc_restrict_id`)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO tc_restrict_applied_temp (`issue_id`, `tc_restrict_id`)
SELECT `issue_id`, `restrict_id` AS `tc_restrict_id`
FROM `tc_restrict_applied`;

DROP TABLE IF EXISTS `tc_restrict`;
DROP TABLE IF EXISTS `tc_restrict_values`;
DROP TABLE IF EXISTS `tc_restrict_applied`;

ALTER TABLE tc_restrict_temp RENAME tc_restrict;
ALTER TABLE tc_restrict_applied_temp RENAME tc_restrict_applied;
