USE AKovrizhnykh

IF OBJECT_ID('Lab3.getGameForTeam') IS NOT NULL
    DROP FUNCTION Lab3.getGameForTeam
IF OBJECT_ID('Lab3.ShowScoreTable') IS NOT NULL
    DROP PROCEDURE Lab3.ShowScoreTable
IF OBJECT_ID('Lab3.missed') IS NOT NULL
    DROP FUNCTION Lab3.missed
IF OBJECT_ID('Lab3.scored') IS NOT NULL
    DROP FUNCTION Lab3.scored
IF OBJECT_ID('Lab3.win_count') IS NOT NULL
    DROP FUNCTION Lab3.win_count
IF OBJECT_ID('Lab3.draw_count') IS NOT NULL
    DROP FUNCTION Lab3.draw_count
IF OBJECT_ID('Lab3.calc_score') IS NOT NULL
    DROP FUNCTION Lab3.calc_score
IF OBJECT_ID('Lab3.GetWinWithEqualsScores') IS NOT NULL
    DROP FUNCTION Lab3.GetWinWithEqualsScores
IF OBJECT_ID('Lab3.GoalsOnGuest') IS NOT NULL
    DROP FUNCTION Lab3.GoalsOnGuest
IF OBJECT_ID('Lab3.GoalsDifferent') IS NOT NULL
    DROP FUNCTION Lab3.GoalsDifferent

IF OBJECT_ID('Lab3.Stat') IS NOT NULL
    DROP VIEW Lab3.Stat
IF OBJECT_ID('Lab3.Rating') IS NOT NULL
    DROP VIEW Lab3.Rating

IF OBJECT_ID('Lab3.Goals') IS NOT NULL
        DROP TABLE Lab3.Goals
IF OBJECT_ID('Lab3.Matches') IS NOT NULL
        DROP TABLE Lab3.Matches
IF OBJECT_ID('Lab3.Players') IS NOT NULL
        DROP TABLE Lab3.Players
IF OBJECT_ID('Lab3.Teams') IS NOT NULL
        DROP TABLE Lab3.Teams
IF OBJECT_ID('Lab3.CheckGoalKeeper') IS NOT NULL
    DROP FUNCTION Lab3.CheckGoalKeeper
IF OBJECT_ID('Lab3.IsMatchPlayer') IS NOT NULL
    DROP FUNCTION Lab3.IsMatchPlayer


IF EXISTS(SELECT * FROM sys.schemas WHERE name='Lab3')
        DROP SCHEMA Lab3
GO
CREATE SCHEMA Lab3
GO

CREATE TABLE Lab3.Teams (
        id int IDENTITY(1,1),
        name nvarchar(60),
        country nvarchar(60),
        CONSTRAINT PK_Teams_id PRIMARY KEY (id)
)
CREATE TABLE Lab3.Players (
        id int IDENTITY(1,1),
        name nvarchar(200),
        number int,
        team_id int,
        CONSTRAINT PK_Players_id PRIMARY KEY (id),
        CONSTRAINT FK_Players_team_id FOREIGN KEY (team_id) REFERENCES Lab3.Teams(id),
        CONSTRAINT UK_Players_team UNIQUE (id, team_id)
)

GO
CREATE FUNCTION Lab3.CheckGoalkeeper(@playerId int, @teamId int)
RETURNS bit
BEGIN
    RETURN IIF(EXISTS(SELECT * FROM Lab3.Players AS p WHERE p.id=@playerId AND p.team_id=@teamId), 1, 0);
END
GO

