loreal-global-kickoff/CSVTransformer.php

<?php
/**
 * CSV Transformer
 * Transforms global campaign CSV into regional CSVs per the Make.com blueprint logic
 */

class CSVTransformer {
    private $config;
    private $isoCodes;
    private $errors = [];
    private $warnings = [];

    public function __construct() {
        $appConfig = require __DIR__ . '/config.php';
        $this->config = $appConfig['global_to_local'];
        $this->isoCodes = $this->config['iso_codes'];
    }

    /**
     * Validate uploaded CSV file
     */
    public function validateUpload($file) {
        $errors = [];

        // Check if file was uploaded
        if (!isset($file['tmp_name']) || empty($file['tmp_name'])) {
            $errors[] = 'No file was uploaded';
            return ['valid' => false, 'errors' => $errors];
        }

        // Check for upload errors
        if ($file['error'] !== UPLOAD_ERR_OK) {
            $errors[] = 'File upload error: ' . $this->getUploadErrorMessage($file['error']);
            return ['valid' => false, 'errors' => $errors];
        }

        // Check file extension
        $ext = strtolower(pathinfo($file['name'], PATHINFO_EXTENSION));
        if ($ext !== 'csv') {
            $errors[] = 'Invalid file type. Expected .csv, got .' . $ext;
            return ['valid' => false, 'errors' => $errors];
        }

        // Check file size
        if ($file['size'] > $this->config['max_file_size']) {
            $maxMB = round($this->config['max_file_size'] / 1048576, 2);
            $sizeMB = round($file['size'] / 1048576, 2);
            $errors[] = "File too large. Maximum size: {$maxMB}MB, uploaded: {$sizeMB}MB";
            return ['valid' => false, 'errors' => $errors];
        }

        // Check if file is empty
        if ($file['size'] === 0) {
            $errors[] = 'File is empty';
            return ['valid' => false, 'errors' => $errors];
        }

        return ['valid' => true, 'filename' => $file['name']];
    }

    /**
     * Get upload error message
     */
    private function getUploadErrorMessage($code) {
        switch ($code) {
            case UPLOAD_ERR_INI_SIZE:
            case UPLOAD_ERR_FORM_SIZE:
                return 'File exceeds maximum allowed size';
            case UPLOAD_ERR_PARTIAL:
                return 'File was only partially uploaded';
            case UPLOAD_ERR_NO_FILE:
                return 'No file was uploaded';
            case UPLOAD_ERR_NO_TMP_DIR:
                return 'Missing temporary folder';
            case UPLOAD_ERR_CANT_WRITE:
                return 'Failed to write file to disk';
            default:
                return 'Unknown upload error';
        }
    }

    /**
     * Parse CSV and validate structure
     * Handles Excel-style CSV with Sep=, on first line
     */
    public function parseCSV($filePath) {
        try {
            // Read file contents
            $contents = file_get_contents($filePath);

            // Remove Excel separator hint if present (Sep=,)
            if (substr($contents, 0, 4) === 'Sep=') {
                $lines = explode("\n", $contents);
                array_shift($lines);  // Remove first line
                $contents = implode("\n", $lines);
            }

            // Create CSV reader from string
            $csv = \League\Csv\Reader::createFromString($contents);
            $csv->setHeaderOffset(0);  // First row (after Sep removal) is headers

            $headers = $csv->getHeader();
            $records = iterator_to_array($csv->getRecords());

            // Validate we have the expected columns
            $requiredColumns = ['Number', 'Title', 'Category', 'Media', 'Supply date', 'Live date', 'End date', 'Language', 'Country'];
            $missingColumns = [];

            foreach ($requiredColumns as $col) {
                if (!in_array($col, $headers)) {
                    $missingColumns[] = $col;
                }
            }

            if (!empty($missingColumns)) {
                return [
                    'success' => false,
                    'error' => 'Missing required columns',
                    'details' => 'Missing: ' . implode(', ', $missingColumns),
                    'action' => 'Verify you uploaded the correct CSV file format'
                ];
            }

            // Check if CSV has data rows
            if (count($records) === 0) {
                return [
                    'success' => false,
                    'error' => 'CSV has no data rows',
                    'details' => 'CSV contains only headers, no data to process',
                    'action' => 'Upload a CSV file with data rows'
                ];
            }

            return [
                'success' => true,
                'headers' => $headers,
                'rows' => $records,
                'rowCount' => count($records)
            ];

        } catch (Exception $e) {
            return [
                'success' => false,
                'error' => 'CSV parsing failed',
                'details' => $e->getMessage(),
                'action' => 'Check if the file is a valid CSV with comma delimiters'
            ];
        }
    }

