drop view if exists view_transactions;

--gopg:split
CREATE VIEW view_transactions AS
SELECT
    'order_details' :: text AS jenis_transaksi,
    o.toko_id,
    od.inventory_barang_id AS id,
    od.nama_barang,
    od.berat,
    od.harga_produk,
    od.harga_jual,
    o.tanggal_jual AS created_at
FROM
    orders o
    LEFT JOIN order_details od ON od.order_id = o.id
    LEFT JOIN inventory_barangs ib ON od.inventory_barang_id = ib.id
WHERE
    o.deleted_at IS NULL
    AND o.deleted_by IS NULL
    AND NOT (
        LOWER(od.nama_barang) NOT LIKE '%logam mulia%'
        OR LOWER(od.nama_barang) NOT LIKE '%lm%'
        OR LOWER(od.nama_barang) NOT LIKE '%berlian%'
        OR ib.jenis_barang_id != 64
    )
    
UNION
SELECT
    'transaksi_pembelians'::text AS jenis_transaksi,
    tp.toko_id,
    tp.inventory_barang_id AS id,
    tp.nama_barang,
    tp.berat_produk AS berat,
    tp.harga_produk,
    0 AS harga_jual,
    tp.tanggal_beli AS created_at
FROM
    transaksi_pembelians tp
    LEFT JOIN inventory_barangs ib ON ib.id = tp.inventory_barang_id
WHERE
    tp.deleted_at IS NULL
    AND tp.deleted_by IS NULL
    AND NOT (
        LOWER(tp.nama_barang) LIKE '%logam mulia%'
        OR LOWER(tp.nama_barang) LIKE '%lm%'
        OR LOWER(tp.nama_barang) LIKE '%berlian%'
        OR ib.jenis_barang_id = 64
    )
UNION
SELECT
    'transaksi_jasas' :: text AS jenis_transaksi,
    tj.toko_id,
    tj.id,
    tj.nama_barang,
    tj.berat,
    tj.total_harga AS harga_produk,
    0 AS harga_jual,
    tj.tgl_pesan AS created_at
FROM
    transaksi_jasas tj
WHERE
    tj.deleted_at IS NULL
    AND tj.deleted_by IS NULL
UNION
SELECT
    'transaksi_pesanan_customs' :: text AS jenis_transaksi,
    tpc.toko_id,
    tpc.id,
    tpc.nama_produk AS nama_barang,
    tpc.berat,
    tpc.total_bayar AS harga_produk,
    0 AS harga_jual,
    tpc.tgl_pesan AS created_at
FROM
    transaksi_pesanan_customs tpc
WHERE
    tpc.deleted_at IS NULL
    AND tpc.deleted_by IS NULL
UNION
SELECT
    'transaksi_keuangans' :: text AS jenis_transaksi,
    tk.toko_id,
    tk.id,
    tk.nama_pengeluaran AS nama_barang,
    tk.berat AS berat,
    tk.jumlah AS harga_produk,
    0 AS harga_jual,
    tk.tanggal_pengeluaran AS created_at
FROM
    transaksi_keuangans tk
WHERE
    tk.deleted_at IS NULL
    AND tk.deleted_by IS NULL
    AND tk.include_in_report = 'yes';