/*
Tables for the IMDB Datasets described at https://www.imdb.com/interfaces/

Most of the foreign key constraints have been commented out because of
problems that were detected when trying to import the data into Postgres.

Also, some attributes are actually arrays of values, or in some cases even
arrays of foreign keys, but I simply used the type TEXT for those attributes.
*/

CREATE TABLE Title_basics(
       tconst TEXT PRIMARY KEY CHECK (tconst LIKE 'tt%'),
       titleType TEXT NOT NULL,
       primaryTitle TEXT NOT NULL,
       originalTitle TEXT NOT NULL,
       isAdult BOOLEAN NOT NULL,
       startYear INT,
       endYear INT,
       runtimeMinutes INT,
       genres TEXT/*[]*/
);

CREATE TABLE Name_basics(
       nconst TEXT PRIMARY KEY CHECK (nconst LIKE 'nm%'),
       primaryName TEXT NOT NULL,
       birthYear INT,
       deathYear INT,
       primaryProfession TEXT/*[]*/,
       knownForTitles TEXT/*[]*/
    -- FOREIGN KEY (EACH ELEMENT OF knownForTitles)
    --             REFERENCES Title_basics(tconst)
);

CREATE TABLE Title_crew(
       tconst TEXT PRIMARY KEY REFERENCES Title_basics(tconst),
       directors TEXT/*[]*/, --REFERENCES Name_basics(nconst)
       writers TEXT/*[]*/ --REFERENCES Name_basics(nconst)
);

CREATE TABLE Title_ratings(
       tconst TEXT PRIMARY KEY REFERENCES Title_basics(tconst),
       averageRating FLOAT NOT NULL,
       numVotes INT NOT NULL
);

CREATE TABLE Title_principals(
       tconst TEXT, -- REFERENCES Title_basics(tconst),
       ordering INT,
       nconst TEXT NOT NULL, --REFERENCES Name_basics(nconst),
       category TEXT,
       job TEXT,
       characters TEXT,
       PRIMARY KEY (tconst,ordering)
);

CREATE TABLE Title_Akas(
       titleId TEXT, --REFERENCES Title_basics(tconst),
       ordering INT,
       title TEXT NOT NULL,
       region TEXT,
       language TEXT,
       types TEXT/*[]*/,
       attributes TEXT/*[]*/,
       isOriginalTitle BOOLEAN,
       PRIMARY KEY (titleId,ordering)
);

CREATE TABLE Title_episode(
       tconst TEXT PRIMARY KEY, --  REFERENCES Title_basics(tconst),
       parentTconst TEXT NOT NULL, -- REFERENCES Title_basics(tconst),
       seasonNumber INT,
       episodeNumber INT
);