/*==============================================================================
                     _
                    / )
                   / /
            .-""-.//'
           /_____C\___
           /// 6 6~\~~`
           (    7  )
            \  '='/
          _//'---'\                     _
         ( \       `\                  (_)   ==  =   -
         (\/`-.__/  /
          "`-._  _.'  
               ``
  mysql databaseball
==============================================================================*/

DROP DATABASE IF EXISTS baseball;
CREATE DATABASE baseball;
USE baseball;

/*==============================================================================
        /
       /                                           
     <<O                                o   .   .  _O
       \                                            /\ 
       /\                                            /\__
      / /                                            |
   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
type table
prépeuplé avec des données qui ne change pas souvent
==============================================================================*/


/*category*/
CREATE TABLE categorie(
  categorie_numero INT AUTO_INCREMENT,
  PRIMARY KEY(categorie_numero),
  categorie_nom VARCHAR(64) NOT NULL
);
/*
INSERT INTO categorie (categorie_nom)
VALUES ('pre-novice'),
       ('novice'),
       ('atome A'),
       ('atome B'),
       ('moustique A'),
       ('moustique B'),
       ('pee wee A'),
       ('pee wee B'),
       ('bantam AA'),
       ('bantam A'),
       ('midget AA'),
       ('midget A');
*/
/*position*/
CREATE TABLE arbitre_position(
  position_numero INT AUTO_INCREMENT,
  PRIMARY KEY(position_numero),
  position_nom VARCHAR(64) NOT NULL
);

INSERT INTO arbitre_position (position_nom)
VALUES ('principal_marbre'),
       ('principal_but'),
       ('present_marbre'),
       ('present_but');

/*position des joueurs*/
CREATE TABLE joueur_position(
  joueur_position_numero INT AUTO_INCREMENT,
  PRIMARY KEY(joueur_position_numero),
  joueur_position_nom VARCHAR(64) NOT NULL
);

INSERT INTO joueur_position (joueur_position_nom)
VALUES ('lanceur'),
       ('receveur'),
       ('premier but'),
       ('deuxieme but'),
       ('troisieme but'),
       ('arret-court'),
       ('voltigeur de gauche'),
       ('voltigeur de centre'),
       ('voltigeur de droite');

/**/
CREATE TABLE marqueur_position
(
   marqueur_position_numero INT AUTO_INCREMENT,
   marqueur_position_nom VARCHAR(64) NOT NULL,
   primary key (marqueur_position_numero)
);
INSERT INTO marqueur_position (marqueur_position_nom)
VALUES ('lanceur'),
       ('receveur'),
       ('premier but'),
       ('deuxieme but'),
       ('troisieme but'),
       ('arret-court'),
       ('voltigeur de gauche'),
       ('voltigeur de centre'),
       ('voltigeur de droite');

/*entraineur type*/
CREATE TABLE type_entraineur(
  type_entraineur_numero INT AUTO_INCREMENT,
  PRIMARY KEY(type_entraineur_numero),
  type_entraineur_nom VARCHAR(64) NOT NULL
);

INSERT INTO type_entraineur (type_entraineur_nom)
VALUES ('principal'),
       ('de lanceurs'),
       ('de frappeurs'),
       ('au premier but'),
       ('au troisième but'),
       ('dans enclos de releve'),
       ('des reveveurs'),
       ('sur le banc');

/*status partie*/
CREATE TABLE partie_status(
  partie_status_numero INT AUTO_INCREMENT,
  PRIMARY KEY(partie_status_numero),
  partie_status_nom VARCHAR(16)
);

/*
INSERT INTO partie_status (partie_status_nom) 
VALUES ('confirme'),
       ('en attente'),
       ('annule'),
       ('non disponible');
*/
/*==============================================================================

                         .   '   '  o    
           /         .               
          /       .  
       O>>     .                                      O
       \                                             /|\
       |\                                            /|
      /  \                                           || 
jgs^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
data table
les tables qui contients les données importante
==============================================================================*/

/*ville*/
CREATE TABLE ville(
  ville_numero INT AUTO_INCREMENT,
  PRIMARY KEY(ville_numero),
  ville_nom VARCHAR(256) NOT NULL
);

/**/

/*arbitre*/
CREATE TABLE arbitre(
  arbitre_numero INT AUTO_INCREMENT,
  PRIMARY KEY(arbitre_numero),

  arbitre_nom VARCHAR(64) NOT NULL,
  arbitre_prenom VARCHAR(64) NOT NULL,
  arbitre_telephone VARCHAR(64) NOT NULL,
  arbitre_adresse VARCHAR(128) NOT NULL,
  arbitre_courriel VARCHAR(128) NOT NULL,
  arbitre_commentaire  varchar(512)
);

