/* delete all reminder purposes from all customer contacts */
DELETE cc
from contact_classifications cc
join contacts c1 on c1.contact_id = cc.contact_id
join lookups l on l.lookup_id = cc.lookup_id and l.arch_short_name = 'lookup.contactPurpose' and l.name = 'Reminder'
join entities cu on cu.entity_id = c1.party_id and cu.arch_short_name = 'party.customerperson'
left join contacts c2 on c2.party_id = cu.entity_id #and c2.arch_short_name = 'contact.email'
where isnull(c2.name) = 0;

/* check lookup id for purpose reminder - should be 137 */
select * from lookups l
where l.arch_short_name = 'lookup.contactPurpose' and l.name = 'Reminder';

/* set purpose Reminder for all preferred email contacts for active customers*/
INSERT INTO contact_classifications (contact_id,lookup_id)
  SELECT c.contact_id, 137
  FROM contacts c 
  join entities cu on cu.entity_id = c.party_id and cu.arch_short_name = 'party.customerperson'
  join contact_details cd on cd.contact_id = c.contact_id
	and cd.name = 'preferred'
  WHERE c.arch_short_name = 'contact.email'
    and cu.active=1
    and cd.value='true';
	
/* set purpose Reminder on the phone contact(s) for those active customers who do not have any contacts with purpose Reminder*/
INSERT INTO contact_classifications (contact_id,lookup_id)
  SELECT c.contact_id, 137
  FROM contacts c 
  join entities cu on cu.entity_id = c.party_id and cu.arch_short_name = 'party.customerperson'
  WHERE cu.active=1
    and c.arch_short_name ='contact.phoneNumber'
    and   (select count(*) as total from contacts c
		join contact_classifications cc on cc.contact_id = c.contact_id
		join lookups l on l.lookup_id = cc.lookup_id and l.arch_short_name = 'lookup.contactPurpose' and l.name = 'Reminder'
		where c.party_id = cu.entity_id
  ) = 0;
	

/* set purpose Reminder on the preferred phone contact(s) for those who do not have a preferred email address*/
INSERT INTO contact_classifications (contact_id,lookup_id)
  SELECT c.contact_id, 137
  FROM contacts c 
  join entities cu on cu.entity_id = c.party_id and cu.arch_short_name = 'party.customerperson'
  join contact_details cd on cd.contact_id = c.contact_id
	and cd.name = 'preferred'
  WHERE c.arch_short_name = 'contact.phoneNumber'
    and cd.value='true'
    and   (select count(*) as total from contacts cemail
      left join contact_details ced on ced.contact_id = cemail.contact_id
            and ced.name = 'preferred'
      where cemail.party_id = cu.entity_id
        and cemail.arch_short_name = 'contact.email'
         and ced.value='true'
  ) = 0;
  
/* set purpose Reminder on the NOT preferred phone contact(s) for those who do not have a preferred email address*/
INSERT INTO contact_classifications (contact_id,lookup_id)
  SELECT c.contact_id, 137
  FROM contacts c 
  join entities cu on cu.entity_id = c.party_id and cu.arch_short_name = 'party.customerperson'
  join contact_details cd on cd.contact_id = c.contact_id and cd.name = 'preferred'
  WHERE c.arch_short_name = 'contact.phoneNumber'
    and cd.value='false'
    and   (select count(*) as total from contacts cemail
      left join contact_details ced on ced.contact_id = cemail.contact_id
            and ced.name = 'preferred'
      where cemail.party_id = cu.entity_id
        and cemail.arch_short_name = 'contact.email'
        and ced.value='true'
   ) = 0;



############################################################################
/* find all contacts with purpose Reminder */
select cu.name as custName, cu.entity_id as custID,
  c.arch_short_name as contactType, c.name as contactName, c.description as contactDescr, l.name as contactPurpose
from entities cu
join contacts c on cu.entity_id = c.party_id 
left join contact_classifications cc on cc.contact_id = c.contact_id
left join lookups l on l.lookup_id = cc.lookup_id and l.arch_short_name = 'lookup.contactPurpose'
where cu.arch_short_name = 'party.customerperson'
and l.name = 'Reminder'
order by cu.name, cu.entity_id, c.arch_short_name, l.name;

