<?php

namespace App\Services\Reporting;

use Config\Database;

/**
 * All queries here are explicitly WHERE agency_id = scoped inline (not via
 * BaseRepository) since reports aggregate across many tables with joins
 * that don't map cleanly to a single-model repository — but the same
 * tenant-isolation principle applies: every query is agency_id-filtered.
 */
class AgencyReportingService
{
    public function recruiterPerformance(int $agencyId): array
    {
        return Database::connect()->table('job_applications ja')
            ->select('u.name as recruiter_name, COUNT(*) as total_assigned,
                      SUM(CASE WHEN ja.status = "hired" THEN 1 ELSE 0 END) as hires,
                      SUM(CASE WHEN ja.status = "rejected" THEN 1 ELSE 0 END) as rejections')
            ->join('users u', 'u.id = ja.assigned_recruiter_id')
            ->join('jobs j', 'j.id = ja.job_id')
            ->where('j.agency_id', $agencyId)
            ->groupBy('u.id')
            ->get()->getResultArray();
    }

    public function candidateViews(int $agencyId, string $from, string $to): array
    {
        return Database::connect()->table('activity_logs')
            ->select('DATE(created_at) as period_date, COUNT(*) as views')
            ->where('agency_id', $agencyId)
            ->where('action', 'profile_view')
            ->where('created_at >=', $from)->where('created_at <=', $to . ' 23:59:59')
            ->groupBy('DATE(created_at)')
            ->orderBy('period_date', 'ASC')
            ->get()->getResultArray();
    }

    public function contactReveals(int $agencyId, string $from, string $to): array
    {
        return Database::connect()->table('candidate_unlocks')
            ->select('unlock_type, COUNT(*) as count, SUM(credits_spent) as credits_spent')
            ->where('agency_id', $agencyId)
            ->where('unlocked_at >=', $from)->where('unlocked_at <=', $to . ' 23:59:59')
            ->groupBy('unlock_type')
            ->get()->getResultArray();
    }

    public function hires(int $agencyId, string $from, string $to): array
    {
        return Database::connect()->table('job_applications ja')
            ->select('j.title as job_title, ja.updated_at as hired_at')
            ->join('jobs j', 'j.id = ja.job_id')
            ->where('j.agency_id', $agencyId)
            ->where('ja.status', 'hired')
            ->where('ja.updated_at >=', $from)->where('ja.updated_at <=', $to . ' 23:59:59')
            ->orderBy('ja.updated_at', 'DESC')
            ->get()->getResultArray();
    }

    public function interviews(int $agencyId, string $from, string $to): array
    {
        return Database::connect()->table('interviews i')
            ->select('i.status, COUNT(*) as count, AVG(i.feedback_rating) as avg_rating')
            ->join('job_applications ja', 'ja.id = i.job_application_id')
            ->join('jobs j', 'j.id = ja.job_id')
            ->where('j.agency_id', $agencyId)
            ->where('i.scheduled_at >=', $from)->where('i.scheduled_at <=', $to . ' 23:59:59')
            ->groupBy('i.status')
            ->get()->getResultArray();
    }

    /**
     * Conversion funnel: applied → under_review → shortlisted →
     * interview_scheduled → interview_completed → hired, with the
     * percentage of the initial "applied" cohort reaching each stage.
     */
    public function conversionRates(int $agencyId, ?int $jobId = null): array
    {
        $stages = ['applied', 'under_review', 'shortlisted', 'interview_scheduled', 'interview_completed', 'hired'];

        $builder = Database::connect()->table('job_applications ja')
            ->select('ja.status, COUNT(*) as count')
            ->join('jobs j', 'j.id = ja.job_id')
            ->where('j.agency_id', $agencyId);

        if ($jobId) {
            $builder->where('ja.job_id', $jobId);
        }

        $counts = array_column($builder->groupBy('ja.status')->get()->getResultArray(), 'count', 'status');
        $totalApplied = array_sum($counts); // includes rejected at any stage; funnel below uses forward-only stages

        $funnel = [];
        foreach ($stages as $stage) {
            $reachedOrPast = 0;
            // Since status is a single current value (not cumulative history
            // here), this reports CURRENT distribution, not true historical
            // funnel — application_status_history has the full timeline for
            // a more precise cohort analysis if needed later.
            $reachedOrPast = $counts[$stage] ?? 0;
            $funnel[$stage] = [
                'count'      => $reachedOrPast,
                'percentage' => $totalApplied > 0 ? round(($reachedOrPast / $totalApplied) * 100, 1) : 0,
            ];
        }

        return $funnel;
    }
}
