CREATE TABLE [dbo].[tblClientInfo1](
    [ClId] [INT]  IDENTITY(1,1),
    [Clname] [VARCHAR](130),
    [ClGender] [nvarchar](6) NOT NULL,
    [ClBirthday] [DATE],
    PRIMARY KEY(ClId)

);


GO
INSERT INTO [dbo].[tblClientInfo1] ([Clname],[ClGender],[ClBirthday]) VALUES ('Hermy Herny','m','1997-12-12');
INSERT INTO [dbo].[tblClientInfo1] ([Clname],[ClGender],[ClBirthday]) VALUES ('Germy Gerny','m','1996-12-12');
INSERT INTO [dbo].[tblClientInfo1] ([Clname],[ClGender],[ClBirthday]) VALUES ('Lermo Lerna','f','1999-11-12');

INSERT INTO [dbo].[tblClientInfo1] ([Clname],[ClGender],[ClBirthday]) VALUES ('Bermy Berni','f','1999-09-12');
INSERT INTO [dbo].[tblClientInfo1] ([Clname],[ClGender],[ClBirthday]) VALUES ('Terho Terne','m','1976-12-25');
INSERT INTO [dbo].[tblClientInfo1] ([Clname],[ClGender],[ClBirthday]) VALUES ('Derby Derve','f','1945-04-19');
INSERT INTO [dbo].[tblClientInfo1] ([Clname],[ClGender],[ClBirthday]) VALUES ('Serve Serne','f','1988-05-12');

-------vocab section------------------

CREATE TABLE [dbo].[VOCTransactions] (
    [VTId] [INT],
    [VTname] [nvarchar] (30)
);
GO
INSERT INTO [dbo].[VOCTransactions] ([VTId],[VTname]) VALUES (1,'Cash pay');
INSERT INTO [dbo].[VOCTransactions] ([VTId],[VTname]) VALUES (2,'Cash withdrawal');
INSERT INTO [dbo].[VOCTransactions] ([VTId],[VTname]) VALUES (3,'Replenishment of cash');

CREATE TABLE [dbo].[VOCGender] (
    [alGen] [nvarchar] (5),
    [gender] [nvarchar] (50)
);
GO
INSERT INTO [dbo].[VOCGender] ([alGen],[gender]) VALUES ('f','female');
INSERT INTO [dbo].[VOCGender] ([alGen],[gender]) VALUES ('m','male');

CREATE TABLE [dbo].[VOCCurrency] (
    [CurId] [INT],
    [CurAL] [nvarchar] (30)
);
GO
INSERT INTO [dbo].[VOCCurrency] ([CurId],[CurAL]) VALUES (1, 'TG');
INSERT INTO [dbo].[VOCCurrency] ([CurId],[CurAL]) VALUES (2, 'RUB');
INSERT INTO [dbo].[VOCCurrency] ([CurId],[CurAL]) VALUES (3, 'USD');
INSERT INTO [dbo].[VOCCurrency] ([CurId],[CurAL]) VALUES (4, 'EURO');


---------table Accounts----------------


CREATE TABLE [dbo].[tblClientAccounts] (
    [ClId] [INT],
    [AcId] [nvarchar](50),
    [AcStatus] [int],
    [AcAmount] [money],
    [MoneyType] [int],
    [AcType] [nvarchar](50),

 );