/*marqueur*/
CREATE TABLE marqueur(
  marqueur_numero INT AUTO_INCREMENT,
  PRIMARY KEY(marqueur_numero),

  marqueur_nom VARCHAR(64) NOT NULL,
  marqueur_prenom VARCHAR(64) NOT NULL,
  marqueur_telephone VARCHAR(16) NOT NULL,
  marqueur_adresse VARCHAR(128) NOT NULL,
  marqueur_courriel VARCHAR(128) NOT NULL,
  marqueur_commentaire VARCHAR(512)
);

/*entraineur*/
CREATE TABLE entraineur(
  entraineur_numero INT AUTO_INCREMENT,
  PRIMARY KEY(entraineur_numero),

  entraineur_nom VARCHAR(64) NOT NULL,
  entraineur_prenom VARCHAR(64) NOT NULL,
  entraineur_telephone VARCHAR(16) NOT NULL,
  entraineur_adresse VARCHAR(128) NOT NULL,
  entraineur_courriel VARCHAR(128) NOT NULL,
  entraineur_commentaire VARCHAR(512)
);

/*equipe*/
CREATE TABLE equipe(
  equipe_numero INT AUTO_INCREMENT,
  PRIMARY KEY(equipe_numero),
  equipe_nom VARCHAR(128) NOT NULL,
  categorie_numero INT NOT NULL,
  FOREIGN KEY(categorie_numero) REFERENCES categorie(categorie_numero)
);

/*terrain*/
CREATE TABLE terrain(
  terrain_numero INT AUTO_INCREMENT,
  PRIMARY KEY(terrain_numero),

  terrain_nom VARCHAR(256) NOT NULL,
  terrain_adresse VARCHAR(256) NOT NULL,
  terrain_commentaires VARCHAR(512),
  ville_numero INT NOT NULL,
  FOREIGN KEY(ville_numero) REFERENCES ville(ville_numero)
);

/*partie*/
CREATE TABLE partie(
  partie_numero INT AUTO_INCREMENT,
  PRIMARY KEY(partie_numero),

  partie_heure_debut_prevue TIME NOT NULL,
  partie_date DATE NOT NULL,
  partie_categorie INT NOT NULL,
  FOREIGN KEY(partie_categorie) REFERENCES categorie(categorie_numero),
  equipe_numero_local INT NOT NULL,
  FOREIGN KEY(equipe_numero_local) REFERENCES equipe(equipe_numero),
  equipe_numero_visite INT NOT NULL,
  FOREIGN KEY(equipe_numero_visite) REFERENCES equipe(equipe_numero),
  partie_status INT,
  FOREIGN KEY(partie_status) REFERENCES partie_status(partie_status_numero),
  partie_terrain INT NOT NULL,
  FOREIGN KEY(partie_terrain) REFERENCES terrain(terrain_numero),

  partie_commentaire VARCHAR(512),
  partie_points_local INT,
  partie_points_visite INT,
  partie_heure_debut TIME,
  partie_heure_de_fin TIME,
  partie_nombre_manches INT
);

/*le status est par défaut 'en attente'
*/
DELIMITER ~~
CREATE TRIGGER newPartie BEFORE INSERT ON partie
  FOR EACH ROW BEGIN
    SET NEW.partie_status = (SELECT partie_status_numero 
                            FROM partie_status 
                            WHERE partie_status_nom='en attente');
  END ~~
DELIMITER ;

/*si en modifiant l'heur de fin , 
l'on ne spécifi pas l'heur de début,
l'heur de début s'y insert par défaut,
egalement si le match est toujour en attente, il se met en confirmé*/
/*
DELIMITER ~~
CREATE TRIGGER apresPartie BEFORE UPDATE ON partie
  FOR EACH ROW BEGIN

    IF (NEW.partie_heurefin IS NOT NULL) THEN
      SET NEW.partie_heuredebut = IF (NEW.partie_heuredebut IS NULL, 
                                      NEW.partie_heureprevue, 
                                      NEW.partie_heuredebut);
      SET NEW.partie_status = IF (NEW.partie_status=(SELECT partie_status.partie_status_numero FROM partie_status WHERE partie_status.partie_status_nom='en attente'),
                                  (SELECT partie_status.partie_status_numero FROM partie_status WHERE partie_status.partie_status_nom='confirme'),
                                  NEW.partie_status);
    END IF

  END ~~
DELIMITER ;
*/

/*==============================================================================
                               ___
                          .-"``   `'.
                        .'           \
                       /              \
                      |            _.-"-._
                      |     __..-"`       `.
                      '---'``-._           /
                                ``--....-'`
relation
les tables de relations
==============================================================================*/

