# Поликлиника
# 1.Описание предметной области
Данная база данных моделирует систему управления медицинской клиникой. В ней содержатся следующие таблицы:

"врачи" (doctors): Содержит информацию о врачах, включая их идентификатор, имя, специализацию, возраст и пол.

"пациенты" (patients): Содержит информацию о пациентах, включая их идентификатор, имя, возраст, пол и контактный телефон.

"записи на приём" (appointments): Связывает врачей и пациентов и содержит информацию о записях на прием, такую как идентификатор записи, идентификатор врача, идентификатор пациента, дату и время приема и примечание.

"диагнозы" (diagnoses): Содержит информацию о диагнозах, включая идентификатор диагноза, название диагноза и описание.

"результаты обследования" (test_results) содержит информацию о результатах обследования пациентов в рамках медицинской клиники"

# 2.Ожидаемые запросы к предметной области
1) Получить список всех врачей
2) Получить список всех пациентов
3) Количество пациентов, принятых каждым врачом поликлиники
4) Количество пациентов, принятых каждым специалистом (например, окулистом - 50 человек; из них Иванов принял 23, Петров - 27)
5) Сколько раз каждый пациент обращался в поликлинику в текущем году, в текущем месяце
6) К каким специалистам обращался пациент в прошлом году, месяце
# 3.Описание базы
В базе Поликлиника содержатся следующие таблицы:

Примечание: при построении ER диаграммы использовался инструмент DBDiagram (доступен
здесь https://dbdiagram.io)

## Таблица: doctors (врачи)
| Column Name | Data Type | Description |
|---------------|--------------|-------------------------------------------|
| id | SERIAL | Уникальный идентификатор врача |
| name | VARCHAR(50) | Имя врача |
| specialization| VARCHAR(50) | Специализация врача |
| age | INT | Возраст врача |
| gender | VARCHAR(10) | Пол врача |

## Таблица: patients (пациенты)
| Column Name | Data Type | Description |
|------------------|--------------|-------------------------------------------|
| id | SERIAL | Уникальный идентификатор пациента |
| name | VARCHAR(50) | Имя пациента |
| age | INT | Возраст пациента |
| gender | VARCHAR(10) | Пол пациента |
| contact_phone | VARCHAR(15) | Контактный телефон пациента |

## Таблица: appointments (записи на приём)
| Column Name | Data Type | Description |
|--------------------|--------------|-------------------------------------------|
| id | SERIAL | Уникальный идентификатор записи на приём |
| doctor_id | INT | Внешний ключ, ссылается на таблицу "doctors" |
| patient_id | INT | Внешний ключ, ссылается на таблицу "patients" |
| date_and_time | TIMESTAMP | Дата и время приёма |
| note | TEXT | Дополнительные заметки или комментарии к приёму |

## Таблица: diagnoses (диагнозы)
| Column Name | Data Type | Description |
|---------------|--------------|-------------------------------------------|
| id | SERIAL | Уникальный идентификатор диагноза |
| name | VARCHAR(100) | Название диагноза |
| description | TEXT | Описание диагноза |

## Таблица: test_results (результаты обследования)
| Column Name | Data Type | Description |
|--------------------|--------------|-------------------------------------------|
| id | SERIAL | Уникальный идентификатор результата обследования |
| appointment_id | INT | Внешний ключ, ссылается на таблицу "appointments" |
| diagnosis_id | INT | Внешний ключ, ссылается на таблицу "diagnoses" |
| result | TEXT | Результат обследования |

# 4.Скрипт создания базы

CREATE TABLE doctors (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50),
  specialization VARCHAR(50),
  age INT,
  gender VARCHAR(10)
);


CREATE TABLE patients (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  gender VARCHAR(10),
  contact_phone VARCHAR(15)
);


CREATE TABLE appointments (
  id SERIAL PRIMARY KEY,
  doctor_id INT REFERENCES doctors(id),
  patient_id INT REFERENCES patients(id),
  date_and_time TIMESTAMP,
  note TEXT
);


CREATE TABLE diagnoses (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  description TEXT
);


CREATE TABLE test_results (
  id SERIAL PRIMARY KEY,
  appointment_id INT REFERENCES appointments(id),
  diagnosis_id INT REFERENCES diagnoses(id),
  result TEXT
);

# 5.Скрипт заполнения базы

CREATE OR REPLACE FUNCTION random_choice(anyarray)
  RETURNS anyelement AS
$$
BEGIN
  RETURN (SELECT $1[FLOOR(RANDOM() * ARRAY_LENGTH($1, 1) + 1)]);
END;
$$
LANGUAGE plpgsql;


