-- 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 tempPreConstructionProperties;
create temporary table tempPreConstructionProperties as select a.*, 1 as PreConstruction 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(pc.PreConstruction) as PreConstruction
, 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 then 'T' when dt.Detached is not null then 'D' else 'C' 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 tempPreConstructionProperties pc on a.id=pc.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;