-- event_scheduler must be set to ON in mysql configuration file
SET GLOBAL event_scheduler = ON;

DROP EVENT IF EXISTS event_module_imobles_view_type_unit_search;
CREATE EVENT event_module_imobles_view_type_unit_search
ON
    SCHEDULE EVERY 60 SECOND
DO
    CALL sp_module_imobles_type_unit_search();

DROP PROCEDURE IF EXISTS sp_module_imobles_type_unit_search;
DELIMITER //
CREATE PROCEDURE sp_module_imobles_type_unit_search()
BEGIN

	DROP TABLE IF EXISTS module_imobles_view_type_units_search;

	CREATE TABLE module_imobles_view_type_units_search 
	SELECT
		e.id,
		e.module_imobles_enterprise_id,
		ent.module_imobles_company_construction_id,
        e.price_min AS min,
		e.price_max AS max,
		e.price_avg AS avg,
		e.iptu_avg AS iptu,
		e.condo_fee_avg AS condo_fee,
		e.available_units,
		e.total_listings,
		e.varanda_sacada,
		e.area,
        e.number_rooms,
        e.number_suite,
        e.number_bathroom,
        e.number_toilet,
        e.number_garage,
        e.promotion,
		e.standard_price,
		e.desc as description,
        e.crawler_type_id,
		co.name AS company_construction_name,
        co.accept_exchange,
		ent.name AS enterprise_name,
		ent.release_date,
		ent.address_number,
		ent.address_neightborhood,
		ent.distance_to_the_beach,
		ent.google_phone,
		ent.google_rating,
        ent.google_place_id,
		ent.value_installment_porcentage,
		ent.value_founding_porcentage,
		ent.value_input_porcentage,
		ent.discount,
        ent.sanitized,        
		ent.site_enabled,
		ent.videoTourLink AS videoTourLink,
		ent.latitude,
		ent.longitude,
		ent.link_google,
		ent.init_sales,
		ent.dwv_id,
		ent.estimated_price,
		ent.financing_input_qtd,
		ent.financing_signal_qtd,
		ent.financing_monthly_qtd,
		ent.financing_semester_qtd,
		ent.financing_keys_qtd,
		ent.value_signal_porcentage,
		ent.value_semester_porcentage,
		ent.value_keys_porcentage,
		ent.value_financed_porcentage,
        ent.address_neighborhood_sanitized,
		ent.address_city_id,
        ent.address_log,
        ent.address_log_sanitized,
        ent.imobles_client,
        ent.verified,
        ent.address_zip,
        ent.crawler_enterprise_id,
        ent.link_hotsite,
		ent.link_presentation,
        ent.site_url,
		DATE(tower.date_delivery) AS date_delivery,
		tower.stage as stage,
		c.city_name,
        c.city_ibge,
        st.uf_state,
		st.state_name,
        st.code_state,
        ent.google_geometry,
        ent.has_no_ri,
		ent.price_type,
        ent.hubspot,
        ent.hotsite_params,
        ent.relevance,
		ent.slug,
        ent.pre_launch,
        ent.searchable,
        e.site_enabled as unit_type_site_enabled,        
        std.name AS property_standard,
        co.hotsite_params AS construction_company_params,
        ent.desc as enterprise_description,
        ent.google_json,
        ent.youtube_video,
		co.slug AS construction_company_slug,
		n.params AS neighborhood_params,
		n.id AS neighborhood_id,
		n.google_details AS neighborhood_google_place_details,
		ent.meeting_date,
        tower.total_units as enterprise_total_units,
        e.imobles_indicates,
		co.status as company_construction_status,
        (
			SELECT 
				concat('[', group_concat(concat('"', module_imobles_enterprise_amenities_id, '"')), ']')
			FROM
				module_imobles_enterprise_entreprise_amenities
			WHERE
				module_imobles_enterprise_id = e.module_imobles_enterprise_id
        ) as enterprise_amenities,
        (
			SELECT 
				concat('[', group_concat(concat('"', module_imobles_type_units_amenities_id, '"')), ']')
			FROM
				module_imobles_enterprise_units_type_amenities
			WHERE
				module_imobles_enterprise_units_type_id = e.id
        ) as type_unit_amenities,        
        ent.created_at as enterprise_created_at
	FROM
		module_imobles_enterprise_units_type e
		
	JOIN
		module_imobles_enterprise ent 
	ON
		ent.id = e.module_imobles_enterprise_id

	LEFT JOIN
		module_imobles_company_construction co
	ON
		co.id = ent.module_imobles_company_construction_id

	LEFT JOIN (
		SELECT 
			sub_tow.module_imobles_enterprise_id,
			sub_tow.stage AS stage,
			MIN(sub_tow.date_delivery) AS date_delivery,
            SUM(sub_tow.total_units) as total_units
		FROM
			module_imobles_enterprise_towers sub_tow
		GROUP BY 1
	) AS tower
	ON
		tower.module_imobles_enterprise_id = ent.id

	LEFT JOIN
		state st
	ON
		st.id = ent.address_state_id

	LEFT JOIN
		city c
	ON
		c.id = ent.address_city_id
    
	LEFT JOIN
		module_imobles_enterprise_standard std
	ON
		std.id = ent.module_imobles_enterprise_standard_id
    
	LEFT JOIN
		module_imobles_neighborhoods n
	ON
		ent.address_neighborhood_sanitized = n.sanitized_name
		AND ent.address_city_id = n.city_id
    
	WHERE
		e.delete_at IS NULL
		AND ent.delete_at IS NULL
		AND e.price_min IS NOT NULL

	ORDER BY
		e.price_min ASC;
        
    ALTER TABLE `module_imobles_view_type_units_search` 
    ADD INDEX `INDEX` (`imobles_client` ASC, `address_city_id` ASC, `address_neighborhood_sanitized` ASC, `area` ASC, `number_rooms` ASC, `min` ASC);
    
END //
DELIMITER ;