/*==============================================================================
                     _
                    / )
                   / /
            .-""-.//'
           /_____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_numero,categorie_nom)
VALUES (1,'pre-novice'),
       (2,'novice'),
       (3,'atome A'),
       (4,'atome B'),
       (5,'moustique A'),
       (6,'moustique B'),
       (7,'pee wee A'),
       (8,'pee wee B'),
       (9,'bantam AA'),
       (10,'bantam A'),
       (11,'midget AA'),
       (12,'midget A');

/*position*/
CREATE TABLE marqueur_position(
  position_numero INT AUTO_INCREMENT,
  PRIMARY KEY(position_numero),
  position_nom VARCHAR(64) NOT NULL
);

INSERT INTO marqueur_position (position_numero,position_nom)
VALUES (1,'principal'),
       (2,'present');
       
/*position des arbitres*/
CREATE TABLE arbitre_position(
  position_numero INT AUTO_INCREMENT,
  PRIMARY KEY(position_numero),
  position_nom VARCHAR(64) NOT NULL
);

INSERT INTO arbitre_position (position_numero,position_nom)
VALUES (1,'marbre'),
       (2,'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_numero,joueur_position_nom)
VALUES (1,'lanceur'),
       (2,'receveur'),
       (3,'premier but'),
       (4,'deuxieme but'),
       (5,'troisieme but'),
       (6,'arret-court'),
       (7,'voltigeur de gauche'),
       (8,'voltigeur de centre'),
       (9,'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_numero,type_entraineur_nom)
VALUES (1,'principal'),
       (2,'de lanceurs'),
       (3,'de frappeurs'),
       (4,'au premier but'),
       (5,'au troisième but'),
       (6,'dans enclos de releve'),
       (7,'des reveveurs'),
       (8,'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_numero, partie_status_nom) 
VALUES (1,'confirme'),
       (2,'en attente'),
       (3,'annule'),
       (4,'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
);

/*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
);

/*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
);

/*equipe*/
CREATE TABLE equipe(
  equipe_numero INT AUTO_INCREMENT,
  PRIMARY KEY(equipe_numero),
  equipe_nom VARCHAR(128) NOT NULL,
  equipe_categorie INT NOT NULL,
  FOREIGN KEY(equipe_categorie) 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,
  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_heureprevue TIME NOT NULL,
  partie_date DATE NOT NULL,
  partie_categorie INT NOT NULL,
  FOREIGN KEY(partie_categorie) REFERENCES categorie(categorie_numero),
  partie_equipeA INT NOT NULL,
  FOREIGN KEY(partie_equipeA) REFERENCES equipe(equipe_numero),
  partie_equipeB INT NOT NULL,
  FOREIGN KEY(partie_equipeB) REFERENCES equipe(equipe_numero),
  partie_status INT NOT NULL,
  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_pointslocal INT,
  partie_pointsvisite INT,
  partie_heuredebut TIME,
  partie_heurefin TIME,
  partie_nombremanches 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),
  position_numero INT NOT NULL,
  FOREIGN KEY(position_numero) REFERENCES arbitre_position(position_numero),
  PRIMARY KEY(arbitre_numero, partie_numero, position_numero)
);


CREATE TABLE partie_marqueur(
  partie_numero INT NOT NULL,
  FOREIGN KEY (partie_numero) REFERENCES partie(partie_numero),
  partie_marqueur INT NOT NULL,
  FOREIGN KEY (partie_marqueur) REFERENCES marqueur(marqueur_numero),
  partie_position INT NOT NULL,
  FOREIGN KEY (partie_position) REFERENCES marqueur_position(position_numero),
  PRIMARY KEY(partie_numero, partie_marqueur, partie_position)
);

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),
  entraineur_type INT NOT NULL,
  FOREIGN KEY (entraineur_type) REFERENCES type_entraineur(type_entraineur_numero),
  PRIMARY KEY(entraineur_numero, equipe_numero, entraineur_type)
);

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

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

/*
vue status des matchs
#partie|heure|terrain|visiteur|local|statut|[catégorie]
*/
CREATE VIEW viewPartie AS
SELECT partie_numero AS '#Match', 
       partie_heureprevue 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_equipeB AND
     local.equipe_numero=partie_equipeA 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());

/*
vue entré des résultats de partie
|#Match|catégorie|Terrain|heure prévue|marqueur|visiteur|local|arbitre marbre|arbitre but|
*/
CREATE VIEW viewEntrerResultat AS
SELECT categorie.categorie_nom AS Categorie,
       terrain.terrain_nom AS Terrain,
       partie.partie_heureprevue AS HeurePrevue,
       partie.partie_date AS DateDuMatch,
       marqueur.marqueur_nom AS Nom_marqueur,
       marqueur.marqueur_prenom AS Prenom_marqueur,
       visiteur.equipe_nom AS Visiteur,
       local.equipe_nom AS Local,
       arbitre_but.arbitre_nom AS Nom_arbitre_au_but,
       arbitre_but.arbitre_prenom AS Prenom_arbitre_au_but,
       arbitre_marbre.arbitre_nom AS Nom_arbitre_au_marbre,
       arbitre_marbre.arbitre_prenom AS Prenom_arbitre_au_marbre
FROM partie,
     categorie,
     terrain,
     marqueur,
     partie_marqueur,
     equipe AS visiteur,
     equipe AS local,
     arbitre AS arbitre_but,
     arbitre_partie AS arbitre_partie_but,
     arbitre AS arbitre_marbre,
     arbitre_partie AS arbitre_partie_marbre
