create database portal;

use portal;

create table groups
(
  groupid int unsigned not null auto_increment primary key,
  groupname char(10) not null
)ENGINE=InnoDB;

INSERT INTO groups(groupname) VALUES('admin');
INSERT INTO groups(groupname) VALUES('sales');
INSERT INTO groups(groupname) VALUES('agent');
INSERT INTO groups(groupname) VALUES('customer');

create table users
(
  uid int unsigned not null auto_increment primary key,
  email varchar(30) not null,
  passwd char(40) not null,
  groupid int unsigned not null references groups(groupid)
)ENGINE=InnoDB;

insert into users (email,passwd,groupid) values  ('admin@vtgus.com', sha1('123456'), '1');
insert into users (email,passwd,groupid) values  ('sales@vtgus.com', sha1('123456'), '2');
insert into users (email,passwd,groupid) values  ('agent@vtgus.com', sha1('123456'), '3');
insert into users (email,passwd,groupid) values  ('customer@vtgus.com', sha1('123456'), '4');

create table orders
(
  ordid int unsigned not null auto_increment primary key,
  cusid int unsigned not null references users(uid),
  manid int unsigned not null references users(uid),
  datetime DATETIME
)ENGINE=InnoDB;

insert into orders (cusid, manid, datetime) VALUES('1','2',NOW());

ALTER TABLE orders AUTO_INCREMENT = 1000;

create table products
(
  prodid int unsigned not null auto_increment primary key,
  prodname varchar(100) not null,
  nrcost float(6,2),
  mrcost float(6,2),
  catid int unsigned not null references catprod(catid)
) ENGINE=InnoDB;

insert into products (prodname,nrcost,mrcost,catid) values  ('Polycom 650', '300','35','1');
insert into products (prodname,nrcost,mrcost,catid) values  ('Polycom 335', '150','25','1');
insert into products (prodname,nrcost,mrcost,catid) values  ('Yealink T20', '70','25','1');
insert into products (prodname,nrcost,mrcost,catid) values  ('Yealink T28', '120','30','1');
insert into products (prodname,nrcost,mrcost,catid) values  ('Polycom SideCar 200', '200','25','2');
insert into products (prodname,nrcost,mrcost,catid) values  ('Yealink Headset', '25','25','2');
insert into products (prodname,nrcost,mrcost,catid) values  ('HP 24 Port POE', '300','300','3');
insert into products (prodname,nrcost,mrcost,catid) values  ('Sonic Wall TZ 200', '400','400','4');
insert into products (prodname,nrcost,mrcost,catid) values  ('Linksys RUO 420', '200','200','4');
insert into products (prodname,nrcost,mrcost,catid) values  ('Simultaneos Call', '20','20','5');
insert into products (prodname,nrcost,mrcost,catid) values  ('E911', '20','20','5');
insert into products (prodname,nrcost,mrcost,catid) values  ('Hosted Seat', '20','20','5');

create table purchase
(
  ordid int unsigned not null references orders(ordid),
  prodid int unsigned not null references products(prodid),
  qty int not null,
  fprice float(6,2) not null
)ENGINE=InnoDB;

insert into purchase (ordid, prodid, qty, fprice) VALUES('1','1','5','100');
insert into purchase (ordid, prodid, qty, fprice) VALUES('1','3','5','40.12');



create table catprod
(
  catid int unsigned not null auto_increment primary key,
  catname char(30) not null
)ENGINE=InnoDB;

insert into catprod (catname) VALUES ('phone');
insert into catprod (catname) VALUES ('accessories');
insert into catprod (catname) VALUES ('switches');
insert into catprod (catname) VALUES ('edge');
insert into catprod (catname) VALUES ('dialtone');





create table cusdata
(
  ordid int not null,
  busname varchar(100) not null,
  conname varchar(100) not null,
  conemail varchar(30) not null,
  address1 varchar(100) not null,
  address2 varchar(100) not null,
  state varchar(5) not null,
  zip int not null,
  mainphone int not null,
  fax int not null
);