-- 32절. 로그인 실패 잠금과 허용 주소.
-- 001_init.sql, 002_audit.sql, 003_notice.sql, 004_staff.sql 은 고치지 않는다.
-- audit_capture 와 audit_redact 는 갈아끼우지 않는다.

create table login_locks (
  id bigint generated always as identity primary key,
  subject text not null,
  customer_id bigint references customers (id) on delete restrict,
  staff_id bigint references staff (id) on delete restrict,
  failed_count integer not null,
  window_started_at timestamptz not null,
  locked_until timestamptz,
  created_at timestamptz not null default now(),
  check (
    (subject = 'customer' and customer_id is not null and staff_id is null)
    or (subject = 'staff' and staff_id is not null and customer_id is null)
  ),
  check (
    failed_count >= 0
    and failed_count <= 5
    and (
      (failed_count = 5 and locked_until is not null)
      or (failed_count < 5 and locked_until is null)
    )
  )
);

create unique index uq_login_locks_customer
  on login_locks (customer_id)
  where customer_id is not null;

create unique index uq_login_locks_staff
  on login_locks (staff_id)
  where staff_id is not null;

create function login_lock_writer() returns trigger
language plpgsql
set search_path = pg_catalog, public
as $$
declare
  kind text;
  sid_text text;
  sid bigint;
  actor_disabled timestamptz;
begin
  kind := nullif(btrim(current_setting('intranet.actor_kind', true)), '');
  if kind is null or kind = 'system' then
    if tg_op = 'DELETE' then
      return old;
    end if;
    return new;
  end if;
  if kind = 'customer' then
    raise exception 'customer cannot write login_locks';
  end if;
  if kind = 'staff' then
    sid_text := nullif(btrim(current_setting('intranet.actor_staff_id', true)), '');
    if sid_text is null then
      raise exception 'staff actor row is missing';
    end if;
    sid := sid_text::bigint;
    select s.disabled_at into actor_disabled from staff s where s.id = sid;
    if not found or actor_disabled is not null then
      raise exception 'disabled staff cannot write login_locks';
    end if;
    if tg_op = 'DELETE' then
      return old;
    end if;
    return new;
  end if;
  raise exception 'invalid actor_kind %', kind;
end;
$$;

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

create table login_ip_allows (
  id bigint generated always as identity primary key,
  subject text not null,
  customer_id bigint references customers (id) on delete restrict,
  staff_id bigint references staff (id) on delete restrict,
  address inet not null,
  created_at timestamptz not null default now(),
  check (
    (subject = 'customer' and customer_id is not null and staff_id is null)
    or (subject = 'staff' and staff_id is not null and customer_id is null)
  ),
  check (
    (family(address) = 4 and masklen(address) = 32)
    or (family(address) = 6 and masklen(address) = 128)
  )
);

create unique index uq_login_ip_allows_customer
  on login_ip_allows (customer_id, address)
  where customer_id is not null;

create unique index uq_login_ip_allows_staff
  on login_ip_allows (staff_id, address)
  where staff_id is not null;

create function login_ip_allow_owner() returns trigger
language plpgsql
set search_path = pg_catalog, public
as $$
declare
  kind text;
  cid_text text;
  cid bigint;
  row_customer bigint;
  row_staff bigint;
  row_subject text;
begin
  kind := nullif(btrim(current_setting('intranet.actor_kind', true)), '');
  if kind is null or kind = 'system' then
    if tg_op = 'DELETE' then
      return old;
    end if;
    return new;
  end if;
  if kind <> 'customer' and kind <> 'staff' and kind <> 'system' then
    raise exception 'invalid actor_kind %', kind;
  end if;
  if kind = 'customer' then
    cid_text := nullif(btrim(current_setting('intranet.actor_customer_id', true)), '');
    if cid_text is null then
      raise exception 'customer actor requires actor_customer_id';
    end if;
    cid := cid_text::bigint;
    if tg_op = 'UPDATE' then
      if old.subject is distinct from 'customer'
         or old.staff_id is not null
         or old.customer_id is distinct from cid
         or new.subject is distinct from 'customer'
         or new.staff_id is not null
         or new.customer_id is distinct from cid then
        raise exception 'customer can only write own login_ip_allows';
      end if;
      return new;
    end if;
    if tg_op = 'DELETE' then
      row_subject := old.subject;
      row_customer := old.customer_id;
      row_staff := old.staff_id;
    else
      row_subject := new.subject;
      row_customer := new.customer_id;
      row_staff := new.staff_id;
    end if;
    if row_subject is distinct from 'customer'
       or row_staff is not null
       or row_customer is distinct from cid then
      raise exception 'customer can only write own login_ip_allows';
    end if;
    if tg_op = 'DELETE' then
      return old;
    end if;
    return new;
  end if;
  if tg_op = 'DELETE' then
    return old;
  end if;
  return new;
end;
$$;

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

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