USE [master]
GO
CREATE DATABASE [WWWConference]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'WWWConference', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\WWWConference_Data', SIZE =25600KB , MAXSIZE = 102400KB , FILEGROWTH = 10%)
 LOG ON
( NAME = N'WWWConference_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\WWWConference_Log' , SIZE =25600KB , MAXSIZE = 51200KB , FILEGROWTH = 20%)
GO
ALTER DATABASE [WWWConference] SET COMPATIBILITY_LEVEL = 120
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [WWWConference].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [WWWConference] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [WWWConference] SET ANSI_NULLS OFF
GO
ALTER DATABASE [WWWConference] SET ANSI_PADDING OFF
GO
ALTER DATABASE [WWWConference] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [WWWConference] SET ARITHABORT OFF
GO
ALTER DATABASE [WWWConference] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [WWWConference] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [WWWConference] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [WWWConference] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [WWWConference] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [WWWConference] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [WWWConference] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [WWWConference] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [WWWConference] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [WWWConference] SET  DISABLE_BROKER
GO
ALTER DATABASE [WWWConference] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [WWWConference] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [WWWConference] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [WWWConference] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [WWWConference] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [WWWConference] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [WWWConference] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [WWWConference] SET RECOVERY SIMPLE
GO
ALTER DATABASE [WWWConference] SET  MULTI_USER
GO
ALTER DATABASE [WWWConference] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [WWWConference] SET DB_CHAINING OFF
GO
ALTER DATABASE [WWWConference] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
GO
ALTER DATABASE [WWWConference] SET TARGET_RECOVERY_TIME = 0 SECONDS
GO
ALTER DATABASE [WWWConference] SET DELAYED_DURABILITY = DISABLED
GO
EXEC sys.sp_db_vardecimal_storage_format N'WWWConference', N'ON'
GO

USE [WWWConference]
GO

 --Create users
CREATE LOGIN moder WITH PASSWORD = 'moder'
CREATE USER moder FOR LOGIN moder
GO

 --Create roles
CREATE ROLE administrator
GRANT INSERT, SELECT, UPDATE, DELETE
    ON messages, users, users_pending_review
    TO administrator
EXEC sp_addrolemember 'administrator', 'moder'
GO

CREATE ROLE www_user
GRANT INSERT
    ON messages, users_pending_review
    TO www_user

GRANT SELECT
    ON messages
    TO www_user

CREATE ROLE guest
GRANT SELECT
    ON messages
    TO guest

-- Create schemas

-- Create tables
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE messages
(
    id INTEGER NOT NULL,
    topic VARCHAR(255) NOT NULL,
    text VARCHAR(1000) NOT NULL,
    date DATE NOT NULL,
    parent INTEGER,
    author INTEGER NOT NULL,
    PRIMARY KEY(id)
);
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE users
(
    id INTEGER NOT NULL,
    username VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,
    fullname VARCHAR(255) NOT NULL,
    birth_date DATE,
    email VARCHAR (255),
    PRIMARY KEY(id)
);
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE users_pending_review
(
    primary_key INTEGER NOT NULL,
    username VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,
    fullname VARCHAR(255) NOT NULL,
    birth_date DATE,
    email VARCHAR (255),
    PRIMARY KEY(primary_key)
);
GO

-- Create FKs
ALTER TABLE messages
    ADD    FOREIGN KEY (author)
    REFERENCES users(primary_key)
   --MATCH SIMPLE
;
GO
ALTER TABLE messages
    ADD    FOREIGN KEY (parent)
    REFERENCES messages(id)
   -- MATCH SIMPLE
;
GO

-- Create Indexes
CREATE INDEX full_text ON messages (text, topic, author);
GO

--Create Views
CREATE VIEW short_message_review AS
    SELECT id, topic, author, date
    FROM messages

--Create functions
--Функция на добавление сообщения с созданием новой темы. @text - текст сообщения, @topic - тема.
CREATE FUNCTION new_topic (@text varchar (1000), @topic varchar (256))
AS
BEGIN
    DECLARE @author int
    SET @author = SELECT FIRST(id) FROM users WHERE username = USER_NAME ()
    INSERT INTO messages (topic, text, date, author)
    VALUES (@topic, @text, GETDATE(), @author)
END
GO

