<?php

namespace Modules\Sale\Services;

use Config\Database;

class SaleReportService
{
    protected $db;

    public function __construct()
    {
        $this->db = Database::connect();
    }

    /**
     * Normalize common report filters from request GET params.
     *
     * @param array<string, mixed> $get
     * @return array{date_from: string, date_to: string, company_id: ?int, rep_id: ?int, group_id: ?int, source_id: ?int}
     */
    public function resolveFilters(array $get): array
    {
        $dateFrom = trim((string) ($get['date_from'] ?? ''));
        $dateTo   = trim((string) ($get['date_to'] ?? ''));

        if ($dateFrom === '' || ! $this->isValidDate($dateFrom)) {
            $dateFrom = date('Y-01-01');
        }
        if ($dateTo === '' || ! $this->isValidDate($dateTo)) {
            $dateTo = date('Y-m-d');
        }
        if ($dateFrom > $dateTo) {
            [$dateFrom, $dateTo] = [$dateTo, $dateFrom];
        }

        return [
            'date_from'  => $dateFrom,
            'date_to'    => $dateTo,
            'company_id' => $this->nullableInt($get['company_id'] ?? null),
            'rep_id'     => $this->nullableInt($get['rep_id'] ?? null),
            'group_id'   => $this->nullableInt($get['group_id'] ?? null),
            'source_id'  => $this->nullableInt($get['source_id'] ?? null),
        ];
    }

    public function salesPerformance(array $filters): array
    {
        $from = $filters['date_from'];
        $to   = $filters['date_to'];
        $companyId = $filters['company_id'];
        $repId     = $filters['rep_id'];

        $orderTotals = $this->documentMoneySummary('sales_orders', 'order_date', $from, $to, $companyId, [
            'draft', 'cancelled', 'rejected',
        ]);
        $invoiceTotals = $this->documentMoneySummary('invoices', 'invoice_date', $from, $to, $companyId, [
            'draft', 'cancelled',
        ]);
        $paidCollected = $this->sumPaidInvoices($from, $to, $companyId);

        $wonLost = $this->opportunityWinLoss($from, $to, $companyId);
        $closed  = $wonLost['won'] + $wonLost['lost'];
        $winRate = $closed > 0 ? round(($wonLost['won'] / $closed) * 100, 1) : 0.0;

        $targets = $this->targetAttainment($from, $to, $repId);
        $targetAmount   = array_sum(array_column($targets, 'target_amount'));
        $achievedAmount = array_sum(array_column($targets, 'achieved_amount'));
        $attainmentPct  = $targetAmount > 0 ? round(($achievedAmount / $targetAmount) * 100, 1) : 0.0;

        $commissions = $this->commissionSummary($from, $to, $repId);
        $monthlyRevenue = $this->monthlySeries('invoices', 'invoice_date', 'total', $from, $to, $companyId, [
            'draft', 'cancelled',
        ]);
        $orderStatusMix = $this->statusMix('sales_orders', 'order_date', $from, $to, $companyId);

        return [
            'kpis' => [
                $this->kpi('ORDER REVENUE', 'fa-cart-shopping', $this->money($orderTotals['total']), number_format($orderTotals['count']) . ' orders', 'positive', 'ORDERS'),
                $this->kpi('INVOICED', 'fa-file-invoice-dollar', $this->money($invoiceTotals['total']), number_format($invoiceTotals['count']) . ' invoices', 'positive', 'BILLING'),
                $this->kpi('COLLECTED', 'fa-hand-holding-dollar', $this->money($paidCollected), 'payments received', 'positive', 'CASH'),
                $this->kpi('WIN RATE', 'fa-trophy', $winRate . '%', $wonLost['won'] . ' won / ' . $wonLost['lost'] . ' lost', $winRate >= 40 ? 'positive' : 'negative', 'PIPELINE'),
                $this->kpi('TARGET ATTAINMENT', 'fa-bullseye', $attainmentPct . '%', $this->money($achievedAmount) . ' of ' . $this->money($targetAmount), $attainmentPct >= 80 ? 'positive' : 'negative', 'TEAM'),
                $this->kpi('COMMISSIONS', 'fa-percent', $this->money($commissions['total']), number_format($commissions['count']) . ' records', 'positive', 'PAYOUTS'),
            ],
            'monthlyRevenue' => $monthlyRevenue,
            'orderStatusMix' => $orderStatusMix,
            'targets'        => $targets,
            'commissions'    => $commissions,
            'charts'         => [
                [
                    'id'          => 'perfRevenueChart',
                    'type'        => 'line',
                    'labels'      => $monthlyRevenue['labels'],
                    'values'      => $monthlyRevenue['values'],
                    'valueSuffix' => '',
                ],
                [
                    'id'     => 'perfOrderStatusChart',
                    'type'   => 'doughnut',
                    'labels' => $orderStatusMix['labels'],
                    'values' => $orderStatusMix['values'],
                ],
            ],
        ];
    }

