<?php

namespace Modules\ManagementTools\Services;

use Carbon\Carbon;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Route;
use Illuminate\Support\Facades\Schema;
use Modules\ManagementTools\Entities\MtExecutiveNote;
use Modules\ManagementTools\Entities\MtExecutiveTarget;
use Modules\ManagementTools\Services\ExecutiveCockpit\CockpitMetricsCache;
use Modules\ManagementTools\Services\ExecutiveCockpit\HoldingComparisonService;
use Modules\ManagementTools\Services\ExecutiveCockpit\ModuleStatusResolver;
use Modules\ManagementTools\Services\ExecutiveCockpit\TransactionMetricsQuery;

class ExecutiveCockpitService
{
    protected ?array $requestKpiCache = null;

    public function __construct(
        protected ModuleStatusResolver $moduleStatusResolver,
        protected CockpitMetricsCache $metricsCache,
        protected HoldingComparisonService $holdingComparisonService,
        protected TransactionMetricsQuery $transactionMetrics
    ) {}

    public function build(int $businessId, Carbon $startDate, Carbon $endDate, ?string $onlyDrillTab = null): array
    {
        $this->requestKpiCache = [];
        $kpis = $this->cachedKpis($businessId, $startDate, $endDate);
        $alerts = $this->alerts($businessId);
        $targets = $this->targets($businessId);
        $moduleCockpit = $this->moduleCockpit($businessId);
        $systemHealth = $this->systemHealth($alerts);
        [$prevStartDate, $prevEndDate] = $this->previousPeriodRange($startDate, $endDate);
        $prevKpis = $this->cachedKpis($businessId, $prevStartDate, $prevEndDate);
        $drilldown = $this->drilldown($businessId, $startDate, $endDate, $kpis, $prevKpis, $prevStartDate, $prevEndDate, $onlyDrillTab);

        $payload = [
            'range' => ['start' => $startDate->copy(), 'end' => $endDate->copy()],
            'previous_range' => ['start' => $prevStartDate->copy(), 'end' => $prevEndDate->copy()],
            'kpis' => $kpis,
            'alerts' => $alerts,
            'focus_items' => $this->focusItems($businessId),
            'trends' => $this->trends($businessId, $startDate, $endDate),
            'risk_score' => $this->riskScore($kpis, $alerts),
            'action_center' => $this->actionCenter($businessId, $kpis, $alerts),
            'targets' => $targets,
            'variance' => $this->variance($kpis, $targets),
            'notes' => $this->latestNotes($businessId),
            'module_cockpit' => $moduleCockpit,
            'system_health' => $systemHealth,
            'summary' => [
                'total_modules' => count($moduleCockpit),
                'enabled_modules' => count(array_filter($moduleCockpit, fn ($m) => $m['enabled'])),
                'critical_alerts' => count($alerts),
                'system_score' => $systemHealth['score'],
            ],
            'drilldown' => $this->filterDrilldownByEnabledModules($drilldown),
            'drilldown_labels' => $this->drilldownTabLabels(),
            'trend_month_labels' => $this->monthlyTrendLabels($endDate),
            'kpi_comparisons' => $this->kpiPeriodComparisons($kpis, $prevKpis),
            'holding_comparison' => $this->holdingEntityComparison($businessId, $startDate, $endDate),
        ];

        $payload['executive_summary'] = $this->executiveSummary($payload);

        return $payload;
    }

    protected function executiveSummary(array $payload): array
    {
        $comparisons = $payload['kpi_comparisons'] ?? [];
        $alerts = $payload['alerts'] ?? [];
        $variance = $payload['variance'] ?? [];
        $riskScore = (int) ($payload['risk_score'] ?? 0);
        $systemHealth = $payload['system_health'] ?? [];
        $range = $payload['range'] ?? [];
        $prevRange = $payload['previous_range'] ?? [];

        $highlights = [];
        $concerns = [];
        $watchItems = [];

        foreach (['sales_this_month', 'gross_margin_estimate', 'workforce_productivity'] as $metric) {
            if (! isset($comparisons[$metric])) {
                continue;
            }

            $comparison = $comparisons[$metric];
            if (($comparison['direction'] ?? 'flat') === 'flat') {
                continue;
            }

            $label = __('managementtools::lang.'.$metric);
            $pct = abs((float) ($comparison['delta_pct'] ?? 0));

            if (! empty($comparison['favorable'])) {
                $highlights[] = __('managementtools::lang.summary_metric_improved', ['metric' => $label, 'pct' => $pct]);
            } else {
                $concerns[] = __('managementtools::lang.summary_metric_declined', ['metric' => $label, 'pct' => $pct]);
            }
        }

        if ($riskScore >= 75) {
            $highlights[] = __('managementtools::lang.summary_risk_stable', ['score' => $riskScore]);
        } elseif ($riskScore < 55) {
            $concerns[] = __('managementtools::lang.summary_risk_critical', ['score' => $riskScore]);
        } else {
            $watchItems[] = __('managementtools::lang.summary_risk_watch', ['score' => $riskScore]);
        }

        if (count($alerts) > 0) {
            $concerns[] = __('managementtools::lang.summary_alerts_count', ['count' => count($alerts)]);
        } else {
            $highlights[] = __('managementtools::lang.summary_no_critical_alerts');
        }

        foreach (['sales_this_month', 'gross_margin_estimate', 'workforce_productivity'] as $metric) {
            if (! isset($variance[$metric]) || ($variance[$metric]['delta'] ?? 0) >= 0) {
                continue;
            }

            $watchItems[] = __('managementtools::lang.summary_below_target', [
                'metric' => __('managementtools::lang.'.$metric),
                'pct' => abs((float) ($variance[$metric]['delta_pct'] ?? 0)),
            ]);
        }

        $periodStart = $range['start'] ?? Carbon::today();
        $periodEnd = $range['end'] ?? Carbon::today();
        $prevStart = $prevRange['start'] ?? null;
        $prevEnd = $prevRange['end'] ?? null;

        return [
            'headline' => $this->executiveHeadline($highlights, $concerns, $riskScore),
            'opening' => __('managementtools::lang.summary_opening', [
                'start' => $periodStart->format('Y-m-d'),
                'end' => $periodEnd->format('Y-m-d'),
                'health' => $systemHealth['score'] ?? 0,
            ]),
            'highlights' => $highlights,
            'concerns' => $concerns,
            'watch_items' => $watchItems,
            'period_label' => $periodStart->format('Y-m-d').' — '.$periodEnd->format('Y-m-d'),
            'previous_period_label' => ($prevStart && $prevEnd)
                ? $prevStart->format('Y-m-d').' — '.$prevEnd->format('Y-m-d')
                : '',
        ];
    }

    protected function executiveHeadline(array $highlights, array $concerns, int $riskScore): string
    {
        if (count($concerns) >= 2 || $riskScore < 55) {
            return __('managementtools::lang.summary_headline_action_required');
        }

        if (count($highlights) >= 2 && count($concerns) === 0) {
            return __('managementtools::lang.summary_headline_strong_performance');
        }

        return __('managementtools::lang.summary_headline_mixed');
    }

    protected function previousPeriodRange(Carbon $startDate, Carbon $endDate): array
    {
        $days = $startDate->diffInDays($endDate) + 1;
        $prevEnd = $startDate->copy()->subDay()->endOfDay();
        $prevStart = $prevEnd->copy()->subDays($days - 1)->startOfDay();

        return [$prevStart, $prevEnd];
    }

    protected function periodComparison(float $current, float $previous, bool $lowerIsBetter = false): array
    {
        $delta = $current - $previous;
        $deltaPct = $previous != 0
            ? round(($delta / abs($previous)) * 100, 1)
            : ($current != 0 ? 100.0 : 0.0);
        $direction = abs($delta) < 0.0001 ? 'flat' : ($delta > 0 ? 'up' : 'down');

        return [
            'previous' => $previous,
            'delta' => $delta,
            'delta_pct' => $deltaPct,
            'direction' => $direction,
            'favorable' => $lowerIsBetter ? ($delta <= 0) : ($delta >= 0),
        ];
    }

    protected function enrichKpiWithComparison(array $kpi, ?array $comparison): array
    {
        if ($comparison !== null) {
            $kpi['comparison'] = $comparison;
            if (! empty($kpi['currency'])) {
                $kpi['comparison']['currency'] = true;
            }
        }

        return $kpi;
    }

    protected function kpiPeriodComparisons(array $kpis, array $prevKpis): array
    {
        $map = [
            'sales_this_month' => false,
            'gross_margin_estimate' => false,
            'purchase_this_month' => true,
            'expense_this_month' => true,
            'workforce_productivity' => false,
            'mt_reports_submitted' => false,
            'open_work_orders' => true,
            'open_tickets' => true,
            'active_projects' => true,
        ];

        $comparisons = [];
        foreach ($map as $metric => $lowerIsBetter) {
            $current = (float) ($kpis[$metric] ?? 0);
            $previous = (float) ($prevKpis[$metric] ?? 0);
            $comparisons[$metric] = $this->periodComparison($current, $previous, $lowerIsBetter);
        }

        return $comparisons;
    }

    protected function drilldownPeriodMetrics(int $businessId, Carbon $startDate, Carbon $endDate): array
    {
        $metrics = [
            'sales_count' => 0,
            'average_sale' => 0,
            'projects_completed_in_period' => 0,
            'crm_new_leads' => 0,
            'production_count' => 0,
            'production_value' => 0,
            'journal_entry_count' => 0,
            'ie_mrp_runs' => 0,
        ];

        if (Schema::hasTable('transactions')) {
            $salesCount = (int) DB::table('transactions')
                ->where('business_id', $businessId)
                ->where('type', 'sell')
                ->where('status', 'final')
                ->whereBetween(DB::raw('date(transaction_date)'), [$startDate->toDateString(), $endDate->toDateString()])
                ->count();

            $salesTotal = (float) DB::table('transactions')
                ->where('business_id', $businessId)
                ->where('type', 'sell')
                ->where('status', 'final')
                ->whereBetween(DB::raw('date(transaction_date)'), [$startDate->toDateString(), $endDate->toDateString()])
                ->sum('final_total');

            $metrics['sales_count'] = $salesCount;
            $metrics['average_sale'] = $salesCount > 0 ? round($salesTotal / $salesCount, 2) : 0;
        }

        if (Schema::hasTable('pp_projects')) {
            $metrics['projects_completed_in_period'] = (int) DB::table('pp_projects')
                ->where('business_id', $businessId)
                ->where('status', 'completed')
                ->whereBetween(DB::raw('date(updated_at)'), [$startDate->toDateString(), $endDate->toDateString()])
                ->count();
        }

        if (Schema::hasTable('contacts')) {
            $metrics['crm_new_leads'] = (int) DB::table('contacts')
                ->where('business_id', $businessId)
                ->where('type', 'lead')
                ->whereBetween(DB::raw('date(created_at)'), [$startDate->toDateString(), $endDate->toDateString()])
                ->count();
        }

        if (Schema::hasTable('transactions')) {
            $metrics['production_count'] = (int) DB::table('transactions')
                ->where('business_id', $businessId)
                ->where('type', 'production_purchase')
                ->whereBetween(DB::raw('date(transaction_date)'), [$startDate->toDateString(), $endDate->toDateString()])
                ->count();

            $metrics['production_value'] = (float) DB::table('transactions')
                ->where('business_id', $businessId)
                ->where('type', 'production_purchase')
                ->whereBetween(DB::raw('date(transaction_date)'), [$startDate->toDateString(), $endDate->toDateString()])
                ->sum('final_total');
        }

        if (Schema::hasTable('accounting_accounts_transactions') && Schema::hasTable('accounting_accounts')) {
            $dateColumn = Schema::hasColumn('accounting_accounts_transactions', 'operation_date') ? 'operation_date' : 'created_at';
            $metrics['journal_entry_count'] = (int) DB::table('accounting_accounts_transactions as aat')
                ->join('accounting_accounts as aa', 'aa.id', '=', 'aat.accounting_account_id')
                ->where('aa.business_id', $businessId)
                ->whereBetween(DB::raw('date(aat.'.$dateColumn.')'), [$startDate->toDateString(), $endDate->toDateString()])
                ->count();
        }

        if (Schema::hasTable('ie_mrp_runs')) {
            $metrics['ie_mrp_runs'] = (int) DB::table('ie_mrp_runs')
                ->where('business_id', $businessId)
                ->whereBetween(DB::raw('date(created_at)'), [$startDate->toDateString(), $endDate->toDateString()])
                ->count();
        }

        return $metrics;
    }

