/* 
Описание базы данных hospital
База данных hospital предназначена для ведения учета медицинского персонала, пациентов и хода лечения.
Используемая СУБД – MySQL,  так как она содержит множество инструментов для управления базами данных, поддерживает стандартный язык SQL, масштабируема, обеспечивает 
хорошую производительность. Для работы с СУБД использовались программы DBeaver и Workbench. 
При проектировании использовался смешанный подход: были выделены атрибуты, из которых были сформированы сущности (восходящий подход); 
также были выявлены сущности и связи между ними, затем сущности были дополнены атрибутами (нисходящий подход).
База данных нормализована до четвертой нормальной формы. Атрибуты являются атомарными, отсутствуют дубликаты в данных (первая нормальная форма); 
добавлены первичные ключи (вторая нормальная форма); столбцы зависят от первичного ключа, устранены транзитивные зависимости в данных (третья нормальная форма); 
в таблицах есть суперключ, который однозначно идентифицирует каждую строку (нормальная форма Бойса-Кодда); устранены многозначные зависимости - 
выстроены корректные отношения многие-ко-многим (четвертая нормальная форма). 
В базе данных используются следующие бизнес-правила: 
- для каждого человека, отделения, фото, медицинской карты, справки, больничного листа, процедуры должен существовать уникальный идентификационный номер;
- для каждого пациента должна существовать медицинская карта;
- пациент не может идти на прием задним числом, справки и больничные листы не могут быть оформлены задним числом;
- один медицинский работник может числиться в одном отделении только один раз;
- один врач может работать только в одном отделении; один сотрудник, относящийся к младшему или среднему медицинскому персоналу, может совмещать работу в более чем одном отделении.
Данные для базы данных были сгенерированы при помощи сервиса filldb.info. 
База данных решает следующие задачи:
- хранение информации о структуре медицинского учреждения; 
- хранение персональной информации о пациентах и медицинском персонале;
- хранение информации о приемах, медицинских справках, больничных листах. 
Примеры запросов, которые могут быть выполнены к базе данных:
- список пациентов и медицинского персонала;
- получение информации о пациентах: сведения о приемах, процедурах и обследованиях, медицинских справках и больничных листах; медицинский персонал, 
участвующий в наблюдении пациента и отделение наблюдения; персональные данные;
- получение информации о медицинском персонале, включая докторов, средний и младший медицинский персонал: отделение работы, в том числе работа по 
совместительству; приемы, справки и больничные листы, оформленные персоналом; фото персонала; расписание работы; персональные данные;
- изменение, удаление и добавление информации о пациентах, врачах, среднем и младшем медицинском персонале, приемах, медицинских справках, больничных листах, 
отделениях, процедурах и обследованиях. 
База данных состоит из 15 таблиц, для установления связей каждому объекту назначен ключ:
- departments (отделения) – содержит информацию о названии отделения, место расположения, сведения о руководителе, расписание работы, контактные данные; 
- hospital_structure (структура медицинского учреждения) - содержит информацию о названии медицинского учреждения, адресе, главном враче, отделениях; поле departments_id 
связывает таблицу с  departments(id), образуя связь один ко многим;
- personal_data (персональные данные) – хранит информацию о персональных данных пациентов и медицинского персонала: ФИО, дату рождения, пол, контактные данные; 
таблица содержит индекс lastname_index для быстрого поиска по фамилии;
- personal_data_log (логирование изменений таблицы personal_data) – хранит данные пользователей, вносивших изменения в таблицу персональных данных; поле personal_data_id 
связывает таблицу с personal_data(id), образуя связь один ко многим; 
- doctors (высший медицинский персонал) – содержит табельный номер, отделение, должность и расписание работы высшего медицинского персонала; поле personal_data_id связывает 
таблицу с personal_data(id), образуя связь один к одному; поле department_id связывает таблицу с departments(id), образуя связь один к одному; 
- medical_staffs (средний и младший медицинский персонал) - содержит табельный номер, отделение, должность и расписание работы среднего и младшего медицинского персонала; 
поле personal_data_id связывает таблицу с personal_data(id), образуя связь один к одному; поле department_id связывает таблицу с departments(id), образуя связь один к одному; 
- departments_medical_staffs (отделения работы среднего и младшего медицинского персонала при совместительстве) – содержит отделения и идентификационные номера сотрудников, 
работающих по совместительству; поле department_id связывает таблицу с departments(id) и поле medical_staff_id связывает таблицу с medical_staffs(personal_data_id), образуя 
связь многие ко многим; 
- photos (фото) – хранит фото медицинского персонала;
- media (фото медицинского персонала) – хранит подписи и ссылки на фото медицинского персонала; поле photo_id связывает таблицу с photos(id), образуя свявзь один к одному; 
поле personal_data_id связывает таблицу с personal_data(id), образуя связь один к одному;
- patients (пациенты) – содержит сведения о пациенте: номер полиса, место работы, дату последнего посещения и полезную информацию; поле personal_data_id связывает таблицу 
с personal_data(id), образуя связь один к одному;
- medical_cards (медицинская карта) – содержит номер и дату открытия медицинской карты; поле personal_data_id связывает таблицу с personal_data(id), образуя связь один к одному;
- receptions (прием врача) – хранит информацию о медицинской карте пациента, дате посещения, медицинском персонале на приеме, жалобах, диагнозе, назначениях и рекомендациях; 
поле medical_card_id связывает таблицу с medical_cards(id), образуя связь один к одному; поле department_id связывает таблицу с departments(id), образуя связь один к одному; 
поле doctor_id связывает таблицу с doctors(personal_data_id) , образуя связь один к одному; поле medical_staff_id связывает таблицу с medical_staffs(personal_data_id), 
образуя связь один к одному;
- procedures_and_examinations (процедуры и обследования) – содержит описание медицинской манипуляции, информацию о дате и медицинском персонале; поле medical_card_id 
связывает таблицу с medical_cards(id), образуя связь один к одному; поле reception_id связывает таблицу с receptions(id), образуя связь один к одному; поле doctor_id связывает 
таблицу с doctors(personal_data_id) , образуя связь один к одному; поле medical_staff_id связывает таблицу с medical_staffs(personal_data_id), образуя связь один к одному;
- sick_leaves (больничный лист) – хранит номер, дату, период действия, идентификаторы врача и пациента, статус больничного листа; поле name_of_the_hospital_id связывает таблицу 
с  hospital_structure(name_of_the_hospital), образуя связь один к одному; поле patient_id связывает таблицу с  personal_data(id), образуя связь один к одному; поле doctor_id 
связывает таблицу с doctors(personal_data_id) , образуя связь один к одному;
- medical_certificates (медицинская справка) - хранит номер, дату, период действия, идентификаторы врача и пациента; поле name_of_the_hospital_id связывает таблицу с  
hospital_structure(name_of_the_hospital), образуя связь один к одному; поле patient_id связывает таблицу с  personal_data(id), образуя связь один к одному; поле doctor_id 
связывает таблицу с doctors(personal_data_id) , образуя связь один к одному.  
Виды отношений между сущностями:
- один к одному:
	media(photo_id) REFERENCES photos(id)
	doctors(personal_data_id) REFERENCES personal_data(id)
	doctors(department_id) REFERENCES departments(id)
	doctors(photo_id) REFERENCES media(id)
	medical_staffs(personal_data_id) REFERENCES personal_data(id)
	medical_staffs(department_id) REFERENCES departments(id)
	medical_staffs(photo_id) REFERENCES media(id)
	departments_medical_staffs(department_id) REFERENCES departments(id)
	departments_medical_staffs(medical_staff_id) REFERENCES medical_staffs(personal_data_id)
	patients(personal_data_id) REFERENCES personal_data(id)
	medical_cards(patient_id) REFERENCES patients(personal_data_id)
	medical_cards(patient_id) REFERENCES patients(flp_idx)
	receptions(medical_card_id) REFERENCES medical_cards(id)
	receptions(department_id) REFERENCES departments(id)
	receptions(doctor_id) REFERENCES doctors(personal_data_id)
	receptions(medical_staff_id) REFERENCES medical_staffs(personal_data_id)
	procedures_and_examinations(medical_card_id) REFERENCES medical_cards(id)
	procedures_and_examinations(reception_id) REFERENCES receptions(id)
	procedures_and_examinations(doctor_id) REFERENCES doctors(personal_data_id)
	procedures_and_examinations(medical_staff_id) REFERENCES medical_staffs(personal_data_id)
	sick_leaves(name_of_the_hospital_id) REFERENCES hospital_structure(name_of_the_hospital)
	sick_leaves(patient_id) REFERENCES personal_data(flp_idx)
	sick_leaves(patient_id) REFERENCES personal_data(date_of_birth)
	sick_leaves(doctor_id) REFERENCES personal_data(flp_idx)
	medical_certificates(name_of_the_hospital_id) REFERENCES hospital_structure(name_of_the_hospital)
	medical_certificates(patient_id) REFERENCES patients(personal_data_id)
	medical_certificates(doctor_id) REFERENCES doctors(personal_data_id)
	medical_certificates(medical_certificate_number_id) REFERENCES receptions(medical_certificate_number)
- один ко многим
	hospital_structure(departments_id) REFERENCES departments(id)
	personal_data_log(personal_id) REFERENCES personal_data(id)
- многие ко многим
hospital_structure(departments_id) departments(id)
Для получения, добавления, изменения и удаления данных были использованы однотабличные запросы, вложенные запросы, операции множеств, JOIN – соединения, динамические запросы. 
В запросах применялись предопределенные функции (агрегирующие, группирующие, сортирующие, календарные и т.д.).  
Дополнительно было создано представление, при помощи которого можно ограничивать доступ ряда пользователей к персональным данным – представление хранит контактные данные в 
зашифрованном виде. 
Были созданы транзакции, например,  для отслеживания изменений таблицы с персональными данными – это позволит вносить в дополнительную таблицу сведения о пользователях, 
которые добавляли, изменяли или удаляли данные о ком-либо.  
Для возможности многократного использования последовательности SQL-операторов были созданы функции и процедуры, позволяющие быстро и просто получать информацию о пациентах, 
приемах, справках и больничных листах.  
 */

