Table_creation.sql

-- CREATE SCHEMA desa0045;

CREATE TABLE desa0045.League (
    LeagueID        INTEGER     NOT NULL GENERATED ALWAYS AS IDENTITY,
    LeagueName      VARCHAR(32) NOT NULL,
    Country         VARCHAR(32) NOT NULL,
    Division        SMALLINT    NOT NULL,
    PRIMARY KEY (LeagueID),
    CONSTRAINT LeagueNameLength CHECK (CHAR_LENGTH(LeagueName) > 4),
    CONSTRAINT AllDivisions CHECK (Division IN (1, 2, 3, 4))
);

CREATE TABLE desa0045.Team (
    TeamID          INTEGER     NOT NULL GENERATED ALWAYS AS IDENTITY,
    League          INTEGER     NOT NULL,
    TeamName        VARCHAR(32) NOT NULL,
    FoundedYear     SMALLINT    NOT NULL,
    City            VARCHAR(32) NOT NULL,
    Country         VARCHAR(32) NOT NULL,
    PRIMARY KEY (TeamID),
    CONSTRAINT ToLeague FOREIGN KEY (League) REFERENCES desa0045.League,
    CONSTRAINT FoundedYearInPast CHECK (FoundedYear BETWEEN 1700 AND EXTRACT(YEAR FROM CURRENT_DATE))
    -- CONSTRAINT TeamNameNotEmpty CHECK (TeamName IS DISTINCT FROM '')
);


CREATE TABLE desa0045.TeamMember (
    MemberID        INTEGER     NOT NULL GENERATED ALWAYS AS IDENTITY,
    FirstName       VARCHAR(32) NOT NULL,
    LastName        VARCHAR(32) NOT NULL,
    Team            INTEGER,
    PRIMARY KEY (MemberID),
    CONSTRAINT ToTeam FOREIGN KEY (Team) REFERENCES desa0045.Team ON DELETE SET NULL,
    CONSTRAINT FirstNameFirstUpper CHECK (FirstName ~ '^[A-Z][a-z]*$'),
    CONSTRAINT LastNameFirstUpper CHECK (LastName ~ '^[A-Z][a-z]*$')
);

CREATE TABLE desa0045.Staff (
    MemberID        INTEGER     NOT NULL,
    StaffRole       VARCHAR(32) NOT NULL,      
    PRIMARY KEY (MemberID),
    CONSTRAINT ToMember FOREIGN KEY (MemberID) REFERENCES desa0045.TeamMember
);


CREATE TABLE desa0045.Player (
    MemberID        INTEGER     NOT NULL,
    Position        VARCHAR(32) NOT NULL,
    Nationality     VARCHAR(32) NOT NULL,
    BirthDate       DATE        NOT NULL,
    PRIMARY KEY (MemberID),
    CONSTRAINT ToMember FOREIGN KEY (MemberID) REFERENCES desa0045.TeamMember,
    CONSTRAINT CheckPlayerAge CHECK (BirthDate <= CURRENT_DATE - INTERVAL '6 years' AND BirthDate >= CURRENT_DATE - INTERVAL '100 years'),
    CONSTRAINT AllPositions CHECK (Position IN ('Goalkeeper', 'Defender', 'Midfielder', 'Forward'))
);

CREATE TABLE desa0045.Stadium (
    StadiumID       INTEGER     NOT NULL GENERATED ALWAYS AS IDENTITY,
    StadiumName     VARCHAR(32) NOT NULL,
    Capacity        INTEGER     NOT NULL,
    Country         VARCHAR(32) NOT NULL,
    City            VARCHAR(32) NOT NULL,
    StadiumAddress  VARCHAR(32) NOT NULL,
    Price           INTEGER     NOT NULL,
    PRIMARY KEY (StadiumID),
    CONSTRAINT CapacityPositive CHECK (Capacity > 0),
    CONSTRAINT PriceNotNegative CHECK (Price >= 0)
);

