<?php

namespace App\Services;

use App\Models\Contract;
use App\Models\Course;
use App\Models\Period;
use App\Models\PeriodPricingConfig;
use App\Models\StatusContract;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Illuminate\Support\Facades\Log;
use Exception;

class PeriodPricingService
{
    private const VALID_TYPES = ['incorporation', 'tuition', 'enrollment_fee', 'third_party'];
    private const VALID_CURRENCIES = ['UF', 'CLP'];

    /**
     * Importar configuraciones de precios desde un archivo Excel.
     */
    public function importFromExcel($periodId, $file)
    {
        try {
            $spreadsheet = IOFactory::load($file->getPathname());
        } catch (Exception $e) {
            throw new Exception('No se pudo leer el archivo Excel: ' . $e->getMessage(), 400);
        }

        $sheet = $spreadsheet->getActiveSheet();
        $rows = $sheet->toArray(null, true, true, true);

        if (count($rows) < 2) {
            throw new Exception('El archivo está vacío o solo tiene encabezados', 400);
        }

        // Extraer y normalizar headers
        $headers = array_shift($rows);
        $headerMap = [];
        foreach ($headers as $col => $val) {
            $headerMap[$col] = strtoupper(trim(strUpperSinTildes($val ?? '')));
        }

        $successful = [];
        $errors = [];
        $created = 0;
        $updated = 0;
        $rowNum = 1;

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

            // Saltar filas completamente vacías
            $rowValues = array_filter(array_values($row), fn($v) => $v !== null && trim($v) !== '');
            if (empty($rowValues)) continue;

            // Construir data mapeada por header normalizado
            $data = [];
            foreach ($headerMap as $col => $header) {
                $data[$header] = trim($row[$col] ?? '');
            }

            try {
                $result = $this->processRow($periodId, $data, $rowNum);
                $result['isUpdate'] ? $updated++ : $created++;
                $successful[] = [
                    'row' => $rowNum,
                    'concept_code' => $result['concept_code'],
                    'action' => $result['isUpdate'] ? 'ACTUALIZADO' : 'CREADO',
                ];
            } catch (Exception $e) {
                $errors[] = ['row' => $rowNum, 'message' => $e->getMessage()];
            }
        }

        if ($updated > 0) {
            $this->recalculateAffectedContracts($periodId);
        }