    public function buildForViewer(int $businessId, Carbon $startDate, Carbon $endDate, $user = null, bool $lazyDrillTabs = false, ?string $activeTab = null): array
    {
        $onlyTab = ($lazyDrillTabs && $activeTab) ? $activeTab : null;
        $data = $this->build($businessId, $startDate, $endDate, $onlyTab);

        if ($user) {
            $data['drilldown'] = $this->filterDrilldownForUser($data['drilldown'], $user);
            $data['focus_items'] = $this->filterFocusItemsForUser($data['focus_items'], $user);
        }

        return $data;
    }

    public function buildDrilldownTabForViewer(int $businessId, Carbon $startDate, Carbon $endDate, string $tabKey, $user = null): ?array
    {
        $labels = $this->drilldownTabLabels();
        if (! array_key_exists($tabKey, $labels)) {
            return null;
        }

        [$prevStartDate, $prevEndDate] = $this->previousPeriodRange($startDate, $endDate);
        $kpis = $this->cachedKpis($businessId, $startDate, $endDate);
        $prevKpis = $this->cachedKpis($businessId, $prevStartDate, $prevEndDate);
        $sections = $this->filterDrilldownByEnabledModules(
            $this->drilldown($businessId, $startDate, $endDate, $kpis, $prevKpis, $prevStartDate, $prevEndDate, $tabKey)
        );

        if (! isset($sections[$tabKey])) {
            return null;
        }

        $section = $sections[$tabKey];
        if ($user) {
            $section['top_items'] = array_values(array_filter(array_map(function ($item) use ($user) {
                return $this->filterPermissionedItem($item, $user);
            }, $section['top_items'] ?? [])));
        }

        return [
            'tab_key' => $tabKey,
            'section' => $section,
            'trend_month_labels' => $this->monthlyTrendLabels($endDate),
            'drilldown_labels' => $labels,
        ];
    }

    protected function drilldownTabLabels(): array
    {
        $labels = [
            'finance' => __('managementtools::lang.drilldown_finance'),
            'sales' => __('managementtools::lang.drilldown_sales'),
            'maintenance' => __('managementtools::lang.drilldown_maintenance'),
            'projects' => __('managementtools::lang.drilldown_projects'),
            'workforce' => __('managementtools::lang.drilldown_workforce'),
        ];

        if ($this->isModuleEnabled('Crm')) {
            $labels['crm'] = __('managementtools::lang.drilldown_crm');
        }
        if ($this->isModuleEnabled('Accounting')) {
            $labels['accounting'] = __('managementtools::lang.drilldown_accounting');
        }
        if ($this->isModuleEnabled('Manufacturing')) {
            $labels['manufacturing'] = __('managementtools::lang.drilldown_manufacturing');
        }
        if ($this->isModuleEnabled('IndustrialEngineering')) {
            $labels['ie'] = __('managementtools::lang.drilldown_ie');
        }
        if ($this->isModuleEnabled('QualityManagement')) {
            $labels['quality'] = __('managementtools::lang.drilldown_quality');
        }
        if ($this->isModuleEnabled('ShopFloor')) {
            $labels['shopfloor'] = __('managementtools::lang.drilldown_shopfloor');
        }
        if ($this->isModuleEnabled('SupplyChain')) {
            $labels['supplychain'] = __('managementtools::lang.drilldown_supplychain');
        }
        if ($this->isModuleEnabled('AdvancedPlanning')) {
            $labels['planning'] = __('managementtools::lang.drilldown_planning');
        }

        return $labels;
    }

    protected function moduleStatuses(): array
    {
        return $this->moduleStatusResolver->all();
    }

    protected function isModuleEnabled(string $moduleKey): bool
    {
        return $this->moduleStatusResolver->isEnabled($moduleKey);
    }

    protected function cachedKpis(int $businessId, Carbon $startDate, Carbon $endDate): array
    {
        $key = $startDate->toDateString().':'.$endDate->toDateString();

        if (is_array($this->requestKpiCache) && array_key_exists($key, $this->requestKpiCache)) {
            return $this->requestKpiCache[$key];
        }

        $kpis = $this->metricsCache->rememberKpis(
            $businessId,
            $startDate,
            $endDate,
            fn () => $this->kpis($businessId, $startDate, $endDate)
        );

        if (is_array($this->requestKpiCache)) {
            $this->requestKpiCache[$key] = $kpis;
        }

        return $kpis;
    }

    protected function filterDrilldownByEnabledModules(array $drilldown): array
    {
        $moduleMap = [
            'crm' => 'Crm',
            'accounting' => 'Accounting',
            'manufacturing' => 'Manufacturing',
            'ie' => 'IndustrialEngineering',
            'quality' => 'QualityManagement',
            'shopfloor' => 'ShopFloor',
            'supplychain' => 'SupplyChain',
            'planning' => 'AdvancedPlanning',
        ];

        foreach ($moduleMap as $tab => $moduleKey) {
            if (! $this->isModuleEnabled($moduleKey)) {
                unset($drilldown[$tab]);
            }
        }

        return $drilldown;
    }

    protected function monthlyTrendLabels(Carbon $endDate): array
    {
        $labels = [];
        for ($i = 5; $i >= 0; $i--) {
            $labels[] = $endDate->copy()->startOfMonth()->subMonths($i)->format('Y/m');
        }

        return $labels;
    }

    public function filterDrilldownForUser(array $drilldown, $user): array
    {
        foreach ($drilldown as $key => $section) {
            $drilldown[$key]['top_items'] = array_values(array_filter(array_map(function ($item) use ($user) {
                return $this->filterPermissionedItem($item, $user);
            }, $section['top_items'] ?? [])));
        }

        return $drilldown;
    }

    public function filterFocusItemsForUser(array $items, $user): array
    {
        return array_values(array_filter(array_map(function ($item) use ($user) {
            return $this->filterPermissionedItem($item, $user);
        }, $items)));
    }

    protected function filterPermissionedItem(array $item, $user): ?array
    {
        if (! $this->userCanAny($user, $item['permissions'] ?? [])) {
            return null;
        }

        $item['actions'] = array_values(array_filter($item['actions'] ?? [], function ($action) use ($user) {
            return $this->userCanAny($user, $action['permissions'] ?? []);
        }));

        return $item;
    }

    protected function userCanAny($user, array $permissions): bool
    {
        if (empty($permissions)) {
            return true;
        }

        if (! $user) {
            return false;
        }

        foreach ($permissions as $permission) {
            if ($user->can($permission)) {
                return true;
            }
        }

        return false;
    }

    protected function contactViewPermissions(): array
    {
        return ['customer.view', 'customer.view_own', 'supplier.view', 'supplier.view_own'];
    }

    protected function contactActions(int $contactId): array
    {
        $contactUrl = $contactId > 0 ? url('/contacts/'.$contactId) : url('/contacts/customers');
        $paymentsUrl = $contactId > 0 ? url('/contacts/payments/'.$contactId) : url('/contacts/customers');
        $permissions = $this->contactViewPermissions();

        return [
            'url' => $contactUrl,
            'permissions' => $permissions,
            'actions' => [
                [
                    'label' => __('managementtools::lang.view_details'),
                    'url' => $contactUrl,
                    'permissions' => $permissions,
                ],
                [
                    'label' => __('managementtools::lang.open_payments'),
                    'url' => $paymentsUrl,
                    'permissions' => $permissions,
                ],
            ],
        ];
    }

    protected function kpis(int $businessId, Carbon $startDate, Carbon $endDate): array
    {
        $sales = 0;
        $purchase = 0;
        $expense = 0;
        $customerDue = 0;
        $supplierDue = 0;

        if (Schema::hasTable('transactions')) {
            $sales = $this->transactionMetrics->sumFinalSells($businessId, $startDate, $endDate);

            $purchase = (float) DB::table('transactions')
                ->where('business_id', $businessId)
                ->where('type', 'purchase')
                ->where('transaction_date', '>=', $startDate->copy()->startOfDay())
                ->where('transaction_date', '<=', $endDate->copy()->endOfDay())
                ->sum('final_total');

            $expense = (float) DB::table('transactions')
                ->where('business_id', $businessId)
                ->where('type', 'expense')
                ->where('transaction_date', '>=', $startDate->copy()->startOfDay())
                ->where('transaction_date', '<=', $endDate->copy()->endOfDay())
                ->sum('final_total');
        }
        
        if (Schema::hasTable('contacts')) {
            $customerDue = (float) DB::table('contacts')
                ->where('business_id', $businessId)
                ->whereIn('type', ['customer', 'both'])
                ->where('balance', '>', 0)
                ->sum('balance');

            $supplierDue = (float) DB::table('contacts')
                ->where('business_id', $businessId)
                ->whereIn('type', ['supplier', 'both'])
                ->where('balance', '>', 0)
                ->sum('balance');
        }

        $openTickets = 0;
        if (Schema::hasTable('mt_user_tickets')) {
            $openTickets = (int) DB::table('mt_user_tickets')
                ->where('business_id', $businessId)
                ->whereIn('status', [0, 1])
                ->count();
        }

        $openWorkOrders = 0;
        if (Schema::hasTable('maintenance_work_orders')) {
            $openWorkOrders = (int) DB::table('maintenance_work_orders')
                ->where('business_id', $businessId)
                ->whereIn('status', ['draft', 'open', 'in_progress', 'on_hold'])
                ->count();
        }

        $activeProjects = 0;
        if (Schema::hasTable('pp_projects')) {
            $activeProjects = (int) DB::table('pp_projects')
                ->where('business_id', $businessId)
                ->whereIn('status', ['not_started', 'in_progress', 'on_hold'])
                ->where('is_archived', false)
                ->count();
        }

        $overdueInvoices = 0;
        if (Schema::hasTable('transactions') && Schema::hasColumn('transactions', 'due_date')) {
            $overdueInvoices = (int) DB::table('transactions')
                ->where('business_id', $businessId)
                ->where('type', 'sell')
                ->where('status', 'final')
                ->where('payment_status', '!=', 'paid')
                ->whereNotNull('due_date')
                ->whereDate('due_date', '<', Carbon::today()->toDateString())
                ->count();
        }

        return [
            'sales_this_month' => $sales,
            'purchase_this_month' => $purchase,
            'expense_this_month' => $expense,
            'gross_margin_estimate' => $sales - $purchase - $expense,
            'customer_due' => $customerDue,
            'supplier_due' => $supplierDue,
            'open_tickets' => $openTickets,
            'open_work_orders' => $openWorkOrders,
            'active_projects' => $activeProjects,
            'overdue_invoices' => $overdueInvoices,
            'mt_reports_submitted' => $this->submittedReportsCount($businessId, $startDate, $endDate),
            'workforce_productivity' => $this->workforceProductivity($businessId, $startDate, $endDate),
            'open_ncrs' => $this->openNcrCount($businessId),
            'avg_oee_30d' => $this->avgOeeLast30Days($businessId),
            'open_iot_alerts' => $this->openIotAlertCount($businessId),
        ];
    }