/* find all contacts with NO purpose Reminder */
select cu.name as custName, cu.entity_id as custID,
  c.arch_short_name as contactType, c.name as contactName, c.description as contactDescr, l.name as contactPurpose
from entities cu
left join contacts c on cu.entity_id = c.party_id 
left join contact_classifications cc on cc.contact_id = c.contact_id
left join lookups l on l.lookup_id = cc.lookup_id and l.arch_short_name = 'lookup.contactPurpose' and l.name = 'Reminder'
where cu.arch_short_name = 'party.customerperson'
and isnull(l.name)
order by cu.name, cu.entity_id, c.arch_short_name, l.name;

/* find all with phone/reminder contact and with email */
select cu.name as custName, cu.entity_id as custID,
  c1.arch_short_name as contactType, c1.name as contactName, c1.description as contactDescr, 
  c2.arch_short_name as contactType2, c2.name as contactName2, c2.description as contactDescr2,
  l.name as contactPurpose
from contact_classifications cc
join contacts c1 on c1.contact_id = cc.contact_id and c1.arch_short_name = 'contact.phoneNumber'
join lookups l on l.lookup_id = cc.lookup_id and l.arch_short_name = 'lookup.contactPurpose' and l.name = 'Reminder'
join entities cu on cu.entity_id = c1.party_id and cu.arch_short_name = 'party.customerperson'
left join contacts c2 on c2.party_id = cu.entity_id and c2.arch_short_name = 'contact.email'
where isnull(c2.name) = 0;

/* show all reminder purposes from all customer contacts */
select cu.entity_id, c1.description
from contact_classifications cc
join contacts c1 on c1.contact_id = cc.contact_id
join lookups l on l.lookup_id = cc.lookup_id and l.arch_short_name = 'lookup.contactPurpose' and l.name = 'Reminder'
join entities cu on cu.entity_id = c1.party_id and cu.arch_short_name = 'party.customerperson'
left join contacts c2 on c2.party_id = cu.entity_id #and c2.arch_short_name = 'contact.email'
where isnull(c2.name) = 0;


/* find all with email contact with purpose reminder */
select cu.name as custName, cu.entity_id as custID,
  c.arch_short_name as contactType, c.name as contactName, c.description as contactDescr, l.name as contactPurpose
from contacts c
join entities cu on cu.entity_id = c.party_id and cu.arch_short_name = 'party.customerperson'
left join contact_classifications cc on cc.contact_id = c.contact_id
left join lookups l on l.lookup_id = cc.lookup_id and l.arch_short_name = 'lookup.contactPurpose'
WHERE c.arch_short_name = 'contact.email'
and l.name = 'Reminder'
and isnull(cc.contact_id) = 0;

/* find all email contacts set as preferred */
select cu.entity_id, c.description 
from contacts c
join entities cu on cu.entity_id = c.party_id and cu.arch_short_name = 'party.customerperson'
join contact_classifications cc on cc.contact_id = c.contact_id
#join lookups l on l.lookup_id = cc.lookup_id and l.arch_short_name = 'lookup.contactPurpose'
left join contact_details cd on cd.contact_id = c.contact_id
	and cd.name = 'preferred'
where c.arch_short_name = 'contact.email'
#and l.name = 'Reminder'
and cd.value = 'true'
;



/* find customers with more than one email contact with purpose reminder */
select cu.name as custName, cu.entity_id as custID, contact.description as emailx, 
     cd2.value as email, cd1.value as preferred,
  (select count(*) as total from contacts c
      join contact_classifications cc on cc.contact_id = c.contact_id
	  join lookups l on l.lookup_id = cc.lookup_id and l.arch_short_name = 'lookup.contactPurpose'
      left join contact_details cd on cd.contact_id = c.contact_id
            and cd.name = 'preferred'
      where c.party_id = cu.entity_id
        and c.arch_short_name = 'contact.email'
        and l.name = 'Reminder'
    ) as count     
