72 lines
2.9 KiB
SQL
72 lines
2.9 KiB
SQL
-- Расписание и запись из 1С: кэш справочников и окон, журнал синхронизации и записей с сайта.
|
||
|
||
CREATE TABLE onec_branches (
|
||
onec_id text PRIMARY KEY,
|
||
name text NOT NULL DEFAULT '',
|
||
address text NOT NULL DEFAULT '',
|
||
seen_at timestamptz NOT NULL DEFAULT now()
|
||
);
|
||
|
||
CREATE TABLE onec_doctors (
|
||
onec_id text PRIMARY KEY,
|
||
full_name text NOT NULL DEFAULT '',
|
||
specialties text[] NOT NULL DEFAULT '{}',
|
||
branch_ids text[] NOT NULL DEFAULT '{}',
|
||
kids_from int,
|
||
active boolean NOT NULL DEFAULT true,
|
||
doctor_id bigint UNIQUE REFERENCES doctors (id) ON DELETE SET NULL,
|
||
seen_at timestamptz NOT NULL DEFAULT now()
|
||
);
|
||
|
||
CREATE TABLE onec_services (
|
||
doctor_onec_id text NOT NULL REFERENCES onec_doctors (onec_id) ON DELETE CASCADE,
|
||
onec_id text NOT NULL,
|
||
name text NOT NULL DEFAULT '',
|
||
duration_min int NOT NULL DEFAULT 0,
|
||
price numeric(12, 2) NOT NULL DEFAULT 0,
|
||
PRIMARY KEY (doctor_onec_id, onec_id)
|
||
);
|
||
|
||
CREATE TABLE onec_slots (
|
||
doctor_onec_id text NOT NULL,
|
||
branch_onec_id text NOT NULL DEFAULT '',
|
||
start_at timestamptz NOT NULL,
|
||
end_at timestamptz NOT NULL,
|
||
cabinet text NOT NULL DEFAULT '',
|
||
status text NOT NULL CHECK (status IN ('free', 'busy')),
|
||
slot_id text NOT NULL DEFAULT '',
|
||
PRIMARY KEY (doctor_onec_id, branch_onec_id, start_at)
|
||
);
|
||
|
||
CREATE INDEX onec_slots_start_idx ON onec_slots (start_at);
|
||
|
||
CREATE TABLE onec_sync_log (
|
||
id bigserial PRIMARY KEY,
|
||
started_at timestamptz NOT NULL DEFAULT now(),
|
||
finished_at timestamptz,
|
||
doctors int NOT NULL DEFAULT 0,
|
||
slots int NOT NULL DEFAULT 0,
|
||
error text NOT NULL DEFAULT ''
|
||
);
|
||
|
||
-- Записи с сайта. request_id — ключ идемпотентности: повтор запроса не создаёт вторую запись.
|
||
CREATE TABLE onec_appointments (
|
||
id bigserial PRIMARY KEY,
|
||
request_id text NOT NULL UNIQUE,
|
||
lead_id bigint REFERENCES leads (id) ON DELETE SET NULL,
|
||
doctor_onec_id text NOT NULL,
|
||
branch_onec_id text NOT NULL DEFAULT '',
|
||
service_onec_id text NOT NULL DEFAULT '',
|
||
start_at timestamptz NOT NULL,
|
||
end_at timestamptz,
|
||
patient_name text NOT NULL DEFAULT '',
|
||
patient_phone text NOT NULL DEFAULT '',
|
||
onec_appointment_id text NOT NULL DEFAULT '',
|
||
status text NOT NULL DEFAULT 'pending', -- pending | booked | failed | cancelled
|
||
error text NOT NULL DEFAULT '',
|
||
created_at timestamptz NOT NULL DEFAULT now(),
|
||
updated_at timestamptz NOT NULL DEFAULT now()
|
||
);
|
||
|
||
CREATE INDEX onec_appointments_created_idx ON onec_appointments (created_at DESC);
|