    protected function openNcrCount(int $businessId): int
    {
        if (! Schema::hasTable('qms_ncr_reports')) {
            return 0;
        }

        return (int) DB::table('qms_ncr_reports')
            ->where('business_id', $businessId)
            ->whereIn('status', ['open', 'investigating', 'containment'])
            ->count();
    }

    protected function avgOeeLast30Days(int $businessId): float
    {
        if (! Schema::hasTable('sf_oee_snapshots')) {
            return 0;
        }

        return round((float) DB::table('sf_oee_snapshots')
            ->where('business_id', $businessId)
            ->where('snapshot_date', '>=', now()->subDays(30)->toDateString())
            ->avg('oee_pct'), 2);
    }

    protected function openIotAlertCount(int $businessId): int
    {
        if (! Schema::hasTable('mt_iot_alerts')) {
            return 0;
        }

        return (int) DB::table('mt_iot_alerts')
            ->where('business_id', $businessId)
            ->where('status', 'open')
            ->count();
    }

    protected function submittedReportsCount(int $businessId, Carbon $startDate, Carbon $endDate): int
    {
        if (! Schema::hasTable('mt_daily_reports')) {
            return 0;
        }

        return (int) DB::table('mt_daily_reports')
            ->where('business_id', $businessId)
            ->whereBetween(DB::raw('date(report_date)'), [$startDate->toDateString(), $endDate->toDateString()])
            ->count();
    }

    protected function workforceProductivity(int $businessId, Carbon $startDate, Carbon $endDate): float
    {
        if (! Schema::hasTable('mt_user_tasks')) {
            return 0;
        }

        $total = (int) DB::table('mt_user_tasks')
            ->where('business_id', $businessId)
            ->whereBetween(DB::raw('date(created_at)'), [$startDate->toDateString(), $endDate->toDateString()])
            ->count();

        if ($total === 0) {
            return 0;
        }

        $completed = (int) DB::table('mt_user_tasks')
            ->where('business_id', $businessId)
            ->whereBetween(DB::raw('date(created_at)'), [$startDate->toDateString(), $endDate->toDateString()])
            ->where('task_status', 1)
            ->count();

        return round(($completed / $total) * 100, 2);
    }

    protected function alerts(int $businessId): array
    {
        $items = [];

        if (Schema::hasTable('transactions') && Schema::hasColumn('transactions', 'due_date')) {
            $overdueInvoices = (int) DB::table('transactions')
                ->where('business_id', $businessId)
                ->where('type', 'sell')
                ->where('status', 'final')
                ->where('payment_status', '!=', 'paid')
                ->whereNotNull('due_date')
                ->whereDate('due_date', '<', Carbon::today()->toDateString())
                ->count();

            if ($overdueInvoices > 0) {
                $items[] = [
                    'level' => 'danger',
                    'title' => __('managementtools::lang.alert_overdue_invoices'),
                    'value' => $overdueInvoices,
                    'url' => url('/reports/sell-payment-report'),
                ];
            }
        }

        if (Schema::hasTable('maintenance_preventive_schedules')) {
            $upcomingPm = (int) DB::table('maintenance_preventive_schedules')
                ->where('business_id', $businessId)
                ->where('is_active', true)
                ->whereNotNull('next_due_at')
                ->whereDate('next_due_at', '<=', Carbon::today()->addDays(7)->toDateString())
                ->count();

            if ($upcomingPm > 0) {
                $items[] = [
                    'level' => 'warning',
                    'title' => __('managementtools::lang.alert_pm_due'),
                    'value' => $upcomingPm,
                    'url' => url('/maintenance/dashboard'),
                ];
            }
        }

        if (Schema::hasTable('mt_user_tickets')) {
            $urgentTickets = (int) DB::table('mt_user_tickets')
                ->where('business_id', $businessId)
                ->where('priority', 'urgent')
                ->whereIn('status', [0, 1])
                ->count();

            if ($urgentTickets > 0) {
                $items[] = [
                    'level' => 'danger',
                    'title' => __('managementtools::lang.alert_urgent_tickets'),
                    'value' => $urgentTickets,
                    'url' => url('/management-tools/tickets'),
                ];
            }
        }

        if (Schema::hasTable('accounting_installment_schedule') && Schema::hasTable('accounting_installment_plans')) {
            $overdueInstallments = (int) DB::table('accounting_installment_schedule as s')
                ->join('accounting_installment_plans as p', 'p.id', '=', 's.installment_plan_id')
                ->where('p.business_id', $businessId)
                ->where('s.status', 'overdue')
                ->count();

            if ($overdueInstallments > 0) {
                $overdueAmount = (float) DB::table('accounting_installment_schedule as s')
                    ->join('accounting_installment_plans as p', 'p.id', '=', 's.installment_plan_id')
                    ->where('p.business_id', $businessId)
                    ->where('s.status', 'overdue')
                    ->selectRaw('SUM(s.amount - COALESCE(s.paid_amount, 0)) as remaining')
                    ->value('remaining');

                $items[] = [
                    'level' => 'danger',
                    'title' => __('managementtools::lang.alert_overdue_installments'),
                    'value' => $overdueInstallments,
                    'subtitle' => __('managementtools::lang.alert_overdue_installments_amount', [
                        'amount' => number_format($overdueAmount, 2),
                    ]),
                    'url' => $this->routeIfExists('accounting.installments'),
                ];
            }
        }

        if (Schema::hasTable('accounting_cheques')) {
            $pendingCheques = (int) DB::table('accounting_cheques')
                ->where('business_id', $businessId)
                ->whereIn('status', ['pending', 'deposited'])
                ->count();

            if ($pendingCheques > 0) {
                $items[] = [
                    'level' => 'warning',
                    'title' => __('managementtools::lang.alert_pending_cheques'),
                    'value' => $pendingCheques,
                    'url' => $this->routeIfExists('accounting.cheques'),
                ];
            }
        }

        if (class_exists(\Modules\Accounting\Services\DeferredDocumentsService::class)) {
            $deferredSummary = app(\Modules\Accounting\Services\DeferredDocumentsService::class)->summary($businessId);
            if ($deferredSummary['total'] > 0) {
                $items[] = [
                    'level' => 'danger',
                    'title' => __('managementtools::lang.alert_deferred_documents'),
                    'value' => $deferredSummary['total'],
                    'subtitle' => __('managementtools::lang.alert_deferred_documents_breakdown', [
                        'cheques' => $deferredSummary['cheques'],
                        'installments' => $deferredSummary['installments'],
                        'invoices' => $deferredSummary['invoices'],
                        'journals' => $deferredSummary['journals'],
                    ]),
                    'url' => $this->routeIfExists('accounting.deferredDocuments'),
                ];
            }
        }

        if (class_exists(\Modules\Accounting\Services\ExchangeRateService::class) && Schema::hasTable('accounting_exchange_rates')) {
            $fxSummary = app(\Modules\Accounting\Services\ExchangeRateService::class)->staleRateSummary($businessId, 2);

            if (($fxSummary['total_issues'] ?? (($fxSummary['stale_count'] ?? 0) + ($fxSummary['missing_count'] ?? 0))) > 0) {
                $items[] = [
                    'level' => 'warning',
                    'title' => __('managementtools::lang.alert_stale_exchange_rates'),
                    'value' => $fxSummary['total_issues'] ?? (($fxSummary['stale_count'] ?? 0) + ($fxSummary['missing_count'] ?? 0)),
                    'subtitle' => __('managementtools::lang.alert_stale_exchange_rates_hint', [
                        'stale' => $fxSummary['stale_count'] ?? 0,
                        'missing' => $fxSummary['missing_count'] ?? 0,
                        'pairs' => implode(', ', array_slice($fxSummary['pairs'] ?? [], 0, 4)),
                    ]),
                    'url' => $this->routeIfExists('accounting.exchangeRates'),
                ];
            }
        }

        if (class_exists(\Modules\Accounting\Services\PaymentAccountGlLinkService::class) && Schema::hasColumn('accounts', 'accounting_account_id')) {
            $paymentGl = app(\Modules\Accounting\Services\PaymentAccountGlLinkService::class)->summary($businessId);
            if (($paymentGl['unlinked'] ?? 0) > 0) {
                $items[] = [
                    'level' => 'warning',
                    'title' => __('managementtools::lang.alert_unlinked_payment_accounts'),
                    'value' => $paymentGl['unlinked'],
                    'subtitle' => __('managementtools::lang.alert_unlinked_payment_accounts_hint', [
                        'linked' => $paymentGl['linked'],
                        'total' => $paymentGl['total'],
                    ]),
                    'url' => url('/accounting/settings').'#payment_gl_tab',
                ];
            }
        }

        if (Schema::hasTable('qms_ncr_reports')) {
            $openNcrs = (int) DB::table('qms_ncr_reports')
                ->where('business_id', $businessId)
                ->whereIn('status', ['open', 'investigating', 'containment'])
                ->count();
            if ($openNcrs > 0) {
                $items[] = [
                    'level' => 'warning',
                    'title' => __('managementtools::lang.alert_open_ncrs'),
                    'value' => $openNcrs,
                    'url' => url('/qualitymanagement/ncr'),
                ];
            }
        }

        if (Schema::hasTable('mt_iot_alerts')) {
            $iotAlerts = (int) DB::table('mt_iot_alerts')
                ->where('business_id', $businessId)
                ->where('status', 'open')
                ->count();
            if ($iotAlerts > 0) {
                $items[] = [
                    'level' => 'danger',
                    'title' => __('managementtools::lang.alert_iot_predictive'),
                    'value' => $iotAlerts,
                    'url' => url('/maintenance/iot'),
                ];
            }
        }

        return $items;
    }

    protected function focusItems(int $businessId): array
    {
        $items = [];

        if (Schema::hasTable('contacts')) {
            $topDebtors = DB::table('contacts')
                ->where('business_id', $businessId)
                ->whereIn('type', ['customer', 'both'])
                ->where('balance', '>', 0)
                ->orderByDesc('balance')
                ->limit(5)
                ->get(['id', 'name', 'balance']);

            foreach ($topDebtors as $debtor) {
                $contactMeta = $this->contactActions((int) $debtor->id);
                $items[] = array_merge([
                    'type' => 'customer_due',
                    'label' => (string) ($debtor->name ?: '-'),
                    'amount' => (float) $debtor->balance,
                    'currency' => true,
                ], $contactMeta);
            }
        }

        return $items;
    }

    protected function trends(int $businessId, Carbon $startDate, Carbon $endDate): array
    {
        $labels = [];
        $salesSeries = [];
        $purchaseSeries = [];

        $salesByDate = [];
        $purchaseByDate = [];

        if (Schema::hasTable('transactions')) {
            $salesRows = DB::table('transactions')
                ->selectRaw('DATE(transaction_date) as d, SUM(final_total) as total')
                ->where('business_id', $businessId)
                ->where('type', 'sell')
                ->where('status', 'final')
                ->whereBetween(DB::raw('date(transaction_date)'), [$startDate->toDateString(), $endDate->toDateString()])
                ->groupBy('d')
                ->pluck('total', 'd')
                ->toArray();

            $purchaseRows = DB::table('transactions')
                ->selectRaw('DATE(transaction_date) as d, SUM(final_total) as total')
                ->where('business_id', $businessId)
                ->where('type', 'purchase')
                ->whereBetween(DB::raw('date(transaction_date)'), [$startDate->toDateString(), $endDate->toDateString()])
                ->groupBy('d')
                ->pluck('total', 'd')
                ->toArray();

            $salesByDate = array_map('floatval', $salesRows);
            $purchaseByDate = array_map('floatval', $purchaseRows);
        }

        $cursor = $startDate->copy()->startOfDay();
        while ($cursor->lte($endDate)) {
            $key = $cursor->toDateString();
            $labels[] = $cursor->format('m/d');
            $salesSeries[] = $salesByDate[$key] ?? 0;
            $purchaseSeries[] = $purchaseByDate[$key] ?? 0;
            $cursor->addDay();
        }

        return [
            'labels' => $labels,
            'sales' => $salesSeries,
            'purchase' => $purchaseSeries,
        ];
    }

