CREATE TABLE [dbo].[Users] (
    [UserId]                BIGINT         IDENTITY (1, 1) NOT NULL,
    [UserRoleId]            TINYINT        CONSTRAINT [DF_Users_UserRoleId] DEFAULT 0 NOT NULL,
	[ApiConsumerId]			INT			   NOT NULL,
    [Email]                 NVARCHAR (250) NOT NULL,
    [UserTypeId]            TINYINT        NOT NULL,
    [Password]              VARCHAR (100)  NOT NULL,
    [FirstName]             NVARCHAR (50)  NULL,
    [LastName]              NVARCHAR (50)  NULL,
    [CountryId]             INT            NULL,
    [PhonePrefix]           VARCHAR (20)   NULL,
    [Phone]                 VARCHAR (50)   NULL,
    [Address]               NVARCHAR (100) NULL,
    [City]                  NVARCHAR (100) NULL,
    [ZipCode]               VARCHAR (16)   NULL,
    [BirthDate]             DATE           NULL,
    [Gender]                VARCHAR (50)   NULL,
    [LanguageId]            TINYINT        NULL,
    [IsUSTaxEntity]         BIT            NULL,
    [RegistrationDate]      DATETIME2 (7)  CONSTRAINT [DF_Users_RegistrationDate] DEFAULT (getutcdate()) NOT NULL,
    [KYC_Approved]          BIT            CONSTRAINT [DF_Users_KYC_Approved] DEFAULT 0 NOT NULL,
    [CreateDate]            DATETIME       NULL,
    [CreateOrigin]          [sysname]      NULL,
    [CreateIPAddress]       VARCHAR (50)   NULL,
    [Source]                VARCHAR (MAX)  NULL,
    [LastModifyDate]        DATETIME2 (7)  NULL,
    [LastModifyOrigin]      [sysname]      NULL,
    [LastModifyOperatorId]  INT            NULL,
    [TermsOfServiceChecked] BIT            NULL,
    [MailVerfied]           BIT            NULL,
    [PhoneVerified]         BIT            NULL,
	[HttpRequestId]			BIGINT		   NULL,
	[HasAvatar] BIT NOT NULL DEFAULT 0
    CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED ([UserId] ASC), 
    [Rank] INT NOT NULL DEFAULT 0, 
    [DistanceFromTarget] SMALLINT NULL, 
    [SuccessRate] SMALLINT NULL, 
    [UserName] NVARCHAR(250) NOT NULL, 
    [SuccessSequence] INT NULL DEFAULT 0,   
    [PasswordResetToken] VARCHAR(255) NULL 
);
GO

CREATE UNIQUE INDEX [IX_Users_ApiConsumerId_Email_UserName] ON [dbo].[Users] ([ApiConsumerId], [Email], [UserName])
GO
CREATE UNIQUE INDEX [IX_Users_ApiConsumerId_Email] ON [dbo].[Users] ([ApiConsumerId], [Email])
GO