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

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

-- ОХимия схемы данных
-- DROP TABLE IF EXISTS Table1 ...
-- ваш код здесь!
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 Table1 ...
CREATE TABLE Клиент (
Телефон CHAR(12) NOT NULL,
ФИО VARCHAR(40) NOT NULL,
ДатаРождения DATE NOT NULL,
Пол CHAR(1),
Email VARCHAR(40),
PRIMARY KEY(Телефон));

CREATE TABLE Сотрудник (
id INTEGER NOT NULL CHECK (id>0),
ФИ VARCHAR(30) NOT NULL,
Материал VARCHAR(80) NOT NULL,
Оклад DECIMAL(10,2) NOT NULL CHECK (Оклад>0),
PRIMARY KEY(id));

CREATE TABLE Заказ (
Стоимость DECIMAL(10,2) NOT NULL CHECK (Стоимость > Компенсация),
ТелефонКлиента CHAR(12) NOT NULL REFERENCES Клиент(Телефон),
КодСотрудника INTEGER NOT NULL REFERENCES Сотрудник(id),
ПриемЗаказа DATE NOT NULL,
ФактДата DATE NOT NULL CHECK (ФактДата > DATE(ПриемЗаказа, '+ 2 DAY')),
НомерЗаказа INTEGER NOT NULL,
Компенсация DECIMAL(10,2) NOT NULL,
PRIMARY KEY(НомерЗаказа));


CREATE TABLE Материал (
МатериалВещи VARCHAR(20) NOT NULL,
КомпенсацияМатериала DECIMAL(10,2) NOT NULL,
PRIMARY KEY(МатериалВещи));

CREATE TABLE Тип (
ТипВещи VARCHAR(20) NOT NULL,
МатериалВещи VARCHAR(20) NOT NULL REFERENCES Материал(МатериалВещи),
КоэффКомпенсации DECIMAL(10,2) NOT NULL,
ТребованияОбработки CHAR(25) NOT NULL,
PRIMARY KEY(ТипВещи, МатериалВещи),
FOREIGN KEY (МатериалВещи) REFERENCES Материал(МатериалВещи));

CREATE TABLE Вещь (
КодЗаказа INTEGER NOT NULL,
Наименование INTEGER NOT NULL,
ТипВещи VARCHAR(20) NOT NULL,
МатериалВещи VARCHAR (20) NOT NULL,
PRIMARY KEY (КодЗаказа, Наименование),
FOREIGN KEY (КодЗаказа) REFERENCES Заказ(НомерЗаказа),
FOREIGN KEY (ТипВещи) REFERENCES Тип(ТипВещи),
FOREIGN KEY (МатериалВещи) REFERENCES Тип(МатериалВещи));

CREATE TABLE Результат (
Результат VARCHAR(35) NOT NULL PRIMARY KEY);

CREATE TABLE Попытка (
КодСотрудника INTEGER NOT NULL,
НомерПопытки INTEGER NOT NULL,
ДатаПопытки DATE NOT NULL,
КодЗаказа INTEGER NOT NULL,
НаименованиеВещи INTEGER NOT NULL,
РезультатДляВещи VARCHAR(30) NOT NULL,
PRIMARY KEY(НомерПопытки, КодЗаказа, НаименованиеВещи),
FOREIGN KEY (КодСотрудника) REFERENCES Сотрудник(id),
FOREIGN KEY (КодЗаказа) REFERENCES Вещь(НомерЗаказа),
FOREIGN KEY (НаименованиеВещи) REFERENCES Вещь(Наименование),
FOREIGN KEY (РезультатДляВещи) REFERENCES Результат(Результат));


CREATE TABLE Химия (
Название VARCHAR(30) NOT NULL,
PRIMARY KEY(Название));

CREATE TABLE ХимияДляПопытки (
Название VARCHAR(30) NOT NULL,
НомерПопытки INTEGER NOT NULL,
КодЗаказа INTEGER NOT NULL,
НаименованиеВещи INTEGER NOT NULL,
FOREIGN KEY (НомерПопытки) REFERENCES Попытка(НомерПопытки),
FOREIGN KEY (КодЗаказа) REFERENCES Попытка(НомерЗаказа),
FOREIGN KEY (НаименованиеВещи) REFERENCES Попытка(Наименование),
FOREIGN KEY (Название) REFERENCES Химия(Название));
-- Конец заполнения схем данных

-- Заполнение данными
-- INSERT INTO Table1 ...
INSERT INTO Клиент VALUES ('+79041315574','Андреев И.О.','13.05.1990','М','AndDrey@gmail.com');
INSERT INTO Клиент VALUES ('+79649167692','Морозов А.В.','01.01.1999','М','Anatoliy72rus@tripper.ya');

INSERT INTO Сотрудник VALUES ('Григорий','1337','Оператор утюга','73000,00');
INSERT INTO Сотрудник VALUES ('Форман И.В','4444','Рабочий сферы обслуживания','20000,00');

INSERT INTO Заказ VALUES ('120,00','+79041315574','1337','13.05.2020','17.05.2020','10','70,00');
INSERT INTO Заказ VALUES ('500,00','+79649167692','4444','08.05.2020','12.05.2020','20','200,00');

INSERT INTO Материал VALUES ('Шелк','1200,00');
INSERT INTO Материал VALUES ('Полиэстер','180,00');
INSERT INTO Материал VALUES ('Шерсть','750,00');

INSERT INTO Тип VALUES ('Пальто','Шерсть','4,75','Не утюжить');
INSERT INTO Тип VALUES ('Шарф','Шелк','0,73','Не более 30 гр.');
INSERT INTO Тип VALUES ('Штаны','Полиэстер','1,3','Холодная вода, не утюжить');

INSERT INTO Вещь VALUES ('10','17856','Шарф','Шелк','Вещь очищена');
INSERT INTO Вещь VALUES ('20','12864','Штаны','Полиэстер','Вещь испорчена');

INSERT INTO Результат VALUES ('Вещь испорчена');
INSERT INTO Результат VALUES ('Вещь очищена');
INSERT INTO Результат VALUES ('Очистить не удалось, но и хуже не стало');

INSERT INTO Попытка VALUES ('4444','1','09.05.2020','20','12864','Вещь испорчена');
INSERT INTO Попытка VALUES ('1337','5','14.05.2020','10','17856','Вещь очищена');

INSERT INTO Химия VALUES ('Dr.Schnell для ковров');
INSERT INTO Химия VALUES ('Pramol антивспениватель');
INSERT INTO Химия VALUES ('Solemate');
INSERT INTO Химия VALUES ('Tapi-3000');
INSERT INTO Химия VALUES ('Белизна');

INSERT INTO ХимияДляПопытки VALUES ('Solemate','5','10','17856');
INSERT INTO ХимияДляПопытки VALUES ('Tapi-3000','1','20','12864');
-- Конец заполнения данных