--  ***************************************************************************
--  *   Copyright (C) 2012, Paul Lutus                                        *
--  *                                                                         *
--  *   This program is free software; you can redistribute it and/or modify  *
--  *   it under the terms of the GNU General Public License as published by  *
--  *   the Free Software Foundation; either version 2 of the License, or     *
--  *   (at your option) any later version.                                   *
--  *                                                                         *
--  *   This program is distributed in the hope that it will be useful,       *
--  *   but WITHOUT ANY WARRANTY; without even the implied warranty of        *
--  *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the         *
--  *   GNU General Public License for more details.                          *
--  *                                                                         *
--  *   You should have received a copy of the GNU General Public License     *
--  *   along with this program; if not, write to the                         *
--  *   Free Software Foundation, Inc.,                                       *
--  *   59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.             *
--  ***************************************************************************

create database if not exists tutorial;
use tutorial;

-- create table if not exists debug (debug text);

drop table if exists invoice;

drop table if exists inventory;

create table if not exists inventory (
  inventory_id integer not null auto_increment,
  description text not null,
  price decimal(10,2) not null,
  in_stock integer not null,
  primary key (inventory_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

drop table if exists salespeople;

create table if not exists salespeople (
  salesperson_id integer not null auto_increment,
  first_name text not null,
  last_name text not null,
  primary key (salesperson_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table if not exists invoice (
  line_id integer not null auto_increment,
  salesperson_id integer not null,
  inventory_id integer not null,
  quantity integer not null default 0,
  in_stock integer not null default 0,
  remain integer not null default 0,
  price decimal(10,2) default 0,
  discount decimal(10,2) default 0,
  pre_tax decimal(10,2) default 0,
  tax decimal(10,2) default 0,
  subtotal decimal(10,2) default 0,
  comment text not null default '',
  primary key (line_id),
  constraint `fk_salesperson_id` foreign key (salesperson_id)
    references salespeople (salesperson_id)
    on delete restrict on update restrict,
  constraint `fk_inventory_id` foreign key (inventory_id)
    references inventory (inventory_id)
    on delete restrict on update restrict
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

drop trigger if exists before_update_invoice;

drop trigger if exists before_insert_invoice;

drop procedure if exists proc_process_invoice;

delimiter |

create procedure proc_process_invoice (
   in salesperson_id integer,
   in inv_id integer,
   in quantity integer,
   inout in_stock integer,
   inout remain integer,
   inout price decimal(10,2),
   inout discount decimal(10,2),
   inout pre_tax decimal(10,2),
   inout tax decimal(10,2),
   inout subtotal decimal(10,2),
   inout comment text
 )
 begin
  select x.in_stock,x.price into in_stock,price from inventory as x
    where inventory_id = inv_id;
  if(in_stock - quantity >= 0) then -- okay to complete transaction
    set discount = if(quantity >= 12,10,0);
    set pre_tax = (price * quantity) * (1- (discount / 100));
    set tax = pre_tax * 0.085;
    set subtotal = pre_tax + tax;
    set remain = in_stock - quantity;
    update inventory set in_stock = remain where inventory_id = inv_id;
  else -- not enough of this item in stock
    set remain = in_stock;
    set comment = 'Insufficient stock';
  end if;
 end|
  

CREATE TRIGGER `before_update_invoice`
before update ON `invoice`
for each row
BEGIN
  call proc_process_invoice(
    new.salesperson_id,
    new.inventory_id,
    new.quantity,
    new.in_stock,
    new.remain,
    new.price,
    new.discount,
    new.pre_tax,
    new.tax,
    new.subtotal,
    new.comment);
END|

CREATE TRIGGER `before_insert_invoice`
before insert ON `invoice`
for each row
BEGIN
  call proc_process_invoice(
    new.salesperson_id,
    new.inventory_id,
    new.quantity,
    new.in_stock,
    new.remain,
    new.price,
    new.discount,
    new.pre_tax,
    new.tax,
    new.subtotal,
    new.comment);
END|

delimiter ;

create or replace view invoice_view as select
  concat(se.first_name,' ',se.last_name) as Salesperson,
  inv.description as Item,
  iv.quantity as Quantity,
  iv.in_stock as `In Stock`,
  iv.remain as `Remain`,
  iv.price as Price,
  iv.discount as `Discount %`,
  iv.pre_tax as Pretax,
  iv.tax as Tax,
  iv.subtotal as Subtotal,
  iv.comment as Comment
  from invoice as iv
  left join inventory as inv on iv.inventory_id = inv.inventory_id
  left join salespeople as se on iv.salesperson_id = se.salesperson_id;

insert into inventory (description,price,in_stock) values
  ('Mousetrap',5.98,20),
  ('Hammer',12.33,10),
  ('Bird Seed',6.44,8),
  ('Hacksaw',18.23,20),
  ('Garden Hose',17.43,10),
  ('Trash Can',14.45,16),
  ('Nail 6d',0.07,200),
  ('Nail 12d',0.28,100);
  
insert into salespeople (first_name,last_name) values
  ('John','Wilson'),
  ('Terry','Philips'),
  ('Mark','Johnson'),
  ('Mary','Jones'),
  ('Frank','Smith');

insert into invoice (salesperson_id,inventory_id,quantity) values
  (1,1,10),
  (2,2,20),
  (3,3,30),
  (4,4,10),
  (5,5,8),
  (4,6,14),
  (3,7,12),
  (2,8,24);
  
commit;

-- select * from debug;