-- CAST - usuwamy najpierw przez klucze obce
IF OBJECT_ID('CAST', 'U') IS NOT NULL
DROP TABLE CAST;

-- MOVIE_DIRECTOR - usuwamy najpierw przez klucze obce
IF OBJECT_ID('MOVIE_DIRECTOR', 'U') IS NOT NULL
DROP TABLE MOVIE_DIRECTOR;


-- ACTOR
IF OBJECT_ID('ACTOR', 'U') IS NOT NULL
DROP TABLE ACTOR;

CREATE TABLE ACTOR (
	id INT NOT NULL PRIMARY KEY,
	imie VARCHAR(255) NOT NULL,
	nazwisko VARCHAR(255) NOT NULL,
	plec CHAR CHECK (plec in ('F', 'M')) NOT NULL
);

-- MOVIE
IF OBJECT_ID('MOVIE', 'U') IS NOT NULL
DROP TABLE MOVIE;

CREATE TABLE MOVIE (
	id INT NOT NULL PRIMARY KEY,
	name VARCHAR(255) NOT NULL,
	year DATE NOT NULL,
	rank FLOAT
);

-- DIRECTOR
IF OBJECT_ID('DIRECTOR', 'U') IS NOT NULL
DROP TABLE DIRECTOR;

CREATE TABLE DIRECTOR (
	id INT NOT NULL PRIMARY KEY,
	imie VARCHAR(255),
	nazwisko VARCHAR(255) 
);


CREATE TABLE CAST (
	pid INT NOT NULL,
	mid INT NOT NULL,
	role VARCHAR(255) NOT NULL,
	FOREIGN KEY (pid) REFERENCES ACTOR(id),
	FOREIGN KEY (mid) REFERENCES MOVIE(id)
);

CREATE TABLE MOVIE_DIRECTOR (
	did INT NOT NULL,
	mid INT NOT NULL,
	FOREIGN KEY (did) REFERENCES DIRECTOR(id),
	FOREIGN KEY (mid) REFERENCES MOVIE(id)
);