41 lines
2.3 KiB
SQL
Executable File
41 lines
2.3 KiB
SQL
Executable File
-- ========= NPS ОПРОСЫ =========
|
||
-- Создание таблиц для опросов NPS
|
||
|
||
-- ========= ОПРОСЫ NPS =========
|
||
CREATE TABLE IF NOT EXISTS nps_surveys (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
building_id VARCHAR(50) NOT NULL REFERENCES buildings(id) ON DELETE CASCADE,
|
||
title TEXT NOT NULL DEFAULT 'Опрос удовлетворенности',
|
||
description TEXT,
|
||
status VARCHAR(20) NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'active', 'closed')),
|
||
access_key TEXT NOT NULL UNIQUE, -- Уникальный ключ доступа для ссылки
|
||
published_at TIMESTAMPTZ,
|
||
expires_at TIMESTAMPTZ, -- Дата окончания опроса
|
||
created_by TEXT NOT NULL,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_nps_surveys_building ON nps_surveys(building_id);
|
||
CREATE INDEX IF NOT EXISTS idx_nps_surveys_status ON nps_surveys(status);
|
||
CREATE INDEX IF NOT EXISTS idx_nps_surveys_access_key ON nps_surveys(access_key);
|
||
|
||
-- ========= ОТВЕТЫ НА ОПРОСЫ NPS =========
|
||
CREATE TABLE IF NOT EXISTS nps_responses (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
survey_id BIGINT NOT NULL REFERENCES nps_surveys(id) ON DELETE CASCADE,
|
||
building_id VARCHAR(50) NOT NULL REFERENCES buildings(id) ON DELETE CASCADE,
|
||
score INTEGER NOT NULL CHECK (score >= 0 AND score <= 10), -- Оценка от 0 до 10
|
||
comment TEXT, -- Комментарий (опционально)
|
||
respondent_name TEXT, -- Имя респондента (опционально)
|
||
apartment TEXT, -- Квартира (опционально)
|
||
phone TEXT, -- Телефон (опционально)
|
||
email TEXT, -- Email (опционально)
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_nps_responses_survey ON nps_responses(survey_id);
|
||
CREATE INDEX IF NOT EXISTS idx_nps_responses_building ON nps_responses(building_id);
|
||
CREATE INDEX IF NOT EXISTS idx_nps_responses_score ON nps_responses(score);
|
||
CREATE INDEX IF NOT EXISTS idx_nps_responses_created ON nps_responses(created_at DESC);
|