DatabaseProgramming: console_12.sql

--6 funkcii / 3 po covek
--KIKO FUNKCII::
--get_avg_rating(content_id) — vrakja prosecen rejting na film/serija

CREATE FUNCTION get_avg_rating(p_content_id INT)
RETURNS NUMERIC AS $$
DECLARE
  v_avg NUMERIC;
BEGIN
  SELECT ROUND(AVG(RatingValue), 2)
  INTO v_avg
  FROM Rating
  WHERE ContentContentID = p_content_id;

  RETURN v_avg;
END;
$$ LANGUAGE plpgsql;




--get_user_watch_count(user_id) — vrakja kolku sodrzini gledal nekoj korisnik

CREATE FUNCTION get_user_watch_count(p_user_id INT)
RETURNS INT AS $$
DECLARE
  v_count INT;
BEGIN
  SELECT COUNT(*)
  INTO v_count
  FROM WatchHistory
  WHERE UserUserID = p_user_id;

  RETURN v_count;
END;
$$ LANGUAGE plpgsql;





--get_content_genres(content_id) — gi vrakja site zhanri za nekoja sodrzina (deka se posebni tabeli, namesto da se prai join, da se dobie direkt sho zhanra e nekoj film/serija)

CREATE FUNCTION get_content_genres(p_content_id INT)
RETURNS VARCHAR AS $$
DECLARE
  v_genres VARCHAR;
BEGIN
  SELECT STRING_AGG(g.Name, ', ' ORDER BY g.Name)
  INTO v_genres
  FROM Genre g
  JOIN Content_Genre cg ON cg.GenreGenreID = g.GenreID
  WHERE cg.ContentContentID = p_content_id;

  RETURN COALESCE(v_genres, 'No genres found');
END;
$$ LANGUAGE plpgsql;





--Damjan FUNKCII:
--get_subscription_info(user_id) — sto platil nekoj korisnik

CREATE FUNCTION get_subscription_info(p_user_id INT)
RETURNS TABLE(plan_name VARCHAR, price NUMERIC, max_devices INT, status VARCHAR) AS $$
BEGIN
  RETURN QUERY
  SELECT
    s.Name,
    s.Price,
    s.MaxDevices,
    us.Status
  FROM User_Subscription us
  JOIN Subscription s ON s.SubscriptionID = us.SubscriptionSubscriptionID
  WHERE us.UserUserID = p_user_id
  LIMIT 1;
END;
$$ LANGUAGE plpgsql;






--get_series_episode_count(series_id) — vkupen broj na epizodi za serija


CREATE FUNCTION get_series_episode_count(p_series_id INT)
RETURNS INT AS $$
DECLARE
  v_count INT;
BEGIN
  SELECT COUNT(e.EpisodeID)
  INTO v_count
  FROM Episode e
  JOIN Season sea ON sea.SeasonID = e.SeasonSeasonID
  WHERE sea.SeriesSeriesID = p_series_id;

  RETURN v_count;
END;
$$ LANGUAGE plpgsql;






--get_top_content_by_genre(genre_name) — vrati top sodrzina spored zhanra

CREATE FUNCTION get_top_content_by_genre(p_genre_name VARCHAR)
RETURNS TABLE(title VARCHAR, avg_rating NUMERIC, total_ratings BIGINT) AS $$
BEGIN
  RETURN QUERY
  SELECT
    m.title,
    ROUND(AVG(r.RatingValue), 2) AS avg_rating,
    COUNT(r.RatingID) AS total_ratings
  FROM Media m
  JOIN Content_Genre cg ON cg.ContentContentID = m.ContentID
  JOIN Genre g ON g.GenreID = cg.GenreGenreID
  JOIN Rating r ON r.ContentContentID = m.ContentID
  WHERE g.Name = p_genre_name
  GROUP BY m.title
  ORDER BY avg_rating DESC
  LIMIT 10;
END;
$$ LANGUAGE plpgsql;





--KIKO TRIGERI

--Trigger 1 — koga ke se izbrishe korisnik, da se logira vo nova tabela

