<?php
namespace Thirty\controllers;

require_once __DIR__ . '/../admin_guard.php';

class AdminController {
    public static function dashboard(){
        require_admin_or_moderator();
        $DB = nebusa_pdo();
        $stats = [
            'users' => 0,
            'photos' => 0,
            'comments' => 0,
            'friends' => 0,
            'reports' => 0,
            'moderators' => 0,
            'total_users' => 0,
            'banned_users' => 0,
            'temp_banned_users' => 0
        ];

        if ($DB instanceof \PDO) {
          try {
            $stats['users'] = (int)$DB->query("SELECT COUNT(*) FROM users")->fetchColumn();
            $stats['photos'] = (int)$DB->query("SELECT COUNT(*) FROM photos")->fetchColumn();
            $stats['comments'] = (int)$DB->query("SELECT COUNT(*) FROM comments")->fetchColumn();
            $stats['friends'] = (int)$DB->query("SELECT COUNT(*) FROM friendships")->fetchColumn();
            $stats['reports'] = (int)$DB->query("SELECT COUNT(*) FROM reports WHERE status = 'pending'")->fetchColumn();
            $stats['moderators'] = (int)$DB->query("SELECT COUNT(*) FROM users WHERE role IN ('moderator', 'admin')")->fetchColumn();
            $stats['total_users'] = $stats['users'];
            $stats['banned_users'] = (int)$DB->query("SELECT COUNT(*) FROM users WHERE status = 'banned' AND (banned_until IS NULL OR banned_until > NOW())")->fetchColumn();
            $stats['temp_banned_users'] = (int)$DB->query("SELECT COUNT(*) FROM users WHERE status = 'banned' AND banned_until IS NOT NULL AND banned_until > NOW()")->fetchColumn();
          } catch (\Throwable $e) {}
        }

        include __DIR__ . '/../views/partials/admin_header.php';
        include __DIR__ . '/../views/admin/dashboard.php';
        include __DIR__ . '/../views/partials/admin_footer.php';
    }

    public static function users(){
        require_admin_or_moderator();
        $DB = nebusa_pdo();
        $users = [];
        $search = trim($_GET['q'] ?? '');
        $role_filter = $_GET['role'] ?? '';
        $status_filter = $_GET['status'] ?? '';
        $sort = $_GET['sort'] ?? 'newest';

        // Estadísticas
        $stats = ['total_users' => 0];
        if ($DB instanceof \PDO) {
            try {
                $stats['total_users'] = (int)$DB->query("SELECT COUNT(*) FROM users")->fetchColumn();
            } catch (\Throwable $e) {}
        }

        if ($DB instanceof \PDO) {
            $sql = "SELECT id, email, nick, first_name, last_name, status, role, created_at, last_login, banned_until, ban_reason FROM users WHERE 1=1";
            $params = [];

            if ($search !== '') {
                $sql .= " AND (email LIKE ? OR nick LIKE ? OR first_name LIKE ? OR last_name LIKE ?)";
                $search_term = '%'.$search.'%';
                $params = array_merge($params, [$search_term, $search_term, $search_term, $search_term]);
            }

            if ($role_filter !== '') {
                $sql .= " AND role = ?";
                $params[] = $role_filter;
            }

            if ($status_filter !== '') {
                $sql .= " AND status = ?";
                $params[] = $status_filter;
            }

            // Ordenamiento - usando switch en lugar de match
            $order_by = 'id DESC'; // valor por defecto
            switch ($sort) {
                case 'oldest':
                    $order_by = 'id ASC';
                    break;
                case 'last_login':
                    $order_by = 'last_login DESC';
                    break;
                case 'name':
                    $order_by = 'nick ASC';
                    break;
                default:
                    $order_by = 'id DESC';
                    break;
            }

            $sql .= " ORDER BY $order_by LIMIT 200";

            $st = $DB->prepare($sql);
            $st->execute($params);
            $users = $st->fetchAll(\PDO::FETCH_ASSOC);
        }

        include __DIR__ . '/../views/partials/admin_header.php';
        include __DIR__ . '/../views/admin/users/index.php';
        include __DIR__ . '/../views/partials/admin_footer.php';
    }

