<?php

namespace LearnPress\Databases;

use LearnPress\Filters\WebhookFilter;
use LP_Helper;

defined( 'ABSPATH' ) || exit;

/**
 * Database access for LearnPress webhooks.
 */
class WebhookDB extends DataBase {
	/**
	 * @var self|null
	 */
	private static $_instance;

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

		return self::$_instance;
	}

	/**
	 * Query webhook rows.
	 *
	 * @param WebhookFilter $filter     Webhook query filter.
	 * @param int           $total_rows Total matching rows.
	 *
	 * @return array|int|string|null
	 * @throws \Exception
	 */
	public function get_webhooks( WebhookFilter $filter, int &$total_rows = 0 ) {
		$filter->fields = array_merge( $filter->all_fields, $filter->fields );

		if ( empty( $filter->collection ) ) {
			$filter->collection = $this->tb_lp_webhooks;
		}

		if ( empty( $filter->collection_alias ) ) {
			$filter->collection_alias = 'w';
		}

		if ( isset( $filter->webhook_id ) ) {
			$filter->where[] = $this->wpdb->prepare( 'AND w.webhook_id = %d', $filter->webhook_id );
		}

		if ( ! empty( $filter->webhook_ids ) ) {
			$ids_format = LP_Helper::db_format_array( $filter->webhook_ids, '%d' );
			// phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- Placeholders are generated for sanitized integer IDs.
			$filter->where[] = $this->wpdb->prepare( "AND w.webhook_id IN ($ids_format)", $filter->webhook_ids );
		}

		if ( isset( $filter->user_id ) ) {
			$filter->where[] = $this->wpdb->prepare( 'AND w.user_id = %d', $filter->user_id );
		}

		if ( isset( $filter->status ) ) {
			$filter->where[] = $this->wpdb->prepare( 'AND w.status = %s', $filter->status );
		}

		if ( ! empty( $filter->statuses ) ) {
			$statuses_format = LP_Helper::db_format_array( $filter->statuses );
			// phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- Placeholders are generated by LP_Helper.
			$filter->where[] = $this->wpdb->prepare( "AND w.status IN ($statuses_format)", $filter->statuses );
		}

		if ( isset( $filter->name ) ) {
			$filter->where[] = $this->wpdb->prepare( 'AND w.name = %s', $filter->name );
		}

		if ( isset( $filter->delivery_url ) ) {
			$filter->where[] = $this->wpdb->prepare( 'AND w.delivery_url = %s', $filter->delivery_url );
		}

		if ( isset( $filter->event ) ) {
			$event           = '%"' . $this->wpdb->esc_like( $filter->event ) . '"%';
			$filter->where[] = $this->wpdb->prepare( 'AND w.events LIKE %s', $event );
		}

		if ( ! empty( $filter->key_word ) ) {
			$search          = '%' . $this->wpdb->esc_like( $filter->key_word ) . '%';
			$filter->where[] = $this->wpdb->prepare( 'AND (w.name LIKE %s OR w.delivery_url LIKE %s)', $search, $search );
		}

		$filter = apply_filters( 'learn-press/webhook/query/filter', $filter );

		return $this->execute( $filter, $total_rows );
	}

	/**
	 * Get a webhook row by ID.
	 *
	 * @param int $webhook_id Webhook ID.
	 *
	 * @return object|null
	 * @throws \Exception
	 */
	public function get_webhook( int $webhook_id ) {
		$filter                  = new WebhookFilter();
		$filter->webhook_id      = absint( $webhook_id );
		$filter->limit           = 1;
		$filter->run_query_count = false;
		$rows                    = $this->get_webhooks( $filter );

		return is_array( $rows ) && ! empty( $rows ) ? reset( $rows ) : null;
	}

	/**
	 * Insert a webhook row.
	 *
	 * @param array<string, mixed> $data Webhook data.
	 *
	 * @return int
	 * @throws \Exception
	 */
	public function insert_webhook( array $data ): int {
		return $this->insert_data(
			array(
				'data'               => $data,
				'filter'             => new WebhookFilter(),
				'table_name'         => $this->tb_lp_webhooks,
				'key_auto_increment' => WebhookFilter::COL_WEBHOOK_ID,
			)
		);
	}

	/**
	 * Update a webhook row.
	 *
	 * @param array<string, mixed> $data Webhook data.
	 *
	 * @return bool
	 * @throws \Exception
	 */
	public function update_webhook( array $data ): bool {
		return $this->update_data(
			array(
				'data'       => $data,
				'filter'     => new WebhookFilter(),
				'table_name' => $this->tb_lp_webhooks,
				'where_key'  => WebhookFilter::COL_WEBHOOK_ID,
			)
		);
	}

	/**
	 * Delete webhook rows by ID.
	 *
	 * @param int[] $webhook_ids Webhook IDs.
	 *
	 * @return int
	 * @throws \Exception
	 */
	public function delete_webhooks( array $webhook_ids ): int {
		$webhook_ids = array_values( array_filter( array_map( 'absint', $webhook_ids ) ) );
		if ( empty( $webhook_ids ) ) {
			return 0;
		}

		$filter             = new WebhookFilter();
		$filter->collection = $this->tb_lp_webhooks;
		$ids_format         = LP_Helper::db_format_array( $webhook_ids, '%d' );
		// phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- Placeholders are generated for sanitized integer IDs.
		$filter->where[] = $this->wpdb->prepare( "AND webhook_id IN ($ids_format)", $webhook_ids );
		$deleted         = $this->delete_execute( $filter );

		return $deleted > 0 ? (int) $deleted : 0;
	}
}