交易式驗收：完整 SQL 測試檔

-- Transactional remote smoke test for the deployed course_team API.
-- Run against a non-local Supabase database only through a trusted SQL channel.
-- Every fixture is rolled back; no student or evaluation data remains.

begin;
set local statement_timeout = '45s';

insert into auth.users (
  instance_id, id, aud, role, email, email_confirmed_at,
  raw_app_meta_data, raw_user_meta_data, created_at, updated_at,
  is_sso_user, is_anonymous
) values
  ('00000000-0000-0000-0000-000000000000', '10000000-0000-4000-8000-000000000001', 'authenticated', 'authenticated', 'ct-smoke-a@example.invalid', now(), '{"provider":"email","providers":["email"]}', '{"full_name":"測試甲"}', now(), now(), false, false),
  ('00000000-0000-0000-0000-000000000000', '10000000-0000-4000-8000-000000000002', 'authenticated', 'authenticated', 'ct-smoke-b@example.invalid', now(), '{"provider":"email","providers":["email"]}', '{"full_name":"測試乙"}', now(), now(), false, false),
  ('00000000-0000-0000-0000-000000000000', '10000000-0000-4000-8000-000000000003', 'authenticated', 'authenticated', 'ct-smoke-c@example.invalid', now(), '{"provider":"email","providers":["email"]}', '{"full_name":"測試丙"}', now(), now(), false, false);

select set_config(
  'ct_smoke.admin_id',
  (select a.user_id::text from course_team.platform_admins a order by a.granted_at limit 1),
  true
);

set local role authenticated;
select set_config('request.jwt.claim.sub', current_setting('ct_smoke.admin_id'), true);

select set_config(
  'ct_smoke.offering_id',
  public.ct_admin_create_offering(
    'yzu-smoke', '元智大學測試', 'CTSMOKE', '交易式測試課程', '2099X', '01', 'Asia/Taipei'
  ) ->> 'id',
  true
);

select public.ct_admin_set_offering_status(current_setting('ct_smoke.offering_id')::uuid, 'active');
select public.ct_admin_import_roster(
  current_setting('ct_smoke.offering_id')::uuid,
  '[
    {"studentNumber":"TST0001","email":"ct-smoke-a@example.invalid","displayName":"測試甲","preferredName":"Alpha","locale":"zh-Hant-TW"},
    {"studentNumber":"TST0002","email":"ct-smoke-b@example.invalid","displayName":"測試乙","preferredName":"Beta","locale":"en"},
    {"studentNumber":"TST0003","email":"ct-smoke-c@example.invalid","displayName":"測試丙","preferredName":"Gamma","locale":"zh-Hant-TW"}
  ]'::jsonb
);

select set_config('request.jwt.claim.sub', '10000000-0000-4000-8000-000000000001', true);
select public.ct_get_context();
select set_config('request.jwt.claim.sub', '10000000-0000-4000-8000-000000000002', true);
select public.ct_get_context();
select set_config('request.jwt.claim.sub', '10000000-0000-4000-8000-000000000003', true);
select public.ct_get_context();

select set_config('request.jwt.claim.sub', current_setting('ct_smoke.admin_id'), true);
select set_config(
  'ct_smoke.scope_id',
  public.ct_admin_create_scope(
    current_setting('ct_smoke.offering_id')::uuid,
    '正式測試小組', 'formal', 2::smallint, 5::smallint, now() - interval '3 days', now() + interval '3 days'
  ) ->> 'id',
  true
);
select public.ct_admin_set_scope_status(current_setting('ct_smoke.scope_id')::uuid, 'open');

with admin_data as (
  select public.ct_admin_get_offering(current_setting('ct_smoke.offering_id')::uuid) as body
), member_ids as (
  select array_agg((member ->> 'id')::uuid order by member ->> 'studentNumber') as ids
  from admin_data, lateral jsonb_array_elements(body -> 'roster') member
)
select public.ct_admin_set_group(
  current_setting('ct_smoke.scope_id')::uuid,
  null,
  'Smoke Team',
  member_ids.ids,
  now() - interval '2 days',
  '交易式整合測試'
)
from member_ids;

