CREATE TABLE [dbo].[tblBlockedUsers] (
    [BlockGroupListID] DECIMAL (18) IDENTITY (1, 1) NOT NULL,
    [BlockedUserName]  DECIMAL (18) NOT NULL,
    [BlockedRequester] DECIMAL (18) NOT NULL
);


GO
CREATE TABLE [dbo].[tblContactLists] (
    [ContactListID]       DECIMAL (18) IDENTITY (1, 1) NOT NULL,
    [MainUserID]          DECIMAL (18) NOT NULL,
    [UserInContactListID] DECIMAL (18) NOT NULL
);


GO
CREATE TABLE [dbo].[tblCountry] (
    [CountryID]   SMALLINT      IDENTITY (1, 1) NOT NULL,
    [CountryName] NVARCHAR (50) COLLATE Arabic_CI_AS NOT NULL,
    [PreNumber]   NCHAR (3)     COLLATE Arabic_CI_AS NOT NULL
);


GO
CREATE TABLE [dbo].[tblGroupMembers] (
    [GroupMemberID]     DECIMAL (18) IDENTITY (1, 1) NOT NULL,
    [GroupID]           DECIMAL (18) NOT NULL,
    [UserID]            DECIMAL (18) NOT NULL,
    [IsThatMemberAdmin] BIT          NOT NULL
);


GO
CREATE TABLE [dbo].[tblGroups] (
    [GroupID]           DECIMAL (18)   IDENTITY (1, 1) NOT NULL,
    [GroupName]         NVARCHAR (100) COLLATE Arabic_CI_AS NOT NULL,
    [GroupCreatorID]    DECIMAL (18)   NOT NULL,
    [GroupCreationDate] DATETIME       NOT NULL
);


GO
CREATE TABLE [dbo].[tblMessagesPrivate] (
    [MessageID]       DECIMAL (18)   IDENTITY (1, 1) NOT NULL,
    [SenderID]        DECIMAL (18)   NOT NULL,
    [MessageDateTime] DATETIME       NOT NULL,
    [MessageBudy]     NVARCHAR (250) COLLATE Arabic_CI_AS NOT NULL,
    [ReceiverUserID]  DECIMAL (18)   NOT NULL
);


GO
CREATE TABLE [dbo].[tblMessageToGroups] (
    [MessageID]       DECIMAL (18)   IDENTITY (1, 1) NOT NULL,
    [SenderID]        DECIMAL (18)   NOT NULL,
    [MessageDateTime] DATETIME       NOT NULL,
    [MessageBudy]     NVARCHAR (250) COLLATE Arabic_CI_AS NOT NULL,
    [GroupID]         DECIMAL (18)   NOT NULL,
    [ReplyToUserID]   NCHAR (10)     COLLATE Arabic_CI_AS NULL
);


GO
CREATE TABLE [dbo].[tblUsers] (
    [IUserID]        DECIMAL (18)  IDENTITY (1, 1) NOT NULL,
    [IUserCountryID] SMALLINT      NOT NULL,
    [IUserName]      NVARCHAR (50) COLLATE Arabic_CI_AS NOT NULL,
    [IUserPhone]     NVARCHAR (50) COLLATE Arabic_CI_AS NOT NULL,
    [IsIUserBlocked] BIT           NOT NULL
);


GO
CREATE FUNCTION [dbo].[UdfCheckMobileAustralia]
(@AustraliaMobieNumber NVARCHAR (30))
RETURNS BIT
AS
BEGIN

DECLARE @str VARCHAR(MAX)
DECLARE @validchars VARCHAR(MAX)
DECLARE @MyRet BIT

IF LEN(@AustraliaMobieNumber) <> 15 
	BEGIN
		SET @MyRet = 0
	END
ELSE
BEGIN			
	SET @str = @AustraliaMobieNumber 
	SET @validchars = '[+][6][1][-][4][0-9][0-9][-][0-9][0-9][0-9][-][0-9][0-9][0-9]'
	DECLARE @idx INT
	SET @idx = PATINDEX('%'+ @validchars +'%',@str)
	IF @idx > 0 AND 
		(@idx = LEN(@str)-8
		OR PATINDEX(SUBSTRING(@str,@idx+9,1),'[0-9]')=0)
		BEGIN
			SET @MyRet = 1
		END
	ELSE 
		BEGIN
			SET @MyRet = 0
		END
END			
RETURN @MyRet	
END


GO
CREATE FUNCTION [dbo].[UdfCheckMobileCanada]
(@CanadaMobieNumber NVARCHAR (30))
RETURNS BIT
AS
BEGIN

DECLARE @str VARCHAR(MAX)
DECLARE @validchars VARCHAR(MAX)
DECLARE @MyRet BIT

