<?php
/**
 * Plugin Name: Exportar formularios participación
 * Description: Agrega una pantalla en el admin para exportar envíos de formularios de Elementor a CSV.
 * Version: 1.0
 * Author: David Cano
 */

if ( ! defined( 'ABSPATH' ) ) {
    exit; // Seguridad: evita acceso directo.
}

// Agregar opción de menú
function elementor_submissions_menu() {
    add_menu_page(
        'Exportar formularios participación', 
        'Exportar formularios participación',
        'manage_options', 
        'elementor_submissions', 
        'elementor_submissions_page_html'
    );
}
add_action( 'admin_menu', 'elementor_submissions_menu' );

// Generar la pantalla en el admin
function elementor_submissions_page_html() {
    ?>
    <div class="wrap">
        <h1>Formularios participación</h1>
        <p>
            <a href="<?php echo admin_url( 'admin.php?page=elementor_submissions&export_elementor_csv=1' ); ?>" 
               class="button button-primary">Exportar todos los formularios</a>
            <a href="<?php echo admin_url( 'admin.php?page=elementor_submissions&export_elementor_csv2=1' ); ?>" 
               class="button button-primary">Exportar formularios en última versión</a>
        </p>
    </div>
    <?php
}

// Función para exportar datos a CSV
function export_elementor_submissions_to_csv() {
    if ( ! current_user_can( 'manage_options' ) ) {
        return;
    }

    if ( isset( $_GET['export_elementor_csv'] ) ) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'wp_users'; // Asegúrate de que este es el nombre correcto de la tabla.

        $results = $wpdb->get_results( "
SELECT
                ws.id AS id,
                ws.user_id AS user_id,
                ws.created_at AS fecha_creacion,
                concurso.value AS reto,
                nombre.value AS nombre,
                empresa.value AS empresa,
                correo.value AS correo,
                direccion_fiscal.value AS direccion_fiscal,
                telefono.value AS telefono,
                cif.value AS cif,
                CASE 
                    WHEN COALESCE(proteccion.value, '') = '' THEN 'No'
                    ELSE 'Si'
                END AS proteccion,

                CASE 
                    WHEN COALESCE(veracidad.value, '') = '' THEN 'No'
                    ELSE 'Si'
                END AS veracidad,

                CASE 
                    WHEN COALESCE(terminos.value, '') = '' THEN 'No'
                    ELSE 'Si'
                END AS terminos,

                CASE 
                    WHEN COALESCE(anonimato.value, '') = '' THEN 'No'
                    ELSE 'Si'
                END AS anonimato,
                COALESCE(adjuntos.value, '') AS adjuntos,
                COALESCE(adjunto1.value, '') AS adjuntos1,
                COALESCE(adjunto2.value, '') AS adjuntos2
            FROM wp_e_submissions ws
                LEFT JOIN wp_e_submissions_values concurso        ON concurso.submission_id = ws.id AND concurso.key = 'concurso'
                LEFT JOIN wp_e_submissions_values nombre          ON nombre.submission_id = ws.id AND nombre.key = 'nombre'
                LEFT JOIN wp_e_submissions_values empresa         ON empresa.submission_id = ws.id AND empresa.key = 'empresa'
                LEFT JOIN wp_e_submissions_values correo          ON correo.submission_id = ws.id AND correo.key = 'correo'
                LEFT JOIN wp_e_submissions_values direccion_fiscal ON direccion_fiscal.submission_id = ws.id AND direccion_fiscal.key = 'direccion_fiscal'
                LEFT JOIN wp_e_submissions_values telefono        ON telefono.submission_id = ws.id AND telefono.key = 'telefono'
                LEFT JOIN wp_e_submissions_values cif             ON cif.submission_id = ws.id AND cif.key = 'cif'
                LEFT JOIN wp_e_submissions_values proteccion      ON proteccion.submission_id = ws.id AND proteccion.key = 'proteccion'
                LEFT JOIN wp_e_submissions_values veracidad       ON veracidad.submission_id = ws.id AND veracidad.key = 'veracidad'
                LEFT JOIN wp_e_submissions_values terminos        ON terminos.submission_id = ws.id AND terminos.key = 'terminos'
                LEFT JOIN wp_e_submissions_values anonimato       ON anonimato.submission_id = ws.id AND anonimato.key = 'anonimato'
                LEFT JOIN wp_e_submissions_values adjuntos        ON adjuntos.submission_id = ws.id AND adjuntos.key = 'adjuntos'
                LEFT JOIN wp_e_submissions_values adjunto1        ON adjunto1.submission_id = ws.id AND adjunto1.key = 'adjunto1'
                LEFT JOIN wp_e_submissions_values adjunto2        ON adjunto2.submission_id = ws.id AND adjunto2.key = 'adjunto2'
            where post_id = 8292 or post_id = 8473
            ORDER BY ws.created_at ASC
        ", ARRAY_A );

        if ( empty( $results ) ) {
            wp_die( 'No hay envíos disponibles para exportar.' );
        }

        header( 'Content-Type: text/csv' );
        header( 'Content-Disposition: attachment; filename=propuestas_todas.csv' );

        $output = fopen( 'php://output', 'w' );
        fputcsv( $output, array_keys( $results[0] ) ); // Agregar encabezados de columna

        foreach ( $results as $row ) {
            fputcsv( $output, $row );
        }

        fclose( $output );
        exit;
    } else if ( isset( $_GET['export_elementor_csv2'] ) ){
        global $wpdb;
        $table_name = $wpdb->prefix . 'wp_users'; // Asegúrate de que este es el nombre correcto de la tabla.

        $results = $wpdb->get_results( "
            SELECT
                ws.id AS id,
                ws.user_id AS user_id,
                ws.created_at AS fecha_creacion,
                concurso.value AS reto,
                nombre.value AS nombre,
                empresa.value AS empresa,
                correo.value AS correo,
                direccion_fiscal.value AS direccion_fiscal,
                telefono.value AS telefono,
                cif.value AS cif,
                CASE 
                    WHEN COALESCE(proteccion.value, '') = '' THEN 'No'
                    ELSE 'Si'
                END AS proteccion,
                CASE 
                    WHEN COALESCE(veracidad.value, '') = '' THEN 'No'
                    ELSE 'Si'
                END AS veracidad,
                CASE 
                    WHEN COALESCE(terminos.value, '') = '' THEN 'No'
                    ELSE 'Si'
                END AS terminos,
                CASE 
                    WHEN COALESCE(anonimato.value, '') = '' THEN 'No'
                    ELSE 'Si'
                END AS anonimato,
                COALESCE(adjuntos.value, '') AS adjuntos,
                COALESCE(adjunto1.value, '') AS adjuntos1,
                COALESCE(adjunto2.value, '') AS adjuntos2
            FROM wp_e_submissions ws
                LEFT JOIN wp_e_submissions_values concurso        ON concurso.submission_id = ws.id AND concurso.key = 'concurso'
                LEFT JOIN wp_e_submissions_values nombre          ON nombre.submission_id = ws.id AND nombre.key = 'nombre'
                LEFT JOIN wp_e_submissions_values empresa         ON empresa.submission_id = ws.id AND empresa.key = 'empresa'
                LEFT JOIN wp_e_submissions_values correo          ON correo.submission_id = ws.id AND correo.key = 'correo'
                LEFT JOIN wp_e_submissions_values direccion_fiscal ON direccion_fiscal.submission_id = ws.id AND direccion_fiscal.key = 'direccion_fiscal'
                LEFT JOIN wp_e_submissions_values telefono        ON telefono.submission_id = ws.id AND telefono.key = 'telefono'
                LEFT JOIN wp_e_submissions_values cif             ON cif.submission_id = ws.id AND cif.key = 'cif'
                LEFT JOIN wp_e_submissions_values proteccion      ON proteccion.submission_id = ws.id AND proteccion.key = 'proteccion'
                LEFT JOIN wp_e_submissions_values veracidad       ON veracidad.submission_id = ws.id AND veracidad.key = 'veracidad'
                LEFT JOIN wp_e_submissions_values terminos        ON terminos.submission_id = ws.id AND terminos.key = 'terminos'
                LEFT JOIN wp_e_submissions_values anonimato       ON anonimato.submission_id = ws.id AND anonimato.key = 'anonimato'
                LEFT JOIN wp_e_submissions_values adjuntos        ON adjuntos.submission_id = ws.id AND adjuntos.key = 'adjuntos'
                LEFT JOIN wp_e_submissions_values adjunto1        ON adjunto1.submission_id = ws.id AND adjunto1.key = 'adjunto1'
                LEFT JOIN wp_e_submissions_values adjunto2        ON adjunto2.submission_id = ws.id AND adjunto2.key = 'adjunto2'
            WHERE ws.id IN (
                SELECT MAX(ws2.id) 
                FROM wp_e_submissions ws2
                LEFT JOIN wp_e_submissions_values concurso2 ON concurso2.submission_id = ws2.id AND concurso2.key = 'concurso'
                WHERE ws.user_id = ws2.user_id 
                GROUP BY ws2.user_id, concurso2.value
            )
            AND (ws.post_id = 8292 OR ws.post_id = 8473)
            ORDER BY ws.created_at ASC;
        ", ARRAY_A );

        if ( empty( $results ) ) {
            wp_die( 'No hay envíos disponibles para exportar.' );
        }

        header( 'Content-Type: text/csv' );
        header( 'Content-Disposition: attachment; filename=propuestas_finales.csv' );

        $output = fopen( 'php://output', 'w' );
        fputcsv( $output, array_keys( $results[0] ) ); // Agregar encabezados de columna

        foreach ( $results as $row ) {
            fputcsv( $output, $row );
        }

        fclose( $output );
        exit;
    }
}
add_action( 'admin_init', 'export_elementor_submissions_to_csv' );