-- Create grade_subject_rules table
CREATE TABLE grade_subject_rules (
    id INT PRIMARY KEY AUTO_INCREMENT,
    grade_id INT NOT NULL,
    subject_id INT NOT NULL,
    min_score DECIMAL(5,2) NOT NULL,
    max_score DECIMAL(5,2) NOT NULL,
    enabled BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Foreign key constraints
    FOREIGN KEY (grade_id) REFERENCES grades(id) ON DELETE RESTRICT,
    FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE RESTRICT,
    
    -- Unique constraint: one rule per grade-subject combination
    UNIQUE KEY unique_grade_subject (grade_id, subject_id),
    
    -- Validation constraints
    CHECK (min_score >= 0),
    CHECK (max_score >= min_score),
    CHECK (max_score <= 999.99)
);

-- Create indexes for better performance
CREATE INDEX idx_grade_subject_rules_grade_id ON grade_subject_rules(grade_id);
CREATE INDEX idx_grade_subject_rules_subject_id ON grade_subject_rules(subject_id);
CREATE INDEX idx_grade_subject_rules_enabled ON grade_subject_rules(enabled);