<?php
/**
 * Calendar Importer Class
 * Importa calendario tributario desde archivos CSV o Excel
 *
 * Formato esperado para vencimientos (Excel o CSV):
 * Columnas: obligation_code (o codigo), period (o periodo), period_name, digit_1...digit_0, notes
 *
 * Formato alternativo (estructura del archivo calendario.xlsx de la DIAN):
 * Primera columna: Nombre de obligacion
 * Siguientes columnas: Fechas por digito
 */

namespace dokuwiki\plugin\beztaxcalendar\lib;

// Incluir el lector XLSX nativo
require_once __DIR__ . '/SimpleXLSXReader.php';

class CalendarImporter {

    protected $sqlite;
    protected $errors = [];
    protected $warnings = [];
    protected $stats = [
        'obligations_new' => 0,
        'obligations_updated' => 0,
        'deadlines_new' => 0,
        'deadlines_updated' => 0,
        'errors' => 0
    ];

    /**
     * Constructor
     */
    public function __construct($sqlite) {
        $this->sqlite = $sqlite;
    }

    /**
     * Importar archivo
     *
     * @param string $filePath Ruta al archivo
     * @param int $year Año del calendario
     * @param string $type Tipo: 'deadlines' o 'obligations'
     * @param bool $preview Solo validar sin guardar
     * @param string $originalFilename Nombre original del archivo (para detectar extension)
     * @return array Resultado de la importación
     */
    public function import($filePath, $year, $type = 'deadlines', $preview = false, $originalFilename = null) {
        $this->errors = [];
        $this->warnings = [];
        $this->stats = [
            'obligations_new' => 0,
            'obligations_updated' => 0,
            'deadlines_new' => 0,
            'deadlines_updated' => 0,
            'errors' => 0
        ];

        // Usar el nombre original si se proporciona, sino usar la ruta del archivo
        $filenameForExtension = $originalFilename ?: $filePath;
        $ext = strtolower(pathinfo($filenameForExtension, PATHINFO_EXTENSION));

        if ($ext === 'csv') {
            return $this->importCSV($filePath, $year, $type, $preview);
        } elseif (in_array($ext, ['xlsx', 'xls'])) {
            // Usar el lector XLSX nativo
            return $this->importExcelNative($filePath, $year, $type, $preview);
        } else {
            $this->errors[] = "Formato de archivo no soportado: {$ext}. Use CSV o XLSX.";
            return $this->buildResult(false, $preview);
        }
    }

    /**
     * Importar desde CSV
     */
    protected function importCSV($filePath, $year, $type, $preview) {
        $handle = fopen($filePath, 'r');
        if (!$handle) {
            $this->errors[] = 'No se pudo abrir el archivo';
            return $this->buildResult(false, $preview);
        }

        // Detectar delimitador
        $firstLine = fgets($handle);
        rewind($handle);
        $delimiter = (strpos($firstLine, ';') !== false) ? ';' : ',';

        // Leer encabezado
        $header = fgetcsv($handle, 0, $delimiter);
        if (!$header) {
            $this->errors[] = 'El archivo está vacío o no tiene encabezado';
            fclose($handle);
            return $this->buildResult(false, $preview);
        }

        // Limpiar BOM y espacios del encabezado
        $header = array_map(function($col) {
            return strtolower(trim(preg_replace('/[\x00-\x1F\x80-\xFF]/', '', $col)));
        }, $header);

        $lineNum = 1;

        while (($row = fgetcsv($handle, 0, $delimiter)) !== false) {
            $lineNum++;

            // Saltar filas vacías
            if (empty(array_filter($row))) continue;

            // Mapear columnas
            $data = array_combine($header, array_pad($row, count($header), ''));

            if ($type === 'obligations') {
                $this->processObligationRow($data, $lineNum, $preview);
            } else {
                $this->processDeadlineRow($data, $year, $lineNum, $preview);
            }
        }

        fclose($handle);

        return $this->buildResult(true, $preview);
    }

    /**
     * Importar desde archivo Excel XLSX usando lector nativo
     */
    protected function importExcelNative($filePath, $year, $type, $preview) {
        $reader = new SimpleXLSXReader($filePath);

        if ($reader->hasErrors()) {
            $this->errors = array_merge($this->errors, $reader->getErrors());
            return $this->buildResult(false, $preview);
        }

        $sheetNames = $reader->getSheetNames();

        if (empty($sheetNames)) {
            $this->errors[] = 'El archivo Excel esta vacio o no se pudo leer';
            return $this->buildResult(false, $preview);
        }

        // Detectar formato del archivo por nombres de hojas
        $format = $this->detectExcelFormatBySheets($sheetNames, $reader);

        if ($format === 'multi_sheet') {
            // Formato con multiples hojas: Obligaciones + Vencimientos
            return $this->importExcelMultiSheet($reader, $year, $preview);
        }

        // Si hay una sola hoja, usar deteccion por contenido
        $rows = $reader->rows();

        if (empty($rows)) {
            $this->errors[] = 'El archivo Excel esta vacio o no se pudo leer';
            return $this->buildResult(false, $preview);
        }

        $format = $this->detectExcelFormat($rows);

        if ($format === 'standard') {
            // Formato estandar: obligation_code, period, period_name, digit_1...digit_0, notes
            return $this->importExcelStandard($rows, $year, $type, $preview);
        } elseif ($format === 'dian') {
            // Formato DIAN: Estructura especial por hojas/obligaciones
            return $this->importExcelDIAN($rows, $year, $preview, $reader);
        } else {
            $this->errors[] = 'No se pudo detectar el formato del archivo Excel. Use el formato estandar o el formato DIAN.';
            return $this->buildResult(false, $preview);
        }
    }