-- Insert random data into the doctors table
INSERT INTO doctors (name, specialization, age, gender)
SELECT
  random_choice(ARRAY['Иван', 'Мария', 'Алексей', 'Екатерина', 'Дмитрий', 'Анна', 'Александр', 'Ольга', 'Николай', 'София', 'Михаил', 'Анастасия', 'Сергей', 'Виктория', 'Артем']),
  random_choice(ARRAY['Кардиолог', 'Дерматолог', 'Невролог', 'Офтальмолог', 'Педиатр', 'Психиатр', 'Хирург', 'Гинеколог', 'Ортопедический хирург', 'Уролог', 'Эндокринолог', 'Онколог', 'Радиолог', 'Анестезиолог', 'Патологоанатом']),
  floor(random() * 40) + 30,
  random_choice(ARRAY['Мужской', 'Женский'])
FROM generate_series(1, 100);

-- Insert random data into the patients table
INSERT INTO patients (name, age, gender, contact_phone)
SELECT
  random_choice(ARRAY['Алексей', 'Мария', 'Никита', 'Ольга', 'Иван', 'Екатерина', 'Александр', 'София', 'Даниил', 'Анна', 'Михаил', 'Анастасия', 'Максим', 'Виктория', 'Егор']),
  floor(random() * 80) + 1,
  random_choice(ARRAY['Мужской', 'Женский']),
  CONCAT('+7', floor(random() * 900) + 100, floor(random() * 9000) + 1000,  floor(random() * 9000) + 1000)
FROM generate_series(1, 100);

-- Insert random data into the appointments table
INSERT INTO appointments (doctor_id, patient_id, date_and_time, note)
SELECT
  (SELECT id FROM doctors ORDER BY random() LIMIT 1),
  (SELECT id FROM patients ORDER BY random() LIMIT 1),
  now() + (floor(random() * 30) || ' days')::interval + (floor(random() * 24) || ' hours')::interval,
  random_choice(ARRAY['Нет особых заметок', 'Контрольный приём', 'Необходимо лабораторное исследование', 'Подтверждение рецепта'])
FROM generate_series(1, 100);

-- Insert random data into the diagnoses table
INSERT INTO diagnoses (name, description)
SELECT
  random_choice(ARRAY['Гипертония', 'Диабет', 'Бронхит', 'Мигрень', 'Артрит', 'Пневмония', 'Депрессия', 'Тревожность', 'Аллергия', 'Гастрит', 'Бессонница', 'Остеопороз', 'Рак', 'Астма', 'Синусит']),
  random_choice(ARRAY['Описание диагноза 1', 'Описание диагноза 2', 'Описание диагноза 3', 'Описание диагноза 4', 'Описание диагноза 5'])
FROM generate_series(1, 100);

-- Insert random data into the test_results table
INSERT INTO test_results (appointment_id, diagnosis_id, result)
SELECT
  (SELECT id FROM appointments ORDER BY random() LIMIT 1),
  (SELECT id FROM diagnoses ORDER BY random() LIMIT 1),
  random_choice(ARRAY['Нормальный', 'Положительный', 'Отрицательный', 'Неоднозначный'])
FROM generate_series(1, 100);

# 6.Запросы к базе
1) Получить список всех врачей, у которых специализация "Кардиолог" и возраст младше 40 лет

SELECT * FROM doctors WHERE specialization = 'Кардиолог' AND age < 40;

2) Получить список пациентов, у которых контактный телефон начинается с кода "+79"

SELECT * FROM patients WHERE contact_phone LIKE '+79%';

3) Получить количество записей на приём для каждого врача

SELECT doctors.name, COUNT(appointments.id) AS appointment_count
FROM doctors
LEFT JOIN appointments ON doctors.id = appointments.doctor_id
GROUP BY doctors.id, doctors.name;

4) Получить список пациентов, у которых были назначены приёмы на следующей неделе

SELECT patients.*
FROM patients
INNER JOIN appointments ON patients.id = appointments.patient_id
WHERE appointments.date_and_time BETWEEN CURRENT_DATE + INTERVAL '1 week' AND CURRENT_DATE + INTERVAL '1 week' + INTERVAL '6 days';

5) Получить список диагнозов, у которых количество результатов обследований больше 10

SELECT DISTINCT diagnoses.name, COUNT(test_results.id) AS result_count
FROM diagnoses
INNER JOIN test_results ON diagnoses.id = test_results.diagnosis_id
GROUP BY diagnoses.id, diagnoses.name
HAVING COUNT(test_results.id) > 10;

6) Количество пациентов, принятых каждым специалистом

SELECT
  doctors.specialization,
  doctors.name AS doctor_name,
  COUNT(DISTINCT appointments.patient_id) AS total_patients_count
FROM
  doctors
LEFT JOIN
  appointments ON doctors.id = appointments.doctor_id
GROUP BY
  doctors.specialization, doctors.name
ORDER BY
  doctors.specialization, doctors.name;