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
       )
)
SELECT *
FROM 
(
    SELECT 
        TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD') AS tgl_tarik_data,
        TO_CHAR(CURRENT_DATE, 'YYYY-MM') AS Bulan_tarik_data,
        TO_CHAR(ca.tanggal_pendaftaran, 'YYYY-MM-DD') as Tanggal_pendaftaran, 
        ca.value as No_anggota, 
        ca.nama as Nama_anggota, 
        ckp.nama_perusahaan as AFFCO, 
        ca.npk, 
        TO_CHAR(ca.tanggal_lahir, 'YYYY-MM-DD') as Tanggal_lahir, 
        EXTRACT(YEAR FROM AGE(CURRENT_DATE, ca.tanggal_lahir)) AS current_usia,
        TO_CHAR(ca.tanggal_masuk_kerja, 'YYYY-MM-DD') as Tanggal_masuk_kerja, 
        ckg.kode_golongan as Golonga_kopastra, 
        ckg2.kode_golongan as Golongan_affco,
        TO_CHAR(cca.tgl_masuk_koperasi, 'YYYY-MM-DD') as Tanggal_masuk_koperasi, 
        ca.pinjaman_coresys, 
        ca.simpanan_coresys,
        CASE 
            WHEN ca.cms_anggota_id IN (SELECT x.cms_anggota_id FROM cms_kartupinjaman x WHERE x.statusangsuran IN  ('AC', 'EP')) 
                 AND ca.cms_anggota_id IN (SELECT x.cms_anggota_id FROM cms_simpanan x WHERE x.status_simpanan = 'Y') THEN 'Member Loan Deposit'
            WHEN ca.cms_anggota_id IN (SELECT x.cms_anggota_id FROM cms_kartupinjaman x WHERE x.statusangsuran IN ('AC', 'EP'))  
                 AND ca.cms_anggota_id IN (SELECT x.cms_anggota_id FROM cms_simpanan x WHERE x.status_simpanan = 'N')  THEN 'Member Loan'
            WHEN ca.cms_anggota_id IN (SELECT x.cms_anggota_id FROM cms_kartupinjaman x WHERE x.statusangsuran IN ('AC', 'EP'))  THEN 'Member Loan'
            WHEN ca.cms_anggota_id IN (SELECT x.cms_anggota_id FROM cms_kartupinjaman x WHERE x.statusangsuran NOT IN ('AC', 'EP'))
                 AND ca.cms_anggota_id IN (SELECT x.cms_anggota_id FROM cms_simpanan x WHERE x.status_simpanan = 'Y') THEN 'Member Deposit'
            WHEN ca.cms_anggota_id IN (SELECT x.cms_anggota_id FROM cms_simpanan x WHERE x.status_simpanan = 'Y') THEN 'Member Deposit'
            WHEN ca.cms_anggota_id NOT IN (SELECT x.cms_anggota_id FROM cms_kartupinjaman x WHERE x.statusangsuran IN ('AC', 'EP'))
                 AND ca.cms_anggota_id NOT IN (SELECT x.cms_anggota_id FROM cms_simpanan x WHERE status_simpanan = 'Y') 
                 AND ca.pinjaman_coresys = 'Y' AND ca.simpanan_coresys = 'Y' THEN 'Sleeping Member Aktif'
   		    WHEN ca.cms_anggota_id NOT IN (SELECT x.cms_anggota_id FROM cms_kartupinjaman x WHERE x.statusangsuran IN ('AC', 'EP'))
                 AND ca.cms_anggota_id NOT IN (SELECT x.cms_anggota_id FROM cms_simpanan x WHERE status_simpanan = 'Y') 
                 AND ca.pinjaman_coresys = 'Y' AND ca.simpanan_coresys = 'N' THEN 'Sleeping Member Aktif'
   	        WHEN ca.cms_anggota_id NOT IN (SELECT x.cms_anggota_id FROM cms_kartupinjaman x WHERE x.statusangsuran IN ('AC', 'EP'))
                 AND ca.cms_anggota_id NOT IN (SELECT x.cms_anggota_id FROM cms_simpanan x WHERE status_simpanan = 'Y') 
                 AND ca.pinjaman_coresys = 'N' AND ca.simpanan_coresys = 'Y' THEN 'Sleeping Member Aktif'
            WHEN ca.cms_anggota_id NOT IN (SELECT x.cms_anggota_id FROM cms_kartupinjaman x WHERE x.statusangsuran IN ('AC', 'EP'))
                 AND ca.cms_anggota_id NOT IN (SELECT x.cms_anggota_id FROM cms_simpanan x WHERE status_simpanan = 'Y') 
                 AND ca.pinjaman_coresys = 'N' AND ca.simpanan_coresys = 'N' 
                 AND ca.cms_anggota_id IN (SELECT x.cms_anggota_id FROM cms_kartupinjaman x WHERE x.statusangsuran NOT IN ('AC', 'EP'))
                 AND ca.cms_anggota_id IN (SELECT x.cms_anggota_id FROM cms_simpanan x WHERE status_simpanan = 'N') 
                 THEN 'Sleeping Member Aktif'
            WHEN ca.cms_anggota_id NOT IN (SELECT x.cms_anggota_id FROM cms_kartupinjaman x WHERE x.statusangsuran IN ('AC', 'EP'))
                 AND ca.cms_anggota_id NOT IN (SELECT x.cms_anggota_id FROM cms_simpanan x WHERE status_simpanan = 'Y') 
                 AND ca.pinjaman_coresys = 'N' AND ca.simpanan_coresys = 'N' 
                 AND ca.cms_anggota_id IN (SELECT x.cms_anggota_id FROM cms_kartupinjaman x WHERE x.statusangsuran NOT IN ('AC', 'EP'))
                 THEN 'Sleeping Member Aktif'
            WHEN ca.cms_anggota_id NOT IN (SELECT x.cms_anggota_id FROM cms_kartupinjaman x WHERE x.statusangsuran IN ('AC', 'EP'))
                 AND ca.cms_anggota_id NOT IN (SELECT x.cms_anggota_id FROM cms_simpanan x WHERE status_simpanan = 'Y') 
                 AND ca.pinjaman_coresys = 'N' AND ca.simpanan_coresys = 'N' 
                 AND ca.cms_anggota_id IN (SELECT x.cms_anggota_id FROM cms_simpanan x WHERE status_simpanan = 'N') 
                 THEN 'Sleeping Member Aktif'
            WHEN ca.cms_anggota_id NOT IN (SELECT x.cms_anggota_id FROM cms_kartupinjaman x WHERE x.statusangsuran IN ('AC', 'EP'))
                 AND ca.cms_anggota_id NOT IN (SELECT x.cms_anggota_id FROM cms_simpanan x WHERE status_simpanan = 'Y') 
                 AND ca.pinjaman_coresys = 'N' AND ca.simpanan_coresys = 'N' 
                 AND ca.cms_anggota_id NOT IN (SELECT x.cms_anggota_id FROM cms_kartupinjaman x)
                 AND ca.cms_anggota_id NOT IN (SELECT x.cms_anggota_id FROM cms_simpanan x) 
                 THEN 'Sleeping Member Pasif'
            END as Kategori_anggota, 
            CASE 
                WHEN EXISTS (SELECT 1 FROM cms_kartupinjaman ck_sub WHERE ck_sub.cms_anggota_id = ca.cms_anggota_id AND ck_sub.statusangsuran NOT IN ('AC','EP','IA'))
                     AND ca.pinjaman_coresys = 'N' THEN 'Sleeping Loan - Active' 
                WHEN NOT EXISTS (SELECT 1 FROM cms_kartupinjaman ck_sub WHERE ck_sub.cms_anggota_id = ca.cms_anggota_id)
                     AND ca.pinjaman_coresys = 'Y' THEN 'Sleeping Loan - Active' 
                WHEN EXISTS (SELECT 1 FROM cms_kartupinjaman ck_sub WHERE ck_sub.cms_anggota_id = ca.cms_anggota_id AND ck_sub.statusangsuran NOT IN ('AC','EP','IA'))
                     AND ca.pinjaman_coresys = 'Y' THEN 'Sleeping Loan - Active' 
                WHEN NOT EXISTS (SELECT 1 FROM cms_kartupinjaman ck_sub WHERE ck_sub.cms_anggota_id = ca.cms_anggota_id AND ck_sub.statusangsuran NOT IN ('AC','EP','IA'))
                     AND ca.pinjaman_coresys = 'N' THEN 'Sleeping Loan - Pasif'          
            END AS Type_sleeping_Loan_Member, 
            CASE 
                WHEN EXISTS (SELECT 1 FROM cms_simpanan cs_sub WHERE cs_sub.cms_anggota_id = ca.cms_anggota_id AND cs_sub.status_simpanan != 'Y')
                     AND NOT EXISTS (SELECT 1 FROM cms_simpanan cs_sub WHERE cs_sub.cms_anggota_id = ca.cms_anggota_id AND cs_sub.status_simpanan = 'Y')
                     AND ca.simpanan_coresys = 'N' THEN 'Sleeping Deposit - Active'
                WHEN NOT EXISTS (SELECT 1 FROM cms_simpanan cs_sub WHERE cs_sub.cms_anggota_id = ca.cms_anggota_id) 
                     AND ca.simpanan_coresys = 'Y' THEN 'Sleeping Deposit Active'
                WHEN NOT EXISTS (SELECT 1 FROM cms_simpanan cs_sub WHERE cs_sub.cms_anggota_id = ca.cms_anggota_id AND cs_sub.status_simpanan != 'Y')
                     AND ca.simpanan_coresys = 'N' THEN 'Sleeping Deposit Pasif'
                ELSE NULL 
            END AS Sleeping_deposit_typ,
            lp.pinjamanno, 
            lp.tanggal_cair,
            lp.statusangsuran,
    CASE 
        WHEN lp.statusangsuran = 'PA' THEN COALESCE(ck2.tanggallunas, ck2.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,
    CASE 
        WHEN COALESCE(ck2.tanggallunas, ck2.datedoc) IS NOT NULL THEN 
            EXTRACT(YEAR FROM AGE(CURRENT_DATE, COALESCE(ck2.tanggallunas, ck2.datedoc))) * 12 + 
            EXTRACT(MONTH FROM AGE(CURRENT_DATE, COALESCE(ck2.tanggallunas, ck2.datedoc))) 
        ELSE NULL -- Kosongkan jika tidak ada tanggal lunas
    END AS bulan_terakhir_lunas
    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 cms_keanggotaan_golongan ckg ON ca.cms_keanggotaan_golongan_id = ckg.cms_keanggotaan_golongan_id 
    LEFT JOIN cms_keanggotaan_golongan ckg2 ON ca.cms_perusahaan_golmap_id = ckg2.cms_keanggotaan_golongan_id 
    LEFT OUTER JOIN cms_kartupinjaman ck ON ca.cms_anggota_id = ck.cms_anggota_id AND ck.statusangsuran != 'IA'
    LEFT JOIN cms_kartuangsuran ck2 ON ck2.cms_kartupinjaman_id = ck.cms_kartupinjaman_id AND ck2.line = 10
    LEFT JOIN PrioritizedPinjaman lp ON ca.cms_anggota_id = lp.cms_anggota_id
    WHERE 1=1 
    AND ca.status_anggota IS null
    AND ca.value = @PARAMETER.NO_ANGGOTA@
) x
WHERE x.Kategori_anggota IN  ('Sleeping Member Pasif', 'Sleeping Member Aktif')