    protected function riskScore(array $kpis, array $alerts): int
    {
        $score = 100;

        $score -= min(40, count($alerts) * 12);
        $score -= min(20, (int) (($kpis['overdue_invoices'] ?? 0) * 1.5));
        $score -= min(20, max(0, 70 - (int) ($kpis['workforce_productivity'] ?? 0)));
        $score -= min(20, ((int) ($kpis['open_tickets'] ?? 0) > 15) ? 20 : (int) ($kpis['open_tickets'] ?? 0));

        return max(0, $score);
    }

    protected function actionCenter(int $businessId, array $kpis, array $alerts): array
    {
        $actions = [];

        if (($kpis['overdue_invoices'] ?? 0) > 0) {
            $actions[] = [
                'priority' => 'high',
                'title' => __('managementtools::lang.action_collect_overdue'),
                'description' => __('managementtools::lang.action_collect_overdue_hint'),
                'url' => url('/reports/sell-payment-report'),
                'drill_tab' => 'finance',
            ];
        }

        if (($kpis['open_work_orders'] ?? 0) > 10) {
            $actions[] = [
                'priority' => 'medium',
                'title' => __('managementtools::lang.action_stabilize_maintenance'),
                'description' => __('managementtools::lang.action_stabilize_maintenance_hint'),
                'url' => url('/maintenance/work-orders'),
                'drill_tab' => 'maintenance',
            ];
        }

        if (($kpis['workforce_productivity'] ?? 0) < 65) {
            $actions[] = [
                'priority' => 'medium',
                'title' => __('managementtools::lang.action_productivity_review'),
                'description' => __('managementtools::lang.action_productivity_review_hint'),
                'url' => url('/management-tools/daily-reports'),
                'drill_tab' => 'workforce',
            ];
        }

        if (class_exists(\Modules\Accounting\Services\ExchangeRateService::class) && Schema::hasTable('accounting_exchange_rates')) {
            $fxSummary = app(\Modules\Accounting\Services\ExchangeRateService::class)->staleRateSummary($businessId, 2);
            if (($fxSummary['total_issues'] ?? 0) > 0) {
                $actions[] = [
                    'priority' => 'medium',
                    'title' => __('managementtools::lang.action_refresh_exchange_rates'),
                    'description' => __('managementtools::lang.action_refresh_exchange_rates_hint'),
                    'url' => $this->routeIfExists('accounting.exchangeRates') ?: url('/accounting/exchange-rates'),
                    'drill_tab' => 'finance',
                ];
            }
        }

        if (empty($actions) && empty($alerts)) {
            $actions[] = [
                'priority' => 'low',
                'title' => __('managementtools::lang.action_keep_monitoring'),
                'description' => __('managementtools::lang.action_keep_monitoring_hint'),
                'url' => url('/management-tools/executive-cockpit'),
                'drill_tab' => 'finance',
            ];
        }

        return $actions;
    }

    protected function targets(int $businessId): array
    {
        if (! Schema::hasTable('mt_executive_targets')) {
            return [];
        }

        $today = Carbon::today()->toDateString();
        $rows = MtExecutiveTarget::query()
            ->where('business_id', $businessId)
            ->where(function ($q) use ($today) {
                $q->whereNull('effective_from')->orWhere('effective_from', '<=', $today);
            })
            ->where(function ($q) use ($today) {
                $q->whereNull('effective_to')->orWhere('effective_to', '>=', $today);
            })
            ->orderByDesc('effective_from')
            ->get();

        $targets = [];
        foreach ($rows as $row) {
            if (! array_key_exists($row->metric_key, $targets)) {
                $targets[$row->metric_key] = (float) $row->target_value;
            }
        }

        return $targets;
    }

    protected function variance(array $kpis, array $targets): array
    {
        $metrics = ['sales_this_month', 'gross_margin_estimate', 'workforce_productivity'];
        $result = [];
        foreach ($metrics as $metric) {
            if (! isset($targets[$metric])) {
                continue;
            }

            $actual = (float) ($kpis[$metric] ?? 0);
            $target = (float) $targets[$metric];
            $delta = $actual - $target;
            $pct = $target != 0 ? round(($delta / $target) * 100, 2) : 0;

            $result[$metric] = [
                'actual' => $actual,
                'target' => $target,
                'delta' => $delta,
                'delta_pct' => $pct,
            ];
        }

        return $result;
    }

    protected function latestNotes(int $businessId): array
    {
        if (! Schema::hasTable('mt_executive_notes')) {
            return [];
        }

        $query = MtExecutiveNote::query()
            ->where('mt_executive_notes.business_id', $businessId)
            ->orderByDesc('mt_executive_notes.note_date')
            ->orderByDesc('mt_executive_notes.id')
            ->limit(8);

        if (Schema::hasTable('users')) {
            $query->leftJoin('users', 'users.id', '=', 'mt_executive_notes.user_id')
                ->select([
                    'mt_executive_notes.title',
                    'mt_executive_notes.note',
                    'mt_executive_notes.note_date',
                    'mt_executive_notes.visibility',
                    'users.first_name',
                    'users.last_name',
                    'users.surname',
                ]);
        } else {
            $query->select(['title', 'note', 'note_date', 'visibility']);
        }

        return $query->get()->map(function ($n) {
            $author = trim(implode(' ', array_filter([
                $n->first_name ?? '',
                $n->last_name ?? '',
                $n->surname ?? '',
            ])));

            return [
                'title' => $n->title,
                'note' => $n->note,
                'note_date' => $n->note_date,
                'author' => $author !== '' ? $author : null,
                'visibility' => $n->visibility ?? 'leadership',
            ];
        })->toArray();
    }

    protected function moduleCockpit(int $businessId): array
    {
        $statuses = $this->moduleStatuses();

        $modules = [
            'Crm' => ['label' => 'CRM', 'table' => 'contacts', 'url' => url('/crm/dashboard'), 'query' => fn () => DB::table('contacts')->where('business_id', $businessId)->count()],
            'Maintenance' => ['label' => 'Maintenance', 'table' => 'maintenance_work_orders', 'url' => url('/maintenance/dashboard'), 'query' => fn () => DB::table('maintenance_work_orders')->where('business_id', $businessId)->count()],
            'ProfessionalProject' => ['label' => 'Project', 'table' => 'pp_projects', 'url' => url('/management-tools/project-categories'), 'query' => fn () => DB::table('pp_projects')->where('business_id', $businessId)->count()],
            'Manufacturing' => ['label' => 'Manufacturing', 'table' => 'mfg_recipes', 'url' => url('/manufacturing/settings'), 'query' => fn () => $this->countManufacturingRecipes($businessId)],
            'Accounting' => [
                'label' => 'Accounting',
                'table' => 'accounting_accounts_transactions',
                'url' => url('/accounting/dashboard'),
                'query' => function () use ($businessId) {
                    return DB::table('accounting_accounts_transactions as aat')
                        ->join('accounting_accounts as aa', 'aa.id', '=', 'aat.accounting_account_id')
                        ->where('aa.business_id', $businessId)
                        ->count();
                },
            ],
            'InventoryManagement' => [
                'label' => 'Inventory',
                'table' => 'variation_location_details',
                'url' => url('/reports/stock-report'),
                'query' => function () use ($businessId) {
                    return DB::table('variation_location_details as vld')
                        ->join('variations as v', 'v.id', '=', 'vld.variation_id')
                        ->join('products as p', 'p.id', '=', 'v.product_id')
                        ->where('p.business_id', $businessId)
                        ->count();
                },
            ],
            'ManagementTools' => ['label' => 'Management Tools', 'table' => 'mt_daily_reports', 'url' => url('/management-tools/executive-cockpit'), 'query' => fn () => DB::table('mt_daily_reports')->where('business_id', $businessId)->count()],
            'IndustrialEngineering' => ['label' => 'Industrial Engineering', 'table' => 'ie_item_masters', 'url' => url('/industrial-engineering/dashboard'), 'query' => fn () => DB::table('ie_item_masters')->where('business_id', $businessId)->count()],
            'QualityManagement' => ['label' => 'Quality (QMS)', 'table' => 'qms_ncr_reports', 'url' => url('/qualitymanagement'), 'query' => fn () => DB::table('qms_ncr_reports')->where('business_id', $businessId)->count()],
            'ShopFloor' => ['label' => 'Shop Floor / MES', 'table' => 'sf_work_centers', 'url' => url('/shopfloor'), 'query' => fn () => DB::table('sf_work_centers')->where('business_id', $businessId)->count()],
            'SupplyChain' => ['label' => 'Supply Chain', 'table' => 'sc_supplier_scorecards', 'url' => url('/supplychain/dashboard'), 'query' => fn () => DB::table('sc_supplier_scorecards')->where('business_id', $businessId)->count()],
            'AdvancedPlanning' => ['label' => __('advancedplanning::lang.module_name'), 'table' => 'ap_capacity_resources', 'url' => url('/advanced-planning/dashboard'), 'query' => fn () => DB::table('ap_capacity_resources')->where('business_id', $businessId)->count()],
            'IntegrationHub' => ['label' => __('integrationhub::lang.module_name'), 'table' => 'ih_webhook_endpoints', 'url' => url('/integrationhub/dashboard'), 'query' => fn () => DB::table('ih_webhook_endpoints')->where('business_id', $businessId)->count()],
        ];

        $result = [];
        foreach ($modules as $key => $meta) {
            $enabled = (bool) ($statuses[$key] ?? false);
            $tableExists = Schema::hasTable($meta['table']);
            $count = 0;
            if ($enabled && $tableExists) {
                try {
                    $count = (int) $meta['query']();
                } catch (\Throwable $e) {
                    report($e);
                    $count = 0;
                }
            }

            $score = 0;
            if (! $enabled) {
                $score = 100;
            } else {
                $score += 50;
                if ($tableExists) {
                    $score += 25;
                }
                if ($count > 0) {
                    $score += 25;
                }
            }

            $result[] = [
                'key' => $key,
                'label' => $meta['label'],
                'enabled' => $enabled,
                'table_exists' => $tableExists,
                'records' => $count,
                'score' => $score,
                'url' => $meta['url'],
            ];
        }

        return $result;
    }

    protected function systemHealth(array $alerts): array
    {
        $severity = count($alerts) === 0 ? 'good' : (count($alerts) <= 2 ? 'watch' : 'critical');
        return [
            'status' => $severity,
            'score' => max(0, 100 - (count($alerts) * 18)),
        ];
    }

