<?php
if (!defined('ABSPATH'))
    exit;

if (!class_exists('WP_List_Table')) {
    require_once ABSPATH . 'wp-admin/includes/class-wp-list-table.php';
}

class WPSSLC_CF7_Entries_Table extends WP_List_Table
{

    private $form_id;
    private $table_name;

    public function __construct($form_id)
    {

        global $wpdb;

        $this->form_id = absint($form_id);
        $this->table_name = esc_sql($wpdb->prefix . 'wpssc_form_entry');

        parent::__construct([
            'singular' => 'entry',
            'plural' => 'entries',
            'ajax' => false
        ]);
    }

    /*--------------------------------------------------------------
     # Columns
     --------------------------------------------------------------*/
    public function get_columns()
    {

        global $wpdb;

        $columns = [
            'cb' => '<input type="checkbox" />',
            'data_id' => __('Entry ID', 'contactsheets-lite'),
        ];

        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Schema-level column listing for dynamic table; result varies per form and cannot be cached globally.
        $fields = $wpdb->get_col(
            $wpdb->prepare(
            "SELECT DISTINCT name
                 FROM {$this->table_name}
                 WHERE cf7_id = %d",
            $this->form_id
        )
        );
        // phpcs:enable

        if (empty($fields)) {
            return $columns;
        }

        $settings = get_option("wpssc_form_columns_{$this->form_id}", []);

        $visible = $settings['wpssc_visible'] ?? $fields;
        $labels = $settings['wpssc_labels'] ?? [];
        $order = $settings['column_order'] ?? $fields;

        foreach ($order as $field) {

            if (!in_array($field, $visible, true)) {
                continue;
            }

            if (in_array($field, ['submit_time', '_db_submission_speed_ms', '_google_submission_speed_ms', '_is_status'], true)) {
                continue;
            }

            $label = $labels[$field] ?? ucwords(str_replace(['-', '_'], ' ', $field));

            $columns[$field] = $label;
        }
        $columns['submit_time'] = __('Submitted On', 'contactsheets-lite');
        return $columns;
    }

    protected function get_sortable_columns()
    {

        return [
            'data_id' => ['data_id', true]
        ];
    }

    /*--------------------------------------------------------------
     # Bulk Actions
     --------------------------------------------------------------*/

    protected function get_bulk_actions()
    {
        return [
            'delete' => __('Delete', 'contactsheets-lite')
        ];
    }

