<?php
namespace App\Controllers;

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

class ReportController extends Controller {
    
    protected $migrantModel;
    protected $statusModel;
    
    public function __construct() {
        parent::__construct();
        $this->migrantModel = new Migrant();
        $this->statusModel = new ApplicationStatus();
        
        // Only Admin and SuperAdmin can access reports
        if (!in_array($_SESSION['user_role'], ['Admin', 'SuperAdmin'])) {
            $this->response->forbidden();
        }
    }
    
    /**
     * Show reports page
     */
    public function index() {
        $this->render('reports/index', [
            'title' => 'Reports'
        ]);
    }
    
    /**
     * Generate report
     */
    public function generate() {
        $type = $this->request->post('report_type');
        $format = $this->request->post('format', 'html');
        $date_from = $this->request->post('date_from');
        $date_to = $this->request->post('date_to');
        
        switch ($type) {
            case 'applications':
                $data = $this->generateApplicationsReport($date_from, $date_to);
                break;
            case 'nationalities':
                $data = $this->generateNationalitiesReport();
                break;
            case 'status':
                $data = $this->generateStatusReport();
                break;
            default:
                $this->json(['error' => 'Invalid report type'], 400);
                return;
        }
        
        if ($format === 'json') {
            $this->json($data);
        } else {
            $this->render('reports/view', [
                'title' => 'Report',
                'report_type' => $type,
                'data' => $data,
                'date_from' => $date_from,
                'date_to' => $date_to
            ]);
        }
    }
    
    /**
     * Generate applications report
     */
    protected function generateApplicationsReport($date_from, $date_to) {
        $db = $this->migrantModel->getConnection();
        $stmt = $db->prepare("
            SELECT 
                DATE(b.created_at) as date,
                COUNT(*) as total,
                SUM(CASE WHEN a.status = 'Approved' THEN 1 ELSE 0 END) as approved,
                SUM(CASE WHEN a.status = 'Rejected' THEN 1 ELSE 0 END) as rejected,
                SUM(CASE WHEN a.status = 'Submitted' THEN 1 ELSE 0 END) as pending
            FROM biographical_information b
            LEFT JOIN (
                SELECT person_id, status
                FROM application_status a1
                WHERE created_at = (
                    SELECT MAX(created_at)
                    FROM application_status a2
                    WHERE a2.person_id = a1.person_id
                )
            ) a ON b.person_id = a.person_id
            WHERE b.created_at BETWEEN ? AND ?
            GROUP BY DATE(b.created_at)
            ORDER BY date DESC
        ");
        
        $stmt->execute([$date_from, $date_to]);
        return $stmt->fetchAll();
    }
    
    /**
     * Generate nationalities report
     */
    protected function generateNationalitiesReport() {
        $db = $this->migrantModel->getConnection();
        $stmt = $db->query("
            SELECT 
                nationality,
                COUNT(*) as count,
                SUM(CASE WHEN a.status = 'Approved' THEN 1 ELSE 0 END) as approved
            FROM biographical_information b
            LEFT JOIN (
                SELECT person_id, status
                FROM application_status a1
                WHERE created_at = (
                    SELECT MAX(created_at)
                    FROM application_status a2
                    WHERE a2.person_id = a1.person_id
                )
            ) a ON b.person_id = a.person_id
            WHERE b.deleted_at IS NULL
            GROUP BY nationality
            ORDER BY count DESC
        ");
        
        return $stmt->fetchAll();
    }
    
    /**
     * Generate status report
     */
    protected function generateStatusReport() {
        return $this->statusModel->getStatusStats();
    }
}