CREATE TABLE desa0045.Match (
    MatchID         INTEGER         NOT NULL GENERATED ALWAYS AS IDENTITY,
    Team1           INTEGER         NOT NULL, --DEFAULT 0,
    Team2           INTEGER         NOT NULL, --DEFAULT 0,
    Result          VARCHAR(5)      NOT NULL,
    StartTime       TIMESTAMP(0)    NOT NULL,
    EndTime         TIMESTAMP(0)    ,--DEFAULT StartTime + INTERVAL '2 hours',--NOT NULL,
    Stadium         INTEGER         NOT NULL,
    Spectators      INTEGER,
    TicketPrice     SMALLINT,
    PRIMARY KEY (MatchID),
    CONSTRAINT ToTeam1 FOREIGN KEY (Team1) REFERENCES desa0045.Team,
    CONSTRAINT ToTeam2 FOREIGN KEY (Team2) REFERENCES desa0045.Team,
    CONSTRAINT ToStadium FOREIGN KEY (Stadium) REFERENCES desa0045.Stadium,
    CONSTRAINT StartNotGreaterThanEndTime CHECK (EndTime > StartTime),
    CONSTRAINT EndTimeInPast CHECK (EndTime < CURRENT_TIMESTAMP),
    CONSTRAINT ResultFormat CHECK (Result LIKE '%-%'),
    CONSTRAINT SpectatorCount CHECK (Spectators > 0),
    CONSTRAINT TicketPricePositive CHECK (TicketPrice > 0)
);

CREATE TABLE desa0045.Performance (
    MatchID         INTEGER     NOT NULL,
    MemberID        INTEGER     NOT NULL,
    Goals           SMALLINT    NOT NULL DEFAULT 0,
    Assists         SMALLINT    NOT NULL DEFAULT 0,
    YellowCards     SMALLINT    NOT NULL DEFAULT 0,
    RedCards        SMALLINT    NOT NULL DEFAULT 0,
    TimePlayed      SMALLINT    NOT NULL DEFAULT 0,
    PRIMARY KEY (MatchID, MemberID),
    CONSTRAINT ToMatch FOREIGN KEY (MatchID) REFERENCES desa0045.Match,
    CONSTRAINT ToMember FOREIGN KEY (MemberID) REFERENCES desa0045.Player
);

CREATE TABLE desa0045.Referee (
    RefereeID       INTEGER     NOT NULL GENERATED ALWAYS AS IDENTITY,
    FirstName       VARCHAR(32) NOT NULL,
    LastName        VARCHAR(32) NOT NULL,
    Qualification   SMALLINT    NOT NULL,
    Nationality     VARCHAR(32) NOT NULL,
    PRIMARY KEY (RefereeID),
    CONSTRAINT FirstNameFirstUpper CHECK (FirstName ~ '^[A-Z][a-z]*$'),
    CONSTRAINT LastNameFirstUpper CHECK (LastName ~ '^[A-Z][a-z]*$'), 
    CONSTRAINT AllQualifications CHECK (Qualification IN (1, 2, 3, 4))

);

CREATE TABLE desa0045.Officiate (
    RefereeID       INTEGER     NOT NULL,
    MatchID         INTEGER     NOT NULL,
    RoleInMatch     VARCHAR(32) NOT NULL,
    RefPrice        INTEGER     NOT NULL,
    PRIMARY KEY (RefereeID, MatchID),
    CONSTRAINT ToReferee FOREIGN KEY (RefereeID) REFERENCES desa0045.Referee,
    CONSTRAINT toMatch FOREIGN KEY (MatchID) REFERENCES desa0045.Match,
    CONSTRAINT RefPricePositive CHECK (RefPrice > 0),
    CONSTRAINT RoleInMatchValidation CHECK (RoleInMatch IN ('Main', 'Linesman', 'Fourth', 'VAR', 'Additional'))
);



CREATE UNIQUE INDEX UniqueStadiumName ON desa0045.Stadium(LOWER(StadiumName));
CREATE UNIQUE INDEX UniqueLeagueNamePerCountry ON desa0045.League(LOWER(LeagueName), LOWER(Country));
-- CREATE UNIQUE INDEX UniqueAbsoluteStadiumAddress ON desa0045.Stadium(LOWER(Country), LOWER(City), LOWER(StadiumAddress));


