xiaoshu-app/scripts/sql/mobile-teaching.sql

-- Atomic, retry-safe teaching writes. No historical teaching or payroll data is rewritten.
CREATE TABLE IF NOT EXISTS "MobileTeachingCommand" (
  "company" text NOT NULL, "actorId" text NOT NULL, "requestKey" text NOT NULL,
  "fingerprint" text NOT NULL, "result" jsonb NOT NULL,
  "createdAt" timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY ("company","actorId","requestKey")
);
CREATE OR REPLACE FUNCTION xs_mobile_teaching_write(p_company text,p_actor text,p_key text,p_fingerprint text,p_operations jsonb)
RETURNS jsonb LANGUAGE plpgsql AS $$
DECLARE op jsonb; data jsonb; criteria jsonb; previous jsonb; saved jsonb; output jsonb := '[]';
  tab text; field text; columns_sql text; values_sql text; where_sql text; typ text; counter text;
  existing jsonb; prior "MobileTeachingCommand"%ROWTYPE;
BEGIN
  IF length(p_key)<10 OR length(p_key)>160 OR jsonb_array_length(p_operations)>105 THEN RAISE EXCEPTION 'Invalid teaching command'; END IF;
  -- Serialize the learner's commands as well as duplicate request keys; nested writes share one transaction.
  PERFORM pg_advisory_xact_lock(hashtextextended(p_company||':'||p_actor,0));
  SELECT * INTO prior FROM "MobileTeachingCommand" WHERE "company"=p_company AND "actorId"=p_actor AND "requestKey"=p_key;
  IF FOUND THEN
    IF prior."fingerprint"<>p_fingerprint THEN RAISE EXCEPTION 'Request key reused with different content'; END IF;
    RETURN prior."result";
  END IF;
  FOR op IN SELECT value FROM jsonb_array_elements(p_operations) LOOP
    tab := op->>'table';
    IF tab NOT IN ('PracticeRecord','DailyStudyRecord','MemoryPracticeRecord','CommonModel','UnitLearningRecord','AssessmentProfile','SurveyItem','SurveyLog','DesignAnswer','LessonCompletionReport','LessonRecord') THEN RAISE EXCEPTION 'Teaching table not allowed'; END IF;
    data := op->'data'; criteria := op->'match';
    IF criteria IS NULL OR criteria='{}' THEN RAISE EXCEPTION 'Missing business key'; END IF;
    IF op ? 'reference' THEN
      FOR field,counter IN SELECT key,value#>>'{}' FROM jsonb_each(op->'reference') LOOP
        data := jsonb_set(data,ARRAY[field],previous->counter);
        IF criteria ? field THEN criteria:=jsonb_set(criteria,ARRAY[field],previous->counter); END IF;
      END LOOP;
    END IF;
    where_sql := '';
    FOR field IN SELECT jsonb_object_keys(criteria) LOOP
      SELECT format_type(a.atttypid,a.atttypmod) INTO typ FROM pg_attribute a WHERE a.attrelid=to_regclass(format('%I',tab)) AND a.attname=field AND NOT a.attisdropped;
      IF typ IS NULL THEN RAISE EXCEPTION 'Unknown match field %',field; END IF;
      where_sql:=where_sql||CASE WHEN where_sql='' THEN '' ELSE ' AND ' END||format('%I = ($1->>%L)::%s',field,field,typ);
    END LOOP;
    EXECUTE format('SELECT to_jsonb(t) FROM %I t WHERE %s ORDER BY "createdAt", "objectId" LIMIT 1 FOR UPDATE',tab,where_sql) INTO existing USING criteria;
    IF existing IS NOT NULL THEN
      data := data || jsonb_build_object('objectId',existing->'objectId','createdAt',existing->'createdAt');
      -- Stable legacy IDs must survive retries and concurrent upserts.
      FOREACH field IN ARRAY ARRAY['id','generalId','itemId'] LOOP
        IF existing ? field THEN data:=jsonb_set(data,ARRAY[field],existing->field); END IF;
      END LOOP;
    END IF;
    FOR field IN SELECT jsonb_array_elements_text(COALESCE(op->'increment','[]')) LOOP
      data:=jsonb_set(data,ARRAY[field],to_jsonb(COALESCE((existing->>field)::numeric,0)+COALESCE((data->>field)::numeric,0)));
    END LOOP;
    FOR field IN SELECT jsonb_array_elements_text(COALESCE(op->'union','[]')) LOOP
      data:=jsonb_set(data,ARRAY[field],(SELECT COALESCE(jsonb_agg(DISTINCT value),'[]') FROM jsonb_array_elements(COALESCE(existing->field,'[]')||COALESCE(data->field,'[]'))));
    END LOOP;
    IF tab='UnitLearningRecord' THEN data:=jsonb_set(data,'{learnedCount}',to_jsonb(jsonb_array_length(data->'wordIds'))); END IF;
    data:=data||jsonb_build_object('updatedAt',now());
    columns_sql:=''; values_sql:='';
    FOR field IN SELECT jsonb_object_keys(data) LOOP
      IF NOT EXISTS(SELECT 1 FROM pg_attribute WHERE attrelid=to_regclass(format('%I',tab)) AND attname=field AND NOT attisdropped) THEN RAISE EXCEPTION 'Unknown teaching field %.%',tab,field; END IF;
      columns_sql:=columns_sql||CASE WHEN columns_sql='' THEN '' ELSE ',' END||format('%I',field);
      values_sql:=values_sql||CASE WHEN values_sql='' THEN '' ELSE ',' END||format('r.%I',field);
    END LOOP;
    IF existing IS NULL THEN
      EXECUTE format('INSERT INTO %I (%s) SELECT %s FROM jsonb_populate_record(NULL::%I,$1) r RETURNING to_jsonb(%I.*)',tab,columns_sql,values_sql,tab,tab) INTO saved USING data;
    ELSE
      EXECUTE format('UPDATE %I t SET (%s)=(SELECT %s FROM jsonb_populate_record(NULL::%I,$1) r) WHERE t."objectId"=$2 RETURNING to_jsonb(t)',tab,columns_sql,values_sql,tab) INTO saved USING data,existing->>'objectId';
    END IF;
    previous:=saved; output:=output||jsonb_build_array(saved);
  END LOOP;
  INSERT INTO "MobileTeachingCommand" VALUES(p_company,p_actor,p_key,p_fingerprint,output,now());
  RETURN output;
