-- جدول حضور الطلاب (12 محاضرة لكل مادة في الفصل الدراسي)
-- Student Attendance: one row per student per course per semester, 12 lectures

CREATE TABLE IF NOT EXISTS attendance (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    semester_id INT NOT NULL,
    lecture_1 TINYINT(1) NULL COMMENT '1=حاضر, 0=غائب, NULL=لم يُسجّل',
    lecture_2 TINYINT(1) NULL,
    lecture_3 TINYINT(1) NULL,
    lecture_4 TINYINT(1) NULL,
    lecture_5 TINYINT(1) NULL,
    lecture_6 TINYINT(1) NULL,
    lecture_7 TINYINT(1) NULL,
    lecture_8 TINYINT(1) NULL,
    lecture_9 TINYINT(1) NULL,
    lecture_10 TINYINT(1) NULL,
    lecture_11 TINYINT(1) NULL,
    lecture_12 TINYINT(1) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_attendance (student_id, course_id, semester_id),
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
    FOREIGN KEY (semester_id) REFERENCES semesters(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
