/**
 * Field mapping for Proforma templates
 * Maps BD fields to Excel cell references for each trader type
 */

export type TraderType = 'ketron' | 'larainvest';

interface ProformaFieldMapping {
  // Company/Trader info
  traderName: string;
  traderAddress: string;
  traderUEN: string;
  traderLabel: string; // Company name at top
  
  // Client info
  clientName: string;
  clientAddress: string;
  
  // Consignee info
  consigneeName: string;
  consigneeAddress: string;
  
  // Port & delivery
  port: string;
  
  // Document info
  proformaNumber: string;
  date: string; // Today's date
  etaPlus2Months: string; // ETA + 2 months
  poNumber: string;
  
  // Product line
  reference: string;
  productDescription: string;
  estimatedKg: number;
  pricePerKg: number;
  totalAmount: number; // estimatedKg * pricePerKg
  
  // Provider
  providerName: string;
  providerAddress: string;
  providerPlantNumber: string;
  
  // Totals
  grossAmount: number;
  totalProforma: number;
  
  // Bank details (for payment section)
  bankPaymentTerms: string;
  bankName: string;
  bankCode: string;
  branchCode: string;
  accountNumber: string;
  swift: string;
}

/**
 * Cell references for Ketron Proforma
 */
export const KETRON_PROFORMA_CELLS = {
  traderName: 'B2',
  traderAddress: 'B3',
  traderUEN: 'B4',
  traderLabel: 'E3', // "KETRON GLOBAL SINGAPORE"
  
  clientName: 'E4',
  clientAddress: 'E5',
  
  consigneeName: 'D8', // Based on row 8-10 area
  consigneeAddress: 'D9',
  
  port: 'B8', // Delivery Place
  
  proformaNumber: 'C12', // Proforma N.
  date: 'C12', // Date
  etaPlus2Months: 'E12', // ETA+2mo
  poNumber: 'F12', // PO
  
  reference: 'B15',
  productDescription: 'C15',
  estimatedKg: 'E15',
  pricePerKg: 'F15',
  totalAmount: 'G15',
  
  providerName: 'B20',
  providerAddress: 'B21',
  providerPlantNumber: 'E20',
  
  grossAmount: 'B29',
  totalProforma: 'G29',
  
  bankPaymentTerms: 'B33',
  bankName: 'B34',
  bankCode: 'B35',
  branchCode: 'B35', // Same cell
  accountNumber: 'B35', // Same cell
  swift: 'B36',
};

/**
 * Cell references for Larainvest Proforma
 */
export const LARAINVEST_PROFORMA_CELLS = {
  traderName: 'B2',
  traderAddress: 'B3',
  traderUEN: 'B4',
  traderLabel: 'E3', // "LARAINVEST"
  
  clientName: 'E4',
  clientAddress: 'E5',
  
  consigneeName: 'D8',
  consigneeAddress: 'D9',
  
  port: 'B8',
  
  proformaNumber: 'C12',
  date: 'C12',
  etaPlus2Months: 'E12',
  poNumber: 'F12',
  
  reference: 'B15',
  productDescription: 'C15',
  estimatedKg: 'E15',
  pricePerKg: 'F15',
  totalAmount: 'G15',
  
  providerName: 'B20',
  providerAddress: 'B21',
  providerPlantNumber: 'E20',
  
  grossAmount: 'B29',
  totalProforma: 'G29',
  
  bankPaymentTerms: 'B33',
  bankName: 'B34',
  bankCode: 'B35',
  branchCode: 'B35',
  accountNumber: 'B35',
  swift: 'B36',
};

/**
 * Get cell mapping for a trader type
 */
export function getCellMapping(traderType: TraderType) {
  return traderType === 'ketron' ? KETRON_PROFORMA_CELLS : LARAINVEST_PROFORMA_CELLS;
}