MAINHACK

Viewing / Editing File: 20250811151102_jolly_oasis.sql

-- Arabic Quiz Competition Database Structure

CREATE DATABASE IF NOT EXISTS arabic_quiz CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE arabic_quiz;

-- Users table (Admin, Teachers, Students)
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    full_name VARCHAR(200) NOT NULL,
    email VARCHAR(150),
    role ENUM('admin', 'teacher', 'student') NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Educational stages
CREATE TABLE stages (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Grades
CREATE TABLE grades (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    stage_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (stage_id) REFERENCES stages(id) ON DELETE CASCADE
);

-- Subjects
CREATE TABLE subjects (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Teacher assignments
CREATE TABLE teacher_assignments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    teacher_id INT,
    grade_id INT,
    subject_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (teacher_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (grade_id) REFERENCES grades(id) ON DELETE CASCADE,
    FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE
);

-- Student lists
CREATE TABLE student_lists (
    id INT PRIMARY KEY AUTO_INCREMENT,
    teacher_id INT,
    grade_id INT,
    student_name VARCHAR(200) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (teacher_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (grade_id) REFERENCES grades(id) ON DELETE CASCADE
);

-- Competitions
CREATE TABLE competitions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    teacher_id INT,
    grade_id INT,
    subject_id INT,
    competition_type ENUM('group', 'millionaire') NOT NULL,
    num_groups INT DEFAULT 0,
    is_public BOOLEAN DEFAULT FALSE,
    status ENUM('draft', 'active', 'completed') DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (teacher_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (grade_id) REFERENCES grades(id) ON DELETE CASCADE,
    FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE
);

-- Questions for Group competition
CREATE TABLE group_questions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    competition_id INT,
    question_text TEXT,
    answer1 VARCHAR(500),
    answer2 VARCHAR(500),
    answer3 VARCHAR(500),
    answer4 VARCHAR(500),
    correct_answer INT,
    question_image VARCHAR(255),
    card_type ENUM('question', 'bonus', 'deduction', 'steal') DEFAULT 'question',
    points_value INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (competition_id) REFERENCES competitions(id) ON DELETE CASCADE
);

-- Questions for Millionaire competition
CREATE TABLE millionaire_questions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    competition_id INT,
    question_level INT NOT NULL,
    question_text TEXT NOT NULL,
    answer1 VARCHAR(500) NOT NULL,
    answer2 VARCHAR(500) NOT NULL,
    answer3 VARCHAR(500) NOT NULL,
    answer4 VARCHAR(500) NOT NULL,
    correct_answer INT NOT NULL,
    question_image VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (competition_id) REFERENCES competitions(id) ON DELETE CASCADE
);

-- Game results
CREATE TABLE game_results (
    id INT PRIMARY KEY AUTO_INCREMENT,
    competition_id INT,
    student_name VARCHAR(200),
    group_name VARCHAR(100),
    final_score INT DEFAULT 0,
    game_type ENUM('group', 'millionaire'),
    completed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (competition_id) REFERENCES competitions(id) ON DELETE CASCADE
);

-- System settings
CREATE TABLE settings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Insert default admin user
INSERT INTO users (username, password, full_name, role) 
VALUES ('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'مدير النظام', 'admin');

-- Insert default settings
INSERT INTO settings (setting_key, setting_value) VALUES 
('site_logo', 'assets/images/logo.png'),
('site_name', 'منصة المسابقات التعليمية');

-- Insert sample data
INSERT INTO stages (name) VALUES 
('المرحلة الابتدائية'),
('المرحلة المتوسطة'),
('المرحلة الثانوية');

INSERT INTO grades (name, stage_id) VALUES 
('الصف الأول', 1),
('الصف الثاني', 1),
('الصف الثالث', 1),
('الصف الرابع', 1),
('الصف الخامس', 1),
('الصف السادس', 1),
('الصف الأول متوسط', 2),
('الصف الثاني متوسط', 2),
('الصف الثالث متوسط', 2),
('الصف الأول ثانوي', 3),
('الصف الثاني ثانوي', 3),
('الصف الثالث ثانوي', 3);

INSERT INTO subjects (name) VALUES 
('الرياضيات'),
('اللغة العربية'),
('العلوم'),
('التاريخ'),
('الجغرافيا'),
('اللغة الإنجليزية'),
('التربية الإسلامية'),
('الفيزياء'),
('الكيمياء'),
('الأحياء');

Cancel / Back

[ MAINHACK ]

Viewing / Editing File: 20250811151102_jolly_oasis.sql

Server Info

System Features

User Info