    /**
     * Detectar formato por nombres de hojas
     */
    protected function detectExcelFormatBySheets($sheetNames, $reader) {
        if (empty($sheetNames)) {
            return 'unknown';
        }

        $namesLower = array_map('strtolower', $sheetNames);

        // Buscar hojas "Obligaciones" y "Vencimientos"
        $hasObligations = false;
        $hasDeadlines = false;

        foreach ($namesLower as $name) {
            if (strpos($name, 'obligacion') !== false) {
                $hasObligations = true;
            }
            if (strpos($name, 'vencimiento') !== false) {
                $hasDeadlines = true;
            }
        }

        // Si tiene mas de una hoja y tiene obligaciones o vencimientos
        if (count($sheetNames) > 1 && ($hasObligations || $hasDeadlines)) {
            return 'multi_sheet';
        }

        // Si solo tiene una hoja pero tiene ambos, tambien es multi_sheet
        if ($hasObligations && $hasDeadlines) {
            return 'multi_sheet';
        }

        return 'unknown';
    }

    /**
     * Importar Excel con multiples hojas (Obligaciones + Vencimientos)
     */
    protected function importExcelMultiSheet($reader, $year, $preview) {
        $sheetNames = $reader->getSheetNames();

        // Encontrar hoja de obligaciones y vencimientos por nombre
        $obligationsSheetName = null;
        $deadlinesSheetName = null;

        foreach ($sheetNames as $name) {
            $nameLower = strtolower($name);
            if (strpos($nameLower, 'obligacion') !== false) {
                $obligationsSheetName = $name;
            }
            if (strpos($nameLower, 'vencimiento') !== false) {
                $deadlinesSheetName = $name;
            }
        }

        // Log para debug
        $this->warnings[] = "Hojas encontradas: " . implode(', ', $sheetNames);

        // Primero importar obligaciones si existe la hoja
        if ($obligationsSheetName !== null) {
            $rows = $reader->getSheet($obligationsSheetName);
            $this->warnings[] = "Hoja Obligaciones: " . count($rows) . " filas";
            if (!empty($rows)) {
                $this->importObligationsSheet($rows, $preview);
            }
        }

        // Luego importar vencimientos
        if ($deadlinesSheetName !== null) {
            $rows = $reader->getSheet($deadlinesSheetName);
            $this->warnings[] = "Hoja Vencimientos: " . count($rows) . " filas";
            if (!empty($rows)) {
                $this->importDeadlinesSheet($rows, $year, $preview);
            }
        } else {
            $this->errors[] = 'No se encontro la hoja de Vencimientos';
        }

        return $this->buildResult(true, $preview);
    }

    /**
     * Importar hoja de obligaciones
     */
    protected function importObligationsSheet($rows, $preview) {
        if (count($rows) < 2) {
            return;
        }

        // Primera fila es header
        $header = array_map(function($col) {
            return strtolower(trim(preg_replace('/[\x00-\x1F\x80-\xFF]/', '', $col)));
        }, $rows[0]);

        // Mapear nombres de columnas
        $colMap = $this->mapObligationColumns($header);

        for ($i = 1; $i < count($rows); $i++) {
            $row = $rows[$i];
            if (empty(array_filter($row))) continue;

            $code = trim($row[$colMap['code']] ?? '');
            $name = trim($row[$colMap['name']] ?? '');
            $description = trim($row[$colMap['description']] ?? '');
            $frequency = trim($row[$colMap['frequency']] ?? 'custom');
            $taxpayerType = trim($row[$colMap['taxpayer_type']] ?? '');

            if (empty($code) || empty($name)) continue;

            if (!$preview) {
                $this->upsertObligationDirect($code, $name, $description, $frequency, $taxpayerType);
            } else {
                // En modo preview, solo contar sin verificar en BD
                if ($this->sqlite) {
                    $res = $this->sqlite->query("SELECT id FROM tax_obligations WHERE code = ?", $code);
                    if ($this->sqlite->res2row($res)) {
                        $this->stats['obligations_updated']++;
                    } else {
                        $this->stats['obligations_new']++;
                    }
                } else {
                    // Si no hay BD, asumir que es nuevo
                    $this->stats['obligations_new']++;
                }
            }
        }
    }

    /**
     * Mapear columnas de obligaciones
     */
    protected function mapObligationColumns($header) {
        $map = [
            'code' => null,
            'name' => null,
            'description' => null,
            'frequency' => null,
            'taxpayer_type' => null
        ];

        foreach ($header as $index => $col) {
            $col = strtolower(trim($col));

            if (in_array($col, ['codigo', 'code', 'cod'])) {
                $map['code'] = $index;
            } elseif (in_array($col, ['nombre', 'name'])) {
                $map['name'] = $index;
            } elseif (in_array($col, ['descripcion', 'description', 'desc'])) {
                $map['description'] = $index;
            } elseif (in_array($col, ['frecuencia', 'frequency', 'freq'])) {
                $map['frequency'] = $index;
            } elseif (strpos($col, 'tipo') !== false || strpos($col, 'taxpayer') !== false) {
                $map['taxpayer_type'] = $index;
            }
        }

        return $map;
    }

    /**
     * Insertar o actualizar obligacion directamente
     */
    protected function upsertObligationDirect($code, $name, $description, $frequency, $taxpayerType) {
        $res = $this->sqlite->query("SELECT id FROM tax_obligations WHERE code = ?", $code);
        $existing = $this->sqlite->res2row($res);

        if ($existing) {
            $this->sqlite->query(
                "UPDATE tax_obligations SET name = ?, description = ?, frequency = ?,
                 taxpayer_type = ?, updated_at = ? WHERE code = ?",
                $name, $description, $frequency, $taxpayerType, date('Y-m-d H:i:s'), $code
            );
            $this->stats['obligations_updated']++;
        } else {
            $this->sqlite->query(
                "INSERT INTO tax_obligations (code, name, description, frequency, taxpayer_type, is_active)
                 VALUES (?, ?, ?, ?, ?, 1)",
                $code, $name, $description, $frequency, $taxpayerType
            );
            $this->stats['obligations_new']++;
        }
    }

