<?php
/**
 * Per-client visual look history: what a client actually had done, and what
 * they asked for, kept as a timeline instead of disappearing after checkout.
 *
 * 4.6.2. Before this, the only trace of "what does this client usually get"
 * was a single reference photo the CUSTOMER uploads when booking -- stored on
 * that one booking row, never shown again, gone the moment the appointment is
 * over. There was no way for staff to see "what did we actually do last
 * time" or leave a color/formula note for the next visit. Every mainstream
 * competitor evaluated in the August 2026 benchmark treats this as table
 * stakes; this file is the gap being closed.
 *
 * Deliberately NOT feature-module-gated and NOT Pro-only:
 *  - Not module-gated: this is exactly the pattern that shipped 4.6.0's
 *    boot-time fatal (a class required only when a module is enabled, then
 *    unconditionally assumed loaded elsewhere). This file is required
 *    unconditionally in yatoon-booking-system.php, mirroring
 *    class-yatoon-job-queue.php and class-yatoon-booking-lock.php, so there
 *    is no profile under which it can be "on" in one place and missing in
 *    another.
 *  - Not Pro-only: the client profile itself (notes, allergies, preferences,
 *    tags) is a Free-tier feature. A look history is a natural extension of
 *    that profile, not an advanced operations feature, so it ships in both
 *    editions.
 */
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- plugin-owned table, prefix + fixed suffix only.

class YATOON_Client_Looks {

	const SCHEMA_VERSION = '4.6.2';

	public static function init() {
		self::install_schema();
		// Priority 95: after finalize_design_reference() (90) has already
		// promoted the customer's uploaded reference photo from "pending" to
		// permanent, so design_image_url on the booking row is final.
		add_action( 'yatoon_booking_created', array( __CLASS__, 'capture_reference_from_booking' ), 95, 1 );
		add_action( 'wp_ajax_yatoon_save_client_look', array( __CLASS__, 'ajax_save_look' ) );
		add_action( 'wp_ajax_yatoon_delete_client_look', array( __CLASS__, 'ajax_delete_look' ) );
	}

