<?php

namespace App\Services;

use App\Models\CompanyRegistration;
use App\Models\EmployeeRegistration;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;

class AnalyticsService
{
    private array $months = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'];

    /**
     * Returns a DB-driver-aware expression for extracting the month number from a column.
     * Column is whitelisted to prevent SQL injection if this method is ever called with
     * a non-hardcoded value in future.
     */
    private function monthExpression(string $column): string
    {
        $allowed = ['created_at', 'updated_at'];

        if (!in_array($column, $allowed, true)) {
            throw new \InvalidArgumentException("Column '{$column}' is not allowed in monthExpression.");
        }

        return DB::getDriverName() === 'sqlite'
            ? "CAST(strftime('%m', {$column}) AS INTEGER)"
            : "MONTH({$column})";
    }

    /**
     * Monthly employee registrations for the current year.
     */
    public function employeeRegistrationsByMonth(): array
    {
        $year = now()->year;

        $monthExpr = $this->monthExpression('created_at');

        $counts = EmployeeRegistration::selectRaw("{$monthExpr} as month, COUNT(*) as count")
            ->whereYear('created_at', $year)
            ->groupBy('month')
            ->pluck('count', 'month')
            ->toArray();

        $data = [];
        foreach ($this->months as $index => $name) {
            $data[] = [
                'month' => $name,
                'count' => $counts[$index + 1] ?? 0,
            ];
        }

        return [
            'year' => $year,
            'data' => $data,
        ];
    }

    /**
     * Registration status summary — combined company and employee registrations.
     */
    public function registrationStatusSummary(): array
    {
        $companyStats = $this->getStatusCounts(new CompanyRegistration());
        $employeeStats = $this->getStatusCounts(new EmployeeRegistration());

        return [
            'company_registrations' => $companyStats,
            'employee_registrations' => $employeeStats,
            'combined_total' => $companyStats['total'] + $employeeStats['total'],
        ];
    }

