CREATE TABLE IF NOT EXISTS Users (
  id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(30) NOT NULL UNIQUE,
  password VARCHAR(30) NOT NULL
);

INSERT INTO Users(email, username, password) VALUES('test@test.com', 'test', 'testpassword');
INSERT INTO Users(username, password) VALUES('amber', 'password');
/*INSERT INTO Users(username, password) VALUES('test', 'testpassword');*/

CREATE TABLE IF NOT EXISTS Posts (
  id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(125) NOT NULL,
  entry VARCHAR(25500) NOT NULL,
  author INTEGER NOT NULL,

  FOREIGN KEY (author) REFERENCES Users(id)
);

INSERT INTO Posts(title, entry, author) VALUES('My Sample Post', 'This is some text.', 1);

CREATE TABLE IF NOT EXISTS Comments (
  id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  comment VARCHAR(255) NOT NULL,
  author INTEGER NOT NULL,
  post INTEGER NOT NULL,

  FOREIGN KEY (author) REFERENCES Users(id),
  FOREIGN KEY (post) REFERENCES Posts(id)
);

INSERT INTO Comments(comment, author, post) VALUES('Here is a comment.', 2, 1);

CREATE TABLE IF NOT EXISTS Friends (
  user_id INTEGER NOT NULL,
  friend_id INTEGER NOT NULL,

  FOREIGN KEY (user_id) REFERENCES Users(id),
  FOREIGN KEY (friend_id) REFERENCES Users(id)
);

INSERT INTO Friends(user_id, friend_id) VALUES(1, 2);
INSERT INTO Friends(user_id, friend_id) VALUES(2, 1);

CREATE TABLE IF NOT EXISTS Messages (
  id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  message VARCHAR(255) NOT NULL,
  sender INTEGER NOT NULL,
  recipient INTEGER NOT NULL,

  FOREIGN KEY (sender) REFERENCES Users(id),
  FOREIGN KEY (recipient) REFERENCES Users(id)
);

INSERT INTO Messages(message, sender, recipient) VALUES('This is a message.', 1, 2);
INSERT INTO Messages(message, sender, recipient) VALUES('This is a response.', 2, 1);
INSERT INTO Messages(message, sender, recipient) VALUES('Yet another response.', 2, 1);
