BEGIN;

SET search_path TO project;

TRUNCATE TABLE
    cooperation,
    companies,
    volunteers,
    responsible_for,
    announces_absence,
    required_attendance,
    participation,
    holds_event_function,
    applications,
    related_to,
    activity_sessions,
    event_sessions,
    event_functions,
    event_function_types,
    event_edition_locations,
    event_editions,
    locations,
    event_session_types,
    event_types,
    activity_types,
    function_mandates,
    board,
    non_board,
    organizational_functions,
    has_stage,
    alumni,
    young_members,
    full_members,
    observers,
    membership_stages,
    members,
    students
RESTART IDENTITY CASCADE;

INSERT INTO students (
    student_index, first_name, last_name, faculty, field_of_studies,
    year_of_studies, birthday, email, phone_number
) VALUES
    (230001, 'Elena', 'Petrova', 'FINKI', 'Software Engineering and Information Systems', 4, DATE '2002-05-14', 'elena.petrova@student.ukim.mk', '+38970111001'),
    (230002, 'Marko', 'Nikolov', 'FEEIT', 'Computer Technologies and Engineering', 3, DATE '2003-03-02', 'marko.nikolov@student.ukim.mk', '+38970111002'),
    (230003, 'Ana', 'Trajkovska', 'FINKI', 'Internet, Networks and Security', 3, DATE '2003-09-18', 'ana.trajkovska@student.ukim.mk', '+38970111003'),
    (230004, 'Stefan', 'Kostov', 'FEEIT', 'Telecommunication and Information Engineering', 4, DATE '2002-01-27', 'stefan.kostov@student.ukim.mk', '+38970111004'),
    (230005, 'Mila', 'Jovanovska', 'FINKI', 'Computer Science', 2, DATE '2004-06-08', 'mila.jovanovska@student.ukim.mk', '+38970111005'),
    (230006, 'Boris', 'Angelov', 'MFS', 'Industrial Engineering and Management', 4, DATE '2002-11-11', 'boris.angelov@student.ukim.mk', '+38970111006'),
    (230007, 'Sara', 'Mitrevska', 'FINKI', 'Computer Engineering', 4, DATE '2002-04-20', 'sara.mitrevska@student.ukim.mk', '+38970111007'),
    (230008, 'Petar', 'Stojanovski', 'TMF', 'Design and Management of Technological Processes', 3, DATE '2003-12-05', 'petar.stojanovski@student.ukim.mk', '+38970111008'),
    (230009, 'Ivana', 'Ilievska', 'PMF', 'Molecular Biology with Genetics', 3, DATE '2003-07-30', 'ivana.ilievska@student.ukim.mk', '+38970111009'),
    (230010, 'Viktor', 'Ristovski', 'MFS', 'Mechatronics', 4, DATE '2002-02-16', 'viktor.ristovski@student.ukim.mk', '+38970111010'),
    (230011, 'Jana', 'Arsovska', 'FEEIT', 'Power Systems', 3, DATE '2003-10-01', 'jana.arsovska@student.ukim.mk', '+38970111011'),
    (230012, 'Filip', 'Dimitrov', 'TMF', 'Biotechnology', 3, DATE '2003-08-13', 'filip.dimitrov@student.ukim.mk', '+38970111012'),
    (230013, 'Tamara', 'Gjorgjievska', 'PMF', 'Geography, Physical Geography with Geoecology Module', 2, DATE '2004-05-19', 'tamara.gjorgjievska@student.ukim.mk', '+38970111013'),
    (230014, 'Nikola', 'Spasovski', 'FINKI', 'Application of Information Technologies', 2, DATE '2004-12-22', 'nikola.spasovski@student.ukim.mk', '+38970111014'),
    (230015, 'Marija', 'Karanfilova', 'FEEIT', 'Electrical Power Engineering and Project Management', 4, DATE '2002-06-30', 'marija.karanfilova@student.ukim.mk', '+38970111015'),
    (230016, 'Andrej', 'Pavlov', 'MFS', 'Transport, Mechanization and Logistics', 3, DATE '2003-01-17', 'andrej.pavlov@student.ukim.mk', '+38970111016'),
    (230017, 'Teodora', 'Ilieva', 'TMF', 'Materials Engineering and Nanotechnologies', 2, DATE '2004-03-24', 'teodora.ilieva@student.ukim.mk', '+38970111017'),
    (230018, 'Damjan', 'Mitev', 'PMF', 'Mathematics', 3, DATE '2003-11-09', 'damjan.mitev@student.ukim.mk', '+38970111018'),
    (230019, 'Katerina', 'Naumoska', 'FINKI', 'Professional Studies in Programming', 1, DATE '2005-02-12', 'katerina.naumoska@student.ukim.mk', '+38970111019'),
    (230020, 'Martin', 'Simonovski', 'FEEIT', 'Computer Hardware Engineering and Electronics', 2, DATE '2004-09-04', 'martin.simonovski@student.ukim.mk', '+38970111020'),
    (230021, 'Nevena', 'Lazarevska', 'TMF', 'Food Technology', 1, DATE '2005-07-07', 'nevena.lazarevska@student.ukim.mk', '+38970111021'),
    (230022, 'Luka', 'Popov', 'MFS', 'Energy and Environment', 2, DATE '2004-04-28', 'luka.popov@student.ukim.mk', '+38970111022'),
    (230023, 'Hristina', 'Koleska', 'PMF', 'Ecology and Environment', 1, DATE '2005-09-25', 'hristina.koleska@student.ukim.mk', '+38970111023'),
    (230024, 'Dario', 'Petreski', 'FINKI', 'Informatics Education', 1, DATE '2005-12-03', 'dario.petreski@student.ukim.mk', '+38970111024'),
    (230025, 'Elena', 'Manevska', 'FEEIT', 'Power Engineering, Automation and Renewable Energy Sources', 1, DATE '2005-01-14', 'elena.manevska@student.ukim.mk', '+38970111025'),
    (230026, 'Goran', 'Todorov', 'MFS', 'Thermal Engineering', 2, DATE '2004-08-18', 'goran.todorov@student.ukim.mk', '+38970111026'),
    (230027, 'Irena', 'Naumova', 'PMF', 'Chemistry', 1, DATE '2005-10-30', 'irena.naumova@student.ukim.mk', '+38970111027'),
    (230028, 'Aleksandar', 'Ivanovski', 'FINKI', 'Software Engineering and Information Systems', 0, DATE '1998-04-11', 'aleksandar.ivanovski@alumni.best.org.mk', '+38970111028'),
    (230029, 'Simona', 'Jakimovska', 'TMF', 'Polymer Materials', 0, DATE '1999-02-09', 'simona.jakimovska@alumni.best.org.mk', '+38970111029'),
    (230030, 'Gorjan', 'Naumovski', 'MFS', 'Motor Vehicles', 0, DATE '1998-06-23', 'gorjan.naumovski@alumni.best.org.mk', '+38970111030'),
    (230031, 'Lina', 'Stamenovska', 'PMF', 'Physics', 2, DATE '2004-05-05', 'lina.stamenovska@student.ukim.mk', '+38970111031'),
    (230032, 'David', 'Atanasov', 'FINKI', 'Computer Science', 1, DATE '2005-03-19', 'david.atanasov@student.ukim.mk', '+38970111032');

