<?php
/**
 * Class CRUD and enrollment.
 *
 * @package TradeSchoolManager
 */

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

/*
 * Manages plugin-owned custom tables ($wpdb->prefix . 'tsm_*'). Direct
 * queries are required; interpolation uses table-name literals only;
 * parameters go through $wpdb->prepare() placeholders. Hooks use the
 * registered `tsm`/`tsmp` prefix.
 */
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery
// phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter
// phpcs:disable WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedHooknameFound

class TSM_Classes {

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

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

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

	/** All teacher IDs (primary + co-teachers) for a class. */
	public static function teacher_ids( $class_id ) {
		global $wpdb;
		$class_id = absint( $class_id );
		if ( ! $class_id ) { return array(); }
		$class = self::get( $class_id );
		$ids = array();
		if ( $class && ! empty( $class->teacher_id ) ) { $ids[] = (int) $class->teacher_id; }
		$ct = self::class_teachers_table();
		$extra = $wpdb->get_col( $wpdb->prepare( "SELECT teacher_id FROM {$ct} WHERE class_id = %d", $class_id ) );
		foreach ( (array) $extra as $tid ) { $ids[] = (int) $tid; }
		return array_values( array_unique( array_filter( $ids ) ) );
	}

	/** True if teacher is primary or co-teacher of class. */
	public static function is_teacher_of( $class_id, $teacher_id ) {
		$teacher_id = absint( $teacher_id );
		if ( ! $teacher_id ) { return false; }
		return in_array( $teacher_id, self::teacher_ids( $class_id ), true );
	}

	/** Co-teacher IDs only (excludes primary). */
	public static function co_teacher_ids( $class_id ) {
		global $wpdb;
		$ct = self::class_teachers_table();
		return array_map( 'intval', (array) $wpdb->get_col( $wpdb->prepare(
			"SELECT teacher_id FROM {$ct} WHERE class_id = %d", absint( $class_id )
		) ) );
	}

	/** Replace co-teacher list for a class. */
	public static function set_co_teachers( $class_id, $teacher_ids ) {
		global $wpdb;
		$class_id = absint( $class_id );
		if ( ! $class_id ) { return; }
		$ct = self::class_teachers_table();
		$wpdb->delete( $ct, array( 'class_id' => $class_id ) );
		$class = self::get( $class_id );
		$primary = $class ? (int) $class->teacher_id : 0;
		$seen = array();
		foreach ( (array) $teacher_ids as $tid ) {
			$tid = absint( $tid );
			if ( ! $tid || $tid === $primary || isset( $seen[ $tid ] ) ) { continue; }
			$seen[ $tid ] = 1;
			$wpdb->insert( $ct, array( 'class_id' => $class_id, 'teacher_id' => $tid ) );
		}
	}

	public static function all( $school_year_id = 0 ) {
		global $wpdb;
		$t = self::table();
		if ( $school_year_id ) {
			return $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$t} WHERE school_year_id = %d ORDER BY name ASC", absint( $school_year_id ) ) );
		}
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		return $wpdb->get_results( "SELECT * FROM {$t} ORDER BY 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();

		$grading_config = self::sanitize_grading_config( $data['grading_config'] ?? array() );

		$year_id = absint( $data['school_year_id'] ?? 0 );

		// Archived-year lockdown: block create/edit in archived years. When
		// editing, also block if the existing row already lives in an archived
		// year so users can't move data out of an archived year.
		if ( class_exists( 'TSM_School_Years' ) ) {
			if ( 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 or editing classes.', 'tradeschool-manager' ) );
			}
			if ( TSM_School_Years::is_archived( $year_id ) ) {
				return new WP_Error( 'tsm_year_archived', __( 'This school year is archived. Unarchive it from School Years before adding or editing data.', 'tradeschool-manager' ) );
			}
			if ( $id ) {
				$existing = self::get( $id );
				if ( $existing && TSM_School_Years::is_archived( (int) $existing->school_year_id ) ) {
					return new WP_Error( 'tsm_year_archived', __( 'This class belongs to an archived school year and cannot be edited.', 'tradeschool-manager' ) );
				}
			}
		}

		$grading_method = sanitize_text_field( $data['grading_method'] ?? 'points' );

		// Weighted classes: enforce category weights total exactly 100%.
		if ( 'weighted' === $grading_method ) {
			$cats = isset( $grading_config['categories'] ) ? $grading_config['categories'] : array();
			if ( empty( $cats ) ) {
				return new WP_Error( 'tsm_weighted_invalid', __( 'Weighted classes require at least one category. Add categories that total 100%.', 'tradeschool-manager' ) );
			}
			$sum = 0.0;
			foreach ( $cats as $w ) { $sum += (float) $w; }
			if ( abs( $sum - 100.0 ) > 0.01 ) {
				return new WP_Error( 'tsm_weighted_invalid', sprintf(
					/* translators: %s: current total percent */
					__( 'Weighted category percentages must total exactly 100%% (currently %s%%).', 'tradeschool-manager' ),
					rtrim( rtrim( number_format( $sum, 2 ), '0' ), '.' )
				) );
			}
		}

		$record = array(
			'school_year_id'   => $year_id,
			'name'             => sanitize_text_field( $data['name'] ?? '' ),
			'description'      => sanitize_textarea_field( $data['description'] ?? '' ),
			'grading_method'   => $grading_method,
			'grading_config'   => wp_json_encode( $grading_config ),
			'teacher_id'       => absint( $data['teacher_id'] ?? 0 ),
			'status'           => sanitize_text_field( $data['status'] ?? 'active' ),
			'passing_pct'      => isset( $data['passing_pct'] ) ? max( 0, min( 100, floatval( $data['passing_pct'] ) ) ) : 70.00,
			'credits'          => isset( $data['credits'] ) ? max( 0, floatval( $data['credits'] ) ) : 0,
		);
		if ( $id ) {
			$wpdb->update( $t, $record, array( 'id' => absint( $id ) ) );
			return absint( $id );
		}
		$wpdb->insert( $t, $record );
		return (int) $wpdb->insert_id;
	}

