<?php
/**
 * Data-access layer for service-reminder records.
 *
 * Reminders are follow-up flags that link a customer and a vehicle to a future
 * service, triggered by a due date, a due mileage, or both. At most one pending
 * reminder exists per vehicle: creating a new one retires the vehicle's prior
 * pending reminder (supersede). All reads and writes go through this repository
 * using $wpdb with prepared statements and explicit format arrays; no caller
 * interpolates user input into SQL. The only interpolated identifiers are the
 * trusted, prefix-built table names, always bound as the %i placeholder.
 *
 * Mileage is stored in a single canonical unit (kilometers). Callers convert
 * display values to canonical at the form boundary via mileage_to_canonical()
 * and back via mileage_to_display(); the repository never converts inside a query.
 *
 * @package WrenchPress
 */

defined( 'ABSPATH' ) || exit;

/**
 * Class WrenchPress_Reminders_Repository
 */
class WrenchPress_Reminders_Repository {

	/**
	 * The reminder lifecycle statuses, mapped slug => display label.
	 *
	 * Single source of truth for every status: create_superseding() writes only
	 * 'pending'/'superseded', mark_status() validates its target against these
	 * keys, and the worklist reuses the same slugs as CSS badge classes (so the
	 * slug equals the stored DB value equals the badge class). Only 'pending'
	 * rows appear in the due worklist; the other three are terminal states.
	 *
	 * @return array<string, string> Status slug => display label.
	 */
	public static function statuses() {
		return array(
			'pending'    => 'Pending',
			'handled'    => 'Handled',
			'dismissed'  => 'Dismissed',
			'superseded' => 'Superseded',
		);
	}

	/**
	 * Fully qualified reminders table name.
	 *
	 * @return string
	 */
	protected static function table() {
		global $wpdb;

		return $wpdb->prefix . 'wrenchpress_reminders';
	}

	/**
	 * Fully qualified vehicles table name (joined for current-mileage compares).
	 *
	 * @return string
	 */
	protected static function vehicles_table() {
		global $wpdb;

		return $wpdb->prefix . 'wrenchpress_vehicles';
	}

	/**
	 * The due-soon horizon date: the given day plus 30 calendar days.
	 *
	 * Computed in the WordPress site timezone from a plain Y-m-d string so the
	 * arithmetic stays site-local with no UTC round-trip (Pitfall 2), avoiding
	 * both SQL date functions and current_time( 'timestamp' ).
	 *
	 * @param string $today Base day as Y-m-d.
	 * @return string Horizon day as Y-m-d.
	 */
	protected static function horizon_date( $today ) {
		$base = new DateTimeImmutable( $today, wp_timezone() );

		return $base->modify( '+30 days' )->format( 'Y-m-d' );
	}

	/**
	 * Fetch a single reminder by id.
	 *
	 * @param int $id Reminder id.
	 * @return object|null Row object, or null when not found.
	 */
	public static function get( $id ) {
		global $wpdb;

		$id = absint( $id );

		if ( 0 === $id ) {
			return null;
		}

		$table = self::table();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table, intentionally not object-cached.
		$row = $wpdb->get_row(
			$wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', $table, $id )
		);

		return $row ? $row : null;
	}

