DatabaseCreation: console_10.sql

--views:
--Kiko:
--1. view_top_rated_content — Топ содржини по просечен рејтинг

CREATE OR REPLACE VIEW view_top_rated_content AS
SELECT
  m.ContentID,
  m.title,
  m.releaseDate,
  m.AgeRating,
  ROUND(AVG(r.RatingValue), 2) AS avg_rating,
  COUNT(r.RatingID) AS total_ratings,
  CASE
    WHEN mo.MovieID IS NOT NULL THEN 'Movie'
    ELSE 'Series'
  END AS content_type
FROM Media m
JOIN Rating r ON r.ContentContentID = m.ContentID
LEFT JOIN Movie mo ON mo.MovieID = m.ContentID
LEFT JOIN Series se ON se.SeriesID = m.ContentID
GROUP BY m.ContentID, m.title, m.releaseDate, m.AgeRating, mo.MovieID, se.SeriesID
ORDER BY avg_rating DESC;


--2. view_user_watch_history — Историја на гледање по корисник

CREATE OR REPLACE VIEW view_user_watch_history AS
SELECT
  u.UserID,
  u.FirstName,
  u.LastName,
  m.title AS content_title,
  CASE
    WHEN mo.MovieID IS NOT NULL THEN 'Movie'
    ELSE 'Series'
  END AS content_type,
  wh.WatchedAt,
  wh.Progress_percentage,
  d.DeviceType
FROM WatchHistory wh
JOIN "User" u ON u.UserID = wh.UserUserID
JOIN Media m ON m.ContentID = wh.ContentContentID
LEFT JOIN Movie mo ON mo.MovieID = m.ContentID
LEFT JOIN Series se ON se.SeriesID = m.ContentID
LEFT JOIN Devices d ON d.DeviceID = wh.DevicesDeviceID;

--3. view_content_details — Детали за содржина со жанр, јазик, глумци

CREATE OR REPLACE VIEW view_content_details AS
SELECT
  m.ContentID,
  m.title,
  m.releaseDate,
  m.AgeRating,
  CASE
    WHEN mo.MovieID IS NOT NULL THEN 'Movie'
    ELSE 'Series'
  END AS content_type,
  mo.Duration AS movie_duration,
  se.TotalSeasons,
  STRING_AGG(DISTINCT g.Name, ', ') AS genres,
  STRING_AGG(DISTINCT l.Name, ', ') AS languages,
  STRING_AGG(DISTINCT (a.FirstName || ' ' || a.LastName), ', ') AS artists,
  ROUND(AVG(r.RatingValue), 2) AS avg_rating,
  COUNT(DISTINCT r.RatingID) AS total_ratings
FROM Media m
LEFT JOIN Movie mo ON mo.MovieID = m.ContentID
LEFT JOIN Series se ON se.SeriesID = m.ContentID
LEFT JOIN Content_Genre cg ON cg.ContentContentID = m.ContentID
LEFT JOIN Genre g ON g.GenreID = cg.GenreGenreID
LEFT JOIN Content_Language cl ON cl.ContentContentID = m.ContentID
LEFT JOIN Language l ON l.LanguageID = cl.LanguageLanguageID
LEFT JOIN Content_Artist ca ON ca.ContentContentID = m.ContentID
LEFT JOIN Artist a ON a.ArtistID = ca.ArtistArtistID
LEFT JOIN Rating r ON r.ContentContentID = m.ContentID
GROUP BY m.ContentID, m.title, m.releaseDate, m.AgeRating, mo.MovieID, mo.Duration, se.TotalSeasons;


--Damjan:
--1. view_active_subscriptions — Активни претплати

CREATE OR REPLACE VIEW view_active_subscriptions AS
SELECT
  u.UserID,
  u.FirstName,
  u.LastName,
  u.Email,
  s.Name AS subscription_plan,
  s.Price,
  s.MaxDevices,
  s.VideoQuality,
  us.Start_date,
  us.End_date,
  us.Status,
  us.Auto_renew,
  COUNT(d.DeviceID) AS active_devices
FROM User_Subscription us
JOIN "User" u ON u.UserID = us.UserUserID
JOIN Subscription s ON s.SubscriptionID = us.SubscriptionSubscriptionID
LEFT JOIN Devices d ON d.UserSubscriptionID = us.UserSubscriptionID
WHERE us.Status = 'Active'
GROUP BY u.UserID, u.FirstName, u.LastName, u.Email,
         s.Name, s.Price, s.MaxDevices, s.VideoQuality,
         us.Start_date, us.End_date, us.Status, us.Auto_renew;


--2. view_series_episodes — Епизоди по серија и сезона

CREATE OR REPLACE VIEW view_series_episodes AS
SELECT
  m.ContentID AS series_id,
  m.title AS series_title,
  se.TotalSeasons,
  sea.SeasonID,
  sea.SeasonNumber,
  sea.ReleaseYear,
  e.EpisodeID,
  e.episodeNumber,
  e.Title AS episode_title,
  e.Duration AS episode_duration
FROM Series se
JOIN Media m ON m.ContentID = se.SeriesID
JOIN Season sea ON sea.SeriesSeriesID = se.SeriesID
JOIN Episode e ON e.SeasonSeasonID = sea.SeasonID
ORDER BY m.title, sea.SeasonNumber, e.episodeNumber;


--3. view_user_watchlist — Watchlist по корисник


CREATE OR REPLACE VIEW view_user_watchlist AS
SELECT
  u.UserID,
  u.FirstName,
  u.LastName,
  m.title AS content_title,
  CASE
    WHEN mo.MovieID IS NOT NULL THEN 'Movie'
    ELSE 'Series'
  END AS content_type,
  m.AgeRating,
  m.releaseDate,
  wl.dateAdded
FROM Watchlist wl
JOIN "User" u ON u.UserID = wl.UserUserID
JOIN Media m ON m.ContentID = wl.ContentContentID
LEFT JOIN Movie mo ON mo.MovieID = m.ContentID
LEFT JOIN Series se ON se.SeriesID = m.ContentID
ORDER BY wl.dateAdded DESC;