<?php
/**
 * TitanCart Report Service
 *
 * All reporting SQL queries for Module 12.
 * Optimized: excluded status IDs resolved once and cached; no subqueries per row.
 *
 * @package TitanCart\Modules\Reports
 */

namespace TitanCart\Modules\Reports;

defined( 'ABSPATH' ) || exit;

// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter -- File-wide by design: every flagged query interpolates ONLY $pfx ($wpdb->prefix . TITANCART_TABLE_PREFIX, both constants — never user input) and $goodWhere (a cached fragment built exclusively from intval()'d status IDs at init(), or the literal '1=1'); all report filter values are bound via $wpdb->prepare() placeholders at each site. See docs/WPORG-REVIEWER-NOTES.md.

class ReportService {

	/** @var string Cached full table prefix (e.g. wp_tc_) */
	private string $pfx = '';

	/** @var string Cached WHERE fragment: "o.status_id NOT IN (4,5,6)" or "1=1" */
	private string $goodWhere = '';

	/** Lazy-init prefix + excluded-status cache. */
	private function init(): void {
		if ( $this->pfx ) return;

		global $wpdb;
		$this->pfx = $wpdb->prefix . TITANCART_TABLE_PREFIX;

		// Resolve excluded status IDs ONCE (cancelled, refunded, failed).
		$excluded = $wpdb->get_col(
			"SELECT id FROM {$this->pfx}order_statuses WHERE handle IN ('cancelled','refunded','failed')"
		);

		if ( ! empty( $excluded ) ) {
			$ids = implode( ',', array_map( 'intval', $excluded ) );
			$this->goodWhere = "o.status_id NOT IN ({$ids})";
		} else {
			$this->goodWhere = '1=1';
		}
	}

	/** Full table prefix. */
	private function pfx(): string { $this->init(); return $this->pfx; }

	/** Cached WHERE clause excluding bad statuses. */
	private function goodOrderWhere(): string { $this->init(); return $this->goodWhere; }

	// ── Period helpers ────────────────────────────────────────

	/**
	 * Resolve a named period (or custom from/to) into a date window expressed in
	 * the MERCHANT's display timezone (titancart_timezone()). KI-24 Phase 5: "today",
	 * "this week", "this month" etc. are store-local calendar concepts, so the
	 * window is built in titancart_timezone() (appearance.timezone, e.g. America/New_York)
	 * rather than WP-core tz (UTC here) — otherwise an Eastern merchant's "today"
	 * would be a UTC day and orders placed after ~8 PM ET would fall into the next
	 * day's report. The returned bounds are LOCAL wall-clock 'Y-m-d H:i:s' (store
	 * tz); any caller that queries the UTC-stored orders.created_at column converts
	 * them to UTC with utcBounds() first (getDashboardStats does so directly; the
	 * REST controller substr()s these to date-only and the per-report methods
	 * convert at their own bound chokepoint).
	 */
	public function resolvePeriod( string $period, ?string $date_from = null, ?string $date_to = null ): array {
		$tz    = titancart_timezone();
		$now   = new \DateTime( 'now', $tz );
		$today = $now->format( 'Y-m-d' );
		switch ( $period ) {
			case 'today':      $f = $today;                                                       $t = $today; break;
			case 'yesterday':  $f = $t = ( clone $now )->modify( '-1 day' )->format( 'Y-m-d' );    break;
			case 'this_week':  $f = ( clone $now )->modify( 'monday this week' )->format( 'Y-m-d' ); $t = $today; break;
			case 'last_week':  $f = ( clone $now )->modify( 'monday last week' )->format( 'Y-m-d' ); $t = ( clone $now )->modify( 'sunday last week' )->format( 'Y-m-d' ); break;
			case 'this_month': $f = $now->format( 'Y-m-01' );                                      $t = $today; break;
			case 'last_month': $f = ( clone $now )->modify( 'first day of last month' )->format( 'Y-m-d' ); $t = ( clone $now )->modify( 'last day of last month' )->format( 'Y-m-d' ); break;
			case 'this_year':  $f = $now->format( 'Y' ) . '-01-01';                                $t = $today; break;
			case 'last_year':  $y = (int) $now->format( 'Y' ) - 1; $f = "{$y}-01-01";              $t = "{$y}-12-31"; break;
			default:           $f = $date_from ?: $today;                                          $t = $date_to ?: $today; break;
		}
		return [ 'from' => $f . ' 00:00:00', 'to' => $t . ' 23:59:59' ];
	}

