# $1677. 发票中的产品金额
# https://leetcode.cn/problems/products-worth-over-invoices/

# SQL架构
Create table If Not Exists Product(product_id int, name varchar(15));
Create table If Not Exists Invoice(invoice_id int,product_id int,rest int, paid int, canceled int, refunded int);
Truncate table Product;
insert into Product (product_id, name) values ('0', 'ham');
insert into Product (product_id, name) values ('1', 'bacon');
Truncate table Invoice;
insert into Invoice (invoice_id, product_id, rest, paid, canceled, refunded) values ('23', '0', '2', '0', '5', '0');
insert into Invoice (invoice_id, product_id, rest, paid, canceled, refunded) values ('12', '0', '0', '4', '0', '3');
insert into Invoice (invoice_id, product_id, rest, paid, canceled, refunded) values ('1', '1', '1', '1', '0', '1');
insert into Invoice (invoice_id, product_id, rest, paid, canceled, refunded) values ('2', '1', '1', '0', '1', '1');
insert into Invoice (invoice_id, product_id, rest, paid, canceled, refunded) values ('3', '1', '0', '1', '1', '1');
insert into Invoice (invoice_id, product_id, rest, paid, canceled, refunded) values ('4', '1', '1', '1', '1', '0');

# Write your MySQL query statement below
select
    p.name,
    ifnull(sum(i.rest), 0) as rest,
    ifnull(sum(i.paid), 0) as paid,
    ifnull(sum(i.canceled), 0) as canceled,
    ifnull(sum(i.refunded), 0) as refunded
from
    Product p
    left join Invoice i on p.product_id = i.product_id
group by
    p.product_id,
    p.name
order by
    p.name;

# clean-up
drop table Product;
drop table Invoice;