-- create tables for online banking app
-- also seeds next_id tables w. initial values

DROP TABLE customer_account_xref;
DROP TABLE tx;
DROP TABLE customer;
DROP TABLE account;
DROP TABLE next_tx_id;
DROP TABLE next_customer_id;
DROP TABLE next_account_id;


CREATE TABLE account
   (account_id VARCHAR(8) 
       CONSTRAINT pk_account PRIMARY KEY,
    type VARCHAR(24),
    description VARCHAR(30),
    balance NUMERIC(10,2),
    credit_line NUMERIC(10,2),
    begin_balance NUMERIC(10,2),
    begin_balance_time_stamp TIMESTAMP);

CREATE TABLE customer
   (customer_id VARCHAR(8) 
       CONSTRAINT pk_customer PRIMARY KEY,
    last_name VARCHAR(30),
    first_name VARCHAR(30),
    middle_initial VARCHAR(1),
    street VARCHAR(40),
    city VARCHAR(40),
    state VARCHAR(2),
    zip VARCHAR(5),
    phone VARCHAR(16),
    email VARCHAR(30));

CREATE TABLE tx 
   (tx_id VARCHAR(8) 
       CONSTRAINT pk_tx PRIMARY KEY,
    account_id VARCHAR(8),
    time_stamp TIMESTAMP,
    amount NUMERIC(10,2),
    balance NUMERIC(10,2),
    description VARCHAR(30));

CREATE TABLE customer_account_xref
   (customer_id VARCHAR(8), 
    account_id VARCHAR(8));


CREATE TABLE next_account_id
   (id INTEGER);

CREATE TABLE next_customer_id
   (id INTEGER);

CREATE TABLE next_tx_id
   (id INTEGER); 

INSERT INTO next_account_id
   VALUES (5050);

INSERT INTO next_customer_id
   VALUES (150);

INSERT INTO next_tx_id
   VALUES (1);



DELETE FROM tx;
DELETE FROM customer_account_xref;
DELETE FROM account;
DELETE FROM customer;

INSERT INTO account VALUES
('5005', 'Money Market', 'Hi Balance', 4000.00, 0.00, 3500.00, '2003-07-28 23:03:20');
 
INSERT INTO account VALUES
('5006', 'Checking', 'Checking', 85.00, 0.00, 66.54, '2003-07-21 03:12:00');

INSERT INTO account VALUES
('5007', 'Credit', 'Visa', 599.18, 5000.00, 166.08, '2003-07-23 10:13:54');

INSERT INTO account VALUES
('5008', 'Savings', 'Super Interest Account', 55601.35, 0.00, 5433.89, '2003-07-15 12:55:33');

INSERT INTO customer VALUES
('200', 'Jones', 'Richard', 'K',
 '88 Poplar Ave.', 'Cupertino', 'CA', '95014',
 '408-123-4567', 'rhill@j2ee.com');

INSERT INTO customer VALUES
('201', 'Jones', 'Mary', 'R',
 '88 Poplar Ave.', 'Cupertino', 'CA', '95014',
 '408-123-4567', 'mhill@j2ee.com');

INSERT INTO customer_account_xref VALUES
('200', '5005');

INSERT INTO customer_account_xref VALUES
('201', '5005');

INSERT INTO customer_account_xref VALUES
('200', '5006');

INSERT INTO customer_account_xref VALUES
('200', '5007');

INSERT INTO customer_account_xref VALUES
('201', '5006');

INSERT INTO customer_account_xref VALUES
('201', '5007');

INSERT INTO customer_account_xref VALUES
('200', '5008');

INSERT INTO customer_account_xref VALUES
('201', '5008');