--ni trea nova tabela za ova:
CREATE TABLE IF NOT EXISTS User_Delete_Log (
  LogID      SERIAL PRIMARY KEY,
  UserID     INT,
  Username   VARCHAR(255),
  Email      VARCHAR(255),
  DeletedAt  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


--eve go trigerot:
CREATE FUNCTION trg_log_user_delete()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO User_Delete_Log (UserID, Username, Email)
  VALUES (OLD.UserID, OLD.Username, OLD.Email);
  RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_log_user_delete
BEFORE DELETE ON "User"
FOR EACH ROW
EXECUTE FUNCTION trg_log_user_delete();


--za da testiram deletnav user: DELETE FROM "User" WHERE UserID = 159089;
--za proverka deka stvarno e vo novata tabela: SELECT * FROM User_Delete_Log;






--Trigger 2 — koga ke se vnese rating, da se proveri dali e megju 1 i 10

CREATE FUNCTION trg_validate_rating()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.RatingValue < 1 OR NEW.RatingValue > 10 THEN
    RAISE EXCEPTION 'RatingValue mora da bide pomegu 1 i 10, vnesena vrednost: %', NEW.RatingValue;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_validate_rating
BEFORE INSERT OR UPDATE ON Rating
FOR EACH ROW
EXECUTE FUNCTION trg_validate_rating();

--za proverka deka ke frli greska: INSERT INTO Rating (RatingValue, UserUserID, ContentContentID)
--VALUES (15, 159090, 3);






--Trigger 3 — koga ke se vnese nov watch history zapis, avtomatski da se azhurira na progress = 100 ako e pominato vremetraenjeto

CREATE FUNCTION trg_auto_complete_progress()
RETURNS TRIGGER AS $$
DECLARE
  v_duration INT;
BEGIN
  -- da se zeme traenjeto na watchable
  SELECT duration INTO v_duration
  FROM Watchable
  WHERE WatchableID = NEW.WatchableWatchableID;

  -- ako progress e 90% ili povekje, set na 100%
  IF NEW.Progress_percentage >= 90 THEN
    NEW.Progress_percentage := 100;
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_auto_complete_progress
BEFORE INSERT ON WatchHistory
FOR EACH ROW
EXECUTE FUNCTION trg_auto_complete_progress();

--proverka:

--vnesuvanje so progress 95. treba da stane 100

--INSERT INTO WatchHistory (WatchedAt, Progress_percentage, UserUserID, ContentContentID, WatchableWatchableID)
--VALUES (CURRENT_DATE, 95, 159090, 1, 1);


--proverka za dali stanalo (vekje e izvrshena gornata sho znaci mozeme so ovaa samo da proverime na labot):

--SELECT Progress_percentage FROM WatchHistory
--WHERE UserUserID = 159090
--ORDER BY HistoryID DESC
--LIMIT 1;







--Damjan TRIGERI:

--TRIGGER 1 - koga end_date e pominat na subscription, avtomatski da se stavi status EXPIRED

CREATE FUNCTION trg_auto_expire_subscription()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.End_date IS NOT NULL AND NEW.End_date < CURRENT_DATE AND NEW.Status != 'Expired' THEN
    NEW.Status := 'Expired';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_auto_expire_subscription
BEFORE UPDATE ON User_Subscription
FOR EACH ROW
EXECUTE FUNCTION trg_auto_expire_subscription();

--za da proveram smeniv end date na user subscription 3 za da vidam dali proraboti
--UPDATE User_Subscription
--SET End_date = '2026-05-01'
--WHERE UserSubscriptionID = 3;

--eve ja komandata za da se proveri dali stvarno se promeni samoto vo expired:
--SELECT UserSubscriptionID, Start_date, End_date, Status
--FROM User_Subscription
--WHERE UserSubscriptionID = 3;









--TRIGGER 2 - da se spreci premnogu uredi, odnosno koga ke se dodava nov ured, da se proveruva max_devices spored planot na subscriptionot

CREATE FUNCTION trg_check_max_devices()
RETURNS TRIGGER AS $$
DECLARE
  v_max_devices INT;
  v_current_devices INT;
BEGIN
  -- maksimalniot broj na uredi od planot
  SELECT s.MaxDevices INTO v_max_devices
  FROM User_Subscription us
  JOIN Subscription s ON s.SubscriptionID = us.SubscriptionSubscriptionID
  WHERE us.UserSubscriptionID = NEW.UserSubscriptionID;

  -- proverka za kolku uredi vekje ima
  SELECT COUNT(*) INTO v_current_devices
  FROM Devices
  WHERE UserSubscriptionID = NEW.UserSubscriptionID;

  IF v_current_devices >= v_max_devices THEN
    RAISE EXCEPTION 'Maksimalen broj na uredi e dostигнат: % od %', v_current_devices, v_max_devices;
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_check_max_devices
BEFORE INSERT ON Devices
FOR EACH ROW
EXECUTE FUNCTION trg_check_max_devices();


--probav so user subscription id 3 kade sto imase 0 uredi a maksimalno 1 ured.

-- dodavanje prv ured pomina
--INSERT INTO Devices (DeviceType, LastLogIn, UserSubscriptionID)
--VALUES ('Mobile Phone', CURRENT_DATE, 3);

-- dodavanje vtor ured ne pomina
--INSERT INTO Devices (DeviceType, LastLogIn, UserSubscriptionID)
--VALUES ('Tablet', CURRENT_DATE, 3);










--TRIGGER 3 - log pri promena na status. koga user subscription ke smeni status, da se logira taa promena

CREATE TABLE Subscription_Status_Log (
  LogID       SERIAL PRIMARY KEY,
  UserSubID   INT,
  UserID      INT,
  OldStatus   VARCHAR(255),
  NewStatus   VARCHAR(255),
  ChangedAt   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);



CREATE FUNCTION trg_log_subscription_status_change()
RETURNS TRIGGER AS $$
BEGIN
  IF OLD.Status <> NEW.Status THEN
    INSERT INTO Subscription_Status_Log (UserSubID, UserID, OldStatus, NewStatus)
    VALUES (OLD.UserSubscriptionID, OLD.UserUserID, OLD.Status, NEW.Status);
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_log_subscription_status_change
AFTER UPDATE ON User_Subscription
FOR EACH ROW
EXECUTE FUNCTION trg_log_subscription_status_change();

--testiranje:
--promena na status:
--UPDATE User_Subscription
--SET Status = 'Cancelled'
--WHERE UserSubscriptionID = (
  --SELECT UserSubscriptionID FROM User_Subscription LIMIT 1
--);

--proverkata od tabelata:::: (za lab ova)
--SELECT * FROM Subscription_Status_Log;










--on the lowkey staiv indeksi na rating watch history i review deka se ogromni
CREATE INDEX idx_rating_content ON Rating (ContentContentID);
CREATE INDEX idx_rating_user ON Rating (UserUserID);
CREATE INDEX idx_watchhistory_content ON WatchHistory (ContentContentID);
CREATE INDEX idx_watchhistory_user ON WatchHistory (UserUserID);
CREATE INDEX idx_review_content ON Review (ContentContentID);
CREATE INDEX idx_review_user ON Review (UserUserID);

--So dodavanje na indeksi na Rating tabelata, vremeto za izvrshuvanje na funkcija so taa tabela se namali od 1 cas na 1.7 minuti — 35x zabrzuvanje na 24 milioni zapisi