drop view if exists view_history_transaksis;

--gopg:split

CREATE VIEW view_history_transaksis AS (
  (
    SELECT
      'Penjualan' :: text AS jenis_transaksi,
      orders.anggota_id,
      orders.toko_id,
      tokos.nama_toko,
      orders.relasi_id,
      relasis.nama_lengkap,
      relasis.no_hp,
      orders.no_invoice,
      orders.total_harga,
      orders.pembayaran,
      orders.potongan,
      orders.type_pembayaran AS metode_pembayaran,
      orders.tanggal_jual AS created_at,
      orders.tunai_nominal,
      orders.transfer_nominal,
      orders.edc_nominal
    FROM
      orders
      LEFT JOIN relasis ON relasis.id = orders.relasi_id
      LEFT JOIN tokos ON tokos.id = orders.toko_id
    WHERE
      orders.deleted_at IS NULL
      AND orders.deleted_by IS NULL
    UNION
    ALL
    SELECT
      'Pembelian' :: text AS jenis_transaksi,
      transaksi_pembelians.anggota_id,
      transaksi_pembelians.toko_id,
      tokos.nama_toko,
      transaksi_pembelians.relasi_id,
      relasis.nama_lengkap,
      relasis.no_hp,
      transaksi_pembelians.no_invoice,
      transaksi_pembelians.harga_produk AS total_harga,
      transaksi_pembelians.pembayaran,
      CASE
        WHEN transaksi_pembelians.metode_pembayaran = 'edc' :: text THEN 1.5
        ELSE 0 :: numeric
      END AS potongan,
      transaksi_pembelians.metode_pembayaran,
      transaksi_pembelians.tanggal_beli AS created_at,
      0 :: numeric AS tunai_nominal,
      0 :: numeric AS transfer_nominal,
      0 :: numeric AS edc_nominal
    FROM
      transaksi_pembelians
      LEFT JOIN relasis ON relasis.id = transaksi_pembelians.relasi_id
      LEFT JOIN tokos ON tokos.id = transaksi_pembelians.toko_id
    WHERE
      transaksi_pembelians.deleted_at IS NULL
      AND transaksi_pembelians.deleted_by IS NULL
    UNION
    ALL
    SELECT
      'Penjualan Jasa' :: text AS jenis_transaksi,
      transaksi_jasas.anggota_id,
      transaksi_jasas.toko_id,
      tokos.nama_toko,
      transaksi_jasas.relasi_id,
      relasis.nama_lengkap,
      relasis.no_hp,
      transaksi_jasas.no_invoice,
      transaksi_jasas.total_harga,
      transaksi_jasas.jenis_pembayaran AS pembayaran,
      transaksi_jasas.potongan,
      transaksi_jasas.type_pembayaran AS metode_pembayaran,
      transaksi_jasas.tgl_pesan AS created_at,
      transaksi_jasas.tunai_nominal,
      transaksi_jasas.transfer_nominal,
      transaksi_jasas.edc_nominal
    FROM
      transaksi_jasas
      LEFT JOIN relasis ON relasis.id = transaksi_jasas.relasi_id
      LEFT JOIN tokos ON tokos.id = transaksi_jasas.toko_id
    WHERE
      transaksi_jasas.deleted_at IS NULL
      AND transaksi_jasas.deleted_by IS NULL
  )
  UNION
  SELECT
    'Penjualan Kustom' :: text AS jenis_transaksi,
    transaksi_pesanan_customs.anggota_id,
    transaksi_pesanan_customs.toko_id,
    tokos.nama_toko,
    transaksi_pesanan_customs.relasi_id,
    relasis.nama_lengkap,
    relasis.no_hp,
    transaksi_pesanan_customs.no_invoice,
    transaksi_pesanan_customs.harga_produk AS total_harga,
    transaksi_pesanan_customs.status_transaksi AS pembayaran,
    transaksi_pesanan_customs.potongan,
    transaksi_pesanan_customs.type_pembayaran AS metode_pembayaran,
    transaksi_pesanan_customs.tgl_pesan AS created_at,
    transaksi_pesanan_customs.tunai_nominal,
    transaksi_pesanan_customs.transfer_nominal,
    transaksi_pesanan_customs.edc_nominal
  FROM
    transaksi_pesanan_customs
    LEFT JOIN relasis ON relasis.id = transaksi_pesanan_customs.relasi_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
  'Keuangan' :: text AS jenis_transaksi,
  0 AS anggota_id,
  transaksi_keuangans.toko_id,
  tokos.nama_toko,
  0 AS relasi_id,
  NULL :: text AS nama_lengkap,
  NULL :: text AS no_hp,
  NULL :: text AS no_invoice,
  transaksi_keuangans.jumlah AS total_harga,
  'lunas' :: text AS pembayaran,
  0 :: double precision AS potongan,
  '' :: text AS metode_pembayaran,
  transaksi_keuangans.tanggal_pengeluaran AS created_at,
  0 :: numeric AS tunai_nominal,
  0 :: numeric AS transfer_nominal,
  0 :: numeric AS edc_nominal
FROM
  transaksi_keuangans
  LEFT JOIN tokos ON tokos.id = transaksi_keuangans.toko_id
WHERE
  transaksi_keuangans.deleted_at IS NULL
  AND transaksi_keuangans.deleted_by IS NULL;