-- جدول النتائج المنشورة على الموقع
-- Published Results Table

CREATE TABLE IF NOT EXISTS published_results (
    id INT PRIMARY KEY AUTO_INCREMENT,
    semester_id INT NOT NULL,
    published_by INT NOT NULL,
    published_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (semester_id) REFERENCES semesters(id) ON DELETE CASCADE,
    FOREIGN KEY (published_by) REFERENCES users(id) ON DELETE RESTRICT,
    UNIQUE KEY unique_semester_published (semester_id),
    INDEX idx_expires_at (expires_at),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

