SELECT COUNT(*) AS TOTAL_ALL
FROM HO_HOTEL_MASTER;

SELECT COUNT(*) AS TOTAL_DIRECT
FROM HO_HOTEL_MASTER HM
INNER JOIN (
    SELECT DISTINCT HM.HOTEL_CODE
    FROM HO_HOTEL_MASTER HM
    WHERE 1 = 1
      AND EXISTS (
        SELECT 1
        FROM VD_VENDOR_HOTEL_CONTRACT VVHC
        WHERE VVHC.HOTEL_CODE = HM.HOTEL_CODE
    )
) D_HOTEL ON HM.HOTEL_CODE = D_HOTEL.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
WHERE 1 = 1
;


SELECT distinct HM.HOTEL_CODE AS EllisHotelCode,
    Legacy.VENDOR_HOTEL_CODE AS LegacyHotelCode,
    Legacy.VENDOR_HOTEL_CODE AS EllisCompCode,
    HM.HOTEL_NAME_EN AS AliceHotelName,
    Expedia.VENDOR_HOTEL_CODE AS ExpediaCode,
    HotelBed.VENDOR_HOTEL_CODE AS HotelbedCode,
    Agoda.VENDOR_HOTEL_CODE AS AgodaCode,
    Dotw.VENDOR_HOTEL_CODE AS DOTWCode,
    Fitruums.VENDOR_HOTEL_CODE AS FitruumsCode,
    Dida.VENDOR_HOTEL_CODE AS DidaCode,
    Rakuten.VENDOR_HOTEL_CODE AS RakutenCode,
    CC.COUNTRY_CODE AS CountryCode,
    CC.COUNTRY_NAME_EN AS CountryName,
    REGION.REGION_CODE AS CityCode,
    REGION.REGION_NAME_EN AS CityName,
    HM.ADDRESS_EN AS HotelAddressEn,
    CONCAT(
            HM.STAR_RATING,
            ' ',
            CASE
                WHEN HM.STAR_RATING <> '0.0'
                    AND HM.STAR_RATING <> '1.0' THEN 'Stars'
                ELSE 'Star'
            END
    ) AS StarRating,
    HM.LONGITUDE AS Longitude,
    HM.LATITUDE AS Latitude
