<?php

namespace App\Services;

class Gstr3bExcelExporter
{
    /**
     * @param  array<string, mixed>  $report
     */
    public function download(array $report, string $filename): \Symfony\Component\HttpFoundation\StreamedResponse
    {
        $xml = $this->buildSpreadsheetXml($report);

        return response()->streamDownload(function () use ($xml) {
            echo $xml;
        }, $filename, [
            'Content-Type' => 'application/vnd.ms-excel',
            'Cache-Control' => 'no-store, no-cache, must-revalidate, max-age=0',
        ]);
    }

    /**
     * @param  array<string, mixed>  $report
     */
    private function buildSpreadsheetXml(array $report): string
    {
        $taxCols = ['Taxable Value', 'Integrated Tax', 'Central Tax', 'State/UT Tax', 'Cess'];

        $sheets = [
            'Summary' => $this->summarySheet($report),
            '3.1' => $this->table31Sheet($report['table_31'], $taxCols),
            '3.1.1' => $this->table311Sheet($report['table_311'], $taxCols),
            '3.2' => $this->table32Sheet($report['table_32'], $taxCols),
            '4' => $this->table4Sheet($report['table_4'], $taxCols),
            '5' => $this->table5Sheet($report['table_5']),
            '6.1' => $this->table61Sheet($report['table_61']),
        ];

        $out = '<?xml version="1.0" encoding="UTF-8"?>'."\n";
        $out .= '<?mso-application progid="Excel.Sheet"?>'."\n";
        $out .= '<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" '
            .'xmlns:o="urn:schemas-microsoft-com:office:office" '
            .'xmlns:x="urn:schemas-microsoft-com:office:excel" '
            .'xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">'."\n";
        $out .= '<Styles>'
            .'<Style ss:ID="Header"><Font ss:Bold="1"/><Interior ss:Color="#E8E8E8" ss:Pattern="Solid"/></Style>'
            .'<Style ss:ID="Label"><Font ss:Bold="1"/></Style>'
            .'</Styles>'."\n";

        foreach ($sheets as $name => $body) {
            $out .= '<Worksheet ss:Name="'.$this->escapeXml($name).'">'."\n";
            $out .= '<Table>'.$body.'</Table>'."\n";
            $out .= '</Worksheet>'."\n";
        }

        $out .= '</Workbook>';

        return $out;
    }

    /**
     * @param  array<string, mixed>  $report
     */
    private function summarySheet(array $report): string
    {
        $company = $report['company'];
        $lines = [
            ['GSTR-3B Report Export'],
            ['Company', $company->name ?? ''],
            ['GSTIN', $report['gstin'] ?? ''],
            ['Return Period', $report['ret_period'] ?? ''],
            ['Financial Period', ($report['period_from'] ?? '').' to '.($report['period_to'] ?? '')],
            ['Generated', now()->format('d-m-Y H:i')],
            [],
            ['Documents in period'],
            ['Sales invoices', $report['docs']['invoices'] ?? 0],
            ['Credit notes', $report['docs']['credit_notes'] ?? 0],
            ['Purchase bills', $report['docs']['purchase_bills'] ?? 0],
            ['Debit notes', $report['docs']['debit_notes'] ?? 0],
            [],
            ['Copy values from sheets 3.1–6.1 into the GST portal or offline utility.'],
            ['Verify totals before filing on www.gst.gov.in'],
        ];

        $xml = '';
        foreach ($lines as $line) {
            $xml .= $this->row($line, false);
        }

        return $xml;
    }

    /**
     * @param  array<string, array<string, float>>  $table
     * @param  list<string>  $taxCols
     */
    private function table31Sheet(array $table, array $taxCols): string
    {
        $labels = config('reports.gstr3b.table_31_rows', []);
        $xml = $this->row(array_merge(['Nature of Supplies'], $taxCols), true);
        foreach ($labels as $key => $label) {
            $row = $table[$key] ?? ['taxable' => 0, 'igst' => 0, 'cgst' => 0, 'sgst' => 0, 'cess' => 0];
            $xml .= $this->row([
                $label,
                $row['taxable'],
                $row['igst'],
                $row['cgst'],
                $row['sgst'],
                $row['cess'],
            ], false);
        }

        return $xml;
    }

