; -- 确保上一条语句结束

-- 先删除旧表以确保结构正确 (防止字段不一致报错)
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);