-- =========================
DROP TABLE IF EXISTS consultations CASCADE;
DROP TABLE IF EXISTS complaints CASCADE;
DROP TABLE IF EXISTS prescriptions CASCADE;
DROP TABLE IF EXISTS medical_measurements CASCADE;
DROP TABLE IF EXISTS patient_diagnoses CASCADE;
DROP TABLE IF EXISTS patients CASCADE;
DROP TABLE IF EXISTS measurement_types CASCADE;
DROP TABLE IF EXISTS diagnosis_codes CASCADE;
DROP TABLE IF EXISTS doctors CASCADE;



-- =========================
-- 1. Справочники
-- =========================

-- Таблица врачей
CREATE TABLE doctors (
    id SERIAL PRIMARY KEY,               
    full_name TEXT NOT NULL,             
    specialization TEXT NOT NULL         
);

-- Справочник диагнозов (МКБ-10)
CREATE TABLE diagnosis_codes (
    id SERIAL PRIMARY KEY,               	
    icd10_code VARCHAR(10) UNIQUE NOT NULL, 
    name TEXT NOT NULL                   	
);

-- Справочник типов медицинских измерений
CREATE TABLE measurement_types (
    id SERIAL PRIMARY KEY,               
    name TEXT NOT NULL,                  
    unit TEXT NOT NULL,                  
    min_value NUMERIC,                   
    max_value NUMERIC                    
);

-- =========================
-- 2. Пациенты
-- =========================

-- Таблица пациентов
CREATE TABLE patients (
    id SERIAL PRIMARY KEY,               
    full_name TEXT NOT NULL,              
    birth_date DATE NOT NULL,             
    blood_type VARCHAR(3),                
    rh_factor CHAR(1) CHECK (rh_factor IN ('+', '-')),   
    phone TEXT,                           
    email TEXT                            
);

-- =========================
-- 3. Диагнозы пациентов
-- =========================

-- Таблица диагнозов пациентов
CREATE TABLE patient_diagnoses (
    id SERIAL PRIMARY KEY,               								
    patient_id INT NOT NULL REFERENCES patients(id) ON DELETE CASCADE, 
    diagnosis_id INT NOT NULL REFERENCES diagnosis_codes(id),          
    diagnosed_at DATE NOT NULL,           					
    doctor_id INT REFERENCES doctors(id)            		
);

-- =========================
-- 4. Медицинские измерения
-- =========================
-- Таблица медицинских измерений
-- Хранит значения показателей во времени (так называемые временные ряды, индекс для которых добавим ниже)
CREATE TABLE medical_measurements (
    id BIGSERIAL PRIMARY KEY,             								  
    patient_id INT NOT NULL REFERENCES patients(id) ON DELETE CASCADE,    
    measurement_type_id INT NOT NULL REFERENCES measurement_types(id),    
    value NUMERIC NOT NULL,               								  
    measured_at TIMESTAMP NOT NULL DEFAULT now(),						  

    CHECK (value >= 0)                    
);

-- Индекс для оптимизации запросов временных рядов
CREATE INDEX idx_measurements_patient_date
ON medical_measurements (patient_id, measured_at);

-- =========================
-- 5. Назначения
-- =========================

-- Таблица назначений лекарственных препаратов
CREATE TABLE prescriptions (
    id SERIAL PRIMARY KEY,               
    patient_id INT NOT NULL REFERENCES patients(id),  	
    doctor_id INT NOT NULL REFERENCES doctors(id), 		
    medication_name TEXT NOT NULL,        				
    dosage TEXT NOT NULL,                 				
    start_date DATE NOT NULL,             				
    end_date DATE                        				
);

-- =========================
-- 6. Жалобы пациентов
-- =========================

-- Таблица жалоб пациентов
CREATE TABLE complaints (
    id SERIAL PRIMARY KEY,               				
    patient_id INT NOT NULL REFERENCES patients(id),	
    description TEXT NOT NULL,            				
    created_at TIMESTAMP NOT NULL DEFAULT now()			
);

-- =========================
-- 7. Консультации
-- =========================
-- Таблица консультаций
-- Отражает факт визита пациента к врачу
CREATE TABLE consultations (
    id SERIAL PRIMARY KEY,               			 
    patient_id INT NOT NULL REFERENCES patients(id), 
    doctor_id INT NOT NULL REFERENCES doctors(id), 	 
    consultation_date TIMESTAMP NOT NULL,			 
    notes TEXT                          	 		 
);