<?php
/**
 * Subscriber CRUD operations.
 *
 * Handles all database operations for the wtly_subscribers table.
 *
 * @package Waitlytics
 */

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

/**
 * Class WTLY_Subscriber
 */
class WTLY_Subscriber {

	/**
	 * Table name (without prefix).
	 *
	 * @var string
	 */
	private $table = 'wtly_subscribers';

	/**
	 * Get the full table name with prefix.
	 *
	 * @return string
	 */
	private function get_table() {
		global $wpdb;
		return $wpdb->prefix . $this->table;
	}

	/**
	 * Add a new subscriber.
	 *
	 * @param array $data {
	 *     Subscriber data.
	 *
	 *     @type int    $product_id   Required. WooCommerce product ID.
	 *     @type int    $variation_id Optional. Variation ID. Default 0.
	 *     @type string $email        Required. Subscriber email.
	 *     @type int    $user_id      Optional. WordPress user ID. Default 0.
	 *     @type string $source       Optional. Subscription source. Default 'inline'.
	 *     @type string $ip_address   Optional. Subscriber IP address.
	 * }
	 * @return int|WP_Error Subscriber ID on success, WP_Error on failure.
	 */
	public function add( $data ) {
		global $wpdb;

		if ( empty( $data['email'] ) || ! is_email( $data['email'] ) ) {
			return new WP_Error(
				'invalid_email',
				__( 'Please enter a valid email address.', 'waitlytics' )
			);
		}

		if ( empty( $data['product_id'] ) ) {
			return new WP_Error(
				'missing_product',
				__( 'Product ID is required.', 'waitlytics' )
			);
		}

		$email        = sanitize_email( $data['email'] );
		$product_id   = absint( $data['product_id'] );
		$variation_id = isset( $data['variation_id'] ) ? absint( $data['variation_id'] ) : 0;

		// Check if already subscribed (active).
		$existing = $this->get_by_email( $email, $product_id, $variation_id );
		if ( $existing && 'active' === $existing->status ) {
			return new WP_Error(
				'already_subscribed',
				__( "You're already on the waitlist for this product.", 'waitlytics' )
			);
		}

		$insert_data = array(
			'product_id'   => $product_id,
			'variation_id' => $variation_id,
			'email'        => $email,
			'user_id'      => isset( $data['user_id'] ) ? absint( $data['user_id'] ) : 0,
			'status'       => 'active',
			'source'       => isset( $data['source'] ) ? sanitize_key( $data['source'] ) : 'inline',
			'ip_address'   => isset( $data['ip_address'] ) ? sanitize_text_field( $data['ip_address'] ) : '',
		);

		$format = array( '%d', '%d', '%s', '%d', '%s', '%s', '%s' );

		$result = $wpdb->insert( $this->get_table(), $insert_data, $format ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		if ( false === $result ) {
			return new WP_Error(
				'db_insert_error',
				__( 'Could not save subscription. Please try again.', 'waitlytics' )
			);
		}

		return (int) $wpdb->insert_id;
	}

	/**
	 * Get a subscriber by ID.
	 *
	 * @param int $id Subscriber ID.
	 * @return object|null Subscriber row or null.
	 */
	public function get_by_id( $id ) {
		global $wpdb;

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

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$this->get_table()} WHERE id = %d",
				$id
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
	}

