/**  
 * stock_competition.sql  
 *      Author: Meg Richards (merichar@andrew.cmu.edu)  
 *      Created: 18 February 2008  
 *   
 * Generates a starting database for the stock market competition.
 */  

drop database if exists stock_competition_development;

create database stock_competition_development;

use stock_competition_development;

create table stocks (
  id                         int              not null auto_increment,
  ticker                     varchar(10)      null,
  name                       varchar(50)      null,
  
  primary key (id)
);

create table users (
  id                         int               not null auto_increment,
  username                   varchar(100)      null,
  hashed_password            varchar(255)      null,
  salt                       varchar(255)      null,
  capital                    float(15,2)       null default 150000,

  primary key (id)
);

create table portfolio_items (
  id                         int(11)           not null auto_increment,
  user_id		     int(11)	       null default 0,
  stock_id                   int(11)           null default 0,
  shares                     int(11)           null default 0,

  constraint fk_portfolio_items_users
  foreign key (user_id) 
    references users(id),

  constraint fk_portfolio_items_stocks
  foreign key (stock_id)
    references stocks(id),

  primary key (id)
);

create table transactions (
  id                         int(11)           not null auto_increment,
  user_id		     int(11)	       null default 0,
  stock_id                   int(11)           null default 0,
  shares_traded              int(11)           null default 0,
  price_per_share            float(8,2)        null default 0,
  traded_at                  timestamp         null,
  trade_type                 enum("buy","sell") null,

  constraint fk_transactionss_users
  foreign key (user_id) 
    references users(id),

  constraint fk_transactions_stocks
  foreign key (stock_id)
    references stocks(id),

  primary key (id)
);

insert into stocks (ticker,name) values ('QNBK', 'Qatar National Bank');
insert into stocks (ticker,name) values ('CBQK', 'The Commercial Bank');
insert into stocks (ticker,name) values ('QIBK', 'Qatar Islamic Bank');
insert into stocks (ticker,name) values ('DHBK', 'Doha Bank');
insert into stocks (ticker,name) values ('ABQK', 'Al-Ahli Bank');
insert into stocks (ticker,name) values ('QIIK', 'Qatar International Islamic Bank');
insert into stocks (ticker,name) values ('MARK', 'Masraf Al Rayan');
insert into stocks (ticker,name) values ('KCBK', 'Al Khaliji Commercial Bank');
insert into stocks (ticker,name) values ('FFCK', 'First Finance');
insert into stocks (ticker,name) values ('QATI', 'Qatar Insurance');
insert into stocks (ticker,name) values ('QGRI', 'Qatar General Insurance and Re-Insurance');
insert into stocks (ticker,name) values ('AKHI', 'Al-Khaleej Insurance');
insert into stocks (ticker,name) values ('QISI', 'Qatar Islamic Insurance');
insert into stocks (ticker,name) values ('DOHI', 'Doha Insurance');
insert into stocks (ticker,name) values ('QNCD', 'Qatar National Cement');
insert into stocks (ticker,name) values ('QFMD', 'Qatar Flour Mills');
insert into stocks (ticker,name) values ('IQCD', 'Industries Qatar');
insert into stocks (ticker,name) values ('QGMD', 'Qatari German Company for Medical Devices');
insert into stocks (ticker,name) values ('UDCD', 'United Development');
insert into stocks (ticker,name) values ('QIMD', 'Qatar Industrial Manufacturing');
insert into stocks (ticker,name) values ('GCCD', 'Gulf Cement Company');
insert into stocks (ticker,name) values ('QNNS', 'Qatar Navigation');
insert into stocks (ticker,name) values ('QCFS', 'Qatar Cinema & Film Distributing');
insert into stocks (ticker,name) values ('QEWS', 'Qatar Electricity & Water');
insert into stocks (ticker,name) values ('QSHS', 'Qatar Shipping');
insert into stocks (ticker,name) values ('QRES', 'Qatar Real Estate Investment');
insert into stocks (ticker,name) values ('MCGS', 'MediCare Group');
insert into stocks (ticker,name) values ('QTEL', 'Qatar Telecom');
insert into stocks (ticker,name) values ('SIIS', 'Salam International');
insert into stocks (ticker,name) values ('QTIS', 'Qatar Technical Inspection');
insert into stocks (ticker,name) values ('QFLS', 'Qatar Fuel');
insert into stocks (ticker,name) values ('GWCS', 'Gulf Warehousing');
insert into stocks (ticker,name) values ('NLCS', 'National Leasing');
insert into stocks (ticker,name) values ('DBIS', 'Dlala Brokerage & Investment');
insert into stocks (ticker,name) values ('BRES', 'Barwa Real Estate Company');
insert into stocks (ticker,name) values ('QGTS', 'Qatar Gas Transport Company');
insert into stocks (ticker,name) values ('QMLS', 'Qatar Meat & Livestock');
insert into stocks (ticker,name) values ('MCCS', 'Mannai Corporation');
insert into stocks (ticker,name) values ('AHCS', 'Aamal Holding Company');
insert into stocks (ticker,name) values ('QOIS', 'Qatar Oman Investment Company');
insert into stocks (ticker,name) values ('ERES', 'Ezdan Real Estate Company');