WITH PrioritizedPinjaman AS (
    SELECT 
        cms_anggota_id,
        cms_kartupinjaman_id,
        pinjamanno,
        statusangsuran,
        COALESCE(dateinvoiced, datereceived) AS tanggal_cair,
        ROW_NUMBER() OVER (
            PARTITION BY cms_anggota_id 
            ORDER BY 
                CASE 
                    WHEN statusangsuran = 'AC' THEN 1 -- Prioritaskan pinjaman aktif
                    ELSE 2 -- Pinjaman lainnya
                END,
                GREATEST(dateinvoiced, datereceived) DESC -- Urutkan berdasarkan tanggal cair termuda
        ) AS rn
    FROM cms_kartupinjaman
    WHERE statusangsuran = 'AC' 
       OR cms_kartupinjaman_id IN (
           SELECT cms_kartupinjaman_id 
           FROM cms_kartuangsuran 
           WHERE tanggallunas IS NOT NULL 
              OR datedoc IS NOT NULL
       )
),
PinjamanCount AS (
    SELECT 
        cms_anggota_id,
        COUNT(CASE WHEN statusangsuran = 'AC' THEN 1 END) AS jumlah_pinjaman_aktif,
        COUNT(CASE WHEN statusangsuran != 'IA' THEN 1 END) AS jumlah_pinjaman_total
    FROM cms_kartupinjaman
    GROUP BY cms_anggota_id
)

SELECT 
    'Anggota Referator' AS kategori,
    TO_CHAR(CURRENT_DATE + INTERVAL '1 day', 'YYYY-MM-DD') AS tgl_tarik_data,
    ca.value AS no_anggota,
    ckp.nama_perusahaan AS Affco,
    ca.tanggal_masuk_kerja::DATE AS tgl_Kartap,
    cca.tgl_masuk_koperasi::DATE AS tgl_masuk_koperasi,
    lp.pinjamanno, 
    lp.tanggal_cair,
    lp.statusangsuran,
    CASE 
        WHEN lp.statusangsuran = 'PA' THEN COALESCE(ck.tanggallunas, ck.datedoc) -- Ambil tanggal lunas jika status PA
        WHEN lp.statusangsuran = 'AC' THEN NULL -- Kosongkan jika status AC
        ELSE NULL -- Kosongkan untuk status lainnya jika diperlukan
    END AS tanggal_lunas_terakhir, -- Kolom ini ambil sesuai logika yang diinginkan
    CASE 
        WHEN COALESCE(ck.tanggallunas, ck.datedoc) IS NOT NULL THEN 
            EXTRACT(YEAR FROM AGE(CURRENT_DATE, COALESCE(ck.tanggallunas, ck.datedoc))) * 12 + 
            EXTRACT(MONTH FROM AGE(CURRENT_DATE, COALESCE(ck.tanggallunas, ck.datedoc))) 
        ELSE NULL -- Kosongkan jika tidak ada tanggal lunas
    END AS bulan_terakhir_lunas, -- Menghitung selisih bulan dari tanggal lunas terakhir (baik tanggallunas atau datedoc)
    pc.jumlah_pinjaman_aktif,  -- Jumlah pinjaman aktif untuk masing-masing anggota
    pc.jumlah_pinjaman_total     -- Jumlah pinjaman total untuk masing-masing anggota
FROM cms_anggota ca
LEFT JOIN cms_calon_anggota cca 
    ON ca.cms_anggota_id = cca.cms_anggota_id
LEFT JOIN cms_keanggotaan_perusahaan ckp 
    ON ca.cms_keanggotaan_perusahaan_id = ckp.cms_keanggotaan_perusahaan_id
LEFT JOIN PrioritizedPinjaman lp 
    ON ca.cms_anggota_id = lp.cms_anggota_id 
    AND lp.rn = 1 -- Ambil data prioritas pertama (pinjaman aktif atau pinjaman dengan tanggal cair terbaru)
LEFT JOIN cms_kartuangsuran ck 
    ON lp.cms_kartupinjaman_id = ck.cms_kartupinjaman_id
LEFT JOIN PinjamanCount pc
    ON ca.cms_anggota_id = pc.cms_anggota_id -- Menghubungkan dengan jumlah pinjaman berdasarkan anggota
WHERE 1=1
    and ca.status_anggota IS NULL
    AND lp.pinjamanno IS NOT null
    and ca.value = @PARAMETER.NO_ANGGOTA@
   AND (
       (EXTRACT(YEAR FROM AGE(CURRENT_DATE, COALESCE(ck.tanggallunas, ck.datedoc))) * 12 + 
       EXTRACT(MONTH FROM AGE(CURRENT_DATE, COALESCE(ck.tanggallunas, ck.datedoc))) <= (
           SELECT value::INTEGER FROM AD_SysConfig WHERE name = 'CMS_REFERATOR_BATAS_AKTIF'
       ))
       OR COALESCE(
           EXTRACT(YEAR FROM AGE(CURRENT_DATE, COALESCE(ck.tanggallunas, ck.datedoc))) * 12 + 
           EXTRACT(MONTH FROM AGE(CURRENT_DATE, COALESCE(ck.tanggallunas, ck.datedoc))),
           NULL
       ) IS NULL -- Validasi bahwa bulan tidak lebih dari batas atau bernilai NULL
   );