---

 CREATE TABLE departments (
    department_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE doctors (
    doctor_id INT PRIMARY KEY AUTO_INCREMENT,
    full_name VARCHAR(100) NOT NULL,
    specialization VARCHAR(50) NOT NULL,
    department_id INT NOT NULL,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

CREATE TABLE patients (
    patient_id INT PRIMARY KEY AUTO_INCREMENT,
    full_name VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NOT NULL
);

CREATE TABLE tickets (
    ticket_id INT PRIMARY KEY AUTO_INCREMENT,
    doctor_id INT NOT NULL,
    patient_id INT,
    appointment_datetime DATETIME NOT NULL,
    room_number VARCHAR(10) NOT NULL,
    FOREIGN KEY (doctor_id) REFERENCES doctors(doctor_id),
    FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
);

CREATE TABLE medical_conclusions (
    conclusion_id INT PRIMARY KEY AUTO_INCREMENT,
    ticket_id INT NOT NULL UNIQUE,
    complaints TEXT,
    diagnosis VARCHAR(150) NOT NULL,
    recommendations TEXT,
    FOREIGN KEY (ticket_id) REFERENCES tickets(ticket_id)
);

INSERT INTO departments (name) VALUES ('Терапия', 'Хирургия', 'Офтальмология', 'Оториноларингология', 'УЗИ-диагностика');
INSERT INTO doctors (full_name, specialization, department_id) VALUES 
('Борисов В.В.', 'Терапевт', 1), ('Григорьев Н.А.', 'Хирург', 2), ('Дмитриева Е.Е.', 'Офтальмолог', 3), ('Елисеев С.П.', 'ЛОР', 4), ('Зимина И.В.', 'Врач УЗИ', 5);
INSERT INTO patients (full_name, phone) VALUES ('Акимов К.С.', '89991112233'), ('Быков М.А.', '89992223344'), ('Васин Т.Р.', '89993334455'), ('Глебов Д.Е.', '89994445566'), ('Денисов Ю.В.', '89995556677');
INSERT INTO tickets (doctor_id, patient_id, appointment_datetime, room_number) VALUES 
(1, 1, '2026-10-12 09:00:00', '101'), (2, 2, '2026-10-12 10:00:00', '204'), (3, 3, '2026-10-12 11:00:00', '302'), (4, 4, '2026-10-13 09:30:00', '410'), (5, 5, '2026-10-13 14:00:00', '105');
INSERT INTO medical_conclusions (ticket_id, complaints, diagnosis, recommendations) VALUES 
(1, 'Кашель, температура', 'Бронхит', 'Обильное питье, антибиотики'), (2, 'Боль в колене', 'Артрит', 'Мазь, покой'), (3, 'Снижение зрения', 'Близорукость', 'Ношение очков'), (4, 'Боль в ухе', 'Отит', 'Ушные капли'), (5, 'Боль в боку', 'Дискинезия ЖВП', 'Диета №5');

-- Запросы
SELECT * FROM tickets WHERE appointment_datetime LIKE '2026-10-12%';
SELECT t.ticket_id, p.full_name AS patient, d.full_name AS doctor, mc.diagnosis FROM tickets t JOIN patients p ON t.patient_id = p.patient_id JOIN doctors d ON t.doctor_id = d.doctor_id JOIN medical_conclusions mc ON mc.ticket_id = t.ticket_id;
SELECT doctor_id, COUNT(*) AS booked_tickets FROM tickets WHERE patient_id IS NOT NULL GROUP BY doctor_id;
SELECT full_name FROM patients WHERE patient_id IN (SELECT patient_id FROM tickets WHERE room_number = '101');
UPDATE tickets SET room_number = '101-А' WHERE doctor_id = 1;