<?php

namespace GriffinForms\Admin\Sql;

final class App extends \GriffinForms\Admin\Sql\Format
{
    public function submissionCount($form_id, $status = 1)
    {
        global $wpdb;
        $count = $wpdb->get_var(
            $wpdb->prepare(
                'SELECT COUNT(id) as item_count FROM %i WHERE form_id=%1d AND status=%2d',
                $this->config->getTable('submission'),
                $form_id,
                $status
                )
            );

        return $count;
    }
    
    public function submissionCountMulti($form_ids, $status = 1)
    {
        if (!is_array($form_ids)) {
            return;
        }

        $condition = 'form_id=' . implode(' OR form_id=', $form_ids);
        $table = $this->config->getTable('submission');

        global $wpdb;
        $count = $wpdb->get_var($wpdb->prepare('SELECT COUNT(id) as item_count FROM %i WHERE status=%s AND %s', $table, $status, $condition));

        return $count;
    }

    public function lifetimeSubmissionCountMulti($form_ids)
    {
        if (!is_array($form_ids) || empty($form_ids)) {
            return 0;
        }

        $ids = array_map('absint', $form_ids);
        $ids = array_filter($ids);
        if (empty($ids)) {
            return 0;
        }

        $table = esc_sql($this->config->getTable('form'));
        $placeholders = implode(',', array_fill(0, count($ids), '%d'));

        global $wpdb;
        $count = $wpdb->get_var(
            $wpdb->prepare(
                "SELECT SUM(lifetime_submission_count) FROM {$table} WHERE id IN ({$placeholders})",
                ...$ids
            )
        );

        return (int) $count;
    }

    public function createFormPage($form_id = 0)
    {
        global $wpdb;
        $data = array('name' => __('page', 'griffinforms-form-builder'));
        if ($form_id > 0 && $this->hasColumn($this->config->getTable('formpage'), 'form_id')) {
            $data['form_id'] = $form_id;
        }
        $data = array_merge($data, $this->itemCreateData());
        $table = $this->config->getTable('formpage');
        $format = array('%s');
        if (array_key_exists('form_id', $data)) {
            $format[] = '%d';
        }
        $format[] = '%d';
        $format[] = '%s';
        $insert = $wpdb->insert($table, $data, $format);
        
        if ($insert == 'false') {
            return false;
        } else {
            return $wpdb->insert_id;
        }
    }

    public function createPageRow($form_id = 0)
    {
        global $wpdb;
        $data = array('name' => __('row', 'griffinforms-form-builder'));
        if ($form_id > 0 && $this->hasColumn($this->config->getTable('pagerow'), 'form_id')) {
            $data['form_id'] = $form_id;
        }
        $data = array_merge($data, $this->itemCreateData());
        $table = $this->config->getTable('pagerow');
        $format = array('%s');
        if (array_key_exists('form_id', $data)) {
            $format[] = '%d';
        }
        $format[] = '%d';
        $format[] = '%s';
        $insert = $wpdb->insert($table, $data, $format);
        
        if ($insert == 'false') {
            return false;
        } else {
            $row_id = $wpdb->insert_id;
            $this->attachRowColumn($row_id, null, $form_id);
            return $row_id;
        }
    }

    public function createRowColumn($width = 12, $form_id = 0)
    {
        global $wpdb;
        $data = array('name' => __('column', 'griffinforms-form-builder'), 'width' => absint($width));
        if ($form_id > 0 && $this->hasColumn($this->config->getTable('rowcolumn'), 'form_id')) {
            $data['form_id'] = $form_id;
        }
        $data = array_merge($data, $this->itemCreateData());
        $table = $this->config->getTable('rowcolumn');
        $format = array('%s', '%d');
        if (array_key_exists('form_id', $data)) {
            $format[] = '%d';
        }
        $format[] = '%d';
        $format[] = '%s';
        $insert = $wpdb->insert($table, $data, $format);
        
        if ($insert == 'false') {
            return false;
        } else {
            return $wpdb->insert_id;
        }
    }

