<?php
/**
 * Database Management Class
 *
 * @package Giraforms
 */

// Exit if accessed directly.
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

/**
 * Class GiraForms_Database
 */
class GiraForms_Database {

	/**
	 * Instance of this class.
	 *
	 * @var GiraForms_Database
	 */
	private static $instance = null;

	/**
	 * Table name for submissions.
	 *
	 * @var string
	 */
	private $submissions_table;

	/**
	 * Table name for submission meta.
	 *
	 * @var string
	 */
	private $submission_meta_table;


	/**
	 * Get instance.
	 *
	 * @return GiraForms_Database
	 */
	public static function instance() {
		if ( is_null( self::$instance ) ) {
			self::$instance = new self();
		}
		return self::$instance;
	}

	/**
	 * Constructor.
	 */
	private function __construct() {
		global $wpdb;
		$this->submissions_table     = $wpdb->prefix . 'giraforms_submissions';
		$this->submission_meta_table = $wpdb->prefix . 'giraforms_submission_meta';
	}

	/**
	 * Create plugin tables.
	 */
	public function create_tables() {
		global $wpdb;

		$charset_collate = $wpdb->get_charset_collate();

		// Submissions table
		$submissions_sql = "CREATE TABLE IF NOT EXISTS {$this->submissions_table} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			form_id varchar(255) NOT NULL,
			post_id bigint(20) unsigned DEFAULT NULL,
			user_id bigint(20) unsigned DEFAULT NULL,
			user_ip varchar(100) DEFAULT NULL,
			user_agent text DEFAULT NULL,
			status varchar(20) DEFAULT 'unread',
			created_at datetime NOT NULL,
			PRIMARY KEY (id),
			KEY form_id (form_id),
			KEY post_id (post_id),
			KEY user_id (user_id),
			KEY status (status),
			KEY created_at (created_at)
		) $charset_collate;";

