DatabaseProgramming: console_13.sql

--proceduri:

--Kiko:
--1-Регистрација на нов корисник со почетна претплата


CREATE OR REPLACE PROCEDURE sp_register_user(
  p_first_name   VARCHAR,
  p_last_name    VARCHAR,
  p_username     VARCHAR,
  p_email        VARCHAR,
  p_password     VARCHAR,
  p_subscription_id INT
)
LANGUAGE plpgsql AS $$
DECLARE
  v_user_id INT;
BEGIN
  -- Vmetni nov korisnik
  INSERT INTO "User" (FirstName, LastName, Username, Email, password, Date_registered)
  VALUES (p_first_name, p_last_name, p_username, p_email, p_password, CURRENT_DATE)
  RETURNING UserID INTO v_user_id;

  -- Kreira User_Subscription zapis za noviot korisnik
  INSERT INTO User_Subscription (UserUserID, SubscriptionSubscriptionID, Start_date, End_date, Status, Auto_renew)
  VALUES (v_user_id, p_subscription_id, CURRENT_DATE, CURRENT_DATE + INTERVAL '30 days', 'Active', 1);

  RAISE NOTICE 'Korisnikot % % uspeshno registriran so UserID = %', p_first_name, p_last_name, v_user_id;

EXCEPTION
  WHEN unique_violation THEN
    RAISE EXCEPTION 'Email % veke postoi vo sistemot.', p_email;
  WHEN foreign_key_violation THEN
    RAISE EXCEPTION 'Subscription so ID % ne postoi.', p_subscription_id;
END;
$$;

--Зошто би постоела во реален свет:
--На Netflix/Spotify кога се регистрираш, тоа не е само еден INSERT — треба да се креира корисник, да му се додели план, да се постави почетен датум. Без процедура, фронтендот би морал да праќа 2 одделни барања, со ризик едното да успее а другото да не — процедурата го прави тоа атомски (или се, или ништо).

--2-Додавање содржина во watchlist со проверка за дупликати

CREATE OR REPLACE PROCEDURE sp_add_to_watchlist(
  p_user_id    INT,
  p_content_id INT
)
LANGUAGE plpgsql AS $$
DECLARE
  v_exists INT;
BEGIN
  -- Proverka dali veke postoi vo watchlist-ot
  SELECT COUNT(*) INTO v_exists
  FROM Watchlist
  WHERE UserUserID = p_user_id AND ContentContentID = p_content_id;

  IF v_exists > 0 THEN
    RAISE NOTICE 'Sodrzhinata veke e vo watchlist-ot na korisnikot.';
    RETURN;
  END IF;

  -- Proverka dali sodrzhinata postoi vo Media
  IF NOT EXISTS (SELECT 1 FROM Media WHERE ContentID = p_content_id) THEN
    RAISE EXCEPTION 'Sodrzhinata so ID % ne postoi.', p_content_id;
  END IF;

  INSERT INTO Watchlist (UserUserID, ContentContentID, dateAdded)
  VALUES (p_user_id, p_content_id, CURRENT_DATE);

  RAISE NOTICE 'Sodrzhinata % dodadena vo watchlist za korisnik %.', p_content_id, p_user_id;

EXCEPTION
  WHEN foreign_key_violation THEN
    RAISE EXCEPTION 'Korisnik % ili sodrzina % ne postoi.', p_user_id, p_content_id;
END;
$$;

--Зошто би постоела во реален свет:
--Кога кликаш "Add to My List" на Netflix, апликацијата не знае дали веќе го имаш додадено — процедурата тоа го решава на ниво на база, без фронтендот да прави extra SELECT прво.

--3- Промена на план на претплата за постоечки корисник

CREATE OR REPLACE PROCEDURE sp_record_watch(
  p_user_id      INT,
  p_content_id   INT,
  p_watchable_id INT,
  p_device_id    INT,
  p_progress     INT
)
LANGUAGE plpgsql AS $$
BEGIN
  -- Proverka za progress range
  IF p_progress < 0 OR p_progress > 100 THEN
    RAISE EXCEPTION 'Progress mora da bide pomegu 0 i 100.';
  END IF;

  -- Vmetni vo WatchHistory (trigerot ke go handle 90%+ -> 100%)
  INSERT INTO WatchHistory (WatchedAt, Progress_percentage, UserUserID, ContentContentID, WatchableWatchableID, DevicesDeviceID)
  VALUES (CURRENT_DATE, p_progress, p_user_id, p_content_id, p_watchable_id, p_device_id);

  -- Ako e zavrseno gledanjeto i nema reting, isprati NOTICE (vo realna app = notification za rating)
  IF p_progress >= 90 THEN
    IF NOT EXISTS (
      SELECT 1 FROM Rating
      WHERE UserUserID = p_user_id AND ContentContentID = p_content_id
    ) THEN
      RAISE NOTICE 'Korisnik % ja zavrsil sodrzhinata %. Pokanete go da ostavi rejting.', p_user_id, p_content_id;
    END IF;
  END IF;

EXCEPTION
  WHEN foreign_key_violation THEN
    RAISE EXCEPTION 'Nevaliden user, content, watchable ili device ID.';
END;
$$;

--Зошто би постоела во реален свет:
--Кога корисник на HBO Max upgrade-ува од Basic на Premium, треба да се затвори стариот план и да се отвори нов — а вашиот тригер trigger_log_subscription_status_change автоматски ќе го логира тоа. Процедурата + триgerот работат заедно совршено.

--Damjan:

