<?php
/**
 * BrikPanel — Shared profit computation.
 *
 * Single source of truth for the dashboard Profit section AND the Google
 * Sheets "Profit" snapshot, so both always agree.
 *
 * Net profit = Revenue − Cost of goods − Expenses, where Expenses is the
 * composite of:
 *   - Ad spend          (Ad Platforms, store currency only)
 *   - Tax               (WooCommerce order tax on paid orders)
 *   - Vendor / Inventory (auto-expense rows from received purchase orders)
 *   - Other operating   (everything else in the Expenses module)
 *
 * Vendor/Inventory is NOT added on top of manual expenses — received POs
 * already write a row into wp_brikpanel_expenses, so it is a SUBSET of the
 * manual expenses total and only split out for the breakdown.
 *
 * @package BrikPanel
 * @since   3.0.0
 */

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

/**
 * Cost of goods sold for paid orders inside [$start_gmt, $end_gmt].
 *
 * Source of truth is BrikPanel's own `_brikpanel_cogs` product/variation
 * meta (always written by the product editor regardless of the WC native
 * COGS feature flag), multiplied by quantity sold. Works for BOTH simple and
 * variable products: variation lines prefer the variation's own cost and
 * fall back to the parent product's cost. Reads current cost (not a
 * sale-time snapshot) so past orders gain a cost the moment it is filled in.
 * Admin-placed orders are excluded so this reconciles with the Revenue KPI.
 *
 * @return float
 */
function brikpanel_profit_cogs( $start_gmt, $end_gmt ) {
	global $wpdb;

	$is_hpos  = get_option( 'woocommerce_custom_orders_table_enabled' ) === 'yes';
	$statuses = [ 'wc-processing', 'wc-completed' ];
	$sp       = implode( ', ', array_fill( 0, count( $statuses ), '%s' ) );

	$joins = "
		INNER JOIN {$wpdb->prefix}woocommerce_order_items oi
				ON oi.order_id = %ORDER_ID% AND oi.order_item_type = 'line_item'
		INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta qty
				ON qty.order_item_id = oi.order_item_id AND qty.meta_key = '_qty'
		LEFT JOIN  {$wpdb->prefix}woocommerce_order_itemmeta pid
				ON pid.order_item_id = oi.order_item_id AND pid.meta_key = '_product_id'
		LEFT JOIN  {$wpdb->prefix}woocommerce_order_itemmeta vid
				ON vid.order_item_id = oi.order_item_id AND vid.meta_key = '_variation_id'
		LEFT JOIN  {$wpdb->postmeta} vcog
				ON vcog.post_id = CAST(vid.meta_value AS UNSIGNED)
			   AND vcog.meta_key = '_brikpanel_cogs'
			   AND CAST(vid.meta_value AS UNSIGNED) > 0
		LEFT JOIN  {$wpdb->postmeta} pcog
				ON pcog.post_id = CAST(pid.meta_value AS UNSIGNED)
			   AND pcog.meta_key = '_brikpanel_cogs'";

	$sum = "COALESCE(SUM(
			CAST(qty.meta_value AS DECIMAL(20,4)) *
			CAST(COALESCE(NULLIF(vcog.meta_value, ''), pcog.meta_value, '0') AS DECIMAL(20,4))
		), 0)";

	if ( $is_hpos ) {
		$j    = str_replace( '%ORDER_ID%', 'o.id', $joins );
		$sql  = "SELECT {$sum}
			FROM {$wpdb->prefix}wc_orders o
			{$j}
			WHERE o.type = 'shop_order' AND o.status IN ($sp)
			  AND o.date_created_gmt >= %s AND o.date_created_gmt <= %s";
		$args = array_merge( $statuses, [ $start_gmt, $end_gmt ] );
		$excl = brikpanel_admin_order_exclusion_sql( true );
	} else {
		$j    = str_replace( '%ORDER_ID%', 'p.ID', $joins );
		$sql  = "SELECT {$sum}
			FROM {$wpdb->posts} p
			{$j}
			WHERE p.post_type = 'shop_order' AND p.post_status IN ($sp)
			  AND p.post_date_gmt >= %s AND p.post_date_gmt <= %s";
		$args = array_merge( $statuses, [ $start_gmt, $end_gmt ] );
		$excl = brikpanel_admin_order_exclusion_sql( false, 'p.ID' );
	}

	if ( ! empty( $excl['sql'] ) ) {
		$sql .= $excl['sql'];
		$args = array_merge( $args, $excl['args'] );
	}

	return (float) $wpdb->get_var( $wpdb->prepare( $sql, $args ) ); // phpcs:ignore
}

