CREATE TABLE IF NOT EXISTS api_source ( /* generic for all APIs */
  id int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  api_type varchar(50), /* moviedb, igdb, etc */
  api_id int(10), /* id that's in the api source */
  charactour_id int(10) /* foreign key to bookmovies table */
);
  
CREATE TABLE IF NOT EXISTS api_moviedb_titles ( /* specific fields for moviedb records */
  id int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
	api_source_id int(10), /* foreign key to api_source */
  type varchar(10), /* TV, movie for now */
  title varchar(255),
  slug varchar(255),
  dvd_cover varchar(255),
	synopsis text,
	plot_keywords varchar(500),
	rating varchar(20),
	runtime varchar(20),
	release_date datetime,
	end_date datetime,
	original_language varchar(255),
	created_at datetime,
	updated_at datetime,
	series varchar(255)
);

CREATE TABLE IF NOT EXISTS api_moviedb_genres (
  id int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
	genre varchar(255)
);

CREATE TABLE IF NOT EXISTS api_moviedb_titles_genres (
	title_id int(10),
	genre_id int(10)
);

CREATE TABLE IF NOT EXISTS api_moviedb_castcrew (
	title_id int(10),
	type ENUM('cast', 'crew'),
	name varchar(255)
);

CREATE TABLE api_moviedb_media (
	title_id int(10),
	media_id varchar(50),
	iso_639_1 varchar(50),
	iso_3166_1 varchar(50),
	media_key varchar(50),
	name varchar(100),
	site varchar(50),
	size int(10),
	media_type varchar(50)
);
