-- 29절의 감사 로그. 001_init.sql 은 고치지 않는다.
-- 비밀 값은 표와 칸이 같이 맞을 때만 [redacted] 로 남긴다.

create table audit_events (
  id bigint generated always as identity primary key,
  occurred_at timestamptz not null,
  request_id text,
  actor_kind text not null,
  actor_customer_id bigint references customers (id) on delete restrict,
  actor_label text,
  action text not null,
  table_name text not null,
  row_pk jsonb not null,
  changed_columns text[] not null,
  before jsonb,
  after jsonb,
  created_at timestamptz not null default now(),
  check (actor_kind in ('customer', 'staff', 'system')),
  check (action in ('insert', 'update', 'delete')),
  check (table_name <> 'audit_events'),
  check (request_id is null or char_length(request_id) >= 1),
  check (
    (actor_kind = 'customer' and actor_customer_id is not null and actor_label is null)
    or (actor_kind = 'staff' and actor_customer_id is 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)
  ),
  check (
    (action = 'insert' and before is null and after is not null)
    or (action = 'delete' and before is not null and after is null)
    or (action = 'update' and before is not null and after is not null and cardinality(changed_columns) >= 1)
  )
);

create index audit_events_occurred_at_idx on audit_events (occurred_at);
create index audit_events_table_name_occurred_at_idx on audit_events (table_name, occurred_at);
create index audit_events_actor_customer_id_idx on audit_events (actor_customer_id) where actor_customer_id is not null;

create 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 '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 function audit_events_guard() returns trigger
language plpgsql
set search_path = pg_catalog, public
as $$
begin
  if tg_op = 'INSERT' then
    if current_setting('intranet.audit_internal', true) is distinct from '1' then
      raise exception 'direct insert into audit_events is forbidden';
    end if;
    perform set_config('intranet.audit_internal', '', true);
    return new;
  end if;
  raise exception 'audit_events cannot be updated or deleted';
end;
$$;

create trigger audit_events_guard
  before insert or update or delete on audit_events
  for each row execute function audit_events_guard();

create 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;
  label text;
  req text;
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)), '');
  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 then
      raise exception 'customer actor requires actor_customer_id and a null actor_label';
    end if;
    cid := cid_text::bigint;
  elsif kind = 'staff' then
    if cid_text is not null or label is null then
      raise exception 'staff actor requires actor_label and a null actor_customer_id';
    end if;
  elsif cid_text is not null or label 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;

  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
  ) 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)
  );
  return null;
end;
$$;

create trigger audit_capture_customers after insert or update or delete on customers for each row execute function audit_capture();
create trigger audit_capture_tlds after insert or update or delete on tlds for each row execute function audit_capture();
create trigger audit_capture_products after insert or update or delete on products for each row execute function audit_capture();
create trigger audit_capture_prices after insert or update or delete on prices for each row execute function audit_capture();
create trigger audit_capture_services after insert or update or delete on services for each row execute function audit_capture();
create trigger audit_capture_payment_methods after insert or update or delete on payment_methods for each row execute function audit_capture();
create trigger audit_capture_orders after insert or update or delete on orders for each row execute function audit_capture();
create trigger audit_capture_service_usage after insert or update or delete on service_usage for each row execute function audit_capture();
create trigger audit_capture_order_items after insert or update or delete on order_items for each row execute function audit_capture();
create trigger audit_capture_payments after insert or update or delete on payments for each row execute function audit_capture();
create trigger audit_capture_payment_allocations after insert or update or delete on payment_allocations for each row execute function audit_capture();
create trigger audit_capture_refunds after insert or update or delete on refunds for each row execute function audit_capture();
create trigger audit_capture_refund_lines after insert or update or delete on refund_lines for each row execute function audit_capture();
create trigger audit_capture_customer_balance_entries after insert or update or delete on customer_balance_entries for each row execute function audit_capture();
create trigger audit_capture_domains after insert or update or delete on domains for each row execute function audit_capture();
create trigger audit_capture_domain_services after insert or update or delete on domain_services for each row execute function audit_capture();
create trigger audit_capture_domain_reservations after insert or update or delete on domain_reservations for each row execute function audit_capture();
create trigger audit_capture_hosting_servers after insert or update or delete on hosting_servers for each row execute function audit_capture();
create trigger audit_capture_hosting_accounts after insert or update or delete on hosting_accounts for each row execute function audit_capture();
create trigger audit_capture_hosting_services after insert or update or delete on hosting_services for each row execute function audit_capture();
create trigger audit_capture_certificates after insert or update or delete on certificates for each row execute function audit_capture();
create trigger audit_capture_ssl_services after insert or update or delete on ssl_services for each row execute function audit_capture();
create trigger audit_capture_contacts after insert or update or delete on contacts for each row execute function audit_capture();
create trigger audit_capture_domain_contacts after insert or update or delete on domain_contacts for each row execute function audit_capture();
create trigger audit_capture_our_nameservers after insert or update or delete on our_nameservers for each row execute function audit_capture();
create trigger audit_capture_domain_nameservers after insert or update or delete on domain_nameservers for each row execute function audit_capture();
create trigger audit_capture_dns_zones after insert or update or delete on dns_zones for each row execute function audit_capture();
create trigger audit_capture_dns_records after insert or update or delete on dns_records for each row execute function audit_capture();
create trigger audit_capture_zone_publish after insert or update or delete on zone_publish for each row execute function audit_capture();
create trigger audit_capture_mail_services after insert or update or delete on mail_services for each row execute function audit_capture();
create trigger audit_capture_server_services after insert or update or delete on server_services for each row execute function audit_capture();
create trigger audit_capture_dbms_services after insert or update or delete on dbms_services for each row execute function audit_capture();
create trigger audit_capture_security_services after insert or update or delete on security_services for each row execute function audit_capture();
create trigger audit_capture_provisioning_jobs after insert or update or delete on provisioning_jobs for each row execute function audit_capture();
create trigger audit_capture_registry_events after insert or update or delete on registry_events for each row execute function audit_capture();
create trigger audit_capture_customer_tax_profiles after insert or update or delete on customer_tax_profiles for each row execute function audit_capture();
create trigger audit_capture_tax_documents after insert or update or delete on tax_documents for each row execute function audit_capture();
create trigger audit_capture_tax_document_lines after insert or update or delete on tax_document_lines for each row execute function audit_capture();
create trigger audit_capture_payment_notice_mails after insert or update or delete on payment_notice_mails for each row execute function audit_capture();
create trigger audit_capture_domain_ds_records after insert or update or delete on domain_ds_records for each row execute function audit_capture();