    /**
     * Importar hoja de vencimientos
     */
    protected function importDeadlinesSheet($rows, $year, $preview) {
        if (count($rows) < 2) {
            return;
        }

        // Primera fila es header
        $header = array_map(function($col) {
            return strtolower(trim(preg_replace('/[\x00-\x1F\x80-\xFF]/', '', $col)));
        }, $rows[0]);

        // Mapear columnas
        $colMap = $this->mapDeadlineColumns($header);

        if ($colMap['obligation_code'] === null) {
            $this->errors[] = 'No se encontro la columna de codigo de obligacion en la hoja de vencimientos';
            return;
        }

        for ($i = 1; $i < count($rows); $i++) {
            $row = $rows[$i];
            if (empty(array_filter($row))) continue;

            // Obtener año de la fila si existe, sino usar el proporcionado
            $rowYear = $year;
            if ($colMap['year'] !== null && !empty($row[$colMap['year']])) {
                $rowYear = (int)$row[$colMap['year']];
            }

            $obligationCode = trim($row[$colMap['obligation_code']] ?? '');
            $period = trim($row[$colMap['period']] ?? '');
            $periodName = trim($row[$colMap['period_name']] ?? $period);

            if (empty($obligationCode) || empty($period)) continue;

            // Extraer digitos
            $digits = [];
            $hasValidDate = false;

            for ($d = 0; $d <= 9; $d++) {
                $colIndex = $colMap['digits'][$d] ?? null;
                if ($colIndex !== null && isset($row[$colIndex])) {
                    $dateValue = trim($row[$colIndex]);
                    $parsedDate = $this->parseDate($dateValue);
                    $digits[$d] = $parsedDate;
                    if ($parsedDate) {
                        $hasValidDate = true;
                    }
                } else {
                    $digits[$d] = null;
                }
            }

            if (!$hasValidDate) {
                $this->warnings[] = "Fila " . ($i + 1) . ": No hay fechas validas para {$obligationCode} - {$period}";
                continue;
            }

            $notes = isset($colMap['notes']) && $colMap['notes'] !== null ?
                     trim($row[$colMap['notes']] ?? '') : '';

            if (!$preview) {
                // Verificar que la obligacion existe
                if ($this->sqlite) {
                    $res = $this->sqlite->query("SELECT id FROM tax_obligations WHERE code = ?", $obligationCode);
                    if (!$this->sqlite->res2row($res)) {
                        $this->warnings[] = "Fila " . ($i + 1) . ": Obligacion '{$obligationCode}' no existe, creandola...";
                        $this->createObligationIfNotExists($obligationCode);
                    }
                    $this->upsertDeadline($rowYear, $obligationCode, $period, $periodName, $digits, $notes);
                }
            } else {
                // En modo preview, solo contar
                if ($this->sqlite) {
                    $res = $this->sqlite->query(
                        "SELECT id FROM tax_calendar_master WHERE year = ? AND obligation_code = ? AND period = ?",
                        $rowYear, $obligationCode, $period
                    );
                    if ($this->sqlite->res2row($res)) {
                        $this->stats['deadlines_updated']++;
                    } else {
                        $this->stats['deadlines_new']++;
                    }
                } else {
                    // Si no hay BD, asumir nuevo
                    $this->stats['deadlines_new']++;
                }
            }
        }
    }

    /**
     * Mapear columnas de vencimientos
     */
    protected function mapDeadlineColumns($header) {
        $map = [
            'year' => null,
            'obligation_code' => null,
            'period' => null,
            'period_name' => null,
            'digits' => [],
            'notes' => null
        ];

        foreach ($header as $index => $col) {
            $col = strtolower(trim($col));

            // Año
            if (in_array($col, ['year', 'ano', 'a�o', 'anio'])) {
                $map['year'] = $index;
            }
            // Codigo obligacion
            elseif (in_array($col, ['codigo_obligacion', 'obligation_code', 'codigo', 'code', 'obligacion'])) {
                $map['obligation_code'] = $index;
            }
            // Periodo
            elseif (in_array($col, ['periodo', 'period', 'per'])) {
                $map['period'] = $index;
            }
            // Nombre periodo
            elseif (strpos($col, 'nombre') !== false || in_array($col, ['period_name', 'periodname'])) {
                $map['period_name'] = $index;
            }
            // Notas
            elseif (in_array($col, ['notas', 'notes', 'nota', 'observaciones'])) {
                $map['notes'] = $index;
            }
            // Digitos - formato: digit_1, digito_1, dig_1, d1, 1, etc.
            else {
                $digitMatch = null;
                if (preg_match('/^(digit[o_]?|dig[_.]?|d)(\d)$/i', $col, $m)) {
                    $digitMatch = (int)$m[2];
                } elseif (preg_match('/^(\d)$/', $col, $m)) {
                    $digitMatch = (int)$m[1];
                } elseif (preg_match('/digito[_\s]?(\d)/i', $col, $m)) {
                    $digitMatch = (int)$m[1];
                }

                if ($digitMatch !== null && $digitMatch >= 0 && $digitMatch <= 9) {
                    $map['digits'][$digitMatch] = $index;
                }
            }
        }

        return $map;
    }

