sp_CreateTournament - SingleElimComplete

IF (SELECT COUNT(1) FROM vw_ActiveSeasonRefs A WHERE A.SeasonName = VarSeasonName) = 0
THEN
    SELECT 'Invalid Season Name';
ELSE
    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))))
        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(P.PlayerId,1)
          FROM tt_SetIds S
    CROSS JOIN vw_SeasonTourData D
          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 D.SeasonName = VarSeasonName;

   INSERT INTO t_Substitutes (TourIdFk, PlayerIdFk)
        SELECT T.TourIdPk AS TourIdFk,
               P.PlayerId
          FROM vw_PrioritisedSignups P
     LEFT JOIN tt_SetIds I         ON I.SetId = P.SignupPriority
          JOIN vw_SeasonTourData D ON P.SeasonRef = D.SeasonReference
          JOIN t_Tournaments T     ON T.SeasonTourIdFk = D.SeasonTourIdPk
         WHERE I.SetId IS NULL
           AND D.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,
               T.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 vw_SeasonTourData D ON T.SeasonTourIdFk = D.SeasonTourIdPk
     LEFT JOIN tt_Players P1       ON I2.SetID IS NOT NULL AND P1.SeedId = I.SetId
     LEFT JOIN tt_Players P2       ON I2.SetID IS NOT NULL AND P2.SeedId - 2 = (SELECT MAX(SetId) FROM tt_SetIds) -  I.SetId
         WHERE T.ProgressIdFk = 2
           AND D.SeasonName = VarSeasonName;

    INSERT INTO t_TourStages (TourIdFk, SetIdFk, Round, BracketIdFk, TourStructureIdFk)
    SELECT TourIdFk,
           SetIdPk,
           FLOOR(LOG(2,(SELECT MAX(SetId) FROM tt_SetIds) + 1)) - FLOOR(LOG(2,(SELECT MAX(SetId) FROM tt_SetIds) + 1 - TourSetId)) AS Round,
           1 AS BracketIdFk,
           1 AS TourStructureIdFk
      FROM t_Sets;
    ELSE
        SELECT 'Playoffs are not yet supported';
    END IF;

END IF