-- Β. Εξάσκηση στη δημιουργία εναυσμάτων στην PostgreSQL.
 
CREATE TABLE member_audit
(
  id SERIAL PRIMARY KEY,
  operation character(1),
  operation_time timestamp with time zone, --both date and time, with time zone
  email  character varying(30),
  message character varying
);
 
CREATE OR REPLACE FUNCTION member_update()  RETURNS trigger AS
$BODY$
BEGIN
IF (TG_OP = 'DELETE') THEN
   INSERT INTO member_audit(operation,operation_time,email,message) 
   SELECT 'D', now(), OLD.email, CONCAT('Deletion Attempted: ', OLD."firstName",' ',OLD."secondName");
   RETURN NULL;
ELSIF (TG_OP = 'UPDATE') THEN
   IF (NEW.email <> OLD.email) THEN
INSERT INTO member_audit(operation,operation_time,email,message) 
SELECT 'U', now(), NEW.email, CONCAT('email Update denied:',' ',NEW.email);
RETURN NULL;
   ELSE
INSERT INTO member_audit(operation,operation_time,email,message) 
SELECT 'U', now(), NEW.email, CONCAT(NEW."firstName",' ',NEW."secondName");
NEW."secondName" = OLD."secondName";
RETURN NEW;
   END IF;
ELSIF (TG_OP = 'INSERT') THEN
   IF (CAST( date_part('year',now()) as text ) = left(NEW.email,4)) THEN
insert into member_audit(operation,operation_time,email,message) 
SELECT 'I', now(), NEW.email, CONCAT(NEW."firstName",' ',NEW."secondName");
RETURN NEW;
   ELSE
RAISE EXCEPTION 'Invalid email';
   END IF;
END IF;
END;
$BODY$
LANGUAGE plpgsql;
 
CREATE TRIGGER student_monitor BEFORE INSERT OR UPDATE OR DELETE ON "member" 
FOR EACH ROW EXECUTE PROCEDURE member_update();
 
-- Γ. Δημιουργία και χρήση Όψεων (ενημερώσιμων και μη).
-- 1.
-- Updatable view: Δημιουργήστε μια ενημερώσιμη όψη πάνω στον πίνακα advertisement, 
-- η οποία θα απομονώνει μόνο τις αγγελίες που προσφέρουν αποκλειστικά δυνατότητα τηλεργασίας 
-- (δηλαδή το πεδίο "specialWorkCapability" έχει την τιμή 'REMOTED_WORK')
-- Ένα updatable view (ενημερώσιμη όψη) στην PostgreSQL σας επιτρέπει όχι μόνο να διαβάζετε δεδομένα, 
-- αλλά και να εκτελείτε εντολές INSERT, UPDATE και DELETE απευθείας πάνω του, 
-- με τις αλλαγές να εφαρμόζονται αυτόματα στον υποκείμενο πίνακα.
-- Για να είναι ένα view updatable από προεπιλογή στην PostgreSQL, πρέπει να αντλεί δεδομένα από έναν και μόνο πίνακα 
-- και να μην περιέχει συναρτήσεις συνάθροισης (GROUP BY, COUNT, κ.λπ.), υποερωτήματα στο SELECT, ή τελεστές όπως DISTINCT, UNION κ.λπ.
 
CREATE OR REPLACE VIEW public.remote_job_advertisements AS
SELECT 
    "advertisementID",
    title,
    industry,
    "jobType",
    country,
    salary,
    "specialWorkCapability",
    email
FROM public.advertisement
WHERE "specialWorkCapability" = 'REMOTED_WORK'
WITH CHECK OPTION; -- Αποτρέπει έναν χρήστη από το να κάνει INSERT ή UPDATE μέσω του view σε μια εγγραφή που δεν ικανοποιεί 
-- τη συνθήκη WHERE. Για παράδειγμα, αν προσπαθήσετε να εισάγετε μια αγγελία με "specialWorkCapability" = 'NONE', 
-- το σύστημα θα απορρίψει την ενέργεια, εξασφαλίζοντας την ακεραιότητα των δεδομένων της όψης.
 
-- Αύξηση του μισθού κατά 10% σε όλες τις αγγελίες τηλεργασίας που αφορούν τον κλάδο της Πληροφορικής ('IT').
UPDATE public.remote_job_advertisements
SET salary = salary * 1.10
WHERE industry = 'IT';
-- (Αυτή η εντολή θα ενημερώσει απευθείας τον πραγματικό πίνακα advertisement).
 
-- 2.
-- INSERT σε μη ενημερώσιμη όψη: 
-- Όψη για τη δημοσιοποίηση μιας αγγελίας προσφοράς εργασίας
-- Η πληροφορία μιας αγγελίας προσφοράς εργασίας είναι "σπασμένη" σε δύο πίνακες: τον κεντρικό πίνακα advertisement
-- και τον εξειδικευμένο υπο-πίνακα jobOffer
 
CREATE OR REPLACE VIEW public.job_offer_view AS
SELECT 
    a."advertisementID",
    a.title,
    a.industry,
    a.country,
    a.email,
    a.salary,
    j."jobDescription",
    j.company
FROM public.advertisement a
JOIN public."jobOffer" j ON a."advertisementID" = j."advertisementID";
 
CREATE OR REPLACE FUNCTION insert_into_job_offer_view()
RETURNS TRIGGER AS $$
BEGIN
    -- Εισαγωγή των βασικών στοιχείων στον πίνακα advertisement
    INSERT INTO public.advertisement (
        "advertisementID", 
        title, 
        industry, 
        country, 
        email, 
        salary
    ) VALUES (
        NEW."advertisementID", 
        NEW.title, 
        NEW.industry, 
        NEW.country, 
        NEW.email, 
        NEW.salary
    );
 
    -- Εισαγωγή των στοιχείων στον πίνακα jobOffer. Θυμηθείτε ότι το "advertisementid" είναι foreign key προς τον πίνακα ADVERTISEMENT, επομένως έπρεπε να προηγηθεί η εισγαγωγή στον πίνακα ADVERTISEMENT.
 
    INSERT INTO public."jobOffer" (
        "advertisementID", 
        "jobDescription", 
        company
    ) VALUES (
        NEW."advertisementID", 
        NEW."jobDescription", 
        NEW.company
    );
 
    -- Επιστρέφουμε τη νέα εγγραφή για να ολοκληρωθεί η διαδικασία στο View
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_instead_of_insert_job_offer
INSTEAD OF INSERT ON public.job_offer_view
FOR EACH ROW
EXECUTE FUNCTION insert_into_job_offer_view();