select set_config(
  'ct_smoke.instrument_id',
  (
    select instrument ->> 'id'
    from jsonb_array_elements(
      public.ct_admin_get_offering(current_setting('ct_smoke.offering_id')::uuid) -> 'instruments'
    ) instrument
    order by instrument ->> 'versionKey' desc
    limit 1
  ),
  true
);
select set_config(
  'ct_smoke.round_id',
  public.ct_admin_create_round(
    current_setting('ct_smoke.offering_id')::uuid,
    current_setting('ct_smoke.scope_id')::uuid,
    current_setting('ct_smoke.instrument_id')::uuid,
    '交易式互評測試',
    now() - interval '1 day', now(),
    now() - interval '1 minute', now() + interval '1 day',
    true, 1::smallint, 2::smallint
  ) ->> 'id',
  true
);
select public.ct_admin_freeze_round(current_setting('ct_smoke.round_id')::uuid);
select public.ct_admin_set_round_status(current_setting('ct_smoke.round_id')::uuid, 'open');

select set_config('request.jwt.claim.sub', '10000000-0000-4000-8000-000000000001', true);
with form_data as (
  select public.ct_get_round_form(current_setting('ct_smoke.round_id')::uuid) as body
), rating_payload as (
  select jsonb_agg(jsonb_build_object(
    'assignmentId', assignment ->> 'id',
    'dimensionId', dimension ->> 'id',
    'score', 4,
    'notObservedReason', null
  ) order by assignment ->> 'id', dimension ->> 'order') as body
  from form_data,
       lateral jsonb_array_elements(body -> 'assignments') assignment,
       lateral jsonb_array_elements(body -> 'dimensions') dimension
)
select set_config('ct_smoke.ratings', rating_payload.body::text, true)
from rating_payload;

select set_config(
  'ct_smoke.first_receipt',
  public.ct_submit_evaluation(
    current_setting('ct_smoke.round_id')::uuid,
    '20000000-0000-4000-8000-000000000001', 0::smallint, 'zh-Hant-TW',
    current_setting('ct_smoke.ratings')::jsonb, '[]'::jsonb
  ) ->> 'receiptCode',
  true
);
select set_config(
  'ct_smoke.idempotent',
  public.ct_submit_evaluation(
    current_setting('ct_smoke.round_id')::uuid,
    '20000000-0000-4000-8000-000000000001', 0::smallint, 'zh-Hant-TW',
    current_setting('ct_smoke.ratings')::jsonb, '[]'::jsonb
  ) ->> 'idempotent',
  true
);
select public.ct_submit_evaluation(
  current_setting('ct_smoke.round_id')::uuid,
  '20000000-0000-4000-8000-000000000002', 1::smallint, 'en',
  current_setting('ct_smoke.ratings')::jsonb, '[]'::jsonb
);

reset role;

do $$
declare
  denied boolean := false;
  max_revision_denied boolean := false;
