CREATE TEMPORARY TABLE IF NOT EXISTS BK_TEMP AS
(
    SELECT BBHI.BOOKING_ITEM_CODE AS BookingItemCode
        , BBHI.HOTEL_CODE AS HotelCode
        , BBHI.HOTEL_NAME_EN AS HotelName
        , BBHI.CHECK_IN_DATE AS CheckInDate
        , BBHI.CHECK_OUT_DATE AS CheckOutDate
        , DATEDIFF(BBHI.CHECK_OUT_DATE , BBHI.CHECK_IN_DATE) AS NumberRoomNights
        , BBHI.VENDOR_CURRENCY_CODE AS VendorCurrency
        , ROUND(BBHI.VENDOR_SUM_AMOUNT, 2) AS VendorSumAmount
        , ROUND(BBHI.VENDOR_REVENUE_AMOUNT, 2) AS VendorRevenueAmount
        , BBM.BILLING_CURRENCY_CODE AS BillingCurrency
        , ROUND(BBHI.BILLING_SUM_AMOUNT, 2) AS BillingSumAmount
        , ROUND(BBHI.BILLING_REVENUE_AMOUNT, 2) AS BillingRevenueAmount
        , HHM.COUNTRY_CODE AS HOTEL_COUNTRY_CODE
        , BBHI.REGION_CODE
        , IFNULL(BBHI.HOTEL_CONTRACT_TYPE_CODE, (
            SELECT VHC.HOTEL_CONTRACT_TYPE_CODE
            FROM VD_VENDOR_HOTEL_CONTRACT VHC
            WHERE VHC.HOTEL_CONTRACT_SEQ = BBHI.HOTEL_CONTRACT_SEQ
            LIMIT 1
          )
        ) AS HOTEL_CONTRACT_TYPE_CODE
        , BBHI.BOOKING_STATUS_CODE
        , BBHI.CLIENT_PAYMENT_STATUS_CODE
        , BBHI.VENDOR_PAYMENT_STATUS_CODE
        , BBHI.DEVICE_TYPE_CODE
        , BBM.STATION_TYPE_CODE
        , BBHI.FIRST_INSERT_DATETIME
        , BBHI.VENDOR_COMP_CODE
    FROM BK_BOOKING_HOTEL_ITEM BBHI
    INNER JOIN BK_BOOKING_MASTER BBM ON BBHI.BOOKING_CODE = BBM.BOOKING_CODE
    INNER JOIN HO_HOTEL_MASTER HHM ON BBHI.HOTEL_CODE = HHM.HOTEL_CODE
    LEFT JOIN VD_VENDOR_PLAN_PROMOTION P ON P.PLAN_PROMOTION_SEQ = BBHI.PLAN_PROMOTION_SEQ
    WHERE 1 = 1
      AND HHM.COUNTRY_CODE = 'VN'
--       AND BBHI.CHECK_OUT_DATE BETWEEN '20250101' AND '20250331'
--       AND BBHI.CHECK_OUT_DATE BETWEEN '20250401' AND '20250630'
--       AND BBHI.CHECK_OUT_DATE BETWEEN '20251001' AND '20251231'
      AND BBHI.CHECK_OUT_DATE BETWEEN '20250101' AND '20251231'
      AND BBHI.BOOKING_STATUS_CODE IN ('BKS02', 'BKS05')
      -- AND BBHI.BOOKING_ITEM_CODE = '240324JJ04H01' -- '240430TT10H01' -- Empty ContractType
--       AND BBHI.BOOKING_ITEM_CODE = 'K25032611511H01'-- K25032611511H01 -- Empty PIC - Happy Life Grand Hotel & Sky Bar
      -- AND BBHI.BOOKING_ITEM_CODE = 'K25091110409H01'-- K25091110409H01 -- Empty PIC & ContractType - Hotel Majestic Saigon
);
SELECT COUNTRY.COUNTRY_NAME_EN AS HotelCountry
    , REGION.REGION_NAME_EN AS HotelRegion
    , (
      SELECT
          GROUP_CONCAT(
              CONCAT(
                  BDC.DETAIL_CODE_NAME_EN, ',', 
                  UCP.PIC_UNO, '-', 
                  UUM.NAME, (CASE WHEN DPT.COMP_BASIS_CODE_NAME_EN IS NOT NULL THEN ',' ELSE ''END), 
                  IFNULL(DPT.COMP_BASIS_CODE_NAME_EN, ''), (CASE WHEN POS.COMP_BASIS_CODE_NAME_EN IS NOT NULL THEN ',' ELSE ''END), 
                  IFNULL(POS.COMP_BASIS_CODE_NAME_EN, '')
              )
              ORDER BY UCP.PIC_TYPE_CODE
              SEPARATOR ';'
          ) AS RESULT
      FROM US_COMP_PIC UCP
      JOIN US_USER_MASTER UUM 
             ON UCP.PIC_UNO = UUM.USER_NO
      LEFT JOIN US_CONTROL_STAFF UCS 
             ON UUM.USER_NO = UCS.USER_NO
      LEFT JOIN BS_DETAIL_CODE BDC 
             ON BDC.MASTER_CODE = 'US003'
            AND BDC.DETAIL_CODE = UCP.PIC_TYPE_CODE
      LEFT JOIN US_COMP_BASIS_CODE DPT 
             ON DPT.COMP_CODE = UUM.COMP_CODE
            AND DPT.COMP_BASIS_CODE_TYPE = 'COB01'
            AND DPT.COMP_BASIS_CODE_VALUE = UCS.DEPT_CODE
      LEFT JOIN US_COMP_BASIS_CODE POS 
             ON POS.COMP_CODE = UUM.COMP_CODE
            AND POS.COMP_BASIS_CODE_TYPE = 'COB02'
            AND POS.COMP_BASIS_CODE_VALUE = UCS.POSITION_CODE
      WHERE UCP.COMP_CODE = B.VENDOR_COMP_CODE
        AND UCP.PIC_TYPE_CODE IN ('PIC01','PIC02')
    ) AS PIC
    , B.HotelCode
    , B.HotelName
    , DETAIL_CODE.CONTRACT AS ContractType
    , B.NumberRoomNights
    , B.VendorCurrency
    , B.VendorSumAmount AS BuyingAmount
    , B.VendorRevenueAmount
    , B.BillingCurrency
    , B.BillingSumAmount AS SellingAmount
    , B.BillingRevenueAmount
    , B.BookingItemCode
    , B.CheckInDate
    , B.CheckOutDate
    , ROOM_NIGHT.RoomNights AS RoomNights
    , DETAIL_CODE.BOOKING_STATUS AS BookingStatus
