DROP TABLE IF EXISTS `email_contacts_new`;
CREATE TABLE `email_contacts_new` (
	`id` SMALLINT(6) NOT NULL AUTO_INCREMENT,
	`display_name` VARCHAR(255) NOT NULL DEFAULT '',
	`referral_source_id` SMALLINT(6) NOT NULL DEFAULT '0',
	`type` ENUM('mdt','report') NOT NULL,
	`scope` ENUM('organisation','hospital') NOT NULL,
	`department_id` SMALLINT(6) NULL DEFAULT NULL,
	`contact_address` VARCHAR(255) NOT NULL DEFAULT '',
	`status` ENUM('all','new') NOT NULL DEFAULT 'all',
	`is_active` ENUM('yes','no') NOT NULL DEFAULT 'yes',
    `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, /* set on-update later */
	PRIMARY KEY (`id`),
	UNIQUE INDEX `source_type_address` (`referral_source_id`, `type`, `contact_address`),
	INDEX `contact_address` (`contact_address`),
	INDEX `referral_source_id` (`referral_source_id`),
	INDEX `department_id` (`department_id`), 
    /* email_contacts_ibfk_1 belongs to old email_contacts table: */
	CONSTRAINT `email_contacts_ibfk_2` FOREIGN KEY (`referral_source_id`) REFERENCES `referral_sources` (`id`),
	CONSTRAINT `email_contacts_ibfk_3` FOREIGN KEY (`department_id`) REFERENCES `hospital_departments` (`id`)
)	
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;

INSERT INTO email_contacts_new(display_name,referral_source_id,type,scope,
        department_id,contact_address,`status`,is_active,timestamp)
	SELECT display_name,referral_source_id,type,IF(scope = 'department','hospital',scope),
        IF(scope = 'department',824,NULL),contact_address,'all',is_active,timestamp 
	FROM email_contacts 
	WHERE is_active = 'yes' or display_name RLIKE 'CWT'; /* Scarborough (CWT) is now inactive */
	
UPDATE email_contacts_new SET status = 'new' WHERE display_name RLIKE 'CWT';

ALTER TABLE `email_contacts_new`
	CHANGE COLUMN `timestamp` `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 
        ON UPDATE CURRENT_TIMESTAMP AFTER `is_active`;

/* check organisations correspond to expected */
select ec.display_name, po.description
from email_contacts_new ec
	join referral_sources rs on ec.referral_source_id = rs.id
	join parent_organisations po on rs.parent_organisation_id = po.id
where ec.scope = 'organisation'
order by ec.display_name;

/* rename tables after above check */
RENAME TABLE `email_contacts` TO `_email_contacts`;
RENAME TABLE `email_contacts_new` TO `email_contacts`;