    /**
     * Prepares and validates the field data for insertion.
     *
     * Checks that a valid field type is provided and assigns default values for
     * 'name' and 'heading' if they are empty. For checkbox fields, if no options
     * are provided, assigns three default options. Also merges in common creation data.
     *
     * @param array $data Associative array of field data.
     * @return array|bool Returns the well-formed data array on success, or false on failure.
     */
    private function prepareFieldData($data)
    {
        // Check if field type is provided.
        if (empty($data['field_type'])) {
            $this->log->add('error', 'field', 0, get_current_user_id(), 'prepareFieldData: Field type is empty.', 'createField');
            return false;
        }

        // Retrieve the friendly name for the field type.
        $friendlyName = $this->config->getFriendlyName($data['field_type']);
        // Check if the friendly name indicates an invalid type.
        if (strpos($friendlyName, 'not found') !== false) {
            /* translators: %s is the internal type name of the field (e.g., inputtext, selectfield) */
            $this->log->add('error', 'field', 0, get_current_user_id(), sprintf(__('prepareFieldData: Invalid field type "%s".', 'griffinforms-form-builder'), $data['field_type']), 'createField');
            return false;
        }

        // If 'name' is empty, assign a default value using sprintf for translation.
        if (empty($data['name'])) {
            /* translators: %s is the friendly name of the field type (e.g., "Checkbox", "Email") */
            $data['name'] = sprintf(__('%s Field', 'griffinforms-form-builder'), $friendlyName);
        }

        // If 'heading' is empty, assign a default value using sprintf for translation.
        if (empty($data['heading'])) {
            /* translators: %s is the friendly name of the field type (e.g., "Checkbox", "Email") */
            $data['heading'] = sprintf(__('%s Field', 'griffinforms-form-builder'), $friendlyName);
        }

        // For checkbox and radio fields, if field_options is empty, assign default options.
        if ( strtolower($data['field_type']) === 'inputcheckbox' || strtolower($data['field_type']) === 'inputradio' ) {
            if ( empty($data['field_options']) ) {
                $default_options = array(
                    array(
                        __('Option 1', 'griffinforms-form-builder') => 'option_1',
                        __('Option 2', 'griffinforms-form-builder') => 'option_2',
                        __('Option 3', 'griffinforms-form-builder') => 'option_3'
                    )
                );
                $data['field_options'] = maybe_serialize($default_options);
            }
        }

        // For dropdown fields, if field_options is not set or empty, assign default options.
        if ( strtolower($data['field_type']) === 'selectfield' ) {
            if ( empty($data['field_options']) ) {
                $default_options = array(
                    "" => array(
                        __('Option 1', 'griffinforms-form-builder') => 'option_1',
                        __('Option 2', 'griffinforms-form-builder') => 'option_2',
                        __('Option 3', 'griffinforms-form-builder') => 'option_3'
                    )
                );
                $data['field_options'] = maybe_serialize($default_options);
            }
        }

        // Merge with common item creation data (like author and timestamp).
        $data = array_merge($data, $this->itemCreateData());

        return $data;
    }

    /**
     * Creates a new field record in the database.
     *
     * Uses prepareFieldData() to validate and format the provided data. If the
     * data is valid, it inserts a new record into the field table and returns the
     * new field ID; otherwise, it returns false.
     *
     * @param array $data Associative array of field data.
     * @return int|bool The new field ID on success, or false on failure.
     */
    public function createField($data)
    {
        global $wpdb;

        // Prepare and validate the field data.
        $prepared_data = $this->prepareFieldData($data);
        if ($prepared_data === false) {
            return false;
        }

        $table = $this->config->getTable('field');
        $insert = $wpdb->insert($table, $prepared_data);

        if ($insert === false) {
            $this->log->add('error', 'field', 0, get_current_user_id(), 'createField: Insert failed.', 'createField');
            return false;
        }

        return $wpdb->insert_id;
    }

    public function updateRowColumnFields($column_id, $field_ids)
    {
        $table = $this->config->getTable('rowcolumn');

        global $wpdb;

        $data = array('fields' => maybe_serialize($field_ids));
        $where = array('id' => $column_id);
        
        $update = $wpdb->update(
            $table, $data, $where, array('%s'), array('%d')
        );

        return $update;
    }

/*     public function attachRowColumn($row_id)
    {
        $column_id = $this->createRowColumn();
        
        if ($column_id === false) {
            return;
        }

        $table = $this->config->getTable('pagerow');
        $data = array('rowcolumns' => maybe_serialize(array($column_id)));
        $where = array('id' => $row_id);

        global $wpdb;
        $update = $wpdb->update($table, $data, $where);
    } */

    public function attachRowColumn($row_id, $column_id = null, $form_id = 0)
    {
        $table = $this->config->getTable('pagerow');

        if ($column_id === null) {
            $column_id = $this->createRowColumn(12, $form_id);
        }

        if ($column_id === false) {
            return;
        }

        global $wpdb;

        $columns = $wpdb->get_var(
            $wpdb->prepare(
                'SELECT rowcolumns FROM %i WHERE ID = %d', $table, $row_id
            )
        );

        $columns = $this->maybeDecode($columns);

        if (!is_array($columns) or empty($columns)) {
            $updated_columns = array(absint($column_id));
        } else {
            array_push($columns, absint($column_id));
            $updated_columns = $columns; 
        }

        $update = $wpdb->update(
            $table,
            array('rowcolumns' => maybe_serialize($updated_columns)),
            array('ID' => absint($row_id)),
            array('%s'),
            array('%d')
        );

        return $update;
    }

    protected function hasColumn(string $table, string $column): bool
    {
        $columns = $this->getTableColumns($table);
        return isset($columns[$column]);
    }

    /**
     * Cache table columns per request so repeated layout queries do not keep
     * hitting INFORMATION_SCHEMA on every page load.
     *
     * @return array<string, true>
     */
    private function getTableColumns(string $table): array
    {
        static $cache = array();
        if (array_key_exists($table, $cache)) {
            return $cache[$table];
        }

        global $wpdb;
        $columns = $wpdb->get_col($wpdb->prepare("SHOW COLUMNS FROM %i", $table), 0);
        if (!is_array($columns)) {
            $columns = array();
        }

        $cache[$table] = array_fill_keys(array_map('strval', $columns), true);
        return $cache[$table];
    }


