WISEFEE_SCHEMA.txt

CREATE TABLE ADDRESS (
    ADDR_ID BIGINT NOT NULL AUTO_INCREMENT,
    LOCATION VARCHAR(100) NOT NULL,
    ADDRESS_NAME VARCHAR(200) NOT NULL,
    ADDRESS_DETAIL VARCHAR(300) NOT NULL,
    ZIP_CODE VARCHAR(30) NULL,
    LNG INT NULL,
    LAT INT NULL,
    PREFER_LOCATION VARCHAR(50) NULL,
    CREATED_AT DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UPDATED_AT DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (ADDR_ID)
);


CREATE TABLE SUBSCRIBE (
	SUB_ID	BIGINT NOT NULL AUTO_INCREMENT,
	SUBTCT_ID	BIGINT	NOT NULL,
	PAYMENT_ID	BIGINT	NOT NULL,
	MEMBER_ID	BIGINT	NOT NULL,
	CAFE_ID	BIGINT	NOT NULL,
	TOTAL_PRICE	INT	NULL,
	SUB_COMMENT	VARCHAR(3000)	NULL,
	SUB_TYPE	VARCHAR(100)	NOT NULL,
	SUB_PEOPLE	INT	NOT NULL,
    CREATED_AT DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UPDATED_AT DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (SUB_ID, SUBTCT_ID, PAYMENT_ID)
);

CREATE TABLE CAFE (
	CAFE_ID	BIGINT NOT NULL AUTO_INCREMENT,
	MEMBER_ID	BIGINT	NOT NULL,
	ADDR_ID	BIGINT	NOT NULL,
	TITLE	VARCHAR(255)	NOT NULL,
	CONTENT	VARCHAR(255)	NULL,
	CAFE_PHONE	VARCHAR(20)	NOT NULL,
	FILE_ID	BIGINT	NOT NULL,
    CREATED_AT DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UPDATED_AT DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (CAFE_ID, MEMBER_ID, ADDR_ID)
);

CREATE TABLE PAYMENT (
	PAYMENT_ID	BIGINT NOT NULL AUTO_INCREMENT,
	PAYMENT_PRICE	INT	NOT NULL,
	PAYMENT_INFO	VARCHAR(1000)	NOT NULL,
    CREATED_AT DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (PAYMENT_ID)
);


CREATE TABLE CART (
	CART_ID BIGINT NOT NULL AUTO_INCREMENT,
	MEMBER_ID	INT	NOT NULL,
	CAFE_ID BIGINT	NOT NULL,
	CAFE_QUANTITY	INT NOT	NULL,
	CART_STATUS	VARCHAR(1)	NOT NULL,
    CREATED_AT DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UPDATED_AT DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (CART_ID)
);

CREATE TABLE NOTIFICATIONS (
	NOTIFICATION_ID	BIGINT NOT NULL AUTO_INCREMENT,
	MEMBER_ID	BIGINT	NOT NULL,
	NOTIFICATION_MESSAGE	VARCHAR(2000)	NOT NULL,
	NOTIFICATION_STATUS	VARCHAR(1)	NOT NULL,
	NOTIFICATION_TYPE	VARCHAR(30)	NOT NULL,
	REFERENCE_ID	VARCHAR(50)	NULL,
	CAFE_ID	BIGINT	NOT NULL,
    CREATED_AT DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UPDATED_AT DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (NOTIFICATION_ID, MEMBER_ID)
);

CREATE TABLE ORDERS (
	ORDER_ID	BIGINT NOT NULL AUTO_INCREMENT,
	SUB_ID	BIGINT	NOT NULL,
	PAYMENT_ID	BIGINT	NOT NULL,
	TUMBLR_STATUS	VARCHAR(1)	NOT NULL,
	PRODUCT_RECEIVE_TIME	DATETIME	NULL,
	PRODUCT_STATUS	VARCHAR(30)	NOT NULL,
    CREATED_AT DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UPDATED_AT DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (ORDER_ID, SUB_ID, PAYMENT_ID)
);

CREATE TABLE PRODUCT (
	PRODUCT_ID	BIGINT NOT NULL AUTO_INCREMENT,
	CAFE_ID	BIGINT	NOT NULL,
	PRODUCT_NAME	VARCHAR(100)	NOT NULL,
	PRODUCT_PRICE	INT	NOT NULL,
	PRODUCT_INFO	VARCHAR(2000)	NULL,
    CREATED_AT DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UPDATED_AT DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (PRODUCT_ID)
);

