<?php
/**
 * Grading History Class
 *
 * Handles belt grading/promotion history for members
 *
 * @package KarateClubManager
 * @since 1.0.114
 */

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

/**
 * MACM_Grading_History class
 *
 * Manages grading history records for members
 */
class MACM_Grading_History {

	/**
	 * Create a new grading history record
	 *
	 * @param array $data Grading data.
	 * @return int|WP_Error Record ID on success, WP_Error on failure.
	 */
	public static function create( $data ) {
		global $wpdb;

		// Validate required fields.
		if ( empty( $data['member_id'] ) ) {
			return new WP_Error( 'missing_member', __( 'Member ID is required.', 'martial-arts-club-manager' ) );
		}

		if ( empty( $data['belt_color_key'] ) ) {
			return new WP_Error( 'missing_belt', __( 'Belt color is required.', 'martial-arts-club-manager' ) );
		}

		if ( empty( $data['grading_date'] ) ) {
			return new WP_Error( 'missing_date', __( 'Grading date is required.', 'martial-arts-club-manager' ) );
		}

		// Validate member exists.
		$member = MACM_Member::get( absint( $data['member_id'] ) );
		if ( ! $member ) {
			return new WP_Error( 'invalid_member', __( 'Member not found.', 'martial-arts-club-manager' ) );
		}

		// Validate belt color exists.
		$belt = MACM_Belt_Color::get_by_key( sanitize_text_field( $data['belt_color_key'] ) );
		if ( ! $belt ) {
			return new WP_Error( 'invalid_belt', __( 'Invalid belt color.', 'martial-arts-club-manager' ) );
		}

		// Validate grading date is not in future.
		$grading_date = sanitize_text_field( $data['grading_date'] );
		if ( strtotime( $grading_date ) > time() ) {
			return new WP_Error( 'future_date', __( 'Grading date cannot be in the future.', 'martial-arts-club-manager' ) );
		}

		// Prepare data for insertion.
		$insert_data = array(
			'member_id'      => absint( $data['member_id'] ),
			'belt_color_key' => sanitize_text_field( $data['belt_color_key'] ),
			'grading_date'   => $grading_date,
			'examiner_id'    => ! empty( $data['examiner_id'] ) ? absint( $data['examiner_id'] ) : null,
			'examiner_name'  => ! empty( $data['examiner_name'] ) ? sanitize_text_field( $data['examiner_name'] ) : null,
			'score'          => ! empty( $data['score'] ) ? sanitize_text_field( $data['score'] ) : null,
			'notes'          => ! empty( $data['notes'] ) ? sanitize_textarea_field( $data['notes'] ) : null,
			'created_by'     => get_current_user_id(),
		);

		$format = array( '%d', '%s', '%s', '%d', '%s', '%s', '%s', '%d' );

		$result = $wpdb->insert(
			$wpdb->prefix . 'macm_grading_history',
			$insert_data,
			$format
		);

		if ( false === $result ) {
			return new WP_Error( 'db_error', __( 'Failed to create grading record.', 'martial-arts-club-manager' ) );
		}

		return $wpdb->insert_id;
	}

	/**
	 * Get a grading history record by ID
	 *
	 * @param int $record_id Record ID.
	 * @return object|null Record object or null if not found.
	 */
	public static function get( $record_id ) {
		global $wpdb;

		$grading_table     = $wpdb->prefix . 'macm_grading_history';
		$belt_colors_table = $wpdb->prefix . 'macm_belt_colors';
		$instructors_table = $wpdb->prefix . 'macm_instructors';

		return $wpdb->get_row(
			$wpdb->prepare(
				'SELECT gh.*, bc.color_name, i.full_name as instructor_name
				 FROM %i gh
				 LEFT JOIN %i bc ON gh.belt_color_key = bc.color_key
				 LEFT JOIN %i i ON gh.examiner_id = i.id
				 WHERE gh.id = %d',
				$grading_table,
				$belt_colors_table,
				$instructors_table,
				$record_id
			)
		);
	}

