USE master
GO

IF  EXISTS (
        SELECT name
                FROM sys.DATABASES
                WHERE name = N'ElenaBeklenishcheva'
)
ALTER DATABASE ElenaBeklenishcheva SET single_user WITH ROLLBACK immediate
GO

IF  EXISTS (
        SELECT name
                FROM sys.DATABASES
                WHERE name = N'ElenaBeklenishcheva'
)
DROP DATABASE [ElenaBeklenishcheva]
GO

CREATE DATABASE [ElenaBeklenishcheva]
GO

USE [ElenaBeklenishcheva]
GO

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

CREATE SCHEMA Scheme
GO


IF OBJECT_ID('commands', 'U') IS NOT NULL
    DROP TABLE commands
GO

CREATE TABLE commands (
    command_id INT PRIMARY KEY NOT NULL,
    command_name VARCHAR(50) UNIQUE NOT NULL
)
GO

INSERT INTO commands VALUES
(1, 'Урал'),
(2, 'Спартак'),
(3, 'Рубин')
GO


IF OBJECT_ID('conists', 'U') IS NOT NULL
    DROP TABLE consist
GO

CREATE TABLE consist (
    command_id INT NOT NULL,
    player_id INT PRIMARY KEY NOT NULL,
    player_surname VARCHAR(50) NOT NULL,
    player_name VARCHAR(50) NOT NULL

    FOREIGN KEY (command_id) REFERENCES commands(command_id)
)
GO

INSERT INTO consist VALUES
(1, 1, 'Арапов', 'Дмитрий'),
(1, 2, 'Заболотный', 'Николай'),
(1, 3, 'Жевнов', 'Юрий'),
(1, 4, 'Белозеров', 'Александр'),
(1, 5, 'Фомин', 'Денис'),

(2, 6, 'Ребров', 'Артем'),
(2, 7, 'Гранат', 'Владимир'),
(2, 8, 'Кутепов', 'Илья'),
(2, 9, 'Глушаков', 'Денис'),

(3, 10, 'Кузьмин', 'Олег'),
(3, 11, 'Устинов', 'Виталий'),
(3, 12, 'Сорокин', 'Егор'),
(3, 13, 'Жестоков', 'Максим')
GO


IF OBJECT_ID('games_info', 'U') IS NOT NULL
    DROP TABLE games_info
GO

CREATE TABLE games_info (
    id_game INT PRIMARY KEY NOT NULL,
    game_date DATE NOT NULL,
    id_command_owner INT NOT NULL,
    id_command_guest INT NOT NULL,
    id_kipper_own INT NOT NULL,
    id_kipper_g INT NOT NULL,
    score_command_own INT NOT NULL,
    score_command_guest INT NOT NULL

    FOREIGN KEY (id_kipper_own) REFERENCES consist(player_id),
    FOREIGN KEY (id_kipper_g) REFERENCES consist(player_id)
)
GO

INSERT INTO games_info VALUES
(1, '20151125', 1, 2, 1, 6, 1, 1),
(2, '20151124', 2, 3, 7, 10, 2, 0),
(3, '20151123', 3, 1, 11, 2, 0, 3),
(4, '20160322', 2, 1, 7, 1, 3, 2),
(5, '20160321', 3, 2, 11, 6, 1, 1),
(6, '20160320', 1, 3, 1, 10, 1, 0)
GO



IF OBJECT_ID('goals', 'U') IS NOT NULL
    DROP TABLE goals
GO

CREATE TABLE goals(
    id_game INT NOT NULL,
    id_player INT NOT NULL,
    id_goal INT PRIMARY KEY NOT NULL

    FOREIGN KEY (id_player) REFERENCES consist(player_id),
    FOREIGN KEY (id_game) REFERENCES games_info(id_game)
)
GO

INSERT INTO goals VALUES
(1, 2, 1),
(1, 7, 2),

(2, 8, 3),
(2, 8, 4),

(3, 2, 5),
(3, 3, 6),
(3, 4, 7),

(4, 6, 8),
(4, 8, 9),
(4, 9, 10),
(4, 3, 11),
(4, 4, 12),

(5, 12, 13),
(5, 8, 14),

(6, 3, 15)
GO


SELECT * FROM goals
GO

CREATE TABLE res_raiting_table(
    POSITION INT PRIMARY KEY IDENTITY NOT NULL,
    Team INT UNIQUE NOT NULL,
    Score INT NOT NULL,
    Scored INT NOT NULL,
    Missing INT NOT NULL
)
GO


