<?php
/**
 * Database schema management.
 *
 * @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_DB {

	/**
	 * Run dbDelta to install/upgrade tables.
	 */
	public static function install() {
		global $wpdb;
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$charset_collate = $wpdb->get_charset_collate();
		$p               = $wpdb->prefix;

		$sql = array();

		$sql[] = "CREATE TABLE {$p}tsm_school_years (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			name VARCHAR(120) NOT NULL,
			start_date DATE NULL,
			end_date DATE NULL,
			status VARCHAR(20) NOT NULL DEFAULT 'active',
			archived TINYINT(1) NOT NULL DEFAULT 0,
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY status (status),
			KEY archived (archived)
		) {$charset_collate};";

		$sql[] = "CREATE TABLE {$p}tsm_students (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			first_name VARCHAR(100) NOT NULL,
			last_name VARCHAR(100) NOT NULL,
			email VARCHAR(190) NULL,
			phone VARCHAR(40) NULL,
			username VARCHAR(60) NULL,
			password_hash VARCHAR(255) NULL,
			status VARCHAR(20) NOT NULL DEFAULT 'active',
			notes TEXT NULL,
			photo_url VARCHAR(255) NULL,
			emergency_name VARCHAR(190) NULL,
			emergency_phone VARCHAR(40) NULL,
			emergency_relation VARCHAR(80) NULL,
			reset_token VARCHAR(64) NULL,
			reset_expires DATETIME NULL,
			dropped_date DATE NULL,
			dropped_reason TEXT NULL,
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			UNIQUE KEY email (email),
			UNIQUE KEY username (username),
			KEY status (status),
			KEY reset_token (reset_token),
			KEY dropped_date (dropped_date)
		) {$charset_collate};";

		$sql[] = "CREATE TABLE {$p}tsm_teachers (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			first_name VARCHAR(100) NOT NULL,
			last_name VARCHAR(100) NOT NULL,
			email VARCHAR(190) NULL,
			phone VARCHAR(40) NULL,
			username VARCHAR(60) NULL,
			password_hash VARCHAR(255) NULL,
			photo_url VARCHAR(255) NULL,
			status VARCHAR(20) NOT NULL DEFAULT 'active',
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			UNIQUE KEY email (email),
			UNIQUE KEY username (username)
		) {$charset_collate};";

		$sql[] = "CREATE TABLE {$p}tsm_classes (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			school_year_id BIGINT UNSIGNED NOT NULL,
			name VARCHAR(190) NOT NULL,
			description TEXT NULL,
			grading_method VARCHAR(30) NOT NULL DEFAULT 'points',
			grading_config LONGTEXT NULL,
			teacher_id BIGINT UNSIGNED NULL,
			status VARCHAR(20) NOT NULL DEFAULT 'active',
			passing_pct DECIMAL(5,2) NOT NULL DEFAULT 70.00,
			credits DECIMAL(6,2) NOT NULL DEFAULT 0,
			completed_at DATETIME NULL,
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY school_year_id (school_year_id),
			KEY teacher_id (teacher_id)
		) {$charset_collate};";

		$sql[] = "CREATE TABLE {$p}tsm_enrollments (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			class_id BIGINT UNSIGNED NOT NULL,
			student_id BIGINT UNSIGNED NOT NULL,
			enrolled_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			status VARCHAR(20) NOT NULL DEFAULT 'active',
			PRIMARY KEY (id),
			UNIQUE KEY class_student (class_id, student_id)
		) {$charset_collate};";

		$sql[] = "CREATE TABLE {$p}tsm_class_teachers (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			class_id BIGINT UNSIGNED NOT NULL,
			teacher_id BIGINT UNSIGNED NOT NULL,
			PRIMARY KEY (id),
			UNIQUE KEY class_teacher (class_id, teacher_id),
			KEY teacher_id (teacher_id)
		) {$charset_collate};";

		$sql[] = "CREATE TABLE {$p}tsm_assignments (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			class_id BIGINT UNSIGNED NOT NULL,
			school_year_id BIGINT UNSIGNED NOT NULL,
			title VARCHAR(190) NOT NULL,
			description LONGTEXT NULL,
			category VARCHAR(120) NULL,
			assigned_date DATE NULL,
			due_date DATE NULL,
			points_possible DECIMAL(10,2) NOT NULL DEFAULT 0,
			extra_credit TINYINT(1) NOT NULL DEFAULT 0,
			required TINYINT(1) NOT NULL DEFAULT 1,
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY class_id (class_id),
			KEY school_year_id (school_year_id)
		) {$charset_collate};";

		$sql[] = "CREATE TABLE {$p}tsm_grades (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			assignment_id BIGINT UNSIGNED NOT NULL,
			student_id BIGINT UNSIGNED NOT NULL,
			status VARCHAR(24) NOT NULL DEFAULT '',
			points_earned DECIMAL(10,2) NULL,
			bonus DECIMAL(10,2) NULL,
			excused TINYINT(1) NOT NULL DEFAULT 0,
			late_penalty DECIMAL(6,2) NULL,
			submitted_date DATE NULL,
			comments TEXT NULL,
			updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			UNIQUE KEY assign_student (assignment_id, student_id),
			KEY status (status)
		) {$charset_collate};";

		// Pro tables  -  created up-front so Pro install is seamless.
		$sql[] = "CREATE TABLE {$p}tsm_attendance (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			class_id BIGINT UNSIGNED NOT NULL,
			student_id BIGINT UNSIGNED NOT NULL,
			date DATE NOT NULL,
			status VARCHAR(20) NOT NULL DEFAULT 'present',
			notes TEXT NULL,
			PRIMARY KEY (id),
			UNIQUE KEY class_student_date (class_id, student_id, date)
		) {$charset_collate};";

		$sql[] = "CREATE TABLE {$p}tsm_payments (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			student_id BIGINT UNSIGNED NOT NULL,
			amount DECIMAL(10,2) NOT NULL DEFAULT 0,
			payment_date DATE NULL,
			payment_type VARCHAR(60) NULL,
			notes TEXT NULL,
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY student_id (student_id)
		) {$charset_collate};";

		$sql[] = "CREATE TABLE {$p}tsm_notes (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			student_id BIGINT UNSIGNED NOT NULL,
			author_id BIGINT UNSIGNED NULL,
			body LONGTEXT NULL,
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY student_id (student_id)
		) {$charset_collate};";

		$sql[] = "CREATE TABLE {$p}tsm_audit_log (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			user_id BIGINT UNSIGNED NULL,
			action VARCHAR(60) NOT NULL,
			object_type VARCHAR(60) NOT NULL,
			object_id BIGINT UNSIGNED NULL,
			details LONGTEXT NULL,
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY object (object_type, object_id)
		) {$charset_collate};";

		$sql[] = "CREATE TABLE {$p}tsm_custom_fields (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			label VARCHAR(190) NOT NULL,
			meta_key VARCHAR(120) NOT NULL,
			field_type VARCHAR(30) NOT NULL DEFAULT 'text',
			options LONGTEXT NULL,
			required TINYINT(1) NOT NULL DEFAULT 0,
			visibility VARCHAR(20) NOT NULL DEFAULT 'both',
			sort_order INT NOT NULL DEFAULT 0,
			PRIMARY KEY (id),
			UNIQUE KEY meta_key (meta_key)
		) {$charset_collate};";

		$sql[] = "CREATE TABLE {$p}tsm_custom_field_values (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			field_id BIGINT UNSIGNED NOT NULL,
			student_id BIGINT UNSIGNED NOT NULL,
			value LONGTEXT NULL,
			PRIMARY KEY (id),
			UNIQUE KEY field_student (field_id, student_id)
		) {$charset_collate};";

		// Invoicing system (v1.6.0).
		$sql[] = "CREATE TABLE {$p}tsm_invoices (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			invoice_number VARCHAR(40) NOT NULL,
			student_id BIGINT UNSIGNED NOT NULL,
			class_id BIGINT UNSIGNED NULL,
			context_type VARCHAR(40) NULL,
			context_id BIGINT UNSIGNED NULL,
			status VARCHAR(20) NOT NULL DEFAULT 'draft',
			affects_balance TINYINT(1) NOT NULL DEFAULT 1,
			issue_date DATE NULL,
			due_date DATE NULL,
			subtotal DECIMAL(10,2) NOT NULL DEFAULT 0,
			tax_rate DECIMAL(6,3) NOT NULL DEFAULT 0,
			tax_amount DECIMAL(10,2) NOT NULL DEFAULT 0,
			discount_amount DECIMAL(10,2) NOT NULL DEFAULT 0,
			late_fee DECIMAL(10,2) NOT NULL DEFAULT 0,
			total DECIMAL(10,2) NOT NULL DEFAULT 0,
			amount_paid DECIMAL(10,2) NOT NULL DEFAULT 0,
			balance_due DECIMAL(10,2) NOT NULL DEFAULT 0,
			currency VARCHAR(8) NOT NULL DEFAULT 'USD',
			notes TEXT NULL,
			memo TEXT NULL,
			reminder_schedule VARCHAR(190) NULL,
			payment_plan_id BIGINT UNSIGNED NULL,
			parent_invoice_id BIGINT UNSIGNED NULL,
			program_id BIGINT UNSIGNED NULL,
			program_enrollment_id BIGINT UNSIGNED NULL,
			invoice_type VARCHAR(20) NOT NULL DEFAULT 'invoice',
			late_fee_applied TINYINT(1) NOT NULL DEFAULT 0,
			created_by BIGINT UNSIGNED NULL,
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			UNIQUE KEY invoice_number (invoice_number),
			KEY student_id (student_id),
			KEY status (status),
			KEY due_date (due_date),
			KEY program_id (program_id),
			KEY program_enrollment_id (program_enrollment_id),
			KEY invoice_context (context_type, context_id)
		) {$charset_collate};";

		$sql[] = "CREATE TABLE {$p}tsm_invoice_items (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			invoice_id BIGINT UNSIGNED NOT NULL,
			description VARCHAR(255) NOT NULL,
			quantity DECIMAL(10,2) NOT NULL DEFAULT 1,
			unit_price DECIMAL(10,2) NOT NULL DEFAULT 0,
			line_total DECIMAL(10,2) NOT NULL DEFAULT 0,
			sort_order INT NOT NULL DEFAULT 0,
			PRIMARY KEY (id),
			KEY invoice_id (invoice_id)
		) {$charset_collate};";

		$sql[] = "CREATE TABLE {$p}tsm_invoice_payments (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			invoice_id BIGINT UNSIGNED NULL,
			student_id BIGINT UNSIGNED NOT NULL,
			amount DECIMAL(10,2) NOT NULL DEFAULT 0,
			payment_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			method VARCHAR(40) NOT NULL DEFAULT 'manual',
			reference VARCHAR(190) NULL,
			stripe_payment_intent VARCHAR(190) NULL,
			stripe_charge_id VARCHAR(190) NULL,
			notes TEXT NULL,
			refunded_amount DECIMAL(10,2) NOT NULL DEFAULT 0,
			created_by BIGINT UNSIGNED NULL,
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY invoice_id (invoice_id),
			KEY student_id (student_id),
			KEY stripe_payment_intent (stripe_payment_intent)
		) {$charset_collate};";

		$sql[] = "CREATE TABLE {$p}tsm_invoice_reminders (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			invoice_id BIGINT UNSIGNED NOT NULL,
			days_offset INT NOT NULL DEFAULT 0,
			scheduled_date DATE NOT NULL,
			sent_at DATETIME NULL,
			status VARCHAR(20) NOT NULL DEFAULT 'pending',
			PRIMARY KEY (id),
			UNIQUE KEY inv_offset (invoice_id, days_offset),
			KEY scheduled_date (scheduled_date)
		) {$charset_collate};";

		$sql[] = "CREATE TABLE {$p}tsm_payment_plans (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			student_id BIGINT UNSIGNED NOT NULL,
			name VARCHAR(190) NULL,
			total_amount DECIMAL(10,2) NOT NULL DEFAULT 0,
			installments INT NOT NULL DEFAULT 1,
			frequency VARCHAR(20) NOT NULL DEFAULT 'monthly',
			start_date DATE NULL,
			status VARCHAR(20) NOT NULL DEFAULT 'active',
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY student_id (student_id)
		) {$charset_collate};";

		// Programs (v1.7.0; v1.10.18 added start/end dates + due date policy).
		$sql[] = "CREATE TABLE {$p}tsm_programs (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			name VARCHAR(190) NOT NULL,
			code VARCHAR(60) NULL,
			description LONGTEXT NULL,
			total_cost DECIMAL(10,2) NOT NULL DEFAULT 0,
			default_billing VARCHAR(20) NOT NULL DEFAULT 'manual',
			default_installments INT NOT NULL DEFAULT 1,
			default_frequency VARCHAR(20) NOT NULL DEFAULT 'monthly',
			default_fee_mode VARCHAR(20) NOT NULL DEFAULT 'none',
			default_fee_value DECIMAL(10,3) NOT NULL DEFAULT 0,
			default_deposit DECIMAL(10,2) NOT NULL DEFAULT 0,
			start_date DATE NULL,
			end_date DATE NULL,
			due_date_policy VARCHAR(30) NOT NULL DEFAULT 'on_start',
			due_date_offset_days INT NOT NULL DEFAULT 0,
			default_application_fee DECIMAL(10,2) NOT NULL DEFAULT 0,
			passing_pct DECIMAL(5,2) NOT NULL DEFAULT 70.00,
			completed_at DATETIME NULL,
			status VARCHAR(20) NOT NULL DEFAULT 'active',
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY status (status)
		) {$charset_collate};";

		// Many-to-many: classes assigned to a program (classes still belong to a school year).
		$sql[] = "CREATE TABLE {$p}tsm_program_classes (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			program_id BIGINT UNSIGNED NOT NULL,
			class_id BIGINT UNSIGNED NOT NULL,
			sort_order INT NOT NULL DEFAULT 0,
			PRIMARY KEY (id),
			UNIQUE KEY program_class (program_id, class_id),
			KEY class_id (class_id)
		) {$charset_collate};";

		// Student enrollment in a program (concurrent enrollments allowed).
		$sql[] = "CREATE TABLE {$p}tsm_program_enrollments (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			program_id BIGINT UNSIGNED NOT NULL,
			student_id BIGINT UNSIGNED NOT NULL,
			school_year_id BIGINT UNSIGNED NULL,
			tuition_amount DECIMAL(10,2) NOT NULL DEFAULT 0,
			billing_mode VARCHAR(20) NOT NULL DEFAULT 'manual',
			installments INT NOT NULL DEFAULT 1,
			frequency VARCHAR(20) NOT NULL DEFAULT 'monthly',
			deposit DECIMAL(10,2) NOT NULL DEFAULT 0,
			fee_mode VARCHAR(20) NOT NULL DEFAULT 'none',
			fee_value DECIMAL(10,3) NOT NULL DEFAULT 0,
			first_payment_date DATE NULL,
			contract_id BIGINT UNSIGNED NULL,
			status VARCHAR(20) NOT NULL DEFAULT 'active',
			start_date DATE NULL,
			end_date DATE NULL,
			notes TEXT NULL,
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY program_id (program_id),
			KEY student_id (student_id),
			KEY school_year_id (school_year_id),
			KEY status (status)
		) {$charset_collate};";

		foreach ( $sql as $statement ) {
			dbDelta( $statement );
		}

		// v1.7.0: ensure invoices link back to programs/enrollments.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$inv = $p . 'tsm_invoices';
		$cols = $wpdb->get_col( "SHOW COLUMNS FROM {$inv}" );
		if ( is_array( $cols ) && ! in_array( 'program_id', $cols, true ) ) {
			$wpdb->query( "ALTER TABLE {$inv} ADD COLUMN program_id BIGINT UNSIGNED NULL, ADD KEY program_id (program_id)" );
		}
		if ( is_array( $cols ) && ! in_array( 'program_enrollment_id', $cols, true ) ) {
			$wpdb->query( "ALTER TABLE {$inv} ADD COLUMN program_enrollment_id BIGINT UNSIGNED NULL, ADD KEY program_enrollment_id (program_enrollment_id)" );
		}
		if ( is_array( $cols ) && ! in_array( 'context_type', $cols, true ) ) {
			$wpdb->query( "ALTER TABLE {$inv} ADD COLUMN context_type VARCHAR(40) NULL, ADD COLUMN context_id BIGINT UNSIGNED NULL, ADD KEY invoice_context (context_type, context_id)" );
		} elseif ( is_array( $cols ) && ! in_array( 'context_id', $cols, true ) ) {
			$wpdb->query( "ALTER TABLE {$inv} ADD COLUMN context_id BIGINT UNSIGNED NULL, ADD KEY invoice_context (context_type, context_id)" );
		}

		// v1.7.0: drop legacy per-student tuition (Programs are now the source of truth).
		$stu = $p . 'tsm_students';
		$stu_cols = $wpdb->get_col( "SHOW COLUMNS FROM {$stu}" );
		if ( is_array( $stu_cols ) && in_array( 'total_program_cost', $stu_cols, true ) ) {
			$wpdb->query( "ALTER TABLE {$stu} DROP COLUMN total_program_cost" );
		}
		$pay = $p . 'tsm_payments';
		$pay_cols = $wpdb->get_col( "SHOW COLUMNS FROM {$pay}" );
		if ( is_array( $pay_cols ) && in_array( 'total_program_cost', $pay_cols, true ) ) {
			$wpdb->query( "ALTER TABLE {$pay} DROP COLUMN total_program_cost" );
		}

		// v1.19.36  -  password reset token columns on tsm_students for upgraded installs.
		$stu_cols2 = $wpdb->get_col( "SHOW COLUMNS FROM {$stu}" );
		if ( is_array( $stu_cols2 ) ) {
			if ( ! in_array( 'reset_token', $stu_cols2, true ) ) {
				$wpdb->query( "ALTER TABLE {$stu} ADD COLUMN reset_token VARCHAR(64) NULL, ADD KEY reset_token (reset_token)" );
			}
			if ( ! in_array( 'reset_expires', $stu_cols2, true ) ) {
				$wpdb->query( "ALTER TABLE {$stu} ADD COLUMN reset_expires DATETIME NULL" );
			}
		}


		// v1.10.18: ensure tsm_programs has the new date + due-date-policy columns
		// on upgraded installs (dbDelta cannot always add columns on existing tables).
		$prog = $p . 'tsm_programs';
		$prog_cols = $wpdb->get_col( "SHOW COLUMNS FROM {$prog}" );
		if ( is_array( $prog_cols ) ) {
			if ( ! in_array( 'start_date', $prog_cols, true ) ) {
				$wpdb->query( "ALTER TABLE {$prog} ADD COLUMN start_date DATE NULL" );
			}
			if ( ! in_array( 'end_date', $prog_cols, true ) ) {
				$wpdb->query( "ALTER TABLE {$prog} ADD COLUMN end_date DATE NULL" );
			}
			if ( ! in_array( 'due_date_policy', $prog_cols, true ) ) {
				$wpdb->query( "ALTER TABLE {$prog} ADD COLUMN due_date_policy VARCHAR(30) NOT NULL DEFAULT 'on_start'" );
			}
			if ( ! in_array( 'due_date_offset_days', $prog_cols, true ) ) {
				$wpdb->query( "ALTER TABLE {$prog} ADD COLUMN due_date_offset_days INT NOT NULL DEFAULT 0" );
			}
			// v1.15.0: financing-fee + deposit defaults missed from the
			// original CREATE TABLE. Without these, INSERT into tsm_programs
			// silently failed on fresh installs and "create program" never
			// produced a row. Add them on upgrade for existing installs too.
			if ( ! in_array( 'default_fee_mode', $prog_cols, true ) ) {
				$wpdb->query( "ALTER TABLE {$prog} ADD COLUMN default_fee_mode VARCHAR(20) NOT NULL DEFAULT 'none'" );
			}
			if ( ! in_array( 'default_fee_value', $prog_cols, true ) ) {
				$wpdb->query( "ALTER TABLE {$prog} ADD COLUMN default_fee_value DECIMAL(10,3) NOT NULL DEFAULT 0" );
			}
			if ( ! in_array( 'default_deposit', $prog_cols, true ) ) {
				$wpdb->query( "ALTER TABLE {$prog} ADD COLUMN default_deposit DECIMAL(10,2) NOT NULL DEFAULT 0" );
			}
		}

		// v1.17.0  -  completion columns on classes + programs.
		$cls = $p . 'tsm_classes';
		$cls_cols = $wpdb->get_col( "SHOW COLUMNS FROM {$cls}" );
		if ( is_array( $cls_cols ) ) {
			foreach ( array( 'passing_pct' => "DECIMAL(5,2) NOT NULL DEFAULT 70.00", 'credits' => "DECIMAL(6,2) NOT NULL DEFAULT 0", 'completed_at' => "DATETIME NULL" ) as $c => $def ) {
				if ( ! in_array( $c, $cls_cols, true ) ) {
					$wpdb->query( "ALTER TABLE {$cls} ADD COLUMN {$c} {$def}" );
				}
			}
		}
		$prg = $p . 'tsm_programs';
		$prg_cols2 = $wpdb->get_col( "SHOW COLUMNS FROM {$prg}" );
		if ( is_array( $prg_cols2 ) ) {
			foreach ( array( 'passing_pct' => "DECIMAL(5,2) NOT NULL DEFAULT 70.00", 'completed_at' => "DATETIME NULL" ) as $c => $def ) {
				if ( ! in_array( $c, $prg_cols2, true ) ) {
					$wpdb->query( "ALTER TABLE {$prg} ADD COLUMN {$c} {$def}" );
				}
			}
		}
		// v1.19.32  -  status-driven gradebook on tsm_grades.
		$gr = $p . 'tsm_grades';
		$gr_cols = $wpdb->get_col( "SHOW COLUMNS FROM {$gr}" );
		if ( is_array( $gr_cols ) ) {
			if ( ! in_array( 'status', $gr_cols, true ) ) {
				$wpdb->query( "ALTER TABLE {$gr} ADD COLUMN status VARCHAR(24) NOT NULL DEFAULT '' AFTER student_id, ADD KEY status (status)" );
			}
			if ( ! in_array( 'bonus', $gr_cols, true ) ) {
				$wpdb->query( "ALTER TABLE {$gr} ADD COLUMN bonus DECIMAL(10,2) NULL AFTER points_earned" );
			}
			if ( ! in_array( 'submitted_date', $gr_cols, true ) ) {
				$wpdb->query( "ALTER TABLE {$gr} ADD COLUMN submitted_date DATE NULL AFTER late_penalty" );
			}
			// Backfill status for legacy rows: excused -> excused; has score -> completed_ontime.
			$wpdb->query( "UPDATE {$gr} SET status='excused' WHERE status='' AND excused=1" );
			$wpdb->query( "UPDATE {$gr} SET status='completed_ontime' WHERE status='' AND points_earned IS NOT NULL" );
		}
		// phpcs:enable

		// v1.19.66  -  teacher profile photo.
		$tch = $p . 'tsm_teachers';
		$tch_cols = $wpdb->get_col( "SHOW COLUMNS FROM {$tch}" ); // phpcs:ignore
		if ( is_array( $tch_cols ) && ! in_array( 'photo_url', $tch_cols, true ) ) {
			$wpdb->query( "ALTER TABLE {$tch} ADD COLUMN photo_url VARCHAR(255) NULL AFTER password_hash" ); // phpcs:ignore
		}

		self::normalize_unique_nulls();

		// Announcements (v1.9.5+  -  restored in 1.10.2).
		if ( class_exists( 'TSM_Announcements' ) ) {
			TSM_Announcements::install_schema();
		}

		// Application Forms (v1.10.5).
		if ( class_exists( 'TSM_Applications' ) ) {
			TSM_Applications::install_schema();
		}

		// Workshops (v1.10.19).
		if ( class_exists( 'TSM_Workshops' ) ) {
			TSM_Workshops::install_schema();
		}

		// Tuition Contracts (v1.10.26).
		if ( class_exists( 'TSM_Contracts' ) ) {
			TSM_Contracts::install_schema();
		}

		// Student Documents (v1.10.28).
		if ( class_exists( 'TSM_Student_Docs' ) ) {
			TSM_Student_Docs::install_schema();
		}

		// Parent Portal (v1.14.0).
		if ( class_exists( 'TSM_Parents' ) ) {
			TSM_Parents::install_schema();
		}

		// v1.10.26: program loan defaults (legacy APR columns kept for back-compat
		// but no longer used). v1.10.27: switched to flat-fee model.
		$prog_cols2 = $wpdb->get_col( "SHOW COLUMNS FROM {$prog}" ); // phpcs:ignore
		if ( is_array( $prog_cols2 ) ) {
			if ( ! in_array( 'default_apr', $prog_cols2, true ) ) {
				$wpdb->query( "ALTER TABLE {$prog} ADD COLUMN default_apr DECIMAL(6,3) NOT NULL DEFAULT 0" ); // phpcs:ignore
			}
			if ( ! in_array( 'default_interest_mode', $prog_cols2, true ) ) {
				$wpdb->query( "ALTER TABLE {$prog} ADD COLUMN default_interest_mode VARCHAR(20) NOT NULL DEFAULT 'none'" ); // phpcs:ignore
			}
			if ( ! in_array( 'default_deposit', $prog_cols2, true ) ) {
				$wpdb->query( "ALTER TABLE {$prog} ADD COLUMN default_deposit DECIMAL(10,2) NOT NULL DEFAULT 0" ); // phpcs:ignore
			}
			// v1.10.27  -  flat financing fee defaults.
			if ( ! in_array( 'default_fee_mode', $prog_cols2, true ) ) {
				$wpdb->query( "ALTER TABLE {$prog} ADD COLUMN default_fee_mode VARCHAR(20) NOT NULL DEFAULT 'none'" ); // phpcs:ignore
			}
			if ( ! in_array( 'default_fee_value', $prog_cols2, true ) ) {
				$wpdb->query( "ALTER TABLE {$prog} ADD COLUMN default_fee_value DECIMAL(10,3) NOT NULL DEFAULT 0" ); // phpcs:ignore
			}
			// v1.10.29  -  per-program application fee.
			if ( ! in_array( 'default_application_fee', $prog_cols2, true ) ) {
				$wpdb->query( "ALTER TABLE {$prog} ADD COLUMN default_application_fee DECIMAL(10,2) NOT NULL DEFAULT 0" ); // phpcs:ignore
			}
		}

		// v1.10.29  -  enrollment now stores the contract terms so admins can
		// edit/sync them after the fact (tuition adjustments + payment plan
		// regeneration + sync from a signed tuition contract).
		$enr = $p . 'tsm_program_enrollments';
		$enr_cols = $wpdb->get_col( "SHOW COLUMNS FROM {$enr}" ); // phpcs:ignore
		if ( is_array( $enr_cols ) ) {
			$add = array(
				'installments'        => "INT NOT NULL DEFAULT 1",
				'frequency'           => "VARCHAR(20) NOT NULL DEFAULT 'monthly'",
				'deposit'             => "DECIMAL(10,2) NOT NULL DEFAULT 0",
				'fee_mode'            => "VARCHAR(20) NOT NULL DEFAULT 'none'",
				'fee_value'           => "DECIMAL(10,3) NOT NULL DEFAULT 0",
				'first_payment_date'  => "DATE NULL",
				'contract_id'         => "BIGINT UNSIGNED NULL",
			);
			foreach ( $add as $col => $def ) {
				if ( ! in_array( $col, $enr_cols, true ) ) {
					$wpdb->query( "ALTER TABLE {$enr} ADD COLUMN {$col} {$def}" ); // phpcs:ignore
				}
			}
		}
		$contracts_tbl  = $wpdb->prefix . 'tsm_contracts';
		$contract_cols  = $wpdb->get_col( "SHOW COLUMNS FROM {$contracts_tbl}" ); // phpcs:ignore
		if ( is_array( $contract_cols ) ) {
			if ( ! in_array( 'fee_mode', $contract_cols, true ) ) {
				$wpdb->query( "ALTER TABLE {$contracts_tbl} ADD COLUMN fee_mode VARCHAR(20) NOT NULL DEFAULT 'none' AFTER amount_financed" ); // phpcs:ignore
			}
			if ( ! in_array( 'fee_value', $contract_cols, true ) ) {
				$wpdb->query( "ALTER TABLE {$contracts_tbl} ADD COLUMN fee_value DECIMAL(10,3) NOT NULL DEFAULT 0 AFTER fee_mode" ); // phpcs:ignore
			}
			if ( ! in_array( 'finance_fee', $contract_cols, true ) ) {
				$wpdb->query( "ALTER TABLE {$contracts_tbl} ADD COLUMN finance_fee DECIMAL(10,2) NOT NULL DEFAULT 0 AFTER fee_value" ); // phpcs:ignore
			}
		}

		update_option( 'tsm_db_version', TSM_DB_VERSION );
	}

	/**
	 * Convert empty-string values in UNIQUE columns to NULL so multiple
	 * records without an email/username can coexist. Older installs stored
	 * '' which collides on the UNIQUE index after the first row.
	 */
	public static function normalize_unique_nulls() {
		global $wpdb;
		$p = $wpdb->prefix;
		// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$wpdb->query( "UPDATE {$p}tsm_students SET email = NULL WHERE email = ''" );
		$wpdb->query( "UPDATE {$p}tsm_students SET username = NULL WHERE username = ''" );
		$wpdb->query( "UPDATE {$p}tsm_teachers SET email = NULL WHERE email = ''" );
		$wpdb->query( "UPDATE {$p}tsm_teachers SET username = NULL WHERE username = ''" );
		// phpcs:enable
	}

	/**
	 * Run upgrades when plugin version changes.
	 */
	public static function maybe_upgrade() {
		$installed = get_option( 'tsm_db_version' );
		if ( $installed !== TSM_DB_VERSION ) {
			self::install();
		}
	}
}