    protected function extra_tablenav($which)
    {

        if ('top' === $which) {
?>
            <!-- Hidden Fields -->
            <input type="hidden" name="wpssc_export_mode" id="wpssc-export-hidden" value="">
            <input type="hidden" name="wpssc_export_type" id="wpssc-export-type" value="">
            <input type="hidden" name="wpssc_do_export" value="">

            <!-- Other Export Dropdown -->
            <select id="wpssc-export-mode">
                <option value="">Export...</option>
                <option value="filtered">Export Filtered</option>
                <option value="selected">Export Selected</option>
            </select>

            <?php wp_nonce_field('wpssc_export_nonce', 'wpssc_export_nonce_field'); ?>
            <button type="button" class="wpssc-export-btn wpssc-exportall-button" id="wpssc-export-btn">Export</button>



            <!-- Export All Buttons -->
            <button type="button" class="wpssc-exportall-button" 
                onclick="wpsscExportAll('csv')">
                CSV
            </button>

            <button type="button" class="wpssc-exportall-button" 
                onclick="wpsscExportAll('excel')">
                Excel
            </button>

            <script>
            function wpsscExportAll(type) {

                document.getElementById('wpssc-export-hidden').value = 'all';
                document.getElementById('wpssc-export-type').value   = type;

                var form = document.getElementById('entryfilter');

                var exportFlag = document.querySelector('input[name="wpssc_do_export"]');
                exportFlag.value = '1';

                document.querySelector('#wpssc-export-mode').value = '';

                form.submit();
                exportFlag.value = '0';

                document.getElementById('wpssc-export-hidden').value = '';
                document.getElementById('wpssc-export-type').value   = '';
            }

            document.getElementById('wpssc-export-btn').addEventListener('click', function() {

                var mode = document.getElementById('wpssc-export-mode').value;

                if (!mode) {
                    alert('Please select export option.');
                    return;
                }

                document.getElementById('wpssc-export-hidden').value = mode;
                document.getElementById('wpssc-export-type').value   = 'csv'; // default

                var exportFlag = document.querySelector('input[name="wpssc_do_export"]');
                exportFlag.value = '1';

                this.closest('form').submit();
                exportFlag.value = '0';

                document.getElementById('wpssc-export-hidden').value = '';
                document.getElementById('wpssc-export-type').value   = '';
            });
            </script>

            <?php
        }
    }
    protected function pagination($which)
    {

        // Stop only top pagination
        if ('top' === $which) {
            return;
        }

        // phpcs:disable WordPress.Security.NonceVerification.Recommended -- read-only view parameter, no data mutation.
        $view = isset($_REQUEST['view']) && !empty($_REQUEST['view'])
            ? sanitize_text_field(wp_unslash($_REQUEST['view']))
            : 'entries';
        // phpcs:enable

        $base = add_query_arg(
            array(
            'page' => 'wpsyncsheets-for-contact-form-7',
            'form_id' => $this->form_id,
            'view' => $view,
        ),
            admin_url('admin.php')
        );

        $this->_pagination_args['base'] = $base . '%_%';

        parent::pagination($which);
    }

