SET SQL_MODE = 'ANSI';

--
-- Tabelle.
--

CREATE TABLE "teams" (
  "teamId"                                 INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  "name"                                   VARCHAR(191) NOT NULL
) ENGINE 'innodb' CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';

CREATE TABLE "matches" (
  "matchId"                                INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  "day"                                    TINYINT UNSIGNED NOT NULL,
  "team1Id"                                INTEGER UNSIGNED NOT NULL,
  "goals1"                                 TINYINT UNSIGNED,
  "team2Id"                                INTEGER UNSIGNED NOT NULL,
  "goals2"                                 TINYINT UNSIGNED
) ENGINE 'innodb' CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
ALTER TABLE "matches" ADD CONSTRAINT "matches.team1Id.fk" FOREIGN KEY ("team1Id") REFERENCES "teams" ("teamId") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "matches" ADD CONSTRAINT "matches.team2Id.fk" FOREIGN KEY ("team2Id") REFERENCES "teams" ("teamId") ON DELETE CASCADE ON UPDATE CASCADE;

CREATE TABLE "standings" (
  "standingId"                             INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  "teamId"                                 INTEGER UNSIGNED NOT NULL,
  "wonMatches"                             TINYINT UNSIGNED NOT NULL,
  "drawnMatches"                           TINYINT UNSIGNED NOT NULL,
  "lostMatches"                            TINYINT UNSIGNED NOT NULL,
  "points"                                 TINYINT UNSIGNED NOT NULL
) ENGINE 'innodb' CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
ALTER TABLE "standings" ADD CONSTRAINT "standings.teamId.fk" FOREIGN KEY ("teamId") REFERENCES "teams" ("teamId") ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Funzioni e procedure.
--

DELIMITER $$$

--
-- Azzera i dati della stagione precedente.
--
CREATE PROCEDURE "MZ_RESET_SEASON"()
BEGIN
  -- Svuota le tabelle persistenti.
  TRUNCATE "standings";
  TRUNCATE "matches";
  -- Elimina le tabelle temporanee (che non dovrebbero comunque più esistere).
  DROP TABLE IF EXISTS "temporaryHalfSeasonPossibleMatches";
  DROP TABLE IF EXISTS "temporaryTeams";
END $$$

--
-- Conta il numero di squadre partecipanti.
--
CREATE FUNCTION "MZ_GET_TEAM_COUNT"() RETURNS INTEGER UNSIGNED NOT DETERMINISTIC
BEGIN
  -- Dichiara le variabili utilizzate.
  DECLARE teamCount INTEGER UNSIGNED;
  -- Conta il numero di squadre.
  SELECT COUNT("teams"."teamId") INTO teamCount
  FROM   "teams";
  --
  RETURN teamCount;
END $$$

--
-- Controlla il numero di squadre partecipanti.
--
CREATE PROCEDURE "MZ_CHECK_TEAM_COUNT"()
BEGIN
  -- Controlla che il numero di squadre non sia dispari (resto della divisione
  -- per 2 diverso da zero), nel qual caso interrompi l'esecuzione segnalando un
  -- errore.
  IF ("MZ_GET_TEAM_COUNT"() % 2) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Il numero di squadre partecipanti deve essere pari!';
  END IF;
END $$$

--
-- Prepara il calendario.
--
CREATE PROCEDURE "MZ_SETUP_MATCHES"()
BEGIN
  -- Dichiara le variabili utilizzate.
  DECLARE _day, days TINYINT UNSIGNED DEFAULT 0;
  DECLARE temporaryTeamCount INTEGER UNSIGNED DEFAULT 0;
  DECLARE lastTeam1 BOOLEAN DEFAULT FALSE;
  DECLARE _team1Id, _team2Id INTEGER UNSIGNED;
  -- Dichiara i cursori e gli handler utilizzati.
  DECLARE team1Cursor CURSOR FOR
    SELECT   "teams"."teamId"
    FROM     "teams"
    ORDER BY "teams"."teamId";
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET lastTeam1 = TRUE;
  --
  -- Fase 1: definisci tutte le possibili partite del solo girone di andata.
  --
  -- Crea la tabella temporanea che conterrà tutte le possibili partite del solo
  -- girone di andata.
  CREATE TEMPORARY TABLE "temporaryHalfSeasonPossibleMatches" (
    "matchId"                                INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    "team1Id"                                INTEGER UNSIGNED NOT NULL,
    "team2Id"                                INTEGER UNSIGNED NOT NULL
  ) ENGINE 'innodb' CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
  -- Per ogni squadra partecipante (squadra1)...
  OPEN team1Cursor;
  team1Loop: LOOP
    -- ...prova a recuperare la chiave primaria della squadra, uscendo dal loop
    -- se era l'ultimo record;
    FETCH team1Cursor INTO _team1Id;
    IF (lastTeam1) THEN
      LEAVE team1Loop;
    END IF;
    -- ...(blocco secondario);
    BEGIN
      -- Dichiara le variabili utilizzate.
      DECLARE lastTeam2 BOOLEAN DEFAULT FALSE;
