<?php
/**
 * Data access for cost records (the profit backbone).
 *
 * @package ProfitEngine
 */

declare( strict_types=1 );

namespace ProfitEngine\Costing;

use wpdb;

defined( 'ABSPATH' ) || exit;

/**
 * Repository for wp_pe_cost_records.
 *
 * One row per sold order line, keyed uniquely on order_item_id. Records are
 * point-in-time snapshots: unit_cost is captured at first snapshot and preserved
 * across later recomputes (e.g. refunds) so historical profit never drifts.
 */
class CostRecordRepository {

	private wpdb $db;

	private string $table;

	/**
	 * Constructor.
	 *
	 * @param wpdb $db WordPress database.
	 */
	public function __construct( wpdb $db ) {
		$this->db    = $db;
		$this->table = $db->prefix . 'profit_engine_cost_records';
	}

	/**
	 * Find a record by order item ID.
	 *
	 * @param int $order_item_id Order item ID.
	 * @return array<string,mixed>|null
	 */
	public function find_by_item( int $order_item_id ): ?array {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$row = $this->db->get_row(
			$this->db->prepare( "SELECT * FROM {$this->table} WHERE order_item_id = %d", $order_item_id ),
			ARRAY_A
		);
		return $row ?: null;
	}

	/**
	 * Whether any record exists for an order.
	 *
	 * @param int $order_id Order ID.
	 */
	public function order_has_records( int $order_id ): bool {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		return (bool) $this->db->get_var(
			$this->db->prepare( "SELECT 1 FROM {$this->table} WHERE order_id = %d LIMIT 1", $order_id )
		);
	}

