IF OBJECT_ID ('dbo.Setor') IS NOT NULL
	DROP TABLE dbo.Setor
GO

CREATE TABLE dbo.Setor
	(
	Id                 INT IDENTITY NOT NULL,
	Descricao          VARCHAR (80) NULL,
	MensagemBoasVindas VARCHAR (80) NULL,
	Ativo              BIT NOT NULL,
	UsuarioInclusao    INT NOT NULL,
	UsuarioAlteracao   INT NULL,
	DataInclusao       DATETIME NOT NULL,
	DataAlteracao      DATETIME NULL,
	CONSTRAINT PK_Setor PRIMARY KEY (Id)
	)
GO

IF OBJECT_ID ('dbo.Perfil') IS NOT NULL
	DROP TABLE dbo.Perfil
GO

CREATE TABLE dbo.Perfil
	(
	Id               INT IDENTITY NOT NULL,
	Descricao        VARCHAR (80) NULL,
	UsuarioInclusao  INT NOT NULL,
	UsuarioAlteracao INT NULL,
	DataInclusao     DATETIME NOT NULL,
	DataAlteracao    DATETIME NULL,
	CONSTRAINT PK_Perfil PRIMARY KEY (Id)
	)
GO

IF OBJECT_ID ('dbo.Usuario') IS NOT NULL
	DROP TABLE dbo.Usuario
GO

CREATE TABLE dbo.Usuario
	(
	Id                   INT IDENTITY NOT NULL,
	IdSetor              INT NULL,
	IdPerfil             INT NOT NULL,
	Nome                 VARCHAR (80) NOT NULL,
	Login                VARCHAR (40) NOT NULL,
	Email                VARCHAR (80) NOT NULL,
	Senha                VARCHAR (50) NULL,
	Genero               VARCHAR (50) NULL,
	Telefone1            VARCHAR (20) NULL,
	Telefone2            VARCHAR (20) NULL,
	CodigoValidacaoEmail VARCHAR (20) NULL,
	DataValidacaoEmail   DATETIME NULL,
	UsuarioInclusao      INT NOT NULL,
	UsuarioAlteracao     INT NULL,
	DataInclusao         DATETIME NOT NULL,
	DataAlteracao        DATETIME NULL,
	Ativo                BIT DEFAULT ((1)) NOT NULL,
	CONSTRAINT PK_Usuario PRIMARY KEY (Id),
	CONSTRAINT FK_Usuario_REFERENCE_Setor FOREIGN KEY (IdSetor) REFERENCES dbo.Setor (Id),
	CONSTRAINT FK_Usuario_REFERENCE_Perfil FOREIGN KEY (IdPerfil) REFERENCES dbo.Perfil (Id)
	)
GO

IF OBJECT_ID ('dbo.Chat') IS NOT NULL
	DROP TABLE dbo.Chat
GO

CREATE TABLE dbo.Chat
	(
	Id                 INT IDENTITY NOT NULL,
	IdUsuario          INT NOT NULL,
	IdSetor            INT NOT NULL,
	DataUltimaMensagem DATETIME NOT NULL,
	DataInicio         DATETIME NOT NULL,
	UsuarioInclusao    INT NOT NULL,
	UsuarioAlteracao   INT NULL,
	DataInclusao       DATETIME NOT NULL,
	DataAlteracao      DATETIME NULL,
	CONSTRAINT PK_Chat PRIMARY KEY (Id),
	CONSTRAINT FK_Chat_REFERENCE_Usuario FOREIGN KEY (IdUsuario) REFERENCES dbo.Usuario (Id),
	CONSTRAINT FK_Chat_REFERENCE_Setor FOREIGN KEY (IdSetor) REFERENCES dbo.Setor (Id)
	)
GO

IF OBJECT_ID ('dbo.Mensagem') IS NOT NULL
	DROP TABLE dbo.Mensagem
GO

CREATE TABLE dbo.Mensagem
	(
	Id                  INT IDENTITY NOT NULL,
	IdUsuario           INT NOT NULL,
	IdChat              INT NOT NULL,
	Mensagem            TEXT NULL,
	DataLeitura         DATETIME NULL,
	NomeUsuarioInclusao VARCHAR (90) NOT NULL,
	UsuarioAlteracao    INT NULL,
	DataInclusao        DATETIME NOT NULL,
	DataAlteracao       DATETIME NULL,
	CONSTRAINT PK_Mensagem PRIMARY KEY (Id),
	CONSTRAINT FK_Mensagem_REFERENCE_Usuario FOREIGN KEY (IdUsuario) REFERENCES dbo.Usuario (Id),
	CONSTRAINT FK_Mensagem_REFERENCE_Chat FOREIGN KEY (IdChat) REFERENCES dbo.Chat (Id)
	)
GO


INSERT INTO dbo.Perfil (Descricao, UsuarioInclusao, UsuarioAlteracao, DataInclusao, DataAlteracao)
VALUES ('Administrador', 1, NULL, '2020-04-02 15:56:34.91', NULL)
GO

INSERT INTO dbo.Perfil (Descricao, UsuarioInclusao, UsuarioAlteracao, DataInclusao, DataAlteracao)
VALUES ('Servidor', 1, NULL, '2020-04-02 15:56:34.913', NULL)
GO

INSERT INTO dbo.Perfil (Descricao, UsuarioInclusao, UsuarioAlteracao, DataInclusao, DataAlteracao)
VALUES ('Público externo', 1, NULL, '2020-04-02 15:56:34.913', NULL)
GO

INSERT INTO dbo.Setor (Descricao, MensagemBoasVindas, Ativo, UsuarioInclusao, UsuarioAlteracao, DataInclusao, DataAlteracao)
VALUES ('NTI', NULL, 1, 1, NULL, '2020-04-02 15:56:34.907', NULL)
GO

INSERT INTO dbo.Usuario (IdSetor, IdPerfil, Nome, Login, Email, Senha, Genero, Telefone1, Telefone2, CodigoValidacaoEmail, DataValidacaoEmail, UsuarioInclusao, UsuarioAlteracao, DataInclusao, DataAlteracao, Ativo)
VALUES (1, 1, 'Pitágoras Vieira Borges', 'pitagoras.araujo', 'pitagoras.araujo@jfpe.jus.br', NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, '2020-04-02 15:56:34.933', NULL, 1)
GO
teste teste tesste