45 lines
1.6 KiB
SQL
45 lines
1.6 KiB
SQL
CREATE TABLE IF NOT EXISTS service_departments (
|
|
id uuid PRIMARY KEY,
|
|
name text NOT NULL,
|
|
code text NOT NULL UNIQUE,
|
|
phone text,
|
|
parent_id uuid REFERENCES service_departments(id) ON DELETE SET NULL,
|
|
region_id uuid REFERENCES regions(id) ON DELETE SET NULL,
|
|
description text,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS service_departments_name_idx
|
|
ON service_departments (name);
|
|
|
|
ALTER TABLE IF EXISTS service_departments
|
|
ADD COLUMN IF NOT EXISTS parent_id uuid REFERENCES service_departments(id) ON DELETE SET NULL;
|
|
|
|
ALTER TABLE IF EXISTS service_departments
|
|
ADD COLUMN IF NOT EXISTS phone text;
|
|
|
|
CREATE INDEX IF NOT EXISTS service_departments_parent_idx
|
|
ON service_departments (parent_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS service_department_permits (
|
|
department_id uuid NOT NULL REFERENCES service_departments(id) ON DELETE CASCADE,
|
|
region_id uuid NOT NULL REFERENCES regions(id) ON DELETE CASCADE,
|
|
permit_id uuid NOT NULL REFERENCES permits(id) ON DELETE CASCADE,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
created_by text,
|
|
PRIMARY KEY (department_id, region_id, permit_id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS service_dept_permits_region_idx
|
|
ON service_department_permits (region_id, permit_id);
|
|
|
|
ALTER TABLE IF EXISTS auth_users
|
|
ADD COLUMN IF NOT EXISTS service_department_id uuid REFERENCES service_departments(id);
|
|
|
|
ALTER TABLE IF EXISTS auth_users
|
|
ADD COLUMN IF NOT EXISTS department_role text;
|
|
|
|
CREATE INDEX IF NOT EXISTS auth_users_service_department_idx
|
|
ON auth_users (service_department_id);
|