        return [
            'total' => $rowNum - 1,
            'successful' => $successful,
            'errors' => $errors,
            'created' => $created,
            'updated' => $updated,
        ];
    }

    /**
     * Procesar una fila del Excel y crear/actualizar el registro.
     */
    private function processRow($periodId, array $data, int $rowNum)
    {
        $tipo = strtolower($data['TIPO'] ?? '');
        $concepto = strtoupper($data['CODIGO_CONCEPTO'] ?? '');
        $moneda = strtoupper($data['MONEDA'] ?? '');
        $precioRegular = $data['PRECIO_REGULAR'] ?? '';

        // Validar campos requeridos
        if (!$tipo || !$concepto || !$moneda) {
            throw new Exception('Faltan campos requeridos (TIPO, CODIGO_CONCEPTO, MONEDA)');
        }
        if (!in_array($tipo, self::VALID_TYPES)) {
            throw new Exception("TIPO inválido: '$tipo'. Valores válidos: " . implode(', ', self::VALID_TYPES));
        }
        if (!in_array($moneda, self::VALID_CURRENCIES)) {
            throw new Exception("MONEDA inválida: '$moneda'. Valores válidos: UF, CLP");
        }

        // Resolver curso
        $courseId = null;
        $cursoCode = trim($data['CURSO'] ?? '');
        if ($cursoCode !== '') {
            $course = Course::whereRaw('LOWER(code) = ?', [strtolower($cursoCode)])->first();
            if (!$course) {
                throw new Exception("Curso no encontrado: '$cursoCode'");
            }
            $courseId = $course->id;
        }

        $siblingOrder = ($data['ORDEN_HERMANO'] ?? '') !== '' ? (int)$data['ORDEN_HERMANO'] : null;
        $pagoFamiliar = strtoupper($data['PAGO_FAMILIAR'] ?? '') === 'SI';
        $opcional = strtoupper($data['OPCIONAL'] ?? '') === 'SI';

        // Buscar si existe para determinar acción
        $query = PeriodPricingConfig::where('period_id', $periodId)
            ->where('config_type', $tipo)
            ->where('concept_code', $concepto);
        $courseId === null ? $query->whereNull('course_id') : $query->where('course_id', $courseId);
        $siblingOrder === null ? $query->whereNull('sibling_order') : $query->where('sibling_order', $siblingOrder);

        $existing = $query->first();

        PeriodPricingConfig::updateOrCreate(
            [
                'period_id' => $periodId,
                'config_type' => $tipo,
                'concept_code' => $concepto,
                'course_id' => $courseId,
                'sibling_order' => $siblingOrder,
            ],
            [
                'currency_type' => $moneda,
                'price_regular' => is_numeric($precioRegular) ? $precioRegular : null,
                'price_anticipated' => is_numeric($data['PRECIO_ANTICIPADO'] ?? '') ? $data['PRECIO_ANTICIPADO'] : null,
                'price_extended' => is_numeric($data['PRECIO_EXTENDIDO'] ?? '') ? $data['PRECIO_EXTENDIDO'] : null,
                'is_family_payment' => $pagoFamiliar,
                'is_optional' => $opcional,
                'status' => true,
            ]
        );

        return [
            'isUpdate' => (bool)$existing,
            'concept_code' => $concepto,
        ];
    }

    /**
     * Listar configuraciones de precios por periodo.
     */
    public function list($periodId)
    {
        return PeriodPricingConfig::where('period_id', $periodId)
            ->with('course')
            ->orderBy('config_type')
            ->orderBy('sibling_order')
            ->get();
    }

    /**
     * Crear una configuración de precio.
     */
    public function store(array $data)
    {
        $query = PeriodPricingConfig::where('period_id', $data['period_id'])
            ->where('config_type', $data['config_type'])
            ->where('concept_code', $data['concept_code']);

        if (!empty($data['course_id'])) {
            $query->where('course_id', $data['course_id']);
        } else {
            $query->whereNull('course_id');
        }

        if (!empty($data['sibling_order'])) {
            $query->where('sibling_order', $data['sibling_order']);
        } else {
            $query->whereNull('sibling_order');
        }

        if ($query->exists()) {
            throw new Exception('Ya existe una configuración con la misma combinación de concepto, curso y orden de hermano.', 409);
        }

        return PeriodPricingConfig::create($data);
    }

    /**
     * Actualizar una configuración de precio.
     */
    public function update($id, array $data)
    {
        $config = PeriodPricingConfig::findOrFail($id);
        $priceChanged = $this->hasPriceChanged($config, $data);
        $config->update($data);

        if ($priceChanged) {
            $this->recalculateAffectedContracts($config->period_id);
        }

        return $config;
    }

    /**
     * Eliminar una configuración de precio.
     */
    public function destroy($id)
    {
        PeriodPricingConfig::findOrFail($id)->delete();
    }

    /**
     * Copiar configuraciones de un periodo a otro.
     */
    public function copy($fromPeriodId, $toPeriodId)
    {
        $sources = PeriodPricingConfig::where('period_id', $fromPeriodId)->get();
        $created = 0;
        $skipped = 0;

        foreach ($sources as $src) {
            $dupQuery = PeriodPricingConfig::where('period_id', $toPeriodId)
                ->where('config_type', $src->config_type)
                ->where('concept_code', $src->concept_code);

            $src->course_id ? $dupQuery->where('course_id', $src->course_id) : $dupQuery->whereNull('course_id');
            $src->sibling_order ? $dupQuery->where('sibling_order', $src->sibling_order) : $dupQuery->whereNull('sibling_order');

            if ($dupQuery->exists()) {
                $skipped++;
                continue;
            }

            PeriodPricingConfig::create([
                'period_id'         => $toPeriodId,
                'config_type'       => $src->config_type,
                'concept_code'      => $src->concept_code,
                'course_id'         => $src->course_id,
                'sibling_order'     => $src->sibling_order,
                'currency_type'     => $src->currency_type,
                'price_regular'     => $src->price_regular,
                'price_anticipated' => $src->price_anticipated,
                'price_extended'    => $src->price_extended,
                'is_family_payment' => $src->is_family_payment,
                'is_optional'       => $src->is_optional,
                'status'            => $src->status,
            ]);
            $created++;
        }

        return ['created' => $created, 'skipped' => $skipped];
    }

    /**
     * Eliminar múltiples configuraciones.
     */
    public function bulkDelete(array $ids)
    {
        PeriodPricingConfig::whereIn('id', $ids)->delete();
    }

    /**
     * Guardar configuraciones masivas (crear o actualizar).
     */
    public function bulkSave($periodId, array $configs)
    {
        $created = [];
        $skipped = [];
        $anyPriceChanged = false;

        foreach ($configs as $configData) {
            $configData['period_id'] = $periodId;

            // Si tiene ID, es update
            if (isset($configData['id'])) {
                $config = PeriodPricingConfig::find($configData['id']);
                if ($config) {
                    if ($this->hasPriceChanged($config, $configData)) {
                        $anyPriceChanged = true;
                    }
                    $config->update($configData);
                    $created[] = $config;
                    continue;
                }
            }

            // Verificar duplicado antes de crear
            $dupQuery = PeriodPricingConfig::where('period_id', $periodId)
                ->where('config_type', $configData['config_type'] ?? '')
                ->where('concept_code', $configData['concept_code'] ?? '');

            if (!empty($configData['course_id'])) {
                $dupQuery->where('course_id', $configData['course_id']);
            } else {
                $dupQuery->whereNull('course_id');
            }

            if (!empty($configData['sibling_order'])) {
                $dupQuery->where('sibling_order', $configData['sibling_order']);
            } else {
                $dupQuery->whereNull('sibling_order');
            }

            if ($dupQuery->exists()) {
                $skipped[] = $configData;
                continue;
            }

            $created[] = PeriodPricingConfig::create($configData);
        }

        if ($anyPriceChanged) {
            $this->recalculateAffectedContracts($periodId);
        }

        return ['created' => $created, 'skipped' => $skipped];
    }

    /**
     * Exportar configuraciones de precios a un archivo Excel.
     */
    public function exportToExcel($periodId)
    {
        $configs = PeriodPricingConfig::where('period_id', $periodId)
            ->with('course')
            ->orderBy('config_type')
            ->orderBy('concept_code')
            ->orderBy('sibling_order')
            ->get();

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // Headers
        $headers = [
            'TIPO', 'CODIGO_CONCEPTO', 'CURSO', 'ORDEN_HERMANO', 'MONEDA',
            'PRECIO_REGULAR', 'PRECIO_ANTICIPADO', 'PRECIO_EXTENDIDO',
            'PAGO_FAMILIAR', 'OPCIONAL',
        ];
        $sheet->fromArray($headers, null, 'A1');
        $sheet->getStyle('A1:J1')->getFont()->setBold(true);

        // Data
        $row = 2;
        foreach ($configs as $c) {
            $sheet->fromArray([
                $c->config_type,
                $c->concept_code,
                $c->course ? $c->course->code : '',
                $c->sibling_order,
                $c->currency_type,
                $c->price_regular,
                $c->price_anticipated,
                $c->price_extended,
                $c->is_family_payment ? 'SI' : 'NO',
                $c->is_optional ? 'SI' : 'NO',
            ], null, "A{$row}");
            $row++;
        }

        // Auto-width
        foreach (range('A', 'J') as $col) {
            $sheet->getColumnDimension($col)->setAutoSize(true);
        }

        // Generar archivo temporal
        $period = Period::find($periodId);
        $periodName = $period ? $period->period_year : $periodId;
        $fileName = "precios_periodo_{$periodName}.xlsx";
        $tempFile = tempnam(sys_get_temp_dir(), 'pricing_');

        $writer = new Xlsx($spreadsheet);
        $writer->save($tempFile);

        return [
            'tempFile' => $tempFile,
            'fileName' => $fileName,
        ];
    }

    /**
     * Detecta si algún precio cambió entre el modelo actual y los datos nuevos.
     */
    private function hasPriceChanged($config, array $data)
    {
        return (isset($data['price_regular']) && (float) $data['price_regular'] != (float) $config->price_regular)
            || (isset($data['price_extended']) && (float) $data['price_extended'] != (float) $config->price_extended)
            || (isset($data['price_anticipated']) && (float) $data['price_anticipated'] != (float) $config->price_anticipated);
    }

    /**
     * Recalcula conceptos pendientes de pago en contratos abiertos del período.
     * Solo elimina y recrea detalles no pagados; los pagados no se tocan.
     */
    private function recalculateAffectedContracts($periodId)
    {
        $debtService = app(DebtCalculationService::class);
        $canceledStatusId = StatusContract::where('code', 'canceled')->value('id');

        $contractIds = Contract::where('period_id', $periodId)
            ->when($canceledStatusId, fn($q) => $q->where('status_contract_id', '!=', $canceledStatusId))
            ->whereHas('details', fn($q) => $q->where('paid', false))
            ->pluck('id');

        $recalculated = 0;

        foreach ($contractIds as $contractId) {
            try {
                $debtService->recalculateContractDebts($contractId);
                $recalculated++;
            } catch (Exception $e) {
                Log::warning("Error recalculando contrato {$contractId} tras cambio de precios: " . $e->getMessage());
            }
        }

        if ($recalculated > 0) {
            Log::info("Precios actualizados: {$recalculated} contratos recalculados en período {$periodId}");
        }
    }
}