-- 1.
-- Εύρεση των πιο ενεργών χρηστών σε σχόλια. Αυτή η συνάρτηση υπολογίζει τον συνολικό αριθμό σχολίων ανά μέλος
-- σε έναν "προσωρινό πίνακα" (CTE) και στη συνέχεια τον ενώνει με τον πίνακα των μελών.

CREATE OR REPLACE FUNCTION get_top_commenters(limit_num INT)
RETURNS TABLE(email VARCHAR, "firstName" VARCHAR, "secondName" VARCHAR, total_comments BIGINT) AS $$
BEGIN
RETURN QUERY
WITH CommentCounts AS (
SELECT ac.email, COUNT(ac."commentID") AS cnt
FROM public."articleComment" ac
GROUP BY ac.email
)
SELECT m.email, m."firstName", m."secondName", cc.cnt
FROM public.member m
JOIN CommentCounts cc ON m.email = cc.email
ORDER BY cc.cnt DESC
LIMIT limit_num;
END;
$$ LANGUAGE plpgsql;

select get_top_commenters(10);
select email from get_top_commenters(10);

-- 2.
-- Κλείσιμο παλαιών επαγγελματικών εμπειριών: Βρίσκει όσες εμπειρίες (experience) έχουν δηλωθεί ως τρέχουσες (CURRENT)
-- αλλά το toYear είναι παλαιότερο από τη σημερινή ημερομηνία, τις ενημερώνει σε PAST
-- και επιστρέφει τα IDs και τις εταιρείες που ενημερώθηκαν.

CREATE OR REPLACE FUNCTION close_old_experiences(user_email VARCHAR)
RETURNS TABLE(updated_experience_id SMALLINT, company_name VARCHAR) AS $$
BEGIN
RETURN QUERY
-- Το CTE πραγματοποιεί αποκλειστικά το SELECT
WITH TargetExperiences AS (
SELECT e."experienceID"
FROM public.experience e
WHERE e.email = user_email
AND e."toYear" < CURRENT_DATE
AND e."workStatus" = 'CURRENT'
)
-- Το UPDATE εφαρμόζεται στον πίνακα φιλτράροντας με βάση το CTE
UPDATE public.experience
SET "workStatus" = 'PAST'
WHERE "experienceID" IN (SELECT "experienceID" FROM TargetExperiences)
RETURNING "experienceID", company;
END;
$$ LANGUAGE plpgsql;

select close_old_experiences('9dd338b037@example.com');

-- 3.
-- Εκκαθάριση εκκρεμών παλαιών προσκλήσεων Διαγράφει όλες τις προσκλήσεις δικτύωσης (invitation) που βρίσκονται ακόμα σε κατάσταση PENDING
-- και έχουν σταλεί πριν από μια συγκεκριμένη ημερομηνία, επιστρέφοντας τα στοιχεία αυτών που διαγράφηκαν.

CREATE OR REPLACE FUNCTION cleanup_old_invitations(cutoff_date DATE)
RETURNS TABLE(deleted_id SMALLINT, sender_email VARCHAR, receiver_email VARCHAR) AS $$
BEGIN
RETURN QUERY
-- Το CTE πραγματοποιεί αποκλειστικά το SELECT
WITH TargetInvitations AS (
SELECT i."invitationID"
FROM public.invitation i
WHERE i."dateSent" < cutoff_date
AND i."theStatus" = 'PENDING'
)
-- Το DELETE εφαρμόζεται στον πίνακα φιλτράροντας με βάση το CTE
DELETE FROM public.invitation
WHERE "invitationID" IN (SELECT "invitationID" FROM TargetInvitations)
RETURNING "invitationID", "senderEmail", "receiverEmail";
END;
$$ LANGUAGE plpgsql;

-- 4.
-- Θέλουμε να δημιουργήσουμε μια συνάρτηση η οποία θα υπολογίζει για κάθε μέλος (πίνακας member) δύο ξεχωριστές μετρικές:
-- Πόσα άρθρα έχει δημοσιεύσει (πίνακας article).
-- Πόσες ερωτήσεις έχει υποβάλει (πίνακας question).
-- Στη συνέχεια, το κεντρικό ερώτημα θα ενώνει και θα επιστρέφει αυτά τα δεδομένα.

CREATE OR REPLACE FUNCTION get_user_content_stats()
RETURNS TABLE(
email VARCHAR,
"firstName" VARCHAR,
"secondName" VARCHAR,
total_articles BIGINT,
total_questions BIGINT
) AS $$
BEGIN
RETURN QUERY


WITH ArticleCounts AS (
SELECT a.email, COUNT(a."articleID") AS art_count
FROM public.article a
GROUP BY a.email
),

QuestionCounts AS (
SELECT q.email, COUNT(q."questionID") AS q_count
FROM public.question q
GROUP BY q.email
)

-- Το κεντρικό SELECT που αξιοποιεί και τα δύο CTEs
SELECT
m.email,
m."firstName",
m."secondName",
COALESCE(ac.art_count, 0) AS total_articles,
COALESCE(qc.q_count, 0) AS total_questions
FROM public.member m
LEFT JOIN ArticleCounts ac ON m.email = ac.email
LEFT JOIN QuestionCounts qc ON m.email = qc.email;
END;
$$ LANGUAGE plpgsql;

select * from get_user_content_stats();