DROP DATABASE IF EXISTS hospital;
CREATE DATABASE hospital;
USE hospital;

CREATE TABLE `departments` (
	id SERIAL PRIMARY KEY,-- SERIAL = BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE
	name_of_department VARCHAR(255) NOT NULL UNIQUE COMMENT 'Наименование отделения',
	location VARCHAR(255) NOT NULL COMMENT 'Место расположения',
	head_of_department VARCHAR(255) NOT NULL UNIQUE COMMENT 'Руководитель отделения',
	phone BIGINT UNSIGNED NOT NULL UNIQUE,
	email VARCHAR(120) UNIQUE,
	timetable_of_work VARCHAR(255) COMMENT 'Расписание работы'
)  COMMENT = 'Отделения';
INSERT INTO `departments` VALUES
(1,'Терапевтическое отделение','1 этаж','Иванов',926535,'qwest@example.com','9.00-18.00'),
(2,'Неврологическое отделение','2 этаж','Петров',254368,'bashirian.adrien@example.org','9.00-18.00'),
(3,'Кардиологическое отделение','3 этаж','Сергеев',125345,'christophe.block@example.org','9.00-18.00'),
(4,'Гинекологическое отделение','2 этаж','Андреева',302543,'smitham.juanita@example.com','9.00-18.00'),
(5,'Хирургический корпус','3 этаж','Александрова',573274,'igreenholt@example.org','9.00-18.00'),
(6,'Травматологическое отделение','1 этаж','Дмитриева',850152,'lhintz@example.com','9.00-18.00');

CREATE TABLE `hospital_structure` (
	name_of_the_hospital VARCHAR(255) NOT NULL UNIQUE COMMENT 'Наименование медицинского учреждения',
	address VARCHAR(255) NOT NULL UNIQUE COMMENT 'Адрес медицинского учреждения',
	chief_doctor VARCHAR(255) NOT NULL UNIQUE COMMENT 'Главный врач',
	departments_id BIGINT UNSIGNED NOT NULL UNIQUE COMMENT 'Отделения',
	phone BIGINT UNSIGNED NOT NULL UNIQUE,
	email VARCHAR(120) UNIQUE,
	timetable_of_work VARCHAR(255) COMMENT 'Расписание работы',
	FOREIGN KEY (departments_id) REFERENCES departments(id) ON UPDATE CASCADE ON DELETE CASCADE
)  COMMENT = 'Структура медицинского учреждения';
INSERT INTO `hospital_structure` VALUES
('Центральная городская больница','Ленина, 1','Тимофеев','6',125345,'bolnitsa@example.com','9.00-18.00');

