<?php
/**
 * Summary cache — computes and stores KPI values.
 *
 * @since 1.0.0
 */

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

// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL, PluginCheck.Security.DirectDB

class ZINV_Summary {

	private ZINV_Database $db;

	public function __construct() {
		$this->db = new ZINV_Database( 'zinv_summary_cache' );
	}

	public function get( string $key, string $period = 'current' ): ?string {
		global $wpdb;
		$table = $this->db->get_table_name();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$row = $wpdb->get_row( $wpdb->prepare(
			"SELECT cache_value FROM {$table} WHERE cache_key = %s AND period = %s",
			$key,
			$period
		) );

		return $row ? $row->cache_value : null;
	}

	public function refresh_all( int $ttl_seconds = 300, bool $force = false ): void {
		global $wpdb;
		$table = $this->db->get_table_name();

		// Skip if cache is still fresh unless forced.
		if ( ! $force ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$oldest = $wpdb->get_var( "SELECT MIN(computed_at) FROM {$table} WHERE period = 'current'" );
			if ( $oldest && ( time() - (int) strtotime( $oldest . ' UTC' ) ) < $ttl_seconds ) {
				return;
			}
		}

		// Concurrency semaphore: prevent concurrent request stampedes.
		if ( get_transient( 'zinv_summary_refresh_lock' ) ) {
			return;
		}
		set_transient( 'zinv_summary_refresh_lock', true, 30 );

		$this->set( 'total_revenue',      $this->compute_total_revenue() );
		$this->set( 'active_clients',     (string) $this->compute_active_clients() );
		$this->set( 'pending_amount',     $this->compute_pending_amount() );
		$this->set( 'pending_count',      (string) $this->compute_pending_count() );
		$this->set( 'overdue_count',      (string) $this->compute_overdue_count() );
		$this->set( 'paid_this_month',    $this->compute_paid_this_month() );
		$this->set( 'estimates_sent',     (string) $this->compute_estimates_sent() );
		$this->set( 'estimates_approved', (string) $this->compute_estimates_approved() );

		// Period-over-period figures so the dashboard can show real trend
		// percentages instead of hardcoded ones.
		$this->set( 'paid_last_month',     $this->compute_paid_last_month() );
		$this->set( 'clients_new_30d',     (string) $this->compute_clients_new( 0 ) );
		$this->set( 'clients_new_prev_30d', (string) $this->compute_clients_new( 30 ) );

		delete_transient( 'zinv_summary_refresh_lock' );
	}

	public function get_all(): array {
		global $wpdb;
		$table = $this->db->get_table_name();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT cache_key, cache_value FROM {$table} WHERE period = %s",
			'current'
		) );

		$result = [];
		foreach ( $rows as $row ) {
			$result[ $row->cache_key ] = $row->cache_value;
		}

		return $result;
	}

	private function set( string $key, string $value, string $period = 'current' ): void {
		global $wpdb;
		$table = $this->db->get_table_name();

		// Atomic upsert — avoids TOCTOU race between SELECT+INSERT.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$wpdb->query( $wpdb->prepare(
			"INSERT INTO `{$table}` (cache_key, cache_value, period, computed_at)
			 VALUES (%s, %s, %s, %s)
			 ON DUPLICATE KEY UPDATE cache_value = VALUES(cache_value), computed_at = VALUES(computed_at)",
			$key,
			$value,
			$period,
			gmdate( 'Y-m-d H:i:s' )
		) );
	}

	private function compute_total_revenue(): string {
		global $wpdb;
		// Keep as BCMath string — never (float) cast money sums (precision loss > $1M).
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$v = (string) $wpdb->get_var(
			"SELECT COALESCE(SUM(total), 0) FROM {$wpdb->prefix}zinv_invoices WHERE status = 'paid'"
		);
		return $v ?: '0';
	}

	private function compute_active_clients(): int {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return (int) $wpdb->get_var(
			"SELECT COUNT(*) FROM {$wpdb->prefix}zinv_clients WHERE status = 'active'"
		);
	}

	private function compute_pending_amount(): string {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$v = (string) $wpdb->get_var(
			"SELECT COALESCE(SUM(amount_due), 0) FROM {$wpdb->prefix}zinv_invoices WHERE status IN ('sent', 'viewed', 'partial', 'overdue')"
		);
		return $v ?: '0';
	}

	private function compute_pending_count(): int {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return (int) $wpdb->get_var(
			"SELECT COUNT(*) FROM {$wpdb->prefix}zinv_invoices WHERE status IN ('sent', 'viewed', 'partial', 'overdue')"
		);
	}

	private function compute_overdue_count(): int {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(*) FROM {$wpdb->prefix}zinv_invoices WHERE status IN ('sent', 'viewed', 'partial') AND due_date IS NOT NULL AND due_date < %s",
			current_time( 'Y-m-d' )
		) );
	}

	private function compute_paid_this_month(): string {
		global $wpdb;
		// current_time('timestamp') is deprecated in WP 5.3+. Compute UTC month
		// start so the sum matches gmdate-stored transaction_date values.
		$month_start = gmdate( 'Y-m-01 00:00:00' );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$v = (string) $wpdb->get_var( $wpdb->prepare(
			"SELECT COALESCE(SUM(amount), 0) FROM {$wpdb->prefix}zinv_payments WHERE status = 'successful' AND transaction_date >= %s",
			$month_start
		) );
		return $v ?: '0';
	}

	private function compute_estimates_sent(): int {
		global $wpdb;
		// Count all estimates that were ever sent (sent_at timestamp or status in sent, approved, converted).
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return (int) $wpdb->get_var(
			"SELECT COUNT(*) FROM {$wpdb->prefix}zinv_estimates WHERE sent_at IS NOT NULL OR status IN ('sent', 'approved', 'converted')"
		);
	}

	private function compute_estimates_approved(): int {
		global $wpdb;
		// Count estimates that were approved or converted to invoices.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return (int) $wpdb->get_var(
			"SELECT COUNT(*) FROM {$wpdb->prefix}zinv_estimates WHERE status IN ('approved', 'converted') OR approved_at IS NOT NULL"
		);
	}

	/**
	 * Successful payments in the previous calendar month. Paired with
	 * paid_this_month so the dashboard can show a real month-over-month trend.
	 */
	private function compute_paid_last_month(): string {
		global $wpdb;
		$this_month_start = gmdate( 'Y-m-01 00:00:00' );
		$last_month_start = gmdate( 'Y-m-01 00:00:00', strtotime( $this_month_start . ' -1 month' ) );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$v = (string) $wpdb->get_var( $wpdb->prepare(
			"SELECT COALESCE(SUM(amount), 0) FROM {$wpdb->prefix}zinv_payments
			 WHERE status = 'successful' AND transaction_date >= %s AND transaction_date < %s",
			$last_month_start,
			$this_month_start
		) );
		return $v ?: '0';
	}

	/**
	 * Clients created in a 30-day window. $days_ago = 0 is the most recent
	 * 30 days; 30 is the 30 days before that. Lets the dashboard compare
	 * new-client volume across two equal periods for a real trend.
	 */
	private function compute_clients_new( int $days_ago ): int {
		global $wpdb;
		$window_end   = gmdate( 'Y-m-d H:i:s', strtotime( "-{$days_ago} days" ) );
		$window_start = gmdate( 'Y-m-d H:i:s', strtotime( '-' . ( $days_ago + 30 ) . ' days' ) );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(*) FROM {$wpdb->prefix}zinv_clients
			 WHERE status = 'active' AND created_at >= %s AND created_at < %s",
			$window_start,
			$window_end
		) );
	}
}
