-- Kolo Bank: Tables --

USE KoloBank1;
GO

CREATE SCHEMA HumanResources;
GO
CREATE SCHEMA Accounts;
GO
CREATE SCHEMA Accounting;
GO

-- ================================================================================
-- Table:	Management.Locations
-- Description: A location is a branch where the banks operations can be performed
-- ================================================================================
CREATE TABLE Management.Locations
(
    LocationCode nvarchar(10) UNIQUE NOT NULL,
    Location     nvarchar(50) NOT NULL,
    [Address]    nvarchar(50),
    City         nvarchar(50),
    [State]      nvarchar(5),
    ZIPCode      nvarchar(12),
    Country      nvarchar(100) default N'USA',
    Notes nvarchar(max) NULL, 
    CONSTRAINT PK_Locations PRIMARY KEY(LocationCode)
);
GO
CREATE TABLE Management.Employees
(
    EmployeeNumber nvarchar(10) not null,
    FirstName nvarchar(25),
    MiddleName nvarchar(25),
    LastName nvarchar(25) NOT NULL,
    LocationCode nvarchar(10),
    Title nvarchar(50),
    CanCreateAccount bit,
    EmailAddress nvarchar(50),
    WorkPhone nvarchar(20),
    Extension smallint,
    [Address] nvarchar(50),
    City nvarchar(40),
    [State] nvarchar(5),
    ZIPCode nvarchar(12),
    Country nvarchar(50) default N'USA',
    PhoneNumber nvarchar(20),
    HourlySalary money,
    Notes nvarchar(max), 
    CONSTRAINT PK_Employees PRIMARY KEY(EmployeeNumber),
    CONSTRAINT FK_EmployeesLocations FOREIGN KEY(LocationCode) REFERENCES Management.Locations(LocationCode)
);
GO
-- ================================================================================
-- Table:	Personnel.StartingSalaries
-- Description: This table is used to assign default 
--	        salaries to (newly hired) staff members
-- ================================================================================
CREATE TABLE Management.StartingSalaries
(
	Category nvarchar(20) not null,
	StartingSalary money null
);
-- ================================================================================
-- Table:       Management.AccountTypes
-- Description: The account types are checking, saving or certificate of deposit
-- ================================================================================
CREATE TABLE Accounts.Types
(
    AccountType     nvarchar(25) NOT NULL, 
    TypeDescription nvarchar(max) NULL, 
    CONSTRAINT PK_AccountTypes PRIMARY KEY(AccountType)
);
GO
CREATE TABLE Accounts.Status
(
    AccountStatus     nvarchar(20) not null,
    StatusDescription nvarchar(max) null, 
    CONSTRAINT PK_AccountsStatus PRIMARY KEY(AccountStatus)
);
GO
CREATE TABLE Accounts.ChargesReasons
(
    ChargeReason      nvarchar(20) not null,
    ChargeDescription nvarchar(max) null, 
    CONSTRAINT PK_ChargesReasons PRIMARY KEY(ChargeReason)
);
GO
CREATE TABLE Accounts.CurrenciesTypes
(
    CurrencyType    nvarchar(20) not null,
    TypeDescription nvarchar(max) null, 
    CONSTRAINT PK_CurrenciesTypes PRIMARY KEY(CurrencyType)
);
GO
CREATE TABLE Accounts.TransactionsTypes
(
    TransactionType nvarchar(25) NOT NULL, 
    TypeDescription nvarchar(max) NULL, 
    CONSTRAINT PK_TransactionsTypes PRIMARY KEY(TransactionType)
);
GO
CREATE TABLE Accounts.Customers
(
    AccountNumber nvarchar(20) not null,
    DateCreated date,
    EmployeeNumber nvarchar(10),
    AccountType nvarchar(25),
    FirstName nvarchar(25),
    MiddleName nvarchar(25),
    LastName nvarchar(25) NOT NULL,
    [Address] nvarchar(50) NOT NULL,
    City nvarchar(40) NOT NULL,
    [State] nvarchar(5) NOT NULL,
    ZIPCode nvarchar(12) NOT NULL,
    PhoneNumber nvarchar(20) NOT NULL,
    WorkPhone nvarchar(20),
    DateUpdated date,
    AccountStatus nvarchar(20),
    Notes nvarchar(max),
    Constraint PK_Customers PRIMARY KEY(AccountNumber),
    Constraint FK_AccountCreator Foreign Key(EmployeeNumber) References Management.Employees(EmployeeNumber),
    Constraint FK_TypeOfAccount Foreign Key(AccountType) References Accounts.Types(AccountType),
    Constraint FK_CustomersAccountsStatus Foreign Key(AccountStatus) References Accounts.Status(AccountStatus)
);
GO
CREATE TABLE Accounts.PreviousCustomers
(
    AccountNumber nvarchar(20) not null,
    DateCreated date,
    EmployeeNumber nvarchar(10),
    AccountType nvarchar(25),
    FirstName nvarchar(25),
    MiddleName nvarchar(25),
    LastName nvarchar(25) NOT NULL,
    [Address] nvarchar(50) NOT NULL,
    City nvarchar(40) NOT NULL,
    [State] nvarchar(5) NOT NULL,
    ZIPCode nvarchar(12) NOT NULL,
    PhoneNumber nvarchar(20) NOT NULL,
    WorkPhone nvarchar(20),
    AccountStatus nvarchar(20),
    DateClosed date,
    Notes nvarchar(max),
    Constraint PK_PreviousCustomers PRIMARY KEY(AccountNumber),
    Constraint FK_PreviousAccountsCreators Foreign Key(EmployeeNumber) References Management.Employees(EmployeeNumber),
    Constraint FK_PreviousTypesOfAccount Foreign Key(AccountType) References Accounts.Types(AccountType),
    Constraint FK_PreviousAccountsStatus Foreign Key(AccountStatus) References Accounts.Status(AccountStatus)
);
GO
CREATE TABLE Accounts.Transactions
(
    TransactionID    int identity(1, 1) not null,
    LocationCode     nvarchar(10) not null,
    EmployeeNumber   nvarchar(10), -- Processed by
    AccountNumber    nvarchar(20), -- Processed for customer
    TransactionDate  date,
    TransactionTime  time,
    TransactionType  nvarchar(25),
    CurrencyType     nvarchar(20),
    DepositAmount    money,
    WithdrawalAmount money,
    CheckNumber      int,
    CheckAmount      money,
    Recipient        nvarchar(50),
    ChargeAmount     money,
    ChargeReason     nvarchar(20),
    HasEnoughFunds   bit,
    Balance          money,
    Notes            nvarchar(max),
    CONSTRAINT PK_Transactions PRIMARY KEY(TransactionID),
    Constraint FK_DepositsLocations Foreign Key(LocationCode) References Management.Locations(LocationCode),
    Constraint FK_TransactionsClerks Foreign Key(EmployeeNumber) References Management.Employees(EmployeeNumber),
    Constraint FK_CustomerAccount Foreign Key(AccountNumber) References Accounts.Customers(AccountNumber),
    Constraint FK_TransactionsTypes Foreign Key(TransactionType) References Accounts.TransactionsTypes(TransactionType),
    Constraint FK_TransactionsCurrenciesTypes Foreign Key(CurrencyType) References Accounts.CurrenciesTypes(CurrencyType),
    Constraint FK_AccountsChargesReasons Foreign Key(ChargeReason) References Accounts.ChargesReasons(ChargeReason)
);
GO
CREATE TABLE Accounts.Histories
(
    HistoryID     int identity(1, 1) not null,
    AccountNumber nvarchar(20),
    AccountStatus nvarchar(20),
    DateChanged   date,
    ShortNote	  nvarchar(50),
    DetailedNotes nvarchar(max),
    CONSTRAINT PK_Histories PRIMARY KEY(HistoryID),
    Constraint FK_AccountsHistories Foreign Key(AccountNumber) References Accounts.Customers(AccountNumber),
    Constraint FK_AccountsStatusHistories Foreign Key(AccountStatus) References Accounts.Status(AccountStatus)
);
GO