<?php

/**
 * Data-access layer for impressions, conversions, and revenue used by the Analytics pages.
 *
 * @link       https://nudgesells.com
 * @since      1.0.0
 *
 * @package    NudgeSells
 * @subpackage NudgeSells/includes
 */

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

/**
 * NudgeSells_Analytics_Data
 *
 * Provides the database queries behind impression tracking and the Analytics summary/breakdown views.
 *
 * @since      1.0.0
 * @package    NudgeSells
 * @subpackage NudgeSells/includes
 * @author     NudgeSells <support@nudgesells.com>
 */
class NudgeSells_Analytics_Data
{

	/**
	 * Record impression.
	 *
	 * @param mixed $offer_id
	 *
	 * @return void
	 * @since 1.0.0
	 */
	public static function record_impression($offer_id)
	{
		global $wpdb;
		$offer_id = absint($offer_id);
		if (! $offer_id) {
			return;
		}

		$updated = $wpdb->query($wpdb->prepare(
			"UPDATE {$wpdb->postmeta} SET meta_value = meta_value + 1 WHERE post_id = %d AND meta_key = '_nudgesells_impressions'",
			$offer_id
		));

		if (! $updated) {
			add_post_meta($offer_id, '_nudgesells_impressions', 1, true);
		}

		do_action('nudgesells_impression_recorded', $offer_id);
	}

	/**
	 * Get impressions.
	 *
	 * @param mixed $offer_id
	 *
	 * @return mixed
	 * @since 1.0.0
	 */
	public static function get_impressions($offer_id)
	{
		return (int) get_post_meta(absint($offer_id), '_nudgesells_impressions', true);
	}

	/**
	 * Get summary.
	 *
	 * @param int    $days
	 * @param string $until          Optional explicit upper-bound date (Y-m-d H:i:s). Ignored unless set.
	 * @param string $since_override Optional explicit lower-bound date, overriding $days entirely when set - needed for a custom range where both ends can be in the past.
	 *
	 * @return mixed
	 * @since 1.0.0
	 */
	public static function get_summary($days = 30, $until = null, $since_override = null)
	{
		global $wpdb;

		$since = $since_override ? $since_override : gmdate('Y-m-d H:i:s', time() - ($days * DAY_IN_SECONDS));
		$o     = NudgeSells_Order_Compat::parts();
		$o['table']      = esc_sql($o['table']);
		$o['id_col']     = esc_sql($o['id_col']);
		$o['date_col']   = esc_sql($o['date_col']);
		$o['status_col'] = esc_sql($o['status_col']);
		$in    = NudgeSells_Order_Compat::status_in_clause();
		$until_sql = $until ? 'AND orders_t.' . $o['date_col'] . ' <= %s' : '';

		$summary_params = $until ? array($since, $until) : array($since);
		$rows = $wpdb->get_results($wpdb->prepare(
			"SELECT oim2.meta_value AS role,
			        SUM( CASE WHEN oim_rev.meta_value IS NOT NULL THEN oim_rev.meta_value ELSE 0 END ) AS revenue,
			        SUM( CASE WHEN oim_saved.meta_value IS NOT NULL THEN oim_saved.meta_value ELSE 0 END ) AS saved,
			        COUNT( DISTINCT oi.order_id ) AS order_count
			 FROM {$wpdb->prefix}woocommerce_order_items oi
			 INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim2 ON oim2.order_item_id = oi.order_item_id AND oim2.meta_key = '_nudgesells_role'
			 LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim_rev ON oim_rev.order_item_id = oi.order_item_id AND oim_rev.meta_key = '_nudgesells_revenue_amount'
			 LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim_saved ON oim_saved.order_item_id = oi.order_item_id AND oim_saved.meta_key = '_nudgesells_saved_amount'
			 INNER JOIN {$o['table']} orders_t ON orders_t.{$o['id_col']} = oi.order_id
			 WHERE orders_t.{$o['date_col']} >= %s
			 {$until_sql}
			 AND orders_t.{$o['status_col']} IN ( {$in} )
			 GROUP BY oim2.meta_value",
			$summary_params
		), ARRAY_A);

		$summary = array(
			'addon_revenue'    => 0.0,
			'upgrade_revenue'  => 0.0,
			'total_saved'      => 0.0,
			'orders_with_upsell' => 0,
		);

		$order_ids = array();
		foreach ((array) $rows as $row) {
			if ('addon' === $row['role']) {
				$summary['addon_revenue'] += (float) $row['revenue'];
			} elseif ('upgrade' === $row['role']) {
				$summary['upgrade_revenue'] += (float) $row['revenue'];
			}
			$summary['total_saved'] += (float) $row['saved'];
		}

		$distinct_orders = $wpdb->get_var($wpdb->prepare(
			"SELECT COUNT( DISTINCT oi.order_id )
			 FROM {$wpdb->prefix}woocommerce_order_items oi
			 INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim ON oim.order_item_id = oi.order_item_id AND oim.meta_key = '_nudgesells_role'
			 INNER JOIN {$o['table']} orders_t ON orders_t.{$o['id_col']} = oi.order_id
			 WHERE orders_t.{$o['date_col']} >= %s
			 {$until_sql}
			 AND orders_t.{$o['status_col']} IN ( {$in} )",
			$summary_params
		));
		$summary['orders_with_upsell'] = (int) $distinct_orders;
		$summary['total_extra_revenue'] = $summary['addon_revenue'] + $summary['upgrade_revenue'];

		return $summary;
	}