    public function updateFormPageRows($page_id, $row_ids)
    {
        $table = $this->config->getTable('formpage');

        global $wpdb;

        $data = array('pagerows' => maybe_serialize($row_ids));
        $where = array('id' => absint($page_id));
        
        $update = $wpdb->update(
            $table, $data, $where, array('%s'), array('%d')
        );

        return $update;
    }

    public function updateFormFormPages($form_id, $page_ids)
    {
        $table = $this->config->getTable('form');

        global $wpdb;

        $data = array('formpages' => maybe_serialize($page_ids));
        $where = array('id' => absint($form_id));
        
        $update = $wpdb->update(
            $table, $data, $where, array('%s'), array('%d')
        );

        return $update;
    }

    public function updateRowColumnWidth($column_id, $width)
    {
        $table = $this->config->getTable('rowcolumn');
        $data = array('width' => $width);
        $where = array('ID' => $column_id);
        
        global $wpdb;
        return $wpdb->update(
            $table, $data, $where, array('%d'), array('%d')
        );
    }

    protected function updateChildren($parent_type, $parent_id, $children_type, $child_id)
    {
        global $wpdb;
        $table = $this->config->getTable($parent_type);
    
        $log_item_type = $parent_type;
        $log_item_id = $parent_id;
        if ($parent_id === 0) {
            $log_item_type = $children_type;
            $log_item_id = $child_id;
        }

        if (!$table) {
            /* translators: %s is the parent type name (e.g., formpage, pagerow) */
            $this->log->add('error', $log_item_type, $log_item_id, get_current_user_id(), sprintf(__('Invalid table for parent type "%s".', 'griffinforms-form-builder'), $parent_type), 'formbuilder');
            return false;
        }
    
        // Safely query the child field column
        $children_ids_raw = $wpdb->get_var(
            $wpdb->prepare(
                "SELECT %i FROM %i WHERE id = %d",
                $children_type,
                $table,
                $parent_id
            )
        );

        $decoded_children = $this->maybeDecode($children_ids_raw);
        $was_non_array = !is_array($decoded_children);
        $children_ids = $this->normalizeChildIds($decoded_children);
        $child_key = array_search(absint($child_id), $children_ids, true);
        $child_removed = ($child_key !== false);

        if ($child_removed) {
            unset($children_ids[$child_key]);
            $children_ids = array_values($children_ids);
        }

        if ($was_non_array && !empty($this->log)) {
            /* translators: 1: children type name e.g. 'Rows', 'Pages' etc., 2: table name, 3: parent ID */
            $this->log->add('warning', $log_item_type, $log_item_id, get_current_user_id(), sprintf(__('Recovered non-array "%1$s" payload in table "%2$s" for ID %3$d; normalized relation data.', 'griffinforms-form-builder'), $children_type, $table, $parent_id), 'formbuilder');
        }

        if (!$child_removed && !$was_non_array) {
            if (!empty($this->log)) {
                /* translators: 1: child ID, 2: children type name e.g. 'Rows', 'Pages' etc., 3: parent ID */
                $this->log->add('warning', $log_item_type, $log_item_id, get_current_user_id(), sprintf(__('Child ID %1$s was not present in %2$s for parent ID %3$s (already removed or stale request).', 'griffinforms-form-builder'), $child_id, $children_type, $parent_id), 'formbuilder');
            }
            return true;
        }
    
        // Update the serialized data in the database
        $result = $wpdb->update(
            $table,
            [$children_type => maybe_serialize($children_ids)],
            ['id' => $parent_id],
            ['%s'],
            ['%d']
        );

        if ($result === false) {
            /* translators: %s is the database error message */
            $this->log->add('error', $log_item_type, $log_item_id, get_current_user_id(), sprintf(__('DB error during update: %s', 'griffinforms-form-builder'), $wpdb->last_error), 'formbuilder');
            return false;
        }

        return true;
    }

    /**
     * Normalize relation list payloads to a clean integer ID array.
     *
     * @param mixed $value
     * @return array<int>
     */
    protected function normalizeChildIds($value): array
    {
        if (!is_array($value)) {
            if (is_numeric($value)) {
                $value = array($value);
            } elseif (is_string($value)) {
                $trimmed = trim($value);
                if ($trimmed === '') {
                    $value = array();
                } else {
                    $parts = preg_split('/\s*,\s*/', $trimmed);
                    $value = is_array($parts) ? $parts : array();
                }
            } else {
                $value = array();
            }
        }

        $normalized = array();
        foreach ($value as $id) {
            $id = absint($id);
            if ($id > 0) {
                $normalized[$id] = $id;
            }
        }

        return array_values($normalized);
    }

