<?php
/**
 * Student CRUD.
 *
 * @package TradeSchoolManager
 */

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

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Plugin-owned custom tables; table names interpolated only; user values always pass through $wpdb->prepare().

class TSM_Students {

	public static function table() {
		global $wpdb;
		return $wpdb->prefix . 'tsm_students';
	}

	public static function all( $args = array() ) {
		global $wpdb;
		$t = self::table();
		$args = wp_parse_args( $args, array( 'status' => '', 'school_year_id' => 0 ) );
		$where = array( '1=1' );
		if ( 'active' === $args['status'] ) {
			$where[] = "s.status <> 'dropped'";
		} elseif ( 'dropped' === $args['status'] ) {
			$where[] = "s.status = 'dropped'";
		}
		$year   = absint( $args['school_year_id'] );
		$join   = '';
		if ( $year ) {
			// Include students who EITHER have a class enrollment in the year
			// OR a program enrollment tagged with the year (covers students
			// just admitted via the Applications add-on, before classes are
			// assigned).
			$en  = $wpdb->prefix . 'tsm_enrollments';
			$cl  = $wpdb->prefix . 'tsm_classes';
			$pen = $wpdb->prefix . 'tsm_program_enrollments';
			$join = $wpdb->prepare(
				" INNER JOIN ("
				. "SELECT DISTINCT student_id FROM ("
				. "  SELECT e.student_id FROM {$en} e INNER JOIN {$cl} c ON c.id = e.class_id WHERE c.school_year_id = %d"
				. "  UNION"
				. "  SELECT pe.student_id FROM {$pen} pe WHERE pe.school_year_id = %d"
				. ") u"
				. ") ye ON ye.student_id = s.id",
				$year,
				$year
			); // phpcs:ignore
		}
		$sql = "SELECT s.* FROM {$t} s {$join} WHERE " . implode( ' AND ', $where ) . ' ORDER BY s.last_name ASC, s.first_name ASC';
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery
		return $wpdb->get_results( $sql );
	}

	public static function active( $school_year_id = 0 ) {
		return self::all( array( 'status' => 'active', 'school_year_id' => $school_year_id ) );
	}

	public static function dropped( $school_year_id = 0 ) {
		return self::all( array( 'status' => 'dropped', 'school_year_id' => $school_year_id ) );
	}

	/**
	 * Mark a student as dropped (soft, preserves all records).
	 */
	public static function drop( $id, $reason = '', $date = '' ) {
		global $wpdb;
		$id   = absint( $id );
		if ( ! $id ) { return false; }
		$date = $date ? sanitize_text_field( $date ) : current_time( 'Y-m-d' );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		return false !== $wpdb->update(
			self::table(),
			array(
				'status'         => 'dropped',
				'dropped_date'   => $date,
				'dropped_reason' => sanitize_textarea_field( $reason ),
			),
			array( 'id' => $id )
		);
	}

	/**
	 * Reinstate a previously-dropped student.
	 */
	public static function reinstate( $id ) {
		global $wpdb;
		$id = absint( $id );
		if ( ! $id ) { return false; }
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		return false !== $wpdb->update(
			self::table(),
			array(
				'status'         => 'active',
				'dropped_date'   => null,
				'dropped_reason' => null,
			),
			array( 'id' => $id )
		);
	}