	/**
	 * Get daily revenue.
	 *
	 * Returns add-on and upgrade revenue grouped by calendar day, for
	 * charting. Every day in the range is present in the result (zero-
	 * filled where there were no orders that day), so a line chart built
	 * from this never has gaps.
	 *
	 * @param int    $days
	 * @param string $until          Optional explicit upper-bound date (Y-m-d H:i:s). Ignored unless set.
	 * @param string $since_override Optional explicit lower-bound date, overriding $days entirely when set.
	 *
	 * @return array Array of array( 'date' => 'Y-m-d', 'addon_revenue' => float, 'upgrade_revenue' => float ), oldest first.
	 * @since 1.0.0
	 */
	public static function get_daily_revenue($days = 30, $until = null, $since_override = null)
	{
		global $wpdb;

		$since_ts = $since_override ? strtotime($since_override) : (time() - ($days * DAY_IN_SECONDS));
		$until_ts = $until ? strtotime($until) : time();

		$since = gmdate('Y-m-d H:i:s', $since_ts);
		$o     = NudgeSells_Order_Compat::parts();
		$o['table']      = esc_sql($o['table']);
		$o['id_col']     = esc_sql($o['id_col']);
		$o['date_col']   = esc_sql($o['date_col']);
		$o['status_col'] = esc_sql($o['status_col']);
		$in    = NudgeSells_Order_Compat::status_in_clause();
		$until_sql = $until ? 'AND orders_t.' . $o['date_col'] . ' <= %s' : '';

		$params = $until ? array($since, $until) : array($since);
		$rows = $wpdb->get_results($wpdb->prepare(
			"SELECT DATE( orders_t.{$o['date_col']} ) AS day,
			        oim2.meta_value AS role,
			        SUM( CASE WHEN oim_rev.meta_value IS NOT NULL THEN oim_rev.meta_value ELSE 0 END ) AS revenue
			 FROM {$wpdb->prefix}woocommerce_order_items oi
			 INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim2 ON oim2.order_item_id = oi.order_item_id AND oim2.meta_key = '_nudgesells_role'
			 LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim_rev ON oim_rev.order_item_id = oi.order_item_id AND oim_rev.meta_key = '_nudgesells_revenue_amount'
			 INNER JOIN {$o['table']} orders_t ON orders_t.{$o['id_col']} = oi.order_id
			 WHERE orders_t.{$o['date_col']} >= %s
			 {$until_sql}
			 AND orders_t.{$o['status_col']} IN ( {$in} )
			 GROUP BY DATE( orders_t.{$o['date_col']} ), oim2.meta_value",
			$params
		), ARRAY_A);

		$by_day = array();
		foreach ((array) $rows as $row) {
			$day = $row['day'];
			if (! isset($by_day[$day])) {
				$by_day[$day] = array('addon_revenue' => 0.0, 'upgrade_revenue' => 0.0);
			}
			if ('addon' === $row['role']) {
				$by_day[$day]['addon_revenue'] += (float) $row['revenue'];
			} elseif ('upgrade' === $row['role']) {
				$by_day[$day]['upgrade_revenue'] += (float) $row['revenue'];
			}
		}

		// Zero-fill every day in the range, oldest to newest, regardless
		// of whether any orders actually landed on it.
		$daily = array();
		$cursor = strtotime(gmdate('Y-m-d', $since_ts));
		$last_day = strtotime(gmdate('Y-m-d', $until_ts));
		while ($cursor <= $last_day) {
			$day_key = gmdate('Y-m-d', $cursor);
			$daily[] = array(
				'date'            => $day_key,
				'addon_revenue'   => isset($by_day[$day_key]) ? round($by_day[$day_key]['addon_revenue'], 2) : 0.0,
				'upgrade_revenue' => isset($by_day[$day_key]) ? round($by_day[$day_key]['upgrade_revenue'], 2) : 0.0,
			);
			$cursor += DAY_IN_SECONDS;
		}

		return $daily;
	}

