//trigger 1
CREATE OR REPLACE FUNCTION transaksi_crt()
  RETURNS TRIGGER
  LANGUAGE PLPGSQL
  AS
$$
BEGIN
    IF NEW.checkout = TRUE THEN
         INSERT INTO transaksi(id_method, id_shipment, id_account, tanggal_transaksi, status, total)
         VALUES(1, 1, NEW.id_account, date(), 0, NEW.total_harga);
    END IF;
    RETURN NEW;
END;
$$

CREATE TRIGGER transaksi_crt AFTER UPDATE ON keranjang
FOR EACH ROW EXECUTE PROCEDURE transaksi_crt();

//trigger2
CREATE OR REPLACE FUNCTION transaksi_stok()
  RETURNS TRIGGER
  LANGUAGE PLPGSQL
  AS
$$
BEGIN
    IF NEW.checkout = TRUE THEN
        Update produk
        SET stok_barang = stok_barang - 1
        FROM produk p
        WHERE p.id_barang in(SELECT id_barang from berisikan b WHERE b.id_cart = NEW.id_cart);
    END IF;
    RETURN NEW;
END;
$$

CREATE TRIGGER transaksi_stok AFTER UPDATE ON keranjang
FOR EACH ROW EXECUTE PROCEDURE transaksi_stok();

//trigger3
CREATE OR REPLACE FUNCTION keranjang_harga()
  RETURNS TRIGGER
  LANGUAGE PLPGSQL
  AS
$$
DECLARE price float := (Select harga from produk p WHERE New.id_barang = p.id_barang);
BEGIN
    Update keranjang k
    SET total_harga = total_harga + price
    WHERE New.id_cart = k.id_cart;
    RETURN NEW;
END;
$$

CREATE TRIGGER keranjang_harga AFTER INSERT ON berisikan
FOR EACH ROW EXECUTE PROCEDURE keranjang_harga();

//trigger4
CREATE OR REPLACE FUNCTION transaksi_promo()
  RETURNS TRIGGER
  LANGUAGE PLPGSQL
  AS
$$
BEGIN
    if OLD.id_transaksi <> NULL AND NEW.id_transaksi <> NULL  then
    Update transaction t
    SET total = total - NEW.diskon
    WHERE t.id_transaksi = NEW.id_transaksi;
    end if;
    RETURN NEW;
END;
$$

CREATE TRIGGER transaksi_promo BEFORE UPDATE of id_transaksi ON promo
FOR EACH ROW EXECUTE PROCEDURE transaksi_promo();


//trigger5
CREATE OR REPLACE FUNCTION transaksi_shipment()
  RETURNS TRIGGER
  LANGUAGE PLPGSQL
  AS
$$
DECLARE tarif_old float := (Select tarif from shipment_method s WHERE OLD.id_shipment = p.id_shipment);
DECLARE tarif_new float := (Select tarif from shipment_method s WHERE NEW.id_shipment = p.id_shipment);
BEGIN
    if NEW.id_shipment <> NULL AND OLD.id_shipment <> NULL then
    Update transaction t
    SET total = total - tarif_old + tarif_new
    WHERE t.id_transaksi = NEW.id_transaksi;
    end if;
    RETURN NEW;
END;
$$

CREATE TRIGGER transaksi_shipment BEFORE UPDATE of id_shipment ON transaction
FOR EACH ROW EXECUTE PROCEDURE transaksi_shipment();


//trigger6
CREATE OR REPLACE FUNCTION transaksi_bonus()
  RETURNS TRIGGER
  LANGUAGE PLPGSQL
  AS
$$
DECLARE total float := (Select sum(harga) from transaksi t WHERE New.id_account = t.id_account);
DECLARE id_promo int := (SELECT max(id_promo) from promo);
BEGIN
    if total > 500000 then
    INSERT INTO promo (tanggal_mulai, tanggal_akhir, diskon) values(now() , now()+7, 0.1*total);
    INSERT INTO mengklaim (id_account, id_promo) values(NEW.id_account, id_promo);
    end if;
    RETURN NEW;
END;
$$

