drop view if exists list_kinerja_penjualan;

--gopg:split

CREATE VIEW list_kinerja_penjualan AS SELECT
    transaksi_pesanan_customs.anggota_id,
    transaksi_pesanan_customs.toko_id,
    tokos.nama_toko,
    anggota.nama_lengkap,
    anggota.foto,
    roles.roles_name,
    jenis_barangs.kode,
    transaksi_pesanan_customs.berat,
    CASE
        WHEN jenis_barangs.kode = 'PB' :: text THEN transaksi_pesanan_customs.berat
        ELSE transaksi_pesanan_customs.karat
    END AS karat,
    transaksi_pesanan_customs.tgl_pesan AS created_at,
    transaksi_pesanan_customs.no_invoice,
    transaksi_pesanan_customs.nama_produk AS nama_barang
FROM
    transaksi_pesanan_customs
    LEFT JOIN jenis_barangs ON jenis_barangs.id = transaksi_pesanan_customs.jenis_barang_id
    LEFT JOIN anggota ON anggota.id = transaksi_pesanan_customs.anggota_id
    LEFT JOIN roles ON roles.id = anggota.roles_id
    LEFT JOIN tokos ON tokos.id = transaksi_pesanan_customs.toko_id
WHERE
    transaksi_pesanan_customs.deleted_at IS NULL
    AND transaksi_pesanan_customs.deleted_by IS NULL
UNION
ALL
SELECT
    orders.anggota_id,
    orders.toko_id,
    tokos.nama_toko,
    anggota.nama_lengkap,
    anggota.foto,
    roles.roles_name,
    jenis_barangs.kode,
    inventory_barangs.berat,
    CASE
        WHEN jenis_barangs.kode = 'PB' :: text THEN inventory_barangs.berat
        ELSE inventory_barangs.karat
    END AS karat,
    orders.tanggal_jual AS created_at,
    orders.no_invoice,
    order_details.nama_barang
FROM
    orders
    LEFT JOIN order_details ON order_details.order_id = orders.id
    LEFT JOIN inventory_barangs ON inventory_barangs.id = order_details.inventory_barang_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 roles.id = anggota.roles_id
    LEFT JOIN tokos ON tokos.id = orders.toko_id
WHERE
    orders.deleted_at IS NULL
    AND orders.deleted_by IS NULL;