DatabaseCreation: console_9.sql

--za seasons:
INSERT INTO Season (SeasonNumber, ReleaseYear, SeriesSeriesID)
SELECT
  gs AS SeasonNumber,
  (EXTRACT(YEAR FROM s_media.releaseDate)::int + gs - 1) AS ReleaseYear,
  ser.SeriesID
FROM Series ser
JOIN Media s_media ON ser.SeriesID = s_media.ContentID
CROSS JOIN generate_series(1, ser.TotalSeasons) AS gs;

-- Watchable за секој филм
INSERT INTO Watchable (duration, MovieID, EpisodeID)
SELECT m.Duration, m.MovieID, NULL
FROM Movie m
WHERE m.MovieID NOT IN (SELECT MovieID FROM Watchable WHERE MovieID IS NOT NULL);

INSERT INTO Watchable (duration, MovieID, EpisodeID)
SELECT e.Duration, NULL, e.EpisodeID
FROM Episode e
WHERE e.EpisodeID NOT IN (SELECT EpisodeID FROM Watchable WHERE EpisodeID IS NOT NULL);

--polnenje na devices
INSERT INTO Devices (DeviceType, LastLogIn, UserSubscriptionID)
SELECT
  (ARRAY[
    'Smart TV', 'Mobile Phone', 'Tablet', 'Laptop',
    'Desktop PC', 'Game Console', 'Streaming Stick'
  ])[ceil(random() * 7)::int] AS DeviceType,
  CURRENT_DATE - (random() * 365)::int AS LastLogIn,
  us.UserSubscriptionID
FROM User_Subscription us
WHERE (random() < 0.3);

-- CONTENT_ARTIST: секоја содржина добива 1-3 рандом уметници
INSERT INTO Content_Artist (ContentContentID, ArtistArtistID)
SELECT DISTINCT m.ContentID, a.ArtistID
FROM Media m
CROSS JOIN LATERAL (
  SELECT ArtistID FROM Artist ORDER BY random() LIMIT (1 + (random() * 2)::int)
) a
ON CONFLICT DO NOTHING;

-- CONTENT_GENRE: секоја содржина добива 1-3 рандом жанрови
INSERT INTO Content_Genre (ContentContentID, GenreGenreID)
SELECT DISTINCT m.ContentID, g.GenreID
FROM Media m
CROSS JOIN LATERAL (
  SELECT GenreID FROM Genre ORDER BY random() LIMIT (1 + (random() * 2)::int)
) g
ON CONFLICT DO NOTHING;

-- CONTENT_LANGUAGE: секоја содржина добива 1-2 рандом јазици
INSERT INTO Content_Language (ContentContentID, LanguageLanguageID)
SELECT DISTINCT m.ContentID, l.LanguageID
FROM Media m
CROSS JOIN LATERAL (
  SELECT LanguageID FROM Language ORDER BY random() LIMIT (1 + (random())::int)
) l
ON CONFLICT DO NOTHING;


--prvo, reviews (10,000,000 zapisi)
INSERT INTO Review (Comment, ReviewDate, UserUserID, ContentContentID)
SELECT
  (ARRAY[
    'Absolutely amazing, one of the best!',
    'Great story, highly recommend.',
    'A bit slow at the start but gets better.',
    'Masterpiece of cinema.',
    'Not what I expected but still enjoyable.',
    'Incredible performances all around.',
    'The ending blew my mind.',
    'Would watch again without hesitation.',
    'Overrated in my opinion.',
    'A classic that stands the test of time.',
    'Great cinematography and direction.',
    'The plot had some holes but overall good.',
    'One of the best series ever made.',
    'Keeps you on the edge of your seat.',
    'The writing is absolutely top-notch.',
    'Brilliant acting from start to finish.',
    'A must watch for everyone.',
    'Did not live up to the hype.',
    'Absolutely loved every minute of it.',
    'A cinematic masterpiece.'
    'This man is my worst nightmare. 10/10',
    'No.',
    'The portrayal of toxic relationships is very good.'
  ])[ceil(random() * 20)::int],
  CURRENT_DATE - (random() * 730)::int,
  u.UserID,
  m.ContentID
FROM
  generate_series(1, 10000000) AS gs
  CROSS JOIN LATERAL (
    SELECT UserID FROM "User" ORDER BY random() LIMIT 1
  ) u
  CROSS JOIN LATERAL (
    SELECT ContentID FROM Media ORDER BY random() LIMIT 1
  ) m;


--polnenje na watch history (10,000,000 zapisi):

INSERT INTO WatchHistory (WatchedAt, Progress_percentage, UserUserID, ContentContentID, WatchableWatchableID, DevicesDeviceID)
SELECT
  CURRENT_DATE - (random() * 365)::int,
  (random() * 100)::int,
  u.UserID,
  w.ContentID,
  w.WatchableID,
  d.DeviceID
FROM
  generate_series(1, 10000000) AS gs
  CROSS JOIN LATERAL (
    SELECT UserID FROM "User" ORDER BY random() LIMIT 1
  ) u
  CROSS JOIN LATERAL (
    SELECT
      wa.WatchableID,
      CASE
        WHEN wa.MovieID IS NOT NULL THEN wa.MovieID
        ELSE (
          SELECT me.ContentID FROM Episode ep
          JOIN Season se ON ep.SeasonSeasonID = se.SeasonID
          JOIN Media me ON se.SeriesSeriesID = me.ContentID
          WHERE ep.EpisodeID = wa.EpisodeID
          LIMIT 1
        )
      END AS ContentID
    FROM Watchable wa
    ORDER BY random()
    LIMIT 1
  ) w
  CROSS JOIN LATERAL (
    SELECT DeviceID FROM Devices ORDER BY random() LIMIT 1
  ) d
WHERE w.ContentID IS NOT NULL;



--polnenje na ratings (ova ispadna so 24,450,003 zapisi):

INSERT INTO Rating (Rating_Date, RatingValue, UserUserID, ContentContentID)
SELECT
  CURRENT_DATE - (random() * 730)::int,
  (1 + (random() * 9)::int),
  u.UserID,
  m.ContentID
FROM
  (SELECT UserID FROM "User" ORDER BY random() LIMIT 50000) u
  CROSS JOIN
  (SELECT ContentID FROM Media) m
ON CONFLICT (UserUserID, ContentContentID) DO NOTHING;

--polnenje na watchlist (2,445,001 zapisi) :
INSERT INTO Watchlist (dateAdded, UserUserID, ContentContentID, WatchableWatchableID)
SELECT
  CURRENT_DATE - (random() * 180)::int,
  u.UserID,
  m.ContentID,
  (SELECT wa.WatchableID FROM Watchable wa
   WHERE wa.MovieID = m.ContentID OR wa.EpisodeID IN (
     SELECT e.EpisodeID FROM Episode e
     JOIN Season se ON e.SeasonSeasonID = se.SeasonID
     WHERE se.SeriesSeriesID = m.ContentID
   ) ORDER BY random() LIMIT 1)
FROM
  (SELECT UserID FROM "User" ORDER BY random() LIMIT 5000) u
  CROSS JOIN Media m
ON CONFLICT (UserUserID, ContentContentID) DO NOTHING;