CREATE TABLE User (
	ID INTEGER PRIMARY KEY,
	First_Name VARCHAR(255) NOT NULL,
	Last_Name VARCHAR(255) NOT NULL,
	Email VARCHAR(255) NOT NULL,
	Kills INTEGER NOT NULL DEFAULT 0,
	Deaths INTEGER NOT NULL DEFAULT 0,
	Karma INTEGER NOT NULL DEFAULT 0,
	Phone VARCHAR(127),
	Birthday DATE,
	Password_Hash VARCHAR(127),
	Permissions BIGINT NOT NULL DEFAULT 0,
	Last_Seen TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	Political_Direction INTEGER NOT NULL DEFAULT 0,
  Avatar_Url VARCHAR(255),

	UNIQUE (Email)
);

CREATE TABLE Post (
	ID INTEGER PRIMARY KEY,
	Author_ID INTEGER NOT NULL,
	Page_User_ID INTEGER NOT NULL,
	Time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	Gossip BOOLEAN DEFAULT FALSE NOT NULL,
  Type INTEGER NOT NULL,
	Content BLOB NOT NULL,

	FOREIGN KEY (Author_ID) REFERENCES User(ID),
	FOREIGN KEY (Page_User_ID) REFERENCES User(ID)
);

CREATE TABLE Message (
	ID INTEGER PRIMARY KEY,
	Sender_ID INTEGER NOT NULL,
	Recipient_ID INTEGER NOT NULL,
	Time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	Content TEXT NOT NULL,
  Read BOOLEAN DEFAULT FALSE NOT NULL,

	FOREIGN KEY (Sender_ID) REFERENCES User(ID),
	FOREIGN KEY (Recipient_ID) REFERENCES User(ID)
);

CREATE TABLE Friend (
	ID INTEGER PRIMARY KEY,
	Friend_A_ID INTEGER NOT NULL,
	Friend_B_ID INTEGER NOT NULL,

	FOREIGN KEY (Friend_A_ID) REFERENCES User(ID),
	FOREIGN KEY (Friend_B_ID) REFERENCES User(ID),
	UNIQUE (Friend_A_ID, Friend_B_ID)
);

CREATE TABLE Like (
	ID INTEGER PRIMARY KEY,
	Liker_ID INTEGER NOT NULL,
  Liked_ID INTEGER NOT NULL,
	Type INTEGER NOT NULL,

	FOREIGN KEY (Liker_ID) REFERENCES User(ID),
	FOREIGN KEY (Liked_ID) REFERENCES User(ID),
	UNIQUE (Liker_ID, Liked_ID) ON CONFLICT REPLACE
);

CREATE TABLE Activity (
  ID INTEGER PRIMARY KEY,
  Time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  Event TEXT NOT NULL
)