    /**
     * Detectar el formato del archivo Excel
     */
    protected function detectExcelFormat($rows) {
        if (empty($rows) || empty($rows[0])) {
            return 'unknown';
        }

        $header = $rows[0];
        $headerLower = array_map(function($h) {
            return strtolower(trim($h));
        }, $header);

        // Verificar si tiene columnas del formato estandar
        $standardCols = ['obligation_code', 'codigo', 'codigo_obligacion', 'period', 'periodo'];
        $digitCols = ['digit_1', 'digito_1', '1', 'dig 1', 'dig. 1'];

        foreach ($standardCols as $col) {
            if (in_array($col, $headerLower)) {
                return 'standard';
            }
        }

        foreach ($digitCols as $col) {
            if (in_array($col, $headerLower)) {
                return 'standard';
            }
        }

        // Verificar formato DIAN (primera columna tiene nombre de periodo como "Enero", "Febrero", etc.)
        // O si las columnas son numeros del 1 al 0
        $numericHeaders = 0;
        foreach ($header as $h) {
            $h = trim($h);
            if (is_numeric($h) || preg_match('/^(0|1|2|3|4|5|6|7|8|9)$/', $h)) {
                $numericHeaders++;
            }
        }

        if ($numericHeaders >= 5) {
            return 'dian';
        }

        // Si la segunda fila tiene fechas, probablemente es formato DIAN
        if (isset($rows[1])) {
            $dateCount = 0;
            foreach ($rows[1] as $cell) {
                if ($this->parseDate($cell)) {
                    $dateCount++;
                }
            }
            if ($dateCount >= 5) {
                return 'dian';
            }
        }

        return 'unknown';
    }

    /**
     * Importar Excel con formato estandar
     */
    protected function importExcelStandard($rows, $year, $type, $preview) {
        if (empty($rows)) {
            $this->errors[] = 'No hay datos en el archivo';
            return $this->buildResult(false, $preview);
        }

        // Primera fila es header
        $header = array_map(function($col) {
            return strtolower(trim(preg_replace('/[\x00-\x1F\x80-\xFF]/', '', $col)));
        }, array_shift($rows));

        $lineNum = 1;

        foreach ($rows as $row) {
            $lineNum++;

            // Saltar filas vacias
            if (empty(array_filter($row))) continue;

            // Asegurar mismo numero de columnas
            $row = array_pad($row, count($header), '');
            $row = array_slice($row, 0, count($header));

            // Mapear columnas
            $data = @array_combine($header, $row);
            if ($data === false) continue;

            if ($type === 'obligations') {
                $this->processObligationRow($data, $lineNum, $preview);
            } else {
                $this->processDeadlineRow($data, $year, $lineNum, $preview);
            }
        }

        return $this->buildResult(true, $preview);
    }

    /**
     * Importar Excel con formato DIAN
     * Este formato tiene la obligacion como titulo y los periodos como filas
     */
    protected function importExcelDIAN($rows, $year, $preview, $reader) {
        if (empty($rows)) {
            $this->errors[] = 'No hay datos en el archivo';
            return $this->buildResult(false, $preview);
        }

        // Obtener nombres de las hojas
        $sheetNames = $reader->getSheetNames();

        // Procesar cada hoja como una obligacion diferente
        foreach ($sheetNames as $sheetName) {
            $sheetRows = $reader->getSheet($sheetName);
            if (empty($sheetRows)) continue;

            // El nombre de la hoja podria ser el codigo de la obligacion
            $obligationCode = $this->detectObligationCode($sheetName, $sheetRows);

            if (!$obligationCode) {
                $this->warnings[] = "Hoja '{$sheetName}': No se pudo determinar el codigo de obligacion";
                continue;
            }

            // Procesar la hoja
            $this->processSheetAsDIAN($sheetRows, $obligationCode, $year, $preview);
        }

        // Si solo hay una hoja, procesarla buscando multiples obligaciones
        if (count($sheetNames) <= 1) {
            $this->processMultiObligationSheet($rows, $year, $preview);
        }

        return $this->buildResult(true, $preview);
    }

    /**
     * Detectar codigo de obligacion desde nombre de hoja o contenido
     */
    protected function detectObligationCode($sheetName, $rows) {
        // Mapeo de nombres comunes a codigos
        $nameToCode = [
            'retefuente' => 'RETEFUENTE',
            'retencion' => 'RETEFUENTE',
            'iva' => 'IVA',
            'iva bimestral' => 'IVA_BIM',
            'iva cuatrimestral' => 'IVA_CUATRI',
            'renta' => 'RENTA',
            'renta pj' => 'RENTA_PJ',
            'renta pn' => 'RENTA_PN',
            'ica' => 'ICA',
            'rete ica' => 'RETEICA',
            'impuesto patrimonio' => 'PATRIMONIO',
            'riqueza' => 'PATRIMONIO',
            'informacion exogena' => 'EXOGENA',
            'exogena' => 'EXOGENA',
            'ganancias ocasionales' => 'GAN_OCAS',
        ];

        $nameLower = strtolower(trim($sheetName));

        foreach ($nameToCode as $name => $code) {
            if (strpos($nameLower, $name) !== false) {
                return $code;
            }
        }

        // Si el nombre de la hoja es un codigo valido, usarlo
        $codeUpper = strtoupper(preg_replace('/[^a-zA-Z0-9_]/', '', $sheetName));
        if (strlen($codeUpper) >= 2 && strlen($codeUpper) <= 20) {
            return $codeUpper;
        }

        // Buscar en la primera fila
        if (!empty($rows[0][0])) {
            $firstCell = strtolower(trim($rows[0][0]));
            foreach ($nameToCode as $name => $code) {
                if (strpos($firstCell, $name) !== false) {
                    return $code;
                }
            }
        }

        return null;
    }