from entities cu
join contacts contact on cu.entity_id = contact.party_id 
    and contact.arch_short_name = 'contact.email'
      join contact_classifications cc on cc.contact_id = contact.contact_id
	  join lookups l on l.lookup_id = cc.lookup_id and l.arch_short_name = 'lookup.contactPurpose'
      left join contact_details cd1 on cd1.contact_id = contact.contact_id
            and cd1.name = 'preferred'
      left join contact_details cd2 on cd2.contact_id = contact.contact_id
            and cd2.name = 'emailAddress'
WHERE cu.arch_short_name = 'party.customerperson'
and   (select count(*) as total from contacts c
      join contact_classifications cc on cc.contact_id = c.contact_id
	  join lookups l on l.lookup_id = cc.lookup_id and l.arch_short_name = 'lookup.contactPurpose'
      left join contact_details cd on cd.contact_id = c.contact_id
            and cd.name = 'preferred'
      where c.party_id = cu.entity_id
        and c.arch_short_name = 'contact.email'
        and l.name = 'Reminder'
   ) > 1
order by cu.entity_id   
;

/* find customers email contacts with either > 1 or 0 set with purpose Reminder */
select cu.name as custName, cu.entity_id as custID, 
  (select count(*) as total from contacts c
      join contact_classifications cc on cc.contact_id = c.contact_id
	  join lookups l on l.lookup_id = cc.lookup_id and l.arch_short_name = 'lookup.contactPurpose'
      where c.party_id = cu.entity_id
        and c.arch_short_name = 'contact.email'
        and l.name = 'Reminder'
   ) as count     
from entities cu
join contacts c on c.party_id = cu.entity_id and c.arch_short_name = 'contact.email'
left join contact_classifications cc on cc.contact_id = c.contact_id
left join lookups l on l.lookup_id = cc.lookup_id and l.arch_short_name = 'lookup.contactPurpose'
WHERE cu.arch_short_name = 'party.customerperson'
and   (select count(*) as total from contacts c
      join contact_classifications cc on cc.contact_id = c.contact_id
	  join lookups l on l.lookup_id = cc.lookup_id and l.arch_short_name = 'lookup.contactPurpose'
      where c.party_id = cu.entity_id
        and c.arch_short_name = 'contact.email'
        and l.name = 'Reminder'
   )<>1
;

/* find customers with no preferred email contact and a preferred phone contact */
select cu.name as custName, cu.entity_id as custID, cemail.description as emailx, 
     ced1.value as preferred,
  (select count(*) as total from contacts c
      left join contact_details cd on cd.contact_id = c.contact_id
            and cd.name = 'preferred'
      where c.party_id = cu.entity_id
        and c.arch_short_name = 'contact.email'
    ) as countEmail, 
    cphone.description as phonex, 
     cpd1.value as preferred,
  (select count(*) as total from contacts c
      left join contact_details cd on cd.contact_id = c.contact_id
            and cd.name = 'preferred'
      where c.party_id = cu.entity_id
        and c.arch_short_name = 'contact.phoneNumber'
        and cd.value='true'
    ) as countPhone     
from entities cu
left join contacts cemail on cu.entity_id = cemail.party_id 
    and cemail.arch_short_name = 'contact.email'
      left join contact_details ced1 on ced1.contact_id = cemail.contact_id
            and ced1.name = 'preferred'
left join contacts cphone on cu.entity_id = cphone.party_id 
    and cphone.arch_short_name = 'contact.phoneNumber'
      left join contact_details cpd1 on cpd1.contact_id = cphone.contact_id
            and cpd1.name = 'preferred'
WHERE cu.arch_short_name = 'party.customerperson'
and   (select count(*) as total from contacts cemail
      left join contact_details ced on ced.contact_id = cemail.contact_id
            and ced.name = 'preferred'
      where cemail.party_id = cu.entity_id
        and cemail.arch_short_name = 'contact.email'
   ) = 0
and cpd1.value = 'true'   
order by cu.entity_id   
;