CREATE TABLE University (
    Id SERIAL PRIMARY KEY,
    Name VARCHAR(255) NOT NULL,
    Location VARCHAR(100) NOT NULL,
    IsPrivate BOOLEAN NOT NULL
);

CREATE TYPE study_field_enum AS ENUM ('ENGINEERING', 'MEDICINE', 'LAW', 'ARTS', 'SCIENCE');

CREATE TABLE Faculty (
    Id SERIAL PRIMARY KEY,
    Name VARCHAR(255) NOT NULL,
    Location VARCHAR(100) NOT NULL,
    study_field study_field_enum NOT NULL,
    University_Id INT NOT NULL,
    FOREIGN KEY (University_Id) REFERENCES University(Id) ON DELETE CASCADE
);
CREATE TABLE Professor (
    Id SERIAL PRIMARY KEY,
    Name VARCHAR(255) NOT NULL,
    Surname VARCHAR(255) NOT NULL,
    Age INT NOT NULL CHECK (Age BETWEEN 18 AND 80),
    Faculty_Id INT NOT NULL,
    FOREIGN KEY (Faculty_Id) REFERENCES Faculty(Id) ON DELETE CASCADE
);
CREATE TABLE Student (
    Id SERIAL PRIMARY KEY,
    Name VARCHAR(255) NOT NULL,
    Surname VARCHAR(255) NOT NULL,
    Location VARCHAR(100),
    Student_Index INT NOT NULL UNIQUE, 
    Faculty_Id INT NOT NULL,
    Professor_Id INT,
    FOREIGN KEY (Faculty_Id) REFERENCES Faculty(Id) ON DELETE RESTRICT,
    FOREIGN KEY (Professor_Id) REFERENCES Professor(Id) ON DELETE SET NULL
);
CREATE TABLE Subject (
    Id SERIAL PRIMARY KEY,
    Name VARCHAR(255) NOT NULL,
    Semester INT NOT NULL CHECK (Semester BETWEEN 1 AND 8),
    Credits INT NOT NULL CHECK (Credits BETWEEN 1 AND 10),
    Faculty_Id INT NOT NULL,
    Professor_Id INT,
    FOREIGN KEY (Faculty_Id) REFERENCES Faculty(Id) ON DELETE RESTRICT,
    FOREIGN KEY (Professor_Id) REFERENCES Professor(Id) ON DELETE SET NULL
);

    CREATE TYPE enrollment_status AS ENUM ('ENROLLED', 'PASSED', 'FAILED', 'DROPPED');

CREATE TABLE Student_Subject (
    Ss_Id INT PRIMARY KEY,
    Student_Id INT NOT NULL,
    Subject_Id INT NOT NULL,
    Enrollment_Date DATE NOT NULL,
    Status VARCHAR(20) NOT NULL DEFAULT 'ENROLLED',
    Final_Grade INT CHECK (Final_Grade BETWEEN 5 AND 10),
    Absences_Count INT NOT NULL DEFAULT 0 CHECK (Absences_Count >= 0),
    FOREIGN KEY (Student_Id) REFERENCES Student(Id) ON DELETE CASCADE,
    FOREIGN KEY (Subject_Id) REFERENCES Subject(Id) ON DELETE CASCADE
);