<?php

namespace App\Services;

use App\Models\JobApplication;
use Illuminate\Http\UploadedFile;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Alignment;

class ApplicationExcelMergeService
{
    /**
     * Mevcut bir Excel (.xlsx/.xls) veya CSV dosyasına yeni başvuruları ekler.
     * Mükerrer e-posta kontrolü yapar.
     *
     * @param UploadedFile $uploadedFile Kullanıcının yüklediği mevcut dosya
     * @param int $jobPostingId İlgili ilanın ID'si
     * @return array ['path' => string, 'filename' => string, 'added' => int]
     */
    public function merge(UploadedFile $uploadedFile, int $jobPostingId): array
    {
        $extension = strtolower($uploadedFile->getClientOriginalExtension());
        $isCsv = $extension === 'csv';

        if ($isCsv) {
            return $this->mergeCsv($uploadedFile, $jobPostingId);
        }

        return $this->mergeExcel($uploadedFile, $jobPostingId);
    }

    /**
     * Excel (.xlsx/.xls) dosyasına birleştirme
     */
    private function mergeExcel(UploadedFile $uploadedFile, int $jobPostingId): array
    {
        $spreadsheet = IOFactory::load($uploadedFile->getPathname());
        $sheet = $spreadsheet->getActiveSheet();

        // Mevcut e-postaları topla
        $existingEmails = [];
        $highestRow = $sheet->getHighestRow();

        for ($row = 2; $row <= $highestRow; $row++) {
            $email = $sheet->getCell('C' . $row)->getValue();
            if ($email) {
                $existingEmails[] = strtolower(trim($email));
            }
        }

        // Başlık satırı yoksa ekle
        if ($highestRow < 1 || $sheet->getCell('A1')->getValue() === null) {
            $this->addExcelHeaders($sheet);
            $highestRow = 1;
        }

        $newApplications = JobApplication::where('job_posting_id', $jobPostingId)->latest()->get();

        $addedCount = 0;
        $nextRow = $highestRow + 1;

        foreach ($newApplications as $application) {
            if (in_array(strtolower($application->email), $existingEmails)) {
                continue;
            }

            $sheet->setCellValue('A' . $nextRow, $application->id);
            $sheet->setCellValue('B' . $nextRow, $application->full_name);
            $sheet->setCellValue('C' . $nextRow, $application->email);
            $sheet->setCellValue('D' . $nextRow, $application->phone);
            $sheet->setCellValue('E' . $nextRow, $application->created_at->format('d.m.Y H:i'));
            $sheet->setCellValue('F' . $nextRow, $application->status_label);
            $sheet->setCellValue('G' . $nextRow, $application->linkedin_url ?? '');
            $sheet->setCellValue('H' . $nextRow, $application->source ?? '');
            $sheet->setCellValue('I' . $nextRow, $application->internal_notes ?? '');
            $sheet->setCellValue('J' . $nextRow, $application->cv_original_name);

            // Alternating row color
            $rowBg = ($nextRow % 2 === 0) ? 'F8FAFC' : 'FFFFFF';
            $sheet->getStyle("A{$nextRow}:J{$nextRow}")->applyFromArray([
                'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => $rowBg]],
            ]);

            $existingEmails[] = strtolower($application->email);
            $nextRow++;
            $addedCount++;
        }

        $tempPath = sys_get_temp_dir() . '/merged_' . uniqid() . '.xlsx';
        $writer = new Xlsx($spreadsheet);
        $writer->save($tempPath);

        return [
            'path'     => $tempPath,
            'filename' => 'basvurular-merged-' . now()->format('Y-m-d-His') . '.xlsx',
            'added'    => $addedCount,
        ];
    }

