<?php
/**
 * Reporting engine.
 *
 * Fully functional shared API for free and extensions. The free Reports UI
 * shows a last-30-days overview. Advanced controls (custom ranges, filters,
 * CSV download UI) live in the separate Pro add-on and hook into
 * `lucrsure_reports_*` extension points — they are not locked behind payment in
 * this plugin (WordPress.org Guideline 5).
 *
 * All SQL uses fixed query strings with $wpdb->prepare() placeholders only
 * (no dynamic JOIN/WHERE string concatenation).
 *
 * @package Lucrativeit_Subscription_Recurring_Billing
 */

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

/**
 * Generates date-range reports from the core data tables.
 */
class LUCRSURE_Reports {

	/**
	 * Normalise a date string to a MySQL datetime at the start of the day.
	 *
	 * @param string $date Y-m-d date.
	 * @param string $fallback Fallback Y-m-d if $date is invalid.
	 * @return string Y-m-d 00:00:00
	 */
	public static function start_of_day( $date, $fallback ) {
		$ts = strtotime( $date );
		if ( ! $ts ) {
			$ts = strtotime( $fallback );
		}
		return gmdate( 'Y-m-d 00:00:00', $ts );
	}

	/**
	 * Normalise a date string to a MySQL datetime at the end of the day.
	 *
	 * @param string $date Y-m-d date.
	 * @param string $fallback Fallback Y-m-d if $date is invalid.
	 * @return string Y-m-d 23:59:59
	 */
	public static function end_of_day( $date, $fallback ) {
		$ts = strtotime( $date );
		if ( ! $ts ) {
			$ts = strtotime( $fallback );
		}
		return gmdate( 'Y-m-d 23:59:59', $ts );
	}

	/**
	 * Sanitise a raw filters array into ints.
	 *
	 * @param array $filters Raw filters.
	 * @return array{branch_id:int,plan_id:int,customer_id:int}
	 */
	public static function normalize_filters( $filters ) {
		return array(
			'branch_id'   => isset( $filters['branch_id'] ) ? absint( $filters['branch_id'] ) : 0,
			'plan_id'     => isset( $filters['plan_id'] ) ? absint( $filters['plan_id'] ) : 0,
			'customer_id' => isset( $filters['customer_id'] ) ? absint( $filters['customer_id'] ) : 0,
		);
	}

	/**
	 * Prepare args for optional filters: ( %d = 0 OR column = %d ).
	 *
	 * When the filter id is 0 the OR short-circuits and no row is excluded.
	 * Returned as a list so each value can be passed as its own $wpdb->prepare()
	 * argument (PHPCS cannot count replacements unpacked via ...method()).
	 *
	 * @param array $filters Raw or normalised filters.
	 * @return array{0:int,1:int,2:int,3:int,4:int,5:int}
	 */
	protected static function filter_prepare_args( $filters ) {
		$filters = self::normalize_filters( $filters );

		return array(
			$filters['branch_id'],
			$filters['branch_id'],
			$filters['plan_id'],
			$filters['plan_id'],
			$filters['customer_id'],
			$filters['customer_id'],
		);
	}

	/**
	 * Build the full report for a date range.
	 *
	 * @param string $from    MySQL datetime (inclusive).
	 * @param string $to      MySQL datetime (inclusive).
	 * @param array  $filters Optional { branch_id, plan_id, customer_id }.
	 * @return array
	 */
	public static function generate( $from, $to, $filters = array() ) {
		return array(
			'from'                   => $from,
			'to'                     => $to,
			'filters'                => self::normalize_filters( $filters ),
			'revenue'                => self::revenue( $from, $to, $filters ),
			'invoice_counts'         => self::invoice_counts( $from, $to, $filters ),
			'new_subscriptions'      => self::new_subscriptions( $from, $to, $filters ),
			'canceled_subscriptions' => self::canceled_subscriptions( $from, $to, $filters ),
			'active_now'             => self::active_now( $filters ),
			'mrr_now'                => self::mrr_now( $filters ),
			'revenue_by_day'         => self::revenue_by_day( $from, $to, $filters ),
			'revenue_by_gateway'     => self::revenue_by_gateway( $from, $to, $filters ),
			'revenue_by_plan'        => self::revenue_by_plan( $from, $to, $filters ),
		);
	}

