-- ------------------------------------------------
-- Add id's to um_user, um_role, and um_tcode.
-- Alter um_user_role and um_role_tcode to use ids.
-- Created 20121115
-- ------------------------------------------------

ALTER TABLE `um_user` 
  ADD COLUMN `id` INT NOT NULL AUTO_INCREMENT  FIRST 
, DROP PRIMARY KEY 
, ADD PRIMARY KEY (`id`) 
, ADD UNIQUE INDEX `sys_cli_user` (`system` ASC, `client` ASC, `user` ASC) ;

ALTER TABLE `um_role` 
  ADD COLUMN `id` INT NOT NULL AUTO_INCREMENT  FIRST 
, DROP PRIMARY KEY 
, ADD PRIMARY KEY (`id`) 
, ADD UNIQUE INDEX `sys_cli_role` (`system` ASC, `client` ASC, `role` ASC) ;

ALTER TABLE `um_tcode` 
  ADD COLUMN `id` INT NOT NULL AUTO_INCREMENT  FIRST 
, DROP PRIMARY KEY 
, ADD PRIMARY KEY (`id`) 
, ADD UNIQUE INDEX `sys_cli_tcode` (`system` ASC, `client` ASC, `tcode` ASC) ;

ALTER TABLE `um_user_role` 
  ADD COLUMN `user_id` INT NULL  AFTER `role` 
, ADD COLUMN `role_id` INT NULL  AFTER `user_id` 
, ADD UNIQUE INDEX `user_role` (`user_id` ASC, `role_id` ASC) ;

UPDATE um_user_role AS ur
  SET user_id = (SELECT id FROM um_user AS u WHERE u.system=ur.system AND u.client=ur.client AND u.user=ur.user)
, role_id = (SELECT id FROM um_role AS r WHERE r.system=ur.system AND r.client=ur.client AND r.role=ur.role);

ALTER TABLE `um_user_role`
  DROP COLUMN `role` 
, DROP COLUMN `user` 
, DROP COLUMN `client` 
, DROP COLUMN `system` 
, CHANGE COLUMN `user_id` `user_id` INT(11) NOT NULL  
, CHANGE COLUMN `role_id` `role_id` INT(11) NOT NULL  
, DROP PRIMARY KEY 
, ADD PRIMARY KEY (`user_id`, `role_id`) 
, DROP INDEX `user_role` ;

ALTER TABLE `um_role_tcode` 
  ADD COLUMN `role_id` INT NULL  AFTER `delete_flag` 
, ADD COLUMN `tcode_id` INT NULL  AFTER `role_id` 
, ADD UNIQUE INDEX `role_tcode` (`role_id` ASC, `tcode_id` ASC) ;

UPDATE um_role_tcode AS rt
  SET tcode_id = (SELECT id FROM um_tcode AS t WHERE t.system=rt.system AND t.client=rt.client AND t.tcode=rt.tcode)
, role_id = (SELECT id FROM um_role AS r WHERE r.system=rt.system AND r.client=rt.client AND r.role=rt.role);

INSERT INTO um_tcode (um_tcode.system, client, tcode)
  SELECT um_role_tcode.system, client, tcode 
  FROM um_role_tcode
  WHERE tcode_id IS NULL
  GROUP BY um_role_tcode.system, client, tcode;
  
INSERT INTO um_role (um_role.system, client, role)
  SELECT um_role_tcode.system, client, role
  FROM um_role_tcode
  WHERE role_id IS NULL
  GROUP BY um_role_tcode.system, client, role;
  
UPDATE um_role_tcode AS rt
  SET tcode_id = (SELECT id FROM um_tcode AS t WHERE t.system=rt.system AND t.client=rt.client AND t.tcode=rt.tcode)
  WHERE tcode_id IS NULL;
  
UPDATE um_role_tcode AS rt
  SET role_id = (SELECT id FROM um_role AS r WHERE r.system=rt.system AND r.client=rt.client AND r.role=rt.role)
  WHERE role_id IS NULL;

ALTER TABLE `um_role_tcode` 
  DROP COLUMN `tcode` 
, DROP COLUMN `role` 
, DROP COLUMN `client` 
, DROP COLUMN `system` 
, CHANGE COLUMN `delete_flag` `delete_flag` TINYINT(1) NOT NULL DEFAULT '0'  AFTER `tcode_id` 
, CHANGE COLUMN `role_id` `role_id` INT(11) NOT NULL  
, CHANGE COLUMN `tcode_id` `tcode_id` INT(11) NOT NULL  
, DROP PRIMARY KEY 
, ADD PRIMARY KEY (`role_id`, `tcode_id`) 
, DROP INDEX `role_tcode` ;
