-- -------------------------------------------------------
-- Creating new table for holding data about Locations
-- used in derived controls
-- -------------------------------------------------------
	CREATE TABLE IF NOT EXISTS `ic_location` (
	  `id` INT NOT NULL AUTO_INCREMENT,
	  `system` VARCHAR(8) NOT NULL,
	  `client` VARCHAR(3) NOT NULL,
	  `name` VARCHAR(64) NOT NULL,
	  `description` TEXT NULL DEFAULT NULL,
	PRIMARY KEY (`id`));

-- ----------------------------------------------------
-- Table to pair locations with controls
-- ----------------------------------------------------
	CREATE TABLE IF NOT EXISTS `ic_control_location` (
	  `control_id` INT NOT NULL,
	  `location_id` INT NOT NULL DEFAULT '0',
	PRIMARY KEY (`control_id`,`location_id`));

-- ---------------------------------------------------------------
-- Changes to pair executers and approvers with a control location
-- ---------------------------------------------------------------
	ALTER TABLE `ic_control_approvers` 
		ADD COLUMN `location_id` INT NOT NULL DEFAULT '0' AFTER `control_id`,
		DROP PRIMARY KEY,
	ADD PRIMARY KEY (`control_id`, `approver_id`, `location_id`);
	
	ALTER TABLE `ic_control_executers`
		ADD COLUMN `location_id` INT NOT NULL DEFAULT '0' AFTER `control_id`,
		DROP PRIMARY KEY,
	ADD PRIMARY KEY (`control_id`, `executer_id`, `location_id`);

-- --------------------------------------------------------------------
-- New table to hold potentiel extra information about control_location
-- --------------------------------------------------------------------
	CREATE TABLE IF NOT EXISTS `ic_location_extrainformation` (
	  `control_id` INT NOT NULL,
	  `location_id` INT NOT NULL DEFAULT '0',
	  `system_location_id` INT NULL DEFAULT NULL,
	  `information` TEXT NULL DEFAULT NULL,
	PRIMARY KEY (`control_id`, `location_id`));
	
-- ----------------------------------------------------------------------
-- Change table for approvers to potentially only be used in one location
-- ----------------------------------------------------------------------
	ALTER TABLE `ic_approvers` 
	ADD COLUMN `location_id` INT NOT NULL DEFAULT '0' AFTER `client`;
	
-- ----------------------------------------------------------------------
-- Change table for locations to potentially only be used in one location
-- ----------------------------------------------------------------------
	ALTER TABLE `ic_system_location` 
	ADD COLUMN `location_id` INT NOT NULL DEFAULT '0' AFTER `client`;

	
