USE martine;
-- Generated by Mocodo 4.3.2

CREATE TABLE activites (
  PRIMARY KEY (id_activite),
  id_activite VARCHAR(50) NOT NULL,
  nom_fr      VARCHAR(100)
);

CREATE TABLE candidats (
  PRIMARY KEY (id_candidat),
  id_candidat INT AUTO_INCREMENT NOT NULL,
  pseudo      VARCHAR(100),
  color       VARCHAR(9)
);

CREATE TABLE score (
  PRIMARY KEY (id_activite, id_candidat),
  id_activite VARCHAR(50) NOT NULL,
  id_candidat INT NOT NULL,
  heure       DATETIME,
  points      INT
);

ALTER TABLE score ADD FOREIGN KEY (id_candidat) REFERENCES candidats (id_candidat);
ALTER TABLE score ADD FOREIGN KEY (id_activite) REFERENCES activites (id_activite);

CREATE VIEW classement AS
  SELECT
    candidats.id_candidat, 
    candidats.pseudo, 
    candidats.color,
    SUM(points) as `total`,
    COUNT(points) as `ateliers`,
    RANK() OVER (ORDER BY `total` DESC) as `rang`
  FROM score
  RIGHT JOIN candidats on score.id_candidat = candidats.id_candidat
  GROUP BY id_candidat
  ORDER BY total DESC
;
