USE [auth_mvc]
GO
/****** Object:  User [DEV_CA]    Script Date: 20/05/2019 08:12:33 ******/
CREATE USER [DEV_CA] FOR LOGIN [DEV_CA] WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [db_owner] ADD MEMBER [DEV_CA]
GO
/****** Object:  Schema [auth]    Script Date: 20/05/2019 08:12:33 ******/
CREATE SCHEMA [auth]
GO
/****** Object:  Table [auth].[GROUPS]    Script Date: 20/05/2019 08:12:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [auth].[GROUPS](
	[GR_ID] [int] IDENTITY(1,1) NOT NULL,
	[GR_NAME] [nvarchar](50) NULL,
	[GR_DESCR] [nvarchar](500) NULL,
	[GR_CREATION_DATE] [date] NULL,
	[GR_DEFAULT] [int] NULL,
 CONSTRAINT [PK_MD_GROUPES] PRIMARY KEY CLUSTERED
(
	[GR_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [auth].[PASSWORD]    Script Date: 20/05/2019 08:12:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [auth].[PASSWORD](
	[PWD_ID] [bigint] IDENTITY(1,1) NOT NULL,
	[PWD_VALUE] [nvarchar](200) NULL,
	[PWD_EXPIRATION_DATE] [date] NULL,
	[PWD_RESET_TOKEN] [nvarchar](50) NULL,
	[PWD_RESET_TIME] [datetime] NULL,
	[USR_ID] [bigint] NOT NULL,
 CONSTRAINT [PK_PASSWORD] PRIMARY KEY CLUSTERED
(
	[PWD_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [auth].[REL_USER_GROUP]    Script Date: 20/05/2019 08:12:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [auth].[REL_USER_GROUP](
	[REL_USR_GR_ID] [bigint] IDENTITY(1,1) NOT NULL,
	[USER_ID] [bigint] NULL,
	[GROUP_ID] [bigint] NULL,
	[REL_DATE] [date] NULL,
 CONSTRAINT [PK_MD_REL_USER_GROUP] PRIMARY KEY CLUSTERED
(
	[REL_USR_GR_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [auth].[USERS]    Script Date: 20/05/2019 08:12:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [auth].[USERS](
	[USR_ID] [bigint] NOT NULL,
	[USR_LOGIN] [varbinary](150) NULL,
	[USR_FIRSTNAME] [varbinary](150) NULL,
	[USR_LASTNAME] [varbinary](150) NULL,
	[USR_MAIL] [varbinary](150) NULL,
	[USR_ADDRESS] [varbinary](455) NULL,
	[USR_CREATION_DATE] [date] NULL,
	[USR_CONNECTED] [smallint] NULL,
	[USR_ACTIVE] [smallint] NULL,
 CONSTRAINT [PK_MD_USERS] PRIMARY KEY CLUSTERED
(
	[USR_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [auth].[USERS] ADD  DEFAULT (getdate()) FOR [USR_CREATION_DATE]
GO
ALTER TABLE [auth].[USERS] ADD  CONSTRAINT [DF_MD_USERS_USR_CONNECTED]  DEFAULT ((0)) FOR [USR_CONNECTED]
GO
/****** Object:  StoredProcedure [auth].[get user info and privileges]    Script Date: 20/05/2019 08:12:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [auth].[get user info and privileges]
	-- Add the parameters for the stored procedure here
	@NVC_LOGIN nvarchar(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	select  USR_FIRSTNAME,
			USR_LASTNAME,
			USR_MAIL,
			USR_ADDRESS,
			USR_CREATION_DATE
	from auth.USERS

	--todo : create thesaurus, create REL_GROUP_FN
	SELECT *
	FROM auth.REL_USER_GROUP

END
GO
/****** Object:  StoredProcedure [auth].[get user password and status]    Script Date: 20/05/2019 08:12:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [auth].[get user password and status]
	-- Add the parameters for the stored procedure here
	@NVC_LOGIN nvarchar(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT  Password,
			Expired,
			alreadyConnected,
			active
	FROM
		(SELECT USR_ID,
				USR_CONNECTED as alreadyConnected,
				USR_ACTIVE as active
		 FROM auth.USERS
		 WHERE USR_LOGIN = @NVC_LOGIN ) usr INNER JOIN
		(SELECT PWD_VALUE as Password,
				iif(PWD_EXPIRATION_DATE < GETDATE(), 1,0) as expired,
				USR_ID
		 FROM AUTH.PASSWORD ) pwd
		on pwd.USR_ID = usr.USR_ID
END
GO