WHERE categorie.categorie_numero=partie.partie_categorie AND
      terrain.terrain_numero=partie.partie_terrain AND
      partie_marqueur.partie_numero=partie.partie_numero AND
      partie_marqueur.partie_marqueur=marqueur.marqueur_numero AND
      local.equipe_numero=partie.partie_equipeA AND
      visiteur.equipe_numero=partie.partie_equipeB AND
      arbitre_but.arbitre_numero=arbitre_partie_but.arbitre_numero AND
      partie.partie_numero=arbitre_partie_but.partie_numero AND
      arbitre_partie_but.position_numero=1 AND
      arbitre_marbre.arbitre_numero=arbitre_partie_marbre.arbitre_numero AND
      partie.partie_numero=arbitre_partie_marbre.partie_numero AND
      arbitre_partie_marbre.position_numero=1;
      
/*
procedure entre resultat de match
  partie_numero INT
  partie_commentaire VARCHAR(512),
  partie_pointslocal INT,
  partie_pointsvisite INT,
  partie_heuredebut TIME,
  partie_heurefin TIME,
  partie_nombremanches INT
*/
DELIMITER ~~
CREATE PROCEDURE procEntrerResultat(IN noPartie INT UNSIGNED,
                                    IN debut TIME, 
                                    IN fin TIME, 
                                    IN nbManche INT UNSIGNED,
                                    IN pointVisiteur INT UNSIGNED,
                                    IN pointLocal INT UNSIGNED,
                                    IN noMarqueur INT UNSIGNED,
                                    IN noArbitreBut INT UNSIGNED,
                                    IN noArbitreMarbre INT UNSIGNED,
                                    IN commentaire VARCHAR(512))
BEGIN
  UPDATE partie
  SET partie_heuredebut=debut,
      partie_heurefin=fin,
      partie_nombremanches=nbManche,
      partie_pointslocal=pointLocal,
      partie_pointsvisite=pointVisiteur,
      partie_commentaire=commentaire
  WHERE partie_numero=noPartie;
      
END~~
DELIMITER ;
/*==============================================================================
==============================================================================*/



/*==============================================================================
                                                        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 entraineur (entraineur_nom, 
                        entraineur_prenom, 
                        entraineur_telephone, 
                        entraineur_adresse, 
                        entraineur_courriel)
VALUES ('Pedof','Phil', '(819) 999 9999', '2020 du fin fin', 'pedofphil@ticul.com'),
       ('Mongeau','Monsieur', '(999) 999 9999', '1213 randam', 'mm@telequebec.com');

INSERT INTO arbitre (arbitre_nom, 
                     arbitre_prenom, 
                     arbitre_telephone, 
                     arbitre_adresse, 
                     arbitre_courriel)
VALUES ('Valence','Martin','(123) 456 7890','4321 quelquepart','adresse@comercial.com'),
       ('Groleau','Thibault','(789) 456 0123','1234 nullpart','courriel@domaine.com');

INSERT INTO marqueur (marqueur_nom, 
                      marqueur_prenom, 
                      marqueur_telephone, 
                      marqueur_adresse, 
                      marqueur_courriel)
VALUES ('Lenchenteur','Merlin','(111) 111 1111','camelot','s@s.com'),
       ('Labite','Emerlanus','(000) 000 0000','derriere le cabanon','h@h.com');

INSERT INTO ville (ville_nom) 
VALUES ('Sherbrooke'),
       ('Cookshire'),
       ('Cowansville'),
       ('Weedon');

INSERT INTO terrain (terrain_nom, 
                     terrain_adresse, 
                     ville_numero)
VALUES ('Le Bouetteux', 'la rue labas', 
       (SELECT ville_numero FROM ville WHERE ville_nom='Sherbrooke')),
       ('Le Gazon', 'la rue ici', 
       (SELECT ville_numero FROM ville WHERE ville_nom='Cookshire')),
       ('Le beton', 'pas loin', 
       (SELECT ville_numero FROM ville WHERE ville_nom='Weedon'));

INSERT INTO equipe (equipe_nom, equipe_categorie)
VALUES ('Defonceur', 
       (SELECT categorie_numero FROM categorie WHERE categorie_nom='pee wee B')),
       ('CasseGroleau',
       (SELECT categorie_numero FROM categorie WHERE categorie_nom='atome A'));
       
INSERT INTO partie (partie_heureprevue,
                   partie_date,
                   partie_categorie,
                   partie_equipeA,
                   partie_equipeB,
                   partie_terrain)
VALUES ('12:00:00',
        date(now()),
        (SELECT categorie_numero FROM categorie WHERE categorie_nom='atome A'),
        (SELECT equipe_numero FROM equipe WHERE equipe_nom='Defonceur'),
        (SELECT equipe_numero FROM equipe WHERE equipe_nom='CasseGroleau'),
        (SELECT terrain_numero FROM terrain WHERE terrain_nom='Le Gazon'));

INSERT INTO partie_marqueur (partie_numero, partie_marqueur, partie_position)
VALUES (1,(SELECT marqueur_numero FROM marqueur WHERE marqueur_nom='Labite'),1);

INSERT INTO arbitre_partie (arbitre_numero, partie_numero, position_numero)
VALUES (1,1,1);
INSERT INTO arbitre_partie (arbitre_numero, partie_numero, position_numero)
VALUES (2,1,2);