INSERT INTO members (student_index) VALUES
    (230001), (230002), (230003), (230004), (230005), (230006), (230007), (230008),
    (230009), (230010), (230011), (230012), (230013), (230014), (230015), (230016),
    (230017), (230018), (230019), (230020), (230021), (230022), (230023), (230024),
    (230025), (230026), (230027), (230028), (230029), (230030);

INSERT INTO observers (student_index) VALUES
    (230024), (230025), (230026), (230027);

INSERT INTO full_members (student_index) VALUES
    (230001), (230002), (230003), (230004), (230005), (230006), (230007), (230008),
    (230009), (230010), (230011), (230012), (230013), (230014), (230015), (230016),
    (230017), (230018);

INSERT INTO young_members (student_index, mentor_index) VALUES
    (230019, 230001),
    (230020, 230002),
    (230021, 230005),
    (230022, 230004),
    (230023, 230007);

INSERT INTO alumni (student_index) VALUES
    (230028), (230029), (230030);

INSERT INTO membership_stages (ms_id, ms_name) VALUES
    (1, 'Observer'),
    (2, 'Young member'),
    (3, 'Full member'),
    (4, 'Alumni');

INSERT INTO has_stage (student_index, ms_id, valid_from, valid_to) VALUES
    (230001, 1, DATE '2021-10-03', DATE '2021-11-06'),
    (230001, 2, DATE '2021-11-07', DATE '2022-02-19'),
    (230001, 3, DATE '2022-02-20', NULL),
    (230002, 1, DATE '2022-02-20', DATE '2022-03-19'),
    (230002, 2, DATE '2022-03-20', DATE '2022-10-08'),
    (230002, 3, DATE '2022-10-09', NULL),
    (230003, 1, DATE '2022-10-09', DATE '2022-11-12'),
    (230003, 2, DATE '2022-11-13', DATE '2023-02-18'),
    (230003, 3, DATE '2023-02-19', NULL),
    (230004, 1, DATE '2022-10-09', DATE '2022-11-12'),
    (230004, 2, DATE '2022-11-13', DATE '2023-02-18'),
    (230004, 3, DATE '2023-02-19', NULL),
    (230005, 1, DATE '2023-02-19', DATE '2023-03-18'),
    (230005, 2, DATE '2023-03-19', DATE '2023-10-07'),
    (230005, 3, DATE '2023-10-08', NULL),
    (230006, 1, DATE '2023-02-19', DATE '2023-03-18'),
    (230006, 2, DATE '2023-03-19', DATE '2023-10-07'),
    (230006, 3, DATE '2023-10-08', NULL),
    (230007, 1, DATE '2023-02-19', DATE '2023-03-18'),
    (230007, 2, DATE '2023-03-19', DATE '2023-10-07'),
    (230007, 3, DATE '2023-10-08', NULL),
    (230008, 1, DATE '2023-10-08', DATE '2023-11-11'),
    (230008, 2, DATE '2023-11-12', DATE '2024-02-17'),
    (230008, 3, DATE '2024-02-18', NULL),
    (230009, 1, DATE '2023-10-08', DATE '2023-11-11'),
    (230009, 2, DATE '2023-11-12', DATE '2024-02-17'),
    (230009, 3, DATE '2024-02-18', NULL),
    (230010, 1, DATE '2024-02-18', DATE '2024-03-16'),
    (230010, 2, DATE '2024-03-17', DATE '2024-10-05'),
    (230010, 3, DATE '2024-10-06', NULL),
    (230011, 1, DATE '2024-02-18', DATE '2024-03-16'),
    (230011, 2, DATE '2024-03-17', DATE '2024-10-05'),
    (230011, 3, DATE '2024-10-06', NULL),
    (230012, 1, DATE '2024-02-18', DATE '2024-03-16'),
    (230012, 2, DATE '2024-03-17', DATE '2024-10-05'),
    (230012, 3, DATE '2024-10-06', NULL),
    (230013, 1, DATE '2024-10-06', DATE '2024-11-09'),
    (230013, 2, DATE '2024-11-10', DATE '2025-02-15'),
    (230013, 3, DATE '2025-02-16', NULL),
    (230014, 1, DATE '2024-10-06', DATE '2024-11-09'),
    (230014, 2, DATE '2024-11-10', DATE '2025-02-15'),
    (230014, 3, DATE '2025-02-16', NULL),
    (230015, 1, DATE '2025-02-16', DATE '2025-03-15'),
    (230015, 2, DATE '2025-03-16', DATE '2025-10-11'),
    (230015, 3, DATE '2025-10-12', NULL),
    (230016, 1, DATE '2025-02-16', DATE '2025-03-15'),
    (230016, 2, DATE '2025-03-16', DATE '2025-10-11'),
    (230016, 3, DATE '2025-10-12', NULL),
    (230017, 1, DATE '2025-10-12', DATE '2025-11-15'),
    (230017, 2, DATE '2025-11-16', DATE '2026-02-21'),
    (230017, 3, DATE '2026-02-22', NULL),
    (230018, 1, DATE '2025-10-12', DATE '2025-11-15'),
    (230018, 2, DATE '2025-11-16', DATE '2026-02-21'),
    (230018, 3, DATE '2026-02-22', NULL),
    (230019, 1, DATE '2025-10-12', DATE '2026-02-21'),
    (230019, 2, DATE '2026-02-22', NULL),
    (230020, 1, DATE '2025-10-12', DATE '2026-02-21'),
    (230020, 2, DATE '2026-02-22', NULL),
    (230021, 1, DATE '2025-10-12', DATE '2026-02-21'),
    (230021, 2, DATE '2026-02-22', NULL),
    (230022, 1, DATE '2025-10-12', DATE '2026-02-21'),
    (230022, 2, DATE '2026-02-22', NULL),
    (230023, 1, DATE '2025-10-12', DATE '2026-02-21'),
    (230023, 2, DATE '2026-02-22', NULL),
    (230024, 1, DATE '2026-03-11', NULL),
    (230025, 1, DATE '2026-03-11', NULL),
    (230026, 1, DATE '2026-03-11', NULL),
    (230027, 1, DATE '2026-03-11', NULL),
    (230028, 1, DATE '2019-10-06', DATE '2019-11-09'),
    (230028, 2, DATE '2019-11-10', DATE '2020-02-15'),
    (230028, 3, DATE '2020-02-16', DATE '2025-02-15'),
    (230028, 4, DATE '2025-02-16', NULL),
    (230029, 1, DATE '2019-10-06', DATE '2019-11-09'),
    (230029, 2, DATE '2019-11-10', DATE '2020-02-15'),
    (230029, 3, DATE '2020-02-16', DATE '2025-02-15'),
    (230029, 4, DATE '2025-02-16', NULL),
    (230030, 1, DATE '2018-10-07', DATE '2018-11-10'),
    (230030, 2, DATE '2018-11-11', DATE '2019-02-16'),
    (230030, 3, DATE '2019-02-17', DATE '2024-02-17'),
    (230030, 4, DATE '2024-02-18', NULL);