CREATE INDEX IndexTeamMemberLastName ON desa0045.TeamMember(LastName);
CREATE INDEX IndexRefereeLastName ON desa0045.Referee(LastName);
-- CREATE INDEX IndexLeagueName ON desa0045.League(LeagueName);






CREATE VIEW PlayerPerformanceInMatch AS
SELECT TM.MemberID, P.Position, T.TeamName, M.MatchID, M.Result, Perf.Goals, Perf.Assists, Perf.YellowCards, Perf.RedCards, Perf.TimePlayed
FROM desa0045.TeamMember TM
    JOIN desa0045.Player P ON P.MemberID = TM.MemberID
    LEFT OUTER JOIN desa0045.Performance Perf ON Perf.MemberID = TM.MemberID
    LEFT OUTER JOIN desa0045.Match M ON Perf.MatchID = M.MatchID
    LEFT OUTER JOIN desa0045.Team T ON TM.Team = T.TeamID;


CREATE VIEW PlayersInTeam AS
SELECT TM.MemberID, T.TeamName, TM.FirstName, TM.LastName, P.Position, P.Nationality
FROM desa0045.Player P
    JOIN desa0045.TeamMember TM ON P.MemberID = TM.MemberID
    RIGHT OUTER JOIN desa0045.Team T ON TM.Team = T.TeamID;


CREATE VIEW TeamGoals AS
SELECT T.TeamID, T.TeamName, T.League, COALESCE(SUM(Perf.Goals), 0) AS "Total goals"
FROM desa0045.Team T
    LEFT OUTER JOIN desa0045.TeamMember TM ON T.TeamID = TM.Team
    LEFT OUTER JOIN desa0045.Performance Perf ON Perf.MemberID = TM.MemberID
GROUP BY T.TeamID, T.TeamName, T.League;


CREATE VIEW CurrentYearMatches AS
SELECT M.MatchID, T1.TeamName AS TeamName1, T2.TeamName AS TeamName2, M.Result, S.StadiumName
FROM desa0045.Match M
    JOIN desa0045.Stadium S ON M.Stadium = S.StadiumID
    JOIN desa0045.Team T1 ON T1.TeamID = M.Team1
    JOIN desa0045.Team T2 ON T2.TeamID = M.Team2
WHERE EXTRACT(YEAR FROM M.StartTime) = EXTRACT(YEAR FROM CURRENT_DATE);


-- Add LEFT OUTER JOIN between Team and Match if we want to have all the teams and their last match (NULL if the team has never played).
CREATE VIEW LastTeamMatch AS
SELECT T.TeamID, MAX(M.MatchID) AS MatchID
FROM desa0045.Team T
    JOIN desa0045.Match M ON M.Team1 = T.TeamID OR M.Team2 = T.TeamID
    JOIN desa0045.Performance Perf ON Perf.MatchID = M.MatchID 
GROUP BY T.TeamID;


CREATE VIEW SeniorPlayers AS
SELECT P.MemberID, TM.FirstName, TM.LastName, P.Position, P.Nationality, P.BirthDate
FROM desa0045.Player P
    JOIN desa0045.TeamMember TM ON TM.MemberID = P.MemberID
WHERE AGE(P.BirthDate) > INTERVAL '50 years';
-- WHERE EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM BirthDate) > 50;


CREATE VIEW SuperStadiums AS
SELECT S.StadiumID, S.StadiumName, S.Capacity, S.Country, S.City, S.StadiumAddress, S.Price
FROM desa0045.Stadium S
WHERE Capacity > 40000 AND EXISTS(
    SELECT *
    FROM desa0045.Match M
        JOIN desa0045.Team T ON M.Team1 = T.TeamID OR M.Team2 = T.TeamID
        JOIN desa0045.League L ON T.League = L.LeagueID
    WHERE M.Stadium = S.StadiumID AND L.Division = 1
);


