56 lines
2.7 KiB
SQL
56 lines
2.7 KiB
SQL
; -- 确保上一条语句结束
|
|
|
|
-- 先删除旧表以确保结构正确 (防止字段不一致报错)
|
|
DROP TABLE IF EXISTS public.ec_service_requests CASCADE;
|
|
|
|
-- 创建服务请求表
|
|
CREATE TABLE public.ec_service_requests (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
elder_id uuid NOT NULL REFERENCES public.ec_elders(id) ON DELETE CASCADE,
|
|
type VARCHAR(50) NOT NULL, -- 请求类型: nurse_call, cleaning, meal, medical, other
|
|
priority VARCHAR(20) DEFAULT 'normal', -- 优先级: low, normal, high, urgent
|
|
description TEXT, -- 详细描述
|
|
status VARCHAR(20) DEFAULT 'pending', -- 状态: pending, processing, completed, cancelled
|
|
handler_id uuid REFERENCES public.ak_users(id), -- 处理人ID
|
|
handled_at TIMESTAMP WITH TIME ZONE, -- 处理时间
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- 确保测试老人存在 (如果不存在则插入)
|
|
INSERT INTO public.ec_elders (id, name, gender, status)
|
|
VALUES ('7bf7378e-a027-473e-97ac-3460ed3f170a', '测试老人', 'male', 'active')
|
|
ON CONFLICT (id) DO NOTHING;
|
|
|
|
-- 插入测试数据
|
|
INSERT INTO public.ec_service_requests (elder_id, type, priority, description, status, created_at)
|
|
VALUES
|
|
('7bf7378e-a027-473e-97ac-3460ed3f170a', 'nurse_call', 'high', '老人呼叫护理员', 'pending', '2025-11-26T09:18:05.716Z');
|
|
|
|
-- 创建索引
|
|
CREATE INDEX IF NOT EXISTS idx_ec_service_requests_elder_id ON public.ec_service_requests(elder_id);
|
|
CREATE INDEX IF NOT EXISTS idx_ec_service_requests_status ON public.ec_service_requests(status);
|
|
CREATE INDEX IF NOT EXISTS idx_ec_service_requests_created_at ON public.ec_service_requests(created_at);
|
|
|
|
-- 启用 RLS
|
|
ALTER TABLE public.ec_service_requests ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- RLS 策略
|
|
-- 允许认证用户插入数据 (老人发起请求)
|
|
DROP POLICY IF EXISTS service_requests_insert_authenticated ON public.ec_service_requests;
|
|
CREATE POLICY service_requests_insert_authenticated ON public.ec_service_requests
|
|
FOR INSERT TO authenticated
|
|
WITH CHECK (true);
|
|
|
|
-- 允许认证用户查询数据 (老人查看自己的，员工查看所有的 - 这里简化为允许所有认证用户查看)
|
|
DROP POLICY IF EXISTS service_requests_select_authenticated ON public.ec_service_requests;
|
|
CREATE POLICY service_requests_select_authenticated ON public.ec_service_requests
|
|
FOR SELECT TO authenticated
|
|
USING (true);
|
|
|
|
-- 允许认证用户更新数据 (员工处理请求，老人取消请求)
|
|
DROP POLICY IF EXISTS service_requests_update_authenticated ON public.ec_service_requests;
|
|
CREATE POLICY service_requests_update_authenticated ON public.ec_service_requests
|
|
FOR UPDATE TO authenticated
|
|
USING (true);
|