create table users(user_id int primary key identity(0,1),isim nvarchar(20),soyisim nvarchar(20),email nvarchar(50),sifre nvarchar(20),kayit_tarihi nvarchar(50) DEFAULT convert(varchar,getdate(),113) ,rol_id int references Rol(rol_id) DEFAULT 2,bolum_id int references Bolum(bolum_id) DEFAULT 0,kisi_aktiflik int DEFAULT 0)

create table proje(proje_id int primary key identity(1,1),proje_adi text,proje_baslangic nvarchar(50) DEFAULT getdate(),proje_bitis nvarchar(50),bolum_id int references Bolum(bolum_id),proje_durum int DEFAULT 0)

create table gorev(gorev_id int primary key identity(1,1),proje_id int references proje(proje_id),gorev text,bolum_id int references Bolum(bolum_id),gorev_durum int DEFAULT 0)

create table gorev_ata(atama_id int identity(1,1) primary key,user_id int references users(user_id),gorev_id int references gorev(gorev_id),bolum_id int references Bolum(bolum_id),gorev_durum int default 0)

create table Rol(rol_id int primary key identity(0,1),rol nvarchar(20))



create table Bolum(bolum_id int primary key identity(0,1),bolum_adi nvarchar(20))

create proc user_register
@isim nvarchar(20),@soyisim nvarchar(20),@email nvarchar(50),@sifre nvarchar(20),@result int=1 output
as
begin
DECLARE @number_1 nvarchar(50),@kullaniciemail nvarchar(50)
select @kullaniciemail=email from users where email=@email
if @kullaniciemail=@email
begin
set @result=1
end
else
begin
insert into users(isim,soyisim,email,sifre) values (@isim,@soyisim,@email ,@sifre)
set @result=2
end
end


create procedure user_login @email nvarchar(50),@sifre nvarchar(50),@sayi int OUTPUT,@onay int OUTPUT,@roleid int OUTPUT,@bolumsid int OUTPUT,@kisiid int OUTPUT
as
begin
declare @Emailk nvarchar(50)=' ',@Sifrek nvarchar(50)=' '
select @Emailk=email,@Sifrek=sifre from users where email=@email and sifre=@sifre
select @onay=kisi_aktiflik from users where email=@email and sifre=@sifre
if @Emailk=@email and @Sifrek=@sifre and @onay=1
begin
set @sayi=3
select @roleid=rol_id from users where email=@email and sifre=@sifre
select @bolumsid=bolum_id from users where email=@email and sifre=@sifre
select @kisiid=user_id from users where email=@email and sifre=@sifre
end
else if @Emailk=@email and @Sifrek=@sifre and @onay=0
begin
set @sayi=2
end
else
begin
set @sayi=1
end
end