	/**
	 * Fetch the current pending reminder for a vehicle, if any.
	 *
	 * The one-pending-per-vehicle invariant is enforced by create_superseding(),
	 * but this read defensively takes the newest row (ORDER BY id DESC LIMIT 1)
	 * so a stray duplicate from a supersede race never doubles a worklist entry.
	 *
	 * @param int $vehicle_id Vehicle id.
	 * @return object|null Newest pending reminder row, or null when none.
	 */
	public static function get_pending_for_vehicle( $vehicle_id ) {
		global $wpdb;

		$vehicle_id = absint( $vehicle_id );

		if ( 0 === $vehicle_id ) {
			return null;
		}

		$table = self::table();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table, intentionally not object-cached.
		$row = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM %i WHERE vehicle_id = %d AND status = 'pending' ORDER BY id DESC LIMIT 1",
				$table,
				$vehicle_id
			)
		);

		return $row ? $row : null;
	}

	/**
	 * Create a new pending reminder, superseding the vehicle's prior pending one.
	 *
	 * Both customer_id and vehicle_id must be non-zero; an orphan reminder with
	 * no vehicle or no customer is rejected. The UPDATE that retires the existing
	 * pending reminder runs BEFORE the INSERT (Pitfall 4) so the one-pending-per-
	 * vehicle invariant (D-08) holds. Mileage is stored canonical; the caller has
	 * already converted display -> canonical, so no conversion happens here
	 * (Pitfall 5). An empty due_date is stored as the '0000-00-00' sentinel and a
	 * missing due_mileage as 0; the due query guards both sentinels (Pitfall 1).
	 *
	 * @param array $data customer_id, vehicle_id, optional work_order_id,
	 *                    due_date (Y-m-d or ''), due_mileage (canonical int).
	 * @return int|false New reminder id, or false on a missing link / failure.
	 */
	public static function create_superseding( array $data ) {
		global $wpdb;

		$customer_id = isset( $data['customer_id'] ) ? absint( $data['customer_id'] ) : 0;
		$vehicle_id  = isset( $data['vehicle_id'] ) ? absint( $data['vehicle_id'] ) : 0;

		if ( 0 === $customer_id || 0 === $vehicle_id ) {
			return false;
		}

		$table = self::table();
		$now   = current_time( 'mysql' );

		// 1) Retire any existing pending reminder for this vehicle (D-08).
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table write via the $wpdb array API, intentionally not object-cached.
		$wpdb->update(
			$table,
			array(
				'status'     => 'superseded',
				'updated_at' => $now,
			),
			array(
				'vehicle_id' => $vehicle_id,
				'status'     => 'pending',
			),
			array( '%s', '%s' ),
			array( '%d', '%s' )
		);

		// 2) Insert the new pending reminder (canonical mileage already converted).
		$due_date = ( isset( $data['due_date'] ) && '' !== $data['due_date'] )
			? (string) $data['due_date']
			: '0000-00-00';

		$row = array(
			'customer_id'   => $customer_id,
			'vehicle_id'    => $vehicle_id,
			'work_order_id' => isset( $data['work_order_id'] ) ? absint( $data['work_order_id'] ) : 0,
			'due_date'      => $due_date,
			'due_mileage'   => isset( $data['due_mileage'] ) ? absint( $data['due_mileage'] ) : 0,
			'status'        => 'pending',
			'created_at'    => $now,
			'updated_at'    => $now,
		);

		$formats = array(
			'%d', // customer_id.
			'%d', // vehicle_id.
			'%d', // work_order_id.
			'%s', // due_date.
			'%d', // due_mileage.
			'%s', // status.
			'%s', // created_at.
			'%s', // updated_at.
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table write via the $wpdb array API, intentionally not object-cached.
		$inserted = $wpdb->insert( $table, $row, $formats );

		if ( false === $inserted ) {
			return false;
		}

		return (int) $wpdb->insert_id;
	}

	/**
	 * Move a reminder to a terminal status (handled, dismissed, superseded).
	 *
	 * Validates the target against statuses() (returns false on an unknown slug)
	 * and stamps updated_at. Mirrors the work-order transition_status() shape
	 * (REMD-03).
	 *
	 * @param int    $id     Reminder id.
	 * @param string $status Target status slug.
	 * @return bool True on success, false on invalid id/status or DB failure.
	 */
	public static function mark_status( $id, $status ) {
		global $wpdb;

		$id     = absint( $id );
		$status = (string) $status;

		if ( 0 === $id || ! array_key_exists( $status, self::statuses() ) ) {
			return false;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table write via the $wpdb array API, intentionally not object-cached.
		$result = $wpdb->update(
			self::table(),
			array(
				'status'     => $status,
				'updated_at' => current_time( 'mysql' ),
			),
			array( 'id' => $id ),
			array( '%s', '%s' ),
			array( '%d' )
		);

		return false !== $result;
	}

	/**
	 * List pending reminders in a due bucket, joining vehicles for live mileage.
	 *
	 * Three buckets, whitelisted before the code path is chosen (an unknown value
	 * falls through to 'all'):
	 *
	 * - overdue:  the trigger is reached now. Date-reached OR mileage-reached,
	 *             with both arms sentinel-guarded (Pitfall 1) so a date-only
	 *             reminder never reads as mileage-due and vice versa.
	 * - due_soon: date-only, not yet due but within the next 30 days (Pitfall 3;
	 *             mileage cannot be projected, so it contributes to overdue only).
	 * - all:      every pending reminder, unset-due last.
	 *
	 * today/horizon are computed in PHP site-local time and bound as %s; SQL date
	 * functions (CURDATE/NOW) are never used (Pitfall 2). Ordering is soonest-
	 * first with unset due dates last (D-05).
	 *
	 * @param string $bucket One of 'overdue', 'due_soon', 'all'. Default 'overdue'.
	 * @param array  $args {
	 *     Optional query arguments.
	 *
	 *     @type int $per_page Rows per page. Default 20.
	 *     @type int $offset   Row offset. Default 0.
	 * }
	 * @return object[] List of reminder row objects.
	 */
	public static function list_due( $bucket = 'overdue', array $args = array() ) {
		global $wpdb;

		$bucket = (string) $bucket;
		if ( ! in_array( $bucket, array( 'overdue', 'due_soon', 'all' ), true ) ) {
			$bucket = 'all';
		}

		$per_page = isset( $args['per_page'] ) ? max( 1, absint( $args['per_page'] ) ) : 20;
		$offset   = isset( $args['offset'] ) ? max( 0, absint( $args['offset'] ) ) : 0;

		$reminders = self::table();
		$vehicles  = self::vehicles_table();

		$today   = current_time( 'Y-m-d' );
		$horizon = self::horizon_date( $today );

		if ( 'due_soon' === $bucket ) {
			// Date-only horizon (Pitfall 3): not yet due, within 30 days.
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table, intentionally not object-cached.
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT r.* FROM %i r JOIN %i v ON v.id = r.vehicle_id
					 WHERE r.status = 'pending'
					   AND r.due_date <> '0000-00-00' AND r.due_date > %s AND r.due_date <= %s
					 ORDER BY r.due_date ASC, r.id ASC LIMIT %d OFFSET %d",
					$reminders,
					$vehicles,
					$today,
					$horizon,
					$per_page,
					$offset
				)
			);
		} elseif ( 'all' === $bucket ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table, intentionally not object-cached.
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT r.* FROM %i r JOIN %i v ON v.id = r.vehicle_id
					 WHERE r.status = 'pending'
					 ORDER BY (r.due_date = '0000-00-00') ASC, r.due_date ASC, r.id ASC
					 LIMIT %d OFFSET %d",
					$reminders,
					$vehicles,
					$per_page,
					$offset
				)
			);
		} else {
			// overdue: date reached OR mileage reached; both arms sentinel-guarded (Pitfall 1).
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table, intentionally not object-cached.
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT r.* FROM %i r JOIN %i v ON v.id = r.vehicle_id
					 WHERE r.status = 'pending'
					   AND ( ( r.due_date <> '0000-00-00' AND r.due_date <= %s )
					      OR ( r.due_mileage > 0 AND v.mileage >= r.due_mileage ) )
					 ORDER BY (r.due_date = '0000-00-00') ASC, r.due_date ASC, r.id ASC
					 LIMIT %d OFFSET %d",
					$reminders,
					$vehicles,
					$today,
					$per_page,
					$offset
				)
			);
		}

		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * Count pending reminders in a due bucket.
	 *
	 * Mirrors list_due()'s bucket whitelist and sentinel-guarded predicates so
	 * the tab counts and the listed rows always agree.
	 *
	 * @param string $bucket One of 'overdue', 'due_soon', 'all'. Default 'overdue'.
	 * @return int Count of pending reminders in the bucket.
	 */
	public static function count_due( $bucket = 'overdue' ) {
		global $wpdb;

		$bucket = (string) $bucket;
		if ( ! in_array( $bucket, array( 'overdue', 'due_soon', 'all' ), true ) ) {
			$bucket = 'all';
		}

		$reminders = self::table();
		$vehicles  = self::vehicles_table();

		$today   = current_time( 'Y-m-d' );
		$horizon = self::horizon_date( $today );

		if ( 'due_soon' === $bucket ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table, intentionally not object-cached.
			return (int) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT COUNT(*) FROM %i r JOIN %i v ON v.id = r.vehicle_id
					 WHERE r.status = 'pending'
					   AND r.due_date <> '0000-00-00' AND r.due_date > %s AND r.due_date <= %s",
					$reminders,
					$vehicles,
					$today,
					$horizon
				)
			);
		} elseif ( 'all' === $bucket ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table, intentionally not object-cached.
			return (int) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT COUNT(*) FROM %i r JOIN %i v ON v.id = r.vehicle_id
					 WHERE r.status = 'pending'",
					$reminders,
					$vehicles
				)
			);
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table, intentionally not object-cached.
		return (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM %i r JOIN %i v ON v.id = r.vehicle_id
				 WHERE r.status = 'pending'
				   AND ( ( r.due_date <> '0000-00-00' AND r.due_date <= %s )
				      OR ( r.due_mileage > 0 AND v.mileage >= r.due_mileage ) )",
				$reminders,
				$vehicles,
				$today
			)
		);
	}

	/**
	 * List terminal (non-pending) reminders for the History view.
	 *
	 * Surfaces the retained rows a staff member has already actioned: handled,
	 * dismissed, or superseded (D-07). Joins vehicles for live mileage display and
	 * orders most-recently-actioned first (updated_at DESC) so the newest history
	 * lands on top. Never shows pending rows (those live in the due buckets).
	 *
	 * @param array $args {
	 *     Optional query arguments.
	 *
	 *     @type int $per_page Rows per page. Default 20.
	 *     @type int $offset   Row offset. Default 0.
	 * }
	 * @return object[] List of reminder row objects.
	 */
	public static function list_history( array $args = array() ) {
		global $wpdb;

		$per_page = isset( $args['per_page'] ) ? max( 1, absint( $args['per_page'] ) ) : 20;
		$offset   = isset( $args['offset'] ) ? max( 0, absint( $args['offset'] ) ) : 0;

		$reminders = self::table();
		$vehicles  = self::vehicles_table();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table, intentionally not object-cached.
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT r.* FROM %i r JOIN %i v ON v.id = r.vehicle_id
				 WHERE r.status IN ( 'handled', 'dismissed', 'superseded' )
				 ORDER BY r.updated_at DESC, r.id DESC LIMIT %d OFFSET %d",
				$reminders,
				$vehicles,
				$per_page,
				$offset
			)
		);

		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * Count terminal (non-pending) reminders for the History view.
	 *
	 * Mirrors list_history()'s status predicate so the History tab count and the
	 * listed rows always agree.
	 *
	 * @return int Count of handled/dismissed/superseded reminders.
	 */
	public static function count_history() {
		global $wpdb;

		$reminders = self::table();
		$vehicles  = self::vehicles_table();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table, intentionally not object-cached.
		return (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM %i r JOIN %i v ON v.id = r.vehicle_id
				 WHERE r.status IN ( 'handled', 'dismissed', 'superseded' )",
				$reminders,
				$vehicles
			)
		);
	}

	/**
	 * Convert a display mileage to the canonical stored unit (kilometers).
	 *
	 * Canonical is always kilometers; a mi value is multiplied by 1.609344 and
	 * rounded to an integer. Convert once at the form boundary; the repository
	 * stores and compares canonical only (Pitfall 5).
	 *
	 * @param int|string $value Display mileage.
	 * @param string     $unit  Display unit: 'km' or 'mi'.
	 * @return int Canonical mileage in kilometers.
	 */
	public static function mileage_to_canonical( $value, $unit ) {
		$value = absint( $value );

		return 'mi' === $unit ? (int) round( $value * 1.609344 ) : $value;
	}

	/**
	 * Convert a canonical mileage (kilometers) to a display unit.
	 *
	 * The inverse of mileage_to_canonical(): a mi display divides by 1.609344 and
	 * rounds. Accept ~1-unit round-trip drift (negligible for service intervals).
	 * Convert once at the display boundary (Pitfall 5).
	 *
	 * @param int|string $canonical Canonical mileage in kilometers.
	 * @param string     $unit      Display unit: 'km' or 'mi'.
	 * @return int Display mileage in the requested unit.
	 */
	public static function mileage_to_display( $canonical, $unit ) {
		$canonical = absint( $canonical );

		return 'mi' === $unit ? (int) round( $canonical / 1.609344 ) : $canonical;
	}
}
