use FUBER
go

alter function dbo.getChainID (@username nvarchar(50))
returns int
as
begin
	declare @result int
	select top 1 @result = c.idC
	from Chaining c
	where c.username=@username
	return @result
end

alter proc dbo.addDriver @username nvarchar(50), @password nvarchar(50), @name nvarchar(50), @email nvarchar(50), @phoneNo nvarchar(20), @carType int
as
	set nocount on
	begin tran
	begin try
		insert into Login values (@username,@password)
		insert into Chaining(username) values (@username)
		declare @chainingID int
		set @chainingID = dbo.getChainID(@username)
		insert into Driver(idD,name,email,phoneNo,carType) values (@chainingID,@name,@email,@phoneNo,@carType)
	commit tran
	end try
	begin catch
		rollback tran
		raiserror('Driver already exists',16,1)
	end catch
go

alter proc dbo.addClient @username nvarchar(50), @password nvarchar(50), @name nvarchar(50), @email nvarchar(50), @phoneNo nvarchar(20), @isPro bit
as
	set nocount on
	begin tran
	begin try
		insert into Login values (@username,@password)
		insert into Chaining(username) values (@username)
		declare @chainingID int
		set @chainingID = dbo.getChainID(@username)
		insert into Client(idC,name,email,phoneNo,isPro) values (@chainingID,@name,@email,@phoneNo,@isPro)
		commit tran
	end try
	begin catch
		rollback tran
		raiserror('Client already exists',16,1)
	end catch
go


create function dbo.loginUser (@username nvarchar(50), @password nvarchar(50))
returns int
as
begin
	if (exists(select * from Login l where l.username=@username and l.password=@password))
		return 2
	if (exists(select * from Login l where l.username=@username))
		return 1
	return 0
end


alter function dbo.getDriver (@username nvarchar(50))
returns table
as
	return select d.name, d.email, d.phoneNo, d.carType
	from Chaining c inner join Driver d on c.idC=d.idD
	where c.username=@username
go

create function dbo.getDriverWithName (@name nvarchar(50))
returns table
as
	return select d.email, d.phoneNo, d.carType
	from Driver d
	where d.name=@name
go
create function dbo.getClient (@username nvarchar(50))
returns table
as
	return select cl.name, cl.email, cl.phoneNo, cl.isPro
	from Chaining ch inner join Client cl on ch.idC=cl.idC
	where ch.username=@username

alter function dbo.isDriver (@username nvarchar(50))
returns int
as
begin
	if (exists(select d.idD from Chaining c inner join Driver d on c.idC=d.idD where c.username=@username))
		return 1
	return 0
end

create function dbo.isClient (@username nvarchar(50))
returns int
as
begin
	if (exists(select cl.idC from Chaining c inner join Client cl on c.idC=cl.idC where c.username=@username))
		return 1
	return 0
end

drop function dbo.loginDriver

exec dbo.addDriver 'username','password','name',1,'1234567890'
exec dbo.addDriver 'usernam1e','password','name',1,'1234567890'
exec dbo.addClient 'username','password','name','tudor@myemail.com','1345678',0

print(dbo.loginDriver('username','password'))
drop procedure dbo.addUser
delete from Login
delete from Chaining
delete from Driver
delete from Client
delete from
select * from Login
select * from Chaining
select * from Driver
select * from Client

print(dbo.getChainID('username'))
print(dbo.isDriver('username'))
print(dbo.isDriver('Pro5'))
print(dbo.isDriver('tudor'))
print(dbo.isDriver('balastudor'))
print(dbo.isClientPart('Pro2'))