CREATE TABLE `personal_data`(
	id SERIAL PRIMARY KEY,
	firsname VARCHAR(255) NOT NULL COMMENT 'Имя',
	lastname VARCHAR(255) NOT NULL COMMENT 'Фамилия',
	patronymic VARCHAR(255) COMMENT 'Отчество',
	date_of_birth DATE NOT NULL COMMENT 'Дата рождения',
	gender CHAR(1) NOT NULL CHECK (Gender in('m','f')) COMMENT 'Пол',
	address VARCHAR(255) COMMENT 'Адрес',
	phone BIGINT UNSIGNED NOT NULL UNIQUE,
	email VARCHAR(120) UNIQUE,
	additional_contact VARCHAR(120) COMMENT 'Дополнительный контакт'
--	INDEX lastname_firsname_idx(lastname, firsname), 
) COMMENT = 'Персональные данные';
CREATE INDEX lastname_index ON `personal_data` (lastname);-- индекс для быстрого поиска по фамилии 
INSERT INTO `personal_data` VALUES
(86,'repudiandae','McClure','aut','2021-12-28','m','335 Brendon Glen Suite 004\nBroderickborough, IA 22448',206191,'mitchel.leuschke@example.com','581.550.3651x593'),
(87,'et','Barrows','id','1972-06-21','f','654 Edna Plains Apt. 380\nEast Faefurt, LA 64233-2137',457544,'pollich.dangelo@example.org','1-106-902-9368x41645'),
(88,'eum','Hammes','sunt','1993-11-18','m','555 Gordon Roads Suite 277\nKosstown, NH 49668',71,'utreutel@example.org','1-122-512-3941x45301'),
(89,'ut','Mante','sunt','1972-02-23','f','787 Schneider Terrace\nAmberhaven, LA 16113',0,'yundt.aaron@example.org','875.360.1708'),
(90,'veniam','Cummerata','accusantium','1980-03-13','f','62506 Rickey Manor\nTyrelhaven, DC 94856-6169',1,'maci.huels@example.net','1-804-335-4030x325'),
(91,'itaque','Erdman','suscipit','2003-10-29','m','069 Mya Street\nEast Casandra, MS 74253-4438',468637,'annie89@example.com','(462)021-7952x837'),
(93,'dolorum','Crist','nulla','2023-07-11','f','77739 Oma Fields Suite 289\nWest Don, KY 31902',27,'kertzmann.naomi@example.com','1-260-874-3616x76666'),
(94,'alias','Cassin','et','2011-12-21','m','360 Roderick Tunnel Suite 780\nNew Felipaside, KY 43978-4266',662,'ireynolds@example.org','(355)996-6513x393'),
(95,'illum','Carroll','eos','1974-04-29','m','21524 Ahmed Ridge Suite 438\nBaileychester, NE 07270-7112',855962,'turcotte.juanita@example.com','587.638.9112'),
(96,'modi','Von','sequi','1999-01-03','m','8193 Leta Shores\nNienowbury, DE 01336-5547',94,'dewayne33@example.org','04961487280'),
(97,'saepe','Rohan','laudantium','1983-05-25','f','5578 Kristofer Center Suite 659\nWest Betty, OH 53003',83920,'rroob@example.com','1-973-087-6664'),
(98,'sed','Bergnaum','et','2014-08-23','m','05525 Hettie Wells Suite 189\nDomenickbury, PA 86683-1482',40880,'aufderhar.mable@example.net','405-269-9689x6380'),
(99,'nemo','Mueller','maiores','2023-05-01','m','89244 Kessler Freeway Suite 247\nSouth Roseland, NV 50854-3962',42548,'ali93@example.net','459.979.8225x29969'),
(100,'qui','Shields','distinctio','1976-01-28','f','74197 Millie Forge Suite 682\nSouth Jerrodview, NC 87889',719,'amacejkovic@example.org','815.250.6393x97338'),
(101,'eaque','Botsford','saepe','2007-02-05','f','1831 Bruen Circles Apt. 164\nMcClureberg, DC 55924',34,'marianna97@example.com','+62(0)0763346540'),
(102,'ratione','Mosciski','minus','1971-07-03','m','82988 Cyril Track\nNorth Leonardoborough, MI 76146',272,'dubuque.corbin@example.org','979-032-0362x4313'),
(103,'eaque','Wolf','rerum','2021-07-30','f','832 Schuster Court\nHerminiatown, IA 00423',615661,'idickinson@example.org','1-004-786-2240'),
(104,'tempore','Kilback','consectetur','2010-09-14','f','6852 Olson Ridge Apt. 171\nPort Emersonland, DC 78788',6839824253,'crystel.gutmann@example.com','(149)243-1232x349'),
(108,'animi','Friesen','consequuntur','1972-07-10','f','3311 Marcus Skyway Apt. 274\nNew Benedict, DC 69644',982161,'reta60@example.org','(397)147-2029x164'),
(109,'sit','Cummerata','officiis','2022-02-22','m','9345 Olen Spurs\nTrentshire, NH 51879',297302,'tkoelpin@example.com','847-890-1285x55930'),
(110,'fuga','Wuckert','et','1989-12-03','f','7223 Dashawn Island\nWest Gailstad, ND 81946',902730,'hamill.delpha@example.com','05120773486'),
(111,'vitae','Blanda','sed','1983-08-13','m','24561 Braun Way\nWest Otha, AZ 72584-8202',180734,'eherzog@example.com','712-960-3606'),
(113,'qui','Labadie','ipsam','1978-02-05','f','7611 Bergnaum Burg\nNorth Lonfurt, CT 14666',449799,'fae.wolf@example.com','039-614-8451x349'),
(115,'ipsa','Bernier','nihil','1983-06-23','m','0271 Darrion Lights\nAltheafurt, AL 89636',805,'taryn.ryan@example.com','1-288-243-8948'),
(117,'corrupti','Dickinson','in','1996-11-11','m','019 Graham Crescent\nBashirianport, MI 74808',836135,'vincent43@example.org','437.171.1819x154'),
(118,'nam','Schowalter','neque','1982-03-09','m','1915 Block Ramp\nWest Clairburgh, SC 83855',719617,'gdickens@example.net','1-133-577-4316'),
(120,'totam','Treutel','iure','1980-07-11','f','345 Mia Views Apt. 358\nIsabelfurt, MS 53929-8169',565125,'nspinka@example.net','459.995.0722'),
(122,'omnis','Tillman','accusamus','2007-12-05','m','358 Bechtelar Island\nPort Alenaland, MT 45697-7029',368178,'gideon86@example.org','(921)049-5461x0317'),
(123,'ducimus','Kunde','dolorum','2020-03-25','f','4429 Vena Cove Apt. 960\nRosalindmouth, OK 63503-9896',68035,'wisoky.rosella@example.net','608.673.2721x85314'),
(125,'nisi','Kozey','beatae','2002-09-24','f','95053 Hettinger Station Suite 289\nNew Imelda, MO 63037',891338,'qspencer@example.org','424-941-0983x682'),
(126,'voluptates','O\'Reilly','sed','1989-12-01','f','973 Herman Parkways\nSouth Pearliemouth, DE 35358-2475',655261,'tthiel@example.org','1-954-946-4263x48135'),
(127,'est','Lubowitz','officiis','2003-12-09','m','563 Mayert Fort\nLake Halle, TX 00065',997912,'lura.durgan@example.net','835-273-6163x7969'),
(128,'minus','Muller','cumque','1970-05-29','m','8436 Toni Pines\nPort Hubert, SC 92268-5192',73,'edythe52@example.org','794-729-4795'),
(129,'itaque','Murazik','omnis','1989-02-22','m','645 Jarret Via\nSaraiside, AR 30650',519470,'joana.moore@example.com','485-388-6856'),
(130,'sint','Gleason','voluptatibus','2003-04-18','f','8153 Kreiger Valley Suite 112\nNorth Sebastianmouth, KY 17578',1225690115,'tmarks@example.org','(785)525-9484x4086'),
(132,'et','Reichel','placeat','1979-04-28','m','92090 Breana Isle\nVitoside, AR 70911',534,'schuster.jordyn@example.com','1-407-396-1734x50733'),
(135,'odio','Medhurst','dolor','2013-08-08','f','62684 Berenice Roads Apt. 610\nBrooklynton, SD 10090-4247',9610801388,'zgreenfelder@example.org','882.249.8076x988'),
(136,'eaque','Cummings','eos','1984-07-26','f','4277 Dale Wells Suite 904\nCandidahaven, TN 12639-4000',955,'zkautzer@example.org','1-641-564-5950'),
(138,'cum','Fritsch','dolores','2012-04-06','f','17514 Hackett Shoal Suite 341\nBrownborough, AR 04623-5906',737,'kreiger.darlene@example.com','1-367-469-3441x65547'),
(139,'occaecati','Bayer','qui','1989-03-09','m','907 Romaguera Village\nElsefort, IN 38567',227023,'bernadine.schneider@example.com','013.594.2761x62647'),
(140,'reprehenderit','Sporer','et','2022-12-12','f','5295 Padberg Knoll\nJohnsbury, LA 15140',644,'olson.linwood@example.org','678-402-4009x82358'),
(141,'ducimus','Herman','ut','1998-07-05','f','0856 Rebekah Lights\nSouth Sydnee, OH 69950-0208',899,'kerluke.diego@example.com','793.887.0406x8033'),
(142,'et','Monahan','eum','1970-08-14','f','537 Colten Rest Suite 969\nNitzscheville, VT 12686',45,'reymundo.zulauf@example.com','1-607-273-4646x5930'),
(143,'ullam','Hickle','recusandae','1989-05-12','f','59371 Kassulke Motorway\nNicolaschester, TX 16079-2527',111,'mrodriguez@example.org','08486382246'),
(146,'laboriosam','Wolff','perspiciatis','2012-06-25','f','0130 Mayer Overpass\nSouth Chadrickstad, MT 84289-6331',599,'blick.sonny@example.com','1-529-855-5673'),
(147,'culpa','Rutherford','est','2002-12-21','f','9650 Jacobs Junctions\nLeannaland, WY 87183',495,'nwisoky@example.org','1-669-313-5582x96920'),
(150,'voluptatem','Kulas','dolores','1972-02-14','f','679 Lempi Spur Suite 971\nDemariomouth, MS 16106',98,'bethany.stark@example.net','520.170.2186x74357'),
(151,'maiores','Kuhlman','et','1972-10-10','f','816 Harris Ways\nPacochaborough, PA 05621-1658',928,'eusebio95@example.net','155.645.5265x60450'),
(152,'ut','Johnston','vel','1985-09-14','m','005 Anita Drives Suite 432\nLednershire, MS 60414',141289,'prosacco.claude@example.com','(413)200-3178x006'),
(153,'perspiciatis','Satterfield','veritatis','2013-05-01','m','64274 Windler Inlet Suite 316\nWestmouth, CT 33225-5133',296,'koepp.dena@example.com','02648626176'),
(155,'amet','Haley','temporibus','2004-08-25','m','7935 Kutch Hill\nLake Titus, KY 35914',80108,'mmills@example.com','1-925-809-6427x9496'),
(156,'et','Dietrich','nostrum','1999-07-10','f','822 Medhurst Lake Apt. 755\nNew Kyra, MI 37603-7683',92,'becker.tabitha@example.com','888.387.7521'),
(160,'et','Beier','quia','2005-05-25','m','691 Wolff Neck Suite 367\nLenoraburgh, IA 33844-3360',944,'rpurdy@example.org','+46(6)2318632935'),
(162,'exercitationem','Lemke','consequatur','1989-11-03','f','1887 Clifford Mount\nBeattymouth, CT 10771-6347',755,'vbreitenberg@example.net','330-845-5289x845'),
(163,'possimus','Cruickshank','itaque','2015-09-30','f','3279 Jaime Run\nNorth Humberto, CO 18002-8373',565,'jpurdy@example.org','+70(4)9842021232'),
(164,'rerum','Walter','aut','1985-03-08','f','37762 Margot Grove Apt. 208\nZachariahhaven, SD 62239',851,'nnolan@example.org','(073)769-4974'),
(165,'modi','Waters','nam','1993-11-16','f','18321 Stacy Ridges Suite 550\nRosannamouth, MT 73395-9297',256769262,'ratke.billy@example.net','(192)716-8047x02759'),
(166,'et','Bailey','explicabo','1977-11-09','f','7753 Chelsea Garden\nMauriceside, IL 95996',17,'acormier@example.net','(319)886-4825x510'),
(169,'in','Mills','sed','1987-11-10','m','44708 Tyra Points\nLlewellynshire, FL 98024-6461',74,'camryn83@example.net','448-569-8335x3844');

CREATE TABLE `personal_data_log` ( 
    id SERIAL PRIMARY KEY,
    personal_data_id BIGINT UNSIGNED NOT NULL,
    user VARCHAR( 20 ) NOT NULL,
    action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
    action VARCHAR(50)
) COMMENT = 'Логирование изменений таблицы `personal_data`';
ALTER TABLE personal_data_log ADD FOREIGN KEY (personal_data_id) REFERENCES personal_data(id) ON UPDATE NO ACTION ON DELETE NO ACTION;
-- триггер для логирования при вставке данных в таблицу personal_data
DROP TRIGGER IF EXISTS `after_personal_data_insert`;
CREATE TRIGGER `after_personal_data_insert` 
AFTER INSERT ON `personal_data`
FOR EACH ROW
BEGIN
   INSERT INTO `personal_data_log` (personal_data_id, user, action)
   VALUES (NEW.id, USER(), 'Inserted');
END;
-- INSERT INTO personal_data VALUES (170,'in','Mills','sed','1987-11-10','m','44708 Tyra Points\nLlewellynshire, FL 98024-6461',842744,'camryn83@expjhaHmple.net','448-4569-8335x3844');

-- триггер для логирования при обновлении данных в таблице personal_data
DROP TRIGGER IF EXISTS `after_personal_data_update`;
CREATE TRIGGER `after_personal_data_update`
AFTER UPDATE ON `personal_data`
FOR EACH ROW
BEGIN
    INSERT INTO `personal_data_log` (personal_data_id, user, action)
    VALUES (NEW.id, USER(), 'Updated');
END;
-- UPDATE personal_data SET email = 'jvndfkjbngf@example.ru' WHERE id = 170;