    /**
     * CSV dosyasına birleştirme — sonuç yine .xlsx olarak döner
     */
    private function mergeCsv(UploadedFile $uploadedFile, int $jobPostingId): array
    {
        // CSV'yi oku
        $handle = fopen($uploadedFile->getPathname(), 'r');
        $rows = [];

        // BOM varsa atla
        $bom = fread($handle, 3);
        if ($bom !== "\xEF\xBB\xBF") {
            rewind($handle);
        }

        // Delimiter tespiti (virgül veya noktalı virgül)
        $firstLine = fgets($handle);
        rewind($handle);
        if ($bom === "\xEF\xBB\xBF") {
            fread($handle, 3); // BOM'u tekrar atla
        }
        $delimiter = substr_count($firstLine, ';') >= substr_count($firstLine, ',') ? ';' : ',';

        while (($row = fgetcsv($handle, 0, $delimiter)) !== false) {
            $rows[] = $row;
        }
        fclose($handle);

        // Mevcut e-postaları topla (başlık satırını atla)
        $existingEmails = [];
        foreach (array_slice($rows, 1) as $row) {
            if (isset($row[2]) && $row[2]) {
                $existingEmails[] = strtolower(trim($row[2]));
            }
        }

        // Yeni başvuruları ekle
        $newApplications = JobApplication::where('job_posting_id', $jobPostingId)->latest()->get();
        $addedCount = 0;

        foreach ($newApplications as $application) {
            if (in_array(strtolower($application->email), $existingEmails)) {
                continue;
            }
            $rows[] = [
                $application->id,
                $application->full_name,
                $application->email,
                $application->phone,
                $application->created_at->format('d.m.Y H:i'),
                $application->status_label,
                $application->linkedin_url ?? '',
                $application->source ?? '',
                $application->internal_notes ?? '',
                $application->cv_original_name,
            ];
            $existingEmails[] = strtolower($application->email);
            $addedCount++;
        }

        // Sonucu Excel olarak oluştur
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // Başlık satırı yoksa ekle
        if (empty($rows) || !isset($rows[0][0]) || strtolower($rows[0][0]) !== 'id') {
            $this->addExcelHeaders($sheet);
            $startRow = 2;
            foreach ($rows as $i => $row) {
                $this->writeRow($sheet, $i + 2, $row);
            }
        } else {
            // İlk satır başlık
            $this->addExcelHeaders($sheet);
            foreach (array_slice($rows, 1) as $i => $row) {
                $this->writeRow($sheet, $i + 2, $row);
            }
        }

        $tempPath = sys_get_temp_dir() . '/merged_csv_' . uniqid() . '.xlsx';
        $writer = new Xlsx($spreadsheet);
        $writer->save($tempPath);

        return [
            'path'     => $tempPath,
            'filename' => 'basvurular-merged-' . now()->format('Y-m-d-His') . '.xlsx',
            'added'    => $addedCount,
        ];
    }

    private function writeRow($sheet, int $rowNum, array $data): void
    {
        $cols = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J'];
        foreach ($cols as $i => $col) {
            $sheet->setCellValue($col . $rowNum, $data[$i] ?? '');
        }
        $rowBg = ($rowNum % 2 === 0) ? 'F8FAFC' : 'FFFFFF';
        $sheet->getStyle("A{$rowNum}:J{$rowNum}")->applyFromArray([
            'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => $rowBg]],
        ]);
    }

    private function addExcelHeaders($sheet): void
    {
        $headers = ['ID', 'Ad Soyad', 'E-posta', 'Telefon', 'Başvuru Tarihi', 'Durum', 'LinkedIn', 'Kaynak', 'İç Notlar', 'CV Dosyası'];
        $headerColors = [
            'A' => '374151',
            'B' => '1E40AF',
            'C' => '065F46',
            'D' => '7C3AED',
            'E' => '1E3A5F',
            'F' => '92400E',
            'G' => '0369A1',
            'H' => '374151',
            'I' => '374151',
            'J' => '374151',
        ];

        foreach ($headers as $col => $header) {
            $colLetter = chr(65 + $col);
            $sheet->setCellValue($colLetter . '1', $header);
            $sheet->getStyle($colLetter . '1')->applyFromArray([
                'font' => [
                    'bold'  => true,
                    'color' => ['rgb' => 'FFFFFF'],
                    'size'  => 11,
                ],
                'fill' => [
                    'fillType'   => Fill::FILL_SOLID,
                    'startColor' => ['rgb' => $headerColors[$colLetter] ?? '1E3A5F'],
                ],
                'alignment' => [
                    'horizontal' => Alignment::HORIZONTAL_CENTER,
                    'vertical'   => Alignment::VERTICAL_CENTER,
                ],
            ]);
        }
        $sheet->getRowDimension(1)->setRowHeight(22);
    }
}