CREATE TABLE CUSTOMER_MASTER (
CUSTOMER_ID VARCHAR(32) NOT NULL,
CUSTOMER_NAME VARCHAR(100) NOT NULL,
GENDER CHAR(1),
BIRTH_DATE DATE,
ID_TYPE VARCHAR(20),
ID_NUMBER VARCHAR(50),
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_CUSTOMER_MASTER PRIMARY KEY (CUSTOMER_ID)
);

CREATE INDEX IDX_CUSTOMER_ID_NUMBER ON CUSTOMER_MASTER(ID_TYPE, ID_NUMBER);

CREATE TABLE CUSTOMER_CONTACT (
CONTACT_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
CUSTOMER_ID VARCHAR(32) NOT NULL,
CONTACT_TYPE VARCHAR(20) NOT NULL,
CONTACT_VALUE VARCHAR(200) NOT NULL,
IS_PRIMARY CHAR(1) DEFAULT ‘N’,
CONSTRAINT PK_CUSTOMER_CONTACT PRIMARY KEY (CONTACT_ID)
);

CREATE INDEX IDX_CUSTOMER_CONTACT ON CUSTOMER_CONTACT(CUSTOMER_ID, CONTACT_TYPE);

CREATE OR REPLACE FUNCTION FN_MASK_ID_CARD(p_id_card VARCHAR(18))
RETURNS VARCHAR(18)
LANGUAGE SQL
BEGIN
RETURN SUBSTR(p_id_card, 1, 6) || ‘********’ || SUBSTR(p_id_card, 15, 4);
END;

CREATE OR REPLACE FUNCTION FN_MASK_PHONE(p_phone VARCHAR(11))
RETURNS VARCHAR(11)
LANGUAGE SQL
BEGIN
RETURN SUBSTR(p_phone, 1, 3) || ‘****’ || SUBSTR(p_phone, 8, 4);
END;

CREATE ROLE CUSTOMER_READONLY;
CREATE ROLE CUSTOMER_OPERATOR;
CREATE ROLE CUSTOMER_ADMIN;

GRANT SELECT ON TABLE CUSTOMER_MASTER TO CUSTOMER_READONLY;
GRANT SELECT, UPDATE ON TABLE CUSTOMER_MASTER TO CUSTOMER_OPERATOR;
GRANT ALL ON TABLE CUSTOMER_MASTER TO CUSTOMER_ADMIN;

CREATE TABLE CUSTOMER_ARCHIVE LIKE CUSTOMER_MASTER;

CREATE OR REPLACE PROCEDURE SP_ARCHIVE_CUSTOMER(
IN p_archive_date DATE
)
LANGUAGE SQL
BEGIN
INSERT INTO CUSTOMER_ARCHIVE
SELECT * FROM CUSTOMER_MASTER
WHERE UPDATE_TIME < p_archive_date; DELETE FROM CUSTOMER_MASTER WHERE UPDATE_TIME < p_archive_date; END;

DELETE FROM CUSTOMER_CONTACT
WHERE CUSTOMER_ID NOT IN (
SELECT CUSTOMER_ID FROM CUSTOMER_MASTER
);