Ellis improvement

1/ Create new columns, new tables to adapt new promotion
2/ Migrate current data to new schema promotion

-- add new columns
ALTER TABLE VD_VENDOR_PLAN_PROMOTION
ADD COLUMN BOOKING_FROM_TIME TIME NULL COMMENT 'Booking start time for promotion' AFTER BOOKING_TO_DATETIME,
ADD COLUMN BOOKING_TO_TIME TIME NULL COMMENT 'Booking end time for promotion' AFTER BOOKING_FROM_TIME,
ADD COLUMN ACTIVATE_FROM_TIME TIME NULL COMMENT 'Active from time of promotion' AFTER BOOKING_TO_TIME
;

ALTER TABLE VD_VENDOR_PLAN_PROMOTION_DATE_RANGE
ADD COLUMN DISCOUNT_OR_UPSELL VARCHAR NULL AFTER SAT_YN,
ADD COLUMN RATE_OR_AMOUNT VARCHAR NULL AFTER DISCOUNT_OR_UPSELL,
ADD COLUMN APPLIED_VALUE DECIMAL NULL AFTER RATE_OR_AMOUNT,
ADD COLUMN DISCOUNT_GROUP_ID INT NULL DEFAULT 1 AFTER APPLIED_VALUE
;

-- migrate daterange
INSERT INTO VD_VENDOR_PLAN_PROMOTION_DATE_RANGE(PLAN_PROMOTION_SEQ, RANGE_TYPE, FROM_DATE, TO_DATE, SUN_YN, MON_YN, TUE_YN, WED_YN, THU_YN, FRI_YN, SAT_YN, DISCOUNT_OR_UPSELL, RATE_OR_AMOUNT, APPLIED_VALUE, FIRST_INSERT_UNO, FIRST_INSERT_DATETIME, LAST_UPDATE_UNO, LAST_UPDATE_DATETIME)
SELECT P.PLAN_PROMOTION_SEQ, 'STAY_DATE' AS RANGE_TYPE, P.CHECK_IN_FROM_DATE AS FROM_DATE
, P.CHECK_IN_TO_DATE AS TO_DATE
, IFNULL AS SUN_YN, IFNULL AS MON_YN, IFNULL AS TUE_YN, IFNULL AS WED_YN
, IFNULL AS THU_YN, IFNULL AS FRI_YN, IFNULL AS SAT_YN
, P.DISCOUNT_OR_UPSELL AS DISCOUNT_OR_UPSELL, P.RATE_OR_AMOUNT AS RATE_OR_AMOUNT, P.APPLIED_VALUE AS APPLIED_VALUE
, 90037 AS FIRST_INSERT_UNO, NOW AS FIRST_INSERT_DATETIME, 90037 AS LAST_UPDATE_UNO, NOW AS LAST_UPDATE_DATETIME
FROM VD_VENDOR_PLAN_PROMOTION P
WHERE PLAN_PROMOTION_SEQ NOT IN
(SELECT VD_VENDOR_PLAN_PROMOTION_DATE_RANGE.PLAN_PROMOTION_SEQ
FROM VD_VENDOR_PLAN_PROMOTION_DATE_RANGE)
AND CHECK_IN_FROM_DATE IS NOT NULL
AND CHECK_IN_TO_DATE IS NOT NULL;

-- migrate discount value ( chỉ lấy promotion percentage)
UPDATE VD_VENDOR_PLAN_PROMOTION_DATE_RANGE R
INNER JOIN VD_VENDOR_PLAN_PROMOTION P ON P.PLAN_PROMOTION_SEQ = R.PLAN_PROMOTION_SEQ
SET R.DISCOUNT_OR_UPSELL = P.DISCOUNT_OR_UPSELL, R.RATE_OR_AMOUNT = P.RATE_OR_AMOUNT, R.APPLIED_VALUE = P.APPLIED_VALUE
WHERE R.DISCOUNT_OR_UPSELL IS NULL AND R.RATE_OR_AMOUNT IS NULL AND R.APPLIED_VALUE IS NULL
AND P.DISCOUNT_OR_UPSELL IS NOT NULL AND P.RATE_OR_AMOUNT IS NOT NULL AND P.APPLIED_VALUE IS NOT NULL;

-- migrate trường DISCOUNT_GRUP_ID, mặc định là = 1, nhưng nếu có hơn 1 daterang thì cần order và update lại
UPDATE VD_VENDOR_PLAN_PROMOTION_DATE_RANGE R
INNER JOIN (
SELECT PLAN_PROMOTION_DATE_RANGE_SEQ, GROUP_ID FROM
(SELECT PLAN_PROMOTION_DATE_RANGE_SEQ, PLAN_PROMOTION_SEQ
, FROM_DATE, TO_DATE, APPLIED_VALUE, DISCOUNT_GROUP_ID,
ROW_NUMBER() over (PARTITION BY PLAN_PROMOTION_SEQ ORDER BY FROM_DATE) AS GROUP_ID
FROM VD_VENDOR_PLAN_PROMOTION_DATE_RANGE
WHERE RANGE_TYPE = 'STAY_DATE' ) P
WHERE GROUP_ID > 1 AND DISCOUNT_GROUP_ID = 1) TE ON R.PLAN_PROMOTION_DATE_RANGE_SEQ = TE.PLAN_PROMOTION_DATE_RANGE_SEQ
SET R.DISCOUNT_GROUP_ID = TE.GROUP_ID, LAST_UPDATE_UNO = 90037777 --
WHERE DISCOUNT_GROUP_ID = 1
;
-- set LAST_UPDATE_UNO = 90037777 để mục đích chạy các câu scripts sau, sau khi hoàn tất thì update lại 90037 ( calvin)

-- migrate BOOKING_FROM_DATETIME vào DATE_RANGE, vì logic cũ chỉ có 1 BOOKING_FROM_DATETIME nên sẽ apply cho tất cả STAY_DATE của từng promotion;
INSERT INTO VD_VENDOR_PLAN_PROMOTION_DATE_RANGE(PLAN_PROMOTION_SEQ, RANGE_TYPE, FROM_DATE, TO_DATE, SUN_YN, MON_YN, TUE_YN, WED_YN
, THU_YN, FRI_YN, SAT_YN, DISCOUNT_OR_UPSELL, RATE_OR_AMOUNT, APPLIED_VALUE, FIRST_INSERT_UNO, FIRST_INSERT_DATETIME, LAST_UPDATE_UNO, LAST_UPDATE_DATETIME, DISCOUNT_GROUP_ID)
SELECT R.PLAN_PROMOTION_SEQ, 'BOOKING_DATE', DATE (P.BOOKING_FROM_DATETIME), IFNULL,'2099-12-31')
, IFNULL, IFNULL, IFNULL
, IFNULL, IFNULL, IFNULL, IFNULL, NULL, NULL, NULL, 90037, NOW, 90037, NOW, R.DISCOUNT_GROUP_ID
FROM VD_VENDOR_PLAN_PROMOTION_DATE_RANGE R
INNER JOIN VD_VENDOR_PLAN_PROMOTION P ON P.PLAN_PROMOTION_SEQ = R.PLAN_PROMOTION_SEQ
WHERE R.RANGE_TYPE = 'STAY_DATE' AND P.BOOKING_FROM_DATETIME IS NOT NULL
AND P.FIRST_INSERT_DATETIME <= '2026-02-04 13:56:34'

1. Cause: java.sql.SQLIntegrityConstraintViolationException: Column 'DISCOUNT_OR_UPSELL' in field list is ambiguous
   ; Column 'DISCOUNT_OR_UPSELL' in field list is ambiguous*