	public static function install_schema() {
		if ( self::SCHEMA_VERSION === get_option( 'yatoon_client_looks_schema_version', '' ) ) {
			return;
		}
		global $wpdb;
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		$charset = $wpdb->get_charset_collate();
		dbDelta(
			"CREATE TABLE {$wpdb->prefix}yatoon_client_looks (
				id bigint(20) NOT NULL AUTO_INCREMENT,
				client_id bigint(20) NOT NULL,
				booking_id bigint(20) DEFAULT NULL,
				service_id bigint(20) DEFAULT NULL,
				staff_id bigint(20) DEFAULT NULL,
				visit_date datetime DEFAULT NULL,
				reference_image_url varchar(500) DEFAULT NULL,
				result_image_url varchar(500) DEFAULT NULL,
				notes text,
				created_by bigint(20) DEFAULT NULL,
				created_at datetime DEFAULT CURRENT_TIMESTAMP,
				updated_at datetime DEFAULT CURRENT_TIMESTAMP,
				PRIMARY KEY (id),
				KEY client_id (client_id, visit_date),
				KEY booking_id (booking_id)
			) {$charset};"
		);
		if ( YATOON_Schema_Verifier::tables_exist( array( 'yatoon_client_looks' ) ) ) {
			update_option( 'yatoon_client_looks_schema_version', self::SCHEMA_VERSION, false );
		}
	}

	/**
	 * Look up (never create) the yatoon_clients row for a phone/email pair.
	 *
	 * Every booking upserts a client row via
	 * YATOON_Notifications::upsert_client() as part of the same
	 * yatoon_booking_created dispatch this class also listens on, so by the
	 * time either hook runs the row exists for any booking that has contact
	 * info at all. This deliberately does not create one itself -- a client
	 * with neither phone nor email has no row to attach a look to, and that
	 * is the same rule the rest of the plugin (ajax_get_client, Commerce,
	 * card-on-file) already follows.
	 */
	private static function resolve_client_id( $phone, $email ) {
		global $wpdb;
		$table = $wpdb->prefix . 'yatoon_clients';
		if ( $phone ) {
			$id = (int) $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$table} WHERE customer_phone = %s LIMIT 1", $phone ) );
			if ( $id ) {
				return $id;
			}
		}
		if ( $email ) {
			return (int) $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$table} WHERE customer_email = %s LIMIT 1", $email ) );
		}
		return 0;
	}

	/**
	 * Auto-capture the customer's own reference photo as the first entry in
	 * their look timeline, so the history isn't empty until a staff member
	 * manually logs something.
	 *
	 * Reads the authoritative booking row from the database rather than
	 * trusting the shape of $booking_data: yatoon_booking_created fires from
	 * five different call sites (single, multi-service, Square, group) and
	 * only booking_id is guaranteed present in all five. Everything else --
	 * including whether design_image_url even made it into the in-memory
	 * array -- varies by call site.
	 *
	 * Known limitation: a multi-service booking writes one row per service,
	 * each with its own possible design_image_url, but this only fires once
	 * per booking group and only reads the primary row. A reference photo
	 * attached to a second or third service in the same visit is not
	 * captured here. Left as a follow-up rather than guessed at, since there
	 * is no way to exercise that path in this environment to confirm it.
	 */
	public static function capture_reference_from_booking( $booking_data ) {
		$booking_id = absint( is_array( $booking_data ) ? ( $booking_data['booking_id'] ?? 0 ) : 0 );
		if ( ! $booking_id ) {
			return;
		}
		global $wpdb;
		$booking = $wpdb->get_row( $wpdb->prepare(
			"SELECT id, customer_phone, customer_email, service_id, staff_id, start_time, design_image_url
			 FROM {$wpdb->prefix}yatoon_bookings WHERE id = %d",
			$booking_id
		) );
		if ( ! $booking || empty( $booking->design_image_url ) ) {
			return;
		}
		$client_id = self::resolve_client_id( $booking->customer_phone, $booking->customer_email );
		if ( ! $client_id ) {
			return;
		}
		// A booking already captured (e.g. a retry of this hook) should not
		// duplicate the timeline entry.
		$exists = (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT id FROM {$wpdb->prefix}yatoon_client_looks WHERE booking_id = %d LIMIT 1", $booking_id
		) );
		if ( $exists ) {
			return;
		}
		$wpdb->insert(
			$wpdb->prefix . 'yatoon_client_looks',
			array(
				'client_id'            => $client_id,
				'booking_id'           => $booking_id,
				'service_id'           => (int) $booking->service_id ?: null,
				'staff_id'             => (int) $booking->staff_id ?: null,
				'visit_date'           => $booking->start_time,
				'reference_image_url'  => esc_url_raw( $booking->design_image_url ),
				'created_at'           => current_time( 'mysql' ),
				'updated_at'           => current_time( 'mysql' ),
			)
		);
	}

	/**
	 * Every look entry for a client, most recent visit first.
	 *
	 * @param int $client_id
	 * @param int $limit
	 * @return array
	 */
	public static function get_looks_for_client( $client_id, $limit = 24 ) {
		$client_id = absint( $client_id );
		if ( ! $client_id ) {
			return array();
		}
		global $wpdb;
		return (array) $wpdb->get_results( $wpdb->prepare(
			"SELECT l.*, s.name AS service_name, st.name AS staff_name
			 FROM {$wpdb->prefix}yatoon_client_looks l
			 LEFT JOIN {$wpdb->prefix}yatoon_services s ON l.service_id = s.id
			 LEFT JOIN {$wpdb->prefix}yatoon_staff st ON l.staff_id = st.id
			 WHERE l.client_id = %d
			 ORDER BY COALESCE(l.visit_date, l.created_at) DESC, l.id DESC
			 LIMIT %d",
			$client_id, max( 1, min( 100, $limit ) )
		) );
	}

	/**
	 * The single most recent look for a client identified by phone/email,
	 * for the "what we did last time" quick-reference card on the Calendar
	 * and Bookings screens. Batched (one call per screen render, not per
	 * booking row) by the caller passing every phone/email on the page.
	 *
	 * @param string[] $phones
	 * @param string[] $emails
	 * @return array Keyed by "phone:<value>" and "email:<value>" -> look row.
	 */
	public static function latest_looks_for_contacts( array $phones, array $emails ) {
		global $wpdb;
		$phones = array_values( array_unique( array_filter( array_map( 'strval', $phones ) ) ) );
		$emails = array_values( array_unique( array_filter( array_map( 'strval', $emails ) ) ) );
		if ( ! $phones && ! $emails ) {
			return array();
		}

		$clauses = array();
		$params  = array();
		if ( $phones ) {
			$clauses[] = 'c.customer_phone IN (' . implode( ',', array_fill( 0, count( $phones ), '%s' ) ) . ')';
			$params    = array_merge( $params, $phones );
		}
		if ( $emails ) {
			$clauses[] = 'c.customer_email IN (' . implode( ',', array_fill( 0, count( $emails ), '%s' ) ) . ')';
			$params    = array_merge( $params, $emails );
		}

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $clauses is built entirely from the %s placeholders above.
		$sql = "SELECT l.*, c.customer_phone, c.customer_email, s.name AS service_name
			FROM {$wpdb->prefix}yatoon_client_looks l
			INNER JOIN {$wpdb->prefix}yatoon_clients c ON c.id = l.client_id
			LEFT JOIN {$wpdb->prefix}yatoon_services s ON l.service_id = s.id
			INNER JOIN (
				SELECT client_id, MAX( COALESCE(visit_date, created_at) ) AS max_date
				FROM {$wpdb->prefix}yatoon_client_looks GROUP BY client_id
			) latest ON latest.client_id = l.client_id
				AND COALESCE(l.visit_date, l.created_at) = latest.max_date
			WHERE (" . implode( ' OR ', $clauses ) . ')';

		$rows = $wpdb->get_results( $wpdb->prepare( $sql, $params ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- built above with %s placeholders only.

		$map = array();
		foreach ( (array) $rows as $row ) {
			if ( $row->customer_phone ) {
				$map[ 'phone:' . $row->customer_phone ] = $row;
			}
			if ( $row->customer_email ) {
				$map[ 'email:' . $row->customer_email ] = $row;
			}
		}
		return $map;
	}

	/**
	 * Save (insert or edit) a look entry. When booking_id is provided and a
	 * row already exists for it -- typically the auto-captured reference
	 * photo from the customer's own upload -- this fills in the result photo
	 * and notes on that SAME row instead of creating a second entry for one
	 * visit.
	 */
	public static function ajax_save_look() {
		check_ajax_referer( 'yatoon_client_nonce', 'nonce' );
		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( array( 'message' => __( 'Permission denied.', 'yatoon-booking-system' ) ) );
		}

		global $wpdb;
		$client_id = absint( $_POST['client_id'] ?? 0 );
		if ( ! $client_id || ! $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$wpdb->prefix}yatoon_clients WHERE id = %d", $client_id ) ) ) {
			wp_send_json_error( array( 'message' => __( 'Client not found.', 'yatoon-booking-system' ) ) );
		}

		$look_id    = absint( $_POST['look_id'] ?? 0 );
		$booking_id = absint( $_POST['booking_id'] ?? 0 );
		$notes      = sanitize_textarea_field( wp_unslash( $_POST['notes'] ?? '' ) );
		$result_url = isset( $_POST['result_image_url'] ) ? esc_url_raw( wp_unslash( $_POST['result_image_url'] ) ) : '';
		$visit_date = isset( $_POST['visit_date'] ) ? sanitize_text_field( wp_unslash( $_POST['visit_date'] ) ) : '';
		$visit_date = $visit_date && strtotime( $visit_date ) ? gmdate( 'Y-m-d H:i:s', strtotime( $visit_date ) ) : current_time( 'mysql' );

		if ( '' === $notes && '' === $result_url ) {
			wp_send_json_error( array( 'message' => __( 'Add a photo or a note before saving.', 'yatoon-booking-system' ) ) );
		}

		$table = $wpdb->prefix . 'yatoon_client_looks';

		// Editing an existing row (either the auto-captured one, matched by
		// booking_id, or a manual entry opened by its own id).
		$existing_id = 0;
		if ( $look_id ) {
			$existing_id = (int) $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$table} WHERE id = %d AND client_id = %d", $look_id, $client_id ) );
		} elseif ( $booking_id ) {
			$existing_id = (int) $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$table} WHERE booking_id = %d AND client_id = %d", $booking_id, $client_id ) );
		}

		if ( $existing_id ) {
			$data = array(
				'notes'      => $notes,
				'updated_at' => current_time( 'mysql' ),
			);
			$formats = array( '%s', '%s' );
			if ( $result_url ) {
				$data['result_image_url'] = $result_url;
				$formats[]                = '%s';
			}
			$wpdb->update( $table, $data, array( 'id' => $existing_id ), $formats, array( '%d' ) );
			$id = $existing_id;
		} else {
			$service_id = null;
			$staff_id   = null;
			if ( $booking_id ) {
				$booking = $wpdb->get_row( $wpdb->prepare( "SELECT service_id, staff_id FROM {$wpdb->prefix}yatoon_bookings WHERE id = %d", $booking_id ) );
				if ( $booking ) {
					$service_id = (int) $booking->service_id ?: null;
					$staff_id   = (int) $booking->staff_id ?: null;
				}
			}
			$wpdb->insert(
				$table,
				array(
					'client_id'         => $client_id,
					'booking_id'        => $booking_id ?: null,
					'service_id'        => $service_id,
					'staff_id'          => $staff_id,
					'visit_date'        => $visit_date,
					'result_image_url'  => $result_url ?: null,
					'notes'             => $notes,
					'created_by'        => get_current_user_id(),
					'created_at'        => current_time( 'mysql' ),
					'updated_at'        => current_time( 'mysql' ),
				)
			);
			$id = (int) $wpdb->insert_id;
		}

		if ( ! $id ) {
			wp_send_json_error( array( 'message' => __( 'Could not save.', 'yatoon-booking-system' ) ) );
		}

		$row = $wpdb->get_row( $wpdb->prepare(
			"SELECT l.*, s.name AS service_name, st.name AS staff_name
			 FROM {$wpdb->prefix}yatoon_client_looks l
			 LEFT JOIN {$wpdb->prefix}yatoon_services s ON l.service_id = s.id
			 LEFT JOIN {$wpdb->prefix}yatoon_staff st ON l.staff_id = st.id
			 WHERE l.id = %d",
			$id
		) );
		if ( class_exists( 'YATOON_Insights' ) ) {
			YATOON_Insights::audit( 'client_look_saved', __( 'Look history entry saved.', 'yatoon-booking-system' ), 'client', $client_id );
		}
		wp_send_json_success( array( 'look' => $row ) );
	}

	public static function ajax_delete_look() {
		check_ajax_referer( 'yatoon_client_nonce', 'nonce' );
		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( array( 'message' => __( 'Permission denied.', 'yatoon-booking-system' ) ) );
		}
		global $wpdb;
		$look_id   = absint( $_POST['look_id'] ?? 0 );
		$client_id = absint( $_POST['client_id'] ?? 0 );
		if ( ! $look_id || ! $client_id ) {
			wp_send_json_error();
		}
		$wpdb->delete( $wpdb->prefix . 'yatoon_client_looks', array( 'id' => $look_id, 'client_id' => $client_id ), array( '%d', '%d' ) );
		wp_send_json_success();
	}
}
