/* script to delete one or more test Originators from Exchange 2.0 database */

/* comma separated list of long Originator Reference IDs */
SET @originator_reference_id_list =	'12102001799,12106001845,12106001855,12201001954,1642755491,12103001806,12103001803,12102001800';

/* get short organisation ID(s) for the selected Originator(s) using {long} Originator ID(s) */
SELECT @originator_id_list := GROUP_CONCAT(organisation_id) FROM organisation_details WHERE FIND_IN_SET(organisation_reference_id, @originator_reference_id_list);

/* get credebtor address id(s) */
SELECT @address_id_list_credebtors := GROUP_CONCAT( aid ) 
FROM (
	SELECT address_id as aid FROM debtors_detail WHERE debtor_id IN ( SELECT debtor_id FROM debtor_relation WHERE FIND_IN_SET(organisation_id, @originator_id_list) )
	UNION SElECT billing_address_id as aid FROM debtors_detail WHERE debtor_id IN ( SELECT debtor_id FROM debtor_relation WHERE FIND_IN_SET(organisation_id, @originator_id_list) )
	UNION SElECT contact_address_id as aid FROM debtors_detail WHERE debtor_id IN ( SELECT debtor_id FROM debtor_relation WHERE FIND_IN_SET(organisation_id, @originator_id_list) )
) as T;

/* delete credebtor address records */
DELETE FROM address_details WHERE FIND_IN_SET(address_id, @address_id_list_credebtors);

/* delete credebtor address records */
/* DELETE FROM address_details WHERE address_id IN (
	SElECT address_id FROM debtors_detail WHERE debtor_id IN 
	(
		SELECT debtor_id FROM debtor_relation WHERE organisation_id IN (@originator_id)
	) 
	UNION SElECT billing_address_id FROM debtors_detail WHERE debtor_id IN 
	(
		SELECT debtor_id FROM debtor_relation WHERE organisation_id IN (@originator_id)
	) 
	UNION SElECT contact_address_id FROM debtors_detail WHERE debtor_id IN 
	(
		SELECT debtor_id FROM debtor_relation WHERE organisation_id IN (@originator_id)
	)
);*/

/* delete credebtor contacts records */
DELETE FROM debtor_contacts WHERE debtor_id IN (
	SELECT debtor_id FROM debtor_relation WHERE FIND_IN_SET(organisation_id, @originator_id_list)
);

/* delete credebtor bank details records */
DELETE FROM debtor_bank_details WHERE debtor_id IN (
	SELECT debtor_id FROM debtor_relation WHERE FIND_IN_SET(organisation_id, @originator_id_list)
);

/* delete credebtor details records */
DELETE FROM debtors_detail WHERE debtor_id IN (
	SELECT debtor_id FROM debtor_relation WHERE FIND_IN_SET(organisation_id, @originator_id_list)
);

/* delete credebtor relation records */
DELETE FROM debtor_relation WHERE FIND_IN_SET(organisation_id, @originator_id_list);

/* get Originator address id(s) */
SELECT @address_id_list_originators := GROUP_CONCAT( aid ) 
FROM (
	SElECT organisation_address_id as aid FROM organisation_details WHERE FIND_IN_SET(organisation_id, @originator_id_list) 
	UNION SElECT organisation_regoffice_id as aid FROM organisation_details WHERE FIND_IN_SET(organisation_id, @originator_id_list) 
	UNION SElECT mail_address_id as aid FROM organisation_details WHERE FIND_IN_SET(organisation_id, @originator_id_list) 
	UNION SElECT billing_address_id as aid FROM organisation_details WHERE FIND_IN_SET(organisation_id, @originator_id_list) 
) as T;

/* delete originator address records */
DELETE FROM address_details WHERE FIND_IN_SET(address_id, @address_id_list_originators);

/* delete originator address records */
/* DELETE FROM address_details WHERE address_id IN (
	SElECT organisation_address_id FROM organisation_details WHERE organisation_id IN (@originator_id) 
	UNION SElECT organisation_regoffice_id FROM organisation_details WHERE organisation_id IN (@originator_id) 
	UNION SElECT mail_address_id FROM organisation_details WHERE organisation_id IN (@originator_id) 
	UNION SElECT billing_address_id FROM organisation_details WHERE organisation_id IN (@originator_id) 
);*/

/* delete originator contacts records */
SELECT * FROM contact_details WHERE temp_reg_id IN (
	SELECT temp_reg_id FROM temp_registration 
		JOIN organisation_details ON FIND_IN_SET(organisation_details.organisation_id, @originator_id_list) 
			AND temp_registration.user_id = organisation_details.user_id
);

/* delete user login records */
DELETE FROM user_login WHERE user_id IN (
	SElECT user_id FROM organisation_details WHERE FIND_IN_SET(organisation_id, @originator_id_list) 
);

/* delete temp organisation details records */
DELETE FROM temp_organisation_details WHERE user_id IN (
	SElECT user_id FROM organisation_details WHERE FIND_IN_SET(organisation_id, @originator_id_list) 
);

/* delete organisation bank details records */
DELETE FROM organisation_bank_details WHERE FIND_IN_SET(organisation_id, @originator_id_list);

/* delete organisation bank account details records */
DELETE FROM organisation_bank_account_details WHERE FIND_IN_SET(organisation_id, @originator_id_list);

/* delete organisation currency records */
DELETE FROM organisation_currency WHERE FIND_IN_SET(organisation_id, @originator_id_list);

/* delete organisation currencies with bank records */
DELETE FROM organisation_currencies WHERE FIND_IN_SET(organisation_id, @originator_id_list);

/* delete organisation directos records */
DELETE FROM directorate WHERE FIND_IN_SET(organization_id, @originator_id_list);

/* delete temp registration records */
DELETE FROM temp_registration WHERE user_id IN (
	SElECT user_id FROM organisation_details WHERE FIND_IN_SET(organisation_id, @originator_id_list) 
);

/* delete organisation details records */
DELETE FROM organisation_details WHERE FIND_IN_SET(organisation_id, @originator_id_list);