import { NextRequest, NextResponse } from 'next/server';
import { getServerSession } from 'next-auth';
import { authOptions } from '@/app/api/auth/[...nextauth]/route';
import { db, query } from '@/lib/db';
// POST /api/conflicts/scan-existing - Scan for existing duplicates in the database
export async function POST(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 { accountId, importType } = await request.json();
if (!accountId || !importType) {
return NextResponse.json(
{ success: false, error: 'Account ID and import type are required' },
{ status: 400 }
);
}
const accountIdNum = parseInt(accountId);
if (isNaN(accountIdNum)) {
return NextResponse.json(
{ success: false, error: 'Invalid account ID' },
{ status: 400 }
);
}
// Verify account belongs to user
const account = await db.getAccountById(accountIdNum, userId);
if (!account) {
return NextResponse.json(
{ success: false, error: 'Account not found' },
{ status: 404 }
);
}
let duplicatesFound = 0;
if (importType === 'tradebook') {
// Find duplicate trades (same account_id, trade_id)
const duplicateTrades = await query(
`SELECT trade_id, symbol, COUNT(*) as count
FROM trades
WHERE account_id = ?
AND trade_id IS NOT NULL
GROUP BY trade_id, symbol
HAVING count > 1`,
[accountIdNum]
);
for (const dup of duplicateTrades) {
// Get all instances of this duplicate
const instances = await query(
`SELECT * FROM trades
WHERE account_id = ? AND trade_id = ?
ORDER BY id ASC`,
[accountIdNum, dup.trade_id]
);
if (instances.length > 1) {
const first = instances[0];
// Create conflicts for all duplicates except the first one
for (let i = 1; i < instances.length; i++) {
const duplicate = instances[i];
// Check if conflict already exists
const existingConflict = await query(
`SELECT id FROM import_conflicts
WHERE account_id = ?
AND import_type = 'tradebook'
AND JSON_EXTRACT(existing_data, '$.id') = ?
AND status = 'pending'`,
[accountIdNum, first.id]
);
if (existingConflict.length === 0) {
// Check if data is identical
const isExactDuplicate =
first.quantity === duplicate.quantity &&
first.price === duplicate.price &&
first.symbol === duplicate.symbol &&
first.trade_date.getTime() === duplicate.trade_date.getTime();
await db.createConflict({
account_id: accountIdNum,
import_type: 'tradebook',
conflict_type: isExactDuplicate ? 'exact_duplicate_existing' : 'duplicate_trade_id_existing',
existing_data: first,
new_data: duplicate,
conflict_field: isExactDuplicate ? null : 'quantity,price,symbol',
status: 'pending',
resolved_at: null,
resolved_by: null,
});
duplicatesFound++;
}
}
}
}
} else if (importType === 'ledger') {
// Find duplicate ledger entries (same account_id, posting_date, particular, debit, credit)
const duplicateLedgers = await query(
`SELECT posting_date, particular, debit, credit, COUNT(*) as count
FROM ledger
WHERE account_id = ?
GROUP BY posting_date, particular, debit, credit
HAVING count > 1`,
[accountIdNum]
);
for (const dup of duplicateLedgers) {
// Get all instances of this duplicate
const instances = await query(
`SELECT * FROM ledger
WHERE account_id = ?
AND posting_date = ?
AND (particular = ? OR (particular IS NULL AND ? IS NULL))
AND debit = ?
AND credit = ?
ORDER BY id ASC`,
[accountIdNum, dup.posting_date, dup.particular, dup.particular, dup.debit, dup.credit]
);
if (instances.length > 1) {
const first = instances[0];
// Create conflicts for all duplicates except the first one
for (let i = 1; i < instances.length; i++) {
const duplicate = instances[i];
// Check if conflict already exists
const existingConflict = await query(
`SELECT id FROM import_conflicts
WHERE account_id = ?
AND import_type = 'ledger'
AND JSON_EXTRACT(existing_data, '$.id') = ?
AND status = 'pending'`,
[accountIdNum, first.id]
);
if (existingConflict.length === 0) {
await db.createConflict({
account_id: accountIdNum,
import_type: 'ledger',
conflict_type: 'exact_duplicate_existing',
existing_data: first,
new_data: duplicate,
conflict_field: null,
status: 'pending',
resolved_at: null,
resolved_by: null,
});
duplicatesFound++;
}
}
}
}
}
return NextResponse.json({
success: true,
message: `Scan complete. Found ${duplicatesFound} duplicate(s)`,
duplicatesFound,
});
} catch (error: any) {
console.error('Error scanning for duplicates:', error);
return NextResponse.json(
{ success: false, error: error.message },
{ status: 500 }
);
}
}
