'use client';
import { useState, useEffect } from 'react';
import { Filter } from 'lucide-react';
import PageShortcuts from '@/components/PageShortcuts';
import { equityLinks } from '@/lib/links';
interface Account {
id: number;
name: string;
}
interface LedgerEntry {
id: number;
account_id: number;
particular: string | null;
posting_date: string;
cost_center: string | null;
voucher_type: string | null;
debit: number;
credit: number;
net_balance: number | null;
}
interface CategoryBreakdown {
debit: number;
credit: number;
}
interface AccountSummary {
accountId: number;
accountName: string;
totalDebit: number;
totalCredit: number;
netCashFlow: number;
investedValue: number;
categories: {
feesAndCharges: CategoryBreakdown;
fundsAdded: CategoryBreakdown;
internalAdjustment: CategoryBreakdown;
fundsWithdrawn: CategoryBreakdown;
dematMovement: CategoryBreakdown;
};
entries: LedgerEntry[];
}
export default function LedgerPage() {
const [accounts, setAccounts] = useState<Account[]>([]);
const [selectedAccount, setSelectedAccount] = useState<string>('consolidated');
const [fromDate, setFromDate] = useState<string>('');
const [toDate, setToDate] = useState<string>('');
const [data, setData] = useState<AccountSummary[]>([]);
const [loading, setLoading] = useState(false);
useEffect(() => {
fetchAccounts();
fetchLedger();
}, []);
useEffect(() => {
fetchLedger();
}, [selectedAccount, fromDate, toDate]);
const fetchAccounts = async () => {
try {
const response = await fetch('/api/accounts');
const result = await response.json();
if (result.success) {
setAccounts(result.accounts);
}
} catch (err) {
console.error('Failed to fetch accounts:', err);
}
};
const fetchLedger = async () => {
setLoading(true);
try {
const params = new URLSearchParams();
if (selectedAccount !== 'consolidated') {
params.append('accountId', selectedAccount);
}
if (fromDate) params.append('fromDate', fromDate);
if (toDate) params.append('toDate', toDate);
const response = await fetch(`/api/ledger?${params.toString()}`);
const result = await response.json();
if (result.success) {
setData(result.data);
}
} catch (err) {
console.error('Failed to fetch ledger:', err);
} finally {
setLoading(false);
}
};
// Calculate consolidated summary
const getSummary = () => {
const totalDebit = data.reduce((sum, acc) => sum + acc.totalDebit, 0);
const totalCredit = data.reduce((sum, acc) => sum + acc.totalCredit, 0);
const netCashFlow = totalCredit - totalDebit;
const totalInvested = totalDebit;
const totalWithdrawn = totalCredit;
const availableCash = netCashFlow;
// Category totals - aggregate debit and credit separately
const categories = {
feesAndCharges: {
debit: data.reduce((sum, acc) => sum + acc.categories.feesAndCharges.debit, 0),
credit: data.reduce((sum, acc) => sum + acc.categories.feesAndCharges.credit, 0),
},
fundsAdded: {
debit: data.reduce((sum, acc) => sum + acc.categories.fundsAdded.debit, 0),
credit: data.reduce((sum, acc) => sum + acc.categories.fundsAdded.credit, 0),
},
internalAdjustment: {
debit: data.reduce((sum, acc) => sum + acc.categories.internalAdjustment.debit, 0),
credit: data.reduce((sum, acc) => sum + acc.categories.internalAdjustment.credit, 0),
},
fundsWithdrawn: {
debit: data.reduce((sum, acc) => sum + acc.categories.fundsWithdrawn.debit, 0),
credit: data.reduce((sum, acc) => sum + acc.categories.fundsWithdrawn.credit, 0),
},
dematMovement: {
debit: data.reduce((sum, acc) => sum + acc.categories.dematMovement.debit, 0),
credit: data.reduce((sum, acc) => sum + acc.categories.dematMovement.credit, 0),
},
};
const investedValue = data.reduce((sum, acc) => sum + acc.investedValue, 0);
return {
totalInvested,
totalWithdrawn,
netCashFlow,
availableCash,
categories,
investedValue,
totalEntries: data.reduce((sum, acc) => sum + acc.entries.length, 0),
};
};
const summary = getSummary();
const formatDate = (dateString: string) => {
return new Date(dateString).toLocaleDateString('en-IN', {
day: '2-digit',
month: 'short',
year: 'numeric',
});
};
const formatCurrency = (amount: number) => {
return `₹${Math.abs(amount).toLocaleString('en-IN', { maximumFractionDigits: 2 })}`;
};
return (
<div className="min-h-screen bg-gray-50">
<PageShortcuts links={equityLinks} title="Equity" />
<div className="p-8">
<div className="max-w-7xl mx-auto">
{/* Header */}
<div className="mb-8">
<h1 className="text-3xl font-bold text-gray-900 mb-2">Ledger</h1>
<p className="text-gray-700">Track your cash flows, investments, and withdrawals</p>
</div>
{/* Filters */}
<div className="bg-white rounded-lg shadow p-6 mb-6">
<div className="flex items-center gap-2 mb-4">
<Filter className="h-5 w-5 text-gray-700" />
<h2 className="text-lg font-semibold text-gray-900">Filters</h2>
</div>
<div className="grid grid-cols-1 md:grid-cols-3 gap-4">
{/* Account Filter */}
<div>
<label className="block text-sm font-medium text-gray-800 mb-2">
Account
</label>
<select
value={selectedAccount}
onChange={(e) => setSelectedAccount(e.target.value)}
className="w-full px-3 py-2 border border-gray-300 rounded-lg focus:outline-none focus:ring-2 focus:ring-blue-500 text-gray-900"
>
<option value="consolidated">All Accounts</option>
{accounts.map((account) => (
<option key={account.id} value={account.id}>
{account.name}
</option>
))}
</select>
</div>
{/* From Date */}
<div>
<label className="block text-sm font-medium text-gray-800 mb-2">
From Date
</label>
<input
type="date"
value={fromDate}
onChange={(e) => setFromDate(e.target.value)}
className="w-full px-3 py-2 border border-gray-300 rounded-lg focus:outline-none focus:ring-2 focus:ring-blue-500 text-gray-900"
/>
</div>
{/* To Date */}
<div>
<label className="block text-sm font-medium text-gray-800 mb-2">
To Date
</label>
<input
type="date"
value={toDate}
onChange={(e) => setToDate(e.target.value)}
className="w-full px-3 py-2 border border-gray-300 rounded-lg focus:outline-none focus:ring-2 focus:ring-blue-500 text-gray-900"
/>
</div>
</div>
</div>
{/* Summary Section */}
<div className="bg-white rounded-lg shadow p-6 mb-6">
<h3 className="text-lg font-semibold text-gray-900 mb-4">Ledger Summary</h3>
<div className="grid grid-cols-1 md:grid-cols-5 gap-4">
{/* Bank Receipts */}
<div className="p-4 bg-green-50 rounded-lg border border-green-200">
<div className="text-xs font-semibold text-green-800 mb-2">Bank Receipts</div>
<div className="text-xs text-gray-700 mb-3">Funds Added</div>
<div className="space-y-2">
<div className="flex justify-between items-center text-xs">
<span className="text-red-600 font-medium">Debit:</span>
<span className="font-semibold text-gray-900">{formatCurrency(summary.categories.fundsAdded.debit)}</span>
</div>
<div className="flex justify-between items-center text-xs">
<span className="text-green-600 font-medium">Credit:</span>
<span className="font-semibold text-gray-900">{formatCurrency(summary.categories.fundsAdded.credit)}</span>
</div>
<div className="flex justify-between items-center text-xs pt-2 border-t border-green-300">
<span className="font-bold text-green-700">Net:</span>
<span className="font-bold text-green-900">{formatCurrency(summary.categories.fundsAdded.debit - summary.categories.fundsAdded.credit)}</span>
</div>
</div>
</div>
{/* Bank Payments */}
<div className="p-4 bg-red-50 rounded-lg border border-red-200">
<div className="text-xs font-semibold text-red-800 mb-2">Bank Payments</div>
<div className="text-xs text-gray-700 mb-3">Funds Withdrawn</div>
<div className="space-y-2">
<div className="flex justify-between items-center text-xs">
<span className="text-red-600 font-medium">Debit:</span>
<span className="font-semibold text-gray-900">{formatCurrency(summary.categories.fundsWithdrawn.debit)}</span>
</div>
<div className="flex justify-between items-center text-xs">
<span className="text-green-600 font-medium">Credit:</span>
<span className="font-semibold text-gray-900">{formatCurrency(summary.categories.fundsWithdrawn.credit)}</span>
</div>
<div className="flex justify-between items-center text-xs pt-2 border-t border-red-300">
<span className="font-bold text-red-700">Net:</span>
<span className="font-bold text-red-900">{formatCurrency(Math.abs(summary.categories.fundsWithdrawn.debit - summary.categories.fundsWithdrawn.credit))}</span>
</div>
</div>
</div>
{/* Net Funds Transfer */}
<div className="p-4 bg-blue-50 rounded-lg border border-blue-200">
<div className="text-xs font-semibold text-blue-800 mb-2">Net Funds Transfer</div>
<div className="text-xs text-gray-700 mb-3">Total Investment</div>
<div className="space-y-2">
<div className="flex justify-between items-center text-xs">
<span className="text-gray-700 font-medium">Receipts:</span>
<span className="font-semibold text-gray-900">{formatCurrency(summary.categories.fundsAdded.debit - summary.categories.fundsAdded.credit)}</span>
</div>
<div className="flex justify-between items-center text-xs">
<span className="text-gray-700 font-medium">Payments:</span>
<span className="font-semibold text-gray-900">{formatCurrency(Math.abs(summary.categories.fundsWithdrawn.debit - summary.categories.fundsWithdrawn.credit))}</span>
</div>
<div className="flex justify-between items-center text-xs pt-2 border-t border-blue-300">
<span className="font-bold text-blue-700">Net:</span>
<span className="font-bold text-blue-900">{formatCurrency((summary.categories.fundsAdded.debit - summary.categories.fundsAdded.credit) + (summary.categories.fundsWithdrawn.debit - summary.categories.fundsWithdrawn.credit))}</span>
</div>
</div>
</div>
{/* Purchase Value */}
<div className="p-4 bg-purple-50 rounded-lg border border-purple-200">
<div className="text-xs font-semibold text-purple-800 mb-2">Purchase Value</div>
<div className="text-xs text-gray-700 mb-3">Book Voucher</div>
<div className="space-y-2">
<div className="flex justify-between items-center text-xs">
<span className="text-red-600 font-medium">Debit:</span>
<span className="font-semibold text-gray-900">{formatCurrency(summary.categories.feesAndCharges.debit)}</span>
</div>
<div className="flex justify-between items-center text-xs">
<span className="text-green-600 font-medium">Credit:</span>
<span className="font-semibold text-gray-900">{formatCurrency(summary.categories.feesAndCharges.credit)}</span>
</div>
<div className="flex justify-between items-center text-xs pt-2 border-t border-purple-300">
<span className="font-bold text-purple-700">Net:</span>
<span className="font-bold text-purple-900">{formatCurrency(summary.categories.feesAndCharges.debit - summary.categories.feesAndCharges.credit)}</span>
</div>
</div>
</div>
{/* Closing Cash Balance */}
<div className="p-4 bg-amber-50 rounded-lg border-2 border-amber-300">
<div className="text-xs font-semibold text-amber-900 mb-2">Closing Balance</div>
<div className="text-xs text-gray-700 mb-3">Net Cash Available</div>
<div className="space-y-2">
{(() => {
// Calculate net values
const investmentNet = (summary.categories.fundsAdded.debit - summary.categories.fundsAdded.credit) + (summary.categories.fundsWithdrawn.debit - summary.categories.fundsWithdrawn.credit);
const purchasesNet = summary.categories.feesAndCharges.debit - summary.categories.feesAndCharges.credit;
const chargesNet = summary.categories.internalAdjustment.debit - summary.categories.internalAdjustment.credit;
// Closing Balance Logic:
// Investment - Book Voucher - Journal Entry
// Always use absolute values and subtract
const closingBalance = Math.abs(investmentNet) - Math.abs(purchasesNet) - Math.abs(chargesNet);
return (
<>
<div className="flex justify-between items-center text-xs">
<span className="text-gray-700 font-medium">Investment:</span>
<span className="font-semibold text-green-900">
+{formatCurrency(Math.abs(investmentNet))}
</span>
</div>
<div className="flex justify-between items-center text-xs">
<span className="text-gray-700 font-medium">Book Voucher:</span>
<span className="font-semibold text-red-600">
-{formatCurrency(purchasesNet)}
</span>
</div>
<div className="flex justify-between items-center text-xs">
<span className="text-gray-700 font-medium">Journal Entry:</span>
<span className="font-semibold text-red-600">
-{formatCurrency(chargesNet)}
</span>
</div>
<div className="flex justify-between items-center text-xs pt-2 border-t-2 border-amber-400">
<span className="font-bold text-amber-900">Balance:</span>
<span className={`font-bold text-lg ${closingBalance >= 0 ? 'text-green-900' : 'text-red-900'}`}>
{closingBalance >= 0 ? '' : '-'}{formatCurrency(closingBalance)}
</span>
</div>
</>
);
})()}
</div>
</div>
</div>
</div>
{/* Category Breakdown */}
<div className="bg-white rounded-lg shadow p-6 mb-6">
<h3 className="text-lg font-semibold text-gray-900 mb-4">Transaction Categories</h3>
<div className="grid grid-cols-1 md:grid-cols-5 gap-4">
{/* Book Voucher */}
<div className="p-4 bg-gray-50 rounded-lg border border-gray-200">
<div className="text-xs font-semibold text-gray-700 mb-2">Book Voucher</div>
<div className="text-xs text-gray-700 mb-3">Purchases</div>
<div className="space-y-2">
<div className="flex justify-between items-center text-xs">
<span className="text-red-600 font-medium">Debit:</span>
<span className="font-semibold text-gray-900">{formatCurrency(summary.categories.feesAndCharges.debit)}</span>
</div>
<div className="flex justify-between items-center text-xs">
<span className="text-green-600 font-medium">Credit:</span>
<span className="font-semibold text-gray-900">{formatCurrency(summary.categories.feesAndCharges.credit)}</span>
</div>
<div className="flex justify-between items-center text-xs pt-2 border-t border-gray-300">
<span className="font-bold text-gray-700">Difference:</span>
<span className="font-bold text-gray-900">{formatCurrency(Math.abs(summary.categories.feesAndCharges.credit - summary.categories.feesAndCharges.debit))}</span>
</div>
</div>
</div>
{/* Bank Receipts */}
<div className="p-4 bg-green-50 rounded-lg border border-green-200">
<div className="text-xs font-semibold text-green-800 mb-2">Bank Receipts</div>
<div className="text-xs text-gray-700 mb-3">Funds Added</div>
<div className="space-y-2">
<div className="flex justify-between items-center text-xs">
<span className="text-red-600 font-medium">Debit:</span>
<span className="font-semibold text-gray-900">{formatCurrency(summary.categories.fundsAdded.debit)}</span>
</div>
<div className="flex justify-between items-center text-xs">
<span className="text-green-600 font-medium">Credit:</span>
<span className="font-semibold text-gray-900">{formatCurrency(summary.categories.fundsAdded.credit)}</span>
</div>
<div className="flex justify-between items-center text-xs pt-2 border-t border-green-300">
<span className="font-bold text-green-700">Difference:</span>
<span className="font-bold text-green-900">{formatCurrency(Math.abs(summary.categories.fundsAdded.credit - summary.categories.fundsAdded.debit))}</span>
</div>
</div>
</div>
{/* Journal Entry */}
<div className="p-4 bg-blue-50 rounded-lg border border-blue-200">
<div className="text-xs font-semibold text-blue-800 mb-2">Journal Entry</div>
<div className="text-xs text-gray-700 mb-3">Internal Adjustment</div>
<div className="space-y-2">
<div className="flex justify-between items-center text-xs">
<span className="text-red-600 font-medium">Debit:</span>
<span className="font-semibold text-gray-900">{formatCurrency(summary.categories.internalAdjustment.debit)}</span>
</div>
<div className="flex justify-between items-center text-xs">
<span className="text-green-600 font-medium">Credit:</span>
<span className="font-semibold text-gray-900">{formatCurrency(summary.categories.internalAdjustment.credit)}</span>
</div>
<div className="flex justify-between items-center text-xs pt-2 border-t border-blue-300">
<span className="font-bold text-blue-700">Difference:</span>
<span className="font-bold text-blue-900">{formatCurrency(Math.abs(summary.categories.internalAdjustment.credit - summary.categories.internalAdjustment.debit))}</span>
</div>
</div>
</div>
{/* Bank Payments */}
<div className="p-4 bg-red-50 rounded-lg border border-red-200">
<div className="text-xs font-semibold text-red-800 mb-2">Bank Payments</div>
<div className="text-xs text-gray-700 mb-3">Funds Withdrawn</div>
<div className="space-y-2">
<div className="flex justify-between items-center text-xs">
<span className="text-red-600 font-medium">Debit:</span>
<span className="font-semibold text-gray-900">{formatCurrency(summary.categories.fundsWithdrawn.debit)}</span>
</div>
<div className="flex justify-between items-center text-xs">
<span className="text-green-600 font-medium">Credit:</span>
<span className="font-semibold text-gray-900">{formatCurrency(summary.categories.fundsWithdrawn.credit)}</span>
</div>
<div className="flex justify-between items-center text-xs pt-2 border-t border-red-300">
<span className="font-bold text-red-700">Difference:</span>
<span className="font-bold text-red-900">{formatCurrency(Math.abs(summary.categories.fundsWithdrawn.credit - summary.categories.fundsWithdrawn.debit))}</span>
</div>
</div>
</div>
{/* Delivery Voucher */}
<div className="p-4 bg-purple-50 rounded-lg border border-purple-200">
<div className="text-xs font-semibold text-purple-800 mb-2">Delivery Voucher</div>
<div className="text-xs text-gray-700 mb-3">Demat Movement</div>
<div className="space-y-2">
<div className="flex justify-between items-center text-xs">
<span className="text-red-600 font-medium">Debit:</span>
<span className="font-semibold text-gray-900">{formatCurrency(summary.categories.dematMovement.debit)}</span>
</div>
<div className="flex justify-between items-center text-xs">
<span className="text-green-600 font-medium">Credit:</span>
<span className="font-semibold text-gray-900">{formatCurrency(summary.categories.dematMovement.credit)}</span>
</div>
<div className="flex justify-between items-center text-xs pt-2 border-t border-purple-300">
<span className="font-bold text-purple-700">Difference:</span>
<span className="font-bold text-purple-900">{formatCurrency(Math.abs(summary.categories.dematMovement.credit - summary.categories.dematMovement.debit))}</span>
</div>
</div>
</div>
</div>
</div>
{/* Loading */}
{loading && (
<div className="flex items-center justify-center py-12 bg-white rounded-lg shadow">
<div className="text-gray-700">Loading ledger entries...</div>
</div>
)}
{/* Ledger Entries by Account */}
{!loading && data.map((accountData) => (
<div key={accountData.accountId} className="mb-8">
{/* Entries Table */}
{accountData.entries.length > 0 ? (
<div className="bg-white rounded-lg shadow overflow-hidden">
<div className="overflow-x-auto">
<table className="min-w-full divide-y divide-gray-200">
<thead className="bg-gray-50">
<tr>
<th className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase">Date</th>
<th className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase">Particular</th>
<th className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase">Type</th>
<th className="px-6 py-3 text-right text-xs font-medium text-gray-500 uppercase">Debit</th>
<th className="px-6 py-3 text-right text-xs font-medium text-gray-500 uppercase">Credit</th>
<th className="px-6 py-3 text-right text-xs font-medium text-gray-500 uppercase">Balance</th>
</tr>
</thead>
<tbody className="bg-white divide-y divide-gray-200">
{accountData.entries.map((entry) => (
<tr key={entry.id} className="hover:bg-gray-50">
<td className="px-6 py-4 whitespace-nowrap text-sm text-gray-900">
{formatDate(entry.posting_date)}
</td>
<td className="px-6 py-4 text-sm text-gray-900">
{entry.particular || 'N/A'}
</td>
<td className="px-6 py-4 whitespace-nowrap text-sm text-gray-700">
{entry.voucher_type || 'N/A'}
</td>
<td className="px-6 py-4 whitespace-nowrap text-sm text-right">
{entry.debit > 0 ? (
<span className="text-red-600 font-medium">
{formatCurrency(entry.debit)}
</span>
) : (
<span className="text-gray-300">-</span>
)}
</td>
<td className="px-6 py-4 whitespace-nowrap text-sm text-right">
{entry.credit > 0 ? (
<span className="text-green-600 font-medium">
{formatCurrency(entry.credit)}
</span>
) : (
<span className="text-gray-300">-</span>
)}
</td>
<td className="px-6 py-4 whitespace-nowrap text-sm text-right text-gray-900 font-medium">
{entry.net_balance !== null ? formatCurrency(entry.net_balance) : '-'}
</td>
</tr>
))}
</tbody>
</table>
</div>
</div>
) : (
<div className="bg-white rounded-lg shadow p-6 text-center text-gray-700">
No ledger entries found
</div>
)}
</div>
))}
{/* Empty State */}
{!loading && data.length === 0 && (
<div className="text-center py-12 bg-white rounded-lg shadow">
<p className="text-gray-700 mb-4">No ledger data available</p>
<p className="text-sm text-gray-700">Import your ledger CSV to see cash flow analysis</p>
</div>
)}
</div>
</div>
</div>
);
}
