create view rep_fin_existing_supply_available_om as (
SELECT
   ao.id AS id,
   left(ifnull(fc.focus_city_name_2,'Others'),2) AS market,
   ifnull(fc.focus_city_name_2,'Others') AS city,
   year(ao.published_at) AS year,
   uw.week_up AS week,
   ao.id AS offer_id,
   ao.offer_type AS offer_type,
   p.typology_number_of_bedrooms AS total_bedrooms,
   ao.number_of_units AS total_nits,
   nn.name AS ops_neighborhood,
   (ao.reference_rent_amount / 100) AS rent,
   (case when (ap.account_management_key_account = '1') then 'KA' else 'SML' end) AS ap_type,
   (case when ((ao.created_by_type = 'accommodation-provider') or (ao.created_by = 'c4f8a449-e69e-484a-a277-939b0a1a1e63')) then 'self-service' else 'agents' end) AS created_by,
   (case when (ao.created_by = ap.id) then 'non full edit' else 'full edit' end) AS ss,
   cast(ao.available_from as date) AS availability,if((ao.available_from <= (curdate() + interval 30 day)),'1','0') AS available_30d,
   if((ao.available_from <= (curdate() + interval 90 day)),'1','0') AS available_90d,cast(ao.availability_last_update_at as date) AS last_availability_update,if((ao.availability_last_update_at >= (curdate() + interval -(30) day)),'1','0') AS updated_30d,
   ifnull(bs.score,0) as 'bookable_score'

FROM
prod_reporting.accommodation_offer ao
left join prod_reporting.property p on p.id = ao.property_id
left join prod_reporting.accommodation_provider ap on ap.id = p.accommodation_provider_id
left join prod_reporting_ops.focus_city_other_markets fc on fc.city  = p.address_city_code
left join prod_reporting.prospective_property pp on p.id = pp.id
left join prod_reporting.neighborhood nn on nn.id = p.neighborhood_id
left join prod_reporting_ops.uniplaces_week uw on uw.date_week = date(ao.published_at)
left join prod_reporting_ops.rep_ops_ac_bookable_supply bs on bs.offer_id = ao.id
where ap.is_test = 0
and isnull(ao.parent_id)
and ao.published_at is not null
and p.out_of_platform = 0
and ap.out_of_platform = 0 and ao.published = 1
and fc.flag = 2);