INSERT INTO organizational_functions (of_id, of_title) VALUES
    (1, 'President'),
    (2, 'Treasurer'),
    (3, 'Secretary'),
    (4, 'HR Coordinator'),
    (5, 'FR Coordinator'),
    (6, 'PR Coordinator'),
    (7, 'IT Coordinator'),
    (8, 'Design Coordinator'),
    (9, 'Logistics Coordinator'),
    (10, 'Social Activities Coordinator'),
    (11, 'Vivaldi Coordinator'),
    (12, 'Grants Coordinator');

INSERT INTO board (of_id) VALUES
    (1), (2), (3), (4), (5), (6), (7);

INSERT INTO non_board (of_id) VALUES
    (8), (9), (10), (11), (12);

INSERT INTO function_mandates (student_index, of_id, mandate_year) VALUES
    (230001, 1, 2025),
    (230002, 2, 2025),
    (230003, 3, 2025),
    (230004, 4, 2025),
    (230005, 5, 2025),
    (230006, 6, 2025),
    (230007, 7, 2025),
    (230008, 8, 2025),
    (230009, 9, 2025),
    (230010, 10, 2025),
    (230011, 11, 2025),
    (230012, 12, 2025),
    (230013, 11, 2024);

INSERT INTO activity_types (at_id, at_name, at_description) VALUES
    (1, 'Weekly Meeting', 'Regular weekly meeting held by the President in Baraka 2.2.'),
    (2, 'BCS Meeting', 'Weekly BEST Course in Summer meeting held at the BEST Benches by the Main Organisers.'),
    (3, 'Core Team Meeting', 'Operational meeting for the core team of a specific event.'),
    (4, 'FR Call', 'Sponsorship calling session for an event, supported by members of the organisation.'),
    (5, 'PR Workshop', 'Workshop for promotion and social media posts tasks.'),
    (6, 'Design Workshop', 'Workshop for event visual identity and design tasks.'),
    (7, 'IT Workshop', 'Workshop for event technical and IT tasks.'),
    (8, 'FR Workshop', 'Workshop for fundraising preparation.'),
    (9, 'Logistics Workshop', 'Workshop for logistics planning.'),
    (10, 'Social Activities Workshop', 'Workshop for social activities.'),
    (11, 'Full Member Meeting', 'Monthly meeting intended for full members.'),
    (12, 'Board Meeting', 'Weekly meeting intended for Board members.'),
    (13, 'Training Session', 'Soft skills training session coordinated by HR and held by Trainers.'),
    (14, 'Event Evaluation', 'Meeting after an event used for evaluation and feedback.'),
    (15, 'Party', 'Social activity or party organised by BEST Skopje.');

INSERT INTO event_types (et_id, et_name, et_description) VALUES
    (1, 'Motivational Weekend', 'Traditional BEST Skopje event where members spend a weekend outside their usual environment, combining studying, socializing and team-building activities.'),
    (2, 'Job Fair', 'Large annual BEST Skopje event where students connect directly with technical and technology companies through stands, presentations, internships and employment opportunities.'),
    (3, 'BEST Course in Summer', 'International educational event that brings students from Europe to Macedonia for lectures, workshops, company visits, cultural activities and social programme.'),
    (4, 'beBESTie', 'Recruitment event where UKIM students learn about BEST Skopje and try practical activities from PR, design, IT and fundraising.'),
    (5, 'BOMBA', 'Event where observers can become young members and full members can become mentors.'),
    (6, 'Hackathon', 'Event for innovation, teamwork and hands-on learning where students solve real-world challenges in a dynamic environment.'),
    (7, 'Birthday', 'Annual celebration of BEST Skopje''s birthday on 9 March.'),
    (8, 'Other Event', 'Other event type not covered by the predefined event categories.');

INSERT INTO locations (l_id, l_name) VALUES
    (1, 'Baraka 2.2'),
    (2, 'Baraka 2.1'),
    (3, 'Baraka 3.1'),
    (4, 'Baraka 3.2'),
    (5, 'Netaville'),
    (6, 'Base42'),
    (7, 'BEST Office'),
    (8, 'BEST Benches'),
    (9, 'Vrteshka Mountain Lodge'),
    (10, 'FINKI Room 117'),
    (11, 'FINKI Amphitheater'),
    (12, 'INNOFEIT'),
    (13, 'FEEIT'),
    (14, 'MFS'),
    (15, 'Goce Delchev Student Dormitory'),
    (16, 'FINKI Partner Faculty Premises'),
    (17, 'Nikola Karev Ohrid Dormitory'),
    (18, 'Netcetera Campus'),
    (19, 'Macedonia Square');

