-- Submissions Wide Table
drop table if exists tempAllEmails;
create temporary table tempAllEmails 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';

-- 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, tag_value as WeChat from cc_submission_tags where tag_name='WeChat';

-- 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 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 City
drop table if exists cc_Email2City;
create table cc_Email2City as select distinct email, tag_value as city from cc_submission_tags where tag_name="City";

-- 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 a.*, b.IfBroker, w.WeChat from (
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
union
select distinct email, null, null, ep.property, f.city
from cc_Email2Property ep, cc_property_full f
where ep.property=f.property_name
union
select distinct email, null, null, null, city from cc_Email2City
) a left join tempBroker b on a.email=b.email
    left join tempWeChat w on a.email=w.email;

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