<?php

namespace App\Http\Controllers\Admin\TopUp;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Inertia\Inertia;
use Carbon\Carbon;

class TransactionController extends Controller
{
    /**
     * Display all transactions from all payment gateways
     */
    public function index(Request $request)
    {
        $search = $request->input('search');
        $month = $request->input('month');
        $year = $request->input('year', date('Y'));
        $gateway = $request->input('gateway', 'all');
        $status = $request->input('status', 'all');
        $perPage = $request->input('per_page', 50);

        // Get transactions from all gateways
        $transactions = $this->getAllTransactions($search, $month, $year, $gateway, $status, $perPage);

        // Get statistics (filtered by current filters)
        $stats = $this->getStatistics($month, $year, $gateway, $status);

        // Get available years for filter
        $availableYears = $this->getAvailableYears();

        return Inertia::render('admin/topup/transaction/index', [
            'transactions' => $transactions,
            'stats' => $stats,
            'filters' => [
                'search' => $search,
                'month' => $month,
                'year' => $year,
                'gateway' => $gateway,
                'status' => $status,
                'per_page' => $perPage,
            ],
            'availableYears' => $availableYears,
        ]);
    }

    /**
     * Export transactions to Excel
     */
    public function exportExcel(Request $request)
    {
        $search = $request->input('search');
        $month = $request->input('month');
        $year = $request->input('year', date('Y'));
        $gateway = $request->input('gateway', 'all');
        $status = $request->input('status', 'all');

        // Get all transactions without pagination
        $transactions = $this->getAllTransactions($search, $month, $year, $gateway, $status, null, false);
        $stats = $this->getStatistics($month, $year, $gateway, $status);

        // Create spreadsheet
        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Transactions');

        // Define colors
        $primaryBlue = '1E40AF';
        $lightBlue = 'DBEAFE';
        $headerBg = '2563EB';
        $successBg = 'D1FAE5';
        $successText = '065F46';
        $pendingBg = 'FEF3C7';
        $pendingText = '92400E';
        $failedBg = 'FEE2E2';
        $failedText = '991B1B';
        $expiredBg = 'F3F4F6';
        $expiredText = '4B5563';
        $grayText = '6B7280';
        $zebraLight = 'F9FAFB';
        $zebraDark = 'FFFFFF';

        // Gateway colors
        $gatewayColors = [
            'ipaymu' => '3B82F6',
            'duitku' => 'A855F7',
            'tripay' => 'F97316',
            'pakasir' => '14B8A6',
        ];

        $currentRow = 1;

        // === HEADER SECTION ===
        $sheet->setCellValue('A' . $currentRow, config('app.name', 'Payment Gateway'));
        $sheet->mergeCells('A' . $currentRow . ':J' . $currentRow);
        $sheet->getStyle('A' . $currentRow)->applyFromArray([
            'font' => ['bold' => true, 'size' => 18, 'color' => ['rgb' => $primaryBlue]],
            'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER],
        ]);
        $currentRow++;

        $sheet->setCellValue('A' . $currentRow, 'TRANSACTIONS REPORT');
        $sheet->mergeCells('A' . $currentRow . ':J' . $currentRow);
        $sheet->getStyle('A' . $currentRow)->applyFromArray([
            'font' => ['bold' => true, 'size' => 14, 'color' => ['rgb' => $primaryBlue]],
            'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER],
        ]);
        $currentRow++;

        $sheet->setCellValue('A' . $currentRow, 'Generated on: ' . now()->format('F d, Y H:i:s'));
        $sheet->mergeCells('A' . $currentRow . ':J' . $currentRow);
        $sheet->getStyle('A' . $currentRow)->applyFromArray([
            'font' => ['size' => 10, 'color' => ['rgb' => $grayText]],
            'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER],
        ]);
        $currentRow++;

        // Period info
        $periodText = 'Period: ';
        if ($month) {
            $periodText .= \DateTime::createFromFormat('!m', $month)->format('F') . ' ' . $year;
        } else {
            $periodText .= 'Year ' . $year;
        }
        if ($gateway !== 'all') {
            $periodText .= ' | Gateway: ' . strtoupper($gateway);
        }
        if ($status !== 'all') {
            $periodText .= ' | Status: ' . ucfirst($status);
        }
        $sheet->setCellValue('A' . $currentRow, $periodText);
        $sheet->mergeCells('A' . $currentRow . ':J' . $currentRow);
        $sheet->getStyle('A' . $currentRow)->applyFromArray([
            'font' => ['size' => 10, 'italic' => true, 'color' => ['rgb' => $grayText]],
            'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER],
        ]);
        $currentRow += 2;

        // === SUMMARY STATISTICS SECTION ===
        $sheet->setCellValue('A' . $currentRow, 'SUMMARY STATISTICS');
        $sheet->mergeCells('A' . $currentRow . ':J' . $currentRow);
        $sheet->getStyle('A' . $currentRow)->applyFromArray([
            'font' => ['bold' => true, 'size' => 12, 'color' => ['rgb' => $primaryBlue]],
            'borders' => ['bottom' => ['borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_MEDIUM, 'color' => ['rgb' => $primaryBlue]]],
        ]);
        $currentRow++;

        // Stats row
        $statsData = [
            ['label' => 'Total Transactions', 'value' => number_format($stats['total_transactions']), 'color' => $primaryBlue],
            ['label' => 'Total Amount', 'value' => 'Rp ' . number_format($stats['total_amount'], 0, ',', '.'), 'color' => $primaryBlue],
            ['label' => 'Success', 'value' => number_format($stats['total_success']), 'color' => $successText],
            ['label' => 'Pending', 'value' => number_format($stats['total_pending']), 'color' => $pendingText],
            ['label' => 'Failed/Expired', 'value' => number_format($stats['total_failed']), 'color' => $failedText],
        ];

        $col = 'A';
        foreach ($statsData as $stat) {
            $sheet->setCellValue($col . $currentRow, $stat['label']);
            $sheet->getStyle($col . $currentRow)->applyFromArray([
                'font' => ['size' => 9, 'color' => ['rgb' => $grayText]],
                'fill' => ['fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID, 'startColor' => ['rgb' => $zebraLight]],
            ]);
            $sheet->setCellValue($col . ($currentRow + 1), $stat['value']);
            $sheet->getStyle($col . ($currentRow + 1))->applyFromArray([
                'font' => ['bold' => true, 'size' => 14, 'color' => ['rgb' => $stat['color']]],
                'fill' => ['fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID, 'startColor' => ['rgb' => $zebraLight]],
            ]);
            $col = chr(ord($col) + 2); // Skip a column for spacing
        }
        $currentRow += 3;

        // === DATA TABLE SECTION ===
        $sheet->setCellValue('A' . $currentRow, 'TRANSACTION DETAILS');
        $sheet->mergeCells('A' . $currentRow . ':J' . $currentRow);
        $sheet->getStyle('A' . $currentRow)->applyFromArray([
            'font' => ['bold' => true, 'size' => 12, 'color' => ['rgb' => $primaryBlue]],
            'borders' => ['bottom' => ['borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_MEDIUM, 'color' => ['rgb' => $primaryBlue]]],
        ]);
        $currentRow++;

        // Table headers
        $tableStartRow = $currentRow;
        $headers = ['ID', 'Gateway', 'Reference', 'User Name', 'Amount (IDR)', 'Gold', 'Payment Method', 'Payment Channel', 'Status', 'Created At', 'Paid At'];
        $col = 'A';
        foreach ($headers as $header) {
            $sheet->setCellValue($col . $currentRow, $header);
            $col++;
        }
        
        // Style header row
        $sheet->getStyle('A' . $currentRow . ':K' . $currentRow)->applyFromArray([
            'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF'], 'size' => 10],
            'fill' => ['fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID, 'startColor' => ['rgb' => $headerBg]],
            'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER, 'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER],
            'borders' => ['allBorders' => ['borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN, 'color' => ['rgb' => 'E5E7EB']]],
        ]);
        $sheet->getRowDimension($currentRow)->setRowHeight(25);
        $currentRow++;

        // Data rows
        $rowIndex = 0;
        foreach ($transactions as $transaction) {
            $isEven = $rowIndex % 2 === 0;
            $bgColor = $isEven ? $zebraLight : $zebraDark;
            
            $sheet->setCellValue('A' . $currentRow, '#' . $transaction->id);
            $sheet->setCellValue('B' . $currentRow, strtoupper($transaction->gateway));
            $sheet->setCellValue('C' . $currentRow, $transaction->reference ?? '-');
            $sheet->setCellValue('D' . $currentRow, $transaction->user_name ?? 'Unknown');
            $sheet->setCellValue('E' . $currentRow, $transaction->amount);
            $sheet->setCellValue('F' . $currentRow, $transaction->gold_amount);
            $sheet->setCellValue('G' . $currentRow, $transaction->payment_method ?? '-');
            $sheet->setCellValue('H' . $currentRow, $transaction->payment_channel ?? '-');
            $sheet->setCellValue('I' . $currentRow, ucfirst($transaction->status));
            $sheet->setCellValue('J' . $currentRow, Carbon::parse($transaction->created_at)->format('Y-m-d H:i'));
            $sheet->setCellValue('K' . $currentRow, $transaction->paid_at ? Carbon::parse($transaction->paid_at)->format('Y-m-d H:i') : '-');

            // Apply base styling
            $sheet->getStyle('A' . $currentRow . ':K' . $currentRow)->applyFromArray([
                'fill' => ['fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID, 'startColor' => ['rgb' => $bgColor]],
                'borders' => ['allBorders' => ['borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN, 'color' => ['rgb' => 'E5E7EB']]],
                'alignment' => ['vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER],
            ]);

            // Format amount column as currency
            $sheet->getStyle('E' . $currentRow)->getNumberFormat()->setFormatCode('#,##0');
            $sheet->getStyle('E' . $currentRow)->applyFromArray([
                'font' => ['bold' => true, 'color' => ['rgb' => '059669']],
            ]);

            // Format gold column
            $sheet->getStyle('F' . $currentRow)->getNumberFormat()->setFormatCode('#,##0');
            $sheet->getStyle('F' . $currentRow)->applyFromArray([
                'font' => ['bold' => true, 'color' => ['rgb' => 'D97706']],
            ]);

            // Style gateway column
            $gatewayKey = strtolower($transaction->gateway);
            if (isset($gatewayColors[$gatewayKey])) {
                $sheet->getStyle('B' . $currentRow)->applyFromArray([
                    'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF']],
                    'fill' => ['fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID, 'startColor' => ['rgb' => $gatewayColors[$gatewayKey]]],
                    'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER],
                ]);
            }

            // Style status column
            $statusLower = strtolower($transaction->status);
            $statusStyle = match($statusLower) {
                'success', 'paid' => ['bg' => $successBg, 'text' => $successText],
                'pending' => ['bg' => $pendingBg, 'text' => $pendingText],
                'failed' => ['bg' => $failedBg, 'text' => $failedText],
                'expired' => ['bg' => $expiredBg, 'text' => $expiredText],
                default => ['bg' => $expiredBg, 'text' => $expiredText],
            };
            $sheet->getStyle('I' . $currentRow)->applyFromArray([
                'font' => ['bold' => true, 'color' => ['rgb' => $statusStyle['text']]],
                'fill' => ['fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID, 'startColor' => ['rgb' => $statusStyle['bg']]],
                'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER],
            ]);

            $currentRow++;
            $rowIndex++;
        }

        // If no transactions
        if (count($transactions) === 0) {
            $sheet->setCellValue('A' . $currentRow, 'No transactions found');
            $sheet->mergeCells('A' . $currentRow . ':K' . $currentRow);
            $sheet->getStyle('A' . $currentRow)->applyFromArray([
                'font' => ['italic' => true, 'color' => ['rgb' => $grayText]],
                'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER],
            ]);
            $currentRow++;
        }

        $currentRow++;

        // === FOOTER SECTION ===
        $sheet->setCellValue('A' . $currentRow, '© ' . now()->year . ' ' . config('app.name', 'Payment Gateway') . '. All rights reserved.');
        $sheet->mergeCells('A' . $currentRow . ':K' . $currentRow);
        $sheet->getStyle('A' . $currentRow)->applyFromArray([
            'font' => ['size' => 9, 'italic' => true, 'color' => ['rgb' => $grayText]],
            'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER],
        ]);
        $currentRow++;

        $sheet->setCellValue('A' . $currentRow, 'This is a computer-generated document. No signature is required.');
        $sheet->mergeCells('A' . $currentRow . ':K' . $currentRow);
        $sheet->getStyle('A' . $currentRow)->applyFromArray([
            'font' => ['size' => 9, 'italic' => true, 'color' => ['rgb' => $grayText]],
            'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER],
        ]);

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

        // Freeze header row
        $sheet->freezePane('A' . ($tableStartRow + 1));

        // Create writer and output
        $filename = 'transactions_' . date('Y-m-d_His') . '.xlsx';
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);

        $headers = [
            'Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            'Content-Disposition' => 'attachment; filename="' . $filename . '"',
            'Cache-Control' => 'max-age=0',
        ];

        return response()->streamDownload(function() use ($writer) {
            $writer->save('php://output');
        }, $filename, $headers);
    }

    /**
     * Export transactions to PDF
     */
    public function exportPdf(Request $request)
    {
        $search = $request->input('search');
        $month = $request->input('month');
        $year = $request->input('year', date('Y'));
        $gateway = $request->input('gateway', 'all');
        $status = $request->input('status', 'all');

        // Get all transactions without pagination
        $transactions = $this->getAllTransactions($search, $month, $year, $gateway, $status, null, false);
        $stats = $this->getStatistics($month, $year, $gateway, $status);

        // Return view for PDF generation
        return view('exports.transactions-pdf', [
            'transactions' => $transactions,
            'stats' => $stats,
            'filters' => [
                'month' => $month,
                'year' => $year,
                'gateway' => $gateway,
                'status' => $status,
            ],
        ]);
    }

    /**
     * Get all transactions from all payment gateways
     */
    private function getAllTransactions($search = null, $month = null, $year = null, $gateway = 'all', $status = 'all', $perPage = 50, $paginate = true)
    {
        // Union queries from all gateways
        $ipaymuQuery = DB::table('ipaymu_transactions')
            ->leftJoin('users', 'ipaymu_transactions.user_id', '=', 'users.ID')
            ->select(
                'ipaymu_transactions.id',
                'ipaymu_transactions.user_id',
                'users.name as user_name',
                'transaction_id as reference',
                'ipaymu_transactions.amount',
                'ipaymu_transactions.gold_amount',
                'ipaymu_transactions.payment_method',
                'ipaymu_transactions.payment_channel',
                'ipaymu_transactions.status',
                'ipaymu_transactions.created_at',
                'ipaymu_transactions.paid_at',
                DB::raw("'ipaymu' as gateway")
            );

        $duitkuQuery = DB::table('duitku_transactions')
            ->leftJoin('users', 'duitku_transactions.user_id', '=', 'users.ID')
            ->select(
                'duitku_transactions.id',
                'duitku_transactions.user_id',
                'users.name as user_name',
                'merchant_order_id as reference',
                'duitku_transactions.amount',
                'duitku_transactions.gold_amount',
                'duitku_transactions.payment_method',
                DB::raw("NULL as payment_channel"),
                'duitku_transactions.status',
                'duitku_transactions.created_at',
                'duitku_transactions.paid_at',
                DB::raw("'duitku' as gateway")
            );

        $tripayQuery = DB::table('tripay_transactions')
            ->leftJoin('users', 'tripay_transactions.user_id', '=', 'users.ID')
            ->select(
                'tripay_transactions.id',
                'tripay_transactions.user_id',
                'users.name as user_name',
                'tripay_transactions.reference',
                'tripay_transactions.amount',
                'tripay_transactions.gold_amount',
                'tripay_transactions.payment_method',
                DB::raw("NULL as payment_channel"),
                // Normalize 'paid' to 'success' for consistent display
                DB::raw("CASE WHEN tripay_transactions.status = 'paid' THEN 'success' ELSE tripay_transactions.status END as status"),
                'tripay_transactions.created_at',
                'tripay_transactions.paid_at',
                DB::raw("'tripay' as gateway")
            );

        $pakasirQuery = DB::table('pakasir_transactions')
            ->leftJoin('users', 'pakasir_transactions.user_id', '=', 'users.ID')
            ->select(
                'pakasir_transactions.id',
                'pakasir_transactions.user_id',
                'users.name as user_name',
                'pakasir_transactions.reference_id as reference',
                'pakasir_transactions.amount',
                'pakasir_transactions.gold_amount',
                'pakasir_transactions.payment_method',
                DB::raw("NULL as payment_channel"),
                'pakasir_transactions.status',
                'pakasir_transactions.created_at',
                'pakasir_transactions.paid_at',
                DB::raw("'pakasir' as gateway")
            );

        // Apply filters
        if ($search) {
            $ipaymuQuery->where(function($q) use ($search) {
                $q->where('ipaymu_transactions.transaction_id', 'like', "%{$search}%")
                  ->orWhere('users.name', 'like', "%{$search}%");
            });
            $duitkuQuery->where(function($q) use ($search) {
                $q->where('duitku_transactions.merchant_order_id', 'like', "%{$search}%")
                  ->orWhere('users.name', 'like', "%{$search}%");
            });
            $tripayQuery->where(function($q) use ($search) {
                $q->where('tripay_transactions.reference', 'like', "%{$search}%")
                  ->orWhere('users.name', 'like', "%{$search}%");
            });
            $pakasirQuery->where(function($q) use ($search) {
                $q->where('pakasir_transactions.reference_id', 'like', "%{$search}%")
                  ->orWhere('users.name', 'like', "%{$search}%");
            });
        }

        if ($month && $year) {
            $startDate = Carbon::createFromDate($year, $month, 1)->startOfMonth();
            $endDate = Carbon::createFromDate($year, $month, 1)->endOfMonth();
            
            $ipaymuQuery->whereBetween('ipaymu_transactions.created_at', [$startDate, $endDate]);
            $duitkuQuery->whereBetween('duitku_transactions.created_at', [$startDate, $endDate]);
            $tripayQuery->whereBetween('tripay_transactions.created_at', [$startDate, $endDate]);
            $pakasirQuery->whereBetween('pakasir_transactions.created_at', [$startDate, $endDate]);
        } elseif ($year) {
            $startDate = Carbon::createFromDate($year, 1, 1)->startOfYear();
            $endDate = Carbon::createFromDate($year, 12, 31)->endOfYear();
            
            $ipaymuQuery->whereBetween('ipaymu_transactions.created_at', [$startDate, $endDate]);
            $duitkuQuery->whereBetween('duitku_transactions.created_at', [$startDate, $endDate]);
            $tripayQuery->whereBetween('tripay_transactions.created_at', [$startDate, $endDate]);
            $pakasirQuery->whereBetween('pakasir_transactions.created_at', [$startDate, $endDate]);
        }


        if ($status !== 'all') {
            $ipaymuQuery->where('ipaymu_transactions.status', $status);
            $duitkuQuery->where('duitku_transactions.status', $status);
            // Tripay uses 'paid' instead of 'success'
            if ($status === 'success') {
                $tripayQuery->whereIn('tripay_transactions.status', ['success', 'paid']);
            } else {
                $tripayQuery->where('tripay_transactions.status', $status);
            }
            $pakasirQuery->where('pakasir_transactions.status', $status);
        }

        // Combine queries based on gateway filter
        if ($gateway === 'ipaymu') {
            $query = $ipaymuQuery;
        } elseif ($gateway === 'duitku') {
            $query = $duitkuQuery;
        } elseif ($gateway === 'tripay') {
            $query = $tripayQuery;
        } elseif ($gateway === 'pakasir') {
            $query = $pakasirQuery;
        } else {
            // Union all queries
            $query = $ipaymuQuery
                ->union($duitkuQuery)
                ->union($tripayQuery)
                ->union($pakasirQuery);
        }

        // Wrap in subquery for ordering
        $query = DB::table(DB::raw("({$query->toSql()}) as transactions"))
            ->mergeBindings($query)
            ->orderBy('created_at', 'desc');

        if ($paginate) {
            return $query->paginate($perPage);
        }

        return $query->get();
    }

    /**
     * Get statistics for dashboard (filtered by gateway and status)
     */
    private function getStatistics($month = null, $year = null, $gateway = 'all', $status = 'all')
    {
        $startDate = null;
        $endDate = null;

        if ($month && $year) {
            $startDate = Carbon::createFromDate($year, $month, 1)->startOfMonth();
            $endDate = Carbon::createFromDate($year, $month, 1)->endOfMonth();
        } elseif ($year) {
            $startDate = Carbon::createFromDate($year, 1, 1)->startOfYear();
            $endDate = Carbon::createFromDate($year, 12, 31)->endOfYear();
        }

        $stats = [
            'total_transactions' => 0,
            'total_amount' => 0, // Only success amount
            'total_success' => 0,
            'total_pending' => 0,
            'total_failed' => 0,
            'by_gateway' => [
                'ipaymu' => 0,
                'duitku' => 0,
                'tripay' => 0,
                'pakasir' => 0,
            ],
        ];

        // Determine which gateways to query
        $gatewaysToQuery = ($gateway === 'all') 
            ? ['ipaymu', 'duitku', 'tripay', 'pakasir'] 
            : [$gateway];

        // Get stats for each gateway
        foreach ($gatewaysToQuery as $g) {
            $table = $g . '_transactions';
            $query = DB::table($table);

            if ($startDate && $endDate) {
                $query->whereBetween('created_at', [$startDate, $endDate]);
            }

            // Apply status filter if not 'all'
            if ($status !== 'all') {
                if ($g === 'tripay' && $status === 'success') {
                    $query->whereIn('status', ['success', 'paid']);
                } else {
                    $query->where('status', $status);
                }
            }

            // For tripay, 'paid' is equivalent to 'success'
            if ($g === 'tripay') {
                $gatewayStats = $query->selectRaw('
                    COUNT(*) as total,
                    SUM(CASE WHEN status IN ("success", "paid") THEN amount ELSE 0 END) as success_amount,
                    SUM(CASE WHEN status IN ("success", "paid") THEN 1 ELSE 0 END) as success,
                    SUM(CASE WHEN status = "pending" THEN 1 ELSE 0 END) as pending,
                    SUM(CASE WHEN status IN ("failed", "expired") THEN 1 ELSE 0 END) as failed
                ')->first();
            } else {
                $gatewayStats = $query->selectRaw('
                    COUNT(*) as total,
                    SUM(CASE WHEN status = "success" THEN amount ELSE 0 END) as success_amount,
                    SUM(CASE WHEN status = "success" THEN 1 ELSE 0 END) as success,
                    SUM(CASE WHEN status = "pending" THEN 1 ELSE 0 END) as pending,
                    SUM(CASE WHEN status IN ("failed", "expired") THEN 1 ELSE 0 END) as failed
                ')->first();
            }

            $stats['total_transactions'] += $gatewayStats->total;
            // Only add success amount to total_amount
            $stats['total_amount'] += $gatewayStats->success_amount ?? 0;
            $stats['total_success'] += $gatewayStats->success;
            $stats['total_pending'] += $gatewayStats->pending;
            $stats['total_failed'] += $gatewayStats->failed;
            $stats['by_gateway'][$g] = $gatewayStats->total;
        }

        return $stats;
    }

    /**
     * Get available years from transactions
     */
    private function getAvailableYears()
    {
        $years = [];

        foreach (['ipaymu_transactions', 'duitku_transactions', 'tripay_transactions', 'pakasir_transactions'] as $table) {
            $tableYears = DB::table($table)
                ->selectRaw('DISTINCT YEAR(created_at) as year')
                ->whereNotNull('created_at')
                ->pluck('year')
                ->toArray();
            
            $years = array_merge($years, $tableYears);
        }

        $years = array_unique($years);
        rsort($years);

        return array_values($years);
    }
}
