---

-- Создание базы данных медицинского центра
CREATE DATABASE IF NOT EXISTS health;
USE health;

-- Удаление таблиц, если они уже существовали (для предотвращения конфликтов при перезапуске)
DROP TABLE IF EXISTS оказанная_услуга;
DROP TABLE IF EXISTS запись_на_прием;
DROP TABLE IF EXISTS бригада;
DROP TABLE IF EXISTS трудовой_договор;
DROP TABLE IF EXISTS врач;
DROP TABLE IF EXISTS медицинская_услуга;
DROP TABLE IF EXISTS пациент;

-- 1. Таблица врачей
CREATE TABLE врач (
    id_врач INT AUTO_INCREMENT PRIMARY KEY,
    номер_паспорта VARCHAR(20) NOT NULL,
    фамилия VARCHAR(50) NOT NULL,
    имя VARCHAR(50) NOT NULL,
    отчество VARCHAR(50),
    адрес VARCHAR(255),
    телефон VARCHAR(20)
);

-- 2. Таблица трудовых договоров (Связь 1:1 к врачам)
CREATE TABLE трудовой_договор (
    id_договора INT AUTO_INCREMENT PRIMARY KEY,
    id_врач INT UNIQUE NOT NULL,
    номер_договора VARCHAR(50) NOT NULL,
    дата_заключения DATE NOT NULL,
    срок_договора_лет DECIMAL(4,2) NOT NULL,
    договор_расторгнут BOOLEAN DEFAULT FALSE,
    дата_расторжения DATE DEFAULT NULL,
    FOREIGN KEY (id_врач) REFERENCES врач(id_врач) ON DELETE CASCADE
);

-- 3. Таблица медицинских услуг
CREATE TABLE медицинская_услуга (
    id_услуги INT AUTO_INCREMENT PRIMARY KEY,
    название VARCHAR(150) NOT NULL,
    продолжительность_мин INT NOT NULL,
    себестоимость_руб DECIMAL(10,2) NOT NULL,
    цена_для_пациента_руб DECIMAL(10,2) NOT NULL,
    зарплатный_фонд_руб DECIMAL(10,2) NOT NULL
);

-- 4. Таблица пациентов
CREATE TABLE пациент (
    id_пациента INT AUTO_INCREMENT PRIMARY KEY,
    фамилия VARCHAR(50) NOT NULL,
    имя VARCHAR(50) NOT NULL,
    отчество VARCHAR(50),
    адрес VARCHAR(255),
    телефон VARCHAR(20),
    дата_рождения DATE,
    лис_омс_дмс VARCHAR(50) NOT NULL
);

-- 5. Таблица записей на прием
CREATE TABLE запись_на_прием (
    id_записи INT AUTO_INCREMENT PRIMARY KEY,
    номер_записи VARCHAR(50) NOT NULL,
    id_пациента INT NOT NULL,
    id_услуги INT NOT NULL,
    дата_создания_записи DATETIME NOT NULL,
    дата_и_время_приема DATETIME NOT NULL,
    статус_выполнения BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (id_пациента) REFERENCES пациент(id_пациента) ON DELETE CASCADE,
    FOREIGN KEY (id_услуги) REFERENCES медицинская_услуга(id_услуги) ON DELETE CASCADE
);

-- 6. Таблица оказанных услуг (для фиксации факта выполнения и расчета зарплат)
CREATE TABLE оказанная_услуга (
    id_оказания INT AUTO_INCREMENT PRIMARY KEY,
    id_записи INT NOT NULL,
    id_услуги INT NOT NULL,
    фактическая_дата DATE NOT NULL,
    зарплата_начислена BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (id_записи) REFERENCES запись_на_прием(id_записи) ON DELETE CASCADE,
    FOREIGN KEY (id_услуги) REFERENCES медицинская_услуга(id_услуги) ON DELETE CASCADE
);

-- 7. Таблица бригад (Связь многие-ко-многим для врачей и услуг)
CREATE TABLE бригада (
    id_бригады INT AUTO_INCREMENT PRIMARY KEY,
    id_услуги INT NOT NULL,
    id_врач INT NOT NULL,
    FOREIGN KEY (id_услуги) REFERENCES медицинская_услуга(id_услуги) ON DELETE CASCADE,
    FOREIGN KEY (id_врач) REFERENCES врач(id_врач) ON DELETE CASCADE
);

