Michigan Migration

-- Copy all of the data, using account_id for contact_id even though it's wrong
insert into contacts_contr_contributions_1_c
(id,
contacts_contr_contributions_1contacts_ida,
contacts_contr_contributions_1contr_contributions_idb)
select
accounts_contr_contributions_3_c.id,
accounts_contr_contributions_3_c.accounts_contr_contributions_3accounts_ida,
accounts_contr_contributions_3_c.accounts_contr_contributions_3contr_contributions_idb
from accounts_contr_contributions_3_c where accounts_contr_contributions_3_c.deleted = 0;

-- Replace the account_id with contact_id
update contacts_contr_contributions_1_c
join accounts_contacts on contacts_contr_contributions_1_c.contacts_contr_contributions_1contacts_ida = accounts_contacts.account_id
join contacts on accounts_contacts.contact_id = contacts.id
set contacts_contr_contributions_1_c.contacts_contr_contributions_1contacts_ida
  = contacts.id
where contacts.id in (
  select contacts.id
  from contacts
  join accounts_contacts on contacts.id = accounts_contacts.contact_id
);

-- find the leftovers
select id,name from accounts
where accounts.id in (
select distinct(contacts_contr_contributions_1contacts_ida) from contacts_contr_contributions_1_c
where contacts_contr_contributions_1_c.contacts_contr_contributions_1contacts_ida in
(select accounts.id from accounts)
);

-- turn the leftovers into contacts records of the same name
insert into contacts(id,last_name,date_modified)
select accounts.id,accounts.name,NOW() from accounts
where accounts.id in (
select distinct(contacts_contr_contributions_1contacts_ida) from contacts_contr_contributions_1_c
where contacts_contr_contributions_1_c.contacts_contr_contributions_1contacts_ida in
(select accounts.id from accounts)
);

-- and link those bitches together, use the same ID for all because I'm awful
insert into accounts_contacts (id,account_id,contact_id,date_modified)
select accounts.id,accounts.id,accounts.id,NOW() from accounts
where accounts.id in (
select distinct(contacts_contr_contributions_1contacts_ida) from contacts_contr_contributions_1_c
where contacts_contr_contributions_1_c.contacts_contr_contributions_1contacts_ida in
(select accounts.id from accounts)
);

-- Look up the Accounts that have the same name as a Contact, fuzzy match on first+last name
select accounts.id as account_id,contacts.id as contact_id
from accounts,contacts
where accounts.name is not null and accounts.name <> '' and accounts.deleted = 0
and contacts.first_name is not null and contacts.first_name <> '' and
contacts.last_name is not null and contacts.last_name <> '' and contacts.deleted = 0
and accounts.name = ltrim(rtrim(concat(contacts.first_name,' ',contacts.last_name)))
order by accounts.name;

-- Update the contacts_contr_contributions_1_c Contact to parent account,
-- and parent account to matching name-based contact
update contacts_contr_contributions_1_c
join accounts_contacts on accounts_contacts.contact_id = contacts_contr_contributions_1_c.contacts_contr_contributions_1contacts_ida
join accounts on accounts_contacts.account_id = accounts.id
join contacts new_contacts on accounts.name = ltrim(rtrim(concat(new_contacts.first_name,' ',new_contacts.last_name)))
set contacts_contr_contributions_1_c.contacts_contr_contributions_1contacts_ida = new_contacts.id
where contacts_contr_contributions_1_c.deleted = 0
and accounts_contacts.deleted = 0
and accounts.deleted = 0
and new_contacts.deleted = 0;

-- Copy the one:many relate to the many:many
-- done after the above, and after some manual data cleanup
-- insert into
-- campa_campaigncommittee_contr_contributions_1_c
-- (
--     id,date_modified,
--     campa_camp05f8mmittee_ida,
--     campa_camp678cbutions_idb
-- )
-- select
--     id_c as link_id,NOW(),
--     campa_campaigncommittee_id_c as campaign_committee_id,
--     id_c as contribution_id
-- from contr_contributions_cstm;