/*
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,
       titleType TEXT,
       primaryTitle TEXT,
       originalTitle TEXT,
       isAdult BOOLEAN,
       startYear INT,
       endYear INT,
       runtimeMinutes INT,
       genres TEXT/*[]*/
       CHECK (tconst LIKE 'tt%')
);

CREATE TABLE Name_basics(
       nconst TEXT PRIMARY KEY,
       primaryName TEXT NOT NULL,
       birthYear INT,
       deathYear INT,
       primaryProfession TEXT/*[]*/,
       knownForTitles TEXT/*[]*/,
       CHECK (nconst LIKE 'nm%')
    -- 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,
       numVotes INT
);

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,
       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, -- REFERENCES Title_basics(tconst),
       seasonNumber INT,
       episodeNumber INT
);