	/**
	 * Convert a store-local date window into UTC datetime bounds for querying the
	 * UTC-stored orders.created_at column (KI-24 Phase 5). Accepts date-only
	 * 'Y-m-d' or a 'Y-m-d H:i:s'; the date part is expanded to the full local day
	 * [00:00:00 .. 23:59:59] in titancart_timezone(), then each edge is converted to UTC
	 * (DST-aware via titancart_local_to_utc()). Falls back to the local string if a
	 * conversion fails so a query can still run.
	 */
	private function utcBounds( string $date_from, string $date_to ): array {
		$lf = substr( $date_from, 0, 10 ) . ' 00:00:00';
		$lt = substr( $date_to,   0, 10 ) . ' 23:59:59';
		return [
			'from' => titancart_local_to_utc( $lf ) ?? $lf,
			'to'   => titancart_local_to_utc( $lt ) ?? $lt,
		];
	}

	protected function previousPeriod( string $from, string $to ): array {
		$from_ts = strtotime( $from );
		$diff    = strtotime( $to ) - $from_ts;
		return [ 'from' => gmdate('Y-m-d H:i:s', $from_ts - $diff - 1), 'to' => gmdate('Y-m-d H:i:s', $from_ts - 1) ];
	}

	protected function pct( float $current, float $previous ): ?float {
		if ( $previous == 0 ) return $current > 0 ? 100.0 : null;
		return round( ( ( $current - $previous ) / $previous ) * 100, 2 );
	}

	// ── Dashboard KPIs ───────────────────────────────────────