    /**
     * Procesar hoja en formato DIAN
     */
    protected function processSheetAsDIAN($rows, $obligationCode, $year, $preview) {
        if (count($rows) < 2) {
            return;
        }

        // Encontrar fila de encabezado con digitos
        $headerRowIndex = $this->findDigitHeaderRow($rows);

        if ($headerRowIndex === null) {
            $this->warnings[] = "Obligacion {$obligationCode}: No se encontro fila de encabezado con digitos";
            return;
        }

        $header = $rows[$headerRowIndex];

        // Mapear columnas de digitos
        $digitColumns = $this->mapDigitColumns($header);

        if (empty($digitColumns)) {
            $this->warnings[] = "Obligacion {$obligationCode}: No se pudieron mapear las columnas de digitos";
            return;
        }

        // Procesar filas de datos (despues del encabezado)
        for ($i = $headerRowIndex + 1; $i < count($rows); $i++) {
            $row = $rows[$i];
            if (empty(array_filter($row))) continue;

            // Primera columna es el periodo
            $period = $this->extractPeriod($row[0] ?? '');
            $periodName = trim($row[0] ?? '');

            if (empty($period)) continue;

            // Extraer fechas por digito
            $digits = [];
            $hasValidDate = false;

            foreach ($digitColumns as $digit => $colIndex) {
                $dateValue = isset($row[$colIndex]) ? $row[$colIndex] : '';
                $parsedDate = $this->parseDate($dateValue);
                $digits[$digit] = $parsedDate;

                if ($parsedDate) {
                    $hasValidDate = true;
                }
            }

            if (!$hasValidDate) continue;

            // Verificar que la obligacion exista
            $res = $this->sqlite->query("SELECT id FROM tax_obligations WHERE code = ?", $obligationCode);
            if (!$this->sqlite->res2row($res)) {
                // Crear la obligacion automaticamente
                if (!$preview) {
                    $this->createObligationIfNotExists($obligationCode);
                }
            }

            if (!$preview) {
                $this->upsertDeadline($year, $obligationCode, $period, $periodName, $digits, '');
            } else {
                $res = $this->sqlite->query(
                    "SELECT id FROM tax_calendar_master WHERE year = ? AND obligation_code = ? AND period = ?",
                    $year, $obligationCode, $period
                );
                if ($this->sqlite->res2row($res)) {
                    $this->stats['deadlines_updated']++;
                } else {
                    $this->stats['deadlines_new']++;
                }
            }
        }
    }

    /**
     * Procesar hoja con multiples obligaciones
     * Formato donde hay secciones por cada obligacion
     */
    protected function processMultiObligationSheet($rows, $year, $preview) {
        $currentObligation = null;
        $headerRow = null;
        $digitColumns = [];

        foreach ($rows as $rowIndex => $row) {
            if (empty(array_filter($row))) continue;

            $firstCell = trim($row[0] ?? '');

            // Detectar si es encabezado de seccion (nombre de obligacion)
            $detectedCode = $this->detectObligationFromCell($firstCell);
            if ($detectedCode && $this->looksLikeObligationHeader($row)) {
                $currentObligation = $detectedCode;
                $headerRow = null;
                $digitColumns = [];
                continue;
            }

            // Detectar fila de digitos
            if ($this->isDigitHeaderRow($row)) {
                $headerRow = $row;
                $digitColumns = $this->mapDigitColumns($row);
                continue;
            }

            // Si tenemos obligacion y columnas mapeadas, procesar fila de datos
            if ($currentObligation && !empty($digitColumns)) {
                $period = $this->extractPeriod($firstCell);
                if (empty($period)) continue;

                $periodName = $firstCell;

                $digits = [];
                $hasValidDate = false;

                foreach ($digitColumns as $digit => $colIndex) {
                    $dateValue = isset($row[$colIndex]) ? $row[$colIndex] : '';
                    $parsedDate = $this->parseDate($dateValue);
                    $digits[$digit] = $parsedDate;

                    if ($parsedDate) {
                        $hasValidDate = true;
                    }
                }

                if (!$hasValidDate) continue;

                if (!$preview) {
                    $this->createObligationIfNotExists($currentObligation);
                    $this->upsertDeadline($year, $currentObligation, $period, $periodName, $digits, '');
                } else {
                    $res = $this->sqlite->query(
                        "SELECT id FROM tax_calendar_master WHERE year = ? AND obligation_code = ? AND period = ?",
                        $year, $currentObligation, $period
                    );
                    if ($this->sqlite->res2row($res)) {
                        $this->stats['deadlines_updated']++;
                    } else {
                        $this->stats['deadlines_new']++;
                    }
                }
            }
        }
    }

    /**
     * Buscar fila que contiene encabezados de digitos
     */
    protected function findDigitHeaderRow($rows) {
        for ($i = 0; $i < min(10, count($rows)); $i++) {
            if ($this->isDigitHeaderRow($rows[$i])) {
                return $i;
            }
        }
        return null;
    }

    /**
     * Verificar si una fila es encabezado de digitos
     */
    protected function isDigitHeaderRow($row) {
        $digitCount = 0;
        foreach ($row as $cell) {
            $cell = trim(strtolower($cell));
            // Buscar patrones como: 1, 2, 3, "dig 1", "digito 1", "ultimo digito 1"
            if (preg_match('/^(0|1|2|3|4|5|6|7|8|9)$/', $cell) ||
                preg_match('/dig(ito)?\.?\s*(0|1|2|3|4|5|6|7|8|9)/i', $cell)) {
                $digitCount++;
            }
        }
        return $digitCount >= 5;
    }