-- триггер для логирования при удалении данных в таблице personal_data
DROP TRIGGER IF EXISTS `after_personal_data_delete`;
CREATE TRIGGER `after_personal_data_delete`
AFTER DELETE ON `personal_data`
FOR EACH ROW
BEGIN
    INSERT INTO `personal_data_log` (personal_data_id, user, action)
    VALUES (OLD.id, USER(), 'Deleted');
END;
-- DELETE FROM personal_data WHERE id = 170;

CREATE TABLE `doctors` (
	personal_data_id SERIAL PRIMARY KEY, 
	employee_id BIGINT UNSIGNED NOT NULL UNIQUE COMMENT 'Табельный номер',
	department_id BIGINT UNSIGNED NOT NULL COMMENT 'Отделение',
	post VARCHAR(100) NOT NULL COMMENT 'Должность',
	timetable_of_work VARCHAR(255) COMMENT 'Расписание работы',
	FOREIGN KEY (personal_data_id) REFERENCES personal_data(id) ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (department_id) REFERENCES departments(id) ON UPDATE CASCADE ON DELETE CASCADE
)  COMMENT = 'Высший медицинский персонал';
INSERT INTO `doctors` VALUES
(86,24146895,1,'nam','9.00-18.00'),
(87,8841269,2,'commodi','9.00-18.00'),
(88,5,3,'sunt','9.00-18.00'),
(89,1758,4,'soluta','9.00-18.00'),
(90,46612,5,'iusto','9.00-18.00'),
(93,4,6,'suscipit','9.00-18.00'),
(94,9338,5,'laboriosam','9.00-18.00'),
(95,221780229,4,'optio','9.00-18.00'),
(96,24,3,'aliquam','9.00-18.00'),
(97,6,2,'accusantium','9.00-18.00'),
(98,93,1,'iure','9.00-18.00'),
(99,734193,2,'consequatur','9.00-18.00'),
(100,510,3,'expedita','9.00-18.00'),
(101,851,6,'qui','9.00-18.00');
-- изменение графика работы докторов
UPDATE doctors  
SET timetable_of_work = '21.00-9.00'
WHERE personal_data_id = 88 OR personal_data_id = 94;
UPDATE doctors  
SET timetable_of_work = '9.00-21.00'
WHERE personal_data_id = 99 OR personal_data_id = 101;

CREATE TABLE `medical_staffs` (
	personal_data_id SERIAL PRIMARY KEY, 
	employee_id BIGINT UNSIGNED NOT NULL UNIQUE COMMENT 'Табельный номер',
	department_id BIGINT UNSIGNED NOT NULL COMMENT 'Отделение',
	post VARCHAR(100) NOT NULL COMMENT 'Должность',
	timetable_of_work VARCHAR(255) COMMENT 'Расписание работы',
	FOREIGN KEY (personal_data_id) REFERENCES personal_data(id) ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (department_id) REFERENCES departments(id) ON UPDATE CASCADE ON DELETE CASCADE
) COMMENT = 'Средний и младший медицинский персонал';
INSERT INTO `medical_staffs` VALUES
(102,671154131,1,'commodi','9.00-18.00'),
(103,166469,2,'vel','9.00-18.00'),
(104,497722,3,'rerum','9.00-18.00'),
(108,372289891,4,'et','9.00-18.00'),
(109,50518,5,'non','9.00-18.00'),
(110,60286,6,'nesciunt','9.00-18.00'),
(111,18237183,1,'est','9.00-18.00'),
(113,3851575,2,'laudantium','9.00-18.00'),
(115,794960,3,'et','9.00-18.00'),
(117,223611184,4,'et','9.00-18.00'),
(118,684282040,5,'impedit','9.00-18.00'),
(120,3302,6,'non','9.00-18.00'),
(122,58429037,1,'voluptatum','9.00-18.00'),
(123,8,2,'id','9.00-18.00');

CREATE TABLE `departments_medical_staffs`(
	department_id BIGINT UNSIGNED NOT NULL COMMENT 'Отделение',
	medical_staff_id BIGINT UNSIGNED NOT NULL COMMENT 'Средний и младший медицинский персонал',
	PRIMARY KEY (department_id, medical_staff_id), -- означает, что один мед.работник может числиться в одном отделении только один раз
	FOREIGN KEY (department_id) REFERENCES departments(id) ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (medical_staff_id) REFERENCES medical_staffs(personal_data_id) ON UPDATE CASCADE ON DELETE CASCADE
) COMMENT = 'Отделения работы среднего и младшего медицинского персонала при совместительстве';
INSERT INTO `departments_medical_staffs` VALUES
(4,104),
(4,113),
(5,117),
(6,118);

CREATE TABLE `photos`(
	id SERIAL PRIMARY KEY,
	name VARCHAR(255),
	created_at DATETIME DEFAULT NOW()
) COMMENT = 'Фото';
INSERT INTO `photos` VALUES
(1,'ullam','1975-12-18 16:33:56'),
(2,'quo','2022-03-08 11:08:45'),
(3,'aut','2010-12-19 17:35:29'),
(4,'aut','1991-01-27 05:31:03'),
(5,'voluptate','2021-10-21 17:25:35'),
(8,'assumenda','2003-12-19 06:39:30'),
(9,'dolorem','2014-02-17 04:03:10'),
(10,'laudantium','1978-11-08 10:22:26'),
(11,'aut','2010-11-03 07:09:55'),
(12,'veritatis','1988-03-13 23:35:59'),
(13,'occaecati','1980-04-11 05:15:19'),
(14,'libero','1983-12-28 14:21:21'),
(15,'temporibus','1985-03-23 10:08:45'),
(16,'consectetur','2012-11-27 04:51:50'),
(17,'ullam','2016-05-13 00:34:15'),
(18,'dolorem','1996-03-12 09:50:27'),
(19,'amet','2007-09-11 18:19:26'),
(20,'ut','2007-08-28 17:09:43'),
(21,'ea','2016-01-24 18:44:23'),
(22,'doloribus','1978-11-23 12:27:27'),
(23,'praesentium','2011-07-29 16:53:16'),
(24,'sapiente','1982-08-18 08:24:49'),
(25,'eius','2006-02-06 04:13:04'),
(26,'officia','1972-05-14 00:03:34'),
(27,'eius','2014-04-15 12:06:59'),
(28,'et','1981-08-26 06:47:12'),
(29,'sed','1982-04-01 11:20:18'),
(30,'architecto','2005-02-08 09:50:20');

CREATE TABLE `media`(
	photo_id SERIAL PRIMARY KEY,
	body text COMMENT 'Подпись',
	personal_data_id BIGINT UNSIGNED NOT NULL,
	filename VARCHAR(255) COMMENT 'Местоположение фото',
	`size` INT,
	metadata JSON,
	created_at DATETIME DEFAULT NOW(),
	FOREIGN KEY (photo_id) REFERENCES photos(id) ON UPDATE NO ACTION ON DELETE CASCADE,
	FOREIGN KEY (personal_data_id) REFERENCES personal_data(id) ON UPDATE CASCADE ON DELETE CASCADE
) COMMENT = 'Фото медицинского персонала';
INSERT INTO `media` VALUES
(1,'Aut ut dolorum',86,'voluptates',1,NULL,'1989-07-16 02:05:33'),
(2,'Vitae omnis sed itaque',87,'minus',2,NULL,'1992-06-21 21:37:07'),
(3,'Distinctio itaque reiciendis',88,'qui',4854226,NULL,'2003-11-24 19:10:41'),
(4,'Veniam nihil excepturi quo aliquid neque',89,'et',5071,NULL,'1989-12-10 06:23:23'),
(5,'Saepe aut et sed perspiciatis laboriosam',90,'placeat',0,NULL,'1992-10-07 16:29:18'),
(8,'Distinctio asperiores qui et rem quam doloremque placeat quod',93,'et',2010,NULL,'2009-03-31 07:51:39'),
(9,'Saepe repellat dicta veniam eos porro',94,'laboriosam',387467595,NULL,'1991-11-15 00:05:28'),
(10,'Ad quod aut deserunt ab molestias porro veniam',95,'et',200257,NULL,'1998-03-06 19:27:01'),
(11,'Porro aut ipsam autem illo maxime accusamu',96,'fvfgb',74015325,NULL,'2001-09-03 12:55:25'),
(12,'Et voluptas sed et',97,'recusandae',6031,NULL,'1973-09-24 13:02:06'),
(13,'Similique optio laudantium aut et',98,'quis',8696988,NULL,'2014-09-20 18:16:43'),
(14,'Fuga vel facilis',99,'id',913575,NULL,'1989-10-29 16:47:13'),
(15,'Dicta fugit cumque odit qui eaque',100,'sed',3325722,NULL,'1992-01-24 10:03:56'),
(16,'Ut minus sint excepturi velit molestias',101,'voluptates',951844321,NULL,'1989-07-16 02:05:33'),
(17,'Vero earum nihil libero dolor veritatis',102,'minus',2,NULL,'1992-06-21 21:37:07'),
(18,'Nobis qui totam dolore aut porro est',103,'qui',4854226,NULL,'2003-11-24 19:10:41'),
(19,'Laboriosam saepe dolor ut incidunt sunt',104,'et',5071,NULL,'1989-12-10 06:23:23'),
(20,'Et quia quam iusto sapiente atque ducimus',108,'placeat',0,NULL,'1992-10-07 16:29:18'),
(21,'Tenetur provident autem asperiores adipisci minus',109,'repudiandae',9258493,NULL,'1974-10-10 22:34:37'),
(22,'Corporis aspernatur saepe placeat ut consequatur ipsum',110,'praesentium',9,NULL,'2010-03-10 19:05:01'),
(23,'Consequatur deleniti consequatur eligendi fugit',111,'et',2010,NULL,'2009-03-31 07:51:39'),
(24,'Natus voluptates officia quasi',113,'laboriosam',387467595,NULL,'1991-11-15 00:05:28'),
(25,'Autem distinctio est',115,'et',200257,NULL,'1998-03-06 19:27:01'),
(26,'Totam iste quia quas eos et sint illo ea',117,'voluptates',74015325,NULL,'2001-09-03 12:55:25'),
(27,'Voluptas voluptatem unde nulla illo porro facere atque eos',118,'recusandae',6031,NULL,'1973-09-24 13:02:06'),
(28,'Culpa placeat est quaerat eius accusamus sint omnis',120,'quis',8696988,NULL,'2014-09-20 18:16:43'),
(29,'Qui eos amet fuga quod et',122,'id',913575,NULL,'1989-10-29 16:47:13'),
(30,'Deserunt impedit eum odit quia dolores',123,'sed',3325722,NULL,'1992-01-24 10:03:56');

