105 lines
5.5 KiB
SQL
105 lines
5.5 KiB
SQL
-- 0004_projects — проекты, объекты и мастера проекта.
|
||
--
|
||
-- Иерархия (см. README):
|
||
-- админ создаёт клиента
|
||
-- └── клиент создаёт проект
|
||
-- ├── объекты (здания, адреса) — входят в проект
|
||
-- └── мастера — добавлены в проект (мастер может быть в нескольких)
|
||
-- └── заявка привязана к объекту и достаётся мастеру проекта
|
||
--
|
||
-- Принцип прежний: принадлежность проверяется на уровне БД, а не только
|
||
-- в коде. Составные FK:
|
||
-- tickets (object_id, project_id) → project_objects (id, project_id)
|
||
-- «объект входит в этот проект»;
|
||
-- tickets (project_id, client_id) → projects (id, client_id)
|
||
-- «проект принадлежит клиенту заявки».
|
||
-- Членство мастера в проекте — не FK, а проверка внутри UPDATE назначения:
|
||
-- FK отдалял бы уход мастера из проекта навсегда (ссылки остаются от
|
||
-- закрытых заявок), а «назначить чужого» не даёт ту же строку WHERE.
|
||
--
|
||
-- Применённый файл не редактировать (checksum в schema_migrations).
|
||
|
||
-- Существующие заявки сделаны до появления объектов и не имеют object_id.
|
||
-- В базе они тестовые (решение принято), удаляем — иначе NOT NULL не ввести.
|
||
DELETE FROM tickets;
|
||
|
||
-- ============ projects ============
|
||
|
||
CREATE TABLE IF NOT EXISTS projects (
|
||
id bigserial PRIMARY KEY,
|
||
client_id uuid NOT NULL REFERENCES profiles (id),
|
||
name text NOT NULL,
|
||
is_active boolean NOT NULL DEFAULT true,
|
||
created_at timestamptz NOT NULL DEFAULT now()
|
||
);
|
||
|
||
-- Один клиент не держит два проекта с одинаковым именем.
|
||
ALTER TABLE projects DROP CONSTRAINT IF EXISTS projects_client_name_key;
|
||
ALTER TABLE projects ADD CONSTRAINT projects_client_name_key UNIQUE (client_id, name);
|
||
|
||
-- Цель для составного FK из tickets (project_id, client_id).
|
||
ALTER TABLE projects DROP CONSTRAINT IF EXISTS projects_id_client_key;
|
||
ALTER TABLE projects ADD CONSTRAINT projects_id_client_key UNIQUE (id, client_id);
|
||
|
||
CREATE INDEX IF NOT EXISTS projects_client_idx ON projects (client_id);
|
||
|
||
-- ============ project_objects ============
|
||
|
||
CREATE TABLE IF NOT EXISTS project_objects (
|
||
id bigserial PRIMARY KEY,
|
||
project_id bigint NOT NULL REFERENCES projects (id) ON DELETE CASCADE,
|
||
name text NOT NULL,
|
||
address text NOT NULL,
|
||
is_active boolean NOT NULL DEFAULT true,
|
||
created_at timestamptz NOT NULL DEFAULT now()
|
||
);
|
||
|
||
ALTER TABLE project_objects DROP CONSTRAINT IF EXISTS project_objects_project_name_key;
|
||
ALTER TABLE project_objects ADD CONSTRAINT project_objects_project_name_key UNIQUE (project_id, name);
|
||
|
||
-- Цель для составного FK из tickets (object_id, project_id).
|
||
ALTER TABLE project_objects DROP CONSTRAINT IF EXISTS project_objects_id_project_key;
|
||
ALTER TABLE project_objects ADD CONSTRAINT project_objects_id_project_key UNIQUE (id, project_id);
|
||
|
||
CREATE INDEX IF NOT EXISTS project_objects_project_idx ON project_objects (project_id);
|
||
|
||
-- ============ project_workers ============
|
||
|
||
-- Мастер ↔ проект: многие-ко-многим (мастер работает на разных объектах
|
||
-- и в разных проектах одного клиента).
|
||
CREATE TABLE IF NOT EXISTS project_workers (
|
||
worker_id uuid NOT NULL REFERENCES profiles (id),
|
||
project_id bigint NOT NULL REFERENCES projects (id) ON DELETE CASCADE,
|
||
added_by uuid REFERENCES profiles (id),
|
||
created_at timestamptz NOT NULL DEFAULT now(),
|
||
PRIMARY KEY (worker_id, project_id)
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS project_workers_project_idx ON project_workers (project_id);
|
||
|
||
-- ============ tickets ============
|
||
|
||
ALTER TABLE tickets ADD COLUMN IF NOT EXISTS project_id bigint;
|
||
ALTER TABLE tickets ADD COLUMN IF NOT EXISTS object_id bigint;
|
||
|
||
-- Заявка обязана жить в объекте: адрес копируется из объекта на момент
|
||
-- создания (история работ показывает адрес, который был тогда).
|
||
-- Таблица к этому моменту пуста — SET NOT NULL безопасен.
|
||
ALTER TABLE tickets ALTER COLUMN project_id SET NOT NULL;
|
||
ALTER TABLE tickets ALTER COLUMN object_id SET NOT NULL;
|
||
|
||
ALTER TABLE tickets DROP CONSTRAINT IF EXISTS tickets_object_project_fk;
|
||
ALTER TABLE tickets ADD CONSTRAINT tickets_object_project_fk
|
||
FOREIGN KEY (object_id, project_id)
|
||
REFERENCES project_objects (id, project_id);
|
||
|
||
ALTER TABLE tickets DROP CONSTRAINT IF EXISTS tickets_project_client_fk;
|
||
ALTER TABLE tickets ADD CONSTRAINT tickets_project_client_fk
|
||
FOREIGN KEY (project_id, client_id)
|
||
REFERENCES projects (id, client_id);
|
||
|
||
-- Членство мастера: проверяется в UPDATE назначения
|
||
-- (repository.TicketRepo.Assign), см. комментарий в шапке файла.
|
||
CREATE INDEX IF NOT EXISTS idx_tickets_project ON tickets (project_id);
|
||
CREATE INDEX IF NOT EXISTS idx_tickets_object ON tickets (object_id);
|