	/**
	 * Get product breakdown.
	 *
	 * @param int    $days
	 * @param int    $limit
	 * @param string $type
	 * @param string $search
	 * @param string $orderby
	 * @param string $order
	 * @param string $until          Optional explicit upper-bound date (Y-m-d H:i:s). Ignored unless set.
	 * @param string $since_override Optional explicit lower-bound date, overriding $days entirely when set.
	 *
	 * @return mixed
	 * @since 1.0.0
	 */
	public static function get_product_breakdown($days = 30, $limit = 20, $type = '', $search = '', $orderby = 'revenue', $order = 'desc', $until = null, $since_override = null)
	{
		global $wpdb;

		$since = $since_override ? $since_override : gmdate('Y-m-d H:i:s', time() - ($days * DAY_IN_SECONDS));
		$o     = NudgeSells_Order_Compat::parts();
		$o['table']      = esc_sql($o['table']);
		$o['id_col']     = esc_sql($o['id_col']);
		$o['date_col']   = esc_sql($o['date_col']);
		$o['status_col'] = esc_sql($o['status_col']);
		$in    = NudgeSells_Order_Compat::status_in_clause();
		$until_sql = $until ? 'AND orders_t.' . $o['date_col'] . ' <= %s' : '';

		$role_filter = in_array($type, array('addon', 'upgrade'), true)
			? $wpdb->prepare('AND oim_role.meta_value = %s', $type)
			: "AND oim_role.meta_value IN ( 'addon', 'upgrade' )";

		$search_filter = '';
		if ('' !== trim((string) $search)) {
			$search_filter = 'AND oi.order_item_name LIKE %s';
		}

		$fetch_limit = 'conversion' === $orderby ? max($limit * 5, 100) : $limit;

		$breakdown_params = array($since);
		if ($until) {
			$breakdown_params[] = $until;
		}
		if ($search_filter) {
			$breakdown_params[] = '%' . $wpdb->esc_like($search) . '%';
		}
		$breakdown_params[] = $fetch_limit;
		$rows = $wpdb->get_results($wpdb->prepare(
			"SELECT oi.order_item_name AS name,
			        oim_role.meta_value AS role,
			        MAX( oim_pid.meta_value ) AS product_id,
			        MAX( oim_offer.meta_value ) AS offer_id,
			        SUM( CASE WHEN oim_rev.meta_value IS NOT NULL THEN oim_rev.meta_value ELSE 0 END ) AS revenue,
			        SUM( CASE WHEN oim_saved.meta_value IS NOT NULL THEN oim_saved.meta_value ELSE 0 END ) AS saved,
			        COUNT( DISTINCT oi.order_id ) AS orders,
			        SUM( CASE WHEN oim_qty.meta_value IS NOT NULL THEN oim_qty.meta_value ELSE 0 END ) AS qty
			 FROM {$wpdb->prefix}woocommerce_order_items oi
			 INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim_role ON oim_role.order_item_id = oi.order_item_id AND oim_role.meta_key = '_nudgesells_role'
			 LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim_pid ON oim_pid.order_item_id = oi.order_item_id AND oim_pid.meta_key = '_product_id'
			 LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim_qty ON oim_qty.order_item_id = oi.order_item_id AND oim_qty.meta_key = '_qty'
			 LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim_offer ON oim_offer.order_item_id = oi.order_item_id AND oim_offer.meta_key = '_nudgesells_offer_id'
			 LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim_rev ON oim_rev.order_item_id = oi.order_item_id AND oim_rev.meta_key = '_nudgesells_revenue_amount'
			 LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim_saved ON oim_saved.order_item_id = oi.order_item_id AND oim_saved.meta_key = '_nudgesells_saved_amount'
			 INNER JOIN {$o['table']} orders_t ON orders_t.{$o['id_col']} = oi.order_id
			 WHERE orders_t.{$o['date_col']} >= %s
			 {$until_sql}
			 AND orders_t.{$o['status_col']} IN ( {$in} )
			 {$role_filter}
			 {$search_filter}
			 GROUP BY oi.order_item_name, oim_role.meta_value
			 ORDER BY revenue DESC
			 LIMIT %d",
			$breakdown_params
		), ARRAY_A);

		$rows = (array) $rows;
		foreach ($rows as &$row) {
			$impressions          = self::get_impressions($row['offer_id']);
			$row['impressions']   = $impressions;
			$row['conversion']    = $impressions > 0 ? ((int) $row['orders'] / $impressions) * 100 : null;
		}
		unset($row);

		if ('conversion' === $orderby) {
			$direction = 'asc' === $order ? 1 : -1;
			usort($rows, function ($a, $b) use ($direction) {

				if (null === $a['conversion'] && null === $b['conversion']) {
					return 0;
				}
				if (null === $a['conversion']) {
					return 1;
				}
				if (null === $b['conversion']) {
					return -1;
				}
				return $direction * ($a['conversion'] <=> $b['conversion']);
			});
		} elseif ('asc' === $order) {

			$rows = array_reverse($rows);
		}

		return array_slice($rows, 0, $limit);
	}

