CREATE FUNCTION validare_InsertSolved (@FK_username VARCHAR(50), @FK_id_problema VARCHAR(50))
RETURNS INT AS
BEGIN
IF @FK_username IS NULL OR @FK_id_problema IS NULL OR (EXISTS(SELECT * FROM dbo.SOLVED WHERE FK_username = @FK_username) AND EXISTS(SELECT * FROM dbo.SOLVED WHERE FK_id_problema = @FK_id_problema)) OR NOT EXISTS(SELECT * FROM dbo.PROBLEMS WHERE id_problema = @FK_id_problema) OR NOT EXISTS(SELECT * FROM dbo.STUDENTS WHERE username = @FK_username)
        RETURN 0
RETURN 1
END

GO
CREATE PROCEDURE InsertSolved @username VARCHAR(50), @id_problema VARCHAR(50)
AS
 IF dbo.validare_InsertSolved(@username,@id_problema) = 0
  BEGIN
        print 'Cheia dubla a fost introdusa o data, username sau problema nu exista'
  END
  ELSE
  BEGIN
        INSERT INTO dbo.SOLVED
        VALUES(@username,@id_problema)
  END
GO


CREATE FUNCTION validare_InsertProblema (@id_problema VARCHAR(50), @site VARCHAR(MAX), @dificultate INT)
RETURNS INT AS
BEGIN
IF @id_problema IS NULL OR @site IS NULL OR @dificultate < 0 OR EXISTS(SELECT * FROM dbo.PROBLEMS WHERE id_problema = @id_problema)
    RETURN 0
RETURN 1
END

GO
CREATE PROCEDURE InsertProblema @id_problema VARCHAR(50), @site VARCHAR(MAX), @dificultate INT
AS
 IF dbo.validare_InsertProblema(@id_problema,@site,@dificultate) = 0
  BEGIN
        print 'Problema exista in tabel, unele campuri sunt goare, sau dificultatea e negativa'
  END
  ELSE
  BEGIN
        INSERT INTO dbo.PROBLEMS
        VALUES(@id_problema,@site,@dificultate)
  END
GO



CREATE FUNCTION validare_InsertStudent (@username VARCHAR(50), @password VARCHAR(50), @fullname VARCHAR(MAX))
RETURNS INT AS
BEGIN
IF @username IS NULL OR @password IS NULL OR @fullname IS NULL OR EXISTS(SELECT * FROM dbo.STUDENTS WHERE username = @username)
    RETURN 0
RETURN 1
END

GO
CREATE PROCEDURE InsertStudent @username VARCHAR(50), @password VARCHAR(50), @fullname VARCHAR(MAX)

AS
 IF dbo.validare_InsertStudent(@username,@password,@fullname) = 0
  BEGIN
        print 'Username-ul exista sau ceva e null'
  END
  ELSE
  BEGIN
        INSERT INTO dbo.STUDENTS (username, password, fullname)
        VALUES(@username,@password,@fullname)
  END

EXEC InsertStudent 'marean', '1534fd', 'MARIAN marian'
SELECT * FROM STUDENTS

EXEC InsertStudent 'andrei', '1534fd', 'andrei 1235'
SELECT * FROM STUDENTS

GO
CREATE VIEW [Studenti_Probleme_Rezolvate] AS
SELECT dbo.STUDENTS.username, dbo.STUDENTS.fullname, dbo.PROBLEMS.id_problema
FROM dbo.STUDENTS
JOIN dbo.SOLVED ON (dbo.STUDENTS.username = dbo.SOLVED.FK_username)
JOIN dbo.PROBLEMS ON (dbo.SOLVED.FK_id_problema = dbo.PROBLEMS.id_problema); /*ce echipament are fiecare soldat */

GO
SELECT * FROM [Studenti_Probleme_Rezolvate]


GO
CREATE TRIGGER triggerDelete ON dbo.STUDENTS
FOR DELETE
AS
BEGIN
    SET NOCOUNT ON;
    print 'S-a sters din tabelul Studenti la data:'
    print GETDATE ()
END

DELETE FROM STUDENTS WHERE username = 'marean'



GO
CREATE TRIGGER triggerInsert ON dbo.STUDENTS
FOR INSERT
AS
BEGIN
    SET NOCOUNT ON;
    print 'S-a inserat in tabelul STUDENTS la data:'
    print GETDATE ()
END



EXEC InsertProblema 'permutari', 'infoarena', 5

EXEC InsertSolved 'andrei', 'permutari'

SELECT * FROM SOLVED