    protected function process_bulk_action()
    {

        if (!isset($_POST['_wpnonce']) ||
        !wp_verify_nonce(sanitize_text_field(wp_unslash($_POST['_wpnonce'])), 'bulk-' . $this->_args['plural'])) {
            return;
        }

        $action = $this->current_action();
        $entries = isset($_POST['entry']) ? array_map('absint', $_POST['entry']) : [];

        if (empty($entries)) {
            return;
        }

        $service = WPSSLC_Service::get_instance();

        if ($service->wpssc_is_sheet_sync_active($this->form_id)) {
            $service->wpssc_delete_entries_from_sheet($this->form_id, $entries);
        }


        global $wpdb;

        if ('delete' === $action) {

            foreach ($entries as $data_id) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Nonce-verified bulk delete; write operation, not cacheable.
                $wpdb->delete(
                    $this->table_name,
                [
                    'cf7_id' => $this->form_id,
                    'data_id' => $data_id
                ],
                ['%d', '%d']
                );
            }
        }


    }

    /*--------------------------------------------------------------
     # Data Handling
     --------------------------------------------------------------*/
    public function prepare_items()
    {

        global $wpdb;

        $this->process_bulk_action();

        $per_page = 10;
        $paged = $this->get_pagenum();
        $offset = ($paged - 1) * $per_page;

        // phpcs:ignore WordPress.Security.NonceVerification.Missing,WordPress.Security.NonceVerification.Recommended -- read-only search/filter params, no data mutation.
        $search = isset($_POST['s']) ? sanitize_text_field(wp_unslash($_POST['s'])) : '';

        // phpcs:ignore WordPress.Security.NonceVerification.Recommended -- read-only sort params.
        $orderby = isset($_GET['orderby']) ? sanitize_text_field(wp_unslash($_GET['orderby'])) : 'data_id';
        // phpcs:ignore WordPress.Security.NonceVerification.Recommended -- read-only sort params.
        $order = isset($_GET['order']) ? strtoupper(sanitize_text_field(wp_unslash($_GET['order']))) : 'DESC';
        $order = 'ASC' === $order ? 'ASC' : 'DESC';

        // phpcs:ignore WordPress.Security.NonceVerification.Recommended -- read-only date filter params.
        $date_filter = isset($_GET['date_filter']) ? sanitize_text_field(wp_unslash($_GET['date_filter'])) : '';
        // phpcs:ignore WordPress.Security.NonceVerification.Recommended -- read-only date filter params.
        $date_from = isset($_GET['date_from']) ? sanitize_text_field(wp_unslash($_GET['date_from'])) : '';
        // phpcs:ignore WordPress.Security.NonceVerification.Recommended -- read-only date filter params.
        $date_to = isset($_GET['date_to']) ? sanitize_text_field(wp_unslash($_GET['date_to'])) : '';

        // ==========================
        // BUILD BASE WHERE
        // ==========================

        $where = $wpdb->prepare("WHERE t1.cf7_id = %d", $this->form_id);

        // ==========================
        // SEARCH FILTER
        // ==========================

        if ($search) {
            // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $where .= $wpdb->prepare(
                " AND t1.data_id IN (
                    SELECT data_id FROM {$this->table_name}
                    WHERE cf7_id=%d AND value LIKE %s
                )",
                $this->form_id,
                '%' . $wpdb->esc_like($search) . '%'
            );
        // phpcs:enable
        }

        // ==========================
        // DATE FILTER
        // ==========================

        if ($date_filter) {

            switch ($date_filter) {

                case 'today':
                    $today = current_time('Y-m-d');
                    // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                    $where .= $wpdb->prepare(
                        " AND t1.data_id IN (
                            SELECT data_id FROM {$this->table_name}
                            WHERE cf7_id=%d
                            AND name='submit_time'
                            AND DATE(value) = %s
                        )",
                        $this->form_id,
                        $today
                    );
                    // phpcs:enable
                    break;

                case '7days':
                    $from = wp_date('Y-m-d', strtotime('-7 days'));
                    // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                    $where .= $wpdb->prepare(
                        " AND t1.data_id IN (
                            SELECT data_id FROM {$this->table_name}
                            WHERE cf7_id=%d
                            AND name='submit_time'
                            AND DATE(value) >= %s
                        )",
                        $this->form_id,
                        $from
                    );
                    // phpcs:enable
                    break;

                case 'month':
                    $month = wp_date('m');
                    $year = wp_date('Y');

                    // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                    $where .= $wpdb->prepare(
                        " AND t1.data_id IN (
                            SELECT data_id FROM {$this->table_name}
                            WHERE cf7_id=%d
                            AND name='submit_time'
                            AND MONTH(value)=%d
                            AND YEAR(value)=%d
                        )",
                        $this->form_id,
                        $month,
                        $year
                    );
                    // phpcs:enable
                    break;

                case 'custom':
                    if ($date_from && $date_to) {
                        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                        $where .= $wpdb->prepare(
                            " AND t1.data_id IN (
                                SELECT data_id FROM {$this->table_name}
                                WHERE cf7_id=%d
                                AND name='submit_time'
                                AND DATE(value) BETWEEN %s AND %s
                            )",
                            $this->form_id,
                            $date_from,
                            $date_to
                        );
                    // phpcs:enable
                    }
                    break;
            }
        }

        // ==========================
        // GET PAGINATED DATA IDS
        // ==========================

        if ('data_id' === $orderby) {

            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Paginated admin table query; per-request with dynamic filter state, cannot be cached. $order is whitelisted.
            $data_ids = $wpdb->get_col($wpdb->prepare(
                "
                SELECT DISTINCT t1.data_id
                FROM {$this->table_name} t1
                $where
                ORDER BY t1.data_id $order
                LIMIT %d OFFSET %d
            ", $per_page, $offset));
        // phpcs:enable

        }
        else {

            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Paginated admin table query; per-request with dynamic sort state, cannot be cached.
            $data_ids = $wpdb->get_col($wpdb->prepare(
                "
                SELECT t1.data_id
                FROM {$this->table_name} t1
                INNER JOIN {$this->table_name} t2
                    ON t1.data_id = t2.data_id
                    AND t2.name = %s
                $where
                GROUP BY t1.data_id
                ORDER BY MAX(t2.value) $order
                LIMIT %d OFFSET %d
            ", $orderby, $per_page, $offset));
        // phpcs:enable
        }

        if (empty($data_ids)) {
            $this->items = [];

            $this->set_pagination_args([
                'total_items' => 0,
                'per_page' => $per_page,
                'total_pages' => 0
            ]);

            return;
        }

        // ==========================
        // FETCH ALL ENTRY ROWS (1 QUERY)
        // ==========================

        $placeholders = implode(',', array_fill(0, count($data_ids), '%d'));

        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- Fetches all rows for paginated IDs; dynamic IN() list makes caching impractical.
        $rows = $wpdb->get_results($wpdb->prepare(
            "
            SELECT data_id, name, value
            FROM {$this->table_name}
            WHERE cf7_id = %d
            AND data_id IN ($placeholders)
        ", ...array_merge([$this->form_id], $data_ids)));
        // phpcs:enable

        // ==========================
        // GROUP ENTRIES
        // ==========================

        $grouped = [];

        foreach ($rows as $row) {

            if (!isset($grouped[$row->data_id])) {
                $grouped[$row->data_id] = ['data_id' => $row->data_id];
            }

            $value = maybe_unserialize($row->value);

            if (is_array($value)) {
                $value = implode(', ', $value);
            }

            $grouped[$row->data_id][$row->name] = $value;
        }

        if ('DESC' === $order)
            $grouped = array_reverse($grouped);

        $this->items = array_values($grouped);

        // ==========================
        // TOTAL COUNT (WITH FILTERS)
        // ==========================

        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Filtered total count; per-request with dynamic WHERE state, caching would return stale counts.
        $total_items = (int)$wpdb->get_var(
            "
            SELECT COUNT(DISTINCT t1.data_id)
            FROM {$this->table_name} t1
            $where
        ");
        // phpcs:enable

        $columns = $this->get_columns();
        $hidden = [];
        $sortable = $this->get_sortable_columns();

        $this->_column_headers = [$columns, $hidden, $sortable];

        $this->set_pagination_args([
            'total_items' => $total_items,
            'per_page' => $per_page,
            'total_pages' => ceil($total_items / $per_page)
        ]);
    }

    public function column_default($item, $column_name)
    {

        if (!isset($item[$column_name])) {
            return '-';
        }

        $value = $item[$column_name];

        // If value is array → convert to comma separated string
        if (is_array($value)) {
            $value = implode(', ', $value);
        }

        return esc_html($value);
    }
    public function column_data_id($item)
    {

        $view_url = add_query_arg([
            'page' => 'wpsyncsheets-for-contact-form-7',
            'form_id' => $this->form_id,
            'entryid' => $item['data_id'],
            'view' => 'entry'
        ], admin_url('admin.php'));

        return sprintf(
            '<a href="%s"><strong>#%d</strong></a>',
            esc_url($view_url),
            $item['data_id']
        );
    }

    public function column_cb($item)
    {
        return sprintf(
            '<input type="checkbox" name="entry[]" value="%s" />',
            esc_attr($item['data_id'])
        );
    }
    public function display()
    {
        $get_table_classes = implode(' ', $this->get_table_classes());
?>
        <div class="tablenav top">
            <div class="alignleft actions bulkactions">
                <?php
        $this->bulk_actions('top'); // Bulk actions 
?>
            </div>
            <?php $this->extra_tablenav('top'); ?>
        </div>

        <table class="wp-list-table <?php echo esc_attr($get_table_classes); ?>">
            <thead>
                <tr>
                    <?php $this->print_column_headers(); ?>
                </tr>
            </thead>

            <tbody id="the-list">
                <?php $this->display_rows_or_placeholder(); ?>
            </tbody>

            <?php /* Removed tfoot (bottom titles) */?>
        </table>

        <div class="tablenav bottom">
            <?php $this->pagination('bottom'); ?>
        </div>
        <?php
    }
}