jackyu66gitandCursor
7155b8b53a
小程序可在 Go 上完成微信手机号登录、测评、预约和下单，不再反代 Java。 Co-authored-by: Cursor <cursoragent@cursor.com>
260 lines
11 KiB
SQL
260 lines
11 KiB
SQL
-- ECR-050: miniprogram consult domain (native PostgreSQL)
|
|
|
|
CREATE TABLE IF NOT EXISTS consult_user_ext (
|
|
user_id uuid PRIMARY KEY REFERENCES users(id),
|
|
stay_period varchar(64) NULL,
|
|
id_card varchar(32) NULL,
|
|
real_name varchar(64) NULL,
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS consult_banners (
|
|
id bigserial PRIMARY KEY,
|
|
banner_name varchar(128) NOT NULL DEFAULT '',
|
|
banner_image text NOT NULL DEFAULT '',
|
|
click_url text NOT NULL DEFAULT '',
|
|
describe text NOT NULL DEFAULT '',
|
|
order_index int NOT NULL DEFAULT 0,
|
|
jump_type int NOT NULL DEFAULT 1,
|
|
created_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS consult_news (
|
|
id bigserial PRIMARY KEY,
|
|
type int NOT NULL,
|
|
title varchar(255) NOT NULL DEFAULT '',
|
|
show_image text NOT NULL DEFAULT '',
|
|
content text NOT NULL DEFAULT '',
|
|
show_main int NOT NULL DEFAULT 0,
|
|
created_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS consult_protocols (
|
|
id bigserial PRIMARY KEY,
|
|
code varchar(128) NOT NULL UNIQUE,
|
|
title varchar(255) NOT NULL DEFAULT '',
|
|
context text NOT NULL DEFAULT '',
|
|
status int NOT NULL DEFAULT 1
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS consult_business_scopes (
|
|
code varchar(64) PRIMARY KEY,
|
|
name varchar(64) NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS consult_doctors (
|
|
id bigserial PRIMARY KEY,
|
|
name varchar(64) NOT NULL,
|
|
avatar text NOT NULL DEFAULT '',
|
|
business_scope varchar(255) NOT NULL DEFAULT '',
|
|
cover_url text NOT NULL DEFAULT '',
|
|
consultation_method varchar(128) NOT NULL DEFAULT 'online',
|
|
education varchar(128) NOT NULL DEFAULT '',
|
|
introduction text NOT NULL DEFAULT '',
|
|
resume text NOT NULL DEFAULT '',
|
|
notice text NOT NULL DEFAULT '',
|
|
tags varchar(255) NOT NULL DEFAULT '',
|
|
work_experience varchar(128) NOT NULL DEFAULT '',
|
|
work_start_time date NULL,
|
|
price int NOT NULL DEFAULT 0,
|
|
status int NOT NULL DEFAULT 1,
|
|
address varchar(255) NOT NULL DEFAULT '',
|
|
address_detail varchar(255) NOT NULL DEFAULT '',
|
|
show_service_num int NOT NULL DEFAULT 0,
|
|
is_top int NOT NULL DEFAULT 0,
|
|
created_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS consult_schedule_days (
|
|
id bigserial PRIMARY KEY,
|
|
doctor_id bigint NOT NULL REFERENCES consult_doctors(id),
|
|
schedule_date date NOT NULL,
|
|
UNIQUE (doctor_id, schedule_date)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS consult_slots (
|
|
id bigserial PRIMARY KEY,
|
|
schedule_id bigint NOT NULL REFERENCES consult_schedule_days(id) ON DELETE CASCADE,
|
|
doctor_id bigint NOT NULL REFERENCES consult_doctors(id),
|
|
start_time time NOT NULL,
|
|
end_time time NOT NULL,
|
|
consultation_method varchar(64) NOT NULL DEFAULT 'online',
|
|
status int NOT NULL DEFAULT 0
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_consult_slots_doctor_date ON consult_slots(doctor_id, status);
|
|
|
|
CREATE TABLE IF NOT EXISTS consult_focus (
|
|
id bigserial PRIMARY KEY,
|
|
user_id uuid NOT NULL REFERENCES users(id),
|
|
doctor_id bigint NOT NULL REFERENCES consult_doctors(id),
|
|
status int NOT NULL DEFAULT 1,
|
|
read_status int NOT NULL DEFAULT 0,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
UNIQUE (user_id, doctor_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS consult_feedback (
|
|
id bigserial PRIMARY KEY,
|
|
user_id uuid NOT NULL REFERENCES users(id),
|
|
content_text text NOT NULL,
|
|
contact varchar(32) NOT NULL DEFAULT '',
|
|
status int NOT NULL DEFAULT 0,
|
|
created_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS consult_tests (
|
|
id bigserial PRIMARY KEY,
|
|
test_name varchar(128) NOT NULL,
|
|
sub_title varchar(255) NOT NULL DEFAULT '',
|
|
test_pic text NOT NULL DEFAULT '',
|
|
test_introduction text NOT NULL DEFAULT '',
|
|
test_notice text NOT NULL DEFAULT '',
|
|
total_num int NOT NULL DEFAULT 0,
|
|
actual_num int NOT NULL DEFAULT 0,
|
|
show_main int NOT NULL DEFAULT 0,
|
|
status int NOT NULL DEFAULT 1
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS consult_questions (
|
|
id bigserial PRIMARY KEY,
|
|
test_id bigint NOT NULL REFERENCES consult_tests(id) ON DELETE CASCADE,
|
|
question_type int NOT NULL DEFAULT 1,
|
|
question_text text NOT NULL,
|
|
question_image text NOT NULL DEFAULT '',
|
|
required int NOT NULL DEFAULT 1,
|
|
order_index int NOT NULL DEFAULT 0
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS consult_options (
|
|
id bigserial PRIMARY KEY,
|
|
question_id bigint NOT NULL REFERENCES consult_questions(id) ON DELETE CASCADE,
|
|
option_text text NOT NULL,
|
|
option_score int NOT NULL DEFAULT 0,
|
|
order_index int NOT NULL DEFAULT 0
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS consult_test_results (
|
|
id bigserial PRIMARY KEY,
|
|
test_id bigint NOT NULL REFERENCES consult_tests(id) ON DELETE CASCADE,
|
|
min_score int NOT NULL DEFAULT 0,
|
|
max_score int NOT NULL DEFAULT 0,
|
|
result_desc text NOT NULL DEFAULT '',
|
|
result_analysis text NOT NULL DEFAULT '',
|
|
treat_plan text NOT NULL DEFAULT ''
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS consult_user_choices (
|
|
id bigserial PRIMARY KEY,
|
|
user_id uuid NOT NULL REFERENCES users(id),
|
|
test_id bigint NOT NULL REFERENCES consult_tests(id),
|
|
result_id bigint NOT NULL REFERENCES consult_test_results(id),
|
|
total_time int NOT NULL DEFAULT 0,
|
|
choice_info jsonb NOT NULL DEFAULT '[]',
|
|
created_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS consult_orders (
|
|
id bigserial PRIMARY KEY,
|
|
order_sn varchar(64) NOT NULL UNIQUE,
|
|
slot_id bigint NOT NULL REFERENCES consult_slots(id),
|
|
doctor_id bigint NOT NULL REFERENCES consult_doctors(id),
|
|
user_id uuid NOT NULL REFERENCES users(id),
|
|
appointment_date date NOT NULL,
|
|
start_time time NOT NULL,
|
|
end_time time NOT NULL,
|
|
status int NOT NULL DEFAULT 0,
|
|
user_deleted int NOT NULL DEFAULT 0,
|
|
appointment_name varchar(64) NOT NULL DEFAULT '',
|
|
birthday date NULL,
|
|
phone varchar(20) NOT NULL DEFAULT '',
|
|
sex int NOT NULL DEFAULT 1,
|
|
emergency_contact_info text NOT NULL DEFAULT '',
|
|
user_read int NOT NULL DEFAULT 0,
|
|
total_amount int NOT NULL DEFAULT 0,
|
|
valid_time timestamptz NULL,
|
|
pay_time timestamptz NULL,
|
|
cancel_time timestamptz NULL,
|
|
cancel_flag int NOT NULL DEFAULT 0,
|
|
consultation_method varchar(64) NOT NULL DEFAULT '',
|
|
pay_param jsonb NULL,
|
|
created_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_consult_orders_user ON consult_orders(user_id, user_deleted);
|
|
|
|
-- seed: scopes, protocol, banner, news, doctor, test, 14-day slots
|
|
INSERT INTO consult_business_scopes(code, name) VALUES
|
|
('emotion', '情绪压力'),
|
|
('relation', '亲密关系'),
|
|
('career', '职业发展')
|
|
ON CONFLICT (code) DO NOTHING;
|
|
|
|
INSERT INTO consult_protocols(code, title, context, status) VALUES
|
|
('consultation_appointment_agreement', '咨询预约协议', '<p>预约即表示你已阅读并同意本协议。本服务不替代危机干预或医疗诊断。</p>', 1),
|
|
('psychological_show_appointment_agreement', '心理咨询展示与预约说明', '<p>咨询师信息仅供预约参考。如需紧急帮助请联系当地心理援助热线。</p>', 1),
|
|
('privacy_protection_agreement', '愈心谷咨询小程序隐私保护指引', '<p>我们仅在提供服务所必需的范围内处理你的手机号与预约信息。</p>', 1)
|
|
ON CONFLICT (code) DO NOTHING;
|
|
|
|
INSERT INTO consult_banners(banner_name, banner_image, click_url, describe, order_index, jump_type)
|
|
SELECT '愈心谷', '/static/n-main/yxg-brand-logo.png', '', '欢迎来到愈心谷', 1, 1
|
|
WHERE NOT EXISTS (SELECT 1 FROM consult_banners);
|
|
|
|
INSERT INTO consult_news(type, title, show_image, content, show_main)
|
|
SELECT 1, '艺术疗愈入门', '/static/n-main/yxg-brand-logo.png', '<p>用创作看见自己的情绪。</p>', 1
|
|
WHERE NOT EXISTS (SELECT 1 FROM consult_news WHERE type=1);
|
|
|
|
INSERT INTO consult_news(type, title, show_image, content, show_main)
|
|
SELECT 2, '本周活动', '/static/n-main/yxg-brand-logo.png', '<p>线上主题分享，欢迎预约咨询师一对一。</p>', 1
|
|
WHERE NOT EXISTS (SELECT 1 FROM consult_news WHERE type=2);
|
|
|
|
INSERT INTO consult_doctors(name, avatar, business_scope, consultation_method, education, introduction, resume, notice, tags, work_experience, work_start_time, price, status, address, address_detail, show_service_num, is_top)
|
|
SELECT '林安', '/static/n-main/yxg-brand-logo.png', 'emotion,relation', 'online,face_to_face', '应用心理硕士',
|
|
'关注情绪调节与关系议题', '国家二级心理咨询师', '请提前10分钟进入会谈。', '情绪,关系',
|
|
'8年', DATE '2018-03-01', 22600, 1, '线上/线下', '预约成功后发送地址', 128, 1
|
|
WHERE NOT EXISTS (SELECT 1 FROM consult_doctors);
|
|
|
|
INSERT INTO consult_schedule_days(doctor_id, schedule_date)
|
|
SELECT d.id, (CURRENT_DATE + g.n)
|
|
FROM consult_doctors d
|
|
CROSS JOIN generate_series(1, 14) AS g(n)
|
|
ON CONFLICT (doctor_id, schedule_date) DO NOTHING;
|
|
|
|
INSERT INTO consult_slots(schedule_id, doctor_id, start_time, end_time, consultation_method, status)
|
|
SELECT s.id, s.doctor_id, t.st, t.et, 'online', 0
|
|
FROM consult_schedule_days s
|
|
CROSS JOIN (VALUES (TIME '09:00', TIME '10:00'), (TIME '14:00', TIME '15:00')) AS t(st, et)
|
|
WHERE NOT EXISTS (
|
|
SELECT 1 FROM consult_slots x WHERE x.schedule_id=s.id AND x.start_time=t.st
|
|
);
|
|
|
|
INSERT INTO consult_tests(test_name, sub_title, test_pic, test_introduction, test_notice, total_num, show_main, status)
|
|
SELECT '情绪小测', '用两分钟看看最近的状态', '/static/n-main/yxg-brand-logo.png',
|
|
'本测评仅供自我觉察，不是诊断。', '请按第一直觉作答。', 1280, 1, 1
|
|
WHERE NOT EXISTS (SELECT 1 FROM consult_tests);
|
|
|
|
INSERT INTO consult_questions(test_id, question_type, question_text, required, order_index)
|
|
SELECT t.id, 1, '最近两周，我感到紧张或坐立不安。', 1, 1
|
|
FROM consult_tests t
|
|
WHERE t.test_name='情绪小测' AND NOT EXISTS (SELECT 1 FROM consult_questions q WHERE q.test_id=t.id);
|
|
|
|
INSERT INTO consult_questions(test_id, question_type, question_text, required, order_index)
|
|
SELECT t.id, 1, '最近两周，我仍然能享受日常小事。', 1, 2
|
|
FROM consult_tests t
|
|
WHERE t.test_name='情绪小测' AND (SELECT COUNT(*) FROM consult_questions q WHERE q.test_id=t.id) < 2;
|
|
|
|
INSERT INTO consult_options(question_id, option_text, option_score, order_index)
|
|
SELECT q.id, o.txt, o.sc, o.ord
|
|
FROM consult_questions q
|
|
JOIN consult_tests t ON t.id=q.test_id AND t.test_name='情绪小测'
|
|
CROSS JOIN (VALUES ('很少', 0, 1), ('有时', 1, 2), ('经常', 2, 3)) AS o(txt, sc, ord)
|
|
WHERE NOT EXISTS (SELECT 1 FROM consult_options x WHERE x.question_id=q.id);
|
|
|
|
INSERT INTO consult_test_results(test_id, min_score, max_score, result_desc, result_analysis, treat_plan)
|
|
SELECT t.id, 0, 2, '状态平稳', '你最近的情绪波动在可调节范围内。', '保持作息，需要时可以和咨询师聊聊。'
|
|
FROM consult_tests t WHERE t.test_name='情绪小测'
|
|
AND NOT EXISTS (SELECT 1 FROM consult_test_results r WHERE r.test_id=t.id AND r.min_score=0);
|
|
|
|
INSERT INTO consult_test_results(test_id, min_score, max_score, result_desc, result_analysis, treat_plan)
|
|
SELECT t.id, 3, 8, '需要被看见', '最近的紧绷感偏高，适合做一次梳理。', '建议预约咨询，或先做呼吸放松。'
|
|
FROM consult_tests t WHERE t.test_name='情绪小测'
|
|
AND NOT EXISTS (SELECT 1 FROM consult_test_results r WHERE r.test_id=t.id AND r.min_score=3);
|