<?php
/**
 * Assignment CRUD.
 *
 * @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

class TSM_Assignments {

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

	public static function for_class( $class_id ) {
		global $wpdb;
		$t = self::table();
		return $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$t} WHERE class_id = %d ORDER BY due_date ASC, id ASC", absint( $class_id ) ) );
	}

	/**
	 * Assignments visible to students: hide any whose assigned_date is in the future.
	 * Assignments with no assigned_date are treated as immediately visible.
	 */
	public static function for_class_visible( $class_id ) {
		global $wpdb;
		$t     = self::table();
		$today = current_time( 'Y-m-d' );
		return $wpdb->get_results( $wpdb->prepare(
			"SELECT * FROM {$t} WHERE class_id = %d AND ( assigned_date IS NULL OR assigned_date = '' OR assigned_date <= %s ) ORDER BY due_date ASC, id ASC",
			absint( $class_id ),
			$today
		) );
	}

	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();
		$class_id = absint( $data['class_id'] ?? 0 );

		// Always trust the parent class's school year. Prevents a stale hidden
		// input or a "current year" default from quietly re-homing the row
		// into the wrong year when adding from inside an archived year.
		$year_id = 0;
		if ( $class_id && class_exists( 'TSM_Classes' ) ) {
			$cls = TSM_Classes::get( $class_id );
			if ( $cls ) { $year_id = (int) $cls->school_year_id; }
		}
		if ( ! $year_id ) {
			$year_id = absint( $data['school_year_id'] ?? 0 );
		}

		// Archived-year lockdown.
		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 assignments.', 'tradeschool-manager' ) );
			}
			if ( TSM_School_Years::is_archived( $year_id ) ) {
				return new WP_Error( 'tsm_year_archived', __( 'This class belongs to an archived school year. Unarchive it first to add or edit assignments.', '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 assignment belongs to an archived school year and cannot be edited.', 'tradeschool-manager' ) );
				}
			}
		}

		$points  = floatval( $data['points_possible'] ?? 0 );
		$ec      = ! empty( $data['extra_credit'] ) ? 1 : 0;
		// Pass/Fail classes: every assignment is worth exactly 1 pt under the
		// hood (Pass = 1, Fail = 0) so class averages stay numerically sound.
		// Extra credit is meaningless for P/F and would skew the threshold.
		if ( ! empty( $cls ) && 'pass_fail' === $cls->grading_method ) {
			$points = 1;
			$ec     = 0;
		}

		$record = array(
			'class_id'        => $class_id,
			'school_year_id'  => $year_id,
			'title'           => sanitize_text_field( $data['title'] ?? '' ),
			'description'     => wp_kses_post( $data['description'] ?? '' ),
			'category'        => sanitize_text_field( $data['category'] ?? '' ),
			'assigned_date'   => ! empty( $data['assigned_date'] ) ? ( TSM_Date_Utils::normalize( $data['assigned_date'] ) ?: null ) : null,
			'due_date'        => ! empty( $data['due_date'] ) ? ( TSM_Date_Utils::normalize( $data['due_date'] ) ?: null ) : null,
			'points_possible' => $points,
			'extra_credit'    => $ec,
			'required'        => isset( $data['required'] ) ? (int) (bool) $data['required'] : 1,
		);
		if ( $id ) {
			$wpdb->update( $t, $record, array( 'id' => absint( $id ) ) );
			return absint( $id );
		}
		$wpdb->insert( $t, $record );
		return (int) $wpdb->insert_id;
	}

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

	/**
	 * Return all assignments, optionally filtered by class and/or school year.
	 */
	public static function all( $class_id = 0, $school_year_id = 0 ) {
		global $wpdb;
		$t      = self::table();
		$where  = array( '1=1' );
		$params = array();
		if ( $class_id ) {
			$where[]  = 'class_id = %d';
			$params[] = absint( $class_id );
		}
		if ( $school_year_id ) {
			$where[]  = 'school_year_id = %d';
			$params[] = absint( $school_year_id );
		}
		$sql = "SELECT * FROM {$t} WHERE " . implode( ' AND ', $where ) . ' ORDER BY due_date ASC, id ASC';
		if ( $params ) {
			$sql = $wpdb->prepare( $sql, $params ); // phpcs:ignore
		}
		return $wpdb->get_results( $sql ); // phpcs:ignore
	}

	/**
	 * Find an existing assignment by class + title (used to update on import).
	 */
	public static function find_by_class_title( $class_id, $title ) {
		global $wpdb;
		$t = self::table();
		return $wpdb->get_row( $wpdb->prepare(
			"SELECT * FROM {$t} WHERE class_id = %d AND title = %s LIMIT 1",
			absint( $class_id ),
			(string) $title
		) );
	}
}
