Jorge Morais - Full Stack Developer

// services/TemplateProcessor.ts
interface TemplateMapping {
  sourceColumn: string;
  targetField: string;
  dataType: 'string' | 'number' | 'date' | 'boolean';
  transform?: 'uppercase' | 'lowercase' | 'trim' | 'parse_date';
}

interface Template {
  id: string;
  name: string;
  fileType: 'csv' | 'excel' | 'pdf' | 'txt';
  delimiter?: string;
  encoding?: string;
  mappings: TemplateMapping[];
}

export class TemplateProcessor {
  /**
   * Process file using saved template
   * Handles format detection, extraction, and mapping
   */
  async processFileWithTemplate(
    filePath: string,
    template: Template
  ): Promise<Record<string, any>[]> {
    // Step 1: Extract raw data based on file type
    const rawData = await this.extractRawData(filePath, template);

    // Step 2: Apply template mappings with transformations
    const transformedData = this.applyMappings(rawData, template.mappings);

    // Step 3: Validate data types
    return this.validateData(transformedData, template.mappings);
  }

  /**
   * Extract raw data - delegates PDF to Python service
   */
  private async extractRawData(
    filePath: string,
    template: Template
  ): Promise<Record<string, any>[]> {
    switch (template.fileType) {
      case 'csv':
      case 'txt':
        return this.parseDelimitedFile(filePath, template);
      case 'excel':
        return this.parseExcelFile(filePath);
      case 'pdf':
        return this.parsePdfFile(filePath); // Calls Python FastAPI
      default:
        throw new Error(`Unsupported file type: ${template.fileType}`);
    }
  }

  /**
   * Parse CSV/TXT with auto-detection of delimiter and encoding
   */
  private async parseDelimitedFile(
    filePath: string,
    template: Template
  ): Promise<Record<string, any>[]> {
    const buffer = await readFile(filePath);
    const encoding = template.encoding || this.detectEncoding(buffer);
    const content = buffer.toString(encoding);
    const delimiter = template.delimiter || this.detectDelimiter(content);

    return parse(content, {
      delimiter,
      columns: true,
      skip_empty_lines: true,
      trim: true
    });
  }

  /**
   * Auto-detect delimiter by analyzing first rows
   */
  private detectDelimiter(content: string): string {
    const delimiters = [',', ';', '|', '\t'];
    const lines = content.split('\n').slice(0, 5);

    let bestDelimiter = ',';
    let maxScore = -1;

    delimiters.forEach(delim => {
      const counts = lines.map(line => line.split(delim).length);
      const isConsistent = counts.every(c => c === counts[0]);
      const score = isConsistent ? counts[0] : 0;

      if (score > maxScore) {
        maxScore = score;
        bestDelimiter = delim;
      }
    });

    return bestDelimiter;
  }

  /**
   * Apply template mappings to raw data
   */
  private applyMappings(
    rawData: Record<string, any>[],
    mappings: TemplateMapping[]
  ): Record<string, any>[] {
    return rawData.map(row => {
      const transformedRow: Record<string, any> = {};

      mappings.forEach(mapping => {
        let value = row[mapping.sourceColumn];

        // Apply transformation if specified
        if (value && mapping.transform) {
          value = this.applyTransform(value, mapping.transform);
        }

        // Convert to target data type
        transformedRow[mapping.targetField] =
          this.convertDataType(value, mapping.dataType);
      });

      return transformedRow;
    });
  }

  /**
   * Convert value to target data type
   */
  private convertDataType(value: any, dataType: string): any {
    if (!value) return null;

    switch (dataType) {
      case 'number':
        const num = parseFloat(String(value).replace(/[^0-9.-]/g, ''));
        return isNaN(num) ? null : num;
      case 'date':
        return new Date(value);
      case 'boolean':
        return ['true', '1', 'yes'].includes(String(value).toLowerCase());
      default:
        return String(value);
    }
  }
}

// Python FastAPI Service (python_service/pdf_extractor.py)
"""
from fastapi import FastAPI, File, UploadFile
import tabula
import pdfplumber
import pandas as pd

app = FastAPI()

@app.post("/extract-pdf")
async def extract_pdf(file: UploadFile):
    """
    Dual AI PDF extraction with confidence scoring
    Combines tabula-py and pdfplumber for maximum reliability
    """
    pdf_bytes = await file.read()

    # Method 1: Tabula (lattice-based)
    tabula_data, tabula_conf = extract_with_tabula(pdf_bytes)

    # Method 2: PDFplumber (text-based)
    pdfplumber_data, pdfplumber_conf = extract_with_pdfplumber(pdf_bytes)

    # Select best result based on confidence
    if tabula_conf > pdfplumber_conf:
        result = tabula_data
        method = "tabula"
    else:
        result = pdfplumber_data
        method = "pdfplumber"

    return {
        "success": True,
        "data": result,
        "method_used": method,
        "confidence_scores": {
            "tabula": tabula_conf,
            "pdfplumber": pdfplumber_conf
        }
    }

def extract_with_tabula(pdf_bytes: bytes) -> tuple:
    """Extract using tabula-py with confidence scoring"""
    try:
        dfs = tabula.read_pdf(pdf_bytes, pages='all', lattice=True)
        if not dfs:
            return [], 0.0

        df = max(dfs, key=lambda x: len(x))
        confidence = calculate_confidence(df)

        return df.to_dict('records'), confidence
    except:
        return [], 0.0

def calculate_confidence(df: pd.DataFrame) -> float:
    """Calculate confidence based on completeness and consistency"""
    if df.empty:
        return 0.0

    completeness = 1 - (df.isnull().sum().sum() / (len(df) * len(df.columns)))
    return round(completeness, 2)
"""