src/lib/csvUtils.ts

import fs from 'fs';
import path from 'path';
import { parse } from 'csv-parse/sync';
import type { PromptExample } from '@/types/example';

export interface ComprehensiveExample {
  year: string;
  quiz_number: string;
  question: string;
  choices: string;
  answer: string;
  question_category: string;
  grammar_category: string;
}

/**
 * Load examples from a CSV file
 * @param filename - Name of the CSV file in the data directory
 * @returns Array of PromptExample objects
 */
export async function loadCsvExamples(filename: string): Promise<PromptExample[]> {
  try {
    const csvPath = path.join(process.cwd(), 'data', filename);
    const csvContent = fs.readFileSync(csvPath, 'utf-8');
    
    // Parse CSV with csv-parse library (handles multi-line entries, escaped quotes, etc.)
    const records = parse(csvContent, {
      columns: false, // Return as array of arrays
      skip_empty_lines: true,
      relax_column_count: true, // Allow inconsistent column counts
      trim: true, // Trim whitespace from values
    }) as string[][];
    
    const examples: PromptExample[] = [];
    
    // Skip header row (index 0) and process data rows
    for (let i = 1; i < records.length; i++) {
      const row = records[i];
      
      // Ensure we have at least 5 columns
      if (row && row.length >= 5) {
        examples.push({
          年度: row[0] || '',
          題號: row[1] || '',
          子題型: row[2] || '',
          題幹: row[3] || '',
          選項: row[4] || '',
        });
        console.log(`Loaded example: ${row[2]} (${row[0]}-${row[1]})`);
      }
    }
    
    console.log(`Total examples loaded from ${filename}: ${examples.length}`);
    return examples;
  } catch (error) {
    console.error(`Error loading examples from ${filename}:`, error);
    return [];
  }
}

/**
 * Load comprehensive question examples from CSV, filtered by question_category
 * CSV columns: year, quiz_number, question, choices, answer, worked_solutions, question_point, question_category, grammar_category
 */
export async function loadComprehensiveExamples(category: string): Promise<ComprehensiveExample[]> {
  try {
    const csvPath = path.join(process.cwd(), 'data', 'cz_english_unit1_past_papers.csv');
    const csvContent = fs.readFileSync(csvPath, 'utf-8');

    const records = parse(csvContent, {
      columns: true,
      skip_empty_lines: true,
      relax_column_count: true,
      trim: true,
    }) as Record<string, string>[];

    const filtered = records
      .filter(row => row['question category'] === category)
      .map(row => ({
        year: row['year'] || '',
        quiz_number: row['quiz_number'] || '',
        question: row['question'] || '',
        choices: row['choices'] || '',
        answer: row['answer'] || '',
        question_category: row['question category'] || '',
        grammar_category: row['grammar_category'] || '',
      }));

    console.log(`Loaded ${filtered.length} comprehensive examples for category: ${category}`);
    return filtered;
  } catch (error) {
    console.error('Error loading comprehensive examples:', error);
    return [];
  }
}