DROP TABLE IF EXISTS client CASCADE;
DROP TABLE IF EXISTS dealer CASCADE;
DROP TABLE IF EXISTS contract CASCADE;
DROP TABLE IF EXISTS repair CASCADE;
DROP TABLE IF EXISTS master CASCADE;
DROP TABLE IF EXISTS STO CASCADE;
DROP TABLE IF EXISTS fix CASCADE;
DROP SEQUENCE IF EXISTS s_client CASCADE;
DROP SEQUENCE IF EXISTS s_dealer CASCADE;
DROP SEQUENCE IF EXISTS s_contract CASCADE;
DROP SEQUENCE IF EXISTS s_repair CASCADE;
DROP SEQUENCE IF EXISTS s_master CASCADE;
DROP SEQUENCE IF EXISTS s_STO CASCADE;
DROP SEQUENCE IF EXISTS s_fix CASCADE;
DROP FUNCTION IF EXISTS sumSale(name text) CASCADE ;
DROP FUNCTION IF EXISTS updatePriceSale()  CASCADE ;
DROP FUNCTION IF EXISTS kolv(N INT) CASCADE ;
DROP FUNCTION IF EXISTS kolvD(N INT) CASCADE ;
DROP TABLE IF EXISTS total_priceSale_and_commission CASCADE ;
DROP FUNCTION IF EXISTS checkId() CASCADE ;
DROP FUNCTION IF EXISTS add_dealer() CASCADE ;

CREATE SEQUENCE s_client;
CREATE SEQUENCE s_dealer;
CREATE SEQUENCE s_contract;
CREATE SEQUENCE s_repair;
CREATE SEQUENCE s_master;
CREATE SEQUENCE s_STO;
CREATE SEQUENCE s_fix;