    public function leadConversion(array $filters): array
    {
        $from = $filters['date_from'];
        $to   = $filters['date_to'];
        $sourceId = $filters['source_id'];

        $builder = $this->db->table('leads')
            ->where('created_at >=', $from . ' 00:00:00')
            ->where('created_at <=', $to . ' 23:59:59');
        if ($sourceId) {
            $builder->where('lead_source_id', $sourceId);
        }
        $totalLeads = $builder->countAllResults();

        $funnel = $this->leadFunnel($from, $to, $sourceId);
        $converted = (int) ($funnel['converted'] ?? 0);
        $conversionRate = $totalLeads > 0 ? round(($converted / $totalLeads) * 100, 1) : 0.0;

        $avgDays = $this->avgDaysToConvert($from, $to, $sourceId);
        $bySource = $this->leadsBySource($from, $to);
        $byAssignee = $this->leadsByAssignee($from, $to, $sourceId);
        $recent = $this->recentConversions($from, $to, $sourceId, 15);
        $typeSplit = $this->conversionTypeSplit($from, $to, $sourceId);
        $estimatedValue = $this->convertedEstimatedValue($from, $to, $sourceId);

        $funnelLabels = ['New', 'Contacted', 'Qualified', 'Unqualified', 'Converted'];
        $funnelKeys   = ['new', 'contacted', 'qualified', 'unqualified', 'converted'];
        $funnelValues = array_map(static fn ($k) => (int) ($funnel[$k] ?? 0), $funnelKeys);

        return [
            'kpis' => [
                $this->kpi('TOTAL LEADS', 'fa-user-tag', number_format($totalLeads), 'in selected period', 'positive', 'PIPELINE'),
                $this->kpi('CONVERTED', 'fa-check-double', number_format($converted), $conversionRate . '% conversion rate', $conversionRate >= 20 ? 'positive' : 'negative', 'WINS'),
                $this->kpi('AVG DAYS TO CONVERT', 'fa-clock', $avgDays !== null ? (string) $avgDays : '—', 'from create to convert', 'positive', 'SPEED'),
                $this->kpi('EST. VALUE CONVERTED', 'fa-coins', $this->money($estimatedValue), 'from converted leads', 'positive', 'VALUE'),
            ],
            'funnel'       => array_combine($funnelKeys, $funnelValues),
            'bySource'     => $bySource,
            'byAssignee'   => $byAssignee,
            'recent'       => $recent,
            'typeSplit'    => $typeSplit,
            'charts'       => [
                [
                    'id'     => 'leadFunnelChart',
                    'type'   => 'bar',
                    'labels' => $funnelLabels,
                    'values' => $funnelValues,
                ],
                [
                    'id'     => 'leadSourceChart',
                    'type'   => 'doughnut',
                    'labels' => array_column($bySource, 'label'),
                    'values' => array_map('intval', array_column($bySource, 'total')),
                ],
            ],
        ];
    }

    public function revenueForecast(array $filters): array
    {
        $from = $filters['date_from'];
        $to   = $filters['date_to'];
        $companyId = $filters['company_id'];

        $open = $this->db->table('opportunities o')
            ->select('o.*, c.name as company_name, s.name as stage_name, (o.expected_revenue * o.probability / 100) AS weighted_revenue', false)
            ->join('companies c', 'c.id = o.company_id', 'left')
            ->join('sales_stages s', 's.id = o.sales_stage_id', 'left')
            ->where('o.status', 'open');

        if ($companyId) {
            $open->where('o.company_id', $companyId);
        }
        // Forecast window: expected close within range, or null close date
        $open->groupStart()
            ->groupStart()
                ->where('o.expected_close_date >=', $from)
                ->where('o.expected_close_date <=', $to)
            ->groupEnd()
            ->orWhere('o.expected_close_date IS NULL', null, false)
            ->groupEnd();

        $rows = $open->orderBy('o.expected_close_date', 'ASC')->get()->getResultArray();

        $pipeline = 0.0;
        $weighted = 0.0;
        $commit   = 0.0;
        $best     = 0.0;
        foreach ($rows as $row) {
            $rev = (float) $row['expected_revenue'];
            $w   = (float) $row['weighted_revenue'];
            $pipeline += $rev;
            $weighted += $w;
            $best     += $rev;
            if ((int) $row['probability'] >= 75) {
                $commit += $rev;
            }
        }

        $byMonth = $this->forecastByMonth($rows);
        $byStage = $this->forecastByStage($companyId);

        $wonInPeriod = $this->db->table('opportunities')
            ->selectSum('expected_revenue', 'total')
            ->where('status', 'won')
            ->where('updated_at >=', $from . ' 00:00:00')
            ->where('updated_at <=', $to . ' 23:59:59');
        if ($companyId) {
            $wonInPeriod->where('company_id', $companyId);
        }
        $wonTotal = (float) ($wonInPeriod->get()->getRowArray()['total'] ?? 0);

        return [
            'kpis' => [
                $this->kpi('OPEN PIPELINE', 'fa-chart-kanban', $this->money($pipeline), number_format(count($rows)) . ' opportunities', 'positive', 'PIPELINE'),
                $this->kpi('WEIGHTED FORECAST', 'fa-scale-balanced', $this->money($weighted), 'revenue × probability', 'positive', 'FORECAST'),
                $this->kpi('COMMIT (≥75%)', 'fa-handshake', $this->money($commit), 'high-probability deals', 'positive', 'COMMIT'),
                $this->kpi('WON IN PERIOD', 'fa-trophy', $this->money($wonTotal), 'closed-won revenue', 'positive', 'WINS'),
            ],
            'rows'    => $rows,
            'byMonth' => $byMonth,
            'byStage' => $byStage,
            'charts'  => [
                [
                    'id'     => 'forecastMonthChart',
                    'type'   => 'bar',
                    'labels' => $byMonth['labels'],
                    'values' => $byMonth['weighted'],
                ],
                [
                    'id'     => 'forecastStageChart',
                    'type'   => 'doughnut',
                    'labels' => $byStage['labels'],
                    'values' => $byStage['values'],
                ],
            ],
        ];
    }

