<?php

namespace App\Http\Controllers\Api;

use App\Http\Controllers\Controller;
use App\Models\Company;
use App\Models\Employee;
use App\Models\Department;
use App\Models\Expense;
use App\Models\MonthlyCompanyFinancial;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Carbon\Carbon;

class CompanyDashboardController extends Controller
{
    public function index(Request $request)
    {
        $user    = $request->user();
        $company = Company::find($user->company_id)?->fresh();

        if (!$company) {
            return response()->json(['message' => 'User not associated with a company'], 403);
        }

        $range     = (int) $request->get('range', 90);
        $endDate   = Carbon::now();
        $startDate = Carbon::now()->subDays($range);
        $prevStart = Carbon::now()->subDays($range * 2);
        $prevEnd   = Carbon::now()->subDays($range);

        // ── Get financials for the selected date range (not just current month) ──
        $currentRevenue     = $this->sumRevenue($company->id, $startDate, $endDate);
        $currentExpenses    = $this->sumExpenses($company->id, $startDate, $endDate);
        $currentNetProfit   = $currentRevenue - $currentExpenses;
        $availableProfit    = $currentNetProfit;

        // ── Previous period for comparison ─
        $prevRevenue    = $this->sumRevenue($company->id, $prevStart, $prevEnd);
        $prevExpenses   = $this->sumExpenses($company->id, $prevStart, $prevEnd);
        $prevNetProfit  = $prevRevenue - $prevExpenses;

        $profitChange   = $prevNetProfit > 0
            ? round((($currentNetProfit - $prevNetProfit) / $prevNetProfit) * 100, 1)
            : 0;

        $profitMargin   = $currentRevenue > 0
            ? round(($currentNetProfit / $currentRevenue) * 100, 1)
            : 0;

        // ── Employees ──────────────────────────────────────────────────────────────
        $activeEmp = $company->employees()->where('status', 'active')->count();
        $prevEmp   = $company->employees()->where('status', 'active')
                             ->where('hire_date', '<', $prevEnd)->count();
        $avgSalary = $company->employees()->where('status', 'active')->avg('salary') ?? 0;

        // ── Cash & Assets ──────────────────────────────────────────────────────────
        $fin = DB::table('companies')->where('id', $company->id)->first();

        // Helper: read nullable decimal column — NULL means "never set", 0 means "set to 0"
        $col = fn(string $c) => isset($fin->$c) && $fin->$c !== null ? (float)$fin->$c : null;

        $cashBalance = $col('cash_balance') ?? 0.0;

        // Sum active company_assets (real asset records) first, fall back to manual field
        $assetSum = 0.0;
        try {
            $assetSum = (float) DB::table('company_assets')
                ->where('company_id', $company->id)
                ->where('status', 'active')
                ->sum('current_value');
        } catch (\Exception $e) {}

        $totalAssets = $assetSum; // only real uploaded assets; 0 if none recorded

        $totalLiabilities = $col('total_liabilities') ?? 0.0;
        $runwayDays       = $fin->runway_days ?? ($currentExpenses > 0 ? max(0, (int)($cashBalance / ($currentExpenses / 30))) : 0);
        $creditScore      = $fin->credit_score ?? 0;

        // ── Financial Health (real calculations from actual data) ──────────────
        $totalEquity      = max(0.01, $totalAssets - $totalLiabilities);
        $debtToEquity     = $totalEquity > 0
            ? round($totalLiabilities / $totalEquity, 2)
            : 0;
        $liquidityRatio   = $currentExpenses > 0
            ? round($cashBalance / ($currentExpenses / 30), 2)
            : ($cashBalance > 0 ? 99.99 : 0);
        $operatingMargin  = $currentRevenue > 0
            ? round(($currentNetProfit / $currentRevenue) * 100, 1)
            : 0;

        // ── KPI changes ───────────────────────────────────────────
        $revenueChange  = $prevRevenue  > 0 ? (($currentRevenue  - $prevRevenue)  / $prevRevenue)  * 100 : 0;
        $expensesChange = $prevExpenses > 0 ? (($currentExpenses - $prevExpenses) / $prevExpenses) * 100 : 0;
        $empChange      = $prevEmp      > 0 ? (($activeEmp       - $prevEmp)      / $prevEmp)       * 100 : 0;

        $stats = [
            'companyName'      => $company->name,
            'netProfit'        => $currentNetProfit,
            'totalRevenue'     => $currentRevenue,
            'revenue'          => $currentRevenue,
            'expenses'         => $currentExpenses,
            'totalExpenses'    => $currentExpenses,
            'profitMargin'     => $profitMargin,
            'profitChange'     => $profitChange,
            'availableProfit'  => $availableProfit,
            'loanDeductions'   => 0,
            'profitSwept'      => 0,
            'expensesChange'   => round($expensesChange, 2),
            'cashBalance'      => $cashBalance,
            'cashChange'       => 0,
            'totalAssets'      => $totalAssets,
            'assetsChange'     => 0,
            'totalLiabilities' => $totalLiabilities,
            'activeEmployees'  => $activeEmp,
            'employeesChange'  => round($empChange, 2),
            'departments'      => $company->departments()->count(),
            'totalDepartments' => $company->departments()->count(),
            'totalUsers'       => $company->users()->count(),
            'avgSalary'        => round($avgSalary),
            'creditScore'      => $creditScore,
            'runwayDays'       => $runwayDays,
            'liquidityRatio'   => round($liquidityRatio, 2),
            'debtToEquity'     => round($debtToEquity, 3),
            'healthStatus'     => $company->health_status ?? 'good',
        ];

        return response()->json([
            'stats'       => $stats,
            'revenueData' => $this->getRevenueTrend($company->id),
            'expenseData' => $this->getExpenseBreakdown($company->id, $startDate, $endDate),
            'assetData'   => $this->getAssetDistribution($company),
            'deptData'    => $this->getDeptHeadcount($company->id),
            'payrollData' => $this->getPayrollTrend($company->id),
        ]);
    }

