import { AppEnv, row, rows, stmt, requireValue, money, clean, auditStmt, financial, isDate } from './core';
import { now, uid } from './seed';
export async function matchBank(e: AppEnv, a: any, b: any) {
    const d = await row(e, 'SELECT * FROM donations WHERE id=?', clean(b.donationId));
    const bank = await row(e, 'SELECT * FROM bank_transactions WHERE id=?', clean(b.bankId));
    requireValue(d && bank, 'Donasi atau transaksi bank tidak ditemukan.');
    requireValue(!['verified', 'refunded'].includes(d.status), 'Pencocokan donasi ini sudah dikunci.');
    const account = await row(e, 'SELECT * FROM bank_account WHERE id=?', bank.account_id);
    requireValue(account?.confirmed && bank.credit > 0 && !['other', 'duplicate_excluded'].includes(bank.scope), 'Pilih kredit pada rekening kampanye yang sesuai.');
    if (d.instructions) requireValue(bank.account_id === `bsi:${JSON.parse(d.instructions).number}`, 'Mutasi tidak berasal dari rekening pada instruksi donasi ini.');
    requireValue(!bank.duplicate_candidate || bank.duplicate_resolved, 'Periksa kandidat duplikat terlebih dahulu.');
    requireValue(b.confirmedSource === true && clean(b.reason).length >= 8, 'Konfirmasi asal dana, peruntukan, dan alasan pencocokan. Nominal sama saja tidak cukup.');
    const amount = money(b.amount);
    const exists = await row(e, 'SELECT * FROM bank_matches WHERE bank_id=? AND donation_id=?', bank.id, d.id);
    if (exists) {
        requireValue(exists.amount === amount, 'Pencocokan sudah ada dengan nominal berbeda.');
        return { ok: true, repeated: true };
    }
    await e.DB.batch([stmt(e, 'INSERT INTO bank_matches(id,bank_id,donation_id,amount,reason,created_by,created_at) VALUES (?,?,?,?,?,?,?)', uid(), bank.id, d.id, amount, clean(b.reason), a.id, now()), stmt(e, "UPDATE bank_transactions SET scope='donation' WHERE id=?", bank.id), auditStmt(e, a.id, 'bank_matched', d.id, `Bank ${bank.id}; alokasi ${amount}; ${clean(b.reason)}`)]);
    return { ok: true };
}
export async function verifyDonation(e: AppEnv, a: any, b: any) {
    const d = await row(e, 'SELECT * FROM donations WHERE id=?', clean(b.id));
    requireValue(d, 'Donasi tidak ditemukan.', 404);
    if (['verified', 'refunded'].includes(d.status))
        return { ok: true, repeated: true };
    requireValue(['waiting_verification', 'expired', 'waiting_payment'].includes(d.status), 'Status ini tidak dapat diverifikasi.');
    requireValue(clean(b.reason).length >= 8 && b.confirmedSource === true, 'Konfirmasi pemeriksaan bank dan tulis alasan verifikasi.');
    const matched = await row(e, 'SELECT COALESCE(SUM(amount),0) amount FROM bank_matches WHERE donation_id=?', d.id);
    requireValue(matched.amount === d.amount, 'Pencocokan bank belum lengkap. Cocokkan penerimaan tepat sebesar nominal donasi.');
    const p = d.program_id ? await row(e, 'SELECT live FROM records WHERE id=?', d.program_id) : null;
    const matchedAccount = await row(e, 'SELECT a.bank,a.number,a.holder,a.confirmed FROM bank_account a JOIN bank_transactions b ON b.account_id=a.id JOIN bank_matches m ON m.bank_id=b.id WHERE m.donation_id=? LIMIT 1', d.id);
    requireValue(matchedAccount?.confirmed, 'Rekening penerimaan belum dikonfirmasi.');
    const bank = d.instructions ? { ...JSON.parse(d.instructions), confirmed: 1 } : matchedAccount;
    const date = new Intl.DateTimeFormat('en-CA', { timeZone: 'Asia/Jakarta', year: 'numeric', month: '2-digit', day: '2-digit' }).format(new Date());
    const snapshot = JSON.stringify({ id: d.id, amount: d.amount, program: p ? JSON.parse(p.live).title : 'Kebutuhan Pembangunan Prioritas', name: d.name, verifiedAt: now(), bank, foundation: 'Yayasan Bina Pelajar Cirebon' });
    await e.DB.batch([stmt(e, "INSERT OR IGNORE INTO ledger(id,kind,amount,program_id,donation_id,source_key,date,description,created_by,created_at) SELECT ?,'receipt',amount,program_id,id,?,?,?, ?,? FROM donations WHERE id=? AND status IN ('waiting_verification','waiting_payment','expired') AND (SELECT COALESCE(SUM(amount),0) FROM bank_matches WHERE donation_id=donations.id)=donations.amount", uid(), `receipt:${d.id}`, date, clean(b.reason), a.id, now(), d.id), stmt(e, "UPDATE donations SET status='verified',verified_at=?,receipt_snapshot=?,reason=? WHERE id=? AND EXISTS(SELECT 1 FROM ledger WHERE source_key=?) AND status IN ('waiting_verification','waiting_payment','expired')", now(), snapshot, clean(b.reason), d.id, `receipt:${d.id}`), auditStmt(e, a.id, 'donation_verified', d.id, clean(b.reason))]);
    return { ok: true };
}
export async function rejectDonation(e: AppEnv, a: any, b: any) { const d = await row(e, 'SELECT * FROM donations WHERE id=?', b.id); requireValue(d && !['verified', 'refunded'].includes(d.status), 'Donasi terverifikasi tidak dapat ditolak atau dihapus.'); requireValue(clean(b.reason).length >= 8, 'Tulis alasan penolakan.'); await e.DB.batch([stmt(e, "UPDATE donations SET status='rejected',reason=? WHERE id=? AND status NOT IN ('verified','refunded')", clean(b.reason), d.id), auditStmt(e, a.id, 'donation_rejected', d.id, clean(b.reason))]); return { ok: true }; }
export async function addLedger(e: AppEnv, a: any, b: any) {
    const kind = clean(b.kind);
    requireValue(['expense', 'fee', 'refund', 'correction_credit', 'receipt_reversal', 'allocation'].includes(kind), 'Jenis catatan tidak sesuai.');
    const amount = money(b.amount), reason = clean(b.description);
    requireValue(reason.length >= 8, 'Uraian atau alasan minimal 8 karakter.');
    requireValue(isDate(b.date), 'Tanggal catatan wajib diisi.');
    const programId = b.programId || null;
    if (programId)
        requireValue(await row(e, "SELECT id FROM records WHERE id=? AND type='program'", programId), 'Program tidak ditemukan.');
    if (b.privateMediaId)
        requireValue(await row(e, "SELECT id FROM media WHERE id=? AND visibility='private'", b.privateMediaId), 'Dokumen privat tidak ditemukan.');
    const id = uid();
    const key = clean(b.requestKey) || uid();
    let statements = [];
    const insert = (type: string, program: string | null, related: string | null = null, pair: string | null = null) => stmt(e, 'INSERT OR IGNORE INTO ledger(id,kind,amount,program_id,donation_id,source_key,related_id,pair_id,date,description,category,private_media_id,created_by,created_at) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)', type === 'allocation_in' ? uid() : id, type, amount, program, b.donationId || null, `${key}:${type}`, related, pair, b.date, reason, clean(b.category, 100), b.privateMediaId || null, a.id, now());
    if (kind === 'allocation') {
        requireValue(programId, 'Pilih program tujuan alokasi.');
        const fin = await financial(e, '0000', '9999', '');
        requireValue(fin.balance >= amount, 'Saldo dana prioritas tidak cukup.');
        statements = [insert('allocation_out', null, null, id), insert('allocation_in', programId, null, id)];
    }
    else if (['refund', 'receipt_reversal'].includes(kind)) {
        const d = await row(e, 'SELECT * FROM donations WHERE id=?', b.donationId);
        requireValue(d && ['verified', 'refunded'].includes(d.status), 'Pilih donasi yang terverifikasi.');
        requireValue(programId === d.program_id, 'Program harus sesuai tujuan donasi.');
        const already = await row(e, "SELECT COALESCE(SUM(amount),0) amount FROM ledger WHERE donation_id=? AND kind IN ('refund','receipt_reversal')", d.id);
        requireValue(amount + already.amount <= d.amount, 'Koreksi atau pengembalian melebihi penerimaan awal.');
        statements = [insert(kind, programId, `receipt:${d.id}`)];
        if (kind === 'refund' && already.amount + amount === d.amount)
            statements.push(stmt(e, "UPDATE donations SET status='refunded',reason=? WHERE id=?", reason, d.id));
    }
    else if (kind === 'correction_credit') {
        const original = await row(e, "SELECT * FROM ledger WHERE id=? AND kind IN ('expense','fee')", b.relatedId);
        requireValue(original && original.program_id === programId, 'Pilih catatan biaya/pengeluaran yang dikoreksi.');
        const past = await row(e, "SELECT COALESCE(SUM(amount),0) amount FROM ledger WHERE related_id=? AND kind='correction_credit'", original.id);
        requireValue(past.amount + amount <= original.amount, 'Koreksi melebihi nilai catatan awal.');
        statements = [insert(kind, programId, original.id)];
    }
    else {
        const fin = await financial(e, '0000', '9999', programId || '');
        requireValue(fin.balance >= amount, 'Saldo program tidak cukup untuk catatan ini.');
        statements = [insert(kind, programId)];
    }
    statements.push(auditStmt(e, a.id, 'ledger_recorded', id, `${kind}; ${amount}; ${reason}`));
    await e.DB.batch(statements);
    return { ok: true, id };
}
export async function importBank(e: AppEnv, a: any, b: any) {
    requireValue(Array.isArray(b.rows) && b.rows.length > 0 && b.rows.length <= 500, 'Impor maksimal 500 baris per proses.');
    const account = await row(e, 'SELECT * FROM bank_account WHERE id=?', clean(b.accountId));
    requireValue(account?.confirmed, 'Pilih rekening yang telah dikonfirmasi pemilik.');
    const accountId = `bsi:${account.number}`;
    requireValue(await row(e, 'SELECT id FROM bank_account WHERE id=? AND confirmed=1', accountId), 'Konfirmasi ulang rekening agar memiliki identitas mutasi tetap.');
    requireValue(isDate(b.from) && isDate(b.to) && b.from <= b.to, 'Rentang tanggal wajib diisi.');
    const { sha } = await import('./security');
    const source = clean(b.source, 120) || 'CSV';
    const importId = uid(), valid: any[] = [];
    const references = new Set();
    let skipped = 0;
    for (const raw of b.rows) {
        const parse=(v:unknown)=>v===undefined||v===null||v===''?0:typeof v==='number'?v:typeof v==='string'&&/^\d+$/.test(v.trim())?Number(v):NaN;const date=clean(raw.date),credit=parse(raw.credit),debit=parse(raw.debit);
        requireValue(isDate(date) && date >= b.from && date <= b.to && Number.isSafeInteger(credit) && Number.isSafeInteger(debit) && credit >= 0 && debit >= 0 && ((credit > 0) !== (debit > 0)), 'Periksa tanggal, kredit, dan debit setiap baris. Gunakan angka rupiah tanpa pemisah.');
        const description = clean(raw.description, 2000), reference = clean(raw.reference, 200) || null;
        const fingerprint = await sha(JSON.stringify([accountId, date, credit, debit, description]));
        if (reference && (references.has(reference) || await row(e, 'SELECT id FROM bank_transactions WHERE account_id=? AND reference=?', accountId, reference))) {
            skipped++;
            continue;
        }
        if (reference)
            references.add(reference);
        const duplicate: boolean = !reference && (valid.some(v => v.fingerprint === fingerprint) || !!await row(e, 'SELECT id FROM bank_transactions WHERE fingerprint=?', fingerprint));
        valid.push({ date, credit, debit, description, reference, fingerprint, duplicate });
    }
    await e.DB.batch([stmt(e, 'INSERT INTO bank_imports(id,account_id,source,from_date,to_date,created_by,created_at) VALUES (?,?,?,?,?,?,?)', importId, accountId, source, b.from, b.to, a.id, now()), ...valid.map(v => stmt(e, 'INSERT INTO bank_transactions(id,import_id,account_id,date,credit,debit,description,reference,fingerprint,duplicate_candidate,scope,created_at) VALUES (?,?,?,?,?,?,?,?,?,?,\'unidentified\',?)', uid(), importId, accountId, v.date, v.credit, v.debit, v.description, v.reference, v.fingerprint, v.duplicate ? 1 : 0, now())), auditStmt(e, a.id, 'bank_imported', importId, `${valid.length} baris; ${skipped} referensi telah diimpor`)]);
    return { ok: true, imported: valid.length, skipped };
}
export async function reconciliation(e: AppEnv, from: string, to: string) {
    const banks = await rows(e, 'SELECT b.*,COALESCE((SELECT SUM(m.amount) FROM bank_matches m WHERE m.bank_id=b.id),0) allocated FROM bank_transactions b WHERE date>=? AND date<=? ORDER BY date DESC', from, to);
    const donationLedger = await row(e, "SELECT COALESCE(SUM(CASE WHEN kind='receipt' THEN amount WHEN kind='receipt_reversal' THEN -amount ELSE 0 END),0) amount FROM ledger WHERE date>=? AND date<=?", from, to);
    const matchedBank = await row(e, 'SELECT COALESCE(SUM(m.amount),0) amount FROM bank_matches m JOIN bank_transactions b ON b.id=m.bank_id WHERE b.date>=? AND b.date<=?', from, to);
    return { banks, siteReceipts: donationLedger.amount, matchedBankCredits: matchedBank.amount, difference: matchedBank.amount - donationLedger.amount, unidentifiedCredit: banks.filter(b => b.scope === 'unidentified').reduce((s, b) => s + b.credit, 0), unallocatedCredit: banks.filter(b => b.scope === 'donation').reduce((s, b) => s + b.credit - b.allocated, 0), scope: 'Alokasi kredit BSI yang dihubungkan ke donasi. Tanggal penerimaan situs memakai tanggal verifikasi; tanggal bank memakai mutasi. Selisih tanggal dapat perlu pemeriksaan. Saldo ini bukan keseluruhan saldo rekening.', from, to };
}