	/**
	 * Get a subscriber by email and product.
	 *
	 * @param string $email        Email address.
	 * @param int    $product_id   Product ID.
	 * @param int    $variation_id Optional. Variation ID. Default 0.
	 * @return object|null Subscriber row or null.
	 */
	public function get_by_email( $email, $product_id, $variation_id = 0 ) {
		global $wpdb;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$this->get_table()}
				WHERE email = %s AND product_id = %d AND variation_id = %d AND status = %s
				ORDER BY subscribed_at DESC LIMIT 1",
				sanitize_email( $email ),
				absint( $product_id ),
				absint( $variation_id ),
				'active'
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
	}

	/**
	 * Get all active subscribers for a product.
	 *
	 * @param int $product_id   Product ID.
	 * @param int $variation_id Optional. Variation ID. Default 0.
	 * @return array Array of subscriber objects.
	 */
	public function get_active_by_product( $product_id, $variation_id = 0 ) {
		global $wpdb;

		$product_id   = absint( $product_id );
		$variation_id = absint( $variation_id );

		if ( $variation_id > 0 ) {
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			return $wpdb->get_results(
				$wpdb->prepare(
					"SELECT * FROM {$this->get_table()}
					WHERE product_id = %d AND variation_id = %d AND status = %s
					ORDER BY subscribed_at ASC",
					$product_id,
					$variation_id,
					'active'
				)
			);
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		}

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM {$this->get_table()}
				WHERE product_id = %d AND status = %s
				ORDER BY subscribed_at ASC",
				$product_id,
				'active'
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
	}

	/**
	 * Update subscriber status.
	 *
	 * @param int    $id     Subscriber ID.
	 * @param string $status New status (active, notified, unsubscribed, bounced).
	 * @return bool|WP_Error True on success, WP_Error on failure.
	 */
	public function update_status( $id, $status ) {
		global $wpdb;

		$id     = absint( $id );
		$status = sanitize_key( $status );

		$allowed_statuses = array( 'active', 'notified', 'unsubscribed', 'bounced' );
		if ( ! in_array( $status, $allowed_statuses, true ) ) {
			return new WP_Error(
				'invalid_status',
				__( 'Invalid subscriber status.', 'waitlytics' )
			);
		}

		$update_data = array( 'status' => $status );
		$format      = array( '%s' );

		if ( 'notified' === $status ) {
			$update_data['notified_at'] = current_time( 'mysql' );
			$format[]                   = '%s';
		}

		if ( 'unsubscribed' === $status ) {
			$update_data['unsubscribed_at'] = current_time( 'mysql' );
			$format[]                       = '%s';
		}

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$result = $wpdb->update(
			$this->get_table(),
			$update_data,
			array( 'id' => $id ),
			$format,
			array( '%d' )
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		if ( false === $result ) {
			return new WP_Error(
				'db_update_error',
				__( 'Could not update subscriber status.', 'waitlytics' )
			);
		}

		return true;
	}

	/**
	 * Delete a subscriber.
	 *
	 * @param int $id Subscriber ID.
	 * @return bool|WP_Error True on success, WP_Error on failure.
	 */
	public function delete( $id ) {
		global $wpdb;

		$id = absint( $id );
		if ( 0 === $id ) {
			return new WP_Error(
				'invalid_id',
				__( 'Invalid subscriber ID.', 'waitlytics' )
			);
		}

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$result = $wpdb->delete(
			$this->get_table(),
			array( 'id' => $id ),
			array( '%d' )
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		if ( false === $result ) {
			return new WP_Error(
				'db_delete_error',
				__( 'Could not delete subscriber.', 'waitlytics' )
			);
		}

		return true;
	}

	/**
	 * Count active subscribers for a product.
	 *
	 * @param int $product_id Product ID.
	 * @return int
	 */
	public function count_by_product( $product_id ) {
		global $wpdb;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$this->get_table()}
				WHERE product_id = %d AND status = %s",
				absint( $product_id ),
				'active'
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
	}

	/**
	 * Count all active subscribers.
	 *
	 * @return int
	 */
	public function count_active() {
		global $wpdb;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$this->get_table()} WHERE status = %s",
				'active'
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
	}

	/**
	 * Get subscribers with pagination and filtering.
	 *
	 * @param array $args {
	 *     Query arguments.
	 *
	 *     @type string $status     Filter by status. Default empty (all).
	 *     @type int    $product_id Filter by product ID. Default 0 (all).
	 *     @type string $search     Search by email. Default empty.
	 *     @type int    $page       Page number. Default 1.
	 *     @type int    $per_page   Results per page. Default 20.
	 *     @type string $orderby    Order by column. Default 'subscribed_at'.
	 *     @type string $order      ASC or DESC. Default 'DESC'.
	 * }
	 * @return array {
	 *     @type array $items      Array of subscriber objects.
	 *     @type int   $total      Total matching subscribers.
	 *     @type int   $pages      Total pages.
	 * }
	 */
	public function get_all( $args = array() ) {
		global $wpdb;

		$defaults = array(
			'status'     => '',
			'product_id' => 0,
			'search'     => '',
			'page'       => 1,
			'per_page'   => 20,
			'orderby'    => 'subscribed_at',
			'order'      => 'DESC',
		);

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

		// Build the WHERE clause as fragments containing %s/%d placeholders
		// with a parallel $values array, so the final query call is always
		// wrapped in $wpdb->prepare(). The leading 1=%d keeps the clause
		// valid — and genuinely prepared — when no filters are active.
		$where  = array( '1=%d' );
		$values = array( 1 );

		if ( ! empty( $args['status'] ) ) {
			$where[]  = 'status = %s';
			$values[] = sanitize_key( $args['status'] );
		}

		if ( $args['product_id'] > 0 ) {
			$where[]  = 'product_id = %d';
			$values[] = absint( $args['product_id'] );
		}

		if ( ! empty( $args['search'] ) ) {
			$where[]  = 'email LIKE %s';
			$values[] = '%' . $wpdb->esc_like( sanitize_text_field( $args['search'] ) ) . '%';
		}

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

		// ORDER BY is never built from user input: both the column and the
		// direction are whitelisted against fixed lists.
		$allowed_orderby = array( 'id', 'email', 'product_id', 'status', 'subscribed_at', 'notified_at' );
		$orderby         = in_array( $args['orderby'], $allowed_orderby, true ) ? $args['orderby'] : 'subscribed_at';
		$order           = in_array( strtoupper( $args['order'] ), array( 'ASC', 'DESC' ), true ) ? strtoupper( $args['order'] ) : 'DESC';

		$page     = max( 1, absint( $args['page'] ) );
		$per_page = max( 1, min( 100, absint( $args['per_page'] ) ) );
		$offset   = ( $page - 1 ) * $per_page;

		// Get total count. The query IS genuinely prepared: the %s/%d
		// placeholders live inside the dynamically built $where_clause
		// (which the static sniff cannot resolve, hence UnfinishedPrepare
		// is a false positive) and $values supplies every replacement to
		// prepare() at the call site.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$total = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$this->get_table()} WHERE {$where_clause}",
				$values
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter

		// Get results — the same prepared WHERE plus whitelisted ORDER BY and
		// %d placeholders for LIMIT/OFFSET, all in one prepare() call.
		$query_values   = $values;
		$query_values[] = $per_page;
		$query_values[] = $offset;

		// The sniff counts only the two literal %d placeholders it can see
		// and miscounts them against $query_values, so
		// ReplacementsWrongNumber is a false positive: $query_values was
		// built alongside $where_clause and matches its placeholders plus
		// LIMIT/OFFSET exactly.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$items = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM {$this->get_table()}
				WHERE {$where_clause}
				ORDER BY {$orderby} {$order}
				LIMIT %d OFFSET %d",
				$query_values
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter

		return array(
			'items' => $items,
			'total' => $total,
			'pages' => (int) ceil( $total / $per_page ),
		);
	}
}
