CREATE TABLE "lists"
(
    user_id                     INTEGER
        references lists_users (user_id),
    list_id                     INTEGER not null
        primary key,
    list_title                  TEXT,
    list_movie_number           INTEGER,
    list_update_timestamp_utc   TEXT,
    list_creation_timestamp_utc TEXT,
    list_followers              INTEGER,
    list_url                    TEXT,
    list_comments               INTEGER,
    list_description            TEXT,
    list_cover_image_url        TEXT,
    list_first_image_url        TEXT,
    list_second_image_url       TEXT,
    list_third_image_url        TEXT
);
lists
CREATE TABLE "movies"
(
    movie_id             INTEGER not null
        primary key,
    movie_title          TEXT,
    movie_release_year   INTEGER,
    movie_url            TEXT,
    movie_title_language TEXT,
    movie_popularity     INTEGER,
    movie_image_url      TEXT,
    director_id          TEXT,
    director_name        TEXT,
    director_url         TEXT
);
movies
CREATE TABLE "ratings_users"
(
    user_id                 INTEGER
        references lists_users (user_id),
    rating_date_utc         TEXT,
    user_trialist           INTEGER,
    user_subscriber         INTEGER,
    user_avatar_image_url   TEXT,
    user_cover_image_url    TEXT,
    user_eligible_for_trial INTEGER,
    user_has_payment_method INTEGER
);
ratings_users
CREATE TABLE lists_users
(
    user_id                 INTEGER not null ,
    list_id                 INTEGER not null ,
    list_update_date_utc    TEXT,
    list_creation_date_utc  TEXT,
    user_trialist           INTEGER,
    user_subscriber         INTEGER,
    user_avatar_image_url   TEXT,
    user_cover_image_url    TEXT,
    user_eligible_for_trial TEXT,
    user_has_payment_method TEXT,
    primary key (user_id, list_id),
    foreign key (list_id) references lists(list_id),
    foreign key (user_id) references lists(user_id)
);
lists_users
CREATE TABLE ratings
(
    movie_id                INTEGER,
    rating_id               INTEGER,
    rating_url              TEXT,
    rating_score            INTEGER,
    rating_timestamp_utc    TEXT,
    critic                  TEXT,
    critic_likes            INTEGER,
    critic_comments         INTEGER,
    user_id                 INTEGER,
    user_trialist           INTEGER,
    user_subscriber         INTEGER,
    user_eligible_for_trial INTEGER,
    user_has_payment_method INTEGER,
    foreign key (movie_id) references movies(movie_id),
    foreign key (user_id) references lists_users(user_id),
    foreign key (rating_id) references ratings(rating_id),
    foreign key (user_id) references ratings_users(user_id)
);
ratings
original_column_name,column_name,column_description,data_format,value_description
movie_id,,Movie ID related to the rating,integer,
rating_id,,Rating ID on Mubi,integer,
rating_url,,URL to the rating on Mubi,text,
rating_score,,Rating score ranging from 1 (lowest) to 5 (highest),integer,"commonsense evidence:
The score is proportional to the user's liking.
The higher the score is, the more the user likes the movie"
rating_timestamp_utc ,,Timestamp for the movie rating made by the user on Mubi,text,
critic,,Critic made by the user rating the movie. ,text,"If value = ""None"", the user did not write a critic when rating the movie."
critic_likes,,Number of likes related to the critic made by the user rating the movie,integer,
critic_comments,,Number of comments related to the critic made by the user rating the movie,integer,
user_id,,ID related to the user rating the movie,integer,
user_trialist ,,whether user was a tralist when he rated the movie,integer,"1 = the user was a trialist when he rated the movie 
0 = the user was not a trialist when he rated the movie"
user_subscriber,,,integer,
user_eligible_for_trial,,,integer,
user_has_payment_method,,,integer,
ratings
original_column_name,column_name,column_description,data_format,value_description
user_id,,ID related to the user who created the list.,integer,
list_id,,ID of the list on Mubi,integer,
list_title,,Name of the list,text,
list_movie_number,,Number of movies added to the list,integer,
list_update_timestamp_utc,,Last update timestamp for the list,text,
list_creation_timestamp_utc,,Creation timestamp for the list,text,
list_followers,,Number of followers on the list,integer,
list_url,,URL to the list page on Mubi,text,
list_comments,,Number of comments on the list,integer,
list_description,,List description made by the user,text,
list_cover_image_url,,,,
list_first_image_url,,,,
list_second_image_url,,,,
list_third_image_url,,,,
lists
original_column_name,column_name,column_description,data_format,value_description
user_id,,ID related to the user rating the movie,integer,
rating_date_utc,,Rating date for the movie rating.,text,YYYY-MM-DD
user_trialist,,whether the user was a trialist when he rated the movie,integer,"1 = the user was a trialist when he rated the movie
 0 = the user was not a trialist when he rated the movie"
user_subscriber,,whether the user was a subscriber when he rated the movie,integer,"1 = the user was a subscriber when he rated the movie 
0 = the user was not a subscriber when he rated the movie"
user_avatar_image_url,,URL to the user profile image on Mubi,text,
user_cover_image_url,,URL to the user profile cover image on Mubi,text,
user_eligible_for_trial,,whether the user was eligible for trial when he rated the movie,integer,"1 = the user was eligible for trial when he rated the movie
 0 = the user was not eligible for trial when he rated the movie"
user_has_payment_method ,,whether the user was a paying subscriber when he rated the movie,integer,"1 = the user was a paying subscriber when he rated the movie 
0 = the user was not a paying subscriber when he rated"
ratings_users
original_column_name,column_name,column_description,data_format,value_description
movie_id,,ID related to the movie on Mubi,integer,
movie_title,,Name of the movie,text,
movie_release_year,,Release year of the movie,integer,
movie_url,,URL to the movie page on Mubi,text,
movie_title_language,,"By default, the title is in English.",text,Only contains one value which is 'en'
movie_popularity,,Number of Mubi users who love this movie,integer,
movie_image_url,,Image URL to the movie on Mubi,text,
director_id,,ID related to the movie director on Mubi,text,
director_name,,Full Name of the movie director,text,
director_url ,,URL to the movie director page on Mubi,text,
movies
original_column_name,column_name,column_description,data_format,value_description
user_id,,ID related to the user who created the list.,integer,
list_id,,ID of the list on Mubi,integer,
list_update_date_utc,,Last update date for the list,text,YYYY-MM-DD
list_creation_date_utc,,Creation date for the list,text,YYYY-MM-DD
user_trialist,,whether the user was a tralist when he created the list ,integer,"1 = the user was a trialist when he created the list
 0 = the user was not a trialist when he created the list"
user_subscriber,,whether the user was a subscriber when he created the list ,integer,"1 = the user was a subscriber when he created the list 
0 = the user was not a subscriber when he created the list"
user_avatar_image_url,,User profile image URL on Mubi,text,
user_cover_image_url,,User profile cover image URL on Mubi,text,
user_eligible_for_trial,,whether the user was eligible for trial when he created the list ,text,"1 = the user was eligible for trial when he created the list 
0 = the user was not eligible for trial when he created the list"
user_has_payment_method ,,whether the user was a paying subscriber when he created the list ,text,"1 = the user was a paying subscriber when he created the list 
0 = the user was not a paying subscriber when he created the list "
lists_users