<?php
/**
 * SureForms Pro Save and Resume Database Table Class.
 *
 * @link       https://sureforms.com
 * @since      2.2.0
 * @package    sureforms-pro
 */

namespace SRFM_Pro\Inc\Pro\Database\Tables;

use SRFM\Inc\Database\Base;
use SRFM\Inc\Helper;
use SRFM\Inc\Traits\Get_Instance;

// Exit if accessed directly.
defined( 'ABSPATH' ) || exit;

/**
 * SureForms Pro Save and Resume Database Table Class.
 *
 * @since 2.2.0
 */
class Save_Resume extends Base {
	use Get_Instance;

	/**
	 * {@inheritDoc}
	 *
	 * @var string
	 */
	protected $table_suffix = 'draft_submissions';

	/**
	 * {@inheritDoc}
	 *
	 * @var int
	 */
	protected $table_version = 1;

	/**
	 * {@inheritDoc}
	 */
	public function get_schema() {
		return [
			// Unique identifier for each draft.
			'id'           => [
				'type' => 'string',
			],
			// Email address if the user chooses to receive the resume link.
			'email'        => [
				'type' => 'string',
			],
			// When the draft was saved.
			'date_created' => [
				'type' => 'datetime',
			],
			// IP address of the user.
			'ip'           => [
				'type' => 'string',
			],
			// URL of the form.
			'source_url'   => [
				'type' => 'string',
			],
			// Identifier of the form.
			'form_id'      => [
				'type' => 'number',
			],
			// Serialized snapshot of form state.
			'form_data'    => [
				'type'    => 'array',
				'default' => [],
			],
		];
	}

	/**
	 * {@inheritDoc}
	 */
	public function get_columns_definition() {
		return [
			'id CHAR(36) NOT NULL PRIMARY KEY',
			'email VARCHAR(255)',
			'date_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP',
			'ip VARCHAR(45)',
			'source_url TEXT',
			'form_id BIGINT(20) UNSIGNED',
			'form_data LONGTEXT',
		];
	}

	/**
	 * Add a new draft submission to the database.
	 *
	 * @param array<mixed> $data An associative array of data for the new draft submission. Must include 'id'.
	 * @since 2.2.0
	 * @return int|false The number of rows inserted, or false if the insertion fails.
	 */
	public static function add( $data ) {
		if ( ! is_array( $data ) || empty( $data['id'] ) ) {
			return false;
		}
		// Basic sanitisation of individual fields.
		$data['id'] = sanitize_text_field( $data['id'] );
		if ( isset( $data['email'] ) ) {
			$data['email'] = sanitize_email( $data['email'] );
		}
		if ( isset( $data['ip'] ) ) {
			$data['ip'] = sanitize_text_field( $data['ip'] );
		}
		if ( isset( $data['source_url'] ) ) {
			$data['source_url'] = esc_url_raw( $data['source_url'] );
		}
		if ( isset( $data['form_id'] ) ) {
			$data['form_id'] = absint( $data['form_id'] );
		}
		// Serialize form_data if it's an array.
		if ( isset( $data['form_data'] ) && is_array( $data['form_data'] ) ) {
			$data['form_data'] = wp_json_encode( $data['form_data'] );
		}
		return self::get_instance()->use_insert( $data );
	}

	/**
	 * Retrieve a specific draft submission from the database.
	 *
	 * @param string $id The id of the draft submission to retrieve.
	 * @since 2.2.0
	 * @return array<mixed> An associative array representing the draft submission, or an empty array if not found.
	 */
	public static function get( $id ) {
		if ( empty( $id ) ) {
			return [];
		}

		return self::get_by( 'id', sanitize_text_field( $id ) );
	}

	/**
	 * Update a draft submission by id.
	 *
	 * @param string              $id Draft id.
	 * @param array<string,mixed> $data Data to update.
	 * @since 2.2.0
	 * @return int|false The number of rows updated, or false on error.
	 */
	public static function update( $id, $data = [] ) {
		if ( empty( $id ) ) {
			return false;
		}

		// Serialize form_data if it's an array.
		if ( isset( $data['form_data'] ) && is_array( $data['form_data'] ) ) {
			$data['form_data'] = wp_json_encode( $data['form_data'] );
		}

		return self::get_instance()->use_update( $data, [ 'id' => sanitize_text_field( $id ) ] );
	}

	/**
	 * Delete a draft submission by id.
	 *
	 * @param string $id Draft id to delete.
	 * @since 2.2.0
	 * @return int|false The number of rows deleted, or false on error.
	 */
	public static function delete( $id ) {
		if ( empty( $id ) ) {
			return false;
		}

		return self::get_instance()->use_delete( [ 'id' => sanitize_text_field( $id ) ], [ '%s' ] );
	}

	/**
	 * Delete draft submissions older than specified days.
	 *
	 * @since 2.2.0
	 * @return int|false The number of rows deleted, or false on error.
	 */
	public static function delete_old_drafts() {
		global $wpdb;

		$instance = self::get_instance();

		// Number of days is filterable via 'srfm_delete_old_drafts_days' filter (default: 30 days).
		$days = apply_filters( 'srfm_delete_old_drafts_days', 30 );

		// If days is not a positive integer, use default of 30 days.
		if ( ! is_int( $days ) || $days <= 0 ) {
			$days = 30;
		}

		// Calculate the date threshold.
		$timestamp = strtotime( "-{$days} days" );
		if ( false === $timestamp ) {
			return false;
		}
		$threshold_date = gmdate( 'Y-m-d H:i:s', $timestamp );

		// Build the DELETE query with WHERE clause using Base class infrastructure.
		$where_clauses = [
			[
				[
					'key'     => 'date_created',
					'compare' => '<',
					'value'   => $threshold_date,
				],
			],
		];

		// Prepare the WHERE clause using the Base class method.
		$where_clause = $instance->prepare_where_clauses( $where_clauses );

		// Execute the DELETE query.
		$query = "DELETE FROM {$instance->get_tablename()}{$where_clause}";
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Query is already prepared by prepare_where_clauses() and caching is not applicable for DELETE operations.
		return $wpdb->query( $query );
	}

	/**
	 * Retrieve draft submission data by a given condition.
	 *
	 * @param string     $key Lookup key (e.g., 'id', 'form_id').
	 * @param int|string $value Lookup value.
	 * @return array<mixed> Draft submission data or empty array if not found.
	 */
	private static function get_by( $key, $value ) {
		if ( empty( $key ) || empty( $value ) ) {
			return [];
		}

		$results = self::get_instance()->get_results(
			[
				$key => $value,
			]
		);

		$draft = isset( $results[0] ) && is_array( $results[0] ) ? Helper::get_array_value( $results[0] ) : [];

		// Decode form_data if it exists and is JSON.
		if ( ! empty( $draft['form_data'] ) && is_string( $draft['form_data'] ) ) {
			$decoded_data = json_decode( $draft['form_data'], true );
			if ( json_last_error() === JSON_ERROR_NONE ) {
				$draft['form_data'] = $decoded_data;
			}
		}

		return $draft;
	}

}