	/**
	 * Get all grading history for a member
	 *
	 * @param int   $member_id Member ID.
	 * @param array $args      Optional arguments (order, limit).
	 * @return array Array of grading records.
	 */
	public static function get_member_history( $member_id, $args = array() ) {
		global $wpdb;

		$defaults = array(
			'order'   => 'DESC', // Most recent first.
			'orderby' => 'grading_date',
			'limit'   => 0, // 0 = no limit.
		);

		$args = wp_parse_args( $args, $defaults );

		$order   = in_array( strtoupper( $args['order'] ), array( 'ASC', 'DESC' ), true ) ? strtoupper( $args['order'] ) : 'DESC';
		$orderby = in_array( $args['orderby'], array( 'grading_date', 'created_at', 'belt_color_key' ), true ) ? $args['orderby'] : 'grading_date';

		// Execute query using helper method with literal SQL strings.
		// This approach satisfies WordPress Plugin Check static analysis requirements.
		// Table names are inlined in the helper method to avoid EscapedDBParameter warnings.
		return self::execute_member_history_query(
			$wpdb,
			$member_id,
			$orderby,
			$order,
			$args['limit']
		);
	}

	/**
	 * Execute member history query with literal SQL strings.
	 *
	 * Uses conditional execution with literal ORDER BY strings to satisfy WordPress Plugin Check
	 * static analysis requirements. Each query uses literal table names inline to avoid
	 * variable interpolation warnings.
	 *
	 * @since 1.0.290
	 * @since 1.0.291 Inlined table names to avoid EscapedDBParameter warnings.
	 * @param wpdb   $wpdb    Database object.
	 * @param int    $member_id Member ID.
	 * @param string $orderby   Validated orderby column.
	 * @param string $order     Validated order direction (ASC/DESC).
	 * @param int    $limit     Query limit (0 = no limit).
	 * @return array Array of grading records.
	 */
	private static function execute_member_history_query( $wpdb, $member_id, $orderby, $order, $limit ) {
		$order_key = $orderby . '_' . $order;
		$has_limit = $limit > 0;

		// Each case uses literal SQL string with inline table names to satisfy static analysis.
		// Table names are built from $wpdb->prefix concatenated with literal strings.
		switch ( $order_key ) {
			case 'grading_date_ASC':
				if ( $has_limit ) {
					return $wpdb->get_results(
						$wpdb->prepare(
							'SELECT gh.*, bc.color_name, bc.sort_order as belt_order, i.full_name as instructor_name FROM %i gh LEFT JOIN %i bc ON gh.belt_color_key = bc.color_key LEFT JOIN %i i ON gh.examiner_id = i.id WHERE gh.member_id = %d ORDER BY grading_date ASC LIMIT %d',
							$wpdb->prefix . 'macm_grading_history',
							$wpdb->prefix . 'macm_belt_colors',
							$wpdb->prefix . 'macm_instructors',
							$member_id,
							$limit
						)
					);
				}
				return $wpdb->get_results(
					$wpdb->prepare(
						'SELECT gh.*, bc.color_name, bc.sort_order as belt_order, i.full_name as instructor_name FROM %i gh LEFT JOIN %i bc ON gh.belt_color_key = bc.color_key LEFT JOIN %i i ON gh.examiner_id = i.id WHERE gh.member_id = %d ORDER BY grading_date ASC',
						$wpdb->prefix . 'macm_grading_history',
						$wpdb->prefix . 'macm_belt_colors',
						$wpdb->prefix . 'macm_instructors',
						$member_id
					)
				);

			case 'created_at_ASC':
				if ( $has_limit ) {
					return $wpdb->get_results(
						$wpdb->prepare(
							'SELECT gh.*, bc.color_name, bc.sort_order as belt_order, i.full_name as instructor_name FROM %i gh LEFT JOIN %i bc ON gh.belt_color_key = bc.color_key LEFT JOIN %i i ON gh.examiner_id = i.id WHERE gh.member_id = %d ORDER BY created_at ASC LIMIT %d',
							$wpdb->prefix . 'macm_grading_history',
							$wpdb->prefix . 'macm_belt_colors',
							$wpdb->prefix . 'macm_instructors',
							$member_id,
							$limit
						)
					);
				}
				return $wpdb->get_results(
					$wpdb->prepare(
						'SELECT gh.*, bc.color_name, bc.sort_order as belt_order, i.full_name as instructor_name FROM %i gh LEFT JOIN %i bc ON gh.belt_color_key = bc.color_key LEFT JOIN %i i ON gh.examiner_id = i.id WHERE gh.member_id = %d ORDER BY created_at ASC',
						$wpdb->prefix . 'macm_grading_history',
						$wpdb->prefix . 'macm_belt_colors',
						$wpdb->prefix . 'macm_instructors',
						$member_id
					)
				);

			case 'created_at_DESC':
				if ( $has_limit ) {
					return $wpdb->get_results(
						$wpdb->prepare(
							'SELECT gh.*, bc.color_name, bc.sort_order as belt_order, i.full_name as instructor_name FROM %i gh LEFT JOIN %i bc ON gh.belt_color_key = bc.color_key LEFT JOIN %i i ON gh.examiner_id = i.id WHERE gh.member_id = %d ORDER BY created_at DESC LIMIT %d',
							$wpdb->prefix . 'macm_grading_history',
							$wpdb->prefix . 'macm_belt_colors',
							$wpdb->prefix . 'macm_instructors',
							$member_id,
							$limit
						)
					);
				}
				return $wpdb->get_results(
					$wpdb->prepare(
						'SELECT gh.*, bc.color_name, bc.sort_order as belt_order, i.full_name as instructor_name FROM %i gh LEFT JOIN %i bc ON gh.belt_color_key = bc.color_key LEFT JOIN %i i ON gh.examiner_id = i.id WHERE gh.member_id = %d ORDER BY created_at DESC',
						$wpdb->prefix . 'macm_grading_history',
						$wpdb->prefix . 'macm_belt_colors',
						$wpdb->prefix . 'macm_instructors',
						$member_id
					)
				);

			case 'belt_color_key_ASC':
				if ( $has_limit ) {
					return $wpdb->get_results(
						$wpdb->prepare(
							'SELECT gh.*, bc.color_name, bc.sort_order as belt_order, i.full_name as instructor_name FROM %i gh LEFT JOIN %i bc ON gh.belt_color_key = bc.color_key LEFT JOIN %i i ON gh.examiner_id = i.id WHERE gh.member_id = %d ORDER BY belt_color_key ASC LIMIT %d',
							$wpdb->prefix . 'macm_grading_history',
							$wpdb->prefix . 'macm_belt_colors',
							$wpdb->prefix . 'macm_instructors',
							$member_id,
							$limit
						)
					);
				}
				return $wpdb->get_results(
					$wpdb->prepare(
						'SELECT gh.*, bc.color_name, bc.sort_order as belt_order, i.full_name as instructor_name FROM %i gh LEFT JOIN %i bc ON gh.belt_color_key = bc.color_key LEFT JOIN %i i ON gh.examiner_id = i.id WHERE gh.member_id = %d ORDER BY belt_color_key ASC',
						$wpdb->prefix . 'macm_grading_history',
						$wpdb->prefix . 'macm_belt_colors',
						$wpdb->prefix . 'macm_instructors',
						$member_id
					)
				);

			case 'belt_color_key_DESC':
				if ( $has_limit ) {
					return $wpdb->get_results(
						$wpdb->prepare(
							'SELECT gh.*, bc.color_name, bc.sort_order as belt_order, i.full_name as instructor_name FROM %i gh LEFT JOIN %i bc ON gh.belt_color_key = bc.color_key LEFT JOIN %i i ON gh.examiner_id = i.id WHERE gh.member_id = %d ORDER BY belt_color_key DESC LIMIT %d',
							$wpdb->prefix . 'macm_grading_history',
							$wpdb->prefix . 'macm_belt_colors',
							$wpdb->prefix . 'macm_instructors',
							$member_id,
							$limit
						)
					);
				}
				return $wpdb->get_results(
					$wpdb->prepare(
						'SELECT gh.*, bc.color_name, bc.sort_order as belt_order, i.full_name as instructor_name FROM %i gh LEFT JOIN %i bc ON gh.belt_color_key = bc.color_key LEFT JOIN %i i ON gh.examiner_id = i.id WHERE gh.member_id = %d ORDER BY belt_color_key DESC',
						$wpdb->prefix . 'macm_grading_history',
						$wpdb->prefix . 'macm_belt_colors',
						$wpdb->prefix . 'macm_instructors',
						$member_id
					)
				);

			default: // grading_date_DESC.
				if ( $has_limit ) {
					return $wpdb->get_results(
						$wpdb->prepare(
							'SELECT gh.*, bc.color_name, bc.sort_order as belt_order, i.full_name as instructor_name FROM %i gh LEFT JOIN %i bc ON gh.belt_color_key = bc.color_key LEFT JOIN %i i ON gh.examiner_id = i.id WHERE gh.member_id = %d ORDER BY grading_date DESC LIMIT %d',
							$wpdb->prefix . 'macm_grading_history',
							$wpdb->prefix . 'macm_belt_colors',
							$wpdb->prefix . 'macm_instructors',
							$member_id,
							$limit
						)
					);
				}
				return $wpdb->get_results(
					$wpdb->prepare(
						'SELECT gh.*, bc.color_name, bc.sort_order as belt_order, i.full_name as instructor_name FROM %i gh LEFT JOIN %i bc ON gh.belt_color_key = bc.color_key LEFT JOIN %i i ON gh.examiner_id = i.id WHERE gh.member_id = %d ORDER BY grading_date DESC',
						$wpdb->prefix . 'macm_grading_history',
						$wpdb->prefix . 'macm_belt_colors',
						$wpdb->prefix . 'macm_instructors',
						$member_id
					)
				);
		}
	}

