USE obaasima_db;

CREATE TABLE IF NOT EXISTS course_reviews (
    id int(11) NOT NULL AUTO_INCREMENT,
    course_id int(11) NOT NULL,
    user_id int(11) NOT NULL,
    rating int(11) NOT NULL CHECK (rating >= 1 AND rating <= 5),
    review text DEFAULT NULL,
    created_at timestamp NOT NULL DEFAULT current_timestamp(),
    updated_at timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
    PRIMARY KEY (id),
    UNIQUE KEY unique_user_course_review (user_id, course_id),
    KEY course_id (course_id),
    CONSTRAINT course_reviews_ibfk_1 FOREIGN KEY (course_id) REFERENCES courses (id) ON DELETE CASCADE,
    CONSTRAINT course_reviews_ibfk_2 FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;