Roman Chesnokov
49 lines
1.6 KiB
SQL
49 lines
1.6 KiB
SQL
-- 002_core_employee.sql — core.office, core.department, core.employee, history
|
|
|
|
CREATE TABLE core.office (
|
|
id bigserial PRIMARY KEY,
|
|
name text NOT NULL,
|
|
bitra_id text UNIQUE
|
|
);
|
|
|
|
CREATE TABLE core.department (
|
|
id bigserial PRIMARY KEY,
|
|
name text NOT NULL,
|
|
bitra_id text UNIQUE,
|
|
bitrix_id bigint UNIQUE,
|
|
parent_id bigint REFERENCES core.department,
|
|
source text NOT NULL DEFAULT 'bitra' -- bitra | bitrix
|
|
);
|
|
|
|
CREATE TABLE core.employee (
|
|
id bigserial PRIMARY KEY,
|
|
email text UNIQUE NOT NULL,
|
|
full_name text,
|
|
first_name text,
|
|
last_name text,
|
|
bitra_user_id text UNIQUE,
|
|
eva_person_id text UNIQUE,
|
|
bitrix_user_id bigint UNIQUE,
|
|
rate decimal(10,2),
|
|
office_id bigint REFERENCES core.office,
|
|
department_id bigint REFERENCES core.department,
|
|
is_active boolean DEFAULT true,
|
|
is_target_for_mvp1 boolean DEFAULT false,
|
|
last_synced timestamptz
|
|
);
|
|
|
|
CREATE INDEX idx_employee_email ON core.employee (lower(email));
|
|
CREATE INDEX idx_employee_bitra_id ON core.employee (bitra_user_id);
|
|
CREATE INDEX idx_employee_eva_id ON core.employee (eva_person_id);
|
|
CREATE INDEX idx_employee_bitrix_id ON core.employee (bitrix_user_id);
|
|
CREATE INDEX idx_employee_target_mvp1 ON core.employee (is_target_for_mvp1) WHERE is_target_for_mvp1 = true;
|
|
|
|
CREATE TABLE core.department_history (
|
|
employee_id bigint REFERENCES core.employee,
|
|
valid_from date NOT NULL,
|
|
valid_to date,
|
|
department_id bigint REFERENCES core.department,
|
|
source text NOT NULL DEFAULT 'bitra',
|
|
PRIMARY KEY (employee_id, valid_from)
|
|
);
|