<?php
/**
 * Clase para manejo de vacantes de empleo
 * Turpial Airlines, C.A.
 */

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

class JobVacancy {
    private $db;
    
    public function __construct() {
        $this->db = new Database();
    }
    
    public function getAll($filters = []) {
        $sql = "SELECT jv.*, c.name as category_name, d.name as division_name,
                       d.id as division_id,
                       COUNT(a.id) as application_count
                FROM job_vacancies jv
                JOIN categories c ON jv.category_id = c.id
                JOIN divisions d ON c.division_id = d.id
                LEFT JOIN applications a ON jv.id = a.job_vacancy_id
                WHERE 1=1";
        
        $params = [];
        
        if (!empty($filters['division_id'])) {
            $sql .= " AND d.id = ?";
            $params[] = $filters['division_id'];
        }
        
        if (!empty($filters['category_id'])) {
            $sql .= " AND c.id = ?";
            $params[] = $filters['category_id'];
        }
        
        if (isset($filters['is_active'])) {
            $sql .= " AND jv.is_active = ?";
            $params[] = $filters['is_active'];
        }
        
        if (!empty($filters['search'])) {
            $sql .= " AND (jv.title LIKE ? OR jv.description LIKE ?)";
            $search = '%' . $filters['search'] . '%';
            $params[] = $search;
            $params[] = $search;
        }
        
        $sql .= " GROUP BY jv.id ORDER BY jv.is_featured DESC, jv.created_at DESC";
        
        return $this->db->fetchAll($sql, $params);
    }
    
    public function getById($id) {
        $sql = "SELECT jv.*, c.name as category_name, d.name as division_name,
                       d.id as division_id, u.full_name as created_by_name
                FROM job_vacancies jv
                JOIN categories c ON jv.category_id = c.id
                JOIN divisions d ON c.division_id = d.id
                LEFT JOIN users u ON jv.created_by = u.id
                WHERE jv.id = ?";
        
        return $this->db->fetch($sql, [$id]);
    }
    
    public function getActiveVacancies($limit = null) {
        $sql = "SELECT jv.*, c.name as category_name, d.name as division_name,
                       COUNT(a.id) as application_count
                FROM job_vacancies jv
                JOIN categories c ON jv.category_id = c.id
                JOIN divisions d ON c.division_id = d.id
                LEFT JOIN applications a ON jv.id = a.job_vacancy_id
                WHERE jv.is_active = 1 AND (jv.deadline IS NULL OR jv.deadline >= CURDATE())
                GROUP BY jv.id
                ORDER BY jv.is_featured DESC, jv.created_at DESC";
        
        if ($limit) {
            $sql .= " LIMIT $limit";
        }
        
        return $this->db->fetchAll($sql);
    }
    
    public function create($data) {
        $sql = "INSERT INTO job_vacancies 
                (category_id, title, description, requirements, responsibilities, 
                 salary_range, employment_type, location, experience_required, 
                 education_required, deadline, max_applicants, is_featured, created_by) 
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        
        $this->db->query($sql, [
            $data['category_id'],
            $data['title'],
            $data['description'],
            $data['requirements'],
            $data['responsibilities'],
            $data['salary_range'],
            $data['employment_type'],
            $data['location'],
            $data['experience_required'],
            $data['education_required'],
            !empty($data['deadline']) ? $data['deadline'] : null,
            $data['max_applicants'],
            isset($data['is_featured']) ? 1 : 0,
            $_SESSION['user_id']
        ]);
        
        return $this->db->lastInsertId();
    }
    
    public function update($id, $data) {
        $sql = "UPDATE job_vacancies SET
                category_id = ?, title = ?, description = ?, requirements = ?,
                responsibilities = ?, salary_range = ?, employment_type = ?,
                location = ?, experience_required = ?, education_required = ?,
                deadline = ?, max_applicants = ?, is_featured = ?,
                updated_at = CURRENT_TIMESTAMP
                WHERE id = ?";
        
        return $this->db->query($sql, [
            $data['category_id'],
            $data['title'],
            $data['description'],
            $data['requirements'],
            $data['responsibilities'],
            $data['salary_range'],
            $data['employment_type'],
            $data['location'],
            $data['experience_required'],
            $data['education_required'],
            !empty($data['deadline']) ? $data['deadline'] : null,
            $data['max_applicants'],
            isset($data['is_featured']) ? 1 : 0,
            $id
        ]);
    }
    
    public function delete($id) {
        $sql = "UPDATE job_vacancies SET is_active = 0 WHERE id = ?";
        return $this->db->query($sql, [$id]);
    }
    
    public function toggleStatus($id) {
        $sql = "UPDATE job_vacancies SET is_active = NOT is_active WHERE id = ?";
        return $this->db->query($sql, [$id]);
    }
    
    public function getStats() {
        $stats = [];
        
        try {
            // Total de vacantes
            $sql = "SELECT COUNT(*) as total FROM job_vacancies WHERE is_active = 1";
            $result = $this->db->fetch($sql);
            $stats['total_vacancies'] = $result ? $result['total'] : 0;
            
            // Total de aplicaciones
            $sql = "SELECT COUNT(*) as total FROM applications";
            $result = $this->db->fetch($sql);
            $stats['total_applications'] = $result ? $result['total'] : 0;
            
            // Aplicaciones pendientes
            $sql = "SELECT COUNT(*) as total FROM applications WHERE status = 'Pendiente'";
            $result = $this->db->fetch($sql);
            $stats['pending_applications'] = $result ? $result['total'] : 0;
            
            // Vacantes por división
            $sql = "SELECT d.name, COUNT(jv.id) as count
                    FROM divisions d
                    LEFT JOIN categories c ON d.id = c.division_id
                    LEFT JOIN job_vacancies jv ON c.id = jv.category_id AND jv.is_active = 1
                    GROUP BY d.id, d.name
                    ORDER BY count DESC";
            $stats['vacancies_by_division'] = $this->db->fetchAll($sql) ?: [];
        } catch (Exception $e) {
            // En caso de error, retornar valores por defecto
            $stats['total_vacancies'] = 0;
            $stats['total_applications'] = 0;
            $stats['pending_applications'] = 0;
            $stats['vacancies_by_division'] = [];
        }
        
        return $stats;
    }
    
    public function getApplicationStats() {
        $sql = "SELECT jv.title, COUNT(a.id) as application_count
                FROM job_vacancies jv
                LEFT JOIN applications a ON jv.id = a.job_vacancy_id
                WHERE jv.is_active = 1
                GROUP BY jv.id, jv.title
                ORDER BY application_count DESC
                LIMIT 10";
        
        return $this->db->fetchAll($sql);
    }
    
    public function getTopVacancies($limit = 10) {
        $sql = "SELECT jv.title, d.name as division_name, COUNT(a.id) as applications_count
                FROM job_vacancies jv
                JOIN categories c ON jv.category_id = c.id
                JOIN divisions d ON c.division_id = d.id
                LEFT JOIN applications a ON jv.id = a.job_vacancy_id
                WHERE jv.is_active = 1
                GROUP BY jv.id, jv.title, d.name
                ORDER BY applications_count DESC
                LIMIT ?";
        
        return $this->db->fetchAll($sql, [$limit]);
    }
}

?>