<?php

namespace App\Services;

use App\Models\ActionItem;
use App\Models\Announcement;
use App\Models\AnnouncementView;
use App\Models\Asset;
use App\Models\AssetAssignment;
use App\Models\AttendanceRecord;
use App\Models\Branch;
use App\Models\Candidate;
use App\Models\Department;
use App\Models\Designation;
use App\Models\DocumentAcknowledgment;
use App\Models\Employee;
use App\Models\EmployeeContract;
use App\Models\EmployeeGoal;
use App\Models\EmployeeReview;
use App\Models\EmployeeTraining;
use App\Models\JobPosting;
use App\Models\LeaveApplication;
use App\Models\LeaveBalance;
use App\Models\Meeting;
use App\Models\PayrollEntry;
use App\Models\PayrollRun;
use App\Models\Resignation;
use App\Models\Termination;
use App\Models\TrainingSession;
use App\Models\TrainingSessionAttendance;
use App\Models\User;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;

class HrReportService
{
    public function __construct(private TaxReportService $taxReportService) {}

    private function formatReportDate(mixed $value): string
    {
        if ($value === null || $value === '') {
            return '—';
        }

        try {
            $date = $value instanceof Carbon ? $value : Carbon::parse($value);

            if ($date->year < 1900) {
                return '—';
            }

            return formatDateTime($date, false) ?? '—';
        } catch (\Throwable) {
            return '—';
        }
    }

    private function reportToday(): Carbon
    {
        return now()->startOfDay();
    }

    public function generate(string $category, array $filters = []): array
    {
        $filters = $this->normalizeFilters($filters);

        return match ($category) {
            'workforce' => $this->workforceReport($filters),
            'attendance' => $this->attendanceReport($filters),
            'leave' => $this->leaveReport($filters),
            'payroll' => $this->payrollReport($filters),
            'tax' => $this->taxReportService->generate($filters),
            'performance' => $this->performanceReport($filters),
            'recruitment' => $this->recruitmentReport($filters),
            'training' => $this->trainingReport($filters),
            'assets' => $this->assetsReport($filters),
            'compliance' => $this->complianceReport($filters),
            'engagement' => $this->engagementReport($filters),
            'meetings' => $this->meetingsReport($filters),
            default => throw new \InvalidArgumentException('Unknown report category: '.$category),
        };
    }

    private function companyIds(): array
    {
        return getCompanyAndUsersId();
    }

    private function normalizeFilters(array $filters): array
    {
        return [
            'branch_id' => ! empty($filters['branch_id']) ? (int) $filters['branch_id'] : null,
            'employee_id' => ! empty($filters['employee_id']) ? (int) $filters['employee_id'] : null,
            'start_date' => $this->normalizeFilterDate($filters['start_date'] ?? null),
            'end_date' => $this->normalizeFilterDate($filters['end_date'] ?? null),
        ];
    }

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

