-- 1. Total Debit and Credit in ALL journal entries
SELECT 'TOTAL_ALL', COUNT(*) as count, SUM(debit) as total_debit, SUM(kredit) as total_kredit, SUM(debit) - SUM(kredit) as diff FROM jurnal_entry;

-- 2. Are there any orphan journal entries (not linked to detail_transaksi or transaksi)?
SELECT 'ORPHANS', COUNT(*) as count, SUM(je.debit) as total_debit, SUM(je.kredit) as total_kredit
FROM jurnal_entry as je
LEFT JOIN detail_transaksi as dt ON je.transaksi_detail_id = dt.id
LEFT JOIN transaksi as t ON dt.transaksi_id = t.id
WHERE t.id IS NULL;

-- 3. Are there journal entries with accounts not having 'Aset', 'Liabilitas', 'Ekuitas', 'Pendapatan', 'Beban'?
SELECT a.tipe_akun, COUNT(*) as count, SUM(je.debit) as total_debit, SUM(je.kredit) as total_kredit
FROM jurnal_entry as je
JOIN akun as a ON je.akun_id = a.kode_akun
WHERE a.tipe_akun NOT IN ('Aset', 'Liabilitas', 'Ekuitas', 'Pendapatan', 'Beban') OR a.tipe_akun IS NULL
GROUP BY a.tipe_akun;

-- 4. Missing accounts in jurnal?
SELECT 'MISSING_AKUN', COUNT(*) as count, SUM(je.debit) as total_debit, SUM(je.kredit) as total_kredit
FROM jurnal_entry as je
LEFT JOIN akun as a ON je.akun_id = a.kode_akun
WHERE a.kode_akun IS NULL;

-- 5. Total Balance grouped by Account Type (Including orphans)
SELECT 'ALL_JURNAL', a.tipe_akun, SUM(je.debit) as total_debit, SUM(je.kredit) as total_kredit
FROM jurnal_entry as je
JOIN akun as a ON je.akun_id = a.kode_akun
GROUP BY a.tipe_akun;

-- 6. Total Balance grouped by Account Type (Excluding orphans - via join)
SELECT 'VALID_JURNAL', a.tipe_akun, SUM(je.debit) as total_debit, SUM(je.kredit) as total_kredit
FROM jurnal_entry as je
JOIN akun as a ON je.akun_id = a.kode_akun
JOIN detail_transaksi as dt ON je.transaksi_detail_id = dt.id
JOIN transaksi as t ON dt.transaksi_id = t.id
GROUP BY a.tipe_akun;

-- 7. Saldo directly from 'akun' table (might be initial balance)
SELECT 'SALDO_AKUN_TABLE', tipe_akun, SUM(saldo) as total_saldo
FROM akun
WHERE tipe_akun IN ('Aset', 'Liabilitas', 'Ekuitas')
GROUP BY tipe_akun;