<?php
/**
 * Store reports: live stock totals and the reorder shortlist, plus the unit
 * cost resolution used for variance values.
 *
 * Cost resolution is deliberately independent of any one plugin: our own
 * "_stwc_cost" meta is read first, then known cost-of-goods metas (WC native
 * COGS, SkyVerge, WPFactory, ATUM) as fallbacks. When no cost exists anywhere,
 * figures say so instead of faking a cost from the sale price.
 *
 * @package Stocktake_For_WooCommerce
 */

defined( 'ABSPATH' ) || exit;

class STWC_Report {

	const COST_META = '_stwc_cost';


	/**
	 * Meta keys we read a unit cost from, in priority order. Ours first, then
	 * the cost-of-goods plugins people commonly already have.
	 *
	 * @return string[]
	 */
	public static function cost_meta_keys() {
		return (array) apply_filters(
			'stwc_cost_meta_keys',
			array( self::COST_META, '_cogs_value', '_wc_cog_cost', '_alg_wc_cog_cost', '_purchase_price' )
		);
	}


	/* ---------------------------------------------------------------------
	 * Aggregation (SQL over wc_product_meta_lookup — one query, any catalog size)
	 * ------------------------------------------------------------------- */


	/**
	 * Store-wide totals for every stock-managed line (simple products and
	 * variations — anything with a stock quantity in the lookup table).
	 * Invariant: the lookup only stores stock_quantity for rows whose OWN
	 * _manage_stock is 'yes', so variations managed at parent level are NULL
	 * here and the pooled parent counts exactly once — never per sibling.
	 *
	 * @return array
	 */
	public static function totals() {
		global $wpdb;
		$lookup = $wpdb->prefix . 'wc_product_meta_lookup';

		$row = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT
					COUNT(*) AS line_count,
					COALESCE(SUM(l.stock_quantity), 0) AS units,
					COALESCE(SUM(l.stock_quantity * COALESCE(l.min_price, 0)), 0) AS retail_value
				FROM %i l
				JOIN %i p ON p.ID = l.product_id AND p.post_status = 'publish'
				WHERE l.stock_quantity IS NOT NULL",
				$lookup,
				$wpdb->posts
			),
			ARRAY_A
		);

		$row = $row ?: array();
		return array(
			'lines'        => (int) ( $row['line_count'] ?? 0 ),
			'units'        => (float) ( $row['units'] ?? 0 ),
			'retail_value' => (float) ( $row['retail_value'] ?? 0 ),
		);
	}




	/* ---------------------------------------------------------------------
	 * Reorder shortlist — stock-managed products at/below their low-stock
	 * threshold, or out of stock. Free: the natural "what do I buy now?" list
	 * a shopkeeper wants the moment a count reveals true levels.
	 * ------------------------------------------------------------------- */

	/**
	 * The low-stock / out-of-stock reorder list. Threshold resolution matches
	 * WooCommerce core (wc_get_low_stock_amount): own meta, then — for
	 * variations — the parent product's meta, then the store-wide setting.
	 * Only stock-managed lines are considered (an untracked product has no
	 * meaningful "on hand" to reorder against).
	 *
	 * @param int      $limit    Max rows; 0 = no limit (used by the Excel export).
	 * @param int      $offset   Rows to skip (page views page with this).
	 * @param int|null $after_id Keyset cursor (product_id > $after_id, id ASC):
	 *                           chunked exports page with this — OFFSET over the
	 *                           live urgency ordering skips/duplicates rows when
	 *                           stock moves mid-export.
	 * @return array[]
	 */
	public static function reorder_lines( $limit = 1000, $offset = 0, $after_id = null ) {
		global $wpdb;
		$lookup = $wpdb->prefix . 'wc_product_meta_lookup';
		$global = (float) get_option( 'woocommerce_notify_low_stock_amount', 2 );
		$thresh = "COALESCE(NULLIF(lsa.meta_value, ''), NULLIF(plsa.meta_value, ''), %f)";

		$cursor = '';
		$order  = "ORDER BY ( l.stock_status = 'outofstock' ) DESC, l.stock_quantity ASC, l.product_id ASC";
		if ( null !== $after_id ) {
			$cursor = $wpdb->prepare( ' AND l.product_id > %d', $after_id );
			$order  = 'ORDER BY l.product_id ASC';
		}

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- the thresh/cursor/order fragments are literal SQL assembled above; tables go through %i and values through placeholders.
		$sql = $wpdb->prepare(
			"SELECT l.product_id, l.sku, l.stock_quantity AS qty, l.stock_status,
				{$thresh} AS threshold,
				COALESCE(l.min_price, 0) AS price
			FROM %i l
			JOIN %i p ON p.ID = l.product_id AND p.post_status = 'publish'
			LEFT JOIN %i lsa ON lsa.post_id = l.product_id AND lsa.meta_key = '_low_stock_amount'
			LEFT JOIN %i plsa ON plsa.post_id = p.post_parent AND plsa.meta_key = '_low_stock_amount'
			WHERE l.stock_quantity IS NOT NULL
				AND ( l.stock_status = 'outofstock' OR l.stock_quantity <= {$thresh} ){$cursor}
			{$order}",
			// Args follow PLACEHOLDER ORDER: the SELECT's threshold %f comes
			// before the %i table identifiers; the WHERE threshold %f is last.
			$global,
			$lookup,
			$wpdb->posts,
			$wpdb->postmeta,
			$wpdb->postmeta,
			$global
		);
		if ( $limit > 0 ) {
			$sql .= ( null !== $after_id )
				? $wpdb->prepare( ' LIMIT %d', $limit )
				: $wpdb->prepare( ' LIMIT %d OFFSET %d', $limit, $offset );
		}
		return $wpdb->get_results( $sql, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $sql is fully prepared above (tables via %i, values via placeholders).
		// phpcs:enable
	}

	/**
	 * How many stock-managed products currently show a negative on-hand quantity
	 * (an oversell / bad-edit signal that quietly corrupts valuation figures).
	 *
	 * @return int
	 */
	public static function negative_stock_count() {
		global $wpdb;
		$lookup = $wpdb->prefix . 'wc_product_meta_lookup';
		return (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM %i l
				JOIN %i p ON p.ID = l.product_id AND p.post_status = 'publish'
				WHERE l.stock_quantity < 0",
				$lookup,
				$wpdb->posts
			)
		);
	}

	/** Reorder export column headers. */
	protected static function reorder_headers() {
		return array(
			__( 'Product', 'stocktake-for-woocommerce' ),
			__( 'SKU', 'stocktake-for-woocommerce' ),
			__( 'On hand', 'stocktake-for-woocommerce' ),
			__( 'Low-stock level', 'stocktake-for-woocommerce' ),
			__( 'Status', 'stocktake-for-woocommerce' ),
		);
	}

	/**
	 * One export row from a reorder_lines() row.
	 *
	 * @param array $r
	 * @return array
	 */
	protected static function reorder_row( $r ) {
		$product = wc_get_product( (int) $r['product_id'] );
		$name    = $product ? $product->get_name() : ( '#' . $r['product_id'] );
		$status  = ( 'outofstock' === $r['stock_status'] )
			? __( 'Out of stock', 'stocktake-for-woocommerce' )
			: __( 'Low', 'stocktake-for-woocommerce' );
		return array( $name, (string) $r['sku'], (float) $r['qty'], (float) $r['threshold'], $status );
	}


	public static function export_reorder_csv() {
		nocache_headers();
		header( 'Content-Type: text/csv; charset=utf-8' );
		header( 'Content-Disposition: attachment; filename=reorder-list-' . wp_date( 'Y-m-d' ) . '.csv' );

		$out = fopen( 'php://output', 'w' );
		fwrite( $out, "\xEF\xBB\xBF" ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fwrite -- UTF-8 BOM so Excel decodes accented/Turkish text.
		fputcsv( $out, self::reorder_headers(), ',', '"', '\\' );
		// Chunked with a keyset cursor (rows ordered by product id): an OFFSET
		// walk over the live urgency ordering would skip/duplicate rows whenever
		// stock moves mid-export. Flat memory at any catalogue size.
		$after = 0;
		do {
			$rows = self::reorder_lines( 1000, 0, $after );
			foreach ( $rows as $r ) {
				$after = (int) $r['product_id'];
				fputcsv( $out, array_map( array( __CLASS__, 'csv_cell' ), self::reorder_row( $r ) ), ',', '"', '\\' );
			}
			if ( function_exists( 'wp_cache_flush_runtime' ) ) {
				wp_cache_flush_runtime();
			}
		} while ( count( $rows ) === 1000 );
		fclose( $out ); // phpcs:ignore
	}




	/** Formula-proof string cells; numbers pass through untouched. */
	protected static function csv_cell( $value ) {
		return is_string( $value ) ? self::csv_safe( $value ) : $value;
	}








	/**
	 * Neutralise spreadsheet formula injection.
	 *
	 * @param string $value
	 * @return string
	 */
	protected static function csv_safe( $value ) {
		$value = (string) $value;
		if ( '' !== $value && strpbrk( $value[0], "=+-@\t\r\n" ) !== false ) {
			return "'" . $value;
		}
		return $value;
	}
}