    public function customerAnalysis(array $filters): array
    {
        $from = $filters['date_from'];
        $to   = $filters['date_to'];
        $companyId = $filters['company_id'];
        $groupId   = $filters['group_id'];

        $topCustomers = $this->topCustomersByRevenue($from, $to, $groupId, 15);
        $byGroup = $this->revenueByCustomerGroup($from, $to);
        $outstanding = $this->outstandingByCompany($companyId, $groupId, 15);
        $activity = $this->activityByCompany($from, $to, $companyId, 10);
        $returns = $this->returnRate($from, $to, $companyId);

        $activeCompanies = $this->db->table('companies')->where('status', 'active');
        if ($groupId) {
            $activeCompanies->where('customer_group_id', $groupId);
        }
        if ($companyId) {
            $activeCompanies->where('id', $companyId);
        }
        $companyCount = $activeCompanies->countAllResults();

        $totalRevenue = array_sum(array_map(static fn ($r) => (float) $r['revenue'], $topCustomers));
        $totalOutstanding = array_sum(array_map(static fn ($r) => (float) $r['balance'], $outstanding));
        $aov = $this->averageOrderValue($from, $to, $companyId, $groupId);

        return [
            'kpis' => [
                $this->kpi('ACTIVE CUSTOMERS', 'fa-building', number_format($companyCount), 'companies in scope', 'positive', 'BASE'),
                $this->kpi('REVENUE (TOP)', 'fa-chart-line', $this->money($totalRevenue), 'from listed customers', 'positive', 'SALES'),
                $this->kpi('AVG ORDER VALUE', 'fa-receipt', $this->money($aov), 'approved+ orders', 'positive', 'AOV'),
                $this->kpi('OUTSTANDING AR', 'fa-hand-holding-dollar', $this->money($totalOutstanding), 'unpaid balances', $totalOutstanding > 0 ? 'negative' : 'positive', 'AR'),
                $this->kpi('RETURN RATE', 'fa-rotate-left', $returns['rate'] . '%', $this->money($returns['returns']) . ' returned', $returns['rate'] > 5 ? 'negative' : 'positive', 'QUALITY'),
            ],
            'topCustomers' => $topCustomers,
            'byGroup'      => $byGroup,
            'outstanding'  => $outstanding,
            'activity'     => $activity,
            'charts'       => [
                [
                    'id'     => 'customerRevenueChart',
                    'type'   => 'bar',
                    'labels' => array_map(static fn ($r) => $r['company_name'], array_slice($topCustomers, 0, 8)),
                    'values' => array_map(static fn ($r) => round((float) $r['revenue'], 2), array_slice($topCustomers, 0, 8)),
                ],
                [
                    'id'     => 'customerGroupChart',
                    'type'   => 'doughnut',
                    'labels' => array_column($byGroup, 'label'),
                    'values' => array_map(static fn ($r) => round((float) $r['revenue'], 2), $byGroup),
                ],
            ],
        ];
    }

