-- Submissions Wide Table
drop table if exists cc_AllTagEmails;
create table cc_AllTagEmails as select distinct email from cc_submission_tags;

-- Email to IP
drop table if exists cc_Email2IP;
create table cc_Email2IP as select distinct email, tag_value as IP from cc_submission_tags where tag_name='IP';

-- IP to Property
drop table if exists cc_IP2Property;
create table cc_IP2Property as select distinct ip, property_name, logdate from cc_view_visitors_property_history where property_name is not null;

-- Property to City
drop table if exists cc_Property2City;
create table cc_Property2City as select distinct property_name, city from cc_property_full;

-- Email to IP to Property to City
drop table if exists cc_Email2IP2Property2City;
create table cc_Email2IP2Property2City as select distinct eip.email, eip.IP, ipp.logdate, ipp.property_name, pc.city from cc_Email2IP eip, cc_IP2Property ipp, cc_Property2City pc where eip.ip=ipp.ip and ipp.property_name=pc.property_name;


-- Broker Flag
drop table if exists tempBroker;
create temporary table tempBroker as select distinct email, tag_value as IfBroker from cc_submission_tags where tag_name='Broker';

-- WeChat Flag
drop table if exists tempWeChat;
create temporary table tempWeChat as select distinct email, WeChat from (select email, 'Y' as WeChat from cc_submission_tags where tag_name='WeChat' union select distinct email, 'Y' from cc_submissions_contact_history where upper(notes) like '%WECHAT%') u;

-- Email to Property
drop table if exists cc_Email2Property;
create table cc_Email2Property as select distinct email, tag_value as property from cc_submission_tags where tag_name="Property";

-- Email to Property to City
drop table if exists cc_Email2Property2City;
create table cc_Email2Property2City as select distinct ep.email, ep.property, f.city from cc_Email2Property ep, cc_property_full f where ep.property=f.property_name;

-- Email to City
drop table if exists cc_Email2City;
create table cc_Email2City as (select distinct * from (select distinct email, tag_value as city from cc_submission_tags where tag_name="City" union select distinct email, whereFrom as city from cc_submission_details where whereFrom is not null) as emailcity_temp);

-- Email to Type
drop table if exists cc_Email2Type;
create table cc_Email2Type as select distinct email, tag_value as property_type from cc_submission_tags where tag_name="Property Type";

-- Combine
drop table if exists cc_submissions;
create table cc_submissions as select u.*,eb.IfBroker,ewc.WeChat from (
select a.email, eippc.IP, eippc.logdate,eippc.property_name,eippc.city from cc_AllTagEmails a left join cc_Email2IP2Property2City eippc on a.email=eippc.email
union
select email,null,null,property,city from cc_Email2Property2City
union
select email, null,null,null,city from cc_Email2City 
) u left join tempBroker eb on u.email=eb.email left join tempWeChat ewc on u.email=ewc.email;

update cc_submissions set IfBroker='N' where IfBroker is null;
update cc_submissions set WeChat='N' where WeChat is null;