drop database if exists group44;
create database group44;
use group44;

CREATE TABLE clubs(
id INT auto_increment PRIMARY KEY,
sportType VARCHAR(50) NOT NULL
);

CREATE TABLE coaches(
id INT auto_increment PRIMARY KEY,
coachesName VARCHAR(255) NOT NULL,
egn CHAR(10) NOT NULL UNIQUE
);

CREATE TABLE students(
id INT auto_increment PRIMARY KEY,
studentName VARCHAR(255) NOT NULL,
facNum CHAR(10) NOT NULL UNIQUE,
phone VARCHAR(55) NULL
);

CREATE TABLE groups(
id INT auto_increment PRIMARY KEY,
dayOfWeek ENUM('MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY', 'SUNDAY'),
hourOfDay TIME NOT NULL,
location VARCHAR(255) NOT NULL,
club_id INT NOT NULL,
coach_id INT NOT NULL,
CONSTRAINT FOREIGN KEY(club_id) REFERENCES clubs(id),
CONSTRAINT FOREIGN KEY(coach_id) REFERENCES coaches(id),
UNIQUE KEY(dayOfWeek, hourOfDay, location)
);

CREATE TABLE student_group(
student_id INT NOT NULL,
group_id INT NOT NULL,
CONSTRAINT FOREIGN KEY(student_id) REFERENCES students(id),
CONSTRAINT FOREIGN KEY(group_id) REFERENCES groups(id),
PRIMARY KEY(student_id, group_id)
);