CREATE TABLE `member` (
	`member_id`	int	NOT NULL	COMMENT 'Comment',
	`nickname`	varchar(255)	NOT NULL,
	`email`	varchar(255)	NOT NULL,
	`password`	varchar(255)	NOT NULL,
	`is_adult`	tinyint	NOT NULL,
	`regtime`	timestamp	NOT NULL
);

CREATE TABLE `wine` (
	`wine_id`	int	NOT NULL,
	`name`	varchar(255)	NOT NULL,
	`price`	int	NULL,
	`winery`	varchar(255)	NULL,
	`grapes`	text	NULL,
	`country`	varchar(100)	NULL,
	`regions`	text	NULL,
	`allergens`	text	NULL,
	`cat1`	varchar(255)	NULL,
	`cat2`	varchar(255)	NULL,
	`rating_avg`	float	NULL,
	`rating_num`	int	NULL,
	`taste`	text	NULL,
	`acidic`	float	NULL,
	`alcohol_content`	float	NULL,
	`bold`	float	NULL,
	`tannic`	float	NULL,
	`sweet`	float	NULL,
	`style`	varchar(255)	NULL,
	`food_parings`	text	NULL
);

CREATE TABLE `free_board` (
	`article_id`	int	NOT NULL,
	`member_id`	int	NOT NULL	COMMENT 'Comment',
	`title`	varchar(255)	NOT NULL,
	`content`	Text	NOT NULL,
	`photo`	Text	NULL,
	`tag`	Text	NULL,
	`regtime`	timestamp	NOT NULL
);

CREATE TABLE `resell_board` (
	`article_id`	int	NOT NULL,
	`member_id`	int	NOT NULL	COMMENT 'Comment',
	`title`	varchar(255)	NOT NULL,
	`content`	Text	NOT NULL,
	`photo`	Text	NULL,
	`tag`	Text	NULL,
	`price`	int	NOT NULL,
	`regtime`	timestamp	NOT NULL
);

CREATE TABLE `resell_article_comment` (
	`comment_id`	int	NOT NULL,
	`article_id`	int	NOT NULL,
	`member_id`	int	NOT NULL	COMMENT 'Comment',
	`content`	Text	NOT NULL,
	`regtime`	timestamp	NOT NULL
);

CREATE TABLE `free_article_comment` (
	`comment_id`	int	NOT NULL,
	`article_id`	int	NOT NULL,
	`member_id`	int	NOT NULL	COMMENT 'Comment',
	`content`	Text	NOT NULL,
	`regtime`	timestamp	NOT NULL
);

CREATE TABLE `resell_article_like` (
	`resell_like_id`	int	NOT NULL,
	`article_id`	int	NOT NULL,
	`member_id`	int	NOT NULL	COMMENT 'Comment'
);

CREATE TABLE `free_article_like` (
	`free_like_id`	int	NOT NULL,
	`article_id`	int	NOT NULL,
	`member_id`	int	NOT NULL	COMMENT 'Comment'
);

CREATE TABLE `wine_wishlist` (
	`wish_id`	int	NOT NULL,
	`member_id`	int	NOT NULL	COMMENT 'Comment',
	`wine_id`	int	NOT NULL
);

CREATE TABLE `wine_review` (
	`wine_review_id`	VARCHAR(255)	NOT NULL,
	`wine_id`	int	NOT NULL,
	`member_id`	int	NOT NULL	COMMENT 'Comment',
	`rating`	VARCHAR(255)	NULL,
	`content`	VARCHAR(255)	NULL,
	`regtime`	VARCHAR(255)	NULL
);

CREATE TABLE `CopyOfwine` (
	`wine_id`	int	NOT NULL,
	`name`	varchar(255)	NOT NULL,
	`price`	int	NULL,
	`winery`	varchar(255)	NULL,
	`grapes`	text	NULL,
	`country`	varchar(100)	NULL,
	`regions`	text	NULL,
	`allergens`	text	NULL,
	`cat1`	varchar(255)	NULL,
	`cat2`	varchar(255)	NULL,
	`rating_avg`	float	NULL,
	`rating_num`	int	NULL,
	`earthy`	int	NULL,
	`dried_fruit`	int	NULL,
	`tree_fruit`	int	NULL,
	`oaky`	int	NULL,
	`citrus`	int	NULL,
	`ageing`	int	NULL,
	`yeasty`	int	NULL,
	`floral`	int	NULL,
	`black_fruit`	int	NULL,
	`tropical`	int	NULL,
	`spices`	int	NULL,
	`vegetal`	int	NULL,
	`redfruit`	int	NULL,
	`acidic`	float	NULL,
	`alcohol_content`	float	NULL,
	`bold`	float	NULL,
	`tannic`	float	NULL,
	`sweet`	float	NULL,
	`style`	varchar(255)	NULL,
	`food_parings`	text	NULL
);

