67 lines
4.0 KiB
SQL
Executable File
67 lines
4.0 KiB
SQL
Executable File
-- Миграция: source и поля для ручной карточки заявки (диспетчерская)
|
||
-- Выполнить: psql -d mkd_control_center -f migrations/add_applications_manual_fields.sql
|
||
|
||
DO $$
|
||
BEGIN
|
||
-- source: 'doma' | 'manual'
|
||
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'applications' AND column_name = 'source') THEN
|
||
ALTER TABLE applications ADD COLUMN source VARCHAR(20) NOT NULL DEFAULT 'doma';
|
||
END IF;
|
||
|
||
-- Откуда поступила заявка (Звонок, Заявка с сайта и т.д.)
|
||
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'applications' AND column_name = 'source_channel') THEN
|
||
ALTER TABLE applications ADD COLUMN source_channel TEXT;
|
||
END IF;
|
||
|
||
-- Заявка от жителя (true) / не от жителя (false)
|
||
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'applications' AND column_name = 'is_from_resident') THEN
|
||
ALTER TABLE applications ADD COLUMN is_from_resident BOOLEAN DEFAULT true;
|
||
END IF;
|
||
|
||
-- Телефон, ФИО (для не от жителя)
|
||
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'applications' AND column_name = 'contact_phone') THEN
|
||
ALTER TABLE applications ADD COLUMN contact_phone TEXT;
|
||
END IF;
|
||
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'applications' AND column_name = 'contact_name') THEN
|
||
ALTER TABLE applications ADD COLUMN contact_name TEXT;
|
||
END IF;
|
||
|
||
-- Классификатор: место инцидента, тип работ, в чём проблема
|
||
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'applications' AND column_name = 'place_incident') THEN
|
||
ALTER TABLE applications ADD COLUMN place_incident TEXT;
|
||
END IF;
|
||
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'applications' AND column_name = 'work_type') THEN
|
||
ALTER TABLE applications ADD COLUMN work_type TEXT;
|
||
END IF;
|
||
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'applications' AND column_name = 'problem_detail') THEN
|
||
ALTER TABLE applications ADD COLUMN problem_detail TEXT;
|
||
END IF;
|
||
|
||
-- Чекбоксы: аварийная, платная, гарантийная
|
||
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'applications' AND column_name = 'is_emergency') THEN
|
||
ALTER TABLE applications ADD COLUMN is_emergency BOOLEAN DEFAULT false;
|
||
END IF;
|
||
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'applications' AND column_name = 'is_paid') THEN
|
||
ALTER TABLE applications ADD COLUMN is_paid BOOLEAN DEFAULT false;
|
||
END IF;
|
||
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'applications' AND column_name = 'is_warranty') THEN
|
||
ALTER TABLE applications ADD COLUMN is_warranty BOOLEAN DEFAULT false;
|
||
END IF;
|
||
|
||
-- Назначение: исполнитель, ответственный, наблюдатели
|
||
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'applications' AND column_name = 'executor_name') THEN
|
||
ALTER TABLE applications ADD COLUMN executor_name TEXT;
|
||
END IF;
|
||
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'applications' AND column_name = 'responsible_name') THEN
|
||
ALTER TABLE applications ADD COLUMN responsible_name TEXT;
|
||
END IF;
|
||
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'applications' AND column_name = 'observers_text') THEN
|
||
ALTER TABLE applications ADD COLUMN observers_text TEXT;
|
||
END IF;
|
||
|
||
-- Отображать заявку в мобильном приложении жителя
|
||
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'applications' AND column_name = 'show_in_app') THEN
|
||
ALTER TABLE applications ADD COLUMN show_in_app BOOLEAN DEFAULT false;
|
||
END IF;
|
||
END $$;
|