create database IMDB_temp;
use IMDB_temp;
create table basics
(
tconst varchar(100) ,
titleType varchar(200) ,
primaryTitle varchar(200) ,
originalTitle varchar(200) ,
isAdult tinyint(1) ,
startYear char(4) ,
endYear char(4) ,
runtimeMinutes int ,
genres varchar(200) 
);

alter table basics add column rate float;



create table akas
(
titleId varchar(100) ,
ordering int ,
title varchar(100) ,
region varchar(100) ,
language varchar(100) ,
types text ,
attributes text ,
isOriginalTitle tinyint(1)
);

create table crew
(
tconst varchar(100) ,
directors text ,
writers text 
);


create table episodes
(
tconst varchar(100) ,
parentTconst varchar(100) ,
seasonNumber int ,
episodeNumber int 
);

create table principals
(
tconst varchar(100) ,
ordering int ,
nconst varchar(100) ,
category varchar(100) ,
job varchar(100) ,
characters text 
);


create table ratings
(tconst varchar(100) ,
averageRating float ,
numVotes float );


create table name_basics
(
nconst varchar(100) ,
primaryName varchar(100) ,
birthYear char(4) ,
deathYear char(4) ,
primaryProfession text ,
knownForTitles text 
);



drop index idx_basics_tconst on basics;
drop index idx_basics_primaryTitle on basics;
drop index idx_basics_startYear on basics;
drop index idx_basics_primaryTitle_fulltext on basics;
drop index idx_ratings_tconst on ratings;
drop index idx_episodes_tconst on episodes;
drop index idx_episodes_parentTconst on episodes;

LOAD DATA LOCAL INFILE '/root/IMDB/title.basics.tsv' INTO TABLE basics;
LOAD DATA LOCAL INFILE '/root/IMDB/title.akas.tsv' INTO TABLE akas;
LOAD DATA LOCAL INFILE '/root/IMDB/name.basics.tsv' INTO TABLE name_basics;
LOAD DATA LOCAL INFILE '/root/IMDB/title.crew.tsv' INTO TABLE crew;
LOAD DATA LOCAL INFILE '/root/IMDB/title.episode.tsv' INTO TABLE episodes;
LOAD DATA LOCAL INFILE '/root/IMDB/title.principals.tsv' INTO TABLE principals;
LOAD DATA LOCAL INFILE '/root/IMDB/title.ratings.tsv' INTO TABLE ratings;

create index idx_basics_tconst on basics(tconst);
create index idx_basics_primaryTitle on basics(primaryTitle);
create index idx_basics_startYear on basics(startYear);
ALTER TABLE basics ADD FULLTEXT INDEX idx_basics_primaryTitle_fulltext (primaryTitle);
create index idx_ratings_tconst on ratings(tconst);
create index idx_episodes_tconst on episodes(tconst);
create index idx_episodes_parentTconst on episodes(parentTconst);