CREATE TABLE Lab3.Matches (
        id int IDENTITY(1,1),
        home_id int,
        home_goalkeeper_id int,
        guest_id int,
        guest_goalkeeper_id int,
        datetime datetime,
        CONSTRAINT PK_Matches_id PRIMARY KEY (id),
        CONSTRAINT FK_Matches_home_id FOREIGN KEY (home_id) REFERENCES Lab3.Teams(id),
        CONSTRAINT FK_Matches_guest_id FOREIGN KEY (guest_id) REFERENCES Lab3.Teams(id),
        CONSTRAINT FK_Matches_home_goalkeeper_id FOREIGN KEY (home_goalkeeper_id) REFERENCES Lab3.Players(id),
        CONSTRAINT FK_Matches_guest_goalkeeper_id FOREIGN KEY (guest_goalkeeper_id) REFERENCES Lab3.Players(id),
        CONSTRAINT CH_Matches_teams CHECK (home_id != guest_id),
        CONSTRAINT UK_Matches_teams UNIQUE (home_id, guest_id),
        CONSTRAINT CH_Matches_goalkeepers CHECK (Lab3.CheckGoalkeeper(home_goalkeeper_id, home_id) = 1 AND Lab3.CheckGoalkeeper(guest_goalkeeper_id, guest_id) = 1)
)
GO
CREATE FUNCTION Lab3.IsMatchPlayer(@playerId int, @matchId int)
RETURNS bit
BEGIN
    DECLARE @home int = (SELECT home_id FROM Lab3.Matches WHERE id=@matchId);
    DECLARE @guest int = (SELECT guest_id FROM Lab3.Matches WHERE id=@matchId);
    RETURN IIF(
        EXISTS(SELECT * FROM Lab3.Players WHERE id=@playerId AND team_id=@home) OR EXISTS(SELECT * FROM Lab3.Players WHERE id=@playerId AND team_id=@guest),
        1, 0
    );
END
GO
CREATE TABLE Lab3.Goals (
        id int IDENTITY(1,1),
        match_id int,
        time time,
        attacker_id int,
        home_or_guest bit,
        CONSTRAINT PK_Goals_id PRIMARY KEY (id),
        CONSTRAINT FK_Goals_match_id FOREIGN KEY (match_id) REFERENCES Lab3.Matches(id),
        CONSTRAINT FK_Goals_attacker_id FOREIGN KEY (attacker_id) REFERENCES Lab3.Players(id),
        CONSTRAINT CH_Golas_valid_author CHECK (1=Lab3.IsMatchPlayer(attacker_id, match_id))
)

INSERT INTO Lab3.Teams VALUES
        ('Спартак', 'Россия'),
        ('ЦСКА', 'Россия'),
        ('Рубин', 'Россия'),
        ('Зинит', 'Россия')

INSERT INTO Lab3.Players VALUES
        -- Спартак
        ('Артём Ребров', 32, 1), -- 1
        ('Сердар Таски', 35, 1),
        ('Евгений Макеев', 34, 1),
        ('Сергей Паршивлюк', 4, 1),
        ('Сальваторе Боккетти', 16, 1),
        ('Дмитрий Комбаров', 23, 1),
        ('Денис Глушаков', 8, 1),
        ('Роман Широков', 15, 1),
        ('Квинси Промес', 24, 1),
        ('Ивелин Попов', 71, 1),
        ('Зе Луиш', 20, 1), -- 11
        -- ЦСКА
        ('Игорь Акинфеев', 35, 2), -- 12
        ('Марио Фернандес', 2, 2),
        ('Сергей Игнашевич', 4, 2),
        ('Алексей Березуцкий', 6, 2),
        ('Зоран Тошич', 7, 2),
        ('Александр Головин', 60, 2),
        ('Александр Цауня', 19, 2),
        ('Виктор Васин', 5, 2),
        ('Понтус Вернблум', 3, 2),
        ('Карлос Страндберг', 97, 2),
        ('Кирилл Панченко', 8, 2), -- 22
        -- Рубин
        ('Сергей Рыжиков', 1, 3), -- 23
        ('Руслан Камболов', 88, 3),
        ('Соломон Кверквелия', 5, 3),
        ('Олег Кузьмин', 2, 3),
        ('Эльмир Набиуллин', 3, 3),
        ('Благой Георгиев', 77, 3),
        ('Магомед Оздоев', 27, 3),
        ('Гёкдениз Карадениз', 61, 3),
        ('Карлос Эдуардо', 10, 3),
        ('Максим Канунников', 99, 3),
        ('Марко Девич', 11, 3), -- 33
        -- Зенит
        ('Юрий Лодыгин', 1, 4), -- 34
        ('Эсекьель Гарай', 24, 4),
        ('Николас Ломбертс', 6, 4),
        ('Игорь Смольников', 19, 4),
        ('Доменико Кришито', 4, 4),
        ('Хави Гарсия', 21, 4),
        ('Аксель Витсель', 28, 4),
        ('Данни', 10, 4),
        ('Халк', 7, 4),
        ('Артём Дзюба', 22, 4),
        ('Павел Долгов', 92, 4) -- 44