INSERT INTO event_editions (ee_id, et_id, ee_title, ee_start_time, ee_end_time) VALUES
    (1, 1, 'Winter Motivational Weekend 2026', TIMESTAMP '2026-02-06 18:00:00', TIMESTAMP '2026-02-08 16:00:00'),
    (2, 5, 'BOMBA Spring 2026', TIMESTAMP '2026-02-21 10:00:00', TIMESTAMP '2026-02-22 16:00:00'),
    (3, 7, 'BEST Skopje Birthday 2026', TIMESTAMP '2026-03-09 20:00:00', TIMESTAMP '2026-03-10 01:00:00'),
    (4, 2, 'Job Fair 2026', TIMESTAMP '2026-03-23 09:00:00', TIMESTAMP '2026-03-27 18:00:00'),
    (5, 1, 'Spring Motivational Weekend 2026', TIMESTAMP '2026-05-15 18:00:00', TIMESTAMP '2026-05-17 16:00:00'),
    (6, 3, 'BEST Course in Summer 2026', TIMESTAMP '2026-07-05 12:00:00', TIMESTAMP '2026-07-13 18:00:00'),
    (7, 4, 'beBESTie Autumn 2026', TIMESTAMP '2026-10-05 17:00:00', TIMESTAMP '2026-10-05 21:00:00'),
    (8, 6, 'Impact Hackathon 2026', TIMESTAMP '2026-12-12 09:00:00', TIMESTAMP '2026-12-13 18:00:00');

INSERT INTO event_edition_locations (ee_id, l_id) VALUES
    (1, 9),
    (2, 10),
    (3, 7),
    (4, 12),
    (4, 13),
    (4, 14),
    (5, 9),
    (6, 15),
    (6, 16),
    (6, 17),
    (7, 11),
    (8, 18);

INSERT INTO event_session_types (est_id, est_name, est_description) VALUES
    (1, 'Official Opening', 'Formal opening session for an event edition.'),
    (2, 'Company Stands', 'Session in which students visit company stands.'),
    (3, 'Company Presentation', 'Session in which companies present their work and opportunities.'),
    (4, 'Official Closing', 'Formal closing session for an event edition.'),
    (5, 'Lecture Timeslot', 'Academic or professional lecture session.'),
    (6, 'Coffee Break', 'Break between programme sessions.'),
    (7, 'Lunch Break', 'Meal break during an event programme.'),
    (8, 'City Rally', 'Outdoor city exploration activity for participants.'),
    (9, 'Social Activity', 'Informal social session inside an event.'),
    (10, 'Company Visit', 'Visit to a partner company during an event.'),
    (11, 'Weekend Trip', 'Weekend trip session during an international course.'),
    (12, 'Participants Working', 'Hackathon working block where participants solve tasks.'),
    (13, 'Party', 'Evening party or celebration.'),
    (14, 'Mentor Matching', 'BOMBA session for assigning angels to young members.'),
    (15, 'Info Meeting', 'Informative recruitment session for students.'),
    (16, 'Workshop Session', 'Hands-on session or practical workshop.'),
    (17, 'Team Building', 'Team-building activity for members or participants.');

INSERT INTO event_sessions (
    ee_id, es_no, est_id, l_id, es_start_time, es_title, needed_no_volunteers
) VALUES
    (1, 1, 1, 9, TIMESTAMP '2026-02-06 20:00:00', 'Winter MW Opening', 4),
    (1, 2, 16, 9, TIMESTAMP '2026-02-07 10:00:00', 'Morning Workshop', 2),
    (1, 3, 17, 9, TIMESTAMP '2026-02-07 17:00:00', 'Team Building Challenge', 4),
    (1, 4, 4, 9, TIMESTAMP '2026-02-08 14:00:00', 'Winter MW Closing', 3),
    (2, 1, 1, 10, TIMESTAMP '2026-02-21 10:30:00', 'BOMBA Opening', 3),
    (2, 2, 16, 10, TIMESTAMP '2026-02-21 12:00:00', 'Membership Workshop', 2),
    (2, 3, 14, 10, TIMESTAMP '2026-02-22 14:00:00', 'Angel Matching', 2),
    (3, 1, 13, 7, TIMESTAMP '2026-03-09 21:00:00', 'BEST Skopje Birthday Party', 3),
    (4, 1, 1, 12, TIMESTAMP '2026-03-23 10:00:00', 'Job Fair Opening Ceremony', 4),
    (4, 2, 2, 13, TIMESTAMP '2026-03-24 10:00:00', 'Technical Companies Stands', 4),
    (4, 3, 2, 14, TIMESTAMP '2026-03-24 10:00:00', 'Engineering Companies Stands', 4),
    (4, 4, 3, 13, TIMESTAMP '2026-03-26 12:00:00', 'Career Development Presentations', 3),
    (4, 5, 4, 13, TIMESTAMP '2026-03-27 17:00:00', 'Job Fair Closing', 3),
    (5, 1, 1, 9, TIMESTAMP '2026-05-15 20:00:00', 'Spring MW Opening', 4),
    (5, 2, 16, 9, TIMESTAMP '2026-05-16 10:00:00', 'Afternoon Workshop', 2),
    (5, 3, 9, 9, TIMESTAMP '2026-05-16 21:00:00', 'Campfire Social', 4),
    (5, 4, 4, 9, TIMESTAMP '2026-05-17 14:00:00', 'Spring MW Closing', 3),
    (6, 1, 1, 15, TIMESTAMP '2026-07-05 18:00:00', 'Course Opening', 4),
    (6, 2, 5, 16, TIMESTAMP '2026-07-06 10:00:00', 'First Academic Lecture', 1),
    (6, 3, 6, 16, TIMESTAMP '2026-07-06 11:30:00', 'Morning Coffee Break', 2),
    (6, 4, 10, 18, TIMESTAMP '2026-07-07 14:00:00', 'Partner Company Visit', 4),
    (6, 5, 8, 19, TIMESTAMP '2026-07-08 18:00:00', 'Skopje City Rally', 4),
    (6, 6, 9, 15, TIMESTAMP '2026-07-09 21:00:00', 'International Evening', 5),
    (6, 7, 11, 17, TIMESTAMP '2026-07-11 09:00:00', 'Ohrid Weekend Trip', 6),
    (6, 8, 4, 16, TIMESTAMP '2026-07-13 17:00:00', 'Course Closing', 4),
    (7, 1, 15, 11, TIMESTAMP '2026-10-05 17:30:00', 'beBESTie Info Meeting', 4),
    (7, 2, 16, 11, TIMESTAMP '2026-10-05 18:30:00', 'Hands-on BEST Process Trial', 4),
    (8, 1, 1, 18, TIMESTAMP '2026-12-12 09:30:00', 'Hackathon Opening', 4),
    (8, 2, 12, 18, TIMESTAMP '2026-12-12 10:00:00', 'Challenge Work Block 1', 2),
    (8, 3, 6, 18, TIMESTAMP '2026-12-12 12:00:00', 'Coffee Break', 2),
    (8, 4, 7, 18, TIMESTAMP '2026-12-12 14:00:00', 'Lunch Break', 3),
    (8, 5, 12, 18, TIMESTAMP '2026-12-12 15:00:00', 'Challenge Work Block 2', 2),
    (8, 6, 13, 18, TIMESTAMP '2026-12-12 21:00:00', 'Hackathon Party', 3),
    (8, 7, 4, 18, TIMESTAMP '2026-12-13 17:00:00', 'Hackathon Closing', 4);

