xiaoshu-app/scripts/sql/teacher-earned-months.sql

-- Derived earned-income summaries, not PayrollBatch settlements or payments.
-- Every reader supplies a signature of fresh inputs and the complete calculator.
CREATE TABLE IF NOT EXISTS "TeacherEarnedMonth" (
 company text NOT NULL,"coachId" numeric NOT NULL,month text NOT NULL,
 signature text NOT NULL CHECK(signature ~ '^[0-9a-f]{64}$'),
 "amountCents" bigint NOT NULL CHECK("amountCents" BETWEEN 0 AND 9007199254740991),
 "pendingPayCount" bigint NOT NULL CHECK("pendingPayCount">=0),
 "lineCount" bigint NOT NULL CHECK("lineCount">=0 AND "pendingPayCount"<="lineCount"),
 "updatedAt" timestamptz NOT NULL DEFAULT clock_timestamp(),
 PRIMARY KEY(company,"coachId",month),
 CHECK(month='undated' OR month ~ '^\d{4}-(0[1-9]|1[0-2])$')
);
REVOKE ALL ON "TeacherEarnedMonth" FROM PUBLIC;
CREATE OR REPLACE FUNCTION xs_teacher_earned_month_guard() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
 IF current_setting('xiaoshu.teacher_month_write',true) IS DISTINCT FROM 'yes' THEN RAISE EXCEPTION 'derived teacher months require controlled writer'; END IF;
 IF TG_OP='DELETE' THEN RETURN OLD; END IF;RETURN NEW;
END $$;
DROP TRIGGER IF EXISTS teacher_earned_month_guard ON "TeacherEarnedMonth";
CREATE TRIGGER teacher_earned_month_guard BEFORE INSERT OR UPDATE OR DELETE ON "TeacherEarnedMonth" FOR EACH ROW EXECUTE FUNCTION xs_teacher_earned_month_guard();

CREATE OR REPLACE FUNCTION xs_teacher_earned_month_read(co text,coach numeric,months jsonb)
RETURNS jsonb LANGUAGE sql STABLE AS $$
 SELECT COALESCE(jsonb_agg(jsonb_build_object('month',m.month,'signature',m.signature,'amountCents',m."amountCents",'pendingPayCount',m."pendingPayCount",'lineCount',m."lineCount") ORDER BY m.month),'[]'::jsonb)
 FROM "TeacherEarnedMonth" m WHERE m.company=co AND m."coachId"=coach
 AND (months IS NULL OR m.month IN (SELECT jsonb_array_elements_text(months)));
$$;
CREATE OR REPLACE FUNCTION xs_teacher_earned_month_write(co text,coach numeric,entries jsonb)
RETURNS integer LANGUAGE plpgsql AS $$
DECLARE item jsonb; saved integer:=0; changed integer; previous_flag text:=current_setting('xiaoshu.teacher_month_write',true);
BEGIN
 IF NULLIF(co,'') IS NULL OR coach IS NULL OR jsonb_typeof(entries)<>'array' THEN RAISE EXCEPTION 'invalid teacher monthly write'; END IF;
 PERFORM set_config('xiaoshu.teacher_month_write','yes',true);
 FOR item IN SELECT value FROM jsonb_array_elements(entries) LOOP
  IF COALESCE(item->>'signature','') !~ '^[0-9a-f]{64}$' OR item->>'month' IS NULL
   OR item->>'amountCents' IS NULL OR item->>'pendingPayCount' IS NULL OR item->>'lineCount' IS NULL
   OR (item->>'amountCents')::numeric<>trunc((item->>'amountCents')::numeric)
   OR (item->>'pendingPayCount')::numeric<>trunc((item->>'pendingPayCount')::numeric)
   OR (item->>'lineCount')::numeric<>trunc((item->>'lineCount')::numeric) THEN RAISE EXCEPTION 'invalid teacher monthly summary'; END IF;
  INSERT INTO "TeacherEarnedMonth"(company,"coachId",month,signature,"amountCents","pendingPayCount","lineCount")
  VALUES(co,coach,item->>'month',item->>'signature',(item->>'amountCents')::bigint,(item->>'pendingPayCount')::bigint,(item->>'lineCount')::bigint)
  ON CONFLICT(company,"coachId",month) DO UPDATE SET signature=EXCLUDED.signature,"amountCents"=EXCLUDED."amountCents","pendingPayCount"=EXCLUDED."pendingPayCount","lineCount"=EXCLUDED."lineCount","updatedAt"=clock_timestamp()
  -- An older in-flight calculation cannot overwrite a newer stored generation.
  WHERE "TeacherEarnedMonth".signature IS NOT DISTINCT FROM item->>'expectedSignature';
  GET DIAGNOSTICS changed=ROW_COUNT;saved:=saved+changed;
 END LOOP;
 PERFORM set_config('xiaoshu.teacher_month_write',COALESCE(previous_flag,''),true);
 RETURN saved;
END $$;
REVOKE ALL ON FUNCTION xs_teacher_earned_month_read(text,numeric,jsonb),xs_teacher_earned_month_write(text,numeric,jsonb) FROM PUBLIC;