	/**
	 * Get offer breakdown.
	 *
	 * Same shape as get_product_breakdown(), but grouped by offer
	 * instead of by product/variation name -- one row per offer, with
	 * its add-on/upgrade revenue combined. Impressions and conversion
	 * are correctly scoped here, since the row and its impression count
	 * both key off the same single offer_id (product-level rows have to
	 * pick one arbitrary offer via MAX() when a product is reused across
	 * more than one offer, which this avoids entirely).
	 *
	 * @param int    $days
	 * @param int    $limit
	 * @param string $type           '', 'addon', or 'upgrade' -- restricts which role's revenue counts toward each offer's total.
	 * @param string $search         Matches against the offer's title.
	 * @param string $orderby        'revenue' or 'conversion'.
	 * @param string $order          'asc' or 'desc'.
	 * @param string $until          Optional explicit upper-bound date (Y-m-d H:i:s). Ignored unless set.
	 * @param string $since_override Optional explicit lower-bound date, overriding $days entirely when set.
	 *
	 * @return array
	 * @since 1.0.0
	 */
	public static function get_offer_breakdown($days = 30, $limit = 20, $type = '', $search = '', $orderby = 'revenue', $order = 'desc', $until = null, $since_override = null)
	{
		global $wpdb;

		$since = $since_override ? $since_override : gmdate('Y-m-d H:i:s', time() - ($days * DAY_IN_SECONDS));
		$o     = NudgeSells_Order_Compat::parts();
		$o['table']      = esc_sql($o['table']);
		$o['id_col']     = esc_sql($o['id_col']);
		$o['date_col']   = esc_sql($o['date_col']);
		$o['status_col'] = esc_sql($o['status_col']);
		$in    = NudgeSells_Order_Compat::status_in_clause();
		$until_sql = $until ? 'AND orders_t.' . $o['date_col'] . ' <= %s' : '';

		$role_filter = in_array($type, array('addon', 'upgrade'), true)
			? $wpdb->prepare('AND oim_role.meta_value = %s', $type)
			: "AND oim_role.meta_value IN ( 'addon', 'upgrade' )";

		$offer_post_type = NudgeSells_CPT::POST_TYPE;

		$search_filter = '';
		if ('' !== trim((string) $search)) {
			$search_filter = 'AND p.post_title LIKE %s';
		}

		$fetch_limit = 'conversion' === $orderby ? max($limit * 5, 100) : $limit;

		$breakdown_params = array($since);
		if ($until) {
			$breakdown_params[] = $until;
		}
		if ($search_filter) {
			$breakdown_params[] = '%' . $wpdb->esc_like($search) . '%';
		}
		$breakdown_params[] = $fetch_limit;
		$rows = $wpdb->get_results($wpdb->prepare(
			"SELECT p.post_title AS name,
			        oim_offer.meta_value AS offer_id,
			        SUM( CASE WHEN oim_rev.meta_value IS NOT NULL THEN oim_rev.meta_value ELSE 0 END ) AS revenue,
			        SUM( CASE WHEN oim_saved.meta_value IS NOT NULL THEN oim_saved.meta_value ELSE 0 END ) AS saved,
			        COUNT( DISTINCT oi.order_id ) AS orders,
			        SUM( CASE WHEN oim_qty.meta_value IS NOT NULL THEN oim_qty.meta_value ELSE 0 END ) AS qty
			 FROM {$wpdb->prefix}woocommerce_order_items oi
			 INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim_role ON oim_role.order_item_id = oi.order_item_id AND oim_role.meta_key = '_nudgesells_role'
			 INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim_offer ON oim_offer.order_item_id = oi.order_item_id AND oim_offer.meta_key = '_nudgesells_offer_id'
			 INNER JOIN {$wpdb->posts} p ON p.ID = oim_offer.meta_value AND p.post_type = '{$offer_post_type}'
			 LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim_qty ON oim_qty.order_item_id = oi.order_item_id AND oim_qty.meta_key = '_qty'
			 LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim_rev ON oim_rev.order_item_id = oi.order_item_id AND oim_rev.meta_key = '_nudgesells_revenue_amount'
			 LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim_saved ON oim_saved.order_item_id = oi.order_item_id AND oim_saved.meta_key = '_nudgesells_saved_amount'
			 INNER JOIN {$o['table']} orders_t ON orders_t.{$o['id_col']} = oi.order_id
			 WHERE orders_t.{$o['date_col']} >= %s
			 {$until_sql}
			 AND orders_t.{$o['status_col']} IN ( {$in} )
			 {$role_filter}
			 {$search_filter}
			 GROUP BY oim_offer.meta_value
			 ORDER BY revenue DESC
			 LIMIT %d",
			$breakdown_params
		), ARRAY_A);

		$rows = (array) $rows;
		foreach ($rows as &$row) {
			$impressions          = self::get_impressions($row['offer_id']);
			$row['impressions']   = $impressions;
			$row['conversion']    = $impressions > 0 ? ((int) $row['orders'] / $impressions) * 100 : null;
		}
		unset($row);

		if ('conversion' === $orderby) {
			$direction = 'asc' === $order ? 1 : -1;
			usort($rows, function ($a, $b) use ($direction) {

				if (null === $a['conversion'] && null === $b['conversion']) {
					return 0;
				}
				if (null === $a['conversion']) {
					return 1;
				}
				if (null === $b['conversion']) {
					return -1;
				}
				return $direction * ($a['conversion'] <=> $b['conversion']);
			});
		} elseif ('asc' === $order) {

			$rows = array_reverse($rows);
		}

		return array_slice($rows, 0, $limit);
	}

