CREATE database IF NOT EXISTS Durbin;
use Durbin;
create table IF NOT EXISTS Users(
	username varchar(255) NOT NULL,
	password varchar(255) NOT NULL,
	salt varchar(255) NOT NULL,
	photoUrl varchar(255),
	seekerBadges int NOT NULL,
	virtualCash double NOT NULL default 0,
	PRIMARY KEY (username)
);
create table IF NOT EXISTS Payments(
	paymentID int NOT NULL AUTO_INCREMENT,
	amount double NOT NULL default 0,
	imageUrl varchar(255) NOT NULL,
	PRIMARY KEY (paymentID)
);
create table IF NOT EXISTS UserPayments(
	username varchar(255) NOT NULL,
	paymentID int NOT NULL,
	paymentTime datetime NOT NULL,
	destinationTransactionID varchar(255) NOT NULL,
	amount double NOT NULL default 0,
	status varchar(255) NOT NULL,
	PRIMARY KEY (username,paymentID),
	FOREIGN KEY(username) REFERENCES Users(username) ON DELETE CASCADE,
	FOREIGN KEY(paymentID) REFERENCES Payments(paymentID) ON DELETE CASCADE
);
create table IF NOT EXISTS Places(
	placeID int NOT NULL AUTO_INCREMENT,
	name varchar(255) NOT NULL,
	description varchar(255) NOT NULL,
	photoUrl varchar(255) NOT NULL,
	latitude double NOT NULL,
	longitude double NOT NULL,
	PRIMARY KEY (placeID)
);
create table IF NOT EXISTS PlaceImages(
	placeImageID int NOT NULL AUTO_INCREMENT,
	placeID int NOT NULL,
	photoUrl varchar(255) NOT NULL,
	PRIMARY KEY (placeImageID),
	FOREIGN KEY(placeID) REFERENCES Places(placeID) ON DELETE CASCADE
);
create table IF NOT EXISTS UserMatchedPlaces(
	username varchar(255) NOT NULL,
	placeImageID int NOT NULL,
	startTime datetime NOT NULL,
	finishTime datetime NOT NULL,
	PRIMARY KEY (username,placeImageID),
	FOREIGN KEY(username) REFERENCES Users(username) ON DELETE CASCADE,
	FOREIGN KEY(placeImageID) REFERENCES PlaceImages(placeImageID) ON DELETE CASCADE
);
create table IF NOT EXISTS UserUnlockedPlaces(
	username varchar(255) NOT NULL,
	placeID int NOT NULL,
	played int NOT NULL,
	PRIMARY KEY (username,placeID),
	FOREIGN KEY(username) REFERENCES Users(username) ON DELETE CASCADE,
	FOREIGN KEY(placeID) REFERENCES Places(placeID) ON DELETE CASCADE
);
create table IF NOT EXISTS PlaceHints(
	hintID int NOT NULL AUTO_INCREMENT,
	placeID int NOT NULL,
	title varchar(255) NOT NULL,
	description varchar(255) NOT NULL,
	imageUrl varchar(255) NOT NULL,
	price double NOT NULL default 0,
	PRIMARY KEY (hintID),
	FOREIGN KEY(placeID) REFERENCES Places(placeID) ON DELETE CASCADE
);
create table IF NOT EXISTS UserPurchasedHints(
	username varchar(255) NOT NULL,
	hintID int NOT NULL,
	buyTime datetime NOT NULL,
	PRIMARY KEY (username,hintID),
	FOREIGN KEY(username) REFERENCES Users(username) ON DELETE CASCADE,
	FOREIGN KEY(hintID) REFERENCES PlaceHints(hintID) ON DELETE CASCADE
);