CREATE TABLE COMPANY(
   companyId INT AUTO_INCREMENT,
   companyName VARCHAR(50)  NOT NULL,
   PRIMARY KEY(companyId),
   UNIQUE(companyName)
);

CREATE TABLE CHAR_PLAYED(
   characterId INT AUTO_INCREMENT,
   characterName VARCHAR(50)  NOT NULL,
   PRIMARY KEY(characterId)
);

CREATE TABLE PERSON(
   personId INT AUTO_INCREMENT,
   personName VARCHAR(50)  NOT NULL,
   PRIMARY KEY(personId)
);

CREATE TABLE ROLE(
   roleId INT AUTO_INCREMENT,
   roleType VARCHAR(50)  NOT NULL,
   PRIMARY KEY(roleId),
   UNIQUE(roleType)
);

CREATE TABLE CHANNEL(
   channelId INT AUTO_INCREMENT,
   channelName VARCHAR(50)  NOT NULL,
   PRIMARY KEY(channelId),
   UNIQUE(channelName)
);

CREATE TABLE COUNTRY(
   countryId INT AUTO_INCREMENT,
   countryName VARCHAR(50)  NOT NULL,
   PRIMARY KEY(countryId),
   UNIQUE(countryName)
);

CREATE TABLE SERIES(
   seriesId INT AUTO_INCREMENT,
   seriesTitle VARCHAR(50)  NOT NULL,
   releaseYear YEAR NOT NULL,
   seriesImage VARCHAR(50) ,
   alsoKnownAs VARCHAR(50)  NOT NULL,
   channelId INT,
   seriesId_spinOff INT,
   PRIMARY KEY(seriesId),
   UNIQUE(seriesImage),
   UNIQUE(alsoKnownAs),
   FOREIGN KEY(channelId) REFERENCES CHANNEL(channelId),
   FOREIGN KEY(seriesId_spinOff) REFERENCES SERIES(seriesId)
);

CREATE TABLE FOREIGN_SERIES(
   seriesId INT,
   frenchTitle VARCHAR(50)  NOT NULL,
   frenckAka VARCHAR(50)  NOT NULL,
   frenchReleaseYear YEAR,
   countryId INT NOT NULL,
   channelId INT,
   PRIMARY KEY(seriesId),
   FOREIGN KEY(seriesId) REFERENCES SERIES(seriesId),
   FOREIGN KEY(countryId) REFERENCES COUNTRY(countryId),
   FOREIGN KEY(channelId) REFERENCES CHANNEL(channelId)
);

CREATE TABLE FRENCH_SERIES(
   seriesId INT,
   PRIMARY KEY(seriesId),
   FOREIGN KEY(seriesId) REFERENCES SERIES(seriesId)
);

CREATE TABLE PRODUCTION(
   seriesId INT,
   companyId INT,
   PRIMARY KEY(seriesId, companyId),
   FOREIGN KEY(seriesId) REFERENCES SERIES(seriesId),
   FOREIGN KEY(companyId) REFERENCES COMPANY(companyId)
);

CREATE TABLE CREW(
   seriesId INT,
   personId INT,
   roleId INT,
   PRIMARY KEY(seriesId, personId, roleId),
   FOREIGN KEY(seriesId) REFERENCES SERIES(seriesId),
   FOREIGN KEY(personId) REFERENCES PERSON(personId),
   FOREIGN KEY(roleId) REFERENCES ROLE(roleId)
);

CREATE TABLE CASTING(
   seriesId INT,
   companyId INT,
   characterId INT,
   appearanceOrder TINYINT,
   period VARCHAR(50)  NOT NULL,
   personId INT NOT NULL,
   PRIMARY KEY(seriesId, companyId, characterId, appearanceOrder),
   UNIQUE(seriesId, companyId, characterId, period),
   FOREIGN KEY(seriesId) REFERENCES SERIES(seriesId),
   FOREIGN KEY(companyId) REFERENCES COMPANY(companyId),
   FOREIGN KEY(characterId) REFERENCES CHAR_PLAYED(characterId),
   FOREIGN KEY(personId) REFERENCES PERSON(personId)
);