<?php
/**
 * What keeping the promise costs against what selling it earned.
 *
 * @package WarrantyCart
 */

namespace WarrantyCart\Core;

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

/**
 * The numbers that say whether selling cover is worth doing.
 *
 * Reporting could say how much warranty revenue a shop made and nothing about
 * what that revenue cost. Those are the two halves of one question, and a shop
 * with only the first half is looking at a figure that flatters it: every
 * warranty ever sold is income until somebody claims.
 *
 * The data has existed since claims gained a typed outcome and a cost. Nothing
 * read it until now.
 *
 * Every figure here is reported with the evidence behind it, because a loss
 * ratio computed from partly-recorded costs is worse than no loss ratio at all —
 * it looks authoritative and it is wrong in the direction that pleases the
 * reader. `cost_coverage` says how many decided claims actually carry a cost,
 * and the admin screen refuses to headline a ratio that most of the claims
 * behind it never contributed to.
 */
class LossRatio {

	/**
	 * The figures for a period.
	 *
	 * @param int $days How far back to look.
	 *
	 * @return array<string, mixed>
	 */
	public static function for_range( int $days = 30 ): array {
		global $wpdb;

		$days  = max( 1, $days );
		$since = gmdate( 'Y-m-d H:i:s', strtotime( "-{$days} days" ) );

		$warranties = $wpdb->prefix . 'warranty_cart_warranties';
		$claims     = $wpdb->prefix . 'warranty_cart_claims';

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery

		/*
		 * Cancelled cover is left out of both halves.
		 *
		 * A refunded warranty is money the shop gave back, so counting it as
		 * premium would inflate the denominator and make the ratio look better
		 * than it is.
		 */
		$sold = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT COUNT(*) AS policies, COALESCE( SUM( purchase_price ), 0 ) AS premium
				 FROM {$warranties}
				 WHERE created_at >= %s AND status != %s",
				$since,
				Warranties::STATUS_CANCELLED
			)
		);

		/*
		 * Claims counted by when they were decided, not when they were filed.
		 *
		 * A claim filed in March and settled in May costs the shop in May. Using
		 * the filing date would put the cost in a period whose premium never
		 * paid for it.
		 */
		$decided = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT
					COUNT(*) AS decided,
					SUM( CASE WHEN outcome_cost IS NOT NULL THEN 1 ELSE 0 END ) AS costed,
					COALESCE( SUM( outcome_cost ), 0 ) AS cost
				 FROM {$claims}
				 WHERE outcome IS NOT NULL AND outcome != '' AND updated_at >= %s",
				$since
			)
		);

		$filed = (int) $wpdb->get_var(
			$wpdb->prepare( "SELECT COUNT(*) FROM {$claims} WHERE created_at >= %s", $since )
		);

		/*
		 * What the shop is still on the hook for.
		 *
		 * Not restricted to the period: cover sold three years ago that is still
		 * running is still a promise owed today, and a reserve that forgets it
		 * understates the liability.
		 */
		$outstanding = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT COUNT(*) AS policies, COALESCE( SUM( purchase_price ), 0 ) AS premium
				 FROM {$warranties}
				 WHERE status = %s",
				Warranties::STATUS_ACTIVE
			)
		);

		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery

		$premium  = (float) ( $sold->premium ?? 0 );
		$cost     = (float) ( $decided->cost ?? 0 );
		$policies = (int) ( $sold->policies ?? 0 );
		$settled  = (int) ( $decided->decided ?? 0 );
		$costed   = (int) ( $decided->costed ?? 0 );

		return array(
			'days'              => $days,
			'policies_sold'     => $policies,
			'premium'           => round( $premium, 2 ),
			'claims_filed'      => $filed,
			'claims_decided'    => $settled,
			'claims_cost'       => round( $cost, 2 ),

			/*
			 * Null rather than zero where there is nothing to divide by. A shop
			 * that sold no cover this month has no loss ratio; reporting 0%
			 * would read as "no claims cost us anything", which is a different
			 * and much happier statement.
			 */
			'loss_ratio'        => $premium > 0 ? round( ( $cost / $premium ) * 100, 1 ) : null,
			'claim_rate'        => $policies > 0 ? round( ( $filed / $policies ) * 100, 1 ) : null,

			/*
			 * How much of the ratio is actually evidenced.
			 *
			 * A shop that records the cost of two claims in ten has a ratio
			 * built from a fifth of its decisions. The number is not wrong, but
			 * it is not what the reader will assume it is, so it travels with
			 * the figure rather than being left for somebody to work out.
			 */
			'cost_coverage'     => $settled > 0 ? round( ( $costed / $settled ) * 100, 1 ) : null,
			'claims_costed'     => $costed,
			'outstanding_count' => (int) ( $outstanding->policies ?? 0 ),
			'outstanding_value' => round( (float) ( $outstanding->premium ?? 0 ), 2 ),
			'attach_rate'       => self::attach_rate( $since ),
			'currency'          => get_woocommerce_currency(),
		);
	}

	/**
	 * How often an order includes cover.
	 *
	 * Orders that carried a warranty, against orders the shop completed. The
	 * denominator is completed orders rather than all of them, because that is
	 * the point at which a warranty is written — an abandoned or failed order
	 * never had the chance to attach one.
	 *
	 * @param string $since MySQL datetime to count from.
	 *
	 * @return float|null Percentage, or null when there were no orders to attach to.
	 */
	private static function attach_rate( string $since ): ?float {
		global $wpdb;

		$warranties = $wpdb->prefix . 'warranty_cart_warranties';

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery
		$with_cover = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT( DISTINCT order_id ) FROM {$warranties} WHERE created_at >= %s AND order_id > 0",
				$since
			)
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery

		if ( ! function_exists( 'wc_get_orders' ) ) {
			return null;
		}

		/*
		 * Read through wc_get_orders(), never straight from posts: an HPOS store
		 * keeps its orders in tables of its own, and a direct query would report
		 * zero on exactly the stores most likely to be watching this number.
		 */
		$orders = wc_get_orders(
			array(
				'status'       => array( 'completed' ),
				'date_created' => '>=' . $since,
				'limit'        => -1,
				'return'       => 'ids',
			)
		);

		$completed = is_array( $orders ) ? count( $orders ) : 0;

		if ( $completed < 1 ) {
			return null;
		}

		return round( ( $with_cover / $completed ) * 100, 1 );
	}
}
