CREATE VIEW rep_ops_verification_basis as (
SELECT
ap.id "ap",
p.id "p",
ao.id "id",
p.address_city_code,
p.verification_internal_verified "verified",
IF(ag.roles in ('ROLE_PHOTOGRAPHY, ROLE_STAFF','ROLE_STAFF,ROLE_PHOTOGRAPHY'),1,0) "photography",
IF(p.`has_video` = 1 OR p.video_url is not null,1,0) "video"

FROM prod_reporting.accommodation_provider ap
INNER JOIN prod_reporting.accommodation_provider_admin ap2
ON ap.id = ap2.id
INNER JOIN prod_reporting.property p
ON ap.id = p.accommodation_provider_id
INNER JOIN prod_reporting.accommodation_offer ao
ON p.id = ao.property_id
LEFT JOIN prod_reporting.agency_staff ag
ON ag.id = p.verification_internal_verified_by
WHERE ap.is_test = 0
  AND ao.published = 1
  AND ap.out_of_platform = 0
  AND p.out_of_platform = 0
  AND ap.account_management_key_account = 0
  AND ao.contract_exclusive = 0
GROUP BY  ao.id);