FROM HO_HOTEL_MASTER HM
INNER JOIN (
    SELECT DISTINCT HM.HOTEL_CODE
    FROM HO_HOTEL_MASTER HM
    WHERE 1 = 1
      AND EXISTS (
        SELECT 1
        FROM VD_VENDOR_HOTEL_CONTRACT VVHC
        WHERE VVHC.HOTEL_CODE = HM.HOTEL_CODE
    )
) D_HOTEL ON HM.HOTEL_CODE = D_HOTEL.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 VHM.VENDOR_HOTEL_CODE, VHM.VENDOR_COMP_CODE
  FROM VD_VENDOR_HOTEL_MAPPING VHM
  INNER JOIN VD_VENDOR_HOTEL_MASTER VVHM ON VVHM.VENDOR_HOTEL_CODE = VVHM.VENDOR_HOTEL_CODE
      AND VVHM.VENDOR_COMP_CODE = VHM.VENDOR_COMP_CODE
      AND VVHM.DELETE_YN = 'N'
  INNER JOIN US_COMP_MASTER CM ON VHM.VENDOR_COMP_CODE = CM.COMP_CODE
  WHERE 1 = 1
    AND VHM.HOTEL_CODE = HM.HOTEL_CODE
    AND VHM.VENDOR_COMP_CODE = 110000
  LIMIT 1
) Legacy ON TRUE
LEFT JOIN LATERAL (
  SELECT VHM.VENDOR_HOTEL_CODE
  FROM VD_VENDOR_HOTEL_MAPPING VHM
           INNER JOIN VD_VENDOR_HOTEL_MASTER VVHM ON VVHM.VENDOR_HOTEL_CODE = VVHM.VENDOR_HOTEL_CODE
      AND VVHM.VENDOR_COMP_CODE = VHM.VENDOR_COMP_CODE
      AND VVHM.DELETE_YN = 'N'
           INNER JOIN US_COMP_MASTER CM ON VHM.VENDOR_COMP_CODE = CM.COMP_CODE
  WHERE 1 = 1
    AND VHM.HOTEL_CODE = HM.HOTEL_CODE
    AND VHM.VENDOR_COMP_CODE != 110000
    AND VHM.VENDOR_COMP_CODE = 910001
  LIMIT 1
) Expedia ON TRUE
LEFT JOIN LATERAL (
  SELECT VHM.VENDOR_HOTEL_CODE
  FROM VD_VENDOR_HOTEL_MAPPING VHM
           INNER JOIN VD_VENDOR_HOTEL_MASTER VVHM ON VVHM.VENDOR_HOTEL_CODE = VVHM.VENDOR_HOTEL_CODE
      AND VVHM.VENDOR_COMP_CODE = VHM.VENDOR_COMP_CODE
      AND VVHM.DELETE_YN = 'N'
           INNER JOIN US_COMP_MASTER CM ON VHM.VENDOR_COMP_CODE = CM.COMP_CODE
  WHERE 1 = 1
    AND VHM.HOTEL_CODE = HM.HOTEL_CODE
    AND VHM.VENDOR_COMP_CODE != 110000
    AND VHM.VENDOR_COMP_CODE = 910002
  LIMIT 1
) HotelBed ON TRUE
LEFT JOIN LATERAL (
  SELECT VHM.VENDOR_HOTEL_CODE
  FROM VD_VENDOR_HOTEL_MAPPING VHM
  INNER JOIN VD_VENDOR_HOTEL_MASTER VVHM ON VVHM.VENDOR_HOTEL_CODE = VVHM.VENDOR_HOTEL_CODE
      AND VVHM.VENDOR_COMP_CODE = VHM.VENDOR_COMP_CODE
      AND VVHM.DELETE_YN = 'N'
  INNER JOIN US_COMP_MASTER CM ON VHM.VENDOR_COMP_CODE = CM.COMP_CODE
  WHERE 1 = 1
    AND VHM.HOTEL_CODE = HM.HOTEL_CODE
    AND VHM.VENDOR_COMP_CODE != 110000
    AND VHM.VENDOR_COMP_CODE = 910003
  LIMIT 1
) Agoda ON TRUE
LEFT JOIN LATERAL (
  SELECT VHM.VENDOR_HOTEL_CODE
  FROM VD_VENDOR_HOTEL_MAPPING VHM
  INNER JOIN VD_VENDOR_HOTEL_MASTER VVHM ON VVHM.VENDOR_HOTEL_CODE = VVHM.VENDOR_HOTEL_CODE
      AND VVHM.VENDOR_COMP_CODE = VHM.VENDOR_COMP_CODE
      AND VVHM.DELETE_YN = 'N'
  INNER JOIN US_COMP_MASTER CM ON VHM.VENDOR_COMP_CODE = CM.COMP_CODE
  WHERE 1 = 1
    AND VHM.HOTEL_CODE = HM.HOTEL_CODE
    AND VHM.VENDOR_COMP_CODE != 110000
    AND VHM.VENDOR_COMP_CODE = 910004
  LIMIT 1
) Dotw ON TRUE
LEFT JOIN LATERAL (
  SELECT VHM.VENDOR_HOTEL_CODE
  FROM VD_VENDOR_HOTEL_MAPPING VHM
           INNER JOIN VD_VENDOR_HOTEL_MASTER VVHM ON VVHM.VENDOR_HOTEL_CODE = VVHM.VENDOR_HOTEL_CODE
      AND VVHM.VENDOR_COMP_CODE = VHM.VENDOR_COMP_CODE
      AND VVHM.DELETE_YN = 'N'
           INNER JOIN US_COMP_MASTER CM ON VHM.VENDOR_COMP_CODE = CM.COMP_CODE
  WHERE 1 = 1
    AND VHM.HOTEL_CODE = HM.HOTEL_CODE
    AND VHM.VENDOR_COMP_CODE != 110000
    AND VHM.VENDOR_COMP_CODE = 910005
  LIMIT 1
) Fitruums ON TRUE
LEFT JOIN LATERAL (
  SELECT VHM.VENDOR_HOTEL_CODE
  FROM VD_VENDOR_HOTEL_MAPPING VHM
           INNER JOIN VD_VENDOR_HOTEL_MASTER VVHM ON VVHM.VENDOR_HOTEL_CODE = VVHM.VENDOR_HOTEL_CODE
      AND VVHM.VENDOR_COMP_CODE = VHM.VENDOR_COMP_CODE
      AND VVHM.DELETE_YN = 'N'
           INNER JOIN US_COMP_MASTER CM ON VHM.VENDOR_COMP_CODE = CM.COMP_CODE
  WHERE 1 = 1
    AND VHM.HOTEL_CODE = HM.HOTEL_CODE
    AND VHM.VENDOR_COMP_CODE != 110000
    AND VHM.VENDOR_COMP_CODE = 910016
  LIMIT 1
) Dida ON TRUE
LEFT JOIN LATERAL (
  SELECT VHM.VENDOR_HOTEL_CODE
  FROM VD_VENDOR_HOTEL_MAPPING VHM
           INNER JOIN VD_VENDOR_HOTEL_MASTER VVHM ON VVHM.VENDOR_HOTEL_CODE = VVHM.VENDOR_HOTEL_CODE
      AND VVHM.VENDOR_COMP_CODE = VHM.VENDOR_COMP_CODE
      AND VVHM.DELETE_YN = 'N'
           INNER JOIN US_COMP_MASTER CM ON VHM.VENDOR_COMP_CODE = CM.COMP_CODE
  WHERE 1 = 1
    AND VHM.HOTEL_CODE = HM.HOTEL_CODE
    AND VHM.VENDOR_COMP_CODE != 110000
    AND VHM.VENDOR_COMP_CODE = 910023
  LIMIT 1
) Rakuten ON TRUE
WHERE 1 = 1
ORDER BY HM.COUNTRY_CODE;