for https://github.com/DFE-Digital/manage-courses-api

manage-courses-test-users.sql

-- https://gist.github.com/timabell/b501a4ef5c6fddebe629c15f002cc649
-- for the database generated by https://github.com/DFE-Digital/manage-courses-api
SELECT * FROM mc_user WHERE email like '%abell%';
SELECT * FROM mc_organisation_user WHERE email like '%abell%';

-- all the related tables
select u.email, ui.inst_full, *
from mc_user u, mc_organisation_user ou, mc_organisation_institution oi, ucas_institution ui
where
	--ou.org_id = '5606'
	u.email like '%.abell%'
	and u.email = ou.email
	and ou.org_id = oi.org_id
	and oi.institution_code = ui.inst_code
order by u.email, ui.inst_code
;

-- child data from user, including orgs, institutions and course counts
select
	u.id userid, 
	u.email,
	o.id orgid, o.name orgname,
	i.inst_code,
	(select count(*) qty from ucas_course c where c.inst_code = i.inst_code) coursecount,
	i.*
from mc_user u
left outer join mc_organisation_user ou on ou.email = u.email
left outer join mc_organisation o on o.org_id = ou.org_id
left outer join mc_organisation_institution oi on oi.org_id = o.org_id
left outer join ucas_institution i on i.inst_code = oi.institution_code
where u.email like '%abell%'
order by u.email, i.inst_full;


-- 4) single user, single org, 2x inst
INSERT INTO mc_organisation_user (email, org_id) VALUES ('tim.abell+4@digital.education.gov.uk', '10169');
--delete from mc_organisation_user where email = 'tim.abell+4@digital.education.gov.uk' and org_id = '10169';

-- 5) mismatched case, 1 org, 1 inst, 10 courses
insert into mc_user(email, first_name, last_name) values('Tim.abell+5@digital.education.gov.uk', 'Tim5', 'Abell');
INSERT INTO mc_organisation_user (email, org_id) VALUES ('Tim.abell+5@digital.education.gov.uk', '10169');

-- 6) no sign-in user, no org mapping
insert into mc_user(email, first_name, last_name) values('tim.abell+6@digital.education.gov.uk', 'Tim10', 'Abell');

-- 8) no org mapping
insert into mc_user(email, first_name, last_name) values('tim.abell+8@digital.education.gov.uk', 'Tim10', 'Abell');

-- 9) no mc_user
---- nothing to do

-- 10) multiple orgs - single user, 2 orgs, 3 inst, 12 courses
insert into mc_user(email, first_name, last_name) values('tim.abell+10@digital.education.gov.uk', 'Tim10', 'Abell');
INSERT INTO mc_organisation_user (email, org_id) VALUES ('tim.abell+10@digital.education.gov.uk', '5606');
INSERT INTO mc_organisation_user (email, org_id) VALUES ('tim.abell+10@digital.education.gov.uk', '10169');

-- 11) dupe mc_users entry, different on case, no orgs
insert into mc_user(email, first_name, last_name) values('tim.abell+11@digital.education.gov.uk', 'Tim10', 'Abell');
insert into mc_user(email, first_name, last_name) values('TIM.abell+11@digital.education.gov.uk', 'TIM10', 'Abell');