<?php

namespace App\Http\Controllers\Api;

use App\Http\Controllers\Controller;
use App\Models\Company;
use App\Models\Employee;
use App\Models\IntercompanyLoan;
use App\Models\ProfitSweepRule;
use App\Models\ProfitSweepTransaction;
use App\Models\Expense;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Carbon\Carbon;

class GroupDashboardController extends Controller
{
    public function index(Request $request)
    {
        $user = $request->user();
        if (!$user->hasRole('Super Admin')) {
            return response()->json(['message' => 'Unauthorized'], 403);
        }

        // ── Batch preloads (no N+1 queries, no rand()) ─────────
        $companies  = Company::withCount(['employees', 'departments'])->get();
        $companyIds = $companies->pluck('id')->toArray();

        // Real asset sums per company
        $assetSums = DB::table('company_assets')
            ->whereIn('company_id', $companyIds)
            ->where('status', 'active')
            ->select('company_id', DB::raw('SUM(current_value) as total'))
            ->groupBy('company_id')
            ->pluck('total', 'company_id');

        // Real asset detail records per company
        $assetRecords = DB::table('company_assets')
            ->whereIn('company_id', $companyIds)
            ->where('status', 'active')
            ->select('company_id', 'name', 'category', 'current_value')
            ->orderByDesc('current_value')
            ->get()
            ->groupBy('company_id');

        // Financial metrics (credit_score, runway_days) per company
        $finMetrics = collect();
        try {
            $finMetrics = DB::table('company_financial_metrics')
                ->whereIn('company_id', $companyIds)
                ->get()
                ->keyBy('company_id');
        } catch (\Exception $e) {}

        // Latest cash balance per company from health metrics
        $latestCash = collect();
        try {
            $latestCash = DB::table('company_health_metrics')
                ->whereIn('company_id', $companyIds)
                ->select('company_id', DB::raw('MAX(cash_balance) as cash'))
                ->groupBy('company_id')
                ->pluck('cash', 'company_id');
        } catch (\Exception $e) {}

        // ── Current month financials per company ──────────────────
        $currentMonth = now()->format('Y-m');
        $monthlyFins = collect();
        try {
            $monthlyFins = DB::table('monthly_company_financials')
                ->whereIn('company_id', $companyIds)
                ->where('year_month', $currentMonth)
                ->get()
                ->keyBy('company_id');
        } catch (\Exception $e) {}

        // ── Map companies ───────────────────────────────────────
        $mapped = $companies->map(function ($c) use ($assetSums, $assetRecords, $finMetrics, $latestCash, $monthlyFins) {
            $assetSum     = (float)($assetSums[$c->id] ?? 0);
            $storedAssets = is_numeric($c->getAttributes()['total_assets'] ?? null) ? (float)$c->getAttributes()['total_assets'] : 0;
            $assets       = $assetSum > 0 ? $assetSum : $storedAssets;

            $liabilities  = is_numeric($c->getAttributes()['total_liabilities'] ?? null) ? (float)$c->getAttributes()['total_liabilities'] : 0;

            // Use monthly financials if available, fall back to company table
            $mfin = $monthlyFins->get($c->id);
            if ($mfin) {
                $revenue  = (float) $mfin->revenue;
                $expenses = (float) $mfin->expenses;
                $profit   = (float) $mfin->net_profit;
            } else {
                $revenue  = is_numeric($c->getAttributes()['revenue'] ?? null)         ? (float)$c->getAttributes()['revenue']         : 0;
                $expenses = is_numeric($c->getAttributes()['expenses_manual'] ?? null) ? (float)$c->getAttributes()['expenses_manual'] : 0;
                $profit   = $revenue - $expenses;
            }
            $cash         = (float)($latestCash[$c->id] ?? 0);

            $fin          = $finMetrics[$c->id] ?? null;
            $creditScore  = (int)($fin->credit_score ?? 0);
            $runwayDays   = (int)($fin->runway_days  ?? 0);

            $healthMap    = ['excellent'=>95,'good'=>80,'fair'=>60,'poor'=>40,'critical'=>20];
            $healthScore  = $healthMap[$c->health_status ?? 'good'] ?? 75;
            $healthStatus = $c->health_status ?? 'good';

            $companyAssets = ($assetRecords->get($c->id) ?? collect())->map(fn($a) => [
                'name'     => $a->name,
                'category' => $a->category ?? 'General',
                'value'    => (float) $a->current_value,
            ])->values()->toArray();

            return [
                'id'          => $c->id,
                'name'        => $c->name,
                'industry'    => $c->industry ?? 'General',
                'revenue'     => round($revenue),
                'expenses'    => round($expenses),
                'profit'      => round($profit),
                'cash'        => round($cash),
                'totalAssets' => round($assets),
                'totalLiab'   => round($liabilities),
                'employees'   => $c->employees_count,
                'departments' => $c->departments_count,
                'creditScore' => $creditScore,
                'runwayDays'  => $runwayDays,
                'healthScore' => $healthScore,
                'healthStatus'=> $healthStatus,
                'assets'      => $companyAssets,
            ];
        });

        $totalRevenue = $mapped->sum('revenue');
        $totalProfit  = $mapped->sum('profit');
        $totalAssets  = $mapped->sum('totalAssets');
        $totalCash    = $mapped->sum('cash');

        // ── Loans ───────────────────────────────────────────────
        $loans = IntercompanyLoan::with(['borrower', 'lender'])
            ->latest()->take(10)->get()
            ->map(fn($l) => [
                'id'            => $l->id,
                'company'       => $l->borrower?->name ?? 'Unknown',
                'lender'        => $l->lender?->name   ?? 'Holding',
                'amount'        => $l->principal_amount,
                'outstanding'   => $l->outstanding_balance,
                'interest_rate' => $l->interest_rate,
                'term'          => $l->term_months,
                'purpose'       => $l->purpose,
                'status'        => $l->status,
            ]);

        // ── Profit Allocations ──────────────────────────────────
        $sweeps = ProfitSweepTransaction::with(['fromCompany','toCompany'])
            ->whereIn('status', ['completed','processed'])
            ->latest()->take(8)->get()
            ->map(fn($s) => [
                'id'             => $s->id,
                'company'        => $s->fromCompany?->name ?? 'Unknown',
                'destination'    => $s->toCompany?->name   ?? 'Holding',
                'amount'         => $s->actual_amount ?? $s->calculated_amount,
                'allocation_date'=> $s->sweep_date ? Carbon::parse($s->sweep_date)->format('d M Y') : '—',
                'status'         => $s->status,
            ]);

        // ── Revenue Trend — real data from company_health_metrics ─
        $revenueTrend  = [];
        $revenueGrowth = 0;
        try {
            $trendRows = DB::table('company_health_metrics')
                ->where('metric_date', '>=', Carbon::now()->subMonths(5)->startOfMonth())
                ->select(
                    DB::raw("DATE_FORMAT(metric_date, '%Y-%m') as ym"),
                    DB::raw("DATE_FORMAT(metric_date, '%b') as month"),
                    DB::raw('SUM(monthly_revenue) as revenue'),
                    DB::raw('SUM(monthly_expenses) as expenses')
                )
                ->groupBy('ym', 'month')
                ->orderBy('ym')
                ->get();

            $revenueTrend = $trendRows->map(fn($r) => [
                'month'    => $r->month,
                'revenue'  => (float)($r->revenue  ?? 0),
                'expenses' => (float)($r->expenses ?? 0),
                'profit'   => (float)(($r->revenue ?? 0) - ($r->expenses ?? 0)),
            ])->values()->toArray();

            if (count($revenueTrend) >= 2) {
                $first = $revenueTrend[0]['revenue'];
                $last  = end($revenueTrend)['revenue'];
                $revenueGrowth = $first > 0 ? round((($last - $first) / $first) * 100, 1) : 0;
            }
        } catch (\Exception $e) {}

        // ── Employee distribution ───────────────────────────────
        $empByCompany = $mapped->map(fn($c) => [
            'name'  => strlen($c['name']) > 20 ? substr($c['name'],0,18).'…' : $c['name'],
            'count' => $c['employees'],
        ])->sortByDesc('count')->values();

        return response()->json([
            'stats' => [
                'totalCompanies'  => $mapped->count(),
                'subsidiaries'    => $mapped->count(),
                'totalRevenue'    => $totalRevenue,
                'totalProfit'     => $totalProfit,
                'totalAssets'     => $totalAssets,
                'totalCash'       => $totalCash,
                'totalLiabilities'=> $mapped->sum('totalLiab'),
                'revenueGrowth'   => $revenueGrowth,
                'profitMargin'    => $totalRevenue > 0 ? round(($totalProfit/$totalRevenue)*100, 1) : 0,
                'totalEmployees'  => Employee::count(),
                'activeLoans'     => IntercompanyLoan::whereIn('status',['active','approved'])->count(),
                'pendingLoans'    => IntercompanyLoan::where('status','pending')->count(),
            ],
            'companies'         => $mapped->values(),
            'loanRequests'      => $loans,
            'profitAllocations' => $sweeps,
            'revenueTrend'      => $revenueTrend,
            'empByCompany'      => $empByCompany,
        ]);
    }
}