        try {
            return Carbon::parse($value)->toDateString();
        } catch (\Throwable) {
            return null;
        }
    }

    private function applyEmployeeRecordFilters($query, array $filters): void
    {
        if (! empty($filters['branch_id'])) {
            $query->where('branch_id', $filters['branch_id']);
        }

        if (! empty($filters['employee_id'])) {
            $query->where('user_id', $filters['employee_id']);
        }
    }

    private function filteredEmployeeIds(array $filters): ?array
    {
        if (empty($filters['branch_id']) && empty($filters['employee_id'])) {
            return null;
        }

        return Employee::whereIn('created_by', $this->companyIds())
            ->when(! empty($filters['branch_id']), fn ($query) => $query->where('branch_id', $filters['branch_id']))
            ->when(! empty($filters['employee_id']), fn ($query) => $query->where('user_id', $filters['employee_id']))
            ->pluck('user_id')
            ->map(fn ($id) => (int) $id)
            ->all();
    }

    private function applyEmployeeIdFilter($query, array $filters, string $column = 'employee_id'): void
    {
        $employeeIds = $this->filteredEmployeeIds($filters);
        if ($employeeIds === null) {
            return;
        }

        if ($employeeIds === []) {
            $query->whereRaw('1 = 0');

            return;
        }

        $query->whereIn($column, $employeeIds);
    }

    private function applyDateFilter($query, array $filters, string $column): void
    {
        if (! empty($filters['start_date'])) {
            $query->whereDate($column, '>=', $filters['start_date']);
        }

        if (! empty($filters['end_date'])) {
            $query->whereDate($column, '<=', $filters['end_date']);
        }
    }

    private function workforceReport(array $filters): array
    {
        $companyIds = $this->companyIds();
        $employees = Employee::whereIn('created_by', $companyIds)->with(['branch', 'department', 'designation', 'user']);
        $this->applyEmployeeRecordFilters($employees, $filters);
        $this->applyDateFilter($employees, $filters, 'date_of_joining');

        $total = (clone $employees)->count();
        $active = (clone $employees)->where('employee_status', 'active')->count();
        $probation = (clone $employees)->where('employee_status', 'probation')->count();

        $hireSince30 = $this->reportToday()->copy()->subDays(30)->toDateString();
        $hireSince90 = $this->reportToday()->copy()->subDays(90)->toDateString();

        $newHires30 = (clone $employees)
            ->whereNotNull('date_of_joining')
            ->where('date_of_joining', '>=', $hireSince30)
            ->count();
        $newHires90 = (clone $employees)
            ->whereNotNull('date_of_joining')
            ->where('date_of_joining', '>=', $hireSince90)
            ->count();

        $exitSince30 = $this->reportToday()->copy()->subDays(30)->toDateString();
        $resignations = Resignation::whereIn('created_by', $companyIds);
        $this->applyEmployeeIdFilter($resignations, $filters, 'employee_id');
        $this->applyDateFilter($resignations, $filters, 'resignation_date');

        $terminations = Termination::whereIn('created_by', $companyIds);
        $this->applyEmployeeIdFilter($terminations, $filters, 'employee_id');
        $this->applyDateFilter($terminations, $filters, 'termination_date');

        $exits30 = (clone $resignations)
            ->whereNotNull('resignation_date')
            ->where('resignation_date', '>=', $exitSince30)
            ->count()
            + (clone $terminations)
                ->whereNotNull('termination_date')
                ->where('termination_date', '>=', $exitSince30)
                ->count();

        $byBranch = (clone $employees)
            ->select('branch_id', DB::raw('count(*) as total'))
            ->groupBy('branch_id')
            ->with('branch:id,name')
            ->get()
            ->map(fn ($row) => [$row->branch?->name ?? 'Unassigned', (string) $row->total])
            ->values()
            ->all();

        $byDepartment = (clone $employees)
            ->select('department_id', DB::raw('count(*) as total'))
            ->groupBy('department_id')
            ->with('department:id,name')
            ->get()
            ->map(fn ($row) => [$row->department?->name ?? 'Unassigned', (string) $row->total])
            ->values()
            ->all();

        return [
            'summary' => [
                ['label' => 'Total Employees', 'value' => $total, 'format' => 'number'],
                ['label' => 'Active', 'value' => $active, 'format' => 'number'],
                ['label' => 'On Probation', 'value' => $probation, 'format' => 'number'],
                ['label' => 'New Hires (30 days)', 'value' => $newHires30, 'format' => 'number'],
                ['label' => 'New Hires (90 days)', 'value' => $newHires90, 'format' => 'number'],
                ['label' => 'Exits (30 days)', 'value' => $exits30, 'format' => 'number'],
                ['label' => 'Branches', 'value' => Branch::whereIn('created_by', $companyIds)->when(! empty($filters['branch_id']), fn ($query) => $query->whereKey($filters['branch_id']))->count(), 'format' => 'number'],
                ['label' => 'Departments', 'value' => Department::whereIn('created_by', $companyIds)->when(! empty($filters['branch_id']), fn ($query) => $query->where('branch_id', $filters['branch_id']))->count(), 'format' => 'number'],
                ['label' => 'Designations', 'value' => Designation::whereIn('created_by', $companyIds)->when(! empty($filters['branch_id']), fn ($query) => $query->whereHas('department', fn ($department) => $department->where('branch_id', $filters['branch_id'])))->count(), 'format' => 'number'],
            ],
            'tables' => [
                [
                    'title' => 'Headcount by Branch',
                    'columns' => ['Branch', 'Employees'],
                    'rows' => $byBranch,
                ],
                [
                    'title' => 'Headcount by Department',
                    'columns' => ['Department', 'Employees'],
                    'rows' => $byDepartment,
                ],
            ],
        ];
    }

    private function attendanceReport(array $filters): array
    {
        $companyIds = $this->companyIds();
        $defaultStart = $this->reportToday()->copy()->startOfMonth()->toDateString();
        $defaultEnd = $this->reportToday()->copy()->endOfMonth()->toDateString();

        $records = AttendanceRecord::whereIn('created_by', $companyIds);
        $this->applyEmployeeIdFilter($records, $filters, 'employee_id');

        if (! empty($filters['start_date']) || ! empty($filters['end_date'])) {
            $this->applyDateFilter($records, $filters, 'date');
        } else {
            $records->whereBetween('date', [$defaultStart, $defaultEnd]);
        }

        $present = (clone $records)->where('status', 'present')->count();
        $absent = (clone $records)->where('status', 'absent')->count();
        $late = (clone $records)->where('status', 'late')->count();
        $halfDay = (clone $records)->where('status', 'half_day')->count();
        $onLeave = (clone $records)->where('status', 'on_leave')->count();
        $overtimeAmount = (clone $records)->sum('overtime_amount');

        $byStatus = (clone $records)
            ->select('status', DB::raw('count(*) as total'))
            ->groupBy('status')
            ->pluck('total', 'status')
            ->map(fn ($count, $status) => [ucfirst(str_replace('_', ' ', $status)), (string) $count])
            ->values()
            ->all();

        return [
            'summary' => [
                ['label' => 'Records This Month', 'value' => (clone $records)->count(), 'format' => 'number'],
                ['label' => 'Present', 'value' => $present, 'format' => 'number'],
                ['label' => 'Absent', 'value' => $absent, 'format' => 'number'],
                ['label' => 'Late', 'value' => $late, 'format' => 'number'],
                ['label' => 'Half Day', 'value' => $halfDay, 'format' => 'number'],
                ['label' => 'On Leave', 'value' => $onLeave, 'format' => 'number'],
                ['label' => 'Overtime Amount', 'value' => $overtimeAmount, 'format' => 'currency'],
            ],
            'tables' => [
                [
                    'title' => 'Attendance Status (Current Month)',
                    'columns' => ['Status', 'Count'],
                    'rows' => $byStatus,
                ],
            ],
        ];
    }

    private function leaveReport(array $filters): array
    {
        $companyIds = $this->companyIds();

        $reportYear = $this->reportToday()->year;

        $applications = LeaveApplication::whereIn('created_by', $companyIds);
        $this->applyEmployeeIdFilter($applications, $filters, 'employee_id');
        $this->applyDateFilter($applications, $filters, 'start_date');

        $pending = (clone $applications)->where('status', 'pending')->count();
        $approvedYtd = (clone $applications)
            ->where('status', 'approved')
            ->whereYear('start_date', $reportYear)
            ->count();
        $rejectedYtd = (clone $applications)
            ->where('status', 'rejected')
            ->whereYear('created_at', $reportYear)
            ->count();

        $daysUsed = (clone $applications)
            ->where('status', 'approved')
            ->whereYear('start_date', $reportYear)
            ->sum('total_days');

        $balances = LeaveBalance::whereIn('created_by', $companyIds);
        $this->applyEmployeeIdFilter($balances, $filters, 'employee_id');

        $balanceTotals = (clone $balances)
            ->selectRaw('SUM(allocated_days) as allocated, SUM(used_days) as used, SUM(remaining_days) as remaining')
            ->first();

        $byStatus = (clone $applications)
            ->select('status', DB::raw('count(*) as total'))
            ->groupBy('status')
            ->pluck('total', 'status')
            ->map(fn ($count, $status) => [ucfirst($status), (string) $count])
            ->values()
            ->all();

        return [
            'summary' => [
                ['label' => 'Pending Approvals', 'value' => $pending, 'format' => 'number'],
                ['label' => 'Approved (YTD)', 'value' => $approvedYtd, 'format' => 'number'],
                ['label' => 'Rejected (YTD)', 'value' => $rejectedYtd, 'format' => 'number'],
                ['label' => 'Leave Days Used (YTD)', 'value' => (float) $daysUsed, 'format' => 'number'],
                ['label' => 'Total Allocated Balance', 'value' => (float) ($balanceTotals->allocated ?? 0), 'format' => 'number'],
                ['label' => 'Total Remaining Balance', 'value' => (float) ($balanceTotals->remaining ?? 0), 'format' => 'number'],
            ],
            'tables' => [
                [
                    'title' => 'Leave Applications by Status',
                    'columns' => ['Status', 'Applications'],
                    'rows' => $byStatus,
                ],
            ],
        ];
    }

    private function payrollReport(array $filters): array
    {
        $companyIds = $this->companyIds();

        $runs = PayrollRun::whereIn('created_by', $companyIds);
        $this->applyDateFilter($runs, $filters, 'pay_period_end');

        $lastRun = (clone $runs)->orderByDesc('pay_period_end')->first();
        $completedRuns = (clone $runs)->where('status', 'completed')->count();
        $draftRuns = (clone $runs)->where('status', 'draft')->count();
        $filteredRunIds = (clone $runs)->pluck('id')->all();

        $entriesQuery = PayrollEntry::whereIn('created_by', $companyIds);
        $this->applyEmployeeIdFilter($entriesQuery, $filters, 'employee_id');
        if (! empty($filters['start_date']) || ! empty($filters['end_date'])) {
            if ($filteredRunIds === []) {
                $entriesQuery->whereRaw('1 = 0');
            } else {
                $entriesQuery->whereIn('payroll_run_id', $filteredRunIds);
            }
        }

        $reportMonth = $this->reportToday();
        $entriesThisMonth = (clone $entriesQuery)
            ->when(
                empty($filters['start_date']) && empty($filters['end_date']),
                fn ($query) => $query
                    ->whereMonth('created_at', $reportMonth->month)
                    ->whereYear('created_at', $reportMonth->year)
            )
            ->count();

        $gross = $lastRun?->total_gross_pay ?? 0;
        $net = $lastRun?->total_net_pay ?? 0;
        $deductions = $lastRun?->total_deductions ?? 0;

        $byDepartment = PayrollEntry::query()
            ->when($lastRun, fn ($q) => $q->where('payroll_run_id', $lastRun->id))
            ->whereIn('payroll_entries.created_by', $companyIds)
            ->join('employees', 'payroll_entries.employee_id', '=', 'employees.user_id')
            ->join('departments', 'employees.department_id', '=', 'departments.id')
            ->when(! empty($filters['branch_id']), fn ($query) => $query->where('employees.branch_id', $filters['branch_id']))
            ->when(! empty($filters['employee_id']), fn ($query) => $query->where('employees.user_id', $filters['employee_id']))
            ->groupBy('departments.name')
            ->selectRaw('departments.name as department, SUM(payroll_entries.net_pay) as net_total')
            ->get()
            ->map(fn ($row) => [$row->department, formatCurrency((float) $row->net_total)])
            ->values()
            ->all();

        return [
            'summary' => [
                ['label' => 'Completed Payroll Runs', 'value' => $completedRuns, 'format' => 'number'],
                ['label' => 'Draft Payroll Runs', 'value' => $draftRuns, 'format' => 'number'],
                ['label' => 'Payroll Entries This Month', 'value' => $entriesThisMonth, 'format' => 'number'],
                ['label' => 'Last Run Gross Pay', 'value' => $gross, 'format' => 'currency'],
                ['label' => 'Last Run Deductions', 'value' => $deductions, 'format' => 'currency'],
                ['label' => 'Last Run Net Pay', 'value' => $net, 'format' => 'currency'],
            ],
            'tables' => [
                [
                    'title' => $lastRun
                        ? 'Net Pay by Department ('.$lastRun->title.')'
                        : 'Net Pay by Department',
                    'columns' => ['Department', 'Net Pay'],
                    'rows' => $byDepartment,
                ],
            ],
        ];
    }

    private function performanceReport(array $filters): array
    {
        $companyIds = $this->companyIds();

        $reviews = EmployeeReview::whereIn('created_by', $companyIds);
        $this->applyEmployeeIdFilter($reviews, $filters, 'employee_id');
        $this->applyDateFilter($reviews, $filters, 'review_date');
        $completed = (clone $reviews)->where('status', 'completed')->count();
        $inProgress = (clone $reviews)->where('status', 'in_progress')->count();
        $avgRating = (clone $reviews)->where('status', 'completed')->avg('overall_rating');

        $goals = EmployeeGoal::whereIn('created_by', $companyIds);
        $this->applyEmployeeIdFilter($goals, $filters, 'employee_id');
        $this->applyDateFilter($goals, $filters, 'start_date');
        $goalsCompleted = (clone $goals)->where('status', 'completed')->count();
        $goalsInProgress = (clone $goals)->where('status', 'in_progress')->count();

        $byStatus = (clone $reviews)
            ->select('status', DB::raw('count(*) as total'))
            ->groupBy('status')
            ->pluck('total', 'status')
            ->map(fn ($count, $status) => [ucfirst(str_replace('_', ' ', $status)), (string) $count])
            ->values()
            ->all();

        return [
            'summary' => [
                ['label' => 'Reviews Completed', 'value' => $completed, 'format' => 'number'],
                ['label' => 'Reviews In Progress', 'value' => $inProgress, 'format' => 'number'],
                ['label' => 'Average Rating', 'value' => round((float) $avgRating, 1), 'format' => 'number'],
                ['label' => 'Goals Completed', 'value' => $goalsCompleted, 'format' => 'number'],
                ['label' => 'Goals In Progress', 'value' => $goalsInProgress, 'format' => 'number'],
            ],
            'tables' => [
                [
                    'title' => 'Employee Reviews by Status',
                    'columns' => ['Status', 'Count'],
                    'rows' => $byStatus,
                ],
            ],
        ];
    }

    private function recruitmentReport(array $filters): array
    {
        $companyIds = $this->companyIds();

        $candidates = Candidate::whereIn('created_by', $companyIds);
        if (! empty($filters['branch_id'])) {
            $candidates->where('branch_id', $filters['branch_id']);
        }
        $this->applyDateFilter($candidates, $filters, 'application_date');

        $jobPostings = JobPosting::whereIn('created_by', $companyIds);
        if (! empty($filters['branch_id'])) {
            $jobPostings->where('branch_id', $filters['branch_id']);
        }
        $this->applyDateFilter($jobPostings, $filters, 'start_date');

        $openPostings = (clone $jobPostings)
            ->where(function ($q) {
                $q->where('is_published', true)->orWhereIn('status', ['Open', 'Published', 'active']);
            })
            ->count();
        $totalCandidates = (clone $candidates)->count();

        $byStatus = (clone $candidates)
            ->select('status', DB::raw('count(*) as total'))
            ->groupBy('status')
            ->pluck('total', 'status')
            ->map(fn ($count, $status) => [$status, (string) $count])
            ->values()
            ->all();

        $offers = (clone $candidates)->where('status', 'Offer')->count();

        return [
            'summary' => [
                ['label' => 'Open Job Postings', 'value' => $openPostings, 'format' => 'number'],
                ['label' => 'Total Candidates', 'value' => $totalCandidates, 'format' => 'number'],
                ['label' => 'Candidates in Offer Stage', 'value' => $offers, 'format' => 'number'],
                ['label' => 'In Interview', 'value' => (clone $candidates)->where('status', 'Interview')->count(), 'format' => 'number'],
            ],
            'tables' => [
                [
                    'title' => 'Candidates by Status',
                    'columns' => ['Status', 'Candidates'],
                    'rows' => $byStatus,
                ],
            ],
        ];
    }

    private function trainingReport(array $filters): array
    {
        $companyIds = $this->companyIds();

        $sessions = TrainingSession::whereIn('created_by', $companyIds);
        $this->applyDateFilter($sessions, $filters, 'start_date');
        $completed = (clone $sessions)->where('status', 'completed')->count();
        $scheduled = (clone $sessions)->where('status', 'scheduled')->count();

        $enrollmentsQuery = EmployeeTraining::whereIn('created_by', $companyIds);
        $this->applyEmployeeIdFilter($enrollmentsQuery, $filters, 'employee_id');
        $this->applyDateFilter($enrollmentsQuery, $filters, 'assigned_date');
        $enrollments = (clone $enrollmentsQuery)->count();
        $attendance = TrainingSessionAttendance::query()
            ->join('training_sessions', 'training_session_attendance.training_session_id', '=', 'training_sessions.id')
            ->whereIn('training_sessions.created_by', $companyIds);
        $this->applyEmployeeIdFilter($attendance, $filters, 'training_session_attendance.employee_id');
        $this->applyDateFilter($attendance, $filters, 'training_sessions.start_date');
        $present = (clone $attendance)->where('training_session_attendance.is_present', true)->count();
        $attendanceRecords = (clone $attendance)->count();

        $rate = $attendanceRecords > 0 ? round(($present / $attendanceRecords) * 100, 1) : 0;

        return [
            'summary' => [
                ['label' => 'Training Sessions Completed', 'value' => $completed, 'format' => 'number'],
                ['label' => 'Scheduled Sessions', 'value' => $scheduled, 'format' => 'number'],
                ['label' => 'Training Enrollments', 'value' => $enrollments, 'format' => 'number'],
                ['label' => 'Attendance Records', 'value' => $attendanceRecords, 'format' => 'number'],
                ['label' => 'Attendance Rate', 'value' => $rate, 'format' => 'percent'],
            ],
            'tables' => [],
        ];
    }

    private function assetsReport(array $filters): array
    {
        $companyIds = $this->companyIds();

        $assets = Asset::whereIn('created_by', $companyIds);
        $this->applyDateFilter($assets, $filters, 'purchase_date');

        $employeeIds = $this->filteredEmployeeIds($filters);
        if ($employeeIds !== null) {
            if ($employeeIds === []) {
                $assets->whereRaw('1 = 0');
            } else {
                $assets->whereIn('id', AssetAssignment::whereIn('employee_id', $employeeIds)->select('asset_id'));
            }
        }

        $total = (clone $assets)->count();
        $assigned = (clone $assets)->where('status', 'assigned')->count();
        $available = (clone $assets)->where('status', 'available')->count();
        $purchaseValue = (clone $assets)->sum('purchase_cost');

        $activeAssignmentsQuery = AssetAssignment::query()
            ->join('assets', 'asset_assignments.asset_id', '=', 'assets.id')
            ->whereIn('assets.created_by', $companyIds)
            ->whereNull('asset_assignments.checkin_date');
        if ($employeeIds !== null) {
            if ($employeeIds === []) {
                $activeAssignmentsQuery->whereRaw('1 = 0');
            } else {
                $activeAssignmentsQuery->whereIn('asset_assignments.employee_id', $employeeIds);
            }
        }
        $this->applyDateFilter($activeAssignmentsQuery, $filters, 'asset_assignments.checkout_date');

        $activeAssignments = (clone $activeAssignmentsQuery)->count();

        $byStatus = (clone $assets)
            ->select('status', DB::raw('count(*) as total'))
            ->groupBy('status')
            ->pluck('total', 'status')
            ->map(fn ($count, $status) => [ucfirst(str_replace('_', ' ', $status)), (string) $count])
            ->values()
            ->all();

        return [
            'summary' => [
                ['label' => 'Total Assets', 'value' => $total, 'format' => 'number'],
                ['label' => 'Assigned', 'value' => $assigned, 'format' => 'number'],
                ['label' => 'Available', 'value' => $available, 'format' => 'number'],
                ['label' => 'Active Assignments', 'value' => $activeAssignments, 'format' => 'number'],
                ['label' => 'Total Purchase Value', 'value' => $purchaseValue, 'format' => 'currency'],
            ],
            'tables' => [
                [
                    'title' => 'Assets by Status',
                    'columns' => ['Status', 'Count'],
                    'rows' => $byStatus,
                ],
            ],
        ];
    }

    private function complianceReport(array $filters): array
    {
        $companyIds = $this->companyIds();
        $base = $this->reportToday();
        $today = $base->toDateString();
        $in30 = $base->copy()->addDays(30)->toDateString();
        $in60 = $base->copy()->addDays(60)->toDateString();

        $contracts = EmployeeContract::whereIn('created_by', $companyIds)
            ->whereNotNull('end_date')
            ->where('end_date', '>=', $today);
        $this->applyEmployeeIdFilter($contracts, $filters, 'employee_id');
        $this->applyDateFilter($contracts, $filters, 'end_date');
        $expiring30 = (clone $contracts)->whereBetween('end_date', [$today, $in30])->count();
        $expiring60 = (clone $contracts)->whereBetween('end_date', [$today, $in60])->count();

        $acknowledgments = DocumentAcknowledgment::whereIn('created_by', $companyIds)
            ->where('status', 'Pending');
        $this->applyEmployeeIdFilter($acknowledgments, $filters, 'user_id');
        $this->applyDateFilter($acknowledgments, $filters, 'due_date');

        $pendingAck = (clone $acknowledgments)->count();

        $expiringRows = (clone $contracts)
            ->whereBetween('end_date', [$today, $in60])
            ->with('employee')
            ->orderBy('end_date')
            ->limit(15)
            ->get()
            ->map(fn ($contract) => [
                $contract->employee?->name ?? '—',
                $contract->contract_number ?? '—',
                $this->formatReportDate($contract->end_date),
            ])
            ->values()
            ->all();

        return [
            'summary' => [
                ['label' => 'Contracts Expiring (30 days)', 'value' => $expiring30, 'format' => 'number'],
                ['label' => 'Contracts Expiring (60 days)', 'value' => $expiring60, 'format' => 'number'],
                ['label' => 'Pending Document Acknowledgments', 'value' => $pendingAck, 'format' => 'number'],
            ],
            'tables' => [
                [
                    'title' => 'Contracts Expiring Soon',
                    'columns' => ['Employee', 'Contract', 'End Date'],
                    'rows' => $expiringRows,
                ],
            ],
        ];
    }

    private function engagementReport(array $filters): array
    {
        $companyIds = $this->companyIds();

        $announcements = Announcement::whereIn('created_by', $companyIds);
        $this->applyDateFilter($announcements, $filters, 'start_date');
        $total = (clone $announcements)->count();
        $active = (clone $announcements)->where('is_company_wide', true)->count();

        $announcementIds = (clone $announcements)->pluck('id');
        $viewsQuery = AnnouncementView::whereIn('announcement_id', $announcementIds);
        $this->applyEmployeeIdFilter($viewsQuery, $filters, 'employee_id');
        $this->applyDateFilter($viewsQuery, $filters, 'created_at');
        $views = (clone $viewsQuery)->count();

        $employeeCountQuery = Employee::whereIn('created_by', $companyIds);
        $this->applyEmployeeRecordFilters($employeeCountQuery, $filters);
        $employeeCount = (clone $employeeCountQuery)->count();
        $reach = $employeeCount > 0 && $total > 0
            ? round(($views / max($employeeCount * $total, 1)) * 100, 1)
            : 0;

        $recent = (clone $announcements)
            ->orderByDesc('start_date')
            ->limit(10)
            ->get(['title', 'category', 'start_date'])
            ->map(fn ($item) => [
                $item->title,
                $item->category,
                $this->formatReportDate($item->start_date),
            ])
            ->values()
            ->all();

        return [
            'summary' => [
                ['label' => 'Total Announcements', 'value' => $total, 'format' => 'number'],
                ['label' => 'Company-wide', 'value' => $active, 'format' => 'number'],
                ['label' => 'Total Views Recorded', 'value' => $views, 'format' => 'number'],
                ['label' => 'Estimated Reach Index', 'value' => $reach, 'format' => 'percent'],
            ],
            'tables' => [
                [
                    'title' => 'Recent Announcements',
                    'columns' => ['Title', 'Category', 'Start Date'],
                    'rows' => $recent,
                ],
            ],
        ];
    }

    private function meetingsReport(array $filters): array
    {
        $companyIds = $this->companyIds();

        $meetings = Meeting::whereIn('created_by', $companyIds);
        if (! empty($filters['employee_id'])) {
            $meetings->where('organizer_id', $filters['employee_id']);
        }
        $this->applyDateFilter($meetings, $filters, 'meeting_date');
        $today = $this->reportToday()->toDateString();
        $upcoming = (clone $meetings)->where('meeting_date', '>=', $today)->count();
        $completed = (clone $meetings)->where('status', 'Completed')->count();
        $scheduled = (clone $meetings)->where('status', 'Scheduled')->count();

        $actions = ActionItem::whereIn('created_by', $companyIds)
            ->whereNull('completed_date');
        if (! empty($filters['employee_id'])) {
            $actions->where('assigned_to', $filters['employee_id']);
        }
        $this->applyDateFilter($actions, $filters, 'due_date');

        $openActions = (clone $actions)->count();

        $upcomingRows = (clone $meetings)
            ->where('meeting_date', '>=', $today)
            ->orderBy('meeting_date')
            ->limit(10)
            ->get(['title', 'meeting_date', 'status'])
            ->map(fn ($meeting) => [
                $meeting->title,
                $this->formatReportDate($meeting->meeting_date),
                $meeting->status,
            ])
            ->values()
            ->all();

        return [
            'summary' => [
                ['label' => 'Upcoming Meetings', 'value' => $upcoming, 'format' => 'number'],
                ['label' => 'Scheduled', 'value' => $scheduled, 'format' => 'number'],
                ['label' => 'Completed', 'value' => $completed, 'format' => 'number'],
                ['label' => 'Open Action Items', 'value' => $openActions, 'format' => 'number'],
            ],
            'tables' => [
                [
                    'title' => 'Upcoming Meetings',
                    'columns' => ['Title', 'Date', 'Status'],
                    'rows' => $upcomingRows,
                ],
            ],
        ];
    }
}
