use IMDB;


truncate table akas          ;
truncate table basics        ;
truncate table crew          ;
truncate table episodes      ;
truncate table name_basics   ;
truncate table principals    ;
truncate table ratings       ;


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;
drop index idx_basics_primaryTitle_fulltext on basics;


insert into basics               select a.*,0 from IMDB_temp.basics a   ;
insert into akas                 select * from IMDB_temp.akas           ;
insert into name_basics          select * from IMDB_temp.name_basics    ;
insert into crew                 select * from IMDB_temp.crew           ;
insert into episodes             select * from IMDB_temp.episodes       ;
insert into ratings              select * from IMDB_temp.ratings        ;
insert into principals           select * from IMDB_temp.principals     ;

create index idx_basics_tconst on basics(tconst);
create index idx_basics_primaryTitle on basics(primaryTitle);
create index idx_basics_startYear on basics(startYear);
create index idx_ratings_tconst on ratings(tconst);
create index idx_episodes_tconst on episodes(tconst);
create index idx_episodes_parentTconst on episodes(parentTconst);

update basics a,ratings b set a.rate=b.averageRating where a.tconst=b.tconst;

ALTER TABLE basics ADD FULLTEXT INDEX idx_basics_primaryTitle_fulltext (primaryTitle);