SELECT a.unique_id no_pelanggan, a.nama nama_pelanggan, b.alamat_pemasangan alamat, a.nomor_wa, e.email, a.nomor_hp, b.unique_id id_pemasangan, b.status status_pemasangan, b.keterangan keterangan_pemasangan, c.unique_id no_tagihan, TO_CHAR(c.tgl_bayar, 'DD Month YYYY') tgl_bayar, TO_CHAR(c.tgl_jatuh_tempo, 'DD Month YYYY') tgl_jatuh_tempo, COALESCE(c.nominal, d.harga) AS nominal, CASE WHEN c.status_bayar = 'terbayar' THEN 'Lunas' ELSE 'Belum Lunas' END AS status_bayar, c.payment_method, c.keterangan keterangan_bayar, d.nama layanan FROM pelanggan a
    JOIN pemasangan b ON a.id = b.pelanggan_id
    JOIN pembayaran c ON b.id = c.pemasangan_id
    JOIN layanan d ON b.layanan_id = d.id
    JOIN users e ON a.user_id = e.id
    WHERE c.status_bayar = 'pending' and c.reminder_wa_sent = 'false' and c.tgl_jatuh_tempo is not null and date(c.tgl_jatuh_tempo) =  CURRENT_DATE
    AND TO_CHAR(c.tgl_jatuh_tempo, 'Month') = TO_CHAR(current_date, 'Month') and b.tgl_jatuh_tempo not IN (1, 5, 25)
    and e.main_role = 'pelanggan' and e.status = 'true' and e.blasted_wa = 'true' AND a.nomor_wa != ''
    order BY c.created_at DESC limit 1