-- Лабораторная работа №3, дисциплина "Базы данных", СПбГУАП, весенний семестр 2019

-- Включение форсирования ограничений ссылочной целостности
PRAGMA foreign_keys = ON;

-- Очистка схемы данных
PRAGMA FOREIGN_KEYS=ON;
DROP TABLE IF EXISTS Прогон;
DROP TABLE IF EXISTS Выступление;
DROP TABLE IF EXISTS Репетиция;
DROP TABLE IF EXISTS Участие_сотрудника_спектакле;
DROP TABLE IF EXISTS Строка_расписания;
DROP TABLE IF EXISTS Предмет_спектакля;
DROP TABLE IF EXISTS Роль;
DROP TABLE IF EXISTS Спектакль;
DROP TABLE IF EXISTS Актер;
DROP TABLE IF EXISTS Предмет;
DROP TABLE IF EXISTS Сотрудник;

-- Создание схемы данных
create table Сотрудник(
    ФИО text default('Герман'),
    Паспорт integer primary key,
    Домашний_адрес text,
    Мобильный_телефон text,
    Домашний_телефон text,
    Должность text);

create table Предмет(
    Название_предмета text,
    Код_предмета integer primary key,
    Тип_предмета text);

create table Актер(
    ФИО text,
    Код_актера integer primary key,
    Амплуа text,
    Мобильный_телефон text,
    Домашний_телефон text,
    CHECK (Мобильный_телефон OR Домашний_телефон));

create table Спектакль(
    Название_спектакля text,
    Автор text,
    Год_постановки integer,
    Жанр text,
    Количество_актов integer,
    Сюжет text,
    primary key(Название_спектакля, Год_постановки),
    CHECK(Год_постановки <= strftime('%Y','now')));

create table Роль(
    Название_роли text,
    Тип_роли text,
    Название_пьесы text,
    Год_постановки integer,
    foreign key(Название_пьесы, Год_постановки)references Спектакль(Название_спектакля, Год_постановки),
    primary key(Название_роли, Название_пьесы, Год_постановки));

create table Предмет_спектакля(
    Код_предмета integer,
    Название_спектакля text,
    Год_постановки integer,
    foreign key(Код_предмета)references Предмет(Код_предмета),
    foreign key(Название_спектакля, Год_постановки)references Спектакль(Название_спектакля, Год_постановки),
    primary key(Код_предмета, Название_спектакля, Год_постановки));

create table Строка_расписания(
    Дата_спектакля date,
    Время_спектакля time,
    Стоимость_билета integer,
    Название_спектакля text,
    Год_постановки integer,
    foreign key(Название_спектакля, Год_постановки)references Спектакль(Название_спектакля, Год_постановки),
    primary key(Дата_спектакля, Время_спектакля));

create table Участие_сотрудника_спектакле(
    Паспорт integer,
    Дата_спектакля date,
    Время_спектакля time,
    foreign key(Паспорт)references Сотрудник(Паспорт),
    foreign key(Дата_спектакля, Время_спектакля)references Строка_расписания(Дата_спектакля, Время_спектакля),
    primary key(Паспорт, Дата_спектакля, Время_спектакля));

create table Репетиция(
    Дата_репетиции date,
    Время_репетиции time,
    Название_спектакля text,
    Год_постановки integer,
    foreign key(Название_спектакля, Год_постановки)references Спектакль(Название_спектакля, Год_постановки),
    primary key(Дата_репетиции, Время_репетиции));

create table Выступление(
    Дата_спектакля date,
    Время_спектакля time,
    Код_актера integer,
    Название_роли text,
    Название_пьесы text,
    Год_постановки integer,
    foreign key(Дата_спектакля, Время_спектакля)references Строка_расписания(Дата_спектакля, Время_спектакля),
    foreign key(Код_актера)references Актер(Код_актера),
    foreign key(Название_роли, Название_пьесы, Год_постановки)references Роль(Название_роли, Название_пьесы, Год_постановки),
    primary key(Дата_спектакля, Время_спектакля, Название_роли, Название_пьесы));