CREATE TRIGGER transaksi_bonus AFTER INSERT OR UPDATE ON transaction
FOR EACH ROW EXECUTE PROCEDURE transaksi_bonus();


//trigger7
CREATE OR REPLACE FUNCTION shop_star()
  RETURNS TRIGGER
  LANGUAGE PLPGSQL
  AS
$$
DECLARE tmp_toko int := (
    SELECT id_toko FROM shop_account s, produk pr
    WHERE NEW.id_barang = pr.id_barang AND pr.id_toko = s.id_toko
);

DECLARE total_barang int := (
    Select count(me.id_barang) from menampung me
    WHERE me.id_barang in(SELECT id_barang from produk pro WHERE pro.id_toko = tmp_toko)
);

DECLARE rating_produk float := (
    SELECT average(bintang) FROM review r
    WHERE r.id_barang in (SELECT id_barang from produk pro WHERE pro.id_toko = tmp_toko)
);

BEGIN
    if total_barang > 5 AND rating_produk > 4 then
    UPDATE shop_account
    SET star = 1
    WHERE shop_account.id_toko = tmp_toko;
    end if;
    RETURN NEW;
END;
$$

CREATE TRIGGER shop_star AFTER INSERT ON menampung
FOR EACH ROW EXECUTE PROCEDURE shop_star();


//query 1
SELECT * from transaction t
JOIN account ac on (t.id_account = ac.id_account)
WHERE t.id_account in(
    SELECT id_account FROM account WHERE nama_account = 'aldo'
);


//query 2
SELECT pr.id_barang, nama_barang, AVG(bintang) AS rata2
FROM((SELECT id_barang, bintang FROM review) as r
        JOIN (SELECT id_barang, nama_barang FROM produk) as pr ON (r.id_barang = pr.id_barang) )
GROUP BY pr.id_barang, pr.nama_barang
HAVING AVG(bintang) > 4;


//query 3
SELECT pr.id_barang, pr.nama_barang FROM ((SELECT id_barang, nama_barang, id_toko FROM produk) as pr
        JOIN (SELECT id_toko, nama_toko FROM shop_account) as s ON (pr.id_toko = s.id_toko))
WHERE s.nama_toko = 'sanjaya store';


//query 4
SELECT ac.id_account, ac.nama_account FROM ((SELECT id_account, nama_account FROM account) as ac
        JOIN (SELECT id_account, id_method FROM transaction) as tr ON (tr.id_account = ac.id_account)
                JOIN (SELECT id_method, nama_pembayaran FROM payment_method) as pa ON (tr.id_method = pa.id_method))
WHERE pa.nama_pembayaran = 'ovo';


//query 5
SELECT ac.id_account, ac.nama_account, SUM(total) AS totalharga FROM ((SELECT id_account, nama_account FROM account) as ac
        JOIN (SELECT id_account, total FROM transaction) as tr ON (tr.id_account = ac.id_account))
GROUP BY ac.id_account, ac.nama_account
HAVING SUM(total) > 500000;


indexing
CREATE INDEX index_id_account
ON account(id_account);

CREATE INDEX index_id_transaction
ON transaction(id_transaksi);

CREATE INDEX index_id_barang
ON produk(id_barang);

CREATE INDEX index_id_toko
ON shop_account(id_toko);

--testing trigger 3
SELECT * FROM keranjang;

--testing trigger 1
UPDATE keranjang
set checkout = true
where id_cart = 1;
SELECT * FROM transaction;

--testing trigger 2
SELECT * FROM produk;

--testing trigger 6
INSERT INTO berisikan values(1,3);
SELECT * FROM promo;
SELECT * FROM mengklaim;

--testing trigger 4
update promo
set id_transaksi = null
where id_promo = 3;

SELECT * FROM promo;

update promo
set id_transaksi = 6
where id_promo = 3;
SELECT * FROM transaction;

--testing trigger 5
update transaction
set id_shipment = 2
where id_transaksi = 11;

SELECT * FROM transaction;

--testing trigger 6
INSERT INTO menampung values(6, 1);
INSERT INTO menampung values(6, 3);

SELECT * from shop_account;