DROP TABLE PostComment;
DROP TABLE Post;
DROP TABLE friendsWith;
DROP TABLE follows;
DROP TABLE FBUser;

CREATE TABLE FBUser
(
  username VARCHAR(30) PRIMARY KEY,
  name VARCHAR(30) NOT NULL,
  surname VARCHAR(40) NOT NULL,
  birthday DATE,
  sex CHAR(1) CHECK (sex IN ('M', 'F'))
);

CREATE TABLE Post
(
  id_post INT PRIMARY KEY,
  dateOfPost DATE NOT NULL,
  type VARCHAR(9) DEFAULT 'friends' NOT NULL CHECK (type IN ('friends', 'public', 'followers')),
  author VARCHAR(30) NOT NULL REFERENCES FBUser(username),
  text VARCHAR(500) NOT NULL
);

CREATE TABLE PostComment
(
  id_comment INT PRIMARY KEY,
  dateOfComment DATE NOT NULL,
  id_post INT NOT NULL REFERENCES Post(id_post),
  author VARCHAR(30) NOT NULL REFERENCES FBUser(username),
  text VARCHAR(500) NOT NULL
);

CREATE TABLE friendsWith
(
  friend VARCHAR(30) NOT NULL REFERENCES FBUser(username),
  friendWith VARCHAR(30) NOT NULL REFERENCES FBUser(username),
  PRIMARY KEY (friend, friendWith)
);

CREATE TABLE follows
(
  follower VARCHAR(30) NOT NULL REFERENCES FBUser(username),
  followee VARCHAR(30) NOT NULL REFERENCES FBUser(username),
  PRIMARY KEY (follower, followee)
);