    protected function drilldown(int $businessId, Carbon $startDate, Carbon $endDate, array $kpis, array $prevKpis, Carbon $prevStartDate, Carbon $prevEndDate, ?string $onlyTab = null): array
    {
        $tabsNeedingPeriodMetrics = ['sales', 'projects', 'crm', 'accounting', 'manufacturing', 'ie'];
        $needsPeriodMetrics = $onlyTab === null || in_array($onlyTab, $tabsNeedingPeriodMetrics, true);

        $ctx = [
            'businessId' => $businessId,
            'startDate' => $startDate,
            'endDate' => $endDate,
            'prevStartDate' => $prevStartDate,
            'prevEndDate' => $prevEndDate,
            'kpis' => $kpis,
            'prevKpis' => $prevKpis,
            'periodMetrics' => $needsPeriodMetrics
                ? $this->drilldownPeriodMetrics($businessId, $startDate, $endDate)
                : [],
            'prevPeriodMetrics' => $needsPeriodMetrics
                ? $this->drilldownPeriodMetrics($businessId, $prevStartDate, $prevEndDate)
                : [],
        ];

        $tabs = $onlyTab ? [$onlyTab] : $this->coreDrilldownTabKeys();
        $sections = [];

        foreach ($tabs as $tabKey) {
            $section = $this->buildDrilldownTabSection($ctx, $tabKey);
            if ($section !== null) {
                $sections[$tabKey] = $section;
            }
        }

        return $sections;
    }

    protected function coreDrilldownTabKeys(): array
    {
        return ['finance', 'sales', 'maintenance', 'projects', 'workforce', 'crm', 'accounting', 'manufacturing', 'ie', 'quality', 'shopfloor', 'supplychain', 'planning'];
    }

    protected function buildDrilldownTabSection(array $ctx, string $tabKey): ?array
    {
        $businessId = $ctx['businessId'];
        $startDate = $ctx['startDate'];
        $endDate = $ctx['endDate'];
        $kpis = $ctx['kpis'];
        $prevKpis = $ctx['prevKpis'];
        $periodMetrics = $ctx['periodMetrics'];
        $prevPeriodMetrics = $ctx['prevPeriodMetrics'];

        return match ($tabKey) {
            'finance' => [
                'kpis' => [
                    $this->enrichKpiWithComparison(
                        ['label' => __('managementtools::lang.customer_due'), 'value' => $kpis['customer_due'] ?? 0, 'currency' => true],
                        null
                    ),
                    $this->enrichKpiWithComparison(
                        ['label' => __('managementtools::lang.supplier_due'), 'value' => $kpis['supplier_due'] ?? 0, 'currency' => true],
                        null
                    ),
                    $this->enrichKpiWithComparison(
                        ['label' => __('managementtools::lang.gross_margin_estimate'), 'value' => $kpis['gross_margin_estimate'] ?? 0, 'currency' => true],
                        $this->periodComparison(
                            (float) ($kpis['gross_margin_estimate'] ?? 0),
                            (float) ($prevKpis['gross_margin_estimate'] ?? 0)
                        )
                    ),
                ],
                'trend' => $this->monthlySalesSeries($businessId, $endDate),
                'top_items' => $this->topDebtors($businessId),
            ],
            'sales' => [
                'kpis' => [
                    $this->enrichKpiWithComparison(
                        ['label' => __('managementtools::lang.sales_this_month'), 'value' => $kpis['sales_this_month'] ?? 0, 'currency' => true],
                        $this->periodComparison(
                            (float) ($kpis['sales_this_month'] ?? 0),
                            (float) ($prevKpis['sales_this_month'] ?? 0)
                        )
                    ),
                    $this->enrichKpiWithComparison(
                        ['label' => __('managementtools::lang.sales_count'), 'value' => $periodMetrics['sales_count']],
                        $this->periodComparison(
                            (float) $periodMetrics['sales_count'],
                            (float) ($prevPeriodMetrics['sales_count'] ?? 0)
                        )
                    ),
                    $this->enrichKpiWithComparison(
                        ['label' => __('managementtools::lang.average_sale'), 'value' => $periodMetrics['average_sale'], 'currency' => true],
                        $this->periodComparison(
                            (float) $periodMetrics['average_sale'],
                            (float) ($prevPeriodMetrics['average_sale'] ?? 0)
                        )
                    ),
                ],
                'trend' => $this->monthlySalesSeries($businessId, $endDate),
                'top_items' => $this->topCustomersBySales($businessId, $startDate, $endDate),
            ],
            'maintenance' => $this->buildMaintenanceDrilldownSection($ctx),
            'projects' => $this->buildProjectsDrilldownSection($ctx),
            'workforce' => $this->buildWorkforceDrilldownSection($ctx),
            'crm' => $this->buildCrmDrilldownSection($ctx),
            'accounting' => $this->buildAccountingDrilldownSection($ctx),
            'manufacturing' => $this->buildManufacturingDrilldownSection($ctx),
            'ie' => $this->buildIeDrilldownSection($ctx),
            'quality' => $this->buildQualityDrilldownSection($ctx),
            'shopfloor' => $this->buildShopFloorDrilldownSection($ctx),
            'supplychain' => $this->buildSupplyChainDrilldownSection($ctx),
            'planning' => $this->buildPlanningDrilldownSection($ctx),
            default => null,
        };
    }

    protected function buildMaintenanceDrilldownSection(array $ctx): array
    {
        $businessId = $ctx['businessId'];
        $endDate = $ctx['endDate'];
        $kpis = $ctx['kpis'];
        $prevKpis = $ctx['prevKpis'];
        $draftWorkOrders = 0;
        $inProgressWorkOrders = 0;

        if (Schema::hasTable('maintenance_work_orders')) {
            $draftWorkOrders = (int) DB::table('maintenance_work_orders')
                ->where('business_id', $businessId)
                ->where('status', 'draft')
                ->count();

            $inProgressWorkOrders = (int) DB::table('maintenance_work_orders')
                ->where('business_id', $businessId)
                ->where('status', 'in_progress')
                ->count();
        }

        return [
            'kpis' => [
                $this->enrichKpiWithComparison(
                    ['label' => __('managementtools::lang.open_work_orders'), 'value' => $kpis['open_work_orders'] ?? 0],
                    $this->periodComparison(
                        (float) ($kpis['open_work_orders'] ?? 0),
                        (float) ($prevKpis['open_work_orders'] ?? 0),
                        true
                    )
                ),
                $this->enrichKpiWithComparison(
                    ['label' => __('managementtools::lang.work_orders_draft'), 'value' => $draftWorkOrders],
                    null
                ),
                $this->enrichKpiWithComparison(
                    ['label' => __('managementtools::lang.work_orders_in_progress'), 'value' => $inProgressWorkOrders],
                    null
                ),
            ],
            'trend' => $this->monthlyCountSeries($businessId, 'maintenance_work_orders', 'created_at', $endDate),
            'top_items' => $this->topMaintenanceBacklog($businessId),
        ];
    }

    protected function buildProjectsDrilldownSection(array $ctx): array
    {
        $businessId = $ctx['businessId'];
        $endDate = $ctx['endDate'];
        $kpis = $ctx['kpis'];
        $prevKpis = $ctx['prevKpis'];
        $periodMetrics = $ctx['periodMetrics'];
        $prevPeriodMetrics = $ctx['prevPeriodMetrics'];
        $inProgressProjects = 0;

        if (Schema::hasTable('pp_projects')) {
            $inProgressProjects = (int) DB::table('pp_projects')
                ->where('business_id', $businessId)
                ->where('status', 'in_progress')
                ->count();
        }

        return [
            'kpis' => [
                $this->enrichKpiWithComparison(
                    ['label' => __('managementtools::lang.active_projects'), 'value' => $kpis['active_projects'] ?? 0],
                    $this->periodComparison(
                        (float) ($kpis['active_projects'] ?? 0),
                        (float) ($prevKpis['active_projects'] ?? 0),
                        true
                    )
                ),
                $this->enrichKpiWithComparison(
                    ['label' => __('managementtools::lang.projects_in_progress'), 'value' => $inProgressProjects],
                    null
                ),
                $this->enrichKpiWithComparison(
                    ['label' => __('managementtools::lang.projects_completed_in_period'), 'value' => $periodMetrics['projects_completed_in_period']],
                    $this->periodComparison(
                        (float) $periodMetrics['projects_completed_in_period'],
                        (float) ($prevPeriodMetrics['projects_completed_in_period'] ?? 0)
                    )
                ),
            ],
            'trend' => $this->monthlyCountSeries($businessId, 'pp_projects', 'created_at', $endDate),
            'top_items' => $this->topProjectRisks($businessId),
        ];
    }

    protected function buildWorkforceDrilldownSection(array $ctx): array
    {
        $businessId = $ctx['businessId'];
        $startDate = $ctx['startDate'];
        $endDate = $ctx['endDate'];
        $kpis = $ctx['kpis'];
        $prevKpis = $ctx['prevKpis'];
        $activeEmployees = 0;
        $submittedReports = 0;

        if (Schema::hasTable('users')) {
            $activeEmployees = (int) DB::table('users')
                ->where('business_id', $businessId)
                ->where('allow_login', 1)
                ->where('user_type', 'user')
                ->count();
        }

        if (Schema::hasTable('mt_daily_reports')) {
            $submittedReports = (int) DB::table('mt_daily_reports')
                ->where('business_id', $businessId)
                ->whereBetween(DB::raw('date(report_date)'), [$startDate->toDateString(), $endDate->toDateString()])
                ->count();
        }

        return [
            'kpis' => [
                $this->enrichKpiWithComparison(
                    ['label' => __('managementtools::lang.active_employees'), 'value' => $activeEmployees],
                    null
                ),
                $this->enrichKpiWithComparison(
                    ['label' => __('managementtools::lang.mt_reports_submitted'), 'value' => $submittedReports],
                    $this->periodComparison(
                        (float) $submittedReports,
                        (float) ($prevKpis['mt_reports_submitted'] ?? 0)
                    )
                ),
                $this->enrichKpiWithComparison(
                    ['label' => __('managementtools::lang.workforce_productivity'), 'value' => ($kpis['workforce_productivity'] ?? 0).'%'],
                    $this->periodComparison(
                        (float) ($kpis['workforce_productivity'] ?? 0),
                        (float) ($prevKpis['workforce_productivity'] ?? 0)
                    )
                ),
            ],
            'trend' => $this->monthlyCountSeries($businessId, 'mt_daily_reports', 'report_date', $endDate),
            'top_items' => $this->topReporters($businessId, $startDate, $endDate),
        ];
    }

    protected function buildCrmDrilldownSection(array $ctx): array
    {
        $businessId = $ctx['businessId'];
        $startDate = $ctx['startDate'];
        $endDate = $ctx['endDate'];
        $prevPeriodMetrics = $ctx['prevPeriodMetrics'];
        $crmMetrics = $this->crmDrilldownMetrics($businessId, $startDate, $endDate);

        return [
            'kpis' => [
                $this->enrichKpiWithComparison(
                    ['label' => __('managementtools::lang.crm_total_leads'), 'value' => $crmMetrics['total_leads']],
                    null
                ),
                $this->enrichKpiWithComparison(
                    ['label' => __('managementtools::lang.crm_new_leads'), 'value' => $crmMetrics['new_leads']],
                    $this->periodComparison(
                        (float) $crmMetrics['new_leads'],
                        (float) ($prevPeriodMetrics['crm_new_leads'] ?? 0)
                    )
                ),
                $this->enrichKpiWithComparison(
                    ['label' => __('managementtools::lang.crm_pipeline_value'), 'value' => $crmMetrics['pipeline_value'], 'currency' => true],
                    null
                ),
            ],
            'trend' => $this->monthlyLeadsSeries($businessId, $endDate),
            'top_items' => $this->topCrmLeads($businessId),
        ];
    }

