<?php
/**
 * Auto-reply rule repository.
 *
 * @package SubmissionInbox
 */

namespace SUBIN\AutoReplies;

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Repository methods read/write Submission Inbox custom rule tables directly; WordPress has no native API for these records.
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names are plugin-owned $wpdb->prefix values, and dynamic clauses are built from sanitized allowlists/placeholders in this repository.

/**
 * Reads and writes auto-reply routing rules.
 */
class AutoReplyRuleRepository {
	const PROVIDER_SUBMISSION_INBOX_FORM = 'submission_inbox_form';

	/**
	 * Create a rule.
	 *
	 * @param array<string,mixed> $data Rule data.
	 * @return int
	 */
	public function create( array $data ) {
		global $wpdb;

		$data = $this->sanitize_data( $data );
		if ( '' === $data['name'] || '' === $data['provider'] || '' === $data['form_id'] ) {
			return 0;
		}

		$now                = current_time( 'mysql' );
		$data['created_at'] = $now;
		$data['updated_at'] = $now;

		$inserted = $wpdb->insert(
			$this->table_name(),
			$data,
			array( '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%d', '%s', '%s' )
		);

		return $inserted ? absint( $wpdb->insert_id ) : 0;
	}

	/**
	 * Update a rule.
	 *
	 * @param int                 $rule_id Rule id.
	 * @param array<string,mixed> $data Rule data.
	 * @return bool
	 */
	public function update( $rule_id, array $data ) {
		global $wpdb;

		$rule_id = absint( $rule_id );
		if ( $rule_id <= 0 ) {
			return false;
		}

		$data = $this->sanitize_data( $data );
		if ( '' === $data['name'] || '' === $data['provider'] || '' === $data['form_id'] ) {
			return false;
		}

		$data['updated_at'] = current_time( 'mysql' );
		unset( $data['created_at'] );

		$updated = $wpdb->update(
			$this->table_name(),
			$data,
			array( 'id' => $rule_id ),
			array( '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%d', '%s' ),
			array( '%d' )
		);

		return false !== $updated;
	}

	/**
	 * Delete a rule.
	 *
	 * @param int $rule_id Rule id.
	 * @return bool
	 */
	public function delete( $rule_id ) {
		global $wpdb;

		$rule_id = absint( $rule_id );
		if ( $rule_id <= 0 ) {
			return false;
		}

		return false !== $wpdb->delete( $this->table_name(), array( 'id' => $rule_id ), array( '%d' ) );
	}

	/**
	 * Duplicate a rule as disabled.
	 *
	 * @param int $rule_id Rule id.
	 * @return int
	 */
	public function duplicate( $rule_id ) {
		$rule = $this->find( $rule_id );
		if ( ! $rule ) {
			return 0;
		}

		$rule['name'] = sprintf(
			/* translators: %s: Original rule name. */
			__( '%s Copy', 'submission-inbox' ),
			$rule['name']
		);
		$rule['enabled'] = 0;

		return $this->create( $rule );
	}

	/**
	 * Find a rule by id.
	 *
	 * @param int $rule_id Rule id.
	 * @return array<string,mixed>|null
	 */
	public function find( $rule_id ) {
		global $wpdb;

		$rule_id = absint( $rule_id );
		if ( $rule_id <= 0 ) {
			return null;
		}

		$table = $this->table_name();
		$row   = $wpdb->get_row(
			$wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", $rule_id ), // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			ARRAY_A
		);

		return is_array( $row ) ? $row : null;
	}

	/**
	 * Find any rule for a provider/form pair.
	 *
	 * @param string $provider Provider key.
	 * @param string $form_id Form id.
	 * @return array<string,mixed>|null
	 */
	public function find_rule( $provider, $form_id ) {
		return $this->find_for_form( $provider, $form_id, false );
	}

	/**
	 * Find the enabled rule for a provider/form pair.
	 *
	 * @param string $provider Provider key.
	 * @param string $form_id Form id.
	 * @return array<string,mixed>|null
	 */
	public function find_enabled_rule( $provider, $form_id ) {
		return $this->find_for_form( $provider, $form_id, true );
	}