	public function getDashboardStats( string $period = 'today', ?string $date_from = null, ?string $date_to = null ): array {
		global $wpdb;
		$pfx   = $this->pfx();
		$range = $this->resolvePeriod( $period, $date_from, $date_to ); // store-local window (echoed back as 'period')
		$q     = $this->utcBounds( $range['from'], $range['to'] );      // UTC bounds for the created_at / user_registered queries
		$prev  = $this->previousPeriod( $q['from'], $q['to'] );
		$good  = $this->goodOrderWhere();

		$cur = $wpdb->get_row( $wpdb->prepare(
			"SELECT COUNT(*) AS cnt, COALESCE(SUM(o.total),0) AS rev, COALESCE(AVG(o.total),0) AS avg_val,
			        COALESCE(SUM(o.discount),0) AS disc, COALESCE(SUM(o.shipping_cost),0) AS ship, COALESCE(SUM(o.tax_total),0) AS tax
			 FROM {$pfx}orders o WHERE {$good} AND o.created_at BETWEEN %s AND %s",
			$q['from'], $q['to']
		), ARRAY_A );

		$prv = $wpdb->get_row( $wpdb->prepare(
			"SELECT COUNT(*) AS cnt, COALESCE(SUM(o.total),0) AS rev, COALESCE(AVG(o.total),0) AS avg_val
			 FROM {$pfx}orders o WHERE {$good} AND o.created_at BETWEEN %s AND %s",
			$prev['from'], $prev['to']
		), ARRAY_A );

		$new_cust  = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$wpdb->users} WHERE user_registered BETWEEN %s AND %s", $q['from'], $q['to'] ) );
		$prev_cust = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$wpdb->users} WHERE user_registered BETWEEN %s AND %s", $prev['from'], $prev['to'] ) );

		return [
			'period'    => $range,
			'revenue'   => [ 'current' => (float)($cur['rev']??0),     'previous' => (float)($prv['rev']??0),     'change_percent' => $this->pct( (float)($cur['rev']??0),     (float)($prv['rev']??0) ) ],
			'orders'    => [ 'current' => (int)($cur['cnt']??0),        'previous' => (int)($prv['cnt']??0),        'change_percent' => $this->pct( (int)($cur['cnt']??0),       (int)($prv['cnt']??0) ) ],
			'avg_order' => [ 'current' => round((float)($cur['avg_val']??0),2), 'previous' => round((float)($prv['avg_val']??0),2), 'change_percent' => $this->pct( (float)($cur['avg_val']??0), (float)($prv['avg_val']??0) ) ],
			'customers' => [ 'current' => $new_cust, 'previous' => $prev_cust, 'change_percent' => $this->pct( $new_cust, $prev_cust ) ],
			'discounts' => (float)($cur['disc']??0),
			'shipping'  => (float)($cur['ship']??0),
			'tax'       => (float)($cur['tax']??0),
		];
	}

	// ── Sales chart ──────────────────────────────────────────

	public function getSalesChart( string $date_from, string $date_to, string $granularity = 'day' ): array {
		global $wpdb;
		$pfx  = $this->pfx();
		$good = $this->goodOrderWhere();
		$b    = $this->utcBounds( $date_from, $date_to );

		// KI-24 Phase 5: bucket by the MERCHANT's calendar period, not the UTC
		// one. created_at is stored UTC, so grouping in SQL via DATE_FORMAT would
		// split days on UTC midnight (an order at 11 PM ET lands on the next day).
		// We pull the raw rows in the UTC window, then convert each timestamp to
		// titancart_timezone() and bucket in PHP — DST-correct and portable (no MySQL
		// named-timezone tables, matching the S291 '+00:00' approach).
		$rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT o.created_at, o.total, o.discount, o.shipping_cost, o.tax_total
			 FROM {$pfx}orders o WHERE {$good} AND o.created_at BETWEEN %s AND %s",
			$b['from'], $b['to']
		), ARRAY_A );

		$tz      = titancart_timezone();
		$utc     = new \DateTimeZone( 'UTC' );
		$buckets = [];
		foreach ( $rows ?? [] as $r ) {
			$dt = \DateTime::createFromFormat( 'Y-m-d H:i:s', $r['created_at'], $utc );
			if ( ! $dt ) continue;
			$dt->setTimezone( $tz );
			switch ( $granularity ) {
				case 'month': $key = $dt->format( 'Y-m' );      break; // %Y-%m
				case 'week':  $key = $dt->format( 'o-\WW' );    break; // ISO year-week, == %x-W%v
				default:      $key = $dt->format( 'Y-m-d' );    break; // %Y-%m-%d
			}
			if ( ! isset( $buckets[ $key ] ) ) {
				$buckets[ $key ] = [ 'orders' => 0, 'revenue' => 0.0, 'discounts' => 0.0, 'shipping' => 0.0, 'tax' => 0.0 ];
			}
			$buckets[ $key ]['orders']    += 1;
			$buckets[ $key ]['revenue']   += (float) $r['total'];
			$buckets[ $key ]['discounts'] += (float) $r['discount'];
			$buckets[ $key ]['shipping']  += (float) $r['shipping_cost'];
			$buckets[ $key ]['tax']       += (float) $r['tax_total'];
		}
		ksort( $buckets );

		$out = [];
		foreach ( $buckets as $period => $b2 ) {
			$out[] = [
				'period' => $period, 'orders' => $b2['orders'], 'revenue' => $b2['revenue'],
				'avg_order' => $b2['orders'] > 0 ? round( $b2['revenue'] / $b2['orders'], 2 ) : 0.0,
				'discounts' => $b2['discounts'], 'shipping' => $b2['shipping'], 'tax' => $b2['tax'],
			];
		}
		return $out;
	}

	// ── Top products ─────────────────────────────────────────

	public function getTopProducts( string $date_from, string $date_to, int $limit = 10, string $sort_by = 'revenue' ): array {
		global $wpdb;
		$pfx   = $this->pfx();
		$good  = $this->goodOrderWhere();
		$order = $sort_by === 'quantity' ? 'total_qty DESC' : 'total_revenue DESC';
		$b     = $this->utcBounds( $date_from, $date_to );

		$rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT oi.product_id, oi.name AS product_name, oi.sku AS product_sku,
			        SUM(oi.quantity) AS total_qty, SUM(oi.total) AS total_revenue,
			        AVG(oi.unit_price) AS avg_price, p.stock_quantity AS current_stock
			 FROM {$pfx}order_items oi
			 INNER JOIN {$pfx}orders o ON o.id = oi.order_id
			 LEFT  JOIN {$pfx}products p ON p.id = oi.product_id
			 WHERE {$good} AND o.created_at BETWEEN %s AND %s
			 GROUP BY oi.product_id, oi.name, oi.sku, p.stock_quantity
			 ORDER BY {$order} LIMIT %d",
			$b['from'], $b['to'], $limit
		), ARRAY_A );

		return array_map( static fn( $r ) => [
			'product_id' => (int)$r['product_id'], 'product_name' => $r['product_name'], 'product_sku' => $r['product_sku'],
			'total_qty' => (int)$r['total_qty'], 'total_revenue' => round((float)$r['total_revenue'],2),
			'avg_price' => round((float)$r['avg_price'],2), 'current_stock' => (int)($r['current_stock']??0),
		], $rows ?? [] );
	}

	// ── Recent orders (dashboard widget) ─────────────────────

	public function getRecentOrders( int $limit = 10 ): array {
		global $wpdb;
		$pfx = $this->pfx();

		// LEFT JOIN for item count instead of correlated subquery
		$rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT o.id, o.order_number, o.billing_email, o.total, o.currency,
			        o.billing_first_name, o.billing_last_name, o.created_at,
			        COALESCE(s.handle,'') AS status,
			        COALESCE(t.name, s.handle, '') AS status_name,
			        COALESCE(s.color, '#71797E') AS status_color,
			        COALESCE(ic.cnt, 0) AS item_count
			 FROM {$pfx}orders o
			 LEFT JOIN {$pfx}order_statuses s ON s.id = o.status_id
			 LEFT JOIN {$pfx}order_status_translations t ON t.status_id = s.id AND t.language_code = 'en'
			 LEFT JOIN (
			     SELECT order_id, COUNT(*) AS cnt FROM {$pfx}order_items GROUP BY order_id
			 ) ic ON ic.order_id = o.id
			 WHERE o.deleted_at IS NULL
			 ORDER BY o.created_at DESC LIMIT %d",
			$limit
		), ARRAY_A );

		return array_map( static function ( $r ) {
			return [
				'order_id' => (int)$r['id'], 'order_number' => $r['order_number'],
				'email' => $r['billing_email'], 'status' => $r['status_name'] ?: $r['status'],
				'status_handle' => $r['status'], 'status_color' => $r['status_color'],
				'total' => (float)$r['total'], 'currency' => $r['currency'], 'created_at' => $r['created_at'],
				'customer' => trim( $r['billing_first_name'] . ' ' . $r['billing_last_name'] ),
				'item_count' => (int)$r['item_count'],
			];
		}, $rows ?? [] );
	}


	// ── Low stock ────────────────────────────────────────────

	public function getLowStockProducts( int $threshold = 5 ): array {
		global $wpdb;
		$pfx  = $this->pfx();
		$lang = function_exists('titancart_get_current_language') ? titancart_get_current_language() : 'en';

		return $wpdb->get_results( $wpdb->prepare(
			"SELECT p.id AS product_id, p.sku AS product_code, p.stock_quantity AS stock, p.status,
			        COALESCE(pt.name, '') AS name
			 FROM {$pfx}products p
			 LEFT JOIN {$pfx}product_translations pt ON pt.product_id = p.id AND pt.language_code = %s
			 WHERE p.stock_quantity <= %d AND p.status = 'active' AND p.deleted_at IS NULL
			 ORDER BY p.stock_quantity ASC LIMIT 50",
			$lang, $threshold
		), ARRAY_A ) ?? [];
	}

	// ── Order status counts (dashboard widget) ───────────────

	public function getOrderStatusCounts(): array {
		global $wpdb;
		$pfx = $this->pfx();

		$rows = $wpdb->get_results(
			"SELECT o.status_id, COALESCE(s.handle,'unknown') AS handle,
			        COALESCE(t.name, s.handle, 'Unknown') AS name,
			        COALESCE(s.color, '#71797E') AS color, COUNT(*) AS cnt
			 FROM {$pfx}orders o
			 LEFT JOIN {$pfx}order_statuses s ON s.id = o.status_id
			 LEFT JOIN {$pfx}order_status_translations t ON t.status_id = s.id AND t.language_code = 'en'
			 WHERE o.deleted_at IS NULL
			 GROUP BY o.status_id, s.handle, t.name, s.color ORDER BY cnt DESC",
			ARRAY_A
		);

		$total = 0;
		foreach ( $rows ?? [] as $r ) { $total += (int) $r['cnt']; }

		return array_map( static fn($r) => [
			'handle' => $r['handle'], 'name' => $r['name'], 'color' => $r['color'],
			'count' => (int)$r['cnt'], 'percent' => $total > 0 ? round(((int)$r['cnt']/$total)*100,1) : 0,
		], $rows ?? [] );
	}
}
