CSV Import with AI Mapping

Instrument,Date,Prix,Date,Prix,P&L
ES,2025-09-12 09:41:09,5000.00,2025-09-12 10:15:22,5010.00,50.00

{
  fieldColumns: string[]          // CSV column headers
  firstRows: Record<string, string>[]  // Sample data (first 5 rows)
}

{
  accountNumber: string | null    // Mapped column name
  instrument: string | null
  entryId: string | null
  closeId: string | null
  quantity: string | null
  entryPrice: string | null
  closePrice: string | null
  entryDate: string | null
  closeDate: string | null
  pnl: string | null
  timeInPosition: string | null
  side: string | null
  commission: string | null
}

const prompt = `
You are a trading data expert. Analyze the CSV columns and their data patterns 
to map them to the correct database fields. Look at BOTH the column names AND 
the actual data values to make intelligent mappings.

CRITICAL: Analyze column CONTEXT and ORDER, not just names:
- Column order matters: entryDate → entryPrice → closeDate → closePrice is typical
- If you see duplicate column names (like "Prix"), use POSITION to distinguish:
  * First "Prix" after entryDate = entryPrice
  * Second "Prix" after closeDate = closePrice
- Look for logical sequences: Date → Price → Date → Price
- Analyze data patterns:
  * Date columns: timestamps like "2025-09-12 09:41:09"
  * Price columns: decimal numbers
  * ID columns: unique identifiers (often numeric)
  * Quantity columns: trade sizes (decimal numbers)
  * PnL columns: profit/loss amounts (can be negative)

IMPORTANT: For duplicate column names, you MUST include the column position 
(1-based index) in your response. Format: "ColumnName_Position" 
(e.g., "Prix_1", "Prix_2")

Column order and context:
${fieldColumns.map((col, index) => `${index + 1}. ${col}`).join('\n')}

Sample data (first few rows):
${firstRows.map((row, index) => 
  `Row ${index + 1}: ${Object.entries(row)
    .map(([col, val]) => `${col}: "${val}"`)
    .join(", ")}`
).join('\n')}
`

import { z } from 'zod/v3'

export const mappingSchema = z.object({
  accountNumber: z.string().nullable()
    .describe("The account number or name associated with the trade"),
  instrument: z.string().nullable()
    .describe("The trading instrument (e.g., stock symbol, ticker)"),
  entryId: z.string().nullable()
    .describe("The unique identifier for the buy transaction"),
  closeId: z.string().nullable()
    .describe("The unique identifier for the sell transaction"),
  quantity: z.string().nullable()
    .describe("The number of units traded"),
  entryPrice: z.string().nullable()
    .describe("The price at which the instrument was bought"),
  closePrice: z.string().nullable()
    .describe("The price at which the instrument was sold"),
  entryDate: z.string().nullable()
    .describe("The date when the entry / buy transaction occurred"),
  closeDate: z.string().nullable()
    .describe("The date when the close / sell transaction occurred"),
  pnl: z.string().nullable()
    .describe("The profit or loss from the trade (gross PnL when commission exists)"),
  timeInPosition: z.string().nullable()
    .describe("The duration for which the position was held"),
  side: z.string().nullable()
    .describe("The entry side of the trade (e.g., buy or sell)"),
  commission: z.string().nullable()
    .describe("The commission charged for the trade"),
})

import ColumnMapping from '@/app/[locale]/dashboard/components/import/column-mapping'

<ColumnMapping
  headers={csvHeaders}
  csvData={csvRows}
  mappings={mappings}
  setMappings={setMappings}
  error={error}
  importType="csv"
/>

const [mappings, setMappings] = useState<Record<string, string>>({})

// User clicks "Use AI for mapping"
const handleAIMapping = async () => {
  const sampleData = csvData.slice(1, 6).map(row => {
    const rowObj: Record<string, string> = {}
    headers.forEach((header, index) => {
      rowObj[header] = row[index] || ''
    })
    return rowObj
  })
  
  const response = await fetch('/api/ai/mappings', {
    method: 'POST',
    body: JSON.stringify({ 
      fieldColumns: headers, 
      firstRows: sampleData 
    })
  })
  
  const aiMappings = await response.json()
  setMappings(aiMappings)
}

const createUniqueColumnId = (header: string, index: number) => 
  `${header}_${index}`

