<?php
/**
 * Data-access layer for appointment records.
 *
 * Appointments are timed slots that link a customer and a vehicle over a
 * start/end datetime window, with a lifecycle status and an optional work-order
 * link. 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, always bound as the %i identifier placeholder.
 *
 * @package WrenchPress
 */

defined( 'ABSPATH' ) || exit;

/**
 * Class WrenchPress_Appointments_Repository
 */
class WrenchPress_Appointments_Repository {

	/**
	 * The four appointment lifecycle statuses, mapped slug => display label.
	 *
	 * Single source of truth for every status: create()/update() whitelist the
	 * incoming status against this map, and the calendar reuses the same slugs as
	 * CSS classes (so the slug equals the stored DB value equals the badge class,
	 * including the hyphen in no-show). Statuses cancelled and no-show are the two
	 * the overlap check excludes from a conflict (D-08).
	 *
	 * @return array<string, string> Status slug => display label.
	 */
	public static function statuses() {
		return array(
			'scheduled' => 'Scheduled',
			'completed' => 'Completed',
			'cancelled' => 'Cancelled',
			'no-show'   => 'No-show',
		);
	}

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

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

	/**
	 * Create an appointment linking a customer and a vehicle over a time window.
	 *
	 * Both customer_id and vehicle_id must be non-zero; an orphan appointment
	 * with no vehicle or no customer is rejected (D-06), mirroring the work-order
	 * both-ids rule. The status is whitelisted against statuses() and defaults to
	 * scheduled; notes are sanitized on input; work_order_id is optional and
	 * absint-coerced, defaulting to 0 (no linked work order).
	 *
	 * @param array $data customer_id, vehicle_id, start_at, end_at, optional
	 *                    status, notes, work_order_id.
	 * @return int|false New appointment 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'] : 'scheduled';
		if ( ! array_key_exists( $status, self::statuses() ) ) {
			$status = 'scheduled';
		}

		$start_at      = isset( $data['start_at'] ) ? (string) $data['start_at'] : '';
		$end_at        = isset( $data['end_at'] ) ? (string) $data['end_at'] : '';
		$notes         = isset( $data['notes'] ) ? sanitize_textarea_field( $data['notes'] ) : '';
		$work_order_id = isset( $data['work_order_id'] ) ? absint( $data['work_order_id'] ) : 0;

		$now = current_time( 'mysql' );

		$row = array(
			'customer_id'   => $customer_id,
			'vehicle_id'    => $vehicle_id,
			'start_at'      => $start_at,
			'end_at'        => $end_at,
			'status'        => $status,
			'notes'         => $notes,
			'work_order_id' => $work_order_id,
			'created_at'    => $now,
			'updated_at'    => $now,
		);

		$formats = array(
			'%d', // customer_id.
			'%d', // vehicle_id.
			'%s', // start_at.
			'%s', // end_at.
			'%s', // status.
			'%s', // notes.
			'%d', // work_order_id.
			'%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 appointment by id.
	 *
	 * @param int $id Appointment 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 an appointment's time window, status, and notes.
	 *
	 * The status is whitelisted against statuses(); notes are sanitized on input;
	 * work_order_id is absint-coerced. Callers reschedule by passing new
	 * start_at/end_at values. updated_at is always stamped.
	 *
	 * @param int   $id   Appointment id.
	 * @param array $data start_at, end_at, status, notes, optional work_order_id.
	 * @return bool True on success.
	 */
	public static function update( $id, array $data ) {
		global $wpdb;

		$id = absint( $id );

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

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

		$row = array(
			'start_at'      => isset( $data['start_at'] ) ? (string) $data['start_at'] : '',
			'end_at'        => isset( $data['end_at'] ) ? (string) $data['end_at'] : '',
			'status'        => $status,
			'notes'         => isset( $data['notes'] ) ? sanitize_textarea_field( $data['notes'] ) : '',
			'work_order_id' => isset( $data['work_order_id'] ) ? absint( $data['work_order_id'] ) : 0,
			'updated_at'    => current_time( 'mysql' ),
		);

		$formats = array(
			'%s', // start_at.
			'%s', // end_at.
			'%s', // status.
			'%s', // notes.
			'%d', // work_order_id.
			'%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.
		$result = $wpdb->update( self::table(), $row, array( 'id' => $id ), $formats, array( '%d' ) );

		return false !== $result;
	}

	/**
	 * Delete an appointment by id.
	 *
	 * @param int $id Appointment id.
	 * @return bool True on success.
	 */
	public static function delete( $id ) {
		global $wpdb;

		$id = absint( $id );

		if ( 0 === $id ) {
			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->delete( self::table(), array( 'id' => $id ), array( '%d' ) );

		return false !== $result;
	}

	/**
	 * Link an appointment to a work order (sets work_order_id and updated_at).
	 *
	 * Used by the "Create work order" action once the reused work-order create
	 * flow has produced the new work-order id (D-07).
	 *
	 * @param int $id            Appointment id.
	 * @param int $work_order_id Work-order id to link.
	 * @return bool True on success.
	 */
	public static function set_work_order( $id, $work_order_id ) {
		global $wpdb;

		$id = absint( $id );

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

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

		return false !== $result;
	}

	/**
	 * Find appointments that overlap a proposed time window (warn-only, APPT-03).
	 *
	 * Uses the half-open interval overlap predicate start_at < :end AND
	 * end_at > :start so touching edges (an appointment ending exactly when the
	 * new one starts, or starting exactly when the new one ends) do NOT count as
	 * a conflict. Rows with status cancelled or no-show are excluded (D-08), as
	 * is the row being edited ($exclude_id). The bind order is table, exclude_id,
	 * then the NEW end (compared to existing start_at) then the NEW start
	 * (compared to existing end_at); getting that order wrong silently inverts
	 * the predicate (RESEARCH pitfall 2).
	 *
	 * @param string $start_at    Proposed window start (Y-m-d H:i:s).
	 * @param string $end_at      Proposed window end (Y-m-d H:i:s).
	 * @param int    $exclude_id  Appointment id to exclude (the row being edited).
	 * @return array List of conflicting row objects, ordered by start_at ASC.
	 */
	public static function find_conflicts( $start_at, $end_at, $exclude_id = 0 ) {
		global $wpdb;

		$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 id <> %d AND status NOT IN ('cancelled','no-show') AND start_at < %s AND end_at > %s ORDER BY start_at ASC",
				$table,
				absint( $exclude_id ),
				$end_at,
				$start_at
			)
		);

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

	/**
	 * List appointments overlapping a datetime range, ordered by start_at ASC.
	 *
	 * Powers the calendar's per-view fetch. Uses the same half-open overlap
	 * predicate as find_conflicts() but keeps every status (cancelled/no-show
	 * appointments still render on the calendar). Bind order is table, then the
	 * range end (compared to start_at) then the range start (compared to end_at).
	 *
	 * @param string $start_at Range start (Y-m-d H:i:s).
	 * @param string $end_at   Range end (Y-m-d H:i:s).
	 * @return array List of row objects, ordered by start_at ASC.
	 */
	public static function list_in_range( $start_at, $end_at ) {
		global $wpdb;

		$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 start_at < %s AND end_at > %s ORDER BY start_at ASC',
				$table,
				$end_at,
				$start_at
			)
		);

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

	/**
	 * List every appointment overlapping a single calendar day.
	 *
	 * Powers the dashboard "Today's appointments" snapshot. Delegates to
	 * list_in_range() with the day's midnight-to-last-second bounds.
	 *
	 * @param string $ymd Calendar day as Y-m-d.
	 * @return array List of row objects, ordered by start_at ASC.
	 */
	public static function list_for_day( $ymd ) {
		return self::list_in_range( "$ymd 00:00:00", "$ymd 23:59:59" );
	}
}