	/**
	 * Mark a class completed and fire the auto-issue trigger.
	 * Idempotent  -  second call updates completed_at but skip-by-dedupe still applies.
	 */
	public static function mark_completed( $class_id ) {
		global $wpdb;
		$class_id = absint( $class_id );
		if ( ! $class_id ) { return false; }
		$wpdb->update( self::table(), array(
			'status'       => 'completed',
			'completed_at' => current_time( 'mysql' ),
		), array( 'id' => $class_id ) );
		do_action( 'tradeschool_manager_class_completed', $class_id );
		return true;
	}

	/**
	 * Normalize grading_config from POST into a stable shape:
	 *   array( 'categories' => array( 'Homework' => 30, 'Tests' => 70 ) )
	 *
	 * Accepts either:
	 *   - categories as a list of rows: [ ['name'=>'Homework','weight'=>30], ... ]
	 *   - categories as an associative map: [ 'Homework' => 30, ... ]
	 */
	public static function sanitize_grading_config( $raw ) {
		$out = array( 'categories' => array() );
		if ( ! is_array( $raw ) ) {
			return $out;
		}
		$cats = isset( $raw['categories'] ) && is_array( $raw['categories'] ) ? $raw['categories'] : array();
		foreach ( $cats as $key => $row ) {
			if ( is_array( $row ) ) {
				$name   = isset( $row['name'] ) ? sanitize_text_field( $row['name'] ) : '';
				$weight = isset( $row['weight'] ) ? floatval( $row['weight'] ) : 0;
			} else {
				$name   = sanitize_text_field( (string) $key );
				$weight = floatval( $row );
			}
			if ( '' === $name ) {
				continue;
			}
			$out['categories'][ $name ] = $weight;
		}
		return $out;
	}

	/**
	 * Return the list of category names defined for a class, in the
	 * order they were saved. Empty array if none configured.
	 */
	public static function categories( $class_id ) {
		$class = self::get( $class_id );
		if ( ! $class || empty( $class->grading_config ) ) {
			return array();
		}
		$config = json_decode( $class->grading_config, true );
		if ( ! is_array( $config ) || empty( $config['categories'] ) ) {
			return array();
		}
		return array_keys( $config['categories'] );
	}


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

	public static function enroll( $class_id, $student_id ) {
		global $wpdb;
		$wpdb->replace(
			self::enrollments_table(),
			array(
				'class_id'   => absint( $class_id ),
				'student_id' => absint( $student_id ),
				'status'     => 'active',
			)
		);
	}

	public static function unenroll( $class_id, $student_id ) {
		global $wpdb;
		$wpdb->delete(
			self::enrollments_table(),
			array(
				'class_id'   => absint( $class_id ),
				'student_id' => absint( $student_id ),
			)
		);
	}

	public static function students_in_class( $class_id, $args = array() ) {
		global $wpdb;
		$e = self::enrollments_table();
		$s = TSM_Students::table();
		$args = wp_parse_args( $args, array( 'include_dropped' => false ) );
		$where_status = $args['include_dropped'] ? '' : "AND ( s.status IS NULL OR s.status <> 'dropped' )";
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT s.* FROM {$s} s INNER JOIN {$e} e ON e.student_id = s.id WHERE e.class_id = %d {$where_status} ORDER BY s.last_name ASC",
				absint( $class_id )
			)
		);
	}

	public static function classes_for_student( $student_id ) {
		global $wpdb;
		$e = self::enrollments_table();
		$c = self::table();
		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT c.* FROM {$c} c INNER JOIN {$e} e ON e.class_id = c.id WHERE e.student_id = %d ORDER BY c.name ASC",
				absint( $student_id )
			)
		);
	}

	public static function classes_for_teacher( $teacher_id, $school_year_id = 0 ) {
		global $wpdb;
		$c  = self::table();
		$ct = self::class_teachers_table();
		$tid = absint( $teacher_id );
		if ( $school_year_id ) {
			return $wpdb->get_results( $wpdb->prepare(
				"SELECT DISTINCT c.* FROM {$c} c
				 LEFT JOIN {$ct} ct ON ct.class_id = c.id
				 WHERE c.school_year_id = %d AND ( c.teacher_id = %d OR ct.teacher_id = %d )
				 ORDER BY c.name ASC",
				absint( $school_year_id ), $tid, $tid
			) );
		}
		return $wpdb->get_results( $wpdb->prepare(
			"SELECT DISTINCT c.* FROM {$c} c
			 LEFT JOIN {$ct} ct ON ct.class_id = c.id
			 WHERE c.teacher_id = %d OR ct.teacher_id = %d
			 ORDER BY c.name ASC",
			$tid, $tid
		) );
	}
}
