<?php
namespace Analytics\Services;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Font;

/**
 * Generates Excel exports at all audience levels using PhpSpreadsheet.
 */
class ExcelExportService
{
    // ── Business Owner Excel ──────────────────────────────────────────────────

    public function businessOwner(array $reportRows, string $period): string
    {
        $ss  = new Spreadsheet();
        $ws  = $ss->getActiveSheet();
        $ws->setTitle('Business Owner Summary');

        $headers = ['Project', 'Manager', 'Revenue Managed', 'Total Leads', 'Lead Value (₹)',
                    'ROAS', 'Organic Orders', 'Organic Revenue', 'Overall Users',
                    'LLM Traffic', 'AI Overview', 'Health Score', 'Client Age (mo)', 'Period'];

        $this->writeHeaderRow($ws, $headers, 1);

        $row = 2;
        foreach ($reportRows as $r) {
            $s = $r['snapshot'] ?? [];
            $ws->fromArray([
                $r['project']['business_name'] ?? '',
                $r['project']['manager_name']  ?? '',
                $r['project']['revenue_managed'] ?? 0,
                $s['total_leads']       ?? 0,
                round($r['lead_value']  ?? 0),
                ($r['roas'] ?? 0) . 'x',
                $s['organic_orders']    ?? 0,
                round($s['organic_revenue'] ?? 0),
                $s['overall_users']     ?? 0,
                $s['llm_total']         ?? 0,
                $s['ai_overview']       ?? 0,
                $r['health_score']      ?? 0,
                $r['project']['client_age'] ?? 0,
                $period,
            ], null, 'A' . $row++);
        }

        $this->autoWidth($ws, count($headers));
        return $this->stream($ss, 'business_owner_' . date('Y-m-d'));
    }

    // ── Client Excel ──────────────────────────────────────────────────────────

    public function client(array $reportRows, string $period): string
    {
        $ss = new Spreadsheet();
        $ws = $ss->getActiveSheet();
        $ws->setTitle('Client Summary');

        $headers = ['Project', 'Clicks', 'Impressions', 'CTR (%)', 'Avg Position',
                    'Branded Clicks', 'Non-Branded Clicks',
                    'Branded Impressions', 'Non-Branded Impressions',
                    'Organic Sessions', 'Engagement Rate (%)', 'Total Leads', 'Period'];

        $this->writeHeaderRow($ws, $headers, 1);
        $row = 2;
        foreach ($reportRows as $r) {
            $s = $r['snapshot'] ?? [];
            $ws->fromArray([
                $r['project']['business_name'] ?? '',
                $s['gsc_clicks']              ?? 0,
                $s['gsc_impressions']         ?? 0,
                round(($s['gsc_ctr'] ?? 0) * 100, 2),
                round($s['gsc_position']      ?? 0, 1),
                $s['branded_clicks']          ?? 0,
                $s['non_branded_clicks']      ?? 0,
                $s['branded_impressions']     ?? 0,
                $s['non_branded_impressions'] ?? 0,
                $s['organic_sessions']        ?? 0,
                round(($s['organic_engagement_rate'] ?? 0) * 100, 1),
                $s['total_leads']             ?? 0,
                $period,
            ], null, 'A' . $row++);
        }

        $this->autoWidth($ws, count($headers));
        return $this->stream($ss, 'client_report_' . date('Y-m-d'));
    }

    // ── Team Excel (multi-sheet) ──────────────────────────────────────────────

