P2: schema_creation.sql

-- Избришете ги ако веќе постојат
DROP TABLE IF EXISTS Student_Subject CASCADE;
DROP TABLE IF EXISTS Subject_Professor CASCADE;
DROP TABLE IF EXISTS Subject CASCADE;
DROP TABLE IF EXISTS Advice CASCADE;
DROP TABLE IF EXISTS Student CASCADE;
DROP TABLE IF EXISTS Professor CASCADE;
DROP TABLE IF EXISTS Faculty CASCADE;
DROP TABLE IF EXISTS University CASCADE;
DROP TABLE IF EXISTS Affiliated CASCADE;
DROP TYPE IF EXISTS study_field_enum CASCADE;
DROP TYPE IF EXISTS enrollment_status CASCADE;

-- Креирајте ги типовите
CREATE TYPE study_field_enum AS ENUM (
    'COMPUTER_SCIENCE', 
    'ECONOMICS', 
    'ENGINEERING', 
    'MEDICINE', 
    'LAW', 
    'ARTS', 
    'SCIENCE'
);

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

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

-- Faculty
CREATE TABLE Faculty (
    Id SERIAL PRIMARY KEY,
    Name VARCHAR(255) NOT NULL,
    Location VARCHAR(100),
    Study_Field study_field_enum NOT NULL,
    University_Id INT NOT NULL,
    FOREIGN KEY (University_Id) REFERENCES University(Id) ON DELETE CASCADE
);

-- Professor (без Address поле)
CREATE TABLE Professor (
    Id SERIAL PRIMARY KEY,
    Name VARCHAR(255) NOT NULL,
    Surname VARCHAR(255),
    Age INT CHECK (Age BETWEEN 18 AND 80),
    Faculty_Id INT NOT NULL,
    FOREIGN KEY (Faculty_Id) REFERENCES Faculty(Id) ON DELETE CASCADE
);

-- Student (без Adviser_Id, со Student_Index)
CREATE TABLE Student (
    Id SERIAL PRIMARY KEY,
    Name VARCHAR(255) NOT NULL,
    Surname VARCHAR(255),
    Location VARCHAR(100),
    Student_Index INT NOT NULL UNIQUE,
    Faculty_Id INT NOT NULL,
    FOREIGN KEY (Faculty_Id) REFERENCES Faculty(Id) ON DELETE RESTRICT
);

-- Advice (Student ↔ Professor, M:N)
CREATE TABLE Advice (
    Student_Id INT NOT NULL,
    Professor_Id INT NOT NULL,
    Start_Date DATE,
    End_Date DATE,
    PRIMARY KEY (Student_Id, Professor_Id),
    FOREIGN KEY (Student_Id) REFERENCES Student(Id) ON DELETE CASCADE,
    FOREIGN KEY (Professor_Id) REFERENCES Professor(Id) ON DELETE CASCADE
);

-- Subject (без Professor_Id)
CREATE TABLE Subject (
    Id SERIAL PRIMARY KEY,
    Name VARCHAR(255) NOT NULL,
    Semester INT CHECK (Semester BETWEEN 1 AND 8),
    Credits INT CHECK (Credits BETWEEN 1 AND 10),
    Faculty_Id INT NOT NULL,
    FOREIGN KEY (Faculty_Id) REFERENCES Faculty(Id) ON DELETE RESTRICT
);

-- Subject_Professor (Teach, M:N)
CREATE TABLE Subject_Professor (
    Subject_Id INT NOT NULL,
    Professor_Id INT NOT NULL,
    PRIMARY KEY (Subject_Id, Professor_Id),
    FOREIGN KEY (Subject_Id) REFERENCES Subject(Id) ON DELETE CASCADE,
    FOREIGN KEY (Professor_Id) REFERENCES Professor(Id) ON DELETE CASCADE
);

-- Student_Subject (Enroll & Has)
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
);

-- Affiliated (University ↔ Professor, M:N)
CREATE TABLE Affiliated (
    University_Id INT NOT NULL,
    Professor_Id INT NOT NULL,
    PRIMARY KEY (University_Id, Professor_Id),
    FOREIGN KEY (University_Id) REFERENCES University(Id) ON DELETE CASCADE,
    FOREIGN KEY (Professor_Id) REFERENCES Professor(Id) ON DELETE CASCADE
);