-- Base de données de l'intranet FSA v1.0

DROP TABLE IF EXISTS sms;
DROP TABLE IF EXISTS sms_recipients;
DROP TABLE IF EXISTS registrations_competitions;
DROP TABLE IF EXISTS departures_comp;
DROP TABLE IF EXISTS weapons;
DROP TABLE IF EXISTS bookings;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS locations;

CREATE TABLE users(
   id_user SMALLINT AUTO_INCREMENT,
   licence VARCHAR(50) NOT NULL,
   name VARCHAR(50) NOT NULL,
   first_name VARCHAR(50) NOT NULL,
   laterality VARCHAR(50) DEFAULT 'D',
   birthday DATE,
   category VARCHAR(50),
   email VARCHAR(50) DEFAULT 'pasdemail@gm.com',
   password VARCHAR(255) NOT NULL,
   section BOOLEAN DEFAULT 0,
   admin BOOLEAN DEFAULT  0,
   actif BOOLEAN NOT NULL DEFAULT  1,
   PRIMARY KEY(id_user),
   UNIQUE(licence)
);

CREATE TABLE departures_comp(
   id_dep INT AUTO_INCREMENT,
   club VARCHAR(50),
   date_dep DATE,
   am_pm VARCHAR(50) CHECK(am_pm IN ('Matin','Après-midi')),
   infos VARCHAR(250),
   active TINYINT DEFAULT 1,
   PRIMARY KEY(id_dep)
);

CREATE TABLE sms_recipients(
   id_rec INT AUTO_INCREMENT,
   name VARCHAR(50),
   first_name VARCHAR(50),
   PRIMARY KEY(id_rec)
);

CREATE TABLE locations(
   id_location TINYINT AUTO_INCREMENT,
   name VARCHAR(255) NOT NULL,
   PRIMARY KEY(id_location)
);

CREATE TABLE weapons(
   id_weapon TINYINT AUTO_INCREMENT,
   name VARCHAR(50),
   PRIMARY KEY(id_weapon)
);

CREATE TABLE sms(
   id_sms INT AUTO_INCREMENT,
   message TEXT,
   date_sms DATE,
   id_rec INT NOT NULL,
   PRIMARY KEY(id_sms),
   FOREIGN KEY(id_rec) REFERENCES sms_recipients(id_rec)
);

CREATE TABLE registrations_competitions(
   id_user SMALLINT,
   id_dep INT,
   id_weapon TINYINT,
   PRIMARY KEY(id_user, id_dep, id_weapon),
   FOREIGN KEY(id_user) REFERENCES users(id_user),
   FOREIGN KEY(id_dep) REFERENCES departures_comp(id_dep),
   FOREIGN KEY(id_weapon) REFERENCES weapons(id_weapon)
);

CREATE TABLE bookings(
   id_user SMALLINT,
   id_location TINYINT,
   date_cal DATE,
   PRIMARY KEY(id_user, id_location, date_cal),
   FOREIGN KEY(id_user) REFERENCES users(id_user),
   FOREIGN KEY(id_location) REFERENCES locations(id_location)
);

