-- نظام نتائج الجامعة - قاعدة البيانات
-- University Results System Database

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

-- جدول الكليات
CREATE TABLE colleges (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name_ar VARCHAR(255) NOT NULL,
    name_en VARCHAR(255),
    code VARCHAR(50) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول التخصصات
-- is_general=1: تخصص "الفصول العامة قبل التشعيب" (مشترك لجميع التخصصات) - للكليات التي بها تشعيب فقط
CREATE TABLE majors (
    id INT PRIMARY KEY AUTO_INCREMENT,
    college_id INT NOT NULL,
    name_ar VARCHAR(80) NOT NULL,
    name_en VARCHAR(80),
    code VARCHAR(50) NOT NULL,
    total_semesters INT NOT NULL DEFAULT 8,
    is_general TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1=فصول عامة قبل التشعيب مشتركة لجميع التخصصات',
    FOREIGN KEY (college_id) REFERENCES colleges(id) ON DELETE CASCADE,
    UNIQUE KEY unique_major_code (college_id, code),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول الفصول الدراسية
CREATE TABLE semesters (
    id INT PRIMARY KEY AUTO_INCREMENT,
    major_id INT NOT NULL,
    semester_number INT NOT NULL,
    academic_year VARCHAR(20) NOT NULL,
    start_date DATE,
    end_date DATE,
    is_active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (major_id) REFERENCES majors(id) ON DELETE CASCADE,
    UNIQUE KEY unique_semester (major_id, semester_number, academic_year),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول المواد الدراسية
CREATE TABLE courses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    major_id INT NOT NULL,
    semester_id INT,
    code VARCHAR(50) NOT NULL,
    name_ar VARCHAR(255) NOT NULL,
    name_en VARCHAR(255),
    credit_hours INT NOT NULL,
    is_required BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (major_id) REFERENCES majors(id) ON DELETE CASCADE,
    FOREIGN KEY (semester_id) REFERENCES semesters(id) ON DELETE SET NULL,
    UNIQUE KEY unique_course_code (major_id, code),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول المستخدمين (موظفين وأعضاء هيئة تدريس)
-- college_id = NULL للمستخدم العام (مثل Administrater) للوصول لجميع الكليات
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    college_id INT NULL,
    username VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    full_name_ar VARCHAR(255) NOT NULL,
    full_name_en VARCHAR(255),
    email VARCHAR(255),
    role ENUM('admin', 'college_admin', 'registrar', 'instructor', 'student') NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (college_id) REFERENCES colleges(id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول الطلاب
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    college_id INT NOT NULL,
    major_id INT NOT NULL,
    student_number VARCHAR(50) UNIQUE NOT NULL,
    national_id VARCHAR(50) UNIQUE,
    full_name_ar VARCHAR(255) NOT NULL,
    full_name_en VARCHAR(255),
    email VARCHAR(255),
    phone VARCHAR(50),
    address TEXT,
    enrollment_date DATE NOT NULL,
    graduation_date DATE,
    status ENUM('active', 'graduated', 'suspended', 'withdrawn') DEFAULT 'active',
    user_id INT,
    FOREIGN KEY (college_id) REFERENCES colleges(id) ON DELETE CASCADE,
    FOREIGN KEY (major_id) REFERENCES majors(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول تسجيل المواد (الطلاب المسجلين في المواد)
CREATE TABLE student_courses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    semester_id INT NOT NULL,
    registration_date DATE NOT NULL,
    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,
    UNIQUE KEY unique_student_course_semester (student_id, course_id, semester_id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول الدرجات
CREATE TABLE grades (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_course_id INT NOT NULL,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    semester_id INT NOT NULL,
    marks DECIMAL(5,2) NOT NULL,
    letter_grade VARCHAR(5),
    points DECIMAL(3,2),
    entered_by INT NOT NULL,
    entered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (student_course_id) REFERENCES student_courses(id) ON DELETE CASCADE,
    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,
    FOREIGN KEY (entered_by) REFERENCES users(id) ON DELETE RESTRICT,
    UNIQUE KEY unique_student_course_grade (student_course_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول المعدلات الفصلية
CREATE TABLE semester_gpas (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    semester_id INT NOT NULL,
    total_credit_hours DECIMAL(5,2) NOT NULL,
    total_points DECIMAL(10,2) NOT NULL,
    gpa DECIMAL(4,2) NOT NULL,
    calculated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    FOREIGN KEY (semester_id) REFERENCES semesters(id) ON DELETE CASCADE,
    UNIQUE KEY unique_student_semester_gpa (student_id, semester_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول المعدل التراكمي
CREATE TABLE cumulative_gpas (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    total_credit_hours DECIMAL(5,2) NOT NULL,
    total_points DECIMAL(10,2) NOT NULL,
    cgpa DECIMAL(4,2) NOT NULL,
    calculated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    UNIQUE KEY unique_student_cgpa (student_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول الرسوم الدراسية
CREATE TABLE fees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    semester_id INT NOT NULL,
    fee_type ENUM('tuition', 'registration', 'other') NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    due_date DATE,
    paid_amount DECIMAL(10,2) DEFAULT 0,
    payment_date DATE,
    payment_status ENUM('pending', 'partial', 'paid') DEFAULT 'pending',
    notes TEXT,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    FOREIGN KEY (semester_id) REFERENCES semesters(id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول طلبات الشهادات
CREATE TABLE certificate_requests (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    certificate_type ENUM('detailed', 'university', 'transcript', 'enrollment', 'graduation') NOT NULL,
    request_date DATE NOT NULL,
    status ENUM('pending', 'approved', 'rejected', 'issued') DEFAULT 'pending',
    rejection_reason TEXT,
    issued_date DATE,
    issued_by INT,
    certificate_number VARCHAR(100),
    notes TEXT,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    FOREIGN KEY (issued_by) REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول الشهادات الصادرة
CREATE TABLE certificates (
    id INT PRIMARY KEY AUTO_INCREMENT,
    certificate_request_id INT NOT NULL,
    student_id INT NOT NULL,
    certificate_type ENUM('detailed', 'university', 'transcript', 'enrollment', 'graduation') NOT NULL,
    certificate_number VARCHAR(100) UNIQUE NOT NULL,
    issue_date DATE NOT NULL,
    pdf_path VARCHAR(500),
    issued_by INT NOT NULL,
    FOREIGN KEY (certificate_request_id) REFERENCES certificate_requests(id) ON DELETE CASCADE,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    FOREIGN KEY (issued_by) REFERENCES users(id) ON DELETE RESTRICT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول الصلاحيات
CREATE TABLE permissions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    role ENUM('admin', 'college_admin', 'registrar', 'instructor', 'student') NOT NULL,
    permission_name VARCHAR(100) NOT NULL,
    description TEXT,
    UNIQUE KEY unique_role_permission (role, permission_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- إدراج بيانات أولية
INSERT INTO colleges (name_ar, name_en, code) VALUES
('كلية الهندسة', 'College of Engineering', 'ENG'),
('كلية العلوم', 'College of Science', 'SCI'),
('كلية الآداب', 'College of Arts', 'ART'),
('كلية التجارة', 'College of Commerce', 'COM');

-- إدراج صلاحيات افتراضية
INSERT INTO permissions (role, permission_name, description) VALUES
('admin', 'all_access', 'وصول كامل للنظام'),
('college_admin', 'manage_college', 'إدارة الكلية'),
('college_admin', 'view_reports', 'عرض التقارير'),
('registrar', 'manage_students', 'إدارة الطلاب'),
('registrar', 'manage_grades', 'إدارة الدرجات'),
('registrar', 'issue_certificates', 'إصدار الشهادات'),
('instructor', 'view_students', 'عرض الطلاب'),
('instructor', 'enter_grades', 'إدخال الدرجات'),
('student', 'view_grades', 'عرض الدرجات'),
('student', 'request_certificates', 'طلب الشهادات');

-- إنشاء مستخدم إداري افتراضي (كلمة المرور: admin123)
INSERT INTO users (college_id, username, password, full_name_ar, role) VALUES
(1, 'admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'مدير النظام', 'admin');