CREATE FUNCTION raiting_table()
RETURNS @ret_raiting_table TABLE (
    POSITION INT PRIMARY KEY IDENTITY NOT NULL,
    Team INT UNIQUE NOT NULL,
    Score INT NOT NULL,
    Scored INT NOT NULL,
    Missing INT NOT NULL
)
AS
BEGIN
    --заполнить таблицу
    DECLARE @DATE DATE = '20151201';
    DECLARE @temp_table TABLE(tteam INT,
                              tscore INT,
                              tscored INT,
                              tmissing INT)

    DECLARE @teams_count INT;
    SET @teams_count = (SELECT COUNT(commands.command_id)
                        FROM commands)
    DECLARE @counter INT = 1;
    WHILE(@counter <= @teams_count)
    BEGIN
        DECLARE @score INT = 3 * (SELECT COUNT(*)
                                  FROM games_info
                                  WHERE (game_date < @DATE OR game_date = @DATE) AND(
                                        id_command_guest = @counter
                                        AND
                                        score_command_guest > score_command_own
                                    ) OR (
                                        games_info.id_command_owner = @counter
                                        AND score_command_own > score_command_guest
                                    ))
        SET @score = @score + (SELECT COUNT(*)
                                  FROM games_info
                                  WHERE (game_date < @DATE OR game_date = @DATE) AND(
                                        id_command_guest = @counter
                                        AND
                                        score_command_guest = score_command_own
                                    ) OR (
                                        games_info.id_command_owner = @counter
                                        AND score_command_own = score_command_guest
                                    ))

        DECLARE @scored INT = (SELECT SUM(score_command_own)
                               FROM games_info
                               WHERE id_command_owner = @counter AND (game_date < @DATE OR game_date = @DATE))
        SET @scored = @scored +  (SELECT SUM(score_command_guest)
                               FROM games_info
                               WHERE id_command_guest = @counter AND (game_date < @DATE OR game_date = @DATE))

        DECLARE @missing INT = (SELECT SUM(score_command_guest)
                               FROM games_info
                               WHERE id_command_owner = @counter AND (game_date < @DATE OR game_date = @DATE))
        SET @missing = @missing +  (SELECT SUM(score_command_own)
                               FROM games_info
                               WHERE id_command_guest = @counter AND (game_date < @DATE OR game_date = @DATE))

        INSERT INTO @temp_table VALUES(@counter, @score, @scored, @missing)

        SET @counter = @counter + 1
    END

    INSERT @ret_raiting_table
    SELECT *
    FROM @temp_table
    ORDER BY tscore DESC
    RETURN
END
GO


IF OBJECT_ID('raiting', 'V') IS NOT NULL
    DROP VIEW raiting
GO

CREATE VIEW raiting AS
SELECT POSITION,
       commands.command_name AS Commands,
       Score,
       Scored,
       Missing
FROM raiting_table()
INNER JOIN commands ON commands.command_id = Team
GO

-- результаты личных встреч
CREATE FUNCTION Scheme.win_count(@teamId INT)
RETURNS INT
AS
BEGIN
RETURN (
    (
    SELECT COUNT(*)
    FROM games_info
    WHERE (
        id_command_owner = @teamId AND score_command_own > score_command_guest
        ) OR (
        id_command_guest = @teamId AND score_command_own < score_command_guest
        )
        )
)
END
GO

-- количество голов на поле соперника
CREATE FUNCTION Scheme.guest_goals(@teamId INT)
RETURNS INT
AS
BEGIN
RETURN (
    SELECT SUM(games_info.score_command_guest)
    FROM games_info
    WHERE (
        id_command_guest = @teamId
    )
)
END
GO

SELECT * FROM raiting

IF OBJECT_ID('kippers', 'V') IS NOT NULL
    DROP VIEW kippers
GO

--представление с вратарями
CREATE VIEW kippers AS
SELECT games_info.game_date AS 'Дата игры',
       commands.command_name AS 'Команда',
       consist.player_surname + ' ' + consist.player_name AS 'Вратарь'
FROM games_info
    INNER JOIN commands ON commands.command_id = games_info.id_command_owner
    INNER JOIN consist ON consist.player_id = games_info.id_kipper_own
UNION (
SELECT games_info.game_date AS 'Дата игры',
       commands.command_name AS 'Команда',
       consist.player_surname + ' ' + consist.player_name AS 'Вратарь'
FROM games_info
    INNER JOIN commands ON commands.command_id = games_info.id_command_guest
    INNER JOIN consist ON consist.player_id = games_info.id_kipper_g
)
GO

SELECT * FROM kippers
ORDER BY 'Команда'
GO


IF OBJECT_ID('bombardiers', 'V') IS NOT NULL
    DROP VIEW bombardiers
GO

CREATE VIEW bombardiers AS
SELECT games_info.game_date AS 'Дата игры',
       commands.command_name AS 'Команда',
       consist.player_surname + ' ' + consist.player_name AS 'Вратарь'
FROM goals
    INNER JOIN games_info ON games_info.id_game = goals.id_game
    INNER JOIN consist ON consist.player_id = goals.id_player
    INNER JOIN commands ON commands.command_id = consist.command_id
GO

SELECT * FROM bombardiers
ORDER BY 'Дата игры'
GO


SELECT 'Команды', [Урал], [Спартак], [Рубин]
    FROM (SELECT * FROM commands) p
pivot (
    COUNT(command_name)
    FOR command_name IN ([Урал], [Спартак], [Рубин])
) t
GO