CREATE TABLE ORDER_PRODUCT (
	ORDER_PRODUCT_ID BIGINT NOT NULL AUTO_INCREMENT,
	ORDER_ID	BIGINT NOT NULL,
	PRODUCT_ID	BIGINT	NOT NULL,
	PAYMENT_PRICE	BIGINT	NOT NULL,
	PRIMARY KEY(ORDER_PRODUCT_ID)
);

CREATE TABLE FILE (
	FILE_ID	BIGINT NOT NULL AUTO_INCREMENT,
	FILE_TYPE	VARCHAR(50)	NOT NULL,
	NAME	VARCHAR(255)	NOT NULL,
	FILE_CAPACITY	VARCHAR(10)	NOT NULL,
	FILE_PATH	VARCHAR(500)	NOT NULL,
	MEMBER_ID	BIGINT	NOT NULL,
	IS_DELETED	BOOLEAN	NOT NULL,
	FILE_INFO VARCHAR(1000) NULL,
    CREATED_AT DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UPDATED_AT DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (FILE_ID)
);

CREATE TABLE PRODUCT_IMAGE (
	PRODUCT_IMAGE_ID BIGINT	NOT NULL AUTO_INCREMENT,
	PRODUCT_ID	BIGINT NOT NULL,
	FILE_ID	BIGINT	NOT NULL,
	PRIMARY KEY(PRODUCT_IMAGE_ID, PRODUCT_ID, FILE_ID)
);

CREATE TABLE MEMBER (
	MEMBER_ID	BIGINT NOT NULL AUTO_INCREMENT,
	NICKNAME	VARCHAR(100)	NOT NULL,
	EMAIL	VARCHAR(150)	NOT NULL,
	PHONE	VARCHAR(20)	NOT NULL,
	PHONE_OFFICE	VARCHAR(20)	NULL,
	BIRTH	VARCHAR(10)	NOT NULL,
	PASSWORD	VARCHAR(100)	NOT NULL,
	ACCOUNT_TYPE	VARCHAR(1)	NOT NULL,
	AUTH_TYPE	VARCHAR(50)	NOT NULL,
	IS_AUTH_EMAIL	VARCHAR(1)	NOT NULL,
	IS_ALLOW_PUSH_MSG	VARCHAR(1)	NOT NULL,
	PUSH_MSG_TOKEN	VARCHAR(500)	NOT NULL,
	MEMBER_STATUS	VARCHAR(1)	NOT NULL,
	2FE1	VARCHAR(1)	NULL,
	ADDR_ID	BIGINT	NOT NULL,
    CREATED_AT DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UPDATED_AT DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (MEMBER_ID)
);

CREATE TABLE SUBTICKET_TYPE (
	SUBTCT_ID	BIGINT NOT NULL AUTO_INCREMENT,
	SUBTCT_NAME	VARCHAR(50)	NOT NULL,
	SUBTCT_PRICE	INT	NOT NULL,
	SUBTCT_MIN_USER_CNT	INT	NOT NULL,
	SUBTCT_MAX_USER_CNT	INT	NOT NULL,
	SUBTCT_DEPOSIT	INT	NOT NULL,
	SUBTCT_BASE_DISCNT_RATE	INT	NOT NULL,
	SUBTCT_ADT_DISCNT_RATE	INT	NOT NULL,
	SUBTCT_MAX_DISCNT_RATE	INT	NOT NULL,
	SUBTCT_DESC	TEXT	NULL,
    CREATED_AT DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UPDATED_AT DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (SUBTCT_ID)
);

CREATE TABLE PRODUCT_OPTION (
	PRD_OPTION_ID	BIGINT NOT NULL AUTO_INCREMENT,
	PRODUCT_ID	BIGINT	NOT NULL,
	PRD_OPTION_NAME	VARCHAR(50)	NOT NULL,
    CREATED_AT DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UPDATED_AT DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (PRD_OPTION_ID, PRODUCT_ID)
);

