alter type user_role add value if not exists 'technik';

create table if not exists chat_messages (
  id uuid primary key default gen_random_uuid(),
  sender_id uuid not null references users(id) on delete cascade,
  recipient_id uuid not null references users(id) on delete cascade,
  body text not null,
  read_at timestamptz,
  created_at timestamptz not null default now(),
  constraint chat_messages_no_self_message check (sender_id <> recipient_id),
  constraint chat_messages_body_check check (length(trim(body)) between 1 and 4000)
);

create index if not exists chat_messages_sender_idx on chat_messages (sender_id, created_at desc);
create index if not exists chat_messages_recipient_idx on chat_messages (recipient_id, created_at desc);
create index if not exists chat_messages_unread_idx on chat_messages (recipient_id, read_at, created_at desc);
