SELECT HM.HOTEL_CODE AS HotelCode,
    HM.HOTEL_NAME_EN AS HotelName,
    CC.COUNTRY_CODE AS CountryCode,
    CC.COUNTRY_NAME_EN AS CountryName,
    REGION.REGION_CODE AS RegionCode,
    REGION.REGION_NAME_EN AS RegionName,
    VVHC.HOTEL_CONTRACT_SEQ AS ContractSeq,
    CASE WHEN VVHC.CONTRACT_STATUS_CODE = 'A' THEN 'Active'
      ELSE 'Pending' END AS ContractStatus,
    (
      SELECT DETAIL_CODE_NAME_EN
      FROM BS_DETAIL_CODE
      WHERE MASTER_CODE = 'HO023'
        AND DETAIL_CODE = VVHC.HOTEL_CONTRACT_TYPE_CODE
      LIMIT 1
    ) AS ContractType,
    E.EMAIL AS Email
FROM HO_HOTEL_MASTER HM
INNER JOIN VD_VENDOR_HOTEL_CONTRACT VVHC ON VVHC.HOTEL_CODE = HM.HOTEL_CODE
INNER JOIN BS_COUNTRY_CODE CC ON HM.COUNTRY_CODE = CC.COUNTRY_CODE
LEFT JOIN BS_REGION_CODE REGION ON HM.REGION_CODE = REGION.REGION_CODE
LEFT JOIN LATERAL (
  SELECT GROUP_CONCAT(A.EMAIL_ADDRESS_LIST) AS EMAIL
  FROM (
    SELECT DISTINCT VVBS.EMAIL_ADDRESS_LIST
    FROM VD_VENDOR_BOOKING_SEND VVBS
    WHERE VVBS.HOTEL_CONTRACT_SEQ = VVHC.HOTEL_CONTRACT_SEQ
  ) A
) E ON TRUE
WHERE 1 = 1
  AND HM.COUNTRY_CODE = 'VN'
ORDER BY HM.COUNTRY_CODE
-- LIMIT 10
;