CREATE TABLE arbitre_partie(
  arbitre_numero INT NOT NULL,
  FOREIGN KEY(arbitre_numero) REFERENCES arbitre(arbitre_numero),
  partie_numero INT NOT NULL,
  FOREIGN KEY(partie_numero) REFERENCES partie(partie_numero),
  arbitre_position_numero INT NOT NULL,
  FOREIGN KEY(arbitre_position_numero) REFERENCES arbitre_position(position_numero),
  PRIMARY KEY(arbitre_numero, partie_numero, arbitre_position_numero)
);

CREATE TABLE marqueur_partie(
  partie_numero INT NOT NULL,
  FOREIGN KEY (partie_numero) REFERENCES partie(partie_numero),
  marqueur_numero INT NOT NULL,
  FOREIGN KEY (marqueur_numero) REFERENCES marqueur(marqueur_numero),
  marqueur_position_numero INT,
  FOREIGN KEY (marqueur_position_numero) REFERENCES marqueur_position(marqueur_position_numero),
  PRIMARY KEY(partie_numero, marqueur_position_numero, marqueur_numero)
);

CREATE TABLE terrain_categorie(
  terrain_numero INT NOT NULL,
  FOREIGN KEY(terrain_numero) REFERENCES terrain(terrain_numero),
  categorie_numero INT NOT NULL,
  FOREIGN KEY(categorie_numero) REFERENCES categorie(categorie_numero),
  PRIMARY KEY (terrain_numero, categorie_numero)
);

CREATE TABLE entraineur_equipe(
  entraineur_numero INT NOT NULL,
  FOREIGN KEY (entraineur_numero) REFERENCES entraineur(entraineur_numero),
  equipe_numero INT NOT NULL,
  FOREIGN KEY (equipe_numero) REFERENCES equipe(equipe_numero),
  type_entraineur_numero INT NOT NULL,
  FOREIGN KEY (type_entraineur_numero) REFERENCES type_entraineur(type_entraineur_numero),
  PRIMARY KEY(entraineur_numero, equipe_numero, type_entraineur_numero)
);

/*==============================================================================

==============================================================================*/
/*==============================================================================
          _        ____                                       __
         / \     .'    '.        ____......------""""""""`````  `\
        |   `::-/'-....-'\--""```                                 |
        |    :: |        |                                        |
        |   ,:'-\.-''''-./--..___                                /
         \_/     '.____.'        ````````````"""""""------------'
==============================================================================*/

/*
view
#partie|heure|terrain|visiteur|local|statut|[catégorie]
*/

CREATE VIEW viewPartie AS
SELECT partie_numero AS '#Match', 
       partie_heure_debut_prevue AS Heure,
       terrain_nom AS Terrain,
       visiteur.equipe_nom AS Visiteur,
       local.equipe_nom AS Local,
       partie_status.partie_status_nom AS status,
       categorie.categorie_nom AS categorie,
       partie_date AS partie_date
FROM partie, 
     terrain,
     equipe AS visiteur,
     equipe AS local,
     partie_status,
     categorie
WHERE terrain_numero=partie_terrain AND
     visiteur.equipe_numero=partie.equipe_numero_visite AND
     local.equipe_numero=partie.equipe_numero_local AND
     partie_status_numero=partie.partie_status AND
     categorie.categorie_numero=partie.partie_categorie;

CREATE VIEW viewPartieToday AS SELECT * FROM viewPartie WHERE partie_date=date(now());
/*==============================================================================
==============================================================================*/



/*==============================================================================
                                                        dMb
      _--_     dMb                     _--_              MMM         _--_
   __(._  )   d0P                     ( A's)              MMM       ( A's)
     <  (D)  .MP                      /___7                MMM      /___7
   .~ \ /~```M-.                    .~~\ /~~.               "MM   .~~\ /~~.
 .~    V    Mo_ \                 /""_  V    \                MM/""_  V    \
 (   (___. {:)-./                om /____/   /                 om /____/   /
  ~._____.(:}                 .mmmC="_      _/                 '{:)      _/
   /     .M\              .MMM ""  |  `"---"                      `"---" |
  /      "" \        ..mMMM""       |        \                   |        \
  |    /\   |        (  )"          \    /\   |                  \    /\   |
  /   /  \   \                      /   /  \   \                 /   /  \   \
 /   /    \   \                   /"   /    \   \              /"   /    \   \
 \__/      \__/                   \__/"      \__/              \__/"      \__/
 / /        | |                   / /        | |               / /        | |
.^V^.      .^V^.                 .^V^.      .^V^.             .^V^.      .^V^.
 +-+        +-+                   +-+        +-+               +-+        +-+
==============================================================================*/
INSERT INTO ville (ville_nom) VALUES ('Sherbrooke');

