<?php

if (!defined('ABSPATH')) {
    exit;
}

/**
 * Importación de reseñas desde CSV o Excel (.xlsx).
 * Espera columnas: Autor, Texto, Estrellas (opcional), Fecha (opcional), URL foto (opcional).
 */
class ETN_EGR_Import {

    /**
     * Lee un archivo CSV o XLSX y devuelve un array de filas (cada fila es array de celdas).
     * La primera fila puede ser cabecera.
     *
     * @param string $file_path Ruta temporal del archivo subido.
     * @param string $filename Nombre original (para detectar extensión).
     * @return array{headers: array|null, rows: array}
     */
    public static function parse_file($file_path, $filename = '') {
        if (!is_readable($file_path)) {
            return ['headers' => null, 'rows' => []];
        }
        $ext = strtolower(pathinfo($filename, PATHINFO_EXTENSION));
        if ($ext === 'xlsx') {
            return self::parse_xlsx($file_path);
        }
        return self::parse_csv($file_path);
    }

    /**
     * Parsea CSV. Soporta BOM UTF-8, delimitador coma o punto y coma, y campos entrecomillados.
     * Uses WP_Filesystem for file read.
     */
    public static function parse_csv($file_path) {
        global $wp_filesystem;
        if (empty($wp_filesystem)) {
            require_once ABSPATH . 'wp-admin/includes/file.php';
            WP_Filesystem();
        }
        if (!isset($wp_filesystem) || !is_object($wp_filesystem)) {
            return ['headers' => null, 'rows' => []];
        }
        $contents = $wp_filesystem->get_contents($file_path);
        if ($contents === false || $contents === '') {
            return ['headers' => null, 'rows' => []];
        }
        $contents = self::strip_bom($contents);
        $lines    = self::csv_split_lines($contents);
        if (empty($lines)) {
            return ['headers' => null, 'rows' => []];
        }
        $first_line = $lines[0];
        $delimiter  = ',';
        if (strpos($first_line, ';') !== false && substr_count($first_line, ';') >= substr_count($first_line, ',')) {
            $delimiter = ';';
        }
        $headers = null;
        $rows    = [];
        foreach ($lines as $index => $line) {
            $decoded = str_getcsv($line, $delimiter, '"', '\\');
            if (count($decoded) === 1 && $decoded[0] === null) {
                continue;
            }
            $decoded = array_map(function ($cell) {
                $cell = is_string($cell) ? $cell : (string) $cell;
                $cell = self::strip_bom(trim($cell));
                if (function_exists('mb_detect_encoding') && function_exists('mb_convert_encoding') && $cell !== '') {
                    $enc = @mb_detect_encoding($cell, ['UTF-8', 'ISO-8859-1', 'Windows-1252'], true);
                    if ($enc && $enc !== 'UTF-8') {
                        $converted = @mb_convert_encoding($cell, 'UTF-8', $enc);
                        if ($converted !== false) {
                            $cell = $converted;
                        }
                    }
                }
                return $cell;
            }, $decoded);
            if ($index === 0 && self::looks_like_header($decoded)) {
                $headers = $decoded;
            } else {
                $rows[] = $decoded;
            }
        }
        return ['headers' => $headers, 'rows' => $rows];
    }

    /**
     * Splits CSV content into lines, respecting quoted fields that may contain newlines.
     *
     * @param string $str Raw file content.
     * @return string[]
     */
    private static function csv_split_lines($str) {
        $lines      = [];
        $current    = '';
        $in_quotes  = false;
        $len        = strlen($str);
        for ($i = 0; $i < $len; $i++) {
            $c = $str[$i];
            if ($c === '"') {
                $in_quotes = !$in_quotes;
                $current  .= $c;
            } elseif (($c === "\n" || $c === "\r") && !$in_quotes) {
                if ($c === "\r" && $i + 1 < $len && $str[$i + 1] === "\n") {
                    $i++;
                }
                $lines[]  = $current;
                $current  = '';
            } else {
                $current .= $c;
            }
        }
        if ($current !== '') {
            $lines[] = $current;
        }
        return $lines;
    }

    private static function looks_like_header($cells) {
        $first = isset($cells[0]) ? trim($cells[0]) : '';
        $first = str_replace("\xEF\xBB\xBF", '', $first);
        $first = strtolower($first);
        $h = ['autor', 'author', 'nombre', 'texto', 'text', 'reseña', 'review', 'estrellas', 'rating', 'stars'];
        foreach ($h as $keyword) {
            if (strpos($first, $keyword) !== false) {
                return true;
            }
        }
        return false;
    }

    /** Quita BOM de una cadena. */
    private static function strip_bom($s) {
        return str_replace("\xEF\xBB\xBF", '', $s);
    }