    // ── Revenue: sum receivable invoices (money coming in) if table exists, else deterministic fallback ──
    private function sumRevenue(int $companyId, Carbon $from, Carbon $to): float
    {
        try {
            $sum = DB::table('invoices')
                ->where('company_id', $companyId)
                ->where('type', 'receivable')
                ->whereBetween('invoice_date', [$from, $to])
                ->whereIn('status', ['sent', 'viewed', 'partial', 'paid'])
                ->sum('total_amount');
            return (float) ($sum ?? 0);
        } catch (\Exception $e) {
            return 0.0;
        }
    }

    private function sumExpenses(int $companyId, Carbon $from, Carbon $to): float
    {
        $baseExpenses = (float) Expense::where('company_id', $companyId)
            ->whereBetween('expense_date', [$from, $to])
            ->whereNotIn('status', ['rejected', 'cancelled'])
            ->sum('amount');

        // Add payable invoices to expenses (money going out)
        $payableInvoices = (float) DB::table('invoices')
            ->where('company_id', $companyId)
            ->where('type', 'payable')
            ->whereBetween('invoice_date', [$from, $to])
            ->whereIn('status', ['sent', 'viewed', 'partial', 'paid'])
            ->sum('total_amount');

        $loanDeductions = (float) DB::table('staff_loan_deductions')
            ->join('staff_loans', 'staff_loan_deductions.staff_loan_id', '=', 'staff_loans.id')
            ->where('staff_loans.company_id', $companyId)
            ->whereBetween('staff_loan_deductions.due_date', [$from, $to])
            ->where('staff_loan_deductions.status', 'deducted')
            ->sum('staff_loan_deductions.deduction_amount');

        return $baseExpenses + $payableInvoices + $loanDeductions;
    }

