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

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

/**
 * Data access for staff members.
 */
class LUCRSURE_Staff_Repository extends LUCRSURE_Repository {

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

	/**
	 * Column formats.
	 *
	 * @var array<string,string>
	 */
	protected $formats = array(
		'user_id'    => '%d',
		'name'       => '%s',
		'email'      => '%s',
		'role_id'    => '%d',
		'position'   => '%s',
		'branch_id'  => '%d',
		'status'     => '%s',
		'created_at' => '%s',
		'updated_at' => '%s',
	);

	/**
	 * Query staff joined with role name.
	 *
	 * @param array $args { search, role_id, status }.
	 * @return array[]
	 */
	public function query( $args = array() ) {
		global $wpdb;

		$args = wp_parse_args(
			$args,
			array(
				'search'  => '',
				'role_id' => 0,
				'status'  => '',
			)
		);

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

		if ( '' !== $args['search'] ) {
			$like     = '%' . $wpdb->esc_like( $args['search'] ) . '%';
			$where[]  = '(st.name LIKE %s OR st.email LIKE %s OR st.position LIKE %s)';
			$params[] = $like;
			$params[] = $like;
			$params[] = $like;
		}
		if ( $args['role_id'] ) {
			$where[]  = 'st.role_id = %d';
			$params[] = (int) $args['role_id'];
		}
		if ( '' !== $args['status'] ) {
			$where[]  = 'st.status = %s';
			$params[] = $args['status'];
		}

		$sql = 'SELECT st.*, r.name AS role_name FROM %i st
			LEFT JOIN %i r ON r.id = st.role_id
			WHERE ' . implode( ' AND ', $where ) . ' ORDER BY st.created_at DESC';

		$prepare_args = array_merge( array( lucrsure_table( 'staff' ), lucrsure_table( 'roles' ) ), $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
		);
	}
}
