\set ON_ERROR_STOP on
\if :{?app_role}
\else
\set app_role vignette_app
\endif

-- Outcome & Alliance OS G1 live DB proof.
-- Owner/superuser connection에서 실행하며 모든 fixture는 마지막에 rollback한다.

BEGIN;

INSERT INTO app.app_user (
  user_id, external_id, email, display_name, role, cohort, consent_at
) VALUES
  (
    'b1000000-0000-4000-8000-000000000001',
    'dev:alliance-pulse-smoke-a',
    'alliance-pulse-smoke-a@example.invalid',
    '동맹 펄스 스모크 A',
    'learner', 'alliance-smoke-a', now()
  ),
  (
    'b1000000-0000-4000-8000-000000000002',
    'dev:alliance-pulse-smoke-b',
    'alliance-pulse-smoke-b@example.invalid',
    '동맹 펄스 스모크 B',
    'learner', 'alliance-smoke-b', now()
  )
ON CONFLICT (user_id) DO NOTHING;

INSERT INTO app.sessions (id, learner_id, session_no, theory_mode)
VALUES
  (
    'b1000000-0000-4000-8000-000000000011',
    'b1000000-0000-4000-8000-000000000001', 1, 'integrative'
  ),
  (
    'b1000000-0000-4000-8000-000000000012',
    'b1000000-0000-4000-8000-000000000002', 1, 'integrative'
  )
ON CONFLICT (id) DO NOTHING;

-- 다른 학습자의 pulse는 owner fixture로 먼저 만든다.
INSERT INTO app.alliance_pulse (
  pulse_id, session_id, checkpoint, status,
  learner_locked_at, created_at, updated_at
) VALUES (
  'b1000000-0000-4000-8000-000000000022',
  'b1000000-0000-4000-8000-000000000012',
  'post', 'awaiting_agents', now(), now(), now()
);

SET LOCAL ROLE :"app_role";
SELECT set_config('app.current_uid', 'b1000000-0000-4000-8000-000000000001', true);
SELECT set_config('app.current_role', 'learner', true);
SELECT set_config('app.current_cohort', 'alliance-smoke-a', true);
SELECT set_config('app.ai_context', '0', true);
SELECT set_config('app.current_ai_view', '', true);

-- 학습자 자기평가가 외부 에이전트 공개 전 잠긴다.
INSERT INTO app.alliance_pulse (
  pulse_id, session_id, checkpoint, status,
  learner_locked_at, created_at, updated_at
) VALUES (
  'b1000000-0000-4000-8000-000000000021',
  'b1000000-0000-4000-8000-000000000011',
  'post', 'awaiting_agents', now(), now(), now()
);

INSERT INTO app.self_assessment (
  self_assessment_id, pulse_id, session_id, learner_id,
  scores, evidence_turn_ids, locked_at, created_at
) VALUES (
  'b1000000-0000-4000-8000-000000000031',
  'b1000000-0000-4000-8000-000000000021',
  'b1000000-0000-4000-8000-000000000011',
  'b1000000-0000-4000-8000-000000000001',
  '{"goal":0.45,"task":0.55,"bond":0.65}'::jsonb,
  '{}'::uuid[], now(), now()
);

INSERT INTO app.measurement_event (
  measurement_id, session_id, pulse_id, construct, dimension,
  perspective, source_kind, instrument_id, instrument_version,
  value, scale_min, scale_max, visible_to
) VALUES (
  'b1000000-0000-4000-8000-000000000041',
  'b1000000-0000-4000-8000-000000000011',
  'b1000000-0000-4000-8000-000000000021',
  'working_alliance', 'goal', 'learner_self_report', 'learner_reported',
  'alliance-pulse-learner', '1.0.0', 0.45, 0, 1,
  ARRAY['counselor','evaluator','supervisor','research']::text[]
);

DO $$
DECLARE
  visible_pulses integer;
  visible_history integer;
  affected integer;
BEGIN
  SELECT count(*) INTO visible_pulses FROM app.alliance_pulse;
  IF visible_pulses <> 1 THEN
    RAISE EXCEPTION 'learner pulse RLS leak: expected 1, got %', visible_pulses;
  END IF;

  SELECT count(*) INTO visible_history
  FROM audit.alliance_pulse_status_event;
  IF visible_history <> 1 THEN
    RAISE EXCEPTION 'learner pulse history RLS leak: expected 1, got %', visible_history;
  END IF;

  UPDATE app.alliance_pulse
  SET status = 'ready', revealed_at = now(), updated_at = now()
  WHERE pulse_id = 'b1000000-0000-4000-8000-000000000021';
  GET DIAGNOSTICS affected = ROW_COUNT;
  IF affected <> 0 THEN
    RAISE EXCEPTION 'learner unexpectedly updated a locked alliance pulse';
  END IF;

  BEGIN
    INSERT INTO audit.alliance_pulse_status_event (
      pulse_id, from_status, to_status, changed_by_role, changed_at
    ) VALUES (
      'b1000000-0000-4000-8000-000000000021',
      'awaiting_agents', 'ready', 'forged', now()
    );
    RAISE EXCEPTION 'direct status history insert unexpectedly succeeded';
  EXCEPTION WHEN insufficient_privilege THEN
    NULL;
  END;
END;
$$;

-- client agent는 pulse 입력은 읽지만 학습자 자기평가를 볼 수 없다.
SELECT set_config('app.ai_context', '1', true);
SELECT set_config('app.current_ai_view', 'client', true);

