114 lines
3.0 KiB
PL/PgSQL
114 lines
3.0 KiB
PL/PgSQL
BEGIN;
|
|
|
|
CREATE TABLE IF NOT EXISTS public.hss_service_catalog (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
category TEXT NOT NULL DEFAULT '',
|
|
price NUMERIC(10, 2) NOT NULL DEFAULT 0,
|
|
duration_text TEXT NOT NULL DEFAULT '',
|
|
summary TEXT NOT NULL DEFAULT '',
|
|
tags_json JSONB NOT NULL DEFAULT '[]'::jsonb,
|
|
suitable_for TEXT NOT NULL DEFAULT '',
|
|
sort_no INTEGER NOT NULL DEFAULT 0,
|
|
status SMALLINT NOT NULL DEFAULT 1,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
deleted_at TIMESTAMPTZ
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_hss_service_catalog_status_sort
|
|
ON public.hss_service_catalog(status, sort_no)
|
|
WHERE deleted_at IS NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_hss_service_catalog_category
|
|
ON public.hss_service_catalog(category)
|
|
WHERE deleted_at IS NULL;
|
|
|
|
CREATE OR REPLACE FUNCTION public.tg_hss_service_catalog_updated_at()
|
|
RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
BEGIN
|
|
NEW.updated_at = now();
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
DROP TRIGGER IF EXISTS trg_hss_service_catalog_updated_at ON public.hss_service_catalog;
|
|
CREATE TRIGGER trg_hss_service_catalog_updated_at
|
|
BEFORE UPDATE ON public.hss_service_catalog
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION public.tg_hss_service_catalog_updated_at();
|
|
|
|
ALTER TABLE public.hss_service_catalog ENABLE ROW LEVEL SECURITY;
|
|
|
|
DROP POLICY IF EXISTS hss_service_catalog_public_select ON public.hss_service_catalog;
|
|
CREATE POLICY hss_service_catalog_public_select
|
|
ON public.hss_service_catalog
|
|
FOR SELECT
|
|
USING (deleted_at IS NULL AND status = 1);
|
|
|
|
INSERT INTO public.hss_service_catalog (
|
|
id,
|
|
name,
|
|
category,
|
|
price,
|
|
duration_text,
|
|
summary,
|
|
tags_json,
|
|
suitable_for,
|
|
sort_no,
|
|
status
|
|
) VALUES
|
|
(
|
|
'svc-001',
|
|
'基础上门护理',
|
|
'日常照护',
|
|
168,
|
|
'约 2 小时',
|
|
'覆盖生命体征监测、基础照护、风险提醒。',
|
|
'["适老化", "护理员上门", "支持家属陪同"]'::jsonb,
|
|
'行动不便、术后恢复、慢病随访老人',
|
|
10,
|
|
1
|
|
),
|
|
(
|
|
'svc-002',
|
|
'康复训练指导',
|
|
'康复支持',
|
|
260,
|
|
'约 3 小时',
|
|
'提供肢体训练、步态练习和居家康复建议。',
|
|
'["康复师", "步骤清晰", "可连续预约"]'::jsonb,
|
|
'卒中恢复、术后康复、失能半失能老人',
|
|
20,
|
|
1
|
|
),
|
|
(
|
|
'svc-003',
|
|
'慢病健康随访',
|
|
'健康管理',
|
|
128,
|
|
'约 90 分钟',
|
|
'完成血压血糖监测、用药核对与健康宣教。',
|
|
'["随访", "慢病", "可生成记录"]'::jsonb,
|
|
'高血压、糖尿病等长期管理老人',
|
|
30,
|
|
1
|
|
)
|
|
ON CONFLICT (id) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
category = EXCLUDED.category,
|
|
price = EXCLUDED.price,
|
|
duration_text = EXCLUDED.duration_text,
|
|
summary = EXCLUDED.summary,
|
|
tags_json = EXCLUDED.tags_json,
|
|
suitable_for = EXCLUDED.suitable_for,
|
|
sort_no = EXCLUDED.sort_no,
|
|
status = EXCLUDED.status,
|
|
deleted_at = NULL,
|
|
updated_at = now();
|
|
|
|
COMMENT ON TABLE public.hss_service_catalog IS '居家上门服务目录表';
|
|
|
|
COMMIT; |