-- All Property Tags
drop table if exists cc_property_tags;
create table cc_property_tags as
select wp_posts.id, wp_posts.post_title as property_name, wp_posts.guid, wp_posts.post_excerpt as excerpt, wp_terms.name as tag
from wp_terms, wp_term_relationships, wp_posts
where wp_terms.term_id=wp_term_relationships.term_taxonomy_id
and wp_term_relationships.object_id=wp_posts.id
and wp_posts.post_status='publish';

-- All Properties
drop table if exists cc_property_all;
create table cc_property_all as select id, property_name, guid, excerpt from cc_property_tags where tag in ('Condos Project');

-- Cities
drop table if exists cc_cities;
create table cc_cities select a.term_id as id, name as city from wp_term_taxonomy a, wp_terms b where a.term_id=b.term_id and a.description='City';

-- Property Cities
drop table if exists cc_property_cities;
create table cc_property_cities as select a.id, a.property_name, tag as city from cc_property_tags a, cc_cities b where a.tag=b.city;

-- Cities
drop table if exists cc_cities;
create table cc_cities select a.term_id as id, name as city from wp_term_taxonomy a, wp_terms b where a.term_id=b.term_id and a.description='City';

-- Property Cities
drop table if exists cc_property_cities;
create table cc_property_cities as select a.id, a.property_name, tag as city from cc_property_tags a, cc_cities b where a.tag=b.city;

-- Developers
drop table if exists cc_developers;
create table cc_developers select a.term_id as id, name as developer_name from wp_term_taxonomy a, wp_terms b where a.term_id=b.term_id and a.description='Developer';

-- Property Developers
drop table if exists cc_property_developers;
create table cc_property_developers as select a.id, a.property_name, tag as developer_name from cc_property_tags a, cc_developers b where a.tag=b.developer_name;

-- Occupancy
drop table if exists cc_occupancy;
create table cc_occupancy select a.term_id as id, name as occupancy from wp_term_taxonomy a, wp_terms b where a.term_id=b.term_id and a.description='occupancy';

-- Property Occupancy
drop table if exists cc_property_occupancy;
create table cc_property_occupancy as select a.id, a.property_name, tag as occupancy from cc_property_tags a, cc_occupancy b where a.tag=b.occupancy;

-- Project Stage
drop table if exists cc_project_stage;
create table cc_project_stage select a.term_id as id, name as stage from wp_term_taxonomy a, wp_terms b where a.term_id=b.term_id and a.description='project_stage';

-- Property Project Stage
drop table if exists cc_property_stage;
create table cc_property_stage as select a.id, a.property_name, tag as stage from cc_property_tags a, cc_project_stage b where a.tag=b.stage;

-- Specialities
drop table if exists cc_specialities;
create table cc_specialities select a.term_id as id, name as speciality from wp_term_taxonomy a, wp_terms b where a.term_id=b.term_id and a.description='Speciality';

-- Property Specialities
drop table if exists cc_property_speciality;
create table cc_property_speciality as select a.id, a.property_name, tag as speciality from cc_property_tags a, cc_specialities b where a.tag=b.speciality;

-- High Properties (temp)
drop table if exists tempHighProperties;
create temporary table tempHighProperties as select a.*, 1 as High from cc_property_all a, cc_property_speciality sp where a.id=sp.id and sp.speciality='High';

-- Medium Properties (temp)
drop table if exists tempMediumProperties;
create temporary table tempMediumProperties as select a.*, 1 as Medium from cc_property_all a, cc_property_speciality sp where a.id=sp.id and sp.speciality='Medium';

-- Low Properties (temp)
drop table if exists tempLowProperties;
create temporary table tempLowProperties as select a.*, 1 as Low from cc_property_all a, cc_property_speciality sp where a.id=sp.id and sp.speciality='Low';

-- Subway Properties (temp)
drop table if exists tempSubwayProperties;
create temporary table tempSubwayProperties as select a.*, 1 as Subway from cc_property_all a, cc_property_speciality sp where a.id=sp.id and sp.speciality='Subway';

-- Lakeside Properties (temp)
drop table if exists tempLakesideProperties;
create temporary table tempLakesideProperties as select a.*, 1 as Lakeside from cc_property_all a, cc_property_speciality sp where a.id=sp.id and sp.speciality='Lakeside';