	/**
	 * List rules.
	 *
	 * @param array<string,mixed> $args Query args.
	 * @return array<int,array<string,mixed>>
	 */
	public function list_rules( array $args = array() ) {
		global $wpdb;

		$table  = $this->table_name();
		$where  = $this->build_where( $args );
		$limit  = max( 1, absint( $args['per_page'] ?? 100 ) );
		$offset = max( 0, absint( $args['offset'] ?? 0 ) );
		$sql    = "SELECT * FROM {$table} {$where['sql']} ORDER BY updated_at DESC, id DESC LIMIT %d OFFSET %d";
		$params = array_merge( $where['params'], array( $limit, $offset ) );

		$results = $wpdb->get_results( $wpdb->prepare( $sql, $params ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared

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

	/**
	 * Whether a rule exists for a provider/form pair.
	 *
	 * @param string   $provider Provider key.
	 * @param string   $form_id Form id.
	 * @param int|null $exclude_id Rule id to exclude.
	 * @return bool
	 */
	public function exists_for_form( $provider, $form_id, $exclude_id = null ) {
		global $wpdb;

		$provider = $this->sanitize_provider( $provider );
		$form_id  = sanitize_text_field( (string) $form_id );
		if ( '' === $provider || '' === $form_id ) {
			return false;
		}

		$table = $this->table_name();
		$sql   = "SELECT id FROM {$table} WHERE provider = %s AND form_id = %s";
		$args  = array( $provider, $form_id );
		if ( null !== $exclude_id && absint( $exclude_id ) > 0 ) {
			$sql   .= ' AND id != %d';
			$args[] = absint( $exclude_id );
		}
		$sql .= ' LIMIT 1';

		return absint( $wpdb->get_var( $wpdb->prepare( $sql, $args ) ) ) > 0; // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
	}

	/**
	 * Whether another enabled rule exists for a provider/form pair.
	 *
	 * @param string   $provider Provider key.
	 * @param string   $form_id Form id.
	 * @param int|null $exclude_id Rule id to exclude.
	 * @return bool
	 */
	public function exists_enabled_for_form( $provider, $form_id, $exclude_id = null ) {
		global $wpdb;

		$provider = $this->sanitize_provider( $provider );
		$form_id  = sanitize_text_field( (string) $form_id );
		if ( '' === $provider || '' === $form_id ) {
			return false;
		}

		$table = $this->table_name();
		$sql   = "SELECT id FROM {$table} WHERE provider = %s AND form_id = %s AND enabled = 1";
		$args  = array( $provider, $form_id );
		if ( null !== $exclude_id && absint( $exclude_id ) > 0 ) {
			$sql   .= ' AND id != %d';
			$args[] = absint( $exclude_id );
		}
		$sql .= ' LIMIT 1';

		return absint( $wpdb->get_var( $wpdb->prepare( $sql, $args ) ) ) > 0; // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
	}

	/**
	 * Supported providers.
	 *
	 * @return array<int,string>
	 */
	public function allowed_providers() {
		return array( 'submission_inbox_form', 'contact_form_7', 'wpforms', 'fluent_forms', 'forminator', 'ninja_forms' );
	}

	/**
	 * Sanitize a provider.
	 *
	 * @param string $provider Provider key.
	 * @return string
	 */
	public function sanitize_provider( $provider ) {
		$provider = sanitize_key( (string) $provider );

		return in_array( $provider, $this->allowed_providers(), true ) ? $provider : '';
	}

	/**
	 * Get table name.
	 *
	 * @return string
	 */
	private function table_name() {
		global $wpdb;

		return $wpdb->prefix . 'subin_auto_reply_rules';
	}

	/**
	 * Find a provider/form rule.
	 *
	 * @param string $provider Provider key.
	 * @param string $form_id Form id.
	 * @param bool   $enabled_only Enabled only.
	 * @return array<string,mixed>|null
	 */
	private function find_for_form( $provider, $form_id, $enabled_only ) {
		global $wpdb;

		$provider = $this->sanitize_provider( $provider );
		$form_id  = sanitize_text_field( (string) $form_id );
		if ( '' === $provider || '' === $form_id ) {
			return null;
		}

		$table = $this->table_name();
		$sql   = "SELECT * FROM {$table} WHERE provider = %s AND form_id = %s";
		$args  = array( $provider, $form_id );
		if ( $enabled_only ) {
			$sql .= ' AND enabled = 1';
		}
		$sql .= ' ORDER BY enabled DESC, updated_at DESC, id DESC LIMIT 1';

		$row = $wpdb->get_row( $wpdb->prepare( $sql, $args ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared

		return is_array( $row ) ? $row : null;
	}

	/**
	 * Build list where clause.
	 *
	 * @param array<string,mixed> $args Query args.
	 * @return array{sql:string,params:array<int,mixed>}
	 */
	private function build_where( array $args ) {
		$where  = array();
		$params = array();

		if ( isset( $args['provider'] ) ) {
			$provider = $this->sanitize_provider( $args['provider'] );
			if ( '' !== $provider ) {
				$where[]  = 'provider = %s';
				$params[] = $provider;
			}
		}

		if ( isset( $args['enabled'] ) ) {
			$where[]  = 'enabled = %d';
			$params[] = ! empty( $args['enabled'] ) ? 1 : 0;
		}

		return array(
			'sql'    => empty( $where ) ? '' : 'WHERE ' . implode( ' AND ', $where ),
			'params' => $params,
		);
	}

	/**
	 * Sanitize rule data.
	 *
	 * @param array<string,mixed> $data Raw data.
	 * @return array<string,mixed>
	 */
	private function sanitize_data( array $data ) {
		return array(
			'name'                      => sanitize_text_field( (string) ( $data['name'] ?? '' ) ),
			'provider'                  => $this->sanitize_provider( (string) ( $data['provider'] ?? '' ) ),
			'form_id'                   => sanitize_text_field( (string) ( $data['form_id'] ?? '' ) ),
			'form_name'                 => sanitize_text_field( (string) ( $data['form_name'] ?? '' ) ),
			'visitor_email_field'       => sanitize_key( (string) ( $data['visitor_email_field'] ?? '' ) ),
			'visitor_email_field_label' => sanitize_text_field( (string) ( $data['visitor_email_field_label'] ?? '' ) ),
			'template_id'               => absint( $data['template_id'] ?? 0 ),
			'enabled'                   => ! empty( $data['enabled'] ) ? 1 : 0,
			'created_at'                => sanitize_text_field( (string) ( $data['created_at'] ?? '' ) ),
			'updated_at'                => sanitize_text_field( (string) ( $data['updated_at'] ?? '' ) ),
		);
	}
}