    public function updatePageRowColumns($row_id, $column_ids)
    {
        $table = $this->config->getTable('pagerow');
        
        global $wpdb;
        $update = $wpdb->update(
            $table,
            array('rowcolumns' => maybe_serialize($column_ids)),
            array('ID' => $row_id),
            array('%s'),
            array('%d')
        );

        return $update;
    }

    public function getReusableFields($limit)
    {
        $table = $this->config->getTable('field');
        
        global $wpdb;
        $fields = $wpdb->get_results(
            $wpdb->prepare('SELECT * FROM %i WHERE is_reusable = 1', $table)
        );
        
        if ($wpdb->last_error) {
            return false;
        }

        return $fields;
    }

    public function fieldsInForm($form_id)
    {
        $table = $this->config->getTable('form');
        
        global $wpdb;
        $page_ids = $wpdb->get_var(
            $wpdb->prepare(
                'SELECT formpages FROM %i WHERE id = %d', $table, $form_id
                )
        );

        $page_ids = $this->maybeDecode($page_ids);

        $field_ids = array();

        if (is_array($page_ids) and !empty($page_ids)) {
            foreach ($page_ids as $page_id) {
                $page_field_ids = $this->fieldsInFormPage($page_id);
                $field_ids = array_merge($field_ids, $page_field_ids);
            }
        }

        return $field_ids;
    }

    public function fieldsInFormPage($page_id)
    {
        $table = $this->config->getTable('formpage');
        
        global $wpdb;
        $row_ids = $wpdb->get_var(
            $wpdb->prepare(
                'SELECT pagerows FROM %i WHERE id = %d', $table, $page_id
                )
        );

        $row_ids = $this->maybeDecode($row_ids);

        $field_ids = array();

        if (empty($row_ids)) {
            return $field_ids;
        }

        foreach ($row_ids as $row_id) {
            $row_field_ids = $this->fieldsInPageRow($row_id);
            $field_ids = array_merge($field_ids, $row_field_ids);
        }

        return $field_ids;
    }

    public function fieldsInPageRow($row_id)
    {
        $table = $this->config->getTable('pagerow');
        
        global $wpdb;
        $column_ids = $wpdb->get_var(
            $wpdb->prepare(
                'SELECT rowcolumns FROM %i WHERE id = %d', $table, $row_id
                )
        );

        $column_ids = $this->maybeDecode($column_ids);

        $field_ids = array();

        if (empty($column_ids)) {
            return $field_ids;
        }

        foreach ($column_ids as $column_id) {
            $column_field_ids = $this->fieldsInRowColumn($column_id);
            $field_ids = array_merge($field_ids, $column_field_ids);
        }

        return $field_ids;
    }

    public function fieldsInRowColumn($column_id)
    {
        $table = $this->config->getTable('rowcolumn');
        
        global $wpdb;
        $field_ids = $wpdb->get_var(
            $wpdb->prepare(
                'SELECT fields FROM %i WHERE id = %d', $table, $column_id
                )
        );

        $field_ids = $this->maybeDecode($field_ids);

        if (empty($field_ids)) {
            return array();
        } else {
            return $field_ids;
        }
    }

    /**
     * Updates the button label of a form page.
     *
     * @param int    $page_id    The ID of the form page.
     * @param string $label_type The label type: 'next', 'previous', or 'submit'.
     * @param string $label_value The new label value.
     *
     * @return int|false Number of affected rows on success, false on failure.
     */
    public function updatePageButtonLabel($page_id, $label_type, $label_value)
    {
        global $wpdb;

        $table = $this->config->getTable('formpage');
        
        $column_map = array(
            'next'     => 'next_label',
            'previous' => 'previous_label',
            'submit'   => 'submit_label',
        );

        if (!isset($column_map[$label_type])) {
            return false;
        }

        $column = $column_map[$label_type];
        $data = array($column => $label_value);
        $where = array('id' => absint($page_id));

        return $wpdb->update($table, $data, $where, array('%s'), array('%d'));
    }

    /**
     * Updates the submit button label of a form.
     *
     * @param int    $form_id     The ID of the form.
     * @param string $label_value The new label value.
     *
     * @return int|false Number of affected rows on success, false on failure.
     */
    public function updateFormButtonLabel($form_id, $label_value)
    {
        global $wpdb;

        $table = $this->config->getTable('form');
        $data = array('submit_label' => $label_value);
        $where = array('id' => absint($form_id));

        return $wpdb->update($table, $data, $where, array('%s'), array('%d'));
    }

    /**
     * Returns the full nested structure of a form with all its pages, rows, columns, and fields.
     *
     * @param int $form_id The ID of the form to retrieve.
     * @return array|false The nested form structure as an array, or false if not found.
     */
    public function getFormStructure($form_id)
    {
        $form_table = $this->config->getTable('form');
        global $wpdb;

        $form = $wpdb->get_row($wpdb->prepare("SELECT * FROM %i WHERE id = %d", $form_table, $form_id), ARRAY_A);
        if (!$form) {
            return false;
        }

        $form['pages'] = [];

        $page_ids = $this->maybeDecode($form['formpages']);
        if (!is_array($page_ids)) {
            return $form;
        }

        foreach ($page_ids as $page_id) {
            $page = $this->getFormPageStructure($page_id);
            if ($page) {
                $form['pages'][] = $page;
            }
        }

        return $form;
    }

