drop view if exists view_kinerja_penjualan;

--gopg:split

CREATE VIEW view_kinerja_penjualan AS
SELECT
  orders.anggota_id,
  orders.toko_id,
  tokos.nama_toko,
  anggota.nama_lengkap,
  anggota.foto,
  roles.roles_name,
  sum(
    CASE
      WHEN jenis_barangs.kode = 'LM' :: text THEN inventory_barangs.berat
      ELSE NULL :: double precision
    END
  ) AS total_logam_mulia,
  sum(
    CASE
      WHEN jenis_barangs.kode = 'PE' :: text THEN inventory_barangs.berat
      ELSE NULL :: double precision
    END
  ) AS total_perhiasan_emas,
  sum(
    CASE
      WHEN jenis_barangs.kode = 'PB' :: text THEN inventory_barangs.karat
      ELSE NULL :: double precision
    END
  ) AS total_perhiasan_berlian
FROM
  orders
  LEFT JOIN order_details ON order_details.order_id = orders.id
  LEFT JOIN inventory_barangs ON order_details.inventory_barang_id = inventory_barangs.id
  LEFT JOIN jenis_barangs ON jenis_barangs.id = inventory_barangs.jenis_barang_id
  LEFT JOIN anggota ON anggota.id = orders.anggota_id
  LEFT JOIN roles ON anggota.roles_id = roles.id
  LEFT JOIN tokos ON tokos.id = orders.toko_id
WHERE
  orders.deleted_at IS NULL
  AND orders.deleted_by IS NULL
GROUP BY
  orders.anggota_id,
  orders.toko_id,
  anggota.nama_lengkap,
  anggota.foto,
  roles.roles_name,
  tokos.nama_toko;