CREATE VIEW rep_ops_first_property_offer as (
SELECT
ap.id "ap_id",
ap.name,
CONCAT(ag.first_name," ",ag.last_name) "account_manager",
IF(ap.`account_management_key_account` = 1,1,0) "ap_type",
p.id,
ao.id "offer_id",
ifnull(ap.first_offer_published_at, min(ao.published_at)) "published_date",
ao.created_source,
ao.created_by_type,
IF(pp.created_by = 'c4f8a449-e69e-484a-a277-939b0a1a1e63',1,0) "old_ss",
count(distinct ao.id) "total_offers",
p.address_city_code "city",
ao.available_from "availability",
bs.score


FROM prod_reporting.accommodation_offer ao
LEFT JOIN prod_reporting.property AS p ON p.id = ao.property_id
LEFT JOIN prod_reporting.accommodation_provider AS ap ON ap.id = p.accommodation_provider_id
LEFT JOIN prod_reporting.prospective_property pp ON p.id = pp.id
LEFT JOIN prod_reporting_ops.rep_ops_ac_bookable_supply AS bs ON bs.offer_id = ao.id
LEFT JOIN prod_reporting.agency_staff as ag ON ag.id = ap.account_manager

WHERE ap.is_test = 0
AND ao.published_at is not null
GROUP BY p.id
ORDER BY 5,1);