<?php
global $wpdb;

// Recupera i parametri di ricerca
$search = isset($_GET['search']) ? sanitize_text_field($_GET['search']) : '';
$search_type = isset($_GET['search_type']) ? sanitize_text_field($_GET['search_type']) : 'email';

// Paginazione
$items_per_page = 20;
$current_page = isset($_GET['paged']) ? max(1, intval($_GET['paged'])) : 1;
$offset = ($current_page - 1) * $items_per_page;

// Query principale
$table_name = $wpdb->prefix . RINNOVOBOOKINGPREFIX . '_emails';

// Query per le statistiche per sede
$stats_query = "SELECT sede, COUNT(*) as total FROM $table_name GROUP BY sede ORDER BY total DESC";
$stats = $wpdb->get_results($stats_query);

// Costruisci la query con i filtri di ricerca
$where_clauses = array();
$query_params = array();

if (!empty($search)) {
    if ($search_type === 'email') {
        $where_clauses[] = "email LIKE %s";
        $query_params[] = '%' . $wpdb->esc_like($search) . '%';
    } else {
        $where_clauses[] = "sede LIKE %s";
        $query_params[] = '%' . $wpdb->esc_like($search) . '%';
    }
}

$where_sql = !empty($where_clauses) ? "WHERE " . implode(" AND ", $where_clauses) : "";

// Query principale con filtri
$query = "SELECT * FROM $table_name $where_sql ORDER BY created_at DESC LIMIT %d OFFSET %d";
$query_params[] = $items_per_page;
$query_params[] = $offset;

$emails = $wpdb->get_results($wpdb->prepare($query, $query_params));

// Conteggio totale per paginazione
$count_query = "SELECT COUNT(*) FROM $table_name $where_sql";
$total_items = $wpdb->get_var($wpdb->prepare($count_query, array_slice($query_params, 0, -2)));
$total_pages = ceil($total_items / $items_per_page);
?>

<div class="wrap">
    <h1>Email Inviate</h1>

    <!-- Statistiche per sede -->
    <div class="stats-container">
        <h2>Statistiche per Sede</h2>
        <table class="wp-list-table widefat fixed striped">
            <thead>
                <tr>
                    <th>Sede</th>
                    <th>Totale Email</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($stats as $stat): ?>
                    <tr>
                        <td><?php echo esc_html($stat->sede); ?></td>
                        <td><?php echo esc_html($stat->total); ?></td>
                    </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    </div>

    <!-- Form di ricerca -->
    <form method="get" class="search-form">
        <input type="hidden" name="page" value="<?php echo esc_attr($_GET['page']); ?>">
        <p class="search-box">
            <select name="search_type" id="search_type">
                <option value="email" <?php selected($search_type, 'email'); ?>>Email</option>
                <option value="sede" <?php selected($search_type, 'sede'); ?>>Sede</option>
            </select>
            <input type="search" name="search" value="<?php echo esc_attr($search); ?>" placeholder="Cerca...">
            <input type="submit" class="button" value="Cerca">
            <?php if (!empty($search)): ?>
                <a href="<?php echo esc_url(admin_url('admin.php?page=' . $_GET['page'])); ?>" class="button">Reset</a>
            <?php endif; ?>
        </p>
    </form>

    <?php if ($total_items > 0): ?>
        <!-- Tabella -->
        <table class="wp-list-table widefat fixed striped">
            <thead>
                <tr>
                    <th>Data Invio</th>
                    <th>Email</th>
                    <th>Sede</th>
                    <th>Oggetto</th>
                    <th>Testo Email</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($emails as $email): ?>
                    <tr>
                        <td><?php echo esc_html(date('d/m/Y H:i', strtotime($email->created_at))); ?></td>
                        <td><?php echo esc_html($email->email); ?></td>
                        <td><?php echo esc_html($email->sede); ?></td>
                        <td><?php echo esc_html($email->oggetto); ?></td>
                        <td><?php echo esc_html(strip_tags($email->testo)); ?></td>
                    </tr>
                <?php endforeach; ?>
            </tbody>
        </table>

        <!-- Paginazione -->
        <?php if ($total_pages > 1): ?>
            <div class="tablenav bottom">
                <div class="tablenav-pages">
                    <span class="displaying-num">
                        <?php printf(_n('%s elemento', '%s elementi', $total_items), number_format_i18n($total_items)); ?>
                    </span>
                    <span class="pagination-links">
                        <?php
                        echo paginate_links(array(
                            'base' => add_query_arg('paged', '%#%'),
                            'format' => '',
                            'prev_text' => __('&laquo;'),
                            'next_text' => __('&raquo;'),
                            'total' => $total_pages,
                            'current' => $current_page,
                            'type' => 'list'
                        ));
                        ?>
                    </span>
                </div>
            </div>
        <?php endif; ?>
    <?php else: ?>
        <div class="notice notice-info">
            <p>Nessuna email trovata nel database.</p>
        </div>
    <?php endif; ?>
</div>

<style>
.wp-list-table {
    margin-top: 20px;
}
.tablenav {
    margin: 20px 0;
}
.tablenav-pages {
    float: right;
    margin: 0 0 9px;
}
.tablenav-pages .pagination-links {
    margin-left: 10px;
}
.tablenav-pages .displaying-num {
    margin-right: 10px;
}
.search-form {
    margin: 20px 0;
}
.search-box {
    display: flex;
    gap: 10px;
    align-items: center;
}
.search-box select {
    min-width: 100px;
}
.search-box input[type="search"] {
    min-width: 200px;
}
.stats-container {
    margin: 20px 0;
    background: #fff;
    padding: 20px;
    border: 1px solid #ccd0d4;
    box-shadow: 0 1px 1px rgba(0,0,0,.04);
}
.stats-container h2 {
    margin-top: 0;
    padding-bottom: 12px;
    border-bottom: 1px solid #eee;
}
</style>