    /**
     * Mapear columnas a digitos
     */
    protected function mapDigitColumns($header) {
        $map = [];

        foreach ($header as $colIndex => $cell) {
            $cell = trim(strtolower($cell));

            // Patron: numero solo
            if (preg_match('/^(0|1|2|3|4|5|6|7|8|9)$/', $cell, $m)) {
                $map[(int)$m[1]] = $colIndex;
                continue;
            }

            // Patron: "dig 1", "digito_1", "digit 1", etc.
            if (preg_match('/dig(ito|it)?[_.\s]*(0|1|2|3|4|5|6|7|8|9)/i', $cell, $m)) {
                $map[(int)$m[2]] = $colIndex;
                continue;
            }

            // Patron: "ultimo digito del NIT 1"
            if (preg_match('/ultimo\s+digito.*?(0|1|2|3|4|5|6|7|8|9)/i', $cell, $m)) {
                $map[(int)$m[1]] = $colIndex;
            }
        }

        return $map;
    }

    /**
     * Detectar codigo de obligacion desde una celda
     */
    protected function detectObligationFromCell($cell) {
        $nameToCode = [
            'retefuente' => 'RETEFUENTE',
            'retencion en la fuente' => 'RETEFUENTE',
            'iva bimestral' => 'IVA_BIM',
            'iva cuatrimestral' => 'IVA_CUATRI',
            'declaracion de iva' => 'IVA',
            'impuesto de renta' => 'RENTA',
            'renta personas juridicas' => 'RENTA_PJ',
            'renta personas naturales' => 'RENTA_PN',
            'grandes contribuyentes' => 'RENTA_GC',
            'informacion exogena' => 'EXOGENA',
        ];

        $cellLower = strtolower(trim($cell));

        foreach ($nameToCode as $name => $code) {
            if (strpos($cellLower, $name) !== false) {
                return $code;
            }
        }

        return null;
    }

    /**
     * Verificar si una fila parece encabezado de obligacion
     */
    protected function looksLikeObligationHeader($row) {
        // Si la primera celda tiene texto largo y las demas estan vacias
        $nonEmpty = array_filter($row);
        return count($nonEmpty) <= 2 && strlen(trim($row[0] ?? '')) > 10;
    }

    /**
     * Extraer codigo de periodo desde texto
     */
    protected function extractPeriod($text) {
        $text = trim(strtolower($text));

        // Mapeo de meses
        $months = [
            'enero' => '01', 'febrero' => '02', 'marzo' => '03',
            'abril' => '04', 'mayo' => '05', 'junio' => '06',
            'julio' => '07', 'agosto' => '08', 'septiembre' => '09',
            'octubre' => '10', 'noviembre' => '11', 'diciembre' => '12',
            'ene' => '01', 'feb' => '02', 'mar' => '03',
            'abr' => '04', 'may' => '05', 'jun' => '06',
            'jul' => '07', 'ago' => '08', 'sep' => '09',
            'oct' => '10', 'nov' => '11', 'dic' => '12',
        ];

        foreach ($months as $name => $code) {
            if (strpos($text, $name) !== false) {
                return $code;
            }
        }

        // Bimestres
        if (preg_match('/bimestre\s*(\d+)/i', $text, $m)) {
            return 'B' . $m[1];
        }
        if (preg_match('/(\d+)\s*bimestre/i', $text, $m)) {
            return 'B' . $m[1];
        }

        // Cuatrimestres
        if (preg_match('/cuatrimestre\s*(\d+)/i', $text, $m)) {
            return 'C' . $m[1];
        }

        // Trimestres
        if (preg_match('/trimestre\s*(\d+)/i', $text, $m)) {
            return 'T' . $m[1];
        }

        // Si es numero puro (01, 02, etc.)
        if (preg_match('/^(\d{1,2})$/', $text, $m)) {
            return str_pad($m[1], 2, '0', STR_PAD_LEFT);
        }

        return '';
    }

    /**
     * Crear obligacion si no existe
     */
    protected function createObligationIfNotExists($code) {
        $res = $this->sqlite->query("SELECT id FROM tax_obligations WHERE code = ?", $code);
        if (!$this->sqlite->res2row($res)) {
            // Mapeo de nombres
            $names = [
                'RETEFUENTE' => 'Retencion en la Fuente',
                'IVA' => 'Impuesto al Valor Agregado',
                'IVA_BIM' => 'IVA Bimestral',
                'IVA_CUATRI' => 'IVA Cuatrimestral',
                'RENTA' => 'Impuesto de Renta',
                'RENTA_PJ' => 'Renta Personas Juridicas',
                'RENTA_PN' => 'Renta Personas Naturales',
                'RENTA_GC' => 'Renta Grandes Contribuyentes',
                'EXOGENA' => 'Informacion Exogena',
                'ICA' => 'Industria y Comercio',
                'RETEICA' => 'Retencion ICA',
            ];

            $name = isset($names[$code]) ? $names[$code] : $code;

            $this->sqlite->query(
                "INSERT INTO tax_obligations (code, name, description, frequency, is_active)
                 VALUES (?, ?, ?, ?, 1)",
                $code, $name, 'Creado automaticamente', 'monthly'
            );
            $this->stats['obligations_new']++;
        }
    }

