CREATE DATABASE banking CHARACTER SET utf8 COLLATE utf8_general_ci;
USE banking;

CREATE TABLE customers (
customer_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
first_name VARCHAR(20) NOT NULL,
last_name VARCHAR(40) NOT NULL,
PRIMARY KEY (customer_id),
INDEX full_name (last_name, first_name)
) ENGINE = INNODB;

CREATE TABLE accounts (
account_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
customer_id INT UNSIGNED NOT NULL,
type ENUM('Checking', 'Savings') NOT NULL,
balance DECIMAL(10,2) UNSIGNED NOT NULL DEFAULT 0.0,
PRIMARY KEY (account_id),
INDEX (customer_id),
FOREIGN KEY (customer_id) REFERENCES customers (customer_id) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE = INNODB;

CREATE TABLE transactions (
transaction_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
to_account_id INT UNSIGNED NOT NULL,
from_account_id INT UNSIGNED NOT NULL,
amount DECIMAL(10,2) UNSIGNED NOT NULL,
date_entered TIMESTAMP NOT NULL,
PRIMARY KEY (transaction_id),
INDEX (to_account_id),
INDEX (from_account_id),
INDEX (date_entered),
FOREIGN KEY (to_account_id) REFERENCES accounts (account_id)
ON DELETE NO ACTION ON UPDATE NO ACTION,
FOREIGN KEY (from_account_id) REFERENCES accounts (account_id) 
ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE = INNODB;

/* original data */
INSERT INTO customers (first_name, last_name) 
VALUES ('Sarah', 'Vowell'), ('David', 'Sedaris'), ('Kojo', 'Nnamdi');

/* Generated data - need to remove autogenerated pK column */
INSERT INTO `customers` ( `first_name`, `last_name`) VALUES
('Catherine', 'Adams'),
('Evans', 'Adams'),
('Jessica', 'Adams');


/* original data */
INSERT INTO accounts (customer_id, balance) 
VALUES (1, 5460.23), (2, 909325.24), (3, 892.00);
INSERT INTO accounts (customer_id, type, balance) 
VALUES (2, 'Savings', 13546.97);

INSERT INTO `accounts` ( `customer_id`, `type`, `balance`) VALUES
(4, 'Checking', 10000.00),
(5, 'Checking', 15000.00),
(6, 'Checking', 20000.00),
(4, 'Savings', 13546.97),
(5, 'Savings', 13546.97),
(6, 'Savings', 13546.97);

# -- Added by Evans
INSERT INTO transactions( to_account_id, from_account_id, amount, date_entered) 
VALUES (2,4,1000,NOW());
INSERT INTO transactions( to_account_id, from_account_id, amount, date_entered) 
VALUES (4,2,2000,NOW());
INSERT INTO transactions( to_account_id, from_account_id, amount, date_entered) 
VALUES (1,3,2000,NOW());

INSERT INTO `transactions` ( `to_account_id`, `from_account_id`, `amount`, `date_entered`) VALUES
(8, 5, 999.99, '2012-01-10 22:38:12'),
(8, 5, 999.99, '2012-01-10 22:39:21'),
(6, 9, 999.99, '2012-01-10 22:40:32'),
(6, 9, 999.99, '2012-01-10 22:41:02'),
(7, 10, 999.99, '2012-01-10 22:42:14'),
(7, 10, 999.99, '2012-01-10 22:42:59');