drop table if exists payments;
drop table if exists payment_types;
drop table if exists customers;

create table payment_types
(
pay_type_id tinyint unsigned not null auto_increment primary key,
name varchar(255) unique not null
)
engine=innodb;

create table customers
(
cust_id int unsigned not null auto_increment primary key,
email varchar(512) unique not null,
total_amount_paid decimal(10,2) not null default 0
)
engine=innodb;

create table payments
(
pay_id int unsigned not null auto_increment primary key,
cust_id int unsigned not null,
pay_type_id tinyint unsigned not null,
pay_date datetime not null,
amount decimal(10,2) not null default 0,
key (pay_date),
foreign key (cust_id) references customers(cust_id),
foreign key (pay_type_id) references payment_types(pay_type_id)
)
engine=innodb;

drop view if exists payments_view;
create view payments_view as
select
 p.pay_id,
 p.pay_date,
 p.pay_type_id,
 pt.name as pay_type_name,
 p.amount,
 c.cust_id,
 c.email
from
 customers c
inner join payments p on c.cust_id = p.cust_id
inner join payment_types pt on p.pay_type_id = pt.pay_type_id;

delimiter ;

drop procedure if exists insert_payment;

delimiter #

create procedure insert_payment
(
in p_email varchar(512),
in p_pay_type_id tinyint unsigned,
in p_amount decimal(10,2)
)
begin

declare v_cust_id int unsigned default 0;

  if not exists (select 1 from customers where email = p_email) then
    insert into customers (email) values (p_email);
    set v_cust_id = last_insert_id();
  else
    select cust_id into v_cust_id from customers where email = p_email;
  end if;

  insert into payments (cust_id, pay_type_id, amount)
   values (v_cust_id, p_pay_type_id, p_amount);

  select last_insert_id() as new_pay_id;

end#

create trigger payments_before_ins_trig before insert on payments
for each row
begin
 set new.pay_date = now();

 update customers set total_amount_paid = total_amount_paid + new.amount
  where cust_id = new.cust_id;
end#

delimiter ;

insert into payment_types (name) values ('visa'),('mastercard'),('cash');

insert into customers (email) values ('foo@bar.com'),('bar@foo.com'),('pants@elis.com');

call insert_payment('foo@bar.com',1,100);
call insert_payment('bar@foo.com',2,200);
call insert_payment('pants@elis.com',3,300);
call insert_payment('another@customer.com',1,400);
call insert_payment('another@customer.com',2,500);


 mysql> select * from payment_types order by pay_type_id;
    +-------------+------------+
    | pay_type_id | name       |
    +-------------+------------+
    |           1 | visa       |
    |           2 | mastercard |
    |           3 | cash       |
    +-------------+------------+
    3 rows in set (0.00 sec)

    mysql> select * from customers order by cust_id;
    +---------+----------------+-------------------+
    | cust_id | email          | total_amount_paid |
    +---------+----------------+-------------------+
    |       1 | foo@bar.com    |            600.00 |
    |       2 | bar@foo.com    |            900.00 |
    |       3 | pants@elis.com |            600.00 |
    +---------+----------------+-------------------+
    3 rows in set (0.00 sec)

    mysql> select * from payments order by pay_id;
    +--------+---------+-------------+---------------------+--------+
    | pay_id | cust_id | pay_type_id | pay_date            | amount |
    +--------+---------+-------------+---------------------+--------+
    |      1 |       1 |           1 | 2011-03-19 01:04:49 | 100.00 |
    |      2 |       1 |           2 | 2011-03-19 01:04:49 | 200.00 |
    |      3 |       1 |           3 | 2011-03-19 01:04:49 | 300.00 |
    |      4 |       2 |           3 | 2011-03-19 01:04:49 | 400.00 |
    |      5 |       2 |           2 | 2011-03-19 01:04:49 | 500.00 |
    |      6 |       3 |           1 | 2011-03-19 01:04:49 | 600.00 |
    +--------+---------+-------------+---------------------+--------+
    6 rows in set (0.00 sec)

    mysql> select * from payments_view order by pay_id desc;
    +--------+---------------------+-------------+---------------+--------+---------+----------------+
    | pay_id | pay_date            | pay_type_id | pay_type_name | amount | cust_id| email          |
    +--------+---------------------+-------------+---------------+--------+---------+----------------+
    |      6 | 2011-03-19 01:04:49 |           1 | visa          | 600.00 |       3| pants@elis.com |
    |      5 | 2011-03-19 01:04:49 |           2 | mastercard    | 500.00 |       2| bar@foo.com    |
    |      4 | 2011-03-19 01:04:49 |           3 | cash          | 400.00 |       2| bar@foo.com    |
    |      3 | 2011-03-19 01:04:49 |           3 | cash          | 300.00 |       1| foo@bar.com    |
    |      2 | 2011-03-19 01:04:49 |           2 | mastercard    | 200.00 |       1| foo@bar.com    |
    |      1 | 2011-03-19 01:04:49 |           1 | visa          | 100.00 |       1| foo@bar.com    |
    +--------+---------------------+-------------+---------------+--------+---------+----------------+
    6 rows in set (0.00 sec)