sova-bootstrap
297 lines
10 KiB
SQL
297 lines
10 KiB
SQL
\connect sova_cabinet_local
|
|
|
|
DROP SCHEMA public CASCADE;
|
|
CREATE SCHEMA public;
|
|
GRANT ALL ON SCHEMA public TO sova_local;
|
|
|
|
CREATE TABLE users (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
email VARCHAR(180),
|
|
roles JSONB NOT NULL DEFAULT '[]',
|
|
password VARCHAR(255),
|
|
uid INTEGER NOT NULL UNIQUE,
|
|
token VARCHAR(255),
|
|
full_name VARCHAR(255) NOT NULL,
|
|
phone VARCHAR(255),
|
|
confirm BOOLEAN NOT NULL DEFAULT true,
|
|
created_at TIMESTAMP DEFAULT now(),
|
|
last_activity_at TIMESTAMP DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE city (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
region_id INTEGER NOT NULL,
|
|
time_zone INTEGER NOT NULL DEFAULT 4
|
|
);
|
|
|
|
CREATE TABLE filial (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
fid INTEGER NOT NULL,
|
|
name VARCHAR(255) NOT NULL,
|
|
address VARCHAR(255) NOT NULL,
|
|
address_name VARCHAR(255),
|
|
site_id INTEGER,
|
|
city_id INTEGER REFERENCES city(id) ON DELETE SET NULL,
|
|
active BOOLEAN NOT NULL DEFAULT true,
|
|
company VARCHAR(255)
|
|
);
|
|
|
|
CREATE TABLE banner (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
href VARCHAR(255) NOT NULL,
|
|
src VARCHAR(255) NOT NULL,
|
|
active BOOLEAN NOT NULL DEFAULT true,
|
|
city_id INTEGER UNIQUE REFERENCES city(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE review_source (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
city_id INTEGER NOT NULL REFERENCES city(id) ON DELETE CASCADE,
|
|
name VARCHAR(255) NOT NULL,
|
|
count_row INTEGER NOT NULL DEFAULT 0,
|
|
active BOOLEAN NOT NULL DEFAULT true,
|
|
rating DOUBLE PRECISION NOT NULL DEFAULT 5,
|
|
filial_id INTEGER REFERENCES filial(id) ON DELETE SET NULL,
|
|
date_create DATE NOT NULL DEFAULT CURRENT_DATE
|
|
);
|
|
|
|
CREATE TABLE category_page (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
active BOOLEAN NOT NULL DEFAULT true
|
|
);
|
|
|
|
CREATE TABLE page (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
description TEXT NOT NULL,
|
|
active BOOLEAN NOT NULL DEFAULT true,
|
|
alias VARCHAR(255) NOT NULL,
|
|
category_id INTEGER NOT NULL REFERENCES category_page(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE department (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
did INTEGER NOT NULL,
|
|
name VARCHAR(255) NOT NULL,
|
|
group_name VARCHAR(255) NOT NULL,
|
|
online_mode BOOLEAN NOT NULL DEFAULT true,
|
|
alias VARCHAR(255) NOT NULL,
|
|
active BOOLEAN NOT NULL DEFAULT true,
|
|
middle_name VARCHAR(255)
|
|
);
|
|
|
|
CREATE TABLE price_department (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
group_id INTEGER NOT NULL,
|
|
group_name VARCHAR(255),
|
|
doct_count INTEGER DEFAULT 0,
|
|
view_in_web BOOLEAN DEFAULT true
|
|
);
|
|
|
|
CREATE TABLE price_list_view (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
kodoper INTEGER,
|
|
schname VARCHAR(255),
|
|
specname VARCHAR(255),
|
|
speccode INTEGER,
|
|
price_info JSONB,
|
|
discpercent INTEGER,
|
|
discprice NUMERIC(10,2),
|
|
structname VARCHAR(255),
|
|
fname VARCHAR(255),
|
|
filial INTEGER,
|
|
comment TEXT,
|
|
media_id INTEGER,
|
|
date_update TIMESTAMP DEFAULT now(),
|
|
group_id INTEGER
|
|
);
|
|
|
|
CREATE TABLE price (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
name VARCHAR(255),
|
|
value NUMERIC(10,2),
|
|
property_value_id INTEGER,
|
|
date_update TIMESTAMP DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE specialist_view (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
kinder INTEGER,
|
|
speciality VARCHAR(255),
|
|
category VARCHAR(255),
|
|
experience VARCHAR(255),
|
|
description TEXT,
|
|
infoclinica BOOLEAN DEFAULT true,
|
|
alias VARCHAR(255) NOT NULL,
|
|
dcode INTEGER NOT NULL,
|
|
s_type INTEGER,
|
|
active BOOLEAN DEFAULT true,
|
|
region_id INTEGER,
|
|
kodoper JSONB,
|
|
updated TIMESTAMP DEFAULT now(),
|
|
accepts_dms BOOLEAN DEFAULT true,
|
|
degree VARCHAR(255)
|
|
);
|
|
|
|
CREATE TABLE location_view (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
dcode INTEGER NOT NULL,
|
|
department INTEGER NOT NULL,
|
|
filial INTEGER NOT NULL,
|
|
specialist_id INTEGER NOT NULL,
|
|
online_mode BOOLEAN NOT NULL DEFAULT true,
|
|
active BOOLEAN NOT NULL DEFAULT true,
|
|
nearest_date DATE
|
|
);
|
|
|
|
CREATE TABLE remote_review (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
external_id INTEGER,
|
|
specialist_id INTEGER,
|
|
active BOOLEAN DEFAULT true,
|
|
date_create DATE DEFAULT CURRENT_DATE,
|
|
message TEXT,
|
|
author VARCHAR(255),
|
|
rating DOUBLE PRECISION DEFAULT 5,
|
|
source VARCHAR(255)
|
|
);
|
|
|
|
CREATE TABLE record (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
specialist_id INTEGER NOT NULL,
|
|
phone VARCHAR(255) NOT NULL,
|
|
create_at TIMESTAMP NOT NULL DEFAULT now(),
|
|
hash VARCHAR(255) NOT NULL,
|
|
reserve JSONB NOT NULL DEFAULT '{}'
|
|
);
|
|
|
|
CREATE TABLE alert_sms (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
record_id INTEGER UNIQUE REFERENCES record(id) ON DELETE CASCADE,
|
|
date_create TIMESTAMP NOT NULL DEFAULT now(),
|
|
response TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE widget_form (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL
|
|
);
|
|
|
|
CREATE TABLE widget_form_input (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
text VARCHAR(255) NOT NULL,
|
|
type VARCHAR(255) NOT NULL,
|
|
bitrix24_id VARCHAR(255) NOT NULL,
|
|
widget_form_id INTEGER REFERENCES widget_form(id) ON DELETE CASCADE,
|
|
sort INTEGER NOT NULL DEFAULT 100
|
|
);
|
|
|
|
CREATE TABLE usrlog (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
pcode INTEGER,
|
|
agent TEXT,
|
|
client_ip VARCHAR(255),
|
|
method VARCHAR(255),
|
|
created_at TIMESTAMP DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE direct_company (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
name VARCHAR(255),
|
|
company_id BIGINT,
|
|
city VARCHAR(255)
|
|
);
|
|
|
|
CREATE TABLE direct_report (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
date DATE,
|
|
ad_group_id BIGINT,
|
|
campaign_id BIGINT,
|
|
ad_id BIGINT,
|
|
impressions INTEGER,
|
|
clicks INTEGER,
|
|
cost DOUBLE PRECISION,
|
|
conversions INTEGER
|
|
);
|
|
|
|
INSERT INTO users (email, roles, password, uid, token, full_name, phone, confirm)
|
|
VALUES (
|
|
'6c6f63616c2e636162696e6574406578616d706c652e74657374',
|
|
'["ROLE_USER","ROLE_ADMIN"]',
|
|
'$2y$12$hqHoZsKN01W.IBcfZYRf.OygGbGIMM7RW0M8fZxo.DNjswjj07S2u',
|
|
200001,
|
|
'Yjd4N6Xlvfay4sNPfPf6PBeosTdmpfCl6+xg9a9V7BnHmfocaw6YSD4NF6Vrvqrm9+nwzNsm5KzpoFV23nG8Lw==',
|
|
'qpHB5XVk6VcuthTcmGV7zqBFN805ejONu+ciwz5nwC4AyEjJ0oH7Ci5ISfgpvwbn2NhLHyTA5tCnN2jd4qQogQ==',
|
|
'2I/vmVgNZC98ben0LPkqxmZX1oiAP/k+aXCn3+/dxTBCPBlN7VLR9uiSoWcOuxeBi2rAzfl9t1N1b/PJkJO6MQ==',
|
|
true
|
|
);
|
|
|
|
INSERT INTO city (name, region_id, time_zone)
|
|
VALUES
|
|
('Саратов Local', 91, 4),
|
|
('Волгоград Local', 92, 3),
|
|
('Воронеж Local', 93, 3),
|
|
('Краснодар Local', 94, 3);
|
|
|
|
INSERT INTO filial (fid, name, address, address_name, site_id, city_id, company)
|
|
VALUES
|
|
(1, 'Сова Саратов', 'Localhost, 1', 'Саратов', 1, 1, 'ООО Local Clinic'),
|
|
(2, 'Сова Волгоград', 'Localhost, 2', 'Волгоград', 2, 2, 'ООО Local Clinic'),
|
|
(3, 'Сова Воронеж', 'Localhost, 3', 'Воронеж', 3, 3, 'ООО Local Clinic'),
|
|
(4, 'Сова Краснодар', 'Localhost, 4', 'Краснодар', 4, 4, 'ООО Local Clinic');
|
|
|
|
INSERT INTO banner (href, src, city_id)
|
|
VALUES ('http://localhost:8082', '/banners/local.png', 1);
|
|
|
|
INSERT INTO review_source (city_id, name, count_row, rating, filial_id)
|
|
VALUES (1, 'Local Reviews', 1, 5, 1);
|
|
|
|
INSERT INTO category_page (name, active) VALUES ('Local категория', true);
|
|
INSERT INTO page (name, description, active, alias, category_id)
|
|
VALUES ('Local страница', 'Контент локальной страницы', true, 'local-page', 1);
|
|
|
|
INSERT INTO department (did, name, group_name, online_mode, alias, middle_name)
|
|
VALUES (10, 'Терапия', 'Взрослые специалисты', true, 'terapiya', 'терапевта');
|
|
|
|
INSERT INTO price_department (name, group_id, group_name, doct_count, view_in_web)
|
|
VALUES ('Консультации', 100, 'Врачи', 1, true);
|
|
|
|
INSERT INTO specialist_view (name, kinder, speciality, category, experience, description, alias, dcode, s_type, active, region_id, kodoper, accepts_dms, degree)
|
|
VALUES ('Иванов Иван Иванович', 0, 'Терапевт', 'Первая категория', '12', 'Локальный врач', 'ivanov-ivan', 101, 1, true, 91, '[1001]', true, 'к.м.н.');
|
|
|
|
INSERT INTO location_view (dcode, department, filial, specialist_id, online_mode, active, nearest_date)
|
|
VALUES (101, 10, 1, 1, true, true, CURRENT_DATE + interval '1 day');
|
|
|
|
INSERT INTO price_list_view (kodoper, schname, specname, speccode, price_info, discpercent, discprice, structname, fname, filial, group_id)
|
|
VALUES (1001, 'Прием терапевта', 'Иванов Иван Иванович', 101, '{"base":1500,"discount":1200}', 10, 1200, 'Терапия', 'Сова Local', 1, 100);
|
|
|
|
INSERT INTO remote_review (external_id, specialist_id, message, author, rating, source)
|
|
VALUES (1, 1, 'Тестовый отзыв для локальной базы', 'Пациент Local', 5, 'local');
|
|
|
|
INSERT INTO record (specialist_id, phone, hash, reserve)
|
|
VALUES (1, '+70000000000', md5('+70000000000'), '{"local":true}');
|
|
|
|
INSERT INTO alert_sms (record_id, response)
|
|
VALUES (1, '{"status":"local-ok"}');
|
|
|
|
INSERT INTO widget_form (id, name) OVERRIDING SYSTEM VALUE VALUES
|
|
(1, 'Local форма'),
|
|
(2, 'Вызов врача на дом');
|
|
|
|
INSERT INTO widget_form_input (text, type, bitrix24_id, widget_form_id, sort) VALUES
|
|
('Телефон', 'phone', 'PHONE', 1, 10),
|
|
('ФИО пациента', 'text', 'NAME', 2, 10),
|
|
('Телефон', 'phone', 'PHONE', 2, 20),
|
|
('Адрес выезда', 'text', 'ADDRESS', 2, 30),
|
|
('Комментарий / симптомы', 'textarea', 'COMMENTS', 2, 40);
|
|
|
|
SELECT setval(pg_get_serial_sequence('widget_form', 'id'), (SELECT MAX(id) FROM widget_form));
|
|
SELECT setval(pg_get_serial_sequence('widget_form_input', 'id'), (SELECT MAX(id) FROM widget_form_input));
|
|
|
|
INSERT INTO usrlog (pcode, agent, client_ip, method)
|
|
VALUES (200001, 'local-agent', '127.0.0.1', 'seed');
|