<?php
/**
 * Plan repository.
 *
 * @package Lucrativeit_Subscription_Recurring_Billing
 */

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

/**
 * Data access for subscription plans.
 */
class LUCRSURE_Plan_Repository extends LUCRSURE_Repository {

	/**
	 * Table base name.
	 *
	 * @var string
	 */
	protected $table = 'plans';

	/**
	 * Column formats.
	 *
	 * @var array<string,string>
	 */
	protected $formats = array(
		'name'                  => '%s',
		'slug'                  => '%s',
		'description'           => '%s',
		'price'                 => '%f',
		'special_price'         => '%f',
		'special_price_enabled' => '%d',
		'currency'              => '%s',
		'billing_interval'      => '%s',
		'interval_count'        => '%d',
		'trial_days'            => '%d',
		'features'              => '%s',
		'wc_product_id'         => '%d',
		'category_id'           => '%d',
		'thumbnail_id'          => '%d',
		'is_active'             => '%d',
		'is_popular'            => '%d',
		'created_at'            => '%s',
		'updated_at'            => '%s',
	);

	/**
	 * Thumbnail URL for a plan row.
	 *
	 * @param array $plan Plan row.
	 * @return string
	 */
	public static function thumbnail_url( $plan ) {
		if ( empty( $plan['thumbnail_id'] ) ) {
			return '';
		}
		$url = wp_get_attachment_image_url( (int) $plan['thumbnail_id'], 'medium' );
		return $url ? $url : '';
	}