/**
 * How much of the period's sales actually has a cost on file.
 *
 * The COGS figure silently treats any product without a `_brikpanel_cogs`
 * value as zero-cost, which inflates Net profit. This measures that gap so
 * the UI can warn instead of presenting an over-optimistic margin as fact.
 *
 * Coverage is revenue-weighted (line totals of items WITH a cost ÷ all line
 * totals) so a few cheap costless add-ons barely move it while a costless
 * hero product flags loudly. Works for simple AND variable products with the
 * same variation→parent cost fallback as brikpanel_profit_cogs().
 *
 * @return array{total_lines:int,missing_lines:int,total_revenue:float,missing_revenue:float,coverage_pct:float}
 */
function brikpanel_profit_cogs_coverage( $start_gmt, $end_gmt ) {
	global $wpdb;

	$is_hpos  = get_option( 'woocommerce_custom_orders_table_enabled' ) === 'yes';
	$statuses = [ 'wc-processing', 'wc-completed' ];
	$sp       = implode( ', ', array_fill( 0, count( $statuses ), '%s' ) );

	$joins = "
		INNER JOIN {$wpdb->prefix}woocommerce_order_items oi
				ON oi.order_id = %ORDER_ID% AND oi.order_item_type = 'line_item'
		LEFT JOIN  {$wpdb->prefix}woocommerce_order_itemmeta lt
				ON lt.order_item_id = oi.order_item_id AND lt.meta_key = '_line_total'
		LEFT JOIN  {$wpdb->prefix}woocommerce_order_itemmeta pid
				ON pid.order_item_id = oi.order_item_id AND pid.meta_key = '_product_id'
		LEFT JOIN  {$wpdb->prefix}woocommerce_order_itemmeta vid
				ON vid.order_item_id = oi.order_item_id AND vid.meta_key = '_variation_id'
		LEFT JOIN  {$wpdb->postmeta} vcog
				ON vcog.post_id = CAST(vid.meta_value AS UNSIGNED)
			   AND vcog.meta_key = '_brikpanel_cogs'
			   AND CAST(vid.meta_value AS UNSIGNED) > 0
		LEFT JOIN  {$wpdb->postmeta} pcog
				ON pcog.post_id = CAST(pid.meta_value AS UNSIGNED)
			   AND pcog.meta_key = '_brikpanel_cogs'";

	// A line is "covered" when its resolved cost (variation first, parent
	// fallback) is a positive number; everything else counts as missing.
	$cost   = "CAST(COALESCE(NULLIF(vcog.meta_value, ''), pcog.meta_value, '0') AS DECIMAL(20,4))";
	$rev    = "CAST(COALESCE(lt.meta_value, '0') AS DECIMAL(20,4))";
	$select = "
		COUNT(*) AS total_lines,
		COALESCE(SUM(CASE WHEN {$cost} <= 0 THEN 1 ELSE 0 END), 0) AS missing_lines,
		COALESCE(SUM({$rev}), 0) AS total_revenue,
		COALESCE(SUM(CASE WHEN {$cost} <= 0 THEN {$rev} ELSE 0 END), 0) AS missing_revenue";

	if ( $is_hpos ) {
		$j    = str_replace( '%ORDER_ID%', 'o.id', $joins );
		$sql  = "SELECT {$select}
			FROM {$wpdb->prefix}wc_orders o
			{$j}
			WHERE o.type = 'shop_order' AND o.status IN ($sp)
			  AND o.date_created_gmt >= %s AND o.date_created_gmt <= %s";
		$args = array_merge( $statuses, [ $start_gmt, $end_gmt ] );
		$excl = brikpanel_admin_order_exclusion_sql( true );
	} else {
		$j    = str_replace( '%ORDER_ID%', 'p.ID', $joins );
		$sql  = "SELECT {$select}
			FROM {$wpdb->posts} p
			{$j}
			WHERE p.post_type = 'shop_order' AND p.post_status IN ($sp)
			  AND p.post_date_gmt >= %s AND p.post_date_gmt <= %s";
		$args = array_merge( $statuses, [ $start_gmt, $end_gmt ] );
		$excl = brikpanel_admin_order_exclusion_sql( false, 'p.ID' );
	}

	if ( ! empty( $excl['sql'] ) ) {
		$sql .= $excl['sql'];
		$args = array_merge( $args, $excl['args'] );
	}

	$row = $wpdb->get_row( $wpdb->prepare( $sql, $args ) ); // phpcs:ignore

	$total_lines = (int) ( $row->total_lines ?? 0 );
	$missing     = (int) ( $row->missing_lines ?? 0 );
	$total_rev   = (float) ( $row->total_revenue ?? 0 );
	$missing_rev = (float) ( $row->missing_revenue ?? 0 );

	if ( $total_rev > 0 ) {
		$coverage = round( ( ( $total_rev - $missing_rev ) / $total_rev ) * 100, 1 );
	} elseif ( $total_lines > 0 ) {
		$coverage = round( ( ( $total_lines - $missing ) / $total_lines ) * 100, 1 );
	} else {
		$coverage = 100.0;
	}

	return [
		'total_lines'     => $total_lines,
		'missing_lines'   => $missing,
		'total_revenue'   => $total_rev,
		'missing_revenue' => $missing_rev,
		'coverage_pct'    => max( 0.0, min( 100.0, $coverage ) ),
	];
}

