<?php
class RealHRAnalytics {
    private $db;
    private $company_id;
    
    public function __construct($database, $company_id) {
        $this->db = $database;
        $this->company_id = $company_id;
    }
    
    // Get recruitment metrics based on actual job applications
    public function getRecruitmentMetrics($period = '30 days') {
        try {
            $days = $this->parsePeriodToDays($period);
            
            $stmt = $this->db->prepare("
                SELECT 
                    COUNT(*) as total_applications,
                    COUNT(CASE WHEN ja.status = 'applied' THEN 1 END) as applied,
                    COUNT(CASE WHEN ja.status = 'shortlisted' THEN 1 END) as shortlisted,
                    COUNT(CASE WHEN ja.status = 'interviewed' THEN 1 END) as interviewed,
                    COUNT(CASE WHEN ja.status = 'hired' THEN 1 END) as hired,
                    COUNT(CASE WHEN ja.status = 'rejected' THEN 1 END) as rejected
                FROM job_applications ja
                JOIN jobs j ON ja.job_id = j.id
                WHERE j.company_id = ? AND ja.applied_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
            ");
            
            $stmt->execute([$this->company_id, $days]);
            $result = $stmt->fetch();
            
            return [
                'application_received' => ['total' => $result['total_applications']],
                'applied' => ['total' => $result['applied']],
                'shortlisted' => ['total' => $result['shortlisted']],
                'interviewed' => ['total' => $result['interviewed']],
                'hired' => ['total' => $result['hired']],
                'rejected' => ['total' => $result['rejected']]
            ];
        } catch (PDOException $e) {
            error_log("Error getting recruitment metrics: " . $e->getMessage());
            return [
                'application_received' => ['total' => 0],
                'applied' => ['total' => 0],
                'shortlisted' => ['total' => 0],
                'interviewed' => ['total' => 0],
                'hired' => ['total' => 0],
                'rejected' => ['total' => 0]
            ];
        }
    }
    
    // Get hiring funnel data from actual applications
    public function getHiringFunnel($period = '30 days') {
        try {
            $days = $this->parsePeriodToDays($period);
            
            $stmt = $this->db->prepare("
                SELECT 
                    j.title as job_title,
                    COUNT(*) as applications,
                    COUNT(CASE WHEN ja.status = 'shortlisted' THEN 1 END) as shortlisted,
                    COUNT(CASE WHEN ja.status = 'interviewed' THEN 1 END) as interviewed,
                    COUNT(CASE WHEN ja.status = 'hired' THEN 1 END) as hired,
                    COUNT(CASE WHEN ja.status = 'rejected' THEN 1 END) as rejected
                FROM jobs j
                LEFT JOIN job_applications ja ON j.id = ja.job_id 
                    AND ja.applied_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
                WHERE j.company_id = ?
                GROUP BY j.id, j.title
                HAVING applications > 0
                ORDER BY applications DESC
                LIMIT 10
            ");
            
            $stmt->execute([$days, $this->company_id]);
            return $stmt->fetchAll();
        } catch (PDOException $e) {
            error_log("Error getting hiring funnel: " . $e->getMessage());
            return [];
        }
    }
    
    // Get time to hire statistics (simplified since we don't have hire dates)
    public function getTimeToHireStats($period = '90 days') {
        try {
            $stmt = $this->db->prepare("
                SELECT 
                    AVG(DATEDIFF(NOW(), ja.applied_at)) as avg_days_since_application,
                    MIN(DATEDIFF(NOW(), ja.applied_at)) as min_days_since_application,
                    MAX(DATEDIFF(NOW(), ja.applied_at)) as max_days_since_application,
                    COUNT(*) as total_applications
                FROM job_applications ja
                JOIN jobs j ON ja.job_id = j.id
                WHERE j.company_id = ?
            ");
            
            $stmt->execute([$this->company_id]);
            $result = $stmt->fetch();
            
            return [
                'avg_time_to_hire' => round($result['avg_days_since_application'] ?? 0),
                'min_time_to_hire' => $result['min_days_since_application'] ?? 0,
                'max_time_to_hire' => $result['max_days_since_application'] ?? 0,
                'total_hires' => $result['total_applications'] ?? 0
            ];
        } catch (PDOException $e) {
            error_log("Error getting time to hire stats: " . $e->getMessage());
            return ['avg_time_to_hire' => 0, 'min_time_to_hire' => 0, 'max_time_to_hire' => 0, 'total_hires' => 0];
        }
    }
    
    // Get source effectiveness (simplified - assume all from website for now)
    public function getSourceEffectiveness($period = '30 days') {
        try {
            $days = $this->parsePeriodToDays($period);
            
            $stmt = $this->db->prepare("
                SELECT 
                    'Website' as source_name,
                    COUNT(*) as applications_count,
                    COUNT(CASE WHEN ja.status = 'hired' THEN 1 END) as hires_count,
                    CASE 
                        WHEN COUNT(*) > 0 THEN ROUND((COUNT(CASE WHEN ja.status = 'hired' THEN 1 END) / COUNT(*)) * 100, 2)
                        ELSE 0 
                    END as conversion_rate
                FROM job_applications ja
                JOIN jobs j ON ja.job_id = j.id
                WHERE j.company_id = ? AND ja.applied_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
            ");
            
            $stmt->execute([$this->company_id, $days]);
            $result = $stmt->fetch();
            
            if ($result['applications_count'] > 0) {
                return [$result];
            }
            
            return [];
        } catch (PDOException $e) {
            error_log("Error getting source effectiveness: " . $e->getMessage());
            return [];
        }
    }
    
    // Get monthly trends from actual applications
    public function getMonthlyTrends($months = 6) {
        try {
            $stmt = $this->db->prepare("
                SELECT 
                    DATE_FORMAT(ja.applied_at, '%Y-%m') as month,
                    COUNT(*) as applications,
                    COUNT(CASE WHEN ja.status = 'hired' THEN 1 END) as hired,
                    COUNT(CASE WHEN ja.status = 'interviewed' THEN 1 END) as interviewed,
                    COUNT(CASE WHEN ja.status = 'rejected' THEN 1 END) as rejected
                FROM job_applications ja
                JOIN jobs j ON ja.job_id = j.id
                WHERE j.company_id = ? AND ja.applied_at >= DATE_SUB(NOW(), INTERVAL ? MONTH)
                GROUP BY DATE_FORMAT(ja.applied_at, '%Y-%m')
                ORDER BY month ASC
            ");
            
            $stmt->execute([$this->company_id, $months]);
            
            $trends = [];
            while ($row = $stmt->fetch()) {
                $trends[$row['month']] = [
                    'application_received' => $row['applications'],
                    'candidate_hired' => $row['hired'],
                    'interviewed' => $row['interviewed'],
                    'rejected' => $row['rejected']
                ];
            }
            
            return $trends;
        } catch (PDOException $e) {
            error_log("Error getting monthly trends: " . $e->getMessage());
            return [];
        }
    }
    
    // Get jobs statistics
    public function getJobsStatistics() {
        try {
            $stmt = $this->db->prepare("
                SELECT 
                    COUNT(CASE WHEN status = 'active' THEN 1 END) as active_jobs,
                    COUNT(CASE WHEN status = 'closed' THEN 1 END) as closed_jobs,
                    COUNT(CASE WHEN status = 'draft' THEN 1 END) as draft_jobs,
                    COUNT(*) as total_jobs,
                    AVG(DATEDIFF(NOW(), created_at)) as avg_job_age_days
                FROM jobs 
                WHERE company_id = ?
            ");
            
            $stmt->execute([$this->company_id]);
            $result = $stmt->fetch();
            
            return [
                'active_jobs' => $result['active_jobs'] ?? 0,
                'closed_jobs' => $result['closed_jobs'] ?? 0,
                'draft_jobs' => $result['draft_jobs'] ?? 0,
                'total_jobs' => $result['total_jobs'] ?? 0,
                'avg_job_age_days' => round($result['avg_job_age_days'] ?? 0)
            ];
        } catch (PDOException $e) {
            error_log("Error getting jobs statistics: " . $e->getMessage());
            return ['active_jobs' => 0, 'closed_jobs' => 0, 'draft_jobs' => 0, 'total_jobs' => 0, 'avg_job_age_days' => 0];
        }
    }
    
    // Calculate conversion rates from real data
    public function getConversionRates($period = '30 days') {
        $metrics = $this->getRecruitmentMetrics($period);
        
        $applications = $metrics['application_received']['total'] ?? 0;
        $shortlisted = $metrics['shortlisted']['total'] ?? 0;
        $interviewed = $metrics['interviewed']['total'] ?? 0;
        $hired = $metrics['hired']['total'] ?? 0;
        
        return [
            'application_to_shortlist' => $applications > 0 ? round(($shortlisted / $applications) * 100, 2) : 0,
            'shortlist_to_interview' => $shortlisted > 0 ? round(($interviewed / $shortlisted) * 100, 2) : 0,
            'interview_to_hire' => $interviewed > 0 ? round(($hired / $interviewed) * 100, 2) : 0,
            'application_to_hire' => $applications > 0 ? round(($hired / $applications) * 100, 2) : 0,
        ];
    }
    
    // Get detailed application statistics
    public function getDetailedStats($period = '30 days') {
        try {
            $days = $this->parsePeriodToDays($period);
            
            $stmt = $this->db->prepare("
                SELECT 
                    COUNT(*) as total_applications,
                    COUNT(DISTINCT ja.candidate_id) as unique_candidates,
                    COUNT(DISTINCT j.id) as jobs_with_applications,
                    DATE(MIN(ja.applied_at)) as first_application,
                    DATE(MAX(ja.applied_at)) as latest_application
                FROM job_applications ja
                JOIN jobs j ON ja.job_id = j.id
                WHERE j.company_id = ? AND ja.applied_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
            ");
            
            $stmt->execute([$this->company_id, $days]);
            return $stmt->fetch();
        } catch (PDOException $e) {
            error_log("Error getting detailed stats: " . $e->getMessage());
            return [
                'total_applications' => 0,
                'unique_candidates' => 0,
                'jobs_with_applications' => 0,
                'first_application' => null,
                'latest_application' => null
            ];
        }
    }
    
    private function parsePeriodToDays($period) {
        $periods = [
            '7 days' => 7,
            '30 days' => 30,
            '90 days' => 90,
            '6 months' => 180,
            '1 year' => 365
        ];
        
        return $periods[$period] ?? 30;
    }
    
    // Export real data for reports
    public function exportMetricsToCSV($metrics, $filename) {
        $filepath = UPLOAD_PATH . 'reports/' . $filename;
        
        if (!is_dir(UPLOAD_PATH . 'reports/')) {
            mkdir(UPLOAD_PATH . 'reports/', 0755, true);
        }
        
        $file = fopen($filepath, 'w');
        
        fputcsv($file, ['Metric', 'Count', 'Period', 'Date']);
        
        foreach ($metrics as $metric => $data) {
            fputcsv($file, [$metric, $data['total'], '30 days', date('Y-m-d')]);
        }
        
        fclose($file);
        return $filepath;
    }
}
?>