-- Наполнение таблиц демонстрационными данными
INSERT INTO врач (id_врач, номер_паспорта, фамилия, имя, отчество, адрес, телефон) VALUES
(1, '4501123456', 'Иванов', 'Иван', 'Иванович', 'г. Москва, ул. Ленина, д. 10, кв. 5', '+74951234567'),
(2, '4502234567', 'Петрова', 'Мария', 'Сергеевна', 'г. Москва, ул. Пушкина, д. 25, кв. 12', '+74952345678'),
(3, '4503345678', 'Сидоров', 'Алексей', 'Владимирович', 'г. Москва, ул. Гагарина, д. 8, кв. 3', '+74953456789'),
(4, '4504456789', 'Козлова', 'Елена', 'Дмитриевна', 'г. Москва, ул. Тверская, д. 15, кв. 45', '+74954567890'),
(5, '4505567890', 'Михайлов', 'Андрей', 'Петрович', 'г. Москва, ул. Арбат, д. 7, кв. 21', '+74955678901');

INSERT INTO трудовой_договор (id_договора, id_врач, номер_договора, дата_заключения, срок_договора_лет, договор_расторгнут, дата_расторжения) VALUES
(1, 1, 'ТД-2023-001', '2023-01-10', 3.00, 0, NULL),
(2, 2, 'ТД-2023-002', '2023-02-15', 2.00, 0, NULL),
(3, 3, 'ТД-2022-003', '2022-11-20', 5.00, 0, NULL),
(4, 4, 'ТД-2024-001', '2024-01-05', 1.50, 0, NULL),
(5, 5, 'ТД-2021-005', '2021-06-01', 3.00, 1, '2024-06-01');

INSERT INTO медицинская_услуга (id_услуги, название, продолжительность_мин, себестоимость_руб, цена_для_пациента_руб, зарплатный_фонд_руб) VALUES
(1, 'Прием терапевта', 30, 150.00, 1500.00, 450.00),
(2, 'УЗИ брюшной полости', 40, 300.00, 2500.00, 750.00),
(3, 'ЭКГ', 20, 100.00, 800.00, 240.00),
(4, 'МРТ головного мозга', 60, 1500.00, 5000.00, 1500.00),
(5, 'Анализ крови общий', 15, 200.00, 600.00, 180.00),
(6, 'Хирургическая операция', 120, 5000.00, 20000.00, 6000.00),
(7, 'Массаж лечебный', 45, 100.00, 1200.00, 360.00);

INSERT INTO пациент (id_пациента, фамилия, имя, отчество, адрес, телефон, дата_рождения, лис_омс_дмс) VALUES
(1, 'Смирнов', 'Олег', 'Владимирович', 'г. Москва, ул. Лесная, д. 5, кв. 18', '+79161234567', '1985-03-15', 'ОМС-1234567890'),
(2, 'Васильева', 'Анна', 'Игоревна', 'г. Москва, ул. Цветная, д. 12, кв. 7', '+79162345678', '1990-07-22', 'ДМС-9876543210'),
(3, 'Новиков', 'Дмитрий', 'Павлович', 'г. Москва, ул. Садовая, д. 3, кв. 45', '+79163456789', '1978-11-05', 'ОМС-5555555555'),
(4, 'Федорова', 'Екатерина', 'Андреевна', 'г. Москва, ул. Мира, д. 20, кв. 9', '+79164567890', '1995-01-30', 'ДМС-4444444444'),
(5, 'Морозов', 'Илья', 'Сергеевич', 'г. Москва, ул. Верхняя, д. 8, кв. 32', '+79165678901', '1982-09-17', 'ОМС-3333333333');

INSERT INTO запись_на_прием (id_записи, номер_записи, id_пациента, id_услуги, дата_создания_записи, дата_и_время_приема, статус_выполнения) VALUES
(1, 'Z-2024-001', 1, 1, '2024-11-01 10:00:00', '2024-11-05 09:00:00', 1),
(2, 'Z-2024-002', 2, 2, '2024-11-02 11:30:00', '2024-11-06 14:00:00', 1),
(3, 'Z-2024-003', 3, 4, '2024-11-03 09:15:00', '2024-11-07 10:00:00', 0),
(4, 'Z-2024-004', 1, 6, '2024-11-04 14:00:00', '2024-11-10 08:00:00', 0),
(5, 'Z-2024-005', 4, 3, '2024-11-05 16:45:00', '2024-11-08 11:30:00', 1),
(6, 'Z-2024-006', 5, 5, '2024-11-06 12:00:00', '2024-11-09 09:30:00', 0),
(7, 'Z-2024-007', 2, 7, '2024-11-07 10:30:00', '2024-11-12 15:00:00', 0);

INSERT INTO оказанная_услуга (id_оказания, id_записи, id_услуги, фактическая_дата, зарплата_начислена) VALUES
(1, 1, 1, '2024-11-05', 1),
(2, 2, 2, '2024-11-06', 0),
(3, 5, 3, '2024-11-08', 0);

INSERT INTO бригада (id_бригады, id_услуги, id_врач) VALUES
(1, 1, 1),
(2, 2, 2),
(3, 2, 4),
(4, 3, 3),
(5, 4, 2),
(6, 4, 5),
(7, 5, 3),
(8, 6, 1),
(9, 6, 2),
(10, 6, 5),
(11, 7, 4);