CREATE VIEW rep_fin_supply_weekly_om as (
SELECT
LEFT(IFNULL(fc.focus_city_name, 'Others'),2) as "market",
IFNULL(fc.focus_city_name, 'Others') as "city",
YEAR(ao.published_at) as "year",
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_units",
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",
DATE(ao.available_from) AS "availability",
if(available_from between (CURDATE()-1) AND DATE_ADD((CURDATE()-1), INTERVAL 90 day),"1","0")  "available_90",
bs.score as "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_ops.focus_city fc ON fc.city = p.address_city_code
LEFT JOIN			prod_reporting.prospective_property pp ON p.id = pp.id
LEFT JOIN			prod_reporting_ops.rep_googlemaps_neighborhood n ON n.property_id = ao.property_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 as bs ON bs.offer_id = ao.id
WHERE
ap.is_test = 0
AND ao.parent_id IS NULL
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);