INSERT INTO event_function_types (eft_id, eft_name) VALUES
    (1, 'Main Organiser'),
    (2, 'Corporate Relations Responsible'),
    (3, 'Logistics Responsible'),
    (4, 'PR Responsible'),
    (5, 'FR Responsible'),
    (6, 'IT Responsible'),
    (7, 'Design Responsible'),
    (8, 'Social Responsible'),
    (9, 'Participants Responsible'),
    (10, 'Academic Responsible');

INSERT INTO event_functions (ee_id, ef_no, eft_id) VALUES
    (1, 1, 1),
    (3, 1, 1),
    (4, 1, 1),
    (4, 2, 1),
    (4, 3, 2),
    (4, 4, 2),
    (4, 5, 3),
    (4, 6, 4),
    (4, 7, 5),
    (4, 8, 6),
    (4, 9, 7),
    (5, 1, 1),
    (6, 1, 1),
    (6, 2, 1),
    (6, 3, 3),
    (6, 4, 3),
    (6, 5, 8),
    (6, 6, 8),
    (6, 7, 4),
    (6, 8, 5),
    (6, 9, 6),
    (6, 10, 7),
    (6, 11, 9),
    (6, 12, 10),
    (7, 1, 1),
    (8, 1, 1),
    (8, 2, 9),
    (8, 3, 5),
    (8, 4, 6),
    (8, 5, 7),
    (8, 6, 4);

INSERT INTO activity_sessions (as_id, at_id, l_id, as_start_time) VALUES
    (1, 1, 1, TIMESTAMP '2026-02-09 20:00:00'),
    (2, 1, 1, TIMESTAMP '2026-03-02 20:00:00'),
    (3, 1, 1, TIMESTAMP '2026-03-16 20:00:00'),
    (4, 1, 1, TIMESTAMP '2026-04-06 20:00:00'),
    (5, 1, 1, TIMESTAMP '2026-05-04 20:00:00'),
    (6, 12, 7, TIMESTAMP '2026-03-03 19:00:00'),
    (7, 12, 7, TIMESTAMP '2026-03-10 19:00:00'),
    (8, 11, 7, TIMESTAMP '2026-03-12 18:00:00'),
    (9, 11, 7, TIMESTAMP '2026-04-16 18:00:00'),
    (10, 4, 7, TIMESTAMP '2026-03-14 12:00:00'),
    (11, 6, 5, TIMESTAMP '2026-03-08 17:00:00'),
    (12, 3, 7, TIMESTAMP '2026-03-05 18:30:00'),
    (13, 14, 7, TIMESTAMP '2026-03-30 18:00:00'),
    (14, 2, 8, TIMESTAMP '2026-07-06 20:00:00'),
    (15, 2, 8, TIMESTAMP '2026-07-08 20:00:00'),
    (16, 2, 8, TIMESTAMP '2026-07-10 20:00:00'),
    (17, 3, 7, TIMESTAMP '2026-06-25 18:30:00'),
    (18, 5, 2, TIMESTAMP '2026-06-18 17:00:00'),
    (19, 9, 4, TIMESTAMP '2026-06-19 17:00:00'),
    (20, 10, 1, TIMESTAMP '2026-06-21 17:00:00'),
    (21, 13, 6, TIMESTAMP '2026-04-20 17:00:00'),
    (22, 7, 6, TIMESTAMP '2026-11-26 17:00:00'),
    (23, 3, 7, TIMESTAMP '2026-12-01 18:30:00'),
    (24, 14, 7, TIMESTAMP '2026-12-15 18:00:00'),
    (25, 15, 7, TIMESTAMP '2026-03-09 21:00:00'),
    (26, 14, 7, TIMESTAMP '2026-02-10 18:00:00'),
    (27, 14, 7, TIMESTAMP '2026-02-24 18:00:00'),
    (28, 14, 7, TIMESTAMP '2026-03-11 18:00:00'),
    (29, 14, 7, TIMESTAMP '2026-05-19 18:00:00'),
    (30, 14, 7, TIMESTAMP '2026-07-16 18:00:00'),
    (31, 14, 7, TIMESTAMP '2026-10-06 18:00:00');

INSERT INTO related_to (as_id, ee_id) VALUES
    (14, 6),
    (15, 6),
    (16, 6),
    (12, 4),
    (17, 6),
    (23, 8),
    (10, 4),
    (18, 6),
    (19, 6),
    (20, 6),
    (11, 4),
    (22, 8),
    (13, 4),
    (24, 8),
    (25, 3),
    (26, 1),
    (27, 2),
    (28, 3),
    (29, 5),
    (30, 6),
    (31, 7);

