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

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

/**
 * Data access for customers.
 */
class LUCRSURE_Customer_Repository extends LUCRSURE_Repository {

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

	/**
	 * Column formats.
	 *
	 * @var array<string,string>
	 */
	protected $formats = array(
		'user_id'            => '%d',
		'name'               => '%s',
		'email'              => '%s',
		'phone'              => '%s',
		'company'            => '%s',
		'billing_address_1'  => '%s',
		'billing_address_2'  => '%s',
		'billing_city'       => '%s',
		'billing_state'      => '%s',
		'billing_postcode'   => '%s',
		'billing_country'    => '%s',
		'branch_id'          => '%d',
		'status'             => '%s',
		'notes'              => '%s',
		'created_at'         => '%s',
		'updated_at'         => '%s',
	);

	/**
	 * Query customers with optional filters and search.
	 *
	 * @param array $args { search, status, branch_id, plan_id, limit, offset }.
	 * @return array[]
	 */
	public function query( $args = array() ) {
		global $wpdb;

		$args = wp_parse_args(
			$args,
			array(
				'search'    => '',
				'status'    => '',
				'branch_id' => 0,
				'limit'     => 50,
				'offset'    => 0,
			)
		);

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

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

		$sql = 'SELECT * FROM %i WHERE ' . implode( ' AND ', $where ) . ' ORDER BY created_at DESC LIMIT %d OFFSET %d';
		$prepare_args = array_merge(
			array( $this->table() ),
			$params,
			array( (int) $args['limit'], (int) $args['offset'] )
		);

		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
		);
	}

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