/**
 * Total WooCommerce tax on paid orders inside [$start_gmt, $end_gmt].
 * Admin orders excluded to match the revenue/COGS basis.
 *
 * @return float
 */
function brikpanel_profit_tax( $start_gmt, $end_gmt ) {
	global $wpdb;

	$is_hpos  = get_option( 'woocommerce_custom_orders_table_enabled' ) === 'yes';
	$statuses = [ 'wc-processing', 'wc-completed' ];
	$sp       = implode( ', ', array_fill( 0, count( $statuses ), '%s' ) );

	if ( $is_hpos ) {
		$sql  = "SELECT COALESCE(SUM(o.tax_amount), 0)
			FROM {$wpdb->prefix}wc_orders o
			WHERE o.type = 'shop_order' AND o.status IN ($sp)
			  AND o.date_created_gmt >= %s AND o.date_created_gmt <= %s";
		$args = array_merge( $statuses, [ $start_gmt, $end_gmt ] );
		$excl = brikpanel_admin_order_exclusion_sql( true );
	} else {
		// Legacy: cart tax + shipping tax meta.
		$sql  = "SELECT COALESCE(SUM(
				CAST(IFNULL(t1.meta_value,'0') AS DECIMAL(20,4)) +
				CAST(IFNULL(t2.meta_value,'0') AS DECIMAL(20,4))
			), 0)
			FROM {$wpdb->posts} p
			LEFT JOIN {$wpdb->postmeta} t1 ON t1.post_id = p.ID AND t1.meta_key = '_order_tax'
			LEFT JOIN {$wpdb->postmeta} t2 ON t2.post_id = p.ID AND t2.meta_key = '_order_shipping_tax'
			WHERE p.post_type = 'shop_order' AND p.post_status IN ($sp)
			  AND p.post_date_gmt >= %s AND p.post_date_gmt <= %s";
		$args = array_merge( $statuses, [ $start_gmt, $end_gmt ] );
		$excl = brikpanel_admin_order_exclusion_sql( false, 'p.ID' );
	}

	if ( ! empty( $excl['sql'] ) ) {
		$sql .= $excl['sql'];
		$args = array_merge( $args, $excl['args'] );
	}

	$tax = (float) $wpdb->get_var( $wpdb->prepare( $sql, $args ) ); // phpcs:ignore

	/**
	 * Filter the tax total that feeds the Profit section.
	 *
	 * @param float  $tax       Order tax for the window.
	 * @param string $start_gmt Y-m-d H:i:s (UTC)
	 * @param string $end_gmt   Y-m-d H:i:s (UTC)
	 */
	return (float) apply_filters( 'brikpanel_profit_tax', $tax, $start_gmt, $end_gmt );
}

/**
 * Manual expenses inside a local date range, split into the
 * vendor/inventory category (auto-created from received POs) and everything
 * else. Returns [ total, inventory, other ]. All zeros when the Expenses
 * module table doesn't exist yet.
 *
 * @param string $start_local Y-m-d H:i:s
 * @param string $end_local   Y-m-d H:i:s
 * @return array{0:float,1:float,2:float}
 */
function brikpanel_profit_manual_expenses( $start_local, $end_local ) {
	global $wpdb;

	$table = $wpdb->prefix . 'brikpanel_expenses';
	if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) !== $table ) {
		return [ 0.0, 0.0, 0.0 ];
	}

	$start_date = substr( (string) $start_local, 0, 10 );
	$end_date   = substr( (string) $end_local, 0, 10 );
	$inv_cat    = (string) get_option( 'brikpanel_po_expense_category', 'Inventory' );

	$total = (float) $wpdb->get_var( $wpdb->prepare(
		"SELECT COALESCE(SUM(amount), 0) FROM {$table} WHERE expense_date BETWEEN %s AND %s",
		$start_date,
		$end_date
	) ); // phpcs:ignore

	$inventory = (float) $wpdb->get_var( $wpdb->prepare(
		"SELECT COALESCE(SUM(amount), 0) FROM {$table}
		 WHERE expense_date BETWEEN %s AND %s AND category = %s",
		$start_date,
		$end_date,
		$inv_cat
	) ); // phpcs:ignore

	$other = $total - $inventory;
	if ( $other < 0 ) {
		$other = 0.0;
	}

	return [ $total, $inventory, $other ];
}

