SQLize | PHPize | SQLtest

-- 1. Таблица пользователей CREATE TABLE users ( id SERIAL PRIMARY KEY, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, username VARCHAR(50) UNIQUE NOT NULL, email VARCHAR(100) UNIQUE, phone VARCHAR(20) UNIQUE, password_hash VARCHAR(255) NOT NULL, role VARCHAR(20) NOT NULL, is_active BOOLEAN DEFAULT TRUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Индексы для users CREATE INDEX idx_users_role ON users(role); CREATE INDEX idx_users_active ON users(is_active) WHERE is_active = TRUE; CREATE INDEX idx_users_name ON users(last_name, first_name); -- 2. Таблица специализаций CREATE TABLE specializations ( id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL UNIQUE, description TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Индекс для поиска по названию специализации CREATE INDEX idx_specializations_name ON specializations(name); -- 3. Таблица врачей CREATE TABLE doctors ( id SERIAL PRIMARY KEY, user_id INTEGER UNIQUE NOT NULL REFERENCES users(id) ON DELETE CASCADE, specialization_id INTEGER REFERENCES specializations(id), license_number VARCHAR(50), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Индексы для doctors CREATE INDEX idx_doctors_user_id ON doctors(user_id); CREATE INDEX idx_doctors_specialization_id ON doctors(specialization_id); CREATE INDEX idx_doctors_license ON doctors(license_number) WHERE license_number IS NOT NULL; -- 4. Таблица клиентов CREATE TABLE clients ( id SERIAL PRIMARY KEY, user_id INTEGER UNIQUE NOT NULL REFERENCES users(id) ON DELETE CASCADE, birth_date DATE, gender VARCHAR(10), address TEXT, insurance_number VARCHAR(50), registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, last_visit_date TIMESTAMP, notes TEXT, is_vip BOOLEAN DEFAULT FALSE, discount_rate INTEGER DEFAULT 0 CHECK (discount_rate BETWEEN 0 AND 100) ); -- Индексы для clients CREATE INDEX idx_clients_user_id ON clients(user_id); CREATE INDEX idx_clients_birth_date ON clients(birth_date); CREATE INDEX idx_clients_last_visit ON clients(last_visit_date) WHERE last_visit_date IS NOT NULL; CREATE INDEX idx_clients_vip ON clients(is_vip) WHERE is_vip = TRUE; CREATE INDEX idx_clients_insurance ON clients(insurance_number) WHERE insurance_number IS NOT NULL; -- 5. Таблица графика работы врачей CREATE TABLE doctor_schedules ( id SERIAL PRIMARY KEY, doctor_id INTEGER NOT NULL REFERENCES doctors(id) ON DELETE CASCADE, day_of_week SMALLINT NOT NULL CHECK (day_of_week BETWEEN 1 AND 7), start_time TIME NOT NULL, end_time TIME NOT NULL, is_recurring BOOLEAN DEFAULT TRUE, valid_from DATE NOT NULL DEFAULT CURRENT_DATE, valid_to DATE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, CONSTRAINT valid_time_range CHECK (end_time > start_time), UNIQUE (doctor_id, day_of_week, valid_from) ); -- Индексы для doctor_schedules CREATE INDEX idx_schedules_doctor ON doctor_schedules(doctor_id); CREATE INDEX idx_schedules_current ON doctor_schedules(doctor_id, day_of_week) WHERE is_recurring = TRUE AND (valid_to IS NULL OR valid_to >= CURRENT_DATE); CREATE INDEX idx_schedules_date_range ON doctor_schedules(valid_from, valid_to); -- 6. Таблица исключений в графике CREATE TABLE schedule_exceptions ( id SERIAL PRIMARY KEY, doctor_id INTEGER NOT NULL REFERENCES doctors(id) ON DELETE CASCADE, exception_date DATE NOT NULL, is_working_day BOOLEAN NOT NULL, start_time TIME, end_time TIME, reason TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, CONSTRAINT valid_exception_times CHECK ( (is_working_day = FALSE AND start_time IS NULL AND end_time IS NULL) OR (is_working_day = TRUE AND start_time IS NOT NULL AND end_time IS NOT NULL AND end_time > start_time) ), UNIQUE (doctor_id, exception_date) ); -- Индексы для schedule_exceptions CREATE INDEX idx_exceptions_doctor ON schedule_exceptions(doctor_id); CREATE INDEX idx_exceptions_date ON schedule_exceptions(exception_date); -- 7. Таблица временных слотов CREATE TABLE time_slots ( id SERIAL PRIMARY KEY, doctor_id INTEGER NOT NULL REFERENCES doctors(id) ON DELETE CASCADE, date DATE NOT NULL, start_time TIMESTAMP NOT NULL, end_time TIMESTAMP NOT NULL, status VARCHAR(20) NOT NULL DEFAULT 'available' CHECK (status IN ('available', 'booked', 'completed', 'cancelled')), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, CONSTRAINT valid_slot_time CHECK (end_time > start_time), UNIQUE (doctor_id, start_time) ); -- Индексы для time_slots CREATE INDEX idx_slots_doctor ON time_slots(doctor_id); CREATE INDEX idx_slots_date ON time_slots(date); CREATE INDEX idx_slots_status ON time_slots(status); CREATE INDEX idx_slots_available ON time_slots(doctor_id, start_time) WHERE status = 'available' AND start_time > CURRENT_TIMESTAMP; -- 8. Таблица записей на прием CREATE TABLE appointments ( id SERIAL PRIMARY KEY, slot_id INTEGER NOT NULL REFERENCES time_slots(id) ON DELETE CASCADE, client_id INTEGER NOT NULL REFERENCES clients(id) ON DELETE CASCADE, status VARCHAR(20) NOT NULL DEFAULT 'scheduled' CHECK (status IN ('scheduled', 'completed', 'cancelled', 'no_show')), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, notes TEXT, UNIQUE (slot_id) ); -- Индексы для appointments CREATE INDEX idx_appointments_client ON appointments(client_id); CREATE INDEX idx_appointments_status ON appointments(status); CREATE INDEX idx_appointments_slot ON appointments(slot_id); -- 9. Таблица отмененных приемов (форс-мажоры) CREATE TABLE cancellations ( id SERIAL PRIMARY KEY, appointment_id INTEGER REFERENCES appointments(id) ON DELETE SET NULL, doctor_id INTEGER NOT NULL REFERENCES doctors(id) ON DELETE CASCADE, cancellation_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, reason TEXT NOT NULL, is_compensated BOOLEAN DEFAULT FALSE, compensation_details TEXT ); -- Индексы для cancellations CREATE INDEX idx_cancellations_doctor ON cancellations(doctor_id); CREATE INDEX idx_cancellations_time ON cancellations(cancellation_time); CREATE INDEX idx_cancellations_compensated ON cancellations(is_compensated) WHERE is_compensated = FALSE; -- Триггеры для автоматического обновления временных меток CREATE OR REPLACE FUNCTION update_timestamp() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at = CURRENT_TIMESTAMP; RETURN NEW; END; $$ LANGUAGE plpgsql; -- Применяем триггеры к таблицам CREATE TRIGGER users_update_timestamp BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION update_timestamp(); CREATE TRIGGER doctors_update_timestamp BEFORE UPDATE ON doctors FOR EACH ROW EXECUTE FUNCTION update_timestamp(); CREATE TRIGGER appointments_update_timestamp BEFORE UPDATE ON appointments FOR EACH ROW EXECUTE FUNCTION update_timestamp(); CREATE TRIGGER schedules_update_timestamp BEFORE UPDATE ON doctor_schedules FOR EACH ROW EXECUTE FUNCTION update_timestamp(); -- Триггер для обновления last_visit_date у клиента CREATE OR REPLACE FUNCTION update_client_visit() RETURNS TRIGGER AS $$ BEGIN IF NEW.status = 'completed' THEN UPDATE clients SET last_visit_date = CURRENT_TIMESTAMP WHERE id = NEW.client_id; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER appointments_update_visit AFTER UPDATE ON appointments FOR EACH ROW EXECUTE FUNCTION update_client_visit();