    public static function users_update_role(){
        require_admin();
        $DB = nebusa_pdo();
        $id = (int)($_POST['id'] ?? 0);
        $role = in_array(($_POST['role'] ?? 'user'), ['user','moderator','admin']) ? $_POST['role'] : 'user';

        if ($id > 0 && $DB instanceof \PDO) {
            // No permitir quitar el último admin
            if ($role !== 'admin') {
                $st = $DB->prepare("SELECT COUNT(*) FROM users WHERE role='admin' AND id != ?");
                $st->execute([$id]);
                if ((int)$st->fetchColumn() === 0) {
                    set_flash('No puedes quitar el último administrador del sistema.', 'error');
                    header('Location: ' . (function_exists('base_url') ? base_url('/admin/users') : '/admin/users'));
                    exit;
                }
            }

            // Verificar que el usuario existe
            $st = $DB->prepare("SELECT nick FROM users WHERE id = ?");
            $st->execute([$id]);
            $user = $st->fetch();

            if ($user) {
                $st = $DB->prepare("UPDATE users SET role=? WHERE id=?");
                $st->execute([$role, $id]);

                // Log de la acción
                $admin = user();
                $log_message = "Admin {$admin['nick']} cambió el rol de {$user['nick']} a {$role}";
                self::logSecurityEvent('role_change', $log_message, $id);

                set_flash("Rol de {$user['nick']} actualizado correctamente a " . self::getRoleDisplayName($role), 'success');
            } else {
                set_flash('Usuario no encontrado.', 'error');
            }
        } else {
            set_flash('ID de usuario inválido.', 'error');
        }

        header('Location: ' . (function_exists('base_url') ? base_url('/admin/users') : '/admin/users'));
        exit;
    }

