Index: sql/data_load.sql =================================================================== --- sql/data_load.sql (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) +++ sql/data_load.sql (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) @@ -0,0 +1,164 @@ +SET search_path TO project; + +TRUNCATE TABLE + professour_subjects, + dependency_subject, + major_subjects, + payment, + token, + user_documents, + documents, + passed_subjects, + semesters_subjects, + enrolled_semesters, + subjects, + active_semesters, + major, + contact, + high_school, + users +RESTART IDENTITY CASCADE; + +INSERT INTO users (id, name, embg, surname, "index", bday, email, password, role, quota, enrollment_year) VALUES +(1, 'Stefan', '0101999450001', 'Saveski', '233149', '1999-01-01', 'stefan.saveski@students.finki.ukim.mk', '$2a$11$S9QIoRlV/rmkif7CKBjryuHwfLo1wTsXckX9oTduqrdyNWInCqR0S', 'student', 'drzavna', 2023), +(2, 'Boris', '0202999450002', 'Gjorgjievski','233188', '1999-02-02', 'boris.gjorgjievski@students.finki.ukim.mk','$2a$11$S9QIoRlV/rmkif7CKBjryuHwfLo1wTsXckX9oTduqrdyNWInCqR0S', 'student', 'privatna', 2023), +(3, 'Ana', '0303000450003', 'Petrova', '233200', '2000-03-03', 'ana.petrova@students.finki.ukim.mk', '$2a$11$S9QIoRlV/rmkif7CKBjryuHwfLo1wTsXckX9oTduqrdyNWInCqR0S', 'student', 'drzavna', 2023); + +INSERT INTO users (id, name, embg, surname, "index", bday, email, password, role, quota, enrollment_year) VALUES +(4, 'Vangel', '0404198045004', 'Ajanovski', NULL, '1980-04-04', 'vangel.ajanovski@finki.ukim.mk', '$2a$11$S9QIoRlV/rmkif7CKBjryuHwfLo1wTsXckX9oTduqrdyNWInCqR0S', 'prof', NULL, NULL), +(5, 'Marija', '0505198545005', 'Ilievska', NULL, '1985-05-05', 'marija.ilievska@finki.ukim.mk', '$2a$11$S9QIoRlV/rmkif7CKBjryuHwfLo1wTsXckX9oTduqrdyNWInCqR0S', 'prof', NULL, NULL); + +INSERT INTO users (id, name, embg, surname, "index", bday, email, password, role, quota, enrollment_year) VALUES +(6, 'Igor', '0606197545006', 'Adminovski', NULL, '1975-06-06', 'igor.adminovski@finki.ukim.mk', '$2a$11$S9QIoRlV/rmkif7CKBjryuHwfLo1wTsXckX9oTduqrdyNWInCqR0S', 'admin', NULL, NULL); + +INSERT INTO users (id, name, embg, surname, "index", bday, email, password, role, quota, enrollment_year) VALUES +(7, 'Riste', '0707197845007', 'Stojanov', NULL, '1978-07-07', 'riste.stojanov@finki.ukim.mk', '$2a$11$S9QIoRlV/rmkif7CKBjryuHwfLo1wTsXckX9oTduqrdyNWInCqR0S', 'prof', NULL, NULL), +(8, 'Katerina','0808198245008', 'Zdravkova', NULL, '1982-08-08', 'katerina.zdravkova@finki.ukim.mk', '$2a$11$S9QIoRlV/rmkif7CKBjryuHwfLo1wTsXckX9oTduqrdyNWInCqR0S', 'prof', NULL, NULL); + +INSERT INTO high_school (id, user_id, gpa, type) VALUES +(1, 1, 4.85, 'gimnazija'), +(2, 2, 4.60, 'strucen'), +(3, 3, 4.95, 'gimnazija'); + +INSERT INTO contact (id, user_id, city, municipality, address, number, microsoft_email) VALUES +(1, 1, 'Skopje', 'Centar', 'Bul. Partizanski Odredi 15', '070111111', 'stefan.saveski@live.finki.ukim.mk'), +(2, 2, 'Skopje', 'Karpos', 'Ul. Ivan Milutinovic 22', '071222222', 'boris.gjorgjievski@live.finki.ukim.mk'), +(3, 3, 'Tetovo', 'Tetovo', 'Ul. Ilindenska 5', '072333333', NULL); + +INSERT INTO major (id, name) VALUES +(1, 'Software Engineering and Information Systems'), +(2, 'Computer Science and Engineering'); + +INSERT INTO active_semesters (id, year, type) VALUES +(1, 2024, 'winter'), +(2, 2025, 'summer'), +(3, 2025, 'winter'), +(4, 2026, 'summer'), +(5, 2026, 'winter'); + +INSERT INTO subjects (id, name, code, awarded_credits, dependency_credit) VALUES +(1, 'Structured Programming', 'F18L1S001', 6, 0), +(2, 'Object Oriented Programming', 'F18L1S002', 6, 6), +(3, 'Databases', 'F18L2S010', 6, 6), +(4, 'Operating Systems', 'F18L2S011', 6, 0), +(5, 'Web Programming', 'F18L3S020', 6, 12), +(6, 'Algorithms and Data Structures', 'F18L2S012', 6, 6), +(7, 'Computer Networks', 'F18L2S013', 6, 6), +(8, 'Software Engineering', 'F18L3S021', 6, 12), +(9, 'Discrete Mathematics', 'F18L1S003', 6, 0), +(10, 'Calculus', 'F18L1S004', 6, 0), +(11, 'Linear Algebra', 'F18L1S005', 6, 0), +(12, 'Computer Architecture', 'F18L1S006', 6, 0), +(13, 'Artificial Intelligence', 'F18L3S022', 6, 12), +(14, 'Machine Learning', 'F18L4S030', 6, 18), +(15, 'Information Security', 'F18L3S023', 6, 12), +(16, 'Mobile Platforms', 'F18L3S024', 6, 12), +(17, 'Distributed Systems', 'F18L4S031', 6, 18), +(18, 'Human-Computer Interaction', 'F18L2S014', 6, 6), +(19, 'Probability and Statistics', 'F18L2S015', 6, 6), +(20, 'Compiler Design', 'F18L4S032', 6, 18); + +INSERT INTO enrolled_semesters (id, user_id, quota, major_id, note, student_comment, last_change, completed, semester_id) VALUES +(1, 1, 'drzavna', 1, NULL, 'prva godina', '2024-10-01 10:00:00', '2025-02-01 12:00:00', 1), +(2, 1, 'drzavna', 1, NULL, NULL, '2025-03-01 10:00:00', NULL, 2), +(3, 2, 'privatna', 1, NULL, NULL, '2024-10-05 09:30:00', '2025-02-03 11:00:00', 1), +(4, 3, 'drzavna', 2, NULL, NULL, '2024-10-02 08:15:00', NULL, 1); + +INSERT INTO semesters_subjects (id, enrolled_semesters_id, subjects_id, professor_id, signature) VALUES +(1, 1, 1, 4, TRUE), +(2, 1, 4, 5, TRUE), +(3, 2, 2, 4, FALSE), +(4, 3, 1, 4, TRUE), +(5, 4, 1, 4, TRUE), +(6, 4, 3, 5, FALSE); + +INSERT INTO passed_subjects (id, enrolled_id, grade, date_passed) VALUES +(1, 1, '10', '2025-01-25 14:00:00'), +(2, 2, '8', '2025-01-30 11:30:00'), +(3, 4, '9', '2025-01-28 09:45:00'), +(4, 5, '7', '2025-01-29 13:15:00'); + +INSERT INTO documents (id, type, body, cost) VALUES +(1, 'Uverenie za polozeni ispiti', 'Sodrzina na uverenie za polozeni ispiti...', 100), +(2, 'Potvrda za redoven student', 'Sodrzina na potvrda za redoven student...', 50), +(3, 'Diploma', 'Sodrzina na diploma...', 500); + +INSERT INTO user_documents (user_id, document_id) VALUES +(1, 1), +(1, 2), +(2, 2), +(3, 1); + +INSERT INTO token (id, user_id, token, expires_at, is_valid) VALUES +(1, 1, 'tok_abc123_stefan', '2026-08-01 00:00:00', TRUE), +(2, 2, 'tok_def456_boris', '2026-08-01 00:00:00', TRUE), +(3, 1, 'tok_old789_stefan', '2026-01-01 00:00:00', FALSE); + +INSERT INTO payment (id, user_id, enrollment_id, amount) VALUES +(1, 1, 1, 200), +(2, 1, 2, 200), +(3, 2, 3, 400), +(4, 3, 4, 200); + +INSERT INTO major_subjects (major_id, subject_id, mandatory_semester) VALUES +(1, 1, 1), (1, 9, 1), (1, 10, 1), (1, 11, 1), (1, 12, 1), +(1, 2, 2), (1, 6, 2), (1, 18, 2), (1, 19, 2), +(1, 3, 3), (1, 4, 3), (1, 7, 3), +(1, 5, 4), (1, 8, 4), (1, 13, 4), (1, 15, 4), (1, 16, 4), +(1, 14, 5), (1, 17, 5), +(2, 1, 1), (2, 9, 1), (2, 10, 1), (2, 11, 1), (2, 12, 1), +(2, 2, 2), (2, 6, 2), (2, 19, 2), +(2, 3, 3), (2, 4, 3), (2, 7, 3), (2, 18, 3), +(2, 5, 4), (2, 13, 4), (2, 15, 4), +(2, 14, 5), (2, 17, 5), (2, 20, 5); + +INSERT INTO dependency_subject (subject_id, dependency_id) VALUES +(2, 1), +(5, 2), +(3, 1); + +INSERT INTO professour_subjects (prof_id, active_semester_id, subject_id) +SELECT teacher.id, sem.id, subj.id +FROM subjects subj +CROSS JOIN active_semesters sem +CROSS JOIN LATERAL ( + SELECT u.id + FROM users u + WHERE u.role = 'prof' + ORDER BY u.id + OFFSET subj.id % (SELECT count(*) FROM users WHERE role = 'prof') + LIMIT 1 +) AS teacher; + +SELECT setval(pg_get_serial_sequence('users', 'id'), (SELECT max(id) FROM users)); +SELECT setval(pg_get_serial_sequence('high_school', 'id'), (SELECT max(id) FROM high_school)); +SELECT setval(pg_get_serial_sequence('contact', 'id'), (SELECT max(id) FROM contact)); +SELECT setval(pg_get_serial_sequence('major', 'id'), (SELECT max(id) FROM major)); +SELECT setval(pg_get_serial_sequence('active_semesters', 'id'), (SELECT max(id) FROM active_semesters)); +SELECT setval(pg_get_serial_sequence('subjects', 'id'), (SELECT max(id) FROM subjects)); +SELECT setval(pg_get_serial_sequence('enrolled_semesters', 'id'), (SELECT max(id) FROM enrolled_semesters)); +SELECT setval(pg_get_serial_sequence('semesters_subjects', 'id'), (SELECT max(id) FROM semesters_subjects)); +SELECT setval(pg_get_serial_sequence('passed_subjects', 'id'), (SELECT max(id) FROM passed_subjects)); +SELECT setval(pg_get_serial_sequence('documents', 'id'), (SELECT max(id) FROM documents)); +SELECT setval(pg_get_serial_sequence('token', 'id'), (SELECT max(id) FROM token)); +SELECT setval(pg_get_serial_sequence('payment', 'id'), (SELECT max(id) FROM payment)); Index: sql/iknow.dbml =================================================================== --- sql/iknow.dbml (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) +++ sql/iknow.dbml (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) @@ -0,0 +1,207 @@ +// IKnow / FINKI - dbdiagram.io (DBML) source +// Generated from schema_creation.sql, which is the source of truth. +// Paste into https://dbdiagram.io to render the relational diagram. + +Enum project.user_role { + student + prof + admin +} + +Enum project.hs_type { + strucen + gimnazija +} + +Enum project.quota_type { + privatna + drzavna + stipendija +} + +Enum project.semester_type { + summer + winter +} + +Enum project.grade_type { + "6" + "7" + "8" + "9" + "10" +} + +Table project.users { + id integer [pk, increment] + name varchar(100) [not null] + embg varchar(13) + surname varchar(100) [not null] + index varchar(20) + bday timestamp + email varchar(150) [not null, unique] + password varchar(255) [not null] + role project.user_role [not null] + quota project.quota_type + enrollment_year integer + created_at timestamp [not null, default: `now()`] +} + +Table project.high_school { + id integer [pk, increment] + user_id integer [not null, unique] + gpa real [not null] + type project.hs_type [not null] +} + +Table project.contact { + id integer [pk, increment] + user_id integer [not null, unique] + city varchar(100) [not null] + municipality varchar(100) [not null] + address varchar(150) [not null] + number varchar(20) [not null] + microsoft_email varchar(150) +} + +Table project.major { + id integer [pk, increment] + name varchar(150) [not null] +} + +Table project.active_semesters { + id integer [pk, increment] + year integer [not null] + type project.semester_type [not null] + + indexes { + (year, type) [unique] + } +} + +Table project.subjects { + id integer [pk, increment] + name varchar(150) [not null, unique] + code varchar(30) [not null, unique] + awarded_credits integer [not null] + dependency_credit integer +} + +Table project.enrolled_semesters { + id integer [pk, increment] + user_id integer [not null] + quota project.quota_type [not null] + major_id integer [not null] + note varchar(255) + student_comment varchar(255) + created_at timestamp [not null, default: `now()`] + last_change timestamp + completed timestamp + semester_id integer [not null] + + indexes { + (user_id, semester_id) [unique] + } +} + +Table project.semesters_subjects { + id integer [pk, increment] + enrolled_semesters_id integer [not null] + subjects_id integer [not null] + professor_id integer [not null] + signature boolean [not null, default: false] + + indexes { + (enrolled_semesters_id, subjects_id) [unique] + } +} + +Table project.passed_subjects { + id integer [pk, increment] + enrolled_id integer [not null, unique] + grade project.grade_type [not null] + date_passed timestamp [not null] +} + +Table project.documents { + id integer [pk, increment] + type varchar(100) [not null] + body text [not null] + cost integer [not null] +} + +Table project.user_documents { + user_id integer [not null] + document_id integer [not null] + + indexes { + (user_id, document_id) [pk] + } +} + +Table project.token { + id integer [pk, increment] + user_id integer [not null] + token varchar(255) [not null] + expires_at timestamp [not null] + is_valid boolean [not null, default: true] +} + +Table project.payment { + id integer [pk, increment] + user_id integer [not null] + enrollment_id integer [not null] + amount integer [not null] +} + +Table project.major_subjects { + major_id integer [not null] + subject_id integer [not null] + mandatory_semester integer [not null] + + indexes { + (major_id, subject_id) [pk] + } +} + +Table project.dependency_subject { + subject_id integer [not null] + dependency_id integer [not null] + + indexes { + (subject_id, dependency_id) [pk] + } +} + +Table project.professour_subjects { + prof_id integer [not null] + active_semester_id integer [not null] + subject_id integer [not null] + + indexes { + (prof_id, active_semester_id, subject_id) [pk] + } +} + +// ---------- Relationships ---------- +Ref: project.high_school.user_id - project.users.id // has_hs (1:1) +Ref: project.contact.user_id - project.users.id // has_contact (1:1) +Ref: project.token.user_id > project.users.id // has_token +Ref: project.payment.user_id > project.users.id // pays +Ref: project.payment.enrollment_id > project.enrolled_semesters.id // for_enrollment +Ref: project.enrolled_semesters.user_id > project.users.id // submits +Ref: project.enrolled_semesters.major_id > project.major.id // for_major +Ref: project.enrolled_semesters.semester_id > project.active_semesters.id // during +Ref: project.semesters_subjects.enrolled_semesters_id > project.enrolled_semesters.id // contains +Ref: project.semesters_subjects.subjects_id > project.subjects.id // taken_in +Ref: project.semesters_subjects.professor_id > project.users.id // teaches +Ref: project.passed_subjects.enrolled_id - project.semesters_subjects.id // results_in (1:1) +Ref: project.user_documents.user_id > project.users.id // owns +Ref: project.user_documents.document_id > project.documents.id // owns +Ref: project.major_subjects.major_id > project.major.id // includes +Ref: project.major_subjects.subject_id > project.subjects.id // includes +Ref: project.dependency_subject.subject_id > project.subjects.id // requires (dependent) +Ref: project.dependency_subject.dependency_id > project.subjects.id // requires (prerequisite) +Ref: project.professour_subjects.prof_id > project.users.id // teaches_subject +Ref: project.professour_subjects.active_semester_id > project.active_semesters.id // teaches_subject +Ref: project.professour_subjects.subject_id > project.subjects.id // teaches_subject Index: sql/schema.md =================================================================== --- sql/schema.md (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) +++ sql/schema.md (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) @@ -0,0 +1,185 @@ +# Schema diagram + +Generated from [`schema_creation.sql`](schema_creation.sql), which is the source of truth. If you change +a table there, change it here too. + +Renders inline on GitHub and in the VS Code markdown preview (Ctrl+Shift+V) - +no extension or tooling needed. Relationship labels use the names from the +conceptual ER model. + +```mermaid +erDiagram + users { + integer id PK + varchar name + varchar embg + varchar surname + varchar index + timestamp bday + varchar email UK + varchar password + user_role role + quota_type quota + integer enrollment_year + timestamp created_at + } + + high_school { + integer id PK + integer user_id FK,UK + real gpa + hs_type type + } + + contact { + integer id PK + integer user_id FK,UK + varchar city + varchar municipality + varchar address + varchar number + varchar microsoft_email + } + + major { + integer id PK + varchar name + } + + active_semesters { + integer id PK + integer year UK + semester_type type UK + } + + subjects { + integer id PK + varchar name UK + varchar code UK + integer awarded_credits + integer dependency_credit + } + + enrolled_semesters { + integer id PK + integer user_id FK,UK + quota_type quota + integer major_id FK + varchar note + varchar student_comment + timestamp created_at + timestamp last_change + timestamp completed + integer semester_id FK,UK + } + + semesters_subjects { + integer id PK + integer enrolled_semesters_id FK,UK + integer subjects_id FK,UK + integer professor_id FK + boolean signature + } + + passed_subjects { + integer id PK + integer enrolled_id FK,UK + grade_type grade + timestamp date_passed + } + + documents { + integer id PK + varchar type + text body + integer cost + } + + user_documents { + integer user_id PK,FK + integer document_id PK,FK + } + + token { + integer id PK + integer user_id FK + varchar token + timestamp expires_at + boolean is_valid + } + + payment { + integer id PK + integer user_id FK + integer enrollment_id FK + integer amount + } + + major_subjects { + integer major_id PK,FK + integer subject_id PK,FK + integer mandatory_semester + } + + dependency_subject { + integer subject_id PK,FK + integer dependency_id PK,FK + } + + professour_subjects { + integer prof_id PK,FK + integer active_semester_id PK,FK + integer subject_id PK,FK + } + + users ||--o| high_school : has_hs + users ||--o| contact : has_contact + users ||--o{ token : has_token + users ||--o{ payment : pays + users ||--o{ enrolled_semesters : submits + users ||--o{ user_documents : owns + documents ||--o{ user_documents : owned_by + users ||--o{ semesters_subjects : teaches + users ||--o{ professour_subjects : teaches_subject + + major ||--o{ enrolled_semesters : for_major + active_semesters ||--o{ enrolled_semesters : during + enrolled_semesters ||--o{ semesters_subjects : contains + enrolled_semesters ||--o{ payment : for_enrollment + + subjects ||--o{ semesters_subjects : taken_in + semesters_subjects ||--o| passed_subjects : results_in + + major ||--o{ major_subjects : includes + subjects ||--o{ major_subjects : included_in + + subjects ||--o{ dependency_subject : requires + subjects ||--o{ dependency_subject : prerequisite_for + + active_semesters ||--o{ professour_subjects : taught_in + subjects ||--o{ professour_subjects : taught_as +``` + +## Enum types + +| Type | Labels | +|------|--------| +| `user_role` | `student`, `prof`, `admin` | +| `hs_type` | `strucen`, `gimnazija` | +| `quota_type` | `privatna`, `drzavna`, `stipendija` | +| `semester_type` | `summer`, `winter` | +| `grade_type` | `6`, `7`, `8`, `9`, `10` | + +## Notes + +- `UK` on `enrolled_semesters.user_id` / `semester_id` is the composite + `UNIQUE (user_id, semester_id)` - one enrolment per student per semester. + Same for `semesters_subjects (enrolled_semesters_id, subjects_id)` and + `active_semesters (year, type)`. +- `users` appears twice around `semesters_subjects`: once as the student, via + `enrolled_semesters.user_id`, and once as `professor_id`. The ER model links + the student through the enrolment, not directly. +- `dependency_subject` is `subjects` related to itself: `subject_id` is the + dependent subject and `dependency_id` the prerequisite, with a `CHECK` that + they differ. +- `professour_subjects` keeps the spelling used by the table in `schema_creation.sql`. Index: sql/schema_creation.sql =================================================================== --- sql/schema_creation.sql (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) +++ sql/schema_creation.sql (revision ea405569ef29f56fbfc4d77708b6b3faf9286097) @@ -0,0 +1,141 @@ +DROP SCHEMA IF EXISTS project CASCADE; +CREATE SCHEMA project; + +SET search_path TO project; + +CREATE TYPE user_role AS ENUM ('student', 'prof', 'admin'); +CREATE TYPE hs_type AS ENUM ('strucen', 'gimnazija'); +CREATE TYPE quota_type AS ENUM ('privatna', 'drzavna', 'stipendija'); +CREATE TYPE semester_type AS ENUM ('summer', 'winter'); +CREATE TYPE grade_type AS ENUM ('6', '7', '8', '9', '10'); + +CREATE TABLE users ( + id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, + name VARCHAR(100) NOT NULL, + embg VARCHAR(13), + surname VARCHAR(100) NOT NULL, + "index" VARCHAR(20), + bday TIMESTAMP, + email VARCHAR(150) NOT NULL UNIQUE, + password VARCHAR(255) NOT NULL, + role user_role NOT NULL, + quota quota_type, + enrollment_year INTEGER, + created_at TIMESTAMP NOT NULL DEFAULT now() +); + +CREATE TABLE high_school ( + id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + user_id INTEGER NOT NULL UNIQUE REFERENCES users (id), + gpa REAL NOT NULL, + type hs_type NOT NULL +); + +CREATE TABLE contact ( + id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + user_id INTEGER NOT NULL UNIQUE REFERENCES users (id), + city VARCHAR(100) NOT NULL, + municipality VARCHAR(100) NOT NULL, + address VARCHAR(150) NOT NULL, + number VARCHAR(20) NOT NULL, + microsoft_email VARCHAR(150) +); + +CREATE TABLE major ( + id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + name VARCHAR(150) NOT NULL +); + +CREATE TABLE active_semesters ( + id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + year INTEGER NOT NULL, + type semester_type NOT NULL, + UNIQUE (year, type) +); + +CREATE TABLE subjects ( + id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + name VARCHAR(150) NOT NULL UNIQUE, + code VARCHAR(30) NOT NULL UNIQUE, + awarded_credits INTEGER NOT NULL, + dependency_credit INTEGER +); + +CREATE TABLE enrolled_semesters ( + id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + user_id INTEGER NOT NULL REFERENCES users (id), + quota quota_type NOT NULL, + major_id INTEGER NOT NULL REFERENCES major (id), + note VARCHAR(255), + student_comment VARCHAR(255), + created_at TIMESTAMP NOT NULL DEFAULT now(), + last_change TIMESTAMP, + completed TIMESTAMP, + semester_id INTEGER NOT NULL REFERENCES active_semesters (id), + UNIQUE (user_id, semester_id) +); + +CREATE TABLE semesters_subjects ( + id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + enrolled_semesters_id INTEGER NOT NULL REFERENCES enrolled_semesters (id), + subjects_id INTEGER NOT NULL REFERENCES subjects (id), + professor_id INTEGER NOT NULL REFERENCES users (id), + signature BOOLEAN NOT NULL DEFAULT FALSE, + UNIQUE (enrolled_semesters_id, subjects_id) +); + +CREATE TABLE passed_subjects ( + id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + enrolled_id INTEGER NOT NULL UNIQUE REFERENCES semesters_subjects (id), + grade grade_type NOT NULL, + date_passed TIMESTAMP NOT NULL +); + +CREATE TABLE documents ( + id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + type VARCHAR(100) NOT NULL, + body TEXT NOT NULL, + cost INTEGER NOT NULL +); + +CREATE TABLE user_documents ( + user_id INTEGER NOT NULL REFERENCES users (id), + document_id INTEGER NOT NULL REFERENCES documents (id), + PRIMARY KEY (user_id, document_id) +); + +CREATE TABLE token ( + id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + user_id INTEGER NOT NULL REFERENCES users (id), + token VARCHAR(255) NOT NULL, + expires_at TIMESTAMP NOT NULL, + is_valid BOOLEAN NOT NULL DEFAULT TRUE +); + +CREATE TABLE payment ( + id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + user_id INTEGER NOT NULL REFERENCES users (id), + enrollment_id INTEGER NOT NULL REFERENCES enrolled_semesters (id), + amount INTEGER NOT NULL +); + +CREATE TABLE major_subjects ( + major_id INTEGER NOT NULL REFERENCES major (id), + subject_id INTEGER NOT NULL REFERENCES subjects (id), + mandatory_semester INTEGER NOT NULL, + PRIMARY KEY (major_id, subject_id) +); + +CREATE TABLE dependency_subject ( + subject_id INTEGER NOT NULL REFERENCES subjects (id), + dependency_id INTEGER NOT NULL REFERENCES subjects (id), + PRIMARY KEY (subject_id, dependency_id), + CHECK (subject_id <> dependency_id) +); + +CREATE TABLE professour_subjects ( + prof_id INTEGER NOT NULL REFERENCES users (id), + active_semester_id INTEGER NOT NULL REFERENCES active_semesters (id), + subject_id INTEGER NOT NULL REFERENCES subjects (id), + PRIMARY KEY (prof_id, active_semester_id, subject_id) +);