
create table users (
	ID serial primary key not null,
	name VARCHAR(256) not null,
	email VARCHAR(256) not null unique,
	"password" VARCHAR(256) not null,
	"location" VARCHAR(256) not null
);

create table business (
	ID serial primary key not null,
	user_id INT not null,
	address VARCHAR(256) not null,
	foreign key (user_id) references users(ID)
);

create table client (
	ID serial primary key not null,
	user_id INT,
	date_of_birth DATE not null,
	foreign key (user_id) references users(ID)
);

create table feedback (
	ID serial primary key not null,
	client_id INT not null,
	business_id INT not null,
	"comment" VARCHAR(256) not null,
	rating INT not null,
	foreign key (client_id) references client(ID),
	foreign key (business_id) references business(ID)
);

create table competition (
	ID serial primary key not null,
	client_id INT not null,
	business_id INT not null,
	name VARCHAR(256) not null,
	eligibility INT not null,
	foreign key (client_id) references client(ID),
	foreign key (business_id) references business(ID)
);

create table "subscription" (
	ID serial primary key not null,
	client_id INT not null,
	"type" VARCHAR(256) not null,
	price INT not null,
	created_at TIMESTAMP default CURRENT_TIMESTAMP not null,
	valid_by TIMESTAMP default CURRENT_TIMESTAMP + INTERVAL '30 days' not null,
	foreign key (client_id) references client(ID)
);

create table guest_pay (
	ID serial primary key not null,
	client_id INT not null,
	"type" VARCHAR(256) not null,
	amount INT not null,
	foreign key (client_id) references client(ID)
);

create table reservation (
	ID serial primary key not null,
	client_id INT not null,
	business_id INT not null,
	"time" TIME not null,
	"date" DATE not null,
	foreign key (client_id) references client(ID),
	foreign key (business_id) references business(ID)
);