    /**
     * Returns the structure of a form page, including its rows and their columns/fields.
     *
     * @param int $page_id The ID of the page to retrieve.
     * @return array|false
     */
    public function getFormPageStructure($page_id)
    {
        $formpage_table = $this->config->getTable('formpage');
        global $wpdb;

        $page = $wpdb->get_row($wpdb->prepare("SELECT * FROM %i WHERE id = %d", $formpage_table, $page_id), ARRAY_A);
        if (!$page) {
            return false;
        }

        $page['rows'] = [];

        $row_ids = $this->maybeDecode($page['pagerows']);
        if (!is_array($row_ids)) {
            return $page;
        }

        foreach ($row_ids as $row_id) {
            $row = $this->getPageRowStructure($row_id);
            if ($row) {
                $page['rows'][] = $row;
            }
        }

        return $page;
    }

    /**
     * Returns the structure of a page row, including its columns and their fields.
     *
     * @param int $row_id The ID of the row to retrieve.
     * @return array|false
     */
    public function getPageRowStructure($row_id)
    {
        $pagerow_table = $this->config->getTable('pagerow');
        global $wpdb;

        $row = $wpdb->get_row($wpdb->prepare("SELECT * FROM %i WHERE id = %d", $pagerow_table, $row_id), ARRAY_A);
        if (!$row) {
            return false;
        }

        $row['columns'] = [];

        $column_ids = $this->maybeDecode($row['rowcolumns']);
        if (!is_array($column_ids)) {
            return $row;
        }

        foreach ($column_ids as $column_id) {
            $column = $this->getColumnStructure($column_id);
            if ($column) {
                $row['columns'][] = $column;
            }
        }

        return $row;
    }

    /**
     * Returns the structure of a column, including its fields.
     *
     * @param int $column_id The ID of the column to retrieve.
     * @return array|false
     */
    public function getColumnStructure($column_id)
    {
        $rowcolumn_table = $this->config->getTable('rowcolumn');
        $field_table = $this->config->getTable('field');
        global $wpdb;

        $column = $wpdb->get_row($wpdb->prepare("SELECT * FROM %i WHERE id = %d", $rowcolumn_table, $column_id), ARRAY_A);
        if (!$column) {
            return false;
        }

        $field_ids = $this->maybeDecode($column['fields']);
        $column['fields'] = [];

        if (!is_array($field_ids)) {
            return $column;
        }

        foreach ($field_ids as $field_id) {
            $field = $wpdb->get_row($wpdb->prepare("SELECT * FROM %i WHERE id = %d", $field_table, $field_id), ARRAY_A);
            if ($field) {
                $column['fields'][] = $field;
            }
        }

        return $column;
    }

    public function saveRule($form_id, $context_type, $rule_index, $entry_index, array $payload = array(), $condition_group = 'all')
    {
        $form_id = absint($form_id);
        if ($form_id <= 0) {
            return array(
                'success' => false,
                'msg'     => __('Invalid form context supplied.', 'griffinforms-form-builder'),
            );
        }

        global $wpdb;
        $table = $this->config->getTable('form');
        $column = 'conditional_logic';

        $raw_logic = $wpdb->get_var(
            $wpdb->prepare('SELECT %i FROM %i WHERE id = %d', $column, $table, $form_id)
        );

        $logic = maybe_unserialize($raw_logic);
        if (!is_array($logic)) {
            $logic = array();
        }

        $context_type = strtolower((string) $context_type);
        $rule_index = is_numeric($rule_index) ? (int) $rule_index : null;
        $entry_index = is_numeric($entry_index) ? (int) $entry_index : null;
        $condition_group = strtolower((string) $condition_group);
        if ($context_type === 'condition') {
            if ('any' !== $condition_group) {
                $condition_group = 'all';
            }
        } else {
            $condition_group = '';
        }

        $is_new_rule = $rule_index === null || !isset($logic[$rule_index]) || !is_array($logic[$rule_index]);

        if ($is_new_rule) {
            $logic[] = array(
                'all' => array(),
                'any' => array(),
                'actions' => array(),
            );
            end($logic);
            $rule_index = key($logic);
        }

        $rule = $logic[$rule_index];
        if (!isset($rule['all']) || !is_array($rule['all'])) {
            $rule['all'] = array();
        }
        if (!isset($rule['any']) || !is_array($rule['any'])) {
            $rule['any'] = array();
        }
        if (!isset($rule['actions']) || !is_array($rule['actions'])) {
            $rule['actions'] = array();
        }

        if ($context_type === 'action') {
            $target_key = 'actions';
        } else {
            $target_key = ($condition_group === 'any') ? 'any' : 'all';
        }

        $entry = array(
            'item'      => isset($payload['item']) ? sanitize_text_field($payload['item']) : '',
            'item_id'   => isset($payload['item_id']) ? sanitize_text_field($payload['item_id']) : '',
            'attribute' => isset($payload['attribute']) ? sanitize_text_field($payload['attribute']) : '',
            'evaluator' => isset($payload['evaluator']) ? sanitize_text_field($payload['evaluator']) : '',
            'action'    => isset($payload['action']) ? sanitize_text_field($payload['action']) : '',
            'input'     => isset($payload['input']) ? sanitize_text_field($payload['input']) : '',
        );

        $final_entry_index = null;

        if ($entry_index !== null && isset($rule[$target_key][$entry_index])) {
            $rule[$target_key][$entry_index] = $entry;
            $final_entry_index = $entry_index;
        } else {
            $rule[$target_key][] = $entry;
            if (function_exists('array_key_last')) {
                $final_entry_index = array_key_last($rule[$target_key]);
            } else {
                end($rule[$target_key]);
                $final_entry_index = key($rule[$target_key]);
            }
        }

        $logic[$rule_index] = $rule;

        $updated = $wpdb->update(
            $table,
            array($column => maybe_serialize($logic)),
            array('id' => $form_id),
            array('%s'),
            array('%d')
        );

        if ($updated === false) {
            return array(
                'success' => false,
                'msg'     => sprintf(
                    /* translators: %s: last database error message */
                    __('Unable to save conditional logic. Database error: %s', 'griffinforms-form-builder'),
                    $wpdb->last_error
                ),
            );
        }

        return array(
            'success' => true,
            'msg'     => __('Conditional logic saved successfully.', 'griffinforms-form-builder'),
            'rule_index' => $rule_index,
            'entry_index' => $final_entry_index,
            'context' => $context_type,
            'condition_group' => $context_type === 'condition' ? $target_key : '',
        );
    }

