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

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

/**
 * Data access for invoices.
 */
class LUCRSURE_Invoice_Repository extends LUCRSURE_Repository {

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

	/**
	 * Column formats.
	 *
	 * @var array<string,string>
	 */
	protected $formats = array(
		'invoice_number'     => '%s',
		'customer_id'        => '%d',
		'subscription_id'    => '%d',
		'amount'             => '%f',
		'currency'           => '%s',
		'status'             => '%s',
		'due_date'           => '%s',
		'paid_at'            => '%s',
		'gateway'            => '%s',
		'gateway_payment_id' => '%s',
		'payment_url'        => '%s',
		'billing_address_1'  => '%s',
		'billing_address_2'  => '%s',
		'billing_city'       => '%s',
		'billing_state'      => '%s',
		'billing_postcode'   => '%s',
		'billing_country'    => '%s',
		'line_items'         => '%s',
		'created_at'         => '%s',
		'updated_at'         => '%s',
	);

	/**
	 * Query invoices joined with customer.
	 *
	 * @param array $args { status, limit, offset }.
	 * @return array[]
	 */
	public function query( $args = array() ) {
		global $wpdb;

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

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

		if ( '' !== $args['status'] ) {
			$where[]  = 'i.status = %s';
			$params[] = $args['status'];
		}

		$sql = 'SELECT i.*, c.name AS customer_name, c.email AS customer_email
			FROM %i i
			LEFT JOIN %i c ON c.id = i.customer_id
			WHERE ' . implode( ' AND ', $where ) . ' ORDER BY i.created_at DESC LIMIT %d OFFSET %d';

		$prepare_args = array_merge(
			array( lucrsure_table( 'invoices' ), lucrsure_table( 'customers' ) ),
			$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 an invoice with joined customer fields.
	 *
	 * @param int $id Invoice ID.
	 * @return array|null
	 */
	public function find_with_customer( $id ) {
		global $wpdb;

		$row = $wpdb->get_row( // phpcs:ignore WordPress.DB.DirectDatabaseQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->prepare(
				'SELECT i.*, c.name AS customer_name, c.email AS customer_email, c.company AS customer_company
				FROM %i i
				LEFT JOIN %i c ON c.id = i.customer_id
				WHERE i.id = %d',
				lucrsure_table( 'invoices' ),
				lucrsure_table( 'customers' ),
				(int) $id
			),
			ARRAY_A
		);

		return $row ? $row : null;
	}

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

	/**
	 * Find the most recent invoice for a subscription.
	 *
	 * @param int $subscription_id Subscription ID.
	 * @return array|null
	 */
	public function find_latest_for_subscription( $subscription_id ) {
		global $wpdb;

		$row = $wpdb->get_row( // phpcs:ignore WordPress.DB.DirectDatabaseQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->prepare(
				'SELECT * FROM %i WHERE subscription_id = %d ORDER BY id DESC LIMIT 1',
				$this->table(),
				(int) $subscription_id
			),
			ARRAY_A
		);

		return $row ? $row : null;
	}

	/**
	 * Find an invoice by gateway payment/session id.
	 *
	 * @param string $payment_id Gateway payment id.
	 * @return array|null
	 */
	public function find_by_gateway_payment( $payment_id ) {
		global $wpdb;
		if ( '' === $payment_id ) {
			return null;
		}
		$row = $wpdb->get_row( // phpcs:ignore WordPress.DB.DirectDatabaseQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->prepare( 'SELECT * FROM %i WHERE gateway_payment_id = %s', $this->table(), $payment_id ),
			ARRAY_A
		);
		return $row ? $row : null;
	}

	/**
	 * Total of paid invoices (revenue).
	 *
	 * @return float
	 */
	public function total_paid() {
		global $wpdb;
		return (float) $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->prepare(
				"SELECT COALESCE(SUM(amount),0) FROM %i WHERE status = 'paid'",
				$this->table()
			)
		);
	}

	/**
	 * Generate the next sequential invoice number.
	 *
	 * @return string
	 */
	public function next_number() {
		global $wpdb;
		$count = (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->prepare( 'SELECT COUNT(*) FROM %i', $this->table() )
		);
		return sprintf( 'INV-%s-%04d', gmdate( 'Y' ), $count + 1 );
	}

	/**
	 * Revenue grouped by month for the last N months.
	 *
	 * @param int $months Number of months.
	 * @return array<string,float> Keyed by YYYY-MM.
	 */
	public function revenue_by_month( $months = 6 ) {
		global $wpdb;

		$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->prepare(
				"SELECT DATE_FORMAT(paid_at, %s) AS ym, SUM(amount) AS total
				 FROM %i WHERE status = 'paid' AND paid_at IS NOT NULL
				 GROUP BY ym ORDER BY ym ASC LIMIT %d",
				'%Y-%m',
				$this->table(),
				$months
			),
			ARRAY_A
		);

		$out = array();
		foreach ( (array) $rows as $row ) {
			$out[ $row['ym'] ] = (float) $row['total'];
		}
		return $out;
	}

	/**
	 * Paid revenue grouped by month for a calendar year.
	 *
	 * @param int $year Four-digit year.
	 * @return array<string,float> Keyed by YYYY-MM.
	 */
	public function revenue_for_year( $year ) {
		global $wpdb;

		$year = (int) $year;
		$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->prepare(
				"SELECT DATE_FORMAT(paid_at, '%%Y-%%m') AS ym, SUM(amount) AS total
				 FROM %i WHERE status = 'paid' AND paid_at IS NOT NULL
				 AND YEAR(paid_at) = %d
				 GROUP BY ym ORDER BY ym ASC",
				$this->table(),
				$year
			),
			ARRAY_A
		);

		$out = array();
		foreach ( (array) $rows as $row ) {
			$out[ $row['ym'] ] = (float) $row['total'];
		}
		return $out;
	}
}
