sp_CreateTournament - V3

IF (SELECT COUNT(1) FROM vw_ActiveSeasonRefs A WHERE A.SeasonName = VarSeasonName) = 1
THEN
    IF (SELECT COUNT(1)
          FROM t_SeasonTourData S
          JOIN vw_ActiveSeasonRefs A
            ON S.SeasonReference = A.ActiveSeasonRef
         WHERE A.SeasonName = VarSeasonName
           AND S.Playoffs = 1) = 1
    THEN
      SELECT 'This season is complete. No new tournaments may be created until the next season is initalised.';
    ELSEIF (SELECT MAX(S.SeasonTourNo)
              FROM t_SeasonTourData S
              JOIN vw_ActiveSeasonRefs A ON S.SeasonReference = A.ActiveSeasonRef
             WHERE A.SeasonName = VarSeasonName) <= 2
    THEN
        IF (SELECT COUNT(1)
              FROM t_SeasonTourData S
              JOIN vw_ActiveSeasonRefs A ON S.SeasonReference = A.ActiveSeasonRef
              JOIN t_Tournaments T       ON T.SeasonTourIdFk = S.SeasonTourIdPk
             WHERE A.SeasonName = VarSeasonName
               AND T.ProgressIdFk = 2) = 0
        THEN
            IF (SELECT COUNT(1)
                  FROM t_Tiers
                 WHERE TierName = VarTierName) = 1
            THEN
           INSERT INTO t_SeasonTourData (SeasonIdFk, SeasonNo, SeasonTourNo, StartDate, Playoffs, SeasonReference)
                SELECT D.SeasonIdFk,
                       MAX(D.SeasonNo) AS SeasonNo,
                       MAX(D.SeasonTourNo) + 1 AS SeasonTourNo,
                       CUR_DATE() AS StartDate,
                       0 AS Playoffs,
                       D.SeasonReference
                  FROM t_Seasons S
                  JOIN t_SeasonTourData D ON S.SeasonIdPk = D.SeasonIdFk
                 WHERE S.SeasonName = VarSeasonName
              GROUP BY D.SeasonIdFk, D.SeasonReference;

           INSERT INTO t_Tournaments (TierIdFk, TourStructureIdFk, SeasonTourIdFk)
                SELECT IFNULL(SeasonTierId,TierIdPk) AS TierIdFk,
                       1 AS TourStructureIdFk,
                       MAX(SeasonTourIdPk) AS SeasonTourIdFk
                  FROM t_Seasons S
                  JOIN t_SeasonTourData D ON S.SeasonIdPk = D.SeasonIdFk
            CROSS JOIN t_Tiers T
                 WHERE S.SeasonName = VarSeasonName
                   AND T.TierName = VarTierName
              GROUP BY IFNULL(SeasonTierId,TierIdPk);
            ELSE
                SELECT 'Invalid Tier Name' AS ErrorMessage;
            END IF;
        END IF;

        CREATE TEMPORARY TABLE tt_SetIds (SetId INT);

        INSERT INTO tt_SetIds (SetId)
        WITH RECURSIVE cte_SetIds AS
           (SELECT 1 AS SetId
             UNION ALL
            SELECT SetId + 1
              FROM cte_SetIds
             WHERE SetId + 1 <= (SELECT POW(2,CEIL(LOG(2,(SELECT COUNT(1)
                                                            FROM t_Signups S
                                                            JOIN vw_ActiveSeasonRefs A
                                                              ON S.SeasonRefFk = A.ActiveSeasonRef
                                                           WHERE A.SeasonName = VarSeasonName)) - 0.5)) AS BracketSize))
        SELECT SetId
          FROM cte_SetIds;

        CREATE TEMPORARY TABLE tt_Players (SeedId INT PRIMARY KEY, PlayerId INT);

        INSERT INTO tt_Players (SeedID, PlayerId)
        SELECT S.SetId,
               IFNULL(PlayerId,1)
          FROM tt_SetIds S
    CROSS JOIN t_Seasons S2
          JOIN t_SeasonTourData D ON D.SeasonIdFk = S2.SeasonIdPk
          JOIN t_Tournaments T    ON T.SeasonTourIdFk = D.SeasonTourIdPk
     LEFT JOIN vw_PrioritisedSignups_WithSubrandomisation P
            ON S.SetID = P.SignupPriority
           AND P.SeasonRef = D.SeasonReference
           AND T.ProgressIdFk = 2
         WHERE S2.SeasonName = VarSeasonName;

   INSERT INTO t_Substitutes (TourIdFk, PlayerIdFk)
        SELECT TourIdPk AS TourIdFk,
               PlayerIdFk
          FROM vw_PrioritisedSignups P
     LEFT JOIN tt_SetIds I ON I.SetId = P.SignupPriority
          JOIN t_SeasonTourData D ON P.SeasonRef = D.SeasonReference
          JOIN t_Seasons S        ON D.SeasonIdFk = S.SeasonIdPk
          JOIN t_Tournaments T    ON T.SeasonTourIdFk = D.SeasonTourIdPk
         WHERE I.SetId IS NULL
           AND S.SeasonName = VarSeasonName;

   DELETE FROM tt_SetIds WHERE SetID = (SELECT MAX(SetId) FROM tt_SetIds);

   INSERT INTO t_Sets (TourSetId, TourIdFk, Player1IdFk, Player2IdFk)
        SELECT I.SetId AS TourSetId,
               TourIdPk AS TourIdFk,
               IFNULL(P1.PlayerId,1) AS Player1IdFk,
               IFNULL(P2.PlayerId,1) AS Player2IdFk
          FROM tt_SetIds I
     LEFT JOIN tt_SetIds I2
            ON I.SetID * 2 = I2.SetID + 1
    CROSS JOIN t_Tournaments T
          JOIN t_SeasonTourData S ON T.SeasonTourIdFk = S.SeasonTourIdPk
          JOIN t_Seasons S2       ON S2.SeasonIdPk = S.SeasonIdFk
     LEFT JOIN tt_Players P1      ON I2.SetID IS NOT NULL AND P1.SeedId = I.SeedId
     LEFT JOIN tt_Players P2      ON I2.SetID IS NOT NULL AND P2.SeedId + 2 = (SELECT MAX(SetId) FROM tt_SetIds) -  I.SeedId
         WHERE ProgressIdFk = 2
           AND S2.SeasonName = VarSeasonName;

-- generate bracket data into t_TourStages

    ELSE
        SELECT 'Playoffs are not yet supported';
    END IF;
ELSE
 SELECT 'Invalid Season Name';
END IF