<?php
/**
 * The analytics snapshot behind the dashboard.
 *
 * Computed after every full scoring run (at least nightly) and cached, so opening the
 * dashboard reads one option instead of scanning orders.
 *
 * @package Statbird
 */

namespace Statbird;

defined( 'ABSPATH' ) || exit;

/**
 * Headline numbers, groups, cohorts, weekly trends, anomalies and product patterns.
 */
class Insights {

	const OPTION   = 'statbird_snapshot';
	const ACTIVE   = 180;
	const RISK_MIN = 60;

	/**
	 * Hooks.
	 */
	public static function init() {
		add_action( 'statbird_scored', array( __CLASS__, 'refresh' ) );
	}

	/**
	 * Cached snapshot.
	 *
	 * @return array Empty when nothing has been computed yet.
	 */
	public static function get() {
		$s = get_option( self::OPTION, array() );
		return is_array( $s ) ? $s : array();
	}

	/**
	 * Recomputes and caches the snapshot.
	 *
	 * @param array $state Scoring state (unused, the action passes it).
	 * @param int   $now   Unix time to treat as today.
	 * @return array
	 */
	public static function refresh( $state = array(), $now = 0 ) {
		$now               = $now ? (int) $now : time();
		$snap              = array(
			'built'    => gmdate( 'Y-m-d H:i:s', $now ),
			'currency' => get_woocommerce_currency(),
			'kpis'     => self::kpis( $now ),
			'groups'   => self::group_stats(),
			'cohorts'  => self::cohorts( $now ),
			'weeks'    => self::weeks( $now ),
			'patterns' => self::patterns( $now ),
			'model'    => self::model_summary(),
		);
		$snap['anomalies'] = self::anomalies( $snap['weeks'] );
		update_option( self::OPTION, $snap, false );
		/**
		 * Fires after the dashboard snapshot is rebuilt.
		 *
		 * @param array $snap Snapshot.
		 */
		do_action( 'statbird_snapshot_built', $snap );
		return $snap;
	}