    public function deleteRuleEntry($form_id, $context_type, $rule_index, $entry_index, $condition_group = 'all')
    {
        $form_id = absint($form_id);
        $context_type = strtolower((string) $context_type) === 'action' ? 'action' : 'condition';
        $rule_index = is_numeric($rule_index) ? (int) $rule_index : null;
        $entry_index = is_numeric($entry_index) ? (int) $entry_index : null;
        $condition_group = strtolower((string) $condition_group) === 'any' ? 'any' : 'all';

        if ($form_id <= 0 || $rule_index === null || $rule_index < 0 || $entry_index === null || $entry_index < 0) {
            return array(
                'success' => false,
                'msg'     => __('Invalid entry reference supplied.', 'griffinforms-form-builder'),
            );
        }

        if ('action' === $context_type) {
            $condition_group = '';
        }

        global $wpdb;
        $table = $this->config->getTable('form');
        $column = 'conditional_logic';

        $raw_logic = $wpdb->get_var(
            $wpdb->prepare('SELECT %i FROM %i WHERE id = %d', $column, $table, $form_id)
        );

        $logic = maybe_unserialize($raw_logic);
        if (!is_array($logic) || !isset($logic[$rule_index]) || !is_array($logic[$rule_index])) {
            return array(
                'success' => false,
                'msg'     => __('Requested rule could not be found.', 'griffinforms-form-builder'),
            );
        }

        $rule = $logic[$rule_index];

        if ('action' === $context_type) {
            $target_key = 'actions';
        } else {
            $target_key = ($condition_group === 'any') ? 'any' : 'all';
        }

        if (!isset($rule[$target_key]) || !is_array($rule[$target_key]) || !isset($rule[$target_key][$entry_index])) {
            return array(
                'success' => false,
                'msg'     => __('Requested entry could not be found.', 'griffinforms-form-builder'),
            );
        }

        unset($rule[$target_key][$entry_index]);
        $rule[$target_key] = array_values($rule[$target_key]);
        $logic[$rule_index] = $rule;

        $updated = $wpdb->update(
            $table,
            array($column => maybe_serialize($logic)),
            array('id' => $form_id),
            array('%s'),
            array('%d')
        );

        if ($updated === false) {
            return array(
                'success' => false,
                'msg'     => sprintf(
                    /* translators: %s: last database error message */
                    __('Unable to delete entry. Database error: %s', 'griffinforms-form-builder'),
                    $wpdb->last_error
                ),
            );
        }

        $success_msg = ('action' === $context_type)
            ? __('Action deleted successfully.', 'griffinforms-form-builder')
            : __('Condition deleted successfully.', 'griffinforms-form-builder');

        return array(
            'success' => true,
            'msg'     => $success_msg,
            'rule_index' => $rule_index,
            'context' => $context_type,
            'condition_group' => $context_type === 'condition' ? $target_key : '',
        );
    }

