<?php
/**
 * Clase para manejo de divisiones y categorías
 * Turpial Airlines, C.A.
 */

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

class Division {
    private $db;
    
    public function __construct() {
        $this->db = new Database();
    }
    
    public function getAll($active_only = false) {
        $sql = "SELECT d.*, 
                       COUNT(c.id) as categories_count,
                       COUNT(jv.id) as vacancies_count
                FROM divisions d
                LEFT JOIN categories c ON d.id = c.division_id AND c.is_active = 1
                LEFT JOIN job_vacancies jv ON c.id = jv.category_id AND jv.is_active = 1
                WHERE 1=1";
        
        if ($active_only) {
            $sql .= " AND d.is_active = 1";
        }
        
        $sql .= " GROUP BY d.id ORDER BY d.name";
        
        return $this->db->fetchAll($sql);
    }
    
    public function getById($id) {
        $sql = "SELECT * FROM divisions WHERE id = ?";
        return $this->db->fetch($sql, [$id]);
    }
    
    public function create($data) {
        $sql = "INSERT INTO divisions (name, description, manager) VALUES (?, ?, ?)";
        $this->db->query($sql, [
            $data['name'],
            $data['description'],
            $data['manager']
        ]);
        return $this->db->lastInsertId();
    }
    
    public function update($id, $data) {
        $sql = "UPDATE divisions SET name = ?, description = ?, manager = ?, 
                updated_at = CURRENT_TIMESTAMP WHERE id = ?";
        return $this->db->query($sql, [
            $data['name'],
            $data['description'],
            $data['manager'],
            $id
        ]);
    }
    
    public function delete($id) {
        $sql = "UPDATE divisions SET is_active = 0 WHERE id = ?";
        return $this->db->query($sql, [$id]);
    }
    
    public function toggleStatus($id) {
        $sql = "UPDATE divisions SET is_active = NOT is_active WHERE id = ?";
        return $this->db->query($sql, [$id]);
    }
    
    public function getWithCategories($active_only = true) {
        $sql = "SELECT d.*, 
                       GROUP_CONCAT(CONCAT(c.id, ':', c.name) SEPARATOR '|') as categories
                FROM divisions d
                LEFT JOIN categories c ON d.id = c.division_id";
        
        if ($active_only) {
            $sql .= " AND c.is_active = 1";
        }
        
        $sql .= " WHERE d.is_active = 1 GROUP BY d.id ORDER BY d.name";
        
        $divisions = $this->db->fetchAll($sql);
        
        // Procesar categorías
        foreach ($divisions as &$division) {
            $categories = [];
            if ($division['categories']) {
                $cats = explode('|', $division['categories']);
                foreach ($cats as $cat) {
                    $parts = explode(':', $cat);
                    if (count($parts) == 2) {
                        $categories[] = ['id' => $parts[0], 'name' => $parts[1]];
                    }
                }
            }
            $division['categories_list'] = $categories;
        }
        
        return $divisions;
    }
}

class Category {
    private $db;
    
    public function __construct() {
        $this->db = new Database();
    }
    
    public function getAll($filters = []) {
        $sql = "SELECT c.*, d.name as division_name,
                       COUNT(jv.id) as vacancies_count
                FROM categories c
                JOIN divisions d ON c.division_id = d.id
                LEFT JOIN job_vacancies jv ON c.id = jv.category_id AND jv.is_active = 1
                WHERE 1=1";
        
        $params = [];
        
        if (!empty($filters['division_id'])) {
            $sql .= " AND c.division_id = ?";
            $params[] = $filters['division_id'];
        }
        
        if (isset($filters['is_active'])) {
            $sql .= " AND c.is_active = ?";
            $params[] = $filters['is_active'];
        }
        
        $sql .= " GROUP BY c.id ORDER BY d.name, c.name";
        
        return $this->db->fetchAll($sql, $params);
    }
    
    public function getById($id) {
        $sql = "SELECT c.*, d.name as division_name
                FROM categories c
                JOIN divisions d ON c.division_id = d.id
                WHERE c.id = ?";
        return $this->db->fetch($sql, [$id]);
    }
    
    public function getByDivision($division_id, $active_only = true) {
        $sql = "SELECT * FROM categories WHERE division_id = ?";
        $params = [$division_id];
        
        if ($active_only) {
            $sql .= " AND is_active = 1";
        }
        
        $sql .= " ORDER BY name";
        
        return $this->db->fetchAll($sql, $params);
    }
    
    public function create($data) {
        $sql = "INSERT INTO categories (division_id, name, description) VALUES (?, ?, ?)";
        $this->db->query($sql, [
            $data['division_id'],
            $data['name'],
            $data['description']
        ]);
        return $this->db->lastInsertId();
    }
    
    public function update($id, $data) {
        $sql = "UPDATE categories SET division_id = ?, name = ?, description = ?,
                updated_at = CURRENT_TIMESTAMP WHERE id = ?";
        return $this->db->query($sql, [
            $data['division_id'],
            $data['name'],
            $data['description'],
            $id
        ]);
    }
    
    public function delete($id) {
        $sql = "UPDATE categories SET is_active = 0 WHERE id = ?";
        return $this->db->query($sql, [$id]);
    }
    
    public function toggleStatus($id) {
        $sql = "UPDATE categories SET is_active = NOT is_active WHERE id = ?";
        return $this->db->query($sql, [$id]);
    }
    
    public function getStats() {
        $stats = [];
        
        // Total de divisiones
        $sql = "SELECT COUNT(*) as total FROM divisions WHERE is_active = 1";
        $result = $this->db->fetch($sql);
        $stats['total_divisions'] = $result['total'];
        
        // Divisiones con más vacantes
        $sql = "SELECT d.name, COUNT(jv.id) as vacancies_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
                WHERE d.is_active = 1
                GROUP BY d.id, d.name
                ORDER BY vacancies_count DESC";
        $stats['divisions_by_vacancies'] = $this->db->fetchAll($sql);
        
        return $stats;
    }
}

?>