<?php
/**
 * Data-access layer for work-order records.
 *
 * Work orders are the tracked jobs that link a customer and a vehicle through a
 * locked six-status lifecycle. 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 identifier is the
 * trusted, prefix-built table name.
 *
 * @package WrenchPress
 */

defined( 'ABSPATH' ) || exit;

/**
 * Class WrenchPress_Work_Orders_Repository
 */
class WrenchPress_Work_Orders_Repository {

	/**
	 * The locked six-status work-order lifecycle, mapped slug => display label.
	 *
	 * Single source of truth for every status: transition_status() validates the
	 * target against this map, list_by_status()/count_by_status() whitelist the
	 * filter against it, and counts_by_status() seeds its zero-fill from these
	 * keys. Transitions are
	 * free any-to-any (D-11); this enum only constrains which slugs are valid.
	 *
	 * @return array<string, string> Status slug => display label.
	 */
	public static function statuses() {
		return array(
			'new'         => 'New / Lead',
			'estimate'    => 'Estimate',
			'approved'    => 'Approved',
			'in_progress' => 'In Progress',
			'ready'       => 'Ready',
			'closed'      => 'Closed',
		);
	}

	/**
	 * Columns allowed in ORDER BY, mapped from a request key to a safe SQL
	 * fragment. Never interpolate raw orderby input; map through this list.
	 *
	 * @return array<string, string> Request key => safe ORDER BY fragment.
	 */
	public static function sortable_map() {
		return array(
			'created_at' => 'created_at',
			'status'     => 'status',
		);
	}

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

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

	/**
	 * Open a new work order linking a customer and a vehicle.
	 *
	 * Both customer_id and vehicle_id must be non-zero; an orphan work order with
	 * no vehicle or no customer is rejected (D-09). The work-order number is the
	 * auto-increment row id (D-10). tax_rate is bound as a string because it is a
	 * DECIMAL column; binding it as %f would force a float round-trip.
	 *
	 * @param array $data customer_id, vehicle_id, optional status and tax_rate.
	 * @return int|false New work-order id, or false on a missing link / failure.
	 */
	public static function create( 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;
		}

		$status = isset( $data['status'] ) ? (string) $data['status'] : 'new';
		if ( ! array_key_exists( $status, self::statuses() ) ) {
			$status = 'new';
		}

		$tax_rate = isset( $data['tax_rate'] ) ? (string) $data['tax_rate'] : '0';

		$now = current_time( 'mysql' );

		$row = array(
			'customer_id' => $customer_id,
			'vehicle_id'  => $vehicle_id,
			'status'      => $status,
			'tax_rate'    => $tax_rate,
			'created_at'  => $now,
			'updated_at'  => $now,
		);

		$formats = array(
			'%d', // customer_id.
			'%d', // vehicle_id.
			'%s', // status.
			'%s', // tax_rate (DECIMAL passed as string).
			'%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( self::table(), $row, $formats );

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

		return (int) $wpdb->insert_id;
	}

	/**
	 * Fetch a single work order by id.
	 *
	 * @param int $id Work-order 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;
	}

	/**
	 * Update a work order's tax_rate (and updated_at).
	 *
	 * Status changes go through transition_status(); this updates only the
	 * pricing field. tax_rate is bound as a string (DECIMAL column).
	 *
	 * @param int   $id   Work-order id.
	 * @param array $data tax_rate.
	 * @return bool True on success.
	 */
	public static function update( $id, array $data ) {
		global $wpdb;

		$id = absint( $id );

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

		$row = array(
			'tax_rate'   => isset( $data['tax_rate'] ) ? (string) $data['tax_rate'] : '0',
			'updated_at' => current_time( 'mysql' ),
		);

		// 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(), $row, array( 'id' => $id ), array( '%s', '%s' ), array( '%d' ) );

		return false !== $result;
	}

