drop view if exists view_transaksi_penjualan;

--gopg:split

CREATE VIEW view_transaksi_penjualan AS
SELECT
  'Penjualan' :: text AS jenis_transaksi,
  orders.id,
  orders.anggota_id,
  orders.toko_id,
  tokos.nama_toko,
  orders.relasi_id,
  relasis.nama_lengkap,
  relasis.no_hp,
  array_agg(jenis_barangs.nama) AS jenis_barang,
  orders.no_invoice,
  orders.total_harga,
  CASE
    WHEN orders.pembayaran = 'cicil' :: text
    AND orders.status_transaksi = 'belum lunas' :: text THEN 'cicil' :: text
    WHEN orders.pembayaran = 'cicil' :: text
    AND orders.status_transaksi = 'lunas' :: text THEN 'lunas' :: text
    ELSE orders.pembayaran
  END AS pembayaran,
  orders.potongan,
  orders.type_pembayaran AS metode_pembayaran,
  orders.tanggal_jual AS created_at,
  orders.tunai_nominal,
  orders.transfer_nominal,
  orders.edc_nominal,
  orders.deleted_at,
  orders.deleted_by
FROM
  orders
  LEFT JOIN relasis ON relasis.id = orders.relasi_id
  LEFT JOIN tokos ON tokos.id = orders.toko_id
  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
WHERE
  orders.deleted_at IS NULL
  AND orders.deleted_by IS NULL
GROUP BY
  orders.tunai_nominal,
  orders.id,
  orders.transfer_nominal,
  orders.edc_nominal,
  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,
  orders.tanggal_jual,
  orders.status_transaksi
UNION
SELECT
  'Penjualan Kustom' :: text AS jenis_transaksi,
  transaksi_pesanan_customs.id,
  transaksi_pesanan_customs.anggota_id,
  transaksi_pesanan_customs.toko_id,
  tokos.nama_toko,
  transaksi_pesanan_customs.relasi_id,
  relasis.nama_lengkap,
  relasis.no_hp,
  array_agg(jenis_barangs.nama) AS jenis_barang,
  transaksi_pesanan_customs.no_invoice,
  CASE
    WHEN (
      (
        SELECT
          count(order_details.inventory_barang_id) AS count
        FROM
          order_details
        WHERE
          order_details.inventory_barang_id = transaksi_pesanan_customs.id
      )
    ) = 1
    AND transaksi_pesanan_customs.status = 'terjual' :: text THEN transaksi_pesanan_customs.dp
    WHEN (
      (
        SELECT
          count(order_details.inventory_barang_id) AS count
        FROM
          order_details
        WHERE
          order_details.inventory_barang_id = transaksi_pesanan_customs.id
      )
    ) = 0 :: bigint
    AND transaksi_pesanan_customs.status = 'terjual' :: text THEN transaksi_pesanan_customs.total_bayar
    ELSE transaksi_pesanan_customs.dp
  END AS total_harga,
  CASE
    WHEN transaksi_pesanan_customs.status = 'terjual' :: text THEN 'lunas' :: text
    ELSE transaksi_pesanan_customs.status
  END 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,
  transaksi_pesanan_customs.deleted_at,
  transaksi_pesanan_customs.deleted_by
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
  LEFT JOIN jenis_barangs ON jenis_barangs.id = transaksi_pesanan_customs.jenis_barang_id
WHERE
  transaksi_pesanan_customs.deleted_at IS NULL
  AND transaksi_pesanan_customs.deleted_by IS NULL
GROUP BY
  transaksi_pesanan_customs.tunai_nominal,
  transaksi_pesanan_customs.id,
  transaksi_pesanan_customs.transfer_nominal,
  transaksi_pesanan_customs.edc_nominal,
  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.total_bayar,
  transaksi_pesanan_customs.status_transaksi,
  transaksi_pesanan_customs.potongan,
  transaksi_pesanan_customs.type_pembayaran,
  transaksi_pesanan_customs.tgl_pesan,
  transaksi_pesanan_customs.status,
  transaksi_pesanan_customs.dp;