CREATE TABLE `patients` (
	personal_data_id SERIAL PRIMARY KEY, 
	policy_number BIGINT UNSIGNED NOT NULL UNIQUE COMMENT 'Номер страхового полиса',
	place_of_work VARCHAR(255) COMMENT 'Место работы',
	date_of_last_visit DATETIME NOT NULL COMMENT 'Дата последнего посещения', 
	important_information VARCHAR(120) COMMENT 'Информация, которая может быть полезна',
	FOREIGN KEY (personal_data_id) REFERENCES personal_data(id) ON UPDATE CASCADE ON DELETE CASCADE
) COMMENT = 'Пациенты';
INSERT INTO `patients` VALUES
(125,51679451,'aut','2021-08-20 19:15:55','ut'),
(126,78236,'illum','2010-09-06 17:01:03','tempora'),
(127,152683,'consequuntur','2023-04-26 05:50:11','sunt'),
(128,41527,'atque','2011-01-14 12:36:30','magnam'),
(129,57363,'esse','1982-11-12 02:22:03','sit'),
(130,9363858,'nostrum','1989-11-26 02:50:27','nihil'),
(132,143537,'dolores','2005-05-28 01:08:23','sint'),
(135,58725254,'id','1983-08-07 19:36:48','consequatur'),
(136,42537,'eius','2000-02-20 15:09:36','fugiat'),
(138,45263,'voluptates','2017-11-27 15:35:59','hic'),
(139,41523,'est','2013-08-15 00:01:17','sit'),
(140,7584585,'harum','1982-11-18 19:01:22','eos'),
(141,475831,'dolores','2005-05-28 01:08:23','sint'),
(142,3396666,'id','1983-08-07 19:36:48','consequatur'),
(143,7869333,'eius','2000-02-20 15:09:36','fugiat'),
(146,7863,'voluptates','2017-11-27 15:35:59','hic'),
(147,785962,'est','2013-08-15 00:01:17','sit'),
(150,426963,'harum','1982-11-18 19:01:22','eos'),
(151,363231299,'aut','2021-08-20 19:15:55','ut'),
(152,67802,'illum','2010-09-06 17:01:03','tempora'),
(153,3975856,'consequuntur','2023-04-26 05:50:11','sunt'),
(155,277334111,'atque','2011-01-14 12:36:30','magnam'),
(156,28721426,'esse','1982-11-12 02:22:03','sit'),
(160,799392,'nostrum','1989-11-26 02:50:27','nihil'),
(162,363972,'dolores','2005-05-28 01:08:23','sint'),
(163,8422819,'id','1983-08-07 19:36:48','consequatur'),
(164,14880,'eius','2000-02-20 15:09:36','fugiat'),
(165,5452939,'voluptates','2017-11-27 15:35:59','hic'),
(166,576115,'est','2013-08-15 00:01:17','sit'),
(169,496773,'harum','1982-11-18 19:01:22','eos');

CREATE TABLE `medical_cards` (
	id SERIAL PRIMARY KEY,
	patient_id BIGINT UNSIGNED NOT NULL COMMENT 'Пациент',
	card_number BIGINT UNSIGNED NOT NULL UNIQUE COMMENT 'Номер медицинской карты',
	created_at DATETIME  NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Дата регистрации медицинской карты',
	FOREIGN KEY (patient_id) REFERENCES patients(personal_data_id) ON UPDATE CASCADE ON DELETE CASCADE
) COMMENT = 'Медицинская карта';
INSERT INTO `medical_cards` VALUES
(1,151,284,'2005-04-27 03:45:42'),
(2,152,62875936,'2013-11-24 12:56:23'),
(3,153,111111,'1977-10-08 05:30:38'),
(4,155,9209,'2001-10-08 07:25:04'),
(5,156,599,'1987-09-12 14:01:06'),
(6,160,879129334,'1992-10-28 16:25:04'),
(7,162,3817153,'2010-06-25 21:52:27'),
(8,163,356489,'1978-05-01 07:01:49'),
(9,164,5145154,'1982-09-01 04:25:21'),
(10,165,680710149,'1985-10-06 16:28:42'),
(11,166,851089,'2015-07-01 23:09:59'),
(12,169,287657,'2009-08-29 06:52:45'),
(13,125,2845365,'2005-04-27 03:45:42'),
(14,126,62875945,'2013-11-24 12:56:23'),
(15,127,524536,'1977-10-08 05:30:38'),
(16,128,5263786,'2001-10-08 07:25:04'),
(17,129,435396,'1987-09-12 14:01:06'),
(18,130,253638,'1992-10-28 16:25:04'),
(19,132,38171353,'2010-06-25 21:52:27'),
(20,135,5397799,'1978-05-01 07:01:49'),
(21,136,79696,'1982-09-01 04:25:21'),
(22,138,5363333,'1985-10-06 16:28:42'),
(23,139,445752,'2015-07-01 23:09:59'),
(24,140,876996,'2009-08-29 06:52:45'),
(25,141,5536,'2001-10-08 07:25:04'),
(26,142,88896363,'1987-09-12 14:01:06'),
(27,143,425353,'1992-10-28 16:25:04'),
(28,146,857475,'2010-06-25 21:52:27'),
(29,147,5233357,'1978-05-01 07:01:49'),
(30,150,425389,'1982-09-01 04:25:21');

