<?php

namespace App\Services;

use App\Models\Project;
use Maatwebsite\Excel\Facades\Excel;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Facades\Log;

class ExcelReportGenerator
{
    /**
     * Generate Excel report for project
     */
    public function generate(Project $project): string
    {
        $spreadsheet = new Spreadsheet();

        // Create sheets
        $this->createSummarySheet($spreadsheet, $project);
        $this->createRecommendationsSheet($spreadsheet, $project);
        $this->createPageTypeBreakdownSheet($spreadsheet, $project);

        // Save to storage
        $fileName = 'keyword-report-' . $project->id . '-' . time() . '.xlsx';
        $filePath = storage_path('app/exports/' . $fileName);

        // Ensure directory exists
        if (!file_exists(storage_path('app/exports'))) {
            mkdir(storage_path('app/exports'), 0755, true);
        }

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

        Log::info('Excel report generated', [
            'project_id' => $project->id,
            'file_path' => $filePath,
        ]);

        return $fileName;
    }

    /**
     * Create summary sheet
     */
    private function createSummarySheet(Spreadsheet $spreadsheet, Project $project): void
    {
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Summary');

        // Header
        $sheet->setCellValue('A1', 'KeywordMatcherPro Analysis Report');
        $sheet->mergeCells('A1:D1');
        $this->styleHeader($sheet, 'A1');

        // Project info
        $row = 3;
        $sheet->setCellValue('A' . $row, 'Client:');
        $sheet->setCellValue('B' . $row, $project->client_name);
        $row++;
        $sheet->setCellValue('A' . $row, 'Domain:');
        $sheet->setCellValue('B' . $row, $project->domain);
        $row++;
        $sheet->setCellValue('A' . $row, 'Total Pages:');
        $sheet->setCellValue('B' . $row, $project->total_pages);
        $row++;
        $sheet->setCellValue('A' . $row, 'Successfully Processed:');
        $sheet->setCellValue('B' . $row, $project->processed_pages);
        $row++;
        $sheet->setCellValue('A' . $row, 'Failed:');
        $sheet->setCellValue('B' . $row, $project->failed_pages);
        $row++;
        $sheet->setCellValue('A' . $row, 'Analysis Date:');
        $sheet->setCellValue('B' . $row, $project->completed_at ? $project->completed_at->format('Y-m-d H:i:s') : 'In Progress');

        // Auto-size columns
        $sheet->getColumnDimension('A')->setAutoSize(true);
        $sheet->getColumnDimension('B')->setAutoSize(true);
    }

    /**
     * Create recommendations sheet
     */
    private function createRecommendationsSheet(Spreadsheet $spreadsheet, Project $project): void
    {
        $sheet = $spreadsheet->createSheet();
        $sheet->setTitle('Recommendations');

        // Headers
        $headers = ['URL', 'Page Type', 'Primary Keyword', 'Score', 'Search Volume', 'Competition', 'CPC', 'Intent', 'Reasoning'];
        $col = 'A';
        foreach ($headers as $header) {
            $sheet->setCellValue($col . '1', $header);
            $this->styleHeader($sheet, $col . '1');
            $col++;
        }

        // Data rows
        $row = 2;
        $pages = $project->pages()->with('primaryKeyword')->get();

        foreach ($pages as $page) {
            $primaryKeyword = $page->primaryKeyword;

            if (!$primaryKeyword) {
                continue;
            }

            $sheet->setCellValue('A' . $row, $page->url);
            $sheet->setCellValue('B' . $row, $page->page_type);
            $sheet->setCellValue('C' . $row, $primaryKeyword->keyword);
            $sheet->setCellValue('D' . $row, $primaryKeyword->composite_score);
            $sheet->setCellValue('E' . $row, $primaryKeyword->search_volume ?? 'N/A');
            $sheet->setCellValue('F' . $row, $primaryKeyword->competition ?? 'N/A');
            $sheet->setCellValue('G' . $row, $primaryKeyword->cpc ? '$' . number_format($primaryKeyword->cpc, 2) : 'N/A');
            $sheet->setCellValue('H' . $row, $primaryKeyword->intent);
            $sheet->setCellValue('I' . $row, $primaryKeyword->reasoning);

            $row++;
        }

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

    /**
     * Create page type breakdown sheet
     */
    private function createPageTypeBreakdownSheet(Spreadsheet $spreadsheet, Project $project): void
    {
        $sheet = $spreadsheet->createSheet();
        $sheet->setTitle('Page Type Breakdown');

        // Headers
        $sheet->setCellValue('A1', 'Page Type');
        $sheet->setCellValue('B1', 'Count');
        $sheet->setCellValue('C1', 'Percentage');
        $this->styleHeader($sheet, 'A1');
        $this->styleHeader($sheet, 'B1');
        $this->styleHeader($sheet, 'C1');

        // Get page type statistics
        $pageTypes = $project->pages()
            ->selectRaw('page_type, COUNT(*) as count')
            ->groupBy('page_type')
            ->get();

        $row = 2;
        $total = $project->total_pages;

        foreach ($pageTypes as $pageType) {
            $sheet->setCellValue('A' . $row, $pageType->page_type);
            $sheet->setCellValue('B' . $row, $pageType->count);
            $sheet->setCellValue('C' . $row, round(($pageType->count / $total) * 100, 2) . '%');
            $row++;
        }

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

    /**
     * Style header cell
     */
    private function styleHeader($sheet, string $cell): void
    {
        $sheet->getStyle($cell)->applyFromArray([
            'font' => [
                'bold' => true,
                'size' => 12,
            ],
            'fill' => [
                'fillType' => Fill::FILL_SOLID,
                'startColor' => ['rgb' => '4472C4'],
            ],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER,
            ],
        ]);
    }
}
