-- إضافة أعمدة لحفظ الدرجات المعايرة في جدول grades
-- Add columns to store calibrated grades in grades table

ALTER TABLE grades
ADD COLUMN calibrated_marks DECIMAL(5,2) NULL AFTER points,
ADD COLUMN calibrated_letter_grade VARCHAR(5) NULL AFTER calibrated_marks,
ADD COLUMN calibrated_points DECIMAL(3,2) NULL AFTER calibrated_letter_grade,
ADD COLUMN calibration_target_mean DECIMAL(5,2) NULL AFTER calibrated_points,
ADD COLUMN calibration_target_std DECIMAL(5,2) NULL AFTER calibration_target_mean,
ADD COLUMN calibrated_at TIMESTAMP NULL AFTER calibration_target_std,
ADD COLUMN calibrated_by INT NULL AFTER calibrated_at,
ADD FOREIGN KEY (calibrated_by) REFERENCES users(id) ON DELETE SET NULL;

-- إضافة جدول لحفظ إحصائيات المعايرة لكل فصل دراسي
-- Add table to store calibration statistics for each semester

CREATE TABLE IF NOT EXISTS semester_calibrations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    semester_id INT NOT NULL,
    target_mean DECIMAL(5,2) NOT NULL,
    target_std DECIMAL(5,2) NOT NULL,
    calibrated_by INT NOT NULL,
    calibrated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (semester_id) REFERENCES semesters(id) ON DELETE CASCADE,
    FOREIGN KEY (calibrated_by) REFERENCES users(id) ON DELETE RESTRICT,
    UNIQUE KEY unique_semester_calibration (semester_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