DO $$
DECLARE actual integer;
BEGIN
  SELECT count(*) INTO actual
  FROM app.self_assessment
  WHERE pulse_id = 'b1000000-0000-4000-8000-000000000021';
  IF actual <> 0 THEN
    RAISE EXCEPTION 'client AI view exposed learner self-assessment';
  END IF;
END;
$$;

-- evaluator AI가 결과 공개 상태로 단 한 번 전이한다.
SELECT set_config('app.current_ai_view', 'evaluator', true);
UPDATE app.alliance_pulse
SET status = 'ready', error_code = NULL,
    revealed_at = clock_timestamp(), updated_at = clock_timestamp()
WHERE pulse_id = 'b1000000-0000-4000-8000-000000000021';

DO $$
DECLARE actual integer;
BEGIN
  SELECT count(*) INTO actual
  FROM audit.alliance_pulse_status_event
  WHERE pulse_id = 'b1000000-0000-4000-8000-000000000021';
  IF actual <> 2 THEN
    RAISE EXCEPTION 'pulse status history mismatch: expected 2, got %', actual;
  END IF;

  BEGIN
    UPDATE app.alliance_pulse
    SET status = 'degraded', revealed_at = clock_timestamp(), updated_at = clock_timestamp()
    WHERE pulse_id = 'b1000000-0000-4000-8000-000000000021';
    RAISE EXCEPTION 'second pulse transition unexpectedly succeeded';
  EXCEPTION WHEN SQLSTATE '55000' THEN
    NULL;
  END;
END;
$$;

RESET ROLE;

-- DB 레벨 pulse/session 정합성과 append-only 불변식을 owner 우회에서도 검증한다.
INSERT INTO app.alliance_pulse (
  pulse_id, session_id, checkpoint, status,
  learner_locked_at, created_at, updated_at
) VALUES (
  'b1000000-0000-4000-8000-000000000023',
  'b1000000-0000-4000-8000-000000000011',
  'mid', 'awaiting_agents', now(), now(), now()
);

DO $$
BEGIN
  BEGIN
    INSERT INTO app.self_assessment (
      self_assessment_id, pulse_id, session_id, learner_id,
      scores, locked_at, created_at
    ) VALUES (
      'b1000000-0000-4000-8000-000000000032',
      'b1000000-0000-4000-8000-000000000023',
      'b1000000-0000-4000-8000-000000000012',
      'b1000000-0000-4000-8000-000000000002',
      '{"goal":0.5,"task":0.5,"bond":0.5}'::jsonb, now(), now()
    );
    RAISE EXCEPTION 'cross-session self-assessment unexpectedly succeeded';
  EXCEPTION WHEN foreign_key_violation THEN
    NULL;
  END;

  BEGIN
    INSERT INTO app.measurement_event (
      measurement_id, session_id, pulse_id, construct, dimension,
      perspective, source_kind, instrument_id, instrument_version,
      value, scale_min, scale_max, visible_to
    ) VALUES (
      'b1000000-0000-4000-8000-000000000042',
      'b1000000-0000-4000-8000-000000000012',
      'b1000000-0000-4000-8000-000000000023',
      'working_alliance', 'goal', 'learner_self_report', 'learner_reported',
      'alliance-pulse-learner', '1.0.0', 0.5, 0, 1,
      ARRAY['counselor']::text[]
    );
    RAISE EXCEPTION 'cross-session measurement event unexpectedly succeeded';
  EXCEPTION WHEN foreign_key_violation THEN
    NULL;
  END;

  BEGIN
    UPDATE app.self_assessment
    SET scores = '{"goal":0.9,"task":0.9,"bond":0.9}'::jsonb
    WHERE self_assessment_id = 'b1000000-0000-4000-8000-000000000031';
    RAISE EXCEPTION 'self-assessment UPDATE unexpectedly succeeded';
  EXCEPTION WHEN SQLSTATE '55000' THEN
    NULL;
  END;

  BEGIN
    UPDATE app.alliance_pulse
    SET checkpoint = 'pre', status = 'ready',
        revealed_at = clock_timestamp(), updated_at = clock_timestamp()
    WHERE pulse_id = 'b1000000-0000-4000-8000-000000000023';
    RAISE EXCEPTION 'pulse identity mutation unexpectedly succeeded';
  EXCEPTION WHEN SQLSTATE '55000' THEN
    NULL;
  END;

  BEGIN
    DELETE FROM app.alliance_pulse
    WHERE pulse_id = 'b1000000-0000-4000-8000-000000000023';
    RAISE EXCEPTION 'pulse DELETE unexpectedly succeeded';
  EXCEPTION WHEN SQLSTATE '55000' THEN
    NULL;
  END;

  BEGIN
    DELETE FROM audit.alliance_pulse_status_event
    WHERE pulse_id = 'b1000000-0000-4000-8000-000000000023';
    RAISE EXCEPTION 'pulse status history DELETE unexpectedly succeeded';
  EXCEPTION WHEN SQLSTATE '55000' THEN
    NULL;
  END;
END;
$$;

SELECT json_build_object(
  'ok', true,
  'learner_cross_session_leaks', 0,
  'client_self_assessment_leaks', 0,
  'lock_before_reveal', true,
  'single_terminal_transition', true,
  'pulse_session_fk_guards', 2,
  'append_only_guards', 4,
  'status_history_rows', 2
) AS alliance_pulse_ledger_smoke;

ROLLBACK;