142 lines
5.2 KiB
PL/PgSQL
142 lines
5.2 KiB
PL/PgSQL
BEGIN;
|
||
|
||
CREATE OR REPLACE FUNCTION public.tg_set_updated_at()
|
||
RETURNS trigger
|
||
LANGUAGE plpgsql
|
||
AS $$
|
||
BEGIN
|
||
NEW.updated_at = now();
|
||
RETURN NEW;
|
||
END;
|
||
$$;
|
||
|
||
ALTER TABLE public.ml_delivery_staff
|
||
ADD COLUMN IF NOT EXISTS station_id UUID REFERENCES public.ml_delivery_stations(id) ON DELETE SET NULL,
|
||
ADD COLUMN IF NOT EXISTS staff_no TEXT,
|
||
ADD COLUMN IF NOT EXISTS online_status TEXT NOT NULL DEFAULT 'resting',
|
||
ADD COLUMN IF NOT EXISTS certificate_status TEXT NOT NULL DEFAULT 'pending',
|
||
ADD COLUMN IF NOT EXISTS certificate_expire_at DATE,
|
||
ADD COLUMN IF NOT EXISTS service_area TEXT NOT NULL DEFAULT '',
|
||
ADD COLUMN IF NOT EXISTS skills JSONB NOT NULL DEFAULT '[]'::jsonb,
|
||
ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMPTZ,
|
||
ADD COLUMN IF NOT EXISTS deleted_by UUID REFERENCES public.ak_users(id) ON DELETE SET NULL;
|
||
|
||
ALTER TABLE public.ml_delivery_staff
|
||
DROP CONSTRAINT IF EXISTS chk_ml_delivery_staff_online_status;
|
||
ALTER TABLE public.ml_delivery_staff
|
||
ADD CONSTRAINT chk_ml_delivery_staff_online_status
|
||
CHECK (online_status IN ('online', 'resting', 'busy'));
|
||
|
||
ALTER TABLE public.ml_delivery_staff
|
||
DROP CONSTRAINT IF EXISTS chk_ml_delivery_staff_certificate_status;
|
||
ALTER TABLE public.ml_delivery_staff
|
||
ADD CONSTRAINT chk_ml_delivery_staff_certificate_status
|
||
CHECK (certificate_status IN ('valid', 'expired', 'pending'));
|
||
|
||
ALTER TABLE public.ml_delivery_stations
|
||
ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMPTZ,
|
||
ADD COLUMN IF NOT EXISTS deleted_by UUID REFERENCES public.ak_users(id) ON DELETE SET NULL;
|
||
|
||
CREATE UNIQUE INDEX IF NOT EXISTS uq_ml_delivery_staff_uid_active
|
||
ON public.ml_delivery_staff(uid)
|
||
WHERE uid IS NOT NULL AND deleted_at IS NULL;
|
||
|
||
CREATE UNIQUE INDEX IF NOT EXISTS uq_ml_delivery_staff_staff_no_active
|
||
ON public.ml_delivery_staff(staff_no)
|
||
WHERE staff_no IS NOT NULL AND deleted_at IS NULL;
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_ml_delivery_staff_station_id
|
||
ON public.ml_delivery_staff(station_id)
|
||
WHERE deleted_at IS NULL;
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_ml_delivery_staff_deleted_at
|
||
ON public.ml_delivery_staff(deleted_at);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_ml_delivery_staff_status_active
|
||
ON public.ml_delivery_staff(status, is_active)
|
||
WHERE deleted_at IS NULL;
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_ml_delivery_stations_deleted_at
|
||
ON public.ml_delivery_stations(deleted_at);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_ml_delivery_stations_status_active
|
||
ON public.ml_delivery_stations(status)
|
||
WHERE deleted_at IS NULL;
|
||
|
||
DROP TRIGGER IF EXISTS trg_ml_delivery_staff_set_updated_at ON public.ml_delivery_staff;
|
||
CREATE TRIGGER trg_ml_delivery_staff_set_updated_at
|
||
BEFORE UPDATE ON public.ml_delivery_staff
|
||
FOR EACH ROW
|
||
EXECUTE FUNCTION public.tg_set_updated_at();
|
||
|
||
DROP TRIGGER IF EXISTS trg_ml_delivery_stations_set_updated_at ON public.ml_delivery_stations;
|
||
CREATE TRIGGER trg_ml_delivery_stations_set_updated_at
|
||
BEFORE UPDATE ON public.ml_delivery_stations
|
||
FOR EACH ROW
|
||
EXECUTE FUNCTION public.tg_set_updated_at();
|
||
|
||
ALTER TABLE public.ml_delivery_staff ENABLE ROW LEVEL SECURITY;
|
||
ALTER TABLE public.ml_delivery_stations ENABLE ROW LEVEL SECURITY;
|
||
|
||
DROP POLICY IF EXISTS delivery_staff_self_select ON public.ml_delivery_staff;
|
||
DROP POLICY IF EXISTS delivery_staff_self_update ON public.ml_delivery_staff;
|
||
CREATE POLICY delivery_staff_self_select
|
||
ON public.ml_delivery_staff
|
||
FOR SELECT
|
||
TO authenticated
|
||
USING (
|
||
deleted_at IS NULL
|
||
AND EXISTS (
|
||
SELECT 1
|
||
FROM public.ak_users u
|
||
WHERE u.id = ml_delivery_staff.uid
|
||
AND u.auth_id = auth.uid()
|
||
)
|
||
);
|
||
|
||
CREATE POLICY delivery_staff_self_update
|
||
ON public.ml_delivery_staff
|
||
FOR UPDATE
|
||
TO authenticated
|
||
USING (
|
||
deleted_at IS NULL
|
||
AND EXISTS (
|
||
SELECT 1
|
||
FROM public.ak_users u
|
||
WHERE u.id = ml_delivery_staff.uid
|
||
AND u.auth_id = auth.uid()
|
||
)
|
||
)
|
||
WITH CHECK (
|
||
deleted_at IS NULL
|
||
AND EXISTS (
|
||
SELECT 1
|
||
FROM public.ak_users u
|
||
WHERE u.id = ml_delivery_staff.uid
|
||
AND u.auth_id = auth.uid()
|
||
)
|
||
);
|
||
|
||
DROP POLICY IF EXISTS delivery_stations_select_active ON public.ml_delivery_stations;
|
||
CREATE POLICY delivery_stations_select_active
|
||
ON public.ml_delivery_stations
|
||
FOR SELECT
|
||
TO anon, authenticated
|
||
USING (status = 1 AND deleted_at IS NULL);
|
||
|
||
COMMENT ON COLUMN public.ml_delivery_staff.station_id IS '所属机构/服务站点 ID';
|
||
COMMENT ON COLUMN public.ml_delivery_staff.staff_no IS '服务人员编号';
|
||
COMMENT ON COLUMN public.ml_delivery_staff.online_status IS '在线状态：online/resting/busy';
|
||
COMMENT ON COLUMN public.ml_delivery_staff.certificate_status IS '资质状态：valid/expired/pending';
|
||
COMMENT ON COLUMN public.ml_delivery_staff.certificate_expire_at IS '主要资质到期日期';
|
||
COMMENT ON COLUMN public.ml_delivery_staff.service_area IS '服务区域描述';
|
||
COMMENT ON COLUMN public.ml_delivery_staff.skills IS '技能标签 JSON 数组';
|
||
COMMENT ON COLUMN public.ml_delivery_staff.deleted_at IS '软删除时间';
|
||
COMMENT ON COLUMN public.ml_delivery_staff.deleted_by IS '软删除操作人';
|
||
COMMENT ON COLUMN public.ml_delivery_stations.deleted_at IS '软删除时间';
|
||
COMMENT ON COLUMN public.ml_delivery_stations.deleted_by IS '软删除操作人';
|
||
|
||
-- 派单候选人读取统一走 SECURITY DEFINER RPC，避免公开在线人员列表。
|
||
|
||
COMMIT;
|