Ph2: dml.sql

-- ============================================================
--  IKnow / FINKI - DML script (PostgreSQL)
--  Fills all tables with sample data
-- ============================================================

-- ---------- users ----------
-- students
INSERT INTO users (id, name, embg, surname, "index", bday, email, password, role) VALUES
(1, 'Stefan',  '0101999450001', 'Saveski',    '233149', '1999-01-01', 'stefan.saveski@students.finki.ukim.mk',  'hash_pw_1', 'student'),
(2, 'Boris',   '0202999450002', 'Gjorgjievski','233188', '1999-02-02', 'boris.gjorgjievski@students.finki.ukim.mk','hash_pw_2', 'student'),
(3, 'Ana',     '0303000450003', 'Petrova',    '233200', '2000-03-03', 'ana.petrova@students.finki.ukim.mk',      'hash_pw_3', 'student');
-- professors
INSERT INTO users (id, name, embg, surname, "index", bday, email, password, role) VALUES
(4, 'Vangel',  '0404198045004', 'Ajanovski',  NULL,     '1980-04-04', 'vangel.ajanovski@finki.ukim.mk',          'hash_pw_4', 'prof'),
(5, 'Marija',  '0505198545005', 'Ilievska',   NULL,     '1985-05-05', 'marija.ilievska@finki.ukim.mk',           'hash_pw_5', 'prof');
-- admin
INSERT INTO users (id, name, embg, surname, "index", bday, email, password, role) VALUES
(6, 'Igor',    '0606197545006', 'Adminovski', NULL,     '1975-06-06', 'igor.adminovski@finki.ukim.mk',           'hash_pw_6', 'admin');

-- ---------- high_school ----------
INSERT INTO high_school (user_id, gpa, type) VALUES
(1, 4.85, 'gimnazija'),
(2, 4.60, 'strucen'),
(3, 4.95, 'gimnazija');

-- ---------- contact ----------
INSERT INTO contact (user_id, city, municipality, address, number, microsoft_email) VALUES
(1, 'Skopje', 'Centar',    'Bul. Partizanski Odredi 15', '070111111', 'stefan.saveski@live.finki.ukim.mk'),
(2, 'Skopje', 'Karpos',    'Ul. Ivan Milutinovic 22',    '071222222', 'boris.gjorgjievski@live.finki.ukim.mk'),
(3, 'Tetovo', 'Tetovo',    'Ul. Ilindenska 5',           '072333333', NULL);

-- ---------- major ----------
INSERT INTO major (id, name) VALUES
(1, 'Software Engineering and Information Systems'),
(2, 'Computer Science and Engineering');

-- ---------- active_semesters ----------
INSERT INTO active_semesters (id, year_, type) VALUES
(1, 2024, 'winter'),
(2, 2025, 'summer'),
(3, 2025, 'winter');

-- ---------- subjects ----------
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);

-- ---------- enrolled_semesters ----------
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);

-- ---------- semesters_subjects ----------
INSERT INTO semesters_subjects (id, user_id, enrolled_semesters_id, subjects_id, professor_id, signature) VALUES
(1, 1, 1, 1, 4, TRUE),
(2, 1, 1, 4, 5, TRUE),
(3, 1, 2, 2, 4, FALSE),
(4, 2, 3, 1, 4, TRUE),
(5, 3, 4, 1, 4, TRUE),
(6, 3, 4, 3, 5, FALSE);

-- ---------- passed_subjects ----------
INSERT INTO passed_subjects (passed_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');

-- ---------- documents ----------
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);

-- ---------- user_documents ----------
INSERT INTO user_documents (user_id, document_id) VALUES
(1, 1),
(1, 2),
(2, 2),
(3, 1);

-- ---------- token ----------
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);

-- ---------- payment ----------
INSERT INTO payment (id, user_id, enrollment_id, amount, ukim) VALUES
(1, 1, 1, 200, 200),
(2, 1, 2, 200, 200),
(3, 2, 3, 400, 200),
(4, 3, 4, 200, 200);

-- ---------- enrollment_info ----------
INSERT INTO enrollment_info (id, user_id, quota, major_id, enrollment_year) VALUES
(1, 1, 'drzavna',  1, 2023),
(2, 2, 'privatna', 1, 2023),
(3, 3, 'drzavna',  2, 2023);

-- ---------- major_subjects ----------
INSERT INTO major_subjects (major_id, subject_id, manditory_semester, akreditacija) VALUES
(1, 1, 1, 2018),
(1, 2, 2, 2018),
(1, 3, 3, 2018),
(1, 5, 4, 2018),
(2, 1, 1, 2018),
(2, 3, 3, 2018),
(2, 4, 3, 2018);

-- ---------- dependency_subject ----------
-- OOP depends on Structured Programming; Web depends on OOP; Databases depends on Structured Programming
INSERT INTO dependency_subject (subject_id, dependency_id) VALUES
(2, 1),
(5, 2),
(3, 1);

-- ---------- professour_subjects ----------
INSERT INTO professour_subjects (prof_id, active_semester_id, subject_id) VALUES
(4, 1, 1),
(4, 2, 2),
(5, 1, 4),
(5, 3, 3),
(4, 3, 5);