drop view if exists view_laporan_pendapatan;

--gopg:split

CREATE VIEW view_laporan_pendapatan AS
SELECT
  'Pembelian' :: text AS jenis_transaksi,
  relasis.nama_lengkap AS nama_relasi,
  transaksi_pembelians.no_invoice,
  transaksi_pembelians.toko_id,
  transaksi_pembelians.harga_produk AS total_harga,
  transaksi_pembelians.total_pajak,
  transaksi_pembelians.total_bayar,
  transaksi_pembelians.metode_pembayaran AS type_pembayaran,
  0.0 :: double precision AS potongan,
  transaksi_pembelians.tanggal_beli AS created_at,
  transaksi_pembelians.pembayaran AS status
FROM
  transaksi_pembelians
  JOIN relasis ON relasis.id = transaksi_pembelians.relasi_id
WHERE
  transaksi_pembelians.deleted_at IS NULL
  AND transaksi_pembelians.deleted_by IS NULL
UNION
SELECT
  'Penjualan' :: text AS jenis_transaksi,
  relasis.nama_lengkap AS nama_relasi,
  orders.no_invoice,
  orders.toko_id,
  orders.total_harga,
  orders.total_pajak,
  orders.total_bayar,
  orders.type_pembayaran,
  orders.potongan,
  orders.tanggal_jual AS created_at,
  orders.pembayaran AS status
FROM
  orders
  JOIN relasis ON relasis.id = orders.relasi_id
WHERE
  orders.deleted_at IS NULL
  AND orders.deleted_by IS NULL
UNION
SELECT
  'Penjualan Jasa' :: text AS jenis_transaksi,
  relasis.nama_lengkap AS nama_relasi,
  transaksi_jasas.no_invoice,
  transaksi_jasas.toko_id,
  transaksi_jasas.total_harga,
  transaksi_jasas.total_pajak,
  CASE
    WHEN transaksi_jasas.total_pajak IS NULL THEN transaksi_jasas.total_harga
    ELSE transaksi_jasas.total_harga + transaksi_jasas.total_pajak
  END AS total_bayar,
  transaksi_jasas.type_pembayaran,
  transaksi_jasas.potongan,
  transaksi_jasas.tgl_pesan AS created_at,
  transaksi_jasas.jenis_pembayaran AS status
FROM
  transaksi_jasas
  JOIN relasis ON relasis.id = transaksi_jasas.relasi_id
WHERE
  transaksi_jasas.deleted_at IS NULL
  AND transaksi_jasas.deleted_by IS NULL
UNION
SELECT
  'Penjualan Kustom' :: text AS jenis_transaksi,
  relasis.nama_lengkap AS nama_relasi,
  transaksi_pesanan_customs.no_invoice,
  transaksi_pesanan_customs.toko_id,
  transaksi_pesanan_customs.harga_produk AS total_harga,
  transaksi_pesanan_customs.total_pajak,
  transaksi_pesanan_customs.total_bayar,
  transaksi_pesanan_customs.type_pembayaran,
  transaksi_pesanan_customs.potongan,
  transaksi_pesanan_customs.tgl_pesan AS created_at,
  transaksi_pesanan_customs.status_transaksi AS status
FROM
  transaksi_pesanan_customs
  JOIN relasis ON relasis.id = transaksi_pesanan_customs.relasi_id
WHERE
  transaksi_pesanan_customs.deleted_at IS NULL
  AND transaksi_pesanan_customs.deleted_by IS NULL;