INSERT INTO applications (
    student_index, ee_id, ef_no, motivational_letter, application_type, application_status
) VALUES
    (230010, 1, 1, 'I want to organise a productive winter weekend with enough space for bonding and rest.', 'event_function', 'accepted'),
    (230013, 3, 1, 'I want to coordinate the birthday celebration and include members from different generations.', 'event_function', 'accepted'),
    (230001, 4, 1, 'I want to lead Job Fair because I have experience coordinating faculty and company communication.', 'event_function', 'accepted'),
    (230016, 4, 2, 'I want to support the Job Fair team by coordinating logistics and the daily schedule.', 'event_function', 'accepted'),
    (230005, 4, 3, 'I want to contact companies and build partnerships for Job Fair.', 'event_function', 'accepted'),
    (230011, 4, 4, 'I want to help with company relations and make the stands useful for students.', 'event_function', 'accepted'),
    (230009, 4, 5, 'I want to handle venue logistics for FEEIT, MFS and INNOFEIT.', 'event_function', 'accepted'),
    (230006, 4, 6, 'I want to promote Job Fair clearly to students and companies.', 'event_function', 'accepted'),
    (230015, 4, 7, 'I want to work on sponsorship packages and support FR calls.', 'event_function', 'accepted'),
    (230007, 4, 8, 'I want to manage the technical side and online information for Job Fair.', 'event_function', 'accepted'),
    (230008, 4, 9, 'I want to create a consistent visual identity for Job Fair.', 'event_function', 'accepted'),
    (230012, 5, 1, 'I want to organise a spring motivational weekend before the exam period.', 'event_function', 'accepted'),
    (230002, 6, 1, 'I want to coordinate the summer course and keep the team aligned before participants arrive.', 'event_function', 'accepted'),
    (230003, 6, 2, 'I want to be responsible for the participant experience and international atmosphere.', 'event_function', 'accepted'),
    (230009, 6, 3, 'I want to coordinate accommodation and faculty rooms for the course.', 'event_function', 'accepted'),
    (230016, 6, 4, 'I want to support transport, dormitory logistics and weekend trip planning.', 'event_function', 'accepted'),
    (230010, 6, 5, 'I want to organise city rally, parties and participant bonding activities.', 'event_function', 'accepted'),
    (230013, 6, 6, 'I want to help participants experience Macedonia through cultural activities.', 'event_function', 'accepted'),
    (230006, 6, 7, 'I want to document the course and promote it before and during the event.', 'event_function', 'accepted'),
    (230005, 6, 8, 'I want to secure support for accommodation, food and materials.', 'event_function', 'accepted'),
    (230007, 6, 9, 'I want to maintain participant information and technical support for the course.', 'event_function', 'accepted'),
    (230008, 6, 10, 'I want to prepare course materials and visuals.', 'event_function', 'accepted'),
    (230017, 6, 11, 'I want to communicate with international participants and support them during the event.', 'event_function', 'accepted'),
    (230018, 6, 12, 'I want to coordinate lectures, workshops and academic content with the partner faculty.', 'event_function', 'accepted'),
    (230004, 7, 1, 'I want to organise recruitment so interested students understand how BEST works.', 'event_function', 'accepted'),
    (230014, 8, 1, 'I want to organise a hackathon focused on teamwork, innovation and real-world challenges.', 'event_function', 'accepted'),
    (230017, 8, 2, 'I want to communicate with teams and keep the participant flow smooth.', 'event_function', 'accepted'),
    (230005, 8, 3, 'I want to coordinate sponsorship and partner communication for the hackathon.', 'event_function', 'accepted'),
    (230020, 8, 4, 'I want to support the technical infrastructure and team submissions.', 'event_function', 'accepted'),
    (230021, 8, 5, 'I want to create hackathon visuals and participant materials.', 'event_function', 'accepted'),
    (230023, 8, 6, 'I want to promote the hackathon to students and document the event.', 'event_function', 'accepted'),
    (230024, 2, NULL, 'I want to become a young member because I want to contribute more actively to BEST Skopje.', 'young_member', 'accepted'),
    (230025, 2, NULL, 'I want to become a young member and learn from older members.', 'young_member', 'submitted'),
    (230015, 2, NULL, 'I want to become a mentor and help observers feel included.', 'mentor', 'accepted'),
    (230026, 6, NULL, 'I want to attend the course because the academic topic is connected to my studies and I want to learn in an international environment.', 'event_participation', 'submitted'),
    (230027, 6, NULL, 'I want to join the summer course to gain practical knowledge, meet students from other countries and represent my faculty well.', 'event_participation', 'accepted'),
    (230031, 8, NULL, 'I want to participate in the hackathon because I enjoy solving real problems in a team and want to improve my practical skills.', 'event_participation', 'submitted'),
    (230032, 8, NULL, 'I want to take part in the hackathon to work on an innovative solution and learn from mentors and other participants.', 'event_participation', 'accepted');

INSERT INTO holds_event_function (
    student_index, ee_id, ef_no
)
SELECT student_index, ee_id, ef_no
FROM applications
WHERE application_type = 'event_function'
  AND application_status = 'accepted'
  AND ef_no IS NOT NULL;

