create table if not exists public.messages (
    id bigint generated by default as identity primary key,
    room text not null,
    username text not null,
    message_type text not null check (message_type in ('text', 'file')),
    text text,
    file_url text,
    file_path text,
    file_name text,
    file_type text,
    file_size integer,
    created_at timestamptz not null default now(),
    expires_at timestamptz not null default (now() + interval '5 hours')
);

alter table public.messages
    add column if not exists file_path text;

alter table public.messages
    add column if not exists expires_at timestamptz;

update public.messages
set expires_at = created_at + interval '5 hours'
where expires_at is null;

alter table public.messages
    alter column expires_at set default (now() + interval '5 hours');

alter table public.messages
    alter column expires_at set not null;

create index if not exists messages_room_created_at_idx
    on public.messages (room, created_at desc);

create index if not exists messages_expires_at_idx
    on public.messages (expires_at);

alter table public.messages enable row level security;

drop policy if exists "Allow public read messages" on public.messages;