const getColumnDisplayName = (header: string, index: number, headers: string[]) => {
  const duplicateCount = headers.filter(h => h === header).length
  return duplicateCount > 1 ? `${header} (${index + 1})` : header
}

// Example
const headers = ['Date', 'Price', 'Date', 'Price']
getColumnDisplayName('Date', 0, headers)  // "Date (1)"
getColumnDisplayName('Price', 1, headers) // "Price (1)"
getColumnDisplayName('Date', 2, headers)  // "Date (2)"
getColumnDisplayName('Price', 3, headers) // "Price (2)"

// AI response for duplicate columns
{
  entryDate: "Date_1",    // First Date column (position 1)
  entryPrice: "Price_1",   // First Price column (position 1)
  closeDate: "Date_2",     // Second Date column (position 2)
  closePrice: "Price_2"    // Second Price column (position 2)
}

// Parsing logic
const positionMatch = headerValue.match(/^(.+)_(\d+)$/)
if (positionMatch) {
  const [, headerName, positionStr] = positionMatch
  const position = parseInt(positionStr, 10) - 1  // Convert to 0-based
  
  if (headers[position] === headerName) {
    const uniqueId = createUniqueColumnId(headerName, position)
    mappings[uniqueId] = destinationColumn
  }
}

const columnConfig = {
  "accountNumber": { 
    defaultMapping: ["account", "accountnumber"], 
    required: false 
  },
  "instrument": { 
    defaultMapping: ["symbol", "ticker"], 
    required: true 
  },
  "entryId": { 
    defaultMapping: ["buyid", "buyorderid"], 
    required: false 
  },
  "closeId": { 
    defaultMapping: ["sellid", "sellorderid"], 
    required: false 
  },
  "quantity": { 
    defaultMapping: ["qty", "amount"], 
    required: true 
  },
  "entryPrice": { 
    defaultMapping: ["buyprice", "entryprice"], 
    required: true 
  },
  "closePrice": { 
    defaultMapping: ["sellprice", "exitprice"], 
    required: true 
  },
  "entryDate": { 
    defaultMapping: ["buydate", "entrydate"], 
    required: true 
  },
  "closeDate": { 
    defaultMapping: ["selldate", "exitdate"], 
    required: true 
  },
  "pnl": { 
    defaultMapping: ["pnl", "profit"], 
    required: true 
  },
  "timeInPosition": { 
    defaultMapping: ["timeinposition", "duration"], 
    required: false 
  },
  "side": { 
    defaultMapping: ["side", "direction"], 
    required: false 
  },
  "commission": { 
    defaultMapping: ["commission", "fee"], 
    required: false 
  },
}

Symbol,EntryDate,EntryPrice,ExitDate,ExitPrice,Quantity,PnL,Commission
ES,2025-09-12 09:41:09,5000.00,2025-09-12 10:15:22,5010.00,1,50.00,4.20
NQ,2025-09-12 11:30:45,18000.00,2025-09-12 12:05:33,18050.00,2,100.00,8.40

Instrument,Date,Time,Price,Date,Time,Price,P&L
ES,2025-09-12,09:41:09,5000.00,2025-09-12,10:15:22,5010.00,50.00
NQ,2025-09-12,11:30:45,18000.00,2025-09-12,12:05:33,18050.00,100.00

Instrument,Date Entrée,Prix,Date Sortie,Prix,Quantité,Profit
ES,2025-09-12 09:41:09,5000.00,2025-09-12 10:15:22,5010.00,1,50.00
NQ,2025-09-12 11:30:45,18000.00,2025-09-12 12:05:33,18050.00,2,100.00

console.log('Sample rows:', firstRows)
// Ensure data is representative of the full CSV

console.log('Headers:', fieldColumns.length)
console.log('Data columns:', firstRows[0] ? Object.keys(firstRows[0]).length : 0)
// Should match

✅ Correct: 2025-09-12 09:41:09
✅ Correct: 2025-09-12T09:41:09Z
✅ Correct: 09/12/2025 09:41:09
❌ Incorrect: 12-Sep-2025
❌ Incorrect: September 12, 2025

const hasCommission = Object.values(mappings).includes('commission')
if (!hasCommission) {
  console.log('Commission field not mapped - trades will have 0 commission')
}

✅ Correct: 4.20
✅ Correct: 4.2
❌ Incorrect: $4.20
❌ Incorrect: 4.20 USD