    /**
     * Extract campaign number from filename
     * Expected pattern: *_CAMPAIGNNUM_*.csv
     */
    public function extractCampaignNumber($filename) {
        // Remove .csv extension
        $nameWithoutExt = preg_replace('/\.csv$/i', '', $filename);

        // Split by underscore
        $parts = explode('_', $nameWithoutExt);

        // Try to find campaign number (should be in part 2 based on blueprint)
        if (isset($parts[2]) && is_numeric($parts[2])) {
            return [
                'success' => true,
                'campaignNumber' => $parts[2]
            ];
        }

        // Try to find any numeric part
        foreach ($parts as $part) {
            if (is_numeric($part) && strlen($part) >= 4) {
                return [
                    'success' => true,
                    'campaignNumber' => $part,
                    'warning' => 'Campaign number found but not in expected position'
                ];
            }
        }

        return [
            'success' => false,
            'error' => 'Campaign number not found in filename',
            'details' => "Expected pattern: *_CAMPAIGN#_*.csv, got: $filename",
            'action' => 'Rename file to include campaign number (e.g., Global_123456_Assets.csv)'
        ];
    }

    /**
     * Transform CSV data per Make.com blueprint logic
     * Creates 16 separate CSVs, one per ISO code (language/country)
     * Replaces Language and Country fields in each row with the target ISO code
     */
    public function transformData($rows, $campaignNumber, $businessUnit) {
        $this->errors = [];
        $this->warnings = [];

        $outputCSVs = [];  // Array of CSVs, one per ISO code

        // Create one CSV per ISO code
        foreach ($this->isoCodes as $isoCode) {
            $isoCode = trim($isoCode);

            // Extract language and country from ISO code
            // e.g., "en-GB" -> language: "en-GB", country: "GB"
            $parts = explode('-', $isoCode);
            $language = $isoCode;
            $country = isset($parts[1]) ? $parts[1] : '';

            $transformedRows = [];
            $rowNumber = 2;  // Start from 2 (line 1 is Sep=, line 2 is headers)

            foreach ($rows as $inputRow) {
                $rowNumber++;

                // Store original title for Creative Execution
                $originalTitle = $inputRow['Title'] ?? '';

                // Replace Language and Country fields
                $outputRow = $inputRow;
                $outputRow['Language'] = $language;
                $outputRow['Country'] = $country;

                // Add language/country to Title (append to end)
                $outputRow['Title'] = $originalTitle . '_' . $language;

                // Add Creative Execution column (contains the original global title)
                $outputRow['Creative Execution'] = $originalTitle;

                // Transform dates if needed (add 1 month per blueprint)
                if (isset($outputRow['Supply date'])) {
                    $outputRow['Supply date'] = $this->transformDate($outputRow['Supply date'], $rowNumber, 'Supply date');
                }
                if (isset($outputRow['Live date'])) {
                    $outputRow['Live date'] = $this->transformDate($outputRow['Live date'], $rowNumber, 'Live date');
                }
                if (isset($outputRow['End date'])) {
                    $outputRow['End date'] = $this->transformDate($outputRow['End date'], $rowNumber, 'End date');
                }

                // Validate required fields
                if (empty($outputRow['Number'])) {
                    $this->warnings[] = [
                        'row' => $rowNumber,
                        'warning' => 'Missing Number field',
                        'isoCode' => $isoCode,
                        'severity' => 'high'
                    ];
                }

                $transformedRows[] = $outputRow;
            }

            // Generate CSV content for this ISO code
            $csv = \League\Csv\Writer::createFromString();

            // Add headers
            $headers = array_keys($transformedRows[0]);
            $csv->insertOne($headers);

            // Add data rows
            $csv->insertAll($transformedRows);

            // Generate filename for this ISO code
            $filename = $this->generateFilename($campaignNumber, $businessUnit, $isoCode);

            $outputCSVs[] = [
                'isoCode' => $isoCode,
                'country' => $country,
                'filename' => $filename,
                'content' => $csv->toString(),
                'rowCount' => count($transformedRows)
            ];
        }

        return [
            'success' => count($this->errors) === 0,
            'csvFiles' => $outputCSVs,
            'inputRowCount' => count($rows),
            'totalOutputRows' => count($rows) * count($this->isoCodes),
            'fileCount' => count($outputCSVs),
            'errors' => $this->errors,
            'warnings' => $this->warnings
        ];
    }

