CREATE TABLE IF NOT EXISTS "AccountDeletionRequest" (id text PRIMARY KEY, company text NOT NULL, "userId" text NOT NULL, reason text NOT NULL, status text NOT NULL DEFAULT 'requested', response text NOT NULL DEFAULT '', actor text, version integer NOT NULL DEFAULT 1, "createdAt" timestamptz DEFAULT now(), "updatedAt" timestamptz DEFAULT now());
CREATE UNIQUE INDEX IF NOT EXISTS xs_pending_deletion ON "AccountDeletionRequest" (company,"userId") WHERE status IN ('requested','processing');
REVOKE ALL ON "AccountDeletionRequest" FROM PUBLIC;
CREATE OR REPLACE FUNCTION xs_request_deletion(co text,uid text,why text) RETURNS jsonb LANGUAGE plpgsql AS $$
DECLARE r "AccountDeletionRequest";
BEGIN
 IF length(trim(why))<2 OR length(why)>500 THEN RAISE EXCEPTION '请填写 2 至 500 字的申请说明'; END IF;
 IF NOT EXISTS(SELECT 1 FROM "_User" WHERE company=co AND "objectId"=uid AND NOT COALESCE("isDeleted",false)) THEN RAISE EXCEPTION '账号不存在'; END IF;
 PERFORM pg_advisory_xact_lock(hashtext('deletion:'||co||':'||uid));
 SELECT * INTO r FROM "AccountDeletionRequest" WHERE company=co AND "userId"=uid AND status IN ('requested','processing');
 IF FOUND THEN RETURN to_jsonb(r); END IF;
 INSERT INTO "AccountDeletionRequest" (id,company,"userId",reason) VALUES(substr(md5(random()::text||clock_timestamp()::text),1,20),co,uid,trim(why)) RETURNING * INTO r;
 RETURN to_jsonb(r);
END $$;
CREATE OR REPLACE FUNCTION xs_process_deletion(co text,operator_id text,rid text,expected integer,next_status text,note text) RETURNS jsonb LANGUAGE plpgsql AS $$
DECLARE r "AccountDeletionRequest";
BEGIN
 IF next_status NOT IN ('processing','rejected','completed') OR length(trim(note))<2 OR length(note)>1000 THEN RAISE EXCEPTION '请填写有效处理状态和说明'; END IF;
 SELECT * INTO r FROM "AccountDeletionRequest" WHERE company=co AND id=rid FOR UPDATE;
 IF NOT FOUND OR r.version<>expected THEN RAISE EXCEPTION '申请已更新，请刷新'; END IF;
 IF r.status NOT IN ('requested','processing') THEN RAISE EXCEPTION '申请已结束'; END IF;
 IF next_status='completed' AND NOT EXISTS(SELECT 1 FROM "_User" WHERE company=co AND "objectId"=r."userId" AND "isDeleted"=true) THEN RAISE EXCEPTION '请先完成账号资料核查和会员注销，再登记完成'; END IF;
 UPDATE "AccountDeletionRequest" SET status=next_status,response=trim(note),actor=operator_id,version=version+1,"updatedAt"=now() WHERE id=rid RETURNING * INTO r;
 RETURN to_jsonb(r);
END $$;