-- Veritabanı oluştur
CREATE DATABASE UniversityApp;
USE UniversityApp;

-- Öğrenciler Tablosu
CREATE TABLE Students (
    StudentID INT PRIMARY KEY AUTO_INCREMENT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100),
    PhoneNumber VARCHAR(15),
    DateOfBirth DATE,
    FacultyID INT
);

-- Öğretmenler Tablosu
CREATE TABLE Teachers (
    TeacherID INT PRIMARY KEY AUTO_INCREMENT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100),
    PhoneNumber VARCHAR(15),
    HireDate DATE,
    FacultyID INT
);

-- Fakülteler Tablosu
CREATE TABLE Faculties (
    FacultyID INT PRIMARY KEY AUTO_INCREMENT,
    FacultyName VARCHAR(100),
    EstablishedYear INT
);

-- Bölümler Tablosu
CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY AUTO_INCREMENT,
    DepartmentName VARCHAR(100),
    FacultyID INT,
    FOREIGN KEY (FacultyID) REFERENCES Faculties(FacultyID)
);

-- Dersler Tablosu
CREATE TABLE Courses (
    CourseID INT PRIMARY KEY AUTO_INCREMENT,
    CourseName VARCHAR(100),
    CourseCode VARCHAR(20),
    Credits INT,
    TeacherID INT,
    DepartmentID INT,
    Prerequisites TEXT,
    FOREIGN KEY (TeacherID) REFERENCES Teachers(TeacherID),
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

-- Notlar Tablosu
CREATE TABLE Grades (
    GradeID INT PRIMARY KEY AUTO_INCREMENT,
    CourseID INT,
    StudentID INT,
    Grade FLOAT,
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
);

-- Öğrencinin Kayıtlı Olduğu Dersler
CREATE TABLE StudentCourses (
    StudentCourseID INT PRIMARY KEY AUTO_INCREMENT,
    StudentID INT,
    CourseID INT,
    RegistrationDate DATE,
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

-- Öğretmen Yetenekleri/Sertifikalar
CREATE TABLE TeacherSkills (
    SkillID INT PRIMARY KEY AUTO_INCREMENT,
    TeacherID INT,
    SkillName VARCHAR(100),
    CertificationDate DATE,
    FOREIGN KEY (TeacherID) REFERENCES Teachers(TeacherID)
);

-- Ders Programı
CREATE TABLE CourseSchedule (
    ScheduleID INT PRIMARY KEY AUTO_INCREMENT,
    CourseID INT,
    Weekday VARCHAR(15),
    StartTime TIME,
    EndTime TIME,
    Classroom VARCHAR(50),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

-- Öğrenci-Fakülte İlişkisi
ALTER TABLE Students
ADD FOREIGN KEY (FacultyID) REFERENCES Faculties(FacultyID);

-- Öğretmen-Fakülte İlişkisi
ALTER TABLE Teachers
ADD FOREIGN KEY (FacultyID) REFERENCES Faculties(FacultyID);

-- Skills Tablosu
CREATE TABLE Skills (
    SkillID INT PRIMARY KEY AUTO_INCREMENT,
    SkillName VARCHAR(100),
    SkillCategory VARCHAR(50)
);

-- TeacherSkillRelation Tablosu (Bağlantı Tablosu)
CREATE TABLE TeacherSkillRelation (
    RelationID INT PRIMARY KEY AUTO_INCREMENT,
    TeacherID INT,
    SkillID INT,
    CertificationDate DATE,
    FOREIGN KEY (TeacherID) REFERENCES Teachers(TeacherID),
    FOREIGN KEY (SkillID) REFERENCES Skills(SkillID)
);