IF LEN(@CanadaMobieNumber) <> 15 
	BEGIN
		SET @MyRet = 0
	END
ELSE
BEGIN			
	SET @str = @CanadaMobieNumber 
	SET @validchars = '[+][1][-][0-9][0-9][0-9][-][0-9][0-9][0-9][-][0-9][0-9][0-9][0-9]'
	DECLARE @idx INT
	SET @idx = PATINDEX('%'+ @validchars +'%',@str)
	IF @idx > 0 AND 
		(@idx = LEN(@str)-8
		OR PATINDEX(SUBSTRING(@str,@idx+9,1),'[0-9]')=0)
		BEGIN
			SET @MyRet = 1
		END
	ELSE 
		BEGIN
			SET @MyRet = 0
		END
END			
RETURN @MyRet	
END


GO
CREATE FUNCTION [dbo].[UdfCheckMobileEgypt]
(@EgyptMobieNumber NVARCHAR (30))
RETURNS BIT
AS
BEGIN

DECLARE @str VARCHAR(MAX)
DECLARE @validchars VARCHAR(MAX)
DECLARE @MyRet BIT

IF LEN(@EgyptMobieNumber) <> 16 
	BEGIN
		SET @MyRet = 0
	END
ELSE
BEGIN			
	SET @str = @EgyptMobieNumber 
	SET @validchars = '[+][2][0][-][1][0-9][-][0-9][0-9][0-9][0-9][-][0-9][0-9][0-9][0-9]'
	DECLARE @idx INT
	SET @idx = PATINDEX('%'+ @validchars +'%',@str)
	IF @idx > 0 AND 
		(@idx = LEN(@str)-8
		OR PATINDEX(SUBSTRING(@str,@idx+9,1),'[0-9]')=0)
		BEGIN
			SET @MyRet = 1
		END
	ELSE 
		BEGIN
			SET @MyRet = 0
		END
END			
RETURN @MyRet	
END


GO
CREATE FUNCTION [dbo].[UdfCheckMobileFrance]
(@FranceMobieNumber NVARCHAR (30))
RETURNS BIT
AS
BEGIN

DECLARE @str VARCHAR(MAX)
DECLARE @validchars VARCHAR(MAX)
DECLARE @MyRet BIT

IF LEN(@FranceMobieNumber) <> 17 
	BEGIN
		SET @MyRet = 0
	END
ELSE
BEGIN			
	SET @str = @FranceMobieNumber 
	SET @validchars = '[+][3][3][-][0-9][-][0-9][0-9][-][0-9][0-9][-][0-9][0-9][-][0-9][0-9]'
	DECLARE @idx INT
	SET @idx = PATINDEX('%'+ @validchars +'%',@str)
	IF @idx > 0 AND 
		(@idx = LEN(@str)-8
		OR PATINDEX(SUBSTRING(@str,@idx+9,1),'[0-9]')=0)
		BEGIN
			SET @MyRet = 1
		END
	ELSE 
		BEGIN
			SET @MyRet = 0
		END
END			
RETURN @MyRet	
END


GO
CREATE FUNCTION [dbo].[UdfCheckMobileIran]
(@IranMobieNumber NVARCHAR (30))
RETURNS BIT
AS
BEGIN

DECLARE @str VARCHAR(MAX)
DECLARE @validchars VARCHAR(MAX)
DECLARE @MyRet BIT

IF LEN(@IranMobieNumber) <> 16 
	BEGIN
		SET @MyRet = 0
	END
ELSE
BEGIN			
	SET @str = @IranMobieNumber --'+98-912-125-3620'
	SET @validchars = '[+][9][8][-][9][0-9][0-9][-][0-9][0-9][0-9][-][0-9][0-9][0-9][0-9]'
	DECLARE @idx INT
	SET @idx = PATINDEX('%'+ @validchars +'%',@str)
	IF @idx > 0 AND 
		(@idx = LEN(@str)-8
		OR PATINDEX(SUBSTRING(@str,@idx+9,1),'[0-9]')=0)
		BEGIN
			--SET @str=SUBSTRING(@str ,PATINDEX('%'+ @validchars +'%',@str), 16)
			SET @MyRet = 1
		END
	ELSE 
		BEGIN
		--SET @str = ''
			--SELECT @str
			SET @MyRet = 0
		END
END			
RETURN @MyRet	
END


GO
CREATE FUNCTION [dbo].[UdfCheckNumber]
(@CuntryID SMALLINT, @PhoneNumber NVARCHAR (20))
RETURNS BIT
AS
BEGIN
DECLARE @NumberState bit