CREATE TABLE `notice` (
	`notice_id`	int	NOT NULL,
	`member_id`	int	NOT NULL	COMMENT 'Comment',
	`board_id`	int	NULL	COMMENT '게시판 구분',
	`article_id`	int	NULL	COMMENT '글 id',
	`content`	text	NULL	COMMENT '내용'
);

ALTER TABLE `member` ADD CONSTRAINT `PK_MEMBER` PRIMARY KEY (
	`member_id`
);
ALTER TABLE `member` MODIFY `member_id` INT NOT NULL AUTO_INCREMENT;

ALTER TABLE `wine` ADD CONSTRAINT `PK_WINE` PRIMARY KEY (
	`wine_id`
);
ALTER TABLE `wine` MODIFY `wine_id` INT NOT NULL AUTO_INCREMENT;

ALTER TABLE `free_board` ADD CONSTRAINT `PK_FREE_BOARD` PRIMARY KEY (
	`article_id`
);
ALTER TABLE `free_board` MODIFY `article_id` INT NOT NULL AUTO_INCREMENT;

ALTER TABLE `resell_board` ADD CONSTRAINT `PK_RESELL_BOARD` PRIMARY KEY (
	`article_id`
);
ALTER TABLE `resell_board` MODIFY `article_id` INT NOT NULL AUTO_INCREMENT;

ALTER TABLE `resell_article_comment` ADD CONSTRAINT `PK_RESELL_ARTICLE_COMMENT` PRIMARY KEY (
	`comment_id`
);
ALTER TABLE `resell_article_comment` MODIFY `comment_id` INT NOT NULL AUTO_INCREMENT;

ALTER TABLE `free_article_comment` ADD CONSTRAINT `PK_FREE_ARTICLE_COMMENT` PRIMARY KEY (
	`comment_id`
);
ALTER TABLE `free_article_comment` MODIFY `comment_id` INT NOT NULL AUTO_INCREMENT;

ALTER TABLE `resell_article_like` ADD CONSTRAINT `PK_RESELL_ARTICLE_LIKE` PRIMARY KEY (
	`resell_like_id`
);
ALTER TABLE `resell_article_like` MODIFY `resell_like_id` INT NOT NULL AUTO_INCREMENT;

ALTER TABLE `free_article_like` ADD CONSTRAINT `PK_FREE_ARTICLE_LIKE` PRIMARY KEY (
	`free_like_id`
);
ALTER TABLE `free_article_like` MODIFY `free_like_id` INT NOT NULL AUTO_INCREMENT;

ALTER TABLE `wine_wishlist` ADD CONSTRAINT `PK_WINE_WISHLIST` PRIMARY KEY (
	`wish_id`
);
ALTER TABLE `wine_wishlist` MODIFY `wish_id` INT NOT NULL AUTO_INCREMENT;

ALTER TABLE `wine_review` ADD CONSTRAINT `PK_WINE_REVIEW` PRIMARY KEY (
	`wine_review_id`
);
ALTER TABLE `wine_review` MODIFY `wine_review_id` INT NOT NULL AUTO_INCREMENT;

ALTER TABLE `CopyOfwine` ADD CONSTRAINT `PK_COPYOFWINE` PRIMARY KEY (
	`wine_id`
);
ALTER TABLE `CopyOfwine` MODIFY `wine_id` INT NOT NULL AUTO_INCREMENT;

ALTER TABLE `notice` ADD CONSTRAINT `PK_NOTICE` PRIMARY KEY (
	`notice_id`
);
ALTER TABLE `notice` MODIFY `notice_id` INT NOT NULL AUTO_INCREMENT;