	/**
	 * Total paid revenue in range (by paid_at).
	 *
	 * @param string $from    From datetime.
	 * @param string $to      To datetime.
	 * @param array  $filters Filters.
	 * @return float
	 */
	public static function revenue( $from, $to, $filters = array() ) {
		global $wpdb;

		$fa = self::filter_prepare_args( $filters );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Reporting aggregate.
		return (float) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COALESCE(SUM(i.amount),0)
				FROM %i i
				LEFT JOIN %i cf ON cf.id = i.customer_id
				LEFT JOIN %i sf ON sf.id = i.subscription_id
				WHERE i.status = 'paid'
					AND i.paid_at IS NOT NULL
					AND i.paid_at BETWEEN %s AND %s
					AND ( %d = 0 OR cf.branch_id = %d )
					AND ( %d = 0 OR sf.plan_id = %d )
					AND ( %d = 0 OR i.customer_id = %d )",
				lucrsure_table( 'invoices' ),
				lucrsure_table( 'customers' ),
				lucrsure_table( 'subscriptions' ),
				$from,
				$to,
				$fa[0],
				$fa[1],
				$fa[2],
				$fa[3],
				$fa[4],
				$fa[5]
			)
		);
	}

	/**
	 * Invoice counts by status for invoices created in range.
	 *
	 * @param string $from    From datetime.
	 * @param string $to      To datetime.
	 * @param array  $filters Filters.
	 * @return array<string,int>
	 */
	public static function invoice_counts( $from, $to, $filters = array() ) {
		global $wpdb;

		$fa = self::filter_prepare_args( $filters );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Reporting aggregate.
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT i.status AS status, COUNT(*) AS total
				FROM %i i
				LEFT JOIN %i cf ON cf.id = i.customer_id
				LEFT JOIN %i sf ON sf.id = i.subscription_id
				WHERE i.created_at BETWEEN %s AND %s
					AND ( %d = 0 OR cf.branch_id = %d )
					AND ( %d = 0 OR sf.plan_id = %d )
					AND ( %d = 0 OR i.customer_id = %d )
				GROUP BY i.status',
				lucrsure_table( 'invoices' ),
				lucrsure_table( 'customers' ),
				lucrsure_table( 'subscriptions' ),
				$from,
				$to,
				$fa[0],
				$fa[1],
				$fa[2],
				$fa[3],
				$fa[4],
				$fa[5]
			),
			ARRAY_A
		);

		$out = array(
			'draft'   => 0,
			'unpaid'  => 0,
			'paid'    => 0,
			'overdue' => 0,
			'void'    => 0,
		);
		foreach ( (array) $rows as $row ) {
			$out[ $row['status'] ] = (int) $row['total'];
		}
		$out['total'] = array_sum( $out );
		return $out;
	}

	/**
	 * Count of subscriptions created in range.
	 *
	 * @param string $from    From datetime.
	 * @param string $to      To datetime.
	 * @param array  $filters Filters.
	 * @return int
	 */
	public static function new_subscriptions( $from, $to, $filters = array() ) {
		global $wpdb;

		$fa = self::filter_prepare_args( $filters );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Reporting aggregate.
		return (int) $wpdb->get_var(
			$wpdb->prepare(
				'SELECT COUNT(*)
				FROM %i s
				LEFT JOIN %i cf ON cf.id = s.customer_id
				WHERE s.created_at BETWEEN %s AND %s
					AND ( %d = 0 OR cf.branch_id = %d )
					AND ( %d = 0 OR s.plan_id = %d )
					AND ( %d = 0 OR s.customer_id = %d )',
				lucrsure_table( 'subscriptions' ),
				lucrsure_table( 'customers' ),
				$from,
				$to,
				$fa[0],
				$fa[1],
				$fa[2],
				$fa[3],
				$fa[4],
				$fa[5]
			)
		);
	}

	/**
	 * Count of subscriptions canceled in range.
	 *
	 * @param string $from    From datetime.
	 * @param string $to      To datetime.
	 * @param array  $filters Filters.
	 * @return int
	 */
	public static function canceled_subscriptions( $from, $to, $filters = array() ) {
		global $wpdb;

		$fa = self::filter_prepare_args( $filters );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Reporting aggregate.
		return (int) $wpdb->get_var(
			$wpdb->prepare(
				'SELECT COUNT(*)
				FROM %i s
				LEFT JOIN %i cf ON cf.id = s.customer_id
				WHERE s.canceled_at IS NOT NULL
					AND s.canceled_at BETWEEN %s AND %s
					AND ( %d = 0 OR cf.branch_id = %d )
					AND ( %d = 0 OR s.plan_id = %d )
					AND ( %d = 0 OR s.customer_id = %d )',
				lucrsure_table( 'subscriptions' ),
				lucrsure_table( 'customers' ),
				$from,
				$to,
				$fa[0],
				$fa[1],
				$fa[2],
				$fa[3],
				$fa[4],
				$fa[5]
			)
		);
	}

	/**
	 * Currently active/trialing subscription count.
	 *
	 * @param array $filters Filters.
	 * @return int
	 */
	public static function active_now( $filters = array() ) {
		global $wpdb;

		$fa = self::filter_prepare_args( $filters );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Reporting aggregate.
		return (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*)
				FROM %i s
				LEFT JOIN %i cf ON cf.id = s.customer_id
				WHERE s.status IN ('active','trialing')
					AND ( %d = 0 OR cf.branch_id = %d )
					AND ( %d = 0 OR s.plan_id = %d )
					AND ( %d = 0 OR s.customer_id = %d )",
				lucrsure_table( 'subscriptions' ),
				lucrsure_table( 'customers' ),
				$fa[0],
				$fa[1],
				$fa[2],
				$fa[3],
				$fa[4],
				$fa[5]
			)
		);
	}

	/**
	 * Current MRR snapshot.
	 *
	 * @param array $filters Filters.
	 * @return float
	 */
	public static function mrr_now( $filters = array() ) {
		global $wpdb;

		$fa = self::filter_prepare_args( $filters );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Reporting aggregate.
		return (float) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COALESCE(SUM(s.mrr),0)
				FROM %i s
				LEFT JOIN %i cf ON cf.id = s.customer_id
				WHERE s.status IN ('active','trialing')
					AND ( %d = 0 OR cf.branch_id = %d )
					AND ( %d = 0 OR s.plan_id = %d )
					AND ( %d = 0 OR s.customer_id = %d )",
				lucrsure_table( 'subscriptions' ),
				lucrsure_table( 'customers' ),
				$fa[0],
				$fa[1],
				$fa[2],
				$fa[3],
				$fa[4],
				$fa[5]
			)
		);
	}

	/**
	 * Paid revenue grouped by day for the range (keyed by Y-m-d).
	 *
	 * @param string $from    From datetime.
	 * @param string $to      To datetime.
	 * @param array  $filters Filters.
	 * @return array<string,float>
	 */
	public static function revenue_by_day( $from, $to, $filters = array() ) {
		global $wpdb;

		$fa = self::filter_prepare_args( $filters );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Reporting aggregate.
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT DATE(i.paid_at) AS d, SUM(i.amount) AS total
				FROM %i i
				LEFT JOIN %i cf ON cf.id = i.customer_id
				LEFT JOIN %i sf ON sf.id = i.subscription_id
				WHERE i.status = 'paid'
					AND i.paid_at IS NOT NULL
					AND i.paid_at BETWEEN %s AND %s
					AND ( %d = 0 OR cf.branch_id = %d )
					AND ( %d = 0 OR sf.plan_id = %d )
					AND ( %d = 0 OR i.customer_id = %d )
				GROUP BY d
				ORDER BY d ASC",
				lucrsure_table( 'invoices' ),
				lucrsure_table( 'customers' ),
				lucrsure_table( 'subscriptions' ),
				$from,
				$to,
				$fa[0],
				$fa[1],
				$fa[2],
				$fa[3],
				$fa[4],
				$fa[5]
			),
			ARRAY_A
		);

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

	/**
	 * Paid revenue grouped by gateway in range.
	 *
	 * @param string $from    From datetime.
	 * @param string $to      To datetime.
	 * @param array  $filters Filters.
	 * @return array<string,float>
	 */
	public static function revenue_by_gateway( $from, $to, $filters = array() ) {
		global $wpdb;

		$fa = self::filter_prepare_args( $filters );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Reporting aggregate.
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT COALESCE(NULLIF(i.gateway,''),'unknown') AS gateway, SUM(i.amount) AS total
				FROM %i i
				LEFT JOIN %i cf ON cf.id = i.customer_id
				LEFT JOIN %i sf ON sf.id = i.subscription_id
				WHERE i.status = 'paid'
					AND i.paid_at IS NOT NULL
					AND i.paid_at BETWEEN %s AND %s
					AND ( %d = 0 OR cf.branch_id = %d )
					AND ( %d = 0 OR sf.plan_id = %d )
					AND ( %d = 0 OR i.customer_id = %d )
				GROUP BY gateway
				ORDER BY total DESC",
				lucrsure_table( 'invoices' ),
				lucrsure_table( 'customers' ),
				lucrsure_table( 'subscriptions' ),
				$from,
				$to,
				$fa[0],
				$fa[1],
				$fa[2],
				$fa[3],
				$fa[4],
				$fa[5]
			),
			ARRAY_A
		);

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

	/**
	 * Paid revenue grouped by plan in range.
	 *
	 * @param string $from    From datetime.
	 * @param string $to      To datetime.
	 * @param array  $filters Filters.
	 * @return array<string,float>
	 */
	public static function revenue_by_plan( $from, $to, $filters = array() ) {
		global $wpdb;

		$fa = self::filter_prepare_args( $filters );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Reporting aggregate.
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT COALESCE(p.name,'—') AS plan_name, SUM(i.amount) AS total
				FROM %i i
				LEFT JOIN %i cf ON cf.id = i.customer_id
				LEFT JOIN %i s ON s.id = i.subscription_id
				LEFT JOIN %i p ON p.id = s.plan_id
				WHERE i.status = 'paid'
					AND i.paid_at IS NOT NULL
					AND i.paid_at BETWEEN %s AND %s
					AND ( %d = 0 OR cf.branch_id = %d )
					AND ( %d = 0 OR s.plan_id = %d )
					AND ( %d = 0 OR i.customer_id = %d )
				GROUP BY plan_name
				ORDER BY total DESC",
				lucrsure_table( 'invoices' ),
				lucrsure_table( 'customers' ),
				lucrsure_table( 'subscriptions' ),
				lucrsure_table( 'plans' ),
				$from,
				$to,
				$fa[0],
				$fa[1],
				$fa[2],
				$fa[3],
				$fa[4],
				$fa[5]
			),
			ARRAY_A
		);

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

	/**
	 * Invoices in range for CSV-style export (by created_at).
	 *
	 * Shared data helper used by extensions that provide download UI. This
	 * plugin does not register an export action; the Pro add-on does.
	 *
	 * @param string $from    From datetime.
	 * @param string $to      To datetime.
	 * @param array  $filters Filters.
	 * @return array[]
	 */
	public static function invoices_for_export( $from, $to, $filters = array() ) {
		global $wpdb;

		$fa = self::filter_prepare_args( $filters );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Reporting export helper.
		return $wpdb->get_results(
			$wpdb->prepare(
				'SELECT i.invoice_number, i.amount, i.currency, i.status, i.gateway, i.created_at, i.due_date, i.paid_at,
					c.name AS customer_name, c.email AS customer_email
				FROM %i i
				LEFT JOIN %i c ON c.id = i.customer_id
				LEFT JOIN %i sf ON sf.id = i.subscription_id
				WHERE i.created_at BETWEEN %s AND %s
					AND ( %d = 0 OR c.branch_id = %d )
					AND ( %d = 0 OR sf.plan_id = %d )
					AND ( %d = 0 OR i.customer_id = %d )
				ORDER BY i.created_at ASC',
				lucrsure_table( 'invoices' ),
				lucrsure_table( 'customers' ),
				lucrsure_table( 'subscriptions' ),
				$from,
				$to,
				$fa[0],
				$fa[1],
				$fa[2],
				$fa[3],
				$fa[4],
				$fa[5]
			),
			ARRAY_A
		);
	}
}