--      DECLARE _team2Id INTEGER UNSIGNED;
      -- Dichiara i cursori e gli handler utilizzati.
      DECLARE team2Cursor CURSOR FOR
        SELECT   "teams"."teamId"
        FROM     "teams"
        WHERE    "teams"."teamId" > _team1Id
        ORDER BY "teams"."teamId";
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET lastTeam2 = TRUE;
      -- Per ogni altra squadra partecipante (squadra2, diversa da squadra1)...
      OPEN team2Cursor;
      team2Loop: LOOP
        -- ...prova a recuperare la chiave primaria della squadra, uscendo dal
        -- loop se era l'ultimo record;
        FETCH team2Cursor INTO _team2Id;
        IF (lastTeam2) THEN
          LEAVE team2Loop;
        END IF;
        -- ...inserisci il record relativo alla partita possibile.
        INSERT INTO "temporaryHalfSeasonPossibleMatches" ("team1Id", "team2Id") VALUES
          (_team1Id, _team2Id);
      END LOOP team2Loop;
      CLOSE team2Cursor;
    END;
  END LOOP team1Loop;
  CLOSE team1Cursor;
  --
  -- Fase 2: definisci il calendario giornata per giornata del girone di
  --         andata), estraendo le partite da giocare tra quelle possibili
  --         individuate nella fase precedente.
  --
  -- Calcola il numero di giornate totali.
  SET days = ("MZ_GET_TEAM_COUNT"() - 1) * 2;
  -- Per ogni giornata del girone di andata...
  WHILE (_day < (days / 2)) DO
    -- ...imposta il numero della giornata;
    SET _day = _day + 1;
    -- ...crea la tabella temporanea che conterrà tutte le squadre partecipanti
    -- che devono ancora giocare nella giornata corrente, e calcola il loro
    -- numero iniziale;
    CREATE TEMPORARY TABLE "temporaryTeams" SELECT * FROM "teams";
    SELECT   COUNT("temporaryTeams"."teamId") INTO temporaryTeamCount
    FROM     "temporaryTeams";
    -- ...fino a quando ci sono ancora squadre partecipanti che devono giocare
    -- nella giornata corrente...
    WHILE (temporaryTeamCount > 0) DO
      -- ...estrai una squadra tra quelle residue (squadra1);
      SELECT   "temporaryTeams"."teamId" INTO _team1Id
      FROM     "temporaryTeams"
      ORDER BY RAND()
      LIMIT    1;
      -- ...estrai una partita tra quelle possibili residue del girone di
      -- andata, dove squadra1 giochi in casa o fuori casa, in modo da
      -- determinare la squadra avversaria tra quelle che ancora devono giocare
      -- nella giornata corrente (squadra2, diversa da squadra1);
      SELECT   IF("temporaryHalfSeasonPossibleMatches"."team1Id" = _team1Id, "temporaryHalfSeasonPossibleMatches"."team2Id", "temporaryHalfSeasonPossibleMatches"."team1Id") AS "x" INTO _team2Id
      FROM     "temporaryHalfSeasonPossibleMatches"
      WHERE    ("temporaryHalfSeasonPossibleMatches"."team1Id" = _team1Id)
               OR ("temporaryHalfSeasonPossibleMatches"."team2Id" = _team1Id)
      HAVING   "x" IN ( SELECT "temporaryTeams"."teamId" FROM "temporaryTeams" )
      ORDER BY RAND()
      LIMIT    1;
      -- ...determina casualmente chi deve giocare in casa e chi fuori;
      IF (RAND() < 0.5) THEN
        INSERT INTO "matches" ("day", "team1Id", "team2Id") VALUES
          (_day, _team1Id, _team2Id);
      ELSE
        INSERT INTO "matches" ("day", "team1Id", "team2Id") VALUES
          (_day, _team2Id, _team1Id);
      END IF;
      -- ...rimuovi la partita in questione tra quelle possibili del girone di
      -- andata, indipendentemente dal fatto che le due squadre giochino in
      -- casa o fuori casa;
      DELETE
      FROM     "temporaryHalfSeasonPossibleMatches"
      WHERE    (("temporaryHalfSeasonPossibleMatches"."team1Id" = _team1Id) AND ("temporaryHalfSeasonPossibleMatches"."team2Id" = _team2Id))
               OR (("temporaryHalfSeasonPossibleMatches"."team1Id" = _team2Id) AND ("temporaryHalfSeasonPossibleMatches"."team2Id" = _team1Id));
      -- ...elimina le due squadre in questione da quelle che devono ancora
      -- giocare nella giornata corrente;
      DELETE
      FROM     "temporaryTeams"
      WHERE    ("temporaryTeams"."teamId" = _team1Id)
               OR ("temporaryTeams"."teamId" = _team2Id);
      -- ...ricalcola il numero delle squadre partecipanti che devono ancora
      -- giocare nella giornata corrente;
      SELECT   COUNT("temporaryTeams"."teamId") INTO temporaryTeamCount
      FROM     "temporaryTeams";
    END WHILE;
    -- ...elimina la tabella delle squadre che devono ancora giocare nella
    -- giornata corrente (che a questo punto è comunque vuota).
    DROP TABLE IF EXISTS "temporaryTeams";
  END WHILE;
  -- Elimina la tabella di tutte le possibili partite del girone di andata (che
  -- a questo punto è comunque vuota).
  DROP TABLE IF EXISTS "temporaryHalfSeasonPossibleMatches";
  -- Genera il girone di ritorno, copiando i record delle partite finora
  -- inserite (girone di andata), aggiornando il numero della giornata ed
  -- invertendo la squadra in casa e quella fuori casa.
  INSERT INTO "matches" ("day", "team1Id", "team2Id")
    SELECT "matches"."day" + (days / 2), "matches"."team2Id", "matches"."team1Id"
    FROM   "matches";