CREATE VIEW YoungTalentPlayers AS
SELECT P.MemberID, TM.FirstName, TM.LastName, SUM(Perf.Goals) AS TotalGoals
FROM desa0045.Player P
    JOIN desa0045.Performance Perf ON Perf.MemberID = P.MemberID
    JOIN desa0045.TeamMember TM ON P.MemberID = TM.MemberID
WHERE AGE(P.BirthDate) < INTERVAL '21 years'
-- WHERE EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM BirthDate) < 21
GROUP BY P.MemberID, TM.FirstName, TM.LastName
HAVING SUM(Perf.Goals) > 5;










CREATE MATERIALIZED VIEW TeamWinsLossesDraws AS
SELECT
    T.TeamName,
    COUNT(
        CASE
            WHEN T.TeamID = M.Team1 AND CAST(SUBSTRING(M.Result FROM '(\d+)-') AS INTEGER) > CAST(SUBSTRING(M.Result FROM '-(\d+)') AS INTEGER) THEN 1
            WHEN T.TeamID = M.Team2 AND CAST(SUBSTRING(M.Result FROM '-(\d+)') AS INTEGER) > CAST(SUBSTRING(M.Result FROM '(\d+)-') AS INTEGER) THEN 1
        END
    ) AS Wins,
    COUNT(
        CASE
            WHEN T.TeamID = M.Team1 AND CAST(SUBSTRING(M.Result FROM '(\d+)-') AS INTEGER) < CAST(SUBSTRING(M.Result FROM '-(\d+)') AS INTEGER) THEN 1
            WHEN T.TeamID = M.Team2 AND CAST(SUBSTRING(M.Result FROM '-(\d+)') AS INTEGER) < CAST(SUBSTRING(M.Result FROM '(\d+)-') AS INTEGER) THEN 1
        END
    ) AS Losses,
    COUNT(
        CASE
            WHEN CAST(SUBSTRING(M.Result FROM '(\d+)-') AS INTEGER) = CAST(SUBSTRING(M.Result FROM '-(\d+)') AS INTEGER) THEN 1
        END
    ) AS Draws
FROM desa0045.Team T
    LEFT OUTER JOIN desa0045.Match M ON (T.TeamID = M.Team1 OR T.TeamID = M.Team2)
GROUP BY T.TeamName;


CREATE MATERIALIZED VIEW Leaderboard AS
SELECT TeamName, (Wins * 3 + Draws) AS Points
FROM TeamWinsLossesDraws
ORDER BY Points DESC;


CREATE MATERIALIZED VIEW Top5Players AS
SELECT TM.MemberID, TM.FirstName, TM.LastName, SUM(Perf.Goals) AS TotalGoals, SUM(Perf.Assists) AS TotalAssists
FROM desa0045.TeamMember TM
    JOIN desa0045.Player P ON P.MemberID = TM.MemberID
    JOIN desa0045.Performance Perf ON Perf.MemberID = TM.MemberID
GROUP BY TM.MemberID, TM.FirstName, TM.LastName
ORDER BY TotalGoals DESC, TotalAssists DESC
FETCH FIRST 5 ROWS ONLY;


CREATE MATERIALIZED VIEW TeamGoalDifference AS 
WITH Goals(TeamName, LeagueName, GoalsScored, GoalsConceded) AS (
    SELECT T.TeamName, L.LeagueName,
    SUM(
        CASE
            WHEN T.TeamID = M.Team1 THEN CAST(SUBSTRING(M.Result FROM '(\d+)-') AS INTEGER)
            WHEN T.TeamID = M.Team2 THEN CAST(SUBSTRING(M.Result FROM '-(\d+)') AS INTEGER)
            ELSE 0
        END
    ) AS GoalsScored,
    SUM(
        CASE
            WHEN T.TeamID = M.Team1 THEN CAST(SUBSTRING(M.Result FROM '-(\d+)') AS INTEGER)
            WHEN T.TeamID = M.Team2 THEN CAST(SUBSTRING(M.Result FROM '(\d+)-') AS INTEGER)
            ELSE 0
        END
    ) AS GoalsConceded
    FROM desa0045.Team T
        JOIN desa0045.League L ON L.LeagueID = T.League
        LEFT JOIN desa0045.Match M ON T.TeamID = M.Team1 OR T.TeamID = M.Team2
    GROUP BY T.TeamName, L.LeagueName
)
SELECT TeamName, LeagueName, GoalsScored, GoalsConceded, (GoalsScored - GoalsConceded) AS GoalDifference
FROM Goals;