--Функция на добавление ответа к уже созданному сообщению. @text - текст сообщения, @parent_id - id сообщения, на который дается ответ
CREATE FUNCTION reply_message (@text varchar (1000), @parent_id int)
AS
BEGIN
    DECLARE @author int
    DECLARE @topic varchar (255)
    SET @author = SELECT FIRST(id) FROM users WHERE username = USER_NAME ()
    SET @topic = 'RE:' +
                (SELECT FIRST(topic) FROM messages WHERE id = @parent_id)
    INSERT INTO messages (topic, text, date, author, parent)
    VALUES (@topic, @text, GETDATE(), @author, @parent_id)
END
GO

--Функция отправки зароса на регистрацию. @username - имя пользователя, @password - пароль. @fullname - полное имя
--@email - адрес электронной почты, @birth_date - дата рождения. Возвращает 1, если пользователь с таким userame уже есть в таблице, иначе - 0.
CREATE FUNCTION require_registration (@username varchar (255), @password varchar (255), @fullname varchar (255), @email varchar (255), @birth_date DATE)
RETURNS int
AS
BEGIN
    IF EXISTS (SELECT * FROM users WHERE username = @username)
    BEGIN
        PRINT N'User with such name has already been registered';
        RETURN 1
    END
    ELSE
    BEGIN
        INSERT INTO users_pending_review (username, password, fullname, email, birth_date)
        VALUES (@username, @password, @fullname, @email, @birth_date)
        RETURN 0
    END
END
GO


--Функция для подтверждения регистрации пользователя с id @id из таблицы users_pending_review. Возвращает 0, если успешно, 1 - если неуспешно
CREATE FUNCTION confirm_registration (@id int)
RETURNS int
AS
BEGIN
    DECLARE @user_exists int
    DECLARE @username, @password, @fullname, @email varchar (255)
    DECLARE @birth_date DATE
    SET @username = SELECT FIRST (username) FROM users_pending_review WHERE id = @id
    SET @password = SELECT FIRST (password) FROM users_pending_review WHERE id = @id
    SET @fullname = SELECT FIRST (fullname) FROM users_pending_review WHERE id = @id
    SET @birth_date = SELECT FIRST (birth_date) FROM users_pending_review WHERE id = @id
    IF EXISTS (SELECT * FROM users WHERE username = @username)
    BEGIN
        PRINT N'User with such name has already been registered';
        RETURN 1
    END
    ELSE
    BEGIN
        CREATE LOGIN @username WITH PASSWORD = @password
        CREATE USER @username FOR LOGIN @username
        exec sp_addrolemember 'www_user', username
        INSERT INTO users (username, password, fullname, email, birth_date)
        VALUES (@username, @password, @fullname, @email, @birth_date)
        DELETE FROM users_pending_review
        WHERE id = @id
        RETURN 0
    END
END
GO

--Функция, возвращающая текст сообщения по id
CREATE FUNCTION get_text_message (@id int)
RETURNS varchar (1000)
AS
    RETURN (SELECT text FROM messages WHERE id = @id)
GO

--Функция, блокирующая доступ пользователя с логином @username к добавлению сообщений
CREATE FUNCTION block_user (@username varchar (255))
AS
DENY INSERT
    ON messages
    TO @username
GO

--Функция, разблокирующая доступ пользователя с логином @username к добавлению сообщений
CREATE FUNCTION block_user (@username varchar (255))
AS
GRANT INSERT
    ON messages
    TO @username
GO

--Функция, удаляющая сообщение с номером @id из таблицы
CREATE FUNCTION delete_message (@id int)
AS
    DELETE FROM messages
    WHERE id = @id
GO

--Функция поиска по слову в теме
CREATE FUNCTION search_in_topic (@word varchar (255))
AS
    SELECT * FROM messages where topic LIKE '%' + @word + '%'
GO


--Функция поиска по слову в тексте
CREATE FUNCTION search_in_text (@word varchar (255))
AS
    SELECT * FROM messages where text LIKE '%' + @word + '%'
GO


--Функция поиска по автору
CREATE FUNCTION search_in_author (@word varchar (255))
AS
    SELECT * FROM messages where author LIKE '%' + @word + '%'
GO

--create procedures
--Процедура вывода списка сообщений
CREATE PROCEDURE review_messages
AS
    SELECT * FROM short_messages_review
    GROUP BY parent_id
GO


--create triggers
CREATE TRIGGER delete_trigger
ON messages
AFTER DELETE
AS
    DELETE FROM messages
    WHERE parent_id = (SELECT FIRST(id) FROM deleted)
GO


USE [master]
GO
ALTER DATABASE [WWWConference] SET  READ_WRITE
GO