-- STUDENT PERFORMANCE --
CREATE OR REPLACE VIEW vw_student_performance AS
SELECT s.id AS student_id,
       p.name || ' ' || p.surname AS full_name,
       s.group_id,
       COUNT(st.id) FILTER (WHERE st.grade >= 3) AS passes,
       COUNT(st.id) FILTER (WHERE st.grade < 3) AS fails,
       ROUND(AVG(st.grade)::numeric, 2) AS avg_grade
FROM Students s
JOIN Physicals p ON s.person = p.passport_no
LEFT JOIN Statements st ON s.id = st.student_id
GROUP BY s.id, p.name, p.surname, s.group_id;

-- BIRTHDAYS ON THIS MONTH --
CREATE VIEW vw_birthdays_month AS
SELECT passport_no,
       name || ' ' || surname AS full_name,
       birthday,
       EXTRACT(DAY FROM birthday) AS day_of_month
FROM Physicals
WHERE EXTRACT(MONTH FROM birthday) = EXTRACT(MONTH FROM NOW())
ORDER BY day_of_month;