CREATE FUNCTION SetEndTimeIfNotProvided()
RETURNS TRIGGER AS
$$
BEGIN
    IF NEW.EndTime IS NULL THEN
        NEW.EndTime := NEW.StartTime + INTERVAL '2 hours';
    END IF;
    
    IF NEW.EndTime > CURRENT_TIMESTAMP THEN
        RAISE EXCEPTION 'The end time of the match cannot be in the future. Match must be in the past.';
    END IF;
    
    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER DefaultEndTime
BEFORE INSERT OR UPDATE
ON desa0045.Match
FOR EACH ROW
EXECUTE FUNCTION SetEndTimeIfNotProvided();




CREATE FUNCTION CheckSpectatorLimit()
RETURNS TRIGGER AS
$$
BEGIN
    IF COALESCE(NEW.Spectators, 0) > (SELECT Capacity FROM desa0045.Stadium WHERE StadiumID = NEW.Stadium) THEN
        RAISE EXCEPTION 'Spectators cannot exceed Stadium Capacity';
    END IF;
    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER CheckSpectatorLimitTrigger
BEFORE INSERT OR UPDATE
ON desa0045.Match
FOR EACH ROW
EXECUTE FUNCTION CheckSpectatorLimit();


CREATE FUNCTION CheckRefereeQualification()
RETURNS TRIGGER AS
$$
BEGIN
    IF (SELECT Ref.Qualification
            FROM desa0045.Referee Ref
            WHERE Ref.RefereeID = NEW.RefereeID) > 
        (SELECT L.Division
            FROM desa0045.Match M
            JOIN desa0045.Team T1 ON M.Team1 = T1.TeamID
            JOIN desa0045.Team T2 ON M.Team2 = T2.TeamID
            JOIN desa0045.League L ON T1.League = L.LeagueID OR T2.League = L.LeagueID
            WHERE M.MatchID = NEW.MatchID) THEN
                RAISE EXCEPTION 'Referee does not have the required qualification for this league division.';
    END IF;

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;


CREATE TRIGGER CheckRefereeQualificationTrigger
BEFORE INSERT OR UPDATE
ON desa0045.Officiate
FOR EACH ROW
EXECUTE FUNCTION CheckRefereeQualification();

-- Both teams must be from the same league, when entered to the match
CREATE FUNCTION CheckIfLeaguesMatch()
RETURNS TRIGGER AS
$$
BEGIN
    IF (SELECT League FROM desa0045.Team WHERE TeamID = NEW.Team1) != (SELECT League FROM desa0045.Team WHERE TeamID = NEW.Team2) THEN
        RAISE EXCEPTION 'Teams must be from the same league.';
    END IF;
    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER CheckIfLeaguesMatchTrigger
BEFORE INSERT OR UPDATE
ON desa0045.Match
FOR EACH ROW
EXECUTE FUNCTION CheckIfLeaguesMatch();


