xiaoshu-app/scripts/sql/support-requests.sql

CREATE TABLE IF NOT EXISTS "SupportRequestResolution" (
  company text NOT NULL,
  "requestId" text NOT NULL,
  "ownerId" numeric NOT NULL,
  status text NOT NULL CHECK(status IN ('processed','approved','rejected')),
  comment text NOT NULL CHECK(length(btrim(comment)) BETWEEN 1 AND 1000),
  actor text NOT NULL,
  "actorName" text NOT NULL,
  "handledAt" timestamptz NOT NULL DEFAULT now(),
  "readAt" timestamptz,
  PRIMARY KEY(company,"requestId")
);
CREATE INDEX IF NOT EXISTS support_reply_owner ON "SupportRequestResolution"(company,"ownerId","handledAt" DESC);
CREATE OR REPLACE FUNCTION xs_support_request_process(co text,rid text,actor_id text,actor_name text,next_status text,note text,expected text)
RETURNS jsonb LANGUAGE plpgsql AS $$
DECLARE g "Guestbook"; r "SupportRequestResolution"; kind text; fingerprint text;
BEGIN
 IF NULLIF(btrim(co),'') IS NULL OR NULLIF(btrim(actor_id),'') IS NULL OR note IS NULL OR next_status IS NULL OR length(btrim(note)) NOT BETWEEN 1 AND 1000 OR next_status NOT IN ('processed','approved','rejected') THEN RAISE EXCEPTION '请填写处理结果和 1 至 1000 字的处理意见'; END IF;
 SELECT * INTO g FROM "Guestbook" WHERE company=co AND "objectId"=rid AND COALESCE(parentid,0)=0 AND COALESCE(status,0)<>-2 AND (cateid=22 OR "sourceKey" LIKE 'cloud:guestbook_add:%') FOR UPDATE;
 IF NOT FOUND THEN RAISE EXCEPTION '申请不存在或已删除'; END IF;
 SELECT * INTO r FROM "SupportRequestResolution" WHERE company=co AND "requestId"=rid;
 IF FOUND THEN
   IF r.actor=actor_id AND r.status=next_status AND r.comment=btrim(note) THEN RETURN to_jsonb(r); END IF;
   RAISE EXCEPTION '该申请已处理，请刷新查看结果';
 END IF;
 fingerprint=md5(COALESCE(g.title,'')||chr(10)||COALESCE(g.tcontent,'')||chr(10)||COALESCE(g.userid::text,''));
 IF expected IS DISTINCT FROM fingerprint THEN RAISE EXCEPTION '申请内容已变化，请刷新后重新处理'; END IF;
 kind=CASE WHEN g.title LIKE '【休假申请】%' THEN 'leave' ELSE 'feedback' END;
 IF (kind='leave' AND next_status NOT IN ('approved','rejected')) OR (kind='feedback' AND next_status<>'processed') THEN RAISE EXCEPTION '处理结果与申请类型不符'; END IF;
 INSERT INTO "SupportRequestResolution"(company,"requestId","ownerId",status,comment,actor,"actorName") VALUES(co,rid,g.userid,next_status,btrim(note),actor_id,actor_name) RETURNING * INTO r;
 RETURN to_jsonb(r);
END $$;