    public function salesReports(array $filters): array
    {
        $from = $filters['date_from'];
        $to   = $filters['date_to'];
        $companyId = $filters['company_id'];

        $quotations = $this->statusMix('quotations', 'quotation_date', $from, $to, $companyId);
        $orders     = $this->statusMix('sales_orders', 'order_date', $from, $to, $companyId);
        $invoices   = $this->statusMix('invoices', 'invoice_date', $from, $to, $companyId);
        $deliveries = $this->statusMix('deliveries', 'delivery_date', $from, $to, $companyId);
        $returns    = $this->statusMix('sales_returns', 'return_date', $from, $to, $companyId);
        $refunds    = $this->statusMix('refunds', 'refund_date', $from, $to, $companyId);

        $quoteTotals = $this->documentMoneySummary('quotations', 'quotation_date', $from, $to, $companyId, ['cancelled']);
        $orderTotals = $this->documentMoneySummary('sales_orders', 'order_date', $from, $to, $companyId, ['draft', 'cancelled', 'rejected']);
        $invoiceTotals = $this->documentMoneySummary('invoices', 'invoice_date', $from, $to, $companyId, ['draft', 'cancelled']);
        $returnTotals = $this->documentMoneySummary('sales_returns', 'return_date', $from, $to, $companyId, ['cancelled', 'draft']);
        $refundTotals = $this->refundMoneySummary($from, $to, $companyId);

        $quoteConverted = $this->countByStatus('quotations', 'quotation_date', $from, $to, $companyId, 'converted');
        $quoteSentLike  = $quoteConverted
            + $this->countByStatus('quotations', 'quotation_date', $from, $to, $companyId, 'sent')
            + $this->countByStatus('quotations', 'quotation_date', $from, $to, $companyId, 'approved');
        $quoteWinRate = $quoteSentLike > 0 ? round(($quoteConverted / $quoteSentLike) * 100, 1) : 0.0;

        $aging = $this->invoiceAging($companyId);
        $monthly = $this->monthlySeries('invoices', 'invoice_date', 'total', $from, $to, $companyId, ['draft', 'cancelled']);

        $sections = [
            ['label' => 'Quotations', 'path' => 'sale/quotations', 'mix' => $quotations, 'total' => $quoteTotals['total'], 'count' => $quoteTotals['count']],
            ['label' => 'Sales Orders', 'path' => 'sale/orders', 'mix' => $orders, 'total' => $orderTotals['total'], 'count' => $orderTotals['count']],
            ['label' => 'Invoices', 'path' => 'sale/invoices', 'mix' => $invoices, 'total' => $invoiceTotals['total'], 'count' => $invoiceTotals['count']],
            ['label' => 'Deliveries', 'path' => 'sale/delivery-notes', 'mix' => $deliveries, 'total' => null, 'count' => array_sum($deliveries['values'])],
            ['label' => 'Sales Returns', 'path' => 'sale/sales-returns', 'mix' => $returns, 'total' => $returnTotals['total'], 'count' => $returnTotals['count']],
            ['label' => 'Refunds', 'path' => 'sale/refunds', 'mix' => $refunds, 'total' => $refundTotals['total'], 'count' => $refundTotals['count']],
        ];

        return [
            'kpis' => [
                $this->kpi('QUOTATIONS', 'fa-file-circle-plus', number_format($quoteTotals['count']), $this->money($quoteTotals['total']) . ' value', 'positive', 'QUOTES'),
                $this->kpi('QUOTE WIN RATE', 'fa-percent', $quoteWinRate . '%', $quoteConverted . ' converted', $quoteWinRate >= 30 ? 'positive' : 'negative', 'CONVERSION'),
                $this->kpi('ORDERS', 'fa-boxes-stacked', number_format($orderTotals['count']), $this->money($orderTotals['total']), 'positive', 'ORDERS'),
                $this->kpi('INVOICED', 'fa-file-invoice', $this->money($invoiceTotals['total']), number_format($invoiceTotals['count']) . ' invoices', 'positive', 'BILLING'),
                $this->kpi('RETURNS', 'fa-box-open', $this->money($returnTotals['total']), number_format($returnTotals['count']) . ' returns', $returnTotals['count'] > 0 ? 'negative' : 'positive', 'RETURNS'),
                $this->kpi('REFUNDS', 'fa-receipt', $this->money($refundTotals['total']), number_format($refundTotals['count']) . ' refunds', $refundTotals['count'] > 0 ? 'negative' : 'positive', 'CASH'),
            ],
            'sections' => $sections,
            'aging'    => $aging,
            'monthly'  => $monthly,
            'charts'   => [
                [
                    'id'     => 'opsMonthlyChart',
                    'type'   => 'line',
                    'labels' => $monthly['labels'],
                    'values' => $monthly['values'],
                ],
                [
                    'id'     => 'opsInvoiceStatusChart',
                    'type'   => 'doughnut',
                    'labels' => $invoices['labels'],
                    'values' => $invoices['values'],
                ],
            ],
        ];
    }

    // -------------------------------------------------------------------------
    // Shared query helpers
    // -------------------------------------------------------------------------

    private function documentMoneySummary(
        string $table,
        string $dateCol,
        string $from,
        string $to,
        ?int $companyId,
        array $excludeStatuses
    ): array {
        $builder = $this->db->table($table)
            ->select('COUNT(*) AS cnt, COALESCE(SUM(total), 0) AS total', false)
            ->where("{$dateCol} >=", $from)
            ->where("{$dateCol} <=", $to);

        if ($excludeStatuses !== []) {
            $builder->whereNotIn('status', $excludeStatuses);
        }
        if ($companyId && $this->tableHasCompany($table)) {
            $builder->where('company_id', $companyId);
        }

        $row = $builder->get()->getRowArray();

        return [
            'count' => (int) ($row['cnt'] ?? 0),
            'total' => (float) ($row['total'] ?? 0),
        ];
    }

    private function refundMoneySummary(string $from, string $to, ?int $companyId): array
    {
        $builder = $this->db->table('refunds r')
            ->select('COUNT(*) AS cnt, COALESCE(SUM(r.amount), 0) AS total', false)
            ->where('r.refund_date >=', $from)
            ->where('r.refund_date <=', $to)
            ->whereNotIn('r.status', ['cancelled', 'draft']);

        if ($companyId) {
            $builder->where('r.company_id', $companyId);
        }

        $row = $builder->get()->getRowArray();

        return [
            'count' => (int) ($row['cnt'] ?? 0),
            'total' => (float) ($row['total'] ?? 0),
        ];
    }