CREATE TABLE `receptions` (
	id SERIAL PRIMARY KEY, 
	medical_card_id BIGINT UNSIGNED NOT NULL UNIQUE COMMENT 'Медицинская карта',
	date_of_visit DATETIME NOT NULL DEFAULT NOW() COMMENT 'Дата и время посещения',  
	date_of_next_visit DATETIME COMMENT 'Планируемая дата следующего посещения', 
	department_id BIGINT UNSIGNED COMMENT 'Отделение приема',
	doctor_id BIGINT UNSIGNED COMMENT 'Доктор, ведущий прием', 
	medical_staff_id BIGINT UNSIGNED COMMENT 'Медицинский персонал на приеме', 
	complaints TEXT NOT NULL COMMENT 'Жалобы', -- тип данных - как правильно задать
	diagnosis TEXT NOT NULL COMMENT 'Диагноз',
	prescribing_medicines TEXT COMMENT 'Назначение препаратов',
	procedures_and_examinations TEXT COMMENT 'Назначение процедур и обследований',
	general_recommendations TEXT COMMENT 'Общие рекомендации', 
	sick_leave_number BIGINT UNSIGNED UNIQUE COMMENT 'Номер больничного листа', 
	medical_certificate_number BIGINT UNSIGNED UNIQUE COMMENT 'Номер медицинской справки',
	FOREIGN KEY (medical_card_id) REFERENCES medical_cards(id) ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (department_id) REFERENCES departments(id) ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (doctor_id) REFERENCES doctors(personal_data_id) ON UPDATE CASCADE ON DELETE NO ACTION,
	FOREIGN KEY (medical_staff_id) REFERENCES medical_staffs(personal_data_id) ON UPDATE CASCADE ON DELETE NO ACTION
) COMMENT = 'Прием врача';
INSERT INTO `receptions` VALUES
(1,1,'2005-03-22 04:22:08','2004-12-03 09:51:06',1,86,102,'et','exercitationem','quo','cumque','id',681890801,0),
(2,2,'2014-05-23 12:15:49','2007-11-26 00:22:08',2,87,103,'consequatur','vel','officia','officia','vel',7314096,3913954),
(3,3,'2005-04-18 22:44:39','1975-03-19 01:32:55',3,88,104,'labore','aperiam','deleniti','sapiente','at',571,90643082),
(4,4,'2023-09-30 18:52:46','1974-02-07 17:01:24',4,89,103,'et','aut','aut','earum','tempore',5445,7256382),
(5,5,'2010-03-04 22:58:20','1990-11-01 02:21:28',5,90,108,'quos','sequi','ut','eligendi','quae',452,4516707),
(6,6,'2002-01-22 06:51:03','2008-04-16 14:38:22',6,101,109,'repudiandae','unde','sit','facilis','asperiores',77490,95),
(7,7,'2008-12-05 20:23:29','1996-10-23 03:08:37',1,86,110,'nisi','omnis','temporibus','ad','quae',2,7469100),
(8,8,'2004-08-18 22:15:22','1971-06-09 16:19:18',3,98,111,'sit','eos','ut','voluptas','ratione',4796,9001560),
(9,9,'1985-04-22 20:44:50','1980-03-31 07:51:24',5,89,113,'nostrum','maiores','vitae','fugit','dolor',8,4455),
(10,10,'2007-03-20 19:36:55','2018-06-24 03:49:41',2,93,115,'quos','commodi','possimus','sed','velit',396996,353721243),
(11,11,'1976-12-19 12:42:42','2021-12-22 15:38:09',4,93,118,'enim','minus','rerum','doloremque','officia',450,32236067),
(12,12,'1999-03-04 05:18:58','2015-01-15 02:40:11',6,97,120,'iste','ab','voluptatibus','qui','vitae',18756604,25780357),
(13,13,'1974-09-08 05:52:18','1981-08-29 15:45:26',2,94,123,'quod','aut','libero','excepturi','ut',645,845634),
(14,14,'1990-10-26 03:50:52','2006-08-14 00:35:29',4,95,122,'ducimus','doloremque','libero','qui','minima',99,7247),
(15,15,'2023-12-30 03:06:06','1973-07-04 12:11:11',6,86,117,'voluptatem','quam','saepe','necessitatibus','nobis',83979674,931),
(16,16,'1980-09-28 11:32:58','2007-02-11 07:33:10',2,87,118,'est','laboriosam','quia','placeat','quia',34628,82343682),
(17,17,'1988-09-02 08:48:55','1970-08-28 08:02:17',1,88,104,'vitae','quos','ipsam','commodi','et',2284,70732454),
(18,18,'2014-04-23 08:36:42','1976-11-19 06:01:04',3,89,104,'earum','eaque','unde','perferendis','magni',681815,86235651),
(19,19,'2012-02-26 19:05:02','1997-05-08 12:52:27',1,90,103,'assumenda','nobis','sequi','saepe','quo',6388,9772486),
(20,20,'1992-02-16 13:50:30','1997-12-14 01:45:52',5,99,108,'recusandae','non','voluptatem','qui','reprehenderit',815248284,954944222);

CREATE TABLE `procedures_and_examinations` (
	id SERIAL PRIMARY KEY, 
	medical_card_id BIGINT UNSIGNED NOT NULL COMMENT 'Медицинская карта',
	reception_id BIGINT UNSIGNED COMMENT 'Прием - основание назначения',
	date_of_visit DATETIME NOT NULL DEFAULT NOW() COMMENT 'Дата и время посещения', 
	doctor_id BIGINT UNSIGNED COMMENT 'Доктор, проводящий процедуру или обследование', 
	medical_staff_id BIGINT UNSIGNED COMMENT 'Медицинский персонал, проводящий процедуру или обследование',
	description TEXT NOT NULL COMMENT 'Описание хода медицинских манипуляций',
	FOREIGN KEY (medical_card_id) REFERENCES medical_cards(id) ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (reception_id) REFERENCES receptions(id) ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (doctor_id) REFERENCES doctors(personal_data_id) ON UPDATE CASCADE ON DELETE NO ACTION,
	FOREIGN KEY (medical_staff_id) REFERENCES medical_staffs(personal_data_id) ON UPDATE CASCADE ON DELETE NO ACTION
) COMMENT = 'Процедуры и обследования';
INSERT INTO `procedures_and_examinations` VALUES
(1,1,1,'1975-05-02 10:49:41',86,102,'Dicta consequatur labore ut quam aut. Quis ut aut quis fugiat in. Similique similique qui debitis ratione quia.'),
(2,2,2,'1989-01-11 15:03:11',87,108,'Quia aut corrupti qui adipisci modi incidunt. Ut odit hic quis ipsam quis numquam. Quia sit et eius. Voluptatem similique consequatur aspernatur dolores nihil quibusdam dignissimos.'),
(3,3,3,'1977-09-06 12:40:20',88,118,'Aut voluptas ipsam voluptas ducimus. Quos ut sed temporibus qui nam cupiditate consequatur. Amet quaerat nesciunt optio omnis molestiae quasi.'),
(4,4,4,'2011-06-17 23:12:10',89,111,'Ex laborum ab quos modi. Tenetur iste officiis dicta. Nihil illo ipsum et consequatur corporis. Nisi commodi sapiente exercitationem ullam.'),
(5,5,5,'2016-02-29 18:21:33',90,115,'Cum consequuntur et sed porro. A corporis a sit et dolorum. Et eum nihil in veritatis quasi temporibus quasi.');

CREATE TABLE `sick_leaves` (
	id SERIAL PRIMARY KEY,
	sick_leave_number_id BIGINT UNSIGNED COMMENT 'Номер больничного листа',
	name_of_the_hospital_id VARCHAR(255) NOT NULL COMMENT 'Наименование медицинского учреждения, выдавшего больничный лист',
	date_of_issue DATE NOT NULL COMMENT 'Дата выдачи',
	validity_period DATE COMMENT 'Срок действия',
	patient_id BIGINT UNSIGNED NOT NULL COMMENT 'Пациент',
	doctor_id BIGINT UNSIGNED NOT NULL COMMENT 'Врач',
	`status` ENUM('open', 'extended', 'closed') COMMENT 'Статус больничного листа',
	FOREIGN KEY (name_of_the_hospital_id) REFERENCES hospital_structure(name_of_the_hospital) ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (patient_id) REFERENCES personal_data(id) ON UPDATE CASCADE ON DELETE NO ACTION,
	FOREIGN KEY (doctor_id) REFERENCES personal_data(id) ON UPDATE CASCADE ON DELETE NO ACTION
) COMMENT = 'Больничный лист';
INSERT INTO `sick_leaves` VALUES
(1,815248284,'Центральная городская больница','2018-10-18','1980-03-28',151,86,'extended'),
(2,681815,'Центральная городская больница','1993-03-30','1975-06-23',152,87,'extended'),
(3,18756604,'Центральная городская больница','2006-12-03','1971-12-30',153,88,'open'),
(4,450,'Центральная городская больница','1981-06-09','2014-09-11',155,89,'open'),
(5,452,'Центральная городская больница','1978-07-30','2020-05-09',156,90,'open'),
(6,571,'Центральная городская больница','1982-03-06','2019-01-27',160,99,'open'),
(7,645,'Центральная городская больница','1999-09-01','1973-12-28',162,98,'open'),
(8,2284,'Центральная городская больница','2022-04-11','2004-01-15',163,88,'closed'),
(9,4796,'Центральная городская больница','1976-08-04','2023-06-05',164,86,'extended'),
(10,5445,'Центральная городская больница','1993-02-19','1991-12-08',165,94,'open');

CREATE TABLE `medical_certificates` (
	id SERIAL PRIMARY KEY,
	medical_certificate_number_id BIGINT UNSIGNED NOT NULL COMMENT 'Номер медицинской справки',
	name_of_the_hospital_id VARCHAR(255) NOT NULL COMMENT 'Наименование медицинского учреждения, выдавшего справку',
	date_of_issue DATE COMMENT 'Дата выдачи',
	validity_period DATE COMMENT 'Срок действия',
	patient_id BIGINT UNSIGNED NOT NULL COMMENT 'Пациент',
	doctor_id BIGINT UNSIGNED NOT NULL COMMENT 'Врач',
	FOREIGN KEY (name_of_the_hospital_id) REFERENCES hospital_structure(name_of_the_hospital) ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (patient_id) REFERENCES patients(personal_data_id) ON UPDATE CASCADE ON DELETE NO ACTION,
	FOREIGN KEY (doctor_id) REFERENCES doctors(personal_data_id) ON UPDATE CASCADE ON DELETE NO ACTION,
	FOREIGN KEY (medical_certificate_number_id) REFERENCES receptions(medical_certificate_number) ON UPDATE CASCADE ON DELETE NO ACTION
) COMMENT = 'Медицинская справка';
INSERT INTO `medical_certificates` VALUES
('1', '0', 'Центральная городская больница', '1982-08-30', '1996-06-07', '151', '86'),
('2', '95', 'Центральная городская больница', '1999-04-22', '2001-05-29', '152', '87'),
('3', '931', 'Центральная городская больница', '1980-02-27', '1991-06-27', '153', '88'),
('4', '4455', 'Центральная городская больница', '1984-08-14', '2013-07-20', '155', '89'),
('5', '7247', 'Центральная городская больница', '2009-09-12', '1980-06-01', '156', '90');