create table Прогон(
    Дата_репетиции date,
    Время_репетиции time,
    Код_актера integer,
    Название_роли text,
    Название_пьесы text,
    Год_постановки integer,
    foreign key(Дата_репетиции, Время_репетиции)references Репетиция(Дата_репетиции, Время_репетиции),
    foreign key(Код_актера)references Актер(Код_актера),
    foreign key(Название_роли, Название_пьесы, Год_постановки)references Роль(Название_роли, Название_пьесы, Год_постановки),
    primary key(Дата_репетиции, Время_репетиции, Название_роли, Название_пьесы));

-- Заполнение данными
insert into Актер values('Антонов',1,'Кокет','92138','32374');
insert into Актер values('Гавриленко',2,'Трагик','11111','');
insert into Актер values('Нагорных',3,'Грасьосо','','7776');
insert into Актер values('Габен',4,'Простак','','12123');

insert into Сотрудник values('Бондаренко',1825494,'Улица Бондаря','4632730684','','Суфлер');
insert into Сотрудник values('Копытин',2281337,'Улица Колотушкина','5553535','235256','Повар');
insert into Сотрудник values('Гаврила',2281477,'Улица Пушкина','777777','','Уборщик');
insert into Сотрудник values('Блинов',3456789,'Улица Блинов','34446','','Грузчик');

insert into Предмет values('Зонт',1,'Инвентарь');
insert into Предмет values('Стул',2,'Мебель');
insert into Предмет values('Стол',3,'Мебель');
insert into Предмет values('Шкаф',4,'Мебель');
insert into Предмет values('Костюм Льва',5,'Костюм');
insert into Предмет values('Тестовый костюм',6,'Костюм');

insert into Спектакль values('Лебединое озеро','Чайковский',2010,'Комедия',3,'Ьез спойлеров');
insert into Спектакль values('Ромео и Джульета','Шекспир',2018,'Драма',3,'Ьез спойлеров');
insert into Спектакль values('Трагическая история о Гамлете','Шекспир',2017,'Трагедия',1,'Ьез спойлеров');
insert into Спектакль values('Щелкунчик','Чайковский',2016,'Боевик',2,'Ьез спойлеров');

insert into Роль values('Гамлет','Первого плана','Трагическая история о Гамлете', 2017);
insert into Роль values('Джульета','Первого плана','Ромео и Джульета', 2018);
insert into Роль values('Йорик','Второго плана','Трагическая история о Гамлете', 2017);
insert into Роль values('Ромео','Первого плана','Ромео и Джульета', 2018);
insert into Роль values('Лебедь','Первого плана','Лебединое озеро',2010);

insert into Предмет_спектакля values(1,'Ромео и Джульета',2018);
insert into Предмет_спектакля values(2,'Ромео и Джульета',2018);
insert into Предмет_спектакля values(3,'Лебединое озеро',2010);
insert into Предмет_спектакля values(4,'Трагическая история о Гамлете',2017);
insert into Предмет_спектакля values(5,'Щелкунчик',2016);

insert into Строка_расписания values('2019-04-20','18:00:00',2000,'Лебединое озеро',2010);
insert into Строка_расписания values('2019-04-20','21:30:00',3500,'Ромео и Джульета',2018);
insert into Строка_расписания values('2019-05-20','21:30:00',3500,'Ромео и Джульета',2018);
--insert into Строка_расписания values('2019-05-08','17:00:00',2000,'Щелкунчик',2016);
insert into Строка_расписания values('2019-05-10','17:00:00',2500,'Трагическая история о Гамлете',2017);

insert into Строка_расписания values('2019-01-20','21:30:00',3500,'Ромео и Джульета',2018);
insert into Строка_расписания values('2018-02-05','21:30:00',3500,'Лебединое озеро',2010);

--insert into Строка_расписания values('2018-02-06','16:30:00',3500,'Лебединое озеро',2010);
--insert into Строка_расписания values('2018-02-07','16:30:00',3500,'Лебединое озеро',2010);
--insert into Строка_расписания values('2018-02-08','16:30:00',3500,'Лебединое озеро',2010);