    public function team(array $report, string $period): string
    {
        $ss = new Spreadsheet();
        $s  = $report['snapshot'] ?? [];

        // Sheet 1: Summary
        $ws1 = $ss->getActiveSheet();
        $ws1->setTitle('Summary');
        $this->writeHeaderRow($ws1, ['Metric', 'Value', 'Previous', 'Delta %'], 1);
        $row = 2;
        foreach ($report['kpis'] ?? [] as $kpi) {
            $ws1->fromArray([$kpi['label'], $kpi['value'], '', $kpi['delta'] ?? ''], null, 'A' . $row++);
        }

        // Sheet 2: Lead Category Drill-down
        $ws2 = $ss->createSheet();
        $ws2->setTitle('Lead Events');
        $this->writeHeaderRow($ws2, ['Category', 'Event Name', 'Organic Count'], 1);
        $row2 = 2;
        foreach ($report['event_drill'] ?? [] as $e) {
            $ws2->fromArray([$e['category_key'], $e['event_name'], $e['organic_count']], null, 'A' . $row2++);
        }

        // Sheet 3: Page Data
        $ws3 = $ss->createSheet();
        $ws3->setTitle('Page Data');
        $this->writeHeaderRow($ws3, ['URL', 'GA4 Sessions', 'GA4 Page Views', 'GSC Clicks', 'GSC Impressions', 'CTR (%)', 'Position'], 1);
        $row3 = 2;
        foreach ($report['page_data'] ?? [] as $p) {
            $ws3->fromArray([$p['url'], $p['ga4_sessions'], $p['ga4_page_views'],
                $p['gsc_clicks'], $p['gsc_impressions'],
                round(($p['gsc_ctr'] ?? 0) * 100, 2), round($p['gsc_position'] ?? 0, 1)], null, 'A' . $row3++);
        }

        // Sheet 4: Query Data
        $ws4 = $ss->createSheet();
        $ws4->setTitle('Queries');
        $this->writeHeaderRow($ws4, ['Query', 'Clicks', 'Impressions', 'CTR (%)', 'Position'], 1);
        $row4 = 2;
        foreach ($report['query_data'] ?? [] as $q) {
            $ws4->fromArray([$q['query'], $q['clicks'], $q['impressions'],
                round(($q['ctr'] ?? 0) * 100, 2), round($q['position'] ?? 0, 1)], null, 'A' . $row4++);
        }

        $ss->setActiveSheetIndex(0);
        return $this->stream($ss, 'team_report_' . date('Y-m-d'));
    }

    // ── Manager Rollup Excel ──────────────────────────────────────────────────

    public function managerRollup(array $rollupRows, string $period): string
    {
        $ss = new Spreadsheet();
        $ws = $ss->getActiveSheet();
        $ws->setTitle('Manager Rollup');

        $headers = ['Manager', 'Clients', 'Declining ▼', 'Sessions', 'Δ Sessions %',
                    'Clicks', 'Δ Clicks %', 'Impressions', 'Δ Impressions %',
                    'Leads', 'Δ Leads %', 'AI/LLM Traffic', 'Δ LLM %',
                    'ROAS', 'Revenue Managed', 'Lead Value (₹)', 'Period'];

        $this->writeHeaderRow($ws, $headers, 1);
        $row = 2;
        foreach ($rollupRows as $r) {
            $ws->fromArray([
                $r['manager'],
                $r['clients'],
                $r['declining'],
                $r['sessions'],    $r['delta_sessions']    !== null ? $r['delta_sessions'] . '%' : '—',
                $r['clicks'],      $r['delta_clicks']      !== null ? $r['delta_clicks'] . '%' : '—',
                $r['impressions'], $r['delta_impressions'] !== null ? $r['delta_impressions'] . '%' : '—',
                $r['leads'],       $r['delta_leads']       !== null ? $r['delta_leads'] . '%' : '—',
                $r['llm'],         $r['delta_llm']         !== null ? $r['delta_llm'] . '%' : '—',
                $r['roas'] . 'x',
                round($r['revenue_managed']),
                round($r['lead_value']),
                $period,
            ], null, 'A' . $row++);
        }

        $this->autoWidth($ws, count($headers));
        return $this->stream($ss, 'manager_rollup_' . date('Y-m-d'));
    }

    // ── Helpers ───────────────────────────────────────────────────────────────

    private function writeHeaderRow($ws, array $headers, int $rowNum): void
    {
        foreach ($headers as $col => $header) {
            $cell = $ws->getCellByColumnAndRow($col + 1, $rowNum);
            $cell->setValue($header);
            $cell->getStyle()->getFont()->setBold(true);
            $cell->getStyle()->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('1E293B');
            $cell->getStyle()->getFont()->getColor()->setRGB('FFFFFF');
            $cell->getStyle()->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        }
    }

    private function autoWidth($ws, int $colCount): void
    {
        for ($i = 1; $i <= $colCount; $i++) {
            $ws->getColumnDimensionByColumn($i)->setAutoSize(true);
        }
    }

    private function stream(Spreadsheet $ss, string $filename): string
    {
        $tmpFile = sys_get_temp_dir() . '/' . $filename . '.xlsx';
        (new Xlsx($ss))->save($tmpFile);
        return $tmpFile;
    }
}