    protected function buildAccountingDrilldownSection(array $ctx): array
    {
        $businessId = $ctx['businessId'];
        $endDate = $ctx['endDate'];
        $prevPeriodMetrics = $ctx['prevPeriodMetrics'];
        $accountingMetrics = $this->accountingDrilldownMetrics($businessId, $ctx['startDate'], $endDate);

        return [
            'kpis' => [
                $this->enrichKpiWithComparison(
                    ['label' => __('managementtools::lang.accounting_journal_entries'), 'value' => $accountingMetrics['journal_entries']],
                    $this->periodComparison(
                        (float) $accountingMetrics['journal_entries'],
                        (float) ($prevPeriodMetrics['journal_entry_count'] ?? 0)
                    )
                ),
                $this->enrichKpiWithComparison(
                    ['label' => __('managementtools::lang.accounting_pending_cheques'), 'value' => $accountingMetrics['pending_cheques']],
                    $this->periodComparison(
                        (float) $accountingMetrics['pending_cheques'],
                        (float) ($accountingMetrics['prev_pending_cheques'] ?? 0),
                        true
                    )
                ),
                $this->enrichKpiWithComparison(
                    ['label' => __('managementtools::lang.accounting_overdue_installments'), 'value' => $accountingMetrics['overdue_installments']],
                    null
                ),
            ],
            'trend' => $this->monthlyAccountingJournalSeries($businessId, $endDate),
            'top_items' => $this->topAccountingExposure($businessId),
        ];
    }

    protected function buildManufacturingDrilldownSection(array $ctx): array
    {
        $businessId = $ctx['businessId'];
        $startDate = $ctx['startDate'];
        $endDate = $ctx['endDate'];
        $periodMetrics = $ctx['periodMetrics'];
        $prevPeriodMetrics = $ctx['prevPeriodMetrics'];
        $manufacturingMetrics = $this->manufacturingDrilldownMetrics($businessId, $startDate, $endDate, $periodMetrics, $prevPeriodMetrics);

        return [
            'kpis' => $manufacturingMetrics['kpis'],
            'trend' => $this->monthlyProductionCountSeries($businessId, $endDate),
            'top_items' => $this->topProductions($businessId, $startDate, $endDate),
        ];
    }

    protected function buildIeDrilldownSection(array $ctx): array
    {
        $businessId = $ctx['businessId'];
        $startDate = $ctx['startDate'];
        $endDate = $ctx['endDate'];
        $periodMetrics = $ctx['periodMetrics'];
        $prevPeriodMetrics = $ctx['prevPeriodMetrics'];
        $ieMetrics = $this->ieDrilldownMetrics($businessId, $startDate, $endDate, $periodMetrics, $prevPeriodMetrics);

        return [
            'kpis' => $ieMetrics['kpis'],
            'trend' => $this->monthlyMrpRunsSeries($businessId, $endDate),
            'top_items' => $this->topIeExposure($businessId, $startDate, $endDate),
        ];
    }

    protected function buildQualityDrilldownSection(array $ctx): array
    {
        $businessId = $ctx['businessId'];
        $openNcrs = Schema::hasTable('qms_ncr_reports')
            ? (int) DB::table('qms_ncr_reports')->where('business_id', $businessId)->whereNotIn('status', ['closed', 'cancelled'])->count()
            : 0;
        $openCapas = Schema::hasTable('qms_capa_actions')
            ? (int) DB::table('qms_capa_actions')->where('business_id', $businessId)->whereNotIn('status', ['closed', 'cancelled'])->count()
            : 0;
        $spcAlerts = Schema::hasTable('qms_spc_measurements')
            ? (int) DB::table('qms_spc_measurements')->where('business_id', $businessId)->where('in_control', false)->where('measured_at', '>=', now()->subDays(30))->count()
            : 0;

        return [
            'kpis' => [
                $this->enrichKpiWithComparison(['label' => __('managementtools::lang.open_ncrs'), 'value' => $openNcrs], null),
                $this->enrichKpiWithComparison(['label' => __('managementtools::lang.open_capas'), 'value' => $openCapas], null),
                $this->enrichKpiWithComparison(['label' => __('managementtools::lang.spc_out_of_control'), 'value' => $spcAlerts], null),
            ],
            'trend' => $this->monthlyCountSeries($businessId, 'qms_ncr_reports', 'created_at', $ctx['endDate']),
            'top_items' => [],
        ];
    }

    protected function buildShopFloorDrilldownSection(array $ctx): array
    {
        $businessId = $ctx['businessId'];
        $avgOee = Schema::hasTable('sf_oee_snapshots')
            ? (float) DB::table('sf_oee_snapshots')->where('business_id', $businessId)->where('snapshot_date', '>=', $ctx['startDate']->toDateString())->avg('oee_pct')
            : 0;
        $events = Schema::hasTable('sf_production_events')
            ? (int) DB::table('sf_production_events')->where('business_id', $businessId)->whereBetween('recorded_at', [$ctx['startDate'], $ctx['endDate']])->count()
            : 0;
        $downtime = Schema::hasTable('sf_downtime_events')
            ? (int) DB::table('sf_downtime_events')->where('business_id', $businessId)->whereNull('ended_at')->count()
            : 0;

        return [
            'kpis' => [
                $this->enrichKpiWithComparison(['label' => __('managementtools::lang.avg_oee'), 'value' => round($avgOee, 1)], null),
                $this->enrichKpiWithComparison(['label' => __('managementtools::lang.production_events'), 'value' => $events], null),
                $this->enrichKpiWithComparison(['label' => __('managementtools::lang.active_downtime'), 'value' => $downtime], null),
            ],
            'trend' => $this->monthlyCountSeries($businessId, 'sf_production_events', 'recorded_at', $ctx['endDate']),
            'top_items' => [],
        ];
    }

    protected function buildSupplyChainDrilldownSection(array $ctx): array
    {
        $businessId = $ctx['businessId'];
        $avgScore = Schema::hasTable('sc_supplier_scorecards')
            ? (float) DB::table('sc_supplier_scorecards')->where('business_id', $businessId)->avg('overall_score')
            : 0;
        $forecasts = Schema::hasTable('sc_demand_forecasts')
            ? (int) DB::table('sc_demand_forecasts')->where('business_id', $businessId)->count()
            : 0;
        $openRfq = Schema::hasTable('sc_rfq_requests')
            ? (int) DB::table('sc_rfq_requests')->where('business_id', $businessId)->whereIn('status', ['draft', 'open'])->count()
            : 0;

        return [
            'kpis' => [
                $this->enrichKpiWithComparison(['label' => __('managementtools::lang.supplier_score_avg'), 'value' => round($avgScore, 1)], null),
                $this->enrichKpiWithComparison(['label' => __('managementtools::lang.demand_forecasts'), 'value' => $forecasts], null),
                $this->enrichKpiWithComparison(['label' => __('managementtools::lang.open_rfqs'), 'value' => $openRfq], null),
            ],
            'trend' => $this->monthlyCountSeries($businessId, 'sc_rfq_requests', 'created_at', $ctx['endDate']),
            'top_items' => [],
        ];
    }

    protected function buildPlanningDrilldownSection(array $ctx): array
    {
        $businessId = $ctx['businessId'];
        $resources = Schema::hasTable('ap_capacity_resources')
            ? (int) DB::table('ap_capacity_resources')->where('business_id', $businessId)->count()
            : 0;
        $schedules = Schema::hasTable('ap_schedule_runs')
            ? (int) DB::table('ap_schedule_runs')->where('business_id', $businessId)->where('status', 'completed')->count()
            : 0;
        $sopDrafts = Schema::hasTable('ap_sop_cycles')
            ? (int) DB::table('ap_sop_cycles')->where('business_id', $businessId)->where('status', 'draft')->count()
            : 0;

        return [
            'kpis' => [
                $this->enrichKpiWithComparison(['label' => __('managementtools::lang.capacity_resources'), 'value' => $resources], null),
                $this->enrichKpiWithComparison(['label' => __('managementtools::lang.completed_schedules'), 'value' => $schedules], null),
                $this->enrichKpiWithComparison(['label' => __('managementtools::lang.sop_draft_cycles'), 'value' => $sopDrafts], null),
            ],
            'trend' => $this->monthlyCountSeries($businessId, 'ap_schedule_runs', 'created_at', $ctx['endDate']),
            'top_items' => [],
        ];
    }

    public function holdingEntityComparison(int $businessId, Carbon $startDate, Carbon $endDate): array
    {
        return $this->metricsCache->rememberHoldingComparison(
            $businessId,
            $startDate,
            $endDate,
            fn () => $this->holdingComparisonService->compare(
                $businessId,
                $startDate,
                $endDate,
                fn ($current, $previous) => $this->periodComparison($current, $previous),
                fn ($start, $end) => $this->previousPeriodRange($start, $end)
            )
        );
    }

    protected function entityPeriodSales(int $businessId, int $entityId, int $locationId, Carbon $startDate, Carbon $endDate): float
    {
        $batch = $this->transactionMetrics->entitySalesBatch(
            $businessId,
            [$entityId],
            [$entityId => $locationId],
            $startDate,
            $endDate
        );

        return (float) ($batch[$entityId] ?? 0);
    }

    protected function monthlySalesSeries(int $businessId, Carbon $endDate): array
    {
        return $this->transactionMetrics->monthlySalesSeries($businessId, $endDate);
    }

    protected function monthlyCountSeries(int $businessId, string $table, string $dateColumn, Carbon $endDate): array
    {
        return $this->transactionMetrics->monthlyCountSeries($businessId, $table, $dateColumn, $endDate);
    }

    protected function topDebtors(int $businessId): array
    {
        if (! Schema::hasTable('contacts')) {
            return [];
        }

        return DB::table('contacts')
            ->where('business_id', $businessId)
            ->whereIn('type', ['customer', 'both'])
            ->where('balance', '>', 0)
            ->orderByDesc('balance')
            ->limit(5)
            ->get(['id', 'name', 'balance'])
            ->map(function ($row) {
                $contactMeta = $this->contactActions((int) $row->id);

                return array_merge([
                    'label' => (string) ($row->name ?: '-'),
                    'value' => (float) $row->balance,
                    'currency' => true,
                ], $contactMeta);
            })
            ->toArray();
    }

    protected function topCustomersBySales(int $businessId, Carbon $startDate, Carbon $endDate): array
    {
        if (! Schema::hasTable('transactions') || ! Schema::hasColumn('transactions', 'contact_id')) {
            return [];
        }

        $query = DB::table('transactions as t')
            ->leftJoin('contacts as c', 'c.id', '=', 't.contact_id')
            ->where('t.business_id', $businessId)
            ->where('t.type', 'sell')
            ->where('t.status', 'final')
            ->whereBetween(DB::raw('date(t.transaction_date)'), [$startDate->toDateString(), $endDate->toDateString()])
            ->groupBy('t.contact_id', 'c.name')
            ->selectRaw('t.contact_id, COALESCE(c.name, ?) as label, SUM(t.final_total) as total', ['-'])
            ->orderByDesc('total')
            ->limit(5)
            ->get();

        return $query->map(function ($row) {
            $contactId = isset($row->contact_id) ? (int) $row->contact_id : 0;
            $contactMeta = $this->contactActions($contactId);

            return array_merge([
                'label' => (string) $row->label,
                'value' => (float) $row->total,
                'currency' => true,
            ], $contactMeta);
        })->toArray();
    }