    public function deleteRule($form_id, $rule_index)
    {
        $form_id = absint($form_id);
        $rule_index = is_numeric($rule_index) ? (int) $rule_index : null;

        if ($form_id <= 0 || $rule_index === null || $rule_index < 0) {
            return array(
                'success' => false,
                'msg'     => __('Invalid rule reference supplied.', 'griffinforms-form-builder'),
            );
        }

        global $wpdb;
        $table = $this->config->getTable('form');
        $column = 'conditional_logic';

        $raw_logic = $wpdb->get_var(
            $wpdb->prepare('SELECT %i FROM %i WHERE id = %d', $column, $table, $form_id)
        );

        $logic = maybe_unserialize($raw_logic);
        if (!is_array($logic) || !isset($logic[$rule_index])) {
            return array(
                'success' => false,
                'msg'     => __('Requested rule could not be found.', 'griffinforms-form-builder'),
            );
        }

        unset($logic[$rule_index]);
        $logic = array_values($logic);

        $updated = $wpdb->update(
            $table,
            array($column => maybe_serialize($logic)),
            array('id' => $form_id),
            array('%s'),
            array('%d')
        );

        if ($updated === false) {
            return array(
                'success' => false,
                'msg'     => sprintf(
                    /* translators: %s: last database error message */
                    __('Unable to delete conditional logic. Database error: %s', 'griffinforms-form-builder'),
                    $wpdb->last_error
                ),
            );
        }

        return array(
            'success' => true,
            'msg'     => __('Rule deleted successfully.', 'griffinforms-form-builder'),
            'rule_index' => $rule_index,
        );
    }

