// NOMER 2
CREATE VIEW view_produk
AS
SELECT p.id, p.nama AS nama_produk, p.harga AS harga, k.nama AS kategori
FROM PRODUK AS p
INNER JOIN kategori AS k ON p.id_kategori = k.id

SELECT * FROM view_produk;

// NOMER 3

CREATE VIEW penjualan_produk
AS
SELECT nama_produk, harga_satuan, SUM(jumlah), harga_satuan * SUM(jumlah) AS total_pendapatan FROM belanjaan GROUP BY nama_produk

SELECT * FROM penjualan_produk;

// NOMER 4

CREATE VIEW jumlah_belanja_pembeli
AS
SELECT nama_pembeli, COUNT(nama_pembeli), SUM(total) FROM struk GROUP BY nama_pembeli

SELECT * FROM jumlah_belanja_pembeli

// NOMER 5

SELECT npwp, nama, total, total * 0.1 AS ppn FROM (
    SELECT
            a.npwp, a.nama, s.total
    FROM
            admin_toko AS a
    INNER JOIN struk AS s ON
            a.id = s.id_admin
) GROUP BY nama ORDER BY total DESC LIMIT 1;

// NOMER 6

SELECT id, nama, harga AS harga_satuan, SUM(jumlah) AS total_penjualan, harga * SUM(jumlah) AS total_pendapatan FROM (
    SELECT
            p.id, p.nama, p.harga, b.jumlah
    FROM
            produk AS p
    INNER JOIN belanjaan AS b ON
            p.id = b.id_produk
) GROUP BY nama ORDER BY total_penjualan DESC LIMIT 1;

// NOMER 7
SELECT nama_pembeli, COUNT(nama_pembeli) AS jumlah_belanja, SUM(total) AS total_pembelanjaan FROM (
    SELECT
            *
    FROM struk
) GROUP BY nama_pembeli ORDER BY jumlah_belanja DESC LIMIT 1;

// nomer 8

CREATE TRIGGER pengurangan_stock_produk_update
    AFTER INSERT ON belanjaan
BEGIN
    UPDATE produk SET stock = stock - NEW.jumlah WHERE id = NEW.id_produk;
END

INSERT INTO belanjaan ("id", "id_produk", "nama_produk", "jumlah", "harga_satuan", "total_bayar", "id_struk") VALUES (10, 2, 'Sticker Wajah', 100, '400000', 4000, '5')

SELECT * FROM PRODUK

// nomer 9

CREATE TRIGGER log_perubahan_harga_produk
    AFTER UPDATE ON produk
BEGIN
    INSERT INTO histori_harga_produk(nama_produk, tanggal_perubahan, harga_awal, harga_akhir)
    VALUES (NEW.nama_produk, NOW(), OLD.harga, NEW.harga);
END

// nomer 10

CREATE TRIGGER hitung_subtotal_keranjang
    AFTER INSERT ON belanjaan
BEGIN
    UPDATE belanjaan SET total_bayar = NEW.jumlah * NEW.harga_satuan WHERE id_produk;
END

// nomer 11

CREATE TRIGGER update_penghasilan
    AFTER INSERT ON struk
BEGIN
    UPDATE admin_toko SET total_pendapatan = total_pendapatan + NEW.total, total_ppn = total_ppn + (NEW.total * 0.1)
    WHERE id = NEW.id_admin;
END