    private function deterministicRand(int $seed, int $min, int $max): int
    {
        srand($seed + date('Ym'));
        return rand($min, $max);
    }

    private function getRevenueTrend(int $companyId): array
    {
        $months = ['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'];
        $data   = [];

        for ($i = 5; $i >= 0; $i--) {
            $month      = Carbon::now()->subMonths($i);
            $yearMonth  = $month->format('Y-m');

            // Recalculate monthly financials from dated tables (invoices, expenses, loan deductions)
            $mfin = MonthlyCompanyFinancial::recalculateMonth($companyId, $yearMonth);

            $data[] = [
                'month'    => $months[$month->month - 1],
                'revenue'  => (int) $mfin->revenue,
                'expenses' => (int) $mfin->expenses,
                'profit'   => (int) $mfin->net_profit,
            ];
        }

        return $data;
    }

    private function getExpenseBreakdown(int $companyId, Carbon $from, Carbon $to): array
    {
        try {
            $rows = Expense::where('expenses.company_id', $companyId)
                ->whereBetween('expenses.expense_date', [$from, $to])
                ->whereNotIn('expenses.status', ['rejected', 'cancelled'])
                ->join('expense_categories', 'expenses.expense_category_id', '=', 'expense_categories.id')
                ->select('expense_categories.name as cat_name', 'expense_categories.color', DB::raw('SUM(expenses.amount) as total'))
                ->groupBy('cat_name', 'expense_categories.color')
                ->orderByDesc('total')
                ->limit(8)
                ->get();
        } catch (\Exception $e) {
            $rows = collect();
        }

        if ($rows->isEmpty()) {
            // Fallback: show uncategorised total
            $total = $this->sumExpenses($companyId, $from, $to);
            if ($total > 0) {
                return [['name' => 'Expenses', 'value' => $total, 'color' => '#2563EB']];
            }
            return [];
        }

        return $rows->map(fn($r) => [
            'name'  => $r->cat_name,
            'value' => (float) $r->total,
            'color' => $r->color ?? '#2563EB',
        ])->toArray();
    }

    private function getAssetDistribution(Company $company): array
    {
        try {
            $rows = DB::table('company_assets')
                ->where('company_id', $company->id)
                ->where('status', 'active')
                ->select('category', DB::raw('SUM(current_value) as current_val'), DB::raw('SUM(purchase_value) as purchase_val'))
                ->groupBy('category')
                ->orderByDesc('current_val')
                ->get();
        } catch (\Exception $e) {
            $rows = collect();
        }

        if ($rows->isEmpty()) {
            return [];
        }

        return $rows->map(fn($r) => [
            'category' => ucfirst(str_replace('_', ' ', $r->category)),
            'current'  => (int) $r->current_val,
            'target'   => (int) $r->purchase_val,
        ])->toArray();
    }

    private function getDeptHeadcount(int $companyId): array
    {
        return Department::where('departments.company_id', $companyId)
            ->leftJoin('employees', function ($j) {
                $j->on('employees.department_id', '=', 'departments.id')
                  ->where('employees.status', 'active');
            })
            ->select('departments.name', DB::raw('COUNT(employees.id) as count'))
            ->groupBy('departments.id', 'departments.name')
            ->orderByDesc('count')
            ->get()
            ->map(fn($d) => [
                'name'  => strlen($d->name) > 18 ? substr($d->name, 0, 16).'…' : $d->name,
                'count' => (int) $d->count,
            ])
            ->toArray();
    }

    private function getPayrollTrend(int $companyId): array
    {
        $months      = ['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'];
        $basePayroll = (float) Employee::where('company_id', $companyId)->where('status','active')->sum('salary') / 12;
        $data        = [];
        for ($i = 5; $i >= 0; $i--) {
            $month  = Carbon::now()->subMonths($i);
            $data[] = [
                'month'  => $months[$month->month - 1],
                'amount' => (int) $basePayroll,
            ];
        }
        return $data;
    }
}
