/*
Etap 1: logowanie i rejestracja
login.php?email=&password=
register.php?email=&name=&surname=&password=
*/
CREATE TABLE IF NOT EXISTS Users
(
Email VARCHAR(60) NOT NULL,
Name VARCHAR(20) NOT NULL,
Surname VARCHAR(30) NOT NULL,
Password VARCHAR(30) NOT NULL,
PRIMARY KEY (Email)
);

/*
Etap 2: tworzenie, wyszukiwanie i dołączanie do grup
add_group.php?name=&description=&password=&user=&color=
edit_group.php?id=&name=&description=&password=
edit_group_member.php?group=&user=&color=
edit_group_member.php?group=&user=&status=
delete_group.php?id=
*get_users_groups.php?user=
*add_group_tag.php
*delete_group_tag.php
*/
CREATE TABLE IF NOT EXISTS Groups
(
Id INT NOT NULL AUTO_INCREMENT,
Name VARCHAR(40) NOT NULL,
Description TEXT,
Password VARCHAR(40),
PRIMARY KEY (Id)
);

CREATE TABLE IF NOT EXISTS GroupMembers
(
Group_id INT NOT NULL,
User_id VARCHAR(60) NOT NULL,
Marker_color VARCHAR(7) /* hex #A668C3 */,
Status VARCHAR(10) NOT NULL, /* owner, admin, member */
PRIMARY KEY (Group_id, User_id),
FOREIGN KEY (Group_id) REFERENCES Groups(Id),
FOREIGN KEY (User_id) REFERENCES Users(Id)
);

CREATE TABLE IF NOT EXISTS GroupTags
(
Group_id INT NOT NULL,
Tag VARCHAR(20) NOT NULL,
PRIMARY KEY (Group_id, Tag),
FOREIGN KEY (Group_id) REFERENCES Groups(Id)
);

/* Etap 3: tworzenie miejsc przez grupy lub użytkowników */
CREATE TABLE IF NOT EXISTS Spots
(
Id INT NOT NULL AUTO_INCREMENT,
Name VARCHAR(30) NOT NULL,
Latitude DECIMAL NOT NULL,
Longitude DECIMAL NOT NULL,
Description TEXT,
PRIMARY KEY (Id)
);

CREATE TABLE IF NOT EXISTS SpotsOwnedByUsers
(
Spot_id INT NOT NULL,
User_id INT NOT NULL,
FOREIGN KEY (Spot_id) REFERENCES Spots(Id),
FOREIGN KEY (User_id) REFERENCES Users(Id)
);

CREATE TABLE IF NOT EXISTS SpotsOwnedByGroups
(
Spot_id INT NOT NULL,
Group_id INT NOT NULL,
FOREIGN KEY (Spot_id) REFERENCES Spots(Id),
FOREIGN KEY (Group_id) REFERENCES Groups(Id)
);

/* Etap 4: tworzenie wydarzeń w określonych miejscach przez grupy lub użytkowników */
CREATE TABLE IF NOT EXISTS Events
(
Id INT NOT NULL AUTO_INCREMENT,
Name VARCHAR(30) NOT NULL,
Spot_id INT NOT NULL,
Start_time DATETIME,
End_time DATETIME,
Description TEXT,
PRIMARY KEY (Id),
FOREIGN KEY (Spot_id) REFERENCES Spots(Id)
);

CREATE TABLE IF NOT EXISTS EventsOwnedByUsers
(
Event_id INT NOT NULL,
User_id INT NOT NULL,
FOREIGN KEY (Event_id) REFERENCES Events(Id),
FOREIGN KEY (User_id) REFERENCES Users(Id)
);

CREATE TABLE IF NOT EXISTS EventsOwnedByGroups
(
Event_id INT NOT NULL,
Group_id INT NOT NULL,
FOREIGN KEY (Event_id) REFERENCES Events(Id),
FOREIGN KEY (Group_id) REFERENCES Groups(Id)
);