create type group_role_v2 as enum ('MEMBER', 'GROUP_ADMIN', 'GROUP_OWNER');

alter table group_members
  alter column role type group_role_v2
  using role::text::group_role_v2;

drop type group_role;
alter type group_role_v2 rename to group_role;

with ranked_admins as (
  select
    group_id,
    user_id,
    row_number() over (partition by group_id order by created_at asc, user_id asc) as rn
  from group_members
  where role = 'GROUP_ADMIN'
)
update group_members gm
set role = 'GROUP_OWNER'
from ranked_admins ra
where gm.group_id = ra.group_id
  and gm.user_id = ra.user_id
  and ra.rn = 1;

create unique index group_members_owner_unique on group_members(group_id) where role = 'GROUP_OWNER';

alter table users
  add column last_login_at timestamptz;

alter table group_members
  add column last_active_at timestamptz;

create type group_join_policy as enum ('NOT_ACCEPTING', 'AUTO_ACCEPT', 'APPROVAL_REQUIRED');

alter table group_settings
  add column join_policy group_join_policy not null default 'NOT_ACCEPTING';

create type group_join_request_status as enum ('PENDING', 'APPROVED', 'DENIED', 'CANCELED');

create table group_join_requests(
  id bigserial primary key,
  group_id bigint not null references groups(id) on delete cascade,
  user_id bigint not null references users(id) on delete cascade,
  status group_join_request_status not null default 'PENDING',
  decided_by bigint references users(id) on delete set null,
  decided_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create unique index group_join_requests_pending_unique
  on group_join_requests(group_id, user_id)
  where status = 'PENDING';

create index group_join_requests_group_idx on group_join_requests(group_id);

create table group_invite_links(
  id bigserial primary key,
  group_id bigint not null references groups(id) on delete cascade,
  created_by bigint not null references users(id) on delete cascade,
  token text not null unique,
  policy group_join_policy not null,
  single_use boolean not null default false,
  expires_at timestamptz not null,
  used_at timestamptz,
  revoked_at timestamptz,
  created_at timestamptz not null default now()
);

create index group_invite_links_group_idx on group_invite_links(group_id);

create table group_audit_log(
  id bigserial primary key,
  group_id bigint not null references groups(id) on delete cascade,
  actor_user_id bigint references users(id) on delete set null,
  actor_role group_role,
  event_type text not null,
  request_id text not null,
  ip inet,
  user_agent text,
  success boolean not null default true,
  error_code text,
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create index group_audit_log_group_idx on group_audit_log(group_id, created_at desc);
create index group_audit_log_request_idx on group_audit_log(request_id);