INSERT INTO participation (
    student_index, as_id, rsvp_status, rsvp_at, attendance_status
) VALUES
    (230001, 3, 'going', TIMESTAMP '2026-03-15 20:10:00', 'present'),
    (230002, 3, 'going', TIMESTAMP '2026-03-15 20:12:00', 'present'),
    (230003, 3, 'going', TIMESTAMP '2026-03-15 20:14:00', 'late'),
    (230004, 3, 'going', TIMESTAMP '2026-03-15 20:16:00', 'present'),
    (230019, 3, 'maybe', TIMESTAMP '2026-03-15 21:00:00', 'not_checked'),
    (230024, 3, 'not_going', TIMESTAMP '2026-03-15 21:15:00', 'excused'),
    (230001, 7, 'going', TIMESTAMP '2026-03-09 12:00:00', 'present'),
    (230002, 7, 'going', TIMESTAMP '2026-03-09 12:05:00', 'present'),
    (230003, 7, 'going', TIMESTAMP '2026-03-09 12:10:00', 'present'),
    (230004, 7, 'going', TIMESTAMP '2026-03-09 12:15:00', 'present'),
    (230005, 7, 'going', TIMESTAMP '2026-03-09 12:20:00', 'present'),
    (230006, 7, 'going', TIMESTAMP '2026-03-09 12:25:00', 'present'),
    (230007, 7, 'going', TIMESTAMP '2026-03-09 12:30:00', 'present'),
    (230003, 8, 'going', TIMESTAMP '2026-03-11 15:00:00', 'present'),
    (230005, 8, 'not_going', TIMESTAMP '2026-03-11 15:20:00', 'absent'),
    (230006, 10, 'going', TIMESTAMP '2026-03-13 10:00:00', 'present'),
    (230011, 10, 'going', TIMESTAMP '2026-03-13 10:20:00', 'present'),
    (230008, 11, 'going', TIMESTAMP '2026-03-07 13:00:00', 'present'),
    (230021, 22, 'going', TIMESTAMP '2026-11-25 11:00:00', 'not_checked'),
    (230002, 14, 'going', TIMESTAMP '2026-07-05 21:00:00', 'present'),
    (230003, 14, 'going', TIMESTAMP '2026-07-05 21:02:00', 'present'),
    (230017, 14, 'going', TIMESTAMP '2026-07-05 21:08:00', 'present'),
    (230018, 21, 'going', TIMESTAMP '2026-04-19 09:30:00', 'present'),
    (230001, 12, 'going', TIMESTAMP '2026-03-04 11:00:00', 'present'),
    (230005, 12, 'going', TIMESTAMP '2026-03-04 11:05:00', 'present'),
    (230006, 12, 'going', TIMESTAMP '2026-03-04 11:10:00', 'present'),
    (230007, 12, 'going', TIMESTAMP '2026-03-04 11:15:00', 'present'),
    (230008, 12, 'going', TIMESTAMP '2026-03-04 11:20:00', 'present'),
    (230009, 12, 'going', TIMESTAMP '2026-03-04 11:25:00', 'present'),
    (230011, 12, 'going', TIMESTAMP '2026-03-04 11:30:00', 'present'),
    (230015, 12, 'going', TIMESTAMP '2026-03-04 11:35:00', 'present'),
    (230016, 12, 'going', TIMESTAMP '2026-03-04 11:40:00', 'present'),
    (230001, 13, 'going', TIMESTAMP '2026-03-29 18:00:00', 'present'),
    (230005, 13, 'going', TIMESTAMP '2026-03-29 18:05:00', 'present'),
    (230006, 13, 'going', TIMESTAMP '2026-03-29 18:10:00', 'present'),
    (230007, 13, 'going', TIMESTAMP '2026-03-29 18:15:00', 'present'),
    (230008, 13, 'going', TIMESTAMP '2026-03-29 18:20:00', 'present'),
    (230009, 13, 'going', TIMESTAMP '2026-03-29 18:25:00', 'present'),
    (230011, 13, 'going', TIMESTAMP '2026-03-29 18:30:00', 'present'),
    (230015, 13, 'going', TIMESTAMP '2026-03-29 18:35:00', 'present'),
    (230016, 13, 'going', TIMESTAMP '2026-03-29 18:40:00', 'present'),
    (230002, 17, 'going', TIMESTAMP '2026-06-24 12:00:00', 'present'),
    (230003, 17, 'going', TIMESTAMP '2026-06-24 12:05:00', 'present'),
    (230005, 17, 'going', TIMESTAMP '2026-06-24 12:10:00', 'present'),
    (230006, 17, 'going', TIMESTAMP '2026-06-24 12:15:00', 'present'),
    (230007, 17, 'going', TIMESTAMP '2026-06-24 12:20:00', 'present'),
    (230008, 17, 'going', TIMESTAMP '2026-06-24 12:25:00', 'present'),
    (230009, 17, 'going', TIMESTAMP '2026-06-24 12:30:00', 'present'),
    (230010, 17, 'going', TIMESTAMP '2026-06-24 12:35:00', 'present'),
    (230013, 17, 'going', TIMESTAMP '2026-06-24 12:40:00', 'present'),
    (230016, 17, 'going', TIMESTAMP '2026-06-24 12:45:00', 'present'),
    (230017, 17, 'going', TIMESTAMP '2026-06-24 12:50:00', 'present'),
    (230018, 17, 'going', TIMESTAMP '2026-06-24 12:55:00', 'present'),
    (230002, 30, 'going', TIMESTAMP '2026-07-15 12:00:00', 'present'),
    (230003, 30, 'going', TIMESTAMP '2026-07-15 12:05:00', 'present'),
    (230005, 30, 'going', TIMESTAMP '2026-07-15 12:10:00', 'present'),
    (230006, 30, 'going', TIMESTAMP '2026-07-15 12:15:00', 'present'),
    (230007, 30, 'going', TIMESTAMP '2026-07-15 12:20:00', 'present'),
    (230008, 30, 'going', TIMESTAMP '2026-07-15 12:25:00', 'present'),
    (230009, 30, 'going', TIMESTAMP '2026-07-15 12:30:00', 'present'),
    (230010, 30, 'going', TIMESTAMP '2026-07-15 12:35:00', 'present'),
    (230013, 30, 'going', TIMESTAMP '2026-07-15 12:40:00', 'present'),
    (230016, 30, 'going', TIMESTAMP '2026-07-15 12:45:00', 'present'),
    (230017, 30, 'going', TIMESTAMP '2026-07-15 12:50:00', 'present'),
    (230018, 30, 'going', TIMESTAMP '2026-07-15 12:55:00', 'present'),
    (230014, 23, 'going', TIMESTAMP '2026-11-30 14:00:00', 'present'),
    (230017, 23, 'going', TIMESTAMP '2026-11-30 14:05:00', 'present'),
    (230005, 23, 'going', TIMESTAMP '2026-11-30 14:10:00', 'present'),
    (230020, 23, 'going', TIMESTAMP '2026-11-30 14:15:00', 'present'),
    (230021, 23, 'going', TIMESTAMP '2026-11-30 14:20:00', 'present'),
    (230023, 23, 'going', TIMESTAMP '2026-11-30 14:25:00', 'present'),
    (230010, 26, 'going', TIMESTAMP '2026-02-09 12:00:00', 'present'),
    (230004, 27, 'going', TIMESTAMP '2026-02-23 12:00:00', 'present'),
    (230013, 28, 'going', TIMESTAMP '2026-03-10 12:00:00', 'present'),
    (230012, 29, 'going', TIMESTAMP '2026-05-18 12:00:00', 'present'),
    (230004, 31, 'going', TIMESTAMP '2026-10-05 12:00:00', 'present'),
    (230014, 24, 'going', TIMESTAMP '2026-12-14 12:00:00', 'present'),
    (230017, 24, 'going', TIMESTAMP '2026-12-14 12:05:00', 'present'),
    (230005, 24, 'going', TIMESTAMP '2026-12-14 12:10:00', 'present'),
    (230020, 24, 'going', TIMESTAMP '2026-12-14 12:15:00', 'present'),
    (230021, 24, 'going', TIMESTAMP '2026-12-14 12:20:00', 'present'),
    (230023, 24, 'going', TIMESTAMP '2026-12-14 12:25:00', 'present');

