-- 31절. 인트라넷에 로그인하는 직원.
-- 001_init.sql 과 002_audit.sql 은 고치지 않는다.

create table staff (
  id bigint generated always as identity primary key,
  login_id text not null,
  password_hash text not null,
  email text not null,
  name text not null,
  disabled_at timestamptz,
  created_at timestamptz not null default now(),
  check (char_length(login_id) between 4 and 20 and login_id = lower(login_id) and login_id ~ '^[a-z]' and position('@' in login_id) = 0),
  check (email = lower(email)),
  check (char_length(name) between 1 and 100 and name = btrim(name)),
  check (password_hash like '$argon2id$v=19$m=19456,t=2,p=1$%')
);

create unique index uq_staff_login_id on staff (login_id);
create unique index uq_staff_email on staff (email);

create function staff_login_id_frozen() returns trigger
language plpgsql
set search_path = pg_catalog, public
as $$
begin
  if new.login_id is distinct from old.login_id then
    raise exception 'staff login_id is immutable';
  end if;
  return new;
end;
$$;

create trigger staff_login_id_frozen
  before update on staff
  for each row execute function staff_login_id_frozen();

create trigger staff_no_delete
  before delete on staff
  for each row execute function forbid_physical_delete();

alter table audit_events
  add column actor_staff_id bigint references staff (id) on delete restrict;

do $$
declare
  r record;
begin
  for r in
    select con.conname
    from pg_catalog.pg_constraint con
    where con.conrelid = 'public.audit_events'::regclass
      and con.contype = 'c'
      and pg_catalog.pg_get_constraintdef(con.oid) like '%actor_label%'
  loop
    execute format('alter table audit_events drop constraint %I', r.conname);
  end loop;
end;
$$;

alter table audit_events add check (
  (actor_kind = 'customer' and actor_customer_id is not null and actor_label is null and actor_staff_id is null)
  or (actor_kind = 'staff' and actor_customer_id is null and actor_staff_id is not null and actor_label is not null and char_length(btrim(actor_label)) >= 1)
  or (actor_kind = 'system' and actor_customer_id is null and actor_label is null and actor_staff_id is null)
);

create index audit_events_actor_staff_id_idx
  on audit_events (actor_staff_id)
  where actor_staff_id is not null;

create or replace function audit_redact(p_table text, p_row jsonb) returns jsonb
language plpgsql
immutable
set search_path = pg_catalog, public
as $$
declare
  secrets text[];
  key text;
begin
  if p_row is null then
    return null;
  end if;
  secrets := case p_table
    when 'customers' then array['password_hash', 'phone', 'mobile', 'postal_code', 'address', 'address_detail']
    when 'staff' then array['password_hash']
    when 'contacts' then array['identity_value']
    when 'customer_tax_profiles' then array['cash_receipt_identifier']
    when 'tax_documents' then array['cash_receipt_identifier']
    when 'payment_methods' then array['billing_key', 'billing_key_digest']
    when 'order_items' then array['auth_code']
    else array[]::text[]
  end;
  foreach key in array secrets loop
    if p_row ? key and p_row -> key is distinct from 'null'::jsonb then
      p_row := jsonb_set(p_row, array[key], '"[redacted]"'::jsonb);
    end if;
  end loop;
  return p_row;
end;
$$;

create or replace function audit_capture() returns trigger
language plpgsql
set search_path = pg_catalog, public
as $$
declare
  raw_before jsonb;
  raw_after jsonb;
  src jsonb;
  cols text[] := '{}';
  colname text;
  pk jsonb := '{}'::jsonb;
  pk_name text;
  kind text;
  cid_text text;
  cid bigint;
  sid_text text;
  sid bigint;
  label text;
  req text;
  actor_disabled timestamptz;
begin
  kind := nullif(btrim(current_setting('intranet.actor_kind', true)), '');
  if kind is null then
    kind := 'system';
  elsif kind not in ('customer', 'staff', 'system') then
    raise exception 'invalid actor_kind %', kind;
  end if;

  cid_text := nullif(btrim(current_setting('intranet.actor_customer_id', true)), '');
  sid_text := nullif(btrim(current_setting('intranet.actor_staff_id', true)), '');
  label := nullif(btrim(current_setting('intranet.actor_label', true)), '');
  req := nullif(btrim(current_setting('intranet.request_id', true)), '');

  if kind = 'customer' then
    if cid_text is null or label is not null or sid_text is not null then
      raise exception 'customer actor requires actor_customer_id and null staff fields';
    end if;
    cid := cid_text::bigint;
  elsif kind = 'staff' then
    if cid_text is not null or sid_text is null or label is null then
      raise exception 'staff actor requires actor_staff_id, actor_label, and a null actor_customer_id';
    end if;
    sid := sid_text::bigint;
  elsif cid_text is not null or label is not null or sid_text is not null then
    raise exception 'system actor requires null actor fields';
  end if;

  if tg_op = 'INSERT' then
    raw_after := to_jsonb(new);
  elsif tg_op = 'DELETE' then
    raw_before := to_jsonb(old);
  else
    raw_before := to_jsonb(old);
    raw_after := to_jsonb(new);
  end if;

  -- 문 시작 값. 자기 staff 행 UPDATE 는 고치기 전 disabled_at 이다.
  if kind = 'staff' then
    if tg_table_name = 'staff' and tg_op = 'UPDATE'
       and (raw_before->>'id')::bigint = sid then
      if raw_before->>'disabled_at' is not null then
        raise exception 'disabled staff cannot act';
      end if;
    else
      select s.disabled_at into actor_disabled from staff s where s.id = sid;
      if not found then
        raise exception 'staff actor row is missing';
      end if;
      if actor_disabled is not null then
        raise exception 'disabled staff cannot act';
      end if;
    end if;
  end if;

  for colname in
    select a.attname::text
    from pg_catalog.pg_attribute a
    where a.attrelid = tg_relid
      and a.attnum > 0
      and not a.attisdropped
    order by a.attnum
  loop
    if tg_op = 'INSERT' then
      if raw_after -> colname is distinct from 'null'::jsonb then
        cols := cols || colname;
      end if;
    elsif tg_op = 'DELETE' then
      if raw_before -> colname is distinct from 'null'::jsonb then
        cols := cols || colname;
      end if;
    elsif raw_before -> colname is distinct from raw_after -> colname then
      cols := cols || colname;
    end if;
  end loop;

  if tg_op = 'UPDATE' and cardinality(cols) = 0 then
    return null;
  end if;

  src := coalesce(raw_after, raw_before);
  for pk_name in
    select a.attname::text
    from pg_catalog.pg_index i
    join pg_catalog.pg_attribute a
      on a.attrelid = i.indrelid
     and a.attnum = any (i.indkey)
    where i.indrelid = tg_relid
      and i.indisprimary
  loop
    pk := pk || jsonb_build_object(pk_name, src -> pk_name);
  end loop;

  perform set_config('intranet.audit_internal', '1', true);
  insert into audit_events (
    occurred_at, request_id, actor_kind, actor_customer_id, actor_label,
    action, table_name, row_pk, changed_columns, before, after, actor_staff_id
  ) values (
    transaction_timestamp(),
    req,
    kind,
    cid,
    label,
    lower(tg_op),
    tg_table_name,
    pk,
    cols,
    audit_redact(tg_table_name, raw_before),
    audit_redact(tg_table_name, raw_after),
    sid
  );
  return null;
end;
$$;

create trigger audit_capture_staff
  after insert or update or delete on staff
  for each row execute function audit_capture();