drop view if exists view_transactions;

--gopg:split
alter table
    transaksi_keuangans DROP COLUMN IF EXISTS berat;

--gopg:split
alter table
    transaksi_keuangans DROP COLUMN IF EXISTS include_in_report;

--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
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
WHERE
    tp.deleted_at IS NULL
    AND tp.deleted_by IS NULL
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,
    0 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