    private function sumPaidInvoices(string $from, string $to, ?int $companyId): float
    {
        $builder = $this->db->table('invoices')
            ->select('COALESCE(SUM(amount_paid), 0) AS total', false)
            ->where('invoice_date >=', $from)
            ->where('invoice_date <=', $to)
            ->whereNotIn('status', ['draft', 'cancelled']);

        if ($companyId) {
            $builder->where('company_id', $companyId);
        }

        return (float) ($builder->get()->getRowArray()['total'] ?? 0);
    }

    private function opportunityWinLoss(string $from, string $to, ?int $companyId): array
    {
        $builder = $this->db->table('opportunities')
            ->select('status, COUNT(*) AS total')
            ->whereIn('status', ['won', 'lost'])
            ->where('updated_at >=', $from . ' 00:00:00')
            ->where('updated_at <=', $to . ' 23:59:59')
            ->groupBy('status');

        if ($companyId) {
            $builder->where('company_id', $companyId);
        }

        $out = ['won' => 0, 'lost' => 0];
        foreach ($builder->get()->getResultArray() as $row) {
            $out[$row['status']] = (int) $row['total'];
        }

        return $out;
    }

    private function targetAttainment(string $from, string $to, ?int $repId): array
    {
        $builder = $this->db->table('sales_targets t')
            ->select('t.*, r.name as rep_name, ter.name as territory_name')
            ->join('sales_representatives r', 'r.id = t.sales_rep_id', 'left')
            ->join('sales_territories ter', 'ter.id = t.territory_id', 'left')
            ->where('t.status !=', 'cancelled')
            ->where('t.period_start <=', $to)
            ->where('t.period_end >=', $from)
            ->orderBy('t.period_start', 'DESC');

        if ($repId) {
            $builder->where('t.sales_rep_id', $repId);
        }

        $rows = $builder->get()->getResultArray();
        foreach ($rows as &$row) {
            $target = (float) $row['target_amount'];
            $achieved = (float) $row['achieved_amount'];
            $row['attainment_pct'] = $target > 0 ? round(($achieved / $target) * 100, 1) : 0.0;
            $row['target_amount'] = $target;
            $row['achieved_amount'] = $achieved;
        }
        unset($row);

        return $rows;
    }

    private function commissionSummary(string $from, string $to, ?int $repId): array
    {
        $builder = $this->db->table('sales_commissions')
            ->select('COUNT(*) AS cnt, COALESCE(SUM(commission_amount), 0) AS total', false)
            ->whereNotIn('status', ['cancelled'])
            ->where('period_start <=', $to)
            ->where('period_end >=', $from);

        if ($repId) {
            $builder->where('sales_rep_id', $repId);
        }

        $row = $builder->get()->getRowArray();

        $byStatus = $this->db->table('sales_commissions')
            ->select('status, COUNT(*) AS total, COALESCE(SUM(commission_amount), 0) AS amount', false)
            ->whereNotIn('status', ['cancelled'])
            ->where('period_start <=', $to)
            ->where('period_end >=', $from);
        if ($repId) {
            $byStatus->where('sales_rep_id', $repId);
        }
        $byStatus->groupBy('status');

        return [
            'count'    => (int) ($row['cnt'] ?? 0),
            'total'    => (float) ($row['total'] ?? 0),
            'byStatus' => $byStatus->get()->getResultArray(),
        ];
    }

    private function monthlySeries(
        string $table,
        string $dateCol,
        string $amountCol,
        string $from,
        string $to,
        ?int $companyId,
        array $excludeStatuses
    ): array {
        $builder = $this->db->table($table)
            ->select("DATE_FORMAT({$dateCol}, '%Y-%m') AS period, COALESCE(SUM({$amountCol}), 0) AS total", false)
            ->where("{$dateCol} >=", $from)
            ->where("{$dateCol} <=", $to)
            ->groupBy('period')
            ->orderBy('period', 'ASC');

        if ($excludeStatuses !== []) {
            $builder->whereNotIn('status', $excludeStatuses);
        }
        if ($companyId && $this->tableHasCompany($table)) {
            $builder->where('company_id', $companyId);
        }

        $map = [];
        foreach ($builder->get()->getResultArray() as $row) {
            $map[$row['period']] = (float) $row['total'];
        }

        $labels = [];
        $values = [];
        $cursor = strtotime(date('Y-m-01', strtotime($from)));
        $end    = strtotime(date('Y-m-01', strtotime($to)));
        while ($cursor <= $end) {
            $key = date('Y-m', $cursor);
            $labels[] = date('M Y', $cursor);
            $values[] = round($map[$key] ?? 0, 2);
            $cursor = strtotime('+1 month', $cursor);
        }

        return ['labels' => $labels, 'values' => $values];
    }

    private function statusMix(string $table, string $dateCol, string $from, string $to, ?int $companyId): array
    {
        $builder = $this->db->table($table)
            ->select('status, COUNT(*) AS total')
            ->where("{$dateCol} >=", $from)
            ->where("{$dateCol} <=", $to)
            ->groupBy('status')
            ->orderBy('total', 'DESC');

        if ($companyId && $this->tableHasCompany($table)) {
            $builder->where('company_id', $companyId);
        }

        $rows = $builder->get()->getResultArray();
        if ($rows === []) {
            return ['labels' => ['No data'], 'values' => [0], 'rows' => []];
        }

        return [
            'labels' => array_map(static fn ($r) => ucwords(str_replace('_', ' ', $r['status'])), $rows),
            'values' => array_map(static fn ($r) => (int) $r['total'], $rows),
            'rows'   => $rows,
        ];
    }