	/**
	 * Build ORDER BY SQL clause from validated values.
	 *
	 * Returns literal SQL string based on pre-validated orderby and order parameters.
	 * Both parameters MUST be validated against whitelists before calling.
	 *
	 * @since 1.0.289
	 * @param string $orderby Validated orderby column (grading_date, created_at, belt_color_key).
	 * @param string $order   Validated order direction (ASC, DESC).
	 * @return string ORDER BY SQL clause.
	 */
	private static function build_order_sql( $orderby, $order ) {
		// Map validated values to literal SQL strings.
		$order_map = array(
			'grading_date_ASC'    => 'ORDER BY grading_date ASC',
			'grading_date_DESC'   => 'ORDER BY grading_date DESC',
			'created_at_ASC'      => 'ORDER BY created_at ASC',
			'created_at_DESC'     => 'ORDER BY created_at DESC',
			'belt_color_key_ASC'  => 'ORDER BY belt_color_key ASC',
			'belt_color_key_DESC' => 'ORDER BY belt_color_key DESC',
		);

		$key = $orderby . '_' . $order;
		return isset( $order_map[ $key ] ) ? $order_map[ $key ] : 'ORDER BY grading_date DESC';
	}

	/**
	 * Get the latest grading record for a member
	 *
	 * @param int $member_id Member ID.
	 * @return object|null Latest grading record or null.
	 */
	public static function get_latest( $member_id ) {
		$history = self::get_member_history( $member_id, array( 'limit' => 1 ) );
		return ! empty( $history ) ? $history[0] : null;
	}

