<?php
/**
 * Database operations for AD Pro Forms
 *
 * @package ADProForms
 */

if (!defined('ABSPATH')) {
    exit;
}

class ADPF_Database {
    
    /**
     * Create database tables on plugin activation
     */
    public static function create_tables() {
        global $wpdb;
        
        $charset_collate = $wpdb->get_charset_collate();
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        
        // Create entries table
        $table_name = $wpdb->prefix . 'adpf_entries';
        $sql = "CREATE TABLE IF NOT EXISTS $table_name (
            id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            form_id varchar(100) NOT NULL,
            form_data longtext NOT NULL,
            read_status tinyint(1) DEFAULT 0,
            submitted_at datetime DEFAULT CURRENT_TIMESTAMP,
            ip_address varchar(45) DEFAULT NULL,
            user_agent text DEFAULT NULL,
            PRIMARY KEY (id),
            KEY form_id (form_id),
            KEY read_status (read_status),
            KEY submitted_at (submitted_at)
        ) $charset_collate;";
        dbDelta($sql);
        
        // Create forms table
        $forms_table = $wpdb->prefix . 'adpf_forms';
        $sql = "CREATE TABLE IF NOT EXISTS $forms_table (
            id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            form_id varchar(100) NOT NULL,
            title varchar(255) NOT NULL,
            config longtext NOT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY form_id (form_id)
        ) $charset_collate;";
        dbDelta($sql);
        
        // Create default contact form
        self::create_default_form();
    }
    
    /**
     * Create default contact form on installation
     */
    public static function create_default_form() {
        $default_form_id = 'contact';
        $default_title = __('Contact Form', 'ad-pro-forms');
        
        // Check if form already exists
        $existing_form = self::get_form($default_form_id);
        if ($existing_form) {
            return; // Don't overwrite existing form
        }
        
        // Default form fields: First name, Last name, Email
        $default_config = array(
            'fields' => array(
                array(
                    'type' => 'text',
                    'name' => 'first_name',
                    'label' => __('First Name', 'ad-pro-forms'),
                    'required' => true,
                    'placeholder' => '',
                    'explanation' => '',
                ),
                array(
                    'type' => 'text',
                    'name' => 'last_name',
                    'label' => __('Last Name', 'ad-pro-forms'),
                    'required' => true,
                    'placeholder' => '',
                    'explanation' => '',
                ),
                array(
                    'type' => 'email',
                    'name' => 'email',
                    'label' => __('Email', 'ad-pro-forms'),
                    'required' => true,
                    'placeholder' => '',
                    'explanation' => '',
                ),
            ),
        );
        
        // Create the default form
        self::save_form($default_form_id, $default_title, $default_config);
    }
    
    /**
     * Insert form entry
     *
     * @param string $form_id Form identifier
     * @param array $form_data Form submission data
     * @return int|false Entry ID on success, false on failure
     */
    public static function insert_entry($form_id, $form_data) {
        global $wpdb;
        
        $table_name = $wpdb->prefix . 'adpf_entries';
        
        // Validate and sanitize form_id
        $form_id = sanitize_text_field($form_id);
        if (strlen($form_id) > 100) {
            return false; // Prevent DoS
        }
        
        // Validate form_data structure
        if (!is_array($form_data)) {
            return false;
        }
        
        // Sanitize JSON data
        $json_data = wp_json_encode($form_data);
        if ($json_data === false || strlen($json_data) > 65535) { // Max TEXT field size
            return false;
        }
        
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery
        $result = $wpdb->insert(
            $table_name,
            array(
                'form_id' => $form_id,
                'form_data' => $json_data,
                'read_status' => 0,
                'ip_address' => self::get_client_ip(),
                'user_agent' => isset($_SERVER['HTTP_USER_AGENT']) ? substr(sanitize_text_field(wp_unslash($_SERVER['HTTP_USER_AGENT'])), 0, 255) : '',
            ),
            array('%s', '%s', '%d', '%s', '%s')
        );
        
        if ($result === false) {
            return false;
        }
        
        self::invalidate_cache_group('adpf_entries');
        
        return $wpdb->insert_id;
    }
    
    /**
     * Get entries
     *
     * @param array $args Query arguments
     * @return array|object|null Database query results
     */
    public static function get_entries($args = array()) {
        global $wpdb;
        
        $defaults = array(
            'per_page' => 20,
            'page' => 1,
            'form_id' => '',
            'read_status' => '',
            'orderby' => 'submitted_at',
            'order' => 'DESC',
        );
        
        $args = wp_parse_args($args, $defaults);
        
        // Check cache
        $version = self::get_cache_version('adpf_entries');
        $cache_key = 'entries_' . $version . '_' . md5(serialize($args));
        $results = wp_cache_get($cache_key, 'adpf_entries');
        
        if (false !== $results) {
            return $results;
        }
        
        $table_name = $wpdb->prefix . 'adpf_entries';
        
        $where = array('1=1');
        $where_values = array();
        
        if (!empty($args['form_id'])) {
            $where[] = 'form_id = %s';
            $where_values[] = sanitize_text_field($args['form_id']);
        }
        
        if ($args['read_status'] !== '') {
            $where[] = 'read_status = %d';
            $where_values[] = (int) $args['read_status'];
        }
        
        $where_clause = implode(' AND ', $where);
        
        $offset = ($args['page'] - 1) * $args['per_page'];
        
        // Whitelist orderby
        $allowed_orderby = array('id', 'form_id', 'read_status', 'submitted_at');
        $orderby = in_array($args['orderby'], $allowed_orderby) ? $args['orderby'] : 'submitted_at';
        $order = strtoupper($args['order']) === 'ASC' ? 'ASC' : 'DESC';
        
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $query = "SELECT * FROM $table_name WHERE $where_clause ORDER BY $orderby $order LIMIT %d OFFSET %d";
        $query_values = array_merge($where_values, array($args['per_page'], $offset));
        
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $results = $wpdb->get_results($wpdb->prepare($query, $query_values));
        
        wp_cache_set($cache_key, $results, 'adpf_entries', HOUR_IN_SECONDS);
        
        return $results;
    }
    
    /**
     * Get entry count
     *
     * @param array $args Query arguments
     * @return int Entry count
     */
    public static function get_entry_count($args = array()) {
        global $wpdb;

        $args = wp_parse_args($args, array(
            'form_id'     => '',
            'read_status' => '',
        ));

        // Check cache
        $version = self::get_cache_version('adpf_entries');
        $cache_key = 'count_' . $version . '_' . md5(serialize($args));
        $count = wp_cache_get($cache_key, 'adpf_entries');
        
        if (false !== $count) {
            return (int) $count;
        }
        
        $table_name = $wpdb->prefix . 'adpf_entries';
        
        $where = array('1=1');
        $where_values = array();
        
        if (!empty($args['form_id'])) {
            $where[] = 'form_id = %s';
            $where_values[] = sanitize_text_field($args['form_id']);
        }
        
        if ($args['read_status'] !== '') {
            $where[] = 'read_status = %d';
            $where_values[] = (int) $args['read_status'];
        }
        
        $where_clause = implode(' AND ', $where);
        
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $query = "SELECT COUNT(*) FROM $table_name WHERE $where_clause";
        
        if (!empty($where_values)) {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery -- Query is built safely with whitelisted values
            $query = $wpdb->prepare($query, $where_values);
        }

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $count = (int) $wpdb->get_var($query);
        
        wp_cache_set($cache_key, $count, 'adpf_entries', HOUR_IN_SECONDS);
        
        return $count;
    }
    
    /**
     * Get single entry
     *
     * @param int $entry_id Entry ID
     * @return object|null Entry object or null
     */
    public static function get_entry($entry_id) {
        global $wpdb;
        
        $entry_id = intval($entry_id);
        $cache_key = 'entry_' . $entry_id;
        $entry = wp_cache_get($cache_key, 'adpf_entries');
        
        if (false !== $entry) {
            return $entry;
        }
        
        $table_name = $wpdb->prefix . 'adpf_entries';
        
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = "SELECT * FROM {$table_name} WHERE id = %d";
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $entry = $wpdb->get_row( $wpdb->prepare( $sql, $entry_id ) );
        
        wp_cache_set($cache_key, $entry, 'adpf_entries', HOUR_IN_SECONDS);
        
        return $entry;
    }
    
    /**
     * Update entry read status
     *
     * @param int|array $entry_ids Entry ID(s)
     * @param int $read_status Read status (0 or 1)
     * @return int|false Number of rows updated or false on failure
     */
    public static function update_read_status($entry_ids, $read_status) {
        global $wpdb;
        
        $table_name = $wpdb->prefix . 'adpf_entries';
        
        if (!is_array($entry_ids)) {
            $entry_ids = array($entry_ids);
        }
        
        $entry_ids = array_map('intval', $entry_ids);
        $entry_ids = array_filter($entry_ids);
        
        if (empty($entry_ids)) {
            return false;
        }
        
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = "UPDATE {$table_name} SET read_status = %d WHERE id IN (" . implode( ',', array_fill( 0, count( $entry_ids ), '%d' ) ) . ")";
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $result = $wpdb->query( $wpdb->prepare( $sql, array_merge( array( $read_status ), $entry_ids ) ) );
        
        if ($result !== false) {
            self::invalidate_cache_group('adpf_entries');
            foreach ($entry_ids as $id) {
                wp_cache_delete('entry_' . $id, 'adpf_entries');
            }
        }
        
        return $result;
    }
    
    /**
     * Delete entry
     *
     * @param int|array $entry_ids Entry ID(s)
     * @return int|false Number of rows deleted or false on failure
     */
    public static function delete_entry($entry_ids) {
        global $wpdb;
        
        $table_name = $wpdb->prefix . 'adpf_entries';
        
        if (!is_array($entry_ids)) {
            $entry_ids = array($entry_ids);
        }
        
        $entry_ids = array_map('intval', $entry_ids);
        $entry_ids = array_filter($entry_ids);
        
        if (empty($entry_ids)) {
            return false;
        }
        
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = "DELETE FROM {$table_name} WHERE id IN (" . implode( ',', array_fill( 0, count( $entry_ids ), '%d' ) ) . ")";
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $result = $wpdb->query( $wpdb->prepare( $sql, $entry_ids ) );
        
        if ($result !== false) {
            self::invalidate_cache_group('adpf_entries');
            foreach ($entry_ids as $id) {
                wp_cache_delete('entry_' . $id, 'adpf_entries');
            }
        }
        
        return $result;
    }
    
    /**
     * Get client IP address with improved security
     *
     * @return string IP address
     */
    private static function get_client_ip() {
        $ip_keys = array(
            'HTTP_CF_CONNECTING_IP', // Cloudflare
            'HTTP_CLIENT_IP',
            'HTTP_X_FORWARDED_FOR',
            'HTTP_X_FORWARDED',
            'HTTP_X_CLUSTER_CLIENT_IP',
            'HTTP_FORWARDED_FOR',
            'HTTP_FORWARDED',
            'REMOTE_ADDR',
        );
        
        foreach ($ip_keys as $key) {
            if (array_key_exists($key, $_SERVER) === true) {
                // Get first IP from comma-separated list
                $ip_list = explode(',', sanitize_text_field(wp_unslash($_SERVER[$key])));
                $ip = trim($ip_list[0]);

                // Validate IP address
                if (filter_var($ip, FILTER_VALIDATE_IP, FILTER_FLAG_NO_PRIV_RANGE | FILTER_FLAG_NO_RES_RANGE) !== false) {
                    return sanitize_text_field($ip);
                }
            }
        }

        // Fallback to REMOTE_ADDR
        $ip = isset($_SERVER['REMOTE_ADDR']) ? sanitize_text_field(wp_unslash($_SERVER['REMOTE_ADDR'])) : '';
        return sanitize_text_field($ip);
    }
    
    /**
     * Insert or update form
     *
     * @param string $form_id Form identifier
     * @param string $title Form title
     * @param array $config Form configuration
     * @return int|false Form ID on success, false on failure
     */
    public static function save_form($form_id, $title, $config) {
        global $wpdb;
        
        $table_name = $wpdb->prefix . 'adpf_forms';
        
        // Validate and sanitize inputs
        $form_id = sanitize_text_field($form_id);
        $title = sanitize_text_field($title);
        
        // Validate lengths
        if (strlen($form_id) > 100 || strlen($title) > 255) {
            return false;
        }
        
        // Validate config structure
        if (!is_array($config) || !isset($config['fields']) || !is_array($config['fields'])) {
            return false;
        }
        
        // Encode config to JSON
        $config_json = wp_json_encode($config);
        if ($config_json === false || strlen($config_json) > 65535) { // Max TEXT field size
            return false;
        }
        
        $data = array(
            'form_id' => $form_id,
            'title' => $title,
            'config' => $config_json,
        );
        
        // Check cache for existence
        $cache_key = 'form_id_exists_' . $form_id;
        $existing = wp_cache_get($cache_key, 'adpf_forms');
        
        if (false === $existing) {
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $sql = "SELECT id FROM {$table_name} WHERE form_id = %s";
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter
            $existing = $wpdb->get_var( $wpdb->prepare( $sql, $form_id ) );
            wp_cache_set($cache_key, $existing, 'adpf_forms', HOUR_IN_SECONDS);
        }
        
        if ($existing) {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery
            $result = $wpdb->update(
                $table_name,
                $data,
                array('id' => $existing),
                array('%s', '%s', '%s'),
                array('%d')
            );
            self::invalidate_cache_group('adpf_forms');
            wp_cache_delete('form_' . $form_id, 'adpf_forms');
            wp_cache_delete('form_id_exists_' . $form_id, 'adpf_forms');
            return $existing;
        } else {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery
            $result = $wpdb->insert(
                $table_name,
                $data,
                array('%s', '%s', '%s')
            );
            
            if ($result === false) {
                return false;
            }
            
            self::invalidate_cache_group('adpf_forms');
            wp_cache_delete('form_id_exists_' . $form_id, 'adpf_forms');
            wp_cache_delete('form_' . $form_id, 'adpf_forms');
            return $wpdb->insert_id;
        }
    }
    
    /**
     * Get form by form_id
     *
     * @param string $form_id Form identifier
     * @return object|null Form object or null
     */
    public static function get_form($form_id) {
        global $wpdb;
        
        $cache_key = 'form_' . $form_id;
        $form = wp_cache_get($cache_key, 'adpf_forms');
        
        if (false !== $form) {
            return $form;
        }
        
        $table_name = $wpdb->prefix . 'adpf_forms';
        
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = "SELECT * FROM {$table_name} WHERE form_id = %s";
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $form = $wpdb->get_row( $wpdb->prepare( $sql, $form_id ) );
        
        if ($form) {
            $form->config = json_decode($form->config, true);
        }
        
        wp_cache_set($cache_key, $form, 'adpf_forms', HOUR_IN_SECONDS);
        
        return $form;
    }
    
    /**
     * Get all forms
     *
     * @return array Array of form objects
     */
    public static function get_forms() {
        global $wpdb;
        
        $version = self::get_cache_version('adpf_forms');
        $cache_key = 'all_forms_' . $version;
        $forms = wp_cache_get($cache_key, 'adpf_forms');
        
        if (false !== $forms) {
            return $forms;
        }
        
        $table_name = $wpdb->prefix . 'adpf_forms';
        
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = "SELECT * FROM {$table_name} ORDER BY created_at DESC";
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $forms = $wpdb->get_results( $sql );
        
        foreach ($forms as $form) {
            $form->config = json_decode($form->config, true);
        }
        
        wp_cache_set($cache_key, $forms, 'adpf_forms', HOUR_IN_SECONDS);
        
        return $forms;
    }
    
    /**
     * Delete form
     *
     * @param string $form_id Form identifier
     * @return int|false Number of rows deleted or false on failure
     */
    public static function delete_form($form_id) {
        global $wpdb;
        
        $table_name = $wpdb->prefix . 'adpf_forms';
        
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery
        $result = $wpdb->delete(
            $table_name,
            array('form_id' => $form_id),
            array('%s')
        );
        
        if ($result !== false) {
            self::invalidate_cache_group('adpf_forms');
            wp_cache_delete('form_' . $form_id, 'adpf_forms');
            wp_cache_delete('form_id_exists_' . $form_id, 'adpf_forms');
        }
        
        return $result;
    }
    /**
     * Get cache version for group
     */
    private static function get_cache_version($group) {
        $version = wp_cache_get('version', $group);
        if (false === $version) {
            $version = time();
            wp_cache_set('version', $version, $group);
        }
        return $version;
    }

    /**
     * Invalidate cache group
     */
    private static function invalidate_cache_group($group) {
        wp_cache_set('version', time(), $group);
    }
}