---

-- ====================================================================
-- ЧАСТЬ 1: Очистка старых таблиц (Порядок важен из-за связей)
-- ====================================================================
IF OBJECT_ID('dbo.Рецепты', 'U') IS NOT NULL DROP TABLE dbo.Рецепты;
IF OBJECT_ID('dbo.Приемы', 'U') IS NOT NULL DROP TABLE dbo.Приемы;
IF OBJECT_ID('dbo.Пациенты', 'U') IS NOT NULL DROP TABLE dbo.Пациенты;
IF OBJECT_ID('dbo.Врачи', 'U') IS NOT NULL DROP TABLE dbo.Врачи;

-- ====================================================================
-- ЧАСТЬ 2: Создание структуры таблиц базы данных
-- ====================================================================

-- 1. Таблица "Врачи" (Медицинский персонал)
CREATE TABLE Врачи (
    Код_врача INT PRIMARY KEY,
    ФИО VARCHAR(100) NOT NULL,
    Специализация VARCHAR(50) NOT NULL,
    Категория VARCHAR(30),
    Оклад DECIMAL(10, 2),
    Телефон VARCHAR(20)
);

-- 2. Таблица "Пациенты" (Связь 1:* с Врачами — у каждого есть участковый/лечащий врач)
CREATE TABLE Пациенты (
    Код_пациента INT PRIMARY KEY,
    ФИО VARCHAR(100) NOT NULL,
    Дата_рождения DATE NOT NULL,
    Полис_ОМС VARCHAR(20) UNIQUE NOT NULL,
    Адрес VARCHAR(250),
    Телефон VARCHAR(20),
    Код_лечащего_врача INT,
    FOREIGN KEY (Код_лечащего_врача) REFERENCES Врачи(Код_врача)
);

-- 3. Таблица "Приемы" (Журнал посещений, связь 1:* с Пациентами)
CREATE TABLE Приемы (
    Код_приема INT PRIMARY KEY,
    Дата_и_время DATETIME2 NOT NULL,
    Жалобы VARCHAR(MAX),
    Диагноз VARCHAR(150),
    Стоимость_приема DECIMAL(10, 2) DEFAULT 0.00, -- Для платных услуг или хозрасчета
    Код_пациента INT,
    FOREIGN KEY (Код_пациента) REFERENCES Пациенты(Код_пациента)
);

-- 4. Таблица "Рецепты" (Назначенные лекарства, связь 1:* с Приемами)
CREATE TABLE Рецепты (
    Код_рецепта INT PRIMARY KEY,
    Название_препарата VARCHAR(100) NOT NULL,
    Дозировка VARCHAR(50),
    Срок_действия_дней INT DEFAULT 30,
    Код_приема INT,
    FOREIGN KEY (Код_приема) REFERENCES Приемы(Код_приема)
);

-- ====================================================================
-- ЧАСТЬ 3: Заполнение таблиц демонстрационной информацией
-- ====================================================================

-- Наполнение таблицы Врачи
INSERT INTO Врачи (Код_врача, ФИО, Специализация, Категория, Оклад, Telephone) VALUES 
(1, 'Петров Александр Сергеевич', 'Терапевт', 'Высшая', 65000.00, '+7 (999) 444-55-66'),
(2, 'Николаева Ольга Игоревна', 'Невролог', 'Первая', 70000.00, '+7 (999) 777-88-99'),
(3, 'Федоров Дмитрий Васильевич', 'Кардиолог', 'Высшая', 85000.00, '+7 (999) 111-33-22');

-- Наполнение таблицы Пациенты (с адресами, телефонами и полисами)
INSERT INTO Пациенты (Код_пациента, ФИО, Дата_рождения, Полис_ОМС, Адрес, Телефон, Код_лечащего_врача) VALUES 
(501, 'Иванова Мария Ивановна', '1978-05-12', '1111222233334444', 'г. Москва, ул. Ленина, д. 10, кв. 45', '+7 (903) 555-44-33', 1),
(502, 'Сидоров Кирилл Алексеевич', '1995-11-23', '5555666677778888', 'г. Москва, ул. Чехова, д. 4, кв. 12', '+7 (916) 123-88-99', 1),
(503, 'Васильева Анна Петровна', '1963-02-08', '9999888877776666', 'г. Новосибирск, ул. Кирова, д. 17', '+7 (923) 444-11-22', 2),
(504, 'Кузнецов Сергей Владимирович', '1988-08-30', '4444333322221111', 'г. Екатеринбург, ул. Мира, д. 2, кв. 9', '+7 (905) 777-11-00', 3);

-- Наполнение таблицы Приемы
INSERT INTO Приемы (Код_приема, Дата_и_время, Жалобы, Диагноз, Стоимость_приема, Код_пациента) VALUES 
(1001, '2026-09-01 09:30:00', 'Кашель, высокая температура', 'ОРИ', 0.00, 501),
(1002, '2026-09-02 11:00:00', 'Головная боль, бессонница', 'Мигрень', 1200.00, 503),
(1003, '2026-09-03 14:15:00', 'Боль в груди, одышка', 'Гипертония', 0.00, 504);

-- Наполнение таблицы Рецепты
INSERT INTO Рецепты (Код_рецепта, Название_препарата, Дозировка, Срок_действия_дней, Код_приема) VALUES 
(2001, 'Парацетамол', '500 мг, 3 раза в день', 10, 1001),
(2002, 'Ибупрофен', '400 мг при болях', 30, 1002),
(2003, 'Лизиноприл', '10 мг утром', 60, 1003);

-- ====================================================================
-- ЧАСТЬ 4: Итоговый отчетный запрос (Вывод всей информации)
-- ====================================================================
SELECT 
    П.ФИО AS [ФИО Пациента],
    П.Телефон AS [Телефон Пациента],
    П.Адрес AS [Адрес Пациента],
    П.Полис_ОМС AS [Полис ОМС],
    Пр.Диагноз AS [Установленный диагноз],
    Пр.Дата_и_время AS [Дата приема],
    В.ФИО AS [Принимающий Врач],
    В.Специализация AS [Специальность врача]
FROM Пациенты П
JOIN Приемы Пр ON П.Код_пациента = Пр.Код_пациента
JOIN Врачи В ON П.Код_лечащего_врача = В.Код_врача
ORDER BY П.ФИО;