INSERT INTO required_attendance (
    student_index, as_id
)
SELECT student_index, as_id
FROM (
    SELECT fm.student_index, a.as_id
    FROM full_members fm
    CROSS JOIN activity_sessions a
    JOIN activity_types at
        ON at.at_id = a.at_id
    WHERE at.at_name IN ('Weekly Meeting', 'Full Member Meeting')

    UNION

    SELECT fm.student_index, a.as_id
    FROM function_mandates fm
    JOIN board b
        ON b.of_id = fm.of_id
    CROSS JOIN activity_sessions a
    JOIN activity_types at
        ON at.at_id = a.at_id
    WHERE at.at_name = 'Board Meeting'

    UNION

    SELECT hef.student_index, a.as_id
    FROM holds_event_function hef
    JOIN related_to rt
        ON rt.ee_id = hef.ee_id
    JOIN activity_sessions a
        ON a.as_id = rt.as_id
    JOIN activity_types at
        ON at.at_id = a.at_id
    WHERE at.at_name = 'Core Team Meeting'

    UNION

    SELECT fm.student_index, a.as_id
    FROM function_mandates fm
    JOIN board b
        ON b.of_id = fm.of_id
    CROSS JOIN activity_sessions a
    JOIN activity_types at
        ON at.at_id = a.at_id
    WHERE at.at_name = 'Event Evaluation'

    UNION

    SELECT hef.student_index, a.as_id
    FROM holds_event_function hef
    JOIN related_to rt
        ON rt.ee_id = hef.ee_id
    JOIN activity_sessions a
        ON a.as_id = rt.as_id
    JOIN activity_types at
        ON at.at_id = a.at_id
    WHERE at.at_name = 'Event Evaluation'
) mandatory_attendance
ORDER BY as_id, student_index;

INSERT INTO announces_absence (
    student_index, as_id, reason, announced_at
) VALUES
    (230011, 1, 'Traveling back to Skopje after the weekend.', TIMESTAMP '2026-02-08 19:30:00'),
    (230008, 3, 'Sick and unable to attend the weekly meeting.', TIMESTAMP '2026-03-16 12:30:00'),
    (230018, 5, 'Faculty project deadline on the same evening.', TIMESTAMP '2026-05-03 21:00:00'),
    (230003, 6, 'Faculty obligations at the same time.', TIMESTAMP '2026-03-02 15:18:00'),
    (230004, 6, 'Family obligation outside Skopje.', TIMESTAMP '2026-03-02 21:10:00'),
    (230005, 8, 'Faculty laboratory exercise at the same time.', TIMESTAMP '2026-03-11 15:18:00'),
    (230010, 9, 'Project deadline at faculty.', TIMESTAMP '2026-04-15 19:00:00'),
    (230001, 24, 'University exam preparation session.', TIMESTAMP '2026-12-14 18:20:00'),
    (230007, 26, 'Cannot attend because of work obligations.', TIMESTAMP '2026-02-09 16:30:00'),
    (230006, 27, 'Personal obligation during the evaluation time.', TIMESTAMP '2026-02-23 20:00:00'),
    (230005, 28, 'Sponsor meeting for another activity.', TIMESTAMP '2026-03-10 16:45:00'),
    (230006, 29, 'Company meeting for sponsorship preparation.', TIMESTAMP '2026-05-18 13:00:00'),
    (230002, 31, 'Faculty laboratory work at the same time.', TIMESTAMP '2026-10-05 19:00:00');

INSERT INTO responsible_for (
    student_index, as_id
) VALUES
    (230001, 1),
    (230001, 2),
    (230001, 3),
    (230001, 4),
    (230001, 5),
    (230001, 6),
    (230001, 7),
    (230001, 8),
    (230001, 9),
    (230001, 13),
    (230001, 24),
    (230001, 26),
    (230001, 27),
    (230001, 28),
    (230001, 29),
    (230001, 30),
    (230001, 31),
    (230002, 14),
    (230003, 14),
    (230002, 15),
    (230003, 15),
    (230002, 16),
    (230003, 16),
    (230016, 12),
    (230017, 17),
    (230014, 23),
    (230006, 18),
    (230008, 11),
    (230020, 22),
    (230015, 10),
    (230015, 8),
    (230009, 19),
    (230010, 20),
    (230004, 21),
    (230014, 21),
    (230018, 21),
    (230013, 25);

INSERT INTO volunteers (
    student_index, ee_id, es_no, volunteer_role
) VALUES
    (230019, 4, 2, 'stand support'),
    (230020, 4, 2, 'stand support'),
    (230021, 4, 3, 'student guide'),
    (230022, 4, 1, 'guest welcome'),
    (230023, 4, 5, 'closing support'),
    (230010, 6, 5, 'group guide'),
    (230013, 6, 6, 'social party organiser'),
    (230016, 6, 7, 'trip logistics'),
    (230018, 6, 2, 'observer of academic sessions'),
    (230024, 7, 1, 'registration desk'),
    (230019, 7, 2, 'observer support'),
    (230020, 8, 2, 'technical support'),
    (230021, 8, 3, 'break support'),
    (230022, 8, 4, 'lunch support'),
    (230023, 8, 7, 'closing speaker');

INSERT INTO companies (c_id, c_name, naframa_reference) VALUES
    (1, 'Netcetera', 'NAFRAMA-1001'),
    (2, 'Endava', 'NAFRAMA-1002'),
    (3, 'Seavus Education and Development Center', 'NAFRAMA-1003'),
    (4, 'CodeWell', 'NAFRAMA-1004'),
    (5, 'Slice', 'NAFRAMA-1005'),
    (6, 'EVN Macedonia', 'NAFRAMA-1006');

INSERT INTO cooperation (
    c_id, ee_id, cooperation_type
) VALUES
    (1, 4, 'sponsor'),
    (2, 4, 'participant'),
    (3, 4, 'participant'),
    (4, 4, 'participant'),
    (1, 6, 'supporter'),
    (5, 6, 'partner'),
    (1, 8, 'partner'),
    (2, 8, 'challenge_provider'),
    (6, 3, 'supporter');

COMMIT;