-- вывести данные о врачах: ФИО, месторасположение фото, табельный номер, отделение, должность (вложенный запрос)
SELECT
	lastname,
	firsname,
	patronymic,
	(SELECT filename FROM media WHERE personal_data_id = personal_data.id) AS photo,
	(SELECT employee_id FROM doctors WHERE personal_data_id = personal_data.id) AS employee_id,
	(SELECT department_id FROM doctors WHERE personal_data_id = personal_data.id) AS department_id,
	(SELECT post FROM doctors WHERE personal_data_id = personal_data.id) AS post
FROM personal_data
HAVING employee_id IS NOT NULL;

-- вывести данные о враче с id = 88: ФИО, месторасположение фото, табельный номер, отделение, должность (вложенный запрос)
SELECT
	lastname,
	firsname,
	patronymic,
	(SELECT filename FROM media WHERE personal_data_id = personal_data.id) AS photo,
	(SELECT employee_id FROM doctors WHERE personal_data_id = personal_data.id) AS employee_id,
	(SELECT department_id FROM doctors WHERE personal_data_id = personal_data.id) AS department_id,
	(SELECT post FROM doctors WHERE personal_data_id = personal_data.id) AS post
FROM personal_data
WHERE id=88;

-- посчитать количество приемов доктора c id = 86 (однотабличный запрос) 
SELECT 
	COUNT(*)
FROM receptions
WHERE doctor_id=86;

-- посчитать количество больничных листов с каждым из статусов (однотабличный запрос, группировка)
SELECT 
	status,
	COUNT(*) AS cnt
FROM sick_leaves
GROUP BY status;

-- количество выданных больничных листов в каждом месяце (однотабличный запрос, группировка, сортировка)
SELECT 
	MONTH(date_of_issue) AS month_num,
	ANY_VALUE(MONTHNAME(date_of_issue)) AS month_name,
	COUNT(*) AS cnt
FROM sick_leaves
GROUP BY month_num
ORDER BY cnt DESC;
	
-- количество выданных каждым доктором больничных листов с указанием отделения (вложенный запрос, группировка, сортировка)
SELECT 
	doctor_id,
	(SELECT department_id FROM doctors WHERE personal_data_id = sick_leaves.doctor_id) AS department,
	COUNT(*) AS cnt
FROM sick_leaves
GROUP BY doctor_id
ORDER BY cnt DESC;
	
-- количество выданных каждым доктором больничных листов с указанием отделения в каждом месяце (вложенный запрос, группировка, сортировка)
SELECT 
	doctor_id,
	(SELECT department_id FROM doctors WHERE personal_data_id = sick_leaves.doctor_id) AS department,
	MONTH(date_of_issue) AS month_num,
	ANY_VALUE(MONTHNAME(date_of_issue)) AS month_name,	
	COUNT(*) AS cnt
FROM sick_leaves
GROUP BY doctor_id, month_num
ORDER BY doctor_id DESC;	

-- выбрать id среднего и младшего медицинского персонала, работающего в 4 отделении 
SELECT personal_data_id FROM medical_staffs WHERE department_id = 4
UNION
SELECT medical_staff_id FROM departments_medical_staffs WHERE department_id = 4;

-- распределение пациентов по полу
SELECT 
	gender,
	COUNT(gender) 
FROM personal_data
WHERE id = (SELECT personal_data_id FROM patients WHERE personal_data_id = personal_data.id)
GROUP BY gender;

-- количество пациентов каждого возраста 
SELECT 
	ROUND((DATEDIFF(CURRENT_DATE(), date_of_birth)) / 365) AS age,
	COUNT(*) AS cnt
FROM personal_data
WHERE id = (SELECT personal_data_id FROM patients WHERE personal_data_id = personal_data.id)
GROUP BY age
ORDER BY cnt DESC;

-- рассчитать средний возраст докторов и медицинского персонала 
SELECT AVG(ROUND((DATEDIFF(CURRENT_DATE(), date_of_birth)) / 365)) 
FROM personal_data
WHERE id = (SELECT personal_data_id FROM doctors WHERE personal_data_id = personal_data.id);
SELECT AVG(ROUND((DATEDIFF(CURRENT_DATE(), date_of_birth)) / 365)) 
FROM personal_data
WHERE id = (SELECT personal_data_id FROM medical_staffs WHERE personal_data_id = personal_data.id);

-- вывести информацию, указываемую в справках: номер справки, название медицинского учреждения, ФИО и дату рождения пациента, дату выдачи, 
-- период действия, ФИО доктора, выдавшего справку
SELECT 
	mc.medical_certificate_number_id AS medical_certificate_number,
	mc.name_of_the_hospital_id AS name_of_the_hospital,
	CONCAT(pd.lastname, ' ', pd.firsname, ' ', pd.patronymic, ',', pd.date_of_birth) AS patient,
	mc.date_of_issue,
	mc.validity_period,
	CONCAT(pd2.lastname, ' ', pd2.firsname, ' ', pd2.patronymic) AS doctor
FROM medical_certificates mc 
JOIN personal_data pd ON mc.patient_id = pd.id
JOIN personal_data pd2 ON mc.doctor_id = pd2.id;

-- вывести информацию о приемах (id приема, ФИО доктора и младшего медицинского персонала на приеме, дата приема, описание) 
-- вместе с информацией о медицинской карте (номер, дата создания) и данными пациента 
SELECT
	mc.card_number,
	mc.created_at AS date_of_creation_of_medical_card,
	CONCAT(pd.lastname, ' ', pd.firsname, ' ', pd.patronymic, ',', pd.date_of_birth) AS patient,
	pae.reception_id,
	CONCAT(pd2.lastname, ' ', pd2.firsname, ' ', pd2.patronymic) AS doctor,
	CONCAT(pd2.lastname, ' ', pd2.firsname, ' ', pd2.patronymic) AS medical_staff,
	pae.date_of_visit,
	pae.description
FROM procedures_and_examinations pae
JOIN medical_cards mc ON mc.id = pae.medical_card_id
JOIN personal_data pd ON pd.id = mc.patient_id
JOIN personal_data pd2 ON pd2.id = pae.doctor_id
JOIN personal_data pd3 ON pd3.id = pae.medical_staff_id;

-- вывести данные о пациентах (ФИО, дата рождения) с информацией о медицинских картах (номер, дата создания), 
-- приемах (дата посещения, департамент, медицинский персонал, жалобы, диагноз, назначения, процедуры, рекомендации), 
-- процедурах (дата, описание), больничных листах (номер, статус, период действия), медицинских справках (номер, период действия) 
SELECT 
	CONCAT(pd.lastname, ' ', pd.firsname, ' ', pd.patronymic, ',', pd.date_of_birth) AS patient,
	CONCAT(mc.card_number, ' (', mc.created_at, ')') AS medical_card,
	r.date_of_visit,
	d.name_of_department,
	CONCAT(pd2.lastname, ' ', pd2.firsname, ' ', pd2.patronymic) AS doctor_is_at_the_reception,
	CONCAT(pd2.lastname, ' ', pd2.firsname, ' ', pd2.patronymic) AS medical_staff_is_at_the_reception,
	r.complaints,
	r.diagnosis,
	r.prescribing_medicines,
	r.procedures_and_examinations,
	r.general_recommendations, 
	pae.date_of_visit AS date_of_procedure,
	pae.description AS description_of_procedure,
	CONCAT(sl.sick_leave_number_id, ' ', sl.status, ', ', sl.validity_period) AS sick_leave,
	CONCAT(mc2.medical_certificate_number_id, ', ', mc2.validity_period) AS medical_certificate
FROM medical_cards mc 
JOIN personal_data pd ON pd.id = mc.patient_id
LEFT JOIN receptions r ON r.medical_card_id = mc.id
LEFT JOIN personal_data pd2 ON pd2.id = r.doctor_id
LEFT JOIN personal_data pd3 ON pd3.id = r.medical_staff_id
LEFT JOIN departments d ON d.id = r.department_id
LEFT JOIN procedures_and_examinations pae ON pae.medical_card_id = mc.id
LEFT JOIN sick_leaves sl ON sl.patient_id = pd.id
LEFT JOIN medical_certificates mc2 ON mc2.patient_id = pd.id
ORDER BY mc.created_at DESC;

-- вывести ФИО, дату рождения и возраст пациентов младше 18 лет 
SELECT
	pd.lastname,
	pd.firsname,
	pd.patronymic,
	pd.date_of_birth,
	ROUND((DATEDIFF(CURRENT_DATE(), pd.date_of_birth)) / 365) AS age
FROM patients p 
JOIN personal_data pd ON pd.id = p.personal_data_id
HAVING age < 18
ORDER BY date_of_birth DESC;

