import { NextRequest, NextResponse } from 'next/server';
import { getServerSession } from 'next-auth';
import { authOptions } from '@/app/api/auth/[...nextauth]/route';
import * as db from '@/lib/db';
import { calculateStockXIRR } from '@/lib/xirr-calculator';
import { executeKiteTool } from '@/lib/kite-service';
import { calculateFIFOPosition, calculateUnrealizedPnL, type Trade as FIFOTrade } from '@/lib/fifo-calculator';
import priceCache from '@/lib/price-cache';
// Helper to fetch prices from Yahoo Finance (Free API)
async function getYahooFinancePrices(symbols: string[]): Promise<Record<string, number>> {
const prices: Record<string, number> = {};
if (symbols.length === 0) {
return prices;
}
console.log(`[YahooFinance] Fetching prices for ${symbols.length} symbols`);
try {
// Yahoo Finance uses .NS suffix for NSE stocks
const yahooSymbols = symbols.map(symbol => `${symbol}.NS`);
const symbolsParam = yahooSymbols.join(',');
// Using Yahoo Finance v8 API
const url = `https://query1.finance.yahoo.com/v8/finance/chart/${symbolsParam}?interval=1d&range=1d`;
console.log(`[YahooFinance] Fetching from URL: ${url}`);
const response = await fetch(url, {
headers: {
'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36'
}
});
if (!response.ok) {
console.error(`[YahooFinance] HTTP error: ${response.status}`);
// If batch fails, try individual requests
return await getYahooFinancePricesIndividual(symbols);
}
const data = await response.json();
if (data?.chart?.result) {
for (let i = 0; i < symbols.length; i++) {
const symbol = symbols[i];
const result = data.chart.result[i];
if (result?.meta?.regularMarketPrice) {
prices[symbol] = result.meta.regularMarketPrice;
console.log(`[YahooFinance] ${symbol}: ₹${result.meta.regularMarketPrice}`);
} else {
prices[symbol] = 0;
console.log(`[YahooFinance] ${symbol}: No price data`);
}
}
}
} catch (error: any) {
console.error('[YahooFinance] Error fetching prices:', error.message);
// Try individual requests as fallback
return await getYahooFinancePricesIndividual(symbols);
}
return prices;
}
// Fallback: Fetch prices individually (optimized with parallel batching)
async function getYahooFinancePricesIndividual(symbols: string[]): Promise<Record<string, number>> {
const prices: Record<string, number> = {};
console.log(`[YahooFinance] Fetching prices individually for ${symbols.length} symbols`);
// Process in larger batches with parallel execution for better performance
const batchSize = 10; // Increased batch size
const fetchPromises: Promise<void>[] = [];
for (let i = 0; i < symbols.length; i += batchSize) {
const batch = symbols.slice(i, i + batchSize);
// Create promises for all fetches in this batch
const batchPromises = batch.map(async (symbol) => {
try {
const yahooSymbol = `${symbol}.NS`;
const url = `https://query1.finance.yahoo.com/v8/finance/chart/${yahooSymbol}?interval=1d&range=1d`;
const response = await fetch(url, {
headers: {
'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36'
},
signal: AbortSignal.timeout(5000), // 5 second timeout per request
});
if (response.ok) {
const data = await response.json();
if (data?.chart?.result?.[0]?.meta?.regularMarketPrice) {
prices[symbol] = data.chart.result[0].meta.regularMarketPrice;
console.log(`[YahooFinance] ${symbol}: ₹${prices[symbol]}`);
} else {
prices[symbol] = 0;
}
} else {
prices[symbol] = 0;
}
} catch (error) {
console.error(`[YahooFinance] Error fetching ${symbol}:`, error);
prices[symbol] = 0;
}
});
fetchPromises.push(...batchPromises);
}
// Execute all fetches in parallel
await Promise.allSettled(fetchPromises);
return prices;
}
// Helper to get live prices with caching (tries Kite first, falls back to Yahoo Finance)
async function getLivePrices(symbols: string[], accountId?: number, userId?: number): Promise<Record<string, number>> {
if (symbols.length === 0) {
return {};
}
console.log(`[getLivePrices] Fetching prices for ${symbols.length} symbols`);
// Step 1: Get cached prices
const cachedPrices = priceCache.getCachedPrices(symbols);
const cachedCount = Object.keys(cachedPrices).length;
if (cachedCount > 0) {
console.log(`[getLivePrices] Using ${cachedCount} cached prices`);
}
// Step 2: Identify symbols that need to be fetched
const symbolsToFetch = priceCache.getSymbolsToFetch(symbols);
if (symbolsToFetch.length === 0) {
console.log(`[getLivePrices] All prices from cache`);
return cachedPrices;
}
console.log(`[getLivePrices] Need to fetch ${symbolsToFetch.length} symbols`);
const prices: Record<string, number> = { ...cachedPrices };
// Step 3: Try Kite API first if account is authenticated
let kiteSuccess = false;
if (accountId && userId) {
try {
console.log(`[getLivePrices] Trying Kite API with accountId: ${accountId}, userId: ${userId}`);
const instruments = symbolsToFetch.map(symbol => `NSE:${symbol}`);
const args: any = { instruments, account_id: accountId, user_id: userId };
const result = await executeKiteTool('get_ltp', args);
if (result && typeof result === 'object') {
for (const symbol of symbolsToFetch) {
const instrumentKey = `NSE:${symbol}`;
if (result[instrumentKey] && result[instrumentKey].last_price) {
prices[symbol] = result[instrumentKey].last_price;
priceCache.set(symbol, result[instrumentKey].last_price);
console.log(`[getLivePrices-Kite] ${symbol}: ₹${result[instrumentKey].last_price}`);
}
}
// Check if we got any prices from Kite
const pricesReceived = Object.keys(prices).length - cachedCount;
if (pricesReceived > 0) {
console.log(`[getLivePrices] Successfully fetched ${pricesReceived} prices from Kite API`);
kiteSuccess = true;
}
}
} catch (error: any) {
console.log(`[getLivePrices] Kite API failed: ${error.message}`);
}
} else {
console.log('[getLivePrices] No Kite account credentials, skipping Kite API');
}
// Step 4: If Kite API didn't work or returned no prices, use Yahoo Finance
if (!kiteSuccess) {
console.log('[getLivePrices] Falling back to Yahoo Finance API');
const yahooPrices = await getYahooFinancePrices(symbolsToFetch);
// Merge Yahoo prices and cache them
for (const symbol of symbolsToFetch) {
if (!prices[symbol] || prices[symbol] === 0) {
prices[symbol] = yahooPrices[symbol] || 0;
if (yahooPrices[symbol] && yahooPrices[symbol] > 0) {
priceCache.set(symbol, yahooPrices[symbol]);
}
}
}
const pricesReceived = Object.keys(prices).filter(s => prices[s] > 0).length;
console.log(`[getLivePrices] Total ${pricesReceived}/${symbols.length} prices available (${cachedCount} cached + ${pricesReceived - cachedCount} fetched)`);
}
return prices;
}
interface TradeGroup {
symbol: string;
accountId: number;
accountName: string;
totalBuyQuantity: number;
totalSellQuantity: number;
netQuantity: number;
totalBuyValue: number;
totalSellValue: number;
avgBuyPrice: number;
avgSellPrice: number;
currentPrice: number;
currentValue: number;
realizedPnL: number;
realizedPnLPercent: number;
unrealizedPnL: number;
unrealizedPnLPercent: number;
totalPnL: number;
status: 'active' | 'sold';
xirr: number | null;
trades: any[];
firstTradeDate: Date;
lastTradeDate: Date;
// Investment (cost basis of active position)
investment: number;
// For sold positions only - Opportunity cost calculation
currentValueIfHeld?: number;
opportunityCost?: number;
opportunityCostPercent?: number;
}
// GET /api/tradebook?accountId=[id]&fromDate=[date]&toDate=[date]&status=[active|sold|all]
export async function GET(request: NextRequest) {
try {
const session = await getServerSession(authOptions);
if (!session || !session.user) {
return NextResponse.json(
{ success: false, error: 'Unauthorized' },
{ status: 401 }
);
}
const userId = parseInt((session.user as any).id);
const { searchParams } = new URL(request.url);
const accountIdParam = searchParams.get('accountId');
const fromDate = searchParams.get('fromDate');
const toDate = searchParams.get('toDate');
const status = searchParams.get('status') || 'all'; // all, active, sold
const sortBy = searchParams.get('sortBy') || 'symbol'; // symbol, currentValue, totalPnL, xirr, opportunityCost
const sortOrder = searchParams.get('sortOrder') || 'asc'; // asc, desc
let accountId: number | undefined;
if (accountIdParam && accountIdParam !== 'consolidated') {
accountId = parseInt(accountIdParam);
if (isNaN(accountId)) {
return NextResponse.json(
{ success: false, error: 'Invalid account ID' },
{ status: 400 }
);
}
// Verify account belongs to user
const account = await db.db.getAccountById(accountId, userId);
if (!account) {
return NextResponse.json(
{ success: false, error: 'Account not found' },
{ status: 404 }
);
}
}
// Build query for trades - user-specific via account ownership
let sql = `
SELECT t.* FROM trades t
INNER JOIN accounts a ON t.account_id = a.id
WHERE a.user_id = ?
`;
const params: any[] = [userId];
if (accountId) {
sql += ' AND t.account_id = ?';
params.push(accountId);
}
if (fromDate) {
sql += ' AND t.trade_date >= ?';
params.push(fromDate);
}
if (toDate) {
sql += ' AND t.trade_date <= ?';
params.push(toDate);
}
sql += ' ORDER BY t.symbol, t.trade_date';
const trades = await db.query(sql, params);
// Get all accounts for mapping - user-specific
const accounts = await db.db.getAccounts(userId);
const accountMap = new Map(accounts.map(a => [a.id, a.name]));
// Group trades by symbol and account
const tradeGroups = new Map<string, TradeGroup>();
for (const trade of trades) {
const key = `${trade.symbol}_${trade.account_id}`;
if (!tradeGroups.has(key)) {
tradeGroups.set(key, {
symbol: trade.symbol,
accountId: trade.account_id,
accountName: accountMap.get(trade.account_id) || 'Unknown',
totalBuyQuantity: 0,
totalSellQuantity: 0,
netQuantity: 0,
totalBuyValue: 0,
totalSellValue: 0,
avgBuyPrice: 0,
avgSellPrice: 0,
currentPrice: 0,
currentValue: 0,
realizedPnL: 0,
realizedPnLPercent: 0,
unrealizedPnL: 0,
unrealizedPnLPercent: 0,
totalPnL: 0,
status: 'active',
xirr: null,
trades: [],
firstTradeDate: trade.trade_date,
lastTradeDate: trade.trade_date,
investment: 0,
});
}
const group = tradeGroups.get(key)!;
group.trades.push(trade);
// Update date range
if (new Date(trade.trade_date) < new Date(group.firstTradeDate)) {
group.firstTradeDate = trade.trade_date;
}
if (new Date(trade.trade_date) > new Date(group.lastTradeDate)) {
group.lastTradeDate = trade.trade_date;
}
// Accumulate quantities and values (convert to numbers)
const qty = parseFloat(trade.quantity.toString());
const price = parseFloat(trade.price.toString());
if (trade.trade_type === 'buy') {
group.totalBuyQuantity += qty;
group.totalBuyValue += qty * price;
} else {
group.totalSellQuantity += qty;
group.totalSellValue += qty * price;
}
}
// Get live prices for all symbols
const uniqueSymbols = Array.from(new Set(
Array.from(tradeGroups.keys()).map(key => key.split('_')[0])
));
console.log(`[Tradebook API] Processing ${uniqueSymbols.length} unique symbols`);
// Determine which account to use for live prices
// If filtering by specific account, use that account
// Otherwise, try to find the first authenticated account
let accountIdForPrices: number | undefined;
if (accountId) {
// Use the selected account
const account = accounts.find(a => a.id === accountId);
console.log(`[Tradebook API] Looking for account ${accountId}, found:`, account);
if (account && account.access_token) {
accountIdForPrices = accountId;
console.log(`[Tradebook API] Using selected authenticated account: ${accountIdForPrices}`);
} else {
console.log(`[Tradebook API] Selected account not authenticated`);
}
} else {
// Use the first authenticated account
const authenticatedAccount = accounts.find(a => a.access_token);
if (authenticatedAccount) {
accountIdForPrices = authenticatedAccount.id;
console.log(`[Tradebook API] Using first authenticated account: ${accountIdForPrices} (${authenticatedAccount.name})`);
} else {
console.log(`[Tradebook API] No authenticated accounts found`);
}
}
// Fetch live prices with authenticated account
const livePrices = await getLivePrices(uniqueSymbols, accountIdForPrices, userId);
console.log(`[Tradebook API] Live prices fetched:`, livePrices);
// Calculate derived values for each group
const processedGroups: TradeGroup[] = [];
for (const group of tradeGroups.values()) {
// Convert trades to FIFO format
const fifoTrades: FIFOTrade[] = group.trades.map(t => ({
trade_date: new Date(t.trade_date),
trade_type: t.trade_type as 'buy' | 'sell',
quantity: parseFloat(t.quantity.toString()),
price: parseFloat(t.price.toString()),
}));
// Calculate position using FIFO matching
const fifoResult = calculateFIFOPosition(fifoTrades);
// Get current price
const currentPrice = livePrices[group.symbol] || 0;
group.currentPrice = currentPrice;
// Use FIFO results for accurate P&L calculation
group.totalBuyQuantity = fifoResult.totalBuyQuantity;
group.totalSellQuantity = fifoResult.totalSellQuantity;
group.totalBuyValue = fifoResult.totalBuyValue;
group.totalSellValue = fifoResult.totalSellValue;
group.netQuantity = fifoResult.netQuantity;
// Calculate average prices (for display purposes)
group.avgBuyPrice = group.totalBuyQuantity > 0
? group.totalBuyValue / group.totalBuyQuantity
: 0;
group.avgSellPrice = group.totalSellQuantity > 0
? group.totalSellValue / group.totalSellQuantity
: 0;
// Realized P&L from FIFO calculation (only from closed positions)
group.realizedPnL = fifoResult.realizedPnL;
group.realizedPnLPercent = fifoResult.realizedPnLPercent;
// Unrealized P&L for active position only
const unrealizedResult = calculateUnrealizedPnL(fifoResult, currentPrice);
group.unrealizedPnL = unrealizedResult.unrealizedPnL;
group.unrealizedPnLPercent = unrealizedResult.unrealizedPnLPercent;
group.currentValue = unrealizedResult.currentValue;
// Investment (cost basis of active position from FIFO)
group.investment = fifoResult.activePositionCost;
// Total P&L
group.totalPnL = group.realizedPnL + group.unrealizedPnL;
// Determine status
group.status = group.netQuantity === 0 ? 'sold' : 'active';
// Calculate Opportunity Cost for sold positions
if (group.status === 'sold' && currentPrice > 0 && group.totalSellQuantity > 0) {
// What would be the current value if we still held the shares
group.currentValueIfHeld = group.totalSellQuantity * currentPrice;
// Difference from what we actually got (opportunity cost)
// Negative if we sold too early (current value > sold value)
// Positive if we sold at right time (current value < sold value)
group.opportunityCost = group.totalSellValue - group.currentValueIfHeld;
// Percentage of opportunity cost
group.opportunityCostPercent = group.currentValueIfHeld > 0
? (group.opportunityCost / group.currentValueIfHeld) * 100
: 0;
console.log(`[Tradebook Opportunity Cost] ${group.symbol}: Sold ${group.totalSellQuantity} @ avg ₹${group.avgSellPrice.toFixed(2)} = ₹${group.totalSellValue.toFixed(2)}, Current @ ₹${currentPrice} = ₹${group.currentValueIfHeld.toFixed(2)}, Opportunity Cost: ₹${group.opportunityCost.toFixed(2)} (${group.opportunityCostPercent.toFixed(2)}%)`);
}
// Calculate XIRR
try {
// For fully closed positions, use only closed position trades
// For active positions, use active position trades
const tradesForXIRR = group.netQuantity === 0
? fifoResult.closedPositionTrades
: fifoResult.activePositionTrades;
console.log(`[Tradebook XIRR] ${group.symbol}: ${tradesForXIRR.length} trades, netQty: ${group.netQuantity}, price: ${currentPrice}`);
if (tradesForXIRR.length > 0) {
group.xirr = calculateStockXIRR(
tradesForXIRR,
currentPrice,
group.netQuantity
);
console.log(`[Tradebook XIRR] ${group.symbol}: Result = ${group.xirr !== null ? group.xirr.toFixed(2) + '%' : 'N/A'}`);
} else {
console.log(`[Tradebook XIRR] ${group.symbol}: No trades for XIRR calculation`);
group.xirr = null;
}
} catch (err: any) {
console.error(`[Tradebook XIRR] ${group.symbol}: Error -`, err.message || err);
group.xirr = null;
}
// Apply status filter
if (status === 'active' && group.status !== 'active') continue;
if (status === 'sold' && group.status !== 'sold') continue;
processedGroups.push(group);
}
// Sort based on user preference
processedGroups.sort((a, b) => {
let compareValue = 0;
switch (sortBy) {
case 'currentValue':
// For sold positions, use currentValueIfHeld; for active, use currentValue
const aValue = a.status === 'sold' ? (a.currentValueIfHeld || 0) : a.currentValue;
const bValue = b.status === 'sold' ? (b.currentValueIfHeld || 0) : b.currentValue;
compareValue = bValue - aValue; // Default desc for value
break;
case 'totalPnL':
compareValue = b.totalPnL - a.totalPnL; // Default desc for P&L
break;
case 'xirr':
const aXirr = a.xirr ?? -Infinity;
const bXirr = b.xirr ?? -Infinity;
compareValue = bXirr - aXirr; // Default desc for XIRR
break;
case 'opportunityCost':
// Only relevant for sold positions
const aOpp = a.opportunityCost ?? 0;
const bOpp = b.opportunityCost ?? 0;
compareValue = bOpp - aOpp; // Default desc (higher opportunity cost first)
break;
case 'symbol':
default:
compareValue = a.symbol.localeCompare(b.symbol); // Default asc for symbol
break;
}
// Apply sort order
return sortOrder === 'desc' ? -compareValue : compareValue;
});
// Calculate summary stats
const activeGroups = processedGroups.filter(g => g.status === 'active');
const soldGroups = processedGroups.filter(g => g.status === 'sold');
// Calculate opportunity cost totals for sold positions
const soldWithOpportunityCost = soldGroups.filter(g =>
g.opportunityCost !== undefined && g.currentPrice > 0
);
// Stocks sold too early (negative opportunity cost)
const stocksSoldEarly = soldWithOpportunityCost.filter(g => g.opportunityCost! < 0);
const potentialLosses = stocksSoldEarly.reduce((sum, g) => sum + Math.abs(g.opportunityCost!), 0);
const soldEarlyInvestment = stocksSoldEarly.reduce((sum, g) => sum + g.totalBuyValue, 0);
const soldEarlyCount = stocksSoldEarly.length;
// Stocks sold at good timing (positive opportunity cost)
const stocksSoldWell = soldWithOpportunityCost.filter(g => g.opportunityCost! > 0);
const potentialProfits = stocksSoldWell.reduce((sum, g) => sum + g.opportunityCost!, 0);
const soldWellInvestment = stocksSoldWell.reduce((sum, g) => sum + g.totalBuyValue, 0);
const soldWellCount = stocksSoldWell.length;
const summary = {
totalStocks: processedGroups.length,
activeStocks: activeGroups.length,
soldStocks: soldGroups.length,
totalBuyValue: processedGroups.reduce((sum, g) => sum + g.totalBuyValue, 0),
totalSellValue: processedGroups.reduce((sum, g) => sum + g.totalSellValue, 0),
totalRealizedPnL: processedGroups.reduce((sum, g) => sum + g.realizedPnL, 0),
totalUnrealizedPnL: processedGroups.reduce((sum, g) => sum + g.unrealizedPnL, 0),
// For active positions: investment (cost basis) and current value
totalInvestment: activeGroups.reduce((sum, g) => sum + g.investment, 0),
totalCurrentValue: activeGroups.reduce((sum, g) => sum + g.currentValue, 0),
// For sold positions: opportunity cost analysis
potentialLosses, // Total missed gains (sold too early)
potentialProfits, // Total avoided losses (good timing)
soldEarlyCount, // Number of stocks sold too early
soldWellCount, // Number of stocks sold at good timing
soldEarlyInvestment, // Original cost of stocks sold too early
soldWellInvestment, // Original cost of stocks sold at good timing
};
return NextResponse.json({
success: true,
data: {
groups: processedGroups,
summary,
},
});
} catch (error: any) {
console.error('Error fetching tradebook:', error);
return NextResponse.json(
{ success: false, error: error.message },
{ status: 500 }
);
}
}
