SQLtestSholdova

create table Users
(
	Id bigint primary key identity(1,1)
	,Name nvarchar(200)
	,UserName Varchar(30)
	,[Password] Varchar(30)
	,CashAccountId Bigint
	,ClientId Bigint
)

insert into users values ('user3', 'username3', '12345678', 10000052, 12), ('user4',' username4', '21234567', 10000051, 13)

select *
from users

create table Clients
(
	Id bigint primary key identity(1,1)
	,Name Nvarchar(200)
	,[Type] Bit
	,IdentificationNumber Varchar(36)
	,[Address] Varchar(100)
	,Tel Varchar(30)
	,Mob Varchar(30)
	,Email Varchar(30)
	,Web Varchar(100)
)

insert into clients values ('name1', 1, 1, 'treta makedonska brigada br.1','022000001','071000001','name1@yahoo.com', 'www.name1.com'),
('name2', 1, 2, 'treta makedonska brigada br.2','022000002','071000002','name2@yahoo.com', 'www.name2.com'),
('name3', 0, 3, 'treta makedonska brigada br.3','022000003','071000003','name3@yahoo.com', 'www.name3.com'),
('name4', 0, 4, 'treta makedonska brigada br.4','022000004','071000004','name4@yahoo.com', 'www.name4.com')

Select * from clients

create table Currencies
(
	Currency Varchar(3) not null primary key
	,Name Varchar(30)
	,Symbol Nvarchar(5)
	,[Image] Varbinary(8000)
)


insert into Currencies values
('65','Denari','MKD', null),
('1','Euros','Euro', null),
('1.2','Dolars','$', null)

Select * from Currencies

update Currencies
set currency='61'
where currency='65'

create table AccountClasses
(
	Id Bigint primary key identity(1,1)
	,Name Varchar(30)
	,[Description] Nvarchar(100)
	,[Type] Int unique check([Type] in (1,2,3,4))
	,IsCashAccount as case when [Type] = 1 then 1 else 0 end
	,IsCurrentAccount as case when [Type] = 2 then 1 else 0 end
	,IsDepositAccount as case when [Type] = 3 then 1 else 0 end
	,IsCreditAccount as case when [Type] = 4 then 1 else 0 end
)

insert into AccountClasses values
('cash','cash account',1),
('Current','Current account',2),
('Deposit','Deposit account',3),
('Credit','Credit account',4)

select *
from AccountClasses

create table AccountTypes
(
	Id Bigint primary key identity(1,1)
	,ClassId bigint foreign key references AccountClasses(Id)
	,Name Varchar(30)
	,[Description] Nvarchar(100)
)


insert into AccountTypes values
(1,'cash account','cashe'),
(2,'Current account','Current'),
(3,'Deposit account','Deposit'),
(4,'Credit account','Credit')

select *
from AccountClasses AC Join AccountTypes AT on AC.Id=AT.Id

create table Accounts
(
	Id Bigint primary key identity(1,1)
	,TypeId bigint foreign key references AccountTypes(Id)
	,AccountNumber Varchar(30) unique
	,Currency varchar(3) foreign key references Currencies(Currency)
	,ClientId bigint foreign key references Clients(Id)
	,[Status] Varchar(1) check ([Status] in ('A','O','C'))
	,Blocked bit
	,Balance Decimal(19,2)
	,OverdraftLimit Decimal(19,2)
	,AmountOnHold Decimal(19,2)
	,AvailableBalance as Balance+OverdraftLimit-AmountOnHold
)


create table TransactionTypes
(
	Id Bigint primary key identity(1,1)
	,Name Varchar(30)
	,[Description] Nvarchar(100)
	,AccountClassDebit bigint foreign key references AccountClasses(Id)
	,AccountClassCredit bigint foreign key references AccountClasses(Id)
	,ReflectsAmountOnHold bit default 0
	,HasFee bit
)




create table Transactions
(
	Id Bigint primary key identity(1,1)
	,TransactionTypeId bigint foreign key references TransactionTypes(Id)
	,UserId bigint foreign key references Users(Id)
	,EntryDate Date default getdate()
	,[Date] Date
	,Currency varchar(3) foreign key references Currencies(Currency)
	,AccountIdDebit Bigint foreign key references Accounts(Id)
	,AccountIdCredit Bigint foreign key references Accounts(Id)
	,Fee Decimal(19,2)
	,Amount Decimal(19,2)
	,[Description] Nvarchar(100)
)

go

create table PostingEntries
(
	Id Bigint primary key identity(1,1)
	,TransactionId bigint foreign key references Transactions(Id)
	,AccountId bigint foreign key references Accounts(Id)
	,DebitCredit SmallInt check (DebitCredit in (-1,0,1))
	,ContraAccountId bigint foreign key references Accounts(Id)
	,Currency varchar(3) foreign key references Currencies(Currency)
	,Amount Decimal(19,2)
	,StatementNo int
)