<?php

namespace App\Reports\MRRByClient;

use App\Reports\BaseReport;
use Illuminate\Support\Facades\DB;

class MRRByClient extends BaseReport
{
    public function getName(): string
    {
        return __('admin/reports.mrr_by_client_name');
    }

    public function getDescription(): string
    {
        return __('admin/reports.mrr_by_client_description');
    }

    public function getCategory(): string
    {
        return __('admin/reports.mrr_by_client_category');
    }

    public function getIcon(): string
    {
        return 'fas fa-chart-line';
    }

    public function getRouteName(): string
    {
        return 'mrr-by-client';
    }

    public function getRequiredPermission(): string
    {
        return 'generate reports';
    }

    public function getData(array $filters = []): array
    {
        $filters = $this->validateFilters($filters);

        $startDate = $filters['start_date'] ?? now()->subYear();
        $endDate = $filters['end_date'] ?? now();
        $period = $filters['period'] ?? 'monthly';
        $currency = $filters['currency'] ?? 'all';
        $minMRR = $filters['min_mrr'] ?? 0;
        $maxMRR = $filters['max_mrr'] ?? null;

        $query = DB::table('client_products')
            ->whereNull('client_products.deleted_at') // PRC-752 — soft-deleted hariç
            ->join('clients', 'client_products.client_id', '=', 'clients.id')
            ->whereNull('clients.deleted_at') // PRC-735 — soft-deleted hariç
            ->join('products', 'client_products.product_id', '=', 'products.id')
            ->leftJoin('countries', 'clients.country_id', '=', 'countries.id')
            ->where('client_products.status', 'active')
            ->where('client_products.billingcycle', '!=', 'one_time')
            ->whereBetween('client_products.created_at', [$startDate, $endDate]);

        if ($currency !== 'all') {
            $query->where('client_products.currency', $currency);
        }

        // MRR hesaplama
        $query->selectRaw('
            clients.id as client_id,
            CONCAT(clients.first_name, " ", clients.last_name) as client_name,
            clients.email,
            clients.company_name as company,
            countries.a2_iso as country,
            clients.created_at as client_since,
            COUNT(client_products.id) as active_services,
            SUM(CASE
                WHEN client_products.billingcycle = "monthly" THEN client_products.recurring_amount
                WHEN client_products.billingcycle = "quarterly" THEN client_products.recurring_amount / 3
                WHEN client_products.billingcycle = "semi_annually" THEN client_products.recurring_amount / 6
                WHEN client_products.billingcycle = "annually" THEN client_products.recurring_amount / 12
                WHEN client_products.billingcycle = "biennially" THEN client_products.recurring_amount / 24
                WHEN client_products.billingcycle = "triennially" THEN client_products.recurring_amount / 36
                ELSE client_products.recurring_amount
            END) as monthly_recurring_revenue,
            SUM(client_products.recurring_amount) as total_amount,
            GROUP_CONCAT(DISTINCT products.name SEPARATOR ", ") as services
        ')
            ->groupBy('clients.id', 'clients.first_name', 'clients.last_name', 'clients.email', 'clients.company_name', 'countries.a2_iso', 'clients.created_at');

        if ($minMRR > 0) {
            $query->having('monthly_recurring_revenue', '>=', $minMRR);
        }

        if ($maxMRR !== null) {
            $query->having('monthly_recurring_revenue', '<=', $maxMRR);
        }

        $query->orderBy('monthly_recurring_revenue', 'desc');

        $data = $query->get();

        // MRR trend analizi
        if ($period === 'monthly') {
            foreach ($data as $client) {
                $trendData = DB::table('client_products')
                    ->whereNull('client_products.deleted_at') // PRC-752 — soft-deleted hariç
                    ->where('client_products.client_id', $client->client_id)
                    ->where('client_products.status', 'active')
                    ->where('client_products.billingcycle', '!=', 'one_time')
                    ->selectRaw('
                        DATE_FORMAT(client_products.created_at, "%Y-%m") as month,
                        SUM(CASE
                            WHEN client_products.billingcycle = "monthly" THEN client_products.recurring_amount
                            WHEN client_products.billingcycle = "quarterly" THEN client_products.recurring_amount / 3
                            WHEN client_products.billingcycle = "semi_annually" THEN client_products.recurring_amount / 6
                            WHEN client_products.billingcycle = "annually" THEN client_products.recurring_amount / 12
                            WHEN client_products.billingcycle = "biennially" THEN client_products.recurring_amount / 24
                            WHEN client_products.billingcycle = "triennially" THEN client_products.recurring_amount / 36
                            ELSE client_products.recurring_amount
                        END) as mrr
                    ')
                    ->groupByRaw('DATE_FORMAT(client_products.created_at, "%Y-%m")')
                    ->orderBy('month')
                    ->get();

                $client->mrr_trend = $trendData->pluck('mrr')->toArray();
                $client->trend_months = $trendData->pluck('month')->toArray();
            }
        }

        return $data->toArray();
    }

    public function getColumns(): array
    {
        return [
            'client_name' => [
                'title' => __('admin/reports.mrr_by_client_column_client_name'),
                'type' => 'string',
                'sortable' => true,
            ],
            'email' => [
                'title' => __('admin/reports.mrr_by_client_column_email'),
                'type' => 'string',
                'sortable' => true,
            ],
            'company' => [
                'title' => __('admin/reports.mrr_by_client_column_company'),
                'type' => 'string',
                'sortable' => true,
            ],
            'country' => [
                'title' => __('admin/reports.mrr_by_client_column_country'),
                'type' => 'string',
                'sortable' => true,
            ],
            'client_since' => [
                'title' => __('admin/reports.mrr_by_client_column_client_since'),
                'type' => 'date',
                'sortable' => true,
            ],
            'active_services' => [
                'title' => __('admin/reports.mrr_by_client_column_active_services'),
                'type' => 'number',
                'sortable' => true,
            ],
            'monthly_recurring_revenue' => [
                'title' => __('admin/reports.mrr_by_client_column_mrr'),
                'type' => 'currency',
                'sortable' => true,
            ],
            'total_amount' => [
                'title' => __('admin/reports.mrr_by_client_column_total_amount'),
                'type' => 'currency',
                'sortable' => true,
            ],
            'services' => [
                'title' => __('admin/reports.mrr_by_client_column_services'),
                'type' => 'string',
                'sortable' => false,
            ],
        ];
    }

    public function getFilters(): array
    {
        return [
            'start_date' => [
                'type' => 'date',
                'label' => __('admin/reports.mrr_by_client_filter_start_date'),
                'required' => false,
                'default' => now()->subYear()->format('Y-m-d'),
            ],
            'end_date' => [
                'type' => 'date',
                'label' => __('admin/reports.mrr_by_client_filter_end_date'),
                'required' => false,
                'default' => now()->format('Y-m-d'),
            ],
            'period' => [
                'type' => 'select',
                'label' => __('admin/reports.mrr_by_client_filter_period'),
                'required' => false,
                'options' => [
                    'monthly' => __('admin/reports.mrr_by_client_period_monthly'),
                ],
                'default' => 'monthly',
            ],
            'currency' => [
                'type' => 'select',
                'label' => __('admin/reports.mrr_by_client_filter_currency'),
                'required' => false,
                'options' => [
                    'all' => __('admin/reports.profit_loss_currency_all'),
                    'TRY' => __('admin/reports.profit_loss_currency_try'),
                    'USD' => __('admin/reports.profit_loss_currency_usd'),
                    'EUR' => __('admin/reports.profit_loss_currency_eur'),
                ],
                'default' => 'all',
            ],
            'min_mrr' => [
                'type' => 'number',
                'label' => __('admin/reports.mrr_by_client_filter_min_mrr'),
                'required' => false,
                'default' => 0,
            ],
            'max_mrr' => [
                'type' => 'number',
                'label' => __('admin/reports.mrr_by_client_filter_max_mrr'),
                'required' => false,
                'default' => null,
            ],
        ];
    }

    public function hasChart(): bool
    {
        return true;
    }

    public function getChartData(array $filters = []): ?array
    {
        $data = $this->getData($filters);

        // Top 10 MRR müşterisi
        $topClients = array_slice($data, 0, 10);

        return [
            'labels' => array_column($topClients, 'client_name'),
            'datasets' => [
                [
                    'label' => __('admin/reports.mrr_by_client_chart_mrr'),
                    'data' => array_column($topClients, 'monthly_recurring_revenue'),
                    'backgroundColor' => [
                        'rgba(255, 99, 132, 0.8)',
                        'rgba(54, 162, 235, 0.8)',
                        'rgba(255, 205, 86, 0.8)',
                        'rgba(75, 192, 192, 0.8)',
                        'rgba(153, 102, 255, 0.8)',
                        'rgba(255, 159, 64, 0.8)',
                        'rgba(199, 199, 199, 0.8)',
                        'rgba(83, 102, 255, 0.8)',
                        'rgba(78, 252, 3, 0.8)',
                        'rgba(252, 3, 244, 0.8)',
                    ],
                    'borderWidth' => 1,
                ],
            ],
        ];
    }

    public function getChartType(): string
    {
        return 'bar';
    }
}