    private function countByStatus(string $table, string $dateCol, string $from, string $to, ?int $companyId, string $status): int
    {
        $builder = $this->db->table($table)
            ->where("{$dateCol} >=", $from)
            ->where("{$dateCol} <=", $to)
            ->where('status', $status);

        if ($companyId && $this->tableHasCompany($table)) {
            $builder->where('company_id', $companyId);
        }

        return $builder->countAllResults();
    }

    private function leadFunnel(string $from, string $to, ?int $sourceId): array
    {
        $builder = $this->db->table('leads')
            ->select('status, COUNT(*) AS total')
            ->where('created_at >=', $from . ' 00:00:00')
            ->where('created_at <=', $to . ' 23:59:59')
            ->groupBy('status');

        if ($sourceId) {
            $builder->where('lead_source_id', $sourceId);
        }

        $out = [];
        foreach ($builder->get()->getResultArray() as $row) {
            $out[$row['status']] = (int) $row['total'];
        }

        return $out;
    }

    private function avgDaysToConvert(string $from, string $to, ?int $sourceId): ?float
    {
        $builder = $this->db->table('leads')
            ->select('AVG(DATEDIFF(converted_at, created_at)) AS avg_days', false)
            ->where('status', 'converted')
            ->where('converted_at IS NOT NULL', null, false)
            ->where('converted_at >=', $from . ' 00:00:00')
            ->where('converted_at <=', $to . ' 23:59:59');

        if ($sourceId) {
            $builder->where('lead_source_id', $sourceId);
        }

        $val = $builder->get()->getRowArray()['avg_days'] ?? null;

        return $val !== null ? round((float) $val, 1) : null;
    }

