CREATE VIEW rep_ops_avail as
SELECT
ap.id "ap_id",
ao.id "ao_id",
1,
"availability_push"

FROM prod_reporting.accommodation_offer ao
	 INNER JOIN prod_reporting.property p
		     ON p.id = ao.property_id
	 INNER JOIN prod_reporting.accommodation_provider ap
		     ON ap.id = p.accommodation_provider_id
	LEFT JOIN prod_reporting_ops.rep_ops_ac_tiers as t
	        ON ap.id = t.id
    LEFT JOIN prod_reporting_ops.rep_ops_bs_final as bs
            ON bs.offer_id = ao.id
    LEFT JOIN prod_reporting.neighborhood as n
            ON n.id = p.neighborhood_id
    LEFT JOIN rep_ops_ao_rates AS aor
           ON aor.id = ao.id
WHERE ap.is_test = 0
      AND ao.published = 1
      AND ap.out_of_platform = 0
      AND p.out_of_platform = 0
      AND ao.`contract_exclusive` = 0
      AND ap.`account_management_key_account` = 0
      AND p.address_city_code in ("PT-lisbon", "PT-porto", "PT-coimbra", "ES-madrid", "ES-barcelona", "ES-valencia", "IT-milan", "IT-rome")
      AND bs.score in (50,100)
      AND DATEDIFF(CURDATE(),date(ao.availability_last_update_at)) >= 15
      AND available_from_with_minimum_stay <= "2018-10-15"
      AND ao.offer_type <> "bed"
GROUP BY  ao.id;