use lims_test; /* change as needed */
/* ==========================================================================================================
	FOR lims_test ONLY!!! */
INSERT INTO lims_test.hospital_departments (id, display_name) VALUES(312, 'Clinical Cytogenetics & Molecular Genetics');
INSERT INTO lims_test.referral_types(description,`prefix`,default_unknown) VALUES ('scientist', 'CS', 'CS999998');
/* ======================================================================================================== */

SELECT @hd_id := id FROM hospital_departments WHERE display_name = 'Clinical Cytogenetics & Molecular Genetics';
SELECT @src_ref_type_id := id FROM referral_types WHERE description = 'hospital';
SELECT @cs_ref_type_id := id FROM referral_types WHERE description = 'scientist';

INSERT INTO `parent_organisations` (`parent_code`, `description`, `referral_type_id`) VALUES 
	('GENEQN', 'GENEQ NEWCASTLE', @src_ref_type_id),
	('GENEQS', 'GENEQ SHEFFIELD', @src_ref_type_id),
	('GENEQL', 'GENEQ LEEDS',     @src_ref_type_id);

/* Newcastle */
SELECT @po_id := id FROM parent_organisations WHERE parent_code = 'GENEQN';
INSERT INTO `referral_sources` (`display_name`, `organisation_code`, `parent_organisation_id`, `referral_type_id`) 
	VALUES ('GeNEQ Newcastle', 'GENEQN', @po_id, @src_ref_type_id);
INSERT INTO `referrers` (`name`, `national_code`, `referral_type_id`) 
	VALUES ('Cuthbert GD', 'CS00522', @cs_ref_type_id);
SELECT @ref_id := id FROM referrers WHERE national_code = 'CS00522';
INSERT INTO `referrer_department` (`referrer_id`, `parent_organisation_id`, `hospital_department_code`) 
	VALUES (@ref_id, @po_id, @hd_id);
	
/* Sheffield */
SELECT @po_id := id FROM parent_organisations WHERE parent_code = 'GENEQS';
INSERT INTO `referral_sources` (`display_name`, `organisation_code`, `parent_organisation_id`, `referral_type_id`) 
	VALUES ('GeNEQ Sheffield', 'GENEQS', @po_id, @src_ref_type_id);
INSERT INTO `referrers` (`name`, `national_code`, `referral_type_id`) 
	VALUES ('Pollitt R', 'CS19566', @cs_ref_type_id);
SELECT @ref_id := id FROM referrers WHERE national_code = 'CS19566';
INSERT INTO `referrer_department` (`referrer_id`, `parent_organisation_id`, `hospital_department_code`) 
	VALUES (@ref_id, @po_id, @hd_id);

/* Leeds */	
SELECT @po_id := id FROM parent_organisations WHERE parent_code = 'GENEQL';
INSERT INTO `referral_sources` (`display_name`, `organisation_code`, `parent_organisation_id`, `referral_type_id`) 
	VALUES ('GeNEQ Leeds', 'GENEQL', @po_id, @src_ref_type_id);
/* no referrer yet */