    /**
     * Procesar fila de obligación
     */
    protected function processObligationRow($data, $lineNum, $preview) {
        // Campos requeridos
        $code = trim($data['code'] ?? $data['codigo'] ?? '');
        $name = trim($data['name'] ?? $data['nombre'] ?? '');
        $frequency = trim($data['frequency'] ?? $data['frecuencia'] ?? 'custom');

        if (empty($code)) {
            $this->errors[] = "Linea {$lineNum}: Codigo de obligacion vacio";
            $this->stats['errors']++;
            return;
        }

        if (empty($name)) {
            $this->errors[] = "Linea {$lineNum}: Nombre de obligacion vacio";
            $this->stats['errors']++;
            return;
        }

        // Validar frecuencia
        $validFrequencies = ['monthly', 'bimonthly', 'quarterly', 'annual', 'custom'];
        if (!in_array($frequency, $validFrequencies)) {
            $this->warnings[] = "Linea {$lineNum}: Frecuencia '{$frequency}' invalida, usando 'custom'";
            $frequency = 'custom';
        }

        if (!$preview) {
            $this->upsertObligation($code, $name, $data, $frequency);
        } else {
            // Solo contar
            $res = $this->sqlite->query("SELECT id FROM tax_obligations WHERE code = ?", $code);
            if ($this->sqlite->res2row($res)) {
                $this->stats['obligations_updated']++;
            } else {
                $this->stats['obligations_new']++;
            }
        }
    }

    /**
     * Procesar fila de vencimiento
     */
    protected function processDeadlineRow($data, $year, $lineNum, $preview) {
        // Mapear campos con múltiples nombres posibles
        $obligationCode = trim($data['obligation_code'] ?? $data['codigo_obligacion'] ?? $data['codigo'] ?? '');
        $period = trim($data['period'] ?? $data['periodo'] ?? '');
        $periodName = trim($data['period_name'] ?? $data['nombre_periodo'] ?? $period);

        if (empty($obligationCode)) {
            $this->errors[] = "Linea {$lineNum}: Codigo de obligacion vacio";
            $this->stats['errors']++;
            return;
        }

        if (empty($period)) {
            $this->errors[] = "Linea {$lineNum}: Periodo vacio";
            $this->stats['errors']++;
            return;
        }

        // Verificar que la obligación existe
        $res = $this->sqlite->query("SELECT id FROM tax_obligations WHERE code = ?", $obligationCode);
        if (!$this->sqlite->res2row($res)) {
            $this->errors[] = "Linea {$lineNum}: Obligacion '{$obligationCode}' no existe en el catalogo";
            $this->stats['errors']++;
            return;
        }

        // Procesar fechas por dígito
        $digits = [];
        $hasValidDate = false;

        for ($d = 0; $d <= 9; $d++) {
            $colName = ($d == 0) ? 'digit_0' : "digit_{$d}";
            $altColName = ($d == 0) ? 'digito_0' : "digito_{$d}";

            $dateValue = trim($data[$colName] ?? $data[$altColName] ?? '');
            $parsedDate = $this->parseDate($dateValue);

            $digits[$d] = $parsedDate;

            if ($parsedDate) {
                $hasValidDate = true;
            }
        }

        if (!$hasValidDate) {
            $this->errors[] = "Linea {$lineNum}: No hay fechas validas para ningun digito";
            $this->stats['errors']++;
            return;
        }

        $notes = trim($data['notes'] ?? $data['notas'] ?? '');

        if (!$preview) {
            $this->upsertDeadline($year, $obligationCode, $period, $periodName, $digits, $notes);
        } else {
            // Solo contar
            $res = $this->sqlite->query(
                "SELECT id FROM tax_calendar_master WHERE year = ? AND obligation_code = ? AND period = ?",
                $year, $obligationCode, $period
            );
            if ($this->sqlite->res2row($res)) {
                $this->stats['deadlines_updated']++;
            } else {
                $this->stats['deadlines_new']++;
            }
        }
    }

    /**
     * Insertar o actualizar obligación
     */
    protected function upsertObligation($code, $name, $data, $frequency) {
        $description = trim($data['description'] ?? $data['descripcion'] ?? '');
        $taxpayerType = trim($data['taxpayer_type'] ?? $data['tipo_contribuyente'] ?? '');

        $res = $this->sqlite->query("SELECT id FROM tax_obligations WHERE code = ?", $code);
        $existing = $this->sqlite->res2row($res);

        if ($existing) {
            $this->sqlite->query(
                "UPDATE tax_obligations SET name = ?, description = ?, frequency = ?,
                 taxpayer_type = ?, updated_at = ? WHERE code = ?",
                $name, $description, $frequency, $taxpayerType, date('Y-m-d H:i:s'), $code
            );
            $this->stats['obligations_updated']++;
        } else {
            $this->sqlite->query(
                "INSERT INTO tax_obligations (code, name, description, frequency, taxpayer_type)
                 VALUES (?, ?, ?, ?, ?)",
                $code, $name, $description, $frequency, $taxpayerType
            );
            $this->stats['obligations_new']++;
        }
    }

    /**
     * Insertar o actualizar vencimiento
     */
    protected function upsertDeadline($year, $obligationCode, $period, $periodName, $digits, $notes) {
        $res = $this->sqlite->query(
            "SELECT id FROM tax_calendar_master WHERE year = ? AND obligation_code = ? AND period = ?",
            $year, $obligationCode, $period
        );
        $existing = $this->sqlite->res2row($res);

        if ($existing) {
            $this->sqlite->query(
                "UPDATE tax_calendar_master SET
                 period_name = ?, digit_1 = ?, digit_2 = ?, digit_3 = ?, digit_4 = ?,
                 digit_5 = ?, digit_6 = ?, digit_7 = ?, digit_8 = ?, digit_9 = ?,
                 digit_0 = ?, notes = ?, updated_at = ?
                 WHERE id = ?",
                $periodName,
                $digits[1], $digits[2], $digits[3], $digits[4], $digits[5],
                $digits[6], $digits[7], $digits[8], $digits[9], $digits[0],
                $notes,
                date('Y-m-d H:i:s'),
                $existing['id']
            );
            $this->stats['deadlines_updated']++;
        } else {
            $this->sqlite->query(
                "INSERT INTO tax_calendar_master
                 (year, obligation_code, period, period_name, digit_1, digit_2, digit_3,
                  digit_4, digit_5, digit_6, digit_7, digit_8, digit_9, digit_0, notes)
                 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
                $year, $obligationCode, $period, $periodName,
                $digits[1], $digits[2], $digits[3], $digits[4], $digits[5],
                $digits[6], $digits[7], $digits[8], $digits[9], $digits[0],
                $notes
            );
            $this->stats['deadlines_new']++;
        }
    }