END $$;
REVOKE ALL ON FUNCTION xs_mobile_teaching_write(text,text,text,text,jsonb) FROM PUBLIC;

-- Post-lesson acknowledgement never changes credits or pay snapshots.
CREATE OR REPLACE FUNCTION xs_mobile_confirm_appointment(co text,general_id text,target_status integer,actor_id numeric,administrator boolean)
RETURNS jsonb LANGUAGE plpgsql AS $$
DECLARE a record; old_status integer;
BEGIN
 IF target_status NOT IN (20,30) THEN RAISE EXCEPTION '不支持的确认状态';END IF;
 SELECT ca.*,c."status" AS common_status INTO a FROM "CourseAppointment" ca JOIN "CommonModel" c ON c."company"=ca."company" AND c."modelId"=54 AND c."itemId"=ca."id" WHERE c."company"=co AND c."generalId"::text=general_id FOR UPDATE OF ca;
 IF NOT FOUND OR a.common_status=-2 THEN RAISE EXCEPTION '预约不存在或已取消'; END IF;
 IF NOT administrator AND actor_id::text IS DISTINCT FROM (CASE WHEN target_status=20 THEN a."szyh"::text ELSE a."pl"::text END) THEN RAISE EXCEPTION '无权确认该预约';END IF;
 old_status:=a."dszt"::integer;
 IF old_status>=target_status THEN RETURN jsonb_build_object('GeneralID',general_id,'status',old_status,'unchanged',true);END IF;
 IF (target_status=20 AND old_status<>11) OR (target_status=30 AND old_status<>20) THEN RAISE EXCEPTION '预约状态已变更，请刷新';END IF;
 UPDATE "CourseAppointment" SET "dszt"=target_status::text,"updatedAt"=now() WHERE "objectId"=a."objectId";
 RETURN jsonb_build_object('GeneralID',general_id,'previousStatus',old_status,'status',target_status,'unchanged',false);
END $$;
REVOKE ALL ON FUNCTION xs_mobile_confirm_appointment(text,text,integer,numeric,boolean) FROM PUBLIC;