-- Спартак - Зенит
INSERT INTO Lab3.Matches VALUES
        (1, 1, 4, 34, GETDATE())
INSERT INTO Lab3.Goals VALUES
        (1, '0:30', 11, 1),
        (1, '0:45', 10, 1),
        (1, '0:56', 42, 0),
        (1, '1:20', 43, 0)


-- Зенит - Спартак
INSERT INTO Lab3.Matches VALUES
        (4, 34, 1, 1, GETDATE())

-- Спартак - ЦСКА
INSERT INTO Lab3.Matches VALUES
        (1, 1, 2, 12, GETDATE())
INSERT INTO Lab3.Goals VALUES
        (3, '0:23', 11, 1),
        (3, '0:56', 22, 0),
        (3, '01:23', 9, 1)


-- ЦСКА - Рубин
INSERT INTO Lab3.Matches VALUES
        (2, 12, 3, 23, GETDATE())
INSERT INTO Lab3.Goals VALUES
        (4, '0:45', 33, 1),
        (4, '0:46', 22, 0),
        (4, '01:35', 32, 1)

-- Рубин - Спартак
INSERT INTO Lab3.Matches VALUES
        (3, 23, 1, 1, GETDATE())
INSERT INTO Lab3.Goals VALUES
        (5, '0:12', 31, 1),
        (5, '0:33', 11, 0),
        (5, '01:20', 30, 1),
        (5, '1:23', 10  , 0),
        (5, '1:25', 32, 1)

-- ЦСКА - Спартак
INSERT INTO Lab3.Matches VALUES
        (2, 12, 1, 1, GETDATE())
INSERT INTO Lab3.Goals VALUES
        (6, '0:36', 22, 1),
        (6, '0:39', 21, 1)

-- Спартак - Рубин
INSERT INTO Lab3.Matches VALUES
        (1, 1, 3, 23, GETDATE())
INSERT INTO Lab3.Goals VALUES
        (7, '1:12', 5, 1),
        (7, '1:29', 27, 0)

-- Рубин - Зенит
INSERT INTO Lab3.Matches VALUES
        (3, 23, 4, 34, GETDATE())
INSERT INTO Lab3.Goals VALUES
        (8, '0:56', 28, 1),
        (8, '1:04', 38, 0)

-- Зенит - Рубин
INSERT INTO Lab3.Matches VALUES
        (4, 34, 3, 23, GETDATE())
INSERT INTO Lab3.Goals VALUES
        (8, '1:01', 39, 1),
        (8, '1:12', 27, 0)

GO
CREATE VIEW Lab3.Stat AS
SELECT Home.id AS 'home_id', Home.name AS 'host', Guest.id AS 'guest_id', Guest.name AS 'guest',
        (SELECT COUNT(*) FROM Lab3.Goals AS G WHERE G.match_id=M.id AND G.home_or_guest=1) AS 'host_goals',
        (SELECT COUNT(*) FROM Lab3.Goals AS G WHERE G.match_id=M.id AND G.home_or_guest=0) AS 'guest_goals'
                FROM Lab3.Matches AS M
        JOIN Lab3.Teams AS Home ON M.home_id=Home.id
        JOIN Lab3.Teams AS Guest ON M.guest_id=Guest.id

GO

GO
CREATE FUNCTION Lab3.getGameForTeam(@teamId int)
RETURNS TABLE
AS
RETURN
(
    SELECT t.id,
        IIF(
            EXISTS(SELECT id FROM Lab3.Stat AS s WHERE s.home_id=t.id AND s.guest_id=@teamId),
            (SELECT CONCAT(s.host_goals, ':', s.guest_goals) FROM Lab3.Stat AS s WHERE s.home_id=t.id AND s.guest_id=@teamId),
            '-'
        ) AS 'result' FROM Lab3.Teams AS t
)
GO
CREATE FUNCTION Lab3.scored(@teamId int)
RETURNS int
AS
BEGIN
    RETURN (
        SELECT ISNULL((SELECT SUM(s1.host_goals) FROM Lab3.Stat AS s1 WHERE s1.home_id = @teamId), 0) +
            ISNULL((SELECT SUM(s2.guest_goals) FROM Lab3.Stat AS s2 WHERE s2.guest_id = @teamId), 0)
    )