		// Submission meta table
		$meta_sql = "CREATE TABLE IF NOT EXISTS {$this->submission_meta_table} (
			meta_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			submission_id bigint(20) unsigned NOT NULL,
			meta_key varchar(255) NOT NULL,
			meta_value longtext DEFAULT NULL,
			PRIMARY KEY (meta_id),
			KEY submission_id (submission_id),
			KEY meta_key (meta_key)
		) $charset_collate;";

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		dbDelta( $submissions_sql );
		dbDelta( $meta_sql );

		// Update database version
		update_option( 'giraforms_db_version', GIRAFORMS_VERSION );
	}

	/**
	 * Get submissions table name.
	 *
	 * @return string
	 */
	public function get_submissions_table() {
		return $this->submissions_table;
	}

	/**
	 * Get submission meta table name.
	 *
	 * @return string
	 */
	public function get_submission_meta_table() {
		return $this->submission_meta_table;
	}


	/**
	 * Insert a new submission.
	 *
	 * @param array $data Submission data.
	 * @return int|false Submission ID on success, false on failure.
	 */
	public function insert_submission( $data ) {
		global $wpdb;

		// Check if IP and User Agent collection are disabled (privacy settings)
		$settings = get_option( 'giraforms_settings', array() );
		$disable_ip = isset( $settings['disable_ip_collection'] ) && '1' === $settings['disable_ip_collection'];
		$disable_user_agent = isset( $settings['disable_user_agent_collection'] ) && '1' === $settings['disable_user_agent_collection'];

		$default_status = isset( $settings['submission_default_status'] ) && 'read' === $settings['submission_default_status'] ? 'read' : 'unread';

		$defaults = array(
			'form_id'    => '',
			'post_id'    => get_the_ID(),
			'user_id'    => get_current_user_id(),
			'user_ip'    => $disable_ip ? '' : $this->get_user_ip(),
			'user_agent' => $disable_user_agent ? '' : ( isset( $_SERVER['HTTP_USER_AGENT'] ) ? sanitize_text_field( wp_unslash( $_SERVER['HTTP_USER_AGENT'] ) ) : '' ),
			'status'     => $default_status,
			'created_at' => current_time( 'mysql' ),
		);

		$data = wp_parse_args( $data, $defaults );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom table for form submissions, not using WP post system.
		$result = $wpdb->insert(
			$this->submissions_table,
			$data,
			array( '%s', '%d', '%d', '%s', '%s', '%s', '%s' )
		);

		if ( $result ) {
			return $wpdb->insert_id;
		}

		return false;
	}

	/**
	 * Insert submission meta.
	 *
	 * @param int    $submission_id Submission ID.
	 * @param string $meta_key Meta key.
	 * @param mixed  $meta_value Meta value.
	 * @return int|false Meta ID on success, false on failure.
	 */
	public function insert_submission_meta( $submission_id, $meta_key, $meta_value ) {
		global $wpdb;

		if ( is_array( $meta_value ) || is_object( $meta_value ) ) {
			$meta_value = maybe_serialize( $meta_value );
		}

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.SlowDBQuery.slow_db_query_meta_key, WordPress.DB.SlowDBQuery.slow_db_query_meta_value -- Custom table for form submissions metadata.
		$result = $wpdb->insert(
			$this->submission_meta_table,
			array(
				'submission_id' => $submission_id,
				'meta_key'      => $meta_key,
				'meta_value'    => $meta_value,
			),
			array( '%d', '%s', '%s' )
		);
		// phpcs:enable

		if ( $result ) {
			return $wpdb->insert_id;
		}

		return false;
	}

	/**
	 * Update or insert submission meta (upsert).
	 *
	 * @param int    $submission_id Submission ID.
	 * @param string $meta_key Meta key.
	 * @param mixed  $meta_value Meta value.
	 * @return bool True on success, false on failure.
	 */
	public function update_submission_meta( $submission_id, $meta_key, $meta_value ) {
		global $wpdb;

		if ( is_array( $meta_value ) || is_object( $meta_value ) ) {
			$meta_value = maybe_serialize( $meta_value );
		}

		$table = esc_sql( $this->submission_meta_table );

		// Check if the meta key already exists for this submission.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Custom table, table name is safe.
		$existing = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT meta_id FROM {$table} WHERE submission_id = %d AND meta_key = %s LIMIT 1",
				$submission_id,
				$meta_key
			)
		);

		if ( $existing ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.SlowDBQuery.slow_db_query_meta_value -- Custom table for form submissions metadata.
			return $wpdb->update(
				$this->submission_meta_table,
				array( 'meta_value' => $meta_value ),
				array( 'meta_id' => $existing ),
				array( '%s' ),
				array( '%d' )
			) !== false;
		}

		return $this->insert_submission_meta( $submission_id, $meta_key, $meta_value ) !== false;
	}

	/**
	 * Get submission ID by meta key/value pair.
	 *
	 * @param string $meta_key Meta key.
	 * @param string $meta_value Meta value.
	 * @return int|null Submission ID or null if not found.
	 */
	public function get_submission_id_by_meta( $meta_key, $meta_value ) {
		global $wpdb;

		$table = esc_sql( $this->submission_meta_table );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Custom table, table name is safe.
		$result = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT submission_id FROM {$table} WHERE meta_key = %s AND meta_value = %s LIMIT 1",
				$meta_key,
				$meta_value
			)
		);

		return $result ? (int) $result : null;
	}

	/**
	 * Get submission by ID.
	 *
	 * @param int $submission_id Submission ID.
	 * @return object|null
	 */
	public function get_submission( $submission_id ) {
		global $wpdb;

		$table = esc_sql( $this->submissions_table );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_row( 
			$wpdb->prepare(
				"SELECT * FROM {$table} WHERE id = %d",
				$submission_id
			)
		);
	}

	/**
	 * Get submission meta.
	 *
	 * @param int $submission_id Submission ID.
	 * @return array
	 */
	public function get_submission_meta( $submission_id ) {
		global $wpdb;

		$table = esc_sql( $this->submission_meta_table );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Custom table, table name is safe.
		$results = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT meta_key, meta_value FROM {$table} WHERE submission_id = %d",
				$submission_id
			)
		);

		$meta = array();
		foreach ( $results as $row ) {
			$meta[ $row->meta_key ] = maybe_unserialize( $row->meta_value ?? '' );
		}

		return $meta;
	}

	/**
	 * Get submissions with filters.
	 *
	 * @param array $args Query arguments.
	 * @return array
	 */
	public function get_submissions( $args = array() ) {
		global $wpdb;
		
		$defaults = array(
			'form_id'      => '',
			'status'       => '',
			'email_search' => '',
			'limit'        => 20,
			'offset'       => 0,
			'orderby'      => 'created_at',
			'order'        => 'DESC',
		);
		$args = wp_parse_args( $args, $defaults );
		
		// Whitelist allowed orderby columns to prevent SQL injection
		$allowed_orderby = array( 'id', 'form_id', 'post_id', 'user_id', 'status', 'created_at' );
		if ( ! in_array( $args['orderby'], $allowed_orderby, true ) ) {
			$args['orderby'] = 'created_at';
		}
		
		// Whitelist allowed order directions
		$args['order'] = strtoupper( $args['order'] );
		if ( ! in_array( $args['order'], array( 'ASC', 'DESC' ), true ) ) {
			$args['order'] = 'DESC';
		}
		
		// Sanitize limit and offset as integers
		$args['limit'] = absint( $args['limit'] );
		$args['offset'] = absint( $args['offset'] );
		
		// Start building query parts
		$where_conditions = array();
		$query_values = array();
		
		if ( ! empty( $args['form_id'] ) ) {
			$where_conditions[] = 'form_id = %s';
			$query_values[] = $args['form_id'];
		}
		
		if ( ! empty( $args['status'] ) ) {
			$where_conditions[] = 'status = %s';
			$query_values[] = $args['status'];
		}

		if ( ! empty( $args['email_search'] ) ) {
			$where_conditions[] = sprintf(
				'id IN (SELECT submission_id FROM `%s` WHERE meta_value LIKE %%s)',
				$this->submission_meta_table
			);
			$query_values[] = '%' . $wpdb->esc_like( $args['email_search'] ) . '%';
		}

		// Build the WHERE clause
		$where_sql = '';
		if ( ! empty( $where_conditions ) ) {
			$where_sql = ' WHERE ' . implode( ' AND ', $where_conditions );
		}
		
		// Add pagination values
		$query_values[] = $args['limit'];
		$query_values[] = $args['offset'];
		
		// Build query template - identifiers are whitelisted and safe
		$sql = sprintf(
			'SELECT * FROM `%s`%s ORDER BY `%s` %s LIMIT %%d OFFSET %%d',
			$this->submissions_table,
			$where_sql,
			$args['orderby'],
			$args['order']
		);
		
		// Call prepare() directly inside get_results()
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom table with whitelisted identifiers.
		return $wpdb->get_results(
			$wpdb->prepare(
				$sql, // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
				$query_values
			)
		);
	}

	/**
	 * Count submissions.
	 *
	 * @param array $args Query arguments.
	 * @return int
	 */
	public function count_submissions( $args = array() ) {
		global $wpdb;

		$table = esc_sql( $this->submissions_table );

		$where = array( '1=1' );

		if ( ! empty( $args['form_id'] ) ) {
			$where[] = $wpdb->prepare( 'form_id = %s', $args['form_id'] );
		}

		if ( ! empty( $args['status'] ) ) {
			$where[] = $wpdb->prepare( 'status = %s', $args['status'] );
		}

		if ( ! empty( $args['email_search'] ) ) {
			$where[] = $wpdb->prepare(
				"id IN (SELECT submission_id FROM `{$this->submission_meta_table}` WHERE meta_value LIKE %s)",
				'%' . $wpdb->esc_like( $args['email_search'] ) . '%'
			);
		}

		$where_clause = implode( ' AND ', $where );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Custom table, where clause built with prepare().
		return (int) $wpdb->get_var(
			"SELECT COUNT(*) FROM {$table} WHERE {$where_clause}"
		);
	}

	/**
	 * Update submission status.
	 *
	 * @param int    $submission_id Submission ID.
	 * @param string $status New status.
	 * @return bool
	 */
	public function update_submission_status( $submission_id, $status ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table for form submissions.
		return $wpdb->update(
			$this->submissions_table,
			array( 'status' => $status ),
			array( 'id' => $submission_id ),
			array( '%s' ),
			array( '%d' )
		) !== false;
	}

	/**
	 * Delete submission.
	 *
	 * @param int $submission_id Submission ID.
	 * @return bool
	 */
	public function delete_submission( $submission_id ) {
		global $wpdb;

		// Delete meta first
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table for form submissions metadata.
		$wpdb->delete(
			$this->submission_meta_table,
			array( 'submission_id' => $submission_id ),
			array( '%d' )
		);

		// Delete submission
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table for form submissions.
		return $wpdb->delete(
			$this->submissions_table,
			array( 'id' => $submission_id ),
			array( '%d' )
		) !== false;
	}

	/**
	 * Check if user has exceeded rate limit.
	 *
	 * @param string $ip IP address.
	 * @param int    $limit Maximum submissions allowed.
	 * @param int    $timeframe Timeframe in seconds.
	 * @return bool True if rate limit exceeded.
	 */
	public function is_rate_limited( $ip, $limit = 5, $timeframe = 3600 ) {
		global $wpdb;

		$table = esc_sql( $this->submissions_table );

		// Count submissions from this IP in the timeframe
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Custom table for rate limiting, table name is safe.
		$count = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$table}
				WHERE user_ip = %s
				AND created_at > DATE_SUB(NOW(), INTERVAL %d SECOND)",
				$ip,
				$timeframe
			)
		);

		return ( $count >= $limit );
	}

	/**
	 * Check if a submission exists with a specific meta value for a form.
	 * Used for unique entry validation (e.g., prevent duplicate email submissions).
	 *
	 * @param string $form_id Form ID.
	 * @param string $meta_key Meta key (field name, e.g., 'email_w1yw7ps4h').
	 * @param string $meta_value Meta value to check for duplicates.
	 * @return bool True if a submission with this value already exists.
	 */
	public function submission_exists_with_meta_value( $form_id, $meta_key, $meta_value ) {
		global $wpdb;

		$submissions_table = esc_sql( $this->submissions_table );
		$meta_table        = esc_sql( $this->submission_meta_table );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Custom tables, table names are safe.
		$count = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$submissions_table} s
				INNER JOIN {$meta_table} m ON s.id = m.submission_id
				WHERE s.form_id = %s AND m.meta_key = %s AND m.meta_value = %s",
				$form_id,
				$meta_key,
				$meta_value
			)
		);

		return ( (int) $count > 0 );
	}

	/**
	 * Get user IP address.
	 * Prioritizes REMOTE_ADDR as it cannot be spoofed.
	 *
	 * @return string
	 */
	private function get_user_ip() {
		$ip = '';

		// REMOTE_ADDR is the most reliable as it comes from the TCP connection
		if ( isset( $_SERVER['REMOTE_ADDR'] ) ) {
			$ip = sanitize_text_field( wp_unslash( $_SERVER['REMOTE_ADDR'] ) );
		}

		// Only check proxy headers if behind a trusted proxy (e.g., CloudFlare, load balancer)
		// AND if the site admin has explicitly enabled this via filter
		if ( apply_filters( 'giraforms_trust_proxy_headers', false ) ) {
			if ( isset( $_SERVER['HTTP_X_FORWARDED_FOR'] ) ) {
				// X-Forwarded-For can contain multiple IPs: client, proxy1, proxy2
				// Take the first one (original client IP)
				$forwarded = sanitize_text_field( wp_unslash( $_SERVER['HTTP_X_FORWARDED_FOR'] ) );
				$ip_list   = array_map( 'trim', explode( ',', $forwarded ) );
				if ( ! empty( $ip_list[0] ) && filter_var( $ip_list[0], FILTER_VALIDATE_IP ) ) {
					$ip = $ip_list[0];
				}
			} elseif ( isset( $_SERVER['HTTP_CLIENT_IP'] ) ) {
				$client_ip = sanitize_text_field( wp_unslash( $_SERVER['HTTP_CLIENT_IP'] ) );
				if ( filter_var( $client_ip, FILTER_VALIDATE_IP ) ) {
					$ip = $client_ip;
				}
			}
		}

		return $ip;
	}
}