    /**
     * Transform date: parse, add 1 month, format DD/MM/YYYY
     */
    private function transformDate($dateString, $rowNumber, $fieldName) {
        if (empty($dateString)) {
            $this->warnings[] = [
                'row' => $rowNumber,
                'warning' => "Empty $fieldName",
                'severity' => 'medium'
            ];
            return '';
        }

        try {
            // Try parsing with different formats
            // Format 1: "24 Mar 2025 00:00" (from your sample CSV)
            if (preg_match('/\d{1,2}\s+[A-Za-z]{3}\s+\d{4}/', $dateString)) {
                // Extract just the date part (ignore time)
                $datePart = preg_replace('/\s+\d{2}:\d{2}.*$/', '', $dateString);
                $date = \Carbon\Carbon::createFromFormat('d M Y', $datePart);
            }
            // Format 2: "DD/MM/YYYY"
            else if (preg_match('/\d{1,2}\/\d{1,2}\/\d{4}/', $dateString)) {
                $date = \Carbon\Carbon::createFromFormat('d/m/Y', $dateString);
            }
            // Try ISO format or other standard formats
            else {
                $date = \Carbon\Carbon::parse($dateString);
            }

            // Add 1 month per blueprint
            $date->addMonth();

            // Format as DD/MM/YYYY
            return $date->format('d/m/Y');

        } catch (Exception $e) {
            $this->errors[] = [
                'row' => $rowNumber,
                'error' => "Invalid $fieldName format",
                'details' => "Unable to parse '$dateString' - " . $e->getMessage(),
                'action' => 'Use format DD/MM/YYYY or DD MMM YYYY'
            ];

            return $dateString;  // Return original if can't parse
        }
    }

    /**
     * Generate output filename per blueprint
     */
    public function generateFilename($campaignNumber, $businessUnit, $isoCode) {
        $timestamp = time();

        // Extract country from ISO code
        $parts = explode('-', $isoCode);
        $country = isset($parts[1]) ? $parts[1] : '';

        // Format: OMG{campaign}_GlobalACIngest_{BU}-{Country}_{timestamp}.csv
        return "OMG{$campaignNumber}_GlobalACIngest_{$businessUnit}-{$country}_{$timestamp}.csv";
    }

    /**
     * Get errors
     */
    public function getErrors() {
        return $this->errors;
    }

    /**
     * Get warnings
     */
    public function getWarnings() {
        return $this->warnings;
    }

    /**
     * Has errors
     */
    public function hasErrors() {
        return count($this->errors) > 0;
    }

    /**
     * Has warnings
     */
    public function hasWarnings() {
        return count($this->warnings) > 0;
    }
}