	/**
	 * Transition a work order to a new status.
	 *
	 * Validates the target against statuses() (free any-to-any per D-11: no
	 * sequence check) and updates status + updated_at. This method writes no
	 * audit event; the caller logs the change through the events repository so a
	 * note can accompany a status move when needed.
	 *
	 * @param int    $id     Work-order id.
	 * @param string $status Target status slug.
	 * @return bool True on success, false on invalid id/status or DB failure.
	 */
	public static function transition_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;
	}

	/**
	 * Count work orders grouped by status, zero-filled for every known slug.
	 *
	 * Runs a single grouped query (status is column data, not request input, so
	 * no binding is needed) and folds the result onto a zero-seeded map of every
	 * status() slug, plus an 'all' total. Unknown stored statuses still count
	 * toward 'all' but are not added as new buckets.
	 *
	 * @return array<string, int> Map of 'all' and each status slug to its count.
	 */
	public static function counts_by_status() {
		global $wpdb;

		$counts = array( 'all' => 0 );

		foreach ( array_keys( self::statuses() ) as $slug ) {
			$counts[ $slug ] = 0;
		}

		$table = self::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 status, COUNT(*) AS c FROM %i GROUP BY status', $table )
		);

		if ( is_array( $rows ) ) {
			foreach ( $rows as $row ) {
				$count          = (int) $row->c;
				$counts['all'] += $count;
				if ( isset( $counts[ $row->status ] ) ) {
					$counts[ $row->status ] = $count;
				}
			}
		}

		return $counts;
	}

	/**
	 * List work orders in a given status, newest first by default.
	 *
	 * @param string $status Status slug to filter by. Default 'new'.
	 * @param array  $args {
	 *     Optional query arguments.
	 *
	 *     @type string $orderby  Request orderby key: 'created_at' or 'status'.
	 *     @type string $order    ASC or DESC. Default DESC.
	 *     @type int    $per_page Rows per page. Default 20.
	 *     @type int    $offset   Row offset. Default 0.
	 * }
	 * @return array List of row objects.
	 */
	public static function list_by_status( $status = 'new', 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;
		$orderby  = isset( $args['orderby'] ) ? (string) $args['orderby'] : 'created_at';
		$order    = isset( $args['order'] ) ? (string) $args['order'] : 'DESC';

		$status = (string) $status;
		if ( ! array_key_exists( $status, self::statuses() ) ) {
			$status = 'new';
		}

		$table = self::table();
		$desc  = ( 'DESC' === strtoupper( $order ) );

		// Whitelisted sort columns, passed to prepare() as %i identifiers.
		if ( 'status' === $orderby ) {
			$col1 = 'status';
			$col2 = 'id';
		} else {
			$col1 = 'created_at';
			$col2 = 'id';
		}

		if ( $desc ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table, intentionally not object-cached.
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					'SELECT * FROM %i WHERE status = %s ORDER BY %i DESC, %i DESC LIMIT %d OFFSET %d',
					$table,
					$status,
					$col1,
					$col2,
					$per_page,
					$offset
				)
			);
		} else {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table, intentionally not object-cached.
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					'SELECT * FROM %i WHERE status = %s ORDER BY %i ASC, %i ASC LIMIT %d OFFSET %d',
					$table,
					$status,
					$col1,
					$col2,
					$per_page,
					$offset
				)
			);
		}

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

	/**
	 * Count work orders in a given status.
	 *
	 * @param string $status Status slug to filter by. Default 'new'.
	 * @param array  $args   Reserved for parity with list_by_status(); unused.
	 * @return int Count of work orders in the status.
	 */
	public static function count_by_status( $status = 'new', array $args = array() ) {
		global $wpdb;

		unset( $args );

		$status = (string) $status;
		if ( ! array_key_exists( $status, self::statuses() ) ) {
			$status = 'new';
		}

		$table = self::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 WHERE status = %s', $table, $status )
		);
	}

	/**
	 * List work orders across every status, newest first, with SQL pagination.
	 *
	 * Powers the "All" status view. The query spans all statuses, so there is no
	 * status filter to bind; pagination and ordering are pushed into a single
	 * prepared SELECT rather than unioning per-status fetches and paginating in
	 * PHP. The orderby key is whitelisted to a fixed set of columns passed to
	 * prepare() as %i identifiers; the table, sort columns, and limit/offset are
	 * all bound via placeholders, so no PHP value is interpolated into the SQL.
	 *
	 * @param array $args {
	 *     Optional query arguments.
	 *
	 *     @type string $orderby  Request orderby key: 'created_at' or 'status'.
	 *     @type string $order    ASC or DESC. Default DESC.
	 *     @type int    $per_page Rows per page. Default 20.
	 *     @type int    $offset   Row offset. Default 0.
	 * }
	 * @return array List of row objects.
	 */
	public static function list_all( 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;
		$orderby  = isset( $args['orderby'] ) ? (string) $args['orderby'] : 'created_at';
		$order    = isset( $args['order'] ) ? (string) $args['order'] : 'DESC';

		$table = self::table();
		$desc  = ( 'DESC' === strtoupper( $order ) );

		// Whitelisted sort columns, passed to prepare() as %i identifiers.
		if ( 'status' === $orderby ) {
			$col1 = 'status';
			$col2 = 'id';
		} else {
			$col1 = 'created_at';
			$col2 = 'id';
		}

		if ( $desc ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table, intentionally not object-cached.
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					'SELECT * FROM %i ORDER BY %i DESC, %i DESC LIMIT %d OFFSET %d',
					$table,
					$col1,
					$col2,
					$per_page,
					$offset
				)
			);
		} else {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table, intentionally not object-cached.
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					'SELECT * FROM %i ORDER BY %i ASC, %i ASC LIMIT %d OFFSET %d',
					$table,
					$col1,
					$col2,
					$per_page,
					$offset
				)
			);
		}

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

	/**
	 * Count every work order regardless of status, for the "All" view total.
	 *
	 * @return int Total work-order count.
	 */
	public static function count_all() {
		global $wpdb;

		$table = self::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', $table )
		);
	}

	/**
	 * Whether a vehicle has any open (non-closed) work order.
	 *
	 * Used as the archive guard: a vehicle with an open job must not be archived
	 * out from under it. The vehicle id is bound via prepare.
	 *
	 * @param int $vehicle_id Vehicle id.
	 * @return bool True when at least one non-closed work order exists.
	 */
	public static function has_open( $vehicle_id ) {
		global $wpdb;

		$vehicle_id = absint( $vehicle_id );

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

		$table = self::table();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table, intentionally not object-cached.
		$count = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM %i WHERE vehicle_id = %d AND status <> 'closed'",
				$table,
				$vehicle_id
			)
		);

		return $count > 0;
	}

	/**
	 * List every work order for one vehicle, newest first, across all statuses.
	 *
	 * Powers the per-vehicle service history (VEH-02 / D-16). The vehicle id is
	 * bound via prepare.
	 *
	 * @param int $vehicle_id Vehicle id.
	 * @return array List of row objects, newest first.
	 */
	public static function list_for_vehicle( $vehicle_id ) {
		global $wpdb;

		$vehicle_id = absint( $vehicle_id );

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

		$table = self::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 * FROM %i WHERE vehicle_id = %d ORDER BY created_at DESC',
				$table,
				$vehicle_id
			)
		);

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

	/**
	 * List every work order for one customer, newest first, across all statuses.
	 *
	 * Powers the customer 360 recent-work-orders cross-links (D-06). The customer
	 * id is bound via prepare.
	 *
	 * @param int $customer_id Customer id.
	 * @param int $limit       Maximum rows to return (0 = no limit). Default 0.
	 * @return array List of row objects, newest first.
	 */
	public static function list_for_customer( $customer_id, $limit = 0 ) {
		global $wpdb;

		$customer_id = absint( $customer_id );

		if ( 0 === $customer_id ) {
			return array();
		}

		$table = self::table();
		$limit = absint( $limit );

		if ( $limit > 0 ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table, intentionally not object-cached.
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					'SELECT * FROM %i WHERE customer_id = %d ORDER BY created_at DESC LIMIT %d',
					$table,
					$customer_id,
					$limit
				)
			);
		} else {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table, intentionally not object-cached.
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					'SELECT * FROM %i WHERE customer_id = %d ORDER BY created_at DESC',
					$table,
					$customer_id
				)
			);
		}

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