END $$$

--
-- Prepara la classifica.
--
CREATE PROCEDURE "MZ_SETUP_STANDINGS"()
BEGIN
  -- Dichiara le variabili utilizzate.
  DECLARE lastTeam BOOLEAN DEFAULT FALSE;
  DECLARE _teamId INTEGER UNSIGNED;
  -- Dichiara i cursori e gli handler utilizzati.
  DECLARE teamCursor CURSOR FOR
    SELECT "teams"."teamId"
    FROM   "teams";
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET lastTeam = TRUE;
  -- Per ogni squadra partecipante...
  OPEN teamCursor;
  teamLoop: LOOP
    -- ...prova a recuperare la chiave primaria della squadra, uscendo dal loop
    -- se era l'ultimo record;
    FETCH teamCursor INTO _teamId;
    IF (lastTeam) THEN
      LEAVE teamLoop;
    END IF;
    -- ...inserisci la squadra nella classifica, inizializzando con 0 vittorie,
    -- 0 pareggi, 0 sconfitte e 0 punti.
    INSERT INTO "standings" ("teamId", "wonMatches", "drawnMatches", "lostMatches", "points") VALUES
      (_teamId, 0, 0, 0, 0);
  END LOOP teamLoop;
  CLOSE teamCursor;
END $$$

--
-- Gioca tutte le partite in una giornata.
--
CREATE PROCEDURE "MZ_PLAY_DAY_MATCHES"(IN _day TINYINT UNSIGNED)
BEGIN
  -- Dichiara le variabili utilizzate.
  DECLARE lastMatch BOOLEAN DEFAULT FALSE;
  DECLARE _matchId, _team1Id, _team2Id INTEGER UNSIGNED;
  DECLARE _goals1, _goals2 TINYINT UNSIGNED;
  -- Dichiara i cursori e gli handler utilizzati.
  DECLARE matchCursor CURSOR FOR
    SELECT "matches"."matchId", "matches"."team1Id", "matches"."team2Id"
    FROM   "matches"
    WHERE  "matches"."day" = _day;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET lastMatch = TRUE;
  -- Per ogni partita...
  OPEN matchCursor;
  matchLoop: LOOP
    -- ...prova a recuperare la chiave primaria della partita e delle squadre in
    -- casa e fuori casa, uscendo dal loop se era l'ultimo record;
    FETCH matchCursor INTO _matchId, _team1Id, _team2Id;
    IF (lastMatch) THEN
      LEAVE matchLoop;
    END IF;
    -- ...decidi casualmente il numero di goal delle due squadre;
    SET _goals1 = ROUND(RAND() * 3);
    SET _goals2 = ROUND(RAND() * 3);
    UPDATE "matches"
    SET    "goals1" = _goals1,
           "goals2" = _goals2
    WHERE  "matchId" = _matchId;
    -- ...valuta il risultato finale ed aggiorna la classifica di conseguenza.
    IF (_goals1 > _goals2) THEN
      UPDATE "standings"
      SET    "wonMatches" = "wonMatches" + 1,
             "points" = "points" + 3
      WHERE  "standings"."teamId" = _team1Id;
      UPDATE "standings"
      SET    "lostMatches" = "lostMatches" + 1
      WHERE  "standings"."teamId" = _team2Id;
    ELSEIF (_goals1 = _goals2) THEN
      UPDATE "standings"
      SET    "drawnMatches" = "drawnMatches" + 1,
             "points" = "points" + 1
      WHERE  "standings"."teamId" = _team1Id;
      UPDATE "standings"
      SET    "drawnMatches" = "drawnMatches" + 1,
             "points" = "points" + 1
      WHERE  "standings"."teamId" = _team2Id;
    ELSE
      UPDATE "standings"
      SET    "lostMatches" = "lostMatches" + 1
      WHERE  "standings"."teamId" = _team1Id;
      UPDATE "standings"
      SET    "wonMatches" = "wonMatches" + 1,
             "points" = "points" + 3
      WHERE  "standings"."teamId" = _team2Id;
    END IF;
  END LOOP matchLoop;
  CLOSE matchCursor;