    /**
     * Parsear fecha desde varios formatos
     * Soporta: seriales Excel, Y-m-d, d/m/Y, m/d/Y, con - / o .
     * Tambien fechas como texto con o sin ceros: "2026-2-10", "2026/02/10"
     */
    protected function parseDate($value) {
        if (empty($value) || $value === '-' || $value === 'N/A') {
            return null;
        }

        $value = trim($value);

        // Si es numero (formato Excel serial)
        if (is_numeric($value) && $value > 40000) {
            $serial = floatval($value);
            $unix = ($serial - 25569) * 86400;
            return gmdate('Y-m-d', $unix);
        }

        // Normalizar separadores: reemplazar . por / para unificar
        $normalized = str_replace('.', '/', $value);

        // Intentar formatos estrictos primero
        // Priorizar formato colombiano (d/m/Y) sobre estadounidense (m/d/Y)
        $formats = [
            'Y-m-d',       // 2026-02-10
            'Y/m/d',       // 2026/02/10
            'd/m/Y',       // 10/02/2026 (formato colombiano, mayor prioridad)
            'd-m-Y',       // 10-02-2026
            'm/d/Y',       // 02/10/2026 (menor prioridad)
        ];

        foreach ($formats as $format) {
            $testValue = (strpos($format, '/') !== false) ? $normalized : $value;
            $date = \DateTime::createFromFormat($format, $testValue);
            if ($date && $date->format($format) === $testValue) {
                $year = (int)$date->format('Y');
                if ($year >= 2020 && $year <= 2050) {
                    return $date->format('Y-m-d');
                }
            }
        }

        // Intentar con regex para fechas sin ceros: "2026-2-10", "2026/2/10"
        if (preg_match('/^(\d{4})[\-\/](\d{1,2})[\-\/](\d{1,2})$/', $normalized, $m)) {
            $y = (int)$m[1]; $mo = (int)$m[2]; $d = (int)$m[3];
            if ($y >= 2020 && $y <= 2050 && checkdate($mo, $d, $y)) {
                return sprintf('%04d-%02d-%02d', $y, $mo, $d);
            }
        }

        // Intentar formato d/m/Y sin ceros: "10/2/2026"
        if (preg_match('/^(\d{1,2})[\-\/](\d{1,2})[\-\/](\d{4})$/', $normalized, $m)) {
            $d = (int)$m[1]; $mo = (int)$m[2]; $y = (int)$m[3];
            if ($y >= 2020 && $y <= 2050 && checkdate($mo, $d, $y)) {
                return sprintf('%04d-%02d-%02d', $y, $mo, $d);
            }
        }

        // Ultimo recurso: parseo generico PHP
        try {
            $date = new \DateTime($value);
            $year = (int)$date->format('Y');
            if ($year >= 2020 && $year <= 2050) {
                return $date->format('Y-m-d');
            }
        } catch (\Exception $e) {
            // Ignorar
        }

        return null;
    }

    /**
     * Construir resultado
     */
    protected function buildResult($success, $preview) {
        return [
            'success' => $success && empty($this->errors),
            'preview' => $preview,
            'stats' => $this->stats,
            'errors' => $this->errors,
            'warnings' => $this->warnings
        ];
    }

    /**
     * Generar template CSV para vencimientos
     */
    public static function generateDeadlinesTemplate() {
        $header = [
            'obligation_code', 'period', 'period_name',
            'digit_1', 'digit_2', 'digit_3', 'digit_4', 'digit_5',
            'digit_6', 'digit_7', 'digit_8', 'digit_9', 'digit_0',
            'notes'
        ];

        $example = [
            'RETEFUENTE', '01', 'Enero',
            '2026-02-10', '2026-02-11', '2026-02-12', '2026-02-13', '2026-02-14',
            '2026-02-17', '2026-02-18', '2026-02-19', '2026-02-20', '2026-02-21',
            'Declaracion y pago'
        ];

        $output = fopen('php://temp', 'r+');
        fputcsv($output, $header);
        fputcsv($output, $example);
        rewind($output);
        $content = stream_get_contents($output);
        fclose($output);

        return $content;
    }

    /**
     * Generar template CSV para obligaciones
     */
    public static function generateObligationsTemplate() {
        $header = ['code', 'name', 'description', 'frequency', 'taxpayer_type'];

        $examples = [
            ['RETEFUENTE', 'Retencion en la Fuente', 'Declaracion mensual de retencion', 'monthly', 'agente_retencion'],
            ['IVA_BIM', 'IVA Bimestral', 'Declaracion bimestral de IVA', 'bimonthly', 'responsable_iva'],
            ['RENTA_PJ', 'Renta Personas Juridicas', 'Declaracion anual de renta', 'annual', 'persona_juridica'],
        ];

        $output = fopen('php://temp', 'r+');
        fputcsv($output, $header);
        foreach ($examples as $example) {
            fputcsv($output, $example);
        }
        rewind($output);
        $content = stream_get_contents($output);
        fclose($output);

        return $content;
    }
}