--1-Снимање на гледање + автоматски рејтинг промпт логика
CREATE OR REPLACE PROCEDURE sp_record_watch(
  p_user_id      INT,
  p_content_id   INT,
  p_watchable_id INT,
  p_device_id    INT,
  p_progress     INT
)
LANGUAGE plpgsql AS $$
BEGIN
  -- Proverka za progress range
  IF p_progress < 0 OR p_progress > 100 THEN
    RAISE EXCEPTION 'Progress mora da bide pomegu 0 i 100.';
  END IF;

  -- Vmetni vo WatchHistory (trigerot ke go handle 90%+ -> 100%)
  INSERT INTO WatchHistory (WatchedAt, Progress_percentage, UserUserID, ContentContentID, WatchableWatchableID, DevicesDeviceID)
  VALUES (CURRENT_DATE, p_progress, p_user_id, p_content_id, p_watchable_id, p_device_id);

  -- Ako e zavrseno gledanjeto i nema reting, isprati NOTICE (vo realna app = notification za rating)
  IF p_progress >= 90 THEN
    IF NOT EXISTS (
      SELECT 1 FROM Rating
      WHERE UserUserID = p_user_id AND ContentContentID = p_content_id
    ) THEN
      RAISE NOTICE 'Korisnik % ja zavrsil sodrzhinata %. Pokanete go da ostavi rejting.', p_user_id, p_content_id;
    END IF;
  END IF;

EXCEPTION
  WHEN foreign_key_violation THEN
    RAISE EXCEPTION 'Nevaliden user, content, watchable ili device ID.';
END;
$$;

--Зошто би постоела во реален свет:
--Секој пат кога паузираш или завршиш нешто на стриминг сервис, апликацијата снима прогрес. Процедурата работи со постоечкиот тригер trigger_auto_complete_progress (90%→100%) и додава логика за рејтинг нотификација — точно kako "How would you rate Stranger Things?" pop-up по завршување.

--2-Поднесување или ажурирање на рејтинг за содржина

CREATE OR REPLACE PROCEDURE sp_submit_rating(
  p_user_id    INT,
  p_content_id INT,
  p_rating     INT
)
LANGUAGE plpgsql AS $$
DECLARE
  v_watched INT;
BEGIN
  -- Proverka dali korisnikot vosopsto ja gledal sodrzhinata
  SELECT COUNT(*) INTO v_watched
  FROM WatchHistory
  WHERE UserUserID = p_user_id AND ContentContentID = p_content_id;

  IF v_watched = 0 THEN
    RAISE EXCEPTION 'Korisnikot ne ja gledal sodrzhinata i ne moze da ostavi rejting.';
  END IF;

  -- Ako veke postoi rejting, UPDATE; inaku INSERT
  IF EXISTS (SELECT 1 FROM Rating WHERE UserUserID = p_user_id AND ContentContentID = p_content_id) THEN
    UPDATE Rating
    SET RatingValue = p_rating, Rating_Date = CURRENT_DATE
    WHERE UserUserID = p_user_id AND ContentContentID = p_content_id;

    RAISE NOTICE 'Rejtingot azhurirani na % za sodrzina %.', p_rating, p_content_id;
  ELSE
    INSERT INTO Rating (Rating_Date, RatingValue, UserUserID, ContentContentID)
    VALUES (CURRENT_DATE, p_rating, p_user_id, p_content_id);

    RAISE NOTICE 'Nov rejting % vnesen za sodrzina %.', p_rating, p_content_id;
  END IF;

  -- Trigerot trigger_validate_rating ke go proveri rangot avtomatski

EXCEPTION
  WHEN check_violation THEN
    RAISE EXCEPTION 'Rejtingot mora da bide pomegu 1 i 5.';
END;
$$;

--Зошто би постоела во реален свет:
--IMDB/Netflix не дозволуваат рејтинг ако не си гледал — процедурата го enforc-ува тоа. Исто така работи со постоечкиот тригер trigger_validate_rating за валидација на вредноста, и со вашиот индекс idx_rating_content за брзо пребарување.

--3-Деактивирање на корисник — Cancel претплата + чистење на уреди
CREATE OR REPLACE PROCEDURE sp_deactivate_user(
  p_user_id INT
)
LANGUAGE plpgsql AS $$
DECLARE
  v_count INT;
BEGIN
  -- Proverka dali korisnikot postoi
  IF NOT EXISTS (SELECT 1 FROM "User" WHERE UserID = p_user_id) THEN
    RAISE EXCEPTION 'Korisnik so ID % ne postoi.', p_user_id;
  END IF;

  -- Proverka kolku aktivni pretplati ima
  SELECT COUNT(*) INTO v_count
  FROM User_Subscription
  WHERE UserUserID = p_user_id AND Status = 'Active';

  -- Cancel site aktivni pretplati (trigerot ke gi logira site promeni)
  UPDATE User_Subscription
  SET Status = 'Cancelled', End_date = CURRENT_DATE
  WHERE UserUserID = p_user_id AND Status = 'Active';

  -- Izbrishi gi site uredi povrzani so negovite pretplati
  DELETE FROM Devices
  WHERE UserSubscriptionID IN (
    SELECT UserSubscriptionID FROM User_Subscription
    WHERE UserUserID = p_user_id
  );

  RAISE NOTICE 'Korisnik % deaktiviran. % pretplati otkazani, uredite izbrishani.', p_user_id, v_count;

  -- Zabeleska: DELETE od "User" ke go aktivira trigger_log_user_delete avtomatski

EXCEPTION
  WHEN OTHERS THEN
    RAISE EXCEPTION 'Greshka pri deaktiviranje na korisnik %: %', p_user_id, SQLERRM;
END;
$$;

--Зошто би постоела во реален свет:
--"Cancel my account" на стриминг сервис не брише само еден ред — треба да се откажат претплати, да се отстранат уреди, да се логира се. Процедурата го координира сето тоа, а вашите два постоечки тригери (trigger_log_user_delete и trigger_log_subscription_status_change) автоматски снимаат сe во log табелите.