END $$$

--
-- Gioca tutte le partite nella stagione.
--
CREATE PROCEDURE "MZ_PLAY_MATCHES"()
BEGIN
  -- Dichiara le variabili utilizzate.
  DECLARE _day, days TINYINT UNSIGNED DEFAULT 0;
  -- Calcola il numero di giornate totali.
  SET days = ("MZ_GET_TEAM_COUNT"() - 1) * 2;
  -- Per ogni giornata...
  WHILE (_day < days) DO
    SET _day = _day + 1;
    -- ...gioca tutte le partite nella giornata.
    CALL "MZ_PLAY_DAY_MATCHES"(_day);
  END WHILE;
END $$$

--
-- Stampa tutte le partite giocate in una giornata.
--
CREATE PROCEDURE "MZ_PRINT_DAY_MATCHES"(IN _day TINYINT UNSIGNED)
BEGIN
  -- Estrai i dati necessari per stampare le partite giocate nella giornata.
  SELECT   "matches"."day", "teams1"."name" AS "team1", "teams2"."name" AS "team2", "matches"."goals1", "matches"."goals2"
  FROM     "matches"
  JOIN     "teams" AS "teams1" ON "matches"."team1Id" = "teams1"."teamId"
  JOIN     "teams" AS "teams2" ON "matches"."team2Id" = "teams2"."teamId"
  WHERE    "matches"."day" = _day
  ORDER BY "teams1"."name";
END $$$

--
-- Stampa tutte le partite giocate nella stagione.
--
CREATE PROCEDURE "MZ_PRINT_MATCHES"()
BEGIN
  -- Dichiara le variabili utilizzate.
  DECLARE _day, days TINYINT UNSIGNED DEFAULT 0;
  -- Calcola il numero di giornate totali.
  SET days = ("MZ_GET_TEAM_COUNT"() - 1) * 2;
  -- Per ogni giornata...
  WHILE (_day < days) DO
    SET _day = _day + 1;
    -- ...stampa tutte le partite giocate nella giornata.
    CALL "MZ_PRINT_DAY_MATCHES"(_day);
  END WHILE;
END $$$

--
-- Stampa la classifica.
--
CREATE PROCEDURE "MZ_PRINT_STANDINGS"()
BEGIN
  -- Estrai i dati necessari per stampare la classifica ordinata.
  SELECT   "teams"."name", "standings"."wonMatches" AS "W", "standings"."drawnMatches" AS "D", "standings"."lostMatches" AS "L", "standings"."points" AS "p"
  FROM     "standings"
  JOIN     "teams" ON "standings"."teamId" = "teams"."teamId"
  ORDER BY "standings"."points" DESC, "standings"."wonMatches" DESC, "standings"."drawnMatches" DESC;
END $$$

--
-- Gioca la stagione.
--
CREATE PROCEDURE "MZ_PLAY_SEASON"()
BEGIN
  -- Azzera i dati della stagione precedente.
  CALL "MZ_RESET_SEASON"();
  -- Controlla il numero di squadre partecipanti.
  CALL "MZ_CHECK_TEAM_COUNT"();
  -- Prepara il calendario.
  CALL "MZ_SETUP_MATCHES"();
  -- Prepara la classifica.
  CALL "MZ_SETUP_STANDINGS"();
  -- Gioca tutte le partite nella stagione.
  CALL "MZ_PLAY_MATCHES"();
  -- Stampa tutte le partite giocate nella stagione.
  CALL "MZ_PRINT_MATCHES"();
  -- Stampa la classifica.
  CALL "MZ_PRINT_STANDINGS"();
END $$$

DELIMITER ;

--
-- Dati.
--

INSERT INTO "teams" ("name") VALUES
  ('Inter'),
  ('Milan'),
  ('Napoli'),
  ('Roma');

CALL "MZ_PLAY_SEASON"();