	/**
	 * Insert or update a record keyed on order_item_id.
	 *
	 * @param array<string,mixed> $data Column data including order_item_id.
	 */
	public function upsert( array $data ): void {
		$order_item_id = (int) ( $data['order_item_id'] ?? 0 );
		if ( $order_item_id <= 0 ) {
			return;
		}

		$existing           = $this->find_by_item( $order_item_id );
		$data['created_at'] = $existing['created_at'] ?? gmdate( 'Y-m-d H:i:s' );

		if ( $existing ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery
			$this->db->update( $this->table, $data, array( 'order_item_id' => $order_item_id ) );
			return;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$this->db->insert( $this->table, $data );
	}

	/**
	 * Update the stored order status for all records of an order.
	 *
	 * @param int    $order_id Order ID.
	 * @param string $status   New status (without wc- prefix).
	 */
	public function update_order_status( int $order_id, string $status ): void {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$this->db->update(
			$this->table,
			array( 'order_status' => $status ),
			array( 'order_id' => $order_id )
		);
	}

	/**
	 * Delete all records for an order (used when an order is trashed/deleted).
	 *
	 * @param int $order_id Order ID.
	 */
	public function delete_for_order( int $order_id ): void {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$this->db->delete( $this->table, array( 'order_id' => $order_id ) );
	}

	/**
	 * Aggregate totals across counted statuses within a date range.
	 *
	 * @param string   $from     Y-m-d H:i:s (UTC) inclusive lower bound.
	 * @param string   $to       Y-m-d H:i:s (UTC) inclusive upper bound.
	 * @param string[] $statuses Counted order statuses.
	 * @return array<string,float>
	 */
	public function totals( string $from, string $to, array $statuses ): array {
		[ $status_sql, $status_params ] = $this->status_in( $statuses );

		$sql = "SELECT
				COALESCE(SUM(sale_revenue),0) AS revenue,
				COALESCE(SUM(cost_total),0)   AS cost,
				COALESCE(SUM(shipping_alloc),0) AS shipping,
				COALESCE(SUM(payment_fee),0)  AS payment_fee,
				COALESCE(SUM(other_cost),0)   AS other_cost,
				COALESCE(SUM(tax),0)          AS tax,
				COALESCE(SUM(profit),0)       AS profit,
				COALESCE(SUM(qty),0)          AS units,
				COUNT(DISTINCT order_id)      AS orders
			FROM {$this->table}
			WHERE order_date BETWEEN %s AND %s AND {$status_sql}";

		$params = array_merge( array( $from, $to ), $status_params );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared
		$row = $this->db->get_row( $this->db->prepare( $sql, $params ), ARRAY_A );

		return array_map( 'floatval', $row ?: array() );
	}

	/**
	 * Profit grouped by product within a date range.
	 *
	 * @param string   $from     Lower bound (UTC datetime).
	 * @param string   $to       Upper bound (UTC datetime).
	 * @param string[] $statuses Counted statuses.
	 * @param int      $limit    Max rows.
	 * @return array<int,array<string,mixed>>
	 */
	public function by_product( string $from, string $to, array $statuses, int $limit = 200 ): array {
		[ $status_sql, $status_params ] = $this->status_in( $statuses );

		$sql = "SELECT
				product_id,
				MAX(product_name)             AS product_name,
				MAX(sku)                      AS sku,
				COALESCE(SUM(sale_revenue),0) AS revenue,
				COALESCE(SUM(cost_total),0)   AS cost,
				COALESCE(SUM(profit),0)       AS profit,
				COALESCE(SUM(qty),0)          AS units
			FROM {$this->table}
			WHERE order_date BETWEEN %s AND %s AND {$status_sql}
			GROUP BY product_id
			ORDER BY profit DESC
			LIMIT %d";

		$params = array_merge( array( $from, $to ), $status_params, array( $limit ) );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared
		$rows = $this->db->get_results( $this->db->prepare( $sql, $params ), ARRAY_A );

		return $rows ?: array();
	}

	/**
	 * Total units sold per product within a date range (net of refunds).
	 *
	 * @param string   $from     Lower bound (UTC datetime).
	 * @param string   $to       Upper bound (UTC datetime).
	 * @param string[] $statuses Counted statuses.
	 * @return array<int,float> product_id => units.
	 */
	public function units_sold_by_product( string $from, string $to, array $statuses ): array {
		[ $status_sql, $status_params ] = $this->status_in( $statuses );

		$sql = "SELECT product_id, COALESCE(SUM(qty),0) AS units
			FROM {$this->table}
			WHERE order_date BETWEEN %s AND %s AND {$status_sql}
			GROUP BY product_id";

		$params = array_merge( array( $from, $to ), $status_params );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared
		$rows = $this->db->get_results( $this->db->prepare( $sql, $params ), ARRAY_A );

		$out = array();
		foreach ( $rows ?: array() as $row ) {
			$out[ (int) $row['product_id'] ] = (float) $row['units'];
		}
		return $out;
	}

	/**
	 * Revenue / cost / profit as a time series, bucketed by day/week/month.
	 *
	 * @param string   $from     Lower bound (UTC datetime).
	 * @param string   $to       Upper bound (UTC datetime).
	 * @param string[] $statuses Counted statuses.
	 * @param string   $bucket   'day' | 'week' | 'month'.
	 * @return array<int,array<string,mixed>>
	 */
	public function series( string $from, string $to, array $statuses, string $bucket ): array {
		[ $status_sql, $status_params ] = $this->status_in( $statuses );

		// Period keys are built with date functions, not DATE_FORMAT patterns:
		// this SQL goes through $wpdb->prepare(), which treats "%d" inside a
		// format string like '%Y-%m-%d' as a placeholder and breaks the query.
		switch ( $bucket ) {
			case 'hour':
				$period = "CONCAT(DATE(order_date), ' ', LPAD(HOUR(order_date), 2, '0'), ':00:00')";
				break;
			case 'month':
				$period = "CONCAT(LEFT(order_date, 7), '-01')";
				break;
			case 'week':
				$period = 'DATE(DATE_SUB(order_date, INTERVAL WEEKDAY(order_date) DAY))';
				break;
			case 'day':
			default:
				$period = 'DATE(order_date)';
				break;
		}

		$sql = "SELECT {$period} AS period,
				COALESCE(SUM(sale_revenue),0) AS revenue,
				COALESCE(SUM(cost_total),0)   AS cost,
				COALESCE(SUM(profit),0)       AS profit,
				COUNT(DISTINCT order_id)      AS orders
			FROM {$this->table}
			WHERE order_date BETWEEN %s AND %s AND {$status_sql}
			GROUP BY period
			ORDER BY period ASC";

		$params = array_merge( array( $from, $to ), $status_params );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared
		$rows = $this->db->get_results( $this->db->prepare( $sql, $params ), ARRAY_A );

		return array_map(
			static fn( array $r ): array => array(
				'period'  => (string) $r['period'],
				'revenue' => (float) $r['revenue'],
				'cost'    => (float) $r['cost'],
				'profit'  => (float) $r['profit'],
				'orders'  => (int) $r['orders'],
			),
			$rows ?: array()
		);
	}

	/**
	 * Units sold for one product within a range (net of refunds).
	 *
	 * @param int      $product_id Purchasable-unit ID.
	 * @param string   $from       Lower bound (UTC datetime).
	 * @param string   $to         Upper bound (UTC datetime).
	 * @param string[] $statuses   Counted statuses.
	 */
	public function units_sold_for_product( int $product_id, string $from, string $to, array $statuses ): float {
		[ $status_sql, $status_params ] = $this->status_in( $statuses );

		$sql = "SELECT COALESCE(SUM(qty),0) FROM {$this->table}
			WHERE product_id = %d AND order_date BETWEEN %s AND %s AND {$status_sql}";

		$params = array_merge( array( $product_id, $from, $to ), $status_params );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared
		return (float) $this->db->get_var( $this->db->prepare( $sql, $params ) );
	}

	/**
	 * Build a safe "order_status IN (...)" fragment plus its params.
	 *
	 * @param string[] $statuses Statuses.
	 * @return array{0:string,1:array<int,string>}
	 */
	private function status_in( array $statuses ): array {
		$statuses = array_values( array_filter( array_map( 'strval', $statuses ) ) );
		if ( empty( $statuses ) ) {
			// Match nothing rather than everything if misconfigured.
			return array( '1=0', array() );
		}
		$placeholders = implode( ',', array_fill( 0, count( $statuses ), '%s' ) );
		return array( "order_status IN ({$placeholders})", $statuses );
	}
}