<?php
/**
 * Clase para manejo de aplicaciones/postulaciones
 * Turpial Airlines, C.A.
 */

require_once dirname(__DIR__) . '/config/database.php';

class Application {
    private $db;
    
    public function __construct() {
        $this->db = new Database();
    }
    
    public function getAll($filters = []) {
        $sql = "SELECT a.*, jv.title as job_title, c.name as category_name, 
                       d.name as division_name, u.full_name as reviewed_by_name
                FROM applications a
                JOIN job_vacancies jv ON a.job_vacancy_id = jv.id
                JOIN categories c ON jv.category_id = c.id
                JOIN divisions d ON c.division_id = d.id
                LEFT JOIN users u ON a.reviewed_by = u.id
                WHERE 1=1";
        
        $params = [];
        
        if (!empty($filters['job_vacancy_id'])) {
            $sql .= " AND a.job_vacancy_id = ?";
            $params[] = $filters['job_vacancy_id'];
        }
        
        if (!empty($filters['status'])) {
            $sql .= " AND a.status = ?";
            $params[] = $filters['status'];
        }
        
        if (!empty($filters['division_id'])) {
            $sql .= " AND d.id = ?";
            $params[] = $filters['division_id'];
        }
        
        if (!empty($filters['date_from'])) {
            $sql .= " AND DATE(a.application_date) >= ?";
            $params[] = $filters['date_from'];
        }
        
        if (!empty($filters['date_to'])) {
            $sql .= " AND DATE(a.application_date) <= ?";
            $params[] = $filters['date_to'];
        }
        
        $sql .= " ORDER BY a.application_date DESC";
        
        return $this->db->fetchAll($sql, $params);
    }
    
    public function getById($id) {
        $sql = "SELECT a.*, jv.title as job_title, jv.description as job_description,
                       c.name as category_name, d.name as division_name,
                       u.full_name as reviewed_by_name
                FROM applications a
                JOIN job_vacancies jv ON a.job_vacancy_id = jv.id
                JOIN categories c ON jv.category_id = c.id
                JOIN divisions d ON c.division_id = d.id
                LEFT JOIN users u ON a.reviewed_by = u.id
                WHERE a.id = ?";
        
        return $this->db->fetch($sql, [$id]);
    }
    
    public function create($data) {
        $sql = "INSERT INTO applications 
                (job_vacancy_id, full_name, email, phone, address, city, country,
                 education_level, years_experience, current_position, cover_letter,
                 cv_filename, cv_path) 
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        
        $this->db->query($sql, [
            $data['job_vacancy_id'],
            $data['full_name'],
            $data['email'],
            $data['phone'],
            $data['address'],
            $data['city'],
            $data['country'] ?? 'Venezuela',
            $data['education_level'],
            $data['years_experience'],
            $data['current_position'],
            $data['cover_letter'],
            $data['cv_filename'] ?? null,
            $data['cv_path'] ?? null
        ]);
        
        return $this->db->lastInsertId();
    }
    
    public function updateStatus($id, $status, $notes = null) {
        $sql = "UPDATE applications SET 
                status = ?, notes = ?, reviewed_by = ?, reviewed_at = CURRENT_TIMESTAMP
                WHERE id = ?";
        
        return $this->db->query($sql, [
            $status,
            $notes,
            $_SESSION['user_id'] ?? null,
            $id
        ]);
    }
    
    public function getByJobVacancy($job_vacancy_id) {
        $sql = "SELECT a.*, jv.title as job_title
                FROM applications a
                JOIN job_vacancies jv ON a.job_vacancy_id = jv.id
                WHERE a.job_vacancy_id = ?
                ORDER BY a.application_date DESC";
        
        return $this->db->fetchAll($sql, [$job_vacancy_id]);
    }
    
    public function getStats() {
        $stats = [];
        
        // Total de aplicaciones
        $sql = "SELECT COUNT(*) as total FROM applications";
        $stats['total_applications'] = $this->db->fetch($sql)['total'];
        
        // Aplicaciones por estado
        $sql = "SELECT status, COUNT(*) as count 
                FROM applications 
                GROUP BY status
                ORDER BY count DESC";
        $stats['applications_by_status'] = $this->db->fetchAll($sql);
        
        // Aplicaciones por mes (últimos 12 meses)
        $sql = "SELECT DATE_FORMAT(application_date, '%Y-%m') as month,
                       COUNT(*) as count
                FROM applications 
                WHERE application_date >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
                GROUP BY DATE_FORMAT(application_date, '%Y-%m')
                ORDER BY month";
        $stats['applications_by_month'] = $this->db->fetchAll($sql);
        
        // Top 5 ciudades con más aplicaciones
        $sql = "SELECT city, COUNT(*) as count
                FROM applications
                WHERE city IS NOT NULL AND city != ''
                GROUP BY city
                ORDER BY count DESC
                LIMIT 5";
        $stats['top_cities'] = $this->db->fetchAll($sql);
        
        // Nivel educativo de aplicantes
        $sql = "SELECT education_level, COUNT(*) as count
                FROM applications
                WHERE education_level IS NOT NULL AND education_level != ''
                GROUP BY education_level
                ORDER BY count DESC";
        $stats['education_levels'] = $this->db->fetchAll($sql);
        
        return $stats;
    }
    
    public function getMonthlyStats() {
        $sql = "SELECT 
                    DATE_FORMAT(application_date, '%b %Y') as month,
                    COUNT(*) as count
                FROM applications
                WHERE application_date >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
                GROUP BY DATE_FORMAT(application_date, '%Y-%m')
                ORDER BY application_date DESC";
        
        return $this->db->fetchAll($sql);
    }
    
    public function getApplicationsByStatus() {
        $sql = "SELECT 
                    CASE 
                        WHEN status = 'pending' THEN 'Pendiente'
                        WHEN status = 'reviewing' THEN 'En Revisión'
                        WHEN status = 'accepted' THEN 'Aceptada'
                        WHEN status = 'rejected' THEN 'Rechazada'
                        ELSE 'Otro'
                    END as status,
                    COUNT(*) as count
                FROM applications
                GROUP BY status
                ORDER BY count DESC";
        
        return $this->db->fetchAll($sql);
    }
    
    public function getRecentApplications($limit = 10) {
        $sql = "SELECT a.*, jv.title as job_title, c.name as category_name
                FROM applications a
                JOIN job_vacancies jv ON a.job_vacancy_id = jv.id
                JOIN categories c ON jv.category_id = c.id
                ORDER BY a.application_date DESC
                LIMIT ?";
        
        return $this->db->fetchAll($sql, [$limit]);
    }
    
    public function checkDuplicateApplication($email, $job_vacancy_id) {
        $sql = "SELECT COUNT(*) as count 
                FROM applications 
                WHERE email = ? AND job_vacancy_id = ?";
        
        $result = $this->db->fetch($sql, [$email, $job_vacancy_id]);
        return $result['count'] > 0;
    }
    
    public function delete($id) {
        // Obtener información del CV para eliminarlo
        $application = $this->getById($id);
        
        if ($application && $application['cv_path'] && file_exists($application['cv_path'])) {
            unlink($application['cv_path']);
        }
        
        $sql = "DELETE FROM applications WHERE id = ?";
        return $this->db->query($sql, [$id]);
    }
}

?>