<?php
/**
 * Teacher 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_Teachers {

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

	public static function all( $school_year_id = 0 ) {
		global $wpdb;
		$t = self::table();
		$year = absint( $school_year_id );
		if ( $year ) {
			$cl = $wpdb->prefix . 'tsm_classes';
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			return $wpdb->get_results( $wpdb->prepare(
				"SELECT t.* FROM {$t} t INNER JOIN (SELECT DISTINCT teacher_id FROM {$cl} WHERE school_year_id = %d AND teacher_id > 0) yt ON yt.teacher_id = t.id ORDER BY t.last_name ASC, t.first_name ASC",
				$year
			) );
		}
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		return $wpdb->get_results( "SELECT * FROM {$t} ORDER BY last_name ASC, first_name ASC" );
	}

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

	public static function save( $data, $id = 0 ) {
		global $wpdb;
		$t        = self::table();
		$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 teachers 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' ),
		);
		if ( ! empty( $data['password'] ) ) {
			$record['password_hash'] = wp_hash_password( $data['password'] );
		}
		if ( $id ) {
			$wpdb->update( $t, $record, array( 'id' => absint( $id ) ) );
			return absint( $id );
		}
		$result = $wpdb->insert( $t, $record );
		if ( false === $result ) {
			return new WP_Error( 'tsm_teacher_insert_failed', $wpdb->last_error ? $wpdb->last_error : __( 'Could not save teacher.', 'tradeschool-manager' ) );
		}
		return (int) $wpdb->insert_id;
	}

	public static function delete( $id ) {
		global $wpdb;
		$wpdb->delete( self::table(), array( 'id' => absint( $id ) ) );
	}
}
