<?php

namespace App\Services\Reporting;

use App\Models\ReportSummaryModel;
use App\Services\Support\TenantContext;
use Config\Database;

/**
 * Platform-wide reports (Super Admin only — see 04-roles-permissions-matrix.md
 * §reports.platform_wide). Small date ranges query live tables directly;
 * larger ranges read from report_summaries (refreshed nightly by the
 * reports:generate cron — see refreshDaily() below, the cron's entry point).
 */
class AdminReportingService
{
    private ReportSummaryModel $summaries;
    private const LIVE_QUERY_THRESHOLD_DAYS = 31;

    public function __construct()
    {
        $this->summaries = new ReportSummaryModel();
        if (! TenantContext::getInstance()->isSuperAdmin()) {
            throw new \App\Exceptions\ForbiddenException('Platform-wide reports require Super Admin access.');
        }
    }

    /**
     * "Which agency hired whom" — grouped by agency, each with a
     * candidate/job/hired-date drill-down list. Reads job_applications
     * where status='hired', joined to agencies via the job.
     */
    public function hiresByAgency(): array
    {
        $db = Database::connect();

        $hires = $db->table('job_applications ja')
            ->select('a.id as agency_id, a.company_name, u.name as candidate_name, u.email as candidate_email, j.title as job_title, ja.id as application_id,
                      (SELECT ash.created_at FROM application_status_history ash WHERE ash.job_application_id = ja.id AND ash.to_status = "hired" ORDER BY ash.created_at DESC LIMIT 1) as hired_at')
            ->join('jobs j', 'j.id = ja.job_id')
            ->join('agencies a', 'a.id = j.agency_id')
            ->join('candidate_profiles cp', 'cp.id = ja.candidate_profile_id')
            ->join('users u', 'u.id = cp.user_id')
            ->where('ja.status', 'hired')
            ->orderBy('hired_at', 'DESC')
            ->get()->getResultArray();

        // Group by agency for the summary view, keeping the flat list too
        // for a full drill-down table.
        $byAgency = [];
        foreach ($hires as $hire) {
            $agencyId = $hire['agency_id'];
            if (! isset($byAgency[$agencyId])) {
                $byAgency[$agencyId] = ['agency_id' => $agencyId, 'company_name' => $hire['company_name'], 'hire_count' => 0, 'hires' => []];
            }
            $byAgency[$agencyId]['hire_count']++;
            $byAgency[$agencyId]['hires'][] = $hire;
        }

        usort($byAgency, fn ($a, $b) => $b['hire_count'] <=> $a['hire_count']);

        return ['agencies' => array_values($byAgency), 'total_hires' => count($hires)];
    }

    /**
     * Per-candidate: total applications submitted, and for each one,
     * which company (if any) hired them and when.
     */
    public function candidateApplicationReport(int $candidateUserId): array
    {
        $db = Database::connect();

        $profile = $db->table('candidate_profiles')->where('user_id', $candidateUserId)->get()->getRowArray();
        if (! $profile) {
            return ['candidate' => null, 'applications' => [], 'total_applications' => 0, 'total_hires' => 0];
        }

        $applications = $db->table('job_applications ja')
            ->select('ja.id, ja.status, ja.applied_at, j.title as job_title, a.company_name,
                      (SELECT ash.created_at FROM application_status_history ash WHERE ash.job_application_id = ja.id AND ash.to_status = "hired" ORDER BY ash.created_at DESC LIMIT 1) as hired_at')
            ->join('jobs j', 'j.id = ja.job_id')
            ->join('agencies a', 'a.id = j.agency_id')
            ->where('ja.candidate_profile_id', $profile['id'])
            ->orderBy('ja.applied_at', 'DESC')
            ->get()->getResultArray();

        $user = $db->table('users')->where('id', $candidateUserId)->get()->getRowArray();

        return [
            'candidate'           => ['id' => $candidateUserId, 'name' => $user['name'] ?? null, 'email' => $user['email'] ?? null],
            'applications'        => $applications,
            'total_applications'  => count($applications),
            'total_hires'         => count(array_filter($applications, fn ($a) => $a['status'] === 'hired')),
        ];
    }

    public function revenue(string $from, string $to): array
    {
        if ($this->isSmallRange($from, $to)) {
            return $this->liveRevenue($from, $to);
        }
        return $this->summaries->rangeFor('revenue_daily', 'platform', null, $from, $to);
    }

    private function liveRevenue(string $from, string $to): array
    {
        return Database::connect()->table('payments')
            ->select('DATE(created_at) as period_date, SUM(amount) as total_revenue, COUNT(*) as transaction_count')
            ->where('status', 'success')
            ->where('created_at >=', $from)->where('created_at <=', $to . ' 23:59:59')
            ->groupBy('DATE(created_at)')
            ->orderBy('period_date', 'ASC')
            ->get()->getResultArray();
    }

    public function subscriptions(): array
    {
        $db = Database::connect();
        return [
            'by_plan'   => $db->table('agency_subscriptions asub')
                ->select('sp.name as plan_name, COUNT(*) as count')
                ->join('subscription_plans sp', 'sp.id = asub.plan_id')
                ->where('asub.status', 'active')
                ->groupBy('sp.name')->get()->getResultArray(),
            'by_status' => $db->table('agency_subscriptions')
                ->select('status, COUNT(*) as count')->groupBy('status')->get()->getResultArray(),
        ];
    }

    public function agencies(): array
    {
        $db = Database::connect();
        return [
            'total'              => $db->table('agencies')->countAllResults(),
            'by_status'          => $db->table('agencies')->select('status, COUNT(*) as count')->groupBy('status')->get()->getResultArray(),
            'by_verification'    => $db->table('agencies')->select('verification_status, COUNT(*) as count')->groupBy('verification_status')->get()->getResultArray(),
            'new_last_30_days'   => $db->table('agencies')->where('created_at >=', date('Y-m-d', strtotime('-30 days')))->countAllResults(),
        ];
    }

    public function recruiters(): array
    {
        $db = Database::connect();
        return [
            'total_active' => $db->table('users u')->join('roles r', 'r.id = u.role_id')
                ->where('r.slug', 'recruiter')->where('u.status', 'active')->countAllResults(),
            'top_by_hires' => $db->table('job_applications ja')
                ->select('u.name as recruiter_name, COUNT(*) as hires')
                ->join('users u', 'u.id = ja.assigned_recruiter_id')
                ->where('ja.status', 'hired')
                ->groupBy('u.id')->orderBy('hires', 'DESC')->limit(10)
                ->get()->getResultArray(),
        ];
    }

    public function candidates(): array
    {
        $db = Database::connect();
        return [
            'total'                => $db->table('candidate_profiles')->countAllResults(),
            'by_availability'      => $db->table('candidate_profiles')->select('availability_status, COUNT(*) as count')->groupBy('availability_status')->get()->getResultArray(),
            'active_last_30_days'  => $db->table('candidate_profiles')->where('last_active_at >=', date('Y-m-d', strtotime('-30 days')))->countAllResults(),
        ];
    }

    public function jobs(): array
    {
        $db = Database::connect();
        return [
            'total'         => $db->table('jobs')->countAllResults(),
            'by_status'     => $db->table('jobs')->select('status, COUNT(*) as count')->groupBy('status')->get()->getResultArray(),
            'featured_count'=> $db->table('jobs')->where('is_featured', 1)->countAllResults(),
        ];
    }

    public function applications(): array
    {
        $db = Database::connect();
        return [
            'total'      => $db->table('job_applications')->countAllResults(),
            'by_status'  => $db->table('job_applications')->select('status, COUNT(*) as count')->groupBy('status')->get()->getResultArray(),
        ];
    }

    public function credits(): array
    {
        $db = Database::connect();
        return [
            'total_balance_outstanding' => $db->table('credit_balances')->selectSum('balance')->get()->getRow()->balance ?? 0,
            'usage_by_type'             => $db->table('credit_transactions')->select('type, SUM(amount) as total')->groupBy('type')->get()->getResultArray(),
        ];
    }

    public function wallet(): array
    {
        $db = Database::connect();
        return [
            'total_balance' => $db->table('wallets')->selectSum('balance')->get()->getRow()->balance ?? 0,
            'by_type'       => $db->table('wallet_transactions')->select('type, SUM(amount) as total')->groupBy('type')->get()->getResultArray(),
        ];
    }

    public function payments(string $from, string $to): array
    {
        $db = Database::connect();
        return [
            'by_gateway' => $db->table('payments')->select('gateway, COUNT(*) as count, SUM(amount) as total')
                ->where('created_at >=', $from)->where('created_at <=', $to . ' 23:59:59')
                ->where('status', 'success')->groupBy('gateway')->get()->getResultArray(),
            'by_status'  => $db->table('payments')->select('status, COUNT(*) as count')
                ->where('created_at >=', $from)->where('created_at <=', $to . ' 23:59:59')
                ->groupBy('status')->get()->getResultArray(),
        ];
    }

    /**
     * Cron entry point (reports:generate, daily 02:00 per deployment guide).
     * Refreshes yesterday's summary row for each report type.
     */
    public function refreshDaily(): void
    {
        $date = date('Y-m-d', strtotime('-1 day'));
        $revenue = $this->liveRevenue($date, $date);
        $row = $revenue[0] ?? ['total_revenue' => 0, 'transaction_count' => 0];

        $this->summaries->upsert('revenue_daily', 'platform', null, $date, $row);
    }

    private function isSmallRange(string $from, string $to): bool
    {
        $days = (strtotime($to) - strtotime($from)) / 86400;
        return $days <= self::LIVE_QUERY_THRESHOLD_DAYS;
    }
}
