Ellis improvement

create table VD_VENDOR_PROMOTION_CANCEL_POLICY_MASTER
(
POLICY_MASTER_SEQ bigint auto_increment
primary key,
PLAN_PROMOTION_SEQ bigint not null,
DEFAULT_YN varchar(1) not null,
NON_REFUNDABLE_YN varchar(1) not null,
USE_POLICY_OPTION_YN varchar(1) default 'N' not null,
APPLIED_FROM_DATE date not null,
APPLIED_TO_DATE date not null,
DATE_OPTION_YN varchar(1) default 'N' not null,
APPLIED_SUN_YN varchar(1) default 'N' not null,
APPLIED_MON_YN varchar(1) default 'N' not null,
APPLIED_TUE_YN varchar(1) default 'N' not null,
APPLIED_WED_YN varchar(1) default 'N' not null,
APPLIED_THU_YN varchar(1) default 'N' not null,
APPLIED_FRI_YN varchar(1) default 'N' not null,
APPLIED_SAT_YN varchar(1) default 'N' not null,
DELETE_YN varchar(1) default 'N' not null,
DISPLAY_ORDER smallint null,
DISPLAY_TITLE varchar(500) null,
FIRST_INSERT_UNO bigint default 10000 not null,
FIRST_INSERT_DATETIME datetime default CURRENT_TIMESTAMP not null,
LAST_UPDATE_UNO bigint default 10000 not null,
LAST_UPDATE_DATETIME datetime default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP
)
comment 'vendor plan promotion cancellation policy master' avg_row_length = 90;

create index XIF_VD_VENDOR_PROMOTION_CANCEL_POLICY_MASTER_01
on VD_VENDOR_PROMOTION_CANCEL_POLICY_MASTER (PLAN_PROMOTION_SEQ);

create index XIN_VD_VENDOR_PROMOTION_CANCEL_POLICY_MASTER_01
on VD_VENDOR_PROMOTION_CANCEL_POLICY_MASTER (PLAN_PROMOTION_SEQ, DEFAULT_YN);

-- 1/ Xóa các item promotion cancel policy có nonrefundable = Y
DELETE CC

1. SELECT CC.PROMOTION_CANCEL_POLICY_SEQ
   FROM VD_VENDOR_PROMOTION_CANCEL_POLICY CC
   INNER JOIN VD_VENDOR_PLAN_PROMOTION P
   ON CC.PLAN_PROMOTION_SEQ = P.PLAN_PROMOTION_SEQ
   WHERE P.NON_REFUNDABLE_YN = 'Y'

-- 2/ check promotion non-refund = Y -> Không cần tạo data trong bảng policy_master

-- 3/ Migrate all promotion policy item with non-refund = N -> policy master
INSERT INTO VD_VENDOR_PROMOTION_CANCEL_POLICY_MASTER(PLAN_PROMOTION_SEQ, DEFAULT_YN, NON_REFUNDABLE_YN, USE_POLICY_OPTION_YN
, APPLIED_FROM_DATE, APPLIED_TO_DATE, DISPLAY_ORDER, DISPLAY_TITLE)
SELECT C.PLAN_PROMOTION_SEQ, 'Y', 'N', 'N', '2020-01-01', '2099-01-01', 1, NULL
FROM VD_VENDOR_PROMOTION_CANCEL_POLICY C
GROUP BY C.PLAN_PROMOTION_SEQ

-- 4/ Update POLICY_MASTER_SEQ into VD_VENDOR_PROMOTION_CANCEL_POLICY
UPDATE VD_VENDOR_PROMOTION_CANCEL_POLICY C
INNER JOIN VD_VENDOR_PROMOTION_CANCEL_POLICY_MASTER M ON M.PLAN_PROMOTION_SEQ = C.PLAN_PROMOTION_SEQ
SET C.POLICY_MASTER_SEQ = M.POLICY_MASTER_SEQ
WHERE C.POLICY_MASTER_SEQ IS NULL AND M.POLICY_MASTER_SEQ IS NOT NULL