    private function leadsBySource(string $from, string $to): array
    {
        $rows = $this->db->query("
            SELECT COALESCE(s.name, 'Unassigned') AS label,
                   COUNT(l.id) AS total,
                   SUM(CASE WHEN l.status = 'converted' THEN 1 ELSE 0 END) AS converted,
                   COALESCE(SUM(l.estimated_value), 0) AS estimated_value
            FROM leads l
            LEFT JOIN lead_sources s ON s.id = l.lead_source_id
            WHERE l.created_at >= ? AND l.created_at <= ?
            GROUP BY s.id, s.name
            ORDER BY total DESC
        ", [$from . ' 00:00:00', $to . ' 23:59:59'])->getResultArray();

        foreach ($rows as &$row) {
            $total = (int) $row['total'];
            $converted = (int) $row['converted'];
            $row['conversion_rate'] = $total > 0 ? round(($converted / $total) * 100, 1) : 0.0;
            $row['estimated_value'] = (float) $row['estimated_value'];
        }
        unset($row);

        return $rows;
    }

    private function leadsByAssignee(string $from, string $to, ?int $sourceId): array
    {
        $sql = "
            SELECT COALESCE(CONCAT(u.first_name, ' ', u.last_name), 'Unassigned') AS label,
                   COUNT(l.id) AS total,
                   SUM(CASE WHEN l.status = 'converted' THEN 1 ELSE 0 END) AS converted
            FROM leads l
            LEFT JOIN users u ON u.id = l.assigned_to
            WHERE l.created_at >= ? AND l.created_at <= ?
        ";
        $params = [$from . ' 00:00:00', $to . ' 23:59:59'];
        if ($sourceId) {
            $sql .= ' AND l.lead_source_id = ?';
            $params[] = $sourceId;
        }
        $sql .= ' GROUP BY l.assigned_to, u.first_name, u.last_name ORDER BY total DESC LIMIT 15';

        $rows = $this->db->query($sql, $params)->getResultArray();
        foreach ($rows as &$row) {
            $total = (int) $row['total'];
            $converted = (int) $row['converted'];
            $row['conversion_rate'] = $total > 0 ? round(($converted / $total) * 100, 1) : 0.0;
        }
        unset($row);

        return $rows;
    }

    private function recentConversions(string $from, string $to, ?int $sourceId, int $limit): array
    {
        $builder = $this->db->table('lead_conversions lc')
            ->select("lc.*, l.first_name, l.last_name, l.company_name, l.estimated_value, s.name as source_name,
                CONCAT(u.first_name, ' ', u.last_name) as converted_by_name", false)
            ->join('leads l', 'l.id = lc.lead_id')
            ->join('lead_sources s', 's.id = l.lead_source_id', 'left')
            ->join('users u', 'u.id = lc.converted_by', 'left')
            ->where('lc.converted_at >=', $from . ' 00:00:00')
            ->where('lc.converted_at <=', $to . ' 23:59:59')
            ->orderBy('lc.converted_at', 'DESC')
            ->limit($limit);

        if ($sourceId) {
            $builder->where('l.lead_source_id', $sourceId);
        }

        return $builder->get()->getResultArray();
    }

    private function conversionTypeSplit(string $from, string $to, ?int $sourceId): array
    {
        $builder = $this->db->table('lead_conversions lc')
            ->select('lc.conversion_type, COUNT(*) AS total')
            ->join('leads l', 'l.id = lc.lead_id')
            ->where('lc.converted_at >=', $from . ' 00:00:00')
            ->where('lc.converted_at <=', $to . ' 23:59:59')
            ->groupBy('lc.conversion_type');

        if ($sourceId) {
            $builder->where('l.lead_source_id', $sourceId);
        }

        return $builder->get()->getResultArray();
    }

    private function convertedEstimatedValue(string $from, string $to, ?int $sourceId): float
    {
        $builder = $this->db->table('leads')
            ->select('COALESCE(SUM(estimated_value), 0) AS total', false)
            ->where('status', 'converted')
            ->where('converted_at >=', $from . ' 00:00:00')
            ->where('converted_at <=', $to . ' 23:59:59');

        if ($sourceId) {
            $builder->where('lead_source_id', $sourceId);
        }

        return (float) ($builder->get()->getRowArray()['total'] ?? 0);
    }

    private function forecastByMonth(array $rows): array
    {
        $map = [];
        foreach ($rows as $row) {
            $key = $row['expected_close_date']
                ? date('Y-m', strtotime($row['expected_close_date']))
                : 'unscheduled';
            if (! isset($map[$key])) {
                $map[$key] = ['pipeline' => 0.0, 'weighted' => 0.0];
            }
            $map[$key]['pipeline'] += (float) $row['expected_revenue'];
            $map[$key]['weighted'] += (float) $row['weighted_revenue'];
        }
        ksort($map);

        $labels = [];
        $pipeline = [];
        $weighted = [];
        foreach ($map as $key => $vals) {
            $labels[] = $key === 'unscheduled' ? 'Unscheduled' : date('M Y', strtotime($key . '-01'));
            $pipeline[] = round($vals['pipeline'], 2);
            $weighted[] = round($vals['weighted'], 2);
        }

        return ['labels' => $labels, 'pipeline' => $pipeline, 'weighted' => $weighted];
    }

    private function forecastByStage(?int $companyId): array
    {
        $builder = $this->db->table('opportunities o')
            ->select('COALESCE(s.name, \'No stage\') AS label, COALESCE(SUM(o.expected_revenue * o.probability / 100), 0) AS total', false)
            ->join('sales_stages s', 's.id = o.sales_stage_id', 'left')
            ->where('o.status', 'open')
            ->groupBy('o.sales_stage_id, s.name, s.sort_order')
            ->orderBy('s.sort_order', 'ASC');

        if ($companyId) {
            $builder->where('o.company_id', $companyId);
        }

        $rows = $builder->get()->getResultArray();
        if ($rows === []) {
            return ['labels' => ['No data'], 'values' => [0]];
        }

        return [
            'labels' => array_column($rows, 'label'),
            'values' => array_map(static fn ($r) => round((float) $r['total'], 2), $rows),
        ];
    }

    private function topCustomersByRevenue(string $from, string $to, ?int $groupId, int $limit): array
    {
        $sql = "
            SELECT c.id, c.name AS company_name, g.name AS group_name,
                   COUNT(i.id) AS invoice_count,
                   COALESCE(SUM(i.total), 0) AS revenue,
                   COALESCE(SUM(i.amount_paid), 0) AS paid
            FROM companies c
            LEFT JOIN customer_groups g ON g.id = c.customer_group_id
            INNER JOIN invoices i ON i.company_id = c.id
                AND i.invoice_date >= ? AND i.invoice_date <= ?
                AND i.status NOT IN ('draft', 'cancelled')
            WHERE 1 = 1
        ";
        $params = [$from, $to];
        if ($groupId) {
            $sql .= ' AND c.customer_group_id = ?';
            $params[] = $groupId;
        }
        $sql .= ' GROUP BY c.id, c.name, g.name ORDER BY revenue DESC LIMIT ' . (int) $limit;

        return $this->db->query($sql, $params)->getResultArray();
    }

    private function revenueByCustomerGroup(string $from, string $to): array
    {
        return $this->db->query("
            SELECT COALESCE(g.name, 'Ungrouped') AS label,
                   COALESCE(SUM(i.total), 0) AS revenue,
                   COUNT(DISTINCT c.id) AS companies
            FROM invoices i
            INNER JOIN companies c ON c.id = i.company_id
            LEFT JOIN customer_groups g ON g.id = c.customer_group_id
            WHERE i.invoice_date >= ? AND i.invoice_date <= ?
              AND i.status NOT IN ('draft', 'cancelled')
            GROUP BY g.id, g.name
            ORDER BY revenue DESC
        ", [$from, $to])->getResultArray();
    }

    private function outstandingByCompany(?int $companyId, ?int $groupId, int $limit): array
    {
        $sql = "
            SELECT c.id, c.name AS company_name,
                   COUNT(i.id) AS invoice_count,
                   COALESCE(SUM(i.total - i.amount_paid), 0) AS balance
            FROM invoices i
            INNER JOIN companies c ON c.id = i.company_id
            WHERE i.status NOT IN ('draft', 'cancelled', 'paid')
              AND i.total > i.amount_paid
        ";
        $params = [];
        if ($companyId) {
            $sql .= ' AND c.id = ?';
            $params[] = $companyId;
        }
        if ($groupId) {
            $sql .= ' AND c.customer_group_id = ?';
            $params[] = $groupId;
        }
        $sql .= ' GROUP BY c.id, c.name HAVING balance > 0 ORDER BY balance DESC LIMIT ' . (int) $limit;

        return $this->db->query($sql, $params)->getResultArray();
    }

    private function activityByCompany(string $from, string $to, ?int $companyId, int $limit): array
    {
        $builder = $this->db->table('sales_activities a')
            ->select('c.name AS company_name, a.activity_type, COUNT(*) AS total')
            ->join('companies c', 'c.id = a.company_id', 'left')
            ->where('a.created_at >=', $from . ' 00:00:00')
            ->where('a.created_at <=', $to . ' 23:59:59')
            ->where('a.company_id IS NOT NULL', null, false)
            ->groupBy('a.company_id, c.name, a.activity_type')
            ->orderBy('total', 'DESC')
            ->limit($limit);

        if ($companyId) {
            $builder->where('a.company_id', $companyId);
        }

        return $builder->get()->getResultArray();
    }

    private function returnRate(string $from, string $to, ?int $companyId): array
    {
        $inv = $this->documentMoneySummary('invoices', 'invoice_date', $from, $to, $companyId, ['draft', 'cancelled']);
        $ret = $this->documentMoneySummary('sales_returns', 'return_date', $from, $to, $companyId, ['draft', 'cancelled']);
        $rate = $inv['total'] > 0 ? round(($ret['total'] / $inv['total']) * 100, 1) : 0.0;

        return [
            'invoices' => $inv['total'],
            'returns'  => $ret['total'],
            'rate'     => $rate,
        ];
    }

    private function averageOrderValue(string $from, string $to, ?int $companyId, ?int $groupId): float
    {
        $builder = $this->db->table('sales_orders o')
            ->select('COALESCE(AVG(o.total), 0) AS aov', false)
            ->where('o.order_date >=', $from)
            ->where('o.order_date <=', $to)
            ->whereNotIn('o.status', ['draft', 'cancelled', 'rejected']);

        if ($companyId) {
            $builder->where('o.company_id', $companyId);
        }
        if ($groupId) {
            $builder->join('companies c', 'c.id = o.company_id')
                ->where('c.customer_group_id', $groupId);
        }

        return (float) ($builder->get()->getRowArray()['aov'] ?? 0);
    }

    private function invoiceAging(?int $companyId): array
    {
        $today = date('Y-m-d');
        $builder = $this->db->table('invoices')
            ->select("
                SUM(CASE WHEN due_date >= '{$today}' THEN (total - amount_paid) ELSE 0 END) AS current_due,
                SUM(CASE WHEN due_date < '{$today}' AND due_date >= DATE_SUB('{$today}', INTERVAL 30 DAY) THEN (total - amount_paid) ELSE 0 END) AS d1_30,
                SUM(CASE WHEN due_date < DATE_SUB('{$today}', INTERVAL 30 DAY) AND due_date >= DATE_SUB('{$today}', INTERVAL 60 DAY) THEN (total - amount_paid) ELSE 0 END) AS d31_60,
                SUM(CASE WHEN due_date < DATE_SUB('{$today}', INTERVAL 60 DAY) AND due_date >= DATE_SUB('{$today}', INTERVAL 90 DAY) THEN (total - amount_paid) ELSE 0 END) AS d61_90,
                SUM(CASE WHEN due_date < DATE_SUB('{$today}', INTERVAL 90 DAY) THEN (total - amount_paid) ELSE 0 END) AS d90_plus
            ", false)
            ->whereNotIn('status', ['draft', 'cancelled', 'paid'])
            ->where('total > amount_paid', null, false);

        if ($companyId) {
            $builder->where('company_id', $companyId);
        }

        $row = $builder->get()->getRowArray() ?: [];

        return [
            'Current'   => (float) ($row['current_due'] ?? 0),
            '1–30 days' => (float) ($row['d1_30'] ?? 0),
            '31–60 days'=> (float) ($row['d31_60'] ?? 0),
            '61–90 days'=> (float) ($row['d61_90'] ?? 0),
            '90+ days'  => (float) ($row['d90_plus'] ?? 0),
        ];
    }

    private function tableHasCompany(string $table): bool
    {
        return in_array($table, [
            'quotations', 'sales_orders', 'invoices', 'deliveries',
            'sales_returns', 'refunds', 'opportunities',
        ], true);
    }

    private function kpi(string $label, string $icon, string $value, string $badge, string $badgeType, string $dept): array
    {
        return compact('label', 'icon', 'value', 'badge', 'badgeType', 'dept');
    }

    private function money(float $amount): string
    {
        return number_format($amount, 2);
    }

    private function nullableInt(mixed $value): ?int
    {
        if ($value === null || $value === '') {
            return null;
        }

        return (int) $value ?: null;
    }

    private function isValidDate(string $date): bool
    {
        $dt = \DateTime::createFromFormat('Y-m-d', $date);

        return $dt && $dt->format('Y-m-d') === $date;
    }
}