begin
  if current_setting('ct_smoke.idempotent') <> 'true' then
    raise exception 'Idempotent retry did not return true.';
  end if;
  if (select count(*) from course_team.memberships where scope_id = current_setting('ct_smoke.scope_id')::uuid) <> 3 then
    raise exception 'Expected three active memberships.';
  end if;
  if (select count(*) from course_team.evaluation_assignments where round_id = current_setting('ct_smoke.round_id')::uuid) <> 9 then
    raise exception 'Expected nine frozen evaluator-subject assignments.';
  end if;
  if (select count(*) from course_team.submissions where round_id = current_setting('ct_smoke.round_id')::uuid) <> 1 then
    raise exception 'Idempotent retry created a duplicate submission.';
  end if;
  if (select count(*) from course_team.submission_revisions sr join course_team.submissions s on s.id = sr.submission_id where s.round_id = current_setting('ct_smoke.round_id')::uuid) <> 2 then
    raise exception 'Expected exactly two submission revisions.';
  end if;
  if (select count(*) from course_team.ratings r join course_team.submission_revisions sr on sr.id = r.revision_id join course_team.submissions s on s.id = sr.submission_id where s.round_id = current_setting('ct_smoke.round_id')::uuid) <> 30 then
    raise exception 'Expected fifteen ratings in each of two revisions.';
  end if;
  if has_table_privilege('authenticated', 'course_team.enrollments', 'select') then
    raise exception 'Authenticated role unexpectedly has direct table SELECT.';
  end if;
  if has_function_privilege('anon', 'public.ct_get_context()', 'execute') then
    raise exception 'Anonymous role unexpectedly has RPC execution permission.';
  end if;
  if not has_function_privilege('authenticated', 'public.ct_get_context()', 'execute') then
    raise exception 'Authenticated role is missing RPC execution permission.';
  end if;

  perform set_config('request.jwt.claim.sub', '10000000-0000-4000-8000-000000000001', true);
  begin
    perform public.ct_admin_get_offering(current_setting('ct_smoke.offering_id')::uuid);
  exception when insufficient_privilege then
    denied := true;
  end;
  if not denied then
    raise exception 'Student unexpectedly accessed the instructor snapshot.';
  end if;

  begin
    perform public.ct_submit_evaluation(
      current_setting('ct_smoke.round_id')::uuid,
      '20000000-0000-4000-8000-000000000003', 2::smallint, 'zh-Hant-TW',
      current_setting('ct_smoke.ratings')::jsonb, '[]'::jsonb
    );
  exception when invalid_parameter_value then
    max_revision_denied := true;
  end;
  if not max_revision_denied then
    raise exception 'Submission exceeded the configured revision limit.';
  end if;
end;
$$;

set local role authenticated;
select set_config('request.jwt.claim.sub', current_setting('ct_smoke.admin_id'), true);
select public.ct_admin_set_round_status(current_setting('ct_smoke.round_id')::uuid, 'closed');
select public.ct_admin_set_round_status(current_setting('ct_smoke.round_id')::uuid, 'published');
select set_config(
  'ct_smoke.export_round_name',
  public.ct_admin_export_round(current_setting('ct_smoke.round_id')::uuid) #>> '{round,name}',
  true
);
select set_config('request.jwt.claim.sub', '10000000-0000-4000-8000-000000000001', true);
select set_config(
  'ct_smoke.feedback_withheld',
  (public.ct_get_student_feedback(current_setting('ct_smoke.round_id')::uuid) #>> '{dimensions,0,withheld}'),
  true
);
reset role;

do $$
begin
  if current_setting('ct_smoke.export_round_name') <> '交易式互評測試' then
    raise exception 'Export did not retain the round snapshot.';
  end if;
  if current_setting('ct_smoke.feedback_withheld') <> 'true' then
    raise exception 'Low-sample peer feedback was not withheld.';
  end if;
end;
$$;

select jsonb_build_object(
  'ok', true,
  'memberships', (select count(*) from course_team.memberships where scope_id = current_setting('ct_smoke.scope_id')::uuid),
  'assignments', (select count(*) from course_team.evaluation_assignments where round_id = current_setting('ct_smoke.round_id')::uuid),
  'submissions', (select count(*) from course_team.submissions where round_id = current_setting('ct_smoke.round_id')::uuid),
  'revisions', (select count(*) from course_team.submission_revisions sr join course_team.submissions s on s.id = sr.submission_id where s.round_id = current_setting('ct_smoke.round_id')::uuid),
  'ratings', (select count(*) from course_team.ratings r join course_team.submission_revisions sr on sr.id = r.revision_id join course_team.submissions s on s.id = sr.submission_id where s.round_id = current_setting('ct_smoke.round_id')::uuid),
  'idempotentRetry', current_setting('ct_smoke.idempotent')::boolean,
  'lowSampleFeedbackWithheld', current_setting('ct_smoke.feedback_withheld')::boolean,
  'directTableReadDenied', not has_table_privilege('authenticated', 'course_team.enrollments', 'select'),
  'anonymousRpcDenied', not has_function_privilege('anon', 'public.ct_get_context()', 'execute')
) as smoke_result;

rollback;