	/**
	 * Headline numbers.
	 *
	 * @param int $now Unix time.
	 * @return array
	 */
	public static function kpis( $now ) {
		global $wpdb;
		$t      = Store::table();
		$active = gmdate( 'Y-m-d H:i:s', $now - self::ACTIVE * DAY_IN_SECONDS );
		$month  = gmdate( 'Y-m-d H:i:s', $now - 30 * DAY_IN_SECONDS );
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name only.
		$k = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT COUNT(*) AS customers,
					SUM(orders > 1) AS repeat_customers,
					SUM(last_order >= %s) AS active,
					SUM(first_order >= %s) AS new_30d,
					AVG(spent) AS avg_value,
					SUM(spent) AS revenue,
					SUM(value_12m) AS next_12m,
					SUM(CASE WHEN orders > 1 AND churn_risk >= %d THEN 1 ELSE 0 END) AS at_risk_customers,
					SUM(CASE WHEN orders > 1 AND churn_risk >= %d THEN spent / orders * orders / GREATEST(90, DATEDIFF(%s, first_order)) * 365 ELSE 0 END) AS revenue_at_risk
				FROM $t",
				$active,
				$month,
				self::RISK_MIN,
				self::RISK_MIN,
				gmdate( 'Y-m-d H:i:s', $now )
			),
			ARRAY_A
		);
		// phpcs:enable
		$customers = (int) $k['customers'];
		return array(
			'customers'         => $customers,
			'repeat_rate'       => $customers ? round( 100 * $k['repeat_customers'] / $customers, 1 ) : 0,
			'active'            => (int) $k['active'],
			'new_30d'           => (int) $k['new_30d'],
			'avg_value'         => round( (float) $k['avg_value'], 2 ),
			'revenue'           => round( (float) $k['revenue'], 2 ),
			'next_12m'          => round( (float) $k['next_12m'], 2 ),
			'at_risk_customers' => (int) $k['at_risk_customers'],
			'revenue_at_risk'   => round( (float) $k['revenue_at_risk'], 2 ),
		);
	}

	/**
	 * Count, revenue and averages per group.
	 *
	 * @return array<string,array>
	 */
	public static function group_stats() {
		global $wpdb;
		$t = Store::table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name only.
		$rows  = (array) $wpdb->get_results( "SELECT segment, COUNT(*) AS n, SUM(spent) AS revenue, AVG(spent) AS avg_spent, AVG(orders) AS avg_orders, AVG(churn_risk) AS avg_risk, SUM(value_12m) AS next_12m, AVG(DATEDIFF(UTC_TIMESTAMP(), last_order)) AS avg_days FROM $t WHERE segment <> '' GROUP BY segment", ARRAY_A );
		$total = 0.0;
		foreach ( $rows as $r ) {
			$total += (float) $r['revenue'];
		}
		$out = array();
		foreach ( Scoring::groups() as $key => $g ) {
			$out[ $key ] = array(
				'n'          => 0,
				'revenue'    => 0.0,
				'share'      => 0.0,
				'avg_spent'  => 0.0,
				'avg_orders' => 0.0,
				'avg_risk'   => 0,
				'next_12m'   => 0.0,
				'avg_days'   => 0,
			);
		}
		foreach ( $rows as $r ) {
			if ( ! isset( $out[ $r['segment'] ] ) ) {
				continue;
			}
			$out[ $r['segment'] ] = array(
				'n'          => (int) $r['n'],
				'revenue'    => round( (float) $r['revenue'], 2 ),
				'share'      => $total ? round( 100 * $r['revenue'] / $total, 1 ) : 0,
				'avg_spent'  => round( (float) $r['avg_spent'], 2 ),
				'avg_orders' => round( (float) $r['avg_orders'], 1 ),
				'avg_risk'   => (int) round( (float) $r['avg_risk'] ),
				'next_12m'   => round( (float) $r['next_12m'], 2 ),
				'avg_days'   => (int) round( (float) $r['avg_days'] ),
			);
		}
		return $out;
	}

	/**
	 * Purchase months per customer: the raw material for cohorts.
	 *
	 * @param int $since Unix time to start from.
	 * @return array<string,int[]> Customer key => sorted list of month indexes (year*12+month).
	 */
	private static function customer_months( $since ) {
		global $wpdb;
		$sql = 'SELECT ckey, YEAR(created) * 12 + MONTH(created) - 1 AS mi FROM ( ' . Orders::order_rows_sql() . ' ) t WHERE net > 0 GROUP BY ckey, mi';
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- inner SQL built from constants.
		$rows = (array) $wpdb->get_results( $sql, ARRAY_N );
		$out  = array();
		foreach ( $rows as $r ) {
			$out[ $r[0] ][] = (int) $r[1];
		}
		unset( $since );
		foreach ( $out as $k => $months ) {
			sort( $out[ $k ] );
		}
		return $out;
	}

	/**
	 * Who came back: for each of the last 12 first-purchase months, the share of those new
	 * customers who bought again in each later month.
	 *
	 * @param int $now Unix time.
	 * @return array{months:string[],rows:array[]}
	 */
	public static function cohorts( $now ) {
		$cur    = (int) gmdate( 'Y', $now ) * 12 + (int) gmdate( 'n', $now ) - 1;
		$start  = $cur - 11;
		$cohort = array();
		foreach ( self::customer_months( 0 ) as $months ) {
			$first = $months[0];
			if ( $first < $start ) {
				continue;
			}
			if ( ! isset( $cohort[ $first ] ) ) {
				$cohort[ $first ] = array(
					'size'    => 0,
					'returns' => array(),
				);
			}
			++$cohort[ $first ]['size'];
			foreach ( array_unique( $months ) as $m ) {
				$off = $m - $first;
				if ( $off > 0 ) {
					$cohort[ $first ]['returns'][ $off ] = ( $cohort[ $first ]['returns'][ $off ] ?? 0 ) + 1;
				}
			}
		}
		$rows = array();
		for ( $m = $start; $m <= $cur; $m++ ) {
			$c     = $cohort[ $m ] ?? array(
				'size'    => 0,
				'returns' => array(),
			);
			$cells = array();
			for ( $off = 1; $off <= 6; $off++ ) {
				if ( $m + $off > $cur ) {
					$cells[] = null;
				} else {
					$cells[] = $c['size'] ? round( 100 * ( $c['returns'][ $off ] ?? 0 ) / $c['size'], 1 ) : 0.0;
				}
			}
			$rows[] = array(
				'month' => sprintf( '%04d-%02d', intdiv( $m, 12 ), $m % 12 + 1 ),
				'size'  => $c['size'],
				'cells' => $cells,
			);
		}
		return array( 'rows' => $rows );
	}

	/**
	 * The last 26 full weeks: revenue, orders, new and returning customers.
	 *
	 * @param int $now Unix time.
	 * @return array[] Oldest first.
	 */
	public static function weeks( $now ) {
		global $wpdb;
		// Weeks end at the start of today (UTC), so the latest week is complete.
		$end   = strtotime( gmdate( 'Y-m-d 00:00:00', $now ) . ' UTC' );
		$start = $end - 26 * WEEK_IN_SECONDS;
		$sql   = 'SELECT FLOOR((UNIX_TIMESTAMP(created) - %d) / %d) AS wk, COUNT(*) AS orders, SUM(net) AS revenue,
				SUM(created = first_at) AS new_orders
			FROM ( SELECT t.*, f.first_at FROM ( ' . Orders::order_rows_sql() . ' ) t
				JOIN ( SELECT ckey, MIN(created) AS first_at FROM ( ' . Orders::order_rows_sql() . ' ) t2 WHERE net > 0 GROUP BY ckey ) f ON f.ckey = t.ckey
				WHERE t.net > 0 AND t.created >= %s AND t.created < %s ) x
			GROUP BY wk ORDER BY wk';
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- inner SQL built from constants.
		$rows = (array) $wpdb->get_results( $wpdb->prepare( $sql, $start, WEEK_IN_SECONDS, gmdate( 'Y-m-d H:i:s', $start ), gmdate( 'Y-m-d H:i:s', $end ) ), ARRAY_A );
		$by   = array();
		foreach ( $rows as $r ) {
			$by[ (int) $r['wk'] ] = $r;
		}
		$out = array();
		for ( $w = 0; $w < 26; $w++ ) {
			$r     = $by[ $w ] ?? array(
				'orders'     => 0,
				'revenue'    => 0,
				'new_orders' => 0,
			);
			$out[] = array(
				'start'            => gmdate( 'Y-m-d', $start + $w * WEEK_IN_SECONDS ),
				'orders'           => (int) $r['orders'],
				'revenue'          => round( (float) $r['revenue'], 2 ),
				'new_customers'    => (int) $r['new_orders'],
				'returning_orders' => (int) $r['orders'] - (int) $r['new_orders'],
			);
		}
		return $out;
	}

	/**
	 * Unusual weeks: the latest week compared with the 12 before it. A change of more than
	 * two standard deviations (and at least 15%) is flagged.
	 *
	 * @param array[] $weeks From weeks().
	 * @return array[]
	 */
	public static function anomalies( array $weeks ) {
		$out = array();
		if ( count( $weeks ) < 13 ) {
			return $out;
		}
		$latest = end( $weeks );
		$prior  = array_slice( $weeks, -13, 12 );
		foreach ( array( 'revenue', 'orders', 'new_customers', 'returning_orders' ) as $metric ) {
			$vals = array_column( $prior, $metric );
			$mean = array_sum( $vals ) / count( $vals );
			$var  = 0.0;
			foreach ( $vals as $v ) {
				$var += ( $v - $mean ) ** 2 / count( $vals );
			}
			$sd = sqrt( $var );
			$v  = (float) $latest[ $metric ];
			if ( $mean <= 0 || $sd <= 0 ) {
				continue;
			}
			$z      = ( $v - $mean ) / $sd;
			$change = ( $v - $mean ) / $mean;
			if ( abs( $z ) >= 2 && abs( $change ) >= 0.15 ) {
				$out[] = array(
					'metric' => $metric,
					'week'   => $latest['start'],
					'value'  => $v,
					'usual'  => round( $mean, 2 ),
					'change' => round( 100 * $change, 1 ),
					'z'      => round( $z, 2 ),
				);
			}
		}
		return $out;
	}

	/**
	 * Product patterns from the last two years: what sells together in one order, and what
	 * customers come back for after buying something.
	 *
	 * @param int $now Unix time.
	 * @return array{together:array[],next:array[]}
	 */
	public static function patterns( $now ) {
		global $wpdb;
		$since = gmdate( 'Y-m-d H:i:s', $now - 730 * DAY_IN_SECONDS );
		$items = $wpdb->prefix . 'woocommerce_order_items';
		$meta  = $wpdb->prefix . 'woocommerce_order_itemmeta';
		$sql   = 'SELECT t.ckey, t.order_id, UNIX_TIMESTAMP(t.created) AS ts, CAST(im.meta_value AS UNSIGNED) AS product_id
			FROM ( ' . Orders::order_rows_sql() . " ) t
			JOIN $items oi ON oi.order_id = t.order_id AND oi.order_item_type = 'line_item'
			JOIN $meta im ON im.order_item_id = oi.order_item_id AND im.meta_key = '_product_id'
			WHERE t.net > 0 AND t.created >= %s
			ORDER BY t.ckey, t.created";
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- inner SQL built from constants; table names only.
		$rows = (array) $wpdb->get_results( $wpdb->prepare( $sql, $since ), ARRAY_N );

		// Group into customers -> ordered list of orders -> products.
		$cust = array();
		foreach ( $rows as $r ) {
			$cust[ $r[0] ][ $r[1] ]['ts']  = (int) $r[2];
			$cust[ $r[0] ][ $r[1] ]['p'][] = (int) $r[3];
		}
		$together = array();
		$next     = array();
		$gaps     = array();
		$count    = array();
		foreach ( $cust as $orders ) {
			$orders = array_values( $orders );
			foreach ( $orders as $i => $o ) {
				$ps = array_values( array_unique( $o['p'] ) );
				foreach ( $ps as $a ) {
					$count[ $a ] = ( $count[ $a ] ?? 0 ) + 1;
				}
				$np = count( $ps );
				for ( $x = 0; $x < $np; $x++ ) {
					for ( $y = $x + 1; $y < $np; $y++ ) {
						$k              = min( $ps[ $x ], $ps[ $y ] ) . ':' . max( $ps[ $x ], $ps[ $y ] );
						$together[ $k ] = ( $together[ $k ] ?? 0 ) + 1;
					}
				}
				if ( isset( $orders[ $i + 1 ] ) ) {
					$days = ( $orders[ $i + 1 ]['ts'] - $o['ts'] ) / DAY_IN_SECONDS;
					foreach ( $ps as $a ) {
						foreach ( array_unique( $orders[ $i + 1 ]['p'] ) as $b ) {
							$k            = $a . '>' . $b;
							$next[ $k ]   = ( $next[ $k ] ?? 0 ) + 1;
							$gaps[ $k ][] = $days;
						}
					}
				}
			}
		}
		arsort( $together );
		arsort( $next );
		$top_t = array();
		foreach ( array_slice( $together, 0, 8, true ) as $k => $n ) {
			if ( $n < 5 ) {
				break;
			}
			list( $a, $b ) = array_map( 'intval', explode( ':', $k ) );
			$top_t[]       = array(
				'a'      => $a,
				'b'      => $b,
				'a_name' => self::product_name( $a ),
				'b_name' => self::product_name( $b ),
				'n'      => $n,
			);
		}
		$top_n = array();
		foreach ( array_slice( $next, 0, 8, true ) as $k => $n ) {
			if ( $n < 5 ) {
				break;
			}
			list( $a, $b ) = array_map( 'intval', explode( '>', $k ) );
			$g             = $gaps[ $k ];
			sort( $g );
			$top_n[] = array(
				'a'           => $a,
				'b'           => $b,
				'a_name'      => self::product_name( $a ),
				'b_name'      => self::product_name( $b ),
				'n'           => $n,
				'share'       => ! empty( $count[ $a ] ) ? round( 100 * $n / $count[ $a ], 1 ) : 0,
				'median_days' => (int) round( $g[ (int) floor( count( $g ) / 2 ) ] ),
			);
		}
		return array(
			'together' => $top_t,
			'next'     => $top_n,
		);
	}

	/**
	 * Product name, or a placeholder for deleted products.
	 *
	 * @param int $id Product ID.
	 * @return string
	 */
	private static function product_name( $id ) {
		$p = function_exists( 'wc_get_product' ) ? wc_get_product( $id ) : null;
		/* translators: %d: product ID. */
		return $p ? $p->get_name() : sprintf( __( 'Product #%d', 'statbird' ), $id );
	}

	/**
	 * Model facts for the dashboard.
	 *
	 * @return array
	 */
	public static function model_summary() {
		$s   = Scoring::state();
		$fit = $s['fit'] ?? array();
		return array(
			'method'   => $s['method'] ?? 'rule',
			'auc'      => $fit['auc'] ?? null,
			'rule_auc' => $fit['rule_auc'] ?? null,
			'recall'   => $fit['recall'] ?? null,
			'samples'  => $fit['samples'] ?? 0,
			'horizon'  => $fit['horizon'] ?? Model::HORIZON_DAYS,
			'reason'   => $fit['reason'] ?? '',
			'trained'  => $fit['trained'] ?? '',
		);
	}
}