IF @CuntryID = 1 -- Australia
	BEGIN
		SET @NumberState = (SELECT dbo.UdfCheckMobileAustralia(@PhoneNumber))
	END
IF @CuntryID = 2 --Canada
	BEGIN
		SET @NumberState = (SELECT dbo.UdfCheckMobileCanada(@PhoneNumber))
	END
IF @CuntryID = 3 --Egypt
	BEGIN
		SET @NumberState = (SELECT dbo.UdfCheckMobileEgypt(@PhoneNumber))
	END
IF @CuntryID = 4 --France
	BEGIN
		SET @NumberState = (SELECT dbo.UdfCheckMobileFrance(@PhoneNumber))
	END
IF @CuntryID = 5 --Iran
	BEGIN
		SET @NumberState = (SELECT dbo.UdfCheckMobileIran(@PhoneNumber))
	END
RETURN @NumberState
END


GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_tblBlockedUsers]
    ON [dbo].[tblBlockedUsers]([BlockedRequester] ASC, [BlockedUserName] ASC);


GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_tblContactLists]
    ON [dbo].[tblContactLists]([MainUserID] ASC, [UserInContactListID] ASC);


GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_tblGroupMembers]
    ON [dbo].[tblGroupMembers]([GroupID] ASC, [UserID] ASC);


GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_tblGroups]
    ON [dbo].[tblGroups]([GroupName] ASC);


GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_tblUsers]
    ON [dbo].[tblUsers]([IUserName] ASC);


GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_tblUsers_1]
    ON [dbo].[tblUsers]([IUserPhone] ASC);


GO
ALTER TABLE [dbo].[tblBlockedUsers]
    ADD CONSTRAINT [PK_tblBlockedUsers] PRIMARY KEY CLUSTERED ([BlockGroupListID] ASC);


GO
ALTER TABLE [dbo].[tblContactLists]
    ADD CONSTRAINT [PK_tblContactLists] PRIMARY KEY CLUSTERED ([ContactListID] ASC);


GO
ALTER TABLE [dbo].[tblCountry]
    ADD CONSTRAINT [PK_tblCountry] PRIMARY KEY CLUSTERED ([CountryID] ASC);


GO
ALTER TABLE [dbo].[tblGroupMembers]
    ADD CONSTRAINT [PK_tblGroupMembers] PRIMARY KEY CLUSTERED ([GroupMemberID] ASC);


GO
ALTER TABLE [dbo].[tblGroups]
    ADD CONSTRAINT [PK_tblGroups] PRIMARY KEY CLUSTERED ([GroupID] ASC);


GO
ALTER TABLE [dbo].[tblMessagesPrivate]
    ADD CONSTRAINT [PK_tblMessages] PRIMARY KEY CLUSTERED ([MessageID] ASC);


GO
ALTER TABLE [dbo].[tblMessageToGroups]
    ADD CONSTRAINT [PK_tblMessageToGroups] PRIMARY KEY CLUSTERED ([MessageID] ASC);


GO
ALTER TABLE [dbo].[tblUsers]
    ADD CONSTRAINT [PK_tblUsers] PRIMARY KEY CLUSTERED ([IUserID] ASC);


GO
ALTER TABLE [dbo].[tblBlockedUsers]
    ADD CONSTRAINT [FK_tblBlockedUsers_tblUsers] FOREIGN KEY ([BlockedUserName]) REFERENCES [dbo].[tblUsers] ([IUserID]);


GO
ALTER TABLE [dbo].[tblBlockedUsers]
    ADD CONSTRAINT [FK_tblBlockedUsers_tblUsers1] FOREIGN KEY ([BlockedRequester]) REFERENCES [dbo].[tblUsers] ([IUserID]);


GO
ALTER TABLE [dbo].[tblContactLists]
    ADD CONSTRAINT [FK_tblContactLists_tblUsers] FOREIGN KEY ([MainUserID]) REFERENCES [dbo].[tblUsers] ([IUserID]);


GO
ALTER TABLE [dbo].[tblContactLists]
    ADD CONSTRAINT [FK_tblContactLists_tblUsers1] FOREIGN KEY ([UserInContactListID]) REFERENCES [dbo].[tblUsers] ([IUserID]);


GO
ALTER TABLE [dbo].[tblGroupMembers]
    ADD CONSTRAINT [FK_tblGroupMembers_tblGroups] FOREIGN KEY ([GroupID]) REFERENCES [dbo].[tblGroups] ([GroupID]);


GO
ALTER TABLE [dbo].[tblGroupMembers]
    ADD CONSTRAINT [FK_tblGroupMembers_tblUsers] FOREIGN KEY ([UserID]) REFERENCES [dbo].[tblUsers] ([IUserID]);