    /**
     * @param  array<string, array<string, float>>  $table
     * @param  list<string>  $taxCols
     */
    private function table311Sheet(array $table, array $taxCols): string
    {
        $labels = config('reports.gstr3b.table_311_rows', []);
        $xml = $this->row(array_merge(['Section 9(5) — E-Commerce'], $taxCols), true);
        foreach ($labels as $key => $label) {
            $row = $table[$key] ?? ['taxable' => 0, 'igst' => 0, 'cgst' => 0, 'sgst' => 0, 'cess' => 0];
            $xml .= $this->row([$label, $row['taxable'], $row['igst'], $row['cgst'], $row['sgst'], $row['cess']], false);
        }

        return $xml;
    }

    /**
     * @param  array<string, array<string, float>>  $table
     * @param  list<string>  $taxCols
     */
    private function table32Sheet(array $table, array $taxCols): string
    {
        $labels = config('reports.gstr3b.table_32_rows', []);
        $xml = $this->row(array_merge(['Inter-State Supplies'], $taxCols), true);
        foreach ($labels as $key => $label) {
            $row = $table[$key] ?? ['taxable' => 0, 'igst' => 0, 'cgst' => 0, 'sgst' => 0, 'cess' => 0];
            $xml .= $this->row([$label, $row['taxable'], $row['igst'], $row['cgst'], $row['sgst'], $row['cess']], false);
        }

        return $xml;
    }

    /**
     * @param  array<string, array<string, float>>  $table
     * @param  list<string>  $taxCols
     */
    private function table4Sheet(array $table, array $taxCols): string
    {
        $labels = config('reports.gstr3b.table_4_rows', []);
        $xml = $this->row(array_merge(['ITC Details'], $taxCols), true);
        foreach ($labels as $key => $label) {
            $row = $table[$key] ?? ['taxable' => 0, 'igst' => 0, 'cgst' => 0, 'sgst' => 0, 'cess' => 0];
            $xml .= $this->row([$label, '', $row['igst'], $row['cgst'], $row['sgst'], $row['cess']], false);
        }

        return $xml;
    }

    /**
     * @param  array<string, array<string, float>>  $table
     */
    private function table5Sheet(array $table): string
    {
        $labels = config('reports.gstr3b.table_5_rows', []);
        $xml = $this->row(['Nature of Supplies', 'Inter-State', 'Intra-State'], true);
        foreach ($labels as $key => $label) {
            $row = $table[$key] ?? ['inter' => 0, 'intra' => 0];
            $xml .= $this->row([$label, $row['inter'], $row['intra']], false);
        }

        return $xml;
    }

    /**
     * @param  array<string, array{payable: float, itc: float, cash: float}>  $table
     */
    private function table61Sheet(array $table): string
    {
        $labels = config('reports.gstr3b.table_61_rows', []);
        $xml = $this->row(['Tax Head', 'Tax Payable', 'ITC Utilised', 'Cash Payable'], true);
        foreach ($labels as $key => $label) {
            $row = $table[$key] ?? ['payable' => 0, 'itc' => 0, 'cash' => 0];
            $xml .= $this->row([$label, $row['payable'], $row['itc'], $row['cash']], false);
        }

        return $xml;
    }

    /**
     * @param  list<mixed>  $cells
     */
    private function row(array $cells, bool $header): string
    {
        $style = $header ? ' ss:StyleID="Header"' : '';
        $xml = '<Row>';
        foreach ($cells as $cell) {
            if (is_numeric($cell) && ! is_string($cell)) {
                $xml .= '<Cell'.$style.'><Data ss:Type="Number">'.$cell.'</Data></Cell>';
            } else {
                $xml .= '<Cell'.$style.'><Data ss:Type="String">'.$this->escapeXml((string) ($cell ?? '')).'</Data></Cell>';
            }
        }
        $xml .= '</Row>';

        return $xml;
    }

    private function escapeXml(string $value): string
    {
        return htmlspecialchars($value, ENT_XML1 | ENT_QUOTES, 'UTF-8');
    }
}