/**
 * Ad spend in the STORE currency for a local date range. Foreign-currency
 * spend is ignored (can't be converted reliably). 0 when the Ad Platforms
 * module is absent or has no data.
 *
 * @param string $start_local Y-m-d H:i:s
 * @param string $end_local   Y-m-d H:i:s
 * @return float
 */
function brikpanel_profit_ad_spend( $start_local, $end_local ) {
	if ( ! class_exists( 'Brikpanel_Ads_Store' ) ) {
		return 0.0;
	}
	$start_date = substr( (string) $start_local, 0, 10 );
	$end_date   = substr( (string) $end_local, 0, 10 );
	if ( ! preg_match( '/^\d{4}-\d{2}-\d{2}$/', $start_date ) || ! preg_match( '/^\d{4}-\d{2}-\d{2}$/', $end_date ) ) {
		return 0.0;
	}

	$store_cur = function_exists( 'get_woocommerce_currency' ) ? (string) get_woocommerce_currency() : '';
	if ( '' === $store_cur ) {
		return 0.0;
	}

	$rows  = Brikpanel_Ads_Store::totals_for_range( $start_date, $end_date );
	$spend = 0.0;
	foreach ( (array) $rows as $r ) {
		$cur = isset( $r['currency'] ) && $r['currency'] !== '' ? $r['currency'] : $store_cur;
		if ( $cur === $store_cur ) {
			$spend += (float) $r['spend'];
		}
	}

	/**
	 * Filter the store-currency ad spend that feeds the Profit section.
	 *
	 * @param float  $spend       Store-currency ad spend for the window.
	 * @param string $start_local Y-m-d H:i:s
	 * @param string $end_local   Y-m-d H:i:s
	 */
	return (float) apply_filters( 'brikpanel_profit_ad_spend', $spend, $start_local, $end_local );
}

/**
 * Full profit snapshot for one period. Revenue is passed in by the caller so
 * it always matches whatever "Total Sales" figure that surface already shows
 * (the dashboard never displays two different revenue numbers).
 *
 * @param float  $revenue     Pre-computed revenue for the window.
 * @param string $start_gmt   Y-m-d H:i:s (UTC)
 * @param string $end_gmt     Y-m-d H:i:s (UTC)
 * @param string $start_local Y-m-d H:i:s (site time)
 * @param string $end_local   Y-m-d H:i:s (site time)
 * @return array
 */
function brikpanel_profit_snapshot( $revenue, $start_gmt, $end_gmt, $start_local, $end_local ) {
	$revenue  = (float) $revenue;
	$cogs     = brikpanel_profit_cogs( $start_gmt, $end_gmt );
	$coverage = brikpanel_profit_cogs_coverage( $start_gmt, $end_gmt );
	$tax      = brikpanel_profit_tax( $start_gmt, $end_gmt );
	$ads      = brikpanel_profit_ad_spend( $start_local, $end_local );

	list( $exp_manual, $exp_inventory, $exp_other ) = brikpanel_profit_manual_expenses( $start_local, $end_local );

	$expenses_total = $tax + $ads + $exp_manual; // manual already includes inventory
	$net            = $revenue - $cogs - $expenses_total;

	$pct = function ( $part ) use ( $revenue ) {
		return $revenue > 0 ? round( ( $part / $revenue ) * 100, 1 ) : 0.0;
	};

	return [
		'revenue_raw'        => $revenue,
		'cogs_raw'           => $cogs,
		'tax_raw'            => $tax,
		'ad_spend_raw'       => $ads,
		'exp_manual_raw'     => $exp_manual,
		'exp_inventory_raw'  => $exp_inventory,
		'exp_other_raw'      => $exp_other,
		'expenses_total_raw' => $expenses_total,
		'net_raw'            => $net,
		'has_cogs'           => $cogs > 0,
		// Data-quality signals so the UI never presents an over-optimistic
		// Net profit as hard fact. "incomplete" is the actionable one:
		// there is revenue, but part of it has no cost on file.
		'cogs_coverage_pct'  => $coverage['coverage_pct'],
		'cogs_missing_lines' => $coverage['missing_lines'],
		'cogs_incomplete'    => ( $revenue > 0 && $coverage['missing_lines'] > 0 && $coverage['coverage_pct'] < 99.5 ),
		'cogs_pct'           => $pct( $cogs ),
		'expenses_pct'       => $pct( $expenses_total ),
		'margin'             => $pct( $net ),
		// Ordered components that sum to expenses_total. Inventory + Other
		// together equal the manual expenses table; Ads + Tax are external.
		'breakdown'          => [
			'ads'       => $ads,
			'tax'       => $tax,
			'inventory' => $exp_inventory,
			'other'     => $exp_other,
		],
	];
}