END
GO
CREATE FUNCTION Lab3.missed(@teamId int)
RETURNS int
AS
BEGIN
    RETURN (
        SELECT ISNULL((SELECT SUM(s1.host_goals) FROM Lab3.Stat AS s1 WHERE s1.guest_id = @teamId), 0) +
            ISNULL((SELECT SUM(s2.guest_goals) FROM Lab3.Stat AS s2 WHERE s2.home_id = @teamId), 0)
    )
END
GO
CREATE FUNCTION Lab3.win_count(@teamId int)
RETURNS int
AS
BEGIN
    RETURN (
        (SELECT COUNT(*) FROM Lab3.Stat WHERE (home_id = @teamId AND host_goals > guest_goals) OR (guest_id = @teamId AND host_goals < guest_goals))
    )
END
GO
CREATE FUNCTION Lab3.draw_count(@teamId int)
RETURNS int
AS
BEGIN
    RETURN (
        (SELECT COUNT(*) FROM Lab3.Stat WHERE (guest_id = @teamId OR home_id = @teamId) AND host_goals = guest_goals)
    )
END
GO
CREATE FUNCTION Lab3.calc_score(@teamId int)
RETURNS int
AS
BEGIN
    RETURN 3*Lab3.win_count(@teamId) + Lab3.draw_count(@teamId);
END
GO

CREATE PROCEDURE Lab3.ShowScoreTable
AS BEGIN
    DECLARE @table_query nvarchar(4000) = 'SELECT name AS [ ]';
    SELECT @table_query += CONCAT(', team', t.id, '.result AS [', t.name, ']') FROM Lab3.Teams AS t;
    SELECT @table_query += ' FROM Lab3.Teams AS t';
    SELECT @table_query += CONCAT(' JOIN Lab3.getGameForTeam(', t.id, ') AS team', t.id, ' ON team', t.id, '.id=t.id') FROM Lab3.Teams AS t;
    EXEC(@table_query);
END

GO
CREATE FUNCTION Lab3.GetWinWithEqualsScores(@teamId int)
RETURNS int
BEGIN
    DECLARE @score int = Lab3.calc_score(@teamId);
    DECLARE @result int = (SELECT COUNT(*) FROM Lab3.Stat AS s WHERE (s.home_id=@teamId AND Lab3.calc_score(s.guest_id)=@score AND s.host_goals > s.guest_goals) OR
        (s.guest_id=@teamId AND Lab3.calc_score(s.home_id)=@score AND s.host_goals < s.guest_goals))
    RETURN @result;
END

GO
CREATE FUNCTION Lab3.GoalsOnGuest(@teamId int)
RETURNS int
BEGIN
    RETURN (SELECT COUNT(s.guest_goals) FROM Lab3.Stat AS s WHERE s.guest_id=@teamId);
END
GO

CREATE FUNCTION Lab3.GoalsDifferent(@teamId int)
RETURNS int
BEGIN
    DECLARE @scored int = (SELECT SUM(s.host_goals) FROM Lab3.Stat AS s WHERE s.host_goals=@teamId) + (SELECT SUM(s.guest_goals) FROM Lab3.Stat AS s WHERE s.guest_goals=@teamId);
    DECLARE @missed int = (SELECT SUM(s.guest_goals) FROM Lab3.Stat AS s WHERE s.host_goals=@teamId) + (SELECT SUM(s.host_goals) FROM Lab3.Stat AS s WHERE s.guest_goals=@teamId);
    RETURN @scored - @missed;
END
GO

CREATE VIEW Lab3.Rating AS
SELECT TOP(100)
    t.name AS [ ],
    Lab3.calc_score(t.id) AS 'Очки',
    Lab3.scored(t.id) AS 'Забито',
    Lab3.missed(t.id) AS 'Пропущено'
    FROM Lab3.Teams AS t ORDER BY 2 DESC, Lab3.GetWinWithEqualsScores(t.id) DESC, Lab3.GoalsOnGuest(t.id) DESC, Lab3.GoalsDifferent(t.id) DESC;
GO

SELECT * FROM Lab3.Rating;
EXEC Lab3.ShowScoreTable;