<?php
namespace App\Controllers;

use App\Core\Controller;
use App\Models\Migrant;
use App\Models\ApplicationStatus;

class DashboardController extends Controller {
    
    public function index() {
        $this->requireAuth();
        
        $migrantModel = new Migrant();
        $statusModel = new ApplicationStatus();
        
        $stats = $migrantModel->getStatistics();
        $statusStats = $statusModel->getStatusStats();
        
        // Get male and female counts from biographical_information table
        $db = $migrantModel->getConnection();
        
        $maleStmt = $db->query("SELECT COUNT(*) as count FROM biographical_information WHERE sex = 'Male' AND deleted_at IS NULL");
        $maleResult = $maleStmt->fetch();
        $maleCount = $maleResult['count'] ?? 0;
        
        $femaleStmt = $db->query("SELECT COUNT(*) as count FROM biographical_information WHERE sex = 'Female' AND deleted_at IS NULL");
        $femaleResult = $femaleStmt->fetch();
        $femaleCount = $femaleResult['count'] ?? 0;
        
        // Get highest nationality
        $natStmt = $db->query("
            SELECT nationality, COUNT(*) as count 
            FROM biographical_information 
            WHERE deleted_at IS NULL 
            GROUP BY nationality 
            ORDER BY count DESC 
            LIMIT 1
        ");
        $highestNationalityResult = $natStmt->fetch();
        
        $highestNationality = [
            'name' => $highestNationalityResult['nationality'] ?? 'N/A',
            'count' => $highestNationalityResult['count'] ?? 0
        ];
        
        // Get recent migrants with their status
        $recent = $migrantModel->query("
            SELECT b.*, 
                   (SELECT status FROM application_status 
                    WHERE person_id = b.person_id 
                    ORDER BY created_at DESC LIMIT 1) as status
            FROM biographical_information b
            WHERE b.deleted_at IS NULL
            ORDER BY b.created_at DESC
            LIMIT 10
        ")->fetchAll();
        
        $this->render('dashboard/index', [
            'title' => 'Dashboard',
            'stats' => $stats,
            'statusStats' => $statusStats,
            'recent' => $recent,
            'maleCount' => $maleCount,
            'femaleCount' => $femaleCount,
            'highestNationality' => $highestNationality
        ]);
    }
}