SET @startDate = '20260106';

-- 30
SET @fromDate = DATE(@startDate);
SET @toDate = DATE_ADD(@fromDate, INTERVAL 30 DAY);

-- 60
SET @fromDate60 = DATE_ADD(@fromDate, INTERVAL 31 DAY);
SET @toDate60 = DATE_ADD(@fromDate, INTERVAL 60 DAY);

-- 90
SET @fromDate90 = DATE_ADD(@fromDate, INTERVAL 61 DAY);
SET @toDate90 = DATE_ADD(@fromDate, INTERVAL 90 DAY);

-- 1: 0-30; 2: 31-60; 3: 61-90
SET @range = 3;

SET @countryCode = 
'JP' -- 2359 - 363,, bookable
-- 'KR' -- 952 - 180,156,120 bookable
-- 'VN' -- 1248 - 1463,1513,1554 bookable
-- 'HK' -- 38 - 19,19,19
-- 'LA' -- 2 - unbookable
-- 'MX' -- 7 - unbookable
-- 'TH' -- 148 - 76,89,80 bookable
-- 'TW' -- 26 - 2,2,2 bookable
-- 'IL' -- 2 - unbookable
-- 'MY' -- 7 - unbookable
-- 'ID' -- 2 - 2,2,2 bookable
;

SET @usingFromDate = CAST(DATE_FORMAT(
  CASE WHEN @range=1 THEN @fromDate
    WHEN @range=2 THEN @fromDate60
    ELSE @fromDate90
  END
, '%Y%m%d') AS CHAR);
SET @usingToDate = CAST(DATE_FORMAT(
  CASE WHEN @range=1 THEN @toDate
    WHEN @range=2 THEN @toDate60
    ELSE @toDate90
  END
, '%Y%m%d') AS CHAR);


SELECT HM.HOTEL_CODE AS HotelCode
  , A.LegacyHotelCode
  , A.HotelName
  , A.CountryCode
  , A.CountryName
  , A.RegionCode
  , A.RegionName
  , A.Address
  , A.PhoneNumber
  , A.RoomCode
  , A.LegacyRoomCode
  , A.RoomName
  , A.Bookable
FROM (
  SELECT DISTINCT HHM.HOTEL_CODE
  FROM HO_HOTEL_MASTER HHM
  INNER JOIN VD_VENDOR_HOTEL_CONTRACT VHC ON VHC.HOTEL_CODE = HHM.HOTEL_CODE
  WHERE 1=1
    AND HHM.COUNTRY_CODE = @countryCode
    AND HHM.REGISTER_STATUS_CODE = 'RST02'
    AND VHC.CONTRACT_STATUS_CODE = 'A'
  LIMIT 200 OFFSET 2200
) HM
INNER JOIN LATERAL(
  SELECT
    HHM.HOTEL_CODE AS HotelCode
    , IFNULL(HHM.LEGACY_HOTEL_CODE, VHC.LEGACY_HOTEL_CODE) AS LegacyHotelCode
    , HHM.HOTEL_NAME_EN AS HotelName
    , COUNTRY.COUNTRY_CODE AS CountryCode
    , COUNTRY.COUNTRY_NAME_EN AS CountryName
    , REGION.REGION_CODE AS RegionCode
    , REGION.REGION_NAME_EN AS RegionName
    , IFNULL(HHM.ADDRESS_EN, IFNULL(HHM.ADDRESS_KO, IFNULL(HHM.ADDRESS_JA, IFNULL(HHM.ADDRESS_VI, HHM.ADDRESS_ZH)))) AS Address
    , HHM.PHONE_NO AS PhoneNumber
    , RT.VENDOR_ROOM_TYPE_SEQ AS RoomCode
    , RT.LEGACY_ROOM_TYPE_CODE AS LegacyRoomCode
    , RT.ROOM_TYPE_NAME_EN AS RoomName
    , CASE 
          WHEN COUNT(RDP.DAILY_PRICE_SEQ) <= 10
          THEN 'No bookable'
          ELSE 'Bookable'
      END AS Bookable
  FROM VD_VENDOR_ROOM_ALLOTMENT RA
  INNER JOIN VD_VENDOR_ROOM_DAILY_PRICE RDP ON RA.VENDOR_ROOM_TYPE_SEQ = RDP.VENDOR_ROOM_TYPE_SEQ
    AND RA.APPLIED_DATE = RDP.APPLIED_DATE
    AND RA.SALES_STOP_YN = RDP.SALES_STOP_YN
    AND RDP.SALES_STOP_YN = 'N'
    AND RDP.VENDOR_NET_AMOUNT > 0
  INNER JOIN VD_VENDOR_ROOM_PLAN RP ON RDP.ROOM_PLAN_SEQ = RP.ROOM_PLAN_SEQ
    AND RA.HOTEL_CONTRACT_SEQ = RP.HOTEL_CONTRACT_SEQ
  INNER JOIN VD_VENDOR_HOTEL_CONTRACT VHC ON RP.HOTEL_CONTRACT_SEQ = VHC.HOTEL_CONTRACT_SEQ
  INNER JOIN US_COMP_MASTER TRADER ON VHC.VENDOR_COMP_CODE = TRADER.COMP_CODE
  INNER JOIN VD_VENDOR_ROOM_TYPE RT ON RP.VENDOR_ROOM_TYPE_SEQ = RT.VENDOR_ROOM_TYPE_SEQ
  INNER JOIN HO_HOTEL_MASTER HHM ON VHC.HOTEL_CODE = HHM.HOTEL_CODE
  INNER JOIN BS_COUNTRY_CODE COUNTRY ON HHM.COUNTRY_CODE = COUNTRY.COUNTRY_CODE
  LEFT JOIN BS_REGION_CODE REGION ON HHM.REGION_CODE = REGION.REGION_CODE
  WHERE 1=1
    AND HHM.COUNTRY_CODE = @countryCode
    AND VHC.HOTEL_CODE = HM.HOTEL_CODE
    AND RDP.APPLIED_DATE BETWEEN @usingFromDate AND @usingToDate -- '20260106' AND '20260301' 
    AND VHC.CONTRACT_STATUS_CODE = 'A'
    AND TRADER.TRADE_STATUS_CODE = 'TST01'
    AND RP.SALES_STOP_YN = 'N'
    AND RT.SALES_STOP_YN = 'N'
    AND HHM.REGISTER_STATUS_CODE = 'RST02' -- Approved
  GROUP BY HHM.HOTEL_CODE
    , IFNULL(HHM.LEGACY_HOTEL_CODE, VHC.LEGACY_HOTEL_CODE)
    , HHM.HOTEL_NAME_EN
    , COUNTRY.COUNTRY_CODE
    , COUNTRY.COUNTRY_NAME_EN
    , REGION.REGION_CODE
    , REGION.REGION_NAME_EN
    , IFNULL(HHM.ADDRESS_EN, IFNULL(HHM.ADDRESS_KO, IFNULL(HHM.ADDRESS_JA, IFNULL(HHM.ADDRESS_VI, HHM.ADDRESS_ZH))))
    , HHM.PHONE_NO
    , RT.VENDOR_ROOM_TYPE_SEQ
    , RT.LEGACY_ROOM_TYPE_CODE
    , RT.ROOM_TYPE_NAME_EN
) A ON TRUE
;