/*
 * create tables
*/

drop table if exists investment;
create table investment (
  i_id integer not null,
  i_name varchar(64) not null,
  i_currvalue float default 0,
  primary key (i_id)
);


drop table if exists trade;
create table trade (
  t_id integer not null,
  inv_id integer not null,
  p_id integer not null,
  t_date date not null,
  t_qty float not null,
  t_costbasis float not null,
  t_type char(1) not null,
  primary key (t_id),
  foreign key (inv_id) references investment(i_id),
  foreign key (p_id) references portfolio(p_id)
);

drop table if exists portfolio;
create table portfolio (
  p_id integer not null,
  p_value real,
  p_gain real
);


/*
 * insert data
 */

--
-- Portfolios
--

insert into portfolio values 
(1101,null,null),
(2987,null,null),
(1623,null,null);

--
-- Investments
--

insert into investment values 
(10,"IBM",34.22),
(20,"FB",22.75),
(30,"MSFT",78.50),
(40,"JNJ",118),
(50,"NU-AA+",101.125),
(60,"CITBOS-BBB+",98.75),
(70,"AAPL",113.12),
(80,"DIS",92.88);

--
-- Trades
--
insert into trade values
(1,10,1101,"8/7/2016",100,32.50,'b'),
(2,10,1101,"8/9/2016",200,32.75,'b'),
(3,20,1101,"8/9/2016",100,21.50,'b'),
(4,20,1101,"9/12/2016",500,24.75,'s');