-- ==================================================================================================================================
-- CHECK CONSTRAINTS
-- ================================================================================================================================== 
ALTER TABLE [dbo].[T_FELHASZNALOBELEPES] DROP CONSTRAINT IF EXISTS [CK_C_GUID]
GO

ALTER TABLE [dbo].[T_FELHASZNALOBELEPES] DROP CONSTRAINT IF EXISTS [CK_SoNullValidation]
GO

ALTER TABLE [dbo].[T_FELHASZNALOBELEPES] DROP CONSTRAINT IF EXISTS [CK_FELHASZNALOBELEPES_GuidValidation]
GO

ALTER TABLE [dbo].[T_FELHASZNALOBELEPES] DROP CONSTRAINT IF EXISTS [CK_FELHASZNALOBELEPES_PasswordValidation]
GO

IF OBJECT_ID('CK_FelhasznaloBelepes_SoNullValidation', 'C') IS NULL BEGIN
  ALTER TABLE [dbo].[T_FELHASZNALOBELEPES]
    WITH CHECK ADD CONSTRAINT CK_FelhasznaloBelepes_SoNullValidation
    CHECK (NOT([C_JELSZO] IS NOT NULL AND [C_SO] IS NULL AND [C_NEPTUNNAPLOJELSZO] IS NULL))
END
GO

DROP FUNCTION IF EXISTS dbo.fnGuidValidation
GO

CREATE FUNCTION dbo.fnGuidValidation (
     @loginName nvarchar(36)
    ,@intezmenyId int
    ,@isTorolt char(1)
    ,@GUID nvarchar(72)
) RETURNS int
BEGIN
  RETURN (
    SELECT COUNT(DISTINCT C_GUID)
    FROM [dbo].[T_FELHASZNALOBELEPES]
    WHERE C_BEJELENTKEZESINEV = @loginName
      AND C_INTEZMENYID = @intezmenyId
      AND TOROLT='F'
  )
END;
GO

IF OBJECT_ID('CK_FelhasznaloBelepes_GuidValidation', 'C') IS NULL BEGIN
  ALTER TABLE [dbo].[T_FELHASZNALOBELEPES]
    ADD CONSTRAINT CK_FelhasznaloBelepes_GuidValidation
    CHECK (dbo.fnGuidValidation(C_BEJELENTKEZESINEV, C_INTEZMENYID, TOROLT, C_GUID) <= 1)
END
GO


IF OBJECT_ID('CK_FelhasznaloBelepes_PasswordValidation', 'C') IS NULL BEGIN
  ALTER TABLE [dbo].[T_FELHASZNALOBELEPES]
    WITH CHECK ADD CONSTRAINT CK_FelhasznaloBelepes_PasswordValidation
    CHECK (NOT(
        C_JELSZO IS NOT NULL AND
        LEN(C_JELSZO)!=28 AND
        C_JELSZO NOT LIKE '%='
      ))
END
GO