    protected function topMaintenanceBacklog(int $businessId): array
    {
        if (! Schema::hasTable('maintenance_work_orders')) {
            return [];
        }

        $rows = DB::table('maintenance_work_orders')
            ->where('business_id', $businessId)
            ->whereIn('status', ['draft', 'open', 'in_progress', 'on_hold'])
            ->orderByDesc('updated_at')
            ->limit(5)
            ->get(['id', 'status']);

        return $rows->map(function ($row) {
            return [
                'label' => '#'.$row->id.' ('.$row->status.')',
                'value' => 1,
                'url' => url('/maintenance/work-orders/'.$row->id),
                'permissions' => ['maintenance.view', 'maintenance.access'],
                'actions' => [
                    [
                        'label' => __('managementtools::lang.view_details'),
                        'url' => url('/maintenance/work-orders/'.$row->id),
                        'permissions' => ['maintenance.view', 'maintenance.access'],
                    ],
                    [
                        'label' => __('managementtools::lang.open_maintenance_board'),
                        'url' => url('/maintenance/dashboard'),
                        'permissions' => ['maintenance.view', 'maintenance.access'],
                    ],
                ],
            ];
        })->toArray();
    }

    protected function topProjectRisks(int $businessId): array
    {
        if (! Schema::hasTable('pp_projects')) {
            return [];
        }

        $query = DB::table('pp_projects')
            ->where('business_id', $businessId)
            ->whereIn('status', ['not_started', 'in_progress', 'on_hold'])
            ->orderByDesc('updated_at')
            ->limit(5)
            ->get(['id', 'name', 'status']);

        return $query->map(function ($row) {
            return [
                'label' => (string) ($row->name ?: '-').' ('.$row->status.')',
                'value' => 1,
                'url' => url('/professional-project/projects/'.$row->id),
                'permissions' => ['managementtools.board_view'],
                'actions' => [
                    [
                        'label' => __('managementtools::lang.view_details'),
                        'url' => url('/professional-project/projects/'.$row->id),
                        'permissions' => ['managementtools.board_view'],
                    ],
                    [
                        'label' => __('managementtools::lang.open_project_center'),
                        'url' => url('/professional-project/projects?view=list'),
                        'permissions' => ['managementtools.board_view'],
                    ],
                ],
            ];
        })->toArray();
    }

    protected function topReporters(int $businessId, Carbon $startDate, Carbon $endDate): array
    {
        if (! Schema::hasTable('mt_daily_reports') || ! Schema::hasColumn('mt_daily_reports', 'user_id')) {
            return [];
        }

        $query = DB::table('mt_daily_reports as r')
            ->leftJoin('users as u', 'u.id', '=', 'r.user_id')
            ->where('r.business_id', $businessId)
            ->whereBetween(DB::raw('date(r.report_date)'), [$startDate->toDateString(), $endDate->toDateString()])
            ->groupBy('r.user_id', 'u.username')
            ->selectRaw('r.user_id, COALESCE(u.username, ?) as label, COUNT(*) as total', ['-'])
            ->orderByDesc('total')
            ->limit(5)
            ->get();

        return $query->map(function ($row) {
            $userId = isset($row->user_id) ? (int) $row->user_id : 0;
            return [
                'label' => (string) $row->label,
                'value' => (int) $row->total,
                'url' => $userId > 0 ? url('/users/'.$userId.'/edit') : url('/users'),
                'permissions' => ['user.view', 'user.create', 'user.update'],
                'actions' => [
                    [
                        'label' => __('managementtools::lang.view_details'),
                        'url' => $userId > 0 ? url('/users/'.$userId.'/edit') : url('/users'),
                        'permissions' => ['user.view', 'user.create', 'user.update'],
                    ],
                    [
                        'label' => __('managementtools::lang.open_daily_reports'),
                        'url' => url('/management-tools/daily-reports'),
                        'permissions' => ['managementtools.board_view'],
                    ],
                ],
            ];
        })->toArray();
    }

    protected function crmViewPermissions(): array
    {
        return ['crm.access_all_leads', 'crm.access_own_leads', 'managementtools.board_view'];
    }

    protected function accountingViewPermissions(): array
    {
        return ['accounting.access_accounting_module', 'accounting.view_reports', 'managementtools.board_view'];
    }

    protected function manufacturingViewPermissions(): array
    {
        return ['manufacturing.access_production', 'manufacturing.access_recipe', 'managementtools.board_view'];
    }

    protected function crmDrilldownMetrics(int $businessId, Carbon $startDate, Carbon $endDate): array
    {
        $metrics = [
            'total_leads' => 0,
            'new_leads' => 0,
            'pipeline_value' => 0.0,
        ];

        if (! Schema::hasTable('contacts')) {
            return $metrics;
        }

        $leadQuery = DB::table('contacts')
            ->where('business_id', $businessId)
            ->where('type', 'lead');

        if (Schema::hasColumn('contacts', 'contact_status')) {
            $leadQuery->where('contact_status', 'active');
        }

        $metrics['total_leads'] = (int) (clone $leadQuery)->count();
        $metrics['new_leads'] = (int) (clone $leadQuery)
            ->whereBetween(DB::raw('date(created_at)'), [$startDate->toDateString(), $endDate->toDateString()])
            ->count();

        if (Schema::hasColumn('contacts', 'crm_lead_value')) {
            $metrics['pipeline_value'] = (float) (clone $leadQuery)->sum('crm_lead_value');
        }

        return $metrics;
    }

    protected function accountingDrilldownMetrics(int $businessId, Carbon $startDate, Carbon $endDate): array
    {
        $metrics = [
            'journal_entries' => 0,
            'pending_cheques' => 0,
            'prev_pending_cheques' => 0,
            'overdue_installments' => 0,
        ];

        if (Schema::hasTable('accounting_accounts_transactions') && Schema::hasTable('accounting_accounts')) {
            $dateColumn = Schema::hasColumn('accounting_accounts_transactions', 'operation_date') ? 'operation_date' : 'created_at';
            $metrics['journal_entries'] = (int) DB::table('accounting_accounts_transactions as aat')
                ->join('accounting_accounts as aa', 'aa.id', '=', 'aat.accounting_account_id')
                ->where('aa.business_id', $businessId)
                ->whereBetween(DB::raw('date(aat.'.$dateColumn.')'), [$startDate->toDateString(), $endDate->toDateString()])
                ->count();
        }

        if (Schema::hasTable('accounting_cheques')) {
            $metrics['pending_cheques'] = (int) DB::table('accounting_cheques')
                ->where('business_id', $businessId)
                ->whereIn('status', ['pending', 'deposited'])
                ->count();
        }

        if (Schema::hasTable('accounting_installment_schedule') && Schema::hasTable('accounting_installment_plans')) {
            $metrics['overdue_installments'] = (int) DB::table('accounting_installment_schedule as s')
                ->join('accounting_installment_plans as p', 'p.id', '=', 's.installment_plan_id')
                ->where('p.business_id', $businessId)
                ->where('s.status', 'overdue')
                ->count();
        }

        return $metrics;
    }

    protected function manufacturingDrilldownMetrics(int $businessId, Carbon $startDate, Carbon $endDate, array $periodMetrics, array $prevPeriodMetrics): array
    {
        $productionCount = (int) ($periodMetrics['production_count'] ?? 0);
        $productionValue = (float) ($periodMetrics['production_value'] ?? 0);
        $activeRecipes = 0;

        if (Schema::hasTable('mfg_recipes')) {
            $activeRecipes = $this->countManufacturingRecipes($businessId);
        }

        return [
            'kpis' => [
                $this->enrichKpiWithComparison(
                    ['label' => __('managementtools::lang.manufacturing_production_count'), 'value' => $productionCount],
                    $this->periodComparison(
                        (float) $productionCount,
                        (float) ($prevPeriodMetrics['production_count'] ?? 0)
                    )
                ),
                $this->enrichKpiWithComparison(
                    ['label' => __('managementtools::lang.manufacturing_production_value'), 'value' => $productionValue, 'currency' => true],
                    $this->periodComparison(
                        $productionValue,
                        (float) ($prevPeriodMetrics['production_value'] ?? 0)
                    )
                ),
                $this->enrichKpiWithComparison(
                    ['label' => __('managementtools::lang.manufacturing_active_recipes'), 'value' => $activeRecipes],
                    null
                ),
            ],
        ];
    }

    protected function monthlyLeadsSeries(int $businessId, Carbon $endDate): array
    {
        if (! Schema::hasTable('contacts')) {
            return [0, 0, 0, 0, 0, 0];
        }

        $series = [];
        for ($i = 5; $i >= 0; $i--) {
            $monthStart = $endDate->copy()->startOfMonth()->subMonths($i);
            $monthEnd = $monthStart->copy()->endOfMonth();
            $query = DB::table('contacts')
                ->where('business_id', $businessId)
                ->where('type', 'lead')
                ->whereBetween(DB::raw('date(created_at)'), [$monthStart->toDateString(), $monthEnd->toDateString()]);

            if (Schema::hasColumn('contacts', 'contact_status')) {
                $query->where('contact_status', 'active');
            }

            $series[] = (int) $query->count();
        }

        return $series;
    }

    protected function monthlyAccountingJournalSeries(int $businessId, Carbon $endDate): array
    {
        if (! Schema::hasTable('accounting_accounts_transactions') || ! Schema::hasTable('accounting_accounts')) {
            return [0, 0, 0, 0, 0, 0];
        }

        $dateColumn = Schema::hasColumn('accounting_accounts_transactions', 'operation_date') ? 'operation_date' : 'created_at';
        $series = [];

        for ($i = 5; $i >= 0; $i--) {
            $monthStart = $endDate->copy()->startOfMonth()->subMonths($i);
            $monthEnd = $monthStart->copy()->endOfMonth();
            $series[] = (int) DB::table('accounting_accounts_transactions as aat')
                ->join('accounting_accounts as aa', 'aa.id', '=', 'aat.accounting_account_id')
                ->where('aa.business_id', $businessId)
                ->whereBetween(DB::raw('date(aat.'.$dateColumn.')'), [$monthStart->toDateString(), $monthEnd->toDateString()])
                ->count();
        }

        return $series;
    }

    protected function monthlyProductionCountSeries(int $businessId, Carbon $endDate): array
    {
        if (! Schema::hasTable('transactions')) {
            return [0, 0, 0, 0, 0, 0];
        }

        $series = [];
        for ($i = 5; $i >= 0; $i--) {
            $monthStart = $endDate->copy()->startOfMonth()->subMonths($i);
            $monthEnd = $monthStart->copy()->endOfMonth();
            $series[] = (int) DB::table('transactions')
                ->where('business_id', $businessId)
                ->where('type', 'production_purchase')
                ->whereBetween(DB::raw('date(transaction_date)'), [$monthStart->toDateString(), $monthEnd->toDateString()])
                ->count();
        }

        return $series;
    }

    protected function topCrmLeads(int $businessId): array
    {
        if (! Schema::hasTable('contacts')) {
            return [];
        }

        $query = DB::table('contacts')
            ->where('business_id', $businessId)
            ->where('type', 'lead');

        if (Schema::hasColumn('contacts', 'contact_status')) {
            $query->where('contact_status', 'active');
        }

        if (Schema::hasColumn('contacts', 'crm_lead_value')) {
            $query->orderByDesc('crm_lead_value');
        } else {
            $query->orderByDesc('created_at');
        }

        $permissions = $this->crmViewPermissions();
        $hasLeadValue = Schema::hasColumn('contacts', 'crm_lead_value');
        $columns = ['id', 'name'];
        if (Schema::hasColumn('contacts', 'crm_lead_title')) {
            $columns[] = 'crm_lead_title';
        }
        if ($hasLeadValue) {
            $columns[] = 'crm_lead_value';
        }

        return $query->limit(5)->get($columns)
            ->map(function ($row) use ($permissions, $hasLeadValue) {
                $label = (string) (($row->crm_lead_title ?? null) ?: $row->name ?: '-');
                $value = $hasLeadValue ? (float) ($row->crm_lead_value ?? 0) : 0;

                return [
                    'label' => $label,
                    'value' => $value,
                    'currency' => $hasLeadValue,
                    'url' => url('/contacts/'.$row->id),
                    'permissions' => $permissions,
                    'actions' => [
                        [
                            'label' => __('managementtools::lang.view_details'),
                            'url' => url('/contacts/'.$row->id),
                            'permissions' => $permissions,
                        ],
                        [
                            'label' => __('managementtools::lang.open_crm_board'),
                            'url' => url('/crm/dashboard'),
                            'permissions' => $permissions,
                        ],
                    ],
                ];
            })->toArray();
    }