CREATE FUNCTION CheckRedCardInLastMatch()
RETURNS TRIGGER AS
$$
BEGIN
    IF EXISTS(
        SELECT *
        FROM desa0045.Performance Perf
            JOIN desa0045.Player P ON P.MemberID = Perf.MemberID
            JOIN desa0045.TeamMember TM ON TM.MemberID = P.MemberID
            JOIN desa0045.Team T ON T.TeamID = TM.Team
            JOIN (SELECT ST.TeamID AS TeamID, SM.MatchID AS MatchID, ROW_NUMBER() OVER (PARTITION BY TeamID ORDER BY MatchID DESC) AS MatcReverseOrder
                  FROM desa0045.Match SM
                      JOIN desa0045.Team ST ON SM.Team1 = ST.TeamID OR SM.Team2 = ST.TeamID
                  ORDER BY SM.MatchID DESC) AS QFS ON Perf.MatchID = QFS.MatchID 
        WHERE Perf.MemberID = NEW.MemberID
            AND Perf.RedCards = 1
            AND QFS.MatcReverseOrder = 2
            AND QFS.TeamID = TM.Team
            -- DO NOT DELETE, maybe will be needed in future
            -- AND Perf.MatchID = (SELECT MatchID FROM desa0045.Match M WHERE M.Team1 = TM.Team OR M.Team2 = TM.Team ORDER BY MatchID DESC FETCH FIRST 1 ROWS ONLY OFFSET 1)
    ) THEN
        RAISE EXCEPTION 'Player % cannot play in the current match because they received red card in the previous match.', NEW.MemberID;
    END IF;
    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER RedCardPrevention
BEFORE INSERT OR UPDATE
ON desa0045.Performance
FOR EACH ROW
EXECUTE FUNCTION CheckRedCardInLastMatch();



CREATE FUNCTION CheckIfTeamMemberIsStaff()
RETURNS TRIGGER AS
$$
BEGIN
    IF EXISTS(
        SELECT *
        FROM desa0045.Staff St
        WHERE St.MemberID = NEW.MemberID
    )
    THEN
        RAISE EXCEPTION 'Staff member already exists with MemberID: %.', NEW.MemberID;
    END IF;
    RETURN NEW;
END;
$$
LANGUAGE plpgsql;


CREATE TRIGGER TeamMemberRestrictionForPlayer
BEFORE INSERT
ON desa0045.Player
FOR EACH ROW
EXECUTE FUNCTION CheckIfTeamMemberIsStaff();


CREATE FUNCTION CheckIfTeamMemberIsPlayer()
RETURNS TRIGGER AS
$$
BEGIN
    IF EXISTS(
        SELECT *
        FROM desa0045.Player P
        WHERE P.MemberID = NEW.MemberID
    )
    THEN
        RAISE EXCEPTION 'Player already exists with MemberID: %.', NEW.MemberID;
    END IF;
    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER TeamMemberRestrictionForStaff
BEFORE INSERT
ON desa0045.Staff
FOR EACH ROW
EXECUTE FUNCTION CheckIfTeamMemberIsPlayer();






-- Materialized views tables update triggers
CREATE FUNCTION RefreshOtherMVTables()
RETURNS TRIGGER AS
$$
BEGIN
    REFRESH MATERIALIZED VIEW TeamWinsLossesDraws;
    REFRESH MATERIALIZED VIEW Leaderboard;
    REFRESH MATERIALIZED VIEW TeamGoalDifference;

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER RefreshOtherMVTablesTrigger
AFTER INSERT OR UPDATE
ON desa0045.Match
FOR EACH STATEMENT
EXECUTE FUNCTION RefreshOtherMVTables();



CREATE FUNCTION RefreshTop5PlayersTable()
RETURNS TRIGGER AS
$$
BEGIN
    REFRESH MATERIALIZED VIEW Top5Players;

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER RefreshTop5PlayersTableTrigger
AFTER INSERT OR UPDATE
ON desa0045.Performance
FOR EACH STATEMENT
EXECUTE FUNCTION RefreshTop5PlayersTable();



CREATE FUNCTION RefreshTeamGDAndLeaderboardTables()
RETURNS TRIGGER AS
$$
BEGIN
    REFRESH MATERIALIZED VIEW TeamWinsLossesDraws;
    REFRESH MATERIALIZED VIEW TeamGoalDifference;
    REFRESH MATERIALIZED VIEW Leaderboard;

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER RefreshTeamGDAndLeaderboardTablesTrigger
AFTER INSERT OR UPDATE
ON desa0045.Team
FOR EACH STATEMENT
EXECUTE FUNCTION RefreshTeamGDAndLeaderboardTables();