    /**
     * Get recent submission values for a specific field in a form.
     * Fetches the 10 most recent completed submissions and extracts up to 5 values for the given field.
     *
     * Submission structure: a:1:{s:7:"page_31";a:1:{i:170;a:1:{i:0;d:1234;}}}
     * Format: array[page_id][field_id][iteration_index] = value
     *
     * @param int $form_id The form ID to query
     * @param int $field_id The field ID to extract values for
     * @param int $limit Maximum number of values to return (default 5)
     * @return array Array of entries with submission data
     */
    public function getRecentFieldSubmissions($form_id, $field_id, $limit = 5)
    {
        global $wpdb;

        $form_id = absint($form_id);
        $field_id = absint($field_id);
        $limit = absint($limit);

        if ($form_id <= 0 || $field_id <= 0) {
            return array();
        }

        $submission_table = $this->config->getTable('submission');

        // Get 10 most recent completed submissions for this form
        $submissions = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT id, submission, author, created
                FROM %i
                WHERE form_id = %d AND status IN (0,1)
                ORDER BY created DESC
                LIMIT 10",
                $submission_table,
                $form_id
            ),
            ARRAY_A
        );

        if (empty($submissions)) {
            return array();
        }

        $entries = array();

        // Extract field values from submissions
        foreach ($submissions as $submission) {
            // Stop if we already have enough entries
            if (count($entries) >= $limit) {
                break;
            }

            $submission_data = $this->maybeDecode($submission['submission']);

            // Skip if submission data is invalid
            if (!is_array($submission_data)) {
                continue;
            }

            // Submission structure: array[page_id][field_id][iteration_index] = value
            // We need to search through all pages to find the field
            $field_value = null;

            foreach ($submission_data as $page_key => $page_data) {
                if (!is_array($page_data)) {
                    continue;
                }

                // Check if this page contains our field
                if (isset($page_data[$field_id])) {
                    $field_iterations = $page_data[$field_id];

                    // Get the first iteration (index 0) if it exists
                    if (is_array($field_iterations) && isset($field_iterations[0])) {
                        $field_value = $field_iterations[0];
                        break;
                    } elseif (is_array($field_iterations) && !empty($field_iterations)) {
                        // Payment and other complex fields may store associative arrays instead of indexed iterations
                        $field_value = $field_iterations;
                        break;
                    } elseif (!is_array($field_iterations)) {
                        // Sometimes the value might be stored directly without iteration array
                        $field_value = $field_iterations;
                        break;
                    }
                }
            }

            // Skip only if field doesn't exist in submission (null)
            // Allow empty values - they indicate user left field blank
            if ($field_value === null) {
                continue;
            }

            $entries[] = array(
                'submission_id' => absint($submission['id']),
                'value' => $field_value,
                'author' => $submission['author'],
                'created' => $submission['created']
            );
        }

        return $entries;
    }

    /**
     * Get recent submissions for a form.
     *
     * @param int $form_id
     * @param int $limit
     * @return array
     */
    public function getRecentFormSubmissions($form_id, $limit = 5)
    {
        global $wpdb;

        $form_id = absint($form_id);
        $limit = absint($limit);

        if ($form_id <= 0) {
            return array();
        }

        $submission_table = $this->config->getTable('submission');

        $submissions = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT id, author, created, status
                FROM %i
                WHERE form_id = %d
                ORDER BY created DESC
                LIMIT %d",
                $submission_table,
                $form_id,
                $limit
            ),
            ARRAY_A
        );

        if (empty($submissions)) {
            return array();
        }

        return $submissions;
    }

    /**
     * Get file metadata by token.
     * Returns file information from the files table.
     *
     * @param string $token File token
     * @return array File metadata or empty array if not found
     */
    public function getFileMetadataByToken($token)
    {
        if (!is_string($token) || trim($token) === '') {
            return array();
        }

        global $wpdb;
        $files_table = $this->config->getTable('files');

        $file = $wpdb->get_row(
            $wpdb->prepare(
                "SELECT path, url, mime, size_bytes, image_w, image_h
                FROM %i
                WHERE token = %s
                LIMIT 1",
                $files_table,
                trim($token)
            ),
            ARRAY_A
        );

        if (!$file) {
            return array();
        }

        // Extract filename from path or URL
        $filename = '';
        if (!empty($file['path'])) {
            $filename = wp_basename($file['path']);
        } elseif (!empty($file['url'])) {
            $parsed = wp_parse_url($file['url']);
            $filename = isset($parsed['path']) ? wp_basename($parsed['path']) : '';
        }

        return array(
            'filename' => $filename,
            'mime' => $file['mime'] ?? '',
            'size_bytes' => absint($file['size_bytes'] ?? 0),
            'path' => $file['path'] ?? '',
            'url' => $file['url'] ?? '',
            'image_w' => !empty($file['image_w']) ? absint($file['image_w']) : null,
            'image_h' => !empty($file['image_h']) ? absint($file['image_h']) : null
        );
    }

    /**
     * Retrieves all FormTheme categories with count of themes in each.
     *
     * @return array Array of objects with 'category' and 'count' properties
     */
    public function getFormThemeCategories()
    {
        global $wpdb;
        $table = $this->config->getTable('formtheme');

        $results = $wpdb->get_results(
            $wpdb->prepare(
                'SELECT category, COUNT(*) as count FROM %i GROUP BY category ORDER BY category ASC',
                $table
            )
        );

        return is_array($results) ? $results : array();
    }

    /**
     * Retrieves FormThemes by category with optional pagination.
     *
     * @param string $category Category name (defaults to 'default' if empty)
     * @param int $offset Pagination offset (default: 0)
     * @param int $limit Number of themes to return (default: 99)
     * @return array Array of FormTheme objects
     */
    public function getFormThemesByCategory($category = '', $offset = 0, $limit = 99)
    {
        global $wpdb;
        $table = $this->config->getTable('formtheme');

        $category = sanitize_text_field($category);

        if ($category === 'all') {
            $results = $wpdb->get_results(
                $wpdb->prepare(
                    'SELECT * FROM %i ORDER BY created DESC LIMIT %d OFFSET %d',
                    $table,
                    (int) $limit,
                    (int) $offset
                )
            );
        } else {
            if (empty($category)) {
                $category = 'default';
            }

            $results = $wpdb->get_results(
                $wpdb->prepare(
                    'SELECT * FROM %i WHERE category = %s ORDER BY created DESC LIMIT %d OFFSET %d',
                    $table,
                    $category,
                    (int) $limit,
                    (int) $offset
                )
            );
        }

        // Unserialize theme styling data for JavaScript consumption
        if (is_array($results)) {
            foreach ($results as $theme) {
                $this->unserializeThemeData($theme);
            }
        }

        return is_array($results) ? $results : array();
    }

    /**
     * Retrieves the total number of FormThemes for a given category.
     *
     * @param string $category Category name or 'all'.
     * @return int
     */
    public function getFormThemeCount($category = '')
    {
        global $wpdb;
        $table = $this->config->getTable('formtheme');

        $category = sanitize_text_field($category);

        if ($category === 'all') {
            $query = $wpdb->prepare('SELECT COUNT(*) FROM %i', $table);
            return (int) $wpdb->get_var($query);
        }

        if (empty($category)) {
            $category = 'default';
        }

        $query = $wpdb->prepare('SELECT COUNT(*) FROM %i WHERE category = %s', $table, $category);

        return (int) $wpdb->get_var($query);
    }

    /**
     * Unserializes theme styling data from PHP serialized format to arrays.
     * Modifies the theme object in place.
     *
     * @param object $theme The theme object to unserialize
     */
    private function unserializeThemeData(&$theme)
    {
        $fields_to_unserialize = array(
            'form_style',
            'page_style',
            'row_style',
            'field_style',
            'button_style',
            'submit_button',
            'previous_button',
            'next_button',
            'button_typography',
            'form_title',
            'form_description',
            'page_heading',
            'page_description',
            'row_heading',
            'row_description',
            'field_label',
            'field_description',
            'field_errors',
            'success_message'
        );

        foreach ($fields_to_unserialize as $field) {
            if (isset($theme->$field) && is_string($theme->$field)) {
                $unserialized = @unserialize($theme->$field);
                if ($unserialized !== false) {
                    $theme->$field = $unserialized;
                }
            }
        }
    }

    /**
     * Retrieves a single FormTheme by ID with all styling data.
     *
     * @param int $theme_id Theme ID
     * @return object|null FormTheme object or null if not found
     */
    public function getFormThemeById($theme_id)
    {
        global $wpdb;
        $table = $this->config->getTable('formtheme');

        $result = $wpdb->get_row(
            $wpdb->prepare(
                'SELECT * FROM %i WHERE id = %d',
                $table,
                (int) $theme_id
            )
        );

        return $result ?: null;
    }
}