    protected function topAccountingExposure(int $businessId): array
    {
        $items = [];
        $permissions = $this->accountingViewPermissions();

        if (Schema::hasTable('accounting_cheques')) {
            $cheques = DB::table('accounting_cheques')
                ->where('business_id', $businessId)
                ->whereIn('status', ['pending', 'deposited'])
                ->orderByDesc('amount')
                ->limit(5)
                ->get(['id', 'cheque_number', 'amount', 'status']);

            foreach ($cheques as $cheque) {
                $items[] = [
                    'label' => __('managementtools::lang.accounting_cheque_item', [
                        'number' => $cheque->cheque_number,
                        'status' => $cheque->status,
                    ]),
                    'value' => (float) $cheque->amount,
                    'currency' => true,
                    'url' => $this->routeIfExists('accounting.cheques') ?: url('/accounting/cheques'),
                    'permissions' => $permissions,
                    'actions' => [
                        [
                            'label' => __('managementtools::lang.view_details'),
                            'url' => $this->routeIfExists('accounting.cheques') ?: url('/accounting/cheques'),
                            'permissions' => $permissions,
                        ],
                        [
                            'label' => __('managementtools::lang.open_accounting_dashboard'),
                            'url' => url('/accounting/dashboard'),
                            'permissions' => $permissions,
                        ],
                    ],
                ];
            }
        }

        if (empty($items) && Schema::hasTable('accounting_accounts') && Schema::hasTable('accounting_accounts_transactions')) {
            $accounts = DB::table('accounting_accounts as aa')
                ->leftJoin('accounting_accounts_transactions as aat', 'aat.accounting_account_id', '=', 'aa.id')
                ->where('aa.business_id', $businessId)
                ->groupBy('aa.id', 'aa.name')
                ->selectRaw('aa.id, aa.name as label, COUNT(aat.id) as total')
                ->orderByDesc('total')
                ->limit(5)
                ->get();

            foreach ($accounts as $account) {
                $items[] = [
                    'label' => (string) $account->label,
                    'value' => (int) $account->total,
                    'url' => $this->routeIfExists('accounting.ledger') ? route('accounting.ledger', ['id' => $account->id]) : url('/accounting/chart-of-accounts'),
                    'permissions' => $permissions,
                    'actions' => [
                        [
                            'label' => __('managementtools::lang.view_details'),
                            'url' => $this->routeIfExists('accounting.ledger') ? route('accounting.ledger', ['id' => $account->id]) : url('/accounting/chart-of-accounts'),
                            'permissions' => $permissions,
                        ],
                    ],
                ];
            }
        }

        return $items;
    }

    protected function topProductions(int $businessId, Carbon $startDate, Carbon $endDate): array
    {
        if (! Schema::hasTable('transactions')) {
            return [];
        }

        $permissions = $this->manufacturingViewPermissions();

        return DB::table('transactions')
            ->where('business_id', $businessId)
            ->where('type', 'production_purchase')
            ->whereBetween(DB::raw('date(transaction_date)'), [$startDate->toDateString(), $endDate->toDateString()])
            ->orderByDesc('final_total')
            ->limit(5)
            ->get(['id', 'ref_no', 'final_total', 'transaction_date'])
            ->map(function ($row) use ($permissions) {
                $label = (string) ($row->ref_no ?: '#'.$row->id);

                return [
                    'label' => $label,
                    'value' => (float) $row->final_total,
                    'currency' => true,
                    'url' => url('/manufacturing/production/'.$row->id),
                    'permissions' => $permissions,
                    'actions' => [
                        [
                            'label' => __('managementtools::lang.view_details'),
                            'url' => url('/manufacturing/production/'.$row->id),
                            'permissions' => $permissions,
                        ],
                        [
                            'label' => __('managementtools::lang.open_manufacturing_board'),
                            'url' => url('/manufacturing/production'),
                            'permissions' => $permissions,
                        ],
                    ],
                ];
            })->toArray();
    }

    protected function ieViewPermissions(): array
    {
        return ['ie.view', 'ie.bom.view', 'ie.costing.view', 'managementtools.board_view'];
    }

    protected function ieDrilldownMetrics(int $businessId, Carbon $startDate, Carbon $endDate, array $periodMetrics, array $prevPeriodMetrics): array
    {
        $mrpRuns = (int) ($periodMetrics['ie_mrp_runs'] ?? 0);
        $activeBoms = 0;
        $productionLines = 0;
        $releasedRevisions = 0;
        $shortageRuns = 0;

        if (Schema::hasTable('ie_boms')) {
            $activeBoms = (int) DB::table('ie_boms')
                ->where('business_id', $businessId)
                ->whereNull('deleted_at')
                ->count();
        }

        if (Schema::hasTable('ie_line_templates')) {
            $lineQuery = DB::table('ie_line_templates')
                ->where('business_id', $businessId)
                ->whereNull('deleted_at');

            if (Schema::hasColumn('ie_line_templates', 'parent_template_id')) {
                $lineQuery->whereNull('parent_template_id');
            }

            $productionLines = (int) $lineQuery->count();
        }

        if (Schema::hasTable('ie_line_revisions')) {
            $releasedRevisions = (int) DB::table('ie_line_revisions')
                ->where('business_id', $businessId)
                ->where('status', 'released')
                ->whereNull('deleted_at')
                ->count();
        }

        if (Schema::hasTable('ie_mrp_runs')) {
            $runs = DB::table('ie_mrp_runs')
                ->where('business_id', $businessId)
                ->whereBetween(DB::raw('date(created_at)'), [$startDate->toDateString(), $endDate->toDateString()])
                ->get(['shortage_analysis']);

            foreach ($runs as $run) {
                $analysis = json_decode((string) ($run->shortage_analysis ?? ''), true);
                if (! empty($analysis['shortages'])) {
                    $shortageRuns++;
                }
            }
        }

        return [
            'kpis' => [
                $this->enrichKpiWithComparison(
                    ['label' => __('managementtools::lang.ie_mrp_runs'), 'value' => $mrpRuns],
                    $this->periodComparison(
                        (float) $mrpRuns,
                        (float) ($prevPeriodMetrics['ie_mrp_runs'] ?? 0)
                    )
                ),
                $this->enrichKpiWithComparison(
                    ['label' => __('managementtools::lang.ie_active_boms'), 'value' => $activeBoms],
                    null
                ),
                $this->enrichKpiWithComparison(
                    ['label' => __('managementtools::lang.ie_production_lines'), 'value' => $productionLines],
                    null
                ),
            ],
            'released_revisions' => $releasedRevisions,
            'shortage_runs' => $shortageRuns,
        ];
    }

    protected function monthlyMrpRunsSeries(int $businessId, Carbon $endDate): array
    {
        if (! Schema::hasTable('ie_mrp_runs')) {
            return [0, 0, 0, 0, 0, 0];
        }

        $series = [];
        for ($i = 5; $i >= 0; $i--) {
            $monthStart = $endDate->copy()->startOfMonth()->subMonths($i);
            $monthEnd = $monthStart->copy()->endOfMonth();
            $series[] = (int) DB::table('ie_mrp_runs')
                ->where('business_id', $businessId)
                ->whereBetween(DB::raw('date(created_at)'), [$monthStart->toDateString(), $monthEnd->toDateString()])
                ->count();
        }

        return $series;
    }

    protected function topIeExposure(int $businessId, Carbon $startDate, Carbon $endDate): array
    {
        $items = [];
        $permissions = $this->ieViewPermissions();

        if (Schema::hasTable('ie_mrp_runs')) {
            $runs = DB::table('ie_mrp_runs as r')
                ->leftJoin('ie_boms as b', 'b.id', '=', 'r.bom_id')
                ->where('r.business_id', $businessId)
                ->whereBetween(DB::raw('date(r.created_at)'), [$startDate->toDateString(), $endDate->toDateString()])
                ->orderByDesc('r.created_at')
                ->limit(5)
                ->get(['r.id', 'r.run_code', 'r.planned_quantity', 'r.shortage_analysis', 'b.name as bom_name']);

            foreach ($runs as $run) {
                $analysis = json_decode((string) ($run->shortage_analysis ?? ''), true);
                $shortageCount = is_array($analysis['shortages'] ?? null) ? count($analysis['shortages']) : 0;
                $shortageValue = (float) ($analysis['totals']['shortage_value'] ?? 0);
                $label = (string) ($run->run_code ?: 'MRP #'.$run->id);
                if (! empty($run->bom_name)) {
                    $label .= ' — '.$run->bom_name;
                }

                $items[] = [
                    'label' => $label,
                    'value' => $shortageValue > 0 ? $shortageValue : $shortageCount,
                    'currency' => $shortageValue > 0,
                    'url' => url('/industrial-engineering/mrp'),
                    'permissions' => $permissions,
                    'actions' => [
                        [
                            'label' => __('managementtools::lang.view_details'),
                            'url' => url('/industrial-engineering/mrp'),
                            'permissions' => $permissions,
                        ],
                        [
                            'label' => __('managementtools::lang.open_ie_dashboard'),
                            'url' => url('/industrial-engineering/dashboard'),
                            'permissions' => $permissions,
                        ],
                    ],
                ];
            }
        }

        if (empty($items) && Schema::hasTable('ie_cost_scenarios')) {
            $scenarios = DB::table('ie_cost_scenarios')
                ->where('business_id', $businessId)
                ->whereNull('deleted_at')
                ->orderByDesc('total_cost')
                ->limit(5)
                ->get(['id', 'scenario_code', 'name', 'total_cost']);

            foreach ($scenarios as $scenario) {
                $items[] = [
                    'label' => (string) (($scenario->name ?: $scenario->scenario_code) ?: '-'),
                    'value' => (float) $scenario->total_cost,
                    'currency' => true,
                    'url' => url('/industrial-engineering/dashboard'),
                    'permissions' => $permissions,
                    'actions' => [
                        [
                            'label' => __('managementtools::lang.view_details'),
                            'url' => url('/industrial-engineering/dashboard'),
                            'permissions' => $permissions,
                        ],
                    ],
                ];
            }
        }

        return $items;
    }

    protected function countManufacturingRecipes(int $businessId): int
    {
        if (! Schema::hasTable('mfg_recipes')) {
            return 0;
        }

        $query = DB::table('mfg_recipes');

        if (Schema::hasColumn('mfg_recipes', 'business_id')) {
            return (int) $query->where('business_id', $businessId)->count();
        }

        if (! Schema::hasTable('products') || ! Schema::hasColumn('products', 'business_id')) {
            return (int) $query->count();
        }

        return (int) $query
            ->join('products as p', 'p.id', '=', 'mfg_recipes.product_id')
            ->where('p.business_id', $businessId)
            ->count();
    }

    protected function routeIfExists(string $name): ?string
    {
        return Route::has($name) ? route($name) : null;
    }
}