    public static function users_update_status(){
        require_admin();
        $DB = nebusa_pdo();
        $id = (int)($_POST['id'] ?? 0);
        $status = in_array(($_POST['status'] ?? 'active'), ['active','disabled','banned']) ? $_POST['status'] : 'active';
        $ban_duration = (int)($_POST['ban_duration'] ?? 0);
        $ban_reason = trim($_POST['ban_reason'] ?? '');

        if ($id > 0 && $DB instanceof \PDO) {
            // Verificar que el usuario existe
            $st = $DB->prepare("SELECT nick, role FROM users WHERE id = ?");
            $st->execute([$id]);
            $user = $st->fetch();

            if ($user) {
                // No permitir deshabilitar o banear a otros admins
                $current_user = user();
                if ($user['role'] === 'admin' && $user['nick'] !== $current_user['nick']) {
                    set_flash('No puedes modificar el estado de otros administradores.', 'error');
                    header('Location: ' . (function_exists('base_url') ? base_url('/admin/users') : '/admin/users'));
                    exit;
                }

                // Preparar datos para el ban temporal
                $banned_until = null;
                $ban_type = 'permanent';

                if ($status === 'banned' && $ban_duration > 0) {
                    $banned_until = date('Y-m-d H:i:s', strtotime("+{$ban_duration} days"));
                    $ban_type = 'temporary';
                } elseif ($status !== 'banned') {
                    // Si no es ban, limpiar campos de ban
                    $banned_until = null;
                    $ban_reason = null;
                }

                $st = $DB->prepare("UPDATE users SET status=?, banned_until=?, ban_reason=? WHERE id=?");
                $st->execute([$status, $banned_until, $ban_reason, $id]);

                // Log del ban en la tabla ban_logs
                if ($status === 'banned') {
                    $admin = user();
                    $st = $DB->prepare("
                        INSERT INTO ban_logs (user_id, admin_id, ban_type, ban_duration_days, banned_until, ban_reason)
                        VALUES (?, ?, ?, ?, ?, ?)
                    ");
                    $st->execute([
                        $id,
                        $admin['id'],
                        $ban_type,
                        $ban_duration > 0 ? $ban_duration : null,
                        $banned_until,
                        $ban_reason
                    ]);
                }

                // Log de la acción
                $admin = user();
                $duration_text = $ban_duration > 0 ? " por {$ban_duration} días" : " permanente";
                $log_message = "Admin {$admin['nick']} cambió el estado de {$user['nick']} a {$status}{$duration_text}";
                self::logSecurityEvent('status_change', $log_message, $id);

                $message = "Estado de {$user['nick']} actualizado correctamente a " . self::getStatusDisplayName($status);
                if ($status === 'banned' && $ban_duration > 0) {
                    $message .= " por {$ban_duration} días";
                }
                set_flash($message, 'success');
            } else {
                set_flash('Usuario no encontrado.', 'error');
            }
        } else {
            set_flash('ID de usuario inválido.', 'error');
        }

        header('Location: ' . (function_exists('base_url') ? base_url('/admin/users') : '/admin/users'));
        exit;
    }

    public static function users_unban(){
        require_admin();
        $DB = nebusa_pdo();
        $id = (int)($_POST['id'] ?? 0);

        if ($id > 0 && $DB instanceof \PDO) {
            // Verificar que el usuario existe
            $st = $DB->prepare("SELECT nick FROM users WHERE id = ?");
            $st->execute([$id]);
            $user = $st->fetch();

            if ($user) {
                $st = $DB->prepare("UPDATE users SET status='active', banned_until=NULL, ban_reason=NULL WHERE id=?");
                $st->execute([$id]);

                // Log de la acción
                $admin = user();
                $log_message = "Admin {$admin['nick']} desbaneó a {$user['nick']}";
                self::logSecurityEvent('user_unban', $log_message, $id);

                set_flash("Usuario {$user['nick']} desbaneado correctamente.", 'success');
            } else {
                set_flash('Usuario no encontrado.', 'error');
            }
        } else {
            set_flash('ID de usuario inválido.', 'error');
        }

        header('Location: ' . (function_exists('base_url') ? base_url('/admin/users') : '/admin/users'));
        exit;
    }

    public static function view_user_profile(){
        require_admin_or_moderator();
        $DB = nebusa_pdo();
        $id = (int)($_GET['id'] ?? 0);

        if ($id <= 0) {
            set_flash('ID de usuario inválido.', 'error');
            header('Location: ' . (function_exists('base_url') ? base_url('/admin/users') : '/admin/users'));
            exit;
        }

        // Obtener información del usuario
        $user = [];
        if ($DB instanceof \PDO) {
            $st = $DB->prepare("
                SELECT u.*, p.bio, p.province_code, p.municipality_code, p.avatar_url,
                       prov.name as province_name, mun.name as municipality_name
                FROM users u
                LEFT JOIN profiles p ON p.user_id = u.id
                LEFT JOIN provinces prov ON prov.code = p.province_code
                LEFT JOIN municipalities mun ON mun.code = p.municipality_code
                WHERE u.id = ?
            ");
            $st->execute([$id]);
            $user = $st->fetch(\PDO::FETCH_ASSOC);
        }

        if (!$user) {
            set_flash('Usuario no encontrado.', 'error');
            header('Location: ' . (function_exists('base_url') ? base_url('/admin/users') : '/admin/users'));
            exit;
        }

        // Marcar que estamos viendo como administrador
        $_SESSION['admin_viewing_profile'] = true;

        // Redirigir al perfil normal pero con el ID específico
        header('Location: ' . (function_exists('base_url') ? base_url('/profile?user_id=' . $id) : '/profile?user_id=' . $id));
        exit;
    }

    public static function ban_management(){
        require_admin();
        $DB = nebusa_pdo();
        $bans = [];

        if ($DB instanceof \PDO) {
            $st = $DB->prepare("
                SELECT
                    u.id, u.nick, u.email, u.banned_until, u.ban_reason, u.status,
                    bl.created_at as ban_date, bl.ban_type, bl.ban_duration_days,
                    admin.nick as admin_nick
                FROM users u
                LEFT JOIN ban_logs bl ON bl.user_id = u.id AND bl.id = (
                    SELECT MAX(id) FROM ban_logs WHERE user_id = u.id
                )
                LEFT JOIN users admin ON admin.id = bl.admin_id
                WHERE u.status = 'banned' AND (u.banned_until IS NULL OR u.banned_until > NOW())
                ORDER BY u.banned_until ASC, bl.created_at DESC
            ");
            $st->execute();
            $bans = $st->fetchAll(\PDO::FETCH_ASSOC);
        }

        include __DIR__ . '/../views/partials/admin_header.php';
        include __DIR__ . '/../views/admin/moderation/bans.php';
        include __DIR__ . '/../views/partials/admin_footer.php';
    }

    public static function check_expired_bans(){
        require_admin();
        $DB = nebusa_pdo();

        if ($DB instanceof \PDO) {
            $st = $DB->prepare("
                SELECT id, nick FROM users
                WHERE status = 'banned'
                AND banned_until IS NOT NULL
                AND banned_until <= NOW()
            ");
            $st->execute();
            $expired_bans = $st->fetchAll(\PDO::FETCH_ASSOC);

            $unbanned_count = 0;
            foreach ($expired_bans as $user) {
                $update_st = $DB->prepare("UPDATE users SET status='active', banned_until=NULL, ban_reason=NULL WHERE id=?");
                $update_st->execute([$user['id']]);
                $unbanned_count++;

                // Log de la acción automática
                self::logSecurityEvent('auto_unban', "Ban temporal expirado para {$user['nick']}", $user['id']);
            }

            if ($unbanned_count > 0) {
                set_flash("Se han levantado {$unbanned_count} baneos temporales expirados.", 'success');
            } else {
                set_flash('No hay baneos temporales expirados.', 'info');
            }
        }

        header('Location: ' . (function_exists('base_url') ? base_url('/admin/ban-management') : '/admin/ban-management'));
        exit;
    }

    public static function photos(){
        require_admin_or_moderator();
        $DB = nebusa_pdo();
        $photos = [];

        // Parámetros de filtro
        $search = trim($_GET['q'] ?? '');
        $status_filter = $_GET['status'] ?? '';
        $reports_filter = $_GET['reports'] ?? '';

        // Configuración de paginación
        $per_page = 20;
        $current_page = max(1, (int)($_GET['page'] ?? 1));
        $offset = ($current_page - 1) * $per_page;

        $stats = [
            'total_photos' => 0,
            'reported_count' => 0,
            'unique_users_count' => 0
        ];

        if ($DB instanceof \PDO) {
            try {
                // Construir consulta base CORREGIDA
                $sql = "
                    SELECT
                        p.id,
                        p.owner_id,
                        p.storage_rel_path,
                        p.created_at,
                        p.nsfw_flag,
                        p.width,
                        p.height,
                        u.nick,
                        u.email,
                        po.text as caption,
                        (SELECT COUNT(*) FROM reports WHERE target_type='photo' AND target_id=p.id AND status='pending') as pending_reports
                    FROM photos p
                    JOIN users u ON u.id = p.owner_id
                    LEFT JOIN posts po ON p.post_id = po.id
                    WHERE 1=1
                ";

                $count_sql = "
                    SELECT COUNT(*)
                    FROM photos p
                    JOIN users u ON u.id = p.owner_id
                    LEFT JOIN posts po ON p.post_id = po.id
                    WHERE 1=1
                ";

                $params = [];
                $count_params = [];

                // Aplicar filtros
                if ($search !== '') {
                    $sql .= " AND (u.nick LIKE ? OR u.email LIKE ? OR po.text LIKE ?)";
                    $count_sql .= " AND (u.nick LIKE ? OR u.email LIKE ? OR po.text LIKE ?)";
                    $search_term = '%'.$search.'%';
                    $params = array_merge($params, [$search_term, $search_term, $search_term]);
                    $count_params = array_merge($count_params, [$search_term, $search_term, $search_term]);
                }

                if ($status_filter !== '') {
}

                if ($reports_filter === 'with_reports') {
                    $sql .= " AND (SELECT COUNT(*) FROM reports WHERE target_type='photo' AND target_id=p.id AND status='pending') > 0";
                    $count_sql .= " AND (SELECT COUNT(*) FROM reports WHERE target_type='photo' AND target_id=p.id AND status='pending') > 0";
                } elseif ($reports_filter === 'no_reports') {
                    $sql .= " AND (SELECT COUNT(*) FROM reports WHERE target_type='photo' AND target_id=p.id AND status='pending') = 0";
                    $count_sql .= " AND (SELECT COUNT(*) FROM reports WHERE target_type='photo' AND target_id=p.id AND status='pending') = 0";
                }

                // Ordenamiento
                $sql .= " ORDER BY pending_reports DESC, p.created_at DESC LIMIT ? OFFSET ?";

                // Obtener fotos usando bindValue para tipos correctos
                $st = $DB->prepare($sql);

                // Bind de parámetros normales
                $param_index = 1;
                foreach ($params as $param) {
                    $st->bindValue($param_index, $param);
                    $param_index++;
                }

                // Bind de LIMIT y OFFSET con tipos específicos
                $st->bindValue($param_index++, $per_page, \PDO::PARAM_INT);
                $st->bindValue($param_index, $offset, \PDO::PARAM_INT);

                $st->execute();
                $photos = $st->fetchAll(\PDO::FETCH_ASSOC);

                // Obtener total para paginación
                $count_st = $DB->prepare($count_sql);
                $count_st->execute($count_params);
                $total_photos = (int)$count_st->fetchColumn();
                $total_pages = ceil($total_photos / $per_page);

                // Calcular estadísticas
                $stats['total_photos'] = $total_photos;

                // Contar fotos con reportes (sin filtros para estadísticas generales)
                $reported_st = $DB->prepare("
                    SELECT COUNT(DISTINCT p.id)
                    FROM photos p
                    WHERE (SELECT COUNT(*) FROM reports WHERE target_type='photo' AND target_id=p.id AND status='pending') > 0
                ");
                $reported_st->execute();
                $stats['reported_count'] = (int)$reported_st->fetchColumn();

                // Contar usuarios únicos (sin filtros para estadísticas generales)
                $users_st = $DB->prepare("SELECT COUNT(DISTINCT owner_id) FROM photos");
                $users_st->execute();
                $stats['unique_users_count'] = (int)$users_st->fetchColumn();

                // Pasar variables a la vista
                $GLOBALS['total_photos'] = $stats['total_photos'];
                $GLOBALS['reported_count'] = $stats['reported_count'];
                $GLOBALS['unique_users_count'] = $stats['unique_users_count'];
                $GLOBALS['total_pages'] = $total_pages;
                $GLOBALS['current_page'] = $current_page;
                $GLOBALS['per_page'] = $per_page;
                $GLOBALS['search'] = $search;
                $GLOBALS['status_filter'] = $status_filter;
                $GLOBALS['reports_filter'] = $reports_filter;

            } catch (\Throwable $e) {
                error_log("Error en photos admin: " . $e->getMessage());
            }
        }

        include __DIR__ . '/../views/partials/admin_header.php';
        include __DIR__ . '/../views/admin/photos/photos.php';
        include __DIR__ . '/../views/partials/admin_footer.php';
    }

    public static function photos_update_status(){
        require_admin();
        $DB = nebusa_pdo();
        $id = (int)($_POST['id'] ?? 0);
        $status = in_array(($_POST['status'] ?? 'active'), ['active','hidden','deleted']) ? $_POST['status'] : 'active';

        if ($id > 0 && $DB instanceof \PDO) {
            // Obtener información de la foto para el log
            $st = $DB->prepare("SELECT p.storage_rel_path, u.nick FROM photos p JOIN users u ON u.id = p.owner_id WHERE p.id = ?");
            $st->execute([$id]);
            $photo = $st->fetch();

            if ($photo) {
                $st = $DB->prepare("UPDATE photos SET status=? WHERE id=?");
                $st->execute([$status, $id]);

                // Log de la acción
                $admin = user();
                $log_message = "Admin {$admin['nick']} cambió el estado de la foto de {$photo['nick']} a {$status}";
                self::logSecurityEvent('photo_moderation', $log_message, $id);

                set_flash('Estado de la foto actualizado correctamente.', 'success');
            } else {
                set_flash('Foto no encontrada.', 'error');
            }
        } else {
            set_flash('ID de foto inválido.', 'error');
        }

        header('Location: ' . (function_exists('base_url') ? base_url('/admin/photos') : '/admin/photos'));
        exit;
    }

    public static function photos_delete(){
        require_admin();
        $DB = nebusa_pdo();
        $id = (int)($_POST['id'] ?? 0);

        if ($id > 0 && $DB instanceof \PDO) {
            // Primero obtener la ruta para eliminar el archivo
            $st = $DB->prepare("SELECT storage_rel_path, owner_id FROM photos WHERE id=?");
            $st->execute([$id]);
            $photo = $st->fetch();

            if ($photo) {
                // Obtener nombre de usuario para el log
                $user_st = $DB->prepare("SELECT nick FROM users WHERE id = ?");
                $user_st->execute([$photo['owner_id']]);
                $user_nick = $user_st->fetchColumn();

                // Eliminar archivo físico si existe
                $file_path = __DIR__ . '/../public/uploads/' . $photo['storage_rel_path'];
                if (file_exists($file_path)) {
                    unlink($file_path);
                }

                // Eliminar de la base de datos
                $st = $DB->prepare("DELETE FROM photos WHERE id=?");
                $st->execute([$id]);

                // También eliminar comentarios y likes asociados
                $DB->prepare("DELETE FROM comments WHERE photo_id=?")->execute([$id]);
                $DB->prepare("DELETE FROM likes WHERE photo_id=?")->execute([$id]);

                // Log de la acción
                $admin = user();
                $log_message = "Admin {$admin['nick']} eliminó permanentemente una foto de {$user_nick}";
                self::logSecurityEvent('photo_deletion', $log_message, $id);

                set_flash('Foto eliminada permanentemente.', 'success');
            } else {
                set_flash('Foto no encontrada.', 'error');
            }
        } else {
            set_flash('ID de foto inválido.', 'error');
        }

        header('Location: ' . (function_exists('base_url') ? base_url('/admin/photos') : '/admin/photos'));
        exit;
    }

    public static function reports(){
        require_admin_or_moderator();
        $DB = nebusa_pdo();
        $reports = [];
        $stats = ['total' => 0, 'pending' => 0, 'resolved' => 0, 'dismissed' => 0];

        if ($DB instanceof \PDO) {
            $status = $_GET['status'] ?? 'pending';
            $valid_statuses = ['pending', 'resolved', 'dismissed'];
            $status = in_array($status, $valid_statuses) ? $status : 'pending';

            // Nuevos filtros
            $search = trim($_GET['q'] ?? '');
            $type_filter = $_GET['type'] ?? '';

            // Configuración de paginación
            $per_page = 20;
            $current_page = max(1, (int)($_GET['page'] ?? 1));
            $offset = ($current_page - 1) * $per_page;

            // Obtener estadísticas
            $st = $DB->prepare("SELECT status, COUNT(*) as count FROM reports GROUP BY status");
            $st->execute();
            $status_counts = $st->fetchAll(\PDO::FETCH_ASSOC);

            foreach ($status_counts as $row) {
                $stats[$row['status']] = (int)$row['count'];
                $stats['total'] += (int)$row['count'];
            }

            // Construir consulta con filtros
            $sql = "
                SELECT r.*,
                       u_reporter.nick as reporter_nick,
                       u_reported.nick as reported_nick,
                       po.text as photo_text,
                       p.storage_rel_path as photo_path
                FROM reports r
                LEFT JOIN users u_reporter ON u_reporter.id = r.reporter_user_id
                LEFT JOIN users u_reported ON u_reported.id = r.reported_user_id
                LEFT JOIN photos p ON p.id = r.target_id AND r.target_type = 'photo'
                LEFT JOIN posts po ON p.post_id = po.id
                WHERE r.status = ?
            ";

            $count_sql = "SELECT COUNT(*) FROM reports r LEFT JOIN users u_reporter ON u_reporter.id = r.reporter_user_id LEFT JOIN users u_reported ON u_reported.id = r.reported_user_id WHERE r.status = ?";
            $params = [$status];
            $count_params = [$status];

            // Aplicar filtro de búsqueda
            if ($search !== '') {
                $sql .= " AND (u_reporter.nick LIKE ? OR u_reported.nick LIKE ? OR r.reason LIKE ?)";
                $count_sql .= " AND (u_reporter.nick LIKE ? OR u_reported.nick LIKE ? OR r.reason LIKE ?)";
                $search_term = '%'.$search.'%';
                $params = array_merge($params, [$search_term, $search_term, $search_term]);
                $count_params = array_merge($count_params, [$search_term, $search_term, $search_term]);
            }

            // Aplicar filtro por tipo
            if ($type_filter !== '') {
                $sql .= " AND r.target_type = ?";
                $count_sql .= " AND r.target_type = ?";
                $params[] = $type_filter;
                $count_params[] = $type_filter;
            }

            // Ordenamiento
            $sql .= " ORDER BY r.created_at DESC LIMIT ? OFFSET ?";

            // Obtener total de reportes para el estado actual con filtros
            $total_st = $DB->prepare($count_sql);
            $total_st->execute($count_params);
            $total_reports = (int)$total_st->fetchColumn();
            $total_pages = ceil($total_reports / $per_page);

            // Obtener reportes paginados usando bindValue
            $st = $DB->prepare($sql);

            // Bind de parámetros normales
            $param_index = 1;
            foreach ($params as $param) {
                $st->bindValue($param_index, $param);
                $param_index++;
            }

            // Bind de LIMIT y OFFSET con tipos específicos
            $st->bindValue($param_index++, $per_page, \PDO::PARAM_INT);
            $st->bindValue($param_index, $offset, \PDO::PARAM_INT);

            $st->execute();
            $reports = $st->fetchAll(\PDO::FETCH_ASSOC);

            // Pasar variables de paginación a la vista
            $GLOBALS['total_reports'] = $total_reports;
            $GLOBALS['total_pages'] = $total_pages;
            $GLOBALS['current_page'] = $current_page;
            $GLOBALS['per_page'] = $per_page;
            $GLOBALS['search'] = $search;
            $GLOBALS['type_filter'] = $type_filter;
            $GLOBALS['status_filter'] = $status;
        }

        include __DIR__ . '/../views/partials/admin_header.php';
        include __DIR__ . '/../views/admin/reports.php';
        include __DIR__ . '/../views/partials/admin_footer.php';
    }

    public static function reports_update(){
        require_admin();
        $DB = nebusa_pdo();
        $id = (int)($_POST['id'] ?? 0);
        $action = $_POST['action'] ?? '';
        $moderator_notes = trim($_POST['moderator_notes'] ?? '');

        if ($id > 0 && $DB instanceof \PDO && in_array($action, ['resolve', 'dismiss'])) {
            // Obtener información del reporte para el log
            $st = $DB->prepare("SELECT reporter_user_id, reported_user_id, target_type, target_id FROM reports WHERE id = ?");
            $st->execute([$id]);
            $report = $st->fetch();

            if ($report) {
                $status = $action === 'resolve' ? 'resolved' : 'dismissed';

                $st = $DB->prepare("UPDATE reports SET status=?, moderator_notes=?, resolved_at=NOW() WHERE id=?");
                $st->execute([$status, $moderator_notes, $id]);

                // Log de la acción
                $admin = user();
                $log_message = "Admin {$admin['nick']} {$action}d report #{$id} ({$report['target_type']})";
                self::logSecurityEvent('report_handling', $log_message, $id);

                set_flash('Reporte actualizado correctamente.', 'success');
            } else {
                set_flash('Reporte no encontrado.', 'error');
            }
        } else {
            set_flash('Acción inválida.', 'error');
        }

        header('Location: ' . (function_exists('base_url') ? base_url('/admin/reports') : '/admin/reports'));
        exit;
    }

    public static function system_logs(){
        require_admin();
        $DB = nebusa_pdo();
        $logs = [];
        $type_filter = $_GET['type'] ?? '';
        $limit = min((int)($_GET['limit'] ?? 200), 500);

        if ($DB instanceof \PDO) {
            $sql = "SELECT * FROM security_events WHERE 1=1";
            $params = [];

            if ($type_filter !== '') {
                $sql .= " AND event_type = ?";
                $params[] = $type_filter;
            }

            $sql .= " ORDER BY created_at DESC LIMIT ?";
            $params[] = $limit;

            $st = $DB->prepare($sql);

            // Usar bindValue para el límite
            $st->bindValue(1, $type_filter);
            $st->bindValue(2, $limit, \PDO::PARAM_INT);

            $st->execute();
            $logs = $st->fetchAll(\PDO::FETCH_ASSOC);
        }

        include __DIR__ . '/../views/partials/admin_header.php';
        include __DIR__ . '/../views/admin/system/logs.php';
        include __DIR__ . '/../views/partials/admin_footer.php';
    }

    public static function user_detail(){
        require_admin();
        $DB = nebusa_pdo();
        $id = (int)($_GET['id'] ?? 0);

        if ($id <= 0) {
            set_flash('ID de usuario inválido.', 'error');
            header('Location: ' . (function_exists('base_url') ? base_url('/admin/users') : '/admin/users'));
            exit;
        }

        // Obtener información detallada del usuario
        $user = [];
        $photos = [];
        $reports = [];
        $ban_history = [];

        if ($DB instanceof \PDO) {
            // Información básica del usuario
            $st = $DB->prepare("
                SELECT u.*, p.bio, p.province_code, p.municipality_code, p.avatar_url,
                       prov.name as province_name, mun.name as municipality_name
            FROM users u
            LEFT JOIN profiles p ON p.user_id = u.id
            LEFT JOIN provinces prov ON prov.code = p.province_code
            LEFT JOIN municipalities mun ON mun.code = p.municipality_code
            WHERE u.id = ?
            ");
            $st->execute([$id]);
            $user = $st->fetch(\PDO::FETCH_ASSOC);

            if (!$user) {
                set_flash('Usuario no encontrado.', 'error');
                header('Location: ' . (function_exists('base_url') ? base_url('/admin/users') : '/admin/users'));
                exit;
            }

            // Fotos del usuario
            $st = $DB->prepare("
                SELECT p.*, po.text as caption, po.created_at as post_created
            FROM photos p
            JOIN posts po ON p.post_id = po.id
            WHERE p.owner_id = ?
            ORDER BY po.created_at DESC
            LIMIT 50
            ");
            $st->execute([$id]);
            $photos = $st->fetchAll(\PDO::FETCH_ASSOC);

            // Reportes relacionados con el usuario
            $st = $DB->prepare("
                SELECT r.*, u.nick as reporter_nick
            FROM reports r
            JOIN users u ON u.id = r.reporter_user_id
            WHERE r.reported_user_id = ?
            ORDER BY r.created_at DESC
            LIMIT 50
            ");
            $st->execute([$id]);
            $reports = $st->fetchAll(\PDO::FETCH_ASSOC);

            // Historial de baneos
            $st = $DB->prepare("
                SELECT bl.*, admin.nick as admin_nick
            FROM ban_logs bl
            LEFT JOIN users admin ON admin.id = bl.admin_id
            WHERE bl.user_id = ?
            ORDER BY bl.created_at DESC
            LIMIT 20
            ");
            $st->execute([$id]);
            $ban_history = $st->fetchAll(\PDO::FETCH_ASSOC);
        }

        include __DIR__ . '/../views/partials/admin_header.php';
        include __DIR__ . '/../views/admin/users/detail.php';
        include __DIR__ . '/../views/partials/admin_footer.php';
    }

    // Métodos auxiliares privados
    private static function getRoleDisplayName($role) {
        switch ($role) {
            case 'user':
                return 'Usuario';
            case 'moderator':
                return 'Moderador';
            case 'admin':
                return 'Administrador';
            default:
                return $role;
        }
    }

    private static function getStatusDisplayName($status) {
        switch ($status) {
            case 'active':
                return 'Activo';
            case 'disabled':
                return 'Deshabilitado';
            case 'banned':
                return 'Baneado';
            default:
                return $status;
        }
    }

    private static function logSecurityEvent($event_type, $description, $target_id = null) {
        $DB = nebusa_pdo();
        if ($DB instanceof \PDO) {
            try {
                $st = $DB->prepare("
                    INSERT INTO security_events (event_type, description, user_id, target_id, ip_address, user_agent)
                    VALUES (?, ?, ?, ?, ?, ?)
                ");

                $user = user();
                $st->execute([
                    $event_type,
                    $description,
                    $user['id'] ?? null,
                    $target_id,
                    $_SERVER['REMOTE_ADDR'] ?? 'unknown',
                    $_SERVER['HTTP_USER_AGENT'] ?? 'unknown'
                ]);
            } catch (\Throwable $e) {
                error_log("Error logging security event: " . $e->getMessage());
            }
        }
    }

    public static function photos_set_status(){
        require_admin();
        $DB = nebusa_pdo();
        $id = (int)($_POST['id'] ?? 0);
        $new_status = $_POST['status'] ?? '';
        $reason = trim($_POST['reason'] ?? '');

        $allowed = ['active','hidden','deleted'];
        if (!in_array($new_status, $allowed, true) || $id <= 0) {
            $_SESSION['flash'] = 'Acción inválida.';
            header('Location: ' . base_url('/admin/photos'));
            exit;
        }
        try {
            $uid = user() ? (int)user()['id'] : null;
            $st = $DB->prepare("UPDATE photos SET status=?, moderated_by=?, moderated_at=NOW(), moderation_reason=? WHERE id=?");
            $st->execute([$new_status, $uid, ($reason !== '' ? $reason : null), $id]);

            if ($new_status !== 'active') {
                try {
                    $st2 = $DB->prepare("UPDATE reports SET status='actioned' WHERE target_type='photo' AND target_id=? AND status='pending'");
                    $st2->execute([$id]);
                } catch (\Throwable $e) { /* ignore */ }
            }

            $_SESSION['flash'] = 'Estado actualizado.';
        } catch (\Throwable $e) {
            $_SESSION['flash'] = 'Error al actualizar: ' . $e->getMessage();
        }
        header('Location: ' . base_url('/admin/photos'));
        exit;
    }

}