-- Go Train Properties (temp)
drop table if exists tempGoTrainProperties;
create temporary table tempGoTrainProperties as select a.*, 1 as GoTrain from cc_property_all a, cc_property_speciality sp where a.id=sp.id and sp.speciality='GO';

-- LRT Properties (temp)
drop table if exists tempLRTProperties;
create temporary table tempLRTProperties as select a.*, 1 as LRT from cc_property_all a, cc_property_speciality sp where a.id=sp.id and sp.speciality='LRT';

-- Detached Properties (temp)
drop table if exists tempDetachedProperties;
create temporary table tempDetachedProperties as select a.*, 1 as Detached from cc_property_all a, cc_property_speciality sp where a.id=sp.id and sp.speciality='Detached';

-- Townhouse Properties (temp)
drop table if exists tempTownhouseProperties;
create temporary table tempTownhouseProperties as select a.*, 1 as Townhouse from cc_property_all a, cc_property_speciality sp where a.id=sp.id and sp.speciality='Townhouse';

-- Pre-Construction condos Properties (temp)
drop table if exists tempCondoProperties;
create temporary table tempCondoProperties as select a.*, 1 as Condo from cc_property_all a, cc_property_speciality sp where a.id=sp.id and sp.speciality='Pre-Construction condos';

-- University Properties (temp)
drop table if exists tempUniversityProperties;
create temporary table tempUniversityProperties as select a.*, 1 as University from cc_property_all a, cc_property_speciality sp where a.id=sp.id and sp.speciality='University';

-- Property Specialities (temp)
drop table if exists tempPropertySpecialities;
create temporary table tempPropertySpecialities
select a.*
, 1-isnull(h.High) as High
, 1-isnull(m.Medium) as Medium
, 1-isnull(l.Low) as Low
, 1-isnull(u.University) as University
, 1-isnull(th.Townhouse) as Townhouse
, 1-isnull(cd.Condo) as Condo
, 1-isnull(dt.Detached) as Detached
, 1-isnull(sb.Subway) as Subway
, 1-isnull(gt.GoTrain) as GoTrain
, 1-isnull(lrt.LRT) as LRT
, 1-isnull(ls.Lakeside) as Lakeside
, case when th.Townhouse is not null and dt.Detached is not null and cd.Condo is not null then 'CTD' 
       when th.Townhouse is not null and dt.Detached is not null and cd.Condo is     null then 'TD'
       when th.Townhouse is not null and dt.Detached is     null and cd.Condo is not null then 'CT'
       when th.Townhouse is     null and dt.Detached is not null and cd.Condo is not null then 'CD'
       when th.Townhouse is     null and dt.Detached is     null and cd.Condo is not null then 'C'
       when th.Townhouse is     null and dt.Detached is not null and cd.Condo is     null then 'D'
       when th.Townhouse is not null and dt.Detached is     null and cd.Condo is     null then 'T'
       else '' end as Property_Type
from cc_property_all a 
left join tempUniversityProperties u on a.id=u.id 
left join tempTownhouseProperties th on a.id=th.id
left join tempCondoProperties cd on a.id=cd.id
left join tempDetachedProperties dt on a.id=dt.id
left join tempGoTrainProperties gt on a.id=gt.id
left join tempHighProperties h on a.id=h.id
left join tempLRTProperties lrt on a.id=lrt.id
left join tempLakesideProperties ls on a.id=ls.id
left join tempLowProperties l on a.id=l.id
left join tempMediumProperties m on a.id=m.id
left join tempSubwayProperties sb on a.id=sb.id;

-- Property Images
drop table if exists temp_property_feature_image;
create temporary table temp_property_feature_image select post_id as id, json_extract(meta_value, '$[1].elements[0].elements[0].settings.carousel[0].url') as image_url from wp_postmeta where meta_key='_elementor_data' order by 1;
delete from temp_property_feature_image where image_url is null;
drop table if exists cc_property_feature_image;
create table cc_property_feature_image select a.id, replace(image_url,'"','') as image_url from cc_property_all a join temp_property_feature_image im on a.id=im.id;



-- Property Wide Table
drop table if exists cc_property_full;
create table cc_property_full as
select p.*
,c.city
,st.stage
,dv.developer_name
,oc.occupancy
,im.image_url
from tempPropertySpecialities p
left join cc_property_cities c on p.id=c.id
left join cc_property_stage st on p.id=st.id
left join cc_property_developers dv on p.id=dv.id
left join cc_property_occupancy oc on p.id=oc.id
left join cc_property_feature_image im on p.id=im.id;