--insert into Участие_сотрудника_спектакле values(1825494,'2019-05-08','17:00:00');
insert into Участие_сотрудника_спектакле values(2281337,'2019-04-20','18:00:00');
insert into Участие_сотрудника_спектакле values(2281477,'2019-04-20','18:00:00');
insert into Участие_сотрудника_спектакле values(3456789,'2019-05-10','17:00:00');

insert into Репетиция values('2019-04-17','14:00:00','Лебединое озеро',2010);
insert into Репетиция values('2019-04-17','17:00:00','Ромео и Джульета',2018);
insert into Репетиция values('2019-04-17','16:00:00','Ромео и Джульета',2018);--
insert into Репетиция values('2019-04-17','15:00:00','Ромео и Джульета',2018);--
insert into Репетиция values('2019-04-19','17:00:00','Ромео и Джульета',2018);
insert into Репетиция values('2019-04-25','17:00:00','Ромео и Джульета',2018);
insert into Репетиция values('2019-04-18','18:00:00','Лебединое озеро',2010);
insert into Репетиция values('2019-04-18','20:00:00','Трагическая история о Гамлете',2017);
insert into Репетиция values('2019-04-20','16:00:00','Щелкунчик',2016);


--insert into Спектакль values('Лебединое озеро','Чайковский',2010,'Комедия',3,'Ьез спойлеров');
--insert into Спектакль values('Ромео и Джульета','Шекспир',2018,'Драма',3,'Ьез спойлеров');
--insert into Спектакль values('Трагическая история о Гамлете','Шекспир',2017,'Трагедия',1,'Ьез спойлеров');
--insert into Спектакль values('Щелкунчик','Чайковский',2016,'Боевик',2,'Ьез спойлеров');

insert into Выступление values('2019-04-20','21:30:00',2,'Джульета','Ромео и Джульета',2018);
insert into Выступление values('2019-04-20','21:30:00',3,'Ромео','Ромео и Джульета',2018);--1
insert into Выступление values('2019-05-20','21:30:00',2,'Джульета','Ромео и Джульета',2018);
insert into Выступление values('2019-05-20','21:30:00',1,'Ромео','Ромео и Джульета',2018);--1
insert into Выступление values('2019-05-10','17:00:00',3,'Гамлет','Трагическая история о Гамлете',2017);
insert into Выступление values('2019-05-10','17:00:00',4,'Йорик','Трагическая история о Гамлете',2017);

insert into Выступление values('2019-01-20','21:30:00',2,'Джульета','Ромео и Джульета',2018);
insert into Выступление values('2019-01-20','21:30:00',1,'Ромео','Ромео и Джульета',2018);
insert into Выступление values('2018-02-05','21:30:00',1,'Лебедь','Лебединое озеро',2010);

insert into Прогон values('2019-04-17','17:00:00',2,'Джульета','Ромео и Джульета',2018);
insert into Прогон values('2019-04-17','17:00:00',1,'Ромео','Ромео и Джульета',2018);
insert into Прогон values('2019-04-19','17:00:00',2,'Джульета','Ромео и Джульета',2018);
insert into Прогон values('2019-04-19','17:00:00',1,'Ромео','Ромео и Джульета',2018);
insert into Прогон values('2019-04-25','17:00:00',2,'Джульета','Ромео и Джульета',2018);
insert into Прогон values('2019-04-25','17:00:00',1,'Ромео','Ромео и Джульета',2018);
insert into Прогон values('2019-04-18','20:00:00',3,'Гамлет','Трагическая история о Гамлете',2017);
insert into Прогон values('2019-04-18','20:00:00',4,'Йорик','Трагическая история о Гамлете',2017);




DROP TRIGGER IF EXISTS Trigger3
CREATE TRIGGER Trigger3
after insert
on Прогон
when new.Дата_репетиции in ( select Дата_репетиции
                             from Прогон
                             where new.Код_актера = Код_актера
                             group by Код_актера, Дата_репетиции
                             having count(*) > 2 )
begin
select raise(ROLLBACK,"Остынь актеришко");
End;


insert into Строка_расписания values('2019-05-08','17:00:00',2000,'Щелкунчик',2016);