CREATE TABLE PRODUCT_OPT_CHOICE (
	PRD_OPTCHOICE_ID	BIGINT NOT NULL AUTO_INCREMENT,
	PRD_OPTION_ID	BIGINT	NOT NULL,
	PRD_OPTCHOICE_NAME	VARCHAR(50)	NOT NULL,
	PRD_OPTCHOICE_PRICE	INT	NOT NULL,
    CREATED_AT DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UPDATED_AT DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (PRD_OPTCHOICE_ID, PRD_OPTION_ID)
);

CREATE TABLE ORDER_OPTION (
	ORDER_OPTION_ID 	BIGINT NOT NULL AUTO_INCREMENT,
	ORDER_ID	BIGINT	NOT NULL,
	CAFE_ID	BIGINT	NOT NULL,
	ORDER_OPTION_NAME	VARCHAR(50)	NOT NULL,
	ORDER_OPTION_PRICE	INT	NOT NULL,
    CREATED_AT DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UPDATED_AT DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (ORDER_OPTION_ID, ORDER_ID, CAFE_ID)
);

ALTER TABLE SUBSCRIBE ADD CONSTRAINT FK_SUBTICKET_TYPE_TO_SUBSCRIBE_1 FOREIGN KEY (
	SUBTCT_ID
)
REFERENCES SUBTICKET_TYPE (
	SUBTCT_ID
);

ALTER TABLE SUBSCRIBE ADD CONSTRAINT FK_PAYMENT_TO_SUBSCRIBE_1 FOREIGN KEY (
	PAYMENT_ID
)
REFERENCES PAYMENT (
	PAYMENT_ID
);

ALTER TABLE CAFE ADD CONSTRAINT FK_MEMBER_TO_CAFE_1 FOREIGN KEY (
	MEMBER_ID
)
REFERENCES MEMBER (
	MEMBER_ID
);

ALTER TABLE CAFE ADD CONSTRAINT FK_ADDRESS_TO_CAFE_1 FOREIGN KEY (
	ADDR_ID
)
REFERENCES ADDRESS (
	ADDR_ID
);

ALTER TABLE NOTIFICATIONS ADD CONSTRAINT FK_MEMBER_TO_NOTIFICATIONS_1 FOREIGN KEY (
	MEMBER_ID
)
REFERENCES MEMBER (
	MEMBER_ID
);

ALTER TABLE ORDERS ADD CONSTRAINT FK_SUBSCRIBE_TO_ORDERS_1 FOREIGN KEY (
	SUB_ID
)
REFERENCES SUBSCRIBE (
	SUB_ID
);

ALTER TABLE ORDERS ADD CONSTRAINT FK_PAYMENT_TO_ORDERS_1 FOREIGN KEY (
	PAYMENT_ID
)
REFERENCES PAYMENT (
	PAYMENT_ID
);

ALTER TABLE PRODUCT_IMAGE ADD CONSTRAINT FK_PRODUCT_TO_PRODUCT_IMAGE_1 FOREIGN KEY (
	PRODUCT_ID
)
REFERENCES PRODUCT (
	PRODUCT_ID
);

ALTER TABLE PRODUCT_IMAGE ADD CONSTRAINT FK_FILE_TO_PRODUCT_IMAGE_1 FOREIGN KEY (
	FILE_ID
)
REFERENCES FILE (
	FILE_ID
);

ALTER TABLE PRODUCT_OPTION ADD CONSTRAINT FK_PRODUCT_TO_PRODUCT_OPTION_1 FOREIGN KEY (
	PRODUCT_ID
)
REFERENCES PRODUCT (
	PRODUCT_ID
);

ALTER TABLE PRODUCT_OPT_CHOICE ADD CONSTRAINT FK_PRODUCT_OPTION_TO_PRODUCT_OPT_CHOICE_1 FOREIGN KEY (
	PRD_OPTION_ID
)
REFERENCES PRODUCT_OPTION (
	PRD_OPTION_ID
);

ALTER TABLE ORDER_OPTION ADD CONSTRAINT FK_ORDER_TO_ORDER_OPTION_1 FOREIGN KEY (
	ORDER_ID
)
REFERENCES ORDERS (
	ORDER_ID
);

ALTER TABLE ORDER_OPTION ADD CONSTRAINT FK_CAFE_TO_ORDER_OPTION_1 FOREIGN KEY (
	CAFE_ID
)
REFERENCES CAFE (
	CAFE_ID
);