-- вывести информацию о врачах: табельный номер, ФИО, отделение работы и должность
SELECT 
	d.employee_id,
	pd.lastname,
	pd.firsname,
	pd.patronymic,
	d2.name_of_department,
	d.post
FROM doctors d
JOIN personal_data pd ON pd.id = d.personal_data_id 
JOIN departments d2 ON d2.id = d.department_id
ORDER BY pd.lastname;

-- посчитать количество персонала, работающего в каждом отделении
SELECT 
	d.name_of_department,
	COUNT(d2.department_id) AS doctors,
	COUNT(ms.department_id) AS medical_staffs
FROM departments d 
JOIN doctors d2 ON d2.department_id = d.id 
JOIN medical_staffs ms ON ms.department_id = d.id
GROUP BY d.name_of_department;

-- вывести имя и месторасположение фото медицинского персонала вместе с ФИО, отделением работы и должностью 
SELECT
	pd.lastname,
	pd.firsname,
	pd.patronymic,
	post.post,
	m.filename AS path_to_photo,
	p.name AS photo_name,
	d2.name_of_department 
FROM photos p 
JOIN media m ON m.photo_id = p.id 
JOIN personal_data pd ON pd.id = m.personal_data_id
JOIN (SELECT d.personal_data_id, d.post, d.department_id FROM doctors d 
	  UNION ALL
      SELECT ms.personal_data_id, ms.post, ms.department_id FROM medical_staffs ms) AS post ON post.personal_data_id = pd.id
JOIN departments d2 ON d2.id = post.department_id;

-- выбрать больничные у докторов и пациентов со статусом "открыт" (динамический запрос)
-- SELECT doctor_id, patient_id FROM sick_leaves WHERE status = 'open';
SET @table = 'sick_leaves';
SET @columns = 'doctor_id, patient_id';
SET @condition = 'status = "open"';
SET @s = CONCAT('SELECT ', @columns, ' FROM ', @table, ' WHERE ', @condition);
PREPARE open_leaves FROM @s;
EXECUTE open_leaves;

-- представление, которое будет ограничивать доступ к контактной информации 
DROP VIEW IF EXISTS personal_data_view;
CREATE OR REPLACE VIEW personal_data_view AS
    SELECT id,
           firsname,
           lastname,
           patronymic,
           date_of_birth,
           gender,
           CONCAT('***** ', (SUBSTRING_INDEX(address, ' ', -2)))  AS address,
           INSERT(phone, 3, 7, '***') AS phone,
           CONCAT(SUBSTR(email, 1, 2), '*****', SUBSTR(email, -4)) AS email,
           INSERT(additional_contact, 3, 7, '***') AS additional_contact
FROM personal_data;
DESCRIBE personal_data_view;
SELECT * FROM personal_data_view LIMIT 5;

-- транзакция, меняющая статус больничного листа и лечащего врача
START TRANSACTION;
SELECT status FROM sick_leaves WHERE sick_leave_number_id = 18756604;
UPDATE sick_leaves SET status = 'extended' WHERE sick_leave_number_id = 18756604;
SAVEPOINT sick_leaves_savepoint;
SELECT doctor_id FROM sick_leaves WHERE sick_leave_number_id = 18756604;
UPDATE sick_leaves SET doctor_id = 100 WHERE sick_leave_number_id = 18756604;
ROLLBACK TO SAVEPOINT sick_leaves_savepoint;
-- RELEASE SAVEPOINT sick_leaves_savepoint;
COMMIT;

-- количество пациентов в каждом департаменте с открытым больничным листом (оконная функция)
SELECT 
	d.id,
	d.name_of_department, 
	COUNT(sl.status = 'open') OVER(PARTITION BY id ORDER BY id) AS count_sick_leaves
FROM departments d 
JOIN receptions r ON r.department_id = d.id 
JOIN sick_leaves sl ON sl.sick_leave_number_id = r.sick_leave_number; 

-- количество пациентов у каждого врача, больше вводимого значения (хранимая процедура), сохранение в файл csv
DROP PROCEDURE IF EXISTS get_number_of_patients;
CREATE PROCEDURE get_number_of_patients(IN num INT)
BEGIN
	SELECT 
		d.personal_data_id AS doctors, 
		COUNT(mc.patient_id) AS number_of_patients
	INTO OUTFILE 'C:\\\\ProgramData\\\\MySQL\\\\MySQL Server 8.0\\\\Uploads\\\\number_of_patients.csv'
	FROM doctors d 
	LEFT JOIN receptions r ON r.doctor_id = d.personal_data_id
	LEFT JOIN medical_cards mc ON mc.id = r.medical_card_id
	GROUP BY d.personal_data_id
	HAVING number_of_patients  > num;
END;
-- DROP PROCEDURE get_number_of_patients;
CALL get_number_of_patients(1);

-- вывести ФИО пациента по дате приема (хранимая процедура)
DROP PROCEDURE IF EXISTS reception_day_message;
CREATE PROCEDURE reception_day_message(
    IN date_of_visit DATETIME,
    OUT message VARCHAR(255))
BEGIN
    DECLARE quantity INT;
    DECLARE full_name VARCHAR(255);
    SET quantity = (SELECT COUNT(*) FROM receptions WHERE date_of_next_visit = date_of_visit);
    IF quantity = 0 THEN
        SET message = 'No one has an appointment';
    ELSEIF quantity = 1 THEN
        SET full_name = (SELECT CONCAT(pd.firsname, ' ', pd.lastname, ' ', pd.patronymic, ' ')
					FROM receptions r 
					JOIN medical_cards mc ON mc.id = r.medical_card_id 
					JOIN personal_data pd ON pd.id = mc.patient_id
					LIMIT 1);
        SET message = (SELECT CONCAT(full_name, 'appointment is today'));
    ELSE
        SET message = 'Admission of more than one patient';
    END IF;
END;
-- DROP PROCEDURE reception_day_message;
CALL reception_day_message('2014-05-23 12:15:49', @message);
SELECT @message;
CALL reception_day_message('2018-06-24 03:49:41', @message);
SELECT @message;

-- вывести информацию о пациенте по номеру полиса – ФИО, дату рождения, место работы, общие рекомендации и важную информацию (хранимая процедура)
DROP PROCEDURE IF EXISTS information_at_policy_number;
CREATE PROCEDURE information_at_policy_number(
    IN policy_number BIGINT,
    OUT full_name VARCHAR(255),
    OUT place_of_work VARCHAR(255),
    OUT general_recommendations TEXT,
    OUT important_information VARCHAR(120))
BEGIN
    SET full_name = (SELECT CONCAT(pd.lastname, ' ', pd.firsname, ' ', pd.patronymic, ' ', pd.date_of_birth)
					 FROM personal_data pd 
					 JOIN patients p ON p.personal_data_id = pd.id 
					 WHERE p.policy_number = policy_number);
	SET place_of_work = (SELECT p.place_of_work 
						 FROM patients p 
						 WHERE p.policy_number = policy_number);
	SET general_recommendations = (SELECT r.general_recommendations
 								   FROM receptions r 
 								   JOIN medical_cards mc ON mc.patient_id = r.medical_card_id 
 								   JOIN patients p ON p.personal_data_id = mc.patient_id
 								   WHERE p.policy_number = policy_number);
	SET important_information = (SELECT p.important_information 
								 FROM patients p 
								 WHERE p.policy_number = policy_number);
END;
CALL information_at_policy_number(51679451, @full_name, @place_of_work, @general_recommendations, @important_information);
SELECT @full_name, @place_of_work, @general_recommendations, @important_information;
CALL information_at_policy_number(14880, @full_name, @place_of_work, @general_recommendations, @important_information);
SELECT @full_name, @place_of_work, @general_recommendations, @important_information;

-- вывести количество докторов, которым был изменен график работы с 9.00 до 21.00 и с 21.00 до 9.00 (хранимая функция)
DROP FUNCTION IF EXISTS changing_timetable_of_work;
CREATE FUNCTION changing_timetable_of_work(new_timetable VARCHAR(255))
RETURNS INT DETERMINISTIC
BEGIN
    RETURN(SELECT COUNT(personal_data_id) FROM doctors WHERE timetable_of_work = new_timetable);
END;
SELECT changing_timetable_of_work('9.00-21.00');
SELECT changing_timetable_of_work('21.00-9.00');

-- вывести список электронной почты докторов (курсор, цикл)
DROP PROCEDURE IF EXISTS create_email_list;
CREATE PROCEDURE create_email_list (INOUT email_list TEXT )
BEGIN
    DECLARE done BOOL DEFAULT false;
    DECLARE email_address VARCHAR(120) DEFAULT '';
    DECLARE cur_email CURSOR FOR SELECT pd.email FROM personal_data pd JOIN doctors d ON d.personal_data_id = pd.id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true; 
    OPEN cur_email;
    SET email_list = '';
    process_email: LOOP
        FETCH cur_email INTO email_address;
        IF done = true THEN LEAVE process_email;
        END IF;
        SET email_list = CONCAT(email_address,'; ',email_list);
    END LOOP;
    CLOSE cur_email;
END;
CALL create_email_list(@create_email_list);
SELECT @create_email_list;