INSERT INTO tx VALUES
('00000001', '5005', '2004-01-01 12:55:33', 200.00, 4200.00, 'Refund');
UPDATE account SET balance = 4200.00 WHERE account_id = '5005';
INSERT INTO tx VALUES
('00000003', '5008', '2004-01-03 12:55:33', -1000.00, 54601.35, 'Transfer Out');
UPDATE account SET balance = 54604.35 WHERE account_id = '5008'; 
INSERT INTO tx VALUES
('00000004', '5006', '2004-01-03 12:55:33', 1000.00, 1085.00, 'Transfer In');
UPDATE account SET balance = 1085.00 WHERE account_id = '5006'; 
INSERT INTO tx VALUES
('00000005', '5007', '2004-01-05 12:55:33', 33.00, 199.08, 'Clothing');
UPDATE account SET balance = 199.08 WHERE account_id = '5007'; 
INSERT INTO tx VALUES
('00000006', '5006', '2004-01-06 12:55:33', 2000.00, 3085.00, 'Paycheck Deposit');
UPDATE account SET balance = 3085.00 WHERE account_id = '5006'; 
INSERT INTO tx VALUES
('00000007', '5005', '2004-01-07 12:55:33', -200.00, 4000.00, 'ATM Withdrawal');
UPDATE account SET balance = 3085.00 WHERE account_id = '5005'; 
INSERT INTO tx VALUES
('00000008', '5006', '2004-01-08 12:55:33', -200.00, 2885.00, 'Car Insurance');
UPDATE account SET balance = 2885.00 WHERE account_id = '5006'; 
INSERT INTO tx VALUES
('00000009', '5007', '2004-01-09 12:55:33', 186.00, 385.08, 'Car Repair');
UPDATE account SET balance = 385.08 WHERE account_id = '5007'; 
INSERT INTO tx VALUES
('00000010', '5008', '2004-01-10 12:55:33', 1000.00, 55601.35, 'Deposit');
UPDATE account SET balance = 55601.35 WHERE account_id = '5008'; 
INSERT INTO tx VALUES
('00000011', '5007', '2004-01-11 12:55:33', 585.00, 970.08, 'Airplane Tickets');
UPDATE account SET balance = 970.08 WHERE account_id = '5007'; 
INSERT INTO tx VALUES
('00000012', '5006', '2004-01-12 12:55:33', -675.00, 2210.00, 'Mortgage Payment');
UPDATE account SET balance = 2210.00 WHERE account_id = '5006'; 
INSERT INTO tx VALUES
('00000013', '5005', '2004-01-13 12:55:33', -100.00, 3900.00, 'ATM Withdrawal');
UPDATE account SET balance = 3900.00 WHERE account_id = '5005'; 
INSERT INTO tx VALUES
('00000014', '5006', '2004-01-14 12:55:33', -385.08, 1824.92, 'Visa Payment');
UPDATE account SET balance = 1824.92 WHERE account_id = '5006'; 
INSERT INTO tx VALUES
('00000015', '5007', '2004-01-15 12:55:33', -385.08, 585.00, 'Payment');
UPDATE account SET balance = 585.00 WHERE account_id = '5007'; 
INSERT INTO tx VALUES
('00000017', '5007', '2004-01-17 12:55:33', 26.95, 611.95, 'Movies');
UPDATE account SET balance = 611.95 WHERE account_id = '5007'; 
INSERT INTO tx VALUES
('00000018', '5006', '2004-01-18 12:55:33', -31.00, 1793.92, 'Groceries');
UPDATE account SET balance = 1793.92 WHERE account_id = '5006'; 
INSERT INTO tx VALUES
('00000019', '5005', '2004-01-19 12:55:33', -150.00, 3750.00, 'ATM Withdrawal');
UPDATE account SET balance = 3750.00 WHERE account_id = '5005'; 
INSERT INTO tx VALUES
('00000020', '5006', '2004-01-20 12:55:33', 2000.00, 3793.92, 'Paycheck Deposit');
UPDATE account SET balance = 3793.92 WHERE account_id = '5006'; 
INSERT INTO tx VALUES
('00000021', '5007', '2004-01-21 12:55:33', 124.00, 735.95, 'Furnishings');
UPDATE account SET balance = 735.95 WHERE account_id = '5007'; 
INSERT INTO tx VALUES
('00000023', '5007', '2004-01-23 12:55:33', 33.12, 769.07, 'Hardware');
UPDATE account SET balance = 769.07 WHERE account_id = '5007'; 
INSERT INTO tx VALUES
('00000024', '5006', '2004-01-24 12:55:33', -175.33, 3618.59, 'Utility Bill');
UPDATE account SET balance = 3618.59 WHERE account_id = '5006'; 
INSERT INTO tx VALUES
('00000025', '5006', '2004-01-25 12:55:33', -123.00, 3555.59, 'Groceries');
UPDATE account SET balance = 3555.59 WHERE account_id = '5006'; 
INSERT INTO tx VALUES
('00000026', '5006', '2004-01-26 12:55:33', -675.00, 2880.59, 'Mortgage Payment');
UPDATE account SET balance = 2880.59 WHERE account_id = '5006'; 
INSERT INTO tx VALUES
('00000027', '5007', '2004-01-27 12:55:33', 24.72, 793.79, 'Cafe');
UPDATE account SET balance = 793.79 WHERE account_id = '5007'; 
INSERT INTO tx VALUES
('00000028', '5008', '2004-01-28 12:55:33', 1000.00, 56601.35, 'Deposit');
UPDATE account SET balance = 56601.35 WHERE account_id = '5008'; 
INSERT INTO tx VALUES
('00000029', '5007', '2004-01-29 12:55:33', 35.00, 828.79, 'Hair Salon');
UPDATE account SET balance = 828.79 WHERE account_id = '5007'; 
INSERT INTO tx VALUES
('00000030', '5006', '2004-01-30 12:55:33', -20.00, 2860.59, 'Gasoline');
UPDATE account SET balance = 2860.59 WHERE account_id = '5006'; 
INSERT INTO tx VALUES
('00000031', '5005', '2004-02-01 12:55:33', -100.00, 3650.00, 'ATM Withdrawal');
UPDATE account SET balance = 3650.00 WHERE account_id = '5005'; 
INSERT INTO tx VALUES
('00000032', '5006', '2004-02-02 12:55:33', -56.87, 2803.72, 'Phone Bill');
UPDATE account SET balance = 2803.72 WHERE account_id = '5006'; 
INSERT INTO tx VALUES
('00000033', '5007', '2004-02-03 12:55:33', 67.99, 896.78, 'Acme Shoes');
UPDATE account SET balance = 896.78 WHERE account_id = '5007'; 
INSERT INTO tx VALUES
('00000035', '5007', '2004-02-05 12:55:33', 24.00, 920.78, 'Movies');
UPDATE account SET balance = 920.78 WHERE account_id = '5007'; 
INSERT INTO tx VALUES
('00000036', '5006', '2004-02-06 12:55:33', 2000.00, 4803.72, 'Paycheck Deposit');
UPDATE account SET balance = 4803.72 WHERE account_id = '5006'; 
INSERT INTO tx VALUES
('00000038', '5006', '2004-02-08 12:55:33', -100.00, 4703.72, 'Groceries');
UPDATE account SET balance = 4703.72 WHERE account_id = '5006'; 
INSERT INTO tx VALUES
('00000039', '5007', '2004-02-09 12:55:33', 26.95, 947.73, 'Pizza');
UPDATE account SET balance = 947.73 WHERE account_id = '5007'; 
INSERT INTO tx VALUES
('00000041', '5007', '2004-02-11 12:55:33', 125.00, 1072.73, 'Dentist');
UPDATE account SET balance = 1072.73 WHERE account_id = '5007'; 
INSERT INTO tx VALUES
('00000042', '5006', '2004-02-12 12:55:33', -675.00, 4028.72, 'Mortgage Payment');
UPDATE account SET balance = 4028.72 WHERE account_id = '5006'; 
INSERT INTO tx VALUES
('00000043', '5005', '2004-02-13 12:55:33', -150.00, 3500.00, 'ATM Withdrawal');
UPDATE account SET balance = 3500.00 WHERE account_id = '5005'; 
INSERT INTO tx VALUES
('00000044', '5006', '2004-02-14 12:55:33', -947.73, 3080.99, 'Visa Payment');
UPDATE account SET balance = 3080.99 WHERE account_id = '5006'; 
INSERT INTO tx VALUES
('00000045', '5007', '2004-02-15 12:55:33', -947.73, 125.00, 'Payment');
UPDATE account SET balance = 125.00 WHERE account_id = '5007'; 
INSERT INTO tx VALUES
('00000047', '5007', '2004-02-17 12:55:33', 49.90, 100.85, 'Bookstore');
UPDATE account SET balance = 100.85 WHERE account_id = '5007'; 
INSERT INTO tx VALUES
('00000048', '5006', '2004-02-18 12:55:33', -100.00, 2980.99, 'Groceries');
UPDATE account SET balance = 2980.99 WHERE account_id = '5006'; 
INSERT INTO tx VALUES
('00000050', '5006', '2004-02-20 12:55:33', 2000.00, 4980.99, 'Paycheck Deposit');
UPDATE account SET balance = 4980.99 WHERE account_id = '5006'; 
INSERT INTO tx VALUES
('00000051', '5007', '2004-02-21 12:55:33', 80.32, 181.17, 'Restaurant');
UPDATE account SET balance = 181.17 WHERE account_id = '5007'; 
INSERT INTO tx VALUES
('00000053', '5007', '2004-02-23 12:55:33', 11.78, 192.95, 'Electronics');
UPDATE account SET balance = 192.95 WHERE account_id = '5007'; 
INSERT INTO tx VALUES
('00000054', '5006', '2004-02-24 12:55:33', -150.45, 4830.54, 'Utility Bill');
UPDATE account SET balance = 4830.54 WHERE account_id = '5006'; 
INSERT INTO tx VALUES
('00000055', '5005', '2004-02-25 12:55:33', -100.00, 3400.00, 'ATM Withdrawal');
UPDATE account SET balance = 3400.00 WHERE account_id = '5005'; 
INSERT INTO tx VALUES
('00000056', '5006', '2004-02-26 12:55:33', -675.00, 4155.54, 'Mortgage Payment');
UPDATE account SET balance = 4155.54 WHERE account_id = '5006'; 
INSERT INTO tx VALUES
('00000057', '5007', '2004-02-27 12:55:33', 24.00, 216.95, 'Ice Skating');
UPDATE account SET balance = 216.95 WHERE account_id = '5007'; 
INSERT INTO tx VALUES
('00000058', '5006', '2004-02-28 12:55:33', -1000.00, 3155.54, 'Transfer Out');
UPDATE account SET balance = 3155.54 WHERE account_id = '5006'; 
INSERT INTO tx VALUES
('00000059', '5008', '2004-02-28 12:55:33', 1000.00, 57601.35, 'Transfer In');
UPDATE account SET balance = 57601.35 WHERE account_id = '5008'; 
INSERT INTO tx VALUES
('00000060', '5006', '2004-03-02 12:55:33', -99.22, 3056.32, 'Phone Bill');
UPDATE account SET balance = 3056.32 WHERE account_id = '5006'; 
INSERT INTO tx VALUES
('00000061', '5007', '2004-03-03 12:55:33', 29.97, 246.92, 'Toy Store');
UPDATE account SET balance = 246.92 WHERE account_id = '5007'; 
INSERT INTO tx VALUES
('00000062', '5006', '2004-03-04 12:55:33', -2000.00, 1056.32, 'Transfer Out');
UPDATE account SET balance = 1056.32 WHERE account_id = '5006'; 
INSERT INTO tx VALUES
('00000063', '5008', '2004-03-05 12:55:33', 2000.00, 59601.35, 'Transfer In');
UPDATE account SET balance = 59601.35 WHERE account_id = '5008'; 
INSERT INTO tx VALUES
('00000064', '5006', '2004-03-06 12:55:33', 2000.00, 3056.32, 'Paycheck Deposit');
UPDATE account SET balance = 3056.35 WHERE account_id = '5006'; 
INSERT INTO tx VALUES
('00000065', '5007', '2004-03-07 12:55:33', 14.69, 261.61, 'Cafe');
UPDATE account SET balance = 261.61 WHERE account_id = '5007'; 
INSERT INTO tx VALUES
('00000066', '5006', '2004-03-08 12:55:33', -108.99, 2947.33, 'Groceries');
UPDATE account SET balance = 2947.33 WHERE account_id = '5006'; 
INSERT INTO tx VALUES
('00000067', '5006', '2004-03-09 12:55:33', -30.12, 2917.21, 'Gasoline');
UPDATE account SET balance = 2917.21 WHERE account_id = '5006'; 
INSERT INTO tx VALUES
('00000069', '5007', '2004-03-11 12:55:33', 125.00, 386.61, 'Dentist');
UPDATE account SET balance = 2237.21 WHERE account_id = '5007'; 
INSERT INTO tx VALUES
('00000070', '5006', '2004-03-12 12:55:33', -675.00, 2242.21, 'Mortgage Payment');
UPDATE account SET balance = 2242.21 WHERE account_id = '5006';  
INSERT INTO tx VALUES
('00000072', '5006', '2004-03-13 12:55:33', -261.61, 1980.60, 'Visa Payment');
UPDATE account SET balance = 1980.60 WHERE account_id = '5006'; 
INSERT INTO tx VALUES
('00000073', '5007', '2004-03-14 12:55:33', -261.61, 125.00, 'Payment');
UPDATE account SET balance = 125.00 WHERE account_id = '5007'; 
INSERT INTO tx VALUES
('00000075', '5007', '2004-03-15 12:55:33', 24.00, 149.00, 'Drug Store');
UPDATE account SET balance = 149.00 WHERE account_id = '5007'; 
