CREATE TABLE Subscription (
  SubscriptionID SERIAL PRIMARY KEY,
  Name           varchar(255) NOT NULL,
  Price          numeric(19, 2) NOT NULL CHECK (Price >= 0),
  MaxDevices     int4 NOT NULL CHECK (MaxDevices > 0),
  VideoQuality   varchar(255)
);

-- ------------------------------------------------

CREATE TABLE "User" (
  UserID          SERIAL PRIMARY KEY,
  FirstName       varchar(255),
  LastName        varchar(255),
  Username        varchar(255) NOT NULL UNIQUE,
  Email           varchar(255) NOT NULL UNIQUE
                  CHECK (Email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
  password        varchar(255) NOT NULL,
  Date_registered date DEFAULT CURRENT_DATE
);

-- ------------------------------------------------

CREATE TABLE User_Subscription (
  UserSubscriptionID         SERIAL PRIMARY KEY,
  UserUserID                 int4 NOT NULL REFERENCES "User" (UserID)
                             ON DELETE CASCADE ON UPDATE CASCADE,
  SubscriptionSubscriptionID int4 NOT NULL REFERENCES Subscription (SubscriptionID)
                             ON DELETE RESTRICT ON UPDATE CASCADE,
  Start_date                 date NOT NULL DEFAULT CURRENT_DATE,
  End_date                   date CHECK (End_date IS NULL OR End_date >= Start_date),
  Status                     varchar(255),
  Auto_renew                 int4 DEFAULT 0 CHECK (Auto_renew IN (0, 1))
);

-- ------------------------------------------------

CREATE TABLE Devices (
  DeviceID           SERIAL PRIMARY KEY,
  DeviceType         varchar(255),
  LastLogIn          date,
  UserSubscriptionID int4 NOT NULL REFERENCES User_Subscription (UserSubscriptionID)
                     ON DELETE RESTRICT ON UPDATE CASCADE
);

-- ------------------------------------------------

CREATE TABLE User_Devices (
  UserUserID      int4 NOT NULL REFERENCES "User" (UserID)
                  ON DELETE CASCADE ON UPDATE CASCADE,
  DevicesDeviceID int4 NOT NULL REFERENCES Devices (DeviceID)
                  ON DELETE CASCADE ON UPDATE CASCADE,
  PRIMARY KEY (UserUserID, DevicesDeviceID)
);

-- ------------------------------------------------

CREATE TABLE Media (
  ContentID   SERIAL PRIMARY KEY,
  title       varchar(255) NOT NULL,
  description varchar(255),
  releaseDate date,
  AgeRating   numeric(19, 0) CHECK (AgeRating >= 0 AND AgeRating <= 21)
);

-- ------------------------------------------------

CREATE TABLE Movie (
  MovieID  int4 PRIMARY KEY REFERENCES Media (ContentID)
           ON DELETE CASCADE ON UPDATE CASCADE,
  Duration int4 CHECK (Duration > 0)
);

-- ------------------------------------------------

CREATE TABLE Series (
  SeriesID     int4 PRIMARY KEY REFERENCES Media (ContentID)
               ON DELETE CASCADE ON UPDATE CASCADE,
  TotalSeasons int4 CHECK (TotalSeasons > 0)
);

-- ------------------------------------------------

CREATE TABLE Season (
  SeasonID       SERIAL PRIMARY KEY,
  SeasonNumber   int4 NOT NULL CHECK (SeasonNumber > 0),
  ReleaseYear    int4,
  SeriesSeriesID int4 NOT NULL REFERENCES Series (SeriesID)
                 ON DELETE CASCADE ON UPDATE CASCADE
);

-- ------------------------------------------------

CREATE TABLE Episode (
  EpisodeID      SERIAL PRIMARY KEY,
  Title          varchar(255),
  episodeNumber  int4 NOT NULL CHECK (episodeNumber > 0),
  Duration       int4 CHECK (Duration > 0),
  SeasonSeasonID int4 NOT NULL REFERENCES Season (SeasonID)
                 ON DELETE CASCADE ON UPDATE CASCADE
);

-- ------------------------------------------------

CREATE TABLE Watchable (
  WatchableID SERIAL PRIMARY KEY,
  duration    int4 CHECK (duration > 0),
  MovieID     int4 REFERENCES Movie (MovieID)
              ON DELETE SET NULL ON UPDATE CASCADE,
  EpisodeID   int4 REFERENCES Episode (EpisodeID)
              ON DELETE SET NULL ON UPDATE CASCADE
);

-- ------------------------------------------------

CREATE TABLE Artist (
  ArtistID  SERIAL PRIMARY KEY,
  FirstName varchar(255),
  LastName  varchar(255),
  BirthDate date,
  RoleType  varchar(255) CHECK (RoleType IN ('Actor', 'Director', 'Cameraman', 'Both'))
);

-- ------------------------------------------------

CREATE TABLE Genre (
  GenreID SERIAL PRIMARY KEY,
  Name    varchar(255) NOT NULL UNIQUE
);

-- ------------------------------------------------

CREATE TABLE Language (
  LanguageID SERIAL PRIMARY KEY,
  Name       varchar(255) NOT NULL UNIQUE
);

-- ------------------------------------------------

CREATE TABLE Rating (
  RatingID         SERIAL PRIMARY KEY,
  Rating_Date      date DEFAULT CURRENT_DATE,
  RatingValue      int4 NOT NULL CHECK (RatingValue BETWEEN 1 AND 10),
  UserUserID       int4 NOT NULL REFERENCES "User" (UserID)
                   ON DELETE CASCADE ON UPDATE CASCADE,
  ContentContentID int4 NOT NULL REFERENCES Media (ContentID)
                   ON DELETE CASCADE ON UPDATE CASCADE,
  UNIQUE (UserUserID, ContentContentID)
);

-- ------------------------------------------------

CREATE TABLE Review (
  ReviewID         SERIAL PRIMARY KEY,
  Comment          varchar(255),
  ReviewDate       date DEFAULT CURRENT_DATE,
  UserUserID       int4 NOT NULL REFERENCES "User" (UserID)
                   ON DELETE CASCADE ON UPDATE CASCADE,
  ContentContentID int4 NOT NULL REFERENCES Media (ContentID)
                   ON DELETE CASCADE ON UPDATE CASCADE
);

-- ------------------------------------------------

CREATE TABLE WatchHistory (
  HistoryID            SERIAL PRIMARY KEY,
  WatchedAt            date DEFAULT CURRENT_DATE,
  Progress_percentage  int4 CHECK (Progress_percentage BETWEEN 0 AND 100),
  UserUserID           int4 NOT NULL REFERENCES "User" (UserID)
                       ON DELETE CASCADE ON UPDATE CASCADE,
  ContentContentID     int4 NOT NULL REFERENCES Media (ContentID)
                       ON DELETE CASCADE ON UPDATE CASCADE,
  WatchableWatchableID int4 NOT NULL REFERENCES Watchable (WatchableID)
                       ON DELETE CASCADE ON UPDATE CASCADE,
  DevicesDeviceID      int4 REFERENCES Devices (DeviceID)
                       ON DELETE SET NULL ON UPDATE CASCADE
);

-- ------------------------------------------------

CREATE TABLE Watchlist (
  WatchlistID          SERIAL PRIMARY KEY,
  dateAdded            date DEFAULT CURRENT_DATE,
  UserUserID           int4 NOT NULL REFERENCES "User" (UserID)
                       ON DELETE CASCADE ON UPDATE CASCADE,
  ContentContentID     int4 NOT NULL REFERENCES Media (ContentID)
                       ON DELETE CASCADE ON UPDATE CASCADE,
  WatchableWatchableID int4 NOT NULL REFERENCES Watchable (WatchableID)
                       ON DELETE CASCADE ON UPDATE CASCADE,
  UNIQUE (UserUserID, ContentContentID)
);

-- ------------------------------------------------

CREATE TABLE Content_Artist (
  ContentContentID int4 NOT NULL REFERENCES Media (ContentID)
                   ON DELETE CASCADE ON UPDATE CASCADE,
  ArtistArtistID   int4 NOT NULL REFERENCES Artist (ArtistID)
                   ON DELETE CASCADE ON UPDATE CASCADE,
  PRIMARY KEY (ContentContentID, ArtistArtistID)
);

-- ------------------------------------------------

CREATE TABLE Content_Genre (
  ContentContentID int4 NOT NULL REFERENCES Media (ContentID)
                   ON DELETE CASCADE ON UPDATE CASCADE,
  GenreGenreID     int4 NOT NULL REFERENCES Genre (GenreID)
                   ON DELETE CASCADE ON UPDATE CASCADE,
  PRIMARY KEY (ContentContentID, GenreGenreID)
);

-- ------------------------------------------------

CREATE TABLE Content_Language (
  ContentContentID   int4 NOT NULL REFERENCES Media (ContentID)
                     ON DELETE CASCADE ON UPDATE CASCADE,
  LanguageLanguageID int4 NOT NULL REFERENCES Language (LanguageID)
                     ON DELETE CASCADE ON UPDATE CASCADE,
  PRIMARY KEY (ContentContentID, LanguageLanguageID)
);