	/**
	 * Get active plans.
	 *
	 * @return array[]
	 */
	public function active() {
		global $wpdb;
		return $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->prepare(
				'SELECT * FROM %i WHERE is_active = 1 ORDER BY price ASC',
				$this->table()
			),
			ARRAY_A
		);
	}

	/**
	 * Get the most recently created plans.
	 *
	 * @param int $limit Max rows.
	 * @return array[]
	 */
	public function latest( $limit = 5 ) {
		global $wpdb;

		$limit = max( 1, (int) $limit );

		return $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->prepare(
				'SELECT * FROM %i ORDER BY created_at DESC, id DESC LIMIT %d',
				$this->table(),
				$limit
			),
			ARRAY_A
		);
	}

	/**
	 * Find a plan by slug.
	 *
	 * @param string $slug Plan slug.
	 * @return array|null
	 */
	public function find_by_slug( $slug ) {
		global $wpdb;
		$slug = sanitize_title( $slug );
		if ( '' === $slug ) {
			return null;
		}
		$row = $wpdb->get_row( // phpcs:ignore WordPress.DB.DirectDatabaseQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->prepare( 'SELECT * FROM %i WHERE slug = %s LIMIT 1', $this->table(), $slug ),
			ARRAY_A
		);
		return $row ? $row : null;
	}

	/**
	 * Get active plans for a category slug.
	 *
	 * @param string $category_slug Category slug.
	 * @return array[]
	 */
	public function active_by_category_slug( $category_slug ) {
		global $wpdb;

		$slug = sanitize_title( $category_slug );
		if ( '' === $slug ) {
			return array();
		}

		$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->prepare(
				'SELECT p.* FROM %i p
				INNER JOIN %i c ON c.id = p.category_id
				WHERE c.slug = %s AND p.is_active = 1
				ORDER BY p.price ASC',
				$this->table(),
				lucrsure_table( 'plan_categories' ),
				$slug
			),
			ARRAY_A
		);

		return $this->dedupe_plans_by_slug( $rows ? $rows : array() );
	}

	/**
	 * Load all active plans for a category and any duplicate rows (slug/name variants).
	 *
	 * @param array $category Category row.
	 * @return array[]
	 */
	public function active_for_category( array $category ) {
		$cat_repo = new LUCRSURE_Plan_Category_Repository();
		$ids      = $cat_repo->related_ids( $category );

		if ( empty( $ids ) && ! empty( $category['slug'] ) ) {
			return $this->active_by_category_slug( $category['slug'] );
		}

		if ( empty( $ids ) ) {
			return array();
		}

		return $this->dedupe_plans_by_slug( $this->active_by_category_ids( $ids ) );
	}

	/**
	 * Load all active plans for every category row that shares a slug.
	 *
	 * @param string $category_slug Category slug.
	 * @return array[]
	 */
	public function active_by_category_slug_all( $category_slug ) {
		$cat_repo = new LUCRSURE_Plan_Category_Repository();
		$category = $cat_repo->find_by_slug( $category_slug );

		if ( $category ) {
			return $this->active_for_category( $category );
		}

		return $this->active_by_category_slug( $category_slug );
	}

	/**
	 * @param array[] $plans Plan rows.
	 * @return array[]
	 */
	protected function dedupe_plans_by_slug( array $plans ) {
		if ( empty( $plans ) ) {
			return array();
		}
		$best = array();
		foreach ( $plans as $plan ) {
			$slug = $plan['slug'];
			if ( ! isset( $best[ $slug ] ) || (int) $plan['category_id'] > (int) $best[ $slug ]['category_id'] ) {
				$best[ $slug ] = $plan;
			}
		}
		$out = array_values( $best );
		usort( $out, fn( $a, $b ) => lucrsure_plan_effective_price( $a ) <=> lucrsure_plan_effective_price( $b ) );
		return $out;
	}

	/**
	 * Get active plans for a category.
	 *
	 * @param int    $category_id Category ID (0 for all).
	 * @param string $interval    Billing interval slug.
	 * @return array[]
	 */
	public function active_by_category( $category_id = 0, $interval = '' ) {
		if ( $category_id > 0 ) {
			return $this->active_by_category_ids( array( (int) $category_id ), $interval );
		}
		return $this->active_by_category_ids( array(), $interval );
	}

	/**
	 * Get active plans for one or more categories.
	 *
	 * @param int[]  $category_ids Category IDs.
	 * @param string $interval     Billing interval slug.
	 * @return array[]
	 */
	public function active_by_category_ids( array $category_ids = array(), $interval = '' ) {
		global $wpdb;

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

		$category_ids = array_values( array_filter( array_map( 'intval', $category_ids ) ) );
		if ( ! empty( $category_ids ) ) {
			$placeholders = implode( ', ', array_fill( 0, count( $category_ids ), '%d' ) );
			$where[]      = 'category_id IN (' . $placeholders . ')';
			$params       = array_merge( $params, $category_ids );
		}

		if ( '' !== $interval ) {
			$where[]  = 'billing_interval = %s';
			$params[] = sanitize_key( $interval );
		}

		$sql = 'SELECT * FROM %i WHERE ' . implode( ' AND ', $where ) . ' ORDER BY price ASC';
		$prepare_args = array_merge( array( $this->table() ), $params );

		return $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Query prepared below; WHERE uses placeholders only.
			$wpdb->prepare( $sql, ...$prepare_args ), // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			ARRAY_A
		);
	}

	/**
	 * List plans with optional category and billing interval filters.
	 *
	 * @param int    $category_id      Category ID (0 = all).
	 * @param string $orderby          Column to order by.
	 * @param string $order            ASC|DESC.
	 * @param string $billing_interval Billing interval slug (empty = all).
	 * @return array[]
	 */
	public function all_by_category( $category_id = 0, $orderby = 'price', $order = 'ASC', $billing_interval = '' ) {
		global $wpdb;

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

		$category_id = (int) $category_id;
		if ( $category_id > 0 ) {
			$cat_repo = new LUCRSURE_Plan_Category_Repository();
			$category = $cat_repo->find( $category_id );
			if ( ! $category ) {
				return array();
			}

			$category_ids = $cat_repo->related_ids( $category );
			if ( empty( $category_ids ) ) {
				$category_ids = array( $category_id );
			}

			$placeholders = implode( ', ', array_fill( 0, count( $category_ids ), '%d' ) );
			$where[]      = 'category_id IN (' . $placeholders . ')';
			$params       = array_merge( $params, $category_ids );
		}

		$billing_interval = sanitize_key( $billing_interval );
		$valid_intervals  = array_keys( lucrsure_billing_intervals() );
		if ( '' !== $billing_interval && in_array( $billing_interval, $valid_intervals, true ) ) {
			$where[]  = 'billing_interval = %s';
			$params[] = $billing_interval;
		}

		$orderby = preg_replace( '/[^a-z0-9_]/i', '', $orderby );
		$order   = 'ASC' === strtoupper( $order ) ? 'ASC' : 'DESC';

		$sql          = 'SELECT * FROM %i WHERE ' . implode( ' AND ', $where ) . ' ORDER BY %i ' . $order; // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$prepare_args = array_merge( array( $this->table() ), $params, array( $orderby ) );

		return $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Query prepared below; WHERE uses placeholders only.
			$wpdb->prepare( $sql, ...$prepare_args ), // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			ARRAY_A
		);
	}

	/**
	 * Decode the features JSON for a plan row.
	 *
	 * @param array $plan Plan row.
	 * @return string[]
	 */
	public static function features( $plan ) {
		if ( empty( $plan['features'] ) ) {
			return array();
		}
		$decoded = json_decode( $plan['features'], true );
		return is_array( $decoded ) ? $decoded : array();
	}

	/**
	 * Clear the popular flag on sibling plans in the same category and billing interval.
	 *
	 * @param int    $category_id      Category ID.
	 * @param string $billing_interval Billing interval key.
	 * @param int    $except_plan_id   Plan ID to keep unchanged.
	 */
	public function clear_popular_in_category( $category_id, $billing_interval, $except_plan_id = 0 ) {
		global $wpdb;

		$category_id      = (int) $category_id;
		$except_plan_id   = (int) $except_plan_id;
		$billing_interval = sanitize_key( $billing_interval );

		if ( $category_id <= 0 || '' === $billing_interval ) {
			return;
		}

		if ( $except_plan_id > 0 ) {
			$wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter
				$wpdb->prepare(
					'UPDATE %i SET is_popular = 0 WHERE category_id = %d AND billing_interval = %s AND id != %d',
					$this->table(),
					$category_id,
					$billing_interval,
					$except_plan_id
				)
			);
			return;
		}

		$wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->prepare(
				'UPDATE %i SET is_popular = 0 WHERE category_id = %d AND billing_interval = %s',
				$this->table(),
				$category_id,
				$billing_interval
			)
		);
	}
}