	/**
	 * Get offer count.
	 *
	 * @param string $status
	 *
	 * @return mixed
	 * @since 1.0.0
	 */
	public static function get_offer_count($status = 'publish')
	{
		$counts = wp_count_posts(NudgeSells_CPT::POST_TYPE);
		return isset($counts->$status) ? (int) $counts->$status : 0;
	}

	/**
	 * Get diagnostics.
	 *
	 * @param int $sample
	 *
	 * @return array
	 * @since 1.0.0
	 */
	public static function get_diagnostics($sample = 10)
	{
		global $wpdb;
		$o = NudgeSells_Order_Compat::parts();
		$o['table']      = esc_sql($o['table']);
		$o['id_col']     = esc_sql($o['id_col']);
		$o['date_col']   = esc_sql($o['date_col']);
		$o['status_col'] = esc_sql($o['status_col']);

		$total_by_role = $wpdb->get_results(
			"SELECT oim.meta_value AS role, COUNT(*) AS c
			 FROM {$wpdb->prefix}woocommerce_order_itemmeta oim
			 WHERE oim.meta_key = '_nudgesells_role'
			 GROUP BY oim.meta_value",
			ARRAY_A
		);

		$sample_rows = $wpdb->get_results($wpdb->prepare(
			"SELECT oi.order_id, oim.meta_value AS role, orders_t.{$o['status_col']} AS order_status, orders_t.{$o['date_col']} AS order_date
			 FROM {$wpdb->prefix}woocommerce_order_itemmeta oim
			 INNER JOIN {$wpdb->prefix}woocommerce_order_items oi ON oi.order_item_id = oim.order_item_id
			 LEFT JOIN {$o['table']} orders_t ON orders_t.{$o['id_col']} = oi.order_id
			 WHERE oim.meta_key = '_nudgesells_role'
			 ORDER BY oi.order_item_id DESC
			 LIMIT %d",
			$sample
		), ARRAY_A);

		return array(
			'hpos_enabled'   => NudgeSells_Order_Compat::hpos_enabled(),
			'orders_table'   => $o['table'],
			'expected_statuses' => $o['statuses'],
			'total_by_role'  => $total_by_role,
			'sample_rows'    => $sample_rows,
		);
	}
}