GO
ALTER TABLE [dbo].[tblGroups]
    ADD CONSTRAINT [FK_tblGroups_tblUsers] FOREIGN KEY ([GroupCreatorID]) REFERENCES [dbo].[tblUsers] ([IUserID]);


GO
ALTER TABLE [dbo].[tblMessagesPrivate]
    ADD CONSTRAINT [FK_tblMessages_tblUsers1] FOREIGN KEY ([ReceiverUserID]) REFERENCES [dbo].[tblUsers] ([IUserID]);


GO
ALTER TABLE [dbo].[tblMessagesPrivate]
    ADD CONSTRAINT [FK_tblMessagesPrivate_tblUsers] FOREIGN KEY ([SenderID]) REFERENCES [dbo].[tblUsers] ([IUserID]);


GO
ALTER TABLE [dbo].[tblMessageToGroups]
    ADD CONSTRAINT [FK_tblMessageToGroups_tblGroups] FOREIGN KEY ([GroupID]) REFERENCES [dbo].[tblGroups] ([GroupID]);


GO
ALTER TABLE [dbo].[tblMessageToGroups]
    ADD CONSTRAINT [FK_tblMessageToGroups_tblUsers] FOREIGN KEY ([SenderID]) REFERENCES [dbo].[tblUsers] ([IUserID]);


GO
ALTER TABLE [dbo].[tblUsers]
    ADD CONSTRAINT [FK_tblUsers_tblCountry] FOREIGN KEY ([IUserCountryID]) REFERENCES [dbo].[tblCountry] ([CountryID]);


GO
CREATE TRIGGER [dbo].[TrNoMoreThan100Member]
    ON [dbo].[tblGroupMembers]
    AFTER INSERT
    AS BEGIN
DECLARE @MemberCount INT
SET @MemberCount = (SELECT  COUNT(UserID) AS CountOfMembers  FROM dbo.tblGroupMembers GROUP BY GroupID  HAVING (GroupID=(SELECT GroupID FROM Inserted)))
IF @MemberCount > 100
	BEGIN
		PRINT 'تعداد اعضای مجاز در هر گروه حداکثر 100 نفر می باشند'
		ROLLBACK TRANSACTION
	END 
END



GO
CREATE TRIGGER [dbo].[TrAddAdminMember]
    ON [dbo].[tblGroups]
    AFTER INSERT
    AS BEGIN
DECLARE @GroupCreatorID DECIMAL(18,0)
DECLARE @GroupID DECIMAL(18,0)

SET @GroupCreatorID = (SELECT GroupCreatorID FROM Inserted)
SET @GroupID = (SELECT GroupID FROM Inserted)

INSERT INTO dbo.tblGroupMembers
        ( GroupID ,
          UserID ,
          IsThatMemberAdmin
        )
VALUES  ( @GroupID , -- GroupID - decimal
          @GroupCreatorID , -- UserID - decimal
          1  -- IsThatMemberAdmin - bit
        )
END



GO
CREATE TRIGGER [dbo].[TrCheckSenderContactList]
    ON [dbo].[tblMessagesPrivate]
    AFTER INSERT
    AS BEGIN
DECLARE @SendeUserID DECIMAL(18,0)
DECLARE @ReceiverUserID DECIMAL(18,0)

SET @SendeUserID = (SELECT SenderID FROM INSERTED)
SET @ReceiverUserID = (SELECT ReceiverUserID FROM INSERTED)

IF NOT EXISTS(SELECT ContactListID FROM dbo.tblContactLists cl WHERE cl.MainUserID = @SendeUserID AND cl.UserInContactListID = @ReceiverUserID)
	BEGIN
		PRINT N'کاربر مورد نظر در لیست مخاطبان شما موجود نمی باشد'
		ROLLBACK TRANSACTION
	END
END



GO
CREATE TRIGGER [dbo].[TrCheckPhoneNumber]
    ON [dbo].[tblUsers]
    AFTER INSERT
    AS BEGIN
DECLARE @IUserCountryID SMALLINT
DECLARE @MobilePhoneNumber NVARCHAR(20)
DECLARE @MobbleNumberState BIT

SET @IUserCountryID = (SELECT IUserCountryID FROM Inserted)
SET @MobilePhoneNumber = (SELECT IUserPhone FROM Inserted)
SET @MobbleNumberState = (SELECT dbo.UdfCheckNumber(@IUserCountryID , @MobilePhoneNumber))

IF @MobbleNumberState = 0 
	BEGIN
		PRINT 'قالب شماره تلفن وارد شده صحیح نمی باشد'
		ROLLBACK TRANSACTION
	END 
END


