<?php

namespace App\Controllers\Admin;

use App\Controllers\BaseController;
use App\Services\Billing\SubscriptionService;
use Config\Database;

class DashboardController extends BaseController
{
    public function index()
    {
        $user = $this->currentUser();
        $data = ['userName' => $user->name, 'role' => $user->role_slug, 'photoUrl' => $user->profile_photo_path ? base_url($user->profile_photo_path) : null];

        if (in_array($user->role_slug, ['agency_owner', 'recruiter'], true)) {
            $data['subscription'] = (new SubscriptionService())->currentSubscriptionWithPlan($user->agency_id);
            $db = Database::connect();
            $data['stats'] = [
                'open_jobs'    => $db->table('jobs')->where('agency_id', $user->agency_id)->where('status', 'published')->countAllResults(),
                'applications' => $db->table('job_applications ja')->join('jobs j', 'j.id = ja.job_id')->where('j.agency_id', $user->agency_id)->countAllResults(),
                'hires'        => $db->table('job_applications ja')->join('jobs j', 'j.id = ja.job_id')->where('j.agency_id', $user->agency_id)->where('ja.status', 'hired')->countAllResults(),
                'team_size'    => $db->table('users')->where('agency_id', $user->agency_id)->where('status', 'active')->countAllResults(),
            ];
        } elseif ($user->isCandidate()) {
            $profile = Database::connect()->table('candidate_profiles')->where('user_id', $user->id)->get()->getRowArray();
            $data['candidateProfile'] = $profile;
            if ($profile) {
                $data['stats'] = [
                    'applications' => Database::connect()->table('job_applications')->where('candidate_profile_id', $profile['id'])->countAllResults(),
                    'profile_pct'  => $profile['profile_completion_pct'],
                ];
            }
        } elseif ($user->isSuperAdmin()) {
            $db = Database::connect();
            $data['stats'] = [
                'total_users'    => $db->table('users')->countAllResults(),
                'agencies'       => $db->table('agencies')->countAllResults(),
                'agencies_active'=> $db->table('agencies')->where('status', 'active')->countAllResults(),
                'candidates'     => $db->table('candidate_profiles')->countAllResults(),
                'open_to_work'   => $db->table('candidate_profiles')->whereIn('availability_status', ['open_to_work', 'actively_looking', 'available_immediately'])->countAllResults(),
                'jobs_published' => $db->table('jobs')->where('status', 'published')->countAllResults(),
                'jobs_total'     => $db->table('jobs')->countAllResults(),
                'applications'   => $db->table('job_applications')->countAllResults(),
                'hires'          => $db->table('job_applications')->where('status', 'hired')->countAllResults(),
                'pending_verifications' => $db->table('agencies')->where('verification_status', 'pending')->countAllResults(),
                'new_users_7d'   => $db->table('users')->where('created_at >=', date('Y-m-d H:i:s', strtotime('-7 days')))->countAllResults(),
                'new_jobs_7d'    => $db->table('jobs')->where('created_at >=', date('Y-m-d H:i:s', strtotime('-7 days')))->countAllResults(),
            ];

            // Recent registrations (mixed candidates + agency owners) for
            // the "who just joined" feed on the dashboard.
            $data['recentUsers'] = $db->table('users u')
                ->select('u.name, u.email, u.created_at, r.slug as role_slug')
                ->join('roles r', 'r.id = u.role_id')
                ->orderBy('u.created_at', 'DESC')
                ->limit(8)
                ->get()->getResultArray();

            $data['recentJobs'] = $db->table('jobs j')
                ->select('j.title, j.status, j.created_at, a.company_name')
                ->join('agencies a', 'a.id = j.agency_id')
                ->orderBy('j.created_at', 'DESC')
                ->limit(8)
                ->get()->getResultArray();
        }

        return view('admin/dashboard', $data);
    }
}
