# 用户表
CREATE TABLE User(
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  nick VARCHAR(20) NOT NULL,
  gender TINYINT(2) NOT NULL DEFAULT 1,
  telephone VARCHAR(50),
  qq VARCHAR(30),
  birthday TIMESTAMP,
  address VARCHAR(255),
  portrait VARCHAR(255)
)CHARACTER SET UTF8;

# 用户与用户关注
CREATE TABLE Follow(
  u_id BIGINT, # 主动添加好友的用户
  o_id BIGINT, # 被添加好友的用户
  another_already_agree TINYINT(2) NOT NULL DEFAULT 0, # 被添加好友的用户是否同意了
  PRIMARY KEY(u_id, o_id),
  CONSTRAINT FK_F_UU FOREIGN KEY(u_id) REFERENCES User(id) ON DELETE CASCADE,
  CONSTRAINT FK_F_UO FOREIGN KEY(o_id) REFERENCES User(id) ON DELETE CASCADE
)CHARACTER SET UTF8;

# 菜谱分类表
CREATE TABLE RecipeCtg(
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL UNIQUE,
  parent_id BIGINT,
  CONSTRAINT FK_RC_RC FOREIGN KEY('parent_id') REFERENCES RecipeCtg('id') ON DELETE SET NULL
)CHARACTER SET UTF8;

# 烹饪级别表
CREATE TABLE CookieLevel(
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL UNIQUE,
  level int NOT NULL DEFAULT 1
)CHARACTER SET UTF8;

# 烹饪难度表
CREATE TABLE CookieDiff(
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL UNIQUE,
  level INT NOT NULL DEFAULT 1
)CHARACTER SET UTF8;

# 烹饪时间
CREATE TABLE CookieSpend(
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL,
  spend INT NULL DEFAULT 0
)CHARACTER SET UTF8;

# 工艺级别
CREATE TABLE Craftwork(
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL
)CHARACTER SET UTF8;

# 口味
CREATE TABLE Flavour(
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL
)CHARACTER SET UTF8;

# 菜谱
CREATE TABLE Recipe(
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL,
  finishedProductImage VARCHAR(255) NOT NULL,
  backgroundStory TEXT,
  ingredient TEXT, #主料  <---- update 2015-11-27
  seasoning TEXT, #调料
  step TEXT, #步骤
  knack TEXT, #小窍门
  keyword VARCHAR(255),
  upvote INT NOT NULL DEFAULT 0,
  isRaw TINYINT(2) NOT NULL DEFAULT 0,
  createOn TIMESTAMP NOT NULL,
  category_id BIGINT,
  cooking_level_id BIGINT,
  cookie_diff_id BIGINT,
  cookie_spend_id BIGINT,
  craftwork_id BIGINT,
  flavour_id BIGINT,
  owner_id BIGINT,
  CONSTRAINT FK_R_RC FOREIGN KEY(category_id) REFERENCES RecipeCtg(id) ON DELETE SET NULL,
  CONSTRAINT FK_R_CL FOREIGN KEY(cooking_level_id) REFERENCES CookieLevel(id) ON DELETE SET NULL,
  CONSTRAINT FK_R_CD FOREIGN KEY(cookie_diff_id) REFERENCES CookieDiff(id) ON DELETE SET NULL,
  CONSTRAINT FK_R_CS FOREIGN KEY(cookie_spend_id) REFERENCES CookieSpend(id) ON DELETE SET NULL,
  CONSTRAINT FK_R_U FOREIGN KEY(owner_id) REFERENCES User(id) ON DELETE SET NULL,
  CONSTRAINT FK_R_CR FOREIGN KEY(craftwork_id) REFERENCES Craftwork(id) ON DELETE SET NULL,
  CONSTRAINT FK_R_F FOREIGN KEY(flavour_id) REFERENCES Flavour(id) ON DELETE SET NULL
)CHARACTER SET UTF8;

# 菜单表
CREATE TABLE Menu(
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(150) NOT NULL,
  description TEXT,
  upvote INT NOT NULL DEFAULT 0,
  createOn TIMESTAMP NOT NULL
)CHARACTER SET UTF8;

# 菜单于菜谱的第三方表
CREATE TABLE Recipe_Menu(
  r_id BIGINT,
  m_id BIGINT,
  PRIMARY KEY(r_id, m_id),
  CONSTRAINT FK_RM_R FOREIGN KEY(r_id) REFERENCES Recipe(id) ON DELETE CASCADE,
  CONSTRAINT FK_RM_M FOREIGN KEY(m_id) REFERENCES Menu(id) ON DELETE CASCADE
)CHARACTER SET UTF8;

# 评论表 0->评论菜谱，1->评论菜单，2->回复评论
CREATE TABLE Comment(
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT,
  obj_id BIGINT,
  type INT NOT NULL,
  content TEXT,
  createOn TIMESTAMP NOT NULL,
  upvote INT NOT NULL DEFAULT 0,
  downvote INT NOT NULL DEFAULT 0
)CHARACTER SET UTF8;

# 动态信息日志表
# 1 -> 谁谁谁评论了你的菜单
# 2 -> 谁谁谁评论了你的菜谱
# 3 -> 谁谁谁回复了你的评论
# 4 -> 谁谁谁关注了你
# 5 -> 谁谁谁取消了对你的关注
CREATE TABLE Message(
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT,
  another_id BIGINT,
  obj_id BIGINT,
  type INT NOT NULL,
  another_already_read TINYINT(2) NOT NULL DEFAULT 0
)CHARACTER SET UTF8;



ALTER TABLE Recipe ADD COLUMN flavour_id BIGINT;
ALTER TABLE Recipe ADD COLUMN craftwork_id BIGINT;
ALTER TABLE Recipe ADD CONSTRAINT FK_R_CR FOREIGN KEY(craftwork_id) REFERENCES Craftwork(id) ON DELETE SET NULL;
ALTER TABLE Recipe ADD CONSTRAINT FK_R_F FOREIGN KEY(flavour_id) REFERENCES Flavour(id) ON DELETE SET NULL