    /**
     * Company registrations by month with full status breakdown for the current year.
     */
    public function companyRegistrationsByMonth(): array
    {
        $year = now()->year;

        $monthExpr = $this->monthExpression('created_at');

        $rows = CompanyRegistration::selectRaw("
                {$monthExpr} as month,
                status,
                account_status,
                verification_status,
                COUNT(*) as count
            ")
            ->whereYear('created_at', $year)
            ->groupBy('month', 'status', 'account_status', 'verification_status')
            ->get();

        $lookup = [];
        foreach ($rows as $row) {
            $m = $row->month;
            $lookup[$m]['status'][$row->status] = ($lookup[$m]['status'][$row->status] ?? 0) + $row->count;
            $lookup[$m]['account_status'][$row->account_status] = ($lookup[$m]['account_status'][$row->account_status] ?? 0) + $row->count;
            $lookup[$m]['verification_status'][$row->verification_status] = ($lookup[$m]['verification_status'][$row->verification_status] ?? 0) + $row->count;
        }

        $data = [];
        foreach ($this->months as $index => $name) {
            $monthNum = $index + 1;
            $monthData = $lookup[$monthNum] ?? [];

            $data[] = [
                'month' => $name,
                'total' => array_sum($monthData['status'] ?? []),
                'by_status' => [
                    'pending_approval' => $monthData['status']['pending_approval'] ?? 0,
                    'approved'         => $monthData['status']['approved'] ?? 0,
                    'rejected'         => $monthData['status']['rejected'] ?? 0,
                ],
                'by_account_status' => [
                    'pending' => $monthData['account_status']['pending'] ?? 0,
                    'invited' => $monthData['account_status']['invited'] ?? 0,
                    'active'  => $monthData['account_status']['active'] ?? 0,
                ],
                'by_verification_status' => [
                    'not_verified' => $monthData['verification_status']['not_verified'] ?? 0,
                    'verified'     => $monthData['verification_status']['verified'] ?? 0,
                ],
            ];
        }

        return [
            'year' => $year,
            'data' => $data,
        ];
    }

    /**
     * Current week (Monday–Sunday) registration data broken down by day and status.
     */
    public function currentWeekData(): array
    {
        $weekStart = now()->copy()->startOfWeek(Carbon::MONDAY);
        $weekEnd   = now()->copy()->endOfWeek(Carbon::SUNDAY);

        $companyRows = CompanyRegistration::selectRaw('
                DATE(created_at) as date,
                status,
                account_status,
                verification_status,
                COUNT(*) as count
            ')
            ->whereBetween('created_at', [$weekStart, $weekEnd])
            ->groupBy('date', 'status', 'account_status', 'verification_status')
            ->get();

        $employeeRows = EmployeeRegistration::selectRaw('
                DATE(created_at) as date,
                status,
                account_status,
                verification_status,
                COUNT(*) as count
            ')
            ->whereBetween('created_at', [$weekStart, $weekEnd])
            ->groupBy('date', 'status', 'account_status', 'verification_status')
            ->get();

        $companyLookup  = $this->buildDateLookup($companyRows);
        $employeeLookup = $this->buildDateLookup($employeeRows);

        $dayNames = ['Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun'];
        $data = [];

        foreach ($dayNames as $index => $dayName) {
            $date = $weekStart->copy()->addDays($index)->toDateString();
            $data[] = [
                'day'       => $dayName,
                'date'      => $date,
                'companies' => $this->formatCompanyDayStats($companyLookup[$date] ?? []),
                'employees' => $this->formatEmployeeDayStats($employeeLookup[$date] ?? []),
            ];
        }

        return [
            'week_start' => $weekStart->toDateString(),
            'week_end'   => $weekEnd->toDateString(),
            'data'       => $data,
        ];
    }

    /**
     * Recent registrations (company + employee) within the last N hours, newest first.
     */
    public function recentRegistrations(int $hours = 7): array
    {
        $since = now()->subHours($hours);

        $companies = CompanyRegistration::where('created_at', '>=', $since)
            ->orderByDesc('created_at')
            ->get()
            ->map(fn ($r) => [
                'id'                  => $r->id,
                'type'                => 'company',
                'name'                => $r->company_name,
                'email'               => $r->email_address,
                'status'              => $r->status,
                'account_status'      => $r->account_status,
                'verification_status' => $r->verification_status,
                'submission_type'     => $r->submission_type,
                'submitted_at'        => $r->created_at->toISOString(),
            ]);

        $employees = EmployeeRegistration::where('created_at', '>=', $since)
            ->orderByDesc('created_at')
            ->get()
            ->map(fn ($r) => [
                'id'                  => $r->id,
                'type'                => 'employee',
                'name'                => trim($r->first_name . ' ' . $r->last_name),
                'email'               => $r->email_address,
                'status'              => $r->status,
                'account_status'      => $r->account_status,
                'verification_status' => $r->verification_status,
                'submission_type'     => $r->submission_type,
                'submitted_at'        => $r->created_at->toISOString(),
            ]);

        $merged = $companies->concat($employees)
            ->sortByDesc('submitted_at')
            ->values();

        return [
            'hours' => $hours,
            'since' => $since->toISOString(),
            'total' => $merged->count(),
            'data'  => $merged->all(),
        ];
    }

    /**
     * Get status counts for a given model instance.
     */
    private function getStatusCounts(object $model): array
    {
        $query = fn () => $model->newQuery();

        $total = $query()->count();

        $byStatus = $query()
            ->selectRaw('status, COUNT(*) as count')
            ->groupBy('status')
            ->pluck('count', 'status')
            ->toArray();

        $byAccountStatus = $query()
            ->selectRaw('account_status, COUNT(*) as count')
            ->groupBy('account_status')
            ->pluck('count', 'account_status')
            ->toArray();

        $byVerificationStatus = $query()
            ->selectRaw('verification_status, COUNT(*) as count')
            ->groupBy('verification_status')
            ->pluck('count', 'verification_status')
            ->toArray();

        return [
            'total'                  => $total,
            'by_status'              => $byStatus,
            'by_account_status'      => $byAccountStatus,
            'by_verification_status' => $byVerificationStatus,
        ];
    }

    /**
     * Build a date-string-indexed lookup from a query result.
     */
    private function buildDateLookup($rows): array
    {
        $lookup = [];
        foreach ($rows as $row) {
            $d = $row->date;
            $lookup[$d]['status'][$row->status] = ($lookup[$d]['status'][$row->status] ?? 0) + $row->count;
            $lookup[$d]['account_status'][$row->account_status] = ($lookup[$d]['account_status'][$row->account_status] ?? 0) + $row->count;
            $lookup[$d]['verification_status'][$row->verification_status] = ($lookup[$d]['verification_status'][$row->verification_status] ?? 0) + $row->count;
        }
        return $lookup;
    }

    /**
     * Format a single day's stats for company registrations (status: pending_approval, approved, rejected).
     */
    private function formatCompanyDayStats(array $dayData): array
    {
        return [
            'total' => array_sum($dayData['status'] ?? []),
            'by_status' => [
                'pending_approval' => $dayData['status']['pending_approval'] ?? 0,
                'approved'         => $dayData['status']['approved'] ?? 0,
                'rejected'         => $dayData['status']['rejected'] ?? 0,
            ],
            'by_account_status' => [
                'pending' => $dayData['account_status']['pending'] ?? 0,
                'invited' => $dayData['account_status']['invited'] ?? 0,
                'active'  => $dayData['account_status']['active'] ?? 0,
            ],
            'by_verification_status' => [
                'not_verified' => $dayData['verification_status']['not_verified'] ?? 0,
                'verified'     => $dayData['verification_status']['verified'] ?? 0,
            ],
        ];
    }

    /**
     * Format a single day's stats for employee registrations (status: pending, approved, rejected, verified).
     */
    private function formatEmployeeDayStats(array $dayData): array
    {
        return [
            'total' => array_sum($dayData['status'] ?? []),
            'by_status' => [
                'pending'  => $dayData['status']['pending'] ?? 0,
                'approved' => $dayData['status']['approved'] ?? 0,
                'rejected' => $dayData['status']['rejected'] ?? 0,
                'verified' => $dayData['status']['verified'] ?? 0,
            ],
            'by_account_status' => [
                'pending' => $dayData['account_status']['pending'] ?? 0,
                'invited' => $dayData['account_status']['invited'] ?? 0,
                'active'  => $dayData['account_status']['active'] ?? 0,
            ],
            'by_verification_status' => [
                'not_verified' => $dayData['verification_status']['not_verified'] ?? 0,
                'verified'     => $dayData['verification_status']['verified'] ?? 0,
            ],
        ];
    }
}