FROM BK_TEMP B
LEFT JOIN BS_COUNTRY_CODE COUNTRY ON COUNTRY.COUNTRY_CODE = B.HOTEL_COUNTRY_CODE  -- RECHECK LEFT JOIN
INNER JOIN LATERAL(SELECT REGION_NAME_EN FROM BS_REGION_CODE WHERE REGION_CODE = B.REGION_CODE LIMIT 1) REGION ON TRUE
INNER JOIN LATERAL(SELECT  MAX(ROOM_SEQ) * DATEDIFF(B.CheckOutDate, B.CheckInDate) AS RoomNights, MAX(ROOM_SEQ) AS RoomCounts FROM BK_BOOKING_HOTEL_ROOM WHERE BOOKING_ITEM_CODE = B.BookingItemCode) ROOM_NIGHT ON TRUE
LEFT JOIN LATERAL(
    SELECT
        MAX(IF(MASTER_CODE = 'BK001' AND DETAIL_CODE = B.BOOKING_STATUS_CODE, DETAIL_CODE_NAME_EN, NULL)) AS BOOKING_STATUS
        , MAX(IF(MASTER_CODE = 'BK002' AND DETAIL_CODE = B.CLIENT_PAYMENT_STATUS_CODE, DETAIL_CODE_NAME_EN, NULL)) AS CLIENT_PAYMENT_STATUS
        , MAX(IF(MASTER_CODE = 'BK014' AND DETAIL_CODE = B.VENDOR_PAYMENT_STATUS_CODE, DETAIL_CODE_NAME_EN, NULL)) AS VENDOR_PAYMENT_STATUS
        , MAX(IF(MASTER_CODE = 'BS037' AND DETAIL_CODE = B.DEVICE_TYPE_CODE, DETAIL_CODE_NAME_EN, NULL)) AS DEVICE
        , MAX(IF(MASTER_CODE = 'BS002' AND DETAIL_CODE = B.STATION_TYPE_CODE, DETAIL_CODE_NAME_EN, NULL)) AS STATION
        , MAX(IF(MASTER_CODE = 'HO023' AND DETAIL_CODE = B.HOTEL_CONTRACT_TYPE_CODE, DETAIL_CODE_NAME_EN, NULL)) AS CONTRACT
    FROM BS_DETAIL_CODE WHERE MASTER_CODE IN ('BK001', 'BK002', 'BK014', 'BS037', 'BS002', 'HO023')
        AND DETAIL_CODE IN (
            B.BOOKING_STATUS_CODE,
            B.CLIENT_PAYMENT_STATUS_CODE,
            B.VENDOR_PAYMENT_STATUS_CODE,
            B.DEVICE_TYPE_CODE,
            B.STATION_TYPE_CODE,
            B.HOTEL_CONTRACT_TYPE_CODE
        )
) DETAIL_CODE ON TRUE
ORDER BY B.FIRST_INSERT_DATETIME;
DROP TEMPORARY TABLE IF EXISTS BK_TEMP;