    /**
     * Parsea XLSX (primer hoja). Usa ZipArchive + SimpleXML.
     */
    public static function parse_xlsx($file_path) {
        if (!class_exists('ZipArchive')) {
            return ['headers' => null, 'rows' => []];
        }
        $zip = new ZipArchive();
        if ($zip->open($file_path, ZipArchive::RDONLY) !== true) {
            return ['headers' => null, 'rows' => []];
        }
        $sharedStrings = [];
        $ssXml = $zip->getFromName('xl/sharedStrings.xml');
        if ($ssXml !== false && function_exists('simplexml_load_string')) {
            libxml_use_internal_errors(true);
            $xml = @simplexml_load_string($ssXml);
            if ($xml !== false) {
                foreach ($xml->xpath('//*[local-name()="si"]') ?: [] as $si) {
                    $v = (string) $si->t;
                    if ($v === '') {
                        foreach ($si->xpath('*[local-name()="r"]') ?: [] as $r) {
                            $v .= (string) $r->t;
                        }
                    }
                    $sharedStrings[] = $v;
                }
            }
        }
        $sheetNames = ['xl/worksheets/sheet1.xml', 'xl/worksheets/sheet2.xml'];
        $sheetXml = null;
        foreach ($sheetNames as $name) {
            $sheetXml = $zip->getFromName($name);
            if ($sheetXml !== false) {
                break;
            }
        }
        $zip->close();
        if ($sheetXml === false || !function_exists('simplexml_load_string')) {
            return ['headers' => null, 'rows' => []];
        }
        $xml = @simplexml_load_string($sheetXml);
        if ($xml === false) {
            return ['headers' => null, 'rows' => []];
        }
        $rows = [];
        foreach ($xml->xpath('//*[local-name()="row"]') ?: [] as $row) {
            $cells = [];
            $maxCol = 0;
            foreach ($row->xpath('*[local-name()="c"]') ?: [] as $c) {
                $ref = (string) $c['r'];
                preg_match('/^([A-Z]+)(\d+)$/', $ref, $m);
                $col = isset($m[1]) ? self::col_letter_to_index($m[1]) : 0;
                $maxCol = max($maxCol, $col);
                $v = (string) $c->v;
                if ((string) $c['t'] === 's' && $v !== '' && isset($sharedStrings[(int) $v])) {
                    $v = $sharedStrings[(int) $v];
                }
                $cells[$col] = $v;
            }
            $line = [];
            for ($i = 0; $i <= $maxCol; $i++) {
                $line[] = isset($cells[$i]) ? trim($cells[$i]) : '';
            }
            $rows[] = $line;
        }
        $headers = null;
        if (!empty($rows) && self::looks_like_header($rows[0])) {
            $headers = $rows[0];
            $rows = array_slice($rows, 1);
        }
        return ['headers' => $headers, 'rows' => $rows];
    }

    private static function col_letter_to_index($letters) {
        $index = 0;
        $len = strlen($letters);
        for ($i = 0; $i < $len; $i++) {
            $index = $index * 26 + (ord($letters[$i]) - ord('A') + 1);
        }
        return $index - 1;
    }

    /**
     * Convierte filas + cabeceras opcionales en lista de reseñas con keys: author_name, text, rating, etc.
     *
     * @param array|null $headers
     * @param array      $rows
     * @return array Lista de arrays con author_name, text, rating, profile_photo_url, relative_time_description.
     */
    public static function rows_to_reviews($headers, $rows) {
        $reviews = [];
        $map = self::header_map($headers);
        foreach ($rows as $row) {
            $author = self::cell($row, $map['author']);
            $text   = self::cell($row, $map['text']);
            if (trim($author) === '' || trim($text) === '') {
                continue;
            }
            $rating = (int) self::cell($row, $map['rating']);
            if ($rating < 1 || $rating > 5) {
                $rating = 5;
            }
            $reviews[] = [
                'author_name'               => sanitize_text_field(trim($author)),
                'text'                      => sanitize_textarea_field(trim($text)),
                'rating'                    => $rating,
                'profile_photo_url'         => esc_url_raw(trim(self::cell($row, $map['photo']))) ?: '',
                'relative_time_description' => sanitize_text_field(trim(self::cell($row, $map['date']))) ?: '',
            ];
        }
        return $reviews;
    }

    private static function header_map($headers) {
        $author = 0;
        $text   = 1;
        $rating = 2;
        $date   = 3;
        $photo  = 4;
        if (is_array($headers)) {
            $h = array_map('strtolower', $headers);
            foreach ($h as $i => $label) {
                $label = trim($label);
                if (in_array($label, ['autor', 'author', 'nombre', 'nombre del autor'], true)) {
                    $author = $i;
                } elseif (in_array($label, ['texto', 'text', 'reseña', 'review', 'comentario'], true)) {
                    $text = $i;
                } elseif (in_array($label, ['estrellas', 'rating', 'stars', 'puntuación'], true)) {
                    $rating = $i;
                } elseif (in_array($label, ['fecha', 'date', 'fecha reseña'], true)) {
                    $date = $i;
                } elseif (strpos($label, 'foto') !== false || strpos($label, 'url') !== false || strpos($label, 'photo') !== false || strpos($label, 'avatar') !== false) {
                    $photo = $i;
                }
            }
        }
        return ['author' => $author, 'text' => $text, 'rating' => $rating, 'date' => $date, 'photo' => $photo];
    }

    private static function cell($row, $index) {
        return isset($row[$index]) ? $row[$index] : '';
    }
}
