ANGEL月別実績の追加SQL

月別CSVを日別データと分けて保存します。このSQLだけでは実績を取り込まず、既存の日別実績・FANZA・ログイン・通知を変更しません。再実行できます。
「SQLをコピー」を押す
いつものSupabaseプロジェクト → SQL Editor → New query
全文を貼り付け、Runを1回押す
結果をチャットで教えてください
-- Additive monthly history. Does not change daily records, FANZA, logins or notifications.
BEGIN;
CREATE TABLE IF NOT EXISTS public.angellive_monthly_performance (
 cast_id text NOT NULL REFERENCES public.casts(id),
 month_start date NOT NULL CHECK(extract(day FROM month_start)=1),
 total_points bigint NOT NULL CHECK(total_points BETWEEN 0 AND 9007199254740991),
 points_excluding_training bigint NOT NULL CHECK(points_excluding_training BETWEEN 0 AND total_points),
 period_basis text NOT NULL CHECK(period_basis='noon'),
 source_sha256 text NOT NULL CHECK(source_sha256 ~ '^[a-f0-9]{64}$'),
 imported_at timestamptz NOT NULL DEFAULT now(),
 PRIMARY KEY(cast_id,month_start)
);
ALTER TABLE public.angellive_monthly_performance ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS angel_monthly_read ON public.angellive_monthly_performance;
CREATE POLICY angel_monthly_read ON public.angellive_monthly_performance FOR SELECT TO authenticated
 USING(public.adel_can_read_cast(cast_id));
REVOKE ALL ON public.angellive_monthly_performance FROM PUBLIC,anon,authenticated;
GRANT SELECT ON public.angellive_monthly_performance TO authenticated;
GRANT ALL ON public.angellive_monthly_performance TO service_role;

CREATE OR REPLACE FUNCTION public.adel_admin_import_angel_months(records jsonb)
RETURNS jsonb LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
DECLARE item jsonb; target text; day date; total bigint; excluding bigint; digest text;
 stored public.angellive_monthly_performance; processed integer:=0; changed integer:=0;
BEGIN
 IF NOT public.adel_is_admin() THEN RAISE EXCEPTION 'Administrator required' USING ERRCODE='42501'; END IF;
 IF jsonb_typeof(records) IS DISTINCT FROM 'array' THEN RAISE EXCEPTION 'Invalid monthly batch'; END IF;
 IF jsonb_array_length(records) NOT BETWEEN 1 AND 60 THEN RAISE EXCEPTION 'Invalid monthly batch'; END IF;
 IF EXISTS(SELECT 1 FROM jsonb_array_elements(records) x
   GROUP BY x->>'site_cast_id',x->>'month_start' HAVING count(*)>1) THEN RAISE EXCEPTION 'Duplicate month'; END IF;
 -- Serialize approved batches. Any validation failure rolls back the entire call.
 PERFORM pg_advisory_xact_lock(617294308);
 FOR item IN SELECT * FROM jsonb_array_elements(records) LOOP
  IF jsonb_typeof(item) IS DISTINCT FROM 'object' THEN RAISE EXCEPTION 'Invalid monthly record'; END IF;
  IF (SELECT array_agg(k ORDER BY k) FROM jsonb_object_keys(item) k)
     IS DISTINCT FROM ARRAY['expected_cast_id','month_start','period_basis','points_excluding_training','site_cast_id','source_sha256','total_points']::text[]
   OR item->>'site_cast_id' IS NULL OR item->>'site_cast_id' !~ '^[0-9]{1,32}$'
   OR item->>'month_start' IS NULL OR item->>'month_start' !~ '^20[0-9]{2}-(0[1-9]|1[0-2])-01$'
   OR item->>'period_basis' IS DISTINCT FROM 'noon'
   OR item->>'source_sha256' IS NULL OR item->>'source_sha256' !~ '^[a-f0-9]{64}$'
   OR jsonb_typeof(item->'total_points') IS DISTINCT FROM 'number'
   OR jsonb_typeof(item->'points_excluding_training') IS DISTINCT FROM 'number'
   OR item->>'total_points' !~ '^[0-9]{1,16}$'
   OR item->>'points_excluding_training' !~ '^[0-9]{1,16}$'
  THEN RAISE EXCEPTION 'Invalid monthly record'; END IF;
  day:=(item->>'month_start')::date;total:=(item->>'total_points')::bigint;
  excluding:=(item->>'points_excluding_training')::bigint;digest:=item->>'source_sha256';
  IF total>9007199254740991 OR excluding>total
   OR day>=date_trunc('month',(now() AT TIME ZONE 'Asia/Tokyo')-interval '12 hours')::date
  THEN RAISE EXCEPTION 'Invalid monthly period or points'; END IF;
  SELECT a.cast_id INTO target FROM public.cast_site_accounts a JOIN public.casts c ON c.id=a.cast_id
   WHERE a.site='angellive' AND a.site_cast_id=item->>'site_cast_id'
     AND a.cast_id=item->>'expected_cast_id' AND c.active FOR SHARE OF a,c;
  IF NOT FOUND THEN RAISE EXCEPTION 'Confirmed active mapping required'; END IF;
  SELECT * INTO stored FROM public.angellive_monthly_performance
   WHERE cast_id=target AND month_start=day FOR UPDATE;
  IF FOUND THEN
   IF stored.source_sha256<>digest OR stored.total_points<>total OR stored.points_excluding_training<>excluding
    OR stored.period_basis<>'noon' THEN RAISE EXCEPTION 'Monthly source conflict; review required'; END IF;
  ELSE
   INSERT INTO public.angellive_monthly_performance(cast_id,month_start,total_points,points_excluding_training,period_basis,source_sha256)
    VALUES(target,day,total,excluding,'noon',digest);
   changed:=changed+1;
  END IF;
  processed:=processed+1;
 END LOOP;
 RETURN jsonb_build_object('processed',processed,'changed',changed);
END $$;
REVOKE ALL ON FUNCTION public.adel_admin_import_angel_months(jsonb) FROM PUBLIC,anon;
GRANT EXECUTE ON FUNCTION public.adel_admin_import_angel_months(jsonb) TO authenticated;
COMMIT;