GO
INSERT INTO  [dbo].[tblClientAccounts]([ClId],[AcId],[AcStatus],[AcAmount],[MoneyType],[AcType]) VALUES (2,'971212021',1,550.45,1,'dep');
INSERT INTO  [dbo].[tblClientAccounts]([ClId],[AcId],[AcStatus],[AcAmount],[MoneyType],[AcType]) VALUES (2,'971212022',1,1550.45,1,'cred');
INSERT INTO  [dbo].[tblClientAccounts]([ClId],[AcId],[AcStatus],[AcAmount],[MoneyType],[AcType]) VALUES (5,'761225051',1,111550.78,1,'pen');
INSERT INTO  [dbo].[tblClientAccounts]([ClId],[AcId],[AcStatus],[AcAmount],[MoneyType],[AcType]) VALUES (6,'450419061',1,112550.78,1,'pen');
INSERT INTO  [dbo].[tblClientAccounts]([ClId],[AcId],[AcStatus],[AcAmount],[MoneyType],[AcType]) VALUES (7,'880512071',1,56572.78,1,'pen');
INSERT INTO  [dbo].[tblClientAccounts]([ClId],[AcId],[AcStatus],[AcAmount],[MoneyType],[AcType]) VALUES (5,'761225052',1,11750.78,1,'dep');
INSERT INTO  [dbo].[tblClientAccounts]([ClId],[AcId],[AcStatus],[AcAmount],[MoneyType],[AcType]) VALUES (1,'971212011',1,114545.99,1,'cred');
INSERT INTO  [dbo].[tblClientAccounts]([ClId],[AcId],[AcStatus],[AcAmount],[MoneyType],[AcType]) VALUES (3,'991112031',1,117821.78,1,'dep');
INSERT INTO  [dbo].[tblClientAccounts]([ClId],[AcId],[AcStatus],[AcAmount],[MoneyType],[AcType]) VALUES (4,'990912041',1,245550.78,1,'dep');
INSERT INTO  [dbo].[tblClientAccounts]([ClId],[AcId],[AcStatus],[AcAmount],[MoneyType],[AcType]) VALUES (5,'761225053',0,785150.78,1,'cred');
INSERT INTO  [dbo].[tblClientAccounts]([ClId],[AcId],[AcStatus],[AcAmount],[MoneyType],[AcType]) VALUES (4,'990912042',0,0.78,1,'cred');

-------table cards---------------

CREATE TABLE [dbo].[tblClCards](
    [CardId] [INT],
    [ClId] [INT],
    [AcId] [INT]

 );
GO

INSERT INTO [dbo].[tblClCards] ([CardId],[ClId],[AcId]) VALUES (45451414,2,971212022);
INSERT INTO [dbo].[tblClCards] ([CardId],[ClId],[AcId]) VALUES (45451422,1,971212011);
INSERT INTO [dbo].[tblClCards] ([CardId],[ClId],[AcId]) VALUES (45601445,5,761225051);
INSERT INTO [dbo].[tblClCards] ([CardId],[ClId],[AcId]) VALUES (45601466,6,450419061);
----------------------------

--To save transactions log--

CREATE TABLE [dbo].[STtblTransactions] (
    [TrId] [INT] IDENTITY(1,1),
    [TrType] [int],
    [AcIdFrom] [INT],
    [AcIdTo] [INT],
    [TrTime] [DATETIME],
    [TrAmount] [money]
);

---transactions table for update-----

CREATE TABLE [dbo].[TRtblTransactions](
    [TrId] [INT],
    [TrTime] [DATETIME],
    [TrType] [int],
    [TrAmount] [money],
    [AcStatusFr] [int],
    [AcStatusTo] [int],
    [AcIdFr] [INT],
    [AcIdTo] [INT],
    [MoneyTypeFr] [int],
    [MoneyTypeTo] [int],
    [AcAmountFR] [money],
    [AcAmountTo] [money],
    [TrStatus] [int]

);
---------------------
create table [dbo].[sessions](
    [SesId] int,
    [TrId] int
);

INSERT INTO [dbo].[sessions] VALUES (1,1);

---index section----------------
CREATE INDEX idx_tr_time ON [dbo].[TRtblTransactions]([TrTime]);
CREATE INDEX idx_tr_amount ON [dbo].[TRtblTransactions]([TrAmount]);
CREATE INDEX idx_tr_type ON [dbo].[TRtblTransactions]([TrType]);
CREATE INDEX idx_cl_acid ON [dbo].[tblClientAccounts]([AcId]);
--CREATE INDEX idx_tr_time ON [dbo].[tblTransactions](TrTime);
--CREATE INDEX idx_ac_amount ON [dbo].[tblClientAccounts](AcAmount);
--CREATE INDEX idx_card_id ON [dbo].[tblClCards](CardId);
--CREATE INDEX idx_ac_status ON [dbo].[tblClientAccounts](AcStatus);
--CREATE INDEX idx_name ON [dbo].[tblClientInfo1](ClLastname,ClFirstname);