CREATE TABLE `user`(
	name varchar(30) not null,
	regno varchar(50) not null,
	phone bigint(11) not null,
	password varchar(60),
	block varchar(100),
	roomno varchar(100) default "",
	primary key(regno)
	);

CREATE TABLE book(
	id int not null auto_increment,
	name varchar(100) not null,
	author varchar(100) not null,
	course varchar(50) not null,
	price int not null default 0,
	health varchar(200) not null,
	owner_id varchar(50) not null,
	imageuploaded tinyint(1) default 0,
	dispmyname tinyint(1) default 1,
	dispmyemail tinyint(1) default 1,
	dispmyphone tinyint(1) default 1,
	dispmyhostel tinyint(1) default 1,
	primary key(id),
	foreign key(owner_id) references user(regno)
	);
CREATE TABLE grievance(
	id int not null auto_increment,
	name varchar(100) not null,
	authority varchar(100) not null,
	health varchar(200) not null,
	location varchar(200) not null,
	owner_id varchar(50) not null,
	imageuploaded tinyint(1) default 0,
	dispmyname tinyint(1) default 1,
	dispmyemail tinyint(1) default 1,
	dispmyphone tinyint(1) default 1,
	dispmyhostel tinyint(1) default 1,
	upvotes int default 0,
	primary key(id),
	foreign key(owner_id) references user(regno)
	);
CREATE TABLE recommendation(
	id int not null auto_increment,
	name varchar(100) not null,
	authority varchar(100) not null,
	health varchar(200) not null,
	stars int,
	location varchar(200) not null,
	owner_id varchar(50) not null,
	imageuploaded tinyint(1) default 0,
	dispmyname tinyint(1) default 1,
	dispmyemail tinyint(1) default 1,
	dispmyphone tinyint(1) default 1,
	dispmyhostel tinyint(1) default 1,
	upvotes int default 0,
	primary key(id),
	foreign key(owner_id) references user(regno)
	);
CREATE TABLE news(
	id int not null auto_increment,
	name varchar(100) not null,
	health varchar(200) not null,
	location varchar(200) not null,
	owner_id varchar(50) not null,
	imageuploaded tinyint(1) default 0,
	dispmyname tinyint(1) default 1,
	dispmyemail tinyint(1) default 1,
	dispmyphone tinyint(1) default 1,
	dispmyhostel tinyint(1) default 1,
	upvotes int default 0,
	primary key(id),
	foreign key(owner_id) references user(regno)
	);

CREATE TABLE requests(
	id int not null auto_increment,
	from_id varchar(50) not null,
	to_id varchar(50) not null,
	book_id int not null,
	readed tinyint(1) default 0, 
	primary key(id),
	foreign key(to_id) references user(regno),
	foreign key(from_id) references user(regno)
	);

CREATE TABLE transactions(
	id int not null auto_increment,
	book_id int not null,
	buyer_id varchar(9) not null,
	sold_borrowed tinyint(1) not null,
	cost int default 0,
	due_return_date date,
	act_return_date date,
	primary key(id),
	foreign key(book_id) references book,
	foreign key(buyer_id) references user
);
CREATE TABLE reviews(
	id int not null auto_increment,
	regno varchar(9) not null,
	name varchar(50) not null,
	fbid varchar(60),
	message text not null,
	primary key(id)
);