	/**
	 * Update a grading history record
	 *
	 * @param int   $record_id Record ID.
	 * @param array $data      Data to update.
	 * @return bool|WP_Error True on success, WP_Error on failure.
	 */
	public static function update( $record_id, $data ) {
		global $wpdb;

		$record = self::get( $record_id );
		if ( ! $record ) {
			return new WP_Error( 'not_found', __( 'Grading record not found.', 'martial-arts-club-manager' ) );
		}

		$update_data = array();
		$format      = array();

		// Update belt color if provided.
		if ( ! empty( $data['belt_color_key'] ) ) {
			$belt = MACM_Belt_Color::get_by_key( sanitize_text_field( $data['belt_color_key'] ) );
			if ( ! $belt ) {
				return new WP_Error( 'invalid_belt', __( 'Invalid belt color.', 'martial-arts-club-manager' ) );
			}
			$update_data['belt_color_key'] = sanitize_text_field( $data['belt_color_key'] );
			$format[]                      = '%s';
		}

		// Update grading date if provided.
		if ( ! empty( $data['grading_date'] ) ) {
			$grading_date = sanitize_text_field( $data['grading_date'] );
			if ( strtotime( $grading_date ) > time() ) {
				return new WP_Error( 'future_date', __( 'Grading date cannot be in the future.', 'martial-arts-club-manager' ) );
			}
			$update_data['grading_date'] = $grading_date;
			$format[]                    = '%s';
		}

		// Update examiner if provided.
		if ( array_key_exists( 'examiner_id', $data ) ) {
			$update_data['examiner_id'] = ! empty( $data['examiner_id'] ) ? absint( $data['examiner_id'] ) : null;
			$format[]                   = '%d';
		}

		if ( array_key_exists( 'examiner_name', $data ) ) {
			$update_data['examiner_name'] = ! empty( $data['examiner_name'] ) ? sanitize_text_field( $data['examiner_name'] ) : null;
			$format[]                     = '%s';
		}

		// Update score if provided.
		if ( array_key_exists( 'score', $data ) ) {
			$update_data['score'] = ! empty( $data['score'] ) ? sanitize_text_field( $data['score'] ) : null;
			$format[]             = '%s';
		}

		// Update notes if provided.
		if ( array_key_exists( 'notes', $data ) ) {
			$update_data['notes'] = ! empty( $data['notes'] ) ? sanitize_textarea_field( $data['notes'] ) : null;
			$format[]             = '%s';
		}

		if ( empty( $update_data ) ) {
			return new WP_Error( 'no_data', __( 'No data to update.', 'martial-arts-club-manager' ) );
		}

		$result = $wpdb->update(
			$wpdb->prefix . 'macm_grading_history',
			$update_data,
			array( 'id' => $record_id ),
			$format,
			array( '%d' )
		);

		if ( false === $result ) {
			return new WP_Error( 'db_error', __( 'Failed to update grading record.', 'martial-arts-club-manager' ) );
		}

		return true;
	}

	/**
	 * Delete a grading history record
	 *
	 * @param int $record_id Record ID.
	 * @return bool|WP_Error True on success, WP_Error on failure.
	 */
	public static function delete( $record_id ) {
		global $wpdb;

		$record = self::get( $record_id );
		if ( ! $record ) {
			return new WP_Error( 'not_found', __( 'Grading record not found.', 'martial-arts-club-manager' ) );
		}

		$result = $wpdb->delete(
			$wpdb->prefix . 'macm_grading_history',
			array( 'id' => $record_id ),
			array( '%d' )
		);

		if ( false === $result ) {
			return new WP_Error( 'db_error', __( 'Failed to delete grading record.', 'martial-arts-club-manager' ) );
		}

		return true;
	}

	/**
	 * Delete all grading history for a member
	 *
	 * Used when a member is permanently deleted
	 *
	 * @param int $member_id Member ID.
	 * @return int|false Number of rows deleted or false on error.
	 */
	public static function delete_member_history( $member_id ) {
		global $wpdb;

		return $wpdb->delete(
			$wpdb->prefix . 'macm_grading_history',
			array( 'member_id' => $member_id ),
			array( '%d' )
		);
	}

	/**
	 * Count grading records for a member
	 *
	 * @param int $member_id Member ID.
	 * @return int Number of grading records.
	 */
	public static function count_member_gradings( $member_id ) {
		global $wpdb;

		$table_name = $wpdb->prefix . 'macm_grading_history';

		return (int) $wpdb->get_var(
			$wpdb->prepare(
				'SELECT COUNT(*) FROM %i WHERE member_id = %d',
				$table_name,
				$member_id
			)
		);
	}

	/**
	 * Export grading history to CSV format
	 *
	 * @param int $member_id Member ID.
	 * @return array CSV data with headers and rows.
	 */
	public static function export_csv( $member_id ) {
		$member = MACM_Member::get( $member_id );
		if ( ! $member ) {
			return array();
		}

		$history = self::get_member_history( $member_id, array( 'order' => 'ASC' ) );

		$headers = array(
			__( 'Grading Date', 'martial-arts-club-manager' ),
			__( 'Belt Color', 'martial-arts-club-manager' ),
			__( 'Examiner', 'martial-arts-club-manager' ),
			__( 'Score', 'martial-arts-club-manager' ),
			__( 'Notes', 'martial-arts-club-manager' ),
		);

		$rows = array();
		foreach ( $history as $record ) {
			// Determine examiner display name.
			$examiner = '';
			if ( ! empty( $record->instructor_name ) ) {
				$examiner = $record->instructor_name;
			} elseif ( ! empty( $record->examiner_name ) ) {
				$examiner = $record->examiner_name;
			}

			$rows[] = array(
				date_i18n( get_option( 'date_format' ), strtotime( $record->grading_date ) ),
				$record->color_name ?? $record->belt_color_key,
				$examiner,
				$record->score ?? '',
				$record->notes ?? '',
			);
		}

		return array(
			'member_name' => $member->full_name,
			'headers'     => $headers,
			'rows'        => $rows,
		);
	}
}