	public static function get( $id ) {
		global $wpdb;
		$t = self::table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		return $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$t} WHERE id = %d", absint( $id ) ) );
	}

	public static function get_by_username( $username ) {
		global $wpdb;
		$t = self::table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		return $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$t} WHERE username = %s", $username ) );
	}

	public static function save( $data, $id = 0 ) {
		global $wpdb;
		$t = self::table();

		// Archived-year lockdown: block creating NEW students when the admin
		// is viewing an archived school year. Editing existing students is
		// still allowed because they may roll forward into other programs
		// or workshops in a future year.
		if ( ! $id && class_exists( 'TSM_School_Years' ) && TSM_School_Years::is_active_year_archived() ) {
			return new WP_Error( 'tsm_year_archived', __( 'You are viewing an archived school year. Switch to a current year before adding new students.', 'tradeschool-manager' ) );
		}

		$email    = isset( $data['email'] ) ? sanitize_email( $data['email'] ) : '';
		$username = sanitize_user( $data['username'] ?? '', true );

		$record = array(
			'first_name'         => sanitize_text_field( $data['first_name'] ?? '' ),
			'last_name'          => sanitize_text_field( $data['last_name'] ?? '' ),
			// Store NULL (not '') so the UNIQUE index allows multiple students without an email/username.
			'email'              => '' === $email ? null : $email,
			'phone'              => sanitize_text_field( $data['phone'] ?? '' ),
			'username'           => '' === $username ? null : $username,
			'status'             => sanitize_text_field( $data['status'] ?? 'active' ),
			'notes'              => sanitize_textarea_field( $data['notes'] ?? '' ),
			'photo_url'          => isset( $data['photo_url'] ) ? esc_url_raw( $data['photo_url'] ) : '',
			'emergency_name'     => sanitize_text_field( $data['emergency_name'] ?? '' ),
			'emergency_phone'    => sanitize_text_field( $data['emergency_phone'] ?? '' ),
			'emergency_relation' => sanitize_text_field( $data['emergency_relation'] ?? '' ),
			// total_program_cost removed in v1.7.0  -  tuition lives on Programs/enrollments now.
		);

		if ( ! empty( $data['password'] ) ) {
			$record['password_hash'] = wp_hash_password( $data['password'] );
		}

		if ( $id ) {
			$wpdb->update( $t, $record, array( 'id' => absint( $id ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
			return absint( $id );
		}
		$result = $wpdb->insert( $t, $record ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
		if ( false === $result ) {
			return new WP_Error( 'tsm_student_insert_failed', $wpdb->last_error ? $wpdb->last_error : __( 'Could not save student.', 'tradeschool-manager' ) );
		}
		return (int) $wpdb->insert_id;
	}

	public static function delete( $id ) {
		global $wpdb;
		$wpdb->delete( self::table(), array( 'id' => absint( $id ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
	}

	/**
	 * Verify student credentials for portal login.
	 */
	public static function verify_login( $username, $password ) {
		$student = self::get_by_username( $username );
		if ( ! $student || empty( $student->password_hash ) ) {
			return false;
		}
		if ( wp_check_password( $password, $student->password_hash ) ) {
			return $student;
		}
		return false;
	}

	/**
	 * Find a student by their email address (used for password reset).
	 */
	public static function get_by_email( $email ) {
		global $wpdb;
		$t = self::table();
		$email = sanitize_email( $email );
		if ( empty( $email ) ) {
			return null;
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		return $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$t} WHERE email = %s", $email ) );
	}

	/**
	 * Generate a one-time password reset token (expires in 1 hour).
	 * Returns the raw token string, or empty string if student lacks an email.
	 */
	public static function create_reset_token( $student_id ) {
		global $wpdb;
		$student = self::get( $student_id );
		if ( ! $student || empty( $student->email ) ) {
			return '';
		}
		$token   = wp_generate_password( 40, false, false );
		$expires = gmdate( 'Y-m-d H:i:s', time() + HOUR_IN_SECONDS );
		$wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
			self::table(),
			array( 'reset_token' => $token, 'reset_expires' => $expires ),
			array( 'id' => absint( $student_id ) )
		);
		return $token;
	}

	/**
	 * Look up a student by an unexpired reset token.
	 */
	public static function get_by_reset_token( $token ) {
		global $wpdb;
		$t     = self::table();
		$token = preg_replace( '/[^A-Za-z0-9]/', '', (string) $token );
		if ( strlen( $token ) < 20 ) {
			return null;
		}
		$now = gmdate( 'Y-m-d H:i:s' );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		return $wpdb->get_row( $wpdb->prepare(
			"SELECT * FROM {$t} WHERE reset_token = %s AND reset_expires IS NOT NULL AND reset_expires > %s",
			$token,
			$now
		) );
	}

	/**
	 * Set a new password for a student and clear any reset token.
	 */
	public static function set_password( $student_id, $new_password ) {
		global $wpdb;
		$student_id = absint( $student_id );
		if ( ! $student_id || strlen( (string) $new_password ) < 8 ) {
			return false;
		}
		$hash   = wp_hash_password( $new_password );
		$result = $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
			self::table(),
			array( 'password_hash' => $hash ),
			array( 'id' => $student_id ),
			array( '%s' ),
			array( '%d' )
		);
		if ( false === $result ) {
			return false;
		}
		$saved = self::get( $student_id );
		if ( ! $saved || empty( $saved->password_hash ) || ! wp_check_password( $new_password, $saved->password_hash ) ) {
			return false;
		}
		// Clear any pending reset token if those columns exist (older installs may not have them).
		$cols = $wpdb->get_col( "SHOW COLUMNS FROM " . self::table() ); // phpcs:ignore
		if ( is_array( $cols ) && in_array( 'reset_token', $cols, true ) ) {
			$wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
				self::table(),
				array( 'reset_token' => null, 'reset_expires' => null ),
				array( 'id' => $student_id ),
				array( '%s', '%s' ),
				array( '%d' )
			);
		}
		return true;
	}

}
