PRAGMA foreign_keys = ON;

DROP TABLE IF EXISTS Actor;
DROP TABLE IF EXISTS ActorEpisode;
DROP TABLE IF EXISTS Episode;
DROP TABLE IF EXISTS Producer;
DROP TABLE IF EXISTS Scenarist;
DROP TABLE IF EXISTS ScenaristEpisode;
DROP TABLE IF EXISTS ScoreE;
DROP TABLE IF EXISTS ScoreS;
DROP TABLE IF EXISTS Season;
DROP TABLE IF EXISTS Title;
DROP TABLE IF EXISTS User;



CREATE TABLE Scenarist(
      Id INTEGER PRIMARY KEY AUTOINCREMENT,
      Fname  TEXT NOT NULL,
      Surname TEXT NOT NULL
);

CREATE TABLE User(
      Id INTEGER PRIMARY KEY AUTOINCREMENT,
      Mail TEXT NOT NULL,
      Nickname TEXT NOT NULL,
      Password TEXT NOT NULL
);

CREATE TABLE Producer(
      Id INTEGER PRIMARY KEY AUTOINCREMENT,
      Fname  TEXT NOT NULL,
      Surname TEXT NOT NULL
);

CREATE TABLE Actor(
      IdActor INTEGER PRIMARY KEY AUTOINCREMENT,
      Fname  TEXT NOT NULL,
      Surname TEXT NOT NULL,
      date_of_birth DATE,
      date_of_death DATE NULL
);

CREATE TABLE Title(
      Idtitle INTEGER PRIMARY KEY AUTOINCREMENT,
      Startd DATE NOT NULL,
      Endd DATE,
      Title TEXT,
      Genre TEXT
);

CREATE TABLE Season(
      Idtitle INTEGER REFERENCES Title(Idtitle),
      seasonnumber INTEGER,
      NameSeason TEXT,
      PRIMARY KEY(Idtitle,seasonnumber)
);

CREATE TABLE Episode(
      Idtitle INTEGER REFERENCES Season(Idtitle),
      seasonnumber INTEGER REFERENCES Season(seasonnumber),
      series INTEGER,
      Producerid INTEGER REFERENCES Producer(Id),
      Description TEXT,
      NameEpisode TEXT,
      PRIMARY KEY(Idtitle, seasonnumber, series)
);

CREATE TABLE ActorEpisode(
      IdActor INTEGER REFERENCES Actor(IdActor),
      Idtitle INTEGER REFERENCES Episode(Idtitle),
      seasonnumber INTEGER REFERENCES Episode(seasonnumber),
      series INTEGER REFERENCES Episode(series),
      PRIMARY KEY(Idtitle, seasonnumber, series, IdActor)
);

CREATE TABLE ScoreE(
      Score INTEGER CHECK(Score BETWEEN 1 AND 5),
      Idtitle INTEGER REFERENCES Episode(Idtitle),
      seasonnumber INTEGER REFERENCES Episode(seasonnumber),
      series INTEGER REFERENCES Episode(series),
      user INTEGER REFERENCES User(Id),
      PRIMARY KEY(Idtitle, seasonnumber, series, user)
);

CREATE TABLE ScoreS(
      Score INTEGER CHECK(Score BETWEEN 1 AND 5),
      Idtitle INTEGER REFERENCES Title(Idtitle),
      user INTEGER REFERENCES User(Id),
      PRIMARY KEY(Idtitle, user)
);

CREATE TABLE ScenaristEpisode(
      id INTEGER REFERENCES Scenarist(id),
      Idtitle INTEGER REFERENCES Episode(Idtitle),
      seasonnumber INTEGER REFERENCES Episode(seasonnumber),
      series INTEGER REFERENCES Episode(series),
      PRIMARY KEY(Idtitle, seasonnumber, series, id)
);



INSERT INTO Scenarist(Fname,Surname) VALUES('Максим','Демчук');
INSERT INTO Scenarist(Fname,Surname) VALUES('Александр','Тищенко');
INSERT INTO Scenarist(Fname,Surname) VALUES('Александр','Ёлкин');
INSERT INTO Scenarist(Fname,Surname) VALUES('Иван','Житников');
INSERT INTO Scenarist(Fname,Surname) VALUES('Алексей','Люблинский');

INSERT INTO Actor(Fname,Surname,date_of_birth) VALUES('Демчук','Максим',1997);
INSERT INTO Actor(Fname,Surname,date_of_birth) VALUES('Уильям','Пит',1963);
INSERT INTO Actor(Fname,Surname,date_of_birth) VALUES('Уилл','Смит',1968);
INSERT INTO Actor(Fname,Surname,date_of_birth) VALUES('Бенджамин','Мффлек',1972);
INSERT INTO Actor(Fname,Surname,date_of_birth,date_of_death) VALUES('Станислав','Говорухин',1936,2018);

INSERT INTO Title(Startd,Endd,Title,Genre)
       VALUES('2010-02-12','2011-01-9','Название','Комедия');

INSERT INTO Season(Idtitle,seasonnumber,NameSeason) VALUES(1, 1,'Проверка1');
INSERT INTO Season(Idtitle,seasonnumber,NameSeason) VALUES(1, 2,'Проверка2');
INSERT INTO Season(Idtitle,seasonnumber,NameSeason) VALUES(1, 3,'Проверка3');
INSERT INTO Season(Idtitle,seasonnumber,NameSeason) VALUES(1, 4,'Проверка4');
INSERT INTO Season(Idtitle,seasonnumber,NameSeason) VALUES(1, 5,'Проверка5');

INSERT INTO Producer(Fname,Surname) VALUES('Максим','Демчук');
INSERT INTO Producer(Fname,Surname) VALUES('Александр','Тищенко');
INSERT INTO Producer(Fname,Surname) VALUES('Александр','Ёлкин');
INSERT INTO Producer(Fname,Surname) VALUES('Иван','Житников');
INSERT INTO Producer(Fname,Surname) VALUES('Алексей','Люблинский');

INSERT INTO User(mail,Nickname,Password) VALUES('maxdem4uk@yandex.ru','Zeroes','qwerty123');
INSERT INTO User(mail,Nickname,Password) VALUES('mail@mail.com','aerok','qwerty456');
INSERT INTO User(mail,Nickname,Password) VALUES('raf@rambler.ru','rafferty_al','qwerty789');
INSERT INTO User(mail,Nickname,Password) VALUES('hassi@gmail.com','hasenblake','qwerty1546');
INSERT INTO User(mail,Nickname,Password) VALUES('turk@mail.com','stakera','qwerty123');

INSERT INTO ScoreS(Score,Idtitle,user) VALUES(3,1,1);
INSERT INTO ScoreS(Score,Idtitle,user) VALUES(5,1,2);
INSERT INTO ScoreS(Score,Idtitle,user) VALUES(2,1,3);
INSERT INTO ScoreS(Score,Idtitle,user) VALUES(1,1,4);
INSERT INTO ScoreS(Score,Idtitle,user) VALUES(4,1,5);

INSERT INTO Episode(Idtitle, seasonnumber, series, Producerid, Description, NameEpisode) VALUES(1, 4, 1, 1, 'Проверка проверки 1', 'Хоть бы заработло');