CREATE TABLE IF NOT EXISTS STO (
id INT PRIMARY KEY DEFAULT nextval('s_STO'),
name text NOT NULL,
address text NOT NULL,
phone text NOT NULL,
email text DEFAULT null,
dateFounding text NOT NULL,
assessment float DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS client (
id INT PRIMARY KEY DEFAULT nextval('s_client'),
fullName text NOT NULL,
cityName text NOT NULL,
phone text NOT NULL,
address text NOT NULL
);

CREATE TABLE IF NOT EXISTS dealer (
id INT PRIMARY KEY DEFAULT nextval('s_dealer'),
fullName text NOT NULL,
carView text NOT NULL,
photoNumber int NOT NULL,
phone text NOT NULL,
address text NOT NULL
);

CREATE TABLE IF NOT EXISTS fix (
id INT PRIMARY KEY DEFAULT nextval('s_fix'),
nameFix text NOT NULL,
specialty text NOT NULL
);

CREATE TABLE IF NOT EXISTS contract (
id INT PRIMARY KEY DEFAULT nextval('s_contract'),
idClient int NOT NULL,
idDealer int NOT NULL,
dateContract DATE NOT NULL,
carBrand text NOT NULL,
photoCarNumber int NOT NULL,
dateIssue DATE NOT NULL,
mileage int NOT NULL,
dateSale DATE NOT NULL,
priceSale decimal(50,2) CHECK (priceSale>0) NOT NULL,
commission decimal(50,2) CHECK (commission<contract.priceSale) NOT NULL,
notes text DEFAULT NULL,
FOREIGN KEY (idClient) REFERENCES client(id),
FOREIGN KEY (idDealer) REFERENCES dealer(id)
);

CREATE TABLE IF NOT EXISTS master (
id INT PRIMARY KEY DEFAULT nextval('s_master'),
name text NOT NULL,
idSTO int NOT NULL,
specialty text NOT NULL,
experience int CHECK (experience>0) NOT NULL,
salary decimal(50,2) CHECK (salary>0) NOT NULL,
phone text NOT NULL,
address text NOT NULL,
FOREIGN KEY (idSTO) REFERENCES STO(id)
);

CREATE TABLE IF NOT EXISTS repair (
id INT PRIMARY KEY DEFAULT nextval('s_repair'),
idFix int NOT NULL,
idContract int NOT NULL,
idMaster int NOT NULL,
dateStart DATE NOT NULL,
repairPeriodDay int CHECK (repairPeriodDay>0) NOT NULL,
price decimal(50,2) CHECK (price>0) NOT NULL,
FOREIGN KEY (idFix) REFERENCES fix (id),
FOREIGN KEY (idContract) REFERENCES contract(id),
FOREIGN KEY (idMaster) REFERENCES master(id)
);

/*INSERT INTO fix
VALUES (DEFAULT, 'Motor', 'Dvij');
INSERT INTO client
VALUES (DEFAULT, 'Petr Petrov Petrovich', 'Kharkov', 'Kojemako 5a', 38082928);
INSERT INTO dealer
VALUES (DEFAULT, 'John Bar Fergust', 1, 'Fora 3', 390827232);
INSERT INTO contract
VALUES (DEFAULT, 1, 1, '2018-01-18', 'BMW', 1, '2012-05-02', 50000, '2012-06-20',
20000, 2000, 'TEXT: SHOTO SLUCHILOS');
INSERT INTO master
VALUES (DEFAULT, 'Ivan Pedro Kurion', 1, 'All', 15, 30000, 9023842, 'pr. Lemur
45v');
INSERT INTO repair
VALUES (DEFAULT, 1, 1, 1, '2018-01-20', 90, 4000);
SELECT * FROM contract;*/


INSERT INTO STO
VALUES (DEFAULT, 'Галиан', 'город Одесса, улица Разумовская, 14','0964553288, 0633731348, 0509285554, 0487093236', 'Lutskanandrew@gmail.com', '2010-02-01');
INSERT INTO STO
VALUES (DEFAULT, 'Limard', 'город Одесса, улица Приморская 18', '482342233', 'gallian@paco.net', '2012-10-20',5.0);
INSERT INTO STO
VALUES (DEFAULT, 'AutoHelp', 'город Одесса, улица Гаражный кооператив Ветеран 4', '380634790095, 380973328049', 'autohelp@gmail.com', '2014-09-25', 2.5);
INSERT INTO STO
VALUES (DEFAULT, 'Мастер Кар', 'город Одесса, улица Михаила Грушевского, 17А', '0487707788', 'g37od@mail.ru,  info@mastercar-odessa', '2011-07-15',3.5);
INSERT INTO STO
VALUES (DEFAULT, 'Автотрейдинг–Одесса', 'город Одесса, улица 6-й км Овидиопольской дороги, 10/2, 39/4', '0487343658', '', '2015-08-09', 4.8);

/*Работники СТО Галиан*/
INSERT INTO master
VALUES (DEFAULT, 'Федотов Алексей Александрович', 1, 'Моторист', 3, 18000, '380673489234', 'город Одесса, улица Пантелеймоновская 76');
INSERT INTO master
VALUES (DEFAULT, 'Демчук Леонид Викторович', 1, 'Механик-диагност', 5, 20000, '380661398475', 'город Одесса, улица Сегедская 21');
INSERT INTO master
VALUES (DEFAULT, 'Арнович Борис Сергеевич', 1, 'Автоэлектрик', 4, 17000, '380965348712', 'город Одесса, улица Канатная 65');
INSERT INTO master
VALUES (DEFAULT , 'Радужный Дмитрий Михайлович', 1, 'Маляр', 4, 14500, '380934328412', 'город Одесса, улица Академика Королёва 70');
INSERT INTO master
VALUES (DEFAULT , 'Береговой Фёдр Александрович', 1, 'Автожестянщик', 2, 15000, '380934565345', 'город Одесса, улица Академика Филатова 90');
INSERT INTO master
VALUES (DEFAULT, 'Саратов Георгий Викторович', 1, 'Моторист', 6, 23000, '380672398475', 'город Одесса, улица Прохоровская 30');
INSERT INTO master
VALUES (DEFAULT , 'Бравый Александр Александрович', 1, 'Маляр', 5, 18000, '380953745890', 'город Одесса, улица Итальянский бульвар 3');

/*Работники СТО Limard*/
INSERT INTO master
VALUES (DEFAULT , 'Краснянский Артур Левович', 2, 'Моторист', 7, 25000,'380650449122', 'город Одесса, улица Кантная 23');
INSERT INTO master
VALUES (DEFAULT , 'Ягодич Максимильян Игнатиевич', 2, 'Моторист', 4, 18000,'380112876627', 'город Одесса, улица Сегедская 15');
INSERT INTO master
VALUES (DEFAULT , 'Мартюшев Валерьян Иванович', 2, 'Автожестянщик', 4, 20000,'380941701860', 'город Одесса, улица Ришельевская 5');
INSERT INTO master
VALUES (DEFAULT , 'Абушаев Адриан Севастьянович', 2, 'Автоэлектрик', 6, 24000,'380846516326', 'город Одесса, улица Шевченка, 8/3');
INSERT INTO master
VALUES (DEFAULT , 'Кожевин Аристарх Евлампиевич', 2, 'Автоэлектрик', 7, 27000,'380225467720', 'город Одесса, улица Екатерининська, 12');
INSERT INTO master
VALUES (DEFAULT , 'Абоймов Харитон Евстафиевич', 2, 'Механик-диагност', 3, 14000,'380226771478', 'город Одесса, улица Шевченка, 4Е');
INSERT INTO master
VALUES (DEFAULT , 'Попков Юрий Ульянович', 2, 'Механик-диагност', 4, 16000,'380780823150', 'город Одесса, улица Прохоровськая, 8');
INSERT INTO master
VALUES (DEFAULT , 'Попков Юрий Ульянович', 2, 'Механик-диагност', 3, 12000,'380879303623', 'город Одесса, улица Канатна, 132');
INSERT INTO master
VALUES (DEFAULT , 'Попков Юрий Ульянович', 2, 'Маляр', 8, 19000,'380979177143', 'город Одесса, улица Маршала Говорова, 10/1');
INSERT INTO master
VALUES (DEFAULT , 'Занина Лидия Василиевна', 2, 'Маляр', 4, 12000,'380696116615', 'город Одесса, улица Шевченка, 16');
INSERT INTO master
VALUES (DEFAULT , 'Чунца Василиса Федотовна', 2, 'Вулканизаторщик', 2, 10000,'380743976899', 'город Одесса, улица Фонтанська дорога, 61');

/*Работники СТО AutoHelp*/
INSERT INTO master
VALUES (DEFAULT , 'Юнге Аскольд Натанович', 3, 'Моторист', 2, 10000, '380132229055', 'город Одесса, улица Академика Королёва 12 кв. 23');
INSERT INTO master
VALUES (DEFAULT , 'Григорьев Никифор Игнатиевич', 3, 'Механик-диагност', 1, 7000, '380549214204', 'город Одесса, Небесной Сотни проспект 30в');
INSERT INTO master
VALUES (DEFAULT , 'Зёмин Андриян Вадимович', 3, 'Механик-диагност', 3, 13000, '380267453395', 'город Одесса, улица Генерала Пертрова 2 кв. 3');
INSERT INTO master
VALUES (DEFAULT , 'Иванков Матвей Измаилович', 3, 'Автоэлектрик', 2, 11000, '380688897133', 'город Одесса, улица Прохоровская 12');
INSERT INTO master
VALUES (DEFAULT , 'Бойдало Эдуард Леонович', 3, 'Маляр', 4, 10000, '380357144390', 'город Одесса, Люстдорфская дорога 14');

/*Работники СТО Мастер Кар*/
INSERT INTO master
VALUES (DEFAULT , 'Шерешевский Осип Прокофиевич', 4, 'Моторист', 4, 16000, '380635385405', 'город Одесса, улица Академика Глушко 3а кв.45');
INSERT INTO master
VALUES (DEFAULT , 'Куделин Максимильян Зиновиевич', 4, 'Моторист', 3, 15000, '380136882710', 'город Одесса, улица Марсельская 32А');
INSERT INTO master
VALUES (DEFAULT , 'Балахнов Степан Тимурович', 4, 'Автожестянщик', 7, 19000, '380719660773', 'город Одесса, улица Асташкина 19 кв.32');
INSERT INTO master
VALUES (DEFAULT , 'Федосов Валерий Ипатиевич', 4, 'Механик-диагност', 8, 25000, '380627228748', 'город Одесса, проспект Гагрина 49');
INSERT INTO master
VALUES (DEFAULT , 'Труш Лука Зиновиевич', 4, 'Механик-диагност', 2, 12000, '380343960401', 'город Одесса, улица Болгарская 49');
INSERT INTO master
VALUES (DEFAULT , 'Мамонов Максимильян Георгиевич', 4, 'Автоэлектрик', 4, 18000, '380242522147', 'город Одесса, улица Одария 12');
INSERT INTO master
VALUES (DEFAULT , 'Гольца Анисья Борисовна', 4, 'Автоэлектрик', 5, 20000, '380202284424', 'город Одесса, улица Высоцкого 12/3');
INSERT INTO master
VALUES (DEFAULT , 'Румянцева Оксана Анатолиевна', 4, 'Механик-диагност', 6, 28000, '380403646596', 'город Одесса, улица Садовая 3 кв. 39');
INSERT INTO master
VALUES (DEFAULT , 'Аспидов Владилен Иосифович', 4, 'Маляр', 2, 10000, '380554435759', 'город Одесса, улица Канатная 2 кв 23');
INSERT INTO master
VALUES (DEFAULT , 'Ольховский Парфен Фомевич', 4, 'Маляр', 7, 20000, '380736491251', 'город Одесса, улица Сегедская 15а');

/*Работники СТО Авторейдинг Одесса*/
INSERT INTO master
VALUES (DEFAULT , 'Чукреев Аркадий Владимирович', 5, 'Моторист', 10,18000 , '380838777704', 'город Одесса, проспект Добровольского 5');
INSERT INTO master
VALUES (DEFAULT , 'Филенков Дементий Модестович', 5, 'Механик-диагност', 9, 2000, '380950498104', 'город Одесса, улица Пантелеймоновская 99');
INSERT INTO master
VALUES (DEFAULT , 'Устинов Михей Мечиславович', 5, 'Механик-диагност', 15, 30000, '380925035772', 'город Одесса, улица Марсельская 65');
INSERT INTO master
VALUES (DEFAULT , 'Панюшкин Елисей Яковович', 5, 'Автоэлектрик', 9, 23000, '380631701028', 'город Одесса, улица Асташкина 37');
INSERT INTO master
VALUES (DEFAULT , 'Замятин Михей Никонович', 5, 'Моторист', 20, 40000, '380903923780', 'город Одесса, проспект Гагарина 34 квю 73');
INSERT INTO master
VALUES (DEFAULT , 'Махмудов Федот Сергеевич', 5, 'Автожестянщик', 10, 27000, '380852239808', 'город Одесса, улица Одария 2');
INSERT INTO master
VALUES (DEFAULT , 'Гроссман Юлиан Вячеславович', 5, 'Автожестянщик', 7, 17000, '380434230331', 'город Одесса, улица Садовая 89');
INSERT INTO master
VALUES (DEFAULT , 'Луньков Михей Тихонович', 5, 'Вулканизаторщик', 4, 20000, '380754129626', 'город Одесса, улица Канатная 32 кв. 54');
INSERT INTO master
VALUES (DEFAULT , 'Яндарбиев Потап Макарович', 5, 'Маляр', 5, 24000, '380208468539', 'город Одесса, улица Сегедская 17 кв. 42');

INSERT INTO dealer
VALUES (DEFAULT , 'Интер-Авто', 'MAZDA', 1635, '565(051)747-19-50', 'город Одесса, улица Канатная 36');
INSERT INTO dealer
VALUES (DEFAULT , 'Palisade', 'HYUNDAI', 1157, '41(38)420-14-15', 'город Одесса, проспект Шевченка 63');
INSERT INTO dealer
VALUES (DEFAULT , 'Эмералд Моторс', 'BMW', 8810, '849(77)291-33-39', 'город Одесса, улица Академика Королёва 2/5');
INSERT INTO dealer
VALUES (DEFAULT , 'Автомоильный дом', 'WOLKSWAGEN', 4677, '8(646)649-01-19', 'город Одесса, улица Сегедская 30/4');
INSERT INTO dealer
VALUES (DEFAULT , 'Авто Груп', 'RENAULT', 7864, '9(65)997-53-74', 'город Одесса, улица Академика Глушко 112');
INSERT INTO dealer
VALUES (DEFAULT , 'Одесса-Авто', 'KIA', 6026, '1(929)814-17-23', 'город Одесса, улица Ришельевская 74');
INSERT INTO dealer
VALUES (DEFAULT , 'Fiat-Авто', 'FIAT', 1276, '15(25)805-80-23', 'город Одесса, улица Фонтаская 3');
INSERT INTO dealer
VALUES (DEFAULT , 'АВТОМИР', 'JEEP', 9440, '88(123)192-23-40', 'город Одесса, улица Левитана 41');
INSERT INTO dealer
VALUES (DEFAULT , 'Mersedes Company', 'MERSEDES', 4462, '81(549)948-30-63', 'город Одесса, улица Артиллерийская 14');

INSERT INTO client
VALUES (DEFAULT , 'Цейдлица Зоя Данииловна', 'Одесса', '380314106225', 'город Одесса, улица Филатова 23');
INSERT INTO client
VALUES (DEFAULT , 'Кривоухова Милена Елизаровна', 'Одесса', '380154915929', 'город Одесса, улица Канатная 12 кв. 54');
INSERT INTO client
VALUES (DEFAULT , 'Десяткова Анфиса Василиевна', 'Одесса', '380513635207', 'город Одесса, улица Академика Глушко 27 кв. 56');
INSERT INTO client
VALUES (DEFAULT , 'Пыжалов Прокл Сигизмундович', 'Одесса', '380607363669', 'город Одесса, улица Канатная 56 кв. 19');
INSERT INTO client
VALUES (DEFAULT , 'Перевалова Вероника Ефимовна', 'Одесса', '380285183625', 'город Одесса, улица Сегедская 64');
INSERT INTO client
VALUES (DEFAULT , 'Пересторонин Варфоломей Тихонович', 'Одесса', '380963300996', 'город Одесса, улица Ришельевская 94');
INSERT INTO client
VALUES (DEFAULT , 'Горемыкин Эрнест Мирославович', 'Одесса', '380789579999', 'город Одесса, улица Левитана 64');
INSERT INTO client
VALUES (DEFAULT , 'Царегородцев Никита Всеволодович', 'Одесса', '380756445342', 'город Одесса, улица Одария 74');
INSERT INTO client
VALUES (DEFAULT , 'Амелина Рената Иосифовна', 'Одесса', '380531935419', 'город Одесса, улица Асташникская 32 кв.93');
INSERT INTO client
VALUES (DEFAULT , 'Смольникова Агата Алексеевна', 'Одесса', '380418821399', 'город Одесса, улица Марсельская 31');
INSERT INTO client
VALUES (DEFAULT , 'Караева Инга Георгиевна', 'Одесса', '380836010947', 'город Одесса, улица большая Арнаутская 7 кв. 24');
INSERT INTO client
VALUES (DEFAULT , 'Смышляев Вацлав Аполлинариевич', 'Одесса', '380439630844', 'город Одесса, улица малая Арнаутская 74');
INSERT INTO client
VALUES (DEFAULT , 'Якуткин Тарас Матвеевич', 'Одесса', '380518399393', 'город Одесса, улица Болгарская 71 кв. 62');
INSERT INTO client
VALUES (DEFAULT , 'Розенбах Григорий Прокофиевич', 'Одесса', '380661762364', 'город Одесса, улица Деребасовская 3 кв. 95');
INSERT INTO client
VALUES (DEFAULT , 'Труш Платон Евграфович', 'Одесса', '380123909948', 'город Одесса, улица Пантелеймоновская 18/6');
INSERT INTO client
VALUES (DEFAULT , 'Бурцева Агния Ростиславовна', 'Одесса', '380858060690', 'город Одесса, улица Прохоровская 29');
INSERT INTO client
VALUES (DEFAULT , 'Истомин Моисей Назарович', 'Одесса', '380385397683', 'город Одесса, улица Мясоедовская 15б');
INSERT INTO client
VALUES (DEFAULT , 'Ябурова Лариса Семеновна', 'Одесса', '380952418081', 'город Одесса, улица Троицкая 6 ка.38');
INSERT INTO client
VALUES (DEFAULT , 'Севостьянова Арина Захаровна', 'Одесса', '380459900252', 'город Одесса, улица Жуковского 106');
INSERT INTO client
VALUES (DEFAULT , 'Севостьянова Арина Захаровна', 'Одесса', '380971252202', 'город Одесса, улица Пастера 72');
INSERT INTO client
VALUES (DEFAULT , 'Полторак Андриян Касьянович', 'Одесса', '380720993638', 'город Одесса, улица Торговая 22 кв. 63');
INSERT INTO client
VALUES (DEFAULT , 'Сухомлин Даниил Архипович', 'Одесса', '380253961659', 'город Одесса, улица Приморская 82');
INSERT INTO client
VALUES (DEFAULT , 'Лясин Герман Иннокентиевич', 'Одесса', '380835674748', 'город Одесса, улица Успенская 9. кв. 10');
INSERT INTO client
VALUES (DEFAULT , 'Кайпанов Максимильян Леонидович', 'Одесса', '380515771847', 'город Одесса, улица Базарная 73');
INSERT INTO client
VALUES (DEFAULT , 'Ямбушева Ираида Давидовна', 'Одесса', '380201302027', 'город Одесса, улица Затишная 30 кв.30');
INSERT INTO client
VALUES (DEFAULT , 'Хрущёв Даниил Семенович', 'Одесса', '380250118061 ', 'город Одесса, улица Приговорская 15');
INSERT INTO client
VALUES (DEFAULT , 'Антипин Владислав Вадимович', 'Одесса', '380451219486', 'город Одесса, улица Семинарская 83');
INSERT INTO client
VALUES (DEFAULT , 'Балаев Артем Севастьянович', 'Одесса', '380592973301', 'город Одесса, улица Армейская 15/4 кв 42');
INSERT INTO client
VALUES (DEFAULT , 'Греф Поликарп Кондратиевич', 'Одесса', '380962720696', 'город Одесса, улица Зоопарковая 55');
INSERT INTO client
VALUES (DEFAULT , 'Ямковой Юлия Афанасиевна', 'Одесса', '380991937210', 'город Одесса, улица Академическая 18');
INSERT INTO client
VALUES (DEFAULT , 'Головченко Владлена Станиславовна', 'Одесса', '380400355539', 'город Одесса, улица Генуезькая 40 кв.72');
INSERT INTO client
VALUES (DEFAULT , 'Лосевский Феликс Андреевич', 'Одесса', '380907829435', 'город Одесса, улица Тернистая 90/1а');
INSERT INTO client
VALUES (DEFAULT , 'Шаповалов Михей Сократович', 'Одесса', '380409996826', 'город Одесса, улица Гвардейская 32');
INSERT INTO client
VALUES (DEFAULT , 'Поличева Стела Вячеславовна', 'Одесса', '380701488055', 'город Одесса, улица Литературная 82');
INSERT INTO client
VALUES (DEFAULT , 'Ярыгина Агафья Фомевна', 'Одесса', '380377421232', 'город Одесса, улица Репина 30');
INSERT INTO client
VALUES (DEFAULT , 'Лешев Матвей Алексеевич', 'Одесса', '380639266435', 'город Одесса, улица Куприна 23 кв.43');

/*Контракты 1 дилера*/
INSERT INTO contract
VALUES (DEFAULT , 1, 1, '2018-01-01', 'MAZDA', 526, '2017-12-02', 100, '2018-01-02', 150000, 6000, '');
INSERT INTO contract
VALUES (DEFAULT , 2, 1, '2018-02-04', 'MAZDA', 508, '2016-11-22', 345, '2018-02-04', 200000, 15000, '');
INSERT INTO contract
VALUES (DEFAULT , 3, 1, '2017-10-01', 'MAZDA', 672, '2015-07-14', 100, '2017-10-05', 250000, 20000, '');
INSERT INTO contract
VALUES (DEFAULT , 4, 1, '2017-12-20', 'MAZDA', 891, '2017-10-10', 110, '2017-12-21', 175000, 15000, '');
/*Контракты 2 дилера*/
INSERT INTO contract
VALUES (DEFAULT , 5, 2, '2018-11-10', 'HYUNDAI', 132, '2018-11-01', 493, '2018-11-14', 700000, 80000, '');
INSERT INTO contract
VALUES (DEFAULT , 6, 2, '2010-05-11', 'HYUNDAI', 782, '2009-01-23', 500, '2010-05-14', 300000, 20000, '');
INSERT INTO contract
VALUES (DEFAULT , 7, 2, '2017-12-20', 'HYUNDAI', 912, '2016-12-02', 536, '2017-12-23', 450000, 45000, '');
INSERT INTO contract
VALUES (DEFAULT , 8, 2, '2017-04-18', 'HYUNDAI', 392, '2017-03-16', 254, '2016-11-02', 600000, 50000, '');
/*Контракты 3 дилера*/
INSERT INTO contract
VALUES (DEFAULT , 9, 3, '2008-05-02', 'BMW', 113, '2007-01-01', 600, '2008-05-12', 450000, 30000, '');
INSERT INTO contract
VALUES (DEFAULT , 10, 3, '2018-03-03', 'BMW', 345, '2018-01-01', 543, '2018-03-11', 1100000, 100000, '');
INSERT INTO contract
VALUES (DEFAULT , 11, 3, '2017-04-20', 'BMW', 654, '2016-11-01', 932, '2017-04-24', 490000, 34000, '');
INSERT INTO contract
VALUES (DEFAULT , 12, 3, '2017-11-12', 'BMW', 962, '2015-04-01', 1100, '2017-11-17', 780000, 70000, '');
/*Контракты 4 дилера*/
INSERT INTO contract
VALUES (DEFAULT , 13, 4, '2018-07-12', 'WOLKSWAGEN', 912, '2018-07-13', 1100, '2018-06-01', 210000, 15000, '');
INSERT INTO contract
VALUES (DEFAULT , 14, 4, '2018-05-12', 'WOLKSWAGEN', 254, '2015-05-13', 1200, '2017-06-29', 300000, 19000, '');
INSERT INTO contract
VALUES (DEFAULT , 15, 4, '2018-10-12', 'WOLKSWAGEN', 823, '2018-10-14', 943, '2018-01-12', 780000, 43000, '');
INSERT INTO contract
VALUES (DEFAULT , 16, 4, '2018-03-12', 'WOLKSWAGEN', 190, '2018-03-14', 892, '2018-02-20', 780000, 50000, '');
/*Контракты 5 дилера*/
INSERT INTO contract
VALUES (DEFAULT , 17, 5, '2016-02-11', 'RENAULT', 422, '2016-01-01', 365, '2016-02-13', 190000, 30000, '');
INSERT INTO contract
VALUES (DEFAULT , 18, 5, '2016-11-20', 'RENAULT', 672, '2015-12-01', 542, '2016-11-24', 290000, 25000, '');
INSERT INTO contract
VALUES (DEFAULT , 19, 5, '2015-05-01', 'RENAULT', 825, '2015-01-10', 102, '2015-05-08', 550000, 50000, '');
INSERT INTO contract
VALUES (DEFAULT , 20, 5, '2017-07-15', 'RENAULT', 501, '2017-01-29', 321, '2017-07-24', 430000, 41000, '');
/*Контракты 6 дилера*/
INSERT INTO contract
VALUES (DEFAULT , 21, 6, '2015-07-15', 'KIA', 501, '2015-06-22', 382, '2015-07-22', 354000, 24000, '');
INSERT INTO contract
VALUES (DEFAULT , 22, 6, '2018-06-05', 'KIA', 221, '2018-03-29', 454, '2018-06-12', 410000, 41000, '');
INSERT INTO contract
VALUES (DEFAULT , 23, 6, '2017-10-25', 'KIA', 532, '2016-03-10', 333, '2017-11-01', 640000, 64000, '');
INSERT INTO contract
VALUES (DEFAULT , 24, 6, '2016-03-18', 'KIA', 452, '2016-02-01', 205, '2017-03-24', 550000, 55000, '');
/*Контракты 7 дилера*/
INSERT INTO contract
VALUES (DEFAULT , 25, 7, '2016-05-10', 'FIAT', 111, '2016-02-01', 205, '2016-05-23', 320000, 25000, '');
INSERT INTO contract
VALUES (DEFAULT , 26, 7, '2017-06-02', 'FIAT', 145, '2016-05-01', 205, '2017-06-05', 478000, 45000, '');
INSERT INTO contract
VALUES (DEFAULT , 27, 7, '2018-11-20', 'FIAT', 284, '2017-09-01', 205, '2018-11-23', 578000, 54000, '');
INSERT INTO contract
VALUES (DEFAULT , 28, 7, '2018-12-01', 'FIAT', 402, '2018-02-01', 205, '2018-12-01', 400000, 39000, '');
/*Контракты 8 дилера*/
INSERT INTO contract
VALUES (DEFAULT , 29, 8, '2017-03-11', 'JEEP', 325, '2016-03-15', 545, '2018-12-01', 450000, 60000, '');
INSERT INTO contract
VALUES (DEFAULT , 30, 8, '2018-04-03', 'JEEP', 345, '2018-04-05', 265, '2018-12-01', 3600000, 400000, '');
INSERT INTO contract
VALUES (DEFAULT , 31, 8, '2018-05-29', 'JEEP', 432, '2018-05-030', 465, '2018-12-01', 2500000, 230000, '');
INSERT INTO contract
VALUES (DEFAULT , 32, 8, '2015-06-21', 'JEEP', 352, '2015-06-23', 687, '2018-12-01', 1100000, 130000, '');
/*Контракты 9 дилера*/
INSERT INTO contract
VALUES (DEFAULT , 33, 9, '2018-04-14', 'MERSEDES', 124, '2018-04-10', 137, '2018-04-14', 1550000, 200000, '');
INSERT INTO contract
VALUES (DEFAULT , 34, 9, '2018-04-23', 'MERSEDES', 234, '2018-03-01', 143, '2018-04-23', 700000, 100000, '');
INSERT INTO contract
VALUES (DEFAULT , 35, 9, '2017-09-09', 'MERSEDES', 324, '2017-05-20', 192, '2018-09-10', 2300000, 300000, '');
INSERT INTO contract
VALUES (DEFAULT , 36, 9, '2018-01-15', 'MERSEDES', 941, '2018-01-10', 182, '2018-01-16', 550000, 70000, '');
INSERT INTO contract
VALUES (DEFAULT , 36, 7, '2018-01-15', 'MERSEDES', 941, '2018-01-10', 182, '2018-01-16', 550000, 70000, '');

/*Работы Моториста*/
INSERT INTO fix
VALUES (DEFAULT , 'Ремонт ходовой', 'Моторист');
INSERT INTO fix
VALUES (DEFAULT , 'Ремонт двигателя', 'Моторист');
INSERT INTO fix
VALUES (DEFAULT , 'Замена двигателя', 'Моторист');
INSERT INTO fix
VALUES (DEFAULT , 'Установка новых запчастей', 'Моторист');
/*Работы Автоэлектрика*/
INSERT INTO fix
VALUES (DEFAULT , 'Устранения неполадок электрики', 'Автоэлектрик');
INSERT INTO fix
VALUES (DEFAULT , 'Частичная замена электрики', 'Автоэлектрик');
INSERT INTO fix
VALUES (DEFAULT , 'Полная замена электрики', 'Автоэлектрик');
INSERT INTO fix
VALUES (DEFAULT , 'Подключения к автомобилю новых девайсов', 'Автоэлектрик');
/*Работы Маляра*/
INSERT INTO fix
VALUES (DEFAULT , 'Устранения царапин', 'Маляр');
INSERT INTO fix
VALUES (DEFAULT , 'Шпаклевка', 'Маляр');
INSERT INTO fix
VALUES (DEFAULT , 'Устранения корозии', 'Маляр');
INSERT INTO fix
VALUES (DEFAULT , 'Покраска', 'Маляр');
INSERT INTO fix
VALUES (DEFAULT , 'Полная обработка кузова', 'Маляр');
/*Работы Вулканизаторщика*/
INSERT INTO fix
VALUES (DEFAULT , 'Установка камер', 'Вулканизаторщик');
INSERT INTO fix
VALUES (DEFAULT , 'Настройка камер', 'Вулканизаторщик');
INSERT INTO fix
VALUES (DEFAULT , 'Починка камер', 'Вулканизаторщик');
/*Работы Автожестянщика*/
INSERT INTO fix
VALUES (DEFAULT , 'Рихтовка кузова', 'Автожестянщик');
INSERT INTO fix
VALUES (DEFAULT , 'Исправления вмятин', 'Автожестянщик');
INSERT INTO fix
VALUES (DEFAULT , 'Полная обработка кузова', 'Автожестянщик');
/*Работы Механика-диагноста*/
INSERT INTO fix
VALUES (DEFAULT , 'Диагностика автомобиля', 'Механик-диагност');
INSERT INTO fix
VALUES (DEFAULT , 'Диагностика двигателя', 'Механик-диагност');
INSERT INTO fix
VALUES (DEFAULT , 'Диагностика ходовой', 'Механик-диагност');
INSERT INTO fix
VALUES (DEFAULT , 'Диагностика электрики', 'Механик-диагност');



INSERT INTO repair
VALUES (DEFAULT , 1, 14, 24,'2018-11-20', 2, 38230);
INSERT INTO repair
VALUES (DEFAULT , 12, 26, 32,'2018-11-20', 20, 18655);
INSERT INTO repair
VALUES (DEFAULT , 14, 2, 18,'2017-11-20', 20, 11350);
INSERT INTO repair
VALUES (DEFAULT , 20, 31, 27,'2018-11-20', 11, 37710);
INSERT INTO repair
VALUES (DEFAULT , 6, 16, 3,'2018-11-20', 11, 25288);
INSERT INTO repair
VALUES (DEFAULT , 21, 30, 15,'2018-11-20', 20, 22208);
INSERT INTO repair
VALUES (DEFAULT , 2, 15, 1,'2017-11-20', 9, 15892);
INSERT INTO repair
VALUES (DEFAULT , 4, 11, 25,'2018-11-20', 8, 32722);
INSERT INTO repair
VALUES (DEFAULT , 11, 28, 16,'2018-11-20', 10, 37909);
INSERT INTO repair
VALUES (DEFAULT , 9, 10, 23,'2018-11-20', 18, 19248);
INSERT INTO repair
VALUES (DEFAULT , 18, 23, 10,'2018-11-20', 3, 18856);
INSERT INTO repair
VALUES (DEFAULT , 17, 23, 40,'2018-11-20', 9, 10018);
INSERT INTO repair
VALUES (DEFAULT , 3, 17, 34,'2017-11-20', 8, 25377);
INSERT INTO repair
VALUES (DEFAULT , 23, 35, 13,'2018-12-01', 11, 14097);
INSERT INTO repair
VALUES (DEFAULT , 14, 24, 41,'2018-11-20', 24, 11924);
INSERT INTO repair
VALUES (DEFAULT , 4, 11, 9,'2018-11-20', 1, 20863);
INSERT INTO repair
VALUES (DEFAULT , 13, 28, 33,'2018-11-20', 50, 32775);
INSERT INTO repair
VALUES (DEFAULT , 8, 24, 12,'2018-11-20', 20, 18768);
INSERT INTO repair
VALUES (DEFAULT , 16, 36, 41,'2017-11-20', 2, 23196);
INSERT INTO repair
VALUES (DEFAULT , 7, 19, 11,'2018-11-20', 38, 25654);
INSERT INTO repair
VALUES (DEFAULT , 19, 15, 39,'2018-11-20', 45, 21934);

/*Полная информация о дилерах*/
CREATE VIEW FullContractInformation AS
SELECT contract.id ContractId, dealer.fullName Dealer, contract.dateContract, carBrand CarBrand, client.fullName NameClient,
  client.cityName City,  client.address, client.phone, contract.dateSale, contract.priceSale, contract.notes
FROM contract
RIGHT OUTER JOIN dealer ON contract.idDealer = dealer.id
RIGHT OUTER JOIN client ON contract.idClient = client.id;
/*SELECT * FROM FullContractInformation;*/



/*Сортировка клиентов*/
CREATE VIEW sorting AS
SELECT * FROM client
  ORDER BY client.fullName;
/*SELECT *FROM sorting;*/

/*Вывод суммы всех продаж и коммисий*/
CREATE VIEW total_priceSale_and_commission AS
SELECT SUM(priceSale) TotalPriceSale, SUM(commission) TotalCommission From contract;
/*SELECT * FROM total_priceSale_and_commission;*/


/*Зароботок фирмы за все контракты*/
CREATE FUNCTION sumSale(name text) RETURNS float AS
$$
DECLARE
    sum float;

    BEGIN
    sum := (SELECT SUM(priceSale) AS q FROM contract WHERE name=(SELECT fullName FROM dealer WHERE dealer.id= contract.idDealer));

    return sum;
  end;
$$ LANGUAGE plpgsql;
/*SELECT * FROM sumSale('Palisade');*/

/*Вывод контрактов которые младше определенной даты*/
/*SELECT id, idDealer, idClient, notes FROM contract WHERE dateSale<'2017-08-01';*/

/*Вывод клиентов которые находятся на ремонте*/
/*SELECT * FROM client WHERE id IN (SELECT idClient FROM contract WHERE id IN (SELECT idContract FROM repair));*/


/*сколько всего заказов в дилера
SELECT COUNT(*) FROM contract WHERE idDealer = 1;*/

/*Вывод ифнормации о деятельности дилере*/
/*SELECT * FROM contract WHERE idDealer = 4;*/

/*Вывод суммы всех зарплат мастеров и их кол-во*/
/*SELECT SUM(salary) AS Sum_Salary, COUNT(*) AS Quantity FROM master;*/

/*Вывод средней зарплаты мастеров и иx количество*/
/*SELECT AVG(salary) AS average_salary, COUNT(*) AS Quantity FROM master;*/

/*Выводит 5 самых больших заказов
SELECT contract.idClient, client.fullName, contract.idDealer, dealer.fullName, contract.dateContract, contract.carBrand, contract.priceSale
FROM contract, client, dealer
WHERE contract.idDealer = dealer.id AND contract.idClient=client.id
ORDER BY contract.priceSale DESC
LIMIT 5;*/

/*Выводит мастеров у которых зарплата в диапазоне до 10 000 до 15 000*/
/*SELECT * FROM master WHERE salary BETWEEN 10000 AND 15000;*/

/*Вывод мастеров у которых зарплата больше средней*/
SELECT * FROM master WHERE salary > (SELECT AVG(salary) FROM master);

/*Вывод имен клиентов у которых больше 1 заказа*/
SELECT fullName FROM client WHERE 1<(SELECT COUNT(idClient) FROM contract WHERE idClient = client.id);

/*Подсчет количества заключенных контрактов всех клиентов*/
CREATE FUNCTION kolv(N INT) RETURNS int AS
$$
DECLARE
q INT;
BEGIN
  q:=(SELECT COUNT(*) FROM contract WHERE idClient=N);
  RETURN q;
END;
$$LANGUAGE plpgsql;
SELECT idClient, kolv(idClient) FROM contract  GROUP BY idClient HAVING COUNT(*) >=1 ORDER BY idClient ;

/*Подсчет количества заключенных контрактов диллера*/
CREATE FUNCTION kolvD(N INT) RETURNS int AS
$$
DECLARE
q INT;
BEGIN
  q:=(SELECT COUNT(*) FROM contract WHERE idDealer=N);
  RETURN q;
END;
$$LANGUAGE plpgsql;
/*SELECT idDealer, kolvD(idDealer) FROM contract GROUP BY idDealer HAVING COUNT(*) >=1 ORDER BY idDealer ;*/

/*Обеспечения целотности данных*/
CREATE FUNCTION checkId() RETURNS trigger AS
$$
DECLARE
rec record;
BEGIN
  SELECT into rec*
  FROM dealer WHERE id=new.idDealer;
  IF NULL
    THEN
    ELSE
    RETURN new;
  END IF;
END;
$$LANGUAGE plpgsql;

CREATE TRIGGER control
BEFORE INSERT
ON contract
FOR EACH ROW
EXECUTE PROCEDURE checkId();


/*Тригер контроля добавления*/
CREATE OR REPLACE FUNCTION add_dealer()
RETURNS TRIGGER
AS $$ BEGIN
  PERFORM id FROM dealer
  WHERE id=new.id;
  IF FOUND THEN
    RAISE EXCEPTION
    'Error!';
END IF;
RETURN new;
END;$$
LANGUAGE 'plpgsql';

CREATE TRIGGER add_dealer
BEFORE INSERT OR UPDATE ON dealer
FOR EACH ROW
EXECUTE PROCEDURE add_dealer();

/*Проверка уникальности нового контракта*/
CREATE OR REPLACE FUNCTION add_contract()
RETURNS TRIGGER
AS $$ BEGIN
  PERFORM id FROM contract
  WHERE id=new.id;
  IF FOUND THEN
    RAISE EXCEPTION
    'Error new id exist!';
  END IF;
  RETURN new;
END;$$
LANGUAGE 'plpgsql';

CREATE TRIGGER add_contract
BEFORE INSERT OR UPDATE ON contract
FOR EACH ROW
EXECUTE PROCEDURE add_contract();

/*Проверка уникальности нового дилера*/
CREATE OR REPLACE FUNCTION add_dealer()
RETURNS TRIGGER
AS $$ BEGIN
  PERFORM id FROM dealer
  WHERE id=new.id;
  IF FOUND THEN
    RAISE EXCEPTION
    'Error new id exist!';
  END IF;
  RETURN new;
END;$$
LANGUAGE 'plpgsql';

CREATE TRIGGER add_dealer
BEFORE INSERT OR UPDATE ON dealer
FOR EACH ROW
EXECUTE PROCEDURE add_dealer();

/*Проверка уникальности нового клиента*/
CREATE OR REPLACE FUNCTION add_client()
RETURNS TRIGGER
AS $$ BEGIN
  PERFORM id FROM client
  WHERE id=new.id;
  IF FOUND THEN
    RAISE EXCEPTION
    'Error new id exist!';
  END IF;
  RETURN new;
END;$$
LANGUAGE 'plpgsql';

CREATE TRIGGER add_client
BEFORE INSERT OR UPDATE ON client
FOR EACH ROW
EXECUTE PROCEDURE add_client();


/*Владелец*/
CREATE ROLE ownserver WITH PASSWORD 'AFr$gX9@Bf43*7#V12=LoR-S';
GRANT ALL ON ALL TABLES IN SCHEMA public TO ownserver WITH GRANT OPTION;

/*Администратор*/
CREATE ROLE administrator WITH PASSWORD 'KLA(SD898%^J@h)s7KL-S,M';
GRANT SELECT, UPDATE, INSERT, DELETE ON ALL TABLES IN SCHEMA public TO administrator;

/*Работник*/
CREATE ROLE employee WITH PASSWORD 'JlKIl193$*&@0QmwD)';
GRANT SELECT, INSERT, UPDATE ON contract, dealer, client TO employe;